Bug #58933 Assertion `thd- >is_error()' fails when ongoing OPTIMIZE TABLE is killed
Submitted: 15 Dec 2010 1:13 Modified: 12 Jan 2011 18:17
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.5.9-debug, 5.6.1-m5-debug OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Tags: assertion, KILL, Optimize
Triage: Triaged: D1 (Critical)

[15 Dec 2010 1:13] Elena Stepanova
Description:
mysqld: sql/sql_admin.cc:737: bool mysql_admin_table(THD*, TABLE_LIST*, HA_CHECK_OPT*, const char*, thr_lock_type, bool, bool, uint, int (*)(THD*, TABLE_LIST*, HA_CHECK_OPT*), int (handler::*)(THD*, HA_CHECK_OPT*), int (*)(THD*, TABLE_LIST*)): Assertion `thd->is_error()' failed.
101215  3:40:04 - mysqld got signal 6 ;

#6  0x00002ba91df2e286 in __assert_fail () from /lib64/libc.so.6
#7  0x00000000006f5b45 in mysql_admin_table (thd=0x1a7f470, tables=0x1a8a698, check_opt=0x1a81e20, operator_name=0xbd76c0 "optimize", lock_type=TL_WRITE,
    open_for_modify=false, no_warnings_for_error=false, extra_open_options=0, prepare_func=0, operator_func=
    (int ( class handler::*)(class THD *,HA_CHECK_OPT *,)) 963826, view_operator_func=0)
    at sql/sql_admin.cc:737
#8  0x00000000006f63e0 in Sql_cmd_optimize_table::execute (this=0x1a8abe8, thd=0x1a7f470)
    at sql/sql_admin.cc:993
#9  0x00000000005fff18 in mysql_execute_command (thd=0x1a7f470) at sql/sql_parse.cc:4337
#10 0x0000000000600521 in mysql_parse (thd=0x1a7f470, rawbuf=0x1a8a5a0 "OPTIMIZE TABLE test_shutdown", length=28, parser_state=0x458cc9d0)
    at sql/sql_parse.cc:5537
#11 0x0000000000600d7f in dispatch_command (command=COM_QUERY, thd=0x1a7f470, packet=0x1a82571 "", packet_length=28)
    at sql/sql_parse.cc:1075
#12 0x0000000000602252 in do_command (thd=0x1a7f470) at sql/sql_parse.cc:815
#13 0x00000000006ce531 in do_handle_one_connection (thd_arg=0x1a7f470) at sql/sql_connect.cc:745
#14 0x00000000006ce62c in handle_one_connection (arg=0x1a7f470) at sql/sql_connect.cc:684
#15 0x00000000009c0d40 in pfs_spawn_thread (arg=0x1a3e4c0) at storage/perfschema/pfs.cc:1360
#16 0x00002ba91d956143 in start_thread () from /lib64/libpthread.so.0
#17 0x00002ba91dfc58cd in clone () from /lib64/libc.so.6

Query (0x1a8a5a0): OPTIMIZE TABLE test_shutdown
Connection ID (thread ID): 3
Status: KILL_CONNECTION

Thread 2 (process 22537):
#0  0x00002ba91df96ed1 in nanosleep () from /lib64/libc.so.6
#1  0x00002ba91df96cd9 in sleep () from /lib64/libc.so.6
#2  0x0000000000576312 in close_connections () at sql/mysqld.cc:1112
#3  0x000000000057677c in kill_server (sig_ptr=0x0) at sql/mysqld.cc:1284
#4  0x00000000005767b2 in kill_server_thread (arg=0x4584b168) at sql/mysqld.cc:1312
#5  0x00000000009c0d40 in pfs_spawn_thread (arg=0x1a2e1e0) at storage/perfschema/pfs.cc:1360
#6  0x00002ba91d956143 in start_thread () from /lib64/libpthread.so.0
#7  0x00002ba91dfc58cd in clone () from /lib64/libc.so.6
#8  0x0000000000000000 in ?? ()

How to repeat:
# The test case is not totally deterministic, 
# but it was crashing quite reliably when I was running it 
# (~80-85% runs hit the assertion)

--connect (con1,localhost,root,,)
CREATE TABLE test_shutdown (f1 INT, f2 INT) ENGINE=InnoDB;
CHECK TABLE test_shutdown;
insert into test_shutdown values (0,1),(2,3),(4,5),(6,7),(8,9);
let $ins = 14;
while ($ins)
{
  INSERT INTO test_shutdown SELECT * FROM test_shutdown;
  dec $ins;
}
--send OPTIMIZE TABLE test_shutdown
--connection default
--shutdown_server

# End of test
[15 Dec 2010 7:54] Shane Bester
Valeriy, run two instances of "call p1" on a debug build of 5.5:

delimiter $
drop table if exists `t1` $
create table `t1`(`a` int)engine=innodb$
drop procedure if exists `p1`$
create procedure `p1`()
begin
declare continue handler for sqlexception begin end;
repeat
  select id from information_schema.processlist where info like '%optimize table%' limit 1 into @id;
  optimize table `t1`;
  prepare stmt from 'kill query @id';
  execute stmt;
  deallocate prepare stmt;
until 1=2 end repeat;
end $
delimiter ;
call `p1`();
[15 Dec 2010 9:09] Jon Olav Hauglid
Could be a duplicate of Bug#54109
[15 Dec 2010 9:32] Jon Olav Hauglid
A different test case:

CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1), (2);
connect (con1,localhost,root);
let $ID= `SELECT connection_id()`;
while (1)
{
  --send OPTIMIZE TABLE t1
  connection default;
  eval KILL QUERY $id;
  connection con1;
  --reap
}
[15 Dec 2010 9:40] Valeriy Kravchuk
Verified with last test case from Shane on 32-bit Ubuntu 10.04 (current mysql-5.5-security tree):

Version: '5.5.9-valgrind-max-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: /home2/openxs/bzr2/mysql-5.5-security/sql/sql_admin.cc:731: bool mysql_admin_table(THD*, TABLE_LIST*, HA_CHECK_OPT*, const char*, thr_lock_type, bool, bool, uint, int (*)(THD*, TABLE_LIST*, HA_CHECK_OPT*), int (handler::*)(THD*, HA_CHECK_OPT*), int (*)(THD*, TABLE_LIST*)): Assertion `thd->is_error()' failed.
101215 11:38:13 - mysqld got signal 6 ;
...
[15 Dec 2010 9:59] Jon Olav Hauglid
Bug#54109 was marked as a duplicate of this bug.
[15 Dec 2010 10:37] Bugs System
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/126908

3195 Jon Olav Hauglid	2010-12-15
      Bug #58933 Assertion `thd- >is_error()' fails on shutdown with ongoing
                 OPTIMIZE TABLE
      
      OPTIMIZE TABLE for InnoDB tables is handled as recreate + analyze.
      The triggered assert checked that an error had been reported if either
      recreate or analyze failed. However the assert failed to take into
      account that they could have failed because OPTIMIZE TABLE had been
      victim of KILL QUERY, KILL CONNECTION or server shutdown.
      
      This patch adjusts the assert to take this possibility into account.
      The problem was only noticeable on debug versions of the server.
      
      Test case added to innodb_mysql_sync.test.
[10 Jan 2011 13:13] Bugs System
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/128290

3228 Jon Olav Hauglid	2011-01-10
      Bug #58933 Assertion `thd- >is_error()' fails on shutdown with ongoing
                 OPTIMIZE TABLE
      
      OPTIMIZE TABLE for InnoDB tables is handled as recreate + analyze.
      The triggered assert checked that an error had been reported if either
      recreate or analyze failed. However the assert failed to take into
      account that they could have failed because OPTIMIZE TABLE had been
      victim of KILL QUERY, KILL CONNECTION or server shutdown.
      
      This patch adjusts the assert to take this possibility into account.
      The problem was only noticeable on debug versions of the server.
      
      Test case added to innodb_mysql_sync.test.
[10 Jan 2011 13:27] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:jon.hauglid@oracle.com-20110110132613-67jwde8yl5a751t2) (version source revid:jon.hauglid@oracle.com-20110110132613-67jwde8yl5a751t2) (merge vers: 5.6.2) (pib:24)
[10 Jan 2011 13:27] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jon.hauglid@oracle.com-20110110131223-lqx0cgt1w7h64e2y) (version source revid:jon.hauglid@oracle.com-20110110131223-lqx0cgt1w7h64e2y) (merge vers: 5.5.9) (pib:24)
[12 Jan 2011 18:17] Paul Dubois
Noted in 5.5.9, 5.6.2 changelogs.

OPTIMIZE TABLE for an InnoDB table could raise an assertion if the
operation failed because it had been killed.