My contemplations and diurnal novelties are publicized here


Recently I faced any issue where after restoring the database in mysql, auto increment values started from zero.

This started giving duplicated entry error. First I figured out the issue using this

SELECT AUTO_INCREMENT FROM information_schema.tables

WHERE table_schema=’mydb’ AND table_name=’mytablename’

Then I tried to resolve this using:-

UPDATE information_schema.tables

SET AUTO_INCREMENT = 13565

WHERE table_schema=’mydb’ AND table_name=’mytablename’

But got error mentioning:-

Access denied for user ‘root’@’localhost’ to database ‘information_schema’

I tried:-

ALTER TABLE mytable 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.

Reference:- http://stackoverflow.com/questions/3595584/updating-auto-increment-value-of-all-tables-in-a-mysql-database

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: