MySQL Bugs: #28283: innodb assertion and crash when opening a table

Bug #28283	innodb assertion and crash when opening a table
Submitted:	7 May 2007 15:01	Modified:	9 May 2007 12:00
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.0.42BK	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	assertion, crash, innodb

Description:
When running a query, 

mysql> use information_schema;
Database changed
mysql> select * from columns;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Shortened stack trace (see attached file for full version) :

mysqld-debug.exe!btr_root_get
mysqld-debug.exe!btr_get_size
mysqld-debug.exe!dict_update_statistics_low
mysqld-debug.exe!dict_update_statistics
mysqld-debug.exe!dict_table_get_and_increment_handle_count
mysqld-debug.exe!ha_innobase::open
mysqld-debug.exe!handler::ha_open
mysqld-debug.exe!openfrm
mysqld-debug.exe!open_unireg_entry
mysqld-debug.exe!open_table
mysqld-debug.exe!open_tables
mysqld-debug.exe!open_normal_and_derived_tables
mysqld-debug.exe!get_all_tables
mysqld-debug.exe!get_schema_tables_result
mysqld-debug.exe!JOIN::exec
mysqld-debug.exe!mysql_select
mysqld-debug.exe!handle_select
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!mysql_parse
mysqld-debug.exe!dispatch_command
mysqld-debug.exe!do_command
mysqld-debug.exe!handle_one_connection
mysqld-debug.exe!pthread_start
mysqld-debug.exe!_callthreadstart
mysqld-debug.exe!_threadstart

Assertion:

E:\builds\mysql-5.0.42\sql\debug>mysqld-debug --console --skip-grant-tables
070507 16:54:19  InnoDB: Started; log sequence number 0 48849616
070507 16:54:19 [Note] mysqld-debug: ready for connections.
Version: '5.0.42-debug'  socket: ''  port: 3306  yes
070507 16:54:31InnoDB: Assertion failure in thread 1208 in file .\btr\btr0btr.c line 147
InnoDB: Failing assertion: (ibool)!!page_is_comp(root) == UT_LIST_GET_FIRST(tree->tree_indexes)->table->comp
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.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 10200 stopped in file .\os\os0sync.c line 532
InnoDB: Thread 10084 stopped in file .\sync\sync0arr.c line 123

Code:

page_t*
btr_root_get(
/*=========*/
				/* out: root page, x-latched */
	dict_tree_t*	tree,	/* in: index tree */
	mtr_t*		mtr)	/* in: mtr */
{
	ulint	space;
	ulint	root_page_no;
	page_t*	root;
	
	space = dict_tree_get_space(tree);
	root_page_no = dict_tree_get_page(tree);

	root = btr_page_get(space, root_page_no, RW_X_LATCH, mtr);
	ut_a((ibool)!!page_is_comp(root) ==
		UT_LIST_GET_FIRST(tree->tree_indexes)->table->comp);
	
	return(root);
}

How to repeat:
I'll zip and upload entire datadir as it depends on existing tables in database.

Suggested fix:
.

Shane,

has the user substituted .ibd files? Then the flag could be wrong.

Regards,

Heikki

Heikki, it's on my home pc, and all tables created in 5.0.42.  Nothing has been moved or played with.  

I am trying to recreate the crash using a clean datadir and recording the CREATE TABLE statements this time...

Marko,

I think we have seen a similar thing in the past. Was it something wrong with the type of the system tables in InnoDB's data dictionary? Or the insert buffer tree?

Regards,

Heikki

Shane,

what are you trying to do? InnoDB does not support (much of) INFORMATION SCHEMA yet.

Is the 'columns' table supposed to be InnoDB type?

Regards,

Heikki

I'm tempted to set this to 'can't repeat'. Although a crash happens with uploaded datadir, I have been unable to recreate the scenario from scratch, so perhaps it was a once-off memory / disk error.  Many of my tests set innodb_flush_log_at_trx_commit to 0, so it maybe some files got corrupted due to a previous crash.

Heikki,

I guess that the pseudo-tables in INFORMATION_SCHEMA are gradually replacing the unstructured output of SHOW CREATE TABLE, SHOW ENGINE INNODB STATUS and so on.

I am unable to remember any bug where InnoDB would set the "compact format" flag to wrong value. Assertions on page_is_comp() have failed before, but as far as I can remember, it has always been a symptom of something else (corruption caused by bug or hardware).

I will try to repeat this with UNIV_DEBUG enabled, to see if there is any other problem with the database.

The assertion

page_is_comp(root) == dict_table_is_comp(index->table)

fails because the root page of the index tree of the clustered index of db1.table10 is full of zero bytes. Valid InnoDB index pages should always contain the srtings "infimum" and "supremum", among many other things.

Shane, before this crash, was mysqld terminated after only a few seconds of uptime? Please check if Heikki's comments to Bug #23710, starting from 24 Nov 2006.

Marko, that scenario is possible.  mysqld-debug.exe was crashing alot, due to some other bug testing going on at that time.  Incidently, this table was created like this: CREATE TABLE table10 LIKE test.table10.

This is likely to be a duplicate of Bug #23710. However, it's best to keep the database dump around, in case this is caused by something else.