Bug #20437 Events: crash with procedure altering partitions
Submitted: 13 Jun 2006 20:23 Modified: 13 Jun 2006 22:47
Reporter: Peter Gulutzan Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.12-beta-debug OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: CPU Architecture:Any

[13 Jun 2006 20:23] Peter Gulutzan
Description:
I create a procedure which creates tables, adds and drops partitions, inserts, updates, deletes.
I create an event which calls this procedure. No problem.
I create a second event which calls this same procedure. Crash.

How to repeat:
mysql> delimiter //
mysql> set names utf8//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure pmy ()
    ->   begin
    ->     declare v1 int;
    ->     declare v2 int;
    ->     declare v3 int default 0;
    ->     declare continue handler for 1513
    ->     begin
    ->       set @v = 'alter table tmy add partition (partition p';
    ->       set @v = concat(@v,v2);
    ->       set @v = concat(@v,' values in (');
    ->       set @v = concat(@v,v2);
    ->       set @v = concat(@v,'))');
    ->       prepare ペ from @v;
    ->       execute ペ;
    ->       end;
    ->     drop table if exists tmy;
    ->     create table tmy (s1 int, s2 int, s3 int, primary key (s1,s2,s3))
    ->     partition by list (s1)
    ->     (partition p1 values in (1));
    ->     set v1 = 0;
    ->     while v1 < 100 do
    ->       set v2 = rand(v1) * 100;
    ->       if mod(v1,1) = 0 then
    ->         insert into tmy values (v2,v1,v3);
    ->         set v3 = v3 + 1;
    ->         end if;
    ->       if mod(v1,2) = 0 then
    ->         set v2 = rand(v1) * 100;
    ->         update tmy set s1 = v2 where s3 = v3;
    ->         set v3 = v3 + 1;
    ->         end if;
    ->       if mod(v1,3) = 0 then
    ->         delete from tmy where s1 = v3;
    ->         set v3 = v3 + 1;
    ->         end if;
    ->       if mod(v1,4) = 0 then
    ->         set @ = 'alter table tmy drop partition p';
    ->         set @ = concat(@,v2);
    ->         prepare ペ from @;
    ->         execute ペ;
    ->         end if;
    ->       set v1 = v1 + 1;
    ->       end while;
    ->   end//
Query OK, 0 rows affected (0.03 sec)

mysql> create event emy1 on schedule every 1 second do call pmy()//
Query OK, 1 row affected (0.00 sec)

mysql> create event emy2 on schedule every 1 second do call pmy()//
Query OK, 1 row affected (0.00 sec)

mysql> set global event_scheduler = 1//
Query OK, 0 rows affected (0.01 sec)

... at this point mysqld will crash.

The same statements as above, condensed:

delimiter //
set names utf8//
drop procedure pmy//
create procedure pmy ()
  begin
    declare v1 int;
    declare v2 int;
    declare v3 int default 0;
    declare continue handler for 1513
    begin
      set @v = 'alter table tmy add partition (partition p';
      set @v = concat(@v,v2);
      set @v = concat(@v,' values in (');
      set @v = concat(@v,v2);
      set @v = concat(@v,'))');
      prepare ペ from @v;
      execute ペ;
      end;
    drop table if exists tmy;
    create table tmy (s1 int, s2 int, s3 int, primary key (s1,s2,s3))
    partition by list (s1)
    (partition p1 values in (1));
    set v1 = 0;
    while v1 < 100 do
      set v2 = rand(v1) * 100;
      if mod(v1,1) = 0 then
        insert into tmy values (v2,v1,v3);
        set v3 = v3 + 1;
        end if;
      if mod(v1,2) = 0 then
        set v2 = rand(v1) * 100;
        update tmy set s1 = v2 where s3 = v3;
        set v3 = v3 + 1;
        end if;
      if mod(v1,3) = 0 then
        delete from tmy where s1 = v3;
        set v3 = v3 + 1;
        end if;
      if mod(v1,4) = 0 then
        set @ = 'alter table tmy drop partition p';
        set @ = concat(@,v2);
        prepare ペ from @;
        execute ペ;
        end if;
      set v1 = v1 + 1;
      end while;
  end//
create event emy1 on schedule every 1 second do call pmy()//
create event emy2 on schedule every 1 second do call pmy()//
set global event_scheduler = 1//
[13 Jun 2006 21:57] Andrey Hristov
After 1-2 minutes I get the following stack. Seems quite unrelated to events but partitioning stuff.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1118694320 (LWP 24559)]
0x082e8e4d in ha_myisam::external_lock (this=0x91fed20, thd=0x918c990, lock_type=2)
    at ha_myisam.cc:1448
1448      return mi_lock_database(file, !table->s->tmp_table ?

(gdb) p this->table->s
$15 = (TABLE_SHARE *) 0x0

(gdb) f 0
#0  0x082e8e4d in ha_myisam::external_lock (this=0x91fed20, thd=0x918c990, lock_type=2)
    at ha_myisam.cc:1448
1448      return mi_lock_database(file, !table->s->tmp_table ?

(gdb) l
1443    }
1444
1445
1446    int ha_myisam::external_lock(THD *thd, int lock_type)
1447    {
1448      return mi_lock_database(file, !table->s->tmp_table ?
1449                              lock_type : ((lock_type == F_UNLCK) ?
1450                                           F_UNLCK : F_EXTRA_LCK));
1451    }

(gdb) bt
#0  0x082e8e4d in ha_myisam::external_lock (this=0x91fed20, thd=0x918c990, lock_type=2)
    at ha_myisam.cc:1448
#1  0x082f0370 in ha_partition::external_lock (this=0x91feb60, thd=0x918c990, lock_type=2)
    at ha_partition.cc:2374
#2  0x082e2c78 in handler::ha_external_lock (this=0x91feb60, thd=0x918c990, lock_type=2)
    at handler.cc:3259
#3  0x081f5f73 in unlock_external (thd=0x918c990, table=0x91357e0, count=1) at lock.cc:641
#4  0x081f5389 in mysql_unlock_tables (thd=0x918c990, sql_lock=0x9135790) at lock.cc:271
#5  0x08241dc7 in close_thread_tables (thd=0x918c990, lock_in_use=false, skip_derived=false)
    at sql_base.cc:1075
#6  0x0815cb94 in fast_end_partition (thd=0x918c990, copied=0, deleted=0, table=0x9195eb0,
    table_list=0x91ee888, is_empty=false, lpt=0x42adafe0, written_bin_log=true)
    at sql_partition.cc:3518
#7  0x08160d97 in fast_alter_partition_table (thd=0x918c990, table=0x9195eb0, alter_info=0x9230a8c,
    create_info=0x9230930, table_list=0x91ee888, create_list=0x42adb4f0, key_list=0x42adb500,
    db=0x91eea28 "db1", table_name=0x91ee860 "tmy", fast_alter_partition=1) at sql_partition.cc:5863
#8  0x0831bcb5 in mysql_alter_table (thd=0x918c990, new_db=0x91eea28 "db1", new_name=0x91ee860 "tmy",
    create_info=0x9230930, table_list=0x91ee888, fields=@0x9230848, keys=@0x923083c, order_num=0,
    order=0x0, handle_duplicates=DUP_ERROR, ignore=false, alter_info=0x9230a8c, do_send_ok=true)
    at sql_table.cc:5577
#9  0x082176dc in mysql_execute_command (thd=0x918c990) at sql_parse.cc:3077
#10 0x08283f66 in Prepared_statement::execute (this=0x9230380, expanded_query=0x42add240,
    open_cursor=false) at sql_prepare.cc:2930
#11 0x082827d6 in mysql_sql_stmt_execute (thd=0x918c990) at sql_prepare.cc:2324
#12 0x082164f4 in mysql_execute_command (thd=0x918c990) at sql_parse.cc:2555
#13 0x08379df3 in sp_instr_stmt::exec_core (this=0x91d8750, thd=0x918c990, nextp=0x42add950)
    at sp_head.cc:2356
#14 0x08379a33 in sp_lex_keeper::reset_lex_and_exec_core (this=0x91d8778, thd=0x918c990,
    nextp=0x42add950, open_tables=false, instr=0x91d8750) at sp_head.cc:2230
#15 0x08379c75 in sp_instr_stmt::execute (this=0x91d8750, thd=0x918c990, nextp=0x42add950)
    at sp_head.cc:2307
#16 0x08376c44 in sp_head::execute (this=0x9234310, thd=0x918c990) at sp_head.cc:1084
#17 0x08377e1d in sp_head::execute_procedure (this=0x9234310, thd=0x918c990, args=0x922e04c)
    at sp_head.cc:1559
#18 0x0821b705 in mysql_execute_command (thd=0x918c990) at sql_parse.cc:4596
#19 0x08379df3 in sp_instr_stmt::exec_core (this=0x9227ee8, thd=0x918c990, nextp=0x42ade170)
---Type <return> to continue, or q <return> to quit---
    at sp_head.cc:2356
#20 0x08379a33 in sp_lex_keeper::reset_lex_and_exec_core (this=0x9227f10, thd=0x918c990,
    nextp=0x42ade170, open_tables=false, instr=0x9227ee8) at sp_head.cc:2230
#21 0x08379c75 in sp_instr_stmt::execute (this=0x9227ee8, thd=0x918c990, nextp=0x42ade170)
    at sp_head.cc:2307
#22 0x08376c44 in sp_head::execute (this=0x9227bb0, thd=0x918c990) at sp_head.cc:1084
#23 0x08377e1d in sp_head::execute_procedure (this=0x9227bb0, thd=0x918c990, args=0x42ade2e0)
    at sp_head.cc:1559
#24 0x08393510 in Event_timed::execute (this=0x915b880, thd=0x918c990, mem_root=0x918c9b0)
    at event_timed.cc:1413
#25 0x08388cf4 in event_worker_thread (arg=0x426eb300) at event_scheduler.cc:568
#26 0x40053aa7 in start_thread () from /lib/tls/libpthread.so.0
#27 0x40184c2e in clone () from /lib/tls/libc.so.6
[13 Jun 2006 22:47] Peter Gulutzan
I have just pulled again from the MySQL-5.1 tree. Now I cannot repeat.
And, although Andrey was able to repeat, he tells me that he was using
an older version of the tree.

Therefore I have changed my bug's status to "Can't Repeat".
It was a verified crashing bug, but something in the last 24 hours
has apparently fixed it.