Bug #43521 Foreign keys: crash if Falcon, 4 clients, much cascading
Submitted: 9 Mar 2009 23:39 Modified: 20 Dec 2013 6:52
Reporter: Peter Gulutzan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:6.1.0-alpha-debug OS:Linux (SUSE 10 | 32-bit)
Assigned to: CPU Architecture:Any

[9 Mar 2009 23:39] Peter Gulutzan
Description:
I'm using mysql-6.1-fk-stage.
I start mysqld with --foreign-key-all-engines=1.

I create two Falcon tables, one is a parent, the other is a child.
I create a procedure which will repeatedly insert, update, delete, cascade.
I start four connections and call the procedure from each connection.
After a few minutes, crash.

How to repeat:
Start mysqld with --foreign-key-all-engines=1.
Start four clients.

On client #1, say:
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 REFERENCES t1 (s1) ON UPDATE CASCADE ON DELETE CASCADE);
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;
      END;
    BEGIN
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
      SET v2 = RAND() * 1000;
      DELETE FROM t1 WHERE s1 = v2;
      END;
    COMMIT;
    IF (SELECT COUNT(*) FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2)) > 0 THEN
      SELECT 'error';
      END IF;
    ROLLBACK;
    IF (SELECT COUNT(*) FROM t2 WHERE s1 NOT IN (SELECT s1 FROM t1)) > 0 THEN
      SELECT 'error';
      END IF;
    ROLLBACK;
    SET v = v + 1;
    END WHILE;
  END//
DELIMITER ;
CALL p();
DROP TABLE IF EXISTS t2,t1;

On client #2, say:
USE test
CALL p();

On client #3, say:
USE test
CALL p();

On client #4, say:
USE test
CALL p();

Wait a few minutes.

One of three things will happen:
1. The clients will display 'error'.
This is impossible because tables t1 and t2 always are the same.
2. You will see a crash and the message will include the words
mysqld: handler.h:1554: int handler::ha_index_end(): Assertion `inited==INDEX' failed.
3. The third thing.
[10 Mar 2009 1:34] MySQL Verification Team
Thank you for the bug report. Verified as described:

    ->     SET v = v + 1;
    ->     END WHILE;
    ->   END//
Query OK, 0 rows affected (0.00 sec)

T1 >DELIMITER ;
T1 >CALL p();
+------+
| v    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

+-------+
| error |
+-------+
| error |
+-------+
1 row in set (17.10 sec)
[10 Mar 2009 13:53] Dmitry Lenev
Initial investigation shows that assertion failure in handler::ha_index_end() is caused by the fact that in stored procedures item clean up, which invokes this method, is performed after closing tables used by SP statement rather than vice versa as for prepared statements/normal statements. Therefore this problem should not be specific to new foreign keys implementation.
[10 Mar 2009 15:32] Dmitry Lenev
Indeed if one runs above routine in one connection to 6.0 version of server and issues several "flush tables" statements in another connection one fairly soon gets a crash in cleanup_items()/Item_subselect::cleanup(). This confirms above hypothesis about problem with cleanup handling in stored procedures and shows that 2nd issue has nothing to do with new foreign keys (since 6.0 does not support them).
[11 Mar 2009 10:33] Dmitry Lenev
Note that the first issue mentioned by Peter (getting 'error' on client due to child and parent table being out of sync) stems from the same reason as bug #41690 "Foreign keys: failure with two connections" and therefore should be treated as its duplicate. Therefore in this report I suggest to focus on issue #2 only.
[20 Dec 2013 6:52] Erlend Dahl
6.x project was abandoned years ago.