- commits - lists.mariadb.org

[Commits] b528b06: MDEV-16710, Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction
by psergey＠askmonty.org 13 Jul '18

13 Jul '18

revision-id: b528b069a169d6e3d9ef4f9c7de68ead3e29186f parent(s): af1568668f6a910b5739fe1d7181c59f94a95196 committer: Sergei Petrunia branch nick: 10.2-r12-new-submodule timestamp: 2018-07-13 16:23:04 +0300 message: MDEV-16710, Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction rollback_to_stmt_savepoint() calls do_rollback_to_savepoint(). This removes the changes made by this statement, and also removes the set savepoint. Before we start processing the next statement, we need to set the new savepoint, so we will have something to rollback to if the next statement fails. Since rollback_to_stmt_savepoint always sets a new savepoint now, m_n_savepoints is now redundant and is removed. --- storage/rocksdb/ha_rocksdb.cc | 16 +++++++-------- .../mysql-test/rocksdb/r/transaction.result | 17 ++++++++++++++++ .../rocksdb/mysql-test/rocksdb/t/transaction.test | 23 ++++++++++++++++++++++ 3 files changed, 48 insertions(+), 8 deletions(-) diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index bd2e0bf..25bab1d 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -1918,8 +1918,6 @@ class Rdb_transaction { bool m_is_two_phase = false; private: - /* Number of RockDB savepoints taken */ - int m_n_savepoints; /* Number of write operations this transaction had when we took the last savepoint (the idea is not to take another savepoint if we haven't made @@ -2493,7 +2491,6 @@ class Rdb_transaction { entire transaction. */ do_set_savepoint(); - m_n_savepoints= 1; m_writes_at_last_savepoint= m_write_count; } @@ -2510,7 +2507,6 @@ class Rdb_transaction { { do_set_savepoint(); m_writes_at_last_savepoint= m_write_count; - m_n_savepoints++; } } @@ -2521,10 +2517,14 @@ class Rdb_transaction { void rollback_to_stmt_savepoint() { if (m_writes_at_last_savepoint != m_write_count) { do_rollback_to_savepoint(); - if (!--m_n_savepoints) { - do_set_savepoint(); - m_n_savepoints= 1; - } + /* + RollbackToSavePoint "removes the most recent SetSavePoint()", so + we need to set it again so that next statement can roll back to this + stage. + It's ok to do it here at statement end (instead of doing it at next + statement start) because setting a savepoint is cheap. + */ + do_set_savepoint(); m_writes_at_last_savepoint= m_write_count; } } diff --git a/storage/rocksdb/mysql-test/rocksdb/r/transaction.result b/storage/rocksdb/mysql-test/rocksdb/r/transaction.result index 006baaf..7664a11 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/transaction.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/transaction.result @@ -958,3 +958,20 @@ a rollback; drop function func; drop table t1,t2,t3; +# +# MDEV-16710: Slave SQL: Could not execute Update_rows_v1 event with RocksDB and triggers +# Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction +# +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=RocksDB; +CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW INSERT INTO non_existing_table VALUES (NULL); +BEGIN; +DELETE FROM t1; +INSERT INTO t2 VALUES (1); +INSERT INTO t2 VALUES (2); +# Must return empty result: +SELECT * FROM t1; +a +COMMIT; +drop table t1,t2; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/transaction.test b/storage/rocksdb/mysql-test/rocksdb/t/transaction.test index 3350db9..2503cbc 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/transaction.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/transaction.test @@ -133,3 +133,26 @@ rollback; drop function func; drop table t1,t2,t3; +--echo # +--echo # MDEV-16710: Slave SQL: Could not execute Update_rows_v1 event with RocksDB and triggers +--echo # Issue#857: MyRocks: Incorrect behavior when miltiple statements fail inside a transaction +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=RocksDB; + +CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW INSERT INTO non_existing_table VALUES (NULL); + +BEGIN; +DELETE FROM t1; +--error 0,ER_NO_SUCH_TABLE +INSERT INTO t2 VALUES (1); +--error 0,ER_NO_SUCH_TABLE +INSERT INTO t2 VALUES (2); +--echo # Must return empty result: +SELECT * FROM t1; +COMMIT; + +drop table t1,t2; +

1 0

[Commits] 13cb760c7cb: MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
by Varun 10 Jul '18

10 Jul '18

revision-id: 13cb760c7cbbe2420cc1ef1c68d7f5fde0081654 (mariadb-10.0.30-395-g13cb760c7cb) parent(s): a2c0376e08d80d7b7dad8713d1df334b2b81eff9 author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-11 02:36:00 +0530 message: MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed Backport the fix f214d365121 to 10.0 Author: Sergei Golubchik <serg(a)mariadb.org> Date: Tue Apr 17 00:44:34 2018 +0200 ASAN error in is_stat_table() don't memcmp beyond the first argument's end Also: use my_strcasecmp(table_alias_charset), like elsewhere, not memcmp --- mysql-test/r/stat_tables.result | 7 +++++++ mysql-test/r/stat_tables_innodb.result | 7 +++++++ mysql-test/t/stat_tables.test | 6 ++++++ sql/sql_statistics.cc | 4 ++-- 4 files changed, 22 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index fcced761283..4a608089d7d 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -516,4 +516,11 @@ use test; drop database db1; drop database db2; drop table t1; +# +# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed +# +SET use_stat_tables = PREFERABLY; +SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); +CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ) +NULL set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 0e866755532..87049b228e5 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -543,6 +543,13 @@ use test; drop database db1; drop database db2; drop table t1; +# +# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed +# +SET use_stat_tables = PREFERABLY; +SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); +CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ) +NULL set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/stat_tables.test b/mysql-test/t/stat_tables.test index 4cbaa9e27c8..9f94cf1b5a7 100644 --- a/mysql-test/t/stat_tables.test +++ b/mysql-test/t/stat_tables.test @@ -305,4 +305,10 @@ drop database db1; drop database db2; drop table t1; +--echo # +--echo # MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed +--echo # + +SET use_stat_tables = PREFERABLY; +SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index 1febc02b903..be4547a69df 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -3858,11 +3858,11 @@ bool is_stat_table(const char *db, const char *table) { DBUG_ASSERT(db && table); - if (!memcmp(db, stat_tables_db_name.str, stat_tables_db_name.length)) + if (!my_strcasecmp(table_alias_charset, db, stat_tables_db_name.str)) { for (uint i= 0; i < STATISTICS_TABLES; i ++) { - if (!memcmp(table, stat_table_name[i].str, stat_table_name[i].length)) + if (!my_strcasecmp(table_alias_charset, table, stat_table_name[i].str)) return true; } }

1 0

[Commits] 12c8e0a: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server.
by holyfoot＠askmonty.org 09 Jul '18

09 Jul '18

revision-id: 12c8e0a8f718c39fc82750f06337ca8da8c789ab (mariadb-10.3.6-45-g12c8e0a) parent(s): aa01f51bdef9cc38d8e0a75ea9e2788651e41d16 committer: Alexey Botchkov timestamp: 2018-07-10 00:29:48 +0400 message: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server. Scripts added to set safe permissions for the auth_pam_tool and it's directory. --- plugin/auth_pam/CMakeLists.txt | 7 ++++++- scripts/mysql_install_db.sh | 20 ++++++++++++++++++++ 2 files changed, 26 insertions(+), 1 deletion(-) diff --git a/plugin/auth_pam/CMakeLists.txt b/plugin/auth_pam/CMakeLists.txt index 4943d57..fbf0979 100644 --- a/plugin/auth_pam/CMakeLists.txt +++ b/plugin/auth_pam/CMakeLists.txt @@ -11,7 +11,12 @@ IF(HAVE_PAM_APPL_H) ADD_DEFINITIONS(-D_GNU_SOURCE) MYSQL_ADD_PLUGIN(auth_pam_v1 auth_pam_v1.c LINK_LIBRARIES pam MODULE_ONLY) MYSQL_ADD_PLUGIN(auth_pam auth_pam.c LINK_LIBRARIES pam dl MODULE_ONLY) - MYSQL_ADD_EXECUTABLE(auth_pam_tool auth_pam_tool.c DESTINATION ${INSTALL_PLUGINDIR}/auth_pam_tool_dir COMPONENT Server) + MYSQL_ADD_EXECUTABLE(auth_pam_tool auth_pam_tool.c DESTINATION ${INSTALL_PLUGINDIR}/auth_pam_tool_dir COMPONENT Server) TARGET_LINK_LIBRARIES(auth_pam_tool pam) + INSTALL(CODE "EXECUTE_PROCESS( + COMMAND chmod u=rwx,g=,o= auth_pam_tool_dir + COMMAND chmod u=rwxs,g=rx,o=rx auth_pam_tool_dir/auth_pam_tool + WORKING_DIRECTORY \$ENV{DESTDIR}\${CMAKE_INSTALL_PREFIX}/${INSTALL_PLUGINDIR}/)" + COMPONENT Server) ENDIF(HAVE_PAM_APPL_H) diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh index ad7c028..ea5507f 100644 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@ -308,6 +308,7 @@ then srcpkgdatadir="$srcdir/scripts" buildpkgdatadir="$builddir/scripts" plugindir="$builddir/plugin/auth_socket" + pamtooldir="$builddir/plugin/auth_pam" elif test -n "$basedir" then bindir="$basedir/bin" # only used in the help text @@ -337,6 +338,7 @@ then exit 1 fi plugindir=`find_in_dirs --dir auth_socket.so $basedir/lib*/plugin $basedir/lib*/mysql/plugin` + pamtooldir=$plugindir else basedir="@prefix@" bindir="@bindir@" @@ -345,6 +347,7 @@ else srcpkgdatadir="@pkgdatadir@" buildpkgdatadir="@pkgdatadir@" plugindir="@pkgplugindir@" + pamtooldir="@pkgplugindir@" fi # Set up paths to SQL scripts required for bootstrap @@ -445,6 +448,23 @@ done if test -n "$user" then + chown $user "$pamtooldir/auth_pam_tool_dir" + if test $? -ne 0 + then + echo "Cannot change ownership of the '$pamtooldir/auth_pam_tool_dir' directory" + echo " to the '$user' user. Check that you have the necessary permissions and try again." + exit 1 + fi + if test -z "$srcdir" + then + chown 0 "$pamtooldir/auth_pam_tool_dir/auth_pam_tool" + if test $? -ne 0 + then + echo "Couldn't set an owner to '$pamtooldir/auth_pam_tool_dir/auth_pam_tool'." + echo " It must be root, the PAM authentication plugin doesn't work otherwise.." + echo + fi + fi args="$args --user=$user" fi

1 0

[Commits] 1a2748c: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server.
by holyfoot＠askmonty.org 09 Jul '18

09 Jul '18

revision-id: 1a2748ceb0e30770eebc97d82d7baa885b332c49 (mariadb-10.3.6-45-g1a2748c) parent(s): aa01f51bdef9cc38d8e0a75ea9e2788651e41d16 committer: Alexey Botchkov timestamp: 2018-07-09 13:20:42 +0400 message: MDEV-15473 Isolate/sandbox PAM modules, so that they can't crash the server. Scripts added to set safe permissions for the auth_pam_tool and it's directory. --- plugin/auth_pam/CMakeLists.txt | 7 ++++++- scripts/mysql_install_db.sh | 10 ++++++++++ 2 files changed, 16 insertions(+), 1 deletion(-) diff --git a/plugin/auth_pam/CMakeLists.txt b/plugin/auth_pam/CMakeLists.txt index 4943d57..fbf0979 100644 --- a/plugin/auth_pam/CMakeLists.txt +++ b/plugin/auth_pam/CMakeLists.txt @@ -11,7 +11,12 @@ IF(HAVE_PAM_APPL_H) ADD_DEFINITIONS(-D_GNU_SOURCE) MYSQL_ADD_PLUGIN(auth_pam_v1 auth_pam_v1.c LINK_LIBRARIES pam MODULE_ONLY) MYSQL_ADD_PLUGIN(auth_pam auth_pam.c LINK_LIBRARIES pam dl MODULE_ONLY) - MYSQL_ADD_EXECUTABLE(auth_pam_tool auth_pam_tool.c DESTINATION ${INSTALL_PLUGINDIR}/auth_pam_tool_dir COMPONENT Server) + MYSQL_ADD_EXECUTABLE(auth_pam_tool auth_pam_tool.c DESTINATION ${INSTALL_PLUGINDIR}/auth_pam_tool_dir COMPONENT Server) TARGET_LINK_LIBRARIES(auth_pam_tool pam) + INSTALL(CODE "EXECUTE_PROCESS( + COMMAND chmod u=rwx,g=,o= auth_pam_tool_dir + COMMAND chmod u=rwxs,g=rx,o=rx auth_pam_tool_dir/auth_pam_tool + WORKING_DIRECTORY \$ENV{DESTDIR}\${CMAKE_INSTALL_PREFIX}/${INSTALL_PLUGINDIR}/)" + COMPONENT Server) ENDIF(HAVE_PAM_APPL_H) diff --git a/scripts/mysql_install_db.sh b/scripts/mysql_install_db.sh index ad7c028..fd26228 100644 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@ -308,6 +308,7 @@ then srcpkgdatadir="$srcdir/scripts" buildpkgdatadir="$builddir/scripts" plugindir="$builddir/plugin/auth_socket" + pamtooldir="$builddir/plugin/auth_pam" elif test -n "$basedir" then bindir="$basedir/bin" # only used in the help text @@ -337,6 +338,7 @@ then exit 1 fi plugindir=`find_in_dirs --dir auth_socket.so $basedir/lib*/plugin $basedir/lib*/mysql/plugin` + pamtooldir=`find_in_dirs --dir auth_pam.so $basedir/lib*/plugin $basedir/lib*/mysql/plugin` else basedir="@prefix@" bindir="@bindir@" @@ -345,6 +347,7 @@ else srcpkgdatadir="@pkgdatadir@" buildpkgdatadir="@pkgdatadir@" plugindir="@pkgplugindir@" + pamtooldir="@pkgplugindir@" fi # Set up paths to SQL scripts required for bootstrap @@ -445,6 +448,13 @@ done if test -n "$user" then + chown $user "$pamtooldir/auth_pam_tool_dir" + if test $? -ne 0 + then + echo "Cannot change ownership of the '$pamtooldir\auth_pam_tool_dir' directory" + echo " to the '$user' user. Check that you have the necessary permissions and try again." + exit 1 + fi args="$args --user=$user" fi

1 0

[Commits] 6bc722b8530: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views
by Varun 09 Jul '18

09 Jul '18

revision-id: 6bc722b85308709b1df647d97ee83247061c3aa2 (mariadb-5.5.56-247-g6bc722b8530) parent(s): 90cb7212742e9ae3a63bd183e171c95bd12d559f author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-09 13:11:35 +0530 message: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views In this issue we are using derived_with_keys optimization and we are using these keys to do a hash join which is incorrect. We cannot create keys for dervied tables whose keyparts have types are of BLOB or TEXT type. TEXT or BLOB columns can only be indexed over a specified length. --- mysql-test/r/derived.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/t/derived.test | 21 +++++++++++++++++++++ sql/table.cc | 13 +++++++++++-- 3 files changed, 65 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 54c78dc9f6f..5debfaed5e3 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1023,6 +1023,7 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -1052,4 +1053,36 @@ i drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +# +# MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +# +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; # end of 5.5 diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index c5b792c8d4d..217203422e0 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -880,6 +880,7 @@ CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -902,5 +903,25 @@ call pr(2); drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +--echo # +--echo # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +--echo # + +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; --echo # end of 5.5 diff --git a/sql/table.cc b/sql/table.cc index 48aa445e1aa..f6152a36eef 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -6060,6 +6060,14 @@ void TABLE::create_key_part_by_field(KEY_PART_INFO *key_part_info, The function checks whether a possible key satisfies the constraints imposed on the keys of any temporary table. + We need to filter out BLOB columns here, because ref access optimizer creates + KEYUSE objects for equalities for non-key columns for two puproses: + 1. To discover possible keys for derived_with_keys optimization + 2. To do hash joins + For the purpose of #1, KEYUSE objects are not created for "blob_column=..." . + However, they might be created for #2. In order to catch that case, we filter + them out here. + @return TRUE if the key is valid @return FALSE otherwise */ @@ -6075,11 +6083,12 @@ bool TABLE::check_tmp_key(uint key, uint key_parts, { uint fld_idx= next_field_no(arg); reg_field= field + fld_idx; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB) + return FALSE; uint fld_store_len= (uint16) (*reg_field)->key_length(); if ((*reg_field)->real_maybe_null()) fld_store_len+= HA_KEY_NULL_LENGTH; - if ((*reg_field)->type() == MYSQL_TYPE_BLOB || - (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || + if ((*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || (*reg_field)->type() == MYSQL_TYPE_GEOMETRY) fld_store_len+= HA_KEY_BLOB_LENGTH; key_len+= fld_store_len;

1 0

[Commits] 6bc722b8530: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views
by Varun 09 Jul '18

09 Jul '18

revision-id: 6bc722b85308709b1df647d97ee83247061c3aa2 (mariadb-5.5.56-247-g6bc722b8530) parent(s): 90cb7212742e9ae3a63bd183e171c95bd12d559f author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-09 13:11:35 +0530 message: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views In this issue we are using derived_with_keys optimization and we are using these keys to do a hash join which is incorrect. We cannot create keys for dervied tables whose keyparts have types are of BLOB or TEXT type. TEXT or BLOB columns can only be indexed over a specified length. --- mysql-test/r/derived.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/t/derived.test | 21 +++++++++++++++++++++ sql/table.cc | 13 +++++++++++-- 3 files changed, 65 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 54c78dc9f6f..5debfaed5e3 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1023,6 +1023,7 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -1052,4 +1053,36 @@ i drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +# +# MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +# +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived2> hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; # end of 5.5 diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index c5b792c8d4d..217203422e0 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -880,6 +880,7 @@ CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -902,5 +903,25 @@ call pr(2); drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +--echo # +--echo # MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +--echo # + +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; --echo # end of 5.5 diff --git a/sql/table.cc b/sql/table.cc index 48aa445e1aa..f6152a36eef 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -6060,6 +6060,14 @@ void TABLE::create_key_part_by_field(KEY_PART_INFO *key_part_info, The function checks whether a possible key satisfies the constraints imposed on the keys of any temporary table. + We need to filter out BLOB columns here, because ref access optimizer creates + KEYUSE objects for equalities for non-key columns for two puproses: + 1. To discover possible keys for derived_with_keys optimization + 2. To do hash joins + For the purpose of #1, KEYUSE objects are not created for "blob_column=..." . + However, they might be created for #2. In order to catch that case, we filter + them out here. + @return TRUE if the key is valid @return FALSE otherwise */ @@ -6075,11 +6083,12 @@ bool TABLE::check_tmp_key(uint key, uint key_parts, { uint fld_idx= next_field_no(arg); reg_field= field + fld_idx; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB) + return FALSE; uint fld_store_len= (uint16) (*reg_field)->key_length(); if ((*reg_field)->real_maybe_null()) fld_store_len+= HA_KEY_NULL_LENGTH; - if ((*reg_field)->type() == MYSQL_TYPE_BLOB || - (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || + if ((*reg_field)->real_type() == MYSQL_TYPE_VARCHAR || (*reg_field)->type() == MYSQL_TYPE_GEOMETRY) fld_store_len+= HA_KEY_BLOB_LENGTH; key_len+= fld_store_len;

1 0

[Commits] f7c182924bf: MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union
by Varun 09 Jul '18

09 Jul '18

revision-id: f7c182924bfa680db2cbc1eeafca6da5debea948 (mariadb-10.0.30-395-gf7c182924bf) parent(s): a2c0376e08d80d7b7dad8713d1df334b2b81eff9 author: Varun Gupta committer: Varun Gupta timestamp: 2018-07-09 12:59:21 +0530 message: MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union For index merge union[or sort union], the estimates are not taken into account while calculating the selectivity of a condition. So instead of showing the estimates of the index merge union[or sort union], it shows estimates equal to all the records of the table. The fix for the issue is to include the selectivity of index merge union[or sort union] while calculating the selectivity of a condition --- mysql-test/r/index_merge_myisam.result | 50 ++++++++++++++++++++++++++++++++++ mysql-test/t/index_merge_myisam.test | 35 ++++++++++++++++++++++++ sql/opt_range.cc | 8 +++++- sql/opt_range.h | 2 +- sql/sql_select.cc | 2 +- 5 files changed, 94 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index b3beff5a967..6021011f27a 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1712,3 +1712,53 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY,c1,i,c2 NULL NULL NULL 69 Using where DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +# +# MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union +# +create table t0 +( +key1 int not null, +INDEX i1(key1) +); +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +set @d=8; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +insert into t0 select key1+@d from t0; +set @d=@d*2; +alter table t0 add key2 int not null, add index i2(key2); +alter table t0 add key3 int not null, add index i3(key3); +alter table t0 add key8 int not null, add index i8(key8); +update t0 set key2=key1,key3=key1,key8=1024-key1; +analyze table t0; +Table Op Msg_type Msg_text +test.t0 analyze status OK +set @optimizer_switch_save=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +key1 key2 key3 key8 +3 3 3 1021 +set optimizer_use_condition_selectivity=2; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +key1 key2 key3 key8 +3 3 3 1021 +set @@optimizer_switch= @optimizer_switch_save; +drop table t0; diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test index d265007431e..e345723a1fd 100644 --- a/mysql-test/t/index_merge_myisam.test +++ b/mysql-test/t/index_merge_myisam.test @@ -243,3 +243,38 @@ DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +--echo # +--echo # MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union +--echo # + +create table t0 +( + key1 int not null, + INDEX i1(key1) +); + +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +let $1=7; +set @d=8; +while ($1) +{ + eval insert into t0 select key1+@d from t0; + eval set @d=@d*2; + dec $1; +} +alter table t0 add key2 int not null, add index i2(key2); +alter table t0 add key3 int not null, add index i3(key3); +alter table t0 add key8 int not null, add index i8(key8); + +update t0 set key2=key1,key3=key1,key8=1024-key1; +analyze table t0; + +set @optimizer_switch_save=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +set optimizer_use_condition_selectivity=2; +explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5; +set @@optimizer_switch= @optimizer_switch_save; +drop table t0; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f1d84e5c623..63874dd26ea 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3491,7 +3491,7 @@ double records_in_column_ranges(PARAM *param, uint idx, TRUE otherwise */ -bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) +bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond, QUICK_SELECT_I *quick) { uint keynr; uint max_quick_key_parts= 0; @@ -3704,6 +3704,12 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) } + if (quick && (quick->get_type() == QUICK_SELECT_I::QS_TYPE_ROR_UNION || + quick->get_type() == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)) + { + table->cond_selectivity*= (quick->records/table_records); + } + bitmap_union(used_fields, &handled_columns); /* Check if we can improve selectivity estimates by using sampling */ diff --git a/sql/opt_range.h b/sql/opt_range.h index 54b15826d1b..bca446d5b9a 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -1051,7 +1051,7 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, table_map read_tables, COND *conds, bool allow_null_cond, int *error); -bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond); +bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond, QUICK_SELECT_I *quick); #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1ec6cca7c5b..7ef5cd6c77b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3994,7 +3994,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (join->thd->variables.optimizer_use_condition_selectivity > 1) calculate_cond_selectivity_for_table(join->thd, s->table, *s->on_expr_ref ? - *s->on_expr_ref : conds); + *s->on_expr_ref : conds, s->quick); if (s->table->reginfo.impossible_range) { impossible_range= TRUE;

1 0

[Commits] aa01f51bdef: Fix of feedback plugin.
by Oleksandr Byelkin 06 Jul '18

06 Jul '18

revision-id: aa01f51bdef9cc38d8e0a75ea9e2788651e41d16 (mariadb-10.3.6-44-gaa01f51bdef) parent(s): 0e531581e01a493a767a233992d6c1c9b5597c8b author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-06 16:26:19 +0200 message: Fix of feedback plugin. Assign "SELECT" to the table before it usage. --- plugin/feedback/sender_thread.cc | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/plugin/feedback/sender_thread.cc b/plugin/feedback/sender_thread.cc index 92f186a1e2a..b025879b6ee 100644 --- a/plugin/feedback/sender_thread.cc +++ b/plugin/feedback/sender_thread.cc @@ -117,11 +117,12 @@ static int prepare_for_fill(TABLE_LIST *tables) tables->init_one_table(&INFORMATION_SCHEMA_NAME, &tbl_name, 0, TL_READ); tables->schema_table= i_s_feedback; + tables->select_lex= thd->lex->first_select_lex(); + DBUG_ASSERT(tables->select_lex); tables->table= create_schema_table(thd, tables); if (!tables->table) return 1; - tables->select_lex= thd->lex->current_select; tables->table->pos_in_table_list= tables; return 0;

1 0

[Commits] 0e531581e01: A fix moved from sql_yacc.yy to sql_yacc_ora.yy.
by Oleksandr Byelkin 06 Jul '18

06 Jul '18

revision-id: 0e531581e01a493a767a233992d6c1c9b5597c8b (mariadb-10.3.6-43-g0e531581e01) parent(s): 6fae04ea7d17aab3e3ef856c9f6a2d45051b426e author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-06 10:33:00 +0200 message: A fix moved from sql_yacc.yy to sql_yacc_ora.yy. --- mysql-test/main/set_statement.result | 7 +++++++ mysql-test/main/set_statement.test | 7 +++++++ sql/sql_yacc_ora.yy | 9 +++++---- 3 files changed, 19 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/set_statement.result b/mysql-test/main/set_statement.result index c34e1171899..a12954927b6 100644 --- a/mysql-test/main/set_statement.result +++ b/mysql-test/main/set_statement.result @@ -1225,3 +1225,10 @@ set @rnd=1; select @rnd; @rnd 0 +create table t (a int); +SET sql_mode=ORACLE; +SET STATEMENT myisam_sort_buffer_size=800000 FOR OPTIMIZE TABLE t; +Table Op Msg_type Msg_text +test.t optimize status Table is already up to date +SET sql_mode=default; +drop table t; diff --git a/mysql-test/main/set_statement.test b/mysql-test/main/set_statement.test index cc361553cfd..e2a3288106b 100644 --- a/mysql-test/main/set_statement.test +++ b/mysql-test/main/set_statement.test @@ -1130,3 +1130,10 @@ while ($1) --enable_query_log --echo # @rnd should be 0 select @rnd; + +create table t (a int); +SET sql_mode=ORACLE; +SET STATEMENT myisam_sort_buffer_size=800000 FOR OPTIMIZE TABLE t; +SET sql_mode=default; +drop table t; + diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 28a93806469..8cd2ad598ea 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -13176,10 +13176,11 @@ table_list: table_name: table_ident { - if (unlikely(!Select->add_table_to_list(thd, $1, NULL, - TL_OPTION_UPDATING, - YYPS->m_lock_type, - YYPS->m_mdl_type))) + if (!thd->lex->current_select_or_default()-> + add_table_to_list(thd, $1, NULL, + TL_OPTION_UPDATING, + YYPS->m_lock_type, + YYPS->m_mdl_type)) MYSQL_YYABORT; } ;

1 0

[Commits] 6fae04ea7d1: Fixed compile warning of windows.
by Oleksandr Byelkin 06 Jul '18

06 Jul '18

revision-id: 6fae04ea7d17aab3e3ef856c9f6a2d45051b426e (mariadb-10.3.6-42-g6fae04ea7d1) parent(s): f9849766b340ddadfc77e2f6cec9d8424202d5d3 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-06 09:03:13 +0200 message: Fixed compile warning of windows. --- sql/item_subselect.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 7af5472d408..eef6755bc98 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -126,7 +126,7 @@ void Item_subselect::init(st_select_lex *select_lex, else engine= new subselect_single_select_engine(select_lex, result, this); } - DBUG_PRINT("info", ("engine: 0x%lx", (ulong)engine)); + DBUG_PRINT("info", ("engine: %p", engine)); DBUG_VOID_RETURN; }

1 0