[MDEV-16465] Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.0.37, 10.1.36, 10.2.18, 10.3.10
Component/s: Data Definition - Alter Table, Locking, Storage Engine - InnoDB
Labels:
- foreign-keys
- mdl

Description

--source include/have_innodb.inc

CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB;

CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=InnoDB;

--connect (con1,localhost,root,,)

--send

  ALTER TABLE t1 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t2(b) ON DELETE CASCADE ON UPDATE CASCADE;

--connection default

CREATE TABLE t3 LIKE t1;

UPDATE t2 SET b = 1;

--connection con1

--reap

--disconnect con1

--connection default

DROP TABLE IF EXISTS t3;

DROP TABLE IF EXISTS t1;

DROP TABLE IF EXISTS t2;

The test case is non-deterministic, so it can end up differently.
Sometimes it just passes.
Sometimes it executes to the end, but fails with

10.2 a31e99a89cc75
2018-06-11 21:16:45 140673673762560 [ERROR] Invalid (old?) table or database name '#sql-8e9_a'
^ Found warnings in /data/bld/10.2/mysql-test/var/log/mysqld.1.err

Sometimes it hangs upon DROP TABLE

10.2 a31e99a89cc75
Thread 3 (Thread 0x7f6e6c094700 (LWP 2482)):
#0 0x00007f6e7af0b7dd in nanosleep () at ../sysdeps/unix/syscall-template.S:84
#1 0x0000556ea3926439 in os_thread_sleep (tm=20000) at /data/src/10.2/storage/innobase/os/os0thread.cc:230
#2 0x0000556ea39a8d90 in fil_wait_crypt_bg_threads (table=0x7f6e18020e58) at /data/src/10.2/storage/innobase/row/row0mysql.cc:3175
#3 0x0000556ea39a9e6e in row_drop_table_for_mysql (name=0x7f6e6c0907c0 "test/t1", trx=0x7f6e6f1967e8, drop_db=false, create_failed=0, nonatomic=true) at /data/src/10.2/storage/innobase/row/row0mysql.cc:3549
#4 0x0000556ea3876c81 in ha_innobase::delete_table (this=0x7f6e24012d60, name=0x7f6e6c092300 "./test/t1") at /data/src/10.2/storage/innobase/handler/ha_innodb.cc:13270
#5 0x0000556ea355ee90 in handler::ha_delete_table (this=0x7f6e24012d60, name=0x7f6e6c092300 "./test/t1") at /data/src/10.2/sql/handler.cc:4325
#6 0x0000556ea35598c2 in ha_delete_table (thd=0x7f6e24000b00, table_type=0x556ea6ca6b10, path=0x7f6e6c092300 "./test/t1", db=0x7f6e24012b30 "test", alias=0x7f6e240124e8 "t1", generate_warning=true) at /data/src/10.2/sql/handler.cc:2352
#7 0x0000556ea33a41f0 in mysql_rm_table_no_locks (thd=0x7f6e24000b00, tables=0x7f6e24012520, if_exists=true, drop_temporary=false, drop_view=false, dont_log_query=false, dont_free_locks=false) at /data/src/10.2/sql/sql_table.cc:2468
#8 0x0000556ea33a340d in mysql_rm_table (thd=0x7f6e24000b00, tables=0x7f6e24012520, if_exists=1 '\001', drop_temporary=0 '\000') at /data/src/10.2/sql/sql_table.cc:2088
#9 0x0000556ea32e5e47 in mysql_execute_command (thd=0x7f6e24000b00) at /data/src/10.2/sql/sql_parse.cc:4756
#10 0x0000556ea32efe49 in mysql_parse (thd=0x7f6e24000b00, rawbuf=0x7f6e24012448 "DROP TABLE IF EXISTS t1", length=23, parser_state=0x7f6e6c093200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7999
#11 0x0000556ea32dd8f2 in dispatch_command (command=COM_QUERY, thd=0x7f6e24000b00, packet=0x7f6e240088a1 "DROP TABLE IF EXISTS t1", packet_length=23, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1821
#12 0x0000556ea32dc255 in do_command (thd=0x7f6e24000b00) at /data/src/10.2/sql/sql_parse.cc:1375
#13 0x0000556ea342ba66 in do_handle_one_connection (connect=0x556ea71cc470) at /data/src/10.2/sql/sql_connect.cc:1335
#14 0x0000556ea342b7f3 in handle_one_connection (arg=0x556ea71cc470) at /data/src/10.2/sql/sql_connect.cc:1241
#15 0x0000556ea384ccc0 in pfs_spawn_thread (arg=0x556ea71d69e0) at /data/src/10.2/storage/perfschema/pfs.cc:1862
#16 0x00007f6e7af02494 in start_thread (arg=0x7f6e6c094700) at pthread_create.c:333
#17 0x00007f6e792e893f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Thread 2 (Thread 0x7f6e6c04a700 (LWP 2485)):
#0 pthread_cond_timedwait@@GLIBC_2.3.2 () at ../sysdeps/unix/sysv/linux/x86_64/pthread_cond_timedwait.S:225
#1 0x0000556ea3cb72e1 in safe_cond_timedwait (cond=0x556ea4861f60 <COND_thread_cache>, mp=0x556ea4864800 <LOCK_thread_cache>, abstime=0x7f6e6c049da0, file=0x556ea3d46b40 "/data/src/10.2/include/mysql/psi/mysql_thread.h", line=1175) at /data/src/10.2/mysys/thr_mutex.c:545
#2 0x0000556ea31fa60a in inline_mysql_cond_timedwait (that=0x556ea4861f60 <COND_thread_cache>, mutex=0x556ea4864800 <LOCK_thread_cache>, abstime=0x7f6e6c049da0, src_file=0x556ea3d488aa "/data/src/10.2/sql/mysqld.cc", src_line=2973) at /data/src/10.2/include/mysql/psi/mysql_thread.h:1175
#3 0x0000556ea31ff72a in cache_thread (thd=0x7f6e18000b00) at /data/src/10.2/sql/mysqld.cc:2972
#4 0x0000556ea31ff9ce in one_thread_per_connection_end (thd=0x7f6e18000b00, put_in_cache=true) at /data/src/10.2/sql/mysqld.cc:3061
#5 0x0000556ea342bb4f in do_handle_one_connection (connect=0x556ea71b3fe0) at /data/src/10.2/sql/sql_connect.cc:1354
#6 0x0000556ea342b7f3 in handle_one_connection (arg=0x556ea71b3fe0) at /data/src/10.2/sql/sql_connect.cc:1241
#7 0x0000556ea384ccc0 in pfs_spawn_thread (arg=0x556ea71d6d90) at /data/src/10.2/storage/perfschema/pfs.cc:1862
#8 0x00007f6e7af02494 in start_thread (arg=0x7f6e6c04a700) at pthread_create.c:333
#9 0x00007f6e792e893f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Thread 1 (Thread 0x7f6e7b31bb80 (LWP 2453)):
#0 0x00007f6e792df4ed in poll () from /lib/x86_64-linux-gnu/libc.so.6
#1 0x0000556ea320648b in handle_connections_sockets () at /data/src/10.2/sql/mysqld.cc:6604
#2 0x0000556ea320577a in mysqld_main (argc=144, argv=0x556ea6a69370) at /data/src/10.2/sql/mysqld.cc:6070
#3 0x0000556ea31f9dc0 in main (argc=22, argv=0x7fff70057978) at /data/src/10.2/sql/main.cc:25

(all threads are attached).
When it hangs, it produces periodic error messages in the error log:

2018-06-11 21:17:50 140112235677440 [Warning] InnoDB: Waited 30 seconds for ref-count on table: `test`.`t1` space: 7

2018-06-11 21:18:20 140112235677440 [Warning] InnoDB: Waited 60 seconds for ref-count on table: `test`.`t1` space: 7

and in due time starts to complain about long semaphore wait

2018-06-11 21:32:16 139934062102272 [Warning] InnoDB: Waited 240 seconds for ref-count on table: `test`.`t1` space: 7

2018-06-11 21:32:19 139933834528512 [Warning] InnoDB: A long semaphore wait:

--Thread 139933817743104 has waited at srv0srv.cc line 2095 for 241.00 seconds the semaphore:

X-lock on RW-latch at 0x55d7d08ea4b0 created in file dict0dict.cc line 1108

a writer (thread id 139934062102272) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0purge.cc line 853

Last time write locked in file row0mysql.cc line 3356

2018-06-11 21:32:19 139933834528512 [Note] InnoDB: A semaphore wait:

--Thread 139933817743104 has waited at srv0srv.cc line 2095 for 241.00 seconds the semaphore:

X-lock on RW-latch at 0x55d7d08ea4b0 created in file dict0dict.cc line 1108

a writer (thread id 139934062102272) has reserved it in mode  exclusive

number of readers 0, waiters flag 1, lock_word: 0

Last time read locked in file row0purge.cc line 853

Last time write locked in file row0mysql.cc line 3356

Doesn't seem to be reproducible on 10.1

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

threads
30 kB
2018-06-11 18:36

Issue Links

causes

MDEV-17595 Server crashes in copy_data_between_tables or Assertion `thd->transaction.stmt.is_empty() || (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' fails in close_tables_for_reopen upon concurrent ALTER TABLE and FLUSH

Closed

relates to

MDEV-13564 TRUNCATE TABLE and undo tablespace truncation are not compatible with Mariabackup

Closed

MDEV-13200 Assertion `index->id == btr_page_get_index_id(page)' failed in btr_cur_search_to_nth_level

Closed

Activity

Ascending order - Click to sort in descending order

View 4 older comments

Marko Mäkelä added a comment - 2018-06-27 14:24

I think that an isolation violation or race condition between the ALTER and UPDATE should affect any InnoDB version. The dict_table_t::n_ref_count was introduced in MariaDB 10.0.

Marko Mäkelä added a comment - 2018-06-27 14:24 I think that an isolation violation or race condition between the ALTER and UPDATE should affect any InnoDB version. The dict_table_t::n_ref_count was introduced in MariaDB 10.0.

Elena Stepanova added a comment - 2018-06-27 18:11 - edited

Upon marko's advice, I added some data to the test case, this one and one from ~~MDEV-16060~~. Now I get "Invalid (old?) table or database name" on 10.0 and 10.1 as well. Release builds are much more willing to produce it than debug, but it happens on both after enough attempts.

I paste both test cases, because the probability of getting the error seems to vary depending on 10.0/10.1, innodb_plugin/xtradb, and debug/release – with some combinations one test case gives better results, and on other combinations the other one.

Please note that on 10.1 they need to be run with --mysqld=--sequence and on 10.0 with --mysqld=--sequence --mysqld=--plugin-load-add=ha_sequence (and with --repeat of course.

--source include/have_innodb.inc

CREATE TABLE t1 (`pk` INT PRIMARY KEY, f1 INT NOT NULL, INDEX ind_f1 (f1)) ENGINE=InnoDB;

CREATE TABLE t2 (`pk` INT PRIMARY KEY, f2 INT NOT NULL, INDEX ind_f2 (f2)) ENGINE=InnoDB;

--connect (con1,localhost,root,,test)

CREATE TABLE t3 (`pk` INT PRIMARY KEY, f3 INT NOT NULL, INDEX ind_f3 (f3)) ENGINE=InnoDB;

CREATE TABLE t4 (`pk` INT PRIMARY KEY, f4 INT NOT NULL, INDEX ind_f4 (f4)) ENGINE=InnoDB;

CREATE TABLE t5 (`pk` INT PRIMARY KEY, f5 INT NOT NULL, INDEX ind_f5 (f5), FOREIGN KEY (`pk`) REFERENCES t4 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

INSERT INTO t4 SELECT seq, seq FROM seq_1_to_10000;

INSERT INTO t3 SELECT seq, seq FROM seq_1_to_10000;

INSERT INTO t5 SELECT seq, seq FROM seq_1_to_10000;

INSERT INTO t2 SELECT seq, seq FROM seq_1_to_10000;

INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10000;

--send

  ALTER TABLE t3 ADD CONSTRAINT fk3 FOREIGN KEY (`pk`) REFERENCES t5 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE;

--connection default

UPDATE IGNORE t4 SET pk = 10001 WHERE `pk` = 1;

--connection con1

--error 0,ER_NO_REFERENCED_ROW_2,ER_ERROR_ON_RENAME

--reap

ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t2 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE;

# Cleanup

--disconnect con1

--connection default

DROP TABLE t3, t1, t5, t4, t2;

--source include/have_innodb.inc

CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB;

CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO t2 SELECT seq FROM seq_1_to_100000;

INSERT INTO t1 SELECT seq FROM seq_1_to_100000;

--connect (con1,localhost,root,,)

--send

  ALTER TABLE t1 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t2(b) ON DELETE CASCADE ON UPDATE CASCADE;

--connection default

CREATE TABLE t3 LIKE t1;

UPDATE t2 SET b = b + 100000 LIMIT 2;

--connection con1

--error 0,ER_NO_REFERENCED_ROW_2,ER_ERROR_ON_RENAME

--reap

--disconnect con1

--connection default

SELECT * FROM t1 WHERE a NOT IN (SELECT b FROM t2);

DROP TABLE IF EXISTS t3;

DROP TABLE IF EXISTS t1;

DROP TABLE IF EXISTS t2;

10.0 bf4244d1a0d
line
180627 21:08:03 [ERROR] Invalid (old?) table or database name '#sql-118e_4'
^ Found warnings in /data/bld/10.0-rel/mysql-test/var/log/mysqld.1.err

Elena Stepanova added a comment - 2018-06-27 18:11 - edited Upon marko 's advice, I added some data to the test case, this one and one from MDEV-16060 . Now I get "Invalid (old?) table or database name" on 10.0 and 10.1 as well. Release builds are much more willing to produce it than debug, but it happens on both after enough attempts. I paste both test cases, because the probability of getting the error seems to vary depending on 10.0/10.1, innodb_plugin/xtradb, and debug/release – with some combinations one test case gives better results, and on other combinations the other one. Please note that on 10.1 they need to be run with --mysqld=--sequence and on 10.0 with --mysqld=--sequence --mysqld=--plugin-load-add=ha_sequence (and with --repeat of course. --source include/have_innodb.inc CREATE TABLE t1 (`pk` INT PRIMARY KEY , f1 INT NOT NULL , INDEX ind_f1 (f1)) ENGINE=InnoDB; CREATE TABLE t2 (`pk` INT PRIMARY KEY , f2 INT NOT NULL , INDEX ind_f2 (f2)) ENGINE=InnoDB; --connect (con1,localhost,root,,test) CREATE TABLE t3 (`pk` INT PRIMARY KEY , f3 INT NOT NULL , INDEX ind_f3 (f3)) ENGINE=InnoDB; CREATE TABLE t4 (`pk` INT PRIMARY KEY , f4 INT NOT NULL , INDEX ind_f4 (f4)) ENGINE=InnoDB; CREATE TABLE t5 (`pk` INT PRIMARY KEY , f5 INT NOT NULL , INDEX ind_f5 (f5), FOREIGN KEY (`pk`) REFERENCES t4 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO t4 SELECT seq, seq FROM seq_1_to_10000; INSERT INTO t3 SELECT seq, seq FROM seq_1_to_10000; INSERT INTO t5 SELECT seq, seq FROM seq_1_to_10000; INSERT INTO t2 SELECT seq, seq FROM seq_1_to_10000; INSERT INTO t1 SELECT seq, seq FROM seq_1_to_10000; --send ALTER TABLE t3 ADD CONSTRAINT fk3 FOREIGN KEY (`pk`) REFERENCES t5 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE ; --connection default UPDATE IGNORE t4 SET pk = 10001 WHERE `pk` = 1; --connection con1 --error 0,ER_NO_REFERENCED_ROW_2,ER_ERROR_ON_RENAME --reap ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t2 (`pk`) ON DELETE CASCADE ON UPDATE CASCADE ; # Cleanup --disconnect con1 --connection default DROP TABLE t3, t1, t5, t4, t2; --source include/have_innodb.inc CREATE TABLE t1 (a INT NOT NULL ) ENGINE=InnoDB; CREATE TABLE t2 (b INT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO t2 SELECT seq FROM seq_1_to_100000; INSERT INTO t1 SELECT seq FROM seq_1_to_100000; --connect (con1,localhost,root,,) --send ALTER TABLE t1 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t2(b) ON DELETE CASCADE ON UPDATE CASCADE ; --connection default CREATE TABLE t3 LIKE t1; UPDATE t2 SET b = b + 100000 LIMIT 2; --connection con1 --error 0,ER_NO_REFERENCED_ROW_2,ER_ERROR_ON_RENAME --reap --disconnect con1 --connection default SELECT * FROM t1 WHERE a NOT IN ( SELECT b FROM t2); DROP TABLE IF EXISTS t3; DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; 10.0 bf4244d1a0d line 180627 21:08:03 [ERROR] Invalid (old?) table or database name '#sql-118e_4' ^ Found warnings in /data/bld/10.0-rel/mysql-test/var/log/mysqld.1.err

Sergei Golubchik added a comment - 2018-07-16 20:07 - edited

Looking at marko explanation above, here's a modification of elenst test that deterministically fails:

install soname 'ha_sequence';

--source include/have_innodb.inc

--source include/have_debug_sync.inc

CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB;

CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO t2 SELECT seq FROM seq_1_to_100;

INSERT INTO t1 SELECT seq FROM seq_1_to_100;

--connect (con1,localhost,root,,)

set debug_sync='alter_table_before_rename_result_table SIGNAL g1 WAIT_FOR g2';

--send

  ALTER TABLE t1 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t2(b) ON DELETE CASCADE ON UPDATE CASCADE;

--connection default

set debug_sync='before_execute_sql_command WAIT_FOR g1';

UPDATE t2 SET b = b + 100000 LIMIT 2;

set debug_sync='now SIGNAL g2';

--connection con1

--reap

--disconnect con1

--connection default

SELECT * FROM t1 WHERE a NOT IN (SELECT b FROM t2);

DROP TABLE IF EXISTS t1;

DROP TABLE IF EXISTS t2;

flush tables;

uninstall plugin sequence;

set debug_sync='reset';

The trick is to run the update after the ALTER finished creating the temporary table, but before it has renamed it.

Sergei Golubchik added a comment - 2018-07-16 20:07 - edited Looking at marko explanation above, here's a modification of elenst test that deterministically fails: install soname 'ha_sequence' ; --source include/have_innodb.inc --source include/have_debug_sync.inc CREATE TABLE t1 (a INT NOT NULL ) ENGINE=InnoDB; CREATE TABLE t2 (b INT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO t2 SELECT seq FROM seq_1_to_100; INSERT INTO t1 SELECT seq FROM seq_1_to_100; --connect (con1,localhost,root,,) set debug_sync= 'alter_table_before_rename_result_table SIGNAL g1 WAIT_FOR g2' ; --send ALTER TABLE t1 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t2(b) ON DELETE CASCADE ON UPDATE CASCADE ; --connection default set debug_sync= 'before_execute_sql_command WAIT_FOR g1' ; UPDATE t2 SET b = b + 100000 LIMIT 2; set debug_sync= 'now SIGNAL g2' ; --connection con1 --reap --disconnect con1 --connection default SELECT * FROM t1 WHERE a NOT IN ( SELECT b FROM t2); DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; flush tables; uninstall plugin sequence ; set debug_sync= 'reset' ; The trick is to run the update after the ALTER finished creating the temporary table, but before it has renamed it.

Sergei Golubchik added a comment - 2018-07-17 11:41 - edited

And this is a modification of the test that not only results in a seemingly harmless warning, but actually breaks foreign key constraints. All it takes is to add another table, referencing the one that is altered:

install soname 'ha_sequence';

--source include/have_innodb.inc

--source include/have_debug_sync.inc

CREATE TABLE t1 (f1 INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t2 (f2 INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t3 (f3 INT PRIMARY KEY, FOREIGN KEY (f3) REFERENCES t2(f2)) ENGINE=InnoDB;

INSERT INTO t1 SELECT seq FROM seq_1_to_100;

INSERT INTO t2 SELECT seq FROM seq_1_to_100;

INSERT INTO t3 SELECT seq FROM seq_1_to_100;

--connect con1,localhost,root

set debug_sync='alter_table_before_rename_result_table SIGNAL g1 WAIT_FOR g2';

--send

  ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (f2) REFERENCES t1(f1) ON DELETE CASCADE ON UPDATE CASCADE;

--connection default

set debug_sync='before_execute_sql_command WAIT_FOR g1';

UPDATE t1 SET f1 = f1 + 100000 LIMIT 2;

set debug_sync='now SIGNAL g2';

--connection con1

--reap

--disconnect con1

--connection default

SELECT * FROM t2 WHERE f2 NOT IN (SELECT f1 FROM t1);

SELECT * FROM t3 WHERE f3 NOT IN (SELECT f2 FROM t2);

DROP TABLE t3, t2, t1;

flush tables;

uninstall plugin sequence;

set debug_sync='reset';

Sergei Golubchik added a comment - 2018-07-17 11:41 - edited And this is a modification of the test that not only results in a seemingly harmless warning, but actually breaks foreign key constraints. All it takes is to add another table, referencing the one that is altered: install soname 'ha_sequence' ; --source include/have_innodb.inc --source include/have_debug_sync.inc CREATE TABLE t1 (f1 INT PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE t2 (f2 INT PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE t3 (f3 INT PRIMARY KEY , FOREIGN KEY (f3) REFERENCES t2(f2)) ENGINE=InnoDB; INSERT INTO t1 SELECT seq FROM seq_1_to_100; INSERT INTO t2 SELECT seq FROM seq_1_to_100; INSERT INTO t3 SELECT seq FROM seq_1_to_100; --connect con1,localhost,root set debug_sync= 'alter_table_before_rename_result_table SIGNAL g1 WAIT_FOR g2' ; --send ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (f2) REFERENCES t1(f1) ON DELETE CASCADE ON UPDATE CASCADE ; --connection default set debug_sync= 'before_execute_sql_command WAIT_FOR g1' ; UPDATE t1 SET f1 = f1 + 100000 LIMIT 2; set debug_sync= 'now SIGNAL g2' ; --connection con1 --reap --disconnect con1 --connection default SELECT * FROM t2 WHERE f2 NOT IN ( SELECT f1 FROM t1); SELECT * FROM t3 WHERE f3 NOT IN ( SELECT f2 FROM t2); DROP TABLE t3, t2, t1; flush tables; uninstall plugin sequence ; set debug_sync= 'reset' ;

Marko Mäkelä added a comment - 2018-09-03 14:56

During the testing of ~~MDEV-13564~~, mleich got a failure that I think would be prevented if not only ALTER TABLE, OPTIMIZE TABLE, CREATE/DROP INDEX but also TRUNCATE TABLE used the same protection rules: acquire not only MDL but also InnoDB table lock on the tables that are connected by FOREIGN KEY constraints.

bb-10.2-marko 9de899ac0983cec9165f3e8d4a5c67ce63e2b9f5
storage/innobase/row/row0mysql.cc:1724: void init_fts_doc_id_for_ref(dict_table_t, ulint): Assertion `foreign->foreign_table != __null' failed.

Marko Mäkelä added a comment - 2018-09-03 14:56 During the testing of MDEV-13564 , mleich got a failure that I think would be prevented if not only ALTER TABLE , OPTIMIZE TABLE , CREATE / DROP INDEX but also TRUNCATE TABLE used the same protection rules: acquire not only MDL but also InnoDB table lock on the tables that are connected by FOREIGN KEY constraints. bb-10.2-marko 9de899ac0983cec9165f3e8d4a5c67ce63e2b9f5 storage/innobase/row/row0mysql.cc:1724: void init_fts_doc_id_for_ref(dict_table_t*, ulint*): Assertion `foreign->foreign_table != __null' failed.

MariaDB Server

Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration