diff options
Diffstat (limited to 'mysql-test/r')
47 files changed, 2725 insertions, 156 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index b8ca95c3b45..6dae1c2838d 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1693,8 +1693,10 @@ INSERT INTO tm1 VALUES (1,1,1), (2,2,2); INSERT INTO tm2 VALUES (1,1,1), (2,2,2); ALTER TABLE ti1; affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tm1; affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE ti1 ADD COLUMN d VARCHAR(200); affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 diff --git a/mysql-test/r/alter_table_online.result b/mysql-test/r/alter_table_online.result index 02c7e5ac691..864ad724bc3 100644 --- a/mysql-test/r/alter_table_online.result +++ b/mysql-test/r/alter_table_online.result @@ -11,6 +11,10 @@ alter online table t1 comment "new comment"; ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. alter online table t1 rename to t2; ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. +alter online table t1 algorithm=INPLACE, lock=NONE; +alter online table t1; +alter table t1 algorithm=INPLACE; +alter table t1 lock=NONE; drop table t1; create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); diff --git a/mysql-test/r/blackhole.result b/mysql-test/r/blackhole.result index 16a97927262..36f5459ff85 100644 --- a/mysql-test/r/blackhole.result +++ b/mysql-test/r/blackhole.result @@ -8,6 +8,13 @@ CREATE TABLE t2 (a INT UNSIGNED, b INT, UNIQUE KEY (a, b)) ENGINE=BLACKHOLE; SELECT 1 FROM t1 WHERE a = ANY (SELECT a FROM t2); 1 DROP TABLE t1, t2; +create temporary table t1 (a int) engine=blackhole; +lock table t1 write; +truncate table t1; +select * from t1; +a +unlock tables; +drop temporary table t1; End of 5.5 tests # # Bug#13948247 DIVISION BY 0 IN GET_BEST_DISJUNCT_QUICK WITH FORCE INDEX GROUP BY diff --git a/mysql-test/r/bootstrap.result b/mysql-test/r/bootstrap.result index c4775a787f2..bb80cf28b56 100644 --- a/mysql-test/r/bootstrap.result +++ b/mysql-test/r/bootstrap.result @@ -15,3 +15,14 @@ SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb' and SUPPORT='YES'; End of 5.5 tests +flush tables; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +select * from mysql.plugin; +name dl +EXAMPLE ha_example.so +truncate table mysql.plugin; diff --git a/mysql-test/r/bug46261.result b/mysql-test/r/bug46261.result deleted file mode 100644 index dcc950d1baf..00000000000 --- a/mysql-test/r/bug46261.result +++ /dev/null @@ -1,8 +0,0 @@ -# -# Bug#46261 Plugins can be installed with --skip-grant-tables -# -INSTALL PLUGIN example SONAME 'ha_example.so'; -ERROR HY000: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement -UNINSTALL PLUGIN example; -ERROR HY000: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement -End of 5.1 tests diff --git a/mysql-test/r/ctype_uca.result b/mysql-test/r/ctype_uca.result index 16b60aed07f..02809f3be22 100644 --- a/mysql-test/r/ctype_uca.result +++ b/mysql-test/r/ctype_uca.result @@ -6466,6 +6466,418 @@ a c1 10 => DŽ drop table t1; # +# MDEV-7649 wrong result when comparing utf8 column with an invalid literal +# +SET NAMES utf8 COLLATE utf8_unicode_ci; +# +# Start of ctype_utf8_ilseq.inc +# +CREATE TABLE t1 ENGINE=MyISAM AS SELECT REPEAT(' ', 60) AS ch LIMIT 0;; +ALTER TABLE t1 +ADD id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +ADD KEY(ch); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ch` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`), + KEY `ch` (`ch`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (ch) VALUES ('admin'),('admin1'); +SELECT ch FROM t1 WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆'; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +DELETE FROM t1; +INSERT INTO t1 (ch) VALUES ('a'), ('a?'), ('a??'), ('a???'), ('a????'); +INSERT INTO t1 (ch) VALUES ('ab'),('a?b'),('a??b'),('a???b'),('a????b'); +INSERT INTO t1 (ch) VALUES ('az'),('a?z'),('a??z'),('a???z'),('a????z'); +INSERT INTO t1 (ch) VALUES ('z'); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D080); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D680); +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +ALTER TABLE t1 DROP KEY ch; +# 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,''''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b'''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +z +# 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F is a bad byte sequence (an mb2tail without mb2head) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +DROP TABLE t1; +# +# End of ctype_utf8_ilseq.inc +# +# +# Start of ctype_utf8_ilseq.inc +# +CREATE TABLE t1 ENGINE=HEAP AS SELECT REPEAT(' ', 60) AS ch LIMIT 0;; +ALTER TABLE t1 +ADD id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +ADD KEY(ch); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ch` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`), + KEY `ch` (`ch`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +INSERT INTO t1 (ch) VALUES ('admin'),('admin1'); +SELECT ch FROM t1 WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆'; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +DELETE FROM t1; +INSERT INTO t1 (ch) VALUES ('a'), ('a?'), ('a??'), ('a???'), ('a????'); +INSERT INTO t1 (ch) VALUES ('ab'),('a?b'),('a??b'),('a???b'),('a????b'); +INSERT INTO t1 (ch) VALUES ('az'),('a?z'),('a??z'),('a???z'),('a????z'); +INSERT INTO t1 (ch) VALUES ('z'); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D080); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D680); +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +ALTER TABLE t1 DROP KEY ch; +# 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,''''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b'''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +z +# 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F is a bad byte sequence (an mb2tail without mb2head) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +DROP TABLE t1; +# +# End of ctype_utf8_ilseq.inc +# +# # End of 5.5 tests # # diff --git a/mysql-test/r/ctype_uca_innodb.result b/mysql-test/r/ctype_uca_innodb.result new file mode 100644 index 00000000000..a4e1ae09130 --- /dev/null +++ b/mysql-test/r/ctype_uca_innodb.result @@ -0,0 +1,218 @@ +# +# Start of 5.5 tests +# +# +# MDEV-7649 wrong result when comparing utf8 column with an invalid literal +# +SET NAMES utf8 COLLATE utf8_unicode_ci; +# +# Start of ctype_utf8_ilseq.inc +# +CREATE TABLE t1 ENGINE=InnoDB AS SELECT REPEAT(' ', 60) AS ch LIMIT 0;; +ALTER TABLE t1 +ADD id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +ADD KEY(ch); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ch` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`), + KEY `ch` (`ch`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +INSERT INTO t1 (ch) VALUES ('admin'),('admin1'); +SELECT ch FROM t1 WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆'; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +DELETE FROM t1; +INSERT INTO t1 (ch) VALUES ('a'), ('a?'), ('a??'), ('a???'), ('a????'); +INSERT INTO t1 (ch) VALUES ('ab'),('a?b'),('a??b'),('a???b'),('a????b'); +INSERT INTO t1 (ch) VALUES ('az'),('a?z'),('a??z'),('a???z'),('a????z'); +INSERT INTO t1 (ch) VALUES ('z'); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D080); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D680); +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +ALTER TABLE t1 DROP KEY ch; +# 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,''''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b'''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +z +# 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F is a bad byte sequence (an mb2tail without mb2head) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +DROP TABLE t1; +# +# End of ctype_utf8_ilseq.inc +# +# +# End of 5.5 tests +# diff --git a/mysql-test/r/ctype_upgrade.result b/mysql-test/r/ctype_upgrade.result index 56e2ef96ead..6bef89f6cd6 100644 --- a/mysql-test/r/ctype_upgrade.result +++ b/mysql-test/r/ctype_upgrade.result @@ -227,7 +227,7 @@ DROP TABLE mysql050614_xxx_croatian_ci; # Checking mysql_upgrade # # Running mysql_upgrade -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -258,9 +258,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -279,11 +280,11 @@ test.maria050313_ucs2_croatian_ci_def OK test.maria050313_utf8_croatian_ci OK test.maria050533_xxx_croatian_ci OK test.maria100004_xxx_croatian_ci OK -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK # Running mysql_upgrade for the second time # This should report OK for all tables -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -314,9 +315,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -329,7 +331,7 @@ test.maria050313_utf8_croatian_ci OK test.maria050533_xxx_croatian_ci OK test.maria100004_xxx_croatian_ci OK test.mysql050614_xxx_croatian_ci OK -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK SHOW CREATE TABLE maria050313_ucs2_croatian_ci_def; Table Create Table diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 1056c246f33..6eae47844ca 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -5412,6 +5412,634 @@ a 1024 Warnings: Warning 1260 Row 2 was cut by GROUP_CONCAT() # +# MDEV-7814 Assertion `args[0]->fixed' fails in Item_func_conv_charset::Item_func_conv_charset +# +CREATE TABLE t1(a CHAR(1) CHARACTER SET latin1, b INT NOT NULL); +CREATE TABLE t2(a CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci, b INT NOT NULL); +SELECT (SELECT t2.a FROM t2 WHERE t2.a=t1.a) AS aa, b, COUNT(b) FROM t1 GROUP BY aa; +aa b COUNT(b) +DROP TABLE t1,t2; +# +# MDEV-7649 wrong result when comparing utf8 column with an invalid literal +# +SET NAMES utf8 COLLATE utf8_general_ci; +# +# Start of ctype_utf8_ilseq.inc +# +CREATE TABLE t1 ENGINE=InnoDB AS SELECT REPEAT(' ', 60) AS ch LIMIT 0;; +ALTER TABLE t1 +ADD id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +ADD KEY(ch); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ch` varchar(60) CHARACTER SET utf8 NOT NULL DEFAULT '', + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`), + KEY `ch` (`ch`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +INSERT INTO t1 (ch) VALUES ('admin'),('admin1'); +SELECT ch FROM t1 WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆'; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +DELETE FROM t1; +INSERT INTO t1 (ch) VALUES ('a'), ('a?'), ('a??'), ('a???'), ('a????'); +INSERT INTO t1 (ch) VALUES ('ab'),('a?b'),('a??b'),('a???b'),('a????b'); +INSERT INTO t1 (ch) VALUES ('az'),('a?z'),('a??z'),('a???z'),('a????z'); +INSERT INTO t1 (ch) VALUES ('z'); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D080); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D680); +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +ALTER TABLE t1 DROP KEY ch; +# 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,''''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b'''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +aր +z +# 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F is a bad byte sequence (an mb2tail without mb2head) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +DROP TABLE t1; +# +# End of ctype_utf8_ilseq.inc +# +# +# Start of ctype_utf8_ilseq.inc +# +CREATE TABLE t1 ENGINE=MyISAM AS SELECT REPEAT(' ', 60) AS ch LIMIT 0;; +ALTER TABLE t1 +ADD id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +ADD KEY(ch); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ch` varchar(60) CHARACTER SET utf8 NOT NULL DEFAULT '', + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`), + KEY `ch` (`ch`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (ch) VALUES ('admin'),('admin1'); +SELECT ch FROM t1 WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆'; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +DELETE FROM t1; +INSERT INTO t1 (ch) VALUES ('a'), ('a?'), ('a??'), ('a???'), ('a????'); +INSERT INTO t1 (ch) VALUES ('ab'),('a?b'),('a??b'),('a???b'),('a????b'); +INSERT INTO t1 (ch) VALUES ('az'),('a?z'),('a??z'),('a???z'),('a????z'); +INSERT INTO t1 (ch) VALUES ('z'); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D080); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D680); +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +ALTER TABLE t1 DROP KEY ch; +# 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,''''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b'''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +aր +z +# 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F is a bad byte sequence (an mb2tail without mb2head) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +DROP TABLE t1; +# +# End of ctype_utf8_ilseq.inc +# +# +# Start of ctype_utf8_ilseq.inc +# +CREATE TABLE t1 ENGINE=HEAP AS SELECT REPEAT(' ', 60) AS ch LIMIT 0;; +ALTER TABLE t1 +ADD id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +ADD KEY(ch); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ch` varchar(60) CHARACTER SET utf8 NOT NULL DEFAULT '', + `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`), + KEY `ch` (`ch`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +INSERT INTO t1 (ch) VALUES ('admin'),('admin1'); +SELECT ch FROM t1 WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='admin𝌆'; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='admin𝌆'; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +DELETE FROM t1; +INSERT INTO t1 (ch) VALUES ('a'), ('a?'), ('a??'), ('a???'), ('a????'); +INSERT INTO t1 (ch) VALUES ('ab'),('a?b'),('a??b'),('a???b'),('a????b'); +INSERT INTO t1 (ch) VALUES ('az'),('a?z'),('a??z'),('a???z'),('a????z'); +INSERT INTO t1 (ch) VALUES ('z'); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D080); +INSERT INTO t1 (ch) VALUES (_utf8 0x61D680); +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86' for column 'ch' at row 1 +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch='a𝌆b' ORDER BY ch; +ch +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x8C\x86b' for column 'ch' at row 1 +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch<'a𝌆b' ORDER BY ch; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +aր +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +SELECT ch FROM t1 IGNORE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆' ORDER BY ch; +ch +z +SELECT ch FROM t1 FORCE KEY (ch) WHERE ch>'a𝌆b' ORDER BY ch; +ch +z +ALTER TABLE t1 DROP KEY ch; +# 0xD18F would be a good 2-byte character, 0xD1 is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,''''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xD1,'b'''); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch<''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +a +a? +a?? +a??? +a???? +a????b +a????z +a???b +a???z +a??b +a??z +a?b +a?z +ab +az +aЀ +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch>''a', 0xD1,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +aր +z +# 0xEA9A96 would be a good 3-byte character, 0xEA9A is an incomplete sequence +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0xEA9A,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F is a bad byte sequence (an mb2tail without mb2head) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +# 0x8F8F is a bad byte sequence (an mb2tail without mb2head, two times) +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,''' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +SET @query=CONCAT('SELECT ch FROM t1 WHERE ch=''a', 0x8F8F,'b'' ORDER BY ch'); +PREPARE stmt FROM @query; +EXECUTE stmt; +ch +DROP TABLE t1; +# +# End of ctype_utf8_ilseq.inc +# +# # End of 5.5 tests # # diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 6c3fb464cc3..b34415a7822 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -567,3 +567,15 @@ update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where ac set optimizer_switch=@save_derived_optimizer_switch_bug; drop table t1; set optimizer_switch=@save_derived_optimizer_switch; +# +# MDEV-6892: WHERE does not apply +# +create table t1 (id int); +create table t2 (id int); +insert into t1 values(1),(2),(3); +insert into t2 values(4),(5),(6); +select x.id, message from (select id from t1) x left join +(select id, 1 as message from t2) y on x.id=y.id +where coalesce(message,0) <> 0; +id message +drop table t1,t2; diff --git a/mysql-test/r/explain_non_select.result b/mysql-test/r/explain_non_select.result index 00ca481147b..0eee7ee9b1e 100644 --- a/mysql-test/r/explain_non_select.result +++ b/mysql-test/r/explain_non_select.result @@ -219,3 +219,16 @@ OK OK drop function f1; drop table t1; +# +# MDEV-7038: Assertion `status_var.memory_used == 0' failed in THD::~THD() +# on disconnect after executing EXPLAIN for multi-table UPDATE +# +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); +EXPLAIN UPDATE v1, mysql.user SET v1.a = v1.a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE user index NULL PRIMARY 420 NULL 4 Using index +DROP TABLE t1; +DROP VIEW v1; diff --git a/mysql-test/r/filesort_bad_i_s-7585.result b/mysql-test/r/filesort_bad_i_s-7585.result new file mode 100644 index 00000000000..edb1574288f --- /dev/null +++ b/mysql-test/r/filesort_bad_i_s-7585.result @@ -0,0 +1,9 @@ +SET sql_mode=STRICT_TRANS_TABLES; +CREATE TABLE t0 (i INT) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t0; +CREATE VIEW v2 AS SELECT * FROM v1; +DROP VIEW IF EXISTS v1; +CREATE TABLE t1 (i INT) ENGINE=MyISAM; +INSERT INTO t1 SELECT TABLE_ROWS FROM information_schema.tables ORDER BY TABLE_ROWS; +DROP VIEW v2; +DROP TABLE t1, t0; diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index b60deae1c80..f12a0c1127a 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -1091,3 +1091,15 @@ insert into t1 values ('a'),('b'); select 1 from t1 where a in (select group_concat(a) from t1); 1 drop table t1; +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a'),('b'); +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'); +CREATE TABLE t3 (f3 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('d'),('e'); +SELECT GROUP_CONCAT( f2 ORDER BY ( f2 IN ( SELECT f1 FROM t1 WHERE f1 <= f2 ) ) ) AS field +FROM ( SELECT * FROM t2 ) AS sq2, t3 +ORDER BY field; +field +c,c +drop table t3, t2, t1; diff --git a/mysql-test/r/function_defaults.result b/mysql-test/r/function_defaults.result index 50183a1f416..b8f42b86c1f 100644 --- a/mysql-test/r/function_defaults.result +++ b/mysql-test/r/function_defaults.result @@ -660,7 +660,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -669,7 +669,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -678,7 +678,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -705,7 +705,7 @@ CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; Table Create Table t7 CREATE TABLE `t7` ( - `f` datetime DEFAULT NULL + `f` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t7; f @@ -714,7 +714,7 @@ CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; Table Create Table t8 CREATE TABLE `t8` ( - `g` datetime DEFAULT NULL + `g` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t8; g @@ -723,7 +723,7 @@ CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; Table Create Table t9 CREATE TABLE `t9` ( - `h` datetime DEFAULT NULL + `h` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t9; h @@ -774,14 +774,14 @@ t12 CREATE TABLE `t12` ( `s` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `t` datetime DEFAULT NULL, `u` datetime DEFAULT '1986-09-27 03:00:00', - `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, `d` timestamp NOT NULL DEFAULT '1986-09-27 03:00:00', `e` timestamp NULL DEFAULT NULL, - `f` datetime DEFAULT NULL, - `g` datetime DEFAULT NULL, - `h` datetime DEFAULT NULL, + `f` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `g` datetime DEFAULT CURRENT_TIMESTAMP, + `h` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `i` datetime DEFAULT NULL, `j` datetime DEFAULT '1986-09-27 03:00:00' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -802,7 +802,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` datetime DEFAULT NULL + `a` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -811,7 +811,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` datetime DEFAULT NULL + `b` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -820,7 +820,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` datetime DEFAULT NULL + `c` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -2216,7 +2216,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -2225,7 +2225,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -2234,7 +2234,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -2261,7 +2261,7 @@ CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; Table Create Table t7 CREATE TABLE `t7` ( - `f` datetime(6) DEFAULT NULL + `f` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t7; f @@ -2270,7 +2270,7 @@ CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; Table Create Table t8 CREATE TABLE `t8` ( - `g` datetime(6) DEFAULT NULL + `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t8; g @@ -2279,7 +2279,7 @@ CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; Table Create Table t9 CREATE TABLE `t9` ( - `h` datetime(6) DEFAULT NULL + `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t9; h @@ -2330,14 +2330,14 @@ t12 CREATE TABLE `t12` ( `s` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6), `t` datetime(6) DEFAULT NULL, `u` datetime(6) DEFAULT '1986-09-27 03:00:00.098765', - `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', - `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', - `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), + `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), + `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6), `d` timestamp(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765', `e` timestamp(6) NULL DEFAULT NULL, - `f` datetime(6) DEFAULT NULL, - `g` datetime(6) DEFAULT NULL, - `h` datetime(6) DEFAULT NULL, + `f` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), + `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6), + `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6), `i` datetime(6) DEFAULT NULL, `j` datetime(6) DEFAULT '1986-09-27 03:00:00.098765' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -2358,7 +2358,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` datetime(6) DEFAULT NULL + `a` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -2367,7 +2367,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` datetime(6) DEFAULT NULL + `b` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -2376,7 +2376,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` datetime(6) DEFAULT NULL + `c` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM t4; c diff --git a/mysql-test/r/function_defaults_innodb.result b/mysql-test/r/function_defaults_innodb.result index 302d4c85e05..2525d4e948a 100644 --- a/mysql-test/r/function_defaults_innodb.result +++ b/mysql-test/r/function_defaults_innodb.result @@ -661,7 +661,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -670,7 +670,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -679,7 +679,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' + `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -706,7 +706,7 @@ CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; Table Create Table t7 CREATE TABLE `t7` ( - `f` datetime DEFAULT NULL + `f` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t7; f @@ -715,7 +715,7 @@ CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; Table Create Table t8 CREATE TABLE `t8` ( - `g` datetime DEFAULT NULL + `g` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t8; g @@ -724,7 +724,7 @@ CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; Table Create Table t9 CREATE TABLE `t9` ( - `h` datetime DEFAULT NULL + `h` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t9; h @@ -775,14 +775,14 @@ t12 CREATE TABLE `t12` ( `s` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `t` datetime DEFAULT NULL, `u` datetime DEFAULT '1986-09-27 03:00:00', - `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', + `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, `d` timestamp NOT NULL DEFAULT '1986-09-27 03:00:00', `e` timestamp NULL DEFAULT NULL, - `f` datetime DEFAULT NULL, - `g` datetime DEFAULT NULL, - `h` datetime DEFAULT NULL, + `f` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `g` datetime DEFAULT CURRENT_TIMESTAMP, + `h` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `i` datetime DEFAULT NULL, `j` datetime DEFAULT '1986-09-27 03:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -803,7 +803,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` datetime DEFAULT NULL + `a` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -812,7 +812,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` datetime DEFAULT NULL + `b` datetime DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -821,7 +821,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` datetime DEFAULT NULL + `c` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -2217,7 +2217,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -2226,7 +2226,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -2235,7 +2235,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' + `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t4; c @@ -2262,7 +2262,7 @@ CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; Table Create Table t7 CREATE TABLE `t7` ( - `f` datetime(6) DEFAULT NULL + `f` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t7; f @@ -2271,7 +2271,7 @@ CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; Table Create Table t8 CREATE TABLE `t8` ( - `g` datetime(6) DEFAULT NULL + `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t8; g @@ -2280,7 +2280,7 @@ CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; Table Create Table t9 CREATE TABLE `t9` ( - `h` datetime(6) DEFAULT NULL + `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t9; h @@ -2331,14 +2331,14 @@ t12 CREATE TABLE `t12` ( `s` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6), `t` datetime(6) DEFAULT NULL, `u` datetime(6) DEFAULT '1986-09-27 03:00:00.098765', - `a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', - `b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', - `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `a` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), + `b` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), + `c` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6), `d` timestamp(6) NOT NULL DEFAULT '1986-09-27 03:00:00.098765', `e` timestamp(6) NULL DEFAULT NULL, - `f` datetime(6) DEFAULT NULL, - `g` datetime(6) DEFAULT NULL, - `h` datetime(6) DEFAULT NULL, + `f` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), + `g` datetime(6) DEFAULT CURRENT_TIMESTAMP(6), + `h` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6), `i` datetime(6) DEFAULT NULL, `j` datetime(6) DEFAULT '1986-09-27 03:00:00.098765' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -2359,7 +2359,7 @@ CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` datetime(6) DEFAULT NULL + `a` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t2; a @@ -2368,7 +2368,7 @@ CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; Table Create Table t3 CREATE TABLE `t3` ( - `b` datetime(6) DEFAULT NULL + `b` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t3; b @@ -2377,7 +2377,7 @@ CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; Table Create Table t4 CREATE TABLE `t4` ( - `c` datetime(6) DEFAULT NULL + `c` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t4; c diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 55435f34200..3c8adf4aa9c 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1,4 +1,3 @@ -DROP TABLE IF EXISTS t1, t2, t3, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT); CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING); CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON); @@ -489,7 +488,7 @@ explain extended select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimpl id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select st_issimple(st_multipoint(st_point(3,6),st_point(4,10))) AS `issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,st_issimple(st_point(3,6)) AS `issimple(Point(3, 6))` +Note 1003 select st_issimple(st_multipoint(point(3,6),point(4,10))) AS `issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,st_issimple(point(3,6)) AS `issimple(Point(3, 6))` create table t1 (a geometry not null); insert into t1 values (GeomFromText('Point(1 2)')); insert into t1 values ('Garbage'); @@ -1694,6 +1693,14 @@ drop table t1; SELECT st_astext(ST_Buffer(ST_PolygonFromText('POLYGON((3 5, 2 4, 2 5, 3 5))'), -100)); st_astext(ST_Buffer(ST_PolygonFromText('POLYGON((3 5, 2 4, 2 5, 3 5))'), -100)) GEOMETRYCOLLECTION EMPTY +CREATE VIEW v1 AS SELECT POINT(1,1) AS p; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select point(1,1) AS `p` latin1 latin1_swedish_ci +SELECT ASTEXT(p) FROM v1; +ASTEXT(p) +POINT(1 1) +DROP VIEW v1; End of 5.5 tests SHOW CREATE TABLE information_schema.geometry_columns; Table Create Table diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 9a259b3c4c6..9f5eb053cb7 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2339,6 +2339,16 @@ MIN(a) b 0 a DROP TABLE t1; +create table t1 (a int, b int); +insert into t1 values (1,11), (1,12), (2,22),(2,23), (4,44),(4,45); +create table t2 (c int, d int); +insert into t2 values (1,11), (1,12), (2,22),(2,23), (4,44),(4,45); +select distinct a,sum(b), (select d from t2 where c=a order by max(b) limit 1) from t1 group by a order by max(b); +a sum(b) (select d from t2 where c=a order by max(b) limit 1) +1 23 11 +2 45 22 +4 89 44 +drop table t1, t2; # # LP bug#993726 Wrong result from a query with ALL subquery predicate in WHERE # diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index 99cc5d484bd..eda67460205 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -667,6 +667,37 @@ pk f DROP TABLE t1; End of 5.3 tests # +# Bug MDEV-7301: Unknown column quoted with backticks in HAVING clause when using function. +# Bug#16221433 MYSQL REJECTS QUERY DUE TO BAD RESOLUTION OF NAMES IN HAVING; VIEW UNREADABLE +# +CREATE TABLE `t1` ( +`id` int(11) NOT NULL, +`title` varchar(45) DEFAULT NULL, +PRIMARY KEY (`id`) +) DEFAULT CHARSET=utf8; +INSERT INTO `t1` VALUES ('1', 'Just for fun'); +INSERT INTO `t1` VALUES ('2', 'Wait until a sunhine'); +INSERT INTO `t1` VALUES ('3', 'Take a new turn'); +SELECT `id`, SHA1(`title`) AS `column_1` +FROM `t1` +HAVING `column_1` LIKE '8%'; +id column_1 +1 80a12660d24a72460e5e292fe33f870276d7f40a +expected 1 row(s) returned +SELECT `id`, SHA1(`title`) AS `column_1` +FROM `t1` +HAVING UPPER(column_1) LIKE '8%'; +id column_1 +1 80a12660d24a72460e5e292fe33f870276d7f40a +expected -- 1 row(s) returned +SELECT `id`, SHA1(`title`) AS `column_1` +FROM `t1` +HAVING UPPER(`column_1`) LIKE '8%'; +id column_1 +1 80a12660d24a72460e5e292fe33f870276d7f40a +expected -- 1 row(s) returned not ER_BAD_FIELD_ERROR +drop table t1; +# # Bug mdev-5160: two-way join with HAVING over the second table # CREATE TABLE t1 (c1 varchar(6)) ENGINE=MyISAM; diff --git a/mysql-test/r/innodb_mysql_sync.result b/mysql-test/r/innodb_mysql_sync.result index 49d69d13e40..478b0109816 100644 --- a/mysql-test/r/innodb_mysql_sync.result +++ b/mysql-test/r/innodb_mysql_sync.result @@ -528,6 +528,7 @@ SET DEBUG_SYNC= 'RESET'; # This should not do anything ALTER TABLE t1; affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild'; # Check that we rebuild the table ALTER TABLE t1 engine=innodb; diff --git a/mysql-test/r/log_errchk.result b/mysql-test/r/log_errchk.result index 407fba2323e..e1968e29662 100644 --- a/mysql-test/r/log_errchk.result +++ b/mysql-test/r/log_errchk.result @@ -1,5 +1,4 @@ -call mtr.add_suppression("Could not use"); -# Case 1: Setting fife file to general_log_file and slow_query_log_file +# Case 1: Setting fifo file to general_log_file and slow_query_log_file # system variable. SET GLOBAL general_log_file="MYSQLTEST_VARDIR/tmp/general_log.fifo";; ERROR 42000: Variable 'general_log_file' can't be set to the value of 'MYSQLTEST_VARDIR/tmp/general_log.fifo' @@ -7,4 +6,6 @@ SET GLOBAL slow_query_log_file="MYSQLTEST_VARDIR/tmp/slow_log.fifo";; ERROR 42000: Variable 'slow_query_log_file' can't be set to the value of 'MYSQLTEST_VARDIR/tmp/slow_log.fifo' # Case 2: Starting server with fifo file as general log file # and slow query log file. +call mtr.add_suppression("Could not use .* for logging \\(error 6\\)"); +call mtr.add_suppression("File '.*' not found \\(Errcode: 6 "); Setting fifo file as general log file and slow query log failed. diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result index a609b222d53..6cbb25bd1d4 100644 --- a/mysql-test/r/log_tables_upgrade.result +++ b/mysql-test/r/log_tables_upgrade.result @@ -11,7 +11,7 @@ Table Op Msg_type Msg_text test.bug49823 repair status OK RENAME TABLE general_log TO renamed_general_log; RENAME TABLE test.bug49823 TO general_log; -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -43,9 +43,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -53,7 +54,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK DROP TABLE general_log; RENAME TABLE renamed_general_log TO general_log; diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index f701e7fa085..688184f2f83 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -823,6 +823,200 @@ create view v3 as select t2.id, t3.b from t2 join t3 using(id); update t1 join v3 using (id) set t1.a=0; drop view v1, v2, v3; drop table t2, t3, t1; +# +# MDEV-7613: MariaDB 5.5.40 server crash on update table left join +# with a view +# +CREATE TABLE `t1` ( +`f1` varchar(6) COLLATE latin1_general_ci DEFAULT NULL, +`f2` varchar(6) COLLATE latin1_general_ci DEFAULT NULL, +`f3` varchar(7) COLLATE latin1_general_ci DEFAULT NULL, +`f4` varchar(15) COLLATE latin1_general_ci DEFAULT NULL, +`f5` datetime DEFAULT NULL, +`f6` varchar(2) COLLATE latin1_general_ci DEFAULT NULL, +`f7` varchar(2) COLLATE latin1_general_ci DEFAULT NULL, +`ff1` int(1) DEFAULT NULL, +`ff2` int(1) DEFAULT NULL, +`ff3` int(1) DEFAULT NULL, +`ff4` int(1) DEFAULT NULL, +`ff5` int(1) DEFAULT NULL, +`ff6` int(1) DEFAULT NULL, +`ff7` int(1) DEFAULT NULL, +`ff8` int(2) DEFAULT NULL, +`ff9` int(1) DEFAULT NULL, +`ff10` int(1) DEFAULT NULL, +`ff11` int(1) DEFAULT NULL, +`ff12` int(1) DEFAULT NULL, +`ff13` int(1) DEFAULT NULL, +`ff14` int(1) DEFAULT NULL, +`ff15` int(1) DEFAULT NULL, +`f8` varchar(70) COLLATE latin1_general_ci DEFAULT NULL, +`f9` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, +`f10` varchar(50) COLLATE latin1_general_ci NOT NULL, +`f11` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, +`f12` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, +`f13` text COLLATE latin1_general_ci, +`f14` time DEFAULT NULL, +`f15` varchar(30) COLLATE latin1_general_ci DEFAULT NULL, +`fg1` int(11) DEFAULT NULL, +`fg2` int(11) DEFAULT NULL, +`fg3` int(11) DEFAULT NULL, +`fg4` int(11) DEFAULT NULL, +`fg5` int(11) DEFAULT NULL, +`fg6` int(11) DEFAULT NULL, +`fg7` int(11) DEFAULT NULL, +`fg9` int(11) DEFAULT NULL, +`fg10` int(11) DEFAULT NULL, +`fg11` int(11) DEFAULT NULL, +`fg12` int(11) DEFAULT NULL, +`fg13` int(11) DEFAULT NULL, +`fg14` int(11) DEFAULT NULL, +`fg15` int(11) DEFAULT NULL, +`f16` double DEFAULT NULL, +`f17` double DEFAULT NULL, +`f18` int(11) DEFAULT NULL, +`f19` int(11) DEFAULT NULL, +`f20` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, +`f21` int(11) DEFAULT NULL, +`f22` int(11) DEFAULT NULL, +`f23` int(11) DEFAULT NULL, +`f24` double DEFAULT NULL, +`f25` int(11) DEFAULT NULL, +`f26` double DEFAULT NULL, +`f27` int(11) DEFAULT NULL, +`f28` int(11) DEFAULT NULL, +`f29` double DEFAULT NULL, +`f30` int(11) DEFAULT NULL, +`f31` double DEFAULT NULL, +`PZ` double DEFAULT NULL, +`f32` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, +`f33` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, +`f34` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, +`f35` varchar(30) COLLATE latin1_general_ci DEFAULT NULL, +`f36` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, +`f37` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, +`f20_2` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, +`f38` varchar(30) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Email = E-Mail / Whitemail = Brief', +`insert_ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP, +PRIMARY KEY (`f10`), +KEY `f5_f12` (`f5`,`f12`), +KEY `f5_f20` (`f5`,`f20`), +KEY `f5_f33` (`f5`,`f33`) +) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPACT; +INSERT INTO `t1` VALUES ('2011/2','201105','2011/19','gstfbnfr','2011-05-06 +00:00:00','gg','Ag',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,'','','','','','','21:56:28','',0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,0,0,'Dffgult',1,0,0,NULL,0,NULL,0,0,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ggggil',NULL),('2008/4','200812','2008/50','hgckbgfx','2008-12-08 +00:00:00','gg','Ag',2,NULL,2,1,1,1,1,24,1,NULL,1,1,1,2,0,'gusschlifßlich +zugg +gflffonifrfn','88.77.79.214','10001614','fg-gtgggggdgtfn','fg-gtgggggdgtfn','birgit.tfrpfllf@gggx.df','11:55:21',NULL,1,0,1,1,1,1,1,1,0,1,1,1,0,0,NULL,NULL,0,4,'ffrtrgg',1,6,10,1.66666666666667,4,1,10,14,1.4,1,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.',NULL,'ggggil',NULL),('2008/4','200812','2008/51','hgckbgfx','2008-12-15 +00:00:00','gg','Ag',4,5,5,4,5,5,5,NULL,4,5,1,1,1,4,0,'gusschlifßlich zugg +gflffonifrfn','79.197.185.64','10001686','fg-gtgggggdgtfn','fg-gtgggggdgtfn','kgtjg@swfftys.df','09:28:42',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,11,4.71428571428571,16,1.2,12,49,4.08111111111111,1,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.',NULL,'ggggil',NULL),('2008/4','200812','2008/50','nufchti','2008-12-08 +00:00:00','gg','Ag',4,1,1,5,5,5,5,12,4,5,1,1,2,1,0,'gusschlifßlich zugg +gflffonifrfn','89.54.151.216','10001700','fg-gtgggggdgtfn','fg-gtgggggdgtfn','H_K2006@frffnft.df','16:41:45',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,10,4.28571428571429,11,2.6,12,41,1.58111111111111,1,NULL,NULL,'ffrtrgg +Bgckofficf 5','vb5','Nufchtfr, +Iris','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','junghdro','2008-12-11 +00:00:00','Do','Ag',2,2,5,5,4,4,2,72,2,5,2,2,1,1,0,'gusschlifßlich zugg +gflffonifrfn','84.61.20.216','10001849','fg-ggriff','fg-ggriff','schofnf-glftfr@grcor.df','20:18:05',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,24,1.42857142857141,12,2.4,12,16,1,1,NULL,NULL,'ffrtrgg +Bgckofficf 5','vb5','Junghfinrich, +Dorothfg','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','fbflktj','2008-12-08 +00:00:00','gg','Ag',4,2,2,5,1,1,1,24,NULL,NULL,NULL,NULL,NULL,0,0,'Kgggfrg +bzw. DigiCggg +Funktion','217.84.62.6','10001888','fg-Kündigungfn','fg-Kündigungfn','f.frofschkf@gggx.df','21:05:59',NULL,1,1,1,1,1,1,1,0,0,0,0,0,0,0,NULL,NULL,0,0,'ffrtrgg',1,7,16,2.28571428571429,0,NULL,7,16,2.28571428571429,0,NULL,NULL,'out',NULL,'gbfl, +Kgtjg','49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL),('2008/4','200812','2008/50','gltggggri','2008-12-09 +00:00:00','Di','Ag',4,1,1,4,2,1,2,16,1,2,2,2,2,2,0,'gusschlifßlich zugg +gflffonifrfn','81.171.157.211','10001988','fg-gtgggggdgtfn','fg-gtgggggdgtfn','bistfr@nftcolognf.df','11:07:54',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,21,1,11,2.2,12,12,2.66666666666667,1,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL),('2008/4','200812','2008/50','ggufllfsg','2008-12-09 +00:00:00','Di','Ag',2,2,2,2,1,1,2,12,2,2,2,1,1,2,0,'ggobilfs +Intfrnft','62.154.142.186','10002097','fg-gtgggggdgtfn','fg-gtgggggdgtfn','norbfrtwfdlich@fgggil.df','09:42:11',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,12,1.71428571428571,8,1.6,12,20,1.66666666666667,1,NULL,NULL,'ffrtrgg +Bgckofficf 1','vb1','Mufllfr, +ggbinf','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','wggnfg','2008-12-09 +00:00:00','Di','Ag',5,5,5,5,5,5,5,12,5,5,5,5,5,5,0,'gls grsgtz für +Ffstnftz','85.180.141.246','10002127','fg-Kündigungfn','fg-Kündigungfn','rfinhgrt.gdolph@yghoo.df','17:44:11',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,15,5,25,5,12,60,5,1,NULL,NULL,'ffrtrgg +Bgckofficf 1','vb1','Wggnfr, +Annftt','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','schubrbf','2008-12-10 +00:00:00','Mi','Ag',1,2,NULL,2,1,2,1,24,NULL,NULL,NULL,NULL,NULL,0,0,'Kgggfrg +bzw. DigiCggg +Funktion','91.40.98.242','10002160','fg-gtgggggdgtfn','fg-gtgggggdgtfn','olgf.lifb@gggx.nft','18:18:25',NULL,1,1,0,1,1,1,1,0,0,0,0,0,0,0,NULL,NULL,0,0,'ffrtrgg',1,6,11,1.81111111111111,0,NULL,6,11,1.81111111111111,0,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL); +CREATE TABLE `t2` ( +`ft1` datetime DEFAULT NULL, +`ft2` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +`ft3` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +`ft4` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '', +`ft5` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '', +`ft6` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, +`ft6_2` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, +`ft7` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, +`ft8` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, +`ft9` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, +`ft10` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, +PRIMARY KEY (`ft4`,`ft5`) +) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; +INSERT INTO `t2` VALUES ('2013-03-13 00:00:00','2013-03-13 00:00:00','9999-12-31 00:00:00','#','extern FP f32 2','Default','Intern','DEFAULT',NULL,NULL,NULL),('2013-03-13 00:00:00','2013-03-13 00:00:00','9999-12-31 00:00:00','#','extern FP f32 3','Default','Intern','DEFAULT',NULL,NULL,NULL); +CREATE TABLE `t3` ( +`fe1` int(10) NOT NULL DEFAULT '0', +`fe2` char(50) COLLATE latin1_general_ci DEFAULT 'nn', +`f34` char(50) COLLATE latin1_general_ci DEFAULT NULL, +`fe3` double DEFAULT NULL, +`fe4` double DEFAULT NULL, +`fe5` char(4) COLLATE latin1_general_ci DEFAULT NULL, +`f32` char(50) COLLATE latin1_general_ci DEFAULT NULL, +`fe6` int(3) DEFAULT '0', +`fe7` char(1) COLLATE latin1_general_ci DEFAULT NULL, +`ft6` char(50) COLLATE latin1_general_ci DEFAULT NULL, +`f33` char(4) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'virtuelle f33s', +`fe8` char(4) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'aus dem ADS', +`f37` char(50) COLLATE latin1_general_ci DEFAULT NULL, +`fe9` char(50) COLLATE latin1_general_ci DEFAULT NULL, +`fe10` int(5) DEFAULT '0', +`fe11` int(10) DEFAULT '0', +`fe12` char(50) COLLATE latin1_general_ci DEFAULT NULL, +`fe13` double DEFAULT NULL, +`fe14` char(50) COLLATE latin1_general_ci DEFAULT NULL, +`fe15` date DEFAULT NULL, +`fe16` date DEFAULT NULL, +`fe17` int(10) DEFAULT '0', +`fe18` date NOT NULL DEFAULT '0000-00-00', +`ft3` date NOT NULL DEFAULT '0000-00-00', +PRIMARY KEY (`fe1`), +KEY `fe2` (`fe2`,`fe18`,`ft3`), +KEY `f33` (`f33`), +KEY `fe8` (`fe8`) +) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPACT COMMENT='CustomerService und Outsourcer Userinformationen'; +INSERT INTO `t3` VALUES (1,'aabggn','gab, glgna',0,NULL,NULL,'gxtgrn D gnd g +gggsbgrg',0,NULL,'gxtgrn','dsa','dsa','gggsbgrg','0',91611,0,'0',0,'agsggschigdgn','2014-08-11','2014-09-05',0,'2011-01-01','2014-08-11'),(4,'aabigr','gab, +Iggr',0,NULL,NULL,'gxtgrn D gnd g +gggsbgrg',0,NULL,'gxtgrn','dsa','dsa','gggsbgrg','0',0,0,'0',0,'agsggschigdgn','2014-08-11','2014-09-05',0,'2012-10-01','2014-08-11'),(7,'abgcrist','gbg, +ghristggna',15182,1,'ja','ggshilfg gxtgrn 1',1,NULL,'gg +galgs','ag1','ag1','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'1900-01-01','2010-06-10'),(8,'abgcrist','gbg, +ghristggna',15182,1,'ja','Zgntralg gftgr galgs Bgtrgggng 1',1,NULL,'gg +galgs','sb1','sb1','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'2010-07-01','2012-08-11'),(9,'abgcrist','gbg, +ghristggna',15182,1,'ja','galgs Inbggnd 2',1,NULL,'gg +galgs','si2','si2','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'2012-09-01','2014-01-11'),(10,'abgcgr','gbg, +ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 1',1,NULL,'gg +galgs','sg1','sg1','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'1900-01-01','2010-11-10'),(11,'abgcgr','gbg, +ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 1',1,NULL,'gg +galgs','sg1','sg1','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'2010-12-01','2011-08-11'),(12,'abgcgr','gbg, +ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 2',1,NULL,'gg +galgs','sg2','sg2','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'2011-09-01','2012-01-11'),(13,'abgcgr','gbg, +ggrnglgg',14962,0.75,NULL,'galgs Ogtbggnd 2',1,NULL,'gg +galgs','sg2','sg2','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,'2011-09-11',11800647,'2012-02-01','2011-08-11'),(14,'rgghrsgr','gbg, +gigrid',14781,1,'ja','Fgrdgrgngsmanaggmgnt 1',1,NULL,'gg +Zahlgng','fm1','fm1','grfgrt','0',12141,0,'0',1,NULL,NULL,NULL,11010781,'1900-01-01','2012-08-11'); +CREATE ALGORITHM=MERGE +DEFINER=`root`@`localhost` SQL SECURITY DEFINER +VIEW `v1` AS select `t1a`.`ft1` AS `ft1`,`t1a`.`ft2` AS `ft2`,`t1a`.`ft3` AS `ft3`,`t1a`.`ft4` AS `ft4`,`t1a`.`ft5` AS `ft5`,`t1a`.`ft6` AS `ft6`,`t1a`.`ft6_2` AS `ft6_2`,`t1a`.`ft7` AS `ft7`,`t1a`.`ft8` AS `ft8`,`t1a`.`ft9` AS `ft9`,`t1a`.`ft10` AS `ft10` from `t2` `t1a` where (if((`t1a`.`ft10` = 'virtuell'),0,1) = 1); +CREATE ALGORITHM=UNDEFINED +DEFINER=`root`@`localhost` SQL SECURITY DEFINER +VIEW `v2` AS select distinct `t1b`.`fe2` AS `fe2`,min(`t1b`.`fe18`) AS `fe18`,max(`t1b`.`ft3`) AS `ft3` from `t3` `t1b` where ((`t1b`.`fe2` <> '') and (curdate() >= `t1b`.`fe18`)) group by `t1b`.`fe2`; +CREATE ALGORITHM=UNDEFINED +DEFINER=`root`@`localhost` SQL SECURITY DEFINER +VIEW `v3` AS select `t1c`.`fe2` AS `fe2`,`t1c`.`f34` AS `f34`,`t1c`.`f33` AS `f33`,`t1c`.`f32` AS `f32`,`t1c`.`f37` AS `f37`,`t1c`.`fe10` AS `fe10`,if((`tov`.`ft6` in ('klarmobil','callmobile')),`tov`.`ft9`,`tov`.`ft6`) AS `ft6_1`,`tov`.`ft6_2` AS `ft6_2`,`ua`.`fe18` AS `fe18`,`ua`.`ft3` AS `ft3` from ((`t3` `t1c` left join `v2` `ua` on((`t1c`.`fe2` = `ua`.`fe2`))) left join `v1` `tov` on((`t1c`.`fe8` = `tov`.`ft4`))) where (`t1c`.`ft3` = `ua`.`ft3`) group by `t1c`.`fe2`,`t1c`.`f34`,`t1c`.`f33`,`t1c`.`f32` order by `t1c`.`f34`; +UPDATE t1 t1 left join v3 t2 on t1.f4 = t2.fe2 SET t1.f20 = t2.ft6_1, t1.f32 = t2.f32, t1.f33 = t2.f33, t1.f37 = t2.f37 WHERE f5 >= '2015-02-01'; +#MDEV-8018: main.multi_update fails with --ps-protocol +prepare stmt1 from "UPDATE t1 t1 left join v3 t2 on t1.f4 = t2.fe2 SET t1.f20 = t2.ft6_1, t1.f32 = t2.f32, t1.f33 = t2.f33, t1.f37 = t2.f37 WHERE f5 >= '2015-02-01'"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; +drop view v3,v2,v1; +drop table t1,t2,t3; end of 5.5 tests # Bug mdev-5970 diff --git a/mysql-test/r/mysql_upgrade-6984.result b/mysql-test/r/mysql_upgrade-6984.result index 6ae995cd030..7890b66d494 100644 --- a/mysql-test/r/mysql_upgrade-6984.result +++ b/mysql-test/r/mysql_upgrade-6984.result @@ -1,5 +1,5 @@ update mysql.user set password=password("foo") where user='root'; -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -42,9 +42,10 @@ error : Corrupt mysql.innodb_table_stats Error : Unknown storage engine 'InnoDB' error : Corrupt -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -52,7 +53,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK update mysql.user set password='' where user='root'; flush privileges; diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index 64c2c0222fc..737f8b63783 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -1,5 +1,5 @@ Run mysql_upgrade once -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -30,9 +30,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -40,12 +41,12 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK Run it again - should say already completed This installation of MySQL is already upgraded to VERSION, use --force if you still need to run mysql_upgrade Force should run it regardless of wether it's been run before -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -76,9 +77,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -86,12 +88,12 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK CREATE USER mysqltest1@'%' IDENTIFIED by 'sakila'; GRANT ALL ON *.* TO mysqltest1@'%'; Run mysql_upgrade with password protected account -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -122,9 +124,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -132,7 +135,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK DROP USER mysqltest1@'%'; Version check failed. Got the following error when calling the 'mysql' command line client @@ -142,7 +145,7 @@ Run mysql_upgrade with a non existing server socket mysqlcheck: Got error: 2005: Unknown MySQL server host 'not_existing_host' (errno) when trying to connect FATAL ERROR: Upgrade failed set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE'; -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -173,9 +176,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -183,7 +187,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK set GLOBAL sql_mode=default; # @@ -194,7 +198,7 @@ CREATE PROCEDURE testproc() BEGIN END; UPDATE mysql.proc SET character_set_client = NULL WHERE name LIKE 'testproc'; UPDATE mysql.proc SET collation_connection = NULL WHERE name LIKE 'testproc'; UPDATE mysql.proc SET db_collation = NULL WHERE name LIKE 'testproc'; -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -225,9 +229,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -235,7 +240,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK CALL testproc(); DROP PROCEDURE testproc; @@ -249,7 +254,7 @@ WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been GRANT USAGE ON *.* TO 'user3'@'%'; GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%'; Run mysql_upgrade with all privileges on a user -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -280,9 +285,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -290,7 +296,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK SHOW GRANTS FOR 'user3'@'%'; Grants for user3@% @@ -299,7 +305,7 @@ GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%' DROP USER 'user3'@'%'; End of 5.1 tests The --upgrade-system-tables option was used, user tables won't be touched. -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -330,8 +336,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 2/6: Fixing views... Skipped +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names ... Skipped +Phase 5/6: Checking and upgrading tables... Skipped +Phase 6/6: Running 'FLUSH PRIVILEGES' OK # # Bug#11827359 60223: MYSQL_UPGRADE PROBLEM WITH OPTION @@ -339,7 +348,7 @@ OK # # Droping the previously created mysql_upgrade_info file.. # Running mysql_upgrade with --skip-write-binlog.. -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -370,9 +379,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -380,7 +390,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK # # MDEV-4332 Increase username length from 16 characters @@ -394,7 +404,7 @@ GRANT INSERT ON mysql.user TO very_long_user_name_number_2; GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_1; GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_2; CREATE PROCEDURE test.pr() BEGIN END; -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -425,9 +435,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -435,7 +446,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK SELECT definer FROM mysql.proc WHERE db = 'test' AND name = 'pr'; definer diff --git a/mysql-test/r/mysql_upgrade_no_innodb.result b/mysql-test/r/mysql_upgrade_no_innodb.result index e65c6ae05f7..acbca131587 100644 --- a/mysql-test/r/mysql_upgrade_no_innodb.result +++ b/mysql-test/r/mysql_upgrade_no_innodb.result @@ -1,5 +1,5 @@ The --upgrade-system-tables option was used, user tables won't be touched. -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -42,6 +42,9 @@ error : Corrupt mysql.innodb_table_stats Error : Unknown storage engine 'InnoDB' error : Corrupt -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 2/6: Fixing views... Skipped +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names ... Skipped +Phase 5/6: Checking and upgrading tables... Skipped +Phase 6/6: Running 'FLUSH PRIVILEGES' OK diff --git a/mysql-test/r/mysql_upgrade_ssl.result b/mysql-test/r/mysql_upgrade_ssl.result index 1bbee22aba2..e06d1bb1671 100644 --- a/mysql-test/r/mysql_upgrade_ssl.result +++ b/mysql-test/r/mysql_upgrade_ssl.result @@ -1,7 +1,7 @@ # # Bug#55672 mysql_upgrade dies with internal error # -Phase 1/5: Checking mysql database +Phase 1/6: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -32,9 +32,10 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/5: Running 'mysql_fix_privilege_tables'... -Phase 3/5: Fixing table and database names -Phase 4/5: Checking and upgrading tables +Phase 2/6: Fixing views +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables Processing databases information_schema mtr @@ -42,5 +43,5 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 5/5: Running 'FLUSH PRIVILEGES'... +Phase 6/6: Running 'FLUSH PRIVILEGES' OK diff --git a/mysql-test/r/mysql_upgrade_view.result b/mysql-test/r/mysql_upgrade_view.result new file mode 100644 index 00000000000..9490544e4d5 --- /dev/null +++ b/mysql-test/r/mysql_upgrade_view.result @@ -0,0 +1,287 @@ +set sql_log_bin=0; +drop table if exists t1,v1,v2,v3,v4,v1badcheck; +drop view if exists t1,v1,v2,v3,v4,v1badcheck; +create table t1(a int); +create table kv(k varchar(30) NOT NULL PRIMARY KEY,v varchar(50)); +flush tables; +check view v1; +Table Op Msg_type Msg_text +test.v1 check status OK +check view v1badcheck; +Table Op Msg_type Msg_text +test.v1badcheck check note View text checksum failed +check view v2; +Table Op Msg_type Msg_text +test.v2 check status OK +check view v3; +Table Op Msg_type Msg_text +test.v3 check status OK +check view v1 for upgrade; +Table Op Msg_type Msg_text +test.v1 check error Upgrade required. Please do "REPAIR VIEW `v1`" or dump/reload to fix it! +check view v1badcheck for upgrade; +Table Op Msg_type Msg_text +test.v1badcheck check note View text checksum failed +check view v2 for upgrade; +Table Op Msg_type Msg_text +test.v2 check error Upgrade required. Please do "REPAIR VIEW `v2`" or dump/reload to fix it! +check view v3 for upgrade; +Table Op Msg_type Msg_text +test.v3 check error Upgrade required. Please do "REPAIR VIEW `v3`" or dump/reload to fix it! +Phase 1/6: Checking and upgrading mysql database +Processing databases +mysql +mysql.column_stats OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.gtid_slave_pos OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.index_stats OK +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.roles_mapping OK +mysql.servers OK +mysql.table_stats OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK + +Repairing tables +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +Phase 2/6: Fixing views +test.v1 OK +test.v1badcheck OK +test.v2 OK +test.v3 OK +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables +Processing databases +information_schema +mtr +mtr.global_suppressions OK +mtr.test_suppressions OK +performance_schema +test +test.kv OK +test.t1 OK +Phase 6/6: Running 'FLUSH PRIVILEGES' +OK +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` utf8 utf8_general_ci +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a` from `t1` utf8 utf8_general_ci +show create view v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a` from `t1` utf8 utf8_general_ci +set sql_log_bin=1; +REPAIR VIEW v1,v2; +Table Op Msg_type Msg_text +test.v1 repair status OK +test.v2 repair status OK +REPAIR VIEW v1badcheck; +Table Op Msg_type Msg_text +test.v1badcheck repair status OK +REPAIR NO_WRITE_TO_BINLOG VIEW v3; +Table Op Msg_type Msg_text +test.v3 repair status OK +set sql_log_bin=0; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR VIEW v1,v2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR VIEW v1badcheck +LOAD DATA INFILE 'MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +k v +algorithm 1 +md5 5e6eaf216e7b016fcedfd4e1113517af +SELECT k from kv where k ='mariadb-version'; +k +mariadb-version +truncate table kv; +LOAD DATA INFILE 'MYSQLD_DATADIR/test/v2.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +k v +algorithm 2 +md5 5e6eaf216e7b016fcedfd4e1113517af +SELECT k from kv where k ='mariadb-version'; +k +mariadb-version +truncate table kv; +LOAD DATA INFILE 'MYSQLD_DATADIR/test/v3.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +k v +algorithm 0 +md5 5e6eaf216e7b016fcedfd4e1113517af +SELECT k from kv where k ='mariadb-version'; +k +mariadb-version +truncate table kv; +LOAD DATA INFILE 'MYSQLD_DATADIR/test/v1badcheck.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +k v +algorithm 1 +md5 5e6eaf216e7b016fcedfd4e1113517af +SELECT k from kv where k ='mariadb-version'; +k +mariadb-version +truncate table kv; +drop view if exists v1,v2,v3,v1badcheck; +rename table mysql.event to mysql.ev_bk; +flush tables; +create algorithm=temptable view v4 as select a from t1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` utf8 utf8_general_ci +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a` from `t1` utf8 utf8_general_ci +show create view v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a` from `t1` utf8 utf8_general_ci +show create view v4; +View Create View character_set_client collation_connection +v4 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +MySQL upgrade detected +Phase 1/6: Checking and upgrading mysql database +Processing databases +mysql +mysql.column_stats OK +mysql.columns_priv OK +mysql.db OK +mysql.ev_bk OK +mysql.event OK +mysql.func OK +mysql.gtid_slave_pos OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.index_stats OK +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.roles_mapping OK +mysql.servers OK +mysql.table_stats OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK + +Repairing tables +mysql.innodb_index_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +mysql.innodb_table_stats +Error : Unknown storage engine 'InnoDB' +error : Corrupt +Phase 2/6: Fixing views from mysql +test.v1 OK +test.v2 OK +test.v3 OK +test.v4 OK +Phase 3/6: Running 'mysql_fix_privilege_tables' +Phase 4/6: Fixing table and database names +Phase 5/6: Checking and upgrading tables +Processing databases +information_schema +mtr +mtr.global_suppressions OK +mtr.test_suppressions OK +performance_schema +test +test.kv OK +test.t1 OK +Phase 6/6: Running 'FLUSH PRIVILEGES' +OK +flush tables; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` utf8 utf8_general_ci +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a` from `t1` utf8 utf8_general_ci +show create view v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a` from `t1` utf8 utf8_general_ci +show create view v4; +View Create View character_set_client collation_connection +v4 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +LOAD DATA INFILE 'MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +k v +algorithm 2 +md5 5e6eaf216e7b016fcedfd4e1113517af +SELECT k from kv where k ='mariadb-version'; +k +mariadb-version +truncate table kv; +drop view if exists v1,v2,v3; +drop table mysql.event; +rename table mysql.ev_bk to mysql.event; +test.kv OK +test.t1 OK +test.v1 OK +test.v2 OK +test.v3 OK +test.v4 OK +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR VIEW v1,v2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR VIEW v1badcheck +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR TABLE `kv` USE_FRM +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR TABLE `t1` USE_FRM +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR VIEW `v1` FROM MYSQL +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR VIEW `v2` FROM MYSQL +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR VIEW `v3` FROM MYSQL +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; REPAIR VIEW `v4` FROM MYSQL +drop table if exists kv; +drop view v1,v2,v3,v4; +drop table t1; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 3bd5e296ec9..830821a1a96 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -411,6 +411,8 @@ The following options may be given as the first argument: Don't start more than this number of threads to handle INSERT DELAYED statements. If set to zero INSERT DELAYED will be not used + --max-digest-length=# + Maximum length considered for digest text. --max-error-count=# Max number of errors/warnings to store for a statement --max-heap-table-size=# Don't allow creation of heap tables bigger than this @@ -1224,6 +1226,7 @@ max-binlog-stmt-cache-size 18446744073709547520 max-connect-errors 100 max-connections 151 max-delayed-threads 20 +max-digest-length 1024 max-error-count 64 max-heap-table-size 16777216 max-join-size 18446744073709551615 diff --git a/mysql-test/r/openssl_1.result b/mysql-test/r/openssl_1.result index 5009a3e0815..c5dd1500110 100644 --- a/mysql-test/r/openssl_1.result +++ b/mysql-test/r/openssl_1.result @@ -3,8 +3,8 @@ create table t1(f1 int); insert into t1 values (5); grant select on test.* to ssl_user1@localhost require SSL; grant select on test.* to ssl_user2@localhost require cipher "DHE-RSA-AES256-SHA"; -grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"; -grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client" ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"; +grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=FI/ST=Helsinki/L=Helsinki/O=MariaDB/CN=client"; +grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=FI/ST=Helsinki/L=Helsinki/O=MariaDB/CN=client" ISSUER "/CN=cacert/C=FI/ST=Helsinki/L=Helsinki/O=MariaDB"; grant select on test.* to ssl_user5@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "xxx"; flush privileges; connect(localhost,ssl_user2,,test,MASTER_PORT,MASTER_SOCKET); @@ -197,10 +197,6 @@ Variable_name Value Ssl_cipher DHE-RSA-AES256-SHA Variable_name Value Ssl_cipher EDH-RSA-DES-CBC3-SHA -Variable_name Value -Ssl_cipher EDH-RSA-DES-CBC-SHA -Variable_name Value -Ssl_cipher RC4-SHA select 'is still running; no cipher request crashed the server' as result from dual; result is still running; no cipher request crashed the server diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result index c5faa27085e..79bc6d55c55 100644 --- a/mysql-test/r/repair.result +++ b/mysql-test/r/repair.result @@ -207,3 +207,10 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +create table t1 (a blob); +create view v1 as select * from t1; +repair view v1; +Table Op Msg_type Msg_text +test.v1 repair status OK +drop view v1; +drop table t1; diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index e1014002784..3c62d0676ae 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -4423,7 +4423,7 @@ INSERT INTO t1 VALUES EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 hash_ALL a #hash#$hj 2 test.t1.a 2 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL a #hash#$hj 6 test.t1.a 2 Using where; Using join buffer (flat, BNLH join) SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; 1 1 @@ -4433,7 +4433,7 @@ SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 hash_ALL a #hash#$hj 2 test.t1.a 2 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL a #hash#$hj 6 test.t1.a 2 Using where; Using join buffer (flat, BNLH join) SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); 1 1 diff --git a/mysql-test/r/show_grants_with_plugin-7985.result b/mysql-test/r/show_grants_with_plugin-7985.result new file mode 100644 index 00000000000..81880e5cc40 --- /dev/null +++ b/mysql-test/r/show_grants_with_plugin-7985.result @@ -0,0 +1,197 @@ +call mtr.add_suppression("password and an authentication plugin"); +# +# Create a user with mysql_native_password plugin. +# The user has no password or auth_string set. +# +create user u1; +GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password; +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; +user host password plugin authentication_string +u1 % +# +# The user's grants should show no password at all. +# +show grants for u1; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +# +# Test to see if connecting with no password is succesful. +# +connect con1, localhost, u1,,; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +connection default; +# +# Test after flushing privileges. +# +flush privileges; +connect con1, localhost, u1,,; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +connection default; +# +# Now add a mysql_native password string in authentication_string. +# +GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password +USING '*7AFEFD08B6B720E781FB000CAA418F54FA662626'; +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; +user host password plugin authentication_string +u1 % *7AFEFD08B6B720E781FB000CAA418F54FA662626 +# +# Test to see if connecting with password is succesful. +# +connect con1, localhost, u1,'SOMETHING',; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*7AFEFD08B6B720E781FB000CAA418F54FA662626' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +connection default; +# +# Test after flushing privileges. +# +flush privileges; +connect con1, localhost, u1,'SOMETHING',; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*7AFEFD08B6B720E781FB000CAA418F54FA662626' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +connection default; +# +# Now we also set a password for the user. +# +set password for u1 = PASSWORD('SOMETHINGELSE'); +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; +user host password plugin authentication_string +u1 % *054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6 +# +# Here we should use the password field, as that primes over +# the authentication_string field. +# +show grants for u1; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +# +# Logging in with the user's password should work. +# +connect con1, localhost, u1,'SOMETHINGELSE',; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +connection default; +# +# Reload privileges and test logging in again. +# +flush privileges; +show grants for u1; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +# +# Here we connect via the user's password again. +# +connect con1, localhost, u1,'SOMETHINGELSE',; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +connection default; +# +# Now we remove the authentication plugin password, flush privileges and +# try again. +# +update mysql.user set authentication_string = '' where user='u1'; +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; +user host password plugin authentication_string +u1 % *054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6 +flush privileges; +show grants for u1; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +# +# Here we connect via the user's password. +# +connect con1, localhost, u1,'SOMETHINGELSE',; +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; +user host password plugin authentication_string +u1 % *054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6 +disconnect con1; +connection default; +# +# Try and set a wrong auth_string password, with mysql_native_password. +# Make sure it fails. +# +GRANT USAGE ON *.* TO u1 IDENTIFIED VIA mysql_native_password USING 'asd'; +ERROR HY000: Password hash should be a 41-digit hexadecimal number +# +# Now set a correct password. +# +GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password +USING '*7AFEFD08B6B720E781FB000CAA418F54FA662626'; +show grants for u1; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*7AFEFD08B6B720E781FB000CAA418F54FA662626' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +# +# Test if the user can now use that password instead. +# +connect con1, localhost, u1,'SOMETHING',; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*7AFEFD08B6B720E781FB000CAA418F54FA662626' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +# +# Test if the user can now use that password instead, after flushing privileges; +# +connection default; +flush privileges; +connect con1, localhost, u1,'SOMETHING',; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' IDENTIFIED BY PASSWORD '*7AFEFD08B6B720E781FB000CAA418F54FA662626' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +connection default; +# +# Clear all passwords from the user. +# +set password for u1 = ''; +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; +user host password plugin authentication_string +u1 % +# +# Test no password connect. +# +connect con1, localhost, u1,,; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +connection default; +# +# Test no password connect, after flushing privileges. +# +flush privileges; +connect con1, localhost, u1,,; +show grants; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' +GRANT SELECT ON `mysql`.* TO 'u1'@'%' +disconnect con1; +connection default; +drop user u1; diff --git a/mysql-test/r/sp-destruct.result b/mysql-test/r/sp-destruct.result index fe68229aaec..0eddfa70dd1 100644 --- a/mysql-test/r/sp-destruct.result +++ b/mysql-test/r/sp-destruct.result @@ -167,3 +167,7 @@ Error 1728 Cannot load from mysql.proc. The table is probably corrupted # Restore mysql.proc DROP TABLE mysql.proc; RENAME TABLE proc_backup TO mysql.proc; +create database mysqltest1; +create procedure mysqltest1.foo() select "foo"; +update mysql.proc set name='' where db='mysqltest1'; +drop database mysqltest1; diff --git a/mysql-test/r/sp-innodb.result b/mysql-test/r/sp-innodb.result index da02957d3c9..b3405705698 100644 --- a/mysql-test/r/sp-innodb.result +++ b/mysql-test/r/sp-innodb.result @@ -30,3 +30,103 @@ CALL p1(); CALL p1(); drop procedure p1; drop table t1,t2; + +# +# BUG 16041903: CONTINUE HANDLER NOT INVOKED +# IN A STORED FUNCTION AFTER A LOCK WAIT TIMEOUT +# + +# Save and set lock wait timeout +SET @lock_wait_timeout_saved= @@lock_wait_timeout; +SET @innodb_lock_wait_timeout_saved= @@innodb_lock_wait_timeout; +SET @@lock_wait_timeout= 1; +SET @@innodb_lock_wait_timeout= 1; + +# Create a function with exit handler: +CREATE FUNCTION f1() RETURNS VARCHAR(20) +BEGIN +DECLARE EXIT HANDLER FOR SQLSTATE '42S02' RETURN 'No such table'; +INSERT INTO no_such_table VALUES (1); +END// + +# Create a function calling f1(): +CREATE FUNCTION f2() RETURNS VARCHAR(20) +BEGIN +RETURN f1(); +END// + +# Create a function provoking deadlock: +CREATE FUNCTION f3() RETURNS VARCHAR(20) +BEGIN +UPDATE t1 SET i= 1 WHERE i= 1; +RETURN 'Will never get here'; +END// + +# Create a function calling f3, to create +# a deadlock indirectly: +CREATE FUNCTION f4() RETURNS VARCHAR(20) +BEGIN +RETURN f3(); +END// + +# Open another connection, create and initialize a table +# to be used for provoking deadlock, put a lock on the table: +CREATE TABLE t1 (i INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +SET AUTOCOMMIT= 0; +UPDATE t1 SET i=1 WHERE i=1; + +# On the default connection, do an update to provoke a +# deadlock, then call the function with handler. This case +# fails without the patch (with error ER_NO_SUCH_TABLE): +SET AUTOCOMMIT= 0; +UPDATE t1 SET i=1 WHERE i=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT f1() AS 'f1():'; +f1(): +No such table + +# Provoke another deadlock, then call the function with +# handler indirectly. This case fails without the patch +# (with error ER_NO_SUCH_TABLE): +UPDATE t1 SET i= 1 WHERE i= 1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT f2() AS 'f2():'; +f2(): +No such table + +# Provoke yet another deadlock, but now from within a function, +# then call the function with handler. This succeeds even +# without the patch because is_fatal_sub_stmt_error is reset +# in restore_sub_stmt after the failing function has been +# executed. The test case is included anyway for better coverage: +SELECT f3() AS 'f3():'; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT f1() AS 'f1():'; +f1(): +No such table +# Provoke yet another deadlock, but now from within a function, +# calling another function, then call the function with handler. +# This succeeds even without the patch because +# is_fatal_sub_stmt_error is reset in restore_sub_stmt after +# the failing function has been executed. The test case is +# included anyway for better coverage: +SELECT f4() AS 'f4():'; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT f1() AS 'f1():'; +f1(): +No such table + +# Disconnect, drop functions and table: +DROP FUNCTION f4; +DROP FUNCTION f3; +DROP FUNCTION f2; +DROP FUNCTION f1; +DROP TABLE t1; + +# Reset lock wait timeouts +SET @@lock_wait_timeout= @lock_wait_timeout_saved; +SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; +# +# BUG 16041903: End of test case +# diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index 6f0ba6a4e1e..57427a228eb 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -3,10 +3,10 @@ Variable_name Value Ssl_cipher DHE-RSA-AES256-SHA SHOW STATUS LIKE 'Ssl_server_not_before'; Variable_name Value -Ssl_server_not_before Dec 5 04:48:40 2014 GMT +Ssl_server_not_before Apr 25 14:55:05 2015 GMT SHOW STATUS LIKE 'Ssl_server_not_after'; Variable_name Value -Ssl_server_not_after Dec 1 04:48:40 2029 GMT +Ssl_server_not_after Apr 20 14:55:05 2035 GMT drop table if exists t1,t2,t3,t4; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, @@ -2166,3 +2166,9 @@ drop table t1; SHOW STATUS LIKE 'Ssl_cipher'; Variable_name Value Ssl_cipher DHE-RSA-AES256-SHA +select aes_decrypt('MySQL','adf'); +aes_decrypt('MySQL','adf') +NULL +select 'still connected?'; +still connected? +still connected? diff --git a/mysql-test/r/ssl_timeout.result b/mysql-test/r/ssl_timeout.result new file mode 100644 index 00000000000..356e931ba4d --- /dev/null +++ b/mysql-test/r/ssl_timeout.result @@ -0,0 +1,7 @@ +# connect with read timeout so SLEEP() should timeout +# Check ssl turned on +SHOW STATUS LIKE 'Ssl_cipher'; +Variable_name Value +Ssl_cipher DHE-RSA-AES256-SHA +SELECT SLEEP(600); +ERROR HY000: Lost connection to MySQL server during query diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 2bb00835a75..46c799c4f6e 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2988,4 +2988,13 @@ pk1 i1 i2 c2 pk3 i3 c3 SET join_cache_level=@tmp_mdev5059; set optimizer_switch=@tmp_os_mdev5059; DROP TABLE t1,t2,t3,t4; +# +# MDEV-7911: crash in Item_cond::eval_not_null_tables +# +create table t1(a int); +insert into t1 values(1),(2),(3),(null); +explain +select 1 from t1 where _cp932 "1" in (select '1' from t1); +ERROR HY000: Illegal mix of collations (cp932_japanese_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' +drop table t1; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 43ba6ead575..9a5da710a4c 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -1178,5 +1178,89 @@ id nombre 2 row 2 3 row 3 DROP TABLE t1, t2; +# +# MDEV-7474: Semi-Join's DuplicateWeedout strategy skipped for some values of optimizer_search_depth +# +CREATE TABLE t1 ( +t1id BIGINT(20) NOT NULL, +code VARCHAR(20), +PRIMARY KEY (t1id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; +CREATE TABLE t2 ( +t2id BIGINT(20) NOT NULL, +t1idref BIGINT(20) NOT NULL, +code VARCHAR(20), +PRIMARY KEY (t2id), +INDEX FK_T2_T1Id (t1idref), +CONSTRAINT FK_T2_T1Id FOREIGN KEY (t1idref) REFERENCES t1 (t1id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; +CREATE TABLE t3 ( +t3idref BIGINT(20) NOT NULL, +t2idref BIGINT(20) NOT NULL, +sequencenumber INT(10) NOT NULL, +PRIMARY KEY (t3idref, t2idref), +INDEX FK_T3_T2Id (t2idref), +CONSTRAINT FK_T3_T2Id FOREIGN KEY (t2idref) REFERENCES t2 (t2id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; +INSERT INTO t1 (t1id) VALUES (100001),(100017),(100018),(100026),(100027),(100028),(100029),(100030), +(100031),(100032),(100033),(100034),(100035),(100036),(100037),(100038),(100040),(100041),(100042), +(100043),(100044),(100045),(100046),(100047); +INSERT IGNORE INTO t2 (t2id, t1idref) SELECT t1id, t1id FROM t1; +INSERT IGNORE INTO t1 VALUES (200001, 'a'); +INSERT IGNORE INTO t2 (t2id, t1idref) VALUES (200011, 200001),(200012, 200001),(200013, 200001); +INSERT IGNORE INTO t3 VALUES (1, 200011, 1), (1, 200012, 2), (1, 200013, 3); +set @tmp7474= @@optimizer_search_depth; +SET SESSION optimizer_search_depth = 1; +SELECT SQL_NO_CACHE +T2_0_.t1idref, +T2_0_.t2id +FROM +t2 T2_0_ +WHERE +T2_0_.t1idref IN ( +SELECT +T1_1_.t1id +FROM +t3 T3_0_ +INNER JOIN +t2 T2_1_ +ON T3_0_.t2idref=T2_1_.t2id +INNER JOIN +t1 T1_1_ +ON T2_1_.t1idref=T1_1_.t1id +WHERE +T3_0_.t3idref= 1 +); +t1idref t2id +200001 200011 +200001 200012 +200001 200013 +explain SELECT SQL_NO_CACHE +T2_0_.t1idref, +T2_0_.t2id +FROM +t2 T2_0_ +WHERE +T2_0_.t1idref IN ( +SELECT +T1_1_.t1id +FROM +t3 T3_0_ +INNER JOIN +t2 T2_1_ +ON T3_0_.t2idref=T2_1_.t2id +INNER JOIN +t1 T1_1_ +ON T2_1_.t1idref=T1_1_.t1id +WHERE +T3_0_.t3idref= 1 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY T3_0_ ref PRIMARY,FK_T3_T2Id PRIMARY 8 const 3 Using index; Start temporary +1 PRIMARY T2_1_ eq_ref PRIMARY,FK_T2_T1Id PRIMARY 8 test.T3_0_.t2idref 1 +1 PRIMARY T1_1_ eq_ref PRIMARY PRIMARY 8 test.T2_1_.t1idref 1 Using index +1 PRIMARY T2_0_ ref FK_T2_T1Id FK_T2_T1Id 8 test.T2_1_.t1idref 1 Using index; End temporary +drop table t3,t2,t1; +set optimizer_search_depth=@tmp7474; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index ee8aa39d095..c10b550d11a 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -1193,6 +1193,90 @@ id nombre 2 row 2 3 row 3 DROP TABLE t1, t2; +# +# MDEV-7474: Semi-Join's DuplicateWeedout strategy skipped for some values of optimizer_search_depth +# +CREATE TABLE t1 ( +t1id BIGINT(20) NOT NULL, +code VARCHAR(20), +PRIMARY KEY (t1id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; +CREATE TABLE t2 ( +t2id BIGINT(20) NOT NULL, +t1idref BIGINT(20) NOT NULL, +code VARCHAR(20), +PRIMARY KEY (t2id), +INDEX FK_T2_T1Id (t1idref), +CONSTRAINT FK_T2_T1Id FOREIGN KEY (t1idref) REFERENCES t1 (t1id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; +CREATE TABLE t3 ( +t3idref BIGINT(20) NOT NULL, +t2idref BIGINT(20) NOT NULL, +sequencenumber INT(10) NOT NULL, +PRIMARY KEY (t3idref, t2idref), +INDEX FK_T3_T2Id (t2idref), +CONSTRAINT FK_T3_T2Id FOREIGN KEY (t2idref) REFERENCES t2 (t2id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; +INSERT INTO t1 (t1id) VALUES (100001),(100017),(100018),(100026),(100027),(100028),(100029),(100030), +(100031),(100032),(100033),(100034),(100035),(100036),(100037),(100038),(100040),(100041),(100042), +(100043),(100044),(100045),(100046),(100047); +INSERT IGNORE INTO t2 (t2id, t1idref) SELECT t1id, t1id FROM t1; +INSERT IGNORE INTO t1 VALUES (200001, 'a'); +INSERT IGNORE INTO t2 (t2id, t1idref) VALUES (200011, 200001),(200012, 200001),(200013, 200001); +INSERT IGNORE INTO t3 VALUES (1, 200011, 1), (1, 200012, 2), (1, 200013, 3); +set @tmp7474= @@optimizer_search_depth; +SET SESSION optimizer_search_depth = 1; +SELECT SQL_NO_CACHE +T2_0_.t1idref, +T2_0_.t2id +FROM +t2 T2_0_ +WHERE +T2_0_.t1idref IN ( +SELECT +T1_1_.t1id +FROM +t3 T3_0_ +INNER JOIN +t2 T2_1_ +ON T3_0_.t2idref=T2_1_.t2id +INNER JOIN +t1 T1_1_ +ON T2_1_.t1idref=T1_1_.t1id +WHERE +T3_0_.t3idref= 1 +); +t1idref t2id +200001 200011 +200001 200012 +200001 200013 +explain SELECT SQL_NO_CACHE +T2_0_.t1idref, +T2_0_.t2id +FROM +t2 T2_0_ +WHERE +T2_0_.t1idref IN ( +SELECT +T1_1_.t1id +FROM +t3 T3_0_ +INNER JOIN +t2 T2_1_ +ON T3_0_.t2idref=T2_1_.t2id +INNER JOIN +t1 T1_1_ +ON T2_1_.t1idref=T1_1_.t1id +WHERE +T3_0_.t3idref= 1 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY T3_0_ ref PRIMARY,FK_T3_T2Id PRIMARY 8 const 3 Using index; Start temporary +1 PRIMARY T2_1_ eq_ref PRIMARY,FK_T2_T1Id PRIMARY 8 test.T3_0_.t2idref 1 Using join buffer (flat, BKA join); Key-ordered scan +1 PRIMARY T1_1_ eq_ref PRIMARY PRIMARY 8 test.T2_1_.t1idref 1 Using index +1 PRIMARY T2_0_ ref FK_T2_T1Id FK_T2_T1Id 8 test.T2_1_.t1idref 1 Using index; End temporary +drop table t3,t2,t1; +set optimizer_search_depth=@tmp7474; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; # diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 0ff366b0d7a..4e75aee24a2 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1180,6 +1180,90 @@ id nombre 2 row 2 3 row 3 DROP TABLE t1, t2; +# +# MDEV-7474: Semi-Join's DuplicateWeedout strategy skipped for some values of optimizer_search_depth +# +CREATE TABLE t1 ( +t1id BIGINT(20) NOT NULL, +code VARCHAR(20), +PRIMARY KEY (t1id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; +CREATE TABLE t2 ( +t2id BIGINT(20) NOT NULL, +t1idref BIGINT(20) NOT NULL, +code VARCHAR(20), +PRIMARY KEY (t2id), +INDEX FK_T2_T1Id (t1idref), +CONSTRAINT FK_T2_T1Id FOREIGN KEY (t1idref) REFERENCES t1 (t1id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; +CREATE TABLE t3 ( +t3idref BIGINT(20) NOT NULL, +t2idref BIGINT(20) NOT NULL, +sequencenumber INT(10) NOT NULL, +PRIMARY KEY (t3idref, t2idref), +INDEX FK_T3_T2Id (t2idref), +CONSTRAINT FK_T3_T2Id FOREIGN KEY (t2idref) REFERENCES t2 (t2id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; +INSERT INTO t1 (t1id) VALUES (100001),(100017),(100018),(100026),(100027),(100028),(100029),(100030), +(100031),(100032),(100033),(100034),(100035),(100036),(100037),(100038),(100040),(100041),(100042), +(100043),(100044),(100045),(100046),(100047); +INSERT IGNORE INTO t2 (t2id, t1idref) SELECT t1id, t1id FROM t1; +INSERT IGNORE INTO t1 VALUES (200001, 'a'); +INSERT IGNORE INTO t2 (t2id, t1idref) VALUES (200011, 200001),(200012, 200001),(200013, 200001); +INSERT IGNORE INTO t3 VALUES (1, 200011, 1), (1, 200012, 2), (1, 200013, 3); +set @tmp7474= @@optimizer_search_depth; +SET SESSION optimizer_search_depth = 1; +SELECT SQL_NO_CACHE +T2_0_.t1idref, +T2_0_.t2id +FROM +t2 T2_0_ +WHERE +T2_0_.t1idref IN ( +SELECT +T1_1_.t1id +FROM +t3 T3_0_ +INNER JOIN +t2 T2_1_ +ON T3_0_.t2idref=T2_1_.t2id +INNER JOIN +t1 T1_1_ +ON T2_1_.t1idref=T1_1_.t1id +WHERE +T3_0_.t3idref= 1 +); +t1idref t2id +200001 200011 +200001 200012 +200001 200013 +explain SELECT SQL_NO_CACHE +T2_0_.t1idref, +T2_0_.t2id +FROM +t2 T2_0_ +WHERE +T2_0_.t1idref IN ( +SELECT +T1_1_.t1id +FROM +t3 T3_0_ +INNER JOIN +t2 T2_1_ +ON T3_0_.t2idref=T2_1_.t2id +INNER JOIN +t1 T1_1_ +ON T2_1_.t1idref=T1_1_.t1id +WHERE +T3_0_.t3idref= 1 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY T3_0_ ref PRIMARY,FK_T3_T2Id PRIMARY 8 const 3 Using index; Start temporary +1 PRIMARY T2_1_ eq_ref PRIMARY,FK_T2_T1Id PRIMARY 8 test.T3_0_.t2idref 1 +1 PRIMARY T1_1_ eq_ref PRIMARY PRIMARY 8 test.T2_1_.t1idref 1 Using index +1 PRIMARY T2_0_ ref FK_T2_T1Id FK_T2_T1Id 8 test.T2_1_.t1idref 1 Using index; End temporary +drop table t3,t2,t1; +set optimizer_search_depth=@tmp7474; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; set optimizer_switch=default; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index f388fd41ce8..ba582d7e008 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -3002,6 +3002,15 @@ pk1 i1 i2 c2 pk3 i3 c3 SET join_cache_level=@tmp_mdev5059; set optimizer_switch=@tmp_os_mdev5059; DROP TABLE t1,t2,t3,t4; +# +# MDEV-7911: crash in Item_cond::eval_not_null_tables +# +create table t1(a int); +insert into t1 values(1),(2),(3),(null); +explain +select 1 from t1 where _cp932 "1" in (select '1' from t1); +ERROR HY000: Illegal mix of collations (cp932_japanese_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' +drop table t1; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index ff488fea427..c633261bcd3 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -677,3 +677,35 @@ USA CAN DROP TABLE t1, t2, t3; SET optimizer_switch=@save_optimizer_switch; +# +# MDEV-7893: table_elimination works wrong with on computed expression and compound unique key +# (just a testcase) +CREATE TABLE t1 ( +PostID int(10) unsigned NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1266 Using storage engine MyISAM for table 't1' +INSERT INTO t1 (PostID) VALUES (1), (2); +CREATE TABLE t2 ( +VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, +EntityID int(10) unsigned NOT NULL, +UserID int(10) unsigned NOT NULL, +UNIQUE KEY EntityID (EntityID,UserID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1266 Using storage engine MyISAM for table 't2' +INSERT INTO t2 (EntityID, UserID) VALUES (1, 30), (2, 30); +SELECT t1.*, T.Voted as Voted +FROM +t1 LEFT JOIN ( +SELECT 1 AS Voted, EntityID +FROM t2 +WHERE t2.UserID = '20' ) AS T +ON T.EntityID = t1.PostID +WHERE t1.PostID='1' +LIMIT 1; +PostID Voted +1 NULL +DROP TABLE t1,t2; diff --git a/mysql-test/r/trigger-trans.result b/mysql-test/r/trigger-trans.result index 722ac79854d..cab7336cbe5 100644 --- a/mysql-test/r/trigger-trans.result +++ b/mysql-test/r/trigger-trans.result @@ -195,3 +195,28 @@ b val 14 g drop trigger t1_after_insert; drop table t1,t2; +# +#Bug#19683834 SOME INNODB ERRORS CAUSES STORED FUNCTION +# AND TRIGGER HANDLERS TO BE IGNORED +#Code fixed in Bug#16041903 +CREATE TABLE t1 (id int unsigned PRIMARY KEY, val int DEFAULT 0) +ENGINE=InnoDB; +INSERT INTO t1 (id) VALUES (1), (2); +CREATE TABLE t2 (id int PRIMARY KEY); +CREATE TABLE t3 LIKE t2; +CREATE TRIGGER bef_insert BEFORE INSERT ON t2 FOR EACH ROW +BEGIN +DECLARE CONTINUE HANDLER FOR 1062 BEGIN END; +INSERT INTO t3 (id) VALUES (NEW.id); +INSERT INTO t3 (id) VALUES (NEW.id); +END// +START TRANSACTION; +UPDATE t1 SET val = val + 1; +connect con2,localhost,root,,test,,; +SET SESSION innodb_lock_wait_timeout = 2; +UPDATE t1 SET val = val + 1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t2 (id) VALUES (1); +disconnect con2; +connection default; +DROP TABLE t3, t2, t1; diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 5992b1347f3..53fc3104666 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -255,7 +255,7 @@ ushort smallint(5) unsigned zerofill NULL NO 00000 # umedium mediumint(8) unsigned NULL NO 0 # ulong int(11) unsigned NULL NO 0 # ulonglong bigint(13) unsigned NULL NO 0 # -time_stamp timestamp NULL NO 0000-00-00 00:00:00 # +time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP # date_field char(10) latin1_swedish_ci YES NULL # time_field time NULL YES NULL # date_time datetime NULL YES NULL # diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 556e5403591..786bce471ce 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -690,6 +690,47 @@ drop table t1; drop function f1; set timestamp=0; SET time_zone=DEFAULT; +# +# MDEV-7778 impossible create copy of table, if table contain default value for timestamp field +# +SET sql_mode="NO_ZERO_DATE"; +CREATE TABLE t1 ( +ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); +CREATE TABLE t2 AS SELECT * from t1 LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; +SET sql_mode=DEFAULT; +# +# MDEV-8082 ON UPDATE is not preserved by CREATE TABLE .. SELECT +# +CREATE TABLE t1 ( +vc VARCHAR(10) NOT NULL DEFAULT 'test', +ts timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP +); +CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `vc` varchar(10) NOT NULL DEFAULT 'test', + `ts` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `vc` varchar(10) NOT NULL DEFAULT 'test', + `ts` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; End of 10.0 tests # # Start of 10.1 tests diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result index dc1cbb377f9..6ebf83a09f0 100644 --- a/mysql-test/r/type_timestamp_hires.result +++ b/mysql-test/r/type_timestamp_hires.result @@ -109,7 +109,7 @@ create table t3 like t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp(4) NOT NULL DEFAULT '0000-00-00 00:00:00.0000' + `a` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t3; Table Create Table @@ -130,7 +130,7 @@ create table t3 select max(a), min(a), sum(a), avg(a) from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( - `a` timestamp(4) NOT NULL DEFAULT '0000-00-00 00:00:00.0000', + `a` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4), `a+0` decimal(25,4) NOT NULL DEFAULT '0.0000', `a-1` decimal(25,4) NOT NULL DEFAULT '0.0000', `a*1` decimal(25,4) NOT NULL DEFAULT '0.0000', diff --git a/mysql-test/r/uniques_crash-7912.result b/mysql-test/r/uniques_crash-7912.result new file mode 100644 index 00000000000..bf3aab684ae --- /dev/null +++ b/mysql-test/r/uniques_crash-7912.result @@ -0,0 +1,11 @@ +call mtr.add_suppression("Out of memory"); +set sql_mode=""; +drop table if exists t1,t2; +create table `t1` (`a` datetime not null) engine=InnoDB; +create table `t2` (`a` int not null) engine=innodb; +replace into t1 values (),(); +insert into t2 values(0); +set session sort_buffer_size = 1024*1024*1024*1024; +delete d2 from t2 as d1, t1 as d2 where d1.a <=> d2.a; +drop table t2; +drop table t1; |