MySQL Bugs: #54044: Create temporary tables and using innodb crashes.

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	CPU Architecture:	Any

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;

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?

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.

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]

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)

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.

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)

The server-side change tracked in bug #54394

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

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.

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)

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.

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.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:alik@sun.com-20100723121827-3bsh51m5sj6g4oma) (pib:18)

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)

Noted in 5.5.6 changelog.

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)

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)

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)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100629125518-m3am4ia1ffjr0d0j) (pib:20)

Bug does not appear in any released 5.6.x version.

SRT: Severity C, CVSS: 4.9

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)

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)

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)

Already documented in the 5.1.49 changelog. No new changelog entries required. Setting back to Closed.

CVE-2010-3680