Bug #54044 Create temporary tables and using innodb crashes.
Submitted: 27 May 2010 21:20 Modified: 15 Oct 2010 10:36
Reporter: Boris Reisig Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1.41, 5.1.47, 5.1.48-bzr OS:Linux
Assigned to: Jimmy Yang
Triage: Triaged: D1 (Critical) / R3 (Medium)

[27 May 2010 21:20] Boris Reisig
Description:
When using MySQL 5.1.41, 5.1.44, and the latest SVN version from today (5.1.48), the following crashes...

mysql> SET storage_engine=innodb;

mysql> CREATE TEMPORARY TABLE mk_upgrade AS SELECT  IF(     NULL  IS NOT NULL,      NULL , NULL) ; drop table mk_upgrade;

but when setting the storage engine to MYISAM, it doesn't always crash. Example below.

mysql> SET storage_engine=MYISAM;

mysql> CREATE TEMPORARY TABLE mk_upgrade AS SELECT  IF(     NULL  IS NOT NULL,      NULL , NULL) ; drop table mk_upgrade;

Any ideas?

How to repeat:
mysql> SET storage_engine=MYISAM;

mysql> CREATE TEMPORARY TABLE mk_upgrade AS SELECT  IF(     NULL  IS NOT NULL,      NULL , NULL) ; drop table mk_upgrade;
[27 May 2010 21:22] Boris Reisig
When using MySQL 5.1.41, 5.1.44, and the latest version from BZR today (5.1.48), the following crashes...

mysql> SET storage_engine=innodb;

mysql> CREATE TEMPORARY TABLE mk_upgrade AS SELECT  IF(     NULL  IS NOT NULL,      NULL , NULL) ; drop table mk_upgrade;

but when setting the storage engine to MYISAM, it doesn't always crash. Example below.

mysql> SET storage_engine=MYISAM;

mysql> CREATE TEMPORARY TABLE mk_upgrade AS SELECT  IF(     NULL  IS NOT NULL,      NULL , NULL) ; drop table mk_upgrade;

Any ideas?
[28 May 2010 3:40] Valeriy Kravchuk
Recent 5.1.48-bzr debug(!) binaries show possible reason of crash. Assertion happens during the very first CREATE TEMPORARY TABLE execution:

Version: '5.1.48-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (0), function unknown function, file handler/ha_innodb.cc, line 3227.

Then, upon server restart, we get these in the error log:

100528  6:36:09  InnoDB: Error: table 100528  6:36:09 [Warning] Invalid (old?) table or database name '#sql9818_1_2'
``.<result 2 when explaining filename '#sql9818_1_2'> does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
[28 May 2010 5:07] Shane Bester
Version: '5.1.47-enterprise-gpl-advanced'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition (GPL)
100528  7:07:37  InnoDB: Assertion failure in thread 3460 in file G:\mysql-5.1.47-winbuild\mysql-advanced-gpl-5.1.47-build\storage\innobase\include\data0type.ic line 510

mysqld.exe!dtype_get_min_size_low()[data0type.ic:510]
mysqld.exe!dict_col_get_min_size()[dict0dict.ic:67]
mysqld.exe!dict_build_table_def_step()[dict0crea.c:225]
mysqld.exe!dict_create_table_step()[dict0crea.c:912]
mysqld.exe!que_thr_step()[que0que.c:1264]
mysqld.exe!que_run_threads_low()[que0que.c:1319]
mysqld.exe!que_run_threads()[que0que.c:1357]
mysqld.exe!row_create_table_for_mysql()[row0mysql.c:1899]
mysqld.exe!create_table_def()[ha_innodb.cc:5323]
mysqld.exe!ha_innobase::create()[ha_innodb.cc:5635]
mysqld.exe!handler::ha_create()[handler.cc:3403]
mysqld.exe!ha_create_table()[handler.cc:3611]
mysqld.exe!rea_create_table()[unireg.cc:418]
mysqld.exe!mysql_create_table_no_lock()[sql_table.cc:3963]
mysqld.exe!create_table_from_items()[sql_insert.cc:3496]
mysqld.exe!select_create::prepare()[sql_insert.cc:3666]
mysqld.exe!JOIN::prepare()[sql_select.cc:684]
mysqld.exe!mysql_select()[sql_select.cc:2488]
mysqld.exe!handle_select()[sql_select.cc:269]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2733]
mysqld.exe!mysql_parse()[sql_parse.cc:5990]
mysqld.exe!dispatch_command()[sql_parse.cc:1235]
mysqld.exe!do_command()[sql_parse.cc:878]
mysqld.exe!handle_one_connection()[sql_connect.cc:1134]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:293]
[28 May 2010 8:57] JinRong Ye
i met this bug too.

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  create temporary table t1
    ->  (charguid int not null,
    ->   totalexp bigint not null
    ->  ) engine = innodb;
Query OK, 0 rows affected (0.00 sec)

insert into t1 values (1,111);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/home/mysql/mysql.sock' (2)
ERROR: 
Can't connect to the server

and the error log as below:

100528 16:37:03  InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means 'Invalid argument'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: File name /tmp/#sql64c0_c_0.ibd
InnoDB: File operation call: 'aio write'.
InnoDB: Cannot continue operation.
100528 16:37:03 mysqld_safe Number of processes running now: 0
100528 16:37:03 mysqld_safe mysqld restarted
100528 16:37:04 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'-
-slow-query-log-file' instead.
100528 16:37:04 [Note] Buffered information: Performance schema enabled.

100528 16:37:04 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
100528 16:37:04  InnoDB: Using Linux native AIO
100528 16:37:18  InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 134627258752
100528 16:37:22  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 134627262725
100528 16:37:25  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: We removed now the InnoDB internal data dictionary entry
InnoDB: of table 100528 16:37:29 [Warning] Invalid (old?) table or database name '#sql64c0_c_0'
"tmp".<result 2 when explaining filename '#sql64c0_c_0'>.
100528 16:37:29 InnoDB 1.1.0 started; log sequence number 134627262725
100528 16:37:29  InnoDB: Error: table 100528 16:37:29 [Warning] Invalid (old?) table or database name '#sql64c0_c_0'
`tmp`.<result 2 when explaining filename '#sql64c0_c_0'> does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
100528 16:37:29 [Note] Event Scheduler: Loaded 0 events
100528 16:37:29 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.4-m3-log'  socket: '/home/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
[28 May 2010 12:31] Mikhail Izioumtchenko
the original problem, a crash in

CREATE TEMPORARY TABLE mk_upgrade engine=innodb AS SELECT  IF(     NULL  IS NOT NULL,      NULL
, NULL)

is reproducible but looks more of a general problem.
the assert in ha_innodb.cc is on field->type() and the type is 6, 
MYSQL_TYPE_NULL. MyISAM happily creates the following table:

CREATE TEMPORARY TABLE `imk_upgrade` (
  `IF(     NULL  IS NOT NULL,      NULL
, NULL)` null DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

from memory, datatype 'null' is undocumented and it doesn't really make sense 
to me. It should be converted to something or rejected at MySQL level.

As for the crash in 

create temporary table t1
    ->  (charguid int not null,
    ->   totalexp bigint not null
    ->  ) engine = innodb;

then INSERT, it looks like a separate problem.
I can't reproduce it.
Please file a separate bug for it. We are interested in this case more in mysqld
log before the crash, not the messages shown in recovery.
[9 Jun 2010 16:44] Valeriy Kravchuk
This is what we have with current 5.1.49:

valeriy-kravchuks-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 2
Server version: 5.1.49-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> create table Test engine=myisam select IF(NULL IS NOT NULL, NULL, NULL) as a;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table Test\G
*************************** 1. row ***************************
       Table: Test
Create Table: CREATE TABLE `Test` (
  `a` null DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[10 Jun 2010 9:13] Georgi Kodinov
The server-side change tracked in bug #54394
[11 Jun 2010 4:26] Jimmy Yang
We will special handle MYSQL_TYPE_NULL type so that it does not trigger assertion for 5.1. The correct solution to block such column type would be done in the  MySQL layer with bug #54394
[23 Jun 2010 2:32] Jimmy Yang
Checked in mysql-5.1-innodb
Committed revision 3520

------------------------------------------------------------
revno: 3520
committer: Jimmy Yang <jimmy.yang@oracle.com>
branch nick: mysql-5.1-innodb
timestamp: Tue 2010-06-22 19:04:31 -0700
message:
  Fix bug #54044, Create temporary tables and using innodb crashes. Screen
  out NULL type columns, and return without creating the table.
[19 Jul 2010 14:36] Bugs System
Pushed into 5.1.49 (revid:build@mysql.com-20100719143034-omcma40sblwmay3x) (version source revid:vasil.dimov@oracle.com-20100704071244-3lo4okzels3kvy1p) (merge vers: 5.1.49) (pib:16)
[21 Jul 2010 14:18] Paul Dubois
Noted in 5.1.49 changelog.

Use of TEMPORARY InnoDB tables with nullable columns could cause a
server crash. 

Setting report to Need Merge pending push to 5.5.x.
[22 Jul 2010 15:48] Vasil Dimov
Fixed in 5.5 too:

revno: 3073
revision-id: jimmy.yang@oracle.com-20100624084922-e7ha8t8xxhwa0oi5
parent: georgi.kodinov@oracle.com-20100621115144-wh4muhwkjp0c5lii
committer: Jimmy Yang <jimmy.yang@oracle.com>
branch nick: mysql-trunk-security
timestamp: Thu 2010-06-24 01:49:22 -0700
message:
  Port fix for bug #54044 from mysql-5.1-security to mysql-trunk-security:
  
  ------------------------------------------------------------
  revno: 3438
  committer: Jimmy Yang <jimmy.yang@oracle.com>
  branch nick: mysql-5.1-security
  timestamp: Thu 2010-06-24 01:20:25 -0700
  message:
    Fix Bug #54044 Create temporary tables and using innodb crashes.
[23 Jul 2010 12:22] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (merge vers: 5.5.6-m3) (pib:18)
[23 Jul 2010 12:30] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:alik@sun.com-20100723121827-3bsh51m5sj6g4oma) (pib:18)
[24 Jul 2010 6:28] Shane Bester
bug #55374 is a duplicate of this.  two additional testcases from that bug, which no longer crash 5.1.49 release:

set storage_engine=innodb;
drop table if exists tmp;
create table tmp select coalesce(null,null,null);

set storage_engine=innodb;
drop table if exists tmp;
create table tmp select greatest(null,null);

This is the error message 5.1.49 returns:

mysql> create table tmp select greatest(null,null);
ERROR 1005 (HY000): Can't create table 'test.tmp' (errno: -1)
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1005
Message: Error creating table 'test/tmp' with column 'greatest(null,null)'. Please check its column type and try to re-create the table with an appropriate column type.
*************************** 2. row ***************************
  Level: Error
   Code: 1005
Message: Can't create table 'test.tmp' (errno: -1)
2 rows in set (0.00 sec)
[26 Jul 2010 18:31] Paul Dubois
Noted in 5.5.6 changelog.
[4 Aug 2010 7:51] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100629125518-m3am4ia1ffjr0d0j) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:03] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100629125518-m3am4ia1ffjr0d0j) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 8:19] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100629125518-m3am4ia1ffjr0d0j) (merge vers: 5.1.49) (pib:18)
[4 Aug 2010 9:01] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100629125518-m3am4ia1ffjr0d0j) (pib:20)
[4 Aug 2010 22:55] Paul Dubois
Bug does not appear in any released 5.6.x version.
[16 Aug 2010 8:47] Kristofer Pettersson
SRT: Severity C, CVSS: 4.9
[14 Oct 2010 8:27] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:42] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 8:57] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[15 Oct 2010 10:36] Jon Stephens
Already documented in the 5.1.49 changelog. No new changelog entries required. Setting back to Closed.
[3 Nov 2010 15:40] Paul Dubois
CVE-2010-3680