i am migrating data from one db to another. A table is getting populated from combination of 4 tables. I am using stored procedures to populate new table. When i run it first time with some condition(where clause) it populates the table but when i run it second time with another condition, it skips few values in Id(auto incremented).
-
ALTER TABLE table_name AUTO_INCREMENT = XXX but I think - it not help, for more help need check parts of code of You procedure and business logic, it look like - You have other problem, not related to autoincrement values– a_vladNov 24, 2015 at 7:08
-
Be sure to check the results -- depending on particular values of an AI is asking for trouble.– Rick JamesDec 2, 2015 at 18:52
2 Answers
No need to ALTER table its quite overkill. I use to do a fake insert in rollbacked transaction to do the trick.
CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT);
No rows on the table:
MariaDB [mfo]> SELECT COUNT(id) FROM test;
+-----------+
| COUNT(id) |
+-----------+
| 0 |
+-----------+
1 row in set (0.01 sec)
Open a transaction and set your desired value, finally rollback it to not insert the line.
MariaDB [mfo]> BEGIN;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mfo]> INSERT INTO test VALUES (300);
Query OK, 1 row affected (0.00 sec)
MariaDB [mfo]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
The AUTO_INCREMENT was change by that transaction so the next value will be 301:
MariaDB [mfo]> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
So we change the auto_increment value without ALTER the table, note that as the transaction was rollbacked there is still 0 line in my table:
MariaDB [mfo]> SELECT COUNT(id) FROM test;
+-----------+
| COUNT(id) |
+-----------+
| 0 |
+-----------+
1 row in set (0.01 sec)
Last note, we force the auto_increment value to 300 in our transaction so the 300 is no more available, the next one will be 301 so don't forget to force it with your_value - 1.
Max.
The following should do what you want where x is a value greater than the maximum value that appears in the column
ALTER TABLE table_name AUTO_INCREMENT = x
The cause of the skipped values is probably either failed inserts (where the insert fails a unique key check or similar) or insert on duplicate update
queries both of which increment the auto_increment without creating a row.
If this is a problem for your migration you may be better off creating two copies of your new table table_name
and table_name_temp
. Use your stored procedures to populate table_name_temp
ignoring the gaps in the auto increment. Once table_name_temp
is fully populated you can populate table_name
with an insert select query from the table_name_temp
of the table.
INSERT INTO table_name SELECT <all columns except the auto increment> FROM table_name_temp;
This will create a table with a continuous auto_increment