- commits - lists.mariadb.org

[Commits] cb5952b5060: Merge branch '10.0' into bb-10.1-merge-sanja
by Oleksandr Byelkin 25 Jul '18

25 Jul '18

revision-id: cb5952b5060d089c021808fe1f1cf13f7eb88423 (mariadb-10.1.34-30-gcb5952b5060) parent(s): 57cde8ccd19675dc98e3cbacf0ef5c72cb188e49 9fbe360e9f7d41d169eb5abed22dc15f26f6c749 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-25 22:24:40 +0200 message: Merge branch '10.0' into bb-10.1-merge-sanja CMakeLists.txt | 6 ++ cmake/install_macros.cmake | 9 ++ cmake/mysql_add_executable.cmake | 3 + include/m_ctype.h | 28 +++--- mysql-test/include/ctype_mdev13118.inc | 15 ++++ mysql-test/r/ctype_binary.result | 23 +++++ mysql-test/r/ctype_eucjpms.result | 23 +++++ mysql-test/r/ctype_euckr.result | 29 ++++++ mysql-test/r/ctype_gbk.result | 23 +++++ mysql-test/r/ctype_latin1.result | 23 +++++ mysql-test/r/ctype_ucs.result | 23 +++++ mysql-test/r/ctype_ujis.result | 23 +++++ mysql-test/r/ctype_utf16.result | 23 +++++ mysql-test/r/ctype_utf16le.result | 29 ++++++ mysql-test/r/ctype_utf32.result | 23 +++++ mysql-test/r/ctype_utf8.result | 23 +++++ mysql-test/r/ctype_utf8mb4.result | 23 +++++ mysql-test/r/join_outer.result | 50 +++++++++++ mysql-test/r/join_outer_jcl6.result | 50 +++++++++++ mysql-test/r/rename.result | 4 + mysql-test/r/subselect_mat_cost_bugs.result | 2 +- mysql-test/suite/parts/r/truncate_locked.result | 7 ++ mysql-test/suite/parts/t/truncate_locked.test | 10 +++ mysql-test/suite/plugins/r/processlist.result | 4 +- mysql-test/suite/plugins/t/processlist.test | 8 +- mysql-test/t/bootstrap.test | 9 ++ mysql-test/t/ctype_binary.test | 3 + mysql-test/t/ctype_eucjpms.test | 2 + mysql-test/t/ctype_euckr.test | 11 +++ mysql-test/t/ctype_gbk.test | 3 + mysql-test/t/ctype_latin1.test | 3 + mysql-test/t/ctype_ucs.test | 4 + mysql-test/t/ctype_ujis.test | 4 + mysql-test/t/ctype_utf16.test | 5 ++ mysql-test/t/ctype_utf16le.test | 13 +++ mysql-test/t/ctype_utf32.test | 8 ++ mysql-test/t/ctype_utf8.test | 7 ++ mysql-test/t/ctype_utf8mb4.test | 8 ++ mysql-test/t/join_outer.test | 48 ++++++++++ mysql-test/t/rename.test | 7 ++ mysys/my_rename.c | 5 +- scripts/CMakeLists.txt | 2 + scripts/mysql_install_db.pl.in | 2 +- scripts/mysql_install_db.sh | 2 +- scripts/mysql_system_tables_fix.sql | 50 ++++++----- scripts/mysqld_multi.sh | 2 +- sql/item_strfunc.cc | 112 ++++++++++++------------ sql/share/CMakeLists.txt | 12 +-- sql/sql_select.cc | 4 +- sql/sql_table.cc | 2 + sql/sql_truncate.cc | 3 +- sql/tztime.cc | 2 +- storage/connect/filamvct.cpp | 5 -- strings/ctype-bin.c | 8 +- strings/ctype-euc_kr.c | 4 +- strings/ctype-mb.c | 100 +++------------------ strings/ctype-simple.c | 22 +++-- strings/ctype-ucs2.c | 78 +++++++++-------- strings/ctype-ujis.c | 8 +- strings/ctype-utf8.c | 20 +++-- support-files/CMakeLists.txt | 2 + 61 files changed, 799 insertions(+), 265 deletions(-) diff --cc CMakeLists.txt index 8d2efac89cb,5a9ec265de0..0b48ca20485 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@@ -177,11 -163,47 +177,17 @@@ IF(UNIX OPTION(WITH_VALGRIND "Valgrind instrumentation" OFF) ENDIF() OPTION (WITH_UNIT_TESTS "Compile MySQL with unit tests" ON) -MARK_AS_ADVANCED(CYBOZU BACKUP_TEST WITHOUT_SERVER DISABLE_SHARED) +MARK_AS_ADVANCED(CYBOZU) + IF (WITHOUT_SERVER) + SET (SKIP_COMPONENTS "Server|IniFiles|SuportFiles|Readme") + ELSE() + SET (SKIP_COMPONENTS "N-O-N-E") + ENDIF() + OPTION(NOT_FOR_DISTRIBUTION "Allow linking with GPLv2-incompatible system libraries. Only set it you never plan to distribute the resulting binaries" OFF) -include(CheckCSourceCompiles) -include(CheckCXXSourceCompiles) -# We need some extra FAIL_REGEX patterns -# Note that CHECK_C_SOURCE_COMPILES is a misnomer, it will also link. -MACRO (MY_CHECK_C_COMPILER_FLAG FLAG RESULT) - SET(SAVE_CMAKE_REQUIRED_FLAGS "${CMAKE_REQUIRED_FLAGS}") - SET(CMAKE_REQUIRED_FLAGS "${CMAKE_REQUIRED_FLAGS} ${FLAG}") - CHECK_C_SOURCE_COMPILES("int main(void) { return 0; }" ${RESULT} - FAIL_REGEX "argument unused during compilation" - FAIL_REGEX "unsupported .*option" - FAIL_REGEX "unknown .*option" - FAIL_REGEX "unrecognized .*option" - FAIL_REGEX "ignoring unknown option" - FAIL_REGEX "[Ww]arning: [Oo]ption" - ) - SET(CMAKE_REQUIRED_FLAGS "${SAVE_CMAKE_REQUIRED_FLAGS}") -ENDMACRO() - -MACRO (MY_CHECK_CXX_COMPILER_FLAG FLAG RESULT) - SET(SAVE_CMAKE_REQUIRED_FLAGS "${CMAKE_REQUIRED_FLAGS}") - SET(CMAKE_REQUIRED_FLAGS "${CMAKE_REQUIRED_FLAGS} ${FLAG}") - CHECK_CXX_SOURCE_COMPILES("int main(void) { return 0; }" ${RESULT} - FAIL_REGEX "argument unused during compilation" - FAIL_REGEX "unsupported .*option" - FAIL_REGEX "unknown .*option" - FAIL_REGEX "unrecognized .*option" - FAIL_REGEX "ignoring unknown option" - FAIL_REGEX "[Ww]arning: [Oo]ption" - ) - SET(CMAKE_REQUIRED_FLAGS "${SAVE_CMAKE_REQUIRED_FLAGS}") -ENDMACRO() +INCLUDE(check_compiler_flag) OPTION(WITH_ASAN "Enable address sanitizer" OFF) IF (WITH_ASAN) diff --cc cmake/install_macros.cmake index 121825f8f3c,ff4ba593415..b0263e63b7a --- a/cmake/install_macros.cmake +++ b/cmake/install_macros.cmake @@@ -114,7 -115,17 +114,12 @@@ FUNCTION(INSTALL_SCRIPT SET(COMP) ENDIF() + IF (COMP MATCHES ${SKIP_COMPONENTS}) + RETURN() + ENDIF() + - INSTALL(FILES - ${script} - DESTINATION ${ARG_DESTINATION} - PERMISSIONS OWNER_READ OWNER_WRITE - OWNER_EXECUTE GROUP_READ GROUP_EXECUTE - WORLD_READ WORLD_EXECUTE ${COMP} - ) + INSTALL(PROGRAMS ${script} DESTINATION ${ARG_DESTINATION} ${COMP}) ++ INSTALL_MANPAGE(${script}) ENDFUNCTION() diff --cc include/m_ctype.h index ddb4c825e1b,eb2d760359b..74dd2a29984 --- a/include/m_ctype.h +++ b/include/m_ctype.h @@@ -369,35 -365,8 +369,35 @@@ typedef int (*my_charset_conv_mb_wc)(CH typedef int (*my_charset_conv_wc_mb)(CHARSET_INFO *, my_wc_t, uchar *, uchar *); typedef size_t (*my_charset_conv_case)(CHARSET_INFO *, - char *, size_t, char *, size_t); + const char *, size_t, char *, size_t); +/* + A structure to return the statistics of a native string copying, + when no Unicode conversion is involved. + + The stucture is OK to be unitialized before calling a copying routine. + A copying routine must populate the structure as follows: + - m_source_end_pos must be set by to a non-NULL value + in the range of the input string. + - m_well_formed_error_pos must be set to NULL if the string was + well formed, or to the position of the leftmost bad byte sequence. +*/ +typedef struct +{ + const char *m_source_end_pos; /* Position where reading stopped */ + const char *m_well_formed_error_pos; /* Position where a bad byte was found*/ +} MY_STRCOPY_STATUS; + + +/* + A structure to return the statistics of a Unicode string conversion. +*/ +typedef struct +{ + MY_STRCOPY_STATUS m_native_copy_status; + const char *m_cannot_convert_error_pos; +} MY_STRCONV_STATUS; + /* See strings/CHARSET_INFO.txt about information on this structure */ struct my_charset_handler_st diff --cc mysql-test/r/ctype_binary.result index 512368a2d26,b28d9040547..627ba8a35a5 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@@ -3047,6 -3022,29 +3047,29 @@@ DROP TABLE t1 SELECT _binary 0x7E, _binary X'7E', _binary B'01111110'; _binary 0x7E _binary X'7E' _binary B'01111110' ~ ~ ~ + SET NAMES utf8, character_set_connection=binary; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varbinary(10) NOT NULL DEFAULT '' ++ `t` varbinary(10) NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + abcdefghi-abcdefghi + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_eucjpms.result index f9cb4f1eecc,1e2312638c9..160c4758122 --- a/mysql-test/r/ctype_eucjpms.result +++ b/mysql-test/r/ctype_eucjpms.result @@@ -33868,6 -33636,29 +33868,29 @@@ HEX(a) CHAR_LENGTH(a DROP TABLE t1; SELECT _eucjpms 0x8EA0; ERROR HY000: Invalid eucjpms character string: '8EA0' + SET NAMES eucjpms; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET eucjpms NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET eucjpms NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_euckr.result index 90353c6af2a,cea93b0c808..a23ced76df9 --- a/mysql-test/r/ctype_euckr.result +++ b/mysql-test/r/ctype_euckr.result @@@ -25428,3 -25274,32 +25428,32 @@@ A1A1A1A1A1A1202020202020202020202020202 # # End of 5.6 tests # + # + # Start of 10.0 tests + # + SET NAMES utf8, character_set_connection=euckr; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET euckr NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET euckr NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; + # + # End of 10.0 tests + # diff --cc mysql-test/r/ctype_gbk.result index d10d5f4bf75,15f9bc6bfea..ac8de1e229c --- a/mysql-test/r/ctype_gbk.result +++ b/mysql-test/r/ctype_gbk.result @@@ -5099,6 -4943,29 +5099,29 @@@ E05C5 E05B DROP TABLE t1; # Start of ctype_E05C.inc + SET NAMES utf8, character_set_connection=gbk; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET gbk NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET gbk NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant # diff --cc mysql-test/r/ctype_latin1.result index 66c5a37750d,8913c8082c8..3b9b2633480 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@@ -7989,6 -7939,29 +7989,29 @@@ 0 DROP VIEW v1; DROP TABLE t1; + SET NAMES latin1; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) NOT NULL DEFAULT '' ++ `t` varchar(10) NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_ucs.result index 55caabbaa12,58fd308c7e2..9c598780edf --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@@ -5705,6 -5629,29 +5705,29 @@@ c YWJjZGVmZ2hp-YWJjZGVmZ2hp DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; + SET NAMES utf8, character_set_connection=ucs2; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET ucs2 NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_ujis.result index 61541ec7678,d4589f62ae5..9146356b5f9 --- a/mysql-test/r/ctype_ujis.result +++ b/mysql-test/r/ctype_ujis.result @@@ -26173,6 -25942,29 +26173,29 @@@ HEX(a) CHAR_LENGTH(a DROP TABLE t1; SELECT _ujis 0x8EA0; ERROR HY000: Invalid ujis character string: '8EA0' + SET NAMES ujis; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET ujis NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET ujis NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf16.result index c8ba1fedab8,e182432faec..ba72a859351 --- a/mysql-test/r/ctype_utf16.result +++ b/mysql-test/r/ctype_utf16.result @@@ -2138,6 -2134,29 +2138,29 @@@ EXECUTE stmt USING @arg00 CONCAT(_utf16'a' COLLATE utf16_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; + SET NAMES utf8, character_set_connection=utf16; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf16 NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET utf16 NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf16le.result index 93fce843dc8,f6a4d351ad4..c7c8c210014 --- a/mysql-test/r/ctype_utf16le.result +++ b/mysql-test/r/ctype_utf16le.result @@@ -2324,72 -2320,31 +2324,101 @@@ DFFFFFDFFFFF9CFFFF9DFFFF9EFFF # End of 5.6 tests # # + # Start of 10.0 tests + # + SET NAMES utf8, character_set_connection=utf16le; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf16le NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET utf16le NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; + # + # Start of 10.0 tests + # ++# +# Start of 10.1 tests +# +# +# MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character" +# +CREATE TABLE t1 ( +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, +a VARCHAR(10) CHARACTER SET utf16le, KEY(a,id) +); +INSERT INTO t1 (a) VALUES (_utf8mb4 0x61); +INSERT INTO t1 (a) VALUES (_utf8mb4 0xC280),(_utf8mb4 0xDFBF); +INSERT INTO t1 (a) VALUES (_utf8mb4 0xE0A080),(_utf8mb4 0xEFBFBF); +INSERT INTO t1 (a) VALUES (_utf8mb4 0xF0908080),(_utf8mb4 0xF48FBFBF); +SELECT id,HEX(a) FROM t1 ORDER BY a,id; +id HEX(a) +1 6100 +2 8000 +3 FF07 +4 0008 +6 00D800DC +7 FFDBFFDF +5 FFFF +SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC; +id HEX(a) +5 FFFF +7 FFDBFFDF +6 00D800DC +4 0008 +3 FF07 +2 8000 +1 6100 +SELECT COUNT(DISTINCT a) FROM t1; +COUNT(DISTINCT a) +6 +ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16le COLLATE utf16le_bin; +SELECT id,HEX(a) FROM t1 ORDER BY a; +id HEX(a) +1 6100 +2 8000 +3 FF07 +4 0008 +5 FFFF +6 00D800DC +7 FFDBFFDF +SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC; +id HEX(a) +7 FFDBFFDF +6 00D800DC +5 FFFF +4 0008 +3 FF07 +2 8000 +1 6100 +SELECT COUNT(DISTINCT a) FROM t1; +COUNT(DISTINCT a) +7 +DROP TABLE t1; +# +# MDEV-9178 Wrong result for CAST(CONVERT('1Ĳ3' USING ucs2) AS SIGNED) +# +SET NAMES utf8; +SELECT CAST(CONVERT('1Ĳ3' USING utf16le) AS SIGNED); +CAST(CONVERT('1Ĳ3' USING utf16le) AS SIGNED) +1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '1Ĳ3' +# +# End of 10.1 tests +# diff --cc mysql-test/r/ctype_utf32.result index 22b0e9c0fc0,91277cd5108..024f8aa7875 --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@@ -2237,6 -2231,29 +2237,29 @@@ EXECUTE stmt USING @arg00 CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; + SET NAMEs utf8, character_set_connection=utf32; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf32 NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET utf32 NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf8.result index 3da74d12877,6a4001597c4..7b486f0f443 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@@ -10278,6 -10127,29 +10278,29 @@@ SELECT * FROM v1 c ß DROP VIEW v1; + SET NAMES utf8; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET utf8 NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf8mb4.result index 904f432af20,4d91c42cf51..de9ba321ad9 --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@@ -3469,32 -3427,30 +3469,55 @@@ t1 CREATE TABLE `t1` ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; SET NAMES default; + SET NAMES utf8mb4; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' ++ `t` varchar(10) CHARACTER SET utf8mb4 NOT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; +# # End of 10.0 tests # -# End of tests +# +# Start of 10.1 tests +# +# +# MDEV-6572 "USE dbname" with a bad sequence erroneously connects to a wrong database +# +SET NAMES utf8mb4; +SELECT * FROM `test😁😁test`; +ERROR HY000: Invalid utf8mb4 character string: 'test\xF0\x9F\x98\x81\xF0\x9F\x98\x81test' +# +# MDEV-7231 Field ROUTINE_DEFINITION in INFORMATION_SCHEMA.`ROUTINES` contains broken procedure body when used shielding quotes inside. +# +SET NAMES utf8mb4; +CREATE FUNCTION f1() RETURNS TEXT CHARACTER SET utf8mb4 +RETURN CONCAT('😎','x😎','😎y','x😎y'); +SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA='test' AND SPECIFIC_NAME ='f1'; +ROUTINE_DEFINITION +RETURN CONCAT('?','x?','?y','x?y') +SELECT body_utf8 FROM mysql.proc WHERE name='f1'; +body_utf8 +RETURN CONCAT('?','x?','?y','x?y') +DROP FUNCTION f1; +# +# End of 10.1 tests # diff --cc mysql-test/t/ctype_utf16le.test index 42017f9a635,f5b89aa9dc4..5e29408cbd0 --- a/mysql-test/t/ctype_utf16le.test +++ b/mysql-test/t/ctype_utf16le.test @@@ -745,36 -745,15 +745,49 @@@ SET NAMES utf8, collation_connection=ut --echo # End of 5.6 tests --echo # + + --echo # + --echo # Start of 10.0 tests + --echo # + + + SET NAMES utf8, character_set_connection=utf16le; + --source include/ctype_mdev13118.inc + + --echo # + --echo # Start of 10.0 tests + --echo # ++ +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character" +--echo # +CREATE TABLE t1 ( + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + a VARCHAR(10) CHARACTER SET utf16le, KEY(a,id) +); +INSERT INTO t1 (a) VALUES (_utf8mb4 0x61); +INSERT INTO t1 (a) VALUES (_utf8mb4 0xC280),(_utf8mb4 0xDFBF); +INSERT INTO t1 (a) VALUES (_utf8mb4 0xE0A080),(_utf8mb4 0xEFBFBF); +INSERT INTO t1 (a) VALUES (_utf8mb4 0xF0908080),(_utf8mb4 0xF48FBFBF); +SELECT id,HEX(a) FROM t1 ORDER BY a,id; +SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC; +SELECT COUNT(DISTINCT a) FROM t1; +ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16le COLLATE utf16le_bin; +SELECT id,HEX(a) FROM t1 ORDER BY a; +SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC; +SELECT COUNT(DISTINCT a) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-9178 Wrong result for CAST(CONVERT('1Ĳ3' USING ucs2) AS SIGNED) +--echo # +SET NAMES utf8; +SELECT CAST(CONVERT('1Ĳ3' USING utf16le) AS SIGNED); + +--echo # +--echo # End of 10.1 tests +--echo # diff --cc mysql-test/t/ctype_utf8mb4.test index af4c169091a,89007546492..ffc098ff938 --- a/mysql-test/t/ctype_utf8mb4.test +++ b/mysql-test/t/ctype_utf8mb4.test @@@ -1950,33 -1913,16 +1950,41 @@@ DROP TABLE t1 SET NAMES default; + + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET NAMES utf8mb4; + --source include/ctype_mdev13118.inc + + +--echo # --echo # End of 10.0 tests +--echo # + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-6572 "USE dbname" with a bad sequence erroneously connects to a wrong database +--echo # +SET NAMES utf8mb4; +--error ER_INVALID_CHARACTER_STRING +SELECT * FROM `test😁😁test`; + +--echo # +--echo # MDEV-7231 Field ROUTINE_DEFINITION in INFORMATION_SCHEMA.`ROUTINES` contains broken procedure body when used shielding quotes inside. +--echo # +# Non-BMP characters should be replaced to '?' in ROUTINE_DEFINITION/body_utf8 +SET NAMES utf8mb4; +CREATE FUNCTION f1() RETURNS TEXT CHARACTER SET utf8mb4 +RETURN CONCAT('😎','x😎','😎y','x😎y'); +SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA='test' AND SPECIFIC_NAME ='f1'; +SELECT body_utf8 FROM mysql.proc WHERE name='f1'; +DROP FUNCTION f1; --echo # ---echo # End of tests +--echo # End of 10.1 tests --echo # diff --cc scripts/CMakeLists.txt index b994c9f67b5,24b48e1920e..9f320ce6aa4 --- a/scripts/CMakeLists.txt +++ b/scripts/CMakeLists.txt @@@ -74,23 -74,7 +74,24 @@@ IF(UNIX ) ENDIF() +# Configure two scripts from one 'in' file. +# The maria_add_gis_sp.sql - to be sent to 'mysql' tool +# and the maria_add_gis_sp_bootstrap.sql, that can be sent to +# the server as a bootstrap command. + +SET(ADD_GIS_SP_SET_DELIMITER "delimiter |") +SET(ADD_GIS_SP_RESET_DELIMITER "delimiter ;") +SET(ADD_GIS_SP_EOL "|") +CONFIGURE_FILE(${CMAKE_CURRENT_SOURCE_DIR}/maria_add_gis_sp.sql.in + ${CMAKE_CURRENT_BINARY_DIR}/maria_add_gis_sp.sql ESCAPE_QUOTES @ONLY) + +SET(ADD_GIS_SP_SET_DELIMITER "") +SET(ADD_GIS_SP_RESET_DELIMITER "") +SET(ADD_GIS_SP_EOL ";") +CONFIGURE_FILE(${CMAKE_CURRENT_SOURCE_DIR}/maria_add_gis_sp.sql.in + ${CMAKE_CURRENT_BINARY_DIR}/maria_add_gis_sp_bootstrap.sql ESCAPE_QUOTES @ONLY) + + IF (NOT WITHOUT_SERVER) INSTALL(FILES ${CMAKE_CURRENT_SOURCE_DIR}/mysql_system_tables.sql ${CMAKE_CURRENT_SOURCE_DIR}/mysql_system_tables_data.sql diff --cc scripts/mysql_install_db.pl.in index 9d2c1f6874a,2103afc1e63..68d47fed2ea --- a/scripts/mysql_install_db.pl.in +++ b/scripts/mysql_install_db.pl.in @@@ -326,13 -323,12 +326,13 @@@ elsif ( $opt->{basedir} } else { - $opt->{basedir} = '@prefix@'; - $bindir = '@bindir@'; - $extra_bindir = $bindir; - $mysqld = '@sbindir@/mysqld'; - $pkgdatadir = '@pkgdatadir@'; - $scriptdir = '@scriptdir@'; + $opt->{basedir} = '@prefix@'; + $bindir = '@bindir@'; + $extra_bindir = $bindir; - $mysqld = '@libexecdir@/mysqld'; ++ $mysqld = '@sbindir@/mysqld'; + $srcpkgdatadir = '@pkgdatadir@'; + $buildpkgdatadir = '@pkgdatadir@'; + $scriptdir = '@scriptdir@'; } unless ( $opt->{ldata} ) diff --cc scripts/mysql_install_db.sh index b57ac8bc85e,43ff4191e08..40fdf798d71 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@@ -343,9 -313,8 +343,9 @@@ els basedir="@prefix@" bindir="@bindir@" resolveip="$bindir/resolveip" - mysqld="@libexecdir@/mysqld" + mysqld="@sbindir@/mysqld" - pkgdatadir="@pkgdatadir@" + srcpkgdatadir="@pkgdatadir@" + buildpkgdatadir="@pkgdatadir@" fi # Set up paths to SQL scripts required for bootstrap diff --cc scripts/mysql_system_tables_fix.sql index 47459d58bd2,194b1615c2b..a982fc87ec9 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@@ -26,9 -26,8 +26,9 @@@ set sql_mode=''; set storage_engine=MyISAM; +set enforce_storage_engine=NULL; - ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; + ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; # Detect whether or not we had the Grant_priv column SET @hadGrantPriv:=0; @@@ -633,12 -638,12 +641,14 @@@ ALTER TABLE user MODIFY Create_tablespa UPDATE user SET Create_tablespace_priv = Super_priv WHERE @hadCreateTablespacePriv = 0; - ALTER TABLE user ADD plugin char(64) DEFAULT '', ADD authentication_string TEXT; + ALTER TABLE user ADD plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, + ADD authentication_string TEXT NOT NULL; + ALTER TABLE user MODIFY plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, + MODIFY authentication_string TEXT NOT NULL; ALTER TABLE user ADD password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; ALTER TABLE user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; +ALTER TABLE user ADD default_role char(80) binary DEFAULT '' NOT NULL; +ALTER TABLE user ADD max_statement_time decimal(12,6) DEFAULT 0 NOT NULL; - ALTER TABLE user MODIFY plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, MODIFY authentication_string TEXT NOT NULL; -- Somewhere above, we ran ALTER TABLE user .... CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin. -- we want password_expired column to have collation utf8_general_ci. ALTER TABLE user MODIFY password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; diff --cc support-files/CMakeLists.txt index dff4610d490,cc9eed6c7be..4ad3810e082 --- a/support-files/CMakeLists.txt +++ b/support-files/CMakeLists.txt @@@ -41,116 -41,61 +41,118 @@@ ELSE( SET(inst_location ${INSTALL_SUPPORTFILESDIR}) ENDIF() + IF (NOT WITHOUT_SERVER) FOREACH(inifile my-huge my-innodb-heavy-4G my-large my-medium my-small) - CONFIGURE_FILE(${CMAKE_CURRENT_SOURCE_DIR}/${inifile}.cnf.sh + CONFIGURE_FILE(${CMAKE_CURRENT_SOURCE_DIR}/${inifile}.cnf.sh ${CMAKE_CURRENT_BINARY_DIR}/${inifile}.${ini_file_extension} @ONLY) INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/${inifile}.${ini_file_extension} DESTINATION ${inst_location} COMPONENT IniFiles) ENDFOREACH() + ENDIF() +IF(WITH_WSREP) + CONFIGURE_FILE(${CMAKE_CURRENT_SOURCE_DIR}/wsrep.cnf.sh + ${CMAKE_CURRENT_BINARY_DIR}/wsrep.${ini_file_extension} @ONLY) + INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/wsrep.${ini_file_extension} + DESTINATION ${inst_location} COMPONENT IniFiles) +ENDIF() + IF(UNIX) SET(prefix ${CMAKE_INSTALL_PREFIX}) - FOREACH(script mysqld_multi.server mysql-log-rotate binary-configure) + FOREACH(script mysqld_multi.server mysql-log-rotate binary-configure wsrep_notify) CONFIGURE_FILE(${CMAKE_CURRENT_SOURCE_DIR}/${script}.sh ${CMAKE_CURRENT_BINARY_DIR}/${script} @ONLY ) - - IF(script MATCHES ".ini") - SET(comp IniFiles) - SET(permissions OWNER_READ OWNER_WRITE GROUP_READ WORLD_READ) - ELSE() - SET(comp Server_Scripts) - SET(permissions OWNER_READ OWNER_WRITE OWNER_EXECUTE GROUP_READ GROUP_EXECUTE WORLD_READ WORLD_EXECUTE) - ENDIF() - INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/${script} - DESTINATION ${inst_location} COMPONENT ${comp} - PERMISSIONS ${permissions}) + INSTALL(PROGRAMS ${CMAKE_CURRENT_BINARY_DIR}/${script} + DESTINATION ${inst_location} COMPONENT Server_Scripts) ENDFOREACH() + IF(INSTALL_SUPPORTFILESDIR) INSTALL(FILES magic DESTINATION ${inst_location} COMPONENT SupportFiles) - ADD_SUBDIRECTORY(SELinux) + INSTALL(DIRECTORY policy DESTINATION ${inst_location} COMPONENT SupportFiles) + FIND_PROGRAM(CHECKMODULE checkmodule) + FIND_PROGRAM(SEMODULE_PACKAGE semodule_package) + MARK_AS_ADVANCED(CHECKMODULE SEMODULE_PACKAGE) + + # Build pp files in policy/selinux + IF(CHECKMODULE AND SEMODULE_PACKAGE) + FOREACH(pol mariadb) + SET(src ${CMAKE_CURRENT_SOURCE_DIR}/policy/selinux/${pol}.te) + SET(tmp ${CMAKE_CURRENT_BINARY_DIR}${CMAKE_FILES_DIRECTORY}/${pol}-pp.dir/${pol}.mod) + SET(out ${CMAKE_CURRENT_BINARY_DIR}/${pol}.pp) + ADD_CUSTOM_COMMAND(OUTPUT ${out} + COMMAND ${CHECKMODULE} -M -m ${src} -o ${tmp} + COMMAND ${SEMODULE_PACKAGE} -m ${tmp} -o ${out} + DEPENDS ${src}) + ADD_CUSTOM_TARGET(${pol}-pp ALL DEPENDS ${out}) + INSTALL(FILES ${out} DESTINATION ${inst_location}/policy/selinux COMPONENT SupportFiles) + ENDFOREACH() + ENDIF() ENDIF() + CONFIGURE_FILE(mariadb.pc.in ${CMAKE_CURRENT_BINARY_DIR}/mariadb.pc @ONLY) + INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/mariadb.pc DESTINATION ${INSTALL_SHAREDIR}/pkgconfig COMPONENT Development) + INSTALL(FILES mysql.m4 DESTINATION ${INSTALL_SHAREDIR}/aclocal COMPONENT Development) - CONFIGURE_FILE(MySQL-shared-compat.spec.sh ${CMAKE_CURRENT_BINARY_DIR}/MySQL-shared-compat.spec @ONLY) - CONFIGURE_FILE(mysql.spec.sh ${CMAKE_CURRENT_BINARY_DIR}/mysql.spec @ONLY) - - SET(SPECFILENAME "mysql.${VERSION}.spec") - IF("${VERSION}" MATCHES "-ndb-") - STRING(REGEX REPLACE "^.*-ndb-" "" NDBVERSION "${VERSION}") - SET(SPECFILENAME "mysql-cluster-${NDBVERSION}.spec") - ENDIF() - CONFIGURE_FILE(mysql.spec.sh ${CMAKE_CURRENT_BINARY_DIR}/${SPECFILENAME} @ONLY) - CONFIGURE_FILE(MySQL-shared-compat.spec.sh ${CMAKE_CURRENT_BINARY_DIR}/MySQL-shared-compat.spec @ONLY) - - SET(bindir ${prefix}/${INSTALL_BINDIR}) - SET(sbindir ${prefix}/${INSTALL_SBINDIR}) - SET(scriptdir ${prefix}/${INSTALL_SCRIPTDIR}) - SET(libexecdir ${prefix}/${INSTALL_SBINDIR}) - SET(pkgdatadir ${prefix}/${INSTALL_MYSQLSHAREDIR}) + SET(bindir ${INSTALL_BINDIRABS}) + SET(sbindir ${INSTALL_SBINDIRABS}) + SET(scriptdir ${INSTALL_SCRIPTDIRABS}) + SET(libexecdir ${INSTALL_SBINDIRABS}) + SET(pkgdatadir ${INSTALL_MYSQLSHAREDIRABS}) + SET(sysconfdir ${INSTALL_SYSCONFDIR}) + SET(sysconf2dir ${INSTALL_SYSCONF2DIR}) CONFIGURE_FILE(${CMAKE_CURRENT_SOURCE_DIR}/mysql.server.sh ${CMAKE_CURRENT_BINARY_DIR}/mysql.server @ONLY) - INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/mysql.server - DESTINATION ${inst_location} COMPONENT SupportFiles - PERMISSIONS OWNER_READ OWNER_WRITE OWNER_EXECUTE GROUP_READ - GROUP_EXECUTE WORLD_READ WORLD_EXECUTE) + INSTALL(PROGRAMS ${CMAKE_CURRENT_BINARY_DIR}/mysql.server + DESTINATION ${inst_location} COMPONENT SupportFiles) + + IF(HAVE_SYSTEMD) + CONFIGURE_FILE(mariadb.service.in + ${CMAKE_CURRENT_BINARY_DIR}/mariadb.service @ONLY) + INSTALL(FILES use_galera_new_cluster.conf + ${CMAKE_CURRENT_BINARY_DIR}/mariadb.service + DESTINATION ${inst_location}/systemd COMPONENT SupportFiles) + + IF(INSTALL_SYSTEMD_SYSUSERSDIR) + CONFIGURE_FILE(sysusers.conf.in + ${CMAKE_CURRENT_BINARY_DIR}/sysusers.conf @ONLY) + INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/sysusers.conf + DESTINATION ${INSTALL_SYSTEMD_SYSUSERSDIR} COMPONENT Server) + ENDIF() + + IF(INSTALL_SYSTEMD_TMPFILESDIR) + get_filename_component(MYSQL_UNIX_DIR ${MYSQL_UNIX_ADDR} DIRECTORY) + CONFIGURE_FILE(tmpfiles.conf.in + ${CMAKE_CURRENT_BINARY_DIR}/tmpfiles.conf @ONLY) + INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/tmpfiles.conf + DESTINATION ${INSTALL_SYSTEMD_TMPFILESDIR} COMPONENT Server) + ENDIF() + + # @ in directory name broken between CMake version 2.8.12.2 and 3.3 + # http://public.kitware.com/Bug/view.php?id=14782 + IF(NOT CMAKE_VERSION VERSION_LESS 3.3.0 OR NOT RPM) + CONFIGURE_FILE(mariadb@.service.in + ${CMAKE_CURRENT_BINARY_DIR}/mariadb@.service @ONLY) + INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/mariadb@.service + DESTINATION ${inst_location}/systemd COMPONENT SupportFiles) + ENDIF() + + IF(INSTALL_SYSTEMD_UNITDIR) + INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/mariadb.service + DESTINATION ${INSTALL_SYSTEMD_UNITDIR} COMPONENT Server) + + # http://public.kitware.com/Bug/view.php?id=14782 + IF(NOT CMAKE_VERSION VERSION_LESS 3.3.0 OR NOT RPM) + INSTALL(FILES use_galera_new_cluster.conf + DESTINATION + "${INSTALL_SYSTEMD_UNITDIR}/mariadb(a)bootstrap.service.d" + COMPONENT Server) + INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/mariadb@.service + DESTINATION ${INSTALL_SYSTEMD_UNITDIR} COMPONENT Server) + ENDIF() + + ENDIF() + ENDIF() IF (INSTALL_SYSCONFDIR) INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/mysql-log-rotate DESTINATION ${INSTALL_SYSCONFDIR}/logrotate.d

1 0

[Commits] 32be26b: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery
by IgorBabaev 25 Jul '18

25 Jul '18

revision-id: 32be26bdc419be8383fecf315e2c66f81646620a (mariadb-10.2.16-41-g32be26b) parent(s): 969939e89c29bb3f3d91f8b4ab539601b5daa4d8 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-25 11:57:57 -0700 message: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_exists_to_in.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 15 +++++++++++++++ sql/item.h | 5 +++++ sql/sql_select.cc | 7 +++++++ 9 files changed, 123 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index e2ed431..1c75aa6 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7240,6 +7240,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 6306cff..0bd14a5 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7240,6 +7240,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 0db1b03..a3b7871 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7233,6 +7233,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 1fa6f38..9dcc690 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7231,6 +7231,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 986542b..7b3c001 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7246,6 +7246,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index d5fcc17..a062524 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7231,6 +7231,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 7e9e5a7..1048e5a 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6102,6 +6102,21 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests --echo # End of 10.0 tests diff --git a/sql/item.h b/sql/item.h index ce07f99..8fad8da 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1614,6 +1614,11 @@ class Item: public Value_source, virtual bool limit_index_condition_pushdown_processor(void *arg) { return 0; } virtual bool exists2in_processor(void *arg) { return 0; } virtual bool find_selective_predicates_list_processor(void *arg) { return 0; } + bool cleanup_is_expensive_cache_processor(void *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* TRUE if the expression depends only on the table indicated by tab_map diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1fa2143..4cabf5c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1335,6 +1335,13 @@ JOIN::optimize_inner() if (optimize_constant_subqueries()) DBUG_RETURN(1); + if (conds && conds->has_subquery()) + (void) conds->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (void *) 0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (void *) 0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);

1 0

[Commits] f949dd88fa3: MDEV-16812 Semisync slave io thread segfaults at STOP-SLAVE handling
by andrei.elkin＠pp.inet.fi 25 Jul '18

25 Jul '18

revision-id: f949dd88fa375240308efe7216f0f73ab4d28202 (mariadb-10.3.7-96-gf949dd88fa3) parent(s): f74d2a9faa4b4233433dfcb0d2d14a6b269c48ba author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-07-24 18:02:30 +0300 message: MDEV-16812 Semisync slave io thread segfaults at STOP-SLAVE handling When the semisync slave is being stopped with STOP SLAVE just after the master was shut down it attempts to reconnect with the master anyway per a semisync routine. Instead of an expected error the io-thread segfauls in mysql_real_connect() execution at !mysql->options.extension->async_context check trying to reach the extension's member while mysql->options.extension is actually and correctly NULL. Apparently not-NULL check for mysql->options.extension was missed and it's deployed by the patch to fix this issue. As a bonus it also tackles an assert Thread 0x7f16c72148c0 (LWP 24639) 0x00007f16c53b3bf2 in __GI___assert_fail (assertion=0x55a686117558 "global_status_var.global_memory_used == 0", file=0x55a6861171e8 "/home/andrei/MDB/WTs/10.3-clean/sql/mysqld.cc", line=2201, function=0x55a68611fa80 <mysqld_exit(int)::__PRETTY_FUNCTION__> "void mysqld_exit(int)") at assert.c:101 in a new test of the patch. The reason of the assert was insufficient cleanup in Repl_semi_sync_slave::kill_connection() which has a branch where a MYSQL instance was left out unfred. --- .../rpl/r/rpl_semi_sync_master_shutdown.result | 33 ++++++++++++ .../suite/rpl/t/rpl_semi_sync_master_shutdown.test | 60 ++++++++++++++++++++++ sql-common/client.c | 2 +- sql/semisync_slave.cc | 3 +- 4 files changed, 95 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_master_shutdown.result b/mysql-test/suite/rpl/r/rpl_semi_sync_master_shutdown.result new file mode 100644 index 00000000000..786e1682bb0 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_semi_sync_master_shutdown.result @@ -0,0 +1,33 @@ +include/master-slave.inc +[connection master] +connection master; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; +connection slave; +include/stop_slave.inc +SET @@GLOBAL. rpl_semi_sync_slave_enabled = 1; +include/start_slave.inc +connection master; +CREATE TABLE t1 (a INT); +INSERT INTO t1 SET a=1; +connection slave; +connection master; +# Shutdown master +include/rpl_stop_server.inc [server_number=1] +connection slave; +include/stop_slave.inc +# Restart master +include/rpl_start_server.inc [server_number=1] +connection slave; +include/stop_slave.inc +Warnings: +Note 1255 Slave already has been stopped +include/start_slave.inc +connection master; +SET @@GLOBAL.debug_dbug=""; +SET @@GLOBAL. rpl_semi_sync_master_enabled = 0; +connection master; +DROP TABLE t1; +connection slave; +include/stop_slave.inc +SET @@GLOBAL. rpl_semi_sync_slave_enabled = 0; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_master_shutdown.test b/mysql-test/suite/rpl/t/rpl_semi_sync_master_shutdown.test new file mode 100644 index 00000000000..2224f78d6d0 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_master_shutdown.test @@ -0,0 +1,60 @@ +# MDEV-16812 Semisync slave io thread segfaults at STOP-SLAVE handling +# +# The test verifies that the semisync-enabled slave io thread +# finishes off as specified in particular trying to connect even to a shut down +# master for a semisync firewell routine. + +source include/not_embedded.inc; +source include/have_debug.inc; +source include/master-slave.inc; + +--connection master + +--let $sav_enabled_master=`SELECT @@GLOBAL.rpl_semi_sync_master_enabled ` +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; + +--connection slave +source include/stop_slave.inc; +--let $sav_enabled_slave=`SELECT @@GLOBAL.rpl_semi_sync_slave_enabled ` +SET @@GLOBAL. rpl_semi_sync_slave_enabled = 1; +source include/start_slave.inc; + +--connection master +CREATE TABLE t1 (a INT); +INSERT INTO t1 SET a=1; + +--sync_slave_with_master + +connection master; +--echo # Shutdown master +--let $rpl_server_number=1 +source include/rpl_stop_server.inc; + +--connection slave +--source include/stop_slave.inc + +#connection master; +--echo # Restart master +--let $rpl_server_number=1 +source include/rpl_start_server.inc; + +# +# Clean up +# +--connection slave +--source include/stop_slave.inc +--source include/start_slave.inc + +--connection master +SET @@GLOBAL.debug_dbug=""; +--eval SET @@GLOBAL. rpl_semi_sync_master_enabled = $sav_enabled_master + +--connection master +DROP TABLE t1; + +--sync_slave_with_master +source include/stop_slave.inc; +--eval SET @@GLOBAL. rpl_semi_sync_slave_enabled = $sav_enabled_slave + +--let $rpl_only_running_threads= 1 +--source include/rpl_end.inc diff --git a/sql-common/client.c b/sql-common/client.c index 088377f8c52..8bbe08a504a 100644 --- a/sql-common/client.c +++ b/sql-common/client.c @@ -3657,7 +3657,7 @@ CLI_MYSQL_REAL_CONNECT(MYSQL *mysql,const char *host, const char *user, end_server(mysql); mysql_close_free(mysql); if (!(client_flag & CLIENT_REMEMBER_OPTIONS) && - !mysql->options.extension->async_context) + !(mysql->options.extension && mysql->options.extension->async_context)) mysql_close_free_options(mysql); } DBUG_RETURN(0); diff --git a/sql/semisync_slave.cc b/sql/semisync_slave.cc index 86d0176dac1..df8baf045ac 100644 --- a/sql/semisync_slave.cc +++ b/sql/semisync_slave.cc @@ -144,8 +144,7 @@ void Repl_semi_sync_slave::kill_connection(MYSQL *mysql) { sql_print_information("cannot connect to master to kill slave io_thread's " "connection"); - if (!ret) - mysql_close(kill_mysql); + mysql_close(kill_mysql); return; } size_t kill_buffer_length = my_snprintf(kill_buffer, 30, "KILL %lu",

1 0

[Commits] 108af298032: MDEV-15454: Nested SELECT IN returns wrong results
by varunraiko1803＠gmail.com 25 Jul '18

25 Jul '18

revision-id: 108af298032cb4cea316a08c304c306268a2badf (mariadb-5.5.56-213-g108af298032) parent(s): 318097bb8f6e12c546b5dcd287416158209dbb39 author: Varun Gupta committer: Varun Gupta timestamp: 2018-05-11 02:56:33 +0530 message: MDEV-15454: Nested SELECT IN returns wrong results In this case we are setting the field Item_func_eq::in_eqaulity_no for the semi-join equalities. This helps us to remove these equalites as the inner tables are not available during parent select execution while the outer tables are not available during materialization phase. We only have it set for the equalites for the fields involved with the IN subquery and reset it for the equalities which do not belong to the IN subquery. For example in case of nested IN subqueries: SELECT t1.a FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 where t2.b IN (select t3.b from t3 where t3.c=27 )) there are two equalites involving the fields of the IN subquery 1) t2.b = t3.b : the field Item_func_eq::in_eqaulity_no is set when we merge the grandchild select into the child select 2) t1.a = t2.a : the field Item_func_eq::in_eqaulity_no is set when we merge the child select into the parent select But when we perform case 2) we should ensure that we reset the equalities in the child's WHERE clause. --- mysql-test/r/subselect_sj_mat.result | 46 +++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 49 ++++++++++++++++++++++++++ sql/opt_subselect.cc | 66 ++++++++++++++++++++++++++++++++++++ 3 files changed, 161 insertions(+) diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index cb5012a91c9..2af1ac1f7b3 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2400,4 +2400,50 @@ ec70316637232000158bbfc8bcbe5d60 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-15454: Nested SELECT IN returns wrong results +# +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); +CREATE TABLE t2 ( a int, b int ); +INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); +CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); +INSERT INTO t3 (c, b) VALUES (27, 96); +CREATE PROCEDURE prepare_data() +BEGIN +DECLARE i INT DEFAULT 1; +WHILE i < 1000 DO +INSERT INTO t1 (a) VALUES (i); +INSERT INTO t2 (a,b) VALUES (i,56); +INSERT INTO t3 (c,b) VALUES (i,i); +SET i = i + 1; +END WHILE; +END$$ +CALL prepare_data(); +SET optimizer_switch='materialization=off'; +explain +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ref PRIMARY PRIMARY 4 const 2 Using index; Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 1005 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 Using index; End temporary +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +SET optimizer_switch='materialization=on'; +explain +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 999 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t3 ref PRIMARY PRIMARY 4 const 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1005 Using where; Using join buffer (flat, BNL join) +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +drop procedure prepare_data; +drop table t1,t2,t3; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 90f63bea561..67cba08b5df 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2151,4 +2151,53 @@ eval $q; DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +--echo # +--echo # MDEV-15454: Nested SELECT IN returns wrong results +--echo # + +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); + +CREATE TABLE t2 ( a int, b int ); +INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); + +CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); +INSERT INTO t3 (c, b) VALUES (27, 96); + +DELIMITER $$; +CREATE PROCEDURE prepare_data() +BEGIN + DECLARE i INT DEFAULT 1; + + WHILE i < 1000 DO + INSERT INTO t1 (a) VALUES (i); + INSERT INTO t2 (a,b) VALUES (i,56); + INSERT INTO t3 (c,b) VALUES (i,i); + SET i = i + 1; + END WHILE; +END$$ +DELIMITER ;$$ + +CALL prepare_data(); + +SET optimizer_switch='materialization=off'; + +explain +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +SET optimizer_switch='materialization=on'; + +explain +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; + +drop procedure prepare_data; +drop table t1,t2,t3; + --echo # End of 5.5 tests diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index a7edd64e68b..31213a5cf1d 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -440,6 +440,7 @@ bool subquery_types_allow_materialization(Item_in_subselect *in_subs); static bool replace_where_subcondition(JOIN *, Item **, Item *, Item *, bool); static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2, void *arg); +static void reset_equality_number_for_subq_conds(Item * cond); static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred); static bool convert_subq_to_jtbm(JOIN *parent_join, Item_in_subselect *subq_pred, bool *remove); @@ -1453,6 +1454,67 @@ static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2, } +/** + @brief + reset the value of the field in_eqaulity_no for all Item_func_eq + items in the where clause of the subquery. + + Look for in_equality_no description in Item_func_eq class + + DESCRIPTION + Lets have an example: + SELECT t1.a FROM t1 WHERE t1.a IN + (SELECT t2.a FROM t2 where t2.b IN + (select t3.b from t3 where t3.c=27 )) + + So for such a query we have the parent, child and + grandchild select. + + So for the equality t2.b = t3.b we set the value for in_equality_no to + 0 according to its description. Wewe do the same for t1.a = t2.a. + But when we look at the child select (with the grandchild select merged), + the query would be + + SELECT t1.a FROM t1 WHERE t1.a IN + (SELECT t2.a FROM t2 where t2.b = t3.b and t3.c=27) + + and then when the child select is merged into the parent select the query + would look like + + SELECT t1.a FROM t1, semi-join-nest(t2,t3) + WHERE t1.a =t2.a and t2.b = t3.b and t3.c=27 + + Still we would have in_equality_no set for t2.b = t3.b + though it does not take part in the semi-join equality for the parent select, + so we should reset its value to UINT_MAX. + + @param cond WHERE clause of the subquery +*/ + +static void reset_equality_number_for_subq_conds(Item * cond) +{ + if (!cond) + return; + if (cond->type() == Item::COND_ITEM) + { + List_iterator<Item> li(*((Item_cond*) cond)->argument_list()); + Item *item; + while ((item=li++)) + { + if (item->type() == Item::FUNC_ITEM && + ((Item_func*)item)->functype()== Item_func::EQ_FUNC) + ((Item_func_eq*)item)->in_equality_no= UINT_MAX; + } + } + else + { + if (cond->type() == Item::FUNC_ITEM && + ((Item_func*)cond)->functype()== Item_func::EQ_FUNC) + ((Item_func_eq*)cond)->in_equality_no= UINT_MAX; + } + return; +} + /* Convert a subquery predicate into a TABLE_LIST semi-join nest @@ -1481,6 +1543,7 @@ static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2, TRUE Out of memory error */ + static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) { SELECT_LEX *parent_lex= parent_join->select_lex; @@ -1713,6 +1776,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) if (subq_pred->left_expr->cols() == 1) { + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); /* add left = select_list_element */ nested_join->sj_outer_expr_list.push_back(&subq_pred->left_expr); /* @@ -1735,6 +1799,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) } else if (subq_pred->left_expr->type() == Item::ROW_ITEM) { + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); /* disassemple left expression and add left1 = select_list_element1 and left2 = select_list_element2 ... @@ -1759,6 +1824,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) } else { + reset_equality_number_for_subq_conds(sj_nest->sj_on_expr); /* add row operation left = (select_list_element1, select_list_element2, ...)

2 1

[Commits] MDEV-16821 Set password for user makes rpl test to fail
by Sachin Setiya 25 Jul '18

25 Jul '18

commit 969939e89c29bb3f3d91f8b4ab539601b5daa4d8 Author: Sachin <sachin.setiya(a)mariadb.com> Date: Wed Jul 25 12:54:37 2018 +0530 MDEV-16821 Set password for user makes rpl test to fail Actually if we use "set password for " command this changes the checksum of mysql.user table -localhost root Y Y Y Y Y Y Y Y YY Y Y Y Y Y Y Y Y Y Y Y $ Y Y Y Y Y Y Y 0 00 0 N N 0.000000 +localhost root Y Y Y Y Y Y Y Y YY Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y 0 00 0 mysql_native_password N N 0.000000 In short we replace '' with mysql_native_password which make checksum to be different, and hence check test case fails. So we use UPDATE mysql.user command. diff --git a/mysql-test/extra/rpl_tests/rpl_row_001.test b/mysql-test/extra/rpl_tests/rpl_row_001.test index 8eb684e0dff..2706336bdea 100644 --- a/mysql-test/extra/rpl_tests/rpl_row_001.test +++ b/mysql-test/extra/rpl_tests/rpl_row_001.test @@ -14,7 +14,7 @@ connection slave; sync_with_master; STOP SLAVE; connection master; -SET PASSWORD FOR root@"localhost" = PASSWORD('foo'); +UPDATE mysql.user SET password=password('foo') WHERE host='localhost' AND user='root'; connection slave; START SLAVE; connection master; @@ -22,7 +22,7 @@ connection master; # Give slave time to do at last one failed connect retry # This one must be short so that the slave will not stop retrying real_sleep 2; -SET PASSWORD FOR root@"localhost" = PASSWORD(''); +UPDATE mysql.user SET password=password('') WHERE host='localhost' AND user='root'; # Give slave time to connect (will retry every second) sleep 2; diff --git a/mysql-test/suite/rpl/r/rpl_row_001.result b/mysql-test/suite/rpl/r/rpl_row_001.result index 94985548f59..3ca694a8e76 100644 --- a/mysql-test/suite/rpl/r/rpl_row_001.result +++ b/mysql-test/suite/rpl/r/rpl_row_001.result @@ -18,11 +18,11 @@ Ababa connection slave; STOP SLAVE; connection master; -SET PASSWORD FOR root@"localhost" = PASSWORD('foo'); +UPDATE mysql.user SET password=password('foo') WHERE host='localhost' AND user='root'; connection slave; START SLAVE; connection master; -SET PASSWORD FOR root@"localhost" = PASSWORD(''); +UPDATE mysql.user SET password=password('') WHERE host='localhost' AND user='root'; CREATE TABLE t3(n INT); INSERT INTO t3 VALUES(1),(2); connection slave; diff --git a/mysql-test/suite/rpl/r/rpl_stm_000001.result b/mysql-test/suite/rpl/r/rpl_stm_000001.result index 5680d22f233..0b9ed6fc09c 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_000001.result +++ b/mysql-test/suite/rpl/r/rpl_stm_000001.result @@ -19,11 +19,11 @@ abandons connection slave; stop slave; connection master; -set password for root@"localhost" = password('foo'); +UPDATE mysql.user SET password=password('foo') WHERE host='localhost' AND user='root'; connection slave; start slave; connection master; -set password for root@"localhost" = password(''); +UPDATE mysql.user SET password=password('') WHERE host='localhost' AND user='root'; create table t3(n int); insert into t3 values(1),(2); connection slave; diff --git a/mysql-test/suite/rpl/t/rpl_stm_000001.test b/mysql-test/suite/rpl/t/rpl_stm_000001.test index 7e8f669ff34..119fd6168e0 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_000001.test +++ b/mysql-test/suite/rpl/t/rpl_stm_000001.test @@ -19,7 +19,7 @@ select * from t1 limit 10; sync_slave_with_master; stop slave; connection master; -set password for root@"localhost" = password('foo'); +UPDATE mysql.user SET password=password('foo') WHERE host='localhost' AND user='root'; connection slave; start slave; connection master; @@ -27,7 +27,7 @@ connection master; # Give slave time to do at last one failed connect retry # This one must be short so that the slave will not stop retrying real_sleep 2; -set password for root@"localhost" = password(''); +UPDATE mysql.user SET password=password('') WHERE host='localhost' AND user='root'; # Give slave time to connect (will retry every second) sleep 2;

1 0

[Commits] 1fde449: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery
by IgorBabaev 25 Jul '18

25 Jul '18

revision-id: 1fde449f1d4e8ebe12e989754bfdc84b123cf0b2 (mariadb-10.1.34-30-g1fde449) parent(s): 57cde8ccd19675dc98e3cbacf0ef5c72cb188e49 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-24 23:53:12 -0700 message: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_exists_to_in.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 15 +++++++++++++++ sql/item.h | 5 +++++ sql/sql_select.cc | 7 +++++++ 9 files changed, 123 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 40f936f..69db095 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7235,6 +7235,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index a238208..ef3bf7e 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7235,6 +7235,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index baa7430..703572e 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7228,6 +7228,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 039f2fe..39a098b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7226,6 +7226,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 0ce77bb..b9a59bf 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7241,6 +7241,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 574e781..f1df85f 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7226,6 +7226,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 717871d..a4c4c21 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6098,6 +6098,21 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests --echo # End of 10.0 tests diff --git a/sql/item.h b/sql/item.h index 139ce40..dcd773f 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1449,6 +1449,11 @@ class Item: public Value_source, virtual bool exists2in_processor(uchar *opt_arg) { return 0; } virtual bool find_selective_predicates_list_processor(uchar *opt_arg) { return 0; } + bool cleanup_is_expensive_cache_processor(uchar *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e3b725c..460a64e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1212,6 +1212,13 @@ JOIN::optimize_inner() if (optimize_constant_subqueries()) DBUG_RETURN(1); + if (conds && conds->has_subquery()) + (void) conds->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);

1 0

[Commits] c631060: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery
by IgorBabaev 25 Jul '18

25 Jul '18

revision-id: c631060713a2af2890284feb7aea96c0cf4ba49f (mariadb-10.0.35-73-gc631060) parent(s): 1bda5e3a8f21d14b4af416259f293a92c49daebb author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-24 23:45:55 -0700 message: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_exists_to_in.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 15 +++++++++++++++ sql/item.h | 5 +++++ sql/sql_select.cc | 7 +++++++ 9 files changed, 123 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 79b2810..b074fb3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7211,4 +7211,20 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 7b2054f..d5aa16a 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7211,6 +7211,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%exists_to_in=off%'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 51c5263..aff68bd 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7204,6 +7204,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 574af7b..f118178 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7202,5 +7202,21 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 03285cf..6cefce2 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7217,6 +7217,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 6669c27..884374a 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7202,6 +7202,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 8c9a489..390d363 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6096,4 +6096,19 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests diff --git a/sql/item.h b/sql/item.h index b111d74..7c95239 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1275,6 +1275,11 @@ class Item { virtual bool exists2in_processor(uchar *opt_arg) { return 0; } virtual bool find_selective_predicates_list_processor(uchar *opt_arg) { return 0; } + bool cleanup_is_expensive_cache_processor(uchar *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 43b8d2b..19a727f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1207,6 +1207,13 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S if (optimize_constant_subqueries()) DBUG_RETURN(1); + if (conds && conds->has_subquery()) + (void) conds->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);

1 0

[Commits] 92cccf8: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery
by IgorBabaev 25 Jul '18

25 Jul '18

revision-id: 92cccf8708b156f22602f035e0fdc4e3cc893d2f (mariadb-10.0.35-73-g92cccf8) parent(s): 1bda5e3a8f21d14b4af416259f293a92c49daebb author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-24 23:27:28 -0700 message: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ mysql-test/t/subselect.test | 15 +++++++++++++++ sql/item.h | 5 +++++ sql/sql_select.cc | 7 +++++++ 8 files changed, 107 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 79b2810..b074fb3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7211,4 +7211,20 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 51c5263..aff68bd 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7204,6 +7204,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 574af7b..f118178 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7202,5 +7202,21 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 03285cf..6cefce2 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7217,6 +7217,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 6669c27..884374a 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7202,6 +7202,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 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 Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +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 Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 8c9a489..390d363 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6096,4 +6096,19 @@ and t1.a in (select `test`.`t3`.`c` from `test`.`t3`); SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-16820: impossible where with inexpensive subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); + +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); + +explain select * from t1 where (select max(b) from t2) = 10; +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; + +drop table t1,t2; + --echo End of 5.5 tests diff --git a/sql/item.h b/sql/item.h index b111d74..7c95239 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1275,6 +1275,11 @@ class Item { virtual bool exists2in_processor(uchar *opt_arg) { return 0; } virtual bool find_selective_predicates_list_processor(uchar *opt_arg) { return 0; } + bool cleanup_is_expensive_cache_processor(uchar *arg) + { + is_expensive_cache= (int8)(-1); + return 0; + } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 43b8d2b..19a727f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1207,6 +1207,13 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S if (optimize_constant_subqueries()) DBUG_RETURN(1); + if (conds && conds->has_subquery()) + (void) conds->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (having && having->has_subquery()) + (void) having->walk(&Item::cleanup_is_expensive_cache_processor, + 0, (uchar*)0); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);

1 0

[Commits] a116e787869: MDEV-15822: WSREP: BF lock wait long for trx
by jan 25 Jul '18

25 Jul '18

revision-id: a116e7878696cf1e34de93eb2b8ca06db4b776bf (mariadb-10.1.34-11-ga116e787869) parent(s): be5698265a4195586142d1a34fdd1cce9d95d8a1 author: Jan Lindström committer: Jan Lindström timestamp: 2018-07-25 08:21:25 +0300 message: MDEV-15822: WSREP: BF lock wait long for trx In Galera BF (brute force) transactions may not wait for lock requests and normally BF-transaction would select transaction holding conflicting locks as a victim for rollback. However, background statistic calculation transaction is InnoDB internal transaction and it has no thd i.e. it can't be selected as a victim. If background statistics calculation transaction holds conflicting locks to statistics tables it will cause BF lock wait long error message. Correct way to handle background statistics calculation is to acquire thd for transaction but that change is too big for GA-releases and there are other reported problems on background statistics calculation. This fix avoids adding a table to background statistics calculation if --- .../r/galera_bf_background_statistics.result | 95 ++++++++++++++++++++++ .../galera/t/galera_bf_background_statistics.opt | 1 + .../galera/t/galera_bf_background_statistics.test | 49 +++++++++++ storage/innobase/row/row0mysql.cc | 25 +++++- 4 files changed, 167 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/galera/r/galera_bf_background_statistics.result b/mysql-test/suite/galera/r/galera_bf_background_statistics.result new file mode 100644 index 00000000000..dc367b065f6 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_bf_background_statistics.result @@ -0,0 +1,95 @@ +SELECT @@innodb_stats_persistent; +@@innodb_stats_persistent +1 +CREATE TABLE t1 (f1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, f2 INTEGER DEFAULT NULL) ENGINE=InnoDB; +INSERT INTO t1(f1) values (NULL); +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +SELECT count(1) from t1; +count(1) +16384 +SET AUTOCOMMIT=OFF; +INSERT INTO t1 VALUES (9999999,NULL); +SELECT SLEEP(1000);; +ALTER TABLE t1 CHANGE f2 f2 INTEGER NOT NULL DEFAULT 1; +Warnings: +Warning 1265 Data truncated for column 'f2' at row 1 +Warning 1265 Data truncated for column 'f2' at row 2 +Warning 1265 Data truncated for column 'f2' at row 3 +Warning 1265 Data truncated for column 'f2' at row 4 +Warning 1265 Data truncated for column 'f2' at row 5 +Warning 1265 Data truncated for column 'f2' at row 6 +Warning 1265 Data truncated for column 'f2' at row 7 +Warning 1265 Data truncated for column 'f2' at row 8 +Warning 1265 Data truncated for column 'f2' at row 9 +Warning 1265 Data truncated for column 'f2' at row 10 +Warning 1265 Data truncated for column 'f2' at row 11 +Warning 1265 Data truncated for column 'f2' at row 12 +Warning 1265 Data truncated for column 'f2' at row 13 +Warning 1265 Data truncated for column 'f2' at row 14 +Warning 1265 Data truncated for column 'f2' at row 15 +Warning 1265 Data truncated for column 'f2' at row 16 +Warning 1265 Data truncated for column 'f2' at row 17 +Warning 1265 Data truncated for column 'f2' at row 18 +Warning 1265 Data truncated for column 'f2' at row 19 +Warning 1265 Data truncated for column 'f2' at row 20 +Warning 1265 Data truncated for column 'f2' at row 21 +Warning 1265 Data truncated for column 'f2' at row 22 +Warning 1265 Data truncated for column 'f2' at row 23 +Warning 1265 Data truncated for column 'f2' at row 24 +Warning 1265 Data truncated for column 'f2' at row 25 +Warning 1265 Data truncated for column 'f2' at row 26 +Warning 1265 Data truncated for column 'f2' at row 27 +Warning 1265 Data truncated for column 'f2' at row 28 +Warning 1265 Data truncated for column 'f2' at row 29 +Warning 1265 Data truncated for column 'f2' at row 30 +Warning 1265 Data truncated for column 'f2' at row 31 +Warning 1265 Data truncated for column 'f2' at row 32 +Warning 1265 Data truncated for column 'f2' at row 33 +Warning 1265 Data truncated for column 'f2' at row 34 +Warning 1265 Data truncated for column 'f2' at row 35 +Warning 1265 Data truncated for column 'f2' at row 36 +Warning 1265 Data truncated for column 'f2' at row 37 +Warning 1265 Data truncated for column 'f2' at row 38 +Warning 1265 Data truncated for column 'f2' at row 39 +Warning 1265 Data truncated for column 'f2' at row 40 +Warning 1265 Data truncated for column 'f2' at row 41 +Warning 1265 Data truncated for column 'f2' at row 42 +Warning 1265 Data truncated for column 'f2' at row 43 +Warning 1265 Data truncated for column 'f2' at row 44 +Warning 1265 Data truncated for column 'f2' at row 45 +Warning 1265 Data truncated for column 'f2' at row 46 +Warning 1265 Data truncated for column 'f2' at row 47 +Warning 1265 Data truncated for column 'f2' at row 48 +Warning 1265 Data truncated for column 'f2' at row 49 +Warning 1265 Data truncated for column 'f2' at row 50 +Warning 1265 Data truncated for column 'f2' at row 51 +Warning 1265 Data truncated for column 'f2' at row 52 +Warning 1265 Data truncated for column 'f2' at row 53 +Warning 1265 Data truncated for column 'f2' at row 54 +Warning 1265 Data truncated for column 'f2' at row 55 +Warning 1265 Data truncated for column 'f2' at row 56 +Warning 1265 Data truncated for column 'f2' at row 57 +Warning 1265 Data truncated for column 'f2' at row 58 +Warning 1265 Data truncated for column 'f2' at row 59 +Warning 1265 Data truncated for column 'f2' at row 60 +Warning 1265 Data truncated for column 'f2' at row 61 +Warning 1265 Data truncated for column 'f2' at row 62 +Warning 1265 Data truncated for column 'f2' at row 63 +Warning 1265 Data truncated for column 'f2' at row 64 +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +wsrep_local_aborts_increment +1 +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.opt b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt new file mode 100644 index 00000000000..f9b1414a974 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt @@ -0,0 +1 @@ +--innodb_stats_persistent=ON diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.test b/mysql-test/suite/galera/t/galera_bf_background_statistics.test new file mode 100644 index 00000000000..1030e8d4154 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.test @@ -0,0 +1,49 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc + +# +# Test a local transaction being aborted by a slave one while it is running a SLEEP() +# + +SELECT @@innodb_stats_persistent; + +CREATE TABLE t1 (f1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, f2 INTEGER DEFAULT NULL) ENGINE=InnoDB; +INSERT INTO t1(f1) values (NULL); +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +SELECT count(1) from t1; + +--connection node_2 +SET AUTOCOMMIT=OFF; +--let $wsrep_local_bf_aborts_before = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` +INSERT INTO t1 VALUES (9999999,NULL); +--send SELECT SLEEP(1000); + +--connection node_1 +ALTER TABLE t1 CHANGE f2 f2 INTEGER NOT NULL DEFAULT 1; + +--connection node_2 +--error ER_LOCK_DEADLOCK +--reap + +--let $wsrep_local_bf_aborts_after = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +# Check that wsrep_local_bf_aborts has been incremented by exactly 1 +--disable_query_log +--eval SELECT $wsrep_local_bf_aborts_after - $wsrep_local_bf_aborts_before = 1 AS wsrep_local_aborts_increment; +--enable_query_log + +DROP TABLE t1; + diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 6a971a973f5..963d939380c 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -70,6 +70,13 @@ Created 9/17/2000 Heikki Tuuri #include "ha_prototypes.h" #include <algorithm> +#ifdef WITH_WSREP +#include "mysql/service_wsrep.h" +#include "wsrep.h" +#include "log.h" +#include "wsrep_mysqld.h" +#endif + /** Provide optional 4.x backwards compatibility for 5.0 and above */ UNIV_INTERN ibool row_rollback_on_timeout = FALSE; @@ -1093,6 +1100,7 @@ UNIV_INLINE void row_update_statistics_if_needed( /*============================*/ + trx_t* trx, dict_table_t* table) /*!< in: table */ { ib_uint64_t counter; @@ -1114,6 +1122,16 @@ row_update_statistics_if_needed( if (counter > n_rows / 10 /* 10% */ && dict_stats_auto_recalc_is_enabled(table)) { +#ifdef WITH_WSREP + if (wsrep_on(trx->mysql_thd) && + wsrep_thd_is_BF(trx->mysql_thd, FALSE)) { + WSREP_DEBUG("Avoiding background statistics" + " calculation for table %s", + table->name); + return; + } +#endif /* WITH_WSREP */ + dict_stats_recalc_pool_add(table); table->stat_modified_counter = 0; } @@ -1537,7 +1555,8 @@ row_insert_for_mysql( ut_memcpy(prebuilt->row_id, node->row_id_buf, DATA_ROW_ID_LEN); } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); + trx->op_info = ""; return(err); @@ -1921,7 +1940,7 @@ row_update_for_mysql( that changes indexed columns, UPDATEs that change only non-indexed columns would not affect statistics. */ if (node->is_delete || !(node->cmpl_info & UPD_NODE_NO_ORD_CHANGE)) { - row_update_statistics_if_needed(prebuilt->table); + row_update_statistics_if_needed(trx, prebuilt->table); } else { /* Update the table modification counter even when non-indexed columns change if statistics is initialized. */ @@ -2158,7 +2177,7 @@ row_update_cascade_for_mysql( } } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); return(err); }

1 0

[Commits] 9f8d0aed11e: MDEV-15822: WSREP: BF lock wait long for trx
by jan 25 Jul '18

25 Jul '18

revision-id: 9f8d0aed11eb06e3cb765f4fff8c2d61232d9b48 (mariadb-10.1.34-11-g9f8d0aed11e) parent(s): be5698265a4195586142d1a34fdd1cce9d95d8a1 author: Jan Lindström committer: Jan Lindström timestamp: 2018-07-25 07:54:23 +0300 message: MDEV-15822: WSREP: BF lock wait long for trx In Galera BF (brute force) transactions may not wait for lock requests and normally BF-transaction would select transaction holding conflicting locks as a victim for rollback. However, background statistic calculation transaction is InnoDB internal transaction and it has no thd i.e. it can't be selected as a victim. If background statistics calculation transaction holds conflicting locks to statistics tables it will cause BF lock wait long error message. Correct way to handle background statistics calculation is to acquire thd for transaction but that change is too big for GA-releases and there are other reported problems on background statistics calculation. This fix avoids adding a table to background statistics calculation if transaction is running Galera BF-transaction. --- .../galera/t/galera_bf_background_statistics.opt | 1 + .../galera/t/galera_bf_background_statistics.test | 49 ++++++++++++++++++++++ storage/innobase/row/row0mysql.cc | 25 +++++++++-- 3 files changed, 72 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.opt b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt new file mode 100644 index 00000000000..f9b1414a974 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.opt @@ -0,0 +1 @@ +--innodb_stats_persistent=ON diff --git a/mysql-test/suite/galera/t/galera_bf_background_statistics.test b/mysql-test/suite/galera/t/galera_bf_background_statistics.test new file mode 100644 index 00000000000..1030e8d4154 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_background_statistics.test @@ -0,0 +1,49 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc + +# +# Test a local transaction being aborted by a slave one while it is running a SLEEP() +# + +SELECT @@innodb_stats_persistent; + +CREATE TABLE t1 (f1 INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, f2 INTEGER DEFAULT NULL) ENGINE=InnoDB; +INSERT INTO t1(f1) values (NULL); +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +INSERT INTO t1(f1) select NULL from t1; +SELECT count(1) from t1; + +--connection node_2 +SET AUTOCOMMIT=OFF; +--let $wsrep_local_bf_aborts_before = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` +INSERT INTO t1 VALUES (9999999,NULL); +--send SELECT SLEEP(1000); + +--connection node_1 +ALTER TABLE t1 CHANGE f2 f2 INTEGER NOT NULL DEFAULT 1; + +--connection node_2 +--error ER_LOCK_DEADLOCK +--reap + +--let $wsrep_local_bf_aborts_after = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +# Check that wsrep_local_bf_aborts has been incremented by exactly 1 +--disable_query_log +--eval SELECT $wsrep_local_bf_aborts_after - $wsrep_local_bf_aborts_before = 1 AS wsrep_local_aborts_increment; +--enable_query_log + +DROP TABLE t1; + diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 6a971a973f5..963d939380c 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -70,6 +70,13 @@ Created 9/17/2000 Heikki Tuuri #include "ha_prototypes.h" #include <algorithm> +#ifdef WITH_WSREP +#include "mysql/service_wsrep.h" +#include "wsrep.h" +#include "log.h" +#include "wsrep_mysqld.h" +#endif + /** Provide optional 4.x backwards compatibility for 5.0 and above */ UNIV_INTERN ibool row_rollback_on_timeout = FALSE; @@ -1093,6 +1100,7 @@ UNIV_INLINE void row_update_statistics_if_needed( /*============================*/ + trx_t* trx, dict_table_t* table) /*!< in: table */ { ib_uint64_t counter; @@ -1114,6 +1122,16 @@ row_update_statistics_if_needed( if (counter > n_rows / 10 /* 10% */ && dict_stats_auto_recalc_is_enabled(table)) { +#ifdef WITH_WSREP + if (wsrep_on(trx->mysql_thd) && + wsrep_thd_is_BF(trx->mysql_thd, FALSE)) { + WSREP_DEBUG("Avoiding background statistics" + " calculation for table %s", + table->name); + return; + } +#endif /* WITH_WSREP */ + dict_stats_recalc_pool_add(table); table->stat_modified_counter = 0; } @@ -1537,7 +1555,8 @@ row_insert_for_mysql( ut_memcpy(prebuilt->row_id, node->row_id_buf, DATA_ROW_ID_LEN); } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); + trx->op_info = ""; return(err); @@ -1921,7 +1940,7 @@ row_update_for_mysql( that changes indexed columns, UPDATEs that change only non-indexed columns would not affect statistics. */ if (node->is_delete || !(node->cmpl_info & UPD_NODE_NO_ORD_CHANGE)) { - row_update_statistics_if_needed(prebuilt->table); + row_update_statistics_if_needed(trx, prebuilt->table); } else { /* Update the table modification counter even when non-indexed columns change if statistics is initialized. */ @@ -2158,7 +2177,7 @@ row_update_cascade_for_mysql( } } - row_update_statistics_if_needed(table); + row_update_statistics_if_needed(trx, table); return(err); }

1 0