Menu
It could have multiple causes: Check if the auto_increment value on the table itself, has the next highest value. Mind that if you have transactions where you INSERT a row and rollback the transaction, that auto_increment value will be gone/skipped.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
You use TRANCATE table to empty the table. TRUNCATE not only deletes the rows but resets the auto increment value by design. Use DELETE FROM table instead.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
Please do not change the auto_increment_increment. ClearDB is doing this on purpose. It's explained in the documentation:
ClearDB uses circular replication to provide master-master MySQL support. As such, certain things such as auto_increment keys (or sequences) must be configured in order for one master not to use the same key as the other, in all cases. We do this by configuring MySQL to skip certain keys, and by enforcing MySQL to use a specific offset for each key used. The reason why we use a value of 10 instead of 2 is for future development.
Check to see the seed value of the autoincrement isn't set to 10.
You can check by:
SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want';
As noted elsewhere you can change by using the system variable @@set_auto_increment_increment
SET @@auto_increment_increment=1;
If you want to start the values at a number other than one you can go:
ALTER TABLE tbl AUTO_INCREMENT = 100;
Thanks @Jim Fiorato for providing the link.
To check how much the auto increment value increments by, use the following query:
SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 4 |
+--------------------------+-------+
The auto increment increment value is set in the MySQL system variables.
See here: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_auto-increment-increment
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With