Updating AUTO_INCREMENT value of all tables in a M

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

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.

Answer 2

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...

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.

Answer 4

set @db = 'your_db_name';

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

Then copy-paste and run the output you get.

Answer 5

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.

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();