0

I've got a table that suddenly started throwing up these errors on every insert statement.

I've solved the problem once, but now it's back and (due to management pressure) I need to know the cause and guarantee that it does not happen again. Here's the relevant data:

  • id: INT(11) PK AI
  • COUNT(id) -- 274;
  • MAX(id) -- 309;
  • SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name='groups' -- 0
  • Engine: InnoDB
  • Server uptime: 219091

I know how to fix the symptoms, now I need to find the source. Has anybody else tried digging deeper?

P.S.: None of my update/insert statements ever touch the id column.

Update, actions taken:

  • Enabled binlog. Hopefully it won't fill the entire hdd before the error shows up again.
  • Found an insert ... id = $id ... where id = $id situation. Shouldn't be the cause, but killed it with fire anyways.
  • Added a try..catch control in the spot I got the exception that resets AUTO_INCREMENT to 1 automagically and re-tries the erroneous query, in addition to manually logging the incident.
  • Prayed that this works.
Maxim Kumpan
  • 2,545
  • 2
  • 19
  • 23
  • someone may have manually(automaticaly using script) inserted a record along with an id value. When the auto increment value reaches the inserted id and tries to add new record it throws an error. – Bere Jun 06 '13 at 08:36
  • Looks like your using innodb ? Has there been a mysql server restart recently ? – exussum Jun 06 '13 at 08:38
  • Improbable. Nobody but myself has code access and direct database access is less than likely. – Maxim Kumpan Jun 06 '13 at 08:38
  • No, server uptime added to list of data in original post. – Maxim Kumpan Jun 06 '13 at 08:39
  • Inserting a record with a random id (which is higher then the auto_increment value) will just set the auto_increment to that higher value+1, so that can't be the problem, _unless_ the maximum integer value is added... However, we can speculate all we want: do you have a binlog enabled, and can you look up any alterations on the table there? – Wrikken Jun 06 '13 at 08:39
  • I have full server access, I can try enabling binlog. Never used it before, though. – Maxim Kumpan Jun 06 '13 at 08:42
  • Actually, no, that is a problem. I cannot afford a repeat of this bug in production and it never occurred in development. Enabling binlog will not help until the cause is found, which defeats the purpose of enabling binlog. We cannot reproduce the bug without finding the cause. – Maxim Kumpan Jun 06 '13 at 08:49
  • why don't you enable binlog, and put a workaround in your code for the time being? – Marian Theisen Jun 06 '13 at 09:00
  • Hmm. I could capture the error and run the workaround directly in the code, that might help. Just so long as I'm the only one to notice the bloody thing. x_X – Maxim Kumpan Jun 06 '13 at 09:45

0 Answers0