641067 - update sync sql schema to match production

Status: RESOLVED FIXED

(Cloud Services Graveyard :: Server: Sync, defect)

Description

[:Atoll]

we discovered that the production schema does not match what is in hg, leading to data issues in sync1..5.  please verify that the sync sql data in hg matches production.

CREATE TABLE `wbo22` (
  `username` int(11) NOT NULL,
  `collection` smallint(6) NOT NULL DEFAULT '0',
  `id` varbinary(64) NOT NULL DEFAULT '',
  `parentid` varbinary(64) DEFAULT NULL,
  `predecessorid` varbinary(64) DEFAULT NULL,
  `sortindex` int(11) DEFAULT NULL,
  `modified` bigint(20) DEFAULT NULL,
  `payload` longtext,
  `payload_size` int(11) DEFAULT NULL,
  `ttl` int(11) DEFAULT NULL,
  PRIMARY KEY (`username`,`collection`,`id`),
  KEY `ttl_idx` (`ttl`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Comment 1

[Toby Elliott [:telliott]]

Isn't there a

  KEY `modified` (`username`,`collection`,`modified`),

in there? That should be essential for all the "select * from wbo where uid = foo and collection = bar and modified > X" queries that the server does.

We did decide to get rid of the payload_size index and just take the hit when we aren't in memcache, but this one seems crucial.

Comment 2

[:Atoll]

That seems reasonable to me, but it's not present on the samples I checked, sorry.

Comment 3

[Toby Elliott [:telliott]]

Ugh. That means we've had a schema in prod that has never matched anything we've tested.

OTOH, it seems to be holding up OK, which leaves me with a dilemma.

Comment 4

[:Atoll]

Advise committing the prod schema as written, and then opening a bug to fix it to match what you think it should be.

Comment 5

[Toby Elliott [:telliott]]

no more php