MySQL Bugs: #14480: CREATE ... SELECT may crash the server + write wrong data to frm file

Bug #14480	CREATE ... SELECT may crash the server + write wrong data to frm file
Submitted:	30 Oct 2005 0:58	Modified:	20 Nov 2005 4:54
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1/5.0 BK	OS:	Linux (Linux)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
This statement:
create table t5 ( a varchar(12) charset utf8 collate utf8_bin not null, primary key (a) ) select a from t2 ;
will crash the server. After the server has been restarted, attempts to access table t5 will cause  "Incorrect information in file" error.

I was the last one touching the code around CREATE... SELECT (BUG#14139), but the crash happens with tree rolled back to just-before my patch state as well.

How to repeat:
Run this:
create table t2 (a int);
insert into t2 values (111);
create table t1 ( a varchar(12) charset utf8 collate utf8_bin not null, primary key (a) ) select a from t2 ;

And get this:
Program received signal SIGABRT, Aborted.
  [Switching to Thread 147466 (LWP 7690)]
  0xb7dfe3d1 in kill () from /lib/libc.so.6
(gdb) where
  #0  0xb7dfe3d1 in kill () from /lib/libc.so.6
  #1  0xb7f5c131 in pthread_kill () from /lib/libpthread.so.0
  #2  0xb7f5c4ab in raise () from /lib/libpthread.so.0
  #3  0xb7dfe164 in raise () from /lib/libc.so.6
  #4  0xb7dff63d in abort () from /lib/libc.so.6
  #5  0xb7df758f in __assert_fail () from /lib/libc.so.6
  #6  0x081e786d in make_empty_rec (file=31, table_type=DB_TYPE_MYISAM, table_options=9, create_fields=@0x8ba7e9c, reclength=109, null_fields=0, data_offset=1) at unireg.cc:701
  #7  0x081e5f2b in mysql_create_frm (thd=0x8ba7b98, file_name=0xb59f5df4 "./junk80/t5.frm", db=0x8bb58f0 "junk80", table=0x8be0ad8 "t5", create_info=0x8ba7f60, create_fields=@0x8ba7e9c, keys=1, key_info=0x8be18f8, db_file=0x8be1938) at unireg.cc:151
  #8  0x081e6248 in rea_create_table (thd=0x8ba7b98, file_name=0xb59f5df4 "./junk80/t5.frm", db=0x8bb58f0 "junk80", table=0x8be0ad8 "t5", create_info=0x8ba7f60, create_fields=@0x8ba7e9c, keys=1, key_info=0x8be18f8) at unireg.cc:243
  #9  0x08211a19 in mysql_create_table (thd=0x8ba7b98, db=0x8bb58f0 "junk80", table_name=0x8be0ad8 "t5", create_info=0x8ba7f60, fields=@0x8ba7e9c, keys=@0x8ba7e90, tmp_table=false, select_field_count=1) at sql_table.cc:1416
  #10 0x0821205e in create_table_from_items (thd=0x8ba7b98, create_info=0x8ba7f60, db=0x8bb58f0 "junk80", name=0x8be0ad8 "t5", extra_fields=0x8ba7e9c, keys=0x8ba7e90, items=0x8ba7d28, lock=0x8be0d90) at sql_table.cc:1555
  #11 0x081bba68 in select_create::prepare (this=0x8be0d28, values=@0x8ba7d28, u=0x8ba7be0) at sql_insert.cc:1809
  #12 0x0819b531 in JOIN::prepare (this=0x8be0d98, rref_pointer_array=0x8ba7dac, tables_init=0x8be0cc0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x8ba7cc0, unit_arg=0x8ba7be0) at sql_select.cc:406
  #13 0x0819f50d in mysql_select (thd=0x8ba7b98, rref_pointer_array=0x8ba7dac, tables=0x8be0cc0, wild_num=0, fields=@0x8ba7d28, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2424588800, result=0x8be0d28, unit=0x8ba7be0, select_lex=0x8ba7cc0) at sql_select.cc:1601
  #14 0x0819abde in handle_select (thd=0x8ba7b98, lex=0x8ba7bd4, result=0x8be0d28) at sql_select.cc:179
  #15 0x081719ad in mysql_execute_command (thd=0x8ba7b98) at sql_parse.cc:2521
  #16 0x081767ac in mysql_parse (thd=0x8ba7b98, inBuf=0x8be0a40 "create table t5 ( a varchar(12) charset utf8 collate utf8_bin not null, primary key (a) ) select a from t2", length=106) at sql_parse.cc:4339
  #17 0x0816f06d in dispatch_command (command=COM_QUERY, thd=0x8ba7b98, packet=0x8bd7d29 "create table t5 ( a varchar(12) charset utf8 collate utf8_bin not null, primary key (a) ) select a from t2", packet_length=107) at sql_parse.cc:1499
  #18 0x0816e8d5 in do_command (thd=0x8ba7b98) at sql_parse.cc:1312
  #19 0x0816dc88 in handle_one_connection (arg=0x8ba7b98) at sql_parse.cc:1044
  #20 0xb7f5913d in pthread_start_thread () from /lib/libpthread.so.0
  #21 0xb7f592e2 in pthread_start_thread_event () from /lib/libpthread.so.0
  #22 0xb7e8b07a in clone () from /lib/libc.so.6
(gdb) 

After server restart:
select * from t5
ERROR 1033 (HY000): Incorrect information in file: './junk80/t5.frm'

[New Thread 1156033456 (LWP 6595)]
mysqld: unireg.cc:701: bool make_empty_rec(int, db_type, unsigned int, List<create_field>&, unsigned int, unsigned int, long unsigned int): Assertion `data_offset == ((null_fields + null_count + 7) / 8)' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 1156033456 (LWP 6595)]
0xffffe410 in ?? ()
(gdb) bt full
#0  0xffffe410 in ?? ()
No symbol table info available.
#1  0x44e76a7c in ?? ()
No symbol table info available.
#2  0x00000006 in ?? ()
No symbol table info available.
#3  0x40214b75 in abort () from /lib/tls/libc.so.6
No symbol table info available.
#4  0x4020c903 in __assert_fail () from /lib/tls/libc.so.6
No symbol table info available.
#5  0x081df7fb in make_empty_rec 

<cut>

051029 23:28:47 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.16-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1132456880 (LWP 6611)]
mysqld: unireg.cc:780: bool make_empty_rec(THD*, int, db_type, unsigned int, List<create_field>&, unsigned int, long unsigned int): Assertion `data_offset == ((null_count + 7) / 8)' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 1132456880 (LWP 6611)]
0xffffe410 in ?? ()
(gdb)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31669

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32030

The fix has been pushed into 4.1.16 tree
Bug description for the changelog:
a "CREATE TABLE tbl (...) SELECT ..." could crash the server and/or write invalid data into tbl.frm file.

Noted in 4.1.16 changelog.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32097

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32107

During the merge (to 5.0) had to make this fix also (in both 4.1 and 5.0) :
If one specifes a default value in create_list, it is now actually used.

Example:
create table t1 (a int);
insert into t1 values(1);
create table t2 ( a1 int default 3) select a1 from t1;

BEFORE THE FIX:
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

AFTER THE FIX:
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) default '3'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Noted in 5.0.17 changelog.