diff options
author | Sergei Golubchik <serg@mariadb.org> | 2015-12-21 21:24:22 +0100 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2015-12-21 21:24:22 +0100 |
commit | a2bcee626d4ef2836e38e4932305871390164644 (patch) | |
tree | b41e357427318bad8985078b91bbd2b0360defc8 /mysql-test/suite | |
parent | 1788bfe93a745582d938a608d5959b7d2e6b2f23 (diff) | |
parent | 4fdf25afa8188905653a83e08fc387243e584600 (diff) | |
download | mariadb-git-a2bcee626d4ef2836e38e4932305871390164644.tar.gz |
Merge branch '10.0' into 10.1
Diffstat (limited to 'mysql-test/suite')
52 files changed, 742 insertions, 98 deletions
diff --git a/mysql-test/suite/federated/error_on_close-8313.result b/mysql-test/suite/federated/error_on_close-8313.result new file mode 100644 index 00000000000..93b0edb4e49 --- /dev/null +++ b/mysql-test/suite/federated/error_on_close-8313.result @@ -0,0 +1,27 @@ +CREATE DATABASE federated; +CREATE DATABASE federated; +connection slave; +create table t1 (foo int, bar int); +connection master; +create server 's1' foreign data wrapper 'mysql' options +(HOST 'localhost', +DATABASE 'test', +USER 'root', +PASSWORD '', +SOCKET 'SLAVE_MYSOCK'); +create table t1 (foo integer, bar integer) engine=federated +connection='s1'; +select * from t1; +foo bar +connection slave; +connection master; +drop table t1; +drop server s1; +connection slave; +drop table t1; +connection master; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; +connection slave; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; diff --git a/mysql-test/suite/federated/error_on_close-8313.test b/mysql-test/suite/federated/error_on_close-8313.test new file mode 100644 index 00000000000..b0b12861a66 --- /dev/null +++ b/mysql-test/suite/federated/error_on_close-8313.test @@ -0,0 +1,38 @@ +# +# MDEV-8313 Got an error writing communication packets +# +source include/federated.inc; + +enable_connect_log; + +connection slave; +create table t1 (foo int, bar int); + +connection master; + +--replace_result $SLAVE_MYSOCK SLAVE_MYSOCK +eval create server 's1' foreign data wrapper 'mysql' options + (HOST 'localhost', + DATABASE 'test', + USER 'root', + PASSWORD '', + SOCKET '$SLAVE_MYSOCK'); + + +eval create table t1 (foo integer, bar integer) engine=federated + connection='s1'; + +select * from t1; + +connection slave; +source include/restart_mysqld.inc; + +connection master; +drop table t1; +drop server s1; + +connection slave; +drop table t1; + +source include/federated_cleanup.inc; + diff --git a/mysql-test/suite/innodb/r/dropdb.result b/mysql-test/suite/innodb/r/dropdb.result new file mode 100644 index 00000000000..6b11b5e5205 --- /dev/null +++ b/mysql-test/suite/innodb/r/dropdb.result @@ -0,0 +1,9 @@ +# +# Bug #19929435 DROP DATABASE HANGS WITH MALFORMED TABLE +# +set session default_storage_engine=innodb; +create database `b`; +use `b`; +create table `#mysql50#q.q` select 1; +ERROR 42000: Incorrect table name '#mysql50#q.q' +drop database `b`; diff --git a/mysql-test/suite/innodb/r/innodb-autoinc.result b/mysql-test/suite/innodb/r/innodb-autoinc.result index 6ac8bb53c66..ddca4685e05 100644 --- a/mysql-test/suite/innodb/r/innodb-autoinc.result +++ b/mysql-test/suite/innodb/r/innodb-autoinc.result @@ -567,7 +567,7 @@ Variable_name Value auto_increment_increment 65535 auto_increment_offset 65535 INSERT INTO t1 VALUES (NULL),(NULL); -ERROR 22003: Out of range value for column 'c1' at row 1 +ERROR HY000: Failed to read auto-increment value from storage engine SELECT * FROM t1; c1 1 @@ -641,6 +641,18 @@ PRIMARY KEY (m)) ENGINE = InnoDB; INSERT INTO t2 (n,o) VALUES (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false'); +SELECT * FROM t2; +m n o +1 1 TRUE +2 1 FALSE +3 2 TRUE +4 2 FALSE +5 3 TRUE +6 3 FALSE +7 4 TRUE +8 4 FALSE +9 5 TRUE +10 5 FALSE SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( @@ -648,7 +660,7 @@ t2 CREATE TABLE `t2` ( `n` int(10) unsigned NOT NULL, `o` enum('FALSE','TRUE') DEFAULT NULL, PRIMARY KEY (`m`) -) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; SHOW CREATE TABLE t1; Table Create Table diff --git a/mysql-test/suite/innodb/r/innodb-dict.result b/mysql-test/suite/innodb/r/innodb-dict.result new file mode 100644 index 00000000000..e3b2f0d5288 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-dict.result @@ -0,0 +1,40 @@ +CREATE TABLE t1 (D INT) ENGINE=innodb; +INSERT INTO t1 VALUES (10); +ALTER TABLE t1 MODIFY COLUMN d INT; +ALTER TABLE t1 ADD INDEX my_d (d); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` int(11) DEFAULT NULL, + KEY `my_d` (`d`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +EXPLAIN SELECT d FROM t1 WHERE d = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref my_d my_d 5 const 128 Using index +EXPLAIN SELECT D FROM t1 WHERE D = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref my_d my_d 5 const 128 Using index +ALTER TABLE t1 DROP INDEX my_d; +ALTER TABLE t1 MODIFY COLUMN D INT; +ALTER TABLE t1 ADD INDEX my_d (D); +EXPLAIN SELECT d FROM t1 WHERE d = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref my_d my_d 5 const 128 Using index +EXPLAIN SELECT D FROM t1 WHERE D = 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref my_d my_d 5 const 128 Using index +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `D` int(11) DEFAULT NULL, + KEY `my_d` (`D`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-get-fk.result b/mysql-test/suite/innodb/r/innodb-get-fk.result new file mode 100644 index 00000000000..0dca82c2d10 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-get-fk.result @@ -0,0 +1,71 @@ +CREATE SCHEMA `repro`; +CREATE TABLE `repro`.`crew` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`code` varchar(50) NOT NULL, +`name` varchar(50) NOT NULL, +`created_at` timestamp NULL DEFAULT NULL, +`updated_at` timestamp NULL DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `code` (`code`) +) ENGINE=InnoDB; +CREATE TABLE `repro`.`pilot` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`code` varchar(50) NOT NULL, +`name` varchar(50) NOT NULL, +`created_at` timestamp NULL DEFAULT NULL, +`updated_at` timestamp NULL DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `code` (`code`) +) ENGINE=InnoDB; +CREATE TABLE `repro`.`crew_role_assigned` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`crew_id` int(11) NOT NULL, +`role_code` varchar(50) NOT NULL, +PRIMARY KEY (`id`), +KEY `fk_crewRoleAssigned_roleCode` (`role_code`), +CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB COMMENT="This is a comment about tables"; +# Restart mysqld --innodb_read_only_mode=1 +SHOW CREATE TABLE `repro`.`crew_role_assigned`; +Table Create Table +crew_role_assigned CREATE TABLE `crew_role_assigned` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `crew_id` int(11) NOT NULL, + `role_code` varchar(50) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_crewRoleAssigned_roleCode` (`role_code`), + KEY `fk_crewRoleAssigned_pilotId` (`crew_id`), + CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This is a comment about tables' +ALTER TABLE `repro`.`crew_role_assigned` COMMENT = "This is a new comment about tables"; +SHOW CREATE TABLE `repro`.`crew_role_assigned`; +Table Create Table +crew_role_assigned CREATE TABLE `crew_role_assigned` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `crew_id` int(11) NOT NULL, + `role_code` varchar(50) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_crewRoleAssigned_roleCode` (`role_code`), + KEY `fk_crewRoleAssigned_pilotId` (`crew_id`), + CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This is a new comment about tables' +# Restart mysqld --innodb_read_only_mode=1 +SHOW CREATE TABLE `repro`.`crew_role_assigned`; +Table Create Table +crew_role_assigned CREATE TABLE `crew_role_assigned` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `crew_id` int(11) NOT NULL, + `role_code` varchar(50) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_crewRoleAssigned_roleCode` (`role_code`), + KEY `fk_crewRoleAssigned_pilotId` (`crew_id`), + CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='This is a new comment about tables' +DROP TABLE `repro`.`crew_role_assigned`; +DROP TABLE `repro`.`pilot`; +DROP TABLE `repro`.`crew`; +DROP SCHEMA `repro`; diff --git a/mysql-test/suite/innodb/r/innodb-stats-modified-counter.result b/mysql-test/suite/innodb/r/innodb-stats-modified-counter.result new file mode 100644 index 00000000000..db56c6ba81a --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-stats-modified-counter.result @@ -0,0 +1,20 @@ +set global innodb_stats_auto_recalc=off; +CREATE TABLE t1 (i int) ENGINE=InnoDB; +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +NAME STATS_INITIALIZED NUM_ROWS MODIFIED_COUNTER +test/t1 Initialized 0 0 +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +NAME STATS_INITIALIZED NUM_ROWS MODIFIED_COUNTER +test/t1 Initialized 2 2 +DELETE FROM t1 WHERE i = 1; +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +NAME STATS_INITIALIZED NUM_ROWS MODIFIED_COUNTER +test/t1 Initialized 1 3 +UPDATE t1 SET i = 4 WHERE i = 2; +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +NAME STATS_INITIALIZED NUM_ROWS MODIFIED_COUNTER +test/t1 Initialized 1 4 +DROP TABLE t1; +set global innodb_stats_auto_recalc=default; diff --git a/mysql-test/suite/innodb/r/innodb_bug56947.result b/mysql-test/suite/innodb/r/innodb_bug56947.result index 54af78047dd..878450144a2 100644 --- a/mysql-test/suite/innodb/r/innodb_bug56947.result +++ b/mysql-test/suite/innodb/r/innodb_bug56947.result @@ -2,7 +2,7 @@ SET GLOBAL innodb_file_per_table=0; create table bug56947(a int not null) engine = innodb; SET DEBUG_DBUG='+d,ib_rebuild_cannot_rename'; alter table bug56947 add unique index (a); -ERROR HY000: Got error 11 "Resource temporarily unavailable" from storage engine InnoDB +ERROR HY000: Got error 11 "xxx" from storage engine InnoDB SET DEBUG_DBUG='-d,ib_rebuild_cannot_rename'; check table bug56947; Table Op Msg_type Msg_text diff --git a/mysql-test/suite/innodb/r/innodb_information_schema_tables.result b/mysql-test/suite/innodb/r/innodb_information_schema_tables.result new file mode 100644 index 00000000000..ea713ea6f6a --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_information_schema_tables.result @@ -0,0 +1,2 @@ +CREATE TABLE t1 ENGINE=InnoDB AS SELECT * FROM mysql.help_topic; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/dropdb.test b/mysql-test/suite/innodb/t/dropdb.test new file mode 100644 index 00000000000..7f5ac809d7c --- /dev/null +++ b/mysql-test/suite/innodb/t/dropdb.test @@ -0,0 +1,12 @@ +--source include/have_innodb.inc + +--echo # +--echo # Bug #19929435 DROP DATABASE HANGS WITH MALFORMED TABLE +--echo # + +set session default_storage_engine=innodb; +create database `b`; +use `b`; +--error ER_WRONG_TABLE_NAME +create table `#mysql50#q.q` select 1; +drop database `b`; diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test index ab708e8762c..dd4c4ae8603 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc.test @@ -349,7 +349,7 @@ INSERT INTO t1 VALUES (18446744073709551610); #-- 2^64 - 2 SELECT * FROM t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; SHOW VARIABLES LIKE "auto_inc%"; ---error 167 +--error 1467 INSERT INTO t1 VALUES (NULL),(NULL); SELECT * FROM t1; DROP TABLE t1; @@ -401,6 +401,7 @@ CREATE TABLE t2 ( INSERT INTO t2 (n,o) VALUES (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false'); +SELECT * FROM t2; SHOW CREATE TABLE t2; INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; SHOW CREATE TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-dict.test b/mysql-test/suite/innodb/t/innodb-dict.test new file mode 100644 index 00000000000..25a284569db --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-dict.test @@ -0,0 +1,31 @@ +--source include/have_innodb.inc + +# +# Fix MySQL Bug#20755615: InnoDB compares column names case sensitively, +# while according to Storage Engine API column names should be compared +# case insensitively. This can cause FRM and InnoDB data dictionary to +# go out of sync: +# + +CREATE TABLE t1 (D INT) ENGINE=innodb; +INSERT INTO t1 VALUES (10); +ALTER TABLE t1 MODIFY COLUMN d INT; +ALTER TABLE t1 ADD INDEX my_d (d); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +EXPLAIN SELECT d FROM t1 WHERE d = 5; +EXPLAIN SELECT D FROM t1 WHERE D = 5; +ALTER TABLE t1 DROP INDEX my_d; +ALTER TABLE t1 MODIFY COLUMN D INT; +ALTER TABLE t1 ADD INDEX my_d (D); +EXPLAIN SELECT d FROM t1 WHERE d = 5; +EXPLAIN SELECT D FROM t1 WHERE D = 5; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-get-fk.test b/mysql-test/suite/innodb/t/innodb-get-fk.test new file mode 100644 index 00000000000..4245bef289f --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-get-fk.test @@ -0,0 +1,61 @@ +-- source include/have_innodb.inc +# need to restart server +-- source include/not_embedded.inc +CREATE SCHEMA `repro`; + +CREATE TABLE `repro`.`crew` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`code` varchar(50) NOT NULL, +`name` varchar(50) NOT NULL, +`created_at` timestamp NULL DEFAULT NULL, +`updated_at` timestamp NULL DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `code` (`code`) +) ENGINE=InnoDB; + +CREATE TABLE `repro`.`pilot` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`code` varchar(50) NOT NULL, +`name` varchar(50) NOT NULL, +`created_at` timestamp NULL DEFAULT NULL, +`updated_at` timestamp NULL DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `code` (`code`) +) ENGINE=InnoDB; + +CREATE TABLE `repro`.`crew_role_assigned` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`crew_id` int(11) NOT NULL, +`role_code` varchar(50) NOT NULL, +PRIMARY KEY (`id`), +KEY `fk_crewRoleAssigned_roleCode` (`role_code`), +CONSTRAINT `fk_crewRoleAssigned_crewId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`crew` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, +CONSTRAINT `fk_crewRoleAssigned_pilotId` FOREIGN KEY (`crew_id`) REFERENCES `repro`.`pilot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB COMMENT="This is a comment about tables"; + +--echo # Restart mysqld --innodb_read_only_mode=1 +-- let $restart_parameters=--innodb-read-only-mode=1 +-- source include/restart_mysqld.inc + +SHOW CREATE TABLE `repro`.`crew_role_assigned`; + +-- source include/restart_mysqld.inc + +ALTER TABLE `repro`.`crew_role_assigned` COMMENT = "This is a new comment about tables"; +SHOW CREATE TABLE `repro`.`crew_role_assigned`; + +--echo # Restart mysqld --innodb_read_only_mode=1 +-- let $restart_parameters=--innodb-read-only-mode=1 +-- source include/restart_mysqld.inc + +# +# Below create table should contain also fk definitions +# +SHOW CREATE TABLE `repro`.`crew_role_assigned`; + +-- source include/restart_mysqld.inc + +DROP TABLE `repro`.`crew_role_assigned`; +DROP TABLE `repro`.`pilot`; +DROP TABLE `repro`.`crew`; +DROP SCHEMA `repro`; diff --git a/mysql-test/suite/innodb/t/innodb-stats-modified-counter.opt b/mysql-test/suite/innodb/t/innodb-stats-modified-counter.opt new file mode 100644 index 00000000000..3e5b41c6db9 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-stats-modified-counter.opt @@ -0,0 +1 @@ +--loose-innodb-sys-tablestats diff --git a/mysql-test/suite/innodb/t/innodb-stats-modified-counter.test b/mysql-test/suite/innodb/t/innodb-stats-modified-counter.test new file mode 100644 index 00000000000..b3e45002768 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-stats-modified-counter.test @@ -0,0 +1,28 @@ +-- source include/have_innodb.inc + +# +# MDEV-8297: information_schema.innodb_sys_tablestats.modified_counter doesn't change on UPDATE +# + +set global innodb_stats_auto_recalc=off; + +CREATE TABLE t1 (i int) ENGINE=InnoDB; + +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); + +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +DELETE FROM t1 WHERE i = 1; + +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +UPDATE t1 SET i = 4 WHERE i = 2; + +SELECT NAME, STATS_INITIALIZED, NUM_ROWS, MODIFIED_COUNTER FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +DROP TABLE t1; + +set global innodb_stats_auto_recalc=default; diff --git a/mysql-test/suite/innodb/t/innodb_bug56947.test b/mysql-test/suite/innodb/t/innodb_bug56947.test index 4cefeb391cf..7883cafe291 100644 --- a/mysql-test/suite/innodb/t/innodb_bug56947.test +++ b/mysql-test/suite/innodb/t/innodb_bug56947.test @@ -8,6 +8,7 @@ SET GLOBAL innodb_file_per_table=0; create table bug56947(a int not null) engine = innodb; SET DEBUG_DBUG='+d,ib_rebuild_cannot_rename'; +--replace_regex /"[^"]*"/"xxx"/ --error ER_GET_ERRNO alter table bug56947 add unique index (a); SET DEBUG_DBUG='-d,ib_rebuild_cannot_rename'; diff --git a/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt b/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt new file mode 100644 index 00000000000..9f30d81ef9c --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_information_schema_tables.opt @@ -0,0 +1,29 @@ +--loose-innodb_trx +--loose-innodb_locks +--loose-innodb_lock_waits +--loose-innodb_cmp +--loose-innodb_cmp_reset +--loose-innodb_cmp_per_index +--loose-innodb_cmp_per_index_reset +--loose-innodb_cmpmem +--loose-innodb_cmpmem_reset +--loose-innodb_buffer_page +--loose-innodb_buffer_page_lru +--loose-innodb_buffer_stats +--loose-innodb_sys_tables +--loose-innodb_sys_tablestats +--loose-innodb_sys_indexes +--loose-innodb_sys_columns +--loose-innodb_sys_fields +--loose-innodb_sys_foreign +--loose-innodb_sys_foreign_cols +--loose-innodb_changed_pages +--loose-innodb_rseg +--loose-innodb_undo_logs +--loose-innodb_sys_stats +--loose-innodb_table_stats +--loose-innodb_index_stats +--loose-innodb_admin_command +--loose-innodb_buffer_pool_pages +--loose-innodb_buffer_pool_pages_index +--loose-innodb_buffer_pool_pages_blob diff --git a/mysql-test/suite/innodb/t/innodb_information_schema_tables.test b/mysql-test/suite/innodb/t/innodb_information_schema_tables.test new file mode 100644 index 00000000000..15b3bf4f561 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_information_schema_tables.test @@ -0,0 +1,64 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc + +# +# MDEV-7762 InnoDB: Failing assertion: block->page.buf_fix_count > 0 in buf0buf.ic line 730 +# +# Make sure that all supported information_schema tables are readable +# (actual result sets are not important). +# +CREATE TABLE t1 ENGINE=InnoDB AS SELECT * FROM mysql.help_topic; + +--disable_query_log +--disable_result_log +BEGIN; +SELECT * FROM t1 FOR UPDATE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; +SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; +SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP; +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_RESET; +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM; +SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM_RESET; +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_STATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_RSEG; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_UNDO_LOGS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_STATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLE_STATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEX_STATS; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_ADMIN_COMMAND; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_INDEX; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_PAGES_BLOB; +--error 0,1109 +SELECT * FROM INFORMATION_SCHEMA.INNODB_CHANGED_PAGES; +COMMIT; +--enable_query_log +--enable_result_log +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index dd8412c6651..1cdce29cdf9 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -498,7 +498,7 @@ MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) DROP TABLE t1; CREATE TABLE t1(a TEXT) ENGINE = InnoDB; SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE); -ERROR HY000: Incorrect arguments to AGAINST +ERROR HY000: Incorrect arguments to MATCH DROP TABLE t1; CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd'); diff --git a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result index 3668df4c2f0..470d06c341d 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result +++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug45221.result @@ -1,4 +1,4 @@ -CREATE TABLE `CC` ( +CREATE TABLE `t2` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, `date_nokey` date DEFAULT NULL, @@ -8,9 +8,9 @@ KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; Warnings: -Note 1831 Duplicate index 'varchar_key' defined on the table 'test.CC'. This is deprecated and will be disallowed in a future release. -INSERT INTO `CC` VALUES (10,8,NULL,'2002-02-26 06:14:37'),(11,9,'2006-06-14','1900-01-01 00:00:00'),(12,9,'2002-09-12','2006-12-03 09:37:26'),(13,186,'2005-02-15','2008-05-26 12:27:10'),(14,NULL,NULL,'2004-12-14 16:37:30'),(15,2,'2008-11-04','2003-02-11 21:19:41'),(16,3,'2004-09-04','2009-10-18 02:27:49'),(17,0,'2006-06-05','2000-09-26 07:45:57'),(18,133,'1900-01-01',NULL),(19,1,'1900-01-01','2005-11-10 12:40:29'),(20,8,'1900-01-01','2009-04-25 00:00:00'),(21,5,'2005-01-13','2002-11-27 00:00:00'),(22,5,'2006-05-21','2004-01-26 20:32:32'),(23,8,'2003-09-08','2007-10-26 11:41:40'),(24,6,'2006-12-23','2005-10-07 00:00:00'),(25,51,'2006-10-15','2000-07-15 05:00:34'),(26,4,'2005-04-06','2000-04-03 16:33:32'),(27,7,'2008-04-07',NULL),(28,6,'2006-10-10','2001-04-25 01:26:12'),(29,4,'1900-01-01','2000-12-27 00:00:00'); -CREATE TABLE `C` ( +Note 1831 Duplicate index 'varchar_key' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release. +INSERT INTO `t2` VALUES (10,8,NULL,'2002-02-26 06:14:37'),(11,9,'2006-06-14','1900-01-01 00:00:00'),(12,9,'2002-09-12','2006-12-03 09:37:26'),(13,186,'2005-02-15','2008-05-26 12:27:10'),(14,NULL,NULL,'2004-12-14 16:37:30'),(15,2,'2008-11-04','2003-02-11 21:19:41'),(16,3,'2004-09-04','2009-10-18 02:27:49'),(17,0,'2006-06-05','2000-09-26 07:45:57'),(18,133,'1900-01-01',NULL),(19,1,'1900-01-01','2005-11-10 12:40:29'),(20,8,'1900-01-01','2009-04-25 00:00:00'),(21,5,'2005-01-13','2002-11-27 00:00:00'),(22,5,'2006-05-21','2004-01-26 20:32:32'),(23,8,'2003-09-08','2007-10-26 11:41:40'),(24,6,'2006-12-23','2005-10-07 00:00:00'),(25,51,'2006-10-15','2000-07-15 05:00:34'),(26,4,'2005-04-06','2000-04-03 16:33:32'),(27,7,'2008-04-07',NULL),(28,6,'2006-10-10','2001-04-25 01:26:12'),(29,4,'1900-01-01','2000-12-27 00:00:00'); +CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, `date_nokey` date DEFAULT NULL, @@ -20,13 +20,13 @@ KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; Warnings: -Note 1831 Duplicate index 'varchar_key' defined on the table 'test.C'. This is deprecated and will be disallowed in a future release. -INSERT INTO `C` VALUES (1,2,NULL,'2004-10-11 18:13:16'),(2,9,'2001-09-19',NULL),(3,3,'2004-09-12','1900-01-01 00:00:00'),(4,9,NULL,'2009-07-25 00:00:00'),(5,NULL,'2002-07-19',NULL),(6,9,'2002-12-16','2008-07-27 00:00:00'),(7,3,'2006-02-08','2002-11-13 16:37:31'),(8,8,'2006-08-28','1900-01-01 00:00:00'),(9,8,'2001-04-14','2003-12-10 00:00:00'),(10,53,'2000-01-05','2001-12-21 22:38:22'),(11,0,'2003-12-06','2008-12-13 23:16:44'),(12,5,'1900-01-01','2005-08-15 12:39:41'),(13,166,'2002-11-27',NULL),(14,3,NULL,'2006-09-11 12:06:14'),(15,0,'2003-05-27','2007-12-15 12:39:34'),(16,1,'2005-05-03','2005-08-09 00:00:00'),(17,9,'2001-04-18','2001-09-02 22:50:02'),(18,5,'2005-12-27','2005-12-16 22:58:11'),(19,6,'2004-08-20','2007-04-19 00:19:53'),(20,2,'1900-01-01','1900-01-01 00:00:00'); +Note 1831 Duplicate index 'varchar_key' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. +INSERT INTO t1 VALUES (1,2,NULL,'2004-10-11 18:13:16'),(2,9,'2001-09-19',NULL),(3,3,'2004-09-12','1900-01-01 00:00:00'),(4,9,NULL,'2009-07-25 00:00:00'),(5,NULL,'2002-07-19',NULL),(6,9,'2002-12-16','2008-07-27 00:00:00'),(7,3,'2006-02-08','2002-11-13 16:37:31'),(8,8,'2006-08-28','1900-01-01 00:00:00'),(9,8,'2001-04-14','2003-12-10 00:00:00'),(10,53,'2000-01-05','2001-12-21 22:38:22'),(11,0,'2003-12-06','2008-12-13 23:16:44'),(12,5,'1900-01-01','2005-08-15 12:39:41'),(13,166,'2002-11-27',NULL),(14,3,NULL,'2006-09-11 12:06:14'),(15,0,'2003-05-27','2007-12-15 12:39:34'),(16,1,'2005-05-03','2005-08-09 00:00:00'),(17,9,'2001-04-18','2001-09-02 22:50:02'),(18,5,'2005-12-27','2005-12-16 22:58:11'),(19,6,'2004-08-20','2007-04-19 00:19:53'),(20,2,'1900-01-01','1900-01-01 00:00:00'); SELECT `pk` -FROM C OUTR +FROM t1 OUTR WHERE `pk` IN ( SELECT `int_key` -FROM CC +FROM t2 WHERE `date_nokey` < `datetime_nokey` XOR OUTR .`date_nokey` ) ; pk 9 @@ -34,10 +34,10 @@ pk 5 6 SELECT `pk` -FROM C +FROM t1 WHERE `pk` IN ( SELECT `int_key` -FROM CC +FROM t2 WHERE `date_nokey` < `datetime_nokey` XOR '2009-11-25' ) ; pk 2 @@ -62,9 +62,9 @@ Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' Warning 1292 Truncated incorrect INTEGER value: '2009-11-25' -DROP TABLE CC; -DROP TABLE C; -CREATE TABLE `CC` ( +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE `t2` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, @@ -77,9 +77,9 @@ KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; Warnings: -Note 1831 Duplicate index 'varchar_key' defined on the table 'test.CC'. This is deprecated and will be disallowed in a future release. -INSERT INTO `CC` VALUES (10,7,8,NULL,'2002-02-26 06:14:37','2002-02-26 06:14:37'),(11,1,9,'2006-06-14','1900-01-01 00:00:00','1900-01-01 00:00:00'),(12,5,9,'2002-09-12','2006-12-03 09:37:26','2006-12-03 09:37:26'),(13,3,186,'2005-02-15','2008-05-26 12:27:10','2008-05-26 12:27:10'),(14,6,NULL,NULL,'2004-12-14 16:37:30','2004-12-14 16:37:30'),(15,92,2,'2008-11-04','2003-02-11 21:19:41','2003-02-11 21:19:41'),(16,7,3,'2004-09-04','2009-10-18 02:27:49','2009-10-18 02:27:49'),(17,NULL,0,'2006-06-05','2000-09-26 07:45:57','2000-09-26 07:45:57'),(18,3,133,'1900-01-01',NULL,NULL),(19,5,1,'1900-01-01','2005-11-10 12:40:29','2005-11-10 12:40:29'),(20,1,8,'1900-01-01','2009-04-25 00:00:00','2009-04-25 00:00:00'),(21,2,5,'2005-01-13','2002-11-27 00:00:00','2002-11-27 00:00:00'),(22,NULL,5,'2006-05-21','2004-01-26 20:32:32','2004-01-26 20:32:32'),(23,1,8,'2003-09-08','2007-10-26 11:41:40','2007-10-26 11:41:40'),(24,0,6,'2006-12-23','2005-10-07 00:00:00','2005-10-07 00:00:00'),(25,210,51,'2006-10-15','2000-07-15 05:00:34','2000-07-15 05:00:34'),(26,8,4,'2005-04-06','2000-04-03 16:33:32','2000-04-03 16:33:32'),(27,7,7,'2008-04-07',NULL,NULL),(28,5,6,'2006-10-10','2001-04-25 01:26:12','2001-04-25 01:26:12'),(29,NULL,4,'1900-01-01','2000-12-27 00:00:00','2000-12-27 00:00:00'); -CREATE TABLE `C` ( +Note 1831 Duplicate index 'varchar_key' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release. +INSERT INTO `t2` VALUES (10,7,8,NULL,'2002-02-26 06:14:37','2002-02-26 06:14:37'),(11,1,9,'2006-06-14','1900-01-01 00:00:00','1900-01-01 00:00:00'),(12,5,9,'2002-09-12','2006-12-03 09:37:26','2006-12-03 09:37:26'),(13,3,186,'2005-02-15','2008-05-26 12:27:10','2008-05-26 12:27:10'),(14,6,NULL,NULL,'2004-12-14 16:37:30','2004-12-14 16:37:30'),(15,92,2,'2008-11-04','2003-02-11 21:19:41','2003-02-11 21:19:41'),(16,7,3,'2004-09-04','2009-10-18 02:27:49','2009-10-18 02:27:49'),(17,NULL,0,'2006-06-05','2000-09-26 07:45:57','2000-09-26 07:45:57'),(18,3,133,'1900-01-01',NULL,NULL),(19,5,1,'1900-01-01','2005-11-10 12:40:29','2005-11-10 12:40:29'),(20,1,8,'1900-01-01','2009-04-25 00:00:00','2009-04-25 00:00:00'),(21,2,5,'2005-01-13','2002-11-27 00:00:00','2002-11-27 00:00:00'),(22,NULL,5,'2006-05-21','2004-01-26 20:32:32','2004-01-26 20:32:32'),(23,1,8,'2003-09-08','2007-10-26 11:41:40','2007-10-26 11:41:40'),(24,0,6,'2006-12-23','2005-10-07 00:00:00','2005-10-07 00:00:00'),(25,210,51,'2006-10-15','2000-07-15 05:00:34','2000-07-15 05:00:34'),(26,8,4,'2005-04-06','2000-04-03 16:33:32','2000-04-03 16:33:32'),(27,7,7,'2008-04-07',NULL,NULL),(28,5,6,'2006-10-10','2001-04-25 01:26:12','2001-04-25 01:26:12'),(29,NULL,4,'1900-01-01','2000-12-27 00:00:00','2000-12-27 00:00:00'); +CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, @@ -92,17 +92,17 @@ KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; Warnings: -Note 1831 Duplicate index 'varchar_key' defined on the table 'test.C'. This is deprecated and will be disallowed in a future release. -INSERT INTO `C` VALUES (1,NULL,2,NULL,'2004-10-11 18:13:16','2004-10-11 18:13:16'),(2,7,9,'2001-09-19',NULL,NULL),(3,9,3,'2004-09-12','1900-01-01 00:00:00','1900-01-01 00:00:00'),(4,7,9,NULL,'2009-07-25 00:00:00','2009-07-25 00:00:00'),(5,4,NULL,'2002-07-19',NULL,NULL),(6,2,9,'2002-12-16','2008-07-27 00:00:00','2008-07-27 00:00:00'),(7,6,3,'2006-02-08','2002-11-13 16:37:31','2002-11-13 16:37:31'),(8,8,8,'2006-08-28','1900-01-01 00:00:00','1900-01-01 00:00:00'),(9,NULL,8,'2001-04-14','2003-12-10 00:00:00','2003-12-10 00:00:00'),(10,5,53,'2000-01-05','2001-12-21 22:38:22','2001-12-21 22:38:22'),(11,NULL,0,'2003-12-06','2008-12-13 23:16:44','2008-12-13 23:16:44'),(12,6,5,'1900-01-01','2005-08-15 12:39:41','2005-08-15 12:39:41'),(13,188,166,'2002-11-27',NULL,NULL),(14,2,3,NULL,'2006-09-11 12:06:14','2006-09-11 12:06:14'),(15,1,0,'2003-05-27','2007-12-15 12:39:34','2007-12-15 12:39:34'),(16,1,1,'2005-05-03','2005-08-09 00:00:00','2005-08-09 00:00:00'),(17,0,9,'2001-04-18','2001-09-02 22:50:02','2001-09-02 22:50:02'),(18,9,5,'2005-12-27','2005-12-16 22:58:11','2005-12-16 22:58:11'),(19,NULL,6,'2004-08-20','2007-04-19 00:19:53','2007-04-19 00:19:53'),(20,4,2,'1900-01-01','1900-01-01 00:00:00','1900-01-01 00:00:00'); +Note 1831 Duplicate index 'varchar_key' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release. +INSERT INTO t1 VALUES (1,NULL,2,NULL,'2004-10-11 18:13:16','2004-10-11 18:13:16'),(2,7,9,'2001-09-19',NULL,NULL),(3,9,3,'2004-09-12','1900-01-01 00:00:00','1900-01-01 00:00:00'),(4,7,9,NULL,'2009-07-25 00:00:00','2009-07-25 00:00:00'),(5,4,NULL,'2002-07-19',NULL,NULL),(6,2,9,'2002-12-16','2008-07-27 00:00:00','2008-07-27 00:00:00'),(7,6,3,'2006-02-08','2002-11-13 16:37:31','2002-11-13 16:37:31'),(8,8,8,'2006-08-28','1900-01-01 00:00:00','1900-01-01 00:00:00'),(9,NULL,8,'2001-04-14','2003-12-10 00:00:00','2003-12-10 00:00:00'),(10,5,53,'2000-01-05','2001-12-21 22:38:22','2001-12-21 22:38:22'),(11,NULL,0,'2003-12-06','2008-12-13 23:16:44','2008-12-13 23:16:44'),(12,6,5,'1900-01-01','2005-08-15 12:39:41','2005-08-15 12:39:41'),(13,188,166,'2002-11-27',NULL,NULL),(14,2,3,NULL,'2006-09-11 12:06:14','2006-09-11 12:06:14'),(15,1,0,'2003-05-27','2007-12-15 12:39:34','2007-12-15 12:39:34'),(16,1,1,'2005-05-03','2005-08-09 00:00:00','2005-08-09 00:00:00'),(17,0,9,'2001-04-18','2001-09-02 22:50:02','2001-09-02 22:50:02'),(18,9,5,'2005-12-27','2005-12-16 22:58:11','2005-12-16 22:58:11'),(19,NULL,6,'2004-08-20','2007-04-19 00:19:53','2007-04-19 00:19:53'),(20,4,2,'1900-01-01','1900-01-01 00:00:00','1900-01-01 00:00:00'); SELECT OUTR . `pk` AS X -FROM C AS OUTR +FROM t1 AS OUTR WHERE OUTR . `pk` IN ( SELECT INNR . `int_key` AS Y -FROM CC AS INNR +FROM t2 AS INNR WHERE INNR . `date_nokey` < INNR . `datetime_nokey` XOR OUTR . `date_nokey` BETWEEN '2004-07-10' AND '2009-11-25' ORDER BY INNR . `int_nokey` ) AND ( OUTR . `datetime_key` BETWEEN '2000-05-25' AND '2004-08-07' OR OUTR . `datetime_nokey` = '2007-10-24' ) ORDER BY OUTR . `int_key` , OUTR . `pk`; X 9 -DROP TABLE CC; -DROP TABLE C; +DROP TABLE t2; +DROP TABLE t1; diff --git a/mysql-test/suite/optimizer_unfixed_bugs/t/bug45221.test b/mysql-test/suite/optimizer_unfixed_bugs/t/bug45221.test index aab93a72725..f8c6b9104fb 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/t/bug45221.test +++ b/mysql-test/suite/optimizer_unfixed_bugs/t/bug45221.test @@ -1,4 +1,4 @@ -# test for BUG#45221 "Query "SELECT pk FROM C WHERE pk IN (SELECT +# test for BUG#45221 "Query "SELECT pk FROM t1 WHERE pk IN (SELECT # int_key)" failing" --source include/have_debug.inc @@ -13,7 +13,7 @@ #/* Begin test case for query 0 */ -CREATE TABLE `CC` ( +CREATE TABLE `t2` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, `date_nokey` date DEFAULT NULL, @@ -22,8 +22,8 @@ CREATE TABLE `CC` ( KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; -INSERT INTO `CC` VALUES (10,8,NULL,'2002-02-26 06:14:37'),(11,9,'2006-06-14','1900-01-01 00:00:00'),(12,9,'2002-09-12','2006-12-03 09:37:26'),(13,186,'2005-02-15','2008-05-26 12:27:10'),(14,NULL,NULL,'2004-12-14 16:37:30'),(15,2,'2008-11-04','2003-02-11 21:19:41'),(16,3,'2004-09-04','2009-10-18 02:27:49'),(17,0,'2006-06-05','2000-09-26 07:45:57'),(18,133,'1900-01-01',NULL),(19,1,'1900-01-01','2005-11-10 12:40:29'),(20,8,'1900-01-01','2009-04-25 00:00:00'),(21,5,'2005-01-13','2002-11-27 00:00:00'),(22,5,'2006-05-21','2004-01-26 20:32:32'),(23,8,'2003-09-08','2007-10-26 11:41:40'),(24,6,'2006-12-23','2005-10-07 00:00:00'),(25,51,'2006-10-15','2000-07-15 05:00:34'),(26,4,'2005-04-06','2000-04-03 16:33:32'),(27,7,'2008-04-07',NULL),(28,6,'2006-10-10','2001-04-25 01:26:12'),(29,4,'1900-01-01','2000-12-27 00:00:00'); -CREATE TABLE `C` ( +INSERT INTO `t2` VALUES (10,8,NULL,'2002-02-26 06:14:37'),(11,9,'2006-06-14','1900-01-01 00:00:00'),(12,9,'2002-09-12','2006-12-03 09:37:26'),(13,186,'2005-02-15','2008-05-26 12:27:10'),(14,NULL,NULL,'2004-12-14 16:37:30'),(15,2,'2008-11-04','2003-02-11 21:19:41'),(16,3,'2004-09-04','2009-10-18 02:27:49'),(17,0,'2006-06-05','2000-09-26 07:45:57'),(18,133,'1900-01-01',NULL),(19,1,'1900-01-01','2005-11-10 12:40:29'),(20,8,'1900-01-01','2009-04-25 00:00:00'),(21,5,'2005-01-13','2002-11-27 00:00:00'),(22,5,'2006-05-21','2004-01-26 20:32:32'),(23,8,'2003-09-08','2007-10-26 11:41:40'),(24,6,'2006-12-23','2005-10-07 00:00:00'),(25,51,'2006-10-15','2000-07-15 05:00:34'),(26,4,'2005-04-06','2000-04-03 16:33:32'),(27,7,'2008-04-07',NULL),(28,6,'2006-10-10','2001-04-25 01:26:12'),(29,4,'1900-01-01','2000-12-27 00:00:00'); +CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, `date_nokey` date DEFAULT NULL, @@ -32,29 +32,29 @@ CREATE TABLE `C` ( KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; -INSERT INTO `C` VALUES (1,2,NULL,'2004-10-11 18:13:16'),(2,9,'2001-09-19',NULL),(3,3,'2004-09-12','1900-01-01 00:00:00'),(4,9,NULL,'2009-07-25 00:00:00'),(5,NULL,'2002-07-19',NULL),(6,9,'2002-12-16','2008-07-27 00:00:00'),(7,3,'2006-02-08','2002-11-13 16:37:31'),(8,8,'2006-08-28','1900-01-01 00:00:00'),(9,8,'2001-04-14','2003-12-10 00:00:00'),(10,53,'2000-01-05','2001-12-21 22:38:22'),(11,0,'2003-12-06','2008-12-13 23:16:44'),(12,5,'1900-01-01','2005-08-15 12:39:41'),(13,166,'2002-11-27',NULL),(14,3,NULL,'2006-09-11 12:06:14'),(15,0,'2003-05-27','2007-12-15 12:39:34'),(16,1,'2005-05-03','2005-08-09 00:00:00'),(17,9,'2001-04-18','2001-09-02 22:50:02'),(18,5,'2005-12-27','2005-12-16 22:58:11'),(19,6,'2004-08-20','2007-04-19 00:19:53'),(20,2,'1900-01-01','1900-01-01 00:00:00'); +INSERT INTO t1 VALUES (1,2,NULL,'2004-10-11 18:13:16'),(2,9,'2001-09-19',NULL),(3,3,'2004-09-12','1900-01-01 00:00:00'),(4,9,NULL,'2009-07-25 00:00:00'),(5,NULL,'2002-07-19',NULL),(6,9,'2002-12-16','2008-07-27 00:00:00'),(7,3,'2006-02-08','2002-11-13 16:37:31'),(8,8,'2006-08-28','1900-01-01 00:00:00'),(9,8,'2001-04-14','2003-12-10 00:00:00'),(10,53,'2000-01-05','2001-12-21 22:38:22'),(11,0,'2003-12-06','2008-12-13 23:16:44'),(12,5,'1900-01-01','2005-08-15 12:39:41'),(13,166,'2002-11-27',NULL),(14,3,NULL,'2006-09-11 12:06:14'),(15,0,'2003-05-27','2007-12-15 12:39:34'),(16,1,'2005-05-03','2005-08-09 00:00:00'),(17,9,'2001-04-18','2001-09-02 22:50:02'),(18,5,'2005-12-27','2005-12-16 22:58:11'),(19,6,'2004-08-20','2007-04-19 00:19:53'),(20,2,'1900-01-01','1900-01-01 00:00:00'); SELECT `pk` -FROM C OUTR +FROM t1 OUTR WHERE `pk` IN ( SELECT `int_key` -FROM CC +FROM t2 WHERE `date_nokey` < `datetime_nokey` XOR OUTR .`date_nokey` ) ; SELECT `pk` -FROM C +FROM t1 WHERE `pk` IN ( SELECT `int_key` -FROM CC +FROM t2 WHERE `date_nokey` < `datetime_nokey` XOR '2009-11-25' ) ; -DROP TABLE CC; -DROP TABLE C; +DROP TABLE t2; +DROP TABLE t1; #/* End of test case for query 0 */ #/* Begin test case for query 1 */ -CREATE TABLE `CC` ( +CREATE TABLE `t2` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, @@ -66,8 +66,8 @@ CREATE TABLE `CC` ( KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; -INSERT INTO `CC` VALUES (10,7,8,NULL,'2002-02-26 06:14:37','2002-02-26 06:14:37'),(11,1,9,'2006-06-14','1900-01-01 00:00:00','1900-01-01 00:00:00'),(12,5,9,'2002-09-12','2006-12-03 09:37:26','2006-12-03 09:37:26'),(13,3,186,'2005-02-15','2008-05-26 12:27:10','2008-05-26 12:27:10'),(14,6,NULL,NULL,'2004-12-14 16:37:30','2004-12-14 16:37:30'),(15,92,2,'2008-11-04','2003-02-11 21:19:41','2003-02-11 21:19:41'),(16,7,3,'2004-09-04','2009-10-18 02:27:49','2009-10-18 02:27:49'),(17,NULL,0,'2006-06-05','2000-09-26 07:45:57','2000-09-26 07:45:57'),(18,3,133,'1900-01-01',NULL,NULL),(19,5,1,'1900-01-01','2005-11-10 12:40:29','2005-11-10 12:40:29'),(20,1,8,'1900-01-01','2009-04-25 00:00:00','2009-04-25 00:00:00'),(21,2,5,'2005-01-13','2002-11-27 00:00:00','2002-11-27 00:00:00'),(22,NULL,5,'2006-05-21','2004-01-26 20:32:32','2004-01-26 20:32:32'),(23,1,8,'2003-09-08','2007-10-26 11:41:40','2007-10-26 11:41:40'),(24,0,6,'2006-12-23','2005-10-07 00:00:00','2005-10-07 00:00:00'),(25,210,51,'2006-10-15','2000-07-15 05:00:34','2000-07-15 05:00:34'),(26,8,4,'2005-04-06','2000-04-03 16:33:32','2000-04-03 16:33:32'),(27,7,7,'2008-04-07',NULL,NULL),(28,5,6,'2006-10-10','2001-04-25 01:26:12','2001-04-25 01:26:12'),(29,NULL,4,'1900-01-01','2000-12-27 00:00:00','2000-12-27 00:00:00'); -CREATE TABLE `C` ( +INSERT INTO `t2` VALUES (10,7,8,NULL,'2002-02-26 06:14:37','2002-02-26 06:14:37'),(11,1,9,'2006-06-14','1900-01-01 00:00:00','1900-01-01 00:00:00'),(12,5,9,'2002-09-12','2006-12-03 09:37:26','2006-12-03 09:37:26'),(13,3,186,'2005-02-15','2008-05-26 12:27:10','2008-05-26 12:27:10'),(14,6,NULL,NULL,'2004-12-14 16:37:30','2004-12-14 16:37:30'),(15,92,2,'2008-11-04','2003-02-11 21:19:41','2003-02-11 21:19:41'),(16,7,3,'2004-09-04','2009-10-18 02:27:49','2009-10-18 02:27:49'),(17,NULL,0,'2006-06-05','2000-09-26 07:45:57','2000-09-26 07:45:57'),(18,3,133,'1900-01-01',NULL,NULL),(19,5,1,'1900-01-01','2005-11-10 12:40:29','2005-11-10 12:40:29'),(20,1,8,'1900-01-01','2009-04-25 00:00:00','2009-04-25 00:00:00'),(21,2,5,'2005-01-13','2002-11-27 00:00:00','2002-11-27 00:00:00'),(22,NULL,5,'2006-05-21','2004-01-26 20:32:32','2004-01-26 20:32:32'),(23,1,8,'2003-09-08','2007-10-26 11:41:40','2007-10-26 11:41:40'),(24,0,6,'2006-12-23','2005-10-07 00:00:00','2005-10-07 00:00:00'),(25,210,51,'2006-10-15','2000-07-15 05:00:34','2000-07-15 05:00:34'),(26,8,4,'2005-04-06','2000-04-03 16:33:32','2000-04-03 16:33:32'),(27,7,7,'2008-04-07',NULL,NULL),(28,5,6,'2006-10-10','2001-04-25 01:26:12','2001-04-25 01:26:12'),(29,NULL,4,'1900-01-01','2000-12-27 00:00:00','2000-12-27 00:00:00'); +CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, @@ -79,19 +79,19 @@ CREATE TABLE `C` ( KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; -INSERT INTO `C` VALUES (1,NULL,2,NULL,'2004-10-11 18:13:16','2004-10-11 18:13:16'),(2,7,9,'2001-09-19',NULL,NULL),(3,9,3,'2004-09-12','1900-01-01 00:00:00','1900-01-01 00:00:00'),(4,7,9,NULL,'2009-07-25 00:00:00','2009-07-25 00:00:00'),(5,4,NULL,'2002-07-19',NULL,NULL),(6,2,9,'2002-12-16','2008-07-27 00:00:00','2008-07-27 00:00:00'),(7,6,3,'2006-02-08','2002-11-13 16:37:31','2002-11-13 16:37:31'),(8,8,8,'2006-08-28','1900-01-01 00:00:00','1900-01-01 00:00:00'),(9,NULL,8,'2001-04-14','2003-12-10 00:00:00','2003-12-10 00:00:00'),(10,5,53,'2000-01-05','2001-12-21 22:38:22','2001-12-21 22:38:22'),(11,NULL,0,'2003-12-06','2008-12-13 23:16:44','2008-12-13 23:16:44'),(12,6,5,'1900-01-01','2005-08-15 12:39:41','2005-08-15 12:39:41'),(13,188,166,'2002-11-27',NULL,NULL),(14,2,3,NULL,'2006-09-11 12:06:14','2006-09-11 12:06:14'),(15,1,0,'2003-05-27','2007-12-15 12:39:34','2007-12-15 12:39:34'),(16,1,1,'2005-05-03','2005-08-09 00:00:00','2005-08-09 00:00:00'),(17,0,9,'2001-04-18','2001-09-02 22:50:02','2001-09-02 22:50:02'),(18,9,5,'2005-12-27','2005-12-16 22:58:11','2005-12-16 22:58:11'),(19,NULL,6,'2004-08-20','2007-04-19 00:19:53','2007-04-19 00:19:53'),(20,4,2,'1900-01-01','1900-01-01 00:00:00','1900-01-01 00:00:00'); +INSERT INTO t1 VALUES (1,NULL,2,NULL,'2004-10-11 18:13:16','2004-10-11 18:13:16'),(2,7,9,'2001-09-19',NULL,NULL),(3,9,3,'2004-09-12','1900-01-01 00:00:00','1900-01-01 00:00:00'),(4,7,9,NULL,'2009-07-25 00:00:00','2009-07-25 00:00:00'),(5,4,NULL,'2002-07-19',NULL,NULL),(6,2,9,'2002-12-16','2008-07-27 00:00:00','2008-07-27 00:00:00'),(7,6,3,'2006-02-08','2002-11-13 16:37:31','2002-11-13 16:37:31'),(8,8,8,'2006-08-28','1900-01-01 00:00:00','1900-01-01 00:00:00'),(9,NULL,8,'2001-04-14','2003-12-10 00:00:00','2003-12-10 00:00:00'),(10,5,53,'2000-01-05','2001-12-21 22:38:22','2001-12-21 22:38:22'),(11,NULL,0,'2003-12-06','2008-12-13 23:16:44','2008-12-13 23:16:44'),(12,6,5,'1900-01-01','2005-08-15 12:39:41','2005-08-15 12:39:41'),(13,188,166,'2002-11-27',NULL,NULL),(14,2,3,NULL,'2006-09-11 12:06:14','2006-09-11 12:06:14'),(15,1,0,'2003-05-27','2007-12-15 12:39:34','2007-12-15 12:39:34'),(16,1,1,'2005-05-03','2005-08-09 00:00:00','2005-08-09 00:00:00'),(17,0,9,'2001-04-18','2001-09-02 22:50:02','2001-09-02 22:50:02'),(18,9,5,'2005-12-27','2005-12-16 22:58:11','2005-12-16 22:58:11'),(19,NULL,6,'2004-08-20','2007-04-19 00:19:53','2007-04-19 00:19:53'),(20,4,2,'1900-01-01','1900-01-01 00:00:00','1900-01-01 00:00:00'); SELECT OUTR . `pk` AS X -FROM C AS OUTR +FROM t1 AS OUTR WHERE OUTR . `pk` IN ( SELECT INNR . `int_key` AS Y -FROM CC AS INNR +FROM t2 AS INNR WHERE INNR . `date_nokey` < INNR . `datetime_nokey` XOR OUTR . `date_nokey` BETWEEN '2004-07-10' AND '2009-11-25' ORDER BY INNR . `int_nokey` ) AND ( OUTR . `datetime_key` BETWEEN '2000-05-25' AND '2004-08-07' OR OUTR . `datetime_nokey` = '2007-10-24' ) ORDER BY OUTR . `int_key` , OUTR . `pk`; -DROP TABLE CC; -DROP TABLE C; +DROP TABLE t2; +DROP TABLE t1; #/* End of test case for query 1 */ diff --git a/mysql-test/suite/parts/inc/partition_binary.inc b/mysql-test/suite/parts/inc/partition_binary.inc index 08db71b6483..21e9635c036 100644 --- a/mysql-test/suite/parts/inc/partition_binary.inc +++ b/mysql-test/suite/parts/inc/partition_binary.inc @@ -8,11 +8,13 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (repeat('a',255)), ('b'), (repeat('a',128)), (repeat('b',64)); +--sorted_result select hex(a) from t1; select a from t1 where substr(a,1,2)='b\0'; update t1 set a='cc' where substr(a,1,2)= 'b\0'; select a from t1 where substr(a,1,1)='c'; delete from t1 where substr(a,1,2)='cc'; +--sorted_result select hex(a) from t1; drop table t1; @@ -33,6 +35,7 @@ inc $letter; commit; --enable_query_log select count(*) from t2; +--sorted_result select hex(a) from t2; drop table t2; diff --git a/mysql-test/suite/parts/inc/partition_char.inc b/mysql-test/suite/parts/inc/partition_char.inc index 0fa28370c83..d28ed3158c5 100644 --- a/mysql-test/suite/parts/inc/partition_char.inc +++ b/mysql-test/suite/parts/inc/partition_char.inc @@ -8,10 +8,12 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (repeat('a',255)), ('b'), (repeat('a',128)), (repeat('b',64)); +--sorted_result select * from t1; select * from t1 where a='b'; update t1 set a='bb' where a='b'; delete from t1 where a='bb'; +--sorted_result select * from t1; drop table t1; @@ -32,6 +34,7 @@ inc $letter; commit; --enable_query_log select count(*) from t2; +--sorted_result select * from t2; drop table t2; diff --git a/mysql-test/suite/parts/inc/partition_enum.inc b/mysql-test/suite/parts/inc/partition_enum.inc index a545d7fd8c7..87e88983055 100644 --- a/mysql-test/suite/parts/inc/partition_enum.inc +++ b/mysql-test/suite/parts/inc/partition_enum.inc @@ -8,11 +8,13 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values ('A'),('D'),('L'),('G'); +--sorted_result select * from t1; select * from t1 where a='A'; update t1 set a='E' where a='L'; select * from t1; delete from t1 where a='E'; +--sorted_result select * from t1; drop table t1; @@ -37,6 +39,7 @@ commit; --enable_query_log insert into t2 values ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0'); select count(*) from t2; +--sorted_result select * from t2; drop table t2; diff --git a/mysql-test/suite/parts/inc/partition_varbinary.inc b/mysql-test/suite/parts/inc/partition_varbinary.inc index 04ba66d87ea..292407cd525 100644 --- a/mysql-test/suite/parts/inc/partition_varbinary.inc +++ b/mysql-test/suite/parts/inc/partition_varbinary.inc @@ -8,10 +8,12 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (repeat('a',767)), ('b'), (repeat('a',500)), (repeat('b',64)); +--sorted_result select * from t1; select * from t1 where a='b'; update t1 set a='bb' where a='b'; delete from t1 where a='bb'; +--sorted_result select * from t1; drop table t1; @@ -32,6 +34,7 @@ inc $letter; commit; --enable_query_log select count(*) from t2; +--sorted_result select * from t2; drop table t2; diff --git a/mysql-test/suite/parts/inc/partition_varchar.inc b/mysql-test/suite/parts/inc/partition_varchar.inc index 33c61034a52..257c122ddf2 100644 --- a/mysql-test/suite/parts/inc/partition_varchar.inc +++ b/mysql-test/suite/parts/inc/partition_varchar.inc @@ -8,10 +8,12 @@ partition pa3 max_rows=30 min_rows=4, partition pa4 max_rows=40 min_rows=2); show create table t1; insert into t1 values (repeat('a',767)), ('b'), (repeat('a',500)), (repeat('b',64)); +--sorted_result select * from t1; select * from t1 where a='b'; update t1 set a='bb' where a='b'; delete from t1 where a='bb'; +--sorted_result select * from t1; drop table t1; @@ -32,6 +34,7 @@ inc $letter; commit; --enable_query_log select count(*) from t2; +--sorted_result select * from t2; drop table t2; diff --git a/mysql-test/suite/parts/r/partition_char_innodb.result b/mysql-test/suite/parts/r/partition_char_innodb.result Binary files differindex 6f4a107f840..bb99e89b022 100644 --- a/mysql-test/suite/parts/r/partition_char_innodb.result +++ b/mysql-test/suite/parts/r/partition_char_innodb.result diff --git a/mysql-test/suite/parts/r/partition_char_myisam.result b/mysql-test/suite/parts/r/partition_char_myisam.result Binary files differindex 913a874b646..9936f3ec0f4 100644 --- a/mysql-test/suite/parts/r/partition_char_myisam.result +++ b/mysql-test/suite/parts/r/partition_char_myisam.result diff --git a/mysql-test/suite/perfschema/r/misc.result b/mysql-test/suite/perfschema/r/misc.result index 433d793909f..2adf2cba851 100644 --- a/mysql-test/suite/perfschema/r/misc.result +++ b/mysql-test/suite/perfschema/r/misc.result @@ -107,3 +107,14 @@ select mysql_errno, returned_sqlstate, message_text, errors, warnings from performance_schema.events_statements_history_long where errors > 0; mysql_errno returned_sqlstate message_text errors warnings 1146 42S02 Table 'test.t1' doesn't exist 1 0 +use performance_schema; +truncate performance_schema.events_statements_history; +select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AS A; +A +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +select _utf8mb4 'ваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑ' as B; +B +васвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвасвас +select count(*) from events_statements_history where sql_text like "%..."; +count(*) +2 diff --git a/mysql-test/suite/perfschema/t/misc.test b/mysql-test/suite/perfschema/t/misc.test index 00b175ec391..bf3e8afffdc 100644 --- a/mysql-test/suite/perfschema/t/misc.test +++ b/mysql-test/suite/perfschema/t/misc.test @@ -4,6 +4,7 @@ --source include/not_embedded.inc --source include/have_perfschema.inc --source include/have_innodb.inc +--source include/no_protocol.inc # # Bug#12790483 OBJECTS_SUMMARY_GLOBAL_BY_TYPE AND RENAME TABLE @@ -189,3 +190,20 @@ select mysql_errno, returned_sqlstate, message_text, errors, warnings --echo select mysql_errno, returned_sqlstate, message_text, errors, warnings from performance_schema.events_statements_history_long where errors > 0; + +# +# Bug#20519832 - TRUNCATED SQL_TEXT values are not suffixed with '...' +# +# Verify that truncated SQL statements are suffixed with '...' + +use performance_schema; +truncate performance_schema.events_statements_history; + +# Should truncate at 1024 bytes (1024 characters) +select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AS A; + +# Should truncate at 1024 bytes (487 characters) + +select _utf8mb4 'ваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑваÑÑ' as B; + +select count(*) from events_statements_history where sql_text like "%..."; diff --git a/mysql-test/suite/plugins/r/auth_v0100.result b/mysql-test/suite/plugins/r/auth_v0100.result index 6b5f5c917f3..325d3dd882c 100644 --- a/mysql-test/suite/plugins/r/auth_v0100.result +++ b/mysql-test/suite/plugins/r/auth_v0100.result @@ -1,9 +1,8 @@ install soname 'auth_0x0100'; -select plugin_name, plugin_type_version from information_schema.plugins where plugin_type='authentication' order by plugin_name; +select plugin_name, plugin_type_version from information_schema.plugins where +plugin_type='authentication' and plugin_library is not null order by plugin_name; plugin_name plugin_type_version auth_0x0100 1.0 -mysql_native_password 2.0 -mysql_old_password 2.0 create user tt identified via auth_0x0100; create user zzzzzzzzzzzzzzzz; grant select on test.* to zzzzzzzzzzzzzzzz; diff --git a/mysql-test/suite/plugins/t/auth_v0100.test b/mysql-test/suite/plugins/t/auth_v0100.test index 51f8d8941ef..5d0f6f40be9 100644 --- a/mysql-test/suite/plugins/t/auth_v0100.test +++ b/mysql-test/suite/plugins/t/auth_v0100.test @@ -6,7 +6,8 @@ if (!$AUTH_0X0100_SO) { install soname 'auth_0x0100'; -select plugin_name, plugin_type_version from information_schema.plugins where plugin_type='authentication' order by plugin_name; +select plugin_name, plugin_type_version from information_schema.plugins where +plugin_type='authentication' and plugin_library is not null order by plugin_name; create user tt identified via auth_0x0100; create user zzzzzzzzzzzzzzzz; diff --git a/mysql-test/suite/rpl/r/rpl_relay_space_innodb.result b/mysql-test/suite/rpl/r/rpl_relay_space_innodb.result index 4f10ac50f9a..ebce070190d 100644 --- a/mysql-test/suite/rpl/r/rpl_relay_space_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_relay_space_innodb.result @@ -7,7 +7,8 @@ CREATE TABLE t1 (name varchar(64), age smallint(3))ENGINE=InnoDB; INSERT INTO t1 SET name='Andy', age=31; INSERT INTO t1 SET name='Jacob', age=2; INSERT INTO t1 SET name='Caleb', age=1; -ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY; +ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY, +ALGORITHM=COPY; SELECT * FROM t1 ORDER BY id; name age id Andy 31 00000001 diff --git a/mysql-test/suite/rpl/r/rpl_relay_space_myisam.result b/mysql-test/suite/rpl/r/rpl_relay_space_myisam.result index 4c1a50b2f33..b2fc0f715ea 100644 --- a/mysql-test/suite/rpl/r/rpl_relay_space_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_relay_space_myisam.result @@ -7,7 +7,8 @@ CREATE TABLE t1 (name varchar(64), age smallint(3))ENGINE=MyISAM; INSERT INTO t1 SET name='Andy', age=31; INSERT INTO t1 SET name='Jacob', age=2; INSERT INTO t1 SET name='Caleb', age=1; -ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY; +ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY, +ALGORITHM=COPY; SELECT * FROM t1 ORDER BY id; name age id Andy 31 00000001 diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result index 70148afcacb..1035df2456c 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result @@ -493,13 +493,13 @@ include/diff_tables.inc [master:t4, slave:t4] [expecting slave to stop] INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); -include/wait_for_slave_sql_error.inc [errno=1677 ] +include/wait_for_slave_sql_error.inc [errno=1677] Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' include/rpl_reset.inc [expecting slave to stop] INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); -include/wait_for_slave_sql_error.inc [errno=1677 ] +include/wait_for_slave_sql_error.inc [errno=1677] Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' include/rpl_reset.inc [expecting slave to replicate correctly] diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result index 89557b554d0..0c12eb544a5 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result @@ -496,13 +496,13 @@ include/diff_tables.inc [master:t4, slave:t4] [expecting slave to stop] INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); -include/wait_for_slave_sql_error.inc [errno=1677 ] +include/wait_for_slave_sql_error.inc [errno=1677] Last_SQL_Error = 'Column 1 of table 'test.t5' cannot be converted from type 'char(255)' to type 'char(16)'' include/rpl_reset.inc [expecting slave to stop] INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); -include/wait_for_slave_sql_error.inc [errno=1677 ] +include/wait_for_slave_sql_error.inc [errno=1677] Last_SQL_Error = 'Column 1 of table 'test.t6' cannot be converted from type 'char(255)' to type 'char(128)'' include/rpl_reset.inc [expecting slave to replicate correctly] diff --git a/mysql-test/suite/rpl/r/rpl_row_rec_comp_innodb.result b/mysql-test/suite/rpl/r/rpl_row_rec_comp_innodb.result index 523564a222e..d9ebb52493b 100644 --- a/mysql-test/suite/rpl/r/rpl_row_rec_comp_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_rec_comp_innodb.result @@ -25,10 +25,4 @@ INSERT INTO t1(c1) VALUES (NULL); UPDATE t1 SET c1= 0; include/diff_tables.inc [master:t1, slave:t1] DROP TABLE t1; -include/rpl_reset.inc -CREATE TABLE t1 (c1 int(11) NOT NULL, c2 int(11) NOT NULL, c3 int(11) DEFAULT '-1') ENGINE=InnoDB DEFAULT CHARSET=latin1; -INSERT INTO t1 VALUES (1,2,NULL); -UPDATE t1 SET c1=1, c2=2, c3=-1 WHERE c1=1 AND c2=2 AND ISNULL(c3); -include/diff_tables.inc [master:test.t1, slave:test.t1] -DROP TABLE t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_rec_comp_myisam.result b/mysql-test/suite/rpl/r/rpl_row_rec_comp_myisam.result index 4dc7c0bc7a3..1ec98a81a8b 100644 --- a/mysql-test/suite/rpl/r/rpl_row_rec_comp_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_rec_comp_myisam.result @@ -34,10 +34,4 @@ INSERT INTO t1(c1) VALUES (NULL); UPDATE t1 SET c1= 0; include/diff_tables.inc [master:t1, slave:t1] DROP TABLE t1; -include/rpl_reset.inc -CREATE TABLE t1 (c1 int(11) NOT NULL, c2 int(11) NOT NULL, c3 int(11) DEFAULT '-1') ENGINE=MyISAM DEFAULT CHARSET=latin1; -INSERT INTO t1 VALUES (1,2,NULL); -UPDATE t1 SET c1=1, c2=2, c3=-1 WHERE c1=1 AND c2=2 AND ISNULL(c3); -include/diff_tables.inc [master:test.t1, slave:test.t1] -DROP TABLE t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_start_stop_slave.result b/mysql-test/suite/rpl/r/rpl_start_stop_slave.result index 7344acec4ec..b6cbdc9686b 100644 --- a/mysql-test/suite/rpl/r/rpl_start_stop_slave.result +++ b/mysql-test/suite/rpl/r/rpl_start_stop_slave.result @@ -8,4 +8,6 @@ set @time_after_kill := (select CURRENT_TIMESTAMP); [Time after the query] [Killing of the slave IO thread was successful] START SLAVE IO_THREAD; +create table t1 (a int primary key); +drop table t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test b/mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test index e270d81a5e5..d3412c3cd52 100644 --- a/mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test +++ b/mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test @@ -3,6 +3,7 @@ # tables. Specifically when drop temporary tables and create temporary tables # are used. ################################################################################### +--source include/big_test.inc --source include/have_binlog_format_mixed.inc --source include/have_innodb.inc --source include/master-slave.inc diff --git a/mysql-test/suite/rpl/t/rpl_start_stop_slave.test b/mysql-test/suite/rpl/t/rpl_start_stop_slave.test index ab388f3eebc..a38ac8fc6a1 100644 --- a/mysql-test/suite/rpl/t/rpl_start_stop_slave.test +++ b/mysql-test/suite/rpl/t/rpl_start_stop_slave.test @@ -53,5 +53,12 @@ if(`select TIMESTAMPDIFF(SECOND,@time_after_kill, @time_before_kill) > 60`) START SLAVE IO_THREAD; +# Ensure that the slave io thread started properly +connection master; +create table t1 (a int primary key); +sync_slave_with_master; +connection master; +drop table t1; + # End of test --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test b/mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test index 6e3dabf6e32..7770fc77748 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test +++ b/mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test @@ -3,6 +3,7 @@ # tables. Specifically when drop temporary tables and create temporary tables # are used. ################################################################################### +--source include/big_test.inc --source include/have_binlog_format_statement.inc --source include/master-slave.inc --source include/have_innodb.inc diff --git a/mysql-test/suite/rpl/t/semisync_future-7591.test b/mysql-test/suite/rpl/t/semisync_future-7591.test index 772a36b5632..daf3d2f8571 100644 --- a/mysql-test/suite/rpl/t/semisync_future-7591.test +++ b/mysql-test/suite/rpl/t/semisync_future-7591.test @@ -27,5 +27,7 @@ reset slave; set global rpl_semi_sync_slave_enabled = OFF; --connection master drop table t1; +--sync_slave_with_master +--connection master set global rpl_semi_sync_master_enabled = OFF; --source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/all_vars.result b/mysql-test/suite/sys_vars/r/all_vars.result index b812a5dbee3..1bd4e394f6a 100644 --- a/mysql-test/suite/sys_vars/r/all_vars.result +++ b/mysql-test/suite/sys_vars/r/all_vars.result @@ -10,11 +10,5 @@ there should be *no* long test name listed below: select distinct variable_name as `there should be *no* variables listed below:` from t2 left join t1 on variable_name=test_name where test_name is null; there should be *no* variables listed below: -encrypt_tmp_files -innodb_default_encryption_key_id -max_digest_length -strict_password_validation -wsrep_patch_version -pfs_max_digest_length drop table t1; drop table t2; diff --git a/mysql-test/suite/sys_vars/r/max_digest_length_basic.result b/mysql-test/suite/sys_vars/r/max_digest_length_basic.result new file mode 100644 index 00000000000..1a733273614 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/max_digest_length_basic.result @@ -0,0 +1,23 @@ +select @@global.max_digest_length; +@@global.max_digest_length +1024 +select @@session.max_digest_length; +ERROR HY000: Variable 'max_digest_length' is a GLOBAL variable +show global variables like 'max_digest_length'; +Variable_name Value +max_digest_length 1024 +show session variables like 'max_digest_length'; +Variable_name Value +max_digest_length 1024 +select * from information_schema.global_variables +where variable_name='max_digest_length'; +VARIABLE_NAME VARIABLE_VALUE +MAX_DIGEST_LENGTH 1024 +select * from information_schema.session_variables +where variable_name='max_digest_length'; +VARIABLE_NAME VARIABLE_VALUE +MAX_DIGEST_LENGTH 1024 +set global max_digest_length=1; +ERROR HY000: Variable 'max_digest_length' is a read only variable +set session max_digest_length=1; +ERROR HY000: Variable 'max_digest_length' is a read only variable diff --git a/mysql-test/suite/sys_vars/r/pfs_max_digest_length_basic.result b/mysql-test/suite/sys_vars/r/pfs_max_digest_length_basic.result new file mode 100644 index 00000000000..12e48ee50ba --- /dev/null +++ b/mysql-test/suite/sys_vars/r/pfs_max_digest_length_basic.result @@ -0,0 +1,23 @@ +select @@global.performance_schema_max_digest_length; +@@global.performance_schema_max_digest_length +1024 +select @@session.performance_schema_max_digest_length; +ERROR HY000: Variable 'performance_schema_max_digest_length' is a GLOBAL variable +show global variables like 'performance_schema_max_digest_length'; +Variable_name Value +performance_schema_max_digest_length 1024 +show session variables like 'performance_schema_max_digest_length'; +Variable_name Value +performance_schema_max_digest_length 1024 +select * from information_schema.global_variables +where variable_name='performance_schema_max_digest_length'; +VARIABLE_NAME VARIABLE_VALUE +PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH 1024 +select * from information_schema.session_variables +where variable_name='performance_schema_max_digest_length'; +VARIABLE_NAME VARIABLE_VALUE +PERFORMANCE_SCHEMA_MAX_DIGEST_LENGTH 1024 +set global performance_schema_max_digest_length=1; +ERROR HY000: Variable 'performance_schema_max_digest_length' is a read only variable +set session performance_schema_max_digest_length=1; +ERROR HY000: Variable 'performance_schema_max_digest_length' is a read only variable diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff b/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff index 74590d94d29..520728d3def 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff @@ -21,7 +21,7 @@ VARIABLE_NAME INNODB_ADAPTIVE_MAX_SLEEP_DELAY SESSION_VALUE NULL GLOBAL_VALUE 150000 -@@ -341,6 +355,20 @@ +@@ -355,6 +369,20 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -32,7 +32,7 @@ +DEFAULT_VALUE OFF +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE BOOLEAN -+VARIABLE_COMMENT Preallocate (pre-fault) the page frames required for the mapping established by the buffer pool memory region. Disabled by default. ++VARIABLE_COMMENT Depricated. This option is temporary alias of --innodb-numa-interleave. +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL @@ -42,7 +42,7 @@ VARIABLE_NAME INNODB_BUFFER_POOL_SIZE SESSION_VALUE NULL GLOBAL_VALUE 8388608 -@@ -432,7 +460,7 @@ +@@ -446,7 +474,7 @@ DEFAULT_VALUE ON VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BOOLEAN @@ -51,7 +51,7 @@ NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -@@ -453,6 +481,104 @@ +@@ -467,6 +495,104 @@ ENUM_VALUE_LIST CRC32,STRICT_CRC32,INNODB,STRICT_INNODB,NONE,STRICT_NONE READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -156,7 +156,7 @@ VARIABLE_NAME INNODB_CMP_PER_INDEX_ENABLED SESSION_VALUE NULL GLOBAL_VALUE OFF -@@ -551,6 +677,20 @@ +@@ -565,6 +691,20 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -177,7 +177,7 @@ VARIABLE_NAME INNODB_DATA_FILE_PATH SESSION_VALUE NULL GLOBAL_VALUE ibdata1:12M:autoextend -@@ -747,6 +887,20 @@ +@@ -761,6 +901,20 @@ ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT OPTIONAL @@ -198,7 +198,7 @@ VARIABLE_NAME INNODB_ENCRYPTION_ROTATE_KEY_AGE SESSION_VALUE NULL GLOBAL_VALUE 1 -@@ -817,6 +971,20 @@ +@@ -831,6 +985,20 @@ ENUM_VALUE_LIST OFF,ON,FORCE READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL @@ -219,7 +219,7 @@ VARIABLE_NAME INNODB_FAST_SHUTDOWN SESSION_VALUE NULL GLOBAL_VALUE 1 -@@ -944,11 +1112,11 @@ +@@ -958,11 +1126,11 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_FLUSH_LOG_AT_TRX_COMMIT @@ -233,7 +233,7 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Controls the durability/speed trade-off for commits. Set to 0 (write and flush redo log to disk only once per second), 1 (flush to disk at each commit), 2 (write to log at commit but flush to disk only once per second) or 3 (flush to disk at prepare and at commit, slower and usually redundant). 1 and 3 guarantees that after a crash, committed transactions will not be lost and will be consistent with the binlog and other transactional engines. 2 can get inconsistent and lose transactions if there is a power failure or kernel crash but not if mysqld crashes. 0 has no guarantees in case of crash. 0 and 2 can be faster than 1 or 3. NUMERIC_MIN_VALUE 0 -@@ -1041,6 +1209,20 @@ +@@ -1055,6 +1223,20 @@ ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED @@ -254,7 +254,7 @@ VARIABLE_NAME INNODB_FT_AUX_TABLE SESSION_VALUE NULL GLOBAL_VALUE -@@ -1279,6 +1461,20 @@ +@@ -1293,6 +1475,20 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -275,7 +275,7 @@ VARIABLE_NAME INNODB_LARGE_PREFIX SESSION_VALUE NULL GLOBAL_VALUE OFF -@@ -1307,6 +1503,20 @@ +@@ -1321,6 +1517,20 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -296,7 +296,7 @@ VARIABLE_NAME INNODB_LOCKS_UNSAFE_FOR_BINLOG SESSION_VALUE NULL GLOBAL_VALUE OFF -@@ -1335,6 +1545,62 @@ +@@ -1349,6 +1559,62 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -359,7 +359,7 @@ VARIABLE_NAME INNODB_LOG_BUFFER_SIZE SESSION_VALUE NULL GLOBAL_VALUE 1048576 -@@ -1363,6 +1629,20 @@ +@@ -1377,6 +1643,20 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL @@ -380,7 +380,7 @@ VARIABLE_NAME INNODB_LOG_COMPRESSED_PAGES SESSION_VALUE NULL GLOBAL_VALUE OFF -@@ -1433,6 +1713,34 @@ +@@ -1447,6 +1727,34 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -415,7 +415,7 @@ VARIABLE_NAME INNODB_MAX_DIRTY_PAGES_PCT SESSION_VALUE NULL GLOBAL_VALUE 75.000000 -@@ -1699,6 +2007,62 @@ +@@ -1713,6 +2021,62 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL @@ -478,7 +478,7 @@ VARIABLE_NAME INNODB_PURGE_BATCH_SIZE SESSION_VALUE NULL GLOBAL_VALUE 300 -@@ -1867,6 +2231,48 @@ +@@ -1881,6 +2245,48 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL @@ -527,7 +527,7 @@ VARIABLE_NAME INNODB_SCRUB_LOG SESSION_VALUE NULL GLOBAL_VALUE OFF -@@ -1895,6 +2301,34 @@ +@@ -1909,6 +2315,34 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL @@ -562,7 +562,7 @@ VARIABLE_NAME INNODB_SIMULATE_COMP_FAILURES SESSION_VALUE NULL GLOBAL_VALUE 0 -@@ -1958,7 +2392,7 @@ +@@ -1972,7 +2406,7 @@ DEFAULT_VALUE nulls_equal VARIABLE_SCOPE GLOBAL VARIABLE_TYPE ENUM @@ -571,7 +571,7 @@ NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -@@ -2189,6 +2623,34 @@ +@@ -2203,6 +2637,34 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED @@ -606,7 +606,7 @@ VARIABLE_NAME INNODB_TRX_PURGE_VIEW_UPDATE_ONLY_DEBUG SESSION_VALUE NULL GLOBAL_VALUE OFF -@@ -2266,7 +2728,7 @@ +@@ -2280,7 +2742,7 @@ DEFAULT_VALUE OFF VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BOOLEAN @@ -615,7 +615,7 @@ NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -@@ -2287,6 +2749,20 @@ +@@ -2301,6 +2763,20 @@ ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT NONE @@ -636,7 +636,7 @@ VARIABLE_NAME INNODB_USE_MTFLUSH SESSION_VALUE NULL GLOBAL_VALUE OFF -@@ -2301,6 +2777,20 @@ +@@ -2315,6 +2791,20 @@ ENUM_VALUE_LIST NULL READ_ONLY YES COMMAND_LINE_ARGUMENT NONE @@ -657,12 +657,12 @@ VARIABLE_NAME INNODB_USE_SYS_MALLOC SESSION_VALUE NULL GLOBAL_VALUE ON -@@ -2331,12 +2821,12 @@ +@@ -2345,12 +2835,12 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_VERSION SESSION_VALUE NULL -GLOBAL_VALUE 5.6.27 -+GLOBAL_VALUE 5.6.26-74.0 ++GLOBAL_VALUE 5.6.26-76.0 GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE NULL VARIABLE_SCOPE GLOBAL diff --git a/mysql-test/suite/sys_vars/t/max_digest_length_basic.test b/mysql-test/suite/sys_vars/t/max_digest_length_basic.test new file mode 100644 index 00000000000..b5e1d834d7e --- /dev/null +++ b/mysql-test/suite/sys_vars/t/max_digest_length_basic.test @@ -0,0 +1,46 @@ +# Copyright (C) 2010 Sun Microsystems, Inc. +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +--source include/not_embedded.inc + +# +# Only global +# + +select @@global.max_digest_length; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.max_digest_length; + +show global variables like 'max_digest_length'; + +show session variables like 'max_digest_length'; + +select * from information_schema.global_variables + where variable_name='max_digest_length'; + +select * from information_schema.session_variables + where variable_name='max_digest_length'; + +# +# Read-only +# + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set global max_digest_length=1; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set session max_digest_length=1; + diff --git a/mysql-test/suite/sys_vars/t/pfs_max_digest_length-master_basic.opt b/mysql-test/suite/sys_vars/t/pfs_max_digest_length-master_basic.opt new file mode 100644 index 00000000000..a98eb86d2e6 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/pfs_max_digest_length-master_basic.opt @@ -0,0 +1,2 @@ +--loose-enable-performance-schema +--loose-performance-schema-max_digest_length=1024 diff --git a/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test b/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test new file mode 100644 index 00000000000..5c89dbda07f --- /dev/null +++ b/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test @@ -0,0 +1,47 @@ +# Copyright (C) 2010 Sun Microsystems, Inc. +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; version 2 of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + +--source include/not_embedded.inc +--source include/have_perfschema.inc + +# +# Only global +# + +select @@global.performance_schema_max_digest_length; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.performance_schema_max_digest_length; + +show global variables like 'performance_schema_max_digest_length'; + +show session variables like 'performance_schema_max_digest_length'; + +select * from information_schema.global_variables + where variable_name='performance_schema_max_digest_length'; + +select * from information_schema.session_variables + where variable_name='performance_schema_max_digest_length'; + +# +# Read-only +# + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set global performance_schema_max_digest_length=1; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set session performance_schema_max_digest_length=1; + diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index 279777b8b36..451898aa972 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -322,6 +322,14 @@ drop table t1; create table t1 (a int, b int as (b is null) virtual); ERROR HY000: A computed column cannot be based on a computed column # end of 5.3 tests +create table t1 (v1 varchar(255) as (c1) persistent, c1 varchar(50)) collate=latin1_general_ci; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v1` varchar(255) AS (c1) PERSISTENT, + `c1` varchar(50) COLLATE latin1_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci +drop table t1; # # Start of 10.1 tests # diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test index c5b534fe200..c78093bce0f 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -284,6 +284,13 @@ create table t1 (a int, b int as (b is null) virtual); --echo # end of 5.3 tests +# +# MDEV-7655 SHOW CREATE TABLE returns invalid DDL when using virtual columns along with a table collation +# +create table t1 (v1 varchar(255) as (c1) persistent, c1 varchar(50)) collate=latin1_general_ci; +show create table t1; +drop table t1; + --echo # --echo # Start of 10.1 tests |