MySQL Bugs: #59382: mysql import crashes on an innodb insert

Bug #59382	mysql import crashes on an innodb insert
Submitted:	9 Jan 2011 16:05	Modified:	9 Feb 2011 16:51
Reporter:	Jason Bronson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	Ver 14.14 Distrib 5.1.32, for unknown-li	OS:	Linux (ubuntu 10.04 Server)
Assigned to:		CPU Architecture:	Any

Description:
110109 11:00:16  InnoDB: Assertion failure in thread 139795722376960 in file handler/ha_innodb.cc line 3454
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.
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x2e)[0x89ba2e]
/usr/local/mysql/bin/mysqld(handle_segfault+0x322)[0x5d2c42]
/lib/libpthread.so.0(+0xf8f0)[0x7f24c03628f0]
/lib/libc.so.6(gsignal+0x35)[0x7f24bf729a75]
/lib/libc.so.6(abort+0x180)[0x7f24bf72d5c0]
/usr/local/mysql/bin/mysqld(_ZN11ha_innobase9write_rowEPh+0x206)[0x76b706]
/usr/local/mysql/bin/mysqld(_ZN7handler12ha_write_rowEPh+0x85)[0x6c1075]
/usr/local/mysql/bin/mysqld(_Z12write_recordP3THDP8st_tableP12st_copy_info+0x5d)[0x64f20d]
/usr/local/mysql/bin/mysqld(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0x995)[0x652e85]
/usr/local/mysql/bin/mysqld(_Z21mysql_execute_commandP3THD+0x94b)[0x5e35fb]
/usr/local/mysql/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)[0x5e8517]
/usr/local/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xe63)[0x5e9383]
/usr/local/mysql/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5e9c46]
/usr/local/mysql/bin/mysqld(handle_one_connection+0x236)[0x5dc896]
/lib/libpthread.so.0(+0x69ca)[0x7f24c03599ca]
/lib/libc.so.6(clone+0x6d)[0x7f24bf7dc70d]
110109 11:00:16 - 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.

key_buffer_size=26214400
read_buffer_size=131072
max_used_connections=1
max_threads=151

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

thd: 0x30dbcc0
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...
stack_bottom = 0x7f24ba5f8e80 thread_stack 0x20000
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x3105630 = INSERT INTO `connection` VALUES (1,1,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(2,6,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(3,5,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(4,4,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(5,3,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(6,2,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(7,1,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(8,6,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(9,5,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(10,4,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(11,3,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(12,2,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00')
thd->thread_id=3
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.
110109 11:00:16 mysqld_safe Number of processes running now: 0
110109 11:00:16 mysqld_safe mysqld restarted
110109 11:00:16 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
110109 11:00:16 [Warning] The syntax '--log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '--slow_query_log'/'--slow_query_log_file' instead.
InnoDB: Log scan progressed past the checkpoint lsn 0 62669
110109 11:00:16  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...
InnoDB: Doing recovery: scanned up to log sequence number 0 65954

110109 11:00:16  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
110109 11:00:16  InnoDB: Started; log sequence number 0 65954
110109 11:00:16 [Note] Recovering after a crash using /var/log/mysql/bin
110109 11:00:16 [Note] Starting crash recovery...
110109 11:00:16 [Note] Crash recovery finished.
110109 11:00:16 [Note] Event Scheduler: Loaded 0 events
110109 11:00:16 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

How to repeat:
/usr/local/mysql/bin/mysql -uroot databasename < /tmp/databasedump.sql

example in SQL file 

DROP TABLE IF EXISTS `connection`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `connection` (
  `connection_id` double NOT NULL auto_increment,
  `illness_id` double NOT NULL,
  `medicine_id` double NOT NULL,
  `inserted_by` varchar(200) NOT NULL,
  `updated_by` varchar(200) NOT NULL,
  `insert_record_ts` datetime NOT NULL,
  `update_record_ts` datetime NOT NULL,
  PRIMARY KEY  (`connection_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `connection`
--

LOCK TABLES `connection` WRITE;
/*!40000 ALTER TABLE `connection` DISABLE KEYS */;
INSERT INTO `connection` VALUES (1,1,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(2,6,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(3,5,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(4,4,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(5,3,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(6,2,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(7,1,1,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(8,6,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(9,5,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(10,4,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(11,3,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00'),(12,2,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00');
/*!40000 ALTER TABLE `connection` ENABLE KEYS */;
UNLOCK TABLES;

Please, check if the same problem happens with a recent version, 5.1.54. I do not see this crash:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.55-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS `connection`;
Query OK, 0 rows affected, 1 warning (0.41 sec)

mysql> SET @saved_cs_client     = @@character_set_client;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_client = utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `connection` (
    ->   `connection_id` double NOT NULL auto_increment,
    ->   `illness_id` double NOT NULL,
    ->   `medicine_id` double NOT NULL,
    ->   `inserted_by` varchar(200) NOT NULL,
    ->   `updated_by` varchar(200) NOT NULL,
    ->   `insert_record_ts` datetime NOT NULL,
    ->   `update_record_ts` datetime NOT NULL,
    ->   PRIMARY KEY  (`connection_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.24 sec)

mysql> SET character_set_client = @saved_cs_client;
Query OK, 0 rows affected (0.00 sec)

mysql> LOCK TABLES `connection` WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> /*!40000 ALTER TABLE `connection` DISABLE KEYS */;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO `connection` VALUES (1,1,2,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(2,6,1,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(3,5,1,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(4,4,1,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(5,3,1,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(6,2,1,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(7,1,1,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(8,6,2,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(9,5,2,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(10,4,2,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(11,3,2,'','','0000-00-00 00:00:00','0000-00-00
    '> 00:00:00'),(12,2,2,'','','0000-00-00 00:00:00','0000-00-00 00:00:00');
Query OK, 12 rows affected (0.05 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> /*!40000 ALTER TABLE `connection` ENABLE KEYS */;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

I have to wait until a bit later to try an upgraded version because this is a production server

Please, inform about any results after you'll be able to upgrade.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".