MySQL Bugs: #25922: InnoDB crash recovery changes: make DDL in MySQL 'atomic'

Bug #25922	InnoDB crash recovery changes: make DDL in MySQL 'atomic'
Submitted:	29 Jan 2007 16:08	Modified:	5 Oct 2008 13:24
Reporter:	Chris Calender	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	All	OS:	Any (All)
Assigned to:		CPU Architecture:	Any
Tags:	InnoDB crash recovery

Description:
The problem is known.
http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html

This increases maintenance cost and decreases availability. Sometimes, slaves drop table definitions from the InnoDB data dictionary on crash recovery. This requires operator intervention to recover when the table was dropped from InnoDB but is still in MySQL (there is a *.frm file).

Can InnoDB be changed to allow a table drop when the definition is missing from its data dictionary but still in the MySQL data dictionary?

Can InnoDB be changed to remove the *.frm file from the filesystem when dropping the entry from its data dictionary on crash recovery?

How to repeat:
N/A

Suggested fix:
N/A

Chris,

some comments:

> Can InnoDB be changed to allow a table drop when the definition is missing from its data dictionary but still in the MySQL data dictionary?

Yes:

1. When MySQL opens the table that only has an .frm file, InnoDB should mark the table as 'crashed', so that MySQL does not try to do queries on the table.
2. But InnoDB should allow to DROP the table.

> Can InnoDB be changed to remove the *.frm file from the filesystem when dropping the entry from its data dictionary on crash recovery?

That is harder because InnoDB does not know what tables it has recently dropped.

The big picture here is that MySQL and InnoDB should be made 'atomic' also regarding DDL operations. If the .frm file exists for a table, then  the table should exist also in the engine. I think the replication team has been thinking about this for the past 5 years. This feature is more natural to implement on the MySQL side.

Regards,

Heikki

I agree with Heikki. I suspect that the problem exists on all transactional storage engines, but I am not willing to confirm this. The DDL sequence appears to be:
1) create *.frm file
2) commit table create to storage engine

When there is a crash between 1 and 2 the system recovers with the *.frm file but without the table defined in the internal dictionary used by the storage engine.

Hi,
regarding how it will probably be solved in the future: in the partitioning code, when a partitioned table is being re-partitioned differently for eexample, we already have some "DDL log" which tracks what DDL changes are done to the underlying table s by the repartitioning; it ensures that if there is a crash during the repartitioning, at restart a consistent state is restored by completing operations which didn't have time to complete before the crash. Search for "ddl_log" in sql/sql_table.cc in 5.1.
We probably need to re-use this for non-partition-specific operations like DROP TABLE, RENAME TABLE, CREATE TABLE.

Reclassifying as a MySQL Server bug, as the fix will probably involve all transactional storage engines.

Setting to "To be fixed later" since depends on an architecture task which is scheduled, but will take a few releases to implement.

Public reference to the relevant worklog task: http://forge.mysql.com/worklog/task.php?id=2760