Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

CREATE TABLE `txn` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`client_id` int(11) DEFAULT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27368 DEFAULT CHARSET=latin1;

Thanks.

share|improve this question
    
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? –  ypercube Jan 22 at 19:04
    
Check for triggers. –  Mihai Jan 22 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 at 20:08
    
Might be some kind of overflow,but I doubt it.Weird. –  Mihai Jan 22 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 at 6:36

1 Answer 1

up vote 2 down vote accepted

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.

mysql> CREATE TRIGGER txn_bu BEFORE UPDATE ON txn FOR EACH ROW 
       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

mysql>

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.

share|improve this answer
    
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 at 5:37
1  
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 at 3:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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