Today I was asked to look into this problem and I have never seen anything like this before. A table in our MySQL database, with a primary IDs column, which is set to autoincrement int, had some weird IDs. So the sequence goes as follows:

enter image description here

Some more random ids

enter image description here

You can see between 27243 and 27245, and 27324 and 27325 there are some different numbers inserted. This has caused a lot of problem for us since there are FK in another table which are now missing or messed up. Even today, there was one similar incidence.

In my many years of dealing with MySQL, this is the first time ever I am seeing something like this. What could have been the reason and how to correct it?

There are no triggers. Currently table create statement shows:

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`client_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)


  • Show us the CREATE TABLE statement. Perhaps some code is overriding the AUTO_INCREMENT property. And are you sure these "weird" rows were inserted recently - could they be older, updated rows? Jan 22, 2014 at 19:04
  • Check for triggers.
    – Mihai
    Jan 22, 2014 at 19:07
  • There is no code which interacts with the primary key column. Also there are no triggers. I updated the information in the question. Is there some bug in MySQL?
    – zeeshan
    Jan 22, 2014 at 20:08
  • Might be some kind of overflow,but I doubt it.Weird.
    – Mihai
    Jan 22, 2014 at 20:47
  • 1
    You say that there "is no code which interacts with the primary key column", but also you say that some rows do get sometimes updated. Have you considered the possibility that an update touches the timestamp? And if there are broken foreign key relationships, do you know if that happens only on such rows that have had an update? What I am after here is some sort of an application bug.
    – zagrimsan
    Jan 23, 2014 at 6:36

1 Answer 1


I'm having a very difficult time thinking of any possible misbehaving mechanism inside MySQL or InnoDB that could cause this behavior as the result of a bug, without throwing errors or causing a crash. It seems much more likely that the table is being updated by the application. Instead of writing new rows, something is finding and updating old ones.

If that's not supposed to be happening, I would put a trigger on this table to block those updates.

       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'this table does not support updates';
Query OK, 0 rows affected (0.14 sec)

mysql> UPDATE txn SET `timestamp` = NOW() WHERE id = 1;
ERROR 1644 (45000): this table does not support updates


This makes directly updating rows impossible. Note that you don't need the usual DELIMITER change and BEGIN/END blocks because the trigger is comprised of a single simple statement.

If the table is never updated then the trigger would never fire, so there's no significant overhead associated with having it defined. If it sees an update query then it's going to throw an exception back to the application which should generate exception reports or support tickets. Arguably, if your application has a defect that's stomping data, you want it to fail hard, instead of soft.

  • This could have been a good approach. But some cells occasionally require an UPDATE. But still, they shouldn't mess up the primary keys in any way. I believe it is some bug somewhere, I spend whole day figuring out if it was my code. For now, I'll be restarting MySQL weekly, and keep a close eye on this issue.
    – zeeshan
    Jan 23, 2014 at 5:37
  • 2
    After days of troubleshooting and going over various logs (good habit that I always keep logs of database servers and web servers even if they grow very large, for at least a month), I have found out, as you said, that some programming bug caused to UPDATE these old rows with new values instead of doing an INSERT. As a result, the timestamp column also got updated, which is what we see. There is nothing wrong with AUTO INCREMENT and MySQL is working just fine. Some of our records got lost due to this issue. But there is a backup to refer to if required. Now I have to find this programming bug.
    – zeeshan
    Jan 31, 2014 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.