MySQL Bugs: #28652: MySQL (with-debug=full) asserts when alter table operations

Bug #28652	MySQL (with-debug=full) asserts when alter table operations
Submitted:	24 May 2007 12:37	Modified:	18 Jun 2007 19:26
Reporter:	Tianlei HU	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.32, 5.0.33, 5.0.44-bk	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any
Tags:	ALTER TABLE, assertion, debug version

Description:
We compile debug version of mysql, and cause the mysqld assertion failure. We tried the sql on mysql 5.0.32, 5.0.33 and the current bk version(all compiled by --with-debug=full/max debug), and they all asserted.

The core dump shows that the assertion is at:
#0  0x00002b2effe87797 in pthread_kill () from /lib/libpthread.so.0
#1  0x00000000005aefc5 in handle_segfault (sig=6) at mysqld.cc:2132
#2  <signal handler called>
#3  0x00002b2f0069c07b in raise () from /lib/libc.so.6
#4  0x00002b2f0069d84e in abort () from /lib/libc.so.6
#5  0x00002b2f00695af4 in __assert_fail () from /lib/libc.so.6
#6  0x00000000005a9066 in wait_if_global_read_lock (thd=0x2aaabdaad100, abort_on_refresh=false, is_not_commit=false)
    at lock.cc:1218
#7  0x0000000000679f6e in ha_commit_trans (thd=0x2aaabdaad100, all=false) at handler.cc:692
#8  0x00000000006af19d in mysql_alter_table (thd=0x2aaabdaad100, new_db=0x2aaac86f8f78 "space", new_name=0x2aaac86f8c78 "Circle", 
    create_info=0x2aaabdaadbd0, table_list=0x2aaac86f8cc0, fields=@0x2aaabdaada28, keys=@0x2aaabdaada10, order_num=0, order=0x0, 
    ignore=false, alter_info=0x2aaabdaaddb0, do_send_ok=true) at sql_table.cc:3817
#9  0x00000000005c775b in mysql_execute_command (thd=0x2aaabdaad100) at sql_parse.cc:3138
#10 0x00000000005cb24c in mysql_parse (thd=0x2aaabdaad100, inBuf=0x2aaac86f8be0 "alter table Circle alter BlogNum set default 0", 
    length=<value optimized out>) at sql_parse.cc:5834
#11 0x00000000005cb73b in dispatch_command (command=<value optimized out>, thd=0x2aaabdaad100, packet=<value optimized out>, 
    packet_length=<value optimized out>) at sql_parse.cc:1775
#12 0x00000000005cca82 in do_command (thd=0x2aaabdaad100) at sql_parse.cc:1557
#13 0x00000000005cd7ad in handle_one_connection (arg=<value optimized out>) at sql_parse.cc:1188
#14 0x00002b2effe83f1a in start_thread () from /lib/libpthread.so.0
#15 0x00002b2f00736602 in clone () from /lib/libc.so.6
#16 0x0000000000000000 in ?? ()

How to repeat:
Following sql can repeat the assertion:
  create table test.test (i int);
  alter table test.test comment 'a test';
or:
  create table test.test (i int);
  alter table test.test alter i set default 0;

Suggested fix:
remove the assertion.

I have missed one point that, we need to create a innodb table to reproduce the problem. i.e. the reproduce sql should be:
  create table test(i int) engine=innodb;
  alter table test comment '123'
or
  create table test(i int) engine=innodb;
  alter table test alter i set default 0

However, a alter table which needs to copy the whole table to a temporary table would not generate such assertion failure, for example:
  alter table test modify i int default 0

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.44-BK built from today's sources --with-debug=full:

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test'
mysql> create table test (i int);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table test comment 'a test';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test alter i set default 0;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.44-debug |
+--------------+
1 row in set (0.01 sec)

Please, check again. In case of the same problem, please, send your my.cnf.

Have you tried to create an innodb table, not a default myisam one, as i mentioned in the previous comment?

I am using a freshly compile/installed mysqld, with the default my.inf file.
This is the screen copy:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.44-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> drop table test.test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test.test (i int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table test.test comment 'a test'; 
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> drop table test.test;     
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

Query OK, 0 rows affected (0.01 sec)

mysql> create table test.test (i int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table test.test alter i set default 0;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Sorry, I missed your comment. I can repeat with 5.0.44-BK and InnoDB table:

openxs@suse:~/dbs/5.0> 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 1
Server version: 5.0.44-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test (i int) engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table test comment 'a test';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
070518 08:13:03  mysqld restarted

mysql> show create table test\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `i` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='a test'
1 row in set (0.15 sec)

mysql> alter table test alter i set default 0;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
070518 08:13:15  mysqld restarted

Resolved stack trace:

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump -s /tmp/mysqld5.sym 28652.stack
0x81a6028 handle_segfault + 482
0xffffe410 _end + -141009552
0x401b6b75 _end + 934536405
0x401ae903 _end + 934503011
0x81a0e9f _Z24wait_if_global_read_lockP3THDbb + 131
0x827df7a _Z15ha_commit_transP3THDb + 284
0x82a0dab _Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP13st_table_
listP10Alter_infojP8st_orderb + 10153
0x81bfb2c _Z21mysql_execute_commandP3THD + 6838
0x81c6377 _Z11mysql_parseP3THDPKcjPS2_ + 399
0x81bcb29 _Z16dispatch_command19enum_server_commandP3THDPcj + 1959
0x81bc372 _Z10do_commandP3THD + 534
0x81bb4f4 handle_one_connection + 1046
0x40050aa7 _end + 933069831
0x40247c2e _end + 935130510

It is a MySQL assertion that fails. I need to check if InnoDB somehow misuses the ha_commit_trans() call.

Dmitri and Konstantin said that this is a MySQL Server bug.

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/commits/27559

ChangeSet@1.2504, 2007-05-29 16:35:58+05:00, ramil@mysql.com +3 -0
  Fix for bug #28652: MySQL (with-debug=full) asserts when alter table operations
  
  Problem: we may create a deadlock committing changes in the mysql_alter_table() when 
  LOCK_open is set.
  Fix: unlock LOCK_open before commit to avoid possible deadlocks.

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/commits/27948

ChangeSet@1.2504, 2007-06-01 22:53:50+05:00, ramil@mysql.com +3 -0
  Fix for bug #28652: MySQL (with-debug=full) asserts when alter table operations
  
  Problem: we may create a deadlock committing changes in the mysql_alter_table() when 
  LOCK_open is set. Moreover, "in some variants of the ALTER TABLE commit
  happens earlier, outside of LOCK_open, in other later - inside. It's no good, a storage 
  engine code that is called in between could expect a consistency - either there is a 
  transaction or there is not".
  Fix: move the commit to happen earlier and outside of the LOCK_open.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.

For debug builds, ALTER TABLE could trigger an assertion failure due
to occurrence of a deadlock when committing changes.