revision-id: 2be83a28a2cdecad28d28f04a0dfeff2eb5ff957 (mariadb-5.5.61-32-g2be83a2) parent(s): e31e697f17f79ffa6913499e7e2d29866f24b475 author: Dyre Tjeldvoll committer: Sergey Vojtovich timestamp: 2018-10-17 19:59:44 +0400 message: BUG#19988193: ASSERTION `(*TABLES)->REGINFO.LOCK_TYPE >= TL_READ' FAILED IN LOCK_EXTERNAL BUG#21198646: ASSERTION FAILED: (*TABLES)->REGINFO.LOCK_TYPE >= TL_READ FILE LOCK.CC, LINE 356 This patch addresses two related issues: Calling a procedure which creates a view from a trigger (BUG#19988193), and creating a function calling a procedure doing RENAME TABLE (BUG#21198646), could both, in certain circumstances, trigger an assert. Root cause was that prelocking of tables with lock_type==TL_IGNORE is not supported, and so triggers an assert. TL_IGNORE is only used for source tables in CREATE VIEW statements and the table of a RENAME TABLE statement. It is very unusual for these statements to be part of prelocking analysis, as both are implicit commit statements which are not permitted in triggers and stored functions/procedures. But as the test cases show; it is possible to have such statements contribute to the prelocking set, but in both cases the statement is "meaningless", in the sense that it will trigger an error during execution. Fix: In mysql_make_view(), avoid adding the backing tables to view_ref if view_ref->prelocking_placeholder==true and lock_type==TL_IGNORE. In sp_head::add_used_tables_to_table_list() skip SP_TABLES which have lock_type=TL_IGNORE. Test: New test cases added to tablelock.test --- mysql-test/r/tablelock.result | 51 +++++++++++++++++++++++++++++++++++ mysql-test/t/tablelock.test | 62 +++++++++++++++++++++++++++++++++++++++++++ sql/sp_head.cc | 2 +- sql/sql_view.cc | 9 ++++++- 4 files changed, 122 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/tablelock.result b/mysql-test/r/tablelock.result index 6923ad4..379e4e9 100644 --- a/mysql-test/r/tablelock.result +++ b/mysql-test/r/tablelock.result @@ -55,3 +55,54 @@ f1 int(11) YES NULL insert into t1 values(2); drop table t1; unlock tables; +# +# Bug#19988193 ASSERTION `(*TABLES)->REGINFO.LOCK_TYPE >= TL_READ' +# FAILED IN LOCK_EXTERNAL +# +CREATE TABLE t1(a INT); +CREATE PROCEDURE p1() CREATE VIEW v1 AS SELECT * FROM t1; + +# Create trigger calling proc creating view, when view DOES NOT +# exist already +CREATE TRIGGER trg_p1_t1 AFTER INSERT ON t1 FOR EACH ROW CALL p1(); + +# Verify that it is possible to lock table +LOCK TABLES t1 WRITE; +UNLOCK TABLES; + +# Fails, as expected +INSERT INTO t1 VALUES (1); +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. + +# Make sure v1 already exists +CREATE VIEW v1 AS SELECT a+1 FROM t1; + +# Verify that it is possible to lock table +LOCK TABLES t1 WRITE; +UNLOCK TABLES; + +# Verify that we get the expected error when inserting into the table +INSERT INTO t1 VALUES (1); +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. + +# Cleanup +DROP TRIGGER trg_p1_t1; +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; +# +# Bug#21198646 ASSERTION FAILED: (*TABLES)->REGINFO.LOCK_TYPE >= TL_READ +# FILE LOCK.CC, LINE 356 +# +CREATE TABLE t2(a INT); +# Create procedure p1 invoking RENAME TABLE +CREATE PROCEDURE p1() RENAME TABLE t2 TO t3; +# Create function f1 calling p1 +CREATE FUNCTION f1() RETURNS INT BEGIN CALL p1(); RETURN 1; END $ +# Invoke function f1 and verify that we get the expected error +SELECT f1(); +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +# Cleanup +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP TABLE t2; diff --git a/mysql-test/t/tablelock.test b/mysql-test/t/tablelock.test index 5ac93f0..0152a01 100644 --- a/mysql-test/t/tablelock.test +++ b/mysql-test/t/tablelock.test @@ -62,3 +62,65 @@ drop table t1; unlock tables; # End of 5.0 tests + +--echo # +--echo # Bug#19988193 ASSERTION `(*TABLES)->REGINFO.LOCK_TYPE >= TL_READ' +--echo # FAILED IN LOCK_EXTERNAL +--echo # + +CREATE TABLE t1(a INT); +CREATE PROCEDURE p1() CREATE VIEW v1 AS SELECT * FROM t1; +--echo +--echo # Create trigger calling proc creating view, when view DOES NOT +--echo # exist already +CREATE TRIGGER trg_p1_t1 AFTER INSERT ON t1 FOR EACH ROW CALL p1(); +--echo +--echo # Verify that it is possible to lock table +LOCK TABLES t1 WRITE; +UNLOCK TABLES; +--echo +--echo # Fails, as expected +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +INSERT INTO t1 VALUES (1); +--echo +--echo # Make sure v1 already exists +CREATE VIEW v1 AS SELECT a+1 FROM t1; +--echo +--echo # Verify that it is possible to lock table +LOCK TABLES t1 WRITE; +UNLOCK TABLES; +--echo +--echo # Verify that we get the expected error when inserting into the table +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +INSERT INTO t1 VALUES (1); +--echo +--echo # Cleanup +DROP TRIGGER trg_p1_t1; +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # +--echo # Bug#21198646 ASSERTION FAILED: (*TABLES)->REGINFO.LOCK_TYPE >= TL_READ +--echo # FILE LOCK.CC, LINE 356 +--echo # + +CREATE TABLE t2(a INT); + +--echo # Create procedure p1 invoking RENAME TABLE +CREATE PROCEDURE p1() RENAME TABLE t2 TO t3; + +--echo # Create function f1 calling p1 +DELIMITER $; +CREATE FUNCTION f1() RETURNS INT BEGIN CALL p1(); RETURN 1; END $ +DELIMITER ;$ + +--echo # Invoke function f1 and verify that we get the expected error +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +SELECT f1(); + +--echo # Cleanup +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP TABLE t2; \ No newline at end of file diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 14a5791..eec59d8 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -4230,7 +4230,7 @@ sp_head::add_used_tables_to_table_list(THD *thd, char *tab_buff, *key_buff; TABLE_LIST *table; SP_TABLE *stab= (SP_TABLE*) my_hash_element(&m_sptabs, i); - if (stab->temp) + if (stab->temp || stab->lock_type == TL_IGNORE) continue; if (!(tab_buff= (char *)thd->calloc(ALIGN_SIZE(sizeof(TABLE_LIST)) * diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 5bd82fd..39d77ca 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -1464,8 +1464,15 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, NOTE: It is important for UPDATE/INSERT/DELETE checks to have this tables just after VIEW instead of tail of list, to be able check that table is unique. Also we store old next table for the same purpose. + + If prelocking a view which has lock_type==TL_IGNORE we cannot add + the tables, as that would result in tables with + lock_type==TL_IGNORE being added to the prelocking set. That, in + turn, would lead to lock_external() being called on those tables, + which is not permitted (causes assert). */ - if (view_tables) + if (view_tables && !(table->prelocking_placeholder && + table->lock_type == TL_IGNORE)) { if (table->next_global) {