- commits - lists.mariadb.org

[Commits] 6b8d34f: MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key.
by holyfoot＠askmonty.org 12 Jun '18

12 Jun '18

revision-id: 6b8d34fe0d5e73a469383bcb0818d3ff91ed9a84 (mariadb-10.0.35-38-g6b8d34f) parent(s): 0ad9c3a0160d1dd46139b3e6b6b05d0fba01540b committer: Alexey Botchkov timestamp: 2018-06-12 12:36:51 +0400 message: MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key. Check the name of the primary key to be 'PRIMARY'. Than differs it from any implicit primary keys created by an engine. --- mysql-test/r/alter_table.result | 23 +++++++++++++++++++++++ mysql-test/t/alter_table.test | 16 ++++++++++++++++ sql/sql_table.cc | 4 +++- 3 files changed, 42 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index bb78df9..c6e3c7e 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -2225,3 +2225,26 @@ t1 CREATE TABLE `t1` ( `b` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t1; +# +# MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key +# +CREATE TABLE t1 ( +`ID` BIGINT(20) NOT NULL, +`RANK` MEDIUMINT(4) NOT NULL, +`CHECK_POINT` BIGINT(20) NOT NULL, +UNIQUE INDEX `HORIZON_UIDX01` (`ID`, `RANK`) +) ENGINE=InnoDB; +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ID` bigint(20) NOT NULL, + `RANK` mediumint(4) NOT NULL, + `CHECK_POINT` bigint(20) NOT NULL, + PRIMARY KEY (`ID`,`CHECK_POINT`), + UNIQUE KEY `HORIZON_UIDX01` (`ID`,`RANK`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +Warnings: +Note 1061 Multiple primary key defined +DROP TABLE t1; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 585a272..28d8c5b 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1841,3 +1841,19 @@ CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; ALTER TABLE t1 DROP INDEX IF EXISTS fk, DROP COLUMN IF EXISTS c; SHOW CREATE TABLE t1; DROP TABLE t1; + +--echo # +--echo # MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key +--echo # +CREATE TABLE t1 ( + `ID` BIGINT(20) NOT NULL, + `RANK` MEDIUMINT(4) NOT NULL, + `CHECK_POINT` BIGINT(20) NOT NULL, + UNIQUE INDEX `HORIZON_UIDX01` (`ID`, `RANK`) + ) ENGINE=InnoDB; + +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +DROP TABLE t1; + diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 376c136..6c71067 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -5930,7 +5930,9 @@ handle_if_exists_options(THD *thd, TABLE *table, Alter_info *alter_info) /* Check if the table already has a PRIMARY KEY */ if (key->type == Key::PRIMARY && - table->s->primary_key != MAX_KEY) + table->s->primary_key != MAX_KEY && + (keyname= table->s->key_info[table->s->primary_key].name) && + my_strcasecmp(system_charset_info, keyname, primary_key_name) == 0) { push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE, ER_DUP_KEYNAME, ER(ER_MULTIPLE_PRI_KEY));

1 0

[Commits] 9fe7489: Issue #834: Server crash in print_keydup_error / key_unpack or unexpected ER_DUP_KEY
by psergey＠askmonty.org 11 Jun '18

11 Jun '18

revision-id: 9fe7489a3bfd88628097e58f75516b5cad9f8dab parent(s): bd344d9f4ec384020f2133eb13f55272818340eb committer: Sergei Petrunia branch nick: mysql-5.6-rocksdb-spetrunia timestamp: 2018-06-11 19:03:40 +0300 message: Issue #834: Server crash in print_keydup_error / key_unpack or unexpected ER_DUP_KEY The issue is caused by a combination of two factors: 1. Rdb_ddl_manager::rename() loses the value of m_hidden_pk_val. new object used to get 0, which means "not loaded from the db yet". 2. ha_rocksdb::load_hidden_pk_value() uses current transaction (and its snapshot) when loading hidden PK value from disk. This may cause it to load an out-of-date value. This patch fixes #1, which is sufficient to fix the issue. (This is attempt 2, with review input addressed) --- .../suite/rocksdb/r/allow_no_primary_key.result | 25 +++++++++++++++++++ .../suite/rocksdb/t/allow_no_primary_key.test | 28 ++++++++++++++++++++++ storage/rocksdb/rdb_datadic.cc | 3 +++ 3 files changed, 56 insertions(+) diff --git a/mysql-test/suite/rocksdb/r/allow_no_primary_key.result b/mysql-test/suite/rocksdb/r/allow_no_primary_key.result index d86792a..5058934 100644 --- a/mysql-test/suite/rocksdb/r/allow_no_primary_key.result +++ b/mysql-test/suite/rocksdb/r/allow_no_primary_key.result @@ -262,3 +262,28 @@ SELECT * FROM t1; a b 36 foo DROP TABLE t1; +# +# Issue #834/MDEV-15304 ALTER TABLE table_with_hidden_pk causes Can't +# write; duplicate key in table error and/or crash +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1),(1+1); +create table t2 (a int) ENGINE=rocksdb; +insert into t2 values (10),(20),(30); +BEGIN; +select * from t2; +a +10 +20 +30 +alter table t1 force; +select * from t1; +a +insert into t1 values (100); +select * from t1; +a +1 +2 +100 +rollback; +drop table t1,t2; diff --git a/mysql-test/suite/rocksdb/t/allow_no_primary_key.test b/mysql-test/suite/rocksdb/t/allow_no_primary_key.test index 2a064dc..966a70d 100644 --- a/mysql-test/suite/rocksdb/t/allow_no_primary_key.test +++ b/mysql-test/suite/rocksdb/t/allow_no_primary_key.test @@ -96,3 +96,31 @@ DELETE FROM t1 WHERE a = 35 AND b = 'foo'; --sorted_result SELECT * FROM t1; DROP TABLE t1; + +--echo # +--echo # Issue #834/MDEV-15304 ALTER TABLE table_with_hidden_pk causes Can't +--echo # write; duplicate key in table error and/or crash +--echo # +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1),(1+1); +create table t2 (a int) ENGINE=rocksdb; +insert into t2 values (10),(20),(30); + +BEGIN; +select * from t2; + +connect (con1,localhost,root,,); +connection con1; +alter table t1 force; + +connection default; +select * from t1; + +connection con1; +insert into t1 values (100); +select * from t1; + +disconnect con1; +connection default; +rollback; +drop table t1,t2; diff --git a/storage/rocksdb/rdb_datadic.cc b/storage/rocksdb/rdb_datadic.cc index 2c1c97b..04c8a7c 100644 --- a/storage/rocksdb/rdb_datadic.cc +++ b/storage/rocksdb/rdb_datadic.cc @@ -4241,6 +4241,9 @@ bool Rdb_ddl_manager::rename(const std::string &from, const std::string &to, rec->m_auto_incr_val.load(std::memory_order_relaxed); new_rec->m_key_descr_arr = rec->m_key_descr_arr; + new_rec->m_hidden_pk_val = + rec->m_hidden_pk_val.load(std::memory_order_relaxed); + // so that it's not free'd when deleting the old rec rec->m_key_descr_arr = nullptr;

1 0

[Commits] 5736482: Issue #834: Server crash in print_keydup_error / key_unpack or unexpected ER_DUP_KEY
by psergey＠askmonty.org 11 Jun '18

11 Jun '18

revision-id: 5736482c572b443dd8e624b342b5ef7a7c21f26c parent(s): bd344d9f4ec384020f2133eb13f55272818340eb committer: Sergei Petrunia branch nick: mysql-5.6-rocksdb-spetrunia timestamp: 2018-06-11 19:02:54 +0300 message: Issue #834: Server crash in print_keydup_error / key_unpack or unexpected ER_DUP_KEY The issue is caused by a combination of two factors: 1. Rdb_ddl_manager::rename() loses the value of m_hidden_pk_val. new object used to get 0, which means "not loaded from the db yet". 2. ha_rocksdb::load_hidden_pk_value() uses current transaction (and its snapshot) when loading hidden PK value from disk. This may cause it to load an out-of-date value. This patch fixes #1, which is sufficient to fix the issue. --- .../suite/rocksdb/r/allow_no_primary_key.result | 31 ++++++++++++++++++++++ .../suite/rocksdb/t/allow_no_primary_key.test | 28 +++++++++++++++++++ storage/rocksdb/ha_rocksdb.cc | 23 ++++++++++------ storage/rocksdb/rdb_datadic.cc | 3 +++ 4 files changed, 77 insertions(+), 8 deletions(-) diff --git a/mysql-test/suite/rocksdb/r/allow_no_primary_key.result b/mysql-test/suite/rocksdb/r/allow_no_primary_key.result index d86792a..45b0ca7 100644 --- a/mysql-test/suite/rocksdb/r/allow_no_primary_key.result +++ b/mysql-test/suite/rocksdb/r/allow_no_primary_key.result @@ -262,3 +262,34 @@ SELECT * FROM t1; a b 36 foo DROP TABLE t1; +# +# Issue #834/MDEV-15304 ALTER TABLE table_with_hidden_pk causes Can't +# write; duplicate key in table error and/or crash +# +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1),(1+1); +create table t2 (a int); +insert into t2 values (10),(20),(30); +BEGIN; +select * from t2; +a +10 +20 +30 +connect con1,localhost,root,,; +connection con1; +alter table t1 force; +connection default; +select * from t1; +a +connection con1; +insert into t1 values (100); +select * from t1; +a +1 +2 +100 +disconnect con1; +connection default; +rollback; +drop table t1,t2; diff --git a/mysql-test/suite/rocksdb/t/allow_no_primary_key.test b/mysql-test/suite/rocksdb/t/allow_no_primary_key.test index 2a064dc..5f2a37f 100644 --- a/mysql-test/suite/rocksdb/t/allow_no_primary_key.test +++ b/mysql-test/suite/rocksdb/t/allow_no_primary_key.test @@ -96,3 +96,31 @@ DELETE FROM t1 WHERE a = 35 AND b = 'foo'; --sorted_result SELECT * FROM t1; DROP TABLE t1; + +--echo # +--echo # Issue #834/MDEV-15304 ALTER TABLE table_with_hidden_pk causes Can't +--echo # write; duplicate key in table error and/or crash +--echo # +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1),(1+1); +create table t2 (a int); +insert into t2 values (10),(20),(30); + +BEGIN; +select * from t2; + +connect (con1,localhost,root,,); +connection con1; +alter table t1 force; + +connection default; +select * from t1; + +connection con1; +insert into t1 values (100); +select * from t1; + +disconnect con1; +connection default; +rollback; +drop table t1,t2; diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 5f13fee..a884570 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -5216,8 +5216,17 @@ int ha_rocksdb::load_hidden_pk_value() { active_index = m_tbl_def->m_key_count - 1; const uint8 save_table_status = table->status; - Rdb_transaction *const tx = get_or_create_tx(table->in_use); - const bool is_new_snapshot = !tx->has_snapshot(); + /* + We should read the latest committed value in the database. + That is, if we have an open transaction with a snapshot, we should not use + it as we may get old data. Start a new transaction to read the latest + value. + */ + Rdb_transaction *const temp_tx = new Rdb_transaction_impl(table->in_use); + temp_tx->start_tx(); + Rdb_transaction *&tx = get_tx_from_thd(table->in_use); + Rdb_transaction *save_tx= tx; + tx= temp_tx; longlong hidden_pk_id = 1; // Do a lookup. @@ -5227,9 +5236,8 @@ int ha_rocksdb::load_hidden_pk_value() { */ auto err = read_hidden_pk_id_from_rowkey(&hidden_pk_id); if (err) { - if (is_new_snapshot) { - tx->release_snapshot(); - } + delete tx; + tx= save_tx; return err; } @@ -5241,9 +5249,8 @@ int ha_rocksdb::load_hidden_pk_value() { !m_tbl_def->m_hidden_pk_val.compare_exchange_weak(old, hidden_pk_id)) { } - if (is_new_snapshot) { - tx->release_snapshot(); - } + delete tx; + tx= save_tx; table->status = save_table_status; active_index = save_active_index; diff --git a/storage/rocksdb/rdb_datadic.cc b/storage/rocksdb/rdb_datadic.cc index 2c1c97b..04c8a7c 100644 --- a/storage/rocksdb/rdb_datadic.cc +++ b/storage/rocksdb/rdb_datadic.cc @@ -4241,6 +4241,9 @@ bool Rdb_ddl_manager::rename(const std::string &from, const std::string &to, rec->m_auto_incr_val.load(std::memory_order_relaxed); new_rec->m_key_descr_arr = rec->m_key_descr_arr; + new_rec->m_hidden_pk_val = + rec->m_hidden_pk_val.load(std::memory_order_relaxed); + // so that it's not free'd when deleting the old rec rec->m_key_descr_arr = nullptr;

1 0

[Commits] bbf1786: MDEV-16235 Server crashes in my_utf8_uni or in my_strtod_int upon
by IgorBabaev 11 Jun '18

11 Jun '18

revision-id: bbf17861787b5f739df492af49479fbd7aef2e1a (mariadb-5.5.60-19-gbbf1786) parent(s): d9b159a2027c56c5c87385cfe1ae43b8c73a97b6 author: Igor Babaev committer: Igor Babaev timestamp: 2018-06-11 08:52:26 -0700 message: MDEV-16235 Server crashes in my_utf8_uni or in my_strtod_int upon SELECT .. LIMIT 0 (new variant) This is another attempt to fix the problem of mdev-14515. --- mysql-test/r/having.result | 11 +++++++++++ mysql-test/r/subselect4.result | 4 ++-- mysql-test/t/having.test | 9 +++++++++ sql/opt_subselect.cc | 3 ++- sql/sql_select.cc | 1 - 5 files changed, 24 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 18915da..9e0466e 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -712,3 +712,14 @@ a ct 4 2 set sql_mode=@save_sql_mode; drop table t1; +# +# mdev-16235: impossible HAVING in query without aggregation +# +explain extended +select * from mysql.help_topic where example = 'foo' having description is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +Warnings: +Note 1003 select `mysql`.`help_topic`.`help_topic_id` AS `help_topic_id`,`mysql`.`help_topic`.`name` AS `name`,`mysql`.`help_topic`.`help_category_id` AS `help_category_id`,`mysql`.`help_topic`.`description` AS `description`,`mysql`.`help_topic`.`example` AS `example`,`mysql`.`help_topic`.`url` AS `url` from `mysql`.`help_topic` where (`mysql`.`help_topic`.`example` = 'foo') having 0 +select * from mysql.help_topic where example = 'foo' having description is null; +help_topic_id name help_category_id description example url diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 8973330..f726bcf 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1056,7 +1056,7 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); f1 f2 SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off'; @@ -1147,7 +1147,7 @@ EXPLAIN SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); f1 f2 set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index a470f46..c8bd479 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -744,3 +744,12 @@ SELECT a, COUNT(a) as ct FROM t1 GROUP BY a HAVING ct>0; set sql_mode=@save_sql_mode; drop table t1; + +--echo # +--echo # mdev-16235: impossible HAVING in query without aggregation +--echo # + +explain extended +select * from mysql.help_topic where example = 'foo' having description is null; + +select * from mysql.help_topic where example = 'foo' having description is null; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index a7edd64..ec7b10f 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5880,6 +5880,7 @@ bool JOIN::choose_tableless_subquery_plan() functions produce empty subquery result. There is no need to further rewrite the subquery because it will not be executed at all. */ + exec_const_cond= 0; return FALSE; } @@ -5911,6 +5912,6 @@ bool JOIN::choose_tableless_subquery_plan() tmp_having= having; } } - exec_const_cond= conds; + exec_const_cond= zero_result_cause ? 0 : conds; return FALSE; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 016dd59..b79431a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1147,7 +1147,6 @@ JOIN::optimize() { DBUG_PRINT("info", ("Zero limit")); zero_result_cause= "Zero limit"; - conds= 0; } table_count= top_join_tab_count= 0; error= 0;

1 0

[Commits] 2c7c6f70226: MDEV-14572: Assertion `! is_set()' failed in Diagnostics_area::set_eof_status upon EXPLAIN UPDATE in PS
by Oleksandr Byelkin 11 Jun '18

11 Jun '18

revision-id: 2c7c6f702262284ac8e5c0c2cdb6945d62fd8efc (mariadb-10.0.35-37-g2c7c6f70226) parent(s): 24d7cbe1e0a16f75e2325c84f23531742e2a035d author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-06-11 17:32:51 +0200 message: MDEV-14572: Assertion `! is_set()' failed in Diagnostics_area::set_eof_status upon EXPLAIN UPDATE in PS Restore EXPAIN flag in SELECT_LEX before execution multi-update by flag in LEX (the same but in other way made before INSERT/DELETE/SELECT) Without it, mysql_update() didn't know that there will be EXPLAIN result set and was sending OK at the end of the update, which conflicted with the EOF sent later by EXPLAIN. --- mysql-test/r/ps.result | 18 ++++++++++++++++++ mysql-test/t/ps.test | 22 ++++++++++++++++++++++ sql/sql_parse.cc | 10 ++++++++++ 3 files changed, 50 insertions(+) diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index d3fbc595f17..7e87e546fe7 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -4341,3 +4341,21 @@ LINE2 2 LINE3 3 drop table t1; # End of 5.5 tests +# +# MDEV-14572: Assertion `! is_set()' failed in +# Diagnostics_area::set_eof_status upon EXPLAIN UPDATE in PS +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +PREPARE stmt FROM 'EXPLAIN UPDATE t1, t2 SET a = 1'; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found +deallocate prepare stmt; +DROP TABLE t1, t2; +# End of 10.0 tests diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index d60edf4f6a0..c6c339caaa0 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3859,3 +3859,25 @@ FROM ) X; drop table t1; --echo # End of 5.5 tests + + +--echo # +--echo # MDEV-14572: Assertion `! is_set()' failed in +--echo # Diagnostics_area::set_eof_status upon EXPLAIN UPDATE in PS +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); + +#EXPLAIN UPDATE t1, t2 SET a = 1; +PREPARE stmt FROM 'EXPLAIN UPDATE t1, t2 SET a = 1'; +EXECUTE stmt; +EXECUTE stmt; +deallocate prepare stmt; + +# Cleanup +DROP TABLE t1, t2; + + +--echo # End of 10.0 tests + diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 28d11ef2e5c..8e216d1ef2f 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3328,6 +3328,16 @@ case SQLCOM_PREPARE: else res= 0; + /* + We can not use mysql_explain_union() because of parameters of + mysql_select in mysql_multi_update so just set the option if needed + */ + if (thd->lex->describe) + { + select_lex->set_explain_type(FALSE); + select_lex->options|= SELECT_DESCRIBE; + } + res= mysql_multi_update_prepare(thd); #ifdef HAVE_REPLICATION

1 0

[Commits] 20f3479: MDEV-15890 Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>.
by holyfoot＠askmonty.org 11 Jun '18

11 Jun '18

revision-id: 20f347974f6a9b4e2c65a5ca79aefb8e118ba6cd (mariadb-10.1.33-33-g20f3479) parent(s): 3627dd7f6a1cf5bd7151ff23290d64cb6dffea90 committer: Alexey Botchkov timestamp: 2018-06-11 14:58:46 +0400 message: MDEV-15890 Strange error message if you try to FLUSH TABLES <view> after LOCK TABLES <view>. The reload_acl_and_cache() now looks into the VIEW definition to check the involved tables if the locked_tables_mode. --- mysql-test/r/flush.result | 12 ++++++++++++ mysql-test/t/flush.test | 15 +++++++++++++++ sql/sql_base.cc | 38 +++++++++++++++++++++++++------------- sql/sql_base.h | 2 +- sql/sql_reload.cc | 36 ++++++++++++++++++++++++++++++++++-- sql/sql_table.cc | 2 +- sql/sql_trigger.cc | 2 +- sql/sql_truncate.cc | 2 +- 8 files changed, 90 insertions(+), 19 deletions(-) diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index b643510..8991032 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -496,3 +496,15 @@ flush relay logs,relay logs; ERROR HY000: Incorrect usage of FLUSH and RELAY LOGS flush slave,slave; ERROR HY000: Incorrect usage of FLUSH and SLAVE +# +# MDEV-15890 Strange error message if you try to +# FLUSH TABLES <view> after LOCK TABLES <view>. +# +CREATE TABLE t1 (qty INT, price INT); +CREATE VIEW v1 AS SELECT qty, price, qty*price AS value FROM t1; +LOCK TABLES v1 READ; +FLUSH TABLES v1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +UNLOCK TABLES; +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index a1df935..4db7975 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -709,3 +709,18 @@ DROP TABLE t1; flush relay logs,relay logs; --error ER_WRONG_USAGE flush slave,slave; + +--echo # +--echo # MDEV-15890 Strange error message if you try to +--echo # FLUSH TABLES <view> after LOCK TABLES <view>. +--echo # + +CREATE TABLE t1 (qty INT, price INT); +CREATE VIEW v1 AS SELECT qty, price, qty*price AS value FROM t1; +LOCK TABLES v1 READ; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +FLUSH TABLES v1; +UNLOCK TABLES; +DROP VIEW v1; +DROP TABLE t1; + diff --git a/sql/sql_base.cc b/sql/sql_base.cc index d671bb4..8561622 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -522,9 +522,10 @@ bool close_cached_tables(THD *thd, TABLE_LIST *tables, for (TABLE_LIST *table_list= tables_to_reopen; table_list; table_list= table_list->next_global) { + int err; /* A check that the table was locked for write is done by the caller. */ TABLE *table= find_table_for_mdl_upgrade(thd, table_list->db, - table_list->table_name, TRUE); + table_list->table_name, &err); /* May return NULL if this table has already been closed via an alias. */ if (! table) @@ -2666,8 +2667,9 @@ TABLE *find_locked_table(TABLE *list, const char *db, const char *table_name) @param thd Thread context @param db Database name. @param table_name Name of table. - @param no_error Don't emit error if no suitable TABLE - instance were found. + @param p_error In the case of an error (when the function returns NULL) + the error number is stored there. + If the p_error is NULL, function launches the error itself. @note This function checks if the connection holds a global IX metadata lock. If no such lock is found, it is not safe to @@ -2680,15 +2682,15 @@ TABLE *find_locked_table(TABLE *list, const char *db, const char *table_name) */ TABLE *find_table_for_mdl_upgrade(THD *thd, const char *db, - const char *table_name, bool no_error) + const char *table_name, int *p_error) { TABLE *tab= find_locked_table(thd->open_tables, db, table_name); + int error; if (!tab) { - if (!no_error) - my_error(ER_TABLE_NOT_LOCKED, MYF(0), table_name); - return NULL; + error= ER_TABLE_NOT_LOCKED; + goto err_exit; } /* @@ -2700,9 +2702,8 @@ TABLE *find_table_for_mdl_upgrade(THD *thd, const char *db, if (!thd->mdl_context.is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE)) { - if (!no_error) - my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), table_name); - return NULL; + error= ER_TABLE_NOT_LOCKED_FOR_WRITE; + goto err_exit; } while (tab->mdl_ticket != NULL && @@ -2710,10 +2711,21 @@ TABLE *find_table_for_mdl_upgrade(THD *thd, const char *db, (tab= find_locked_table(tab->next, db, table_name))) continue; - if (!tab && !no_error) - my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), table_name); + if (!tab) + { + error= ER_TABLE_NOT_LOCKED_FOR_WRITE; + goto err_exit; + } return tab; + +err_exit: + if (p_error) + *p_error= error; + else + my_error(error, MYF(0), table_name); + + return NULL; } @@ -4446,7 +4458,7 @@ open_tables_check_upgradable_mdl(THD *thd, TABLE_LIST *tables_start, Note that find_table_for_mdl_upgrade() will report an error if no suitable ticket is found. */ - if (!find_table_for_mdl_upgrade(thd, table->db, table->table_name, false)) + if (!find_table_for_mdl_upgrade(thd, table->db, table->table_name, NULL)) return TRUE; } diff --git a/sql/sql_base.h b/sql/sql_base.h index 7415418..e9dd0f5 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -329,7 +329,7 @@ static inline bool tdc_open_view(THD *thd, TABLE_LIST *table_list, TABLE *find_table_for_mdl_upgrade(THD *thd, const char *db, const char *table_name, - bool no_error); + int *p_error); void mark_tmp_table_for_reuse(TABLE *table); int update_virtual_fields(THD *thd, TABLE *table, diff --git a/sql/sql_reload.cc b/sql/sql_reload.cc index 73dd967..a0bee37 100644 --- a/sql/sql_reload.cc +++ b/sql/sql_reload.cc @@ -30,6 +30,7 @@ #include "sql_show.h" #include "debug_sync.h" #include "des_key_file.h" +#include "sql_view.h" static void disable_checkpoints(THD *thd); @@ -288,9 +289,40 @@ bool reload_acl_and_cache(THD *thd, unsigned long long options, */ if (tables) { + int err; for (TABLE_LIST *t= tables; t; t= t->next_local) - if (!find_table_for_mdl_upgrade(thd, t->db, t->table_name, false)) - return 1; + if (!find_table_for_mdl_upgrade(thd, t->db, t->table_name, &err)) + { + TABLE_SHARE *share; + TABLE *table; + const char *key; + uint key_length; + int res; + + key_length= get_table_def_key(t, &key); + share= tdc_acquire_share(thd, t->db, t->table_name, + key, key_length, + t->mdl_request.key.tc_hash_value(), + GTS_VIEW | GTS_TABLE, &table); + if (!share) + return 1; + + if (!share->is_view) + { + my_error(err, MYF(0), t->table_name); + res= 1; + } + else + { + res= mysql_make_view(thd, share, t, false); + t->next_local= t->next_global; + } + + tdc_release_share(share); + + if (res) + return 1; + } } else { diff --git a/sql/sql_table.cc b/sql/sql_table.cc index a68f9e6..d49985b 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2070,7 +2070,7 @@ bool mysql_rm_table(THD *thd,TABLE_LIST *tables, my_bool if_exists, in its elements. */ table->table= find_table_for_mdl_upgrade(thd, table->db, - table->table_name, false); + table->table_name, NULL); if (!table->table) DBUG_RETURN(true); table->mdl_request.ticket= table->table->mdl_ticket; diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc index bbcc757..515a40da 100644 --- a/sql/sql_trigger.cc +++ b/sql/sql_trigger.cc @@ -531,7 +531,7 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create) /* Under LOCK TABLES we must only accept write locked tables. */ if (!(tables->table= find_table_for_mdl_upgrade(thd, tables->db, tables->table_name, - FALSE))) + NULL))) goto end; } else diff --git a/sql/sql_truncate.cc b/sql/sql_truncate.cc index 8a70e6f..5b313fc 100644 --- a/sql/sql_truncate.cc +++ b/sql/sql_truncate.cc @@ -302,7 +302,7 @@ bool Sql_cmd_truncate_table::lock_table(THD *thd, TABLE_LIST *table_ref, if (thd->locked_tables_mode) { if (!(table= find_table_for_mdl_upgrade(thd, table_ref->db, - table_ref->table_name, FALSE))) + table_ref->table_name, NULL))) DBUG_RETURN(TRUE); *hton_can_recreate= ha_check_storage_engine_flag(table->s->db_type(),

1 0

[Commits] 828770eb23e: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's
by andrei.elkin＠pp.inet.fi 11 Jun '18

11 Jun '18

revision-id: 828770eb23ecabed503a8e3bc61f272914093926 (mariadb-10.1.33-31-g828770eb23e) parent(s): 3b7da8a44c8a0ff4b40b37e4db01f7e397aefab5 author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-06-11 11:56:15 +0300 message: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's specific temporary errors The optimistic parallel slave's worker thread could face a run-time error due to the algorithm's specifics which allows for conflicts like the reported "Can't find record in 'table'". A typical stack is like {noformat} #0 handler::print_error (this=0x61c00008f8a0, error=149, errflag=0) at handler.cc:3650 #1 0x0000555555e95361 in write_record (thd=thd@entry=0x62a0000a2208, table=table@entry=0x61f00008ce88, info=info@entry=0x7fffdee356d0) at sql_insert.cc:1944 #2 0x0000555555ea7767 in mysql_insert (thd=thd@entry=0x62a0000a2208, table_list=0x61b00012ada0, fields=..., values_list=..., update_fields=..., update_values=..., duplic=<optimized out>, ignore=<optimized out>) at sql_insert.cc:1039 #3 0x0000555555efda90 in mysql_execute_command (thd=thd@entry=0x62a0000a2208) at sql_parse.cc:3927 #4 0x0000555555f0cc50 in mysql_parse (thd=0x62a0000a2208, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at sql_parse.cc:7449 #5 0x00005555566d4444 in Query_log_event::do_apply_event (this=0x61200005b9c8, rgi=<optimized out>, query_arg=<optimized out>, q_len_arg=<optimized out>) at log_event.cc:4508 #6 0x00005555566d639e in Query_log_event::do_apply_event (this=<optimized out>, rgi=<optimized out>) at log_event.cc:4185 #7 0x0000555555d738cf in Log_event::apply_event (rgi=0x61d0001ea080, this=0x61200005b9c8) at log_event.h:1343 #8 apply_event_and_update_pos_apply (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080, reason=<optimized out>) at slave.cc:3479 #9 0x0000555555d8596b in apply_event_and_update_pos_for_parallel (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080) at slave.cc:3623 #10 0x00005555562aca83 in rpt_handle_event (qev=qev@entry=0x6190000fa088, rpt=rpt@entry=0x62200002bd68) at rpl_parallel.cc:50 #11 0x00005555562bd04e in handle_rpl_parallel_thread (arg=arg@entry=0x62200002bd68) at rpl_parallel.cc:1258 {noformat} Here {{handler::print_error}} computes whether to error log the current error when --log-warnings > 1. The decision flag is consulted bu {{my_message_sql()}} which can be eventually called. In the bug case the decision is to log. However in the optimistic mode slave applier case any conflict is attempted to resolve with rollback and retry to success. Hence the logging is at least extraneous. The case is fixed with adding a new flag {{ME_LOG_AS_WARN}} which {{handler::print_error}} may propagate further on through {{my_error}} when the error comes from an optimistically running slave worker thread. The new flag effectively requests the warning level for the errlog record, while the thread's DA records the actual error (which is regarded as temporary one by the parallel slave error handler). --- include/my_sys.h | 1 + .../suite/rpl/r/rpl_parallel_optimistic.result | 23 ++++++++ .../suite/rpl/t/rpl_parallel_optimistic.test | 64 +++++++++++++++++++++- sql/handler.cc | 5 +- sql/mysqld.cc | 10 ++++ sql/sql_class.cc | 7 +++ sql/sql_class.h | 6 ++ 7 files changed, 114 insertions(+), 2 deletions(-) diff --git a/include/my_sys.h b/include/my_sys.h index 110a2ee9af3..1c5649812d1 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -112,6 +112,7 @@ typedef struct my_aio_result { #define ME_JUST_INFO 1024 /**< not error but just info */ #define ME_JUST_WARNING 2048 /**< not error but just warning */ #define ME_FATALERROR 4096 /* Fatal statement error */ +#define ME_LOG_AS_WARN 8192 /* is error but error-logged as warning */ /* Bits in last argument to fn_format */ #define MY_REPLACE_DIR 1 /* replace dir in name with 'dir' */ diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result index 0177e65b10f..a6da3399fab 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result @@ -1,4 +1,6 @@ include/rpl_init.inc [topology=1->2] +call mtr.add_suppression("Warning.*Deadlock found when trying to get lock; try restarting transaction"); +call mtr.add_suppression("Warning.*mysqld: Can't find record in 't2'"); ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; @@ -543,6 +545,27 @@ a b 57 7 58 8 59 9 +DELETE FROM t1; +DELETE FROM t2; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +BEGIN; +INSERT INTO t1 SET a=1; +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=row; +BEGIN; +INSERT INTO t1 SET a=1; +INSERT INTO t2 SET a=1; +COMMIT; +BEGIN; +DELETE FROM t2; +COMMIT; +ROLLBACK; +SET @@SESSION.binlog_format= @save.binlog_format; +DELETE FROM t1; +DELETE FROM t2; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc include/stop_slave.inc SET GLOBAL slave_parallel_mode=@old_parallel_mode; SET GLOBAL slave_parallel_threads=@old_parallel_threads; diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test index 41fb6ebb72e..28bf4a77fd4 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test @@ -4,6 +4,11 @@ --let $rpl_topology=1->2 --source include/rpl_init.inc +--connection server_2 +call mtr.add_suppression("Warning.*Deadlock found when trying to get lock; try restarting transaction"); +# The following instruction is a part of the proof of MDEV-13577 fixes, below. +call mtr.add_suppression("Warning.*mysqld: Can't find record in 't2'"); + --connection server_1 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; @@ -480,8 +485,65 @@ SELECT * FROM t2 WHERE a >= 40 ORDER BY a; SELECT * FROM t1 WHERE a >= 40 ORDER BY a; SELECT * FROM t2 WHERE a >= 40 ORDER BY a; -# Clean up. +# partial cleanup to reuse the tables by following tests +--connection server_1 +DELETE FROM t1; +DELETE FROM t2; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc +# +# MDEV-13577 optimistic parallel slave errors out to error log unnecessary +# + +# The 1st of the following two trx:s a blocker on slave +--connection server_2 +BEGIN; +INSERT INTO t1 SET a=1; + +--connection server_1 +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=row; + +BEGIN; + INSERT INTO t1 SET a=1; + INSERT INTO t2 SET a=1; +COMMIT; + +# This transaction is going to win optimistical race with above INSERT +# on slave while being depend on it. That means it will face a kind of temporary error +# and then will retry to succeed. +BEGIN; + DELETE FROM t2; +COMMIT; + +# First make sure DELETE raced indeed to get stuck at retrying stage +# where it runs "realistically" now. There is nomore optimistic error +# in the errorlog, which is downgraded to the warning level (when +# --log-warnings > 1), see above suppression. +--connection server_2 +--let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" +--source include/wait_condition.inc + +# Next release the 1st trx to commit. +--connection server_2 +ROLLBACK; + +# MDEV-13577 local cleanup: +--connection server_1 +SET @@SESSION.binlog_format= @save.binlog_format; +DELETE FROM t1; +DELETE FROM t2; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc + +# +# Clean up. +# --connection server_2 --source include/stop_slave.inc SET GLOBAL slave_parallel_mode=@old_parallel_mode; diff --git a/sql/handler.cc b/sql/handler.cc index 397891ceec5..87f0926ad9a 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3639,12 +3639,15 @@ void handler::print_error(int error, myf errflag) if ((debug_assert_if_crashed_table || global_system_variables.log_warnings > 1)) { + THD *thd= ha_thd(); /* Log error to log before we crash or if extended warnings are requested */ errflag|= ME_NOREFRESH; + if (thd && thd->is_optimistic_slave_worker()) + errflag|= ME_LOG_AS_WARN; } - } + } /* if we got an OS error from a file-based engine, specify a path of error */ if (error < HA_ERR_FIRST && bas_ext()[0]) diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 5f954f7576d..5bf5e3f73fc 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3517,6 +3517,16 @@ void my_message_sql(uint error, const char *str, myf MyFlags) level= Sql_condition::WARN_LEVEL_WARN; func= sql_print_warning; } + else if (MyFlags & ME_LOG_AS_WARN) + { + /* + Typical use case is optimistic parallel slave where DA needs to hold + an error condition caused by the current error, but the error-log + level is relaxed to the warning one. + */ + level= Sql_condition::WARN_LEVEL_ERROR; + func= sql_print_warning; + } else { level= Sql_condition::WARN_LEVEL_ERROR; diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 24140246b96..37f29115171 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -7100,6 +7100,13 @@ bool THD::rgi_have_temporary_tables() return rgi_slave->rli->save_temporary_tables != 0; } +bool THD::is_optimistic_slave_worker() +{ + DBUG_ASSERT(system_thread != SYSTEM_THREAD_SLAVE_SQL || rgi_slave); + + return system_thread == SYSTEM_THREAD_SLAVE_SQL && rgi_slave && + rgi_slave->speculation == rpl_group_info::SPECULATE_OPTIMISTIC; +} void wait_for_commit::reinit() diff --git a/sql/sql_class.h b/sql/sql_class.h index ca6155ec93f..0ced84791a0 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4167,6 +4167,12 @@ class THD :public Statement, (THD_TRANS::DID_WAIT | THD_TRANS::CREATED_TEMP_TABLE | THD_TRANS::DROPPED_TEMP_TABLE | THD_TRANS::DID_DDL)); } + + /* + Returns true when the thread handle belongs to a slave worker thread + running in the optimistic execution mode. + */ + bool is_optimistic_slave_worker(); };

1 0

[Commits] 91ed8f78228: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's
by andrei.elkin＠pp.inet.fi 11 Jun '18

11 Jun '18

revision-id: 91ed8f7822894ec8ea493a6168010e381c97d841 (mariadb-10.1.33-31-g91ed8f78228) parent(s): 3b7da8a44c8a0ff4b40b37e4db01f7e397aefab5 author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-06-11 11:42:40 +0300 message: MDEV-13577 slave_parallel_mode=optimistic should not report the mode's specific temporary errors The optimistic parallel slave's worker thread could face a run-time error due to the algorithm's specifics which allows for conflicts like the reported "Can't find record in 'table'". A typical stack is like {noformat} #0 handler::print_error (this=0x61c00008f8a0, error=149, errflag=0) at handler.cc:3650 #1 0x0000555555e95361 in write_record (thd=thd@entry=0x62a0000a2208, table=table@entry=0x61f00008ce88, info=info@entry=0x7fffdee356d0) at sql_insert.cc:1944 #2 0x0000555555ea7767 in mysql_insert (thd=thd@entry=0x62a0000a2208, table_list=0x61b00012ada0, fields=..., values_list=..., update_fields=..., update_values=..., duplic=<optimized out>, ignore=<optimized out>) at sql_insert.cc:1039 #3 0x0000555555efda90 in mysql_execute_command (thd=thd@entry=0x62a0000a2208) at sql_parse.cc:3927 #4 0x0000555555f0cc50 in mysql_parse (thd=0x62a0000a2208, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at sql_parse.cc:7449 #5 0x00005555566d4444 in Query_log_event::do_apply_event (this=0x61200005b9c8, rgi=<optimized out>, query_arg=<optimized out>, q_len_arg=<optimized out>) at log_event.cc:4508 #6 0x00005555566d639e in Query_log_event::do_apply_event (this=<optimized out>, rgi=<optimized out>) at log_event.cc:4185 #7 0x0000555555d738cf in Log_event::apply_event (rgi=0x61d0001ea080, this=0x61200005b9c8) at log_event.h:1343 #8 apply_event_and_update_pos_apply (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080, reason=<optimized out>) at slave.cc:3479 #9 0x0000555555d8596b in apply_event_and_update_pos_for_parallel (ev=ev@entry=0x61200005b9c8, thd=thd@entry=0x62a0000a2208, rgi=rgi@entry=0x61d0001ea080) at slave.cc:3623 #10 0x00005555562aca83 in rpt_handle_event (qev=qev@entry=0x6190000fa088, rpt=rpt@entry=0x62200002bd68) at rpl_parallel.cc:50 #11 0x00005555562bd04e in handle_rpl_parallel_thread (arg=arg@entry=0x62200002bd68) at rpl_parallel.cc:1258 {noformat} Here {{handler::print_error}} computes whether to error log the current error when --log-warnings > 1. The decision flag is consulted bu {{my_message_sql()}} which can be eventually called. In the bug case the decision is to log. However in the optimistic mode slave applier case any conflict is attempted to resolve with rollback and retry to success. Hence the logging is at least extraneous. The case is fixed with adding a new flag {{ME_LOG_AS_WARN}} which {{handler::print_error}} may propagate further on through {{my_error}} when the error comes from an optimistically running slave worker thread. The new flag effectively requests the warning level for the errlog record, while the thread's DA records the actual error (which is regarded as temporary one by the parallel slave error handler). --- include/my_sys.h | 1 + .../suite/rpl/r/rpl_parallel_optimistic.result | 23 ++++++++ .../suite/rpl/t/rpl_parallel_optimistic.test | 64 +++++++++++++++++++++- sql/handler.cc | 4 +- sql/mysqld.cc | 10 ++++ sql/sql_class.cc | 7 +++ sql/sql_class.h | 6 ++ 7 files changed, 113 insertions(+), 2 deletions(-) diff --git a/include/my_sys.h b/include/my_sys.h index 110a2ee9af3..1c5649812d1 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -112,6 +112,7 @@ typedef struct my_aio_result { #define ME_JUST_INFO 1024 /**< not error but just info */ #define ME_JUST_WARNING 2048 /**< not error but just warning */ #define ME_FATALERROR 4096 /* Fatal statement error */ +#define ME_LOG_AS_WARN 8192 /* is error but error-logged as warning */ /* Bits in last argument to fn_format */ #define MY_REPLACE_DIR 1 /* replace dir in name with 'dir' */ diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result index 0177e65b10f..a6da3399fab 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result @@ -1,4 +1,6 @@ include/rpl_init.inc [topology=1->2] +call mtr.add_suppression("Warning.*Deadlock found when trying to get lock; try restarting transaction"); +call mtr.add_suppression("Warning.*mysqld: Can't find record in 't2'"); ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; @@ -543,6 +545,27 @@ a b 57 7 58 8 59 9 +DELETE FROM t1; +DELETE FROM t2; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc +BEGIN; +INSERT INTO t1 SET a=1; +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=row; +BEGIN; +INSERT INTO t1 SET a=1; +INSERT INTO t2 SET a=1; +COMMIT; +BEGIN; +DELETE FROM t2; +COMMIT; +ROLLBACK; +SET @@SESSION.binlog_format= @save.binlog_format; +DELETE FROM t1; +DELETE FROM t2; +include/save_master_gtid.inc +include/sync_with_master_gtid.inc include/stop_slave.inc SET GLOBAL slave_parallel_mode=@old_parallel_mode; SET GLOBAL slave_parallel_threads=@old_parallel_threads; diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test index 41fb6ebb72e..28bf4a77fd4 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test @@ -4,6 +4,11 @@ --let $rpl_topology=1->2 --source include/rpl_init.inc +--connection server_2 +call mtr.add_suppression("Warning.*Deadlock found when trying to get lock; try restarting transaction"); +# The following instruction is a part of the proof of MDEV-13577 fixes, below. +call mtr.add_suppression("Warning.*mysqld: Can't find record in 't2'"); + --connection server_1 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; CREATE TABLE t1 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; @@ -480,8 +485,65 @@ SELECT * FROM t2 WHERE a >= 40 ORDER BY a; SELECT * FROM t1 WHERE a >= 40 ORDER BY a; SELECT * FROM t2 WHERE a >= 40 ORDER BY a; -# Clean up. +# partial cleanup to reuse the tables by following tests +--connection server_1 +DELETE FROM t1; +DELETE FROM t2; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc +# +# MDEV-13577 optimistic parallel slave errors out to error log unnecessary +# + +# The 1st of the following two trx:s a blocker on slave +--connection server_2 +BEGIN; +INSERT INTO t1 SET a=1; + +--connection server_1 +SET @save.binlog_format=@@session.binlog_format; +SET @@SESSION.binlog_format=row; + +BEGIN; + INSERT INTO t1 SET a=1; + INSERT INTO t2 SET a=1; +COMMIT; + +# This transaction is going to win optimistical race with above INSERT +# on slave while being depend on it. That means it will face a kind of temporary error +# and then will retry to succeed. +BEGIN; + DELETE FROM t2; +COMMIT; + +# First make sure DELETE raced indeed to get stuck at retrying stage +# where it runs "realistically" now. There is nomore optimistic error +# in the errorlog, which is downgraded to the warning level (when +# --log-warnings > 1), see above suppression. +--connection server_2 +--let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" +--source include/wait_condition.inc + +# Next release the 1st trx to commit. +--connection server_2 +ROLLBACK; + +# MDEV-13577 local cleanup: +--connection server_1 +SET @@SESSION.binlog_format= @save.binlog_format; +DELETE FROM t1; +DELETE FROM t2; +--source include/save_master_gtid.inc + +--connection server_2 +--source include/sync_with_master_gtid.inc + +# +# Clean up. +# --connection server_2 --source include/stop_slave.inc SET GLOBAL slave_parallel_mode=@old_parallel_mode; diff --git a/sql/handler.cc b/sql/handler.cc index 397891ceec5..dc12a006049 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3643,8 +3643,10 @@ void handler::print_error(int error, myf errflag) Log error to log before we crash or if extended warnings are requested */ errflag|= ME_NOREFRESH; + if (ha_thd()->is_optimistic_slave_worker()) + errflag|= ME_LOG_AS_WARN; } - } + } /* if we got an OS error from a file-based engine, specify a path of error */ if (error < HA_ERR_FIRST && bas_ext()[0]) diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 5f954f7576d..5bf5e3f73fc 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3517,6 +3517,16 @@ void my_message_sql(uint error, const char *str, myf MyFlags) level= Sql_condition::WARN_LEVEL_WARN; func= sql_print_warning; } + else if (MyFlags & ME_LOG_AS_WARN) + { + /* + Typical use case is optimistic parallel slave where DA needs to hold + an error condition caused by the current error, but the error-log + level is relaxed to the warning one. + */ + level= Sql_condition::WARN_LEVEL_ERROR; + func= sql_print_warning; + } else { level= Sql_condition::WARN_LEVEL_ERROR; diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 24140246b96..37f29115171 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -7100,6 +7100,13 @@ bool THD::rgi_have_temporary_tables() return rgi_slave->rli->save_temporary_tables != 0; } +bool THD::is_optimistic_slave_worker() +{ + DBUG_ASSERT(system_thread != SYSTEM_THREAD_SLAVE_SQL || rgi_slave); + + return system_thread == SYSTEM_THREAD_SLAVE_SQL && rgi_slave && + rgi_slave->speculation == rpl_group_info::SPECULATE_OPTIMISTIC; +} void wait_for_commit::reinit() diff --git a/sql/sql_class.h b/sql/sql_class.h index ca6155ec93f..0ced84791a0 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4167,6 +4167,12 @@ class THD :public Statement, (THD_TRANS::DID_WAIT | THD_TRANS::CREATED_TEMP_TABLE | THD_TRANS::DROPPED_TEMP_TABLE | THD_TRANS::DID_DDL)); } + + /* + Returns true when the thread handle belongs to a slave worker thread + running in the optimistic execution mode. + */ + bool is_optimistic_slave_worker(); };

1 0

[Commits] f72361e0734: MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY
by varunraiko1803＠gmail.com 09 Jun '18

09 Jun '18

revision-id: f72361e07346d142c4f3dda482b441c086c626f2 (mariadb-10.0.30-372-gf72361e0734) parent(s): 3a724800eebf78bd179bd1a2637ebe0a175e84d6 author: Varun Gupta committer: Varun Gupta timestamp: 2018-06-10 02:16:12 +0530 message: MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY The issue in this case is that we take in account the estimates from quick keys instead of rec_per_key. The estimates for quick keys are better than rec_per_key only if we have ref(const), so we need to check that all keyparts in the ref key are of the type ref(const). Also we need to make sure that the # of keyparts in ref and quick keys are same. --- mysql-test/r/order_by_innodb.result | 48 +++++++++++++++++++++++++++++++++++++ mysql-test/t/order_by_innodb.test | 34 +++++++++++++++++++++++++- sql/sql_select.cc | 18 +++++++++++++- 3 files changed, 98 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 3c6c4053741..d98e89c730a 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -11,3 +11,51 @@ a b c d 8 NULL 9 NULL 8 NULL 10 NULL DROP TABLE t1; +# +# MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY +# +create table t1(a int) engine=innodb; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2( +id int primary key, +key1 int, +col1 int, +key(key1)) engine=innodb; +insert into t2 select A.a + B.a*10 + C.a*100 + D.a* 1000,A.a + 10*B.a, 123456 +from t1 A, t1 B, t1 C, t1 D; +alter table t2 add key2 int; +update t2 set key2=key1; +alter table t2 add key(key2); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain select +(SELECT +concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # +2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a # Using index condition; Using where; Using filesort +select(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +9900-0-123456 +9901-1-123456 +9902-2-123456 +9903-3-123456 +9904-4-123456 +9905-5-123456 +9906-6-123456 +9907-7-123456 +9908-8-123456 +9909-9-123456 +drop table t1,t2; diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index c20eaceb053..3dad752b16b 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -18,6 +18,38 @@ INSERT INTO t1 (a,c) VALUES (20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28); SELECT * FROM t1 WHERE a = 8 AND (b = 1 OR b IS NULL) ORDER BY c; - DROP TABLE t1; +--echo # +--echo # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY +--echo # + +create table t1(a int) engine=innodb; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2( +id int primary key, +key1 int, +col1 int, +key(key1)) engine=innodb; +insert into t2 select A.a + B.a*10 + C.a*100 + D.a* 1000,A.a + 10*B.a, 123456 +from t1 A, t1 B, t1 C, t1 D; + +alter table t2 add key2 int; +update t2 set key2=key1; +alter table t2 add key(key2); +analyze table t2; + +--replace_column 9 # +explain select +(SELECT +concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +select(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +drop table t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f8435eca995..c6ab451501d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25117,7 +25117,23 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, */ if (ref_key >= 0 && tab->type == JT_REF) { - if (table->quick_keys.is_set(ref_key)) + bool all_ref_parts_const= TRUE; + /* + For all the parts of the ref key we check if all of them belong + to the type ref(const). This is done because if all parts of the ref + key are of type ref(const), then we are sure that the estimates + provides by quick keys is better than that provide by rec_per_key. + */ + for (uint key_part=0; key_part < tab->ref.key_parts; key_part++) + { + if (!(tab->ref.const_ref_part_map & (key_part_map(1) << key_part))) + { + all_ref_parts_const= FALSE; + break; + } + } + if (all_ref_parts_const && table->quick_keys.is_set(ref_key) && + table->quick_key_parts[ref_key] == tab->ref.key_parts) refkey_rows_estimate= table->quick_rows[ref_key]; else {

1 0

[Commits] 3a724800eeb: MDEV-15247: Crash when SET NAMES 'utf8' is set
by varunraiko1803＠gmail.com 09 Jun '18

09 Jun '18

revision-id: 3a724800eebf78bd179bd1a2637ebe0a175e84d6 (mariadb-10.0.30-371-g3a724800eeb) parent(s): cd33280b682692f0517a26178d7b5337db648751 author: Varun Gupta committer: Varun Gupta timestamp: 2018-06-09 18:52:50 +0530 message: MDEV-15247: Crash when SET NAMES 'utf8' is set In this case we are accessing incorrect memory when we have mergeable semi-joins. In the case when we have mergeable semi joins parent select will have a table count of all the tables in that select plus all the tables involved in the IN-subquery. But this table count does not include the "sjm table" (only includes the inner and outer tables) denotes as <subquery#> in explain. --- mysql-test/r/subselect_sj2_mat.result | 90 +++++++++++++++++++++++++++++++++++ mysql-test/t/subselect_sj2_mat.test | 89 ++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 4 +- 3 files changed, 182 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 5d0c64e9ddb..ff40d908c63 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1634,3 +1634,93 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1,t2; +# +# MDEV-15247: Crash when SET NAMES 'utf8' is set +# +CREATE TABLE t1 ( +id_category int unsigned, +id_product int unsigned, +PRIMARY KEY (id_category,id_product) +) ENGINE=MyISAM; +INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102); +CREATE TABLE t2 ( +id_product int, +id_t2 int, +KEY id_t2 (id_t2), +KEY id_product (id_product) +) ENGINE=MyISAM; +INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32), +(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26), +(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32), +(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19), +(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19), +(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20), +(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32), +(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19), +(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16), +(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31), +(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24), +(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19), +(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31), +(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32), +(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26), +(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22), +(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19), +(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32), +(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30), +(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23), +(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19), +(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15), +(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33), +(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18), +(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15), +(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19); +CREATE TABLE t3 ( +id_product int unsigned, +PRIMARY KEY (id_product) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES +(102),(103),(104),(105),(106),(107),(108),(109),(110), +(315371),(315373),(315374),(315375),(315376),(315377), +(315378),(315379),(315380); +CREATE TABLE t4 ( +id_product int not null, +id_shop int, +PRIMARY KEY (id_product,id_shop) +) ENGINE=MyISAM ; +INSERT INTO t4 VALUES +(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1), +(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1), +(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1), +(177,1),(176,1),(126,1),(315380,1); +CREATE TABLE t5 (id_product int) ENGINE=MyISAM; +INSERT INTO `t5` VALUES +(652),(668),(669),(670),(671),(673),(674),(675),(676), +(677),(679),(680),(681),(682),(683),(684),(685),(686); +explain +SELECT * FROM t3 +JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1) +JOIN t1 ON (t1.id_product = t3.id_product) +LEFT JOIN t5 ON (t5.id_product = t3.id_product) +WHERE 1=1 +AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32) +AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15) +AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19) +AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) +AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.id_product 1 Using index +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t1.id_product,const 1 Using where; Using index +1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) +5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where +4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 32 Using index condition; Using where +3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 +2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 50 +6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 30 Using index condition; Using where +drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index cfb6c8c2819..4b0394e617a 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -303,3 +303,92 @@ eval $q; eval explain $q; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-15247: Crash when SET NAMES 'utf8' is set +--echo # + +CREATE TABLE t1 ( + id_category int unsigned, + id_product int unsigned, + PRIMARY KEY (id_category,id_product) +) ENGINE=MyISAM; + +INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102); + +CREATE TABLE t2 ( + id_product int, + id_t2 int, + KEY id_t2 (id_t2), + KEY id_product (id_product) +) ENGINE=MyISAM; + +INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32), +(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26), +(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32), +(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19), +(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19), +(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20), +(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32), +(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19), +(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16), +(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31), +(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24), +(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19), +(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31), +(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32), +(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26), +(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22), +(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19), +(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32), +(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30), +(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23), +(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19), +(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15), +(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33), +(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18), +(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15), +(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19); + + +CREATE TABLE t3 ( + id_product int unsigned, + PRIMARY KEY (id_product) +) ENGINE=MyISAM; + +INSERT INTO t3 VALUES +(102),(103),(104),(105),(106),(107),(108),(109),(110), +(315371),(315373),(315374),(315375),(315376),(315377), +(315378),(315379),(315380); + + +CREATE TABLE t4 ( + id_product int not null, + id_shop int, + PRIMARY KEY (id_product,id_shop) +) ENGINE=MyISAM ; + +INSERT INTO t4 VALUES +(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1), +(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1), +(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1), +(177,1),(176,1),(126,1),(315380,1); + +CREATE TABLE t5 (id_product int) ENGINE=MyISAM; +INSERT INTO `t5` VALUES +(652),(668),(669),(670),(671),(673),(674),(675),(676), +(677),(679),(680),(681),(682),(683),(684),(685),(686); + +explain +SELECT * FROM t3 + JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1) + JOIN t1 ON (t1.id_product = t3.id_product) +LEFT JOIN t5 ON (t5.id_product = t3.id_product) +WHERE 1=1 + AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32) + AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15) + AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19) + AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) + AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); + +drop table t1,t2,t3,t4,t5; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1f15d5bcec0..f8435eca995 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9584,7 +9584,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) table_map current_map; i= join->const_tables; for (tab= first_depth_first_tab(join); tab; - tab= next_depth_first_tab(join, tab), i++) + tab= next_depth_first_tab(join, tab)) { bool is_hj; /* @@ -10055,6 +10055,8 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) } first_inner_tab= first_inner_tab->first_upper; } + if (!tab->bush_children) + i++; } } DBUG_RETURN(0);

1 0