SearchEngineUpdater time zone retrieval skipping IDs

Re: SEupdate / DB issue?

Jeff Theroux

Sent: Wednesday, January 25, 2012 4:32 PM

To:

Caitlyn Duggan

Cc:

Jeff Shih; Gavin Nicol; Jeffrey Collemer; Jonah Edwards

Attachments:

We still have a problem of incrementing more than 1 id at a time. When

we first added the table, it was incrementing linearly. Then it jumped

from 5766 to 16770 and then to 65535, then over 100 ids to 65642. Any

idea what would cause this? Could it have something to do with multiple

apps running concurrently and querying/inserting into this table.

How long has that error in the SEUpdate users/accounts jobs been

happening for? We can use the below query to monitor the situation.

Fortunately, since no tables reference the ID column, it would be safe

to reset IDs.

select now(), max(id), count(1), (max(id) - 65535) as 'diff from max

smallint unsigned' from searchEngineTimeZones\G

• • • • • • • • • • • • • • • • • • • • • • • • • • • 1. row ***************************

now(): 2012-01-25 13:17:17

max(id): 70595

count(1): 5820

diff from max smallint unsigned: 5060

select now(), max(id), count(1), (max(id) - 65535) as 'diff from max

smallint unsigned' from searchEngineTimeZones\G

• • • • • • • • • • • • • • • • • • • • • • • • • • • 1. row ***************************

now(): 2012-01-25 13:23:11

max(id): 70597

count(1): 5822

diff from max smallint unsigned: 5062

pager less ; select id from searchEngineTimeZones order by id asc limit

5500, 200;

<snip>

select id from searchEngineTimeZones order by id asc limit 20;

----

----

----

On 01/25/2012 04:11 PM, Caitlyn Duggan wrote:

> It looks like there is an seupdate.sh --accounts job running right now (screenshot of Cluster in UI attached). Once it's done stderr can be looked at. Maybe there are still problems happening, causing the IDs to jump?

> ________________________________________

> From: Jeff Theroux

> Sent: Wednesday, January 25, 2012 4:03 PM

> To: Caitlyn Duggan

> Cc: Jeff Shih; Gavin Nicol; Jeffrey Collemer; Jonah Edwards

> Subject: Re: SEupdate / DB issue?

>

> I believe the default behavior of SHOW CREATE TABLE is to include the

> next auto increment value, but I'm also confused why it jumped from id

> 5766 to id 65535 (the max value of UNsigned SMALLINT). I'm concerned it

> could happen again, and jump to the max int value (2147483647).

>

> Since the mysql doc seems to be down...

> http://help.scibit.com/mascon/masconMySQL_Field_Types.html

>

> select * from searchEngineTimeZones where id> 5000 and id< 6000 order

> by id asc;

>

> ...<snip>

> | 5757 | 3 | 1325 | 1 |

> | 5766 | 3 | 801 | 62 |

> --------------------------------------------------+

> 522 rows in set (0.05 sec)

>

>

> mysql> select count(1) from searchEngineTimeZones where id> 65534;

> ----------

> | count(1) |

> ----------

> | 296 |

> ----------

> 1 row in set (0.02 sec)

>

>

> mysql> select min(id) from searchEngineTimeZones;

> ---------

> | min(id) |

> ---------

> | 2 |

> ---------

> 1 row in set (0.02 sec)

>

> mysql> select max(id) from searchEngineTimeZones;

> ---------

> | max(id) |

> ---------

> | 70359 |

> ---------

> 1 row in set (0.02 sec)

>

> mysql> select * from searchEngineTimeZones where id=65534;

> Empty set (0.02 sec)

>

> mysql> select * from searchEngineTimeZones where id=65535;

> ---------------------------------------------------+

> | id | distributionID | searchEngineMapperID | timeZoneID |

> ---------------------------------------------------+

> | 65535 | 147 | 59047 | 4 |

> ---------------------------------------------------+

> 1 row in set (0.02 sec)

>

> mysql> select * from searchEngineTimeZones where id=65533;

> Empty set (0.02 sec)

>

> mysql> select * from searchEngineTimeZones where id=65532;

> Empty set (0.02 sec)

>

> mysql> select * from searchEngineTimeZones where id=65536;

> Empty set (0.02 sec)

>

> mysql> select * from searchEngineTimeZones where id=70359;

> ---------------------------------------------------+

> | id | distributionID | searchEngineMapperID | timeZoneID |

> ---------------------------------------------------+

> | 70359 | 147 | 59498 | 1 |

> ---------------------------------------------------+

> 1 row in set (0.02 sec)

>

>

> On 01/25/2012 03:26 PM, Caitlyn Duggan wrote:

>> Notice in SHOW CREATE TABLE searchEngineTimeZones; AUTO_INCREMENT=65536

>>

>> I'm not sure how that got set to that.. should it be changed?

>> ________________________________________

>> From: Caitlyn Duggan

>> Sent: Wednesday, January 25, 2012 3:06 PM

>> To: Caitlyn Duggan; Jeff Theroux

>> Cc: Jeff Shih; Gavin Nicol; Jeffrey Collemer; Jonah Edwards

>> Subject: RE: SEupdate / DB issue?

>>

>> Updated on all slaves and master.

>>

>> Jeff Shih - I also ran seupdate again for oodle to verify no exceptions are thrown (log on xml-06: /tmp/2012-01-25/oodleautosseupdate.log2). Maybe try running data for them again?

>>

>> mysql> show create table searchEngineTimeZones;

>> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

>> | Table | Create Table |

>> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

>> | searchEngineTimeZones | CREATE TABLE `searchEngineTimeZones` (

>> `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

>> `distributionID` smallint(5) unsigned NOT NULL,

>> `searchEngineMapperID` mediumint(9) NOT NULL,

>> `timeZoneID` smallint(5) unsigned NOT NULL,

>> PRIMARY KEY (`id`),

>> UNIQUE KEY `distributionID_searchEngineMapperID` (`distributionID`,`searchEngineMapperID`)

>> ) ENGINE=InnoDB AUTO_INCREMENT=65536 DEFAULT CHARSET=latin1 PACK_KEYS=0 |

>> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

>> 1 row in set (0.02 sec)

>>

>> mysql> ALTER TABLE `tsacommon`.`searchEngineTimeZones` MODIFY COLUMN `id` INT NOT NULL AUTO_INCREMENT;

>> Query OK, 5523 rows affected (0.44 sec)

>> Records: 5523 Duplicates: 0 Warnings: 0

>>

>> mysql> show create table searchEngineTimeZones;

>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

>> | Table | Create Table |

>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

>> | searchEngineTimeZones | CREATE TABLE `searchEngineTimeZones` (

>> `id` int(11) NOT NULL AUTO_INCREMENT,

>> `distributionID` smallint(5) unsigned NOT NULL,

>> `searchEngineMapperID` mediumint(9) NOT NULL,

>> `timeZoneID` smallint(5) unsigned NOT NULL,

>> PRIMARY KEY (`id`),

>> UNIQUE KEY `distributionID_searchEngineMapperID` (`distributionID`,`searchEngineMapperID`)

>> ) ENGINE=InnoDB AUTO_INCREMENT=65536 DEFAULT CHARSET=latin1 PACK_KEYS=0 |

>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

>> 1 row in set (0.06 sec)

>>