Bug #17018 InnoDB failure causing repeated mysqld crashes
Submitted: 1 Feb 2006 17:57 Modified: 3 Feb 2006 10:11
Reporter: Jack Levy Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18 OS:Linux (Linux (Slackware))
Assigned to: CPU Architecture:Any

[1 Feb 2006 17:57] Jack Levy
Description:
I've been running MySQL 5.0.18 as a Slave to a version 4.0.16 Master in preparation to upgrade our production service to version 5.0.  I started replication using a file-by-file copy of the 4.0 data, including the files ib_logfile0,  ib_logfile1, and ibdata1, followed by running the mysql_fix_privilege_tables.sql script.  Apart from some small application-specific problems, this was running well until an InnoDB failure caused mysqld to crash and then repeatedly restart and crash again, until I used a kill -9 to stop everything.

I have put copies of the relevant files in my web pages at http://www.ucl.ac.uk/~ccaajrl/mysql_problem/

crash.txt is an extract from the error log.  This repeated until I finally killed the daemon

stacktrace.txt is the stack trace, as generated by resolve_stack_dump

'guilty' is the SQL extracted from the Master's binary log that seems to be the culprit.  It is the only query about the time of the crash that involved InnoDB tables.  The file contains some binary data, so can't be viewed but can be downloaded.

guilty.txt is an expurgated version with the binary data removed so that it can be viewed in a browser.

How to repeat:
I haven't been able to repeat this with test InnoDB tables, but will continue to investigate.  Any suggestion as to what the problem might be would be welcome.
[2 Feb 2006 14:21] Valeriy Kravchuk
Thank you for a problem report. So, you had not dumped your tables in 4.0.x with mysqldump and restored them in 5.0.x, right? Can you, please, send the SHOW CREATE TABLE results (from MASTER!) for all the tables mentioned in that guilty.txt file?
[2 Feb 2006 14:56] Heikki Tuuri
This is probably a duplicate of http://bugs.mysql.com/bug.php?id=16298

Fixed in 5.0.19.
[2 Feb 2006 15:06] Jack Levy
The SHOW CREATE TABLE results are as follows.  I suspect it was the 'objectcache' table that causes the failure, because when I repeated that first DELETE query manually, it crashed mysqld, but I rebuilt the table using:

  select * into outfile 'objectcache' from objectcache;
  create table obj like objectcache;
  load data infile 'objectcache' into table obj;
  drop table objectcache;
  rename table obj to objectcache;

and that fixed it.  But should a corrupt table crash the daemon?

I intended to use mysqldump to copy the data, but some of the databases have dashes in their names and the 4.0 version of mysqldump doesn't quote them when the -Q option is used.  These databases have been created by users, probably with phpMyAdmin.  Maybe I should get a later version of mysqldump?  Would the 4.1 version work on 4.0 data?

Create Table: CREATE TABLE `objectcache` (
  `keyname` varchar(255) binary NOT NULL default '',
  `value` mediumblob,
  `exptime` datetime default NULL,
  UNIQUE KEY `keyname` (`keyname`),
  KEY `exptime` (`exptime`)
) TYPE=InnoDB

Create Table: CREATE TABLE `page` (
  `page_id` int(8) unsigned NOT NULL auto_increment,
  `page_namespace` int(11) NOT NULL default '0',
  `page_title` varchar(255) binary NOT NULL default '',
  `page_restrictions` tinyblob NOT NULL,
  `page_counter` bigint(20) unsigned NOT NULL default '0',
  `page_is_redirect` tinyint(1) unsigned NOT NULL default '0',
  `page_is_new` tinyint(1) unsigned NOT NULL default '0',
  `page_random` double unsigned NOT NULL default '0',
  `page_touched` varchar(14) binary NOT NULL default '',
  `page_latest` int(8) unsigned NOT NULL default '0',
  `page_len` int(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`page_id`),
  UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
  KEY `page_random` (`page_random`),
  KEY `page_len` (`page_len`)
) TYPE=InnoDB

Create Table: CREATE TABLE `site_stats` (
  `ss_row_id` int(8) unsigned NOT NULL default '0',
  `ss_total_views` bigint(20) unsigned default '0',
  `ss_total_edits` bigint(20) unsigned default '0',
  `ss_good_articles` bigint(20) unsigned default '0',
  `ss_total_pages` bigint(20) default '-1',
  `ss_users` bigint(20) default '-1',
  `ss_admins` int(10) default '-1',
  UNIQUE KEY `ss_row_id` (`ss_row_id`)
) TYPE=InnoDB
[2 Feb 2006 15:39] Jack Levy
Sorry, correction to my last comment: the -Q option was fixed in the 4.0 version of mysqldump.  However, database-by-database locking was introduced in 4.1, which is what I'd really need.
[2 Feb 2006 17:24] Heikki Tuuri
Jack,

5.0.19 will no longer crash in this case.

Regards,

Heikki
[3 Feb 2006 10:09] Valeriy Kravchuk
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html
[3 Feb 2006 10:11] Valeriy Kravchuk
It is a duplicate of bug #16298. At least, should be fixed by the same patch. That bug is already closed. Please, wait for 5.0.19 to be officially released.