diff options
| author | Nirbhay Choubey <nirbhay@mariadb.com> | 2016-06-24 12:01:22 -0400 |
|---|---|---|
| committer | Nirbhay Choubey <nirbhay@mariadb.com> | 2016-06-24 12:01:22 -0400 |
| commit | 14d62505d90f01eb6d81466c1d69fa38523c4d3e (patch) | |
| tree | 98e08ad71dd605fdfbad22080be969ecae6747ce /mysql-test | |
| parent | 868c2ceb013e06c29ba37d4634f2d543b96539aa (diff) | |
| parent | 9fc102b37ec45bf5a1010a9f660137304de482bc (diff) | |
| download | mariadb-git-14d62505d90f01eb6d81466c1d69fa38523c4d3e.tar.gz | |
Merge tag 'mariadb-10.0.26' into 10.0-galera
Diffstat (limited to 'mysql-test')
59 files changed, 1606 insertions, 325 deletions
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index a13a820dfe7..154f2b27d57 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -1813,9 +1813,12 @@ sub set_build_thread_ports($) { if ( lc($opt_build_thread) eq 'auto' ) { my $found_free = 0; $build_thread = 300; # Start attempts from here + my $build_thread_upper = $build_thread + ($opt_parallel > 1500 + ? 3000 + : 2 * $opt_parallel) + 300; while (! $found_free) { - $build_thread= mtr_get_unique_id($build_thread, 349); + $build_thread= mtr_get_unique_id($build_thread, $build_thread_upper); if ( !defined $build_thread ) { mtr_error("Could not get a unique build thread id"); } diff --git a/mysql-test/r/contributors.result b/mysql-test/r/contributors.result index 1820c0a5f06..1e01ca81990 100644 --- a/mysql-test/r/contributors.result +++ b/mysql-test/r/contributors.result @@ -7,7 +7,7 @@ Visma http://visma.com Member of the MariaDB Foundation Nexedi http://www.nexedi.com Member of the MariaDB Foundation Acronis http://www.acronis.com Member of the MariaDB Foundation Verkkokauppa.com Finland Sponsor of the MariaDB Foundation -Webyog Bangalore Sponsor of the MariaDB Foundation +Virtuozzo https://virtuozzo.com/ Sponsor of the MariaDB Foundation Google USA Sponsoring encryption, parallel replication and GTID Facebook USA Sponsoring non-blocking API, LIMIT ROWS EXAMINED etc Ronald Bradford Brisbane, Australia EFF contribution for UC2006 Auction diff --git a/mysql-test/r/create_or_replace.result b/mysql-test/r/create_or_replace.result index ff8170b7309..3a894e9fcb1 100644 --- a/mysql-test/r/create_or_replace.result +++ b/mysql-test/r/create_or_replace.result @@ -264,11 +264,11 @@ create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock mysqltest2 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock mysqltest2 t2 +# MDL_INTENTION_EXCLUSIVE NULL Global read lock +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 create or replace table test.t1; ERROR 42000: A table must have at least 1 column show tables; @@ -276,10 +276,10 @@ Tables_in_test t2 select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock mysqltest2 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock mysqltest2 t2 +# MDL_INTENTION_EXCLUSIVE NULL Global read lock +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 create or replace table mysqltest2.t2; ERROR 42000: A table must have at least 1 column select * from information_schema.metadata_lock_info; @@ -291,11 +291,11 @@ create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock mysqltest2 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock mysqltest2 t2 +# MDL_INTENTION_EXCLUSIVE NULL Global read lock +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 create or replace table test.t1 (a int) select 1 as 'a', 2 as 'a'; ERROR 42S21: Duplicate column name 'a' show tables; @@ -303,10 +303,10 @@ Tables_in_test t2 select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock mysqltest2 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock mysqltest2 t2 +# MDL_INTENTION_EXCLUSIVE NULL Global read lock +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 create or replace table mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a'; ERROR 42S21: Duplicate column name 'a' select * from information_schema.metadata_lock_info; @@ -400,31 +400,31 @@ create table t1 (a int); lock table t1 write, t2 read; select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test -# MDL_SHARED_READ MDL_EXPLICIT Table metadata lock test t2 +# MDL_INTENTION_EXCLUSIVE NULL Global read lock +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 +# MDL_SHARED_READ NULL Table metadata lock test t2 create or replace table t1 (i int); select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test -# MDL_SHARED_READ MDL_EXPLICIT Table metadata lock test t2 +# MDL_INTENTION_EXCLUSIVE NULL Global read lock +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 +# MDL_SHARED_READ NULL Table metadata lock test t2 create or replace table t1 like t2; select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test -# MDL_SHARED_READ MDL_EXPLICIT Table metadata lock test t2 +# MDL_INTENTION_EXCLUSIVE NULL Global read lock +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 +# MDL_SHARED_READ NULL Table metadata lock test t2 create or replace table t1 select 1 as f1; select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Global read lock -# MDL_SHARED_NO_READ_WRITE MDL_EXPLICIT Table metadata lock test t1 -# MDL_INTENTION_EXCLUSIVE MDL_EXPLICIT Schema metadata lock test -# MDL_SHARED_READ MDL_EXPLICIT Table metadata lock test t2 +# MDL_INTENTION_EXCLUSIVE NULL Global read lock +# MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock test +# MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 +# MDL_SHARED_READ NULL Table metadata lock test t2 drop table t1; unlock tables; # diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result index c84da0d7496..2555749fa8c 100644 --- a/mysql-test/r/ctype_recoding.result +++ b/mysql-test/r/ctype_recoding.result @@ -277,9 +277,40 @@ CREATE TABLE t1 ( a VARCHAR(1) ); INSERT INTO t1 VALUES ('m'),('n'); CREATE VIEW v1 AS SELECT 'w' ; SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v1 ); -ERROR HY000: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' +a +m +n drop view v1; drop table t1; SET character_set_connection = default; SET optimizer_switch= default; #End of 5.3 tests +# +# Start of 5.5 tests +# +# +# MDEV-10181 Illegal mix of collation for a field and an ASCII string as a view field +# +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('A'),('a'),('B'),('b'); +CREATE VIEW v1 AS SELECT 'a'; +SELECT * FROM v1,t1 where t1.a=v1.a; +a a +a A +a a +DROP VIEW v1; +DROP TABLE t1; +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('a'),('b'),('c'); +CREATE VIEW v1 AS SELECT 'a' AS a UNION SELECT 'b'; +SELECT * FROM v1,t1 WHERE t1.a=v1.a; +a a +a a +b b +DROP VIEW v1; +DROP TABLE t1; +# +# End of 5.5 tests +# diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 4123b06b0a7..e5d6caf397b 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -6108,6 +6108,45 @@ OCTET_LENGTH(a) a 255 ＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡＡ DROP TABLE t1; # +# MDEV-8402 Bug#77473 Bug#21317406 TRUNCATED DATA WITH SUBQUERY & UTF8 +# +# +SET NAMES utf8; +SELECT length(rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2)) AS data; +data +131072 +SELECT length(data) AS len FROM ( +SELECT rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2) AS data +) AS sub; +len +131072 +SELECT length(rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2)) AS data; +data +131070 +SELECT length(data) AS len FROM ( +SELECT rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2) AS data +) AS sub; +len +131070 +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36766) AS data) AS sub; +len +73532 +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36767) AS data) AS sub; +len +73534 +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36778) AS data) AS sub; +len +73556 +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65535) AS data) AS sub; +len +131070 +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65536) AS data) AS sub; +len +131072 +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65537) AS data) AS sub; +len +131074 +# # End of 5.5 tests # # diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result index d8e97371294..38814bc36d6 100644 --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@ -2825,6 +2825,40 @@ OCTET_LENGTH(a) a 252 😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎😎 DROP TABLE t1; # +# MDEV-8402 Bug#77473 Bug#21317406 TRUNCATED DATA WITH SUBQUERY & UTF8 +# +# +SET NAMES utf8mb4; +SELECT length(repeat(_utf8mb4 0xE29883, 21844)) AS data; +data +65532 +SELECT length(data) AS len +FROM ( SELECT repeat(_utf8mb4 0xE29883, 21844) AS data ) AS sub; +len +65532 +SELECT length(repeat(_utf8mb4 0xE29883, 21846)) AS data; +data +65538 +SELECT length(data) AS len +FROM ( SELECT repeat(_utf8mb4 0xE29883, 21846) AS data ) AS sub; +len +65538 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 21844) AS data ) AS sub; +len +65532 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 21845) AS data ) AS sub; +len +65535 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 21846) AS data ) AS sub; +len +65538 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 65535) AS data ) AS sub; +len +196605 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 65536) AS data ) AS sub; +len +196608 +# # End of 5.5 tests # # diff --git a/mysql-test/r/events_1.result b/mysql-test/r/events_1.result index e03ccf51d8b..4e7ff526eca 100644 --- a/mysql-test/r/events_1.result +++ b/mysql-test/r/events_1.result @@ -469,6 +469,26 @@ DROP EVENT ev1; SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +# +# MDEV-9524 Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH +# +CREATE TABLE t1 (a INT); +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test ev1 root@localhost SYSTEM RECURRING NULL 5 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SET sql_mode=PAD_CHAR_TO_FULL_LENGTH; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test ev1 root@localhost SYSTEM RECURRING NULL 5 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT ev1; +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test ev1 root@localhost SYSTEM RECURRING NULL 5 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT ev1; +DROP TABLE t1; +SET sql_mode=DEFAULT; # # End of tests diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index fc56660ac62..210b0a9ef91 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -812,3 +812,22 @@ EXECUTE s; 1 DROP TABLE t1; # End of 5.3 tests +# +# Start of 10.0 tests +# +# +# MDEV-10020 InnoDB NOT IN Query Crash When One Item Is NULL +# +CREATE TABLE t1 +( +a INT(11), +b VARCHAR(10), +KEY (b) +); +INSERT INTO t1 VALUES (1,'x'),(2,'y'),(3,'z'); +SELECT * FROM t1 WHERE b NOT IN (NULL, '', 'A'); +a b +DROP TABLE t1; +# +# End of 10.0 tests +# diff --git a/mysql-test/r/help.result b/mysql-test/r/help.result index 16719cc8193..319a1ba3e85 100644 --- a/mysql-test/r/help.result +++ b/mysql-test/r/help.result @@ -148,6 +148,21 @@ help 'impossible_category_1'; source_category_name name is_it_category impossible_category_1 impossible_function_1 N impossible_category_1 impossible_function_2 N +# MDEV-9524 Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH +help 'impossible_function_1'; +name description example +impossible_function_1 description of + impossible_function1 + example of + impossible_function1 +SET sql_mode=PAD_CHAR_TO_FULL_LENGTH; +help 'impossible_function_1'; +name description example +impossible_function_1 description of + impossible_function1 + example of + impossible_function1 +SET sql_mode=DEFAULT; alter table mysql.help_relation engine=innodb; alter table mysql.help_keyword engine=innodb; alter table mysql.help_topic engine=innodb; diff --git a/mysql-test/r/information_schema_stats.result b/mysql-test/r/information_schema_stats.result new file mode 100644 index 00000000000..cd73636879c --- /dev/null +++ b/mysql-test/r/information_schema_stats.result @@ -0,0 +1,70 @@ +set global userstat=1; +create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30), state varchar(30)); +insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','California'), +(2,'fb','lb','china_b',22222222,'fb_lb@163.com','Arizona'), +(3,'fc','lc','china_c',33333333,'fc_lc@163.com','California'), +(4,'fd','ld','china_d',44444444,'fd_ld@163.com','Utah'), +(5,'fe','le','china_e',55555555,'fe_le@163.com','Arizona'); +alter table just_a_test add primary key (id); +alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name,last_name); +alter table just_a_test add key IND_just_a_test_state(state); +select count(*) from just_a_test where first_name='fc' and last_name='lc'; +count(*) +1 +select count(*) from just_a_test where state = 'California'; +count(*) +2 +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ +test just_a_test IND_just_a_test_state 2 +test just_a_test IND_just_a_test_first_name_last_name 1 +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES +test just_a_test 18 5 5 +alter table just_a_test drop key IND_just_a_test_first_name_last_name; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ +test just_a_test IND_just_a_test_state 2 +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES +test just_a_test 23 5 5 +alter table just_a_test drop column state; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES +test just_a_test 28 5 5 +drop table just_a_test; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES +create table just_a_test(id int not null primary key,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30), state varchar(30),key(first_name,last_name),key(state)); +insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','California'), +(2,'fb','lb','china_b',22222222,'fb_lb@163.com','Arizona'), +(3,'fc','lc','china_c',33333333,'fc_lc@163.com','California'), +(4,'fd','ld','china_d',44444444,'fd_ld@163.com','Utah'), +(5,'fe','le','china_e',55555555,'fe_le@163.com','Arizona'); +select count(*) from just_a_test where first_name='fc' and last_name='lc'; +count(*) +1 +select count(*) from just_a_test where state = 'California'; +count(*) +2 +select count(*) from just_a_test where id between 2 and 4; +count(*) +3 +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ +test just_a_test first_name 1 +test just_a_test state 2 +test just_a_test PRIMARY 5 +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES +test just_a_test 8 5 15 +drop table just_a_test; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES +set global userstat=0; diff --git a/mysql-test/r/insert_innodb.result b/mysql-test/r/insert_innodb.result index ffba9388ec4..e5e2b4b8623 100644 --- a/mysql-test/r/insert_innodb.result +++ b/mysql-test/r/insert_innodb.result @@ -28,3 +28,18 @@ ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (` UPDATE t1, t2 SET t1.fld1= t1.fld1 + 3; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) DROP TABLE t2, t1; +# +# BUG#22037930: INSERT IGNORE FAILS TO IGNORE FOREIGN +# KEY CONSTRAINT +CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE= INNODB; +CREATE TABLE t2 (fld1 VARCHAR(10), fld2 INT NOT NULL, +CONSTRAINT fk FOREIGN KEY (fld2) REFERENCES t1(fld1)) ENGINE= INNODB; +# Without patch, reports incorrect error. +INSERT INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def'; +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) +REPLACE INTO t2 VALUES('abc', 2); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) +INSERT IGNORE INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def'; +Warnings: +Warning 1452 Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) +DROP TABLE t2, t1; diff --git a/mysql-test/r/locale.result b/mysql-test/r/locale.result index c28cedae8b1..95c1f51fd87 100644 --- a/mysql-test/r/locale.result +++ b/mysql-test/r/locale.result @@ -102,6 +102,34 @@ SELECT monthname('2001-03-01'); monthname('2001-03-01') März # +# MDEV-10052 Illegal mix of collations with DAYNAME(date_field)<>varchar_field +# +SET NAMES utf8; +CREATE TABLE t1 (c VARCHAR(8) CHARACTER SET latin1, d DATE); +INSERT INTO t1 VALUES ('test',now()); +Warnings: +Note 1265 Data truncated for column 'd' at row 1 +SET lc_time_names=ru_RU; +SELECT c FROM t1 WHERE DAYNAME(d)<>c; +ERROR HY000: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<>' +SELECT c FROM t1 WHERE MONTHNAME(d)<>c; +ERROR HY000: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<>' +SET lc_time_names=en_US; +SELECT c FROM t1 WHERE DAYNAME(d)<>c; +c +test +SELECT c FROM t1 WHERE MONTHNAME(d)<>c; +c +test +SET NAMES latin1; +SELECT c FROM t1 WHERE DAYNAME(d)<>c; +c +test +SELECT c FROM t1 WHERE MONTHNAME(d)<>c; +c +test +DROP TABLE t1; +# # Start of 5.6 tests # # diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 688184f2f83..3b75cef2cf0 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -1039,4 +1039,36 @@ f1 f2 1 97 DROP TABLE t1, t2; DROP VIEW v1; +# +# MDEV-5973: MySQL Bug#11757486:49539: NON-DESCRIPTIVE ERR (ERROR 0 +# FROM STORAGE ENGINE) WITH MULTI-TABLE UPDATE +# +CREATE TABLE table_11757486 (field1 tinyint) ENGINE=INNODB; +INSERT INTO table_11757486 VALUES (0),(0); +SET SESSION SQL_MODE='STRICT_ALL_TABLES'; +UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; +Warnings: +Warning 1264 Out of range value for column 'field1' at row 1 +Warning 1264 Out of range value for column 'field1' at row 2 +UPDATE IGNORE table_11757486 SET field1=128; +Warnings: +Warning 1264 Out of range value for column 'field1' at row 1 +Warning 1264 Out of range value for column 'field1' at row 2 +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. UPDATE IGNORE is unsafe because the order in which rows are updated determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave. +UPDATE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; +ERROR 22003: Out of range value for column 'field1' at row 1 +UPDATE table_11757486 SET field1=128; +ERROR 22003: Out of range value for column 'field1' at row 1 +SET SESSION SQL_MODE=''; +UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; +Warnings: +Warning 1264 Out of range value for column 'field1' at row 1 +Warning 1264 Out of range value for column 'field1' at row 2 +UPDATE IGNORE table_11757486 SET field1=128; +Warnings: +Warning 1264 Out of range value for column 'field1' at row 1 +Warning 1264 Out of range value for column 'field1' at row 2 +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. UPDATE IGNORE is unsafe because the order in which rows are updated determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave. +DROP TABLE table_11757486; +SET SESSION SQL_MODE=default; end of 10.0 tests diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index 66e23c18355..b86f3a7e1aa 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -376,6 +376,11 @@ Repairing views test.v1 OK drop view v1; drop table t1; +create table `#mysql50#t1``1` (a int) engine=myisam; +show tables; +Tables_in_test +t1`1 +drop table `t1``1`; # #MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck # diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result index 5b0fbbcbe87..7e2593eb9c9 100644 --- a/mysql-test/r/parser.result +++ b/mysql-test/r/parser.result @@ -643,3 +643,23 @@ CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH ROW SET default_storage_engine = NEW.INNODB; ERROR 42S22: Unknown column 'INNODB' in 'NEW' DROP TABLE t1; +select 0==0; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=0' at line 1 +select 1=!0, 1 = ! 0; +1=!0 1 = ! 0 +1 1 +select !!0, ! ! 0; +!!0 ! ! 0 +0 0 +select 2>!0, 2 > ! 0; +2>!0 2 > ! 0 +1 1 +select 0<=!0, 0 <= !0; +0<=!0 0 <= !0 +1 1 +select 1<<!0, 1 << !0; +1<<!0 1 << !0 +2 2 +select 0<!0, 0 < ! 0; +0<!0 0 < ! 0 +1 1 diff --git a/mysql-test/r/ssl_ca.result b/mysql-test/r/ssl_ca.result new file mode 100644 index 00000000000..ffc5671f85f --- /dev/null +++ b/mysql-test/r/ssl_ca.result @@ -0,0 +1,24 @@ +# +# Bug#21920657: SSL-CA FAILS SILENTLY IF THE PATH CANNOT BE FOUND +# +# try to connect with wrong '--ssl-ca' path : should fail +ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed +# try to connect with correct '--ssl-ca' path : should connect +Variable_name Value +Ssl_cipher DHE-RSA-AES256-SHA +# +# Bug#21920678: SSL-CA DOES NOT ACCEPT ~USER TILDE HOME DIRECTORY +# PATH SUBSTITUTION +# +# try to connect with '--ssl-ca' option using tilde home directoy +# path substitution : should connect +Variable_name Value +Ssl_cipher DHE-RSA-AES256-SHA +# try to connect with '--ssl-key' option using tilde home directoy +# path substitution : should connect +Variable_name Value +Ssl_cipher DHE-RSA-AES256-SHA +# try to connect with '--ssl-cert' option using tilde home directoy +# path substitution : should connect +Variable_name Value +Ssl_cipher DHE-RSA-AES256-SHA diff --git a/mysql-test/r/status_user.result b/mysql-test/r/status_user.result index 829c8abb634..c6248a85d3a 100644 --- a/mysql-test/r/status_user.result +++ b/mysql-test/r/status_user.result @@ -128,16 +128,12 @@ handler_read_key set @@global.userstat=0; select * from information_schema.index_statistics; TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ -test t1 PRIMARY 2 select * from information_schema.table_statistics; TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES -test t1 6 13 13 show table_statistics; Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes -test t1 6 13 13 show index_statistics; Table_schema Table_name Index_name Rows_read -test t1 PRIMARY 2 select TOTAL_CONNECTIONS, CONCURRENT_CONNECTIONS, ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, SELECT_COMMANDS, UPDATE_COMMANDS, OTHER_COMMANDS, COMMIT_TRANSACTIONS, ROLLBACK_TRANSACTIONS, DENIED_CONNECTIONS, LOST_CONNECTIONS, ACCESS_DENIED, EMPTY_QUERIES from information_schema.client_statistics;; TOTAL_CONNECTIONS 1 CONCURRENT_CONNECTIONS 0 diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 95b82bdef82..7e5ac85a552 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -440,6 +440,7 @@ select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; drop table t1; # # MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null +# MDEV-9972 Least function retuns date in date time format # CREATE TABLE t1 ( id BIGINT NOT NULL, @@ -463,9 +464,9 @@ LEAST(IFNULL(t2.date_fin, IFNULL(t1.date_fin, NULL)), IFNULL(t1.date_fin, IFNULL(t2.date_fin, NULL))) AS date_fin FROM t1 LEFT JOIN t2 ON (t1.id=t2.id); id date_debut date_fin -1 2016-01-01 2016-01-31 00:00:00 -2 2016-02-01 2016-01-28 00:00:00 -3 2016-03-01 2016-03-31 00:00:00 +1 2016-01-01 2016-01-31 +2 2016-02-01 2016-01-28 +3 2016-03-01 2016-03-31 4 2016-04-01 NULL DROP TABLE t1,t2; SELECT diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 82b64d30d96..e033fe48607 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -834,5 +834,13 @@ a b a b DEALLOCATE PREPARE stmt1; DROP TABLE t1,t2; # +# MDEV-9374 having '2015-01-01 01:00:00.000001' > coalesce(NULL) returns true +# +CREATE TABLE t1 (c1 DATETIME(0)); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1 HAVING '2015-01-01 01:00:00.000001' > COALESCE(c1); +c1 +DROP TABLE t1; +# # End of 5.5 tests # diff --git a/mysql-test/suite/innodb/r/innodb-fkcheck.result b/mysql-test/suite/innodb/r/innodb-fkcheck.result new file mode 100644 index 00000000000..c6beabb0f50 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-fkcheck.result @@ -0,0 +1,89 @@ +set global innodb_file_per_table = 1; +drop table if exists b; +drop database if exists bug_fk; +create database bug_fk; +use bug_fk; +CREATE TABLE b ( +b int unsigned NOT NULL, +d1 datetime NOT NULL, +PRIMARY KEY (b,d1) +) ENGINE=InnoDB; +CREATE TABLE c ( +b int unsigned NOT NULL, +d1 datetime NOT NULL, +d2 datetime NOT NULL, +PRIMARY KEY (b,d1), +CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b) +) ENGINE=InnoDB; +show warnings; +Level Code Message +set foreign_key_checks = 0; +DROP TABLE IF EXISTS b; +show create table c; +Table Create Table +c CREATE TABLE `c` ( + `b` int(10) unsigned NOT NULL, + `d1` datetime NOT NULL, + `d2` datetime NOT NULL, + PRIMARY KEY (`b`,`d1`), + CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +CREATE TABLE b ( +b bigint unsigned NOT NULL, +d1 date NOT NULL, +PRIMARY KEY (b,d1) +) ENGINE=InnoDB; +ERROR HY000: Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed") +show warnings; +Level Code Message +Error 1005 Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed") +Warning 1215 Cannot add foreign key constraint +DROP TABLE IF EXISTS d; +Warnings: +Note 1051 Unknown table 'bug_fk.d' +CREATE TABLE d ( +b bigint unsigned NOT NULL, +d1 date NOT NULL, +PRIMARY KEY (b,d1), +CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b) +) ENGINE=InnoDB; +show warnings; +Level Code Message +set foreign_key_checks = 1; +show create table c; +Table Create Table +c CREATE TABLE `c` ( + `b` int(10) unsigned NOT NULL, + `d1` datetime NOT NULL, + `d2` datetime NOT NULL, + PRIMARY KEY (`b`,`d1`), + CONSTRAINT `b_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +show create table d; +Table Create Table +d CREATE TABLE `d` ( + `b` bigint(20) unsigned NOT NULL, + `d1` date NOT NULL, + PRIMARY KEY (`b`,`d1`), + CONSTRAINT `bd_fk` FOREIGN KEY (`b`) REFERENCES `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +CREATE TABLE b ( +b bigint unsigned NOT NULL, +d1 date NOT NULL, +PRIMARY KEY (b,d1) +) ENGINE=InnoDB; +ERROR HY000: Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed") +show warnings; +Level Code Message +Error 1005 Can't create table `bug_fk`.`b` (errno: 150 "Foreign key constraint is incorrectly formed") +Warning 1215 Cannot add foreign key constraint +set foreign_key_checks=0; +drop table c; +drop table d; +create table b(id int) engine=innodb; +show warnings; +Level Code Message +b.frm +b.ibd +drop table if exists b; +drop database if exists bug_fk; diff --git a/mysql-test/suite/innodb/r/innodb_corrupt_bit.result b/mysql-test/suite/innodb/r/innodb_corrupt_bit.result index 353303825e5..0ef6f65d0ff 100644 --- a/mysql-test/suite/innodb/r/innodb_corrupt_bit.result +++ b/mysql-test/suite/innodb/r/innodb_corrupt_bit.result @@ -1,82 +1,48 @@ -set names utf8; -CREATE TABLE corrupt_bit_test_ā( -a INT AUTO_INCREMENT PRIMARY KEY, -b CHAR(100), -c INT, -z INT, -INDEX(b)) -ENGINE=InnoDB; -INSERT INTO corrupt_bit_test_ā VALUES(0,'x',1, 1); -CREATE UNIQUE INDEX idxā ON corrupt_bit_test_ā(c, b); -CREATE UNIQUE INDEX idxē ON corrupt_bit_test_ā(z, b); -SELECT * FROM corrupt_bit_test_ā; a b c z 1 x 1 1 -select @@unique_checks; -@@unique_checks -0 -select @@innodb_change_buffering_debug; -@@innodb_change_buffering_debug -1 -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+1,z+1 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+10,z+10 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+20,z+20 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+50,z+50 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+100,z+100 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+200,z+200 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+400,z+400 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+800,z+800 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+1600,z+1600 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+4000,z+4000 FROM corrupt_bit_test_ā; -select count(*) from corrupt_bit_test_ā; count(*) -1024 -CREATE INDEX idx3 ON corrupt_bit_test_ā(b, c); -INSERT INTO corrupt_bit_test_ā VALUES(13000,'x',1,1); -CREATE INDEX idx4 ON corrupt_bit_test_ā(b, z); -check table corrupt_bit_test_ā; +2 Table Op Msg_type Msg_text -test.corrupt_bit_test_ā check Warning InnoDB: The B-tree of index "idxā" is corrupted. -test.corrupt_bit_test_ā check Warning InnoDB: The B-tree of index "idxē" is corrupted. +test.corrupt_bit_test_ā check Warning InnoDB: Index "idx" is marked as corrupted +test.corrupt_bit_test_ā check Warning InnoDB: Index "idxā" is marked as corrupted +test.corrupt_bit_test_ā check Warning InnoDB: Index "idxē" is marked as corrupted test.corrupt_bit_test_ā check error Corrupt -select c from corrupt_bit_test_ā; +ERROR HY000: Index "idx" is corrupted +ERROR HY000: Index "idx" is corrupted ERROR HY000: Index corrupt_bit_test_ā is corrupted -select z from corrupt_bit_test_ā; ERROR HY000: Index corrupt_bit_test_ā is corrupted -show warnings; Level Code Message Warning 180 InnoDB: Index "idxē" for table "test"."corrupt_bit_test_ā" is marked as corrupted Error 1712 Index corrupt_bit_test_ā is corrupted -insert into corrupt_bit_test_ā values (10001, "a", 20001, 20001); -select * from corrupt_bit_test_ā use index(primary) where a = 10001; a b c z 10001 a 20001 20001 -begin; -insert into corrupt_bit_test_ā values (10002, "a", 20002, 20002); -delete from corrupt_bit_test_ā where a = 10001; -insert into corrupt_bit_test_ā values (10001, "a", 20001, 20001); -rollback; -drop index idxā on corrupt_bit_test_ā; -check table corrupt_bit_test_ā; Table Op Msg_type Msg_text +test.corrupt_bit_test_ā check Warning InnoDB: Index "idx" is marked as corrupted test.corrupt_bit_test_ā check Warning InnoDB: Index "idxē" is marked as corrupted test.corrupt_bit_test_ā check error Corrupt -set names utf8; -select z from corrupt_bit_test_ā; ERROR HY000: Index corrupt_bit_test_ā is corrupted -drop index idxē on corrupt_bit_test_ā; -select z from corrupt_bit_test_ā limit 10; +Table Create Table +corrupt_bit_test_ā CREATE TABLE `corrupt_bit_test_ā` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `b` char(100) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + `z` int(11) DEFAULT NULL, + PRIMARY KEY (`a`), + UNIQUE KEY `idxē` (`z`,`b`), + KEY `idx` (`b`) +) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=latin1 +ERROR HY000: Index "idx" is corrupted +ERROR HY000: Index "idx" is corrupted +Table Create Table +corrupt_bit_test_ā CREATE TABLE `corrupt_bit_test_ā` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `b` char(100) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + `z` int(11) DEFAULT NULL, + PRIMARY KEY (`a`), + KEY `idx` (`b`) +) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=latin1 z 20001 1 -1 2 -11 -12 -21 -22 -31 -32 -drop table corrupt_bit_test_ā; -DROP DATABASE pad; -SET GLOBAL innodb_change_buffering_debug = 0; diff --git a/mysql-test/suite/innodb/t/innodb-fkcheck.test b/mysql-test/suite/innodb/t/innodb-fkcheck.test new file mode 100644 index 00000000000..51e36ae6984 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-fkcheck.test @@ -0,0 +1,115 @@ +--source include/have_innodb.inc + +# +# MDEV-10083: Orphan ibd file when playing with foreign keys +# +--disable_query_log +SET @start_global_fpt = @@global.innodb_file_per_table; +SET @start_global_fkc = @@global.foreign_key_checks; +--enable_query_log + +set global innodb_file_per_table = 1; + +--disable_warnings +drop table if exists b; +drop database if exists bug_fk; +--enable_warnings + +let $MYSQLD_DATADIR = `select @@datadir`; + +create database bug_fk; +use bug_fk; + +CREATE TABLE b ( + b int unsigned NOT NULL, + d1 datetime NOT NULL, + PRIMARY KEY (b,d1) +) ENGINE=InnoDB; + +CREATE TABLE c ( + b int unsigned NOT NULL, + d1 datetime NOT NULL, + d2 datetime NOT NULL, + PRIMARY KEY (b,d1), + CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b) +) ENGINE=InnoDB; + +show warnings; + +set foreign_key_checks = 0; + +DROP TABLE IF EXISTS b; + +show create table c; + +# +# Note that column b has different type in parent table +# +--error 1005 +CREATE TABLE b ( + b bigint unsigned NOT NULL, + d1 date NOT NULL, + PRIMARY KEY (b,d1) +) ENGINE=InnoDB; + +show warnings; + +DROP TABLE IF EXISTS d; + +CREATE TABLE d ( + b bigint unsigned NOT NULL, + d1 date NOT NULL, + PRIMARY KEY (b,d1), + CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b) +) ENGINE=InnoDB; + +show warnings; + +set foreign_key_checks = 1; + +show create table c; +show create table d; + +# +# Table c column b used on foreign key has different type +# compared referenced column b in table b, but this +# create still produced b.ibd file. This is because +# we row_drop_table_for_mysql was called and referenced +# table is not allowed to be dropped even in case +# when actual create is not successfull. +# +--error 1005 +CREATE TABLE b ( + b bigint unsigned NOT NULL, + d1 date NOT NULL, + PRIMARY KEY (b,d1) +) ENGINE=InnoDB; + +show warnings; + +--list_files $MYSQLD_DATADIR/bug_fk b* + +set foreign_key_checks=0; + +drop table c; +drop table d; + +--list_files $MYSQLD_DATADIR/bug_fk b* + +create table b(id int) engine=innodb; +show warnings; + +--list_files $MYSQLD_DATADIR/bug_fk b* + +# +# Cleanup +# +--disable_query_log +SET @@global.innodb_file_per_table = @start_global_fpt; +SET @@global.foreign_key_checks = @start_global_fkc; +--enable_query_log + +--disable_warnings +drop table if exists b; +drop database if exists bug_fk; +--enable_warnings diff --git a/mysql-test/suite/innodb/t/innodb_corrupt_bit.test b/mysql-test/suite/innodb/t/innodb_corrupt_bit.test index d6d77076b83..ee04e8d66fc 100644 --- a/mysql-test/suite/innodb/t/innodb_corrupt_bit.test +++ b/mysql-test/suite/innodb/t/innodb_corrupt_bit.test @@ -2,46 +2,24 @@ # Test for persistent corrupt bit for corrupted index and table # -- source include/have_innodb.inc --- source include/have_innodb_16k.inc - -# Issues with innodb_change_buffering_debug on Windows, so the test scenario -# cannot be created on windows ---source include/not_windows.inc - +#-- source include/have_innodb_16k.inc +-- source include/not_embedded.inc # This test needs debug server ---source include/have_debug.inc +-- source include/have_debug.inc -- disable_query_log -call mtr.add_suppression("Flagged corruption of idx.*in CHECK TABLE"); -# This test setup is extracted from bug56680.test: -# The flag innodb_change_buffering_debug is only available in debug builds. -# It instructs InnoDB to try to evict pages from the buffer pool when -# change buffering is possible, so that the change buffer will be used -# whenever possible. -SET @innodb_change_buffering_debug_orig = @@innodb_change_buffering_debug; -SET GLOBAL innodb_change_buffering_debug = 1; - -# Turn off Unique Check to create corrupted index with dup key -SET UNIQUE_CHECKS=0; - -CREATE DATABASE pad; -let $i=338; -while ($i) -{ ---eval CREATE TABLE pad.t$i(a INT PRIMARY KEY)ENGINE=InnoDB; - dec $i; -} - --- enable_query_log +call mtr.add_suppression("Flagged corruption of idx.*in"); set names utf8; +SET UNIQUE_CHECKS=0; + CREATE TABLE corrupt_bit_test_ā( a INT AUTO_INCREMENT PRIMARY KEY, b CHAR(100), c INT, z INT, - INDEX(b)) + INDEX idx(b)) ENGINE=InnoDB; INSERT INTO corrupt_bit_test_ā VALUES(0,'x',1, 1); @@ -54,38 +32,21 @@ CREATE UNIQUE INDEX idxē ON corrupt_bit_test_ā(z, b); SELECT * FROM corrupt_bit_test_ā; -select @@unique_checks; -select @@innodb_change_buffering_debug; - -# Create enough rows for the table, so that the insert buffer will be -# used for modifying the secondary index page. There must be multiple -# index pages, because changes to the root page are never buffered. - INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+1,z+1 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+10,z+10 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+20,z+20 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+50,z+50 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+100,z+100 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+200,z+200 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+400,z+400 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+800,z+800 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+1600,z+1600 FROM corrupt_bit_test_ā; -INSERT INTO corrupt_bit_test_ā SELECT 0,b,c+4000,z+4000 FROM corrupt_bit_test_ā; select count(*) from corrupt_bit_test_ā; -CREATE INDEX idx3 ON corrupt_bit_test_ā(b, c); - -# Create a dup key error on index "idxē" and "idxā" by inserting a dup value -INSERT INTO corrupt_bit_test_ā VALUES(13000,'x',1,1); +# This will flag all secondary indexes corrupted +SET SESSION debug_dbug="+d,dict_set_index_corrupted"; +check table corrupt_bit_test_ā; +SET SESSION debug_dbug=""; -# creating an index should succeed even if other secondary indexes are corrupted +# Cannot create new indexes while corrupted indexes exist +--error ER_INDEX_CORRUPT +CREATE INDEX idx3 ON corrupt_bit_test_ā(b, c); +--error ER_INDEX_CORRUPT CREATE INDEX idx4 ON corrupt_bit_test_ā(b, z); -# Check table will find the unique indexes corrupted -# with dup key -check table corrupt_bit_test_ā; - # This selection intend to use the corrupted index. Expect to fail -- error ER_INDEX_CORRUPT select c from corrupt_bit_test_ā; @@ -109,7 +70,6 @@ delete from corrupt_bit_test_ā where a = 10001; insert into corrupt_bit_test_ā values (10001, "a", 20001, 20001); rollback; -# Drop one corrupted index before reboot drop index idxā on corrupt_bit_test_ā; check table corrupt_bit_test_ā; @@ -119,14 +79,26 @@ set names utf8; -- error ER_INDEX_CORRUPT select z from corrupt_bit_test_ā; +show create table corrupt_bit_test_ā; + # Drop the corrupted index drop index idxē on corrupt_bit_test_ā; +# Cannot create new indexes while a corrupt index exists. +--error ER_INDEX_CORRUPT +CREATE INDEX idx3 ON corrupt_bit_test_ā(b, c); +--error ER_INDEX_CORRUPT +CREATE INDEX idx4 ON corrupt_bit_test_ā(b, z); + +show create table corrupt_bit_test_ā; +drop index idx on corrupt_bit_test_ā; + +# Now that there exist no corrupted indexes, we can create new indexes. +CREATE INDEX idx3 ON corrupt_bit_test_ā(b, c); +CREATE INDEX idx4 ON corrupt_bit_test_ā(b, z); + # Now select back to normal select z from corrupt_bit_test_ā limit 10; # Drop table drop table corrupt_bit_test_ā; -DROP DATABASE pad; - -SET GLOBAL innodb_change_buffering_debug = 0; diff --git a/mysql-test/suite/perfschema/r/digest_table_full.result b/mysql-test/suite/perfschema/r/digest_table_full.result index 64742d1dbe9..70e4213ffcc 100644 --- a/mysql-test/suite/perfschema/r/digest_table_full.result +++ b/mysql-test/suite/perfschema/r/digest_table_full.result @@ -113,7 +113,7 @@ SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARN SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest; SCHEMA_NAME DIGEST DIGEST_TEXT COUNT_STAR SUM_ROWS_AFFECTED SUM_WARNINGS SUM_ERRORS NULL NULL NULL 55 32 1 2 -statements_digest b12e7d0f2ac88c8fad9ac8dabb347b09 TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 0 0 0 +statements_digest 52e3729216b72a67a671ac3b93a1f1d3 TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 0 0 0 SHOW VARIABLES LIKE "performance_schema_digests_size"; Variable_name Value performance_schema_digests_size 2 diff --git a/mysql-test/suite/perfschema/r/rpl_gtid_func.result b/mysql-test/suite/perfschema/r/rpl_gtid_func.result index 389d21e179f..962e3e19e47 100644 --- a/mysql-test/suite/perfschema/r/rpl_gtid_func.result +++ b/mysql-test/suite/perfschema/r/rpl_gtid_func.result @@ -37,7 +37,7 @@ select digest, digest_text, count_star from performance_schema.events_statements_summary_by_digest where digest_text like "%in_%_digest%"; digest digest_text count_star -1281ed1e23aaa2e5528e90ebf98cfbdd SELECT ? AS `in_master_digest` 1 +d4c0df8aac0e1b575629aad4534b5c7b SELECT ? AS `in_master_digest` 1 insert into test.marker values (2); **** On Slave **** select * from test.marker; @@ -64,7 +64,7 @@ select digest, digest_text, count_star from performance_schema.events_statements_summary_by_digest where digest_text like "%in_%_digest%"; digest digest_text count_star -e0d84aed3bfac675887b38c4902f057f SELECT ? AS `in_slave_digest` 1 +aa6b10b84ad0f249ef4fcece8da6dd77 SELECT ? AS `in_slave_digest` 1 **** On Master **** delete from performance_schema.setup_objects where object_schema='master'; diff --git a/mysql-test/suite/perfschema/r/sizing_low.result b/mysql-test/suite/perfschema/r/sizing_low.result index dce5a994099..a698f55aa07 100644 --- a/mysql-test/suite/perfschema/r/sizing_low.result +++ b/mysql-test/suite/perfschema/r/sizing_low.result @@ -67,3 +67,4 @@ Performance_schema_table_instances_lost 0 Performance_schema_thread_classes_lost 0 Performance_schema_thread_instances_lost 0 Performance_schema_users_lost 0 +CALL mtr.add_suppression("innodb_open_files should not be greater than the open_files_limit."); diff --git a/mysql-test/suite/perfschema/r/start_server_low_digest.result b/mysql-test/suite/perfschema/r/start_server_low_digest.result index aff444250e0..eebebe5af88 100644 --- a/mysql-test/suite/perfschema/r/start_server_low_digest.result +++ b/mysql-test/suite/perfschema/r/start_server_low_digest.result @@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1 #################################### SELECT event_name, digest, digest_text, sql_text FROM events_statements_history_long; event_name digest digest_text sql_text -statement/sql/truncate e1c917a43f978456fab15240f89372ca TRUNCATE TABLE truncate table events_statements_history_long -statement/sql/select 4cc1c447d79877c4e8df0423fd0cde9a SELECT ? + ? + SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1 +statement/sql/truncate c4afa12dd9165da1a5fe8b74cf43005d TRUNCATE TABLE truncate table events_statements_history_long +statement/sql/select 719c3d02e550844d831d96809f405c39 SELECT ? + ? + SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1 diff --git a/mysql-test/suite/perfschema/r/statement_digest.result b/mysql-test/suite/perfschema/r/statement_digest.result index a48006c8ba3..13a82fb31ad 100644 --- a/mysql-test/suite/perfschema/r/statement_digest.result +++ b/mysql-test/suite/perfschema/r/statement_digest.result @@ -112,41 +112,41 @@ DROP TRIGGER trg; SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_WARNINGS, SUM_ERRORS FROM performance_schema.events_statements_summary_by_digest; SCHEMA_NAME DIGEST DIGEST_TEXT COUNT_STAR SUM_ROWS_AFFECTED SUM_WARNINGS SUM_ERRORS -statements_digest b12e7d0f2ac88c8fad9ac8dabb347b09 TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 0 0 0 -statements_digest 390f3ff3444c4de44fcbd052b11caf4e SELECT ? FROM `t1` 2 0 0 0 -statements_digest 8726524372f6e4924bbe1393b772498e SELECT ?, ... FROM `t1` 2 0 0 0 -statements_digest 5b06fb13fa9af5e8a4ec26bac8f994cd SELECT ? FROM `t2` 1 0 0 0 -statements_digest 82201946968b4baca616292f96e933a7 SELECT ?, ... FROM `t2` 2 0 0 0 -statements_digest 35d365779571257e8837f01b39dd9df5 INSERT INTO `t1` VALUES (?) 2 2 0 0 -statements_digest eaaf6c26e98130ec21cfae1389e3eb94 INSERT INTO `t2` VALUES (?) 1 1 0 0 -statements_digest 28f3cfdcfffeff3219bdd255ed15e6ac INSERT INTO `t3` VALUES (...) 4 4 0 0 -statements_digest 166a9591b81371a6ea389f27cfc1e5fd INSERT INTO `t4` VALUES (...) 1 1 0 0 -statements_digest fb25b9f9146120fb72c3c732e79dcc82 INSERT INTO `t5` VALUES (...) 1 1 0 0 -statements_digest 58bb7798d974224ff08742502eed1aae INSERT INTO `t1` VALUES (?) /* , ... */ 2 7 0 0 -statements_digest 3352b44dcaf21f59141ea76b5cace5c0 INSERT INTO `t3` VALUES (...) /* , ... */ 1 3 0 0 -statements_digest 1905c012e5d6a3a12e39b0b3ce13b22a INSERT INTO `t5` VALUES (...) /* , ... */ 1 3 0 0 -statements_digest 981d682c1e63b437c33230eb558d0f64 INSERT INTO `t6` VALUES (...) 5 5 0 0 -statements_digest aeb185ab9b6e9d5a49e47c8741b8acdf SELECT ? + ? 3 0 0 0 -statements_digest d3804664eeee11407f3fcbd5c29a1f73 SELECT ? 1 0 0 0 -statements_digest 6382c1dfc79755af2dd46113acea142b CREATE SCHEMA `statements_digest_temp` 2 2 0 0 -statements_digest 256f8dfc97d90a79103ebd6616b8d7aa DROP SCHEMA `statements_digest_temp` 2 0 0 0 -statements_digest 5315d33e7ef87b104b73912d484af6a3 SELECT ? FROM `no_such_table` 1 0 0 1 -statements_digest dcde8f720a419aa5d52246207268cf6c CREATE TABLE `dup_table` ( `c` CHARACTER (?) ) 2 0 0 1 -statements_digest b4b4abab3f030642444fb32c44f28058 DROP TABLE `dup_table` 1 0 0 0 -statements_digest a34ed519fdeb4fe4460038db92ea0c20 INSERT INTO `t11` VALUES (?) 1 1 1 0 -statements_digest d3eda26b379bd56340ce84fe395dfff7 SHOW WARNINGS 1 0 0 0 -statements_digest e6aa634cf5a630087fefe9868b018329 PREPARE `stmt` FROM ? 1 0 0 0 -statements_digest 4de34527c0dfef6ad8387d4359f78c78 EXECUTE `stmt` 2 0 0 0 -statements_digest 54592849b6cf7386568c88e7fb20f61e DEALLOCATE PREPARE `stmt` 1 0 0 0 -statements_digest ee90db91a06cedfbcccf80f951dc58cd CREATE PROCEDURE `p1` ( ) BEGIN SELECT * FROM `t12` ; END 1 0 0 0 -statements_digest f964655a0037d2f194030bd024eab748 CALL `p1` ( ) 2 0 0 0 -statements_digest 788d8223f67ba10d1b97fcaa42fec081 DROP PROCEDURE `p1` 1 0 0 0 -statements_digest 5146273ef7d98ee1954d23fd98a35d68 CREATE FUNCTION `func` ( `a` INTEGER , `b` INTEGER ) RETURNS INTEGER (?) RETURN `a` + `b` 1 0 0 0 -statements_digest 2c9b8e1e7f2ad3ca3c49abb2ea30e871 SELECT `func` (...) 2 0 0 0 -statements_digest f48e30910f8e7758b818c088916424cd DROP FUNCTION `func` 1 0 0 0 -statements_digest 53ccf1d241eeb749f1e1b7becc65006f CREATE TRIGGER `trg` BEFORE INSERT ON `t12` FOR EACH ROW SET @? := ? 1 0 0 0 -statements_digest a9722a0717a57275431575c7204d71c1 INSERT INTO `t12` VALUES (?) 2 2 0 0 -statements_digest 2d48809e6899f63ec304776466a63eef DROP TRIGGER `trg` 1 0 0 0 +statements_digest 52e3729216b72a67a671ac3b93a1f1d3 TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 0 0 0 +statements_digest a76073841b59a83de0fcdb6a0158b94a SELECT ? FROM `t1` 2 0 0 0 +statements_digest d91813c4d7a128822624a55b43bab7b2 SELECT ?, ... FROM `t1` 2 0 0 0 +statements_digest 8d1d0319e2ce41e1c41455a06b8905f8 SELECT ? FROM `t2` 1 0 0 0 +statements_digest 704f1e85525022d18028b3493bf61e65 SELECT ?, ... FROM `t2` 2 0 0 0 +statements_digest 7f60599ab03830f5571b306d71e47ba3 INSERT INTO `t1` VALUES (?) 2 2 0 0 +statements_digest 103d388f122df6a6a2c9f7fa01d90d7d INSERT INTO `t2` VALUES (?) 1 1 0 0 +statements_digest f1f56fda9303c1e2555bd67d431398ab INSERT INTO `t3` VALUES (...) 4 4 0 0 +statements_digest 08fc8813613c3cd44736a4abbb0cd095 INSERT INTO `t4` VALUES (...) 1 1 0 0 +statements_digest ab209b79451b94d03d8e20374ec18795 INSERT INTO `t5` VALUES (...) 1 1 0 0 +statements_digest 4729eb58cad3b77435bcd17864cfe322 INSERT INTO `t1` VALUES (?) /* , ... */ 2 7 0 0 +statements_digest 8e543c7785feeeb3e9a1957397a1033f INSERT INTO `t3` VALUES (...) /* , ... */ 1 3 0 0 +statements_digest 3dd587a1c42991bb323cbaa4c6fb61d0 INSERT INTO `t5` VALUES (...) /* , ... */ 1 3 0 0 +statements_digest 6f2f9f471f739d16b4ff4faf256e839e INSERT INTO `t6` VALUES (...) 5 5 0 0 +statements_digest 9701bfa1fb64563334f1a52953e065f3 SELECT ? + ? 3 0 0 0 +statements_digest b0785a540ffc1743c4e0879d193a4b10 SELECT ? 1 0 0 0 +statements_digest bee0eebfc340dbd233ee8c86270ac6ea CREATE SCHEMA `statements_digest_temp` 2 2 0 0 +statements_digest a35fd3ac67e64b9ac41a53781a7f5662 DROP SCHEMA `statements_digest_temp` 2 0 0 0 +statements_digest 52ec0213cba551f38d069c94a50cd2c7 SELECT ? FROM `no_such_table` 1 0 0 1 +statements_digest 27d4298be49de7a7606fcc8122ce7cd6 CREATE TABLE `dup_table` ( `c` CHARACTER (?) ) 2 0 0 1 +statements_digest 8a9b185842f12475c7ffa350ade45408 DROP TABLE `dup_table` 1 0 0 0 +statements_digest bda68c0a1eca7b625a5158da41ebbcf9 INSERT INTO `t11` VALUES (?) 1 1 1 0 +statements_digest 196c9f451360b5e24e03aa82f86006ae SHOW WARNINGS 1 0 0 0 +statements_digest 3413dd64a34c2148e669e3283ca41ff5 PREPARE `stmt` FROM ? 1 0 0 0 +statements_digest fcec9dcf45c26dabade2c7a4ab818543 EXECUTE `stmt` 2 0 0 0 +statements_digest 9e5e4f78f8226cc853fa1ce62ae61f9d DEALLOCATE PREPARE `stmt` 1 0 0 0 +statements_digest c92f30dceb52f470a6c36400bdb372c6 CREATE PROCEDURE `p1` ( ) BEGIN SELECT * FROM `t12` ; END 1 0 0 0 +statements_digest db338b4f4a13d74acda7a7b9dae2b0b4 CALL `p1` ( ) 2 0 0 0 +statements_digest c2c92e9e7ac73741622d1f264e08c384 DROP PROCEDURE `p1` 1 0 0 0 +statements_digest c99aad5579088b31cdd53be4bfbc2b6e CREATE FUNCTION `func` ( `a` INTEGER , `b` INTEGER ) RETURNS INTEGER (?) RETURN `a` + `b` 1 0 0 0 +statements_digest 1f4ce8758787f5aa5f51f1ee7f3b8119 SELECT `func` (...) 2 0 0 0 +statements_digest ab76a0821015fa000a1df9c684072e37 DROP FUNCTION `func` 1 0 0 0 +statements_digest ce5db6554a357045978a5572c84a7655 CREATE TRIGGER `trg` BEFORE INSERT ON `t12` FOR EACH ROW SET @? := ? 1 0 0 0 +statements_digest 801f02819c67e56fe3f22cc7dda99707 INSERT INTO `t12` VALUES (?) 2 2 0 0 +statements_digest dc3b07fe8e4d5fa91b383605f18512b0 DROP TRIGGER `trg` 1 0 0 0 #################################### # CLEANUP #################################### diff --git a/mysql-test/suite/perfschema/r/statement_digest_consumers.result b/mysql-test/suite/perfschema/r/statement_digest_consumers.result index 351bd41159c..a1c4fbe1d25 100644 --- a/mysql-test/suite/perfschema/r/statement_digest_consumers.result +++ b/mysql-test/suite/perfschema/r/statement_digest_consumers.result @@ -125,41 +125,41 @@ DROP TRIGGER trg; #################################### SELECT schema_name, digest, digest_text, count_star FROM performance_schema.events_statements_summary_by_digest; schema_name digest digest_text count_star -statements_digest b12e7d0f2ac88c8fad9ac8dabb347b09 TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 -statements_digest 390f3ff3444c4de44fcbd052b11caf4e SELECT ? FROM `t1` 2 -statements_digest 8726524372f6e4924bbe1393b772498e SELECT ?, ... FROM `t1` 2 -statements_digest 5b06fb13fa9af5e8a4ec26bac8f994cd SELECT ? FROM `t2` 1 -statements_digest 82201946968b4baca616292f96e933a7 SELECT ?, ... FROM `t2` 2 -statements_digest 35d365779571257e8837f01b39dd9df5 INSERT INTO `t1` VALUES (?) 2 -statements_digest eaaf6c26e98130ec21cfae1389e3eb94 INSERT INTO `t2` VALUES (?) 1 -statements_digest 28f3cfdcfffeff3219bdd255ed15e6ac INSERT INTO `t3` VALUES (...) 4 -statements_digest 166a9591b81371a6ea389f27cfc1e5fd INSERT INTO `t4` VALUES (...) 1 -statements_digest fb25b9f9146120fb72c3c732e79dcc82 INSERT INTO `t5` VALUES (...) 1 -statements_digest 58bb7798d974224ff08742502eed1aae INSERT INTO `t1` VALUES (?) /* , ... */ 2 -statements_digest 3352b44dcaf21f59141ea76b5cace5c0 INSERT INTO `t3` VALUES (...) /* , ... */ 1 -statements_digest 1905c012e5d6a3a12e39b0b3ce13b22a INSERT INTO `t5` VALUES (...) /* , ... */ 1 -statements_digest 981d682c1e63b437c33230eb558d0f64 INSERT INTO `t6` VALUES (...) 5 -statements_digest aeb185ab9b6e9d5a49e47c8741b8acdf SELECT ? + ? 3 -statements_digest d3804664eeee11407f3fcbd5c29a1f73 SELECT ? 1 -statements_digest 6382c1dfc79755af2dd46113acea142b CREATE SCHEMA `statements_digest_temp` 2 -statements_digest 256f8dfc97d90a79103ebd6616b8d7aa DROP SCHEMA `statements_digest_temp` 2 -statements_digest 5315d33e7ef87b104b73912d484af6a3 SELECT ? FROM `no_such_table` 1 -statements_digest dcde8f720a419aa5d52246207268cf6c CREATE TABLE `dup_table` ( `c` CHARACTER (?) ) 2 -statements_digest b4b4abab3f030642444fb32c44f28058 DROP TABLE `dup_table` 1 -statements_digest a34ed519fdeb4fe4460038db92ea0c20 INSERT INTO `t11` VALUES (?) 1 -statements_digest d3eda26b379bd56340ce84fe395dfff7 SHOW WARNINGS 1 -statements_digest e6aa634cf5a630087fefe9868b018329 PREPARE `stmt` FROM ? 1 -statements_digest 4de34527c0dfef6ad8387d4359f78c78 EXECUTE `stmt` 2 -statements_digest 54592849b6cf7386568c88e7fb20f61e DEALLOCATE PREPARE `stmt` 1 -statements_digest ee90db91a06cedfbcccf80f951dc58cd CREATE PROCEDURE `p1` ( ) BEGIN SELECT * FROM `t12` ; END 1 -statements_digest f964655a0037d2f194030bd024eab748 CALL `p1` ( ) 2 -statements_digest 788d8223f67ba10d1b97fcaa42fec081 DROP PROCEDURE `p1` 1 -statements_digest 5146273ef7d98ee1954d23fd98a35d68 CREATE FUNCTION `func` ( `a` INTEGER , `b` INTEGER ) RETURNS INTEGER (?) RETURN `a` + `b` 1 -statements_digest 2c9b8e1e7f2ad3ca3c49abb2ea30e871 SELECT `func` (...) 2 -statements_digest f48e30910f8e7758b818c088916424cd DROP FUNCTION `func` 1 -statements_digest 53ccf1d241eeb749f1e1b7becc65006f CREATE TRIGGER `trg` BEFORE INSERT ON `t12` FOR EACH ROW SET @? := ? 1 -statements_digest a9722a0717a57275431575c7204d71c1 INSERT INTO `t12` VALUES (?) 2 -statements_digest 2d48809e6899f63ec304776466a63eef DROP TRIGGER `trg` 1 +statements_digest 52e3729216b72a67a671ac3b93a1f1d3 TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 1 +statements_digest a76073841b59a83de0fcdb6a0158b94a SELECT ? FROM `t1` 2 +statements_digest d91813c4d7a128822624a55b43bab7b2 SELECT ?, ... FROM `t1` 2 +statements_digest 8d1d0319e2ce41e1c41455a06b8905f8 SELECT ? FROM `t2` 1 +statements_digest 704f1e85525022d18028b3493bf61e65 SELECT ?, ... FROM `t2` 2 +statements_digest 7f60599ab03830f5571b306d71e47ba3 INSERT INTO `t1` VALUES (?) 2 +statements_digest 103d388f122df6a6a2c9f7fa01d90d7d INSERT INTO `t2` VALUES (?) 1 +statements_digest f1f56fda9303c1e2555bd67d431398ab INSERT INTO `t3` VALUES (...) 4 +statements_digest 08fc8813613c3cd44736a4abbb0cd095 INSERT INTO `t4` VALUES (...) 1 +statements_digest ab209b79451b94d03d8e20374ec18795 INSERT INTO `t5` VALUES (...) 1 +statements_digest 4729eb58cad3b77435bcd17864cfe322 INSERT INTO `t1` VALUES (?) /* , ... */ 2 +statements_digest 8e543c7785feeeb3e9a1957397a1033f INSERT INTO `t3` VALUES (...) /* , ... */ 1 +statements_digest 3dd587a1c42991bb323cbaa4c6fb61d0 INSERT INTO `t5` VALUES (...) /* , ... */ 1 +statements_digest 6f2f9f471f739d16b4ff4faf256e839e INSERT INTO `t6` VALUES (...) 5 +statements_digest 9701bfa1fb64563334f1a52953e065f3 SELECT ? + ? 3 +statements_digest b0785a540ffc1743c4e0879d193a4b10 SELECT ? 1 +statements_digest bee0eebfc340dbd233ee8c86270ac6ea CREATE SCHEMA `statements_digest_temp` 2 +statements_digest a35fd3ac67e64b9ac41a53781a7f5662 DROP SCHEMA `statements_digest_temp` 2 +statements_digest 52ec0213cba551f38d069c94a50cd2c7 SELECT ? FROM `no_such_table` 1 +statements_digest 27d4298be49de7a7606fcc8122ce7cd6 CREATE TABLE `dup_table` ( `c` CHARACTER (?) ) 2 +statements_digest 8a9b185842f12475c7ffa350ade45408 DROP TABLE `dup_table` 1 +statements_digest bda68c0a1eca7b625a5158da41ebbcf9 INSERT INTO `t11` VALUES (?) 1 +statements_digest 196c9f451360b5e24e03aa82f86006ae SHOW WARNINGS 1 +statements_digest 3413dd64a34c2148e669e3283ca41ff5 PREPARE `stmt` FROM ? 1 +statements_digest fcec9dcf45c26dabade2c7a4ab818543 EXECUTE `stmt` 2 +statements_digest 9e5e4f78f8226cc853fa1ce62ae61f9d DEALLOCATE PREPARE `stmt` 1 +statements_digest c92f30dceb52f470a6c36400bdb372c6 CREATE PROCEDURE `p1` ( ) BEGIN SELECT * FROM `t12` ; END 1 +statements_digest db338b4f4a13d74acda7a7b9dae2b0b4 CALL `p1` ( ) 2 +statements_digest c2c92e9e7ac73741622d1f264e08c384 DROP PROCEDURE `p1` 1 +statements_digest c99aad5579088b31cdd53be4bfbc2b6e CREATE FUNCTION `func` ( `a` INTEGER , `b` INTEGER ) RETURNS INTEGER (?) RETURN `a` + `b` 1 +statements_digest 1f4ce8758787f5aa5f51f1ee7f3b8119 SELECT `func` (...) 2 +statements_digest ab76a0821015fa000a1df9c684072e37 DROP FUNCTION `func` 1 +statements_digest ce5db6554a357045978a5572c84a7655 CREATE TRIGGER `trg` BEFORE INSERT ON `t12` FOR EACH ROW SET @? := ? 1 +statements_digest 801f02819c67e56fe3f22cc7dda99707 INSERT INTO `t12` VALUES (?) 2 +statements_digest dc3b07fe8e4d5fa91b383605f18512b0 DROP TRIGGER `trg` 1 SELECT digest, digest_text FROM performance_schema.events_statements_current; digest digest_text #################################### diff --git a/mysql-test/suite/perfschema/r/statement_digest_long_query.result b/mysql-test/suite/perfschema/r/statement_digest_long_query.result index dd0244475cc..a723b8e9fa5 100644 --- a/mysql-test/suite/perfschema/r/statement_digest_long_query.result +++ b/mysql-test/suite/perfschema/r/statement_digest_long_query.result @@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1 #################################### SELECT schema_name, digest, digest_text, count_star FROM events_statements_summary_by_digest; schema_name digest digest_text count_star -performance_schema b6650e3f746acc31ef465aede8087e93 TRUNCATE TABLE `events_statements_summary_by_digest` 1 -performance_schema 63f9aaeed7859671c6a42c75fcd43785 SELECT ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? 1 +performance_schema 2f6bc98e6ca82311b17aac2f1e7cd85d TRUNCATE TABLE `events_statements_summary_by_digest` 1 +performance_schema 6c4b347800e3aa6bd3e41e3b97b3828a SELECT ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? 1 diff --git a/mysql-test/suite/perfschema/r/table_name.result b/mysql-test/suite/perfschema/r/table_name.result new file mode 100644 index 00000000000..d6369ffc79e --- /dev/null +++ b/mysql-test/suite/perfschema/r/table_name.result @@ -0,0 +1,156 @@ + +# +# TEST 1: Normal tables prefixed with "#sql" and "sql". +# +USE test; +CREATE TABLE `#sql_1` (a int, b text); +INSERT INTO `#sql_1` VALUES(1,'one'); + +CREATE TABLE `sql_1` (a int, b text); +INSERT INTO `sql_1` VALUES(1,'one'); + +# Verify that the tables are treated as normal tables . + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name +TABLE test #sql_1 +TABLE test sql_1 + +# Drop the tables, verify that the table objects are removed. + +DROP TABLE `#sql_1`; +DROP TABLE `sql_1`; + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# +# TEST 2: Temporary tables, no special prefix. +# +CREATE TEMPORARY TABLE sql_temp2_myisam (a int, b text) ENGINE=MYISAM; +INSERT INTO sql_temp2_myisam VALUES(1,'one'); + +CREATE TEMPORARY TABLE sql_temp2_innodb (a int, b text) ENGINE=INNODB; +INSERT INTO sql_temp2_innodb VALUES(1,'one'); + +# Confirm that the temporary tables are ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Drop the tables, verify that the table objects are not created. + +DROP TABLE sql_temp2_myisam; +DROP TABLE sql_temp2_innodb; + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# +# TEST 3: Temporary tables with the "#sql" prefix. +# +CREATE TEMPORARY TABLE `#sql_temp3_myisam` (a int, b text) ENGINE=MYISAM; +CHECK TABLE `#sql_temp3_myisam`; +Table Op Msg_type Msg_text +test.#sql_temp3_myisam check status OK +INSERT INTO `#sql_temp3_myisam` VALUES(1,'one'); + +CREATE TEMPORARY TABLE `#sql_temp3_innodb` (a int, b text) ENGINE=INNODB; +CHECK TABLE `#sql_temp3_innodb`; +Table Op Msg_type Msg_text +test.#sql_temp3_innodb check status OK +INSERT INTO `#sql_temp3_innodb` VALUES(1,'one'); + +# Confirm that the temporary tables are ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Drop the temporary tables. + +DROP TABLE `#sql_temp3_myisam`; +DROP TABLE `#sql_temp3_innodb`; + +# Confirm that the temporary tables are still ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# +# TEST 4: Special case: MyISAM temporary tables are recreated as non-temporary +# when they are truncated. +# +CREATE TEMPORARY TABLE `sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; +INSERT INTO `sql_temp4_myisam` VALUES(1,'one'); + +CREATE TEMPORARY TABLE `#sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; +INSERT INTO `#sql_temp4_myisam` VALUES(1,'one'); + +# Confirm that the MyISAM temporary tables are ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Truncate the MyISAM temporary tables, forcing them to be recreated as non-temporary. + +TRUNCATE TABLE `sql_temp4_myisam`; +TRUNCATE TABLE `#sql_temp4_myisam`; + +# Confirm that the recreated MyISAM tables are still regarded as temporary and ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Drop the recreated MyISAM tables; + +DROP TABLE `sql_temp4_myisam`; +DROP TABLE `#sql_temp4_myisam`; + +# Confirm that the recreated temporary tables are still ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# +# TEST 5: Generate temporary tables with ALTER MyISAM table. +# +USE test; +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1), (2), (3); +ALTER TABLE t1 ADD COLUMN (b int); + +# Confirm that the recreated temporary tables are still ignored. + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name + +# Drop the MyISAM table + +DROP TABLE t1; + +# Confirm that no tables remain; + +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +object_type object_schema object_name diff --git a/mysql-test/suite/perfschema/t/sizing_low.test b/mysql-test/suite/perfschema/t/sizing_low.test index 4d02d41aecd..56446fd6973 100644 --- a/mysql-test/suite/perfschema/t/sizing_low.test +++ b/mysql-test/suite/perfschema/t/sizing_low.test @@ -7,3 +7,4 @@ --source ../include/sizing_auto.inc +CALL mtr.add_suppression("innodb_open_files should not be greater than the open_files_limit."); diff --git a/mysql-test/suite/perfschema/t/table_name.test b/mysql-test/suite/perfschema/t/table_name.test new file mode 100644 index 00000000000..a8179f2d1f8 --- /dev/null +++ b/mysql-test/suite/perfschema/t/table_name.test @@ -0,0 +1,165 @@ +# +# Performance Schema +# +# Verify that the Performance Schema correctly identifies normal and temporary +# tables with non-standard names. + +# The server uses the table name prefix "#sql" for temporary and intermediate +# tables, however user-defined tables having the "#sql" prefix are also permitted. +# Independent of the table name, temporary or intermediate tables always have the +# "#sql" prefix in the filename. (For non-temporary tables starting with "#", +# the "#" is encoded to @0023 in the filename.) +# +# Given the ambiguity with temporary table names, the Performance Schema identifies +# temporary tables tables either by the table category or by the filename. +# +--source include/have_perfschema.inc +--source include/have_innodb.inc +--source include/not_embedded.inc + +--echo +--echo # +--echo # TEST 1: Normal tables prefixed with "#sql" and "sql". +--echo # +USE test; +CREATE TABLE `#sql_1` (a int, b text); +# INSERT forces path through get_table_share() +INSERT INTO `#sql_1` VALUES(1,'one'); +--echo +CREATE TABLE `sql_1` (a int, b text); +INSERT INTO `sql_1` VALUES(1,'one'); +--echo +--echo # Verify that the tables are treated as normal tables . +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the tables, verify that the table objects are removed. +--echo +DROP TABLE `#sql_1`; +DROP TABLE `sql_1`; +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; + +--echo +--echo # +--echo # TEST 2: Temporary tables, no special prefix. +--echo # +CREATE TEMPORARY TABLE sql_temp2_myisam (a int, b text) ENGINE=MYISAM; +INSERT INTO sql_temp2_myisam VALUES(1,'one'); +--echo +CREATE TEMPORARY TABLE sql_temp2_innodb (a int, b text) ENGINE=INNODB; +INSERT INTO sql_temp2_innodb VALUES(1,'one'); +--echo +--echo # Confirm that the temporary tables are ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the tables, verify that the table objects are not created. +--echo +DROP TABLE sql_temp2_myisam; +DROP TABLE sql_temp2_innodb; +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; + +--echo +--echo # +--echo # TEST 3: Temporary tables with the "#sql" prefix. +--echo # +CREATE TEMPORARY TABLE `#sql_temp3_myisam` (a int, b text) ENGINE=MYISAM; +CHECK TABLE `#sql_temp3_myisam`; +INSERT INTO `#sql_temp3_myisam` VALUES(1,'one'); +--echo +CREATE TEMPORARY TABLE `#sql_temp3_innodb` (a int, b text) ENGINE=INNODB; +CHECK TABLE `#sql_temp3_innodb`; +INSERT INTO `#sql_temp3_innodb` VALUES(1,'one'); +--echo +--echo # Confirm that the temporary tables are ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the temporary tables. +--echo +DROP TABLE `#sql_temp3_myisam`; +DROP TABLE `#sql_temp3_innodb`; +--echo +--echo # Confirm that the temporary tables are still ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; + +--echo +--echo # +--echo # TEST 4: Special case: MyISAM temporary tables are recreated as non-temporary +--echo # when they are truncated. +--echo # +CREATE TEMPORARY TABLE `sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; +INSERT INTO `sql_temp4_myisam` VALUES(1,'one'); +--echo +CREATE TEMPORARY TABLE `#sql_temp4_myisam` (a int, b text) ENGINE=MYISAM; +INSERT INTO `#sql_temp4_myisam` VALUES(1,'one'); +--echo +--echo # Confirm that the MyISAM temporary tables are ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Truncate the MyISAM temporary tables, forcing them to be recreated as non-temporary. +--echo +TRUNCATE TABLE `sql_temp4_myisam`; +TRUNCATE TABLE `#sql_temp4_myisam`; +--echo +--echo # Confirm that the recreated MyISAM tables are still regarded as temporary and ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the recreated MyISAM tables; +--echo +DROP TABLE `sql_temp4_myisam`; +DROP TABLE `#sql_temp4_myisam`; +--echo +--echo # Confirm that the recreated temporary tables are still ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; + +--echo +--echo # +--echo # TEST 5: Generate temporary tables with ALTER MyISAM table. +--echo # +USE test; +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1), (2), (3); +# Force a path throug mysql_alter_table() and ha_create_table(). +ALTER TABLE t1 ADD COLUMN (b int); +--echo +--echo # Confirm that the recreated temporary tables are still ignored. +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; +--echo +--echo # Drop the MyISAM table +--echo +DROP TABLE t1; + +--echo +--echo # Confirm that no tables remain; +--echo +SELECT object_type, object_schema, object_name +FROM performance_schema.objects_summary_global_by_type +WHERE object_schema="test"; diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result index 2577a36cad4..83b88ed0480 100644 --- a/mysql-test/suite/plugins/r/server_audit.result +++ b/mysql-test/suite/plugins/r/server_audit.result @@ -165,7 +165,7 @@ CREATE USER u1 IDENTIFIED BY 'pwd-123'; GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; SET PASSWORD FOR u1 = PASSWORD('pwd 098'); SET PASSWORD FOR u1=<secret>; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=<secret>' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<secret>' at line 1 CREATE USER u3 IDENTIFIED BY ''; drop user u1, u2, u3; select 2; diff --git a/mysql-test/suite/plugins/r/thread_pool_server_audit.result b/mysql-test/suite/plugins/r/thread_pool_server_audit.result index 2577a36cad4..83b88ed0480 100644 --- a/mysql-test/suite/plugins/r/thread_pool_server_audit.result +++ b/mysql-test/suite/plugins/r/thread_pool_server_audit.result @@ -165,7 +165,7 @@ CREATE USER u1 IDENTIFIED BY 'pwd-123'; GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; SET PASSWORD FOR u1 = PASSWORD('pwd 098'); SET PASSWORD FOR u1=<secret>; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=<secret>' at line 1 +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<secret>' at line 1 CREATE USER u3 IDENTIFIED BY ''; drop user u1, u2, u3; select 2; diff --git a/mysql-test/suite/roles/rpl_grant_revoke_current_role-8638.result b/mysql-test/suite/roles/rpl_grant_revoke_current_role-8638.result new file mode 100644 index 00000000000..67af4a068d6 --- /dev/null +++ b/mysql-test/suite/roles/rpl_grant_revoke_current_role-8638.result @@ -0,0 +1,21 @@ +include/master-slave.inc +[connection master] +create role r1; +set role r1; +grant select on db.* to current_role; +revoke all privileges, grant option from current_role; +drop role r1; +include/rpl_end.inc +connection server_2; +connection server_2; +connection server_2; +connection server_2; +connection server_1; +connection server_1; +connection server_1; +connection server_2; +connection server_1; +connection server_2; +connection server_2; +connection server_1; +connection server_1; diff --git a/mysql-test/suite/roles/rpl_grant_revoke_current_role-8638.test b/mysql-test/suite/roles/rpl_grant_revoke_current_role-8638.test new file mode 100644 index 00000000000..6a6c4f2a756 --- /dev/null +++ b/mysql-test/suite/roles/rpl_grant_revoke_current_role-8638.test @@ -0,0 +1,12 @@ +--source include/master-slave.inc +--source include/have_binlog_format_mixed.inc + +--enable_connect_log + +create role r1; +set role r1; +grant select on db.* to current_role; +revoke all privileges, grant option from current_role; +drop role r1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/roles/set_role-9614.result b/mysql-test/suite/roles/set_role-9614.result new file mode 100644 index 00000000000..37f6db070c0 --- /dev/null +++ b/mysql-test/suite/roles/set_role-9614.result @@ -0,0 +1,99 @@ +# +# MDEV-9614 Roles and Users Longer than 6 characters +# +# This test case checks the edge case presented in the MDEV. The +# real issue is actually apparent when the username is longer than the +# rolename. +# +# We need a separate database not including test or test_% names. Due to +# default privileges given on these databases. +# +DROP DATABASE IF EXISTS `bug_db`; +Warnings: +Note 1008 Can't drop database 'bug_db'; database doesn't exist +# +# The first user did not show the bug as john's length is smaller +# than client. The bug is apparent most of the time for usertestjohn. +# +CREATE USER `john`@`%`; +CREATE USER `usertestjohn`@`%`; +CREATE ROLE `client`; +# +# Setup the required tables. +# +CREATE DATABASE `bug_db`; +CREATE TABLE `bug_db`.`t0`(`c0` INT); +# +# Setup select privileges only on the role. Setting the role should give +# select access to bug_db.t0. +# +GRANT SELECT ON `bug_db`.`t0` TO `client`; +GRANT `client` TO `john`@`%`; +GRANT `client` TO `usertestjohn`@`%`; +# +# Check to see grants are set. +# +SHOW GRANTS FOR `john`@`%`; +Grants for john@% +GRANT client TO 'john'@'%' +GRANT USAGE ON *.* TO 'john'@'%' +SHOW GRANTS FOR `usertestjohn`@`%`; +Grants for usertestjohn@% +GRANT client TO 'usertestjohn'@'%' +GRANT USAGE ON *.* TO 'usertestjohn'@'%' +SHOW GRANTS FOR `client`; +Grants for client +GRANT USAGE ON *.* TO 'client' +GRANT SELECT ON `bug_db`.`t0` TO 'client' +show databases; +Database +bug_db +information_schema +mtr +mysql +performance_schema +test +# +# Try using the database as john. +# +connect john, localhost, john,,information_schema; +show databases; +Database +information_schema +test +set role client; +show databases; +Database +bug_db +information_schema +test +use bug_db; +# +# Try using the database as usertestjohn. +# +connect usertestjohn, localhost, usertestjohn,,information_schema; +show databases; +Database +information_schema +test +set role client; +show databases; +Database +bug_db +information_schema +test +show grants; +Grants for usertestjohn@% +GRANT client TO 'usertestjohn'@'%' +GRANT USAGE ON *.* TO 'usertestjohn'@'%' +GRANT USAGE ON *.* TO 'client' +GRANT SELECT ON `bug_db`.`t0` TO 'client' +use bug_db; +# +# Cleanup +# +connection default; +drop user john; +drop user usertestjohn; +drop role client; +drop database bug_db; diff --git a/mysql-test/suite/roles/set_role-9614.test b/mysql-test/suite/roles/set_role-9614.test new file mode 100644 index 00000000000..5e9f7dacf19 --- /dev/null +++ b/mysql-test/suite/roles/set_role-9614.test @@ -0,0 +1,79 @@ +--source include/not_embedded.inc + +--echo # +--echo # MDEV-9614 Roles and Users Longer than 6 characters +--echo # +--echo # This test case checks the edge case presented in the MDEV. The +--echo # real issue is actually apparent when the username is longer than the +--echo # rolename. + +--enable_connect_log +--echo # +--echo # We need a separate database not including test or test_% names. Due to +--echo # default privileges given on these databases. +--echo # +DROP DATABASE IF EXISTS `bug_db`; + +--echo # +--echo # The first user did not show the bug as john's length is smaller +--echo # than client. The bug is apparent most of the time for usertestjohn. +--echo # +CREATE USER `john`@`%`; +CREATE USER `usertestjohn`@`%`; +CREATE ROLE `client`; + +--echo # +--echo # Setup the required tables. +--echo # +CREATE DATABASE `bug_db`; +CREATE TABLE `bug_db`.`t0`(`c0` INT); + +--echo # +--echo # Setup select privileges only on the role. Setting the role should give +--echo # select access to bug_db.t0. +--echo # +GRANT SELECT ON `bug_db`.`t0` TO `client`; +GRANT `client` TO `john`@`%`; +GRANT `client` TO `usertestjohn`@`%`; + +--echo # +--echo # Check to see grants are set. +--echo # +SHOW GRANTS FOR `john`@`%`; +SHOW GRANTS FOR `usertestjohn`@`%`; +SHOW GRANTS FOR `client`; + +show databases; + +--echo # +--echo # Try using the database as john. +--echo # +connect (john, localhost, john,,information_schema); + +show databases; +set role client; +show databases; +use bug_db; + +--echo # +--echo # Try using the database as usertestjohn. +--echo # +connect (usertestjohn, localhost, usertestjohn,,information_schema); + +show databases; +set role client; +show databases; + +show grants; +use bug_db; + + +--echo # +--echo # Cleanup +--echo # +connection default; +drop user john; +drop user usertestjohn; +drop role client; +drop database bug_db; +--disable_connect_log diff --git a/mysql-test/suite/rpl/r/rpl_ignore_table.result b/mysql-test/suite/rpl/r/rpl_ignore_table.result index 8e7166fa278..6d7c4e3686c 100644 --- a/mysql-test/suite/rpl/r/rpl_ignore_table.result +++ b/mysql-test/suite/rpl/r/rpl_ignore_table.result @@ -142,4 +142,5 @@ HEX(word) SELECT * FROM tmptbl504451f4258$1; ERROR 42S02: Table 'test.tmptbl504451f4258$1' doesn't exist DROP TABLE t5; +flush privileges; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_ignore_table.test b/mysql-test/suite/rpl/t/rpl_ignore_table.test index b0019b70dc8..f4c2c32a805 100644 --- a/mysql-test/suite/rpl/t/rpl_ignore_table.test +++ b/mysql-test/suite/rpl/t/rpl_ignore_table.test @@ -1,6 +1,6 @@ -source include/master-slave.inc; let collation=utf8_unicode_ci; ---source include/have_collation.inc +source include/have_collation.inc; +source include/master-slave.inc; call mtr.add_suppression("Can't find record in 't.'"); call mtr.add_suppression("Can't find record in 'user'"); @@ -180,6 +180,7 @@ SELECT HEX(word) FROM t5; SELECT * FROM tmptbl504451f4258$1; connection master; DROP TABLE t5; +flush privileges; sync_slave_with_master; --source include/rpl_end.inc diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test index e2d21c0d990..f92b7c5b148 100644 --- a/mysql-test/t/bootstrap.test +++ b/mysql-test/t/bootstrap.test @@ -89,3 +89,13 @@ drop table t1; --replace_result .dll .so select * from mysql.plugin; truncate table mysql.plugin; + + +# +# MDEV-9969 mysql_install_db error processing ignore_db_dirs. +# +--write_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql +use test; +EOF +--exec $MYSQLD_BOOTSTRAP_CMD --ignore-db-dirs='some_dir' --ignore-db-dirs='some_dir' < $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 +--remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql diff --git a/mysql-test/t/create_or_replace.test b/mysql-test/t/create_or_replace.test index 9e37950dbef..7bba2b341c0 100644 --- a/mysql-test/t/create_or_replace.test +++ b/mysql-test/t/create_or_replace.test @@ -216,15 +216,18 @@ create table test.t1 (i int); create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; --error ER_TABLE_MUST_HAVE_COLUMNS create or replace table test.t1; show tables; --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; --error ER_TABLE_MUST_HAVE_COLUMNS create or replace table mysqltest2.t2; --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; create table t1 (i int); drop table t1; @@ -233,15 +236,18 @@ create table test.t1 (i int); create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; --error ER_DUP_FIELDNAME create or replace table test.t1 (a int) select 1 as 'a', 2 as 'a'; show tables; --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; --error ER_DUP_FIELDNAME create or replace table mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a'; --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; create table t1 (i int); drop table t1; @@ -319,15 +325,19 @@ drop view t1; create table t1 (a int); lock table t1 write, t2 read; --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; create or replace table t1 (i int); --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; create or replace table t1 like t2; --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; create or replace table t1 select 1 as f1; --replace_column 1 # +--sorted_result select * from information_schema.metadata_lock_info; drop table t1; unlock tables; diff --git a/mysql-test/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test index ee07ef24def..81c04fc9c30 100644 --- a/mysql-test/t/ctype_recoding.test +++ b/mysql-test/t/ctype_recoding.test @@ -220,7 +220,6 @@ SET character_set_connection = utf8; CREATE TABLE t1 ( a VARCHAR(1) ); INSERT INTO t1 VALUES ('m'),('n'); CREATE VIEW v1 AS SELECT 'w' ; ---error ER_CANT_AGGREGATE_2COLLATIONS SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v1 ); drop view v1; drop table t1; @@ -228,3 +227,30 @@ SET character_set_connection = default; SET optimizer_switch= default; --echo #End of 5.3 tests + +--echo # +--echo # Start of 5.5 tests +--echo # + +--echo # +--echo # MDEV-10181 Illegal mix of collation for a field and an ASCII string as a view field +--echo # +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('A'),('a'),('B'),('b'); +CREATE VIEW v1 AS SELECT 'a'; +SELECT * FROM v1,t1 where t1.a=v1.a; +DROP VIEW v1; +DROP TABLE t1; + +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('a'),('b'),('c'); +CREATE VIEW v1 AS SELECT 'a' AS a UNION SELECT 'b'; +SELECT * FROM v1,t1 WHERE t1.a=v1.a; +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # End of 5.5 tests +--echo # diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 2f34e47823b..7c0723ae82e 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1678,6 +1678,29 @@ SELECT OCTET_LENGTH(a),a FROM t1; DROP TABLE t1; --echo # +--echo # MDEV-8402 Bug#77473 Bug#21317406 TRUNCATED DATA WITH SUBQUERY & UTF8 +--echo # +--echo # + +SET NAMES utf8; +SELECT length(rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2)) AS data; +SELECT length(data) AS len FROM ( + SELECT rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2) AS data +) AS sub; + +SELECT length(rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2)) AS data; +SELECT length(data) AS len FROM ( + SELECT rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2) AS data +) AS sub; + +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36766) AS data) AS sub; +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36767) AS data) AS sub; +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36778) AS data) AS sub; +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65535) AS data) AS sub; +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65536) AS data) AS sub; +SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65537) AS data) AS sub; + +--echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/ctype_utf8mb4.test b/mysql-test/t/ctype_utf8mb4.test index d59c6d397fc..6b876cc5eba 100644 --- a/mysql-test/t/ctype_utf8mb4.test +++ b/mysql-test/t/ctype_utf8mb4.test @@ -1822,6 +1822,26 @@ DROP TABLE t1; --echo # +--echo # MDEV-8402 Bug#77473 Bug#21317406 TRUNCATED DATA WITH SUBQUERY & UTF8 +--echo # +--echo # + +SET NAMES utf8mb4; +SELECT length(repeat(_utf8mb4 0xE29883, 21844)) AS data; +SELECT length(data) AS len +FROM ( SELECT repeat(_utf8mb4 0xE29883, 21844) AS data ) AS sub; + +SELECT length(repeat(_utf8mb4 0xE29883, 21846)) AS data; +SELECT length(data) AS len +FROM ( SELECT repeat(_utf8mb4 0xE29883, 21846) AS data ) AS sub; + +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 21844) AS data ) AS sub; +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 21845) AS data ) AS sub; +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 21846) AS data ) AS sub; +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 65535) AS data ) AS sub; +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('☃', 65536) AS data ) AS sub; + +--echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/events_1.test b/mysql-test/t/events_1.test index bf5a356cee3..250b0d004dc 100644 --- a/mysql-test/t/events_1.test +++ b/mysql-test/t/events_1.test @@ -459,6 +459,25 @@ DROP EVENT ev1; SHOW EVENTS; +--echo # +--echo # MDEV-9524 Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH +--echo # +CREATE TABLE t1 (a INT); +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +--replace_column 8 # 9 # +SHOW EVENTS; +SET sql_mode=PAD_CHAR_TO_FULL_LENGTH; +--replace_column 8 # 9 # +SHOW EVENTS; +DROP EVENT ev1; +CREATE EVENT ev1 ON SCHEDULE EVERY 5 SECOND DO DELETE FROM t1; +--replace_column 8 # 9 # +SHOW EVENTS; +DROP EVENT ev1; +DROP TABLE t1; +SET sql_mode=DEFAULT; + + --echo --echo # --echo # End of tests diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 1e695142d90..17736ac40c6 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -606,3 +606,24 @@ EXECUTE s; DROP TABLE t1; --echo # End of 5.3 tests + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-10020 InnoDB NOT IN Query Crash When One Item Is NULL +--echo # +CREATE TABLE t1 +( + a INT(11), + b VARCHAR(10), + KEY (b) +); +INSERT INTO t1 VALUES (1,'x'),(2,'y'),(3,'z'); +SELECT * FROM t1 WHERE b NOT IN (NULL, '', 'A'); +DROP TABLE t1; + +--echo # +--echo # End of 10.0 tests +--echo # diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test index 71821e46771..881299a216c 100644 --- a/mysql-test/t/help.test +++ b/mysql-test/t/help.test @@ -61,6 +61,12 @@ help '%function_7'; help '%category_2'; help 'impossible_function_1'; help 'impossible_category_1'; + +--echo # MDEV-9524 Cannot load from mysql.event when sql_mode is set to PAD_CHAR_TO_FULL_LENGTH +help 'impossible_function_1'; +SET sql_mode=PAD_CHAR_TO_FULL_LENGTH; +help 'impossible_function_1'; +SET sql_mode=DEFAULT; ############## --disable_warnings diff --git a/mysql-test/t/information_schema_stats.test b/mysql-test/t/information_schema_stats.test new file mode 100644 index 00000000000..38248063d68 --- /dev/null +++ b/mysql-test/t/information_schema_stats.test @@ -0,0 +1,44 @@ +# +# MDEV-8633: information_schema.index_statistics doesn't delete item when drop table indexes or drop table; +# +set global userstat=1; +create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30), state varchar(30)); +insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','California'), +(2,'fb','lb','china_b',22222222,'fb_lb@163.com','Arizona'), +(3,'fc','lc','china_c',33333333,'fc_lc@163.com','California'), +(4,'fd','ld','china_d',44444444,'fd_ld@163.com','Utah'), +(5,'fe','le','china_e',55555555,'fe_le@163.com','Arizona'); +alter table just_a_test add primary key (id); +alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name,last_name); +alter table just_a_test add key IND_just_a_test_state(state); +select count(*) from just_a_test where first_name='fc' and last_name='lc'; +select count(*) from just_a_test where state = 'California'; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +alter table just_a_test drop key IND_just_a_test_first_name_last_name; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +alter table just_a_test drop column state; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +drop table just_a_test; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +# +# Test direct drop table +# +create table just_a_test(id int not null primary key,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30), state varchar(30),key(first_name,last_name),key(state)); +insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','California'), +(2,'fb','lb','china_b',22222222,'fb_lb@163.com','Arizona'), +(3,'fc','lc','china_c',33333333,'fc_lc@163.com','California'), +(4,'fd','ld','china_d',44444444,'fd_ld@163.com','Utah'), +(5,'fe','le','china_e',55555555,'fe_le@163.com','Arizona'); +select count(*) from just_a_test where first_name='fc' and last_name='lc'; +select count(*) from just_a_test where state = 'California'; +select count(*) from just_a_test where id between 2 and 4; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +drop table just_a_test; +select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; +set global userstat=0; diff --git a/mysql-test/t/insert_innodb.test b/mysql-test/t/insert_innodb.test index 8c8d2690c11..68b6ca4e315 100644 --- a/mysql-test/t/insert_innodb.test +++ b/mysql-test/t/insert_innodb.test @@ -41,3 +41,24 @@ UPDATE t1, t2 SET t2.fld2= t2.fld2 + 3; UPDATE t1, t2 SET t1.fld1= t1.fld1 + 3; DROP TABLE t2, t1; + +--echo # +--echo # BUG#22037930: INSERT IGNORE FAILS TO IGNORE FOREIGN +--echo # KEY CONSTRAINT + +CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE= INNODB; + +CREATE TABLE t2 (fld1 VARCHAR(10), fld2 INT NOT NULL, +CONSTRAINT fk FOREIGN KEY (fld2) REFERENCES t1(fld1)) ENGINE= INNODB; + +--echo # Without patch, reports incorrect error. +--error ER_NO_REFERENCED_ROW_2 +INSERT INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def'; +--error ER_NO_REFERENCED_ROW_2 +REPLACE INTO t2 VALUES('abc', 2); + +--enable_warnings +INSERT IGNORE INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def'; +--disable_warnings + +DROP TABLE t2, t1; diff --git a/mysql-test/t/locale.test b/mysql-test/t/locale.test index 5d1fd24d750..b1fbc40f0c8 100644 --- a/mysql-test/t/locale.test +++ b/mysql-test/t/locale.test @@ -65,6 +65,25 @@ SELECT monthname('2001-02-01'); SELECT monthname('2001-03-01'); --echo # +--echo # MDEV-10052 Illegal mix of collations with DAYNAME(date_field)<>varchar_field +--echo # +SET NAMES utf8; +CREATE TABLE t1 (c VARCHAR(8) CHARACTER SET latin1, d DATE); +INSERT INTO t1 VALUES ('test',now()); +SET lc_time_names=ru_RU; +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT c FROM t1 WHERE DAYNAME(d)<>c; +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT c FROM t1 WHERE MONTHNAME(d)<>c; +SET lc_time_names=en_US; +SELECT c FROM t1 WHERE DAYNAME(d)<>c; +SELECT c FROM t1 WHERE MONTHNAME(d)<>c; +SET NAMES latin1; +SELECT c FROM t1 WHERE DAYNAME(d)<>c; +SELECT c FROM t1 WHERE MONTHNAME(d)<>c; +DROP TABLE t1; + +--echo # --echo # Start of 5.6 tests --echo # diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index c013938416e..04611fef7dc 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -1082,4 +1082,28 @@ SELECT * FROM v1; DROP TABLE t1, t2; DROP VIEW v1; +--echo # +--echo # MDEV-5973: MySQL Bug#11757486:49539: NON-DESCRIPTIVE ERR (ERROR 0 +--echo # FROM STORAGE ENGINE) WITH MULTI-TABLE UPDATE +--echo # + +CREATE TABLE table_11757486 (field1 tinyint) ENGINE=INNODB; +INSERT INTO table_11757486 VALUES (0),(0); +SET SESSION SQL_MODE='STRICT_ALL_TABLES'; +UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; +UPDATE IGNORE table_11757486 SET field1=128; + +--error ER_WARN_DATA_OUT_OF_RANGE +UPDATE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; +--error ER_WARN_DATA_OUT_OF_RANGE +UPDATE table_11757486 SET field1=128; + +SET SESSION SQL_MODE=''; +UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; +UPDATE IGNORE table_11757486 SET field1=128; + +DROP TABLE table_11757486; + +SET SESSION SQL_MODE=default; + --echo end of 10.0 tests diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index 88f0d034bf0..e479a2232f0 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -350,6 +350,11 @@ create table t1(a int); drop view v1; drop table t1; +create table `#mysql50#t1``1` (a int) engine=myisam; +--exec $MYSQL_CHECK --fix-table-names --databases test +show tables; +drop table `t1``1`; + --echo # --echo #MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck --echo # diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test index f59a5d01cf5..496a6829577 100644 --- a/mysql-test/t/parser.test +++ b/mysql-test/t/parser.test @@ -758,3 +758,15 @@ CREATE TABLE t1 (s VARCHAR(100)); CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH ROW SET default_storage_engine = NEW.INNODB; DROP TABLE t1; + +# +# MDEV-8328 Evaluation of two "!" operators depends on space in beetween +# +--error ER_PARSE_ERROR +select 0==0; +select 1=!0, 1 = ! 0; +select !!0, ! ! 0; +select 2>!0, 2 > ! 0; +select 0<=!0, 0 <= !0; +select 1<<!0, 1 << !0; +select 0<!0, 0 < ! 0; diff --git a/mysql-test/t/ssl_ca.test b/mysql-test/t/ssl_ca.test new file mode 100644 index 00000000000..8e81f44e61c --- /dev/null +++ b/mysql-test/t/ssl_ca.test @@ -0,0 +1,36 @@ +--source include/have_ssl_communication.inc +--source include/not_embedded.inc + +--echo # +--echo # Bug#21920657: SSL-CA FAILS SILENTLY IF THE PATH CANNOT BE FOUND +--echo # + +--echo # try to connect with wrong '--ssl-ca' path : should fail +--error 1 +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/wrong-cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" 2>&1 + +--echo # try to connect with correct '--ssl-ca' path : should connect +--replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" + +--echo # +--echo # Bug#21920678: SSL-CA DOES NOT ACCEPT ~USER TILDE HOME DIRECTORY +--echo # PATH SUBSTITUTION +--echo # + +--let $mysql_test_dir_path= `SELECT IF(LENGTH('$HOME'), REPLACE('=$MYSQL_TEST_DIR', '=$HOME', '=~'), '=$MYSQL_TEST_DIR')` + +--echo # try to connect with '--ssl-ca' option using tilde home directoy +--echo # path substitution : should connect +--replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA +--exec $MYSQL --ssl-ca$mysql_test_dir_path/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" + +--echo # try to connect with '--ssl-key' option using tilde home directoy +--echo # path substitution : should connect +--replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key$mysql_test_dir_path/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" + +--echo # try to connect with '--ssl-cert' option using tilde home directoy +--echo # path substitution : should connect +--replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert$mysql_test_dir_path/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 696cfe67586..e7e53fb4c37 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -387,6 +387,7 @@ drop table t1; --echo # --echo # MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null +--echo # MDEV-9972 Least function retuns date in date time format --echo # CREATE TABLE t1 ( id BIGINT NOT NULL, diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index e44b190def0..3f96673c43f 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -611,5 +611,13 @@ DEALLOCATE PREPARE stmt1; DROP TABLE t1,t2; --echo # +--echo # MDEV-9374 having '2015-01-01 01:00:00.000001' > coalesce(NULL) returns true +--echo # +CREATE TABLE t1 (c1 DATETIME(0)); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1 HAVING '2015-01-01 01:00:00.000001' > COALESCE(c1); +DROP TABLE t1; + +--echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index 25927cd0e35..fc3a2d08213 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -19,36 +19,6 @@ # Suppress some common (not fatal) errors in system libraries found by valgrind # -# -# Pthread doesn't free all thread specific memory before program exists -# -{ - pthread allocate_tls memory loss - Memcheck:Leak - fun:calloc - fun:_dl_allocate_tls - fun:allocate_stack - fun:pthread_create* -} - -{ - pthread allocate_tls memory loss - Memcheck:Leak - fun:calloc - fun:_dl_allocate_tls - fun:pthread_create* - -} - -{ - pthread allocate_tls memory loss - Memcheck:Leak - fun:calloc - obj:/lib*/ld*.so - fun:_dl_allocate_tls - fun:pthread_create* -} - { pthead_exit memory loss 1 Memcheck:Leak @@ -90,43 +60,6 @@ } { - pthread allocate_dtv memory loss - Memcheck:Leak - fun:calloc - fun:allocate_dtv - fun:_dl_allocate_tls_storage - fun:__GI__dl_allocate_tls - fun:pthread_create -} - -{ - pthread allocate_dtv memory loss second - Memcheck:Leak - fun:calloc - fun:allocate_dtv - fun:_dl_allocate_tls - fun:pthread_create* -} - -{ - pthread memalign memory loss - Memcheck:Leak - fun:memalign - fun:_dl_allocate_tls_storage - fun:__GI__dl_allocate_tls - fun:pthread_create -} - -{ - pthread memalign memory loss2 - Memcheck:Leak - fun:memalign - fun:tls_get_addr_tail - ... - fun:*ha_initialize_handlerton* -} - -{ pthread pthread_key_create Memcheck:Leak fun:malloc @@ -1012,18 +945,6 @@ fun:nptl_pthread_exit_hack_handler } -# -# Pthread doesn't free all thread specific memory before program exists -# -{ - pthread allocate_tls memory loss in 2.6.1. - Memcheck:Leak - fun:calloc - obj:*/ld-*.so - fun:_dl_allocate_tls - fun:pthread_create* -} - { memory "leak" in backtrace() of glibc 2.9 (not present in 2.13) Memcheck:Leak @@ -1176,6 +1097,23 @@ fun:gethostbyaddr_r } +# +# Detached threads may not complete deiniitialization by the time shutdown +# thread calls exit. This is unfortunate property of detached threads, which +# we currently can only ignore. Unfortunately there is no way to distinguish +# between false positives generated by detached threads and real memory leaks +# generated by not joined joinable threads. So we hide both cases. +# +# To avoid enumeration of the whole variety of possible traces we ignore all +# "possibly lost" blocks allocated by pthread_create (and it's callees). +# +{ + Detached threads memory loss + Memcheck:Leak + match-leak-kinds:possible + ... + fun:pthread_create* +} { ConnectSE: unixODBC SQLAllocEnv leaves some "still reachable" pointers |