Updating AUTO_INCREMENT value of all tables in a MySQL database

Question

It is possbile set/reset the AUTO_INCREMENT value of a MySQL table via

ALTER TABLE some_table AUTO_INCREMENT = 1000

However I need to set the AUTO_INCREMENTupon its existing value (to fix M-M replication), something like:

ALTER TABLE some_table SET AUTO_INCREMENT = AUTO_INCREMENT + 1 which is not working

Well actually, I would like to run this query for all tables within a database. But actually this is not very crucial.

I could not find out a way to deal with this problem, except running the queries manually. Will you please suggest something or point me out to some ideas.

Thanks

Odie

This is not working as well: ALTER TABLE my_db.customer auto_increment = ( SELECT auto_increment FROM information_schema.tables WHERE table_name = 'customer' ) – Odie

Since you mentioned M-M replication: Is there really a need to touch AUTO_INCREMENT in order to make replication work? I usually just set the auto-increment-increment and auto-increment-offset values in my.cnf. – Sherrod

@Sherrod yes you are right. I also have the same configuration which was working OK. But to re-sync two databases after a hardware failure, I had to dump everything from one DB and import it to another. During the process there were some errors. Surprisingly, they are gone without me doing anything. – Odie

Answer 1

Using:

ALTER TABLE some_table AUTO_INCREMENT = 0

...will reset the auto_increment value to be the next value based on the highest existing value in the auto_increment column.

To run this over all the tables, you'll need to use MySQL's dynamic SQL syntax called PreparedStatements because you can't supply the table name for an ALTER TABLE statement as a variable. You'll have to loop over the output from:

SELECT t.table_name
  FROM INFORMATION_SCHEMA.TABLES t
 WHERE t.table_schema = 'your_database_name'

...running the ALTER TABLE statement above for each table.

Cardholder

ALTER TABLE some_table AUTO_INCREMENT = 0 did not work as expected. On the other hand I might also need to set the auto_increment value explicitly. Like increasing it by 9. This thing is getting complicated. I suspect I am at the wrong direction. Maybe I should find another way. – Odie

@celalo: I disagree that AUTO_INCREMENT = 0 does not work - I tested it myself before posting it: I added three records, updated the auto_increment to 100 before inserting a fourth record. Deleted the record whose id value was 100, then ran the AUTO_INCREMENT = 0 before inserting a fifth record. The record inserted as one higher than the existing third record. – Cardholder

Up to and including MySQL 5.5, documentation stated: For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed. As of MySQL 5.6, documentation now states: For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one. – Hadfield

Answer 2

set @db = 'your_db_name';

SELECT concat('ALTER TABLE ', @db, '.', TABLE_NAME, ' AUTO_INCREMENT = 0;') 
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @db AND TABLE_TYPE = 'BASE TABLE'

Then copy-paste and run the output you get.

Cabbage

This works really well and is very simple to use. Remember: don't forget to replace your_db_name with your actual database name first! – Instancy

nice. haven't done it, but this can be put in an sp as well and each record wrapped around a PreparedStatement to execute – Issiah

Thanks, this helps for more than just what I was initially looking for. – Excrescence

Answer 3

In the below instructions you will need to replace everything that is in [brackets] with your correct value. BACKUP BEFORE ATTEMPTING.

If you can login to mysql as root through the command line then you could do the following to reset the auto_increment on all tables, first we will construct our queries which we want to run:

Make a database backup:

mysqldump -u [uname] -p [dbname] | gzip -9 > [backupfile.sql.gz]

Login:

mysql -u root -p

Set the group_concat_max_length to a higher value so our list of queries doesn't get truncated:

SET group_concat_max_len=100000;

Create our list of queries by using the following:

SELECT GROUP_CONCAT(CONCAT("ALTER TABLE ", table_name, " AUTO_INCREMENT = 0") SEPARATOR ";") FROM information_schema.tables WHERE table_schema = "[DATABASENAME]";

Then you will receive a long string of mysql queries followed by a bunch of dashes. Copy the string of queries to your clipboard, it will look something similar to:

ALTER table1 AUTO_INCREMENT = 0;ALTER table2 AUTO_INCREMENT = 0;...continued...

Change to the database you would like to run the command on:

USE [DATABASENAME];

Then paste the string that is on your clipboard and hit enter to run it. This should run the alter on every table in your database.

Messed up? Restore from your backup, be sure to logout of mysql before running the following (just type exit; to do so)

gzip -d < [backupfile.sql.gz] | mysql -u [uname] -p [dbname]

I will not take responsibility for any damage cause by your use of any of these commands, use at your own risk.

Morbilli

Answer 4

Assuming that you must fix this by amending the auto-increment column rather than the foreign keys in the table decomposing the N:M relationship, and that you can predict what the right values are, try using a temporary table where the relevant column is not auto-increment, then map this back in place of the original table and change the column type to auto-increment afterwards, or truncate the original table and load the data from the temp table.

Stocks

Answer 5

I found this gist on github and it worked like a charm for me: https://gist.github.com/abhinavlal/4571478

The command:

mysql -Nsr -e "SELECT t.table_name FROM INFORMATION_SCHEMA.TABLES t WHERE t.table_schema = 'DB_NAME'" | xargs -I {} mysql DB_NAME -e "ALTER TABLE {} AUTO_INCREMENT = 1;"

If your DB requires a password, you unfortunately have to put that in the command for it to work. One work-around (still not great but works) is to put the password in a secure file. You can always delete the file after so the password doesn't stay in your command history:

 ... | xargs -I {} mysql -u root -p`cat /path/to/pw.txt` DB_NAME -e...

Phelps

Answer 6

I have written below procedure change the database name and execute the procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `setAutoIncrement`()
BEGIN
DECLARE done int default false;
    DECLARE table_name CHAR(255);
DECLARE cur1 cursor for SELECT t.table_name FROM INFORMATION_SCHEMA.TABLES t 
        WHERE t.table_schema = "buzzer_verifone";

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;

    myloop: loop
        fetch cur1 into table_name;
        if done then
            leave myloop;
        end if;
        set @sql = CONCAT('ALTER TABLE ',table_name, ' AUTO_INCREMENT = 1');
        prepare stmt from @sql;
        execute stmt;
        drop prepare stmt;
    end loop;

    close cur1;
END

Execute the procedure above using below line

Call setAutoIncrement();

Benfield

Answer 7

The Quickest solution to Update/Reset AUTO_INCREMENT in MySQL Database

Ensure that the AUTO_INCREMENT column has not been used as a FOREIGN_KEY on another table.

Firstly, Drop the AUTO_INCREMENT COLUMN as:

ALTER TABLE table_name DROP column_name

Example: ALTER TABLE payments DROP payment_id

Then afterward re-add the column, and move it as the first column in the table

ALTER TABLE table_name ADD column_name DATATYPE AUTO_INCREMENT PRIMARY KEY FIRST

Example: ALTER TABLE payments ADD payment_id INT AUTO_INCREMENT PRIMARY KEY FIRST

Ronna

This is not a good solution at all. Maybe you had deleted the user 5, and now the user with id 6 becomes the user 5, etc... – Cabbage

Answer 8

Reset mysql table auto increment was very easy, we can do it with single query, please see this http://webobserve.blogspot.com/2011/02/reset-mysql-table-autoincrement.html.

Googol

Please summarize the contents of the link; simply posting a link is not helpful. – Shamrock