DROP DATABASE Causes an Assertion Failure (Doc ID 1350279.1)

Last updated on MARCH 08, 2017

Applies to:

MySQL Server - Version: 5.5 and later   [Release: 5.5 and later ]
Information in this document applies to any platform.

Symptoms

On : 5.5.10 version, Server crashes when attempting to do a DROP DATABASE command, and the following error occurs.

ERROR
-----------------------
...
InnoDB: ###### Diagnostic info printed to the standard error stream
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.
110810 19:28:21 InnoDB: Assertion failure in thread 1222838592 in file ./mysql-5.5.10/storage/innobase/srv/srv0srv.c line 2446
...
110810 20:10:25 InnoDB: MySQL and InnoDB data dictionaries are out of sync.
InnoDB: Unable to find the AUTOINC column questionId in the InnoDB table dbname/tablename.
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.
110810 20:10:25 InnoDB: AUTOINC next value generation is disabled for 'dbname/tablename'
InnoDB: Error: trying to load index PRIMARY for table dbname/tablename
InnoDB: but the index tree has been freed!
110810 20:10:31 [ERROR] Cannot find or open table dbname/tablename from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine doesn't support.
...
InnoDB: Error: trying to load index PRIMARY for table dbname/tablename
InnoDB: but the index tree has been freed!
110810 21:14:30 InnoDB: Error: table `dbname`.`tablename` does not exist in the InnoDB internal
...
InnoDB: Error: trying to load index PRIMARY for table dbname/tablename
InnoDB: but the index tree has been freed!
110810 21:14:30 InnoDB: Assertion failure in thread 1104963904 in file ./mysql-5.5.10/storage/innobase/row/row0mysql.c line 3625
InnoDB: Failing assertion: table
...


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a database of extremely large size for number of tables and/or size of data
2. Deliberately corrupt a table
3. Issue a DROP DATABASE command and continue with high concurrency connections

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot get a consistent state of data as some information is deleted at the filesystem level, but tablespace metadata is not matching.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms