Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6.0
Description
SET unique_checks=0,foreign_key_checks=0,autocommit=0;
|
CREATE TABLE t1 (c INT) ENGINE=InnoDB;
|
CREATE TABLE t2 (c INT) ENGINE=InnoDB;
|
LOCK TABLE t2 WRITE;
|
INSERT INTO t2 VALUES (1);
|
CREATE TABLE t1 (c INT) ENGINE=InnoDB;
|
CREATE TEMPORARY TABLE t3 (a INT) ENGINE=InnoDB;
|
INSERT INTO t3 VALUES (1);
|
INSERT INTO t2 VALUES (2);
|
Leads to:
|
10.6.0 a3e3225cd3b816d47621e749e21a71b6a864a96a (Debug) |
mysqld: /test/10.6_dbg/storage/innobase/lock/lock0lock.cc:4695: dberr_t lock_rec_insert_check_and_lock(const rec_t*, buf_block_t*, dict_index_t*, que_thr_t*, mtr_t*, bool*): Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed.
|
|
10.6.0 a3e3225cd3b816d47621e749e21a71b6a864a96a (Debug) |
Core was generated by `/test/MD150421-mariadb-10.6.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
|
Program terminated with signal SIGABRT, Aborted.
|
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
|
[Current thread is 1 (Thread 0x14a0f075f700 (LWP 300047))]
|
(gdb) bt
|
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
|
#1 0x000014a0f331d859 in __GI_abort () at abort.c:79
|
#2 0x000014a0f331d729 in __assert_fail_base (fmt=0x14a0f34b3588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x559157c328c8 "lock_table_has(trx, index->table, LOCK_IX)", file=0x559157c304d0 "/test/10.6_dbg/storage/innobase/lock/lock0lock.cc", line=4695, function=<optimized out>) at assert.c:92
|
#3 0x000014a0f332ef36 in __GI___assert_fail (assertion=assertion@entry=0x559157c328c8 "lock_table_has(trx, index->table, LOCK_IX)", file=file@entry=0x559157c304d0 "/test/10.6_dbg/storage/innobase/lock/lock0lock.cc", line=line@entry=4695, function=function@entry=0x559157c32800 "dberr_t lock_rec_insert_check_and_lock(const rec_t*, buf_block_t*, dict_index_t*, que_thr_t*, mtr_t*, bool*)") at assert.c:101
|
#4 0x0000559157495869 in lock_rec_insert_check_and_lock (rec=rec@entry=0x14a0d069807e "", block=0x14a0d0011020, index=index@entry=0x14a0900263e8, thr=thr@entry=0x14a09002bc60, mtr=mtr@entry=0x14a0f075cc70, inherit=inherit@entry=0x14a0f075c297) at /test/10.6_dbg/storage/innobase/lock/lock0lock.cc:4695
|
#5 0x00005591576779d0 in btr_cur_ins_lock_and_undo (flags=flags@entry=0, cursor=cursor@entry=0x14a0f075c6c0, entry=entry@entry=0x14a090022e48, thr=thr@entry=0x14a09002bc60, mtr=mtr@entry=0x14a0f075cc70, inherit=inherit@entry=0x14a0f075c297) at /test/10.6_dbg/storage/innobase/include/btr0cur.ic:61
|
#6 0x000055915767e79f in btr_cur_optimistic_insert (flags=flags@entry=0, cursor=cursor@entry=0x14a0f075c6c0, offsets=offsets@entry=0x14a0f075c688, heap=heap@entry=0x14a0f075c680, entry=entry@entry=0x14a090022e48, rec=rec@entry=0x14a0f075c698, big_rec=0x14a0f075c678, n_ext=<optimized out>, thr=0x14a09002bc60, mtr=0x14a0f075cc70) at /test/10.6_dbg/storage/innobase/btr/btr0cur.cc:3526
|
#7 0x000055915755776c in row_ins_clust_index_entry_low (flags=flags@entry=0, mode=<optimized out>, mode@entry=2, index=index@entry=0x14a0900263e8, n_uniq=n_uniq@entry=0, entry=entry@entry=0x14a090022e48, n_ext=n_ext@entry=0, thr=<optimized out>) at /test/10.6_dbg/storage/innobase/row/row0ins.cc:2790
|
#8 0x0000559157559331 in row_ins_clust_index_entry (index=index@entry=0x14a0900263e8, entry=entry@entry=0x14a090022e48, thr=thr@entry=0x14a09002bc60, n_ext=n_ext@entry=0) at /test/10.6_dbg/storage/innobase/row/row0ins.cc:3263
|
#9 0x000055915755df0a in row_ins_index_entry (thr=0x14a09002bc60, entry=0x14a090022e48, index=0x14a0900263e8) at /test/10.6_dbg/storage/innobase/row/row0ins.cc:3388
|
#10 row_ins_index_entry_step (thr=0x14a09002bc60, node=0x14a09002ba48) at /test/10.6_dbg/storage/innobase/row/row0ins.cc:3557
|
#11 row_ins (thr=0x14a09002bc60, node=0x14a09002ba48) at /test/10.6_dbg/storage/innobase/row/row0ins.cc:3703
|
#12 row_ins_step (thr=thr@entry=0x14a09002bc60) at /test/10.6_dbg/storage/innobase/row/row0ins.cc:3845
|
#13 0x00005591575828e0 in row_insert_for_mysql (mysql_rec=mysql_rec@entry=0x14a090028b58 "\375\002", prebuilt=0x14a09002b318, ins_mode=ROW_INS_NORMAL) at /test/10.6_dbg/storage/innobase/row/row0mysql.cc:1395
|
#14 0x000055915740873a in ha_innobase::write_row (this=0x14a090028fc0, record=0x14a090028b58 "\375\002") at /test/10.6_dbg/storage/innobase/handler/ha_innodb.cc:7424
|
#15 0x0000559157013f0d in handler::ha_write_row (this=0x14a090028fc0, buf=0x14a090028b58 "\375\002") at /test/10.6_dbg/sql/handler.cc:7156
|
#16 0x0000559156cedbfd in write_record (thd=thd@entry=0x14a090000db8, table=table@entry=0x14a090020048, info=info@entry=0x14a0f075dd00, sink=sink@entry=0x0) at /test/10.6_dbg/sql/sql_insert.cc:2106
|
#17 0x0000559156cf9474 in mysql_insert (thd=thd@entry=0x14a090000db8, table_list=0x14a090013c88, fields=@0x14a090005e60: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55915830f280 <end_of_list>, last = 0x14a090005e60, elements = 0}, <No data fields>}, values_list=@0x14a090005ea8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14a0900148b8, last = 0x14a0900148b8, elements = 1}, <No data fields>}, update_fields=@0x14a090005e90: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55915830f280 <end_of_list>, last = 0x14a090005e90, elements = 0}, <No data fields>}, update_values=@0x14a090005e78: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x55915830f280 <end_of_list>, last = 0x14a090005e78, elements = 0}, <No data fields>}, duplic=DUP_ERROR, ignore=false, result=0x0) at /test/10.6_dbg/sql/sql_insert.cc:1099
|
#18 0x0000559156d401ac in mysql_execute_command (thd=thd@entry=0x14a090000db8) at /test/10.6_dbg/sql/sql_parse.cc:4559
|
#19 0x0000559156d2aa06 in mysql_parse (thd=thd@entry=0x14a090000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14a0f075e410) at /test/10.6_dbg/sql/sql_parse.cc:8017
|
#20 0x0000559156d397df in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14a090000db8, packet=packet@entry=0x14a09000b369 "INSERT INTO t2 VALUES (2)", packet_length=packet_length@entry=25, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_class.h:1331
|
#21 0x0000559156d3cbd5 in do_command (thd=0x14a090000db8, blocking=blocking@entry=true) at /test/10.6_dbg/sql/sql_parse.cc:1406
|
#22 0x0000559156e9677c in do_handle_one_connection (connect=<optimized out>, connect@entry=0x5591595116c8, put_in_cache=put_in_cache@entry=true) at /test/10.6_dbg/sql/sql_connect.cc:1410
|
#23 0x0000559156e96d81 in handle_one_connection (arg=arg@entry=0x5591595116c8) at /test/10.6_dbg/sql/sql_connect.cc:1312
|
#24 0x0000559157344a03 in pfs_spawn_thread (arg=0x559159427a88) at /test/10.6_dbg/storage/perfschema/pfs.cc:2201
|
#25 0x000014a0f382b609 in start_thread (arg=<optimized out>) at pthread_create.c:477
|
#26 0x000014a0f341a293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
Bug confirmed present in:
10.6.0 (dbg)
Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.2.38 (dbg), 10.2.38 (opt), 10.3.29 (dbg), 10.3.29 (opt), 10.4.19 (dbg), 10.4.19 (opt), 10.5.10 (dbg), 10.5.10 (opt), 10.6.0 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.33 (dbg), 5.7.33 (opt), 8.0.23 (dbg), 8.0.23 (opt)
Optimized output:
|
10.6.0 a3e3225cd3b816d47621e749e21a71b6a864a96a (Optimized) |
10.6.0-opt>SET unique_checks=0,foreign_key_checks=0,autocommit=0;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
10.6.0-opt>CREATE TABLE t1 (c INT) ENGINE=InnoDB;
|
Query OK, 0 rows affected (0.019 sec)
|
|
|
10.6.0-opt>CREATE TABLE t2 (c INT) ENGINE=InnoDB;
|
Query OK, 0 rows affected (0.012 sec)
|
|
|
10.6.0-opt>LOCK TABLE t2 WRITE;
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
10.6.0-opt>INSERT INTO t2 VALUES (1);
|
Query OK, 1 row affected (0.007 sec)
|
|
|
10.6.0-opt>CREATE TABLE t1 (c INT) ENGINE=InnoDB;
|
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
|
10.6.0-opt>CREATE TEMPORARY TABLE t3 (a INT) ENGINE=InnoDB;
|
Query OK, 0 rows affected (0.002 sec)
|
|
|
10.6.0-opt>INSERT INTO t3 VALUES (1);
|
Query OK, 1 row affected (0.000 sec)
|
|
|
10.6.0-opt>INSERT INTO t2 VALUES (2);
|
Query OK, 1 row affected (0.000 sec)
|
Attachments
Issue Links
- blocks
-
-
- Closed
-
- is caused by
-
MDEV-515 innodb bulk insert
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
| Link |
This issue is caused by |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Component/s | Data Definition - Temporary [ 10123 ] | |
| Component/s | Locking [ 10900 ] | |
| Affects Version/s | 10.6.0 [ 24431 ] | |
| Affects Version/s | 10.6 [ 24028 ] |
| Link |
This issue relates to |
The root cause of this problem is that some error handling in the SQL layer is invoking COMMIT and thus releasing the locks that LOCK TABLE t2 WRITE acquired:
|
10.6 9db14e93acc4ec9023d50686c66dbef7d4d8c15c |
Thread 2 hit Hardware watchpoint 1: -location index.table.n_lock_x_or_s
|
|
|
Old value = 1
|
New value = 0
|
0x000055ddfda2efe2 in lock_table_remove_low (lock=0x7fcfcbb00cb8) at /mariadb/10.6/storage/innobase/lock/lock0lock.cc:3252
|
3252 --table->n_lock_x_or_s;
|
(rr) bt
|
#0 0x000055ddfda2efe2 in lock_table_remove_low (lock=0x7fcfcbb00cb8) at /mariadb/10.6/storage/innobase/lock/lock0lock.cc:3252
|
#1 lock_table_dequeue (in_lock=in_lock@entry=0x7fcfcbb00cb8, owns_wait_mutex=<optimized out>) at /mariadb/10.6/storage/innobase/lock/lock0lock.cc:3500
|
#2 0x000055ddfda2ddd0 in lock_release_try (trx=0x7fcfcbb00168) at /mariadb/10.6/storage/innobase/lock/lock0lock.cc:3741
|
#3 lock_release (trx=trx@entry=0x7fcfcbb00168) at /mariadb/10.6/storage/innobase/lock/lock0lock.cc:3777
|
#4 0x000055ddfdbbe0fa in trx_t::release_locks (this=this@entry=0x7fcfcbb00168) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:496
|
#5 0x000055ddfdbbe777 in trx_t::commit_in_memory (this=this@entry=0x7fcfcbb00168, mtr=mtr@entry=0x7fcfc80a3168) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1340
|
#6 0x000055ddfdbb972b in trx_t::commit_low (this=this@entry=0x7fcfcbb00168, mtr=mtr@entry=0x7fcfc80a3168) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1505
|
#7 0x000055ddfdbba1bf in trx_t::commit (this=this@entry=0x7fcfcbb00168) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1519
|
#8 0x000055ddfdbbb072 in trx_commit_for_mysql (trx=trx@entry=0x7fcfcbb00168) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:1636
|
#9 0x000055ddfd9aa418 in innobase_commit_low (trx=0x7fcfcbb00168) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:3874
|
#10 innobase_commit_ordered_2 (trx=0x7fcfcbb00168, thd=0x7fcfac001ab8) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:3980
|
#11 innobase_commit (hton=<optimized out>, thd=0x7fcfac001ab8, commit_trx=<optimized out>) at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:4084
|
#12 0x000055ddfd669e2b in commit_one_phase_2 (thd=0x7fcfac001ab8, thd@entry=0x55ddfde54500, all=false, trans=0x7fcfac0053e8, trans@entry=0x7fcfac004570, is_real_trans=false) at /mariadb/10.6/sql/handler.cc:1944
|
#13 0x000055ddfd66a39e in ha_commit_one_phase (thd=<optimized out>, thd@entry=0x7fcfac001ab8, all=<optimized out>) at /mariadb/10.6/sql/handler.cc:1923
|
#14 0x000055ddfd66934d in ha_commit_trans (thd=thd@entry=0x7fcfac001ab8, all=<optimized out>) at /mariadb/10.6/sql/handler.cc:1717
|
#15 0x000055ddfd53cca2 in trans_commit_implicit (thd=thd@entry=0x7fcfac001ab8) at /mariadb/10.6/sql/transaction.cc:329
|
#16 0x000055ddfd4060a3 in mysql_execute_command (thd=thd@entry=0x7fcfac001ab8) at /mariadb/10.6/sql/sql_parse.cc:3743
|
#17 0x000055ddfd40197e in mysql_parse (thd=thd@entry=0x7fcfac001ab8, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, parser_state@entry=0x7fcfc80a4580)
|
at /mariadb/10.6/sql/sql_parse.cc:8018
|
#18 0x000055ddfd3ff297 in dispatch_command (command=<optimized out>, command@entry=COM_QUERY, thd=thd@entry=0x7fcfac001ab8, packet=<optimized out>,
|
packet@entry=0x7fcfac00bc79 "CREATE TABLE t1 (c INT) ENGINE=InnoDB", packet_length=2885777104, packet_length@entry=37, blocking=true) at /mariadb/10.6/sql/sql_parse.cc:1897
|
Note: This CREATE TABLE t1 statement failed due to LOCK TABLES. There was no call to ha_innobase::create() for it. The only calls to ha_innobase::create() were for CREATE TABLE t2 and the CREATE TEMPORARY TABLE t3.
Later, a transaction was silently restarted. The ins_node_t probably remained in an inconsistent state, assuming that the table lock on t2 was still being held:
#1 0x000055ddfdbbc0c7 in trx_start_if_not_started_xa_low (trx=0x7fcfac001ab8, trx@entry=0x7fcfcbb00168, read_write=<optimized out>) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:2111
|
#2 0x000055ddfdb0b110 in row_insert_for_mysql (mysql_rec=<optimized out>, mysql_rec@entry=0x7fcfac94d3e8 "\375\001", prebuilt=0x7fcfac951138, ins_mode=ROW_INS_NORMAL)
|
at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1361
|
#3 0x000055ddfd998f99 in ha_innobase::write_row (this=0x7fcfac9508f0, record=0x7fcfac94d3e8 "\375\001") at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:7420
|
#4 0x000055ddfd677f3e in handler::ha_write_row (this=0x7fcfac9508f0, buf=0x7fcfac94d3e8 "\375\001") at /mariadb/10.6/sql/handler.cc:7162
|
#5 0x000055ddfd3cac12 in write_record (thd=thd@entry=0x7fcfac001ab8, table=table@entry=0x7fcfac9504e8, info=info@entry=0x7fcfc80a3da0, sink=0x0) at /mariadb/10.6/sql/sql_insert.cc:2106
|
#6 0x000055ddfd3c8365 in mysql_insert (thd=<optimized out>, thd@entry=0x7fcfac001ab8, table_list=<optimized out>, fields=<optimized out>, values_list=<optimized out>, update_fields=<optimized out>,
|
update_values=<optimized out>, duplic=DUP_ERROR, ignore=<optimized out>, result=0x0) at /mariadb/10.6/sql/sql_insert.cc:1099
|
#7 0x000055ddfd40bba7 in mysql_execute_command (thd=thd@entry=0x7fcfac001ab8) at /mariadb/10.6/sql/sql_parse.cc:4559
|
#8 0x000055ddfd40197e in mysql_parse (thd=thd@entry=0x7fcfac001ab8, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, parser_state@entry=0x7fcfc80a4580)
|
at /mariadb/10.6/sql/sql_parse.cc:8018
|
#9 0x000055ddfd3ff297 in dispatch_command (command=<optimized out>, command@entry=COM_QUERY, thd=thd@entry=0x7fcfac001ab8, packet=<optimized out>, packet@entry=0x7fcfac00bc79 "INSERT INTO t3 VALUES (1)",
|
packet_length=2885777104, packet_length@entry=25, blocking=true) at /mariadb/10.6/sql/sql_parse.cc:1897
|
The reason why InnoDB is skipping the table locking is directly related to the unnecessary call to ha_commit_trans for the refused CREATE TABLE t1 statement.
It looks like the challenge of identifying the start of an SQL statement is contributing to this:
Thread 2 hit Hardware watchpoint 8: -location node->trx_id
|
|
|
Old value = 27
|
New value = 30
|
0x000055ddfdb0b854 in row_insert_for_mysql (mysql_rec=<optimized out>, mysql_rec@entry=0x7fcfac028be8 "\375\002", prebuilt=<optimized out>, ins_mode=<optimized out>)
|
at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1388
|
1388 node->trx_id = trx->id;
|
(rr) bt
|
#0 0x000055ddfdb0b854 in row_insert_for_mysql (mysql_rec=<optimized out>, mysql_rec@entry=0x7fcfac028be8 "\375\002", prebuilt=<optimized out>, ins_mode=<optimized out>)
|
at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1388
|
#1 0x000055ddfd998f99 in ha_innobase::write_row (this=0x7fcfac02f640, record=0x7fcfac028be8 "\375\002") at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:7420
|
#2 0x000055ddfd678016 in handler::ha_write_row (this=0x7fcfac02f640, buf=<optimized out>) at /mariadb/10.6/sql/handler.cc:7162
|
#3 0x000055ddfd3cac12 in write_record (thd=thd@entry=0x7fcfac001ab8, table=table@entry=0x7fcfac02f238, info=info@entry=0x7fcfc80a3da0, sink=0x0) at /mariadb/10.6/sql/sql_insert.cc:2106
|
#4 0x000055ddfd3c8365 in mysql_insert (thd=<optimized out>, thd@entry=0x7fcfac001ab8, table_list=<optimized out>, fields=<optimized out>, values_list=<optimized out>, update_fields=<optimized out>,
|
update_values=<optimized out>, duplic=DUP_ERROR, ignore=<optimized out>, result=0x0) at /mariadb/10.6/sql/sql_insert.cc:1099
|
#5 0x000055ddfd40bba7 in mysql_execute_command (thd=thd@entry=0x7fcfac001ab8) at /mariadb/10.6/sql/sql_parse.cc:4559
|
#6 0x000055ddfd40197e in mysql_parse (thd=thd@entry=0x7fcfac001ab8, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, parser_state@entry=0x7fcfc80a4580)
|
at /mariadb/10.6/sql/sql_parse.cc:8018
|
#7 0x000055ddfd3ff297 in dispatch_command (command=<optimized out>, command@entry=COM_QUERY, thd=thd@entry=0x7fcfac001ab8, packet=<optimized out>, packet@entry=0x7fcfac00bc79 "INSERT INTO t2 VALUES (2)",
|
packet_length=2885777104, packet_length@entry=25, blocking=true) at /mariadb/10.6/sql/sql_parse.cc:1897
|
The problem here is that prebuilt->sql_stat_start had been reset on the very first INSERT and never set thereafter. (Heikki Tuuri’s abbreviation stat might better have been named stmt, for "statement", not "statistics".)
#0 row_insert_for_mysql (mysql_rec=<optimized out>, mysql_rec@entry=0x7fcfac028be8 "\375\001", prebuilt=<optimized out>, ins_mode=<optimized out>) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1385
|
#1 0x000055ddfd998f99 in ha_innobase::write_row (this=0x7fcfac02f640, record=0x7fcfac028be8 "\375\001") at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:7420
|
#2 0x000055ddfd678016 in handler::ha_write_row (this=0x7fcfac02f640, buf=<optimized out>) at /mariadb/10.6/sql/handler.cc:7162
|
#3 0x000055ddfd3cac12 in write_record (thd=thd@entry=0x7fcfac001ab8, table=table@entry=0x7fcfac02f238, info=info@entry=0x7fcfc80a3da0, sink=0x0) at /mariadb/10.6/sql/sql_insert.cc:2106
|
#4 0x000055ddfd3c8365 in mysql_insert (thd=<optimized out>, thd@entry=0x7fcfac001ab8, table_list=<optimized out>, fields=<optimized out>, values_list=<optimized out>, update_fields=<optimized out>,
|
update_values=<optimized out>, duplic=DUP_ERROR, ignore=<optimized out>, result=0x0) at /mariadb/10.6/sql/sql_insert.cc:1099
|
#5 0x000055ddfd40bba7 in mysql_execute_command (thd=thd@entry=0x7fcfac001ab8) at /mariadb/10.6/sql/sql_parse.cc:4559
|
#6 0x000055ddfd40197e in mysql_parse (thd=thd@entry=0x7fcfac001ab8, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, parser_state@entry=0x7fcfc80a4580)
|
at /mariadb/10.6/sql/sql_parse.cc:8018
|
#7 0x000055ddfd3ff297 in dispatch_command (command=<optimized out>, command@entry=COM_QUERY, thd=thd@entry=0x7fcfac001ab8, packet=<optimized out>, packet@entry=0x7fcfac00bc79 "INSERT INTO t2 VALUES (1)",
|
packet_length=2885777104, packet_length@entry=25, blocking=true) at /mariadb/10.6/sql/sql_parse.cc:1897
|
In MDEV-25496 we have the same problem: InnoDB has to jump through hoops to guess when a SQL statement is starting, because there is no explicit indication of it that would also work while LOCK TABLES is in effect.
| Link |
This issue blocks |
| Assignee | Marko Mäkelä [ marko ] | Oleksandr Byelkin [ sanja ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Nikita Malyavin [ nikitamalyavin ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
Here is a patch for an existing test case for reproducing this. The autocommit=0 is an important part of it. With explicit BEGIN and COMMIT I was not able to reproduce a failure:
diff --git a/mysql-test/suite/innodb/t/insert_into_empty.test b/mysql-test/suite/innodb/t/insert_into_empty.test
|
index d28d1738462..87da67dc0c5 100644
|
--- a/mysql-test/suite/innodb/t/insert_into_empty.test
|
+++ b/mysql-test/suite/innodb/t/insert_into_empty.test
|
@@ -167,3 +167,19 @@ SET GLOBAL innodb_limit_optimistic_insert_debug = @save_limit;
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
+
|
+--echo #
|
+--echo # MDEV-25534 Assertion lock_table_has...LOCK_IX
|
+--echo #
|
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
|
+SET autocommit=0;
|
+LOCK TABLE t1 WRITE;
|
+INSERT INTO t1 VALUES (1);
|
+COMMIT;
|
+CREATE TEMPORARY TABLE t0 (a INT PRIMARY KEY) ENGINE=InnoDB;
|
+INSERT INTO t0 VALUES (1);
|
+INSERT INTO t1 VALUES (2);
|
+COMMIT;
|
+SET autocommit=1;
|
+DROP TABLE t1;
|
+DROP TEMPORARY TABLE t0; |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
For the record, I was unable to repeat the failure with the following patch:
diff --git a/mysql-test/suite/innodb/t/insert_into_empty.test b/mysql-test/suite/innodb/t/insert_into_empty.test
|
index d28d1738462..f6761c86864 100644
|
--- a/mysql-test/suite/innodb/t/insert_into_empty.test
|
+++ b/mysql-test/suite/innodb/t/insert_into_empty.test
|
@@ -167,3 +167,19 @@ SET GLOBAL innodb_limit_optimistic_insert_debug = @save_limit;
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
+
|
+--echo #
|
+--echo # MDEV-25534 Assertion lock_table_has...LOCK_IX
|
+--echo #
|
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
|
+BEGIN;
|
+LOCK TABLE t1 WRITE;
|
+INSERT INTO t1 VALUES (1);
|
+COMMIT;
|
+BEGIN;
|
+CREATE TEMPORARY TABLE t0 (a INT PRIMARY KEY) ENGINE=InnoDB;
|
+INSERT INTO t0 VALUES (1);
|
+INSERT INTO t1 VALUES (2);
|
+COMMIT;
|
+DROP TABLE t1;
|
+DROP TEMPORARY TABLE t0;
|
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
|
index 1ac85c267ac..608fa96ae0d 100644
|
--- a/storage/innobase/handler/ha_innodb.cc
|
+++ b/storage/innobase/handler/ha_innodb.cc
|
@@ -15563,7 +15563,8 @@ ha_innobase::external_lock(
|
|
if (sql_command == SQLCOM_LOCK_TABLES
|
&& THDVAR(thd, table_locks)
|
- && thd_test_options(thd, OPTION_NOT_AUTOCOMMIT)
|
+ && thd_test_options(thd, OPTION_NOT_AUTOCOMMIT
|
+ | OPTION_BEGIN)
|
&& thd_in_lock_tables(thd)) {
|
|
dberr_t error = row_lock_table(m_prebuilt); |
There seem to be some convoluted logic around thd_in_lock_tables() and OPTION_NOT_AUTOCOMMIT at play. Either way, to fix this problem, InnoDB needs a reliable indication when a statement is starting. That indication is now missing between the two INSERT into the non-temporary table.
marko, the method you are looking for is ha_innobase::start_stmt.
The problem INSERT stmt start is not detected may be in this code, recently changed by you in
8ea923f55b7666a359ac2c54f6c10e8609d16846 MDEV-24818: Optimize multi-statement INSERT into an empty table:
case SQLCOM_INSERT: |
case SQLCOM_INSERT_SELECT: |
if (trx->is_bulk_insert()) { |
/* Allow a subsequent INSERT into an empty table |
if !unique_checks && !foreign_key_checks. */
|
break; |
}
|
/* fall through */ |
default: |
trx->end_bulk_insert(*m_prebuilt->table);
|
| Assignee | Nikita Malyavin [ nikitamalyavin ] | Marko Mäkelä [ marko ] |
| Assignee | Marko Mäkelä [ marko ] | Nikita Malyavin [ nikitamalyavin ] |
| Assignee | Nikita Malyavin [ nikitamalyavin ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
We now correctly notice the start of the last INSERT statement:
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
|
index 1ac85c267ac..75c076eeb96 100644
|
--- a/storage/innobase/handler/ha_innodb.cc
|
+++ b/storage/innobase/handler/ha_innodb.cc
|
@@ -15279,7 +15279,6 @@ ha_innobase::start_stmt(
|
/* fall through */
|
default:
|
trx->end_bulk_insert(*m_prebuilt->table);
|
- m_prebuilt->sql_stat_start = TRUE;
|
if (!trx->bulk_insert) {
|
break;
|
}
|
@@ -15287,6 +15286,8 @@ ha_innobase::start_stmt(
|
trx->last_sql_stat_start.least_undo_no = trx->undo_no;
|
}
|
|
+ m_prebuilt->sql_stat_start = TRUE;
|
+
|
if (m_prebuilt->table->is_temporary()
|
&& m_mysql_has_locked
|
&& m_prebuilt->select_lock_type == LOCK_NONE) { |
Thank you, nikitamalyavin, for checking that ha_innobase::start_stmt() should work reliably nowadays.
Nothing was done about the difference between SET autocommit=0 and BEGIN, nor the fact that a rejected CREATE TABLE would be equivalent to COMMIT.
| issue.field.resolutiondate | 2021-04-28 15:06:59.0 | 2021-04-28 15:06:59.541 |
| Fix Version/s | 10.6.1 [ 24437 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 121441 ] | MariaDB v4 [ 159210 ] |
The specified revision is 90 commits behind the mariadb-10.6.0 tagged commit. You can identify the commit as
10.6 a3e3225cd3b816d47621e749e21a71b6a864a96abut identifying it as
10.6.0 a3e3225cd3b816d47621e749e21a71b6a864a96ais misleading.
That said, the test reproduces a crash on mariadb-10.6.0 as well. Here it is in the .test file format and slightly simplified:
--source include/have_innodb.incI think that the fix could be to refuse enabling the bulk insert mode if LOCK TABLES is in effect, or in effect but not on the current table. But that could be easier said than done.
I am also not sure about the expected semantics of LOCK TABLES. Should the final INSERT INTO t2 be allowed at all?