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