From 81a8d8be76b1cc22d8567ed66cf8c6b93591112c Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 3 Apr 2019 10:57:45 +0200 Subject: MDEV-18923 Assertion `!lex_string_cmp(system_charset_info, fk_info->referenced_table, &table->s->table_name)' failed in fk_truncate_illegal_if_parent don't assert the correctness of FK constraints, as it can be broken under `SET FOREIGN_KEY_CHECKS= OFF` --- mysql-test/suite/innodb/r/innodb-truncate.result | 13 +++++++++++++ mysql-test/suite/innodb/t/innodb-truncate.test | 16 ++++++++++++++++ sql/sql_truncate.cc | 14 +++++--------- 3 files changed, 34 insertions(+), 9 deletions(-) diff --git a/mysql-test/suite/innodb/r/innodb-truncate.result b/mysql-test/suite/innodb/r/innodb-truncate.result index f63e9272850..e4b571cbc2f 100644 --- a/mysql-test/suite/innodb/r/innodb-truncate.result +++ b/mysql-test/suite/innodb/r/innodb-truncate.result @@ -66,3 +66,16 @@ a 1 2 DROP TABLE t1; +call mtr.add_suppression('InnoDB: Error: in RENAME TABLE table `test`.`t3`'); +SET FOREIGN_KEY_CHECKS= OFF; +CREATE TABLE t1 (f2 INT, f4 INT, KEY(f2), FOREIGN KEY (f4) REFERENCES t3 (f4)) ENGINE=InnoDB; +SET FOREIGN_KEY_CHECKS= ON; +CREATE TABLE t2 (f2 INT, FOREIGN KEY(f2) REFERENCES t1 (f2)) ENGINE=InnoDB; +CREATE TABLE t3 (a INT) ENGINE=InnoDB; +ERROR HY000: Can't create table 'test.t3' (errno: 150) +ALTER TABLE t1 RENAME TO t3; +ERROR HY000: Error on rename of './test/t1' to './test/t3' (errno: 150) +ALTER TABLE t1 FORCE; +TRUNCATE TABLE t1; +ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `test`.`t3` (`f2`)) +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb/t/innodb-truncate.test b/mysql-test/suite/innodb/t/innodb-truncate.test index ae25aabd323..d7262c30543 100644 --- a/mysql-test/suite/innodb/t/innodb-truncate.test +++ b/mysql-test/suite/innodb/t/innodb-truncate.test @@ -62,3 +62,19 @@ INSERT INTO t1 VALUES (NULL), (NULL); SELECT * FROM t1 ORDER BY a; DROP TABLE t1; +# +# MDEV-18923 Assertion `!lex_string_cmp(system_charset_info, fk_info->referenced_table, &table->s->table_name)' failed in fk_truncate_illegal_if_parent +# +call mtr.add_suppression('InnoDB: Error: in RENAME TABLE table `test`.`t3`'); +SET FOREIGN_KEY_CHECKS= OFF; +CREATE TABLE t1 (f2 INT, f4 INT, KEY(f2), FOREIGN KEY (f4) REFERENCES t3 (f4)) ENGINE=InnoDB; +SET FOREIGN_KEY_CHECKS= ON; +CREATE TABLE t2 (f2 INT, FOREIGN KEY(f2) REFERENCES t1 (f2)) ENGINE=InnoDB; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t3 (a INT) ENGINE=InnoDB; +--error ER_ERROR_ON_RENAME +ALTER TABLE t1 RENAME TO t3; +ALTER TABLE t1 FORCE; +--error ER_TRUNCATE_ILLEGAL_FK +TRUNCATE TABLE t1; +DROP TABLE t2, t1; diff --git a/sql/sql_truncate.cc b/sql/sql_truncate.cc index ed36ab428a2..b225d5f04df 100644 --- a/sql/sql_truncate.cc +++ b/sql/sql_truncate.cc @@ -150,15 +150,11 @@ fk_truncate_illegal_if_parent(THD *thd, TABLE *table) /* Loop over the set of foreign keys for which this table is a parent. */ while ((fk_info= it++)) { - DBUG_ASSERT(!my_strcasecmp(system_charset_info, - fk_info->referenced_db->str, - table->s->db.str)); - - DBUG_ASSERT(!my_strcasecmp(system_charset_info, - fk_info->referenced_table->str, - table->s->table_name.str)); - - if (my_strcasecmp(system_charset_info, fk_info->foreign_db->str, + if (my_strcasecmp(system_charset_info, fk_info->referenced_db->str, + table->s->db.str) || + my_strcasecmp(system_charset_info, fk_info->referenced_table->str, + table->s->table_name.str) || + my_strcasecmp(system_charset_info, fk_info->foreign_db->str, table->s->db.str) || my_strcasecmp(system_charset_info, fk_info->foreign_table->str, table->s->table_name.str)) -- cgit v1.2.1 From 66099b8f2d10cedf4ee75d12c0188f3a9b383e6c Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 3 Apr 2019 17:21:10 +0200 Subject: cleanup --- mysql-test/r/multi_update2.result | 25 --------- mysql-test/r/multi_update_big.result | 25 +++++++++ mysql-test/t/multi_update2-master.opt | 1 - mysql-test/t/multi_update2.test | 78 ---------------------------- mysql-test/t/multi_update_big.opt | 1 + mysql-test/t/multi_update_big.test | 78 ++++++++++++++++++++++++++++ sql/sql_acl.cc | 71 +++++++++++++------------- sql/sql_base.cc | 95 +++++++++++++++++++---------------- sql/sql_base.h | 16 +++++- sql/sql_yacc.yy | 29 ++++++----- 10 files changed, 223 insertions(+), 196 deletions(-) delete mode 100644 mysql-test/r/multi_update2.result create mode 100644 mysql-test/r/multi_update_big.result delete mode 100644 mysql-test/t/multi_update2-master.opt delete mode 100644 mysql-test/t/multi_update2.test create mode 100644 mysql-test/t/multi_update_big.opt create mode 100644 mysql-test/t/multi_update_big.test diff --git a/mysql-test/r/multi_update2.result b/mysql-test/r/multi_update2.result deleted file mode 100644 index 3712e638f40..00000000000 --- a/mysql-test/r/multi_update2.result +++ /dev/null @@ -1,25 +0,0 @@ -DROP TABLE IF EXISTS t1,t2; -CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL) ; -# The protocolling of many inserts into t1 is suppressed. -ALTER TABLE t1 ADD INDEX i1(a); -DELETE FROM t1 WHERE a > 2000000; -CREATE TABLE t2 LIKE t1; -INSERT INTO t2 SELECT * FROM t1; -SELECT 't2 rows before small delete', COUNT(*) FROM t1; -t2 rows before small delete COUNT(*) -t2 rows before small delete 2000000 -DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 2; -SELECT 't2 rows after small delete', COUNT(*) FROM t2; -t2 rows after small delete COUNT(*) -t2 rows after small delete 1999999 -SELECT 't1 rows after small delete', COUNT(*) FROM t1; -t1 rows after small delete COUNT(*) -t1 rows after small delete 1999999 -DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 100*1000; -SELECT 't2 rows after big delete', COUNT(*) FROM t2; -t2 rows after big delete COUNT(*) -t2 rows after big delete 1900001 -SELECT 't1 rows after big delete', COUNT(*) FROM t1; -t1 rows after big delete COUNT(*) -t1 rows after big delete 1900001 -DROP TABLE t1,t2; diff --git a/mysql-test/r/multi_update_big.result b/mysql-test/r/multi_update_big.result new file mode 100644 index 00000000000..3712e638f40 --- /dev/null +++ b/mysql-test/r/multi_update_big.result @@ -0,0 +1,25 @@ +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL) ; +# The protocolling of many inserts into t1 is suppressed. +ALTER TABLE t1 ADD INDEX i1(a); +DELETE FROM t1 WHERE a > 2000000; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1; +SELECT 't2 rows before small delete', COUNT(*) FROM t1; +t2 rows before small delete COUNT(*) +t2 rows before small delete 2000000 +DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 2; +SELECT 't2 rows after small delete', COUNT(*) FROM t2; +t2 rows after small delete COUNT(*) +t2 rows after small delete 1999999 +SELECT 't1 rows after small delete', COUNT(*) FROM t1; +t1 rows after small delete COUNT(*) +t1 rows after small delete 1999999 +DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 100*1000; +SELECT 't2 rows after big delete', COUNT(*) FROM t2; +t2 rows after big delete COUNT(*) +t2 rows after big delete 1900001 +SELECT 't1 rows after big delete', COUNT(*) FROM t1; +t1 rows after big delete COUNT(*) +t1 rows after big delete 1900001 +DROP TABLE t1,t2; diff --git a/mysql-test/t/multi_update2-master.opt b/mysql-test/t/multi_update2-master.opt deleted file mode 100644 index da78f987af3..00000000000 --- a/mysql-test/t/multi_update2-master.opt +++ /dev/null @@ -1 +0,0 @@ ---tmp_table_size=1024 diff --git a/mysql-test/t/multi_update2.test b/mysql-test/t/multi_update2.test deleted file mode 100644 index a0f17fabec4..00000000000 --- a/mysql-test/t/multi_update2.test +++ /dev/null @@ -1,78 +0,0 @@ ---source include/long_test.inc - -# -# Test of update statement that uses many tables. -# - -# -# If we are running with -# - Valgrind -> $VALGRIND_TEST <> 0 -# - debug tracing -> @@session.debug LIKE '%trace%' -# the resource consumption (storage space needed, runtime) will be extreme. -# Therefore we require that the option "--big-test" is also set. -# - -let $need_big= 0; ---disable_query_log ---error 0,ER_UNKNOWN_SYSTEM_VARIABLE -SET @aux = @@session.debug; -if (!$mysql_errno) -{ - # We have returncode 0 = the server system variable @@session.debug exists. - # But we only need "--big-test" in case of tracing. - if (`SELECT @@session.debug LIKE '%trace%'`) - { - let $need_big= 1; - } -} ---enable_query_log -if ($VALGRIND_TEST) -{ - # We are running with Valgrind - inc $need_big; -} -if (`SELECT '$BIG_TEST' = '' AND $need_big = 1`) -{ - --skip Need "--big-test" when running with the option "--debug" or "--valgrind" -} - -# -# Bug#1820 Rows not deleted from second table on multi-table delete -# - ---disable_warnings -DROP TABLE IF EXISTS t1,t2; ---enable_warnings - -CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL) ; ---echo # The protocolling of many inserts into t1 is suppressed. ---disable_query_log -INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); -let $1=19; -set @d=4; -begin; -while ($1) -{ - eval INSERT INTO t1 SELECT a+@d,b+@d FROM t1; - eval SET @d=@d*2; - dec $1; -} -commit; ---enable_query_log -ALTER TABLE t1 ADD INDEX i1(a); -DELETE FROM t1 WHERE a > 2000000; -CREATE TABLE t2 LIKE t1; -INSERT INTO t2 SELECT * FROM t1; - -SELECT 't2 rows before small delete', COUNT(*) FROM t1; -DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 2; -SELECT 't2 rows after small delete', COUNT(*) FROM t2; -SELECT 't1 rows after small delete', COUNT(*) FROM t1; - -## Try deleting many rows - -DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 100*1000; -SELECT 't2 rows after big delete', COUNT(*) FROM t2; -SELECT 't1 rows after big delete', COUNT(*) FROM t1; - -DROP TABLE t1,t2; diff --git a/mysql-test/t/multi_update_big.opt b/mysql-test/t/multi_update_big.opt new file mode 100644 index 00000000000..da78f987af3 --- /dev/null +++ b/mysql-test/t/multi_update_big.opt @@ -0,0 +1 @@ +--tmp_table_size=1024 diff --git a/mysql-test/t/multi_update_big.test b/mysql-test/t/multi_update_big.test new file mode 100644 index 00000000000..a0f17fabec4 --- /dev/null +++ b/mysql-test/t/multi_update_big.test @@ -0,0 +1,78 @@ +--source include/long_test.inc + +# +# Test of update statement that uses many tables. +# + +# +# If we are running with +# - Valgrind -> $VALGRIND_TEST <> 0 +# - debug tracing -> @@session.debug LIKE '%trace%' +# the resource consumption (storage space needed, runtime) will be extreme. +# Therefore we require that the option "--big-test" is also set. +# + +let $need_big= 0; +--disable_query_log +--error 0,ER_UNKNOWN_SYSTEM_VARIABLE +SET @aux = @@session.debug; +if (!$mysql_errno) +{ + # We have returncode 0 = the server system variable @@session.debug exists. + # But we only need "--big-test" in case of tracing. + if (`SELECT @@session.debug LIKE '%trace%'`) + { + let $need_big= 1; + } +} +--enable_query_log +if ($VALGRIND_TEST) +{ + # We are running with Valgrind + inc $need_big; +} +if (`SELECT '$BIG_TEST' = '' AND $need_big = 1`) +{ + --skip Need "--big-test" when running with the option "--debug" or "--valgrind" +} + +# +# Bug#1820 Rows not deleted from second table on multi-table delete +# + +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +--enable_warnings + +CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL) ; +--echo # The protocolling of many inserts into t1 is suppressed. +--disable_query_log +INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); +let $1=19; +set @d=4; +begin; +while ($1) +{ + eval INSERT INTO t1 SELECT a+@d,b+@d FROM t1; + eval SET @d=@d*2; + dec $1; +} +commit; +--enable_query_log +ALTER TABLE t1 ADD INDEX i1(a); +DELETE FROM t1 WHERE a > 2000000; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1; + +SELECT 't2 rows before small delete', COUNT(*) FROM t1; +DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 2; +SELECT 't2 rows after small delete', COUNT(*) FROM t2; +SELECT 't1 rows after small delete', COUNT(*) FROM t1; + +## Try deleting many rows + +DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 100*1000; +SELECT 't2 rows after big delete', COUNT(*) FROM t2; +SELECT 't1 rows after big delete', COUNT(*) FROM t1; + +DROP TABLE t1,t2; diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 066e7e4c7f4..19ecd870307 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -1150,6 +1150,16 @@ void acl_free(bool end) } +static void fix_table_list(TABLE_LIST *tl, uint n) +{ + TABLE_LIST *end; + for (end= tl + n - 1; tl < end; tl++) + { + tl->next_local= tl->next_global= tl + 1; + } +} + + /* Forget current user/db-level privileges and read new privileges from the privilege tables. @@ -1191,11 +1201,7 @@ my_bool acl_reload(THD *thd) tables[3].init_one_table(C_STRING_WITH_LEN("mysql"), C_STRING_WITH_LEN("proxies_priv"), "proxies_priv", TL_READ); - tables[0].next_local= tables[0].next_global= tables + 1; - tables[1].next_local= tables[1].next_global= tables + 2; - tables[2].next_local= tables[2].next_global= tables + 3; - tables[0].open_type= tables[1].open_type= tables[2].open_type= - tables[3].open_type= OT_BASE_ONLY; + fix_table_list(tables, 4); tables[3].open_strategy= TABLE_LIST::OPEN_IF_EXISTS; if (open_and_lock_tables(thd, tables, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT)) @@ -1921,6 +1927,7 @@ bool change_password(THD *thd, const char *host, const char *user, DBUG_RETURN(1); tables.init_one_table("mysql", 5, "user", 4, "user", TL_WRITE); + fix_table_list(&tables, 1); #ifdef HAVE_REPLICATION /* @@ -2282,6 +2289,7 @@ static bool test_if_create_new_users(THD *thd) ulong db_access; tl.init_one_table(C_STRING_WITH_LEN("mysql"), C_STRING_WITH_LEN("user"), "user", TL_WRITE); + fix_table_list(&tl, 1); create_new_users= 1; db_access=acl_get(sctx->host, sctx->ip, @@ -3689,10 +3697,11 @@ int mysql_table_grant(THD *thd, TABLE_LIST *table_list, tables[2].init_one_table(C_STRING_WITH_LEN("mysql"), C_STRING_WITH_LEN("columns_priv"), "columns_priv", TL_WRITE); - tables[0].next_local= tables[0].next_global= tables+1; /* Don't open column table if we don't need it ! */ if (column_priv || (revoke_grant && ((rights & COL_ACLS) || columns.elements))) - tables[1].next_local= tables[1].next_global= tables+2; + fix_table_list(tables, 3); + else + fix_table_list(tables, 2); /* This statement will be replicated as a statement, even when using @@ -3930,7 +3939,7 @@ bool mysql_routine_grant(THD *thd, TABLE_LIST *table_list, bool is_proc, C_STRING_WITH_LEN("user"), "user", TL_WRITE); tables[1].init_one_table(C_STRING_WITH_LEN("mysql"), C_STRING_WITH_LEN("procs_priv"), "procs_priv", TL_WRITE); - tables[0].next_local= tables[0].next_global= tables+1; + fix_table_list(tables, 2); /* This statement will be replicated as a statement, even when using @@ -4104,7 +4113,7 @@ bool mysql_grant(THD *thd, const char *db, List &list, C_STRING_WITH_LEN("db"), "db", TL_WRITE); - tables[0].next_local= tables[0].next_global= tables+1; + fix_table_list(tables, 2); /* This statement will be replicated as a statement, even when using @@ -4525,10 +4534,7 @@ my_bool grant_reload(THD *thd) tables[2].init_one_table(C_STRING_WITH_LEN("mysql"), C_STRING_WITH_LEN("procs_priv"), "procs_priv", TL_READ); - - tables[0].next_local= tables[0].next_global= tables+1; - tables[1].next_local= tables[1].next_global= tables+2; - tables[0].open_type= tables[1].open_type= tables[2].open_type= OT_BASE_ONLY; + fix_table_list(tables, 3); /* Reload will work in the following manner:- @@ -5864,29 +5870,24 @@ int open_grant_tables(THD *thd, TABLE_LIST *tables) DBUG_RETURN(-1); } - tables->init_one_table(C_STRING_WITH_LEN("mysql"), - C_STRING_WITH_LEN("user"), "user", TL_WRITE); - (tables+1)->init_one_table(C_STRING_WITH_LEN("mysql"), - C_STRING_WITH_LEN("db"), "db", TL_WRITE); - (tables+2)->init_one_table(C_STRING_WITH_LEN("mysql"), - C_STRING_WITH_LEN("tables_priv"), - "tables_priv", TL_WRITE); - (tables+3)->init_one_table(C_STRING_WITH_LEN("mysql"), - C_STRING_WITH_LEN("columns_priv"), - "columns_priv", TL_WRITE); - (tables+4)->init_one_table(C_STRING_WITH_LEN("mysql"), - C_STRING_WITH_LEN("procs_priv"), - "procs_priv", TL_WRITE); - (tables+5)->init_one_table(C_STRING_WITH_LEN("mysql"), - C_STRING_WITH_LEN("proxies_priv"), - "proxies_priv", TL_WRITE); + tables[0].init_one_table(C_STRING_WITH_LEN("mysql"), + C_STRING_WITH_LEN("user"), "user", TL_WRITE); + tables[1].init_one_table(C_STRING_WITH_LEN("mysql"), + C_STRING_WITH_LEN("db"), "db", TL_WRITE); + tables[2].init_one_table(C_STRING_WITH_LEN("mysql"), + C_STRING_WITH_LEN("tables_priv"), + "tables_priv", TL_WRITE); + tables[3].init_one_table(C_STRING_WITH_LEN("mysql"), + C_STRING_WITH_LEN("columns_priv"), + "columns_priv", TL_WRITE); + tables[4].init_one_table(C_STRING_WITH_LEN("mysql"), + C_STRING_WITH_LEN("procs_priv"), + "procs_priv", TL_WRITE); + tables[5].init_one_table(C_STRING_WITH_LEN("mysql"), + C_STRING_WITH_LEN("proxies_priv"), + "proxies_priv", TL_WRITE); tables[5].open_strategy= TABLE_LIST::OPEN_IF_EXISTS; - - tables->next_local= tables->next_global= tables + 1; - (tables+1)->next_local= (tables+1)->next_global= tables + 2; - (tables+2)->next_local= (tables+2)->next_global= tables + 3; - (tables+3)->next_local= (tables+3)->next_global= tables + 4; - (tables+4)->next_local= (tables+4)->next_global= tables + 5; + fix_table_list(tables, 6); #ifdef HAVE_REPLICATION /* diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 272aa11977d..9a2da7a8658 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4468,6 +4468,45 @@ open_and_process_routine(THD *thd, Query_tables_list *prelocking_ctx, DBUG_RETURN(FALSE); } +/* + If we are not already in prelocked mode and extended table list is not + yet built we might have to build the prelocking set for this statement. + + Since currently no prelocking strategy prescribes doing anything for + tables which are only read, we do below checks only if table is going + to be changed. +*/ +bool extend_table_list(THD *thd, TABLE_LIST *tables, + Prelocking_strategy *prelocking_strategy, + bool has_prelocking_list) +{ + bool error= false; + LEX *lex= thd->lex; + + if (thd->locked_tables_mode <= LTM_LOCK_TABLES && + ! has_prelocking_list && + tables->lock_type >= TL_WRITE_ALLOW_WRITE) + { + bool need_prelocking= FALSE; + TABLE_LIST **save_query_tables_last= lex->query_tables_last; + /* + Extend statement's table list and the prelocking set with + tables and routines according to the current prelocking + strategy. + + For example, for DML statements we need to add tables and routines + used by triggers which are going to be invoked for this element of + table list and also add tables required for handling of foreign keys. + */ + error= prelocking_strategy->handle_table(thd, lex, tables, + &need_prelocking); + + if (need_prelocking && ! lex->requires_prelocking()) + lex->mark_as_requiring_prelocking(save_query_tables_last); + } + return error; +} + /** Handle table list element by obtaining metadata lock, opening table or view @@ -4496,15 +4535,14 @@ open_and_process_routine(THD *thd, Query_tables_list *prelocking_ctx, */ static bool -open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables, - uint *counter, uint flags, +open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, Prelocking_strategy *prelocking_strategy, - bool has_prelocking_list, - Open_table_context *ot_ctx, + bool has_prelocking_list, Open_table_context *ot_ctx, MEM_ROOT *new_frm_mem) { bool error= FALSE; bool safe_to_ignore_table= FALSE; + LEX *lex= thd->lex; DBUG_ENTER("open_and_process_table"); DEBUG_SYNC(thd, "open_and_process_table"); @@ -4654,38 +4692,9 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables, if (tables->open_strategy && !tables->table) goto end; - /* - If we are not already in prelocked mode and extended table list is not - yet built we might have to build the prelocking set for this statement. - - Since currently no prelocking strategy prescribes doing anything for - tables which are only read, we do below checks only if table is going - to be changed. - */ - if (thd->locked_tables_mode <= LTM_LOCK_TABLES && - ! has_prelocking_list && - tables->lock_type >= TL_WRITE_ALLOW_WRITE) - { - bool need_prelocking= FALSE; - TABLE_LIST **save_query_tables_last= lex->query_tables_last; - /* - Extend statement's table list and the prelocking set with - tables and routines according to the current prelocking - strategy. - - For example, for DML statements we need to add tables and routines - used by triggers which are going to be invoked for this element of - table list and also add tables required for handling of foreign keys. - */ - error= prelocking_strategy->handle_table(thd, lex, tables, - &need_prelocking); - - if (need_prelocking && ! lex->requires_prelocking()) - lex->mark_as_requiring_prelocking(save_query_tables_last); - - if (error) - goto end; - } + error= extend_table_list(thd, tables, prelocking_strategy, has_prelocking_list); + if (error) + goto end; if (tables->lock_type != TL_UNLOCK && ! thd->locked_tables_mode) { @@ -4994,8 +5003,9 @@ open_tables_check_upgradable_mdl(THD *thd, TABLE_LIST *tables_start, @retval TRUE Error, reported. */ -bool open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags, - Prelocking_strategy *prelocking_strategy) +bool open_tables(THD *thd, TABLE_LIST **start, uint *counter, + Sroutine_hash_entry **sroutine_to_open_list, uint flags, + Prelocking_strategy *prelocking_strategy) { /* We use pointers to "next_global" member in the last processed TABLE_LIST @@ -5043,7 +5053,7 @@ restart: has_prelocking_list= thd->lex->requires_prelocking(); table_to_open= start; - sroutine_to_open= (Sroutine_hash_entry**) &thd->lex->sroutines_list.first; + sroutine_to_open= sroutine_to_open_list; *counter= 0; thd_proc_info(thd, "Opening tables"); @@ -5112,10 +5122,9 @@ restart: for (tables= *table_to_open; tables; table_to_open= &tables->next_global, tables= tables->next_global) { - error= open_and_process_table(thd, thd->lex, tables, counter, - flags, prelocking_strategy, - has_prelocking_list, &ot_ctx, - &new_frm_mem); + error= open_and_process_table(thd, tables, counter, flags, + prelocking_strategy, has_prelocking_list, + &ot_ctx, &new_frm_mem); if (error) { diff --git a/sql/sql_base.h b/sql/sql_base.h index 646e391a58b..4d9ccc460e9 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -276,7 +276,8 @@ int init_ftfuncs(THD *thd, SELECT_LEX* select, bool no_order); bool lock_table_names(THD *thd, TABLE_LIST *table_list, TABLE_LIST *table_list_end, ulong lock_wait_timeout, uint flags); -bool open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags, +bool open_tables(THD *thd, TABLE_LIST **tables, uint *counter, + Sroutine_hash_entry **sroutine_to_open, uint flags, Prelocking_strategy *prelocking_strategy); /* open_and_lock_tables with optional derived handling */ bool open_and_lock_tables(THD *thd, TABLE_LIST *tables, @@ -498,6 +499,15 @@ private: }; +inline bool +open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags, + Prelocking_strategy *prelocking_strategy) +{ + return open_tables(thd, tables, counter, &thd->lex->sroutines_list.first, + flags, prelocking_strategy); +} + + inline bool open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags) { @@ -528,6 +538,10 @@ inline bool open_and_lock_tables(THD *thd, TABLE_LIST *tables, } +bool extend_table_list(THD *thd, TABLE_LIST *tables, + Prelocking_strategy *prelocking_strategy, + bool has_prelocking_list); + /** A context of open_tables() function, used to recover from a failed open_table() or open_routine() attempt. diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index ba0041cf477..760aed5c5e3 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11159,14 +11159,14 @@ update: opt_low_priority opt_ignore join_table_list SET update_list { - LEX *lex= Lex; - if (lex->select_lex.table_list.elements > 1) - lex->sql_command= SQLCOM_UPDATE_MULTI; - else if (lex->select_lex.get_table_list()->derived) + SELECT_LEX *slex= &Lex->select_lex; + if (slex->table_list.elements > 1) + Lex->sql_command= SQLCOM_UPDATE_MULTI; + else if (slex->get_table_list()->derived) { /* it is single table update and it is update of derived table */ my_error(ER_NON_UPDATABLE_TABLE, MYF(0), - lex->select_lex.get_table_list()->alias, "UPDATE"); + slex->get_table_list()->alias, "UPDATE"); MYSQL_YYABORT; } /* @@ -11174,7 +11174,7 @@ update: be too pessimistic. We will decrease lock level if possible in mysql_multi_update(). */ - Select->set_lock_for_tables($3); + slex->set_lock_for_tables($3); } where_clause opt_order_clause delete_limit_clause {} ; @@ -13834,13 +13834,16 @@ table_lock: table_ident opt_table_alias lock_option { thr_lock_type lock_type= (thr_lock_type) $3; - bool lock_for_write= (lock_type >= TL_WRITE_ALLOW_WRITE); - if (!Select->add_table_to_list(thd, $1, $2, 0, lock_type, - (lock_for_write ? - lock_type == TL_WRITE_CONCURRENT_INSERT ? - MDL_SHARED_WRITE : - MDL_SHARED_NO_READ_WRITE : - MDL_SHARED_READ))) + bool lock_for_write= lock_type >= TL_WRITE_ALLOW_WRITE; + ulong table_options= 0; + enum_mdl_type mdl_type= !lock_for_write + ? MDL_SHARED_READ + : lock_type == TL_WRITE_CONCURRENT_INSERT + ? MDL_SHARED_WRITE + : MDL_SHARED_NO_READ_WRITE; + + if (!Select->add_table_to_list(thd, $1, $2, table_options, + lock_type, mdl_type)) MYSQL_YYABORT; } ; -- cgit v1.2.1 From 822071ca5b6d109e5497f1c15efa44fe47d277c5 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 5 Apr 2019 13:02:51 +0200 Subject: MDEV-18241 Downgrade from 10.4 to 10.3 crashes privilege tables can never be views or temporary tables, don't even try to open them, if they are. --- mysql-test/r/grant4.result | 11 +++++++++++ mysql-test/t/grant4.test | 15 +++++++++++++++ sql/sql_acl.cc | 4 ++++ 3 files changed, 30 insertions(+) diff --git a/mysql-test/r/grant4.result b/mysql-test/r/grant4.result index d0b6627cd0b..9b6d4c42c59 100644 --- a/mysql-test/r/grant4.result +++ b/mysql-test/r/grant4.result @@ -121,3 +121,14 @@ View Create View character_set_client collation_connection v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t_select_priv`.`a` AS `a`,`t_select_priv`.`b` AS `b` from `t_select_priv` latin1 latin1_swedish_ci drop database mysqltest_db1; drop user mysqltest_u1@localhost; +call mtr.add_suppression("Table 'mysql.user' doesn't exist"); +rename table mysql.user to mysql.user1; +create view mysql.user as select * from mysql.user1; +flush privileges; +ERROR 42S02: Table 'mysql.user' doesn't exist +drop view mysql.user; +create temporary table mysql.user select * from mysql.user1 limit 0; +flush privileges; +ERROR 42S02: Table 'mysql.user' doesn't exist +drop temporary table mysql.user; +rename table mysql.user1 to mysql.user; diff --git a/mysql-test/t/grant4.test b/mysql-test/t/grant4.test index f3e551cd623..2762182b978 100644 --- a/mysql-test/t/grant4.test +++ b/mysql-test/t/grant4.test @@ -144,3 +144,18 @@ connection default; disconnect con1; drop database mysqltest_db1; drop user mysqltest_u1@localhost; + +# +# MDEV-18241 Downgrade from 10.4 to 10.3 crashes +# +call mtr.add_suppression("Table 'mysql.user' doesn't exist"); +rename table mysql.user to mysql.user1; +create view mysql.user as select * from mysql.user1; +--error ER_NO_SUCH_TABLE +flush privileges; +drop view mysql.user; +create temporary table mysql.user select * from mysql.user1 limit 0; +--error ER_NO_SUCH_TABLE +flush privileges; +drop temporary table mysql.user; +rename table mysql.user1 to mysql.user; diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 19ecd870307..5d7f82ff5dd 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -1155,8 +1155,12 @@ static void fix_table_list(TABLE_LIST *tl, uint n) TABLE_LIST *end; for (end= tl + n - 1; tl < end; tl++) { + tl->i_s_requested_object= OPEN_TABLE_ONLY; + tl->open_type= OT_BASE_ONLY; tl->next_local= tl->next_global= tl + 1; } + tl->i_s_requested_object= OPEN_TABLE_ONLY; + tl->open_type= OT_BASE_ONLY; } -- cgit v1.2.1 From 5057d4637525eadad438d25ee6a4870a4e6b384c Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 4 Apr 2019 22:41:58 +0200 Subject: bugfix: multi-update checked privileges on views incorrectly it always required UPDATE privilege on views, not being able to detect when a views was not actually updated in multi-update. fix: instead of marking all tables as "updating" by default, only set "updating" on tables that will actually be updated by multi-update. And mark the view "updating" if any of the view's tables is. --- mysql-test/r/view_grant.result | 4 ++++ mysql-test/t/view_grant.test | 5 +++++ sql/sql_lex.h | 4 ++-- sql/sql_parse.cc | 6 +----- sql/sql_update.cc | 4 +++- sql/sql_yacc.yy | 10 +++++----- 6 files changed, 20 insertions(+), 13 deletions(-) diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index ac09c19a4b4..b2d3a0b8ca4 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -173,11 +173,14 @@ create table mysqltest.t1 (a int, b int, primary key(a)); insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create table mysqltest.t2 (x int); insert into mysqltest.t2 values (3), (4), (5), (6); +create table mysqltest.t3 (x int); +insert into mysqltest.t3 values (3), (4), (5), (6); create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; grant update (a) on mysqltest.v2 to mysqltest_1@localhost; grant update on mysqltest.v1 to mysqltest_1@localhost; +grant update on mysqltest.t3 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; use mysqltest; update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c; @@ -212,6 +215,7 @@ a b 48 4 62 5 71 10 +update t3,v3 set t3.x=t3.x+v3.c where t3.x=v3.c; update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for column 'c' in table 'v2' update v2 set c=a+c; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 847153d19f4..ee7374e06f4 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -240,12 +240,15 @@ create table mysqltest.t1 (a int, b int, primary key(a)); insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10); create table mysqltest.t2 (x int); insert into mysqltest.t2 values (3), (4), (5), (6); +create table mysqltest.t3 (x int); +insert into mysqltest.t3 values (3), (4), (5), (6); create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; grant update (a) on mysqltest.v2 to mysqltest_1@localhost; grant update on mysqltest.v1 to mysqltest_1@localhost; +grant update on mysqltest.t3 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; connection user1; @@ -260,6 +263,8 @@ update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c; select * from t1; update v2 set a=a+c; select * from t1; +# update a table, select only on view +update t3,v3 set t3.x=t3.x+v3.c where t3.x=v3.c; # no rights on column --error ER_COLUMNACCESS_DENIED_ERROR update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 5b589714e1a..c20994d1ff9 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -589,7 +589,7 @@ public: enum_mdl_type mdl_type= MDL_SHARED_READ, List *hints= 0, LEX_STRING *option= 0); - virtual void set_lock_for_tables(thr_lock_type lock_type) {} + virtual void set_lock_for_tables(thr_lock_type lock_type, bool for_update) {} friend class st_select_lex_unit; friend bool mysql_new_select(LEX *lex, bool move_down); @@ -960,7 +960,7 @@ public: TABLE_LIST *convert_right_join(); List* get_item_list(); ulong get_table_join_options(); - void set_lock_for_tables(thr_lock_type lock_type); + void set_lock_for_tables(thr_lock_type lock_type, bool for_update); inline void init_order() { order_list.elements= 0; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index bb53c116b0c..13f5f985d01 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2065,9 +2065,6 @@ mysql_execute_command(THD *thd) reset_one_shot_variables(thd); DBUG_RETURN(0); } - - for (table=all_tables; table; table=table->next_global) - table->updating= TRUE; } /* @@ -6541,9 +6538,8 @@ TABLE_LIST *st_select_lex::convert_right_join() query */ -void st_select_lex::set_lock_for_tables(thr_lock_type lock_type) +void st_select_lex::set_lock_for_tables(thr_lock_type lock_type, bool for_update) { - bool for_update= lock_type >= TL_READ_NO_INSERT; DBUG_ENTER("set_lock_for_tables"); DBUG_PRINT("enter", ("lock_type: %d for_update: %d", lock_type, for_update)); diff --git a/sql/sql_update.cc b/sql/sql_update.cc index fe007d5823d..b23c295a1af 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1305,6 +1305,9 @@ int mysql_multi_update_prepare(THD *thd) If table will be updated we should not downgrade lock for it and leave it as is. */ + tl->updating= 1; + if (tl->belong_to_view) + tl->belong_to_view->updating= 1; } else { @@ -1323,7 +1326,6 @@ int mysql_multi_update_prepare(THD *thd) tl->lock_type= read_lock_type_for_table(thd, lex, tl); else tl->set_lock_type(thd, read_lock_type_for_table(thd, lex, tl)); - tl->updating= 0; } } for (tl= table_list; tl; tl= tl->next_local) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 760aed5c5e3..9fd4cbcc26f 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -7676,14 +7676,14 @@ select_lock_type: | FOR_SYM UPDATE_SYM { LEX *lex=Lex; - lex->current_select->set_lock_for_tables(TL_WRITE); + lex->current_select->set_lock_for_tables(TL_WRITE, false); lex->safe_to_cache_query=0; } | LOCK_SYM IN_SYM SHARE_SYM MODE_SYM { LEX *lex=Lex; lex->current_select-> - set_lock_for_tables(TL_READ_WITH_SHARED_LOCKS); + set_lock_for_tables(TL_READ_WITH_SHARED_LOCKS, false); lex->safe_to_cache_query=0; } ; @@ -10966,7 +10966,7 @@ insert: insert_lock_option opt_ignore insert2 { - Select->set_lock_for_tables($3); + Select->set_lock_for_tables($3, true); Lex->current_select= &Lex->select_lex; } insert_field_spec opt_insert_update @@ -10983,7 +10983,7 @@ replace: } replace_lock_option insert2 { - Select->set_lock_for_tables($3); + Select->set_lock_for_tables($3, true); Lex->current_select= &Lex->select_lex; } insert_field_spec @@ -11174,7 +11174,7 @@ update: be too pessimistic. We will decrease lock level if possible in mysql_multi_update(). */ - slex->set_lock_for_tables($3); + slex->set_lock_for_tables($3, slex->table_list.elements == 1); } where_clause opt_order_clause delete_limit_clause {} ; -- cgit v1.2.1 From 5d510fdbf006afa82c8acc9ea2e0c6cbeaebe0fa Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 5 Apr 2019 12:54:09 +0200 Subject: MDEV-18507 can't update temporary table when joined with table with triggers on read-only triggers are opened and tables used in triggers are prelocked in open_tables(). But multi-update can detect what tables will actually be updated only later, after all main tables are opened. Meaning, if a table is used in multi-update, but is not actually updated, its on-update treggers will be opened and tables will be prelocked, even if it's unnecessary. This can cause more tables to be write-locked than needed, causing read_only errors, privilege errors and lock waits. Fix: don't open/prelock triggers unless table->updating is true. In multi-update after setting table->updating=true, do a second open_tables() for newly added tables, if any. --- mysql-test/r/multi_update.result | 21 +++++++++++++++++++++ mysql-test/t/multi_update.test | 31 +++++++++++++++++++++++++++++++ sql/sp_head.cc | 1 + sql/sql_base.cc | 2 +- sql/sql_update.cc | 31 ++++++++++++++++++++++++++----- sql/sql_view.cc | 1 + sql/sql_yacc.yy | 2 +- 7 files changed, 82 insertions(+), 7 deletions(-) diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 09df98c741e..e04637a3a54 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -1017,4 +1017,25 @@ execute stmt1; deallocate prepare stmt1; drop view v3,v2,v1; drop table t1,t2,t3; +create table t1 (id int not null, v1 varchar(10) not null); +insert into t1 values (1,1),(2,2); +create table t2 (log varchar(10) not null); +create trigger t1_after_update after update on t1 +for each row insert into t2 values ('triggered'); +create user foo; +grant select, insert, update, delete, create, drop, reload, index, alter, show databases, create temporary tables, lock tables, execute, create view, show view, create routine, alter routine, trigger on *.* to 'foo'@'%'; +set global read_only=1; +create temporary table temp_t1 (id int not null, update_me varchar(10)); +insert into temp_t1 values (1,1),(2,2),(3,3); +update temp_t1 left join t1 on temp_t1.id = t1.id set temp_t1.update_me = 'hello'; +set global read_only = 0; +create table t3 (id int not null); +insert t3 values (2); +update t1 left join t3 on t1.id = t3.id set t1.v1 = 'hello'; +select * from t2; +log +triggered +triggered +drop table t1,t2, t3; +drop user foo; end of 5.5 tests diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 8184d8ded1a..964108b9b99 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -1055,5 +1055,36 @@ deallocate prepare stmt1; drop view v3,v2,v1; drop table t1,t2,t3; + +# +# MDEV-18507 can't update temporary table when joined with table with triggers on read-only +# +create table t1 (id int not null, v1 varchar(10) not null); +insert into t1 values (1,1),(2,2); +create table t2 (log varchar(10) not null); +create trigger t1_after_update after update on t1 + for each row insert into t2 values ('triggered'); + +create user foo; +grant select, insert, update, delete, create, drop, reload, index, alter, show databases, create temporary tables, lock tables, execute, create view, show view, create routine, alter routine, trigger on *.* to 'foo'@'%'; + +set global read_only=1; +connect a, localhost, foo; + +create temporary table temp_t1 (id int not null, update_me varchar(10)); +insert into temp_t1 values (1,1),(2,2),(3,3); +update temp_t1 left join t1 on temp_t1.id = t1.id set temp_t1.update_me = 'hello'; + +connection default; +set global read_only = 0; + +create table t3 (id int not null); +insert t3 values (2); +update t1 left join t3 on t1.id = t3.id set t1.v1 = 'hello'; +select * from t2; + +drop table t1,t2, t3; +drop user foo; + --echo end of 5.5 tests diff --git a/sql/sp_head.cc b/sql/sp_head.cc index fec7f51eaf0..e820cfd9d45 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -4250,6 +4250,7 @@ sp_head::add_used_tables_to_table_list(THD *thd, table->table_name_length= stab->table_name_length; table->alias= table->table_name + table->table_name_length + 1; table->lock_type= stab->lock_type; + table->updating= stab->lock_type >= TL_WRITE_ALLOW_WRITE; table->cacheable_table= 1; table->prelocking_placeholder= 1; table->belong_to_view= belong_to_view; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 9a2da7a8658..c774334e695 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4484,7 +4484,7 @@ bool extend_table_list(THD *thd, TABLE_LIST *tables, LEX *lex= thd->lex; if (thd->locked_tables_mode <= LTM_LOCK_TABLES && - ! has_prelocking_list && + ! has_prelocking_list && tables->updating && tables->lock_type >= TL_WRITE_ALLOW_WRITE) { bool need_prelocking= FALSE; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index b23c295a1af..c6da864c0a1 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1208,6 +1208,9 @@ int mysql_multi_update_prepare(THD *thd) List *fields= &lex->select_lex.item_list; table_map tables_for_update; bool update_view= 0; + DML_prelocking_strategy prelocking_strategy; + bool has_prelocking_list= thd->lex->requires_prelocking(); + /* if this multi-update was converted from usual update, here is table counter else junk will be assigned here, but then replaced with real @@ -1228,10 +1231,10 @@ int mysql_multi_update_prepare(THD *thd) keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE and global read lock. */ - if ((original_multiupdate && - open_tables(thd, &table_list, &table_count, - (thd->stmt_arena->is_stmt_prepare() ? - MYSQL_OPEN_FORCE_SHARED_MDL : 0))) || + if ((original_multiupdate && open_tables(thd, &table_list, &table_count, + thd->stmt_arena->is_stmt_prepare() + ? MYSQL_OPEN_FORCE_SHARED_MDL : 0, + &prelocking_strategy)) || mysql_handle_derived(lex, DT_INIT)) DBUG_RETURN(TRUE); /* @@ -1278,6 +1281,9 @@ int mysql_multi_update_prepare(THD *thd) if (unsafe_key_update(lex->select_lex.leaf_tables, tables_for_update)) DBUG_RETURN(true); + TABLE_LIST **new_tables= lex->query_tables_last; + DBUG_ASSERT(*new_tables== NULL); + /* Setup timestamp handling and locking mode */ @@ -1308,6 +1314,8 @@ int mysql_multi_update_prepare(THD *thd) tl->updating= 1; if (tl->belong_to_view) tl->belong_to_view->updating= 1; + if (extend_table_list(thd, tl, &prelocking_strategy, has_prelocking_list)) + DBUG_RETURN(TRUE); } else { @@ -1328,6 +1336,19 @@ int mysql_multi_update_prepare(THD *thd) tl->set_lock_type(thd, read_lock_type_for_table(thd, lex, tl)); } } + + uint addon_table_count= 0; + if (*new_tables) + { + Sroutine_hash_entry **new_routines= thd->lex->sroutines_list.next; + DBUG_ASSERT(*new_routines == NULL); + if (open_tables(thd, new_tables, &addon_table_count, new_routines, + thd->stmt_arena->is_stmt_prepare() + ? MYSQL_OPEN_FORCE_SHARED_MDL : 0, + &prelocking_strategy)) + DBUG_RETURN(TRUE); + } + for (tl= table_list; tl; tl= tl->next_local) { /* Check access privileges for table */ @@ -1360,7 +1381,7 @@ int mysql_multi_update_prepare(THD *thd) /* now lock and fill tables */ if (!thd->stmt_arena->is_stmt_prepare() && - lock_tables(thd, table_list, table_count, 0)) + lock_tables(thd, table_list, table_count + addon_table_count, 0)) { DBUG_RETURN(TRUE); } diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 5bd82fdd842..8f37c14c2d3 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -1511,6 +1511,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, tbl->lock_type= table->lock_type; tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ? MDL_SHARED_WRITE : MDL_SHARED_READ); + tbl->updating= table->updating; } /* If the view is mergeable, we might want to diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 9fd4cbcc26f..0da8c87ff93 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13835,7 +13835,7 @@ table_lock: { thr_lock_type lock_type= (thr_lock_type) $3; bool lock_for_write= lock_type >= TL_WRITE_ALLOW_WRITE; - ulong table_options= 0; + ulong table_options= lock_for_write ? TL_OPTION_UPDATING : 0; enum_mdl_type mdl_type= !lock_for_write ? MDL_SHARED_READ : lock_type == TL_WRITE_CONCURRENT_INSERT -- cgit v1.2.1 From e52a4ab693002ccfe9eb65e409f8b3457de450b9 Mon Sep 17 00:00:00 2001 From: Robert Bindar Date: Mon, 1 Apr 2019 11:54:29 +0300 Subject: MDEV-15907 ASAN heap-use-after-free This patch fixes an invalid read in fill_effective_table_privileges triggered by a grant_version increase between a PREPARE for a statement creating a view from I_S and EXECUTE. A tmp table was created and free'd while preparing the statement, TABLE_LIST::table_name was set to point to the tmp table TABLE_SHARE::table_name which no longer existed after preparing was done. The grant version increase made fill_effective_table_privileges called during EXECUTE to try fetch the updated grant info and this is where the dangling table name was used. --- mysql-test/r/mdev15907.result | 4 ++++ mysql-test/t/mdev15907.test | 4 ++++ sql/sql_show.cc | 2 -- sql/table.cc | 3 ++- 4 files changed, 10 insertions(+), 3 deletions(-) create mode 100644 mysql-test/r/mdev15907.result create mode 100644 mysql-test/t/mdev15907.test diff --git a/mysql-test/r/mdev15907.result b/mysql-test/r/mdev15907.result new file mode 100644 index 00000000000..3b5ee16b14b --- /dev/null +++ b/mysql-test/r/mdev15907.result @@ -0,0 +1,4 @@ +PREPARE stmt2 FROM "CREATE VIEW v AS SELECT * FROM INFORMATION_SCHEMA.TABLES"; +FLUSH PRIVILEGES; +EXECUTE stmt2; +DROP VIEW v; diff --git a/mysql-test/t/mdev15907.test b/mysql-test/t/mdev15907.test new file mode 100644 index 00000000000..3b5ee16b14b --- /dev/null +++ b/mysql-test/t/mdev15907.test @@ -0,0 +1,4 @@ +PREPARE stmt2 FROM "CREATE VIEW v AS SELECT * FROM INFORMATION_SCHEMA.TABLES"; +FLUSH PRIVILEGES; +EXECUTE stmt2; +DROP VIEW v; diff --git a/sql/sql_show.cc b/sql/sql_show.cc index db33a9de781..a69f7a8b970 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -7620,8 +7620,6 @@ int mysql_schema_table(THD *thd, LEX *lex, TABLE_LIST *table_list) table->alias_name_used= my_strcasecmp(table_alias_charset, table_list->schema_table_name, table_list->alias); - table_list->table_name= table->s->table_name.str; - table_list->table_name_length= table->s->table_name.length; table_list->table= table; table->next= thd->derived_tables; thd->derived_tables= table; diff --git a/sql/table.cc b/sql/table.cc index f6152a36eef..c8253649a1f 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5373,7 +5373,8 @@ const char *Field_iterator_table_ref::get_table_name() return natural_join_it.column_ref()->table_name(); DBUG_ASSERT(!strcmp(table_ref->table_name, - table_ref->table->s->table_name.str)); + table_ref->table->s->table_name.str) || + table_ref->schema_table); return table_ref->table_name; } -- cgit v1.2.1 From 2be3ab977631085dff93f583cab91dc0b8c2178d Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 19 Apr 2019 12:48:47 +0200 Subject: MDEV-15907 ASAN heap-use-after-free in strnmov / .. / fill_effective_table_privileges on concurrent GRANT and CREATE VIEW rename a test file. Closes #1253 --- mysql-test/r/information_schema_prepare.result | 4 ++++ mysql-test/r/mdev15907.result | 4 ---- mysql-test/t/information_schema_prepare.test | 7 +++++++ mysql-test/t/mdev15907.test | 4 ---- 4 files changed, 11 insertions(+), 8 deletions(-) create mode 100644 mysql-test/r/information_schema_prepare.result delete mode 100644 mysql-test/r/mdev15907.result create mode 100644 mysql-test/t/information_schema_prepare.test delete mode 100644 mysql-test/t/mdev15907.test diff --git a/mysql-test/r/information_schema_prepare.result b/mysql-test/r/information_schema_prepare.result new file mode 100644 index 00000000000..3b5ee16b14b --- /dev/null +++ b/mysql-test/r/information_schema_prepare.result @@ -0,0 +1,4 @@ +PREPARE stmt2 FROM "CREATE VIEW v AS SELECT * FROM INFORMATION_SCHEMA.TABLES"; +FLUSH PRIVILEGES; +EXECUTE stmt2; +DROP VIEW v; diff --git a/mysql-test/r/mdev15907.result b/mysql-test/r/mdev15907.result deleted file mode 100644 index 3b5ee16b14b..00000000000 --- a/mysql-test/r/mdev15907.result +++ /dev/null @@ -1,4 +0,0 @@ -PREPARE stmt2 FROM "CREATE VIEW v AS SELECT * FROM INFORMATION_SCHEMA.TABLES"; -FLUSH PRIVILEGES; -EXECUTE stmt2; -DROP VIEW v; diff --git a/mysql-test/t/information_schema_prepare.test b/mysql-test/t/information_schema_prepare.test new file mode 100644 index 00000000000..c5f3f89ff29 --- /dev/null +++ b/mysql-test/t/information_schema_prepare.test @@ -0,0 +1,7 @@ +# +# MDEV-15907 ASAN heap-use-after-free in strnmov / .. / fill_effective_table_privileges on concurrent GRANT and CREATE VIEW +# +PREPARE stmt2 FROM "CREATE VIEW v AS SELECT * FROM INFORMATION_SCHEMA.TABLES"; +FLUSH PRIVILEGES; +EXECUTE stmt2; +DROP VIEW v; diff --git a/mysql-test/t/mdev15907.test b/mysql-test/t/mdev15907.test deleted file mode 100644 index 3b5ee16b14b..00000000000 --- a/mysql-test/t/mdev15907.test +++ /dev/null @@ -1,4 +0,0 @@ -PREPARE stmt2 FROM "CREATE VIEW v AS SELECT * FROM INFORMATION_SCHEMA.TABLES"; -FLUSH PRIVILEGES; -EXECUTE stmt2; -DROP VIEW v; -- cgit v1.2.1 From 932e29d4ad3cd6ffb445f0080191c88c8b2693c0 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 22 Mar 2019 01:55:35 +0100 Subject: don't run SysV scripts in scriptlets if systemd is used --- support-files/rpm/server-postin.sh | 4 +--- support-files/rpm/server-postun.sh | 11 ++++++----- support-files/rpm/server-preun.sh | 18 +++++++++++------- 3 files changed, 18 insertions(+), 15 deletions(-) diff --git a/support-files/rpm/server-postin.sh b/support-files/rpm/server-postin.sh index 08b046dc272..ad783812184 100644 --- a/support-files/rpm/server-postin.sh +++ b/support-files/rpm/server-postin.sh @@ -17,9 +17,7 @@ fi if [ $1 = 1 ] ; then if [ -x /usr/bin/systemctl ] ; then /usr/bin/systemctl daemon-reload >/dev/null 2>&1 - fi - - if [ -x /sbin/chkconfig ] ; then + elif [ -x /sbin/chkconfig ] ; then /sbin/chkconfig --add mysql fi diff --git a/support-files/rpm/server-postun.sh b/support-files/rpm/server-postun.sh index 412c6f4c67b..21468e39e18 100644 --- a/support-files/rpm/server-postun.sh +++ b/support-files/rpm/server-postun.sh @@ -1,9 +1,10 @@ if [ $1 -ge 1 ]; then - if [ -x %{_sysconfdir}/init.d/mysql ] ; then - # only restart the server if it was alredy running - if %{_sysconfdir}/init.d/mysql status > /dev/null 2>&1; then - %{_sysconfdir}/init.d/mysql restart - fi + # only restart the server if it was alredy running + if [ -x /usr/bin/systemctl ] ; then + /usr/bin/systemctl daemon-reload > /dev/null 2>&1 + /usr/bin/systemctl try-restart mariadb.service > /dev/null 2>&1 + elif %{_sysconfdir}/init.d/mysql status > /dev/null 2>&1; then + %{_sysconfdir}/init.d/mysql restart fi fi diff --git a/support-files/rpm/server-preun.sh b/support-files/rpm/server-preun.sh index 7ef48f1c8d4..1d733a7d899 100644 --- a/support-files/rpm/server-preun.sh +++ b/support-files/rpm/server-preun.sh @@ -1,12 +1,16 @@ if [ $1 = 0 ] ; then - # Stop MySQL before uninstalling it - if [ -x %{_sysconfdir}/init.d/mysql ] ; then - %{_sysconfdir}/init.d/mysql stop > /dev/null - fi + # Stop MySQL before uninstalling it # Don't start it automatically anymore - if [ -x /sbin/chkconfig ] ; then - /sbin/chkconfig --del mysql - fi + if [ -x /usr/bin/systemctl ] ; then + /usr/bin/systemctl stop mariadb.service > /dev/null 2>&1 + /usr/bin/systemctl disable mariadb.service > /dev/null 2>&1 + fi + if [ -x %{_sysconfdir}/init.d/mysql ] ; then + %{_sysconfdir}/init.d/mysql stop > /dev/null + fi + if [ -x /sbin/chkconfig ] ; then + /sbin/chkconfig --del mysql > /dev/null 2>&1 + fi fi # We do not remove the mysql user since it may still own a lot of -- cgit v1.2.1 From 2a6f00ed6ad2787ff21194b26282a09e2ff295b4 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 10 Apr 2019 10:08:42 +0300 Subject: use the correct SQL SECURITY type for OpenGIS stored procedures --- scripts/maria_add_gis_sp.sql.in | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/scripts/maria_add_gis_sp.sql.in b/scripts/maria_add_gis_sp.sql.in index 3a5dd49b95b..5826cc1a038 100644 --- a/scripts/maria_add_gis_sp.sql.in +++ b/scripts/maria_add_gis_sp.sql.in @@ -24,12 +24,12 @@ DROP PROCEDURE IF EXISTS AddGeometryColumn; DROP PROCEDURE IF EXISTS DropGeometryColumn; CREATE DEFINER=`root`@`localhost` PROCEDURE AddGeometryColumn(catalog varchar(64), t_schema varchar(64), - t_name varchar(64), geometry_column varchar(64), t_srid int) + t_name varchar(64), geometry_column varchar(64), t_srid int) SQL SECURITY INVOKER begin set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end @ADD_GIS_SP_EOL@ CREATE DEFINER=`root`@`localhost` PROCEDURE DropGeometryColumn(catalog varchar(64), t_schema varchar(64), - t_name varchar(64), geometry_column varchar(64)) + t_name varchar(64), geometry_column varchar(64)) SQL SECURITY INVOKER begin set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end @ADD_GIS_SP_EOL@ -- cgit v1.2.1 From 38850995639912687b34ed4585b6ae0866fb1f13 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 10 Apr 2019 11:55:49 +0300 Subject: tests for AddGeometryColumn and DropGeometryColumn --- mysql-test/mysql-test-run.pl | 4 ++++ mysql-test/r/gis2.result | 42 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/gis2.test | 23 +++++++++++++++++++++++ 3 files changed, 69 insertions(+) diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index f768bf05c66..b5d145f914b 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -3210,6 +3210,10 @@ sub mysql_install_db { mtr_appendfile_to_file("$sql_dir/mysql_system_tables.sql", $bootstrap_sql_file); + my $gis_sp_path = $source_dist ? "$bindir/scripts" : $sql_dir; + mtr_appendfile_to_file("$gis_sp_path/maria_add_gis_sp_bootstrap.sql", + $bootstrap_sql_file); + # Add the performance tables # for a production system mtr_appendfile_to_file("$sql_dir/mysql_performance_tables.sql", diff --git a/mysql-test/r/gis2.result b/mysql-test/r/gis2.result index 214431e1d2d..cb98ed9ac55 100644 --- a/mysql-test/r/gis2.result +++ b/mysql-test/r/gis2.result @@ -12,3 +12,45 @@ WHERE ST_Contains(point_data, GeomFromText('Point(38.0248492 23.8512726)')); id 2 DROP TABLE t1; +show create procedure mysql.AddGeometryColumn; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +AddGeometryColumn CREATE DEFINER=`root`@`localhost` PROCEDURE `AddGeometryColumn`(catalog varchar(64), t_schema varchar(64), + t_name varchar(64), geometry_column varchar(64), t_srid int) + SQL SECURITY INVOKER +begin + set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end latin1 latin1_swedish_ci latin1_swedish_ci +show create procedure mysql.DropGeometryColumn; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +DropGeometryColumn CREATE DEFINER=`root`@`localhost` PROCEDURE `DropGeometryColumn`(catalog varchar(64), t_schema varchar(64), + t_name varchar(64), geometry_column varchar(64)) + SQL SECURITY INVOKER +begin + set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end latin1 latin1_swedish_ci latin1_swedish_ci +create table t1 (a int, b int); +call mysql.AddGeometryColumn('', 'test', 't1', 'c', 10); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL, + `c` geometry DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +call mysql.DropGeometryColumn('', 'test', 't1', 'c'); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +call mysql.DropGeometryColumn('', 'test', 't1', 'b'); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create user foo@localhost; +grant execute on mysql.* to foo@localhost; +call mysql.AddGeometryColumn('', 'mysql', 'proc', 'c', 10); +ERROR 42000: ALTER command denied to user 'foo'@'localhost' for table 'proc' +drop user foo@localhost; diff --git a/mysql-test/t/gis2.test b/mysql-test/t/gis2.test index b734ab19ecd..26422b95411 100644 --- a/mysql-test/t/gis2.test +++ b/mysql-test/t/gis2.test @@ -15,3 +15,26 @@ SELECT id FROM t1 WHERE ST_Contains(point_data, GeomFromText('Point(38.0248492 23.8512726)')); DROP TABLE t1; +# +# MDEV-60 Support for Spatial Reference systems for the GIS data. +# +show create procedure mysql.AddGeometryColumn; +show create procedure mysql.DropGeometryColumn; +create table t1 (a int, b int); +call mysql.AddGeometryColumn('', 'test', 't1', 'c', 10); +show create table t1; +call mysql.DropGeometryColumn('', 'test', 't1', 'c'); +show create table t1; +call mysql.DropGeometryColumn('', 'test', 't1', 'b'); +show create table t1; +drop table t1; + +create user foo@localhost; +grant execute on mysql.* to foo@localhost; +connect (foo, localhost, foo); +--error ER_TABLEACCESS_DENIED_ERROR +call mysql.AddGeometryColumn('', 'mysql', 'proc', 'c', 10); +disconnect foo; +connection default; +drop user foo@localhost; + -- cgit v1.2.1 From 2f9cd06da4f3a4695a63efb0d4a8af7ef99bd5b3 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 17 Apr 2019 13:20:48 +0200 Subject: MDEV-18362 EPOCH=1 needs to be set for Ubuntu 18.10 cosmic builds Apply dbart's patch to set epoch for all ubuntu releases starting from bionic. Not only for bionic. This works for Debian too (VERNUM=unstable, etc). --- debian/autobake-deb.sh | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/debian/autobake-deb.sh b/debian/autobake-deb.sh index 648c95cb416..98071ba6d03 100755 --- a/debian/autobake-deb.sh +++ b/debian/autobake-deb.sh @@ -30,6 +30,7 @@ LOGSTRING="MariaDB build" # Look up distro-version specific stuff. CODENAME="$(lsb_release -sc)" +VERNUM="$(lsb_release -sr)" # add libcrack2 (>= 2.9.0) as a build dependency # but only where the distribution can possibly satisfy it @@ -57,7 +58,7 @@ sed -i -e "s/\\\${LIBSSL}/${LIBSSL}/g" debian/control # echo "Incrementing changelog and starting build scripts" -if [[ "$CODENAME" == bionic ]]; then +if [[ "${VERNUM%.*}" -ge 18 ]]; then EPOCH="1:" fi dch -b -D ${CODENAME} -v "${EPOCH}${UPSTREAM}${PATCHLEVEL}-${RELEASE_NAME}${RELEASE_EXTRA:+-${RELEASE_EXTRA}}1~${CODENAME}" "Automatic build with ${LOGSTRING}." -- cgit v1.2.1 From 17088dd941b6c759e294cc3a9767574b19734694 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 17 Apr 2019 13:42:32 +0200 Subject: MDEV-19024 sys_vars.transaction_prealloc_size_bug27322 fails in buildbot with wrong result Take into account ps-protocol states. Note, repace_regex was redundant, because Host column was replaced anyway --- .../suite/sys_vars/t/transaction_prealloc_size_bug27322.test | 9 +++++---- 1 file changed, 5 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/sys_vars/t/transaction_prealloc_size_bug27322.test b/mysql-test/suite/sys_vars/t/transaction_prealloc_size_bug27322.test index c83f3dc2507..6e3c330ce06 100644 --- a/mysql-test/suite/sys_vars/t/transaction_prealloc_size_bug27322.test +++ b/mysql-test/suite/sys_vars/t/transaction_prealloc_size_bug27322.test @@ -44,22 +44,23 @@ SET @def_var= @@session.transaction_prealloc_size; SET SESSION transaction_prealloc_size=1024*1024*1024*1; # Embedded server is shows "cleaning up" as STATE, while non-embedded shows "init" --replace_column 1 3 6