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:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.1.45 OS:Microsoft 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
Description:
100815 13:12:11  InnoDB: MySQL and InnoDB data dictionaries are out of sync.
100815 13:12:11  InnoDB: Assertion failure

Developing code on Windows machine got this error. Has been working
Some portion of this code is working well on a Linux server.
Problem began today during testing. Worked in the morning, many test runs. Then failed about 1:30 pm

System configuration:
	new Dell Inc. Studio XPS 8100  (few months old, working wonderfully)
	Windows 7 Pro x64
	Microsoft® Visual Studio® 2005 Version 8.0.50727.867
	Microsoft SQL Server Version 9.00.4053.00
	php = 5.3.1
	MySQL = 5.1.45
	phpMyAdmin = 3.3.1
	Firefox 2.6.7
	IE8
	
NOTE: I saw that you have development version 5.5 but hesitate to upgrade to that
unless on your recommendation since that version is not General Availability yet.	
	
First observation of error situation:	
Using phpmyadmin failed. When I chose the database 'vault', it listed (6) as the number
of tables, but below that it said "No tables found in database."
After this first request which gives an error, cannot get into phpmyadmin at all.
I think the service gets stopped/killed at this point (see below).

mysql behaves sort-of on command line. I looked at another bug report online
	http://bugs.mysql.com/bug.php?id=52609 
and a response there made a request to do SHOW CREATE TABLE <table>

I issued that command  SHOW CREATE TABLE equipment_test;
and got:
	No connection. Trying to reconnect...
	Connection id: 1
	Current database: vault
	ERROR 2013 (HY000): Lost connction to MySQL server during query

I checked that the service MySQL was running. It wasn't. I started it.
Tried the command again. It failed same way. The service was NOT running.
Repeated all this. Same result.
Running this command stops/kills the service.

I earlier had done:
	SELECT * FROM equipment_test INTO OUTFILE dumpEqTable.txt
I was able to repeat this OK as long as I started the MySQL service.

With this report, I am including the following files
	dumpEqTable.txt
	eqTable description.png		
	
(Side issue: I had trouble redirecting output from the mysql describe command,
so I used the Win7 snipping tool to make this PNG file. 
	DESCRIBE equipment_test > describeEQ.txt
This command failed giving 'you have an error in your sql syntax' ???? )
	
	
Log file from C:\ProgramData\MySQL\MySQL Server 5.1\data\Gryphon.err:

100815 14:00:37  InnoDB: MySQL and InnoDB data dictionaries are out of sync.
InnoDB: Unable to find the AUTOINC column recid in the InnoDB table vault/equipment_test.
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.
100815 14:08:47  InnoDB: Assertion failure in thread 5604 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.
100815 14:08:47 - mysqld got exception 0xc0000005 ;
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.

key_buffer_size=57671680
read_buffer_size=65536
max_used_connections=1
max_threads=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 89212 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x5875500
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
InnoDB: Thread 5636 stopped in file G:\mysql-5.1.45-winbuild\mysql-community-nt-5.1.45-build\storage\innobase\include\sync0sync.ic line 115
0000000140214A64    mysqld.exe!ha_innobase::innobase_peek_autoinc()[ha_innodb.cc:7931]
0000000140216A95    mysqld.exe!ha_innobase::info()[ha_innodb.cc:6542]
0000000140087C86    mysqld.exe!get_schema_tables_record()[sql_show.cc:3718]
000000014009409F    mysqld.exe!get_all_tables()[sql_show.cc:3487]
0000000140095EDC    mysqld.exe!get_schema_tables_result()[sql_show.cc:6134]
000000014017251A    mysqld.exe!JOIN::exec()[sql_select.cc:1814]
00000001401736EA    mysqld.exe!mysql_select()[sql_select.cc:2487]
0000000140173AE6    mysqld.exe!handle_select()[sql_select.cc:269]
0000000140069300    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5053]
000000014006BBE7    mysqld.exe!mysql_execute_command()[sql_parse.cc:2248]
000000014007010E    mysqld.exe!mysql_parse()[sql_parse.cc:5975]
0000000140070CAA    mysqld.exe!dispatch_command()[sql_parse.cc:1235]
0000000140071917    mysqld.exe!do_command()[sql_parse.cc:874]
00000001400981F7    mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
000000014031AE95    mysqld.exe!pthread_start()[my_winthread.c:85]
00000001402E4F67    mysqld.exe!_callthreadstart()[thread.c:295]
00000001402E5035    mysqld.exe!_threadstart()[thread.c:275]
0000000076DBF56D    kernel32.dll!BaseThreadInitThunk()
0000000076FF3021    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 00000000058D6390=SHOW TABLE STATUS FROM `vault`
thd->thread_id=4
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
InnoDB: Thread 5060 stopped in file G:\mysql-5.1.45-winbuild\mysql-community-nt-5.1.45-build\storage\innobase\include\sync0sync.ic line 115
100815 14:22:06 [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!
100815 14:22:06  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...
100815 14:22:06  InnoDB: Started; log sequence number 0 701361
100815 14:22:06 [Note] Event Scheduler: Loaded 0 events
100815 14:22:06 [Note] C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: ready for connections.
Version: '5.1.45-community'  socket: ''  port: 3306  MySQL Community Server (GPL)
100815 14:22:52  InnoDB: MySQL and InnoDB data dictionaries are out of sync.
InnoDB: Unable to find the AUTOINC column recid in the InnoDB table vault/equipment_test.
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.

How to repeat:
See details in note:

My php code could not access the database, so I looked at the database using phymyadmin. Could not view table got error: No tables found in database.
Went to file system and command line. Files exist, command line mostly works.

Using command
 SHOW CREATE TABLE equipment_test;
failed

Suggested fix:
unknown
[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] Shane Bester
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