Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.1, 10.0
-
Fix Version/s: 10.0.21
-
Component/s: Data Manipulation - Update, Optimizer, Prepared Statements
-
Labels:None
-
Sprint:10.0.20
Description
CREATE TABLE t1 (f1 INT); INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (f2 INT); INSERT INTO t2 VALUES (3),(4); CREATE TABLE t3 (f3 INT); INSERT INTO t3 VALUES (5),(6); PREPARE stmt FROM ' UPDATE t1, t2 SET f1 = 5 WHERE 8 IN ( SELECT MIN(f3) FROM t3 ) '; EXECUTE stmt; EXECUTE stmt;
Stack trace from 10.0 commit 50d98e9c
10.0/sql/sql_acl.cc:10717: static const ACL_internal_schema_access* ACL_internal_schema_registry::lookup(const char*): Assertion `name != __null' failed. 150409 20:14:21 [ERROR] mysqld got signal 6 ; #6 0x00007f5990d3b311 in *__GI___assert_fail (assertion=0xef24c4 "name != __null", file=<optimized out>, line=10717, function=0xef3aa0 "static const ACL_internal_schema_access* ACL_internal_schema_registry::lookup(const char*)") at assert.c:81 #7 0x000000000060fe2a in ACL_internal_schema_registry::lookup (name=0x0) at 10.0/sql/sql_acl.cc:10717 #8 0x000000000060fee0 in get_cached_schema_access (grant_internal_info=0x7f59891f4358, schema_name=0x0) at 10.0/sql/sql_acl.cc:10744 #9 0x000000000060ff63 in get_cached_table_access (grant_internal_info=0x7f59891f4358, schema_name=0x0, table_name=0x0) at 10.0/sql/sql_acl.cc:10767 #10 0x0000000000605179 in check_grant (thd=0x7f598bb2f070, want_access=4, tables=0x7f59891f4088, any_combination_will_do=false, number=0, no_errors=true) at 10.0/sql/sql_acl.cc:6680 #11 0x00000000006898e2 in multi_update_precheck (thd=0x7f598bb2f070, tables=0x7f59893475c0) at 10.0/sql/sql_parse.cc:7746 #12 0x000000000067e437 in mysql_execute_command (thd=0x7f598bb2f070) at 10.0/sql/sql_parse.cc:3313 #13 0x000000000069f51e in Prepared_statement::execute (this=0x7f5989291470, expanded_query=0x7f5992f43ae0, open_cursor=false) at 10.0/sql/sql_prepare.cc:3971 #14 0x000000000069e5f9 in Prepared_statement::execute_loop (this=0x7f5989291470, expanded_query=0x7f5992f43ae0, open_cursor=false, packet=0x0, packet_end=0x0) at 10.0/sql/sql_prepare.cc:3626 #15 0x000000000069c8fb in mysql_sql_stmt_execute (thd=0x7f598bb2f070) at 10.0/sql/sql_prepare.cc:2777 #16 0x000000000067c68c in mysql_execute_command (thd=0x7f598bb2f070) at 10.0/sql/sql_parse.cc:2560 #17 0x0000000000686e85 in mysql_parse (thd=0x7f598bb2f070, rawbuf=0x7f59890e0088 "EXECUTE stmt", length=12, parser_state=0x7f5992f44600) at 10.0/sql/sql_parse.cc:6517 #18 0x00000000006798b1 in dispatch_command (command=COM_QUERY, thd=0x7f598bb2f070, packet=0x7f598afeb071 "EXECUTE stmt", packet_length=12) at 10.0/sql/sql_parse.cc:1300 #19 0x0000000000678c47 in do_command (thd=0x7f598bb2f070) at 10.0/sql/sql_parse.cc:996 #20 0x0000000000796457 in do_handle_one_connection (thd_arg=0x7f598bb2f070) at 10.0/sql/sql_connect.cc:1375 #21 0x00000000007961aa in handle_one_connection (arg=0x7f598bb2f070) at 10.0/sql/sql_connect.cc:1289 #22 0x0000000000cd391e in pfs_spawn_thread (arg=0x7f598af6ce70) at 10.0/storage/perfschema/pfs.cc:1860 #23 0x00007f5992b35b50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #24 0x00007f5990deb95d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
The problem appeared in 10.0 tree with this commit:
commit 775528ada3c2cc3a2a7ebafadebbcf44530aab50 0f8b194146b50afe442682c5a14a8a179e28f3b8
Author: Sergei Petrunia <psergey@askmonty.org>
Date: Fri Feb 20 03:17:46 2015 +0300
MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT
Enable subquery materialization for non-SELECT queries with a SELECT part
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- In Progress
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
If I make multi_update_precheck to ignore tables that are non-merged semi-joins (tbl->is_jtbm() == true), then I get similar crash in unsafe_key_update, and in multiple locations in mysql_multi_update_prepare.
This is odd, SELECT handling code did not have much problem with non-merged semi-join nest being present as a base table. setup_tables has special code handling for them, but that's all what was needed for prepare phase to work.
Followed through and made all of Multi-table update code to skip non-merged semi-join nests: http://lists.askmonty.org/pipermail/commits/2015-July/008194.html
Elena Stepanova, I need a test pass for this fix.
The following test case appears to be another representation of MDEV-7971 – it also passes before MDEV-7220 and fails with the same assertion after; only it does not get fixed by the patch above, instead it causes the crash as below.
--source include/have_innodb.inc CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) ENGINE=InnoDB; CREATE TABLE t2 (b INT) ENGINE=InnoDB; CREATE TABLE t3 (c INT) ENGINE=Aria; CREATE OR REPLACE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; # Data is not necesssary, it fails with empty tables too INSERT INTO t1 VALUES (1,1),(2,2); INSERT INTO t2 VALUES (3),(4); INSERT INTO t3 VALUES (5),(6); PREPARE stmt FROM 'UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT COUNT(*) FROM v3 )'; EXECUTE stmt; EXECUTE stmt;
#3 <signal handler called> #4 0x0000000000747d30 in unsafe_key_update (leaves=..., tables_for_update=1) at sql/sql_update.cc:1186 #5 0x00000000007485d4 in mysql_multi_update_prepare (thd=0x7f8a2b81a070) at sql/sql_update.cc:1401 #6 0x000000000067fb39 in mysql_execute_command (thd=0x7f8a2b81a070) at sql/sql_parse.cc:3331 #7 0x00000000006a0cd6 in Prepared_statement::execute (this=0x7f8a241e4870, expanded_query=0x7f8a390eeae0, open_cursor=false) at sql/sql_prepare.cc:3971 #8 0x000000000069fdb1 in Prepared_statement::execute_loop (this=0x7f8a241e4870, expanded_query=0x7f8a390eeae0, open_cursor=false, packet=0x0, packet_end=0x0) at sql/sql_prepare.cc:3626 #9 0x000000000069e0b3 in mysql_sql_stmt_execute (thd=0x7f8a2b81a070) at sql/sql_prepare.cc:2777 #10 0x000000000067dd62 in mysql_execute_command (thd=0x7f8a2b81a070) at sql/sql_parse.cc:2572 #11 0x000000000068855b in mysql_parse (thd=0x7f8a2b81a070, rawbuf=0x7f8a240dd088 "EXECUTE stmt", length=12, parser_state=0x7f8a390ef600) at sql/sql_parse.cc:6529 #12 0x000000000067af47 in dispatch_command (command=COM_QUERY, thd=0x7f8a2b81a070, packet=0x7f8a2b66d071 "EXECUTE stmt", packet_length=12) at sql/sql_parse.cc:1308 #13 0x000000000067a22d in do_command (thd=0x7f8a2b81a070) at sql/sql_parse.cc:999 #14 0x00000000007982d6 in do_handle_one_connection (thd_arg=0x7f8a2b81a070) at sql/sql_connect.cc:1378 #15 0x0000000000798035 in handle_one_connection (arg=0x7f8a2b81a070) at sql/sql_connect.cc:1293 #16 0x0000000000cd81c3 in pfs_spawn_thread (arg=0x7f8a30ff4670) at storage/perfschema/pfs.cc:1860 #17 0x00007f8a38d2ab50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #18 0x00007f8a36fe095d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112 #19 0x0000000000000000 in ?? ()
Elena Stepanova, good catch, thanks. My patch missed one case where multi-table UPDATE could encounter a merged semi-join.
Here is the updated patch: http://lists.askmonty.org/pipermail/commits/2015-July/008209.html
Elena Stepanova, the patch for MDEV-8554 is here: http://lists.askmonty.org/pipermail/commits/2015-July/008211.html
Can you do another test run with the patch?
My tests passed on the second patch; but they were only run for crashes, if you think the patch could have caused problems with wrong results, please let me know.
I don't expect wrong query results from any of these fixes.
Sanja has also said he doesn't see any issues in the patch.
I'm going to push it, then.
Thanks
Analysis:
The crash happens inside permission check in multi_update_precheck() call.
We crash when attempting to verify permissions for "<subquery2>" table.
Looking at table->next_local chain in multi_update_precheck():
PREPARE: t1->t2
EXECUTE: t1->t2
EXECUTE: t1->t2->"<subquery2>".
"<subquery2>" is injected into next_local chain by convert_subq_to_jtbm().