Bug #43531 Crash if Falcon or MyISAM, subqueries, 4 clients
Submitted: 10 Mar 2009 11:24 Modified: 8 Sep 2009 8:18
Reporter: Dmitry Lenev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1, 6.0.11-bzr OS:Linux
Assigned to: Evgeny Potemkin CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[10 Mar 2009 11:24] Dmitry Lenev
Description:
While investigating reasons behind bug #43521 I have stumbled over the following problem which is repeatable even without new foreign keys and for 6.0 server.

I create two Falcon tables.
I create a procedure which will repeatedly insert, update, deletes and
execute sub-queries on these tables.
I start four connections and call the procedure from each connection.
After several minutes, crash.

Here is the backtrace:

...

(my_print_stacktrace+0x32) [0x8944ed5]
(handle_segfault+0x2cd) [0x831cbd7]
[0xffffe420]
(StorageInterface::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function)+0x2c3) [0x85fdc63]
(handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)+0x62) [0x846aa66]
(safe_index_read(st_join_table*)+0x60) [0x839f65a]
(subselect_indexsubquery_engine::exec()+0x342) [0x82cd394]
(Item_subselect::exec()+0x6a) [0x82d21ae]
(Item_in_subselect::exec()+0x135) [0x82d2327]
(Item_in_subselect::val_bool()+0x50) [0x82cac9e]
(Item::val_bool_result()+0x18) [0x8260298]
(Item_in_optimizer::val_int()+0x2c7) [0x82915b7]
(Item::val_bool()+0x50) [0x8250284]
(Item_func_not::val_int()+0x51) [0x82917c9]
[0x83a22f3]
(sub_select(JOIN*, st_join_table*, bool)+0x2be) [0x83a28e4]
[0x83aed8f]
(JOIN::exec()+0x20f9) [0x83c9875]
(subselect_single_select_engine::exec()+0x4b1) [0x82cdd3d]
(Item_subselect::exec()+0x6a) [0x82d21ae]
(Item_singlerow_subselect::val_int()+0x49) [0x82caf65]
(Arg_comparator::compare_int_signed()+0x20) [0x828ccb0]
(Arg_comparator::compare()+0x49) [0x829c2fd]
(Item_func_gt::val_int()+0x43) [0x8291115]
(Item::val_bool()+0x50) [0x8250284]
(sp_instr_jump_if_not::exec_core(THD*, unsigned int*)+0x46) [0x84f6d9e]
(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*)+0x1b7) [0x84f7b05]
(sp_instr_jump_if_not::execute(THD*, unsigned int*)+0x91) [0x84f7f03]
(sp_head::execute(THD*)+0x52c) [0x84fa208]
(sp_head::execute_procedure(THD*, List<Item>*)+0x76b) [0x84faf81]
(mysql_execute_command(THD*)+0x755a) [0x8335316]
(mysql_parse(THD*, char const*, unsigned int, char const**)+0x22f) [0x8336def]
(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x8ae) [0x8337de8]
(do_command(THD*)+0x242) [0x833911c]
(handle_one_connection+0x11d) [0x8325c8f]

How to repeat:
In the first connection:

USE test
DROP TABLE IF EXISTS t2,t1;
DROP PROCEDURE IF EXISTS p;
SET @@storage_engine = falcon;
CREATE TABLE t1 (s1 INT PRIMARY KEY);
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES
(new.s1);
CREATE TABLE t2 (s1 INT, key (s1));
DELIMITER //
CREATE PROCEDURE P ()
BEGIN
  DECLARE v INT DEFAULT 0;
  DECLARE v2 INT;
  SET @@autocommit = 0;
  WHILE v < 1000000 DO
    IF v MOD 1000 = 0 THEN SELECT v; END IF;
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
      SET v2 = RAND() * 1000;
      INSERT INTO t1 VALUES (v2);
      END;
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
      UPDATE t1 SET s1 = s1 - 1;
      UPDATE t2 SET s1 = s1 - 1;
      END;
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
      SET v2 = RAND() * 1000;
      DELETE FROM t1 WHERE s1 = v2;
      DELETE FROM t2 WHERE s1 = v2;
      END;
    COMMIT;
    IF (SELECT COUNT(*) FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2)) > 0
THEN SET @a:=1;
      END IF;
    ROLLBACK;
    IF (SELECT COUNT(*) FROM t2 WHERE s1 NOT IN (SELECT s1 FROM t1)) > 0
THEN SET @a:=1;
      END IF;
    ROLLBACK;
    SET v = v + 1;
    END WHILE;
  END//
DELIMITER ;
CALL p();

In the second, the third and the fourth connections start the same routine:

CALL p();

And wait till server crashes due to SIGSEGV.
[10 Mar 2009 15:56] Sveta Smirnova
Thank you for the report.

Verified as described. Crash happens if @@storage_engine = myisam too.
[10 Mar 2009 16:38] Dmitry Lenev
After a bit more thinking I believe that this bug (especially for MyISAM) can be caused by the same problem with item cleanup in stored routines as one mentioned in bug #43521 (see test case with flush tables).
[18 Mar 2009 19:41] Sveta Smirnova
Bug exists in version 5.1 as well. Version 5.0 fails with timeout.
[18 Mar 2009 19:59] Sveta Smirnova
5.1 backtrace

Attachment: bug43531_5_1.trace (application/octet-stream, text), 13.19 KiB.

[18 Mar 2009 20:00] Sveta Smirnova
6.0 backtrace

Attachment: bug43531_6_0.trace (application/octet-stream, text), 21.88 KiB.