| Bug #55993 | Assertion Failure / MySQL and InnoDB data dictionaries are out of sync | ||
|---|---|---|---|
| Submitted: | 15 Aug 2010 21:19 | Modified: | 20 Sep 2010 19:14 |
| Reporter: | Judy Whiteside | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.1.45 | OS: | Windows (Win 7 Pro x64) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cannot access table in database, dictionaries out of sync, phpmyadmin cannot see tables | ||
[15 Aug 2010 21:19]
Judy Whiteside
[15 Aug 2010 21:19]
Judy Whiteside
structure description of the bad table
Attachment: eqtable description.PNG (image/png, text), 60.92 KiB.
[15 Aug 2010 21:21]
Judy Whiteside
the content of the equipment table
Attachment: dumpEqTable.txt (text/plain), 6.20 KiB.
[15 Aug 2010 21:22]
Judy Whiteside
minor correction: Firefox is 3.6.7
[15 Aug 2010 21:38]
Judy Whiteside
My database "vault" has 6 tables. I was able to successfully run the command SHOW CREATE TABLE xxx; on each of them EXCEPT the equipment_test table. I also have other databases set up that work fine under phpmyadmin etc. Only the table equipment_test seems to be broken/corrupted. All of the tables have an auto-increment field that serves as the primary key.
[17 Aug 2010 20:03]
Judy Whiteside
I was able to solve my particular development problem by the following: 1. DROP TABLE on the trouble table (equipment_test) 2. CREATE TABLE to construct it again (tedious, but I had enough notes to do this) 3. LOAD DATA LOCAL INFILE ..... (I had a fairly recent set of data dumped to use) This allowed me to do SHOW TABLE CREATE successfully and to use phpmyadmin successfully on this database. I have moved the severity to S2 since I can now work. I would still like to know what caused this (so I can avoid it if possible) and how to better and more easily recover/repair such situations.
[18 Aug 2010 12:23]
eduardo cury
I having the same problem in severals tables. Following my log. 100818 09:12:48 mysqld_safe mysqld restarted 100818 9:12:48 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 100818 9:12:48 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 100818 9:12:48 InnoDB: Started; log sequence number 1 3257383233 100818 9:12:48 [Warning] Found invalid password for user: '*922A4B420903CAD@localhost'; Ignoring user 100818 9:12:48 [Note] Event Scheduler: Loaded 0 events 100818 9:12:48 [Note] /usr/local/libexec/mysqld: ready for connections. Version: '5.1.47-log' socket: '/tmp/mysql.sock' port: 0 FreeBSD port: mysql-server-5.1.47 100818 9:14:10 InnoDB: MySQL and InnoDB data dictionaries are out of sync. InnoDB: Unable to find the AUTOINC column orderItem__code in the InnoDB table dev_ressoris/orderItem. 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. 100818 9:14:10 InnoDB: MySQL and InnoDB data dictionaries are out of sync. InnoDB: Unable to find the AUTOINC column orderItem__code in the InnoDB table dev_ressoris/orderItem. 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. 100818 9:14:10 InnoDB: Assertion failure in thread 679487488 in file handler/ha_innodb.cc line 7931 InnoDB: Failing assertion: auto_inc > 0 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: about forcing recovery. 100818 9:14:10 - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.
[20 Sep 2010 19:14]
MySQL Verification Team
duplicate of private bug #55277 please wait for next release, 5.1.51 which should solve it.
[30 Aug 2011 9:10]
Mariano Biencinto
Hello, I also happened to version 5.5.9, I see no possible solution. Need to know if it's really a bug or have some kind of solution. Copy and paste part of my error. thank you very much a greeting ----------------------------" 110830 2:08:30 InnoDB: MySQL and InnoDB data dictionaries are out of sync. InnoDB: Unable to find the AutoInc column in the InnoDB table id_log xxxxxxxxxxxxxxxx InnoDB: We Set the value column next AutoInc to 0, InnoDB: in effect disabling the value AutoInc next generation. InnoDB: You can Either the next AutoInc September Explicitly value using ALTER TABLE InnoDB: or fix the data dictionary by recreating the table. 110830 2:08:30 InnoDB: AutoInc next generation value is disabled for 'xxxxxxxxxxxxxxxx' 110830 2:08:30 InnoDB: AutoInc next generation value is disabled for 'xxxxxxxxxxxxxxxxxx' "
[19 Sep 2011 22:03]
Sveta Smirnova
Mariano, thank you for the feedback. But you provided not enough information for us to consider if this is same bug or not. Please send us full error log file and confirm there were no database files corruptions.
[29 Jul 2012 14:25]
S H
I've the same problem. Tables which are inaccessible and the error "InnoDB: Unable to find the AUTOINC column Tablename in the InnoDB table" Similar to bug #52609
