Activities

July 2012
M T W T F S S
« Jun   Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

InnoDB: Unable to find the AUTOINC column id in the InnoDB table

I got an error when I dump a database backup from a mission critical server. It’s shows the following error on the console even though my application working fine with this database. I googled lots of the forum and blog to get it solved. Most of the forum lead me to adjust the mysql variable like “connect_timeout, max_allowed_packet” to get rid of. unfortunately nothing got work for me 🙁 a sad situation like emergency cases.

[root@qa ec2-user]# mysqldump -B db_prod -r db_prod.sql
mysqldump: Couldn't execute 'show table status like 'ifavourite'': Lost connection to MySQL server during query (2013)
[root@qa ec2-user]#

After many trial and error, finally I see there was something more added in “/var/log/mysqld.log” while I’m executing backup. So it’s something wrong with that database. So I’m looking closely on it.

 InnoDB: We set the next AUTOINC column value to 0,
InnoDB: in effect disabling the AUTOINC next value generation.
InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE
InnoDB: or fix the data dictionary by recreating the table.
120702  9:29:01  InnoDB: MySQL and InnoDB data dictionaries are out of sync.
InnoDB: Unable to find the AUTOINC column id in the InnoDB table db_prod/ifavourite.
InnoDB: We set the next AUTOINC column value to 0,
InnoDB: in effect disabling the AUTOINC next value generation.
InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE
InnoDB: or fix the data dictionary by recreating the table.

Finally I see that all tables are accessible and I can see the data in it.

mysql> select * from ifavourite limit 1;
+----+---------+-------------+---------+
| id | version | exercise_id | user_id |
+----+---------+-------------+---------+
|  1 |       0 |           4 |      72 |
+----+---------+-------------+---------+
1 row in set (0.00 sec)
mysql> check table  ifavourite;
+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| db_prod.ifavourite | check | status   | OK       |
+----------------------+-------+----------+----------+
1 row in set (0.00 sec)

Now I’m planning to export data from this table to another table. Hence I’ll have a secure backup 🙂

Now I’m creating a copy of this table with empty structure. mysqldump command won’t allow to take table structure from console and hence I have to create one by hand.

mysql> desc  ifavourite;
+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id          | bigint(20) | NO   | PRI | NULL    | auto_increment |
| version     | bigint(20) | NO   |     | NULL    |                |
| exercise_id | bigint(20) | NO   | MUL | NULL    |                |
| user_id     | bigint(20) | NO   |     | NULL    |                |
+-------------+------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Then I created new table as ifavourite_new and load the schema in database. Now I’m going to import all the data from ifavourite to ifavourite_new table. After that I take the backup of that table. Now I have all the data and I’m safe :-).

 mysql> source ifavourite_new.sql;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into ifavourite_new select * from ifavourite;
Query OK, 279 rows affected (0.00 sec)
Records: 279  Duplicates: 0  Warnings: 0
mysql> exit
Bye
[root@qa ec2-user]# mysqldump -d prod_db ifavourite_new  -r ifavourite.sql
[root@qa ec2-user]#

Now I’m going to edit the sql file which I backed up and replace all the table name entry with original table name
ie open the file in VI editor and “:%s/avourite_new/avourite/g”.
Database changed

mysql> drop table ifavourite;
Query OK, 0 rows affected (0.01 sec)
mysql> source ifavourite.sql;
mysql> Select count(*) FROM ifavourite ;
+----------+
| count(*) |
+----------+
|      279 |
+----------+
1 row in set (0.00 sec)
mysql>

Ahha I restored the data back and I’m able to execute mysqldump command without any issue.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>