The mysql auto_increment column is incremented by a random value

Question

Today, I came across one of the strangest things in MySQL that I have seen. I have a trivial table:

CREATE TABLE `features` ( `feature_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT, `feature_name` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, PRIMARY KEY (`feature_id`), UNIQUE KEY `feature_name_key` (`feature_name`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 

I am inserting data internally using the Java library and mysql-connector-java-5.1.15. The data in feature_name can be duplicated, and I want only unique values. I can use INSERT IGNORE, but in case the data is too long, I can ignore it, so I use this:

 pstmt = conn.prepareStatement( "INSERT INTO features (feature_name) VALUES (?)" ); for ( String featureName: data4db.keySet() ) { pstmt.setString(1, featureName ); try { pstmt.executeUpdate(); } catch ( SQLException se ) { if ( se.getErrorCode() == 1062 ) // duplicate entry { continue; // ignore } throw se; // do not ignore anything else } } 

After the data was inserted into db, I noticed that there were some problems that I did not even expect. The table above contains about 4,000 entries. The only problem is that some data cannot be inserted due to the duplication of the primary key, so I looked at what the auto inc values ​​for this table look like. It turns out that for most of the data, the next adjacent row of rows has been increased by 1, as expected. For some reason, I don’t know, sometimes the feature_id function was increased by 3, 5, 1000, 100000 - a completely random value. Therefore, I “did not manage to take a place in this table”, since it cannot be inserted after id reaches max val for the int environment.

How can this happen? Has anyone come across a similar one? It is worth saying that in this table there was only one program with one thread. I have another table, almost identical - the width of the columns and the names are different. There is a similar problem for this.

BTW - some more data:

 mysql> show global variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.01 sec) mysql> show global variables like 'ver%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 5.5.10 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86 | | version_compile_os | Win32 | +-------------------------+------------------------------+ 

Thanks for any tips in advance.

Answer 1

This is normal MySQL behavior. The following happened: you inserted the data before the keyword auto_increment 3, after which you got a duplicated key, because the unique function feature_name_key. The fact is that MySQL will “send” the integer 4 and move on to the next, it will not reuse integers that could not be written due to the key constraint.

If you have something like this:

 PK | feature_name_key 1 | key1 2 | key2 3 | key3 4 | key1 (fails due to constraint, 4 is not going to be used for next successful insertion, hence the gaps in your primary key) 

then you lose the integers available for the primary key / auto _increment. Rethink your strategy when inserting or building a table to store your data.

Answer 2

Are there rows deleted between your inserts? MySQL may just remember the auto increment counter.