Mysql – Auto Increment Primary ID inserting random values

auto-incrementMySQLprimary-key

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.

Best Answer

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.

Related Solutions

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Best Answer

Related Solutions

Mysql – Finding rows for a specified date range

Mysql – Adding index to large thesql tables

Related Question