[MDEV-27428] Server crashes in Query_cache_query::lock_writing upon invalidation - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
Fix Version/s: 10.5, 10.6, 10.11
Component/s: Query Cache
Labels:
None

Description

The reason why the test case is so ugly is that the failure apparently depends strongly on the length of the queries, column names, etc. So, I removed non-essential parts of queries – not all of them, but most – and replaced them with comment fillers of the corresponding length.
It is reproducible deterministically for me, on different versions and builds, but further changing the sizes of the queries more than by a few symbols makes it go away. It is difficult to do it blindly, I hope when the problem is known and exact correlation between the sizes and the failure is clear, a better test case can be constructed instead.

--source include/have_partition.inc

--source include/have_innodb.inc

SET @qcache_size= @@global.query_cache_size, @qcache_type= @@global.query_cache_type;

SET GLOBAL query_cache_size=128*1024, query_cache_type=1;

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

CREATE TABLE t1 (

  id INT,

  colname_10s INT,

  colname_12sm DATE,

  colname_14smbl DATE,

  colname_13smb TIME,

  colname_15symbl TIME,

  colname_16symbol DATETIME,

  colname_18_symbols DATETIME,

  colname_15smbls VARCHAR(1),

  colname_17symbols VARCHAR(1)

) ENGINE=InnoDB;

CREATE TABLE t2 (

  id INT,

  colname_10s INT,

  colname_15symbl TIME,

  colname_16symbol DATETIME,

  colname_15smbls VARCHAR(1),

  colname_17symbols VARCHAR(1)

) ENGINE=InnoDB PARTITION BY KEY(id) PARTITIONS 9;

CREATE TABLE t3 (

  id INT,

  colname_10s INT,

  colname_15smbls VARCHAR(1)

) ENGINE=InnoDB;

SELECT * FROM t1 /* QUERY 01 */;

SELECT * FROM t1 /* QUERY 02 */;

SELECT * FROM t1 /* QUERY 03 */;

SELECT * FROM t1 /* QUERY 04 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

SELECT * FROM t1 /* QUERY 05 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

SELECT * FROM t1 /* QUERY 06 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

SELECT * FROM t1 AS table1 /* QUERY 07 */;

SELECT * FROM t1 AS table1 /* QUERY 08 */;

SELECT * FROM ( SELECT * FROM t1 ) AS derived_table /* QUERY 09 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2 /* QUERY 10 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

( SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2, t1 ) UNION ( SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2, t1 ) /* QUERY 11 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

( SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2, t1 ) UNION ( SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2, t1 ) /* QUERY 12 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

( SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2, t1 ) UNION ( SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2, t1 ) /* QUERY 13 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

( SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2, t1 ) UNION ( SELECT ( SELECT COUNT(*) FROM t2 JOIN t3 ) FROM t2, t1 ) /* QUERY 14 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

( SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2, t1 ) INTERSECT ( SELECT ( SELECT COUNT(*) FROM t2 JOIN t1 ) FROM t2 ) /* QUERY 15 filler: 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789 */;

( SELECT ( SELECT COUNT(*) FROM t2 JOIN t3 ) FROM t2 ) INTERSECT ( SELECT ( SELECT COUNT(*) FROM t2 JOIN t3 ) FROM t2 ) /* QUERY 16 */;

SELECT ( SELECT COUNT(*) FROM t2 ) FROM t2 /* QUERY 17 */;

SELECT ( SELECT COUNT(*) FROM t2 ) FROM t2 /* QUERY 18 */;

--error ER_BAD_FIELD_ERROR

SELECT x FROM t2 AS t2_1 JOIN t2 AS t2_2 WHERE EXISTS ( SELECT 1 FROM t2 WHERE EXISTS ( SELECT 1 FROM t2 AS t2_3 JOIN t2 AS t2_4 ) );

FLUSH QUERY CACHE;

INSERT INTO t3 (id) VALUES (NULL);

# Cleanup

DROP TABLE t1, t2, t3;

--connection default

SET GLOBAL query_cache_size= @qcache_size, query_cache_type= @qcache_type;

10.4 f5dceafd
#3 <signal handler called>
#4 0x000055706fcee2c6 in start_rwlock_wait_v1 (state=0x7efee9039500, rwlock=0x2, op=PSI_RWLOCK_WRITELOCK, src_file=0x557070ce5e20 "/data/src/10.4/sql/sql_cache.cc", src_line=921) at /data/src/10.4/storage/perfschema/pfs.cc:2318
#5 0x000055706eb5c1c3 in inline_mysql_rwlock_wrlock (that=0x6340000c5c98, src_file=0x557070ce5e20 "/data/src/10.4/sql/sql_cache.cc", src_line=921) at /data/src/10.4/include/mysql/psi/mysql_thread.h:956
#6 0x000055706eb5d161 in Query_cache_query::lock_writing (this=0x6340000c5c90) at /data/src/10.4/sql/sql_cache.cc:921
#7 0x000055706eb4c363 in Query_cache::invalidate_query_block_list (this=0x5570725ca960 <query_cache>, thd=0x62b0000d9208, list_root=0x6340000d00c8) at /data/src/10.4/sql/sql_cache.cc:3364
#8 0x000055706eb4c2fd in Query_cache::invalidate_table_internal (this=0x5570725ca960 <query_cache>, thd=0x62b0000d9208, key=0x61b00008bf30 "test", key_length=8) at /data/src/10.4/sql/sql_cache.cc:3341
#9 0x000055706eb4c27d in Query_cache::invalidate_table (this=0x5570725ca960 <query_cache>, thd=0x62b0000d9208, key=0x61b00008bf30 "test", key_length=8) at /data/src/10.4/sql/sql_cache.cc:3319
#10 0x000055706eb4c148 in Query_cache::invalidate_table (this=0x5570725ca960 <query_cache>, thd=0x62b0000d9208, table=0x620000055088) at /data/src/10.4/sql/sql_cache.cc:3302
#11 0x000055706eb4c00a in Query_cache::invalidate_table (this=0x5570725ca960 <query_cache>, thd=0x62b0000d9208, table_list=0x62b0000a8340) at /data/src/10.4/sql/sql_cache.cc:3288
#12 0x000055706eb457ea in Query_cache::invalidate (this=0x5570725ca960 <query_cache>, thd=0x62b0000d9208, tables_used=0x62b0000a8340, using_transactions=0 '\000') at /data/src/10.4/sql/sql_cache.cc:2251
#13 0x000055706ebdab7a in mysql_insert (thd=0x62b0000d9208, table_list=0x62b0000a8340, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /data/src/10.4/sql/sql_insert.cc:1152
#14 0x000055706ec95505 in mysql_execute_command (thd=0x62b0000d9208) at /data/src/10.4/sql/sql_parse.cc:4613
#15 0x000055706ecad463 in mysql_parse (thd=0x62b0000d9208, rawbuf=0x62b0000a8228 "INSERT INTO t3 (id) VALUES (NULL)", length=33, parser_state=0x7efee903b860, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:8008
#16 0x000055706ec837a6 in dispatch_command (command=COM_QUERY, thd=0x62b0000d9208, packet=0x6290002cb209 "INSERT INTO t3 (id) VALUES (NULL)", packet_length=33, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1857
#17 0x000055706ec80315 in do_command (thd=0x62b0000d9208) at /data/src/10.4/sql/sql_parse.cc:1378
#18 0x000055706f07f0ba in do_handle_one_connection (connect=0x608000000ca8) at /data/src/10.4/sql/sql_connect.cc:1420
#19 0x000055706f07e9d1 in handle_one_connection (arg=0x608000000ca8) at /data/src/10.4/sql/sql_connect.cc:1324
#20 0x000055706fcebaee in pfs_spawn_thread (arg=0x615000006988) at /data/src/10.4/storage/perfschema/pfs.cc:1869
#21 0x00007efefdea7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
#22 0x00007efefdf285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Attachments

Activity

People

Assignee:: Oleksandr Byelkin

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2022-01-05 22:06

Updated:: 2024-09-10 15:34

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.