[MDEV-25339] Assertion `thd->transaction.stmt.is_empty() || thd->in_sub_stmt' failed Created: 2020-03-29  Updated: 2021-04-05  Resolved: 2020-03-31

Status: Closed
Project: MariaDB Server
Component/s: Partitioning, Versioned Tables
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
Relates

 Description   
Extra locks required upon INSERT into table with auto-partitioning; Assertion `thd->transaction.stmt.is_empty() || thd->in_sub_stmt' failed

In the test case below INSERT works immediately, but if the AUTO clause is added to partitioning, INSERT waits for lock_wait_timeout seconds. Apparently, INSERT wants an extra lock for auto-partitioned tables. I'm not sure it was intended.

Test case without auto-partitioning

 
--source include/have_partition.inc
 
--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY system_time INTERVAL 4 DAY;
 
START TRANSACTION;
 
SELECT * FROM t1;
 
 
 
--connect (con1,localhost,root,,test)
 
INSERT INTO t1 VALUES (1);
 
 
 
# Cleanup
 
--disconnect con1
 
--connection default
 
DROP TABLE t1;

Test case with auto-partitioning

 
--source include/have_partition.inc
 
--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY system_time INTERVAL 4 DAY AUTO;
 
START TRANSACTION;
 
SELECT * FROM t1;
 
 
 
--connect (con1,localhost,root,,test)
 
INSERT INTO t1 VALUES (1);
 
 
 
# Cleanup
 
--disconnect con1
 
--connection default
 
DROP TABLE t1;

If we extend the test case a little, it causes an assertion failure. It doesn't happen without auto-partitioning.

Test case for assertion failure

 
--source include/have_partition.inc
 
--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY system_time INTERVAL 4 DAY AUTO;
 
START TRANSACTION;
 
SELECT * FROM t1;
 
 
 
--connect (con1,localhost,root,,test)
 
SET lock_wait_timeout= 1;
 
--error ER_LOCK_WAIT_TIMEOUT
 
INSERT INTO t1 VALUES (1);
 
SELECT * FROM t1;
 
 
 
# Cleanup
 
--disconnect con1
 
--connection default
 
DROP TABLE t1;

bb-10.5-midenok-MENT-654 7db57b2

 
mysqld: /data/src/bb-10.5-midenok-MENT-654/sql/sql_parse.cc:3328: int mysql_execute_command(THD*): Assertion `thd->transaction.stmt.is_empty() || thd->in_sub_stmt' failed.
 
200330  1:21:14 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fa122ab6102 in __GI___assert_fail (assertion=0x55b1d57c8cc0 "thd->transaction.stmt.is_empty() || thd->in_sub_stmt", file=0x55b1d57c6ee0 "/data/src/bb-10.5-midenok-MENT-654/sql/sql_parse.cc", line=3328, function=0x55b1d57ccf60 <mysql_execute_command(THD*)::__PRETTY_FUNCTION__> "int mysql_execute_command(THD*)") at assert.c:101
 
#8  0x000055b1d38b77ed in mysql_execute_command (thd=0x62b0000d9270) at /data/src/bb-10.5-midenok-MENT-654/sql/sql_parse.cc:3328
 
#9  0x000055b1d38d94f7 in mysql_parse (thd=0x62b0000d9270, rawbuf=0x62b0000a8290 "SELECT * FROM t1", length=16, parser_state=0x7fa114c13a60, is_com_multi=false, is_next_command=false) at /data/src/bb-10.5-midenok-MENT-654/sql/sql_parse.cc:7999
 
#10 0x000055b1d38ae414 in dispatch_command (command=COM_QUERY, thd=0x62b0000d9270, packet=0x6290002d0271 "SELECT * FROM t1", packet_length=16, is_com_multi=false, is_next_command=false) at /data/src/bb-10.5-midenok-MENT-654/sql/sql_parse.cc:1846
 
#11 0x000055b1d38aade7 in do_command (thd=0x62b0000d9270) at /data/src/bb-10.5-midenok-MENT-654/sql/sql_parse.cc:1364
 
#12 0x000055b1d3ccb034 in do_handle_one_connection (connect=0x6100000044b0, put_in_cache=true) at /data/src/bb-10.5-midenok-MENT-654/sql/sql_connect.cc:1422
 
#13 0x000055b1d3cca8ca in handle_one_connection (arg=0x6100000044b0) at /data/src/bb-10.5-midenok-MENT-654/sql/sql_connect.cc:1319
 
#14 0x000055b1d49b8de3 in pfs_spawn_thread (arg=0x6160000327f0) at /data/src/bb-10.5-midenok-MENT-654/storage/perfschema/pfs.cc:1862
 
#15 0x00007fa12333afa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
 
#16 0x00007fa122b7f4cf in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95



 Comments   
Comment by Aleksey Midenkov [ 2020-03-31 ]

My concern about INSERT (and his variations) is whether we should exclude him from rotation (and partition auto-creation). This case explains my concern:

--source include/have_partition.inc
 
set timestamp= unix_timestamp('2020-01-01 00:00:00');
 
create or replace table t1 (x int) with system versioning
 
partition by system_time interval 1 month auto_increment;
 
insert t1 values ();
 
set timestamp= unix_timestamp('2020-02-01 00:00:00');
 
insert t1 values ();
 
set timestamp= unix_timestamp('2020-03-01 00:00:00');
 
insert t1 values ();
 
set timestamp= unix_timestamp('2020-04-01 00:00:00');
 
insert t1 values ();
 
set timestamp= unix_timestamp('2020-05-01 00:00:00');
 
insert t1 values ();
 
set timestamp= unix_timestamp('2020-06-01 00:00:00');
 
insert t1 values ();
 
delete from t1;
 
show create table t1;
 
select * from t1 partition (p0);
 
--error 0,ER_UNKNOWN_PARTITION
 
select * from t1 partition (p5);
 
# cleanup
 
drop table t1;

Variant 1. If INSERT is included into rotation (and auto-creation) the result is:

...
 
delete from t1;
 
show create table t1;
 
Table   Create Table
 
t1      CREATE TABLE `t1` (
 
  `x` int(11) DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 
 PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH STARTS TIMESTAMP'2020-01-01 00:00:00' AUTO_INCREMENT
 
PARTITIONS 8
 
select * from t1 partition (p0);
 
x
 
select * from t1 partition (p5);
 
x
 
NULL
 
NULL
 
NULL
 
NULL
 
NULL
 
NULL
 
drop table t1;

Variant 2. If INSERT is excluded from rotation (and auto-creation) the result is:

...
 
delete from t1;
 
Warnings:
 
Warning 4114    Versioned table `test`.`t1`: last HISTORY partition (`p0`) is out of INTERVAL, need more HISTORY partitions
 
show create table t1;
 
Table   Create Table
 
t1      CREATE TABLE `t1` (
 
  `x` int(11) DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 
 PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH STARTS TIMESTAMP'2020-01-01 00:00:00' AUTO_INCREMENT
 
PARTITIONS 3
 
select * from t1 partition (p0);
 
x
 
NULL
 
NULL
 
NULL
 
NULL
 
NULL
 
NULL
 
select * from t1 partition (p5);
 
drop table t1;

Variant 1. is more user friendly but it creates empty partitions even when not needed. INSERT is vulnerable to DDL locking, though this is not frequent case and may be neglected.

Variant 2. does not create partitions on INSERT which seems to be logically correct but it requires user actions after long time without DELETE/UPDATE. INSERT is invulnerable to DDL locking issues. Many INSERTs long time without DELETE/UPDATE seems to be untypical case.

I'd prefer user-friendliness and choose "Variant 1".

Comment by Aleksey Midenkov [ 2020-03-31 ]

Fixed in `bb-10.5-midenok-MENT-654`.

Generated at Thu Feb 08 09:36:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.