diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/loaddata.result | 50 | ||||
| -rw-r--r-- | mysql-test/r/log_slow.result | 37 | ||||
| -rw-r--r-- | mysql-test/r/sp.result | 33 | ||||
| -rw-r--r-- | mysql-test/suite/encryption/r/innodb-checksum-algorithm,32k.rdiff | 18 | ||||
| -rw-r--r-- | mysql-test/suite/encryption/r/innodb-checksum-algorithm,64k.rdiff | 18 | ||||
| -rw-r--r-- | mysql-test/suite/encryption/r/innodb-checksum-algorithm.result | 18 | ||||
| -rw-r--r-- | mysql-test/suite/encryption/r/innodb-compressed-blob.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/encryption/r/innodb-encryption-alter.result | 33 | ||||
| -rw-r--r-- | mysql-test/suite/encryption/t/innodb-encryption-alter.test | 13 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-alter-nullable.result | 36 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-alter-nullable.test | 33 | ||||
| -rw-r--r-- | mysql-test/t/loaddata.test | 45 | ||||
| -rw-r--r-- | mysql-test/t/log_slow.test | 40 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 41 |
14 files changed, 363 insertions, 54 deletions
diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 4c5cee0aa25..f09e6d70258 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -581,6 +581,56 @@ HEX(a) C3A4 DROP TABLE t1; # +# MDEV-15744: Assertion `derived->table' failed in mysql_derived_merge_for_insert +# +create table t1 (a int, b int); +CREATE OR REPLACE VIEW t2 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT * FROM t2; +LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2 +FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; +select * from v2; +a b +2 2 +3 3 +4 4 +5 5 +6 6 +select * from t2; +a b +2 2 +3 3 +4 4 +5 5 +6 6 +DROP VIEW IF EXISTS v2,t2; +DROP TABLE IF EXISTS t1; +# +# MDEV-15950: LOAD DATA INTO compex_view crashed +# +create table t1 (a int, b int); +create table t0 (x int, y int); +CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1,t0; +CREATE VIEW v2 AS SELECT * FROM v1; +LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v1 +FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; +ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD +LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2 +FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; +ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD +DROP VIEW IF EXISTS v2,v1; +DROP TABLE IF EXISTS t1,t0; +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE(b)); +INSERT INTO t1 VALUES (1,1); +CREATE TABLE t2 (c INT); +CREATE VIEW v AS SELECT t1.* FROM t1 JOIN t2; +SELECT a, b FROM t1 INTO OUTFILE '15645.data'; +LOAD DATA INFILE '15645.data' IGNORE INTO TABLE v (a,b); +ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD +LOAD DATA INFILE '15645.data' REPLACE INTO TABLE v (a,b); +ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD +drop table t1,t2; +drop view v; +# # MDEV-15497 Wrong empty value in a GEOMETRY column on LOAD DATA # SET sql_mode=''; diff --git a/mysql-test/r/log_slow.result b/mysql-test/r/log_slow.result index 510bb929d84..485b1fd9826 100644 --- a/mysql-test/r/log_slow.result +++ b/mysql-test/r/log_slow.result @@ -76,3 +76,40 @@ set @@log_slow_filter=default; set @@log_slow_verbosity=default; set global log_output= default; truncate mysql.slow_log; +# +# MDEV-18333 Slow_queries count doesn't increase when slow_query_log is turned off +# +SET SESSION slow_query_log=OFF; +SET GLOBAL slow_query_log=OFF; +SET long_query_time=0.1; +# Although this query is disallowed by slow_query_log, it should still increment Slow_queries +SELECT VARIABLE_VALUE INTO @global_slow_queries +FROM INFORMATION_SCHEMA.GLOBAL_STATUS +WHERE VARIABLE_NAME='SLOW_QUERIES'; +SELECT sleep(0.2) INTO @tmp FROM DUAL; +SELECT +CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment +FROM +INFORMATION_SCHEMA.GLOBAL_STATUS +WHERE +VARIABLE_NAME='SLOW_QUERIES'; +Slow_queries_increment +1 +# Although this query is disallowed by log_slow_filter, it should still increment Slow_queries +SET log_slow_filter=filesort; +SELECT sleep(0.2) INTO @tmp FROM DUAL; +SELECT VARIABLE_VALUE INTO @global_slow_queries +FROM INFORMATION_SCHEMA.GLOBAL_STATUS +WHERE VARIABLE_NAME='SLOW_QUERIES'; +SELECT sleep(0.2) INTO @tmp FROM DUAL; +SELECT +CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment +FROM +INFORMATION_SCHEMA.GLOBAL_STATUS +WHERE +VARIABLE_NAME='SLOW_QUERIES'; +Slow_queries_increment +1 +SET log_slow_filter=DEFAULT; +SET @@long_query_time=default; +SET GLOBAL slow_query_log= @org_slow_query_log; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index fd173b479fb..422a76921f3 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7926,6 +7926,39 @@ CALL sp; c a b a b DROP PROCEDURE sp; DROP TABLE t1; +# +# MDEV-17055: Server crashes in find_order_in_list upon +# 2nd (3rd) execution of SP with UPDATE +# +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TABLE t2 (c INT); +CREATE PROCEDURE sp() UPDATE v1 SET a = 1 ORDER BY a, b LIMIT 1; +LOCK TABLE t2 READ; +CALL sp; +ERROR HY000: Table 'v1' was not locked with LOCK TABLES +UNLOCK TABLES; +CALL sp; +ERROR 42S22: Unknown column 'b' in 'order clause' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'order clause' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'order clause' +DROP PROCEDURE sp; +CREATE PROCEDURE sp() UPDATE v1 SET a = 1 WHERE a=1 and b=2; +LOCK TABLE t2 READ; +CALL sp; +ERROR HY000: Table 'v1' was not locked with LOCK TABLES +UNLOCK TABLES; +CALL sp; +ERROR 42S22: Unknown column 'b' in 'where clause' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'where clause' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'where clause' +DROP PROCEDURE sp; +DROP VIEW v1; +DROP TABLE t1, t2; # End of 5.5 test # # MDEV-7040: Crash in field_conv, memcpy_field_possible, part#2 diff --git a/mysql-test/suite/encryption/r/innodb-checksum-algorithm,32k.rdiff b/mysql-test/suite/encryption/r/innodb-checksum-algorithm,32k.rdiff index cd66df7440b..d963cde132a 100644 --- a/mysql-test/suite/encryption/r/innodb-checksum-algorithm,32k.rdiff +++ b/mysql-test/suite/encryption/r/innodb-checksum-algorithm,32k.rdiff @@ -1,5 +1,5 @@ --- suite/encryption/r/innodb-checksum-algorithm.result -+++ suite/encryption/r/innodb-checksum-algorithm,32k.reject ++++ suite/encryption/r/innodb-checksum-algorithm.result @@ -13,9 +13,9 @@ SET GLOBAL innodb_default_encryption_key_id=4; SET GLOBAL innodb_checksum_algorithm=crc32; @@ -9,10 +9,10 @@ create table tc_crc32(a serial, b blob, index(b(10))) engine=innodb -ROW_FORMAT=COMPRESSED encrypted=no; +ROW_FORMAT=DYNAMIC encrypted=no; + Warnings: + Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table te_crc32(a serial, b blob, index(b(10))) engine=innodb - encrypted=yes; - create table t_crc32(a serial, b blob, index(b(10))) engine=innodb -@@ -222,9 +222,9 @@ +@@ -153,9 +153,9 @@ t_crc32, tpe_crc32, tp_crc32; SET GLOBAL innodb_checksum_algorithm=innodb; create table tce_innodb(a serial, b blob, index(b(10))) engine=innodb @@ -21,10 +21,10 @@ create table tc_innodb(a serial, b blob, index(b(10))) engine=innodb -ROW_FORMAT=COMPRESSED encrypted=no; +ROW_FORMAT=DYNAMIC encrypted=no; + Warnings: + Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table te_innodb(a serial, b blob, index(b(10))) engine=innodb - encrypted=yes; - create table t_innodb(a serial, b blob, index(b(10))) engine=innodb -@@ -431,9 +431,9 @@ +@@ -293,9 +293,9 @@ t_innodb, tpe_innodb, tp_innodb; SET GLOBAL innodb_checksum_algorithm=none; create table tce_none(a serial, b blob, index(b(10))) engine=innodb @@ -33,6 +33,6 @@ create table tc_none(a serial, b blob, index(b(10))) engine=innodb -ROW_FORMAT=COMPRESSED encrypted=no; +ROW_FORMAT=DYNAMIC encrypted=no; + Warnings: + Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table te_none(a serial, b blob, index(b(10))) engine=innodb - encrypted=yes; - create table t_none(a serial, b blob, index(b(10))) engine=innodb diff --git a/mysql-test/suite/encryption/r/innodb-checksum-algorithm,64k.rdiff b/mysql-test/suite/encryption/r/innodb-checksum-algorithm,64k.rdiff index 523074297da..d963cde132a 100644 --- a/mysql-test/suite/encryption/r/innodb-checksum-algorithm,64k.rdiff +++ b/mysql-test/suite/encryption/r/innodb-checksum-algorithm,64k.rdiff @@ -1,5 +1,5 @@ --- suite/encryption/r/innodb-checksum-algorithm.result -+++ suite/encryption/r/innodb-checksum-algorithm,64k.reject ++++ suite/encryption/r/innodb-checksum-algorithm.result @@ -13,9 +13,9 @@ SET GLOBAL innodb_default_encryption_key_id=4; SET GLOBAL innodb_checksum_algorithm=crc32; @@ -9,10 +9,10 @@ create table tc_crc32(a serial, b blob, index(b(10))) engine=innodb -ROW_FORMAT=COMPRESSED encrypted=no; +ROW_FORMAT=DYNAMIC encrypted=no; + Warnings: + Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table te_crc32(a serial, b blob, index(b(10))) engine=innodb - encrypted=yes; - create table t_crc32(a serial, b blob, index(b(10))) engine=innodb -@@ -222,9 +222,9 @@ +@@ -153,9 +153,9 @@ t_crc32, tpe_crc32, tp_crc32; SET GLOBAL innodb_checksum_algorithm=innodb; create table tce_innodb(a serial, b blob, index(b(10))) engine=innodb @@ -21,10 +21,10 @@ create table tc_innodb(a serial, b blob, index(b(10))) engine=innodb -ROW_FORMAT=COMPRESSED encrypted=no; +ROW_FORMAT=DYNAMIC encrypted=no; + Warnings: + Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table te_innodb(a serial, b blob, index(b(10))) engine=innodb - encrypted=yes; - create table t_innodb(a serial, b blob, index(b(10))) engine=innodb -@@ -431,9 +431,9 @@ +@@ -293,9 +293,9 @@ t_innodb, tpe_innodb, tp_innodb; SET GLOBAL innodb_checksum_algorithm=none; create table tce_none(a serial, b blob, index(b(10))) engine=innodb @@ -33,6 +33,6 @@ create table tc_none(a serial, b blob, index(b(10))) engine=innodb -ROW_FORMAT=COMPRESSED encrypted=no; +ROW_FORMAT=DYNAMIC encrypted=no; + Warnings: + Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table te_none(a serial, b blob, index(b(10))) engine=innodb - encrypted=yes; - create table t_none(a serial, b blob, index(b(10))) engine=innodb diff --git a/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result b/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result index 075e4089546..270645c89a7 100644 --- a/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result +++ b/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result @@ -14,14 +14,20 @@ create table tce_crc32(a serial, b blob, index(b(10))) engine=innodb ROW_FORMAT=COMPRESSED encrypted=yes; create table tc_crc32(a serial, b blob, index(b(10))) engine=innodb ROW_FORMAT=COMPRESSED encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table te_crc32(a serial, b blob, index(b(10))) engine=innodb encrypted=yes; create table t_crc32(a serial, b blob, index(b(10))) engine=innodb encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table tpe_crc32(a serial, b blob, index(b(10))) engine=innodb page_compressed=yes encrypted=yes; create table tp_crc32(a serial, b blob, index(b(10))) engine=innodb page_compressed=yes encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 begin; insert into tce_crc32(b) values (repeat('secret',20)); insert into tc_crc32(b) values (repeat('secret',20)); @@ -148,14 +154,20 @@ create table tce_innodb(a serial, b blob, index(b(10))) engine=innodb ROW_FORMAT=COMPRESSED encrypted=yes; create table tc_innodb(a serial, b blob, index(b(10))) engine=innodb ROW_FORMAT=COMPRESSED encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table te_innodb(a serial, b blob, index(b(10))) engine=innodb encrypted=yes; create table t_innodb(a serial, b blob, index(b(10))) engine=innodb encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table tpe_innodb(a serial, b blob, index(b(10))) engine=innodb page_compressed=yes encrypted=yes; create table tp_innodb(a serial, b blob, index(b(10))) engine=innodb page_compressed=yes encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 begin; insert into tce_innodb(b) values (repeat('secret',20)); insert into tc_innodb(b) values (repeat('secret',20)); @@ -282,14 +294,20 @@ create table tce_none(a serial, b blob, index(b(10))) engine=innodb ROW_FORMAT=COMPRESSED encrypted=yes; create table tc_none(a serial, b blob, index(b(10))) engine=innodb ROW_FORMAT=COMPRESSED encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table te_none(a serial, b blob, index(b(10))) engine=innodb encrypted=yes; create table t_none(a serial, b blob, index(b(10))) engine=innodb encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 create table tpe_none(a serial, b blob, index(b(10))) engine=innodb page_compressed=yes encrypted=yes; create table tp_none(a serial, b blob, index(b(10))) engine=innodb page_compressed=yes encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 begin; insert into tce_none(b) values (repeat('secret',20)); insert into tc_none(b) values (repeat('secret',20)); diff --git a/mysql-test/suite/encryption/r/innodb-compressed-blob.result b/mysql-test/suite/encryption/r/innodb-compressed-blob.result index e156266edc6..f163f6141dd 100644 --- a/mysql-test/suite/encryption/r/innodb-compressed-blob.result +++ b/mysql-test/suite/encryption/r/innodb-compressed-blob.result @@ -7,6 +7,8 @@ set GLOBAL innodb_default_encryption_key_id=4; create table t1(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed; create table t2(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes; create table t3(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=no; +Warnings: +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 insert into t1 values (1, repeat('secret',6000)); insert into t2 values (1, repeat('secret',6000)); insert into t3 values (1, repeat('secret',6000)); diff --git a/mysql-test/suite/encryption/r/innodb-encryption-alter.result b/mysql-test/suite/encryption/r/innodb-encryption-alter.result index 2c6372e1c61..934f0607958 100644 --- a/mysql-test/suite/encryption/r/innodb-encryption-alter.result +++ b/mysql-test/suite/encryption/r/innodb-encryption-alter.result @@ -2,9 +2,16 @@ SET GLOBAL innodb_encrypt_tables = ON; SET GLOBAL innodb_encryption_threads = 4; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=4; Warnings: -Warning 140 InnoDB: Ignored ENCRYPTION_KEY_ID 4 when encryption is disabled +Warning 140 InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 DROP TABLE t1; +set @save_global = @@GLOBAL.innodb_default_encryption_key_id; set innodb_default_encryption_key_id = 99; +Warnings: +Warning 1210 innodb_default_encryption_key=99 is not available +set global innodb_default_encryption_key_id = 99; +Warnings: +Warning 1210 innodb_default_encryption_key=99 is not available +set global innodb_default_encryption_key_id = @save_global; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") SHOW WARNINGS; @@ -38,8 +45,6 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`pk`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4 CREATE TABLE t2 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=1; -Warnings: -Warning 140 InnoDB: Ignored ENCRYPTION_KEY_ID 1 when encryption is disabled ALTER TABLE t1 ENCRYPTION_KEY_ID=99; ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID' SHOW WARNINGS; @@ -51,38 +56,32 @@ drop table t1,t2; SET GLOBAL innodb_encrypt_tables=OFF; CREATE TABLE t1 (a int not null primary key) engine=innodb; ALTER TABLE t1 ENCRYPTION_KEY_ID=4; -ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID' -SHOW WARNINGS; -Level Code Message -Warning 140 InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 -Error 1478 Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4 DROP TABLE t1; CREATE TABLE t2 (a int not null primary key) engine=innodb; ALTER TABLE t2 ENCRYPTION_KEY_ID=4, ALGORITHM=COPY; -ERROR HY000: Can't create table `test`.`t2` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 -Error 1005 Can't create table `test`.`t2` (errno: 140 "Wrong create options") -Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 `ENCRYPTION_KEY_ID`=4 CREATE TABLE t3 (a int not null primary key) engine=innodb ENCRYPTION_KEY_ID=4; -ERROR HY000: Can't create table `test`.`t3` (errno: 140 "Wrong create options") +DROP TABLE t3; +SET GLOBAL innodb_encrypt_tables='FORCE'; +CREATE TABLE t1 (a int primary key) engine=innodb encrypted=no; +ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message -Warning 140 InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 -Error 1005 Can't create table `test`.`t3` (errno: 140 "Wrong create options") +Warning 140 InnoDB: ENCRYPTED=NO cannot be used with innodb_encrypt_tables=FORCE +Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB FLUSH TABLES; create table t1(f1 int not null, f2 int not null)engine=innodb encrypted=yes; diff --git a/mysql-test/suite/encryption/t/innodb-encryption-alter.test b/mysql-test/suite/encryption/t/innodb-encryption-alter.test index bc7c3b3f13d..d9a0e4e95cd 100644 --- a/mysql-test/suite/encryption/t/innodb-encryption-alter.test +++ b/mysql-test/suite/encryption/t/innodb-encryption-alter.test @@ -12,7 +12,10 @@ SET GLOBAL innodb_encryption_threads = 4; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=4; DROP TABLE t1; +set @save_global = @@GLOBAL.innodb_default_encryption_key_id; set innodb_default_encryption_key_id = 99; +set global innodb_default_encryption_key_id = 99; +set global innodb_default_encryption_key_id = @save_global; --error 1005 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; SHOW WARNINGS; @@ -83,22 +86,24 @@ drop table t1,t2; # # MDEV-17230: encryption_key_id from alter is ignored by encryption threads # +--enable_warnings SET GLOBAL innodb_encrypt_tables=OFF; CREATE TABLE t1 (a int not null primary key) engine=innodb; ---error ER_ILLEGAL_HA_CREATE_OPTION ALTER TABLE t1 ENCRYPTION_KEY_ID=4; -SHOW WARNINGS; SHOW CREATE TABLE t1; DROP TABLE t1; CREATE TABLE t2 (a int not null primary key) engine=innodb; ---error ER_CANT_CREATE_TABLE ALTER TABLE t2 ENCRYPTION_KEY_ID=4, ALGORITHM=COPY; SHOW WARNINGS; SHOW CREATE TABLE t2; ---error ER_CANT_CREATE_TABLE CREATE TABLE t3 (a int not null primary key) engine=innodb ENCRYPTION_KEY_ID=4; +DROP TABLE t3; + +SET GLOBAL innodb_encrypt_tables='FORCE'; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 (a int primary key) engine=innodb encrypted=no; SHOW WARNINGS; FLUSH TABLES; diff --git a/mysql-test/suite/innodb/r/innodb-alter-nullable.result b/mysql-test/suite/innodb/r/innodb-alter-nullable.result index ddf1ac7de7a..22aa39ba923 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-nullable.result +++ b/mysql-test/suite/innodb/r/innodb-alter-nullable.result @@ -3,12 +3,9 @@ INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9); ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 -set @old_sql_mode = @@sql_mode; -set @@sql_mode = 'STRICT_TRANS_TABLES'; ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 -set @@sql_mode = @old_sql_mode; ALTER TABLE t CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL; ERROR 42S22: Unknown column 'c2' in 't' ALTER TABLE t MODIFY c2 INT, MODIFY c2 INT NOT NULL; @@ -24,8 +21,6 @@ ALTER TABLE t MODIFY c2 INT NOT NULL; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 connect con1,localhost,root,,; -connection con1; -SET SQL_MODE='STRICT_ALL_TABLES'; UPDATE t SET c2=NULL; ERROR 23000: Column 'c2' cannot be null SELECT * FROM t; @@ -61,3 +56,34 @@ CREATE TABLE t1(c1 INT) ENGINE=InnoDB; ALTER TABLE t1 ADD CONSTRAINT UNIQUE KEY i1(c1); ALTER TABLE t1 CHANGE c1 c1 INT NOT NULL,ADD KEY(c1); DROP TABLE t1; +# +# MDEV-18732 InnoDB: ALTER IGNORE returns error for NULL +# +CREATE TABLE t1(c INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +ALTER IGNORE TABLE t1 MODIFY c INT NOT NULL; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +INSERT INTO t1 VALUES (NULL); +ERROR 23000: Column 'c' cannot be null +SELECT * FROM t1; +c +0 +DROP TABLE t1; +CREATE TABLE t1(c INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL),(1),(1); +ALTER IGNORE TABLE t1 ADD UNIQUE(c); +affected rows: 3 +info: Records: 3 Duplicates: 1 Warnings: 0 +ALTER IGNORE TABLE t1 ADD PRIMARY KEY(c); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 1 +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +SELECT * FROM t1; +c +0 +1 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-alter-nullable.test b/mysql-test/suite/innodb/t/innodb-alter-nullable.test index d039459f91f..19d3df75b5d 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-nullable.test +++ b/mysql-test/suite/innodb/t/innodb-alter-nullable.test @@ -13,14 +13,7 @@ INSERT INTO t VALUES (1,2,3),(4,5,6),(7,8,9); ALTER TABLE t CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE; # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. ---disable_info -set @old_sql_mode = @@sql_mode; -set @@sql_mode = 'STRICT_TRANS_TABLES'; ---enable_info ALTER TABLE t MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE; ---disable_info -set @@sql_mode = @old_sql_mode; ---enable_info # Request some conflicting changes for a single column. --error ER_BAD_FIELD_ERROR @@ -38,9 +31,6 @@ ALTER TABLE t MODIFY c2 INT NOT NULL; --disable_info connect (con1,localhost,root,,); -connection con1; - -SET SQL_MODE='STRICT_ALL_TABLES'; --error ER_BAD_NULL_ERROR UPDATE t SET c2=NULL; @@ -76,6 +66,29 @@ ALTER TABLE t1 ADD CONSTRAINT UNIQUE KEY i1(c1); ALTER TABLE t1 CHANGE c1 c1 INT NOT NULL,ADD KEY(c1); DROP TABLE t1; +--echo # +--echo # MDEV-18732 InnoDB: ALTER IGNORE returns error for NULL +--echo # + +CREATE TABLE t1(c INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +--enable_info +ALTER IGNORE TABLE t1 MODIFY c INT NOT NULL; +--disable_info +--error ER_BAD_NULL_ERROR +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(c INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL),(1),(1); +--enable_info +ALTER IGNORE TABLE t1 ADD UNIQUE(c); +ALTER IGNORE TABLE t1 ADD PRIMARY KEY(c); +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index a283e64d473..3a5bcad1685 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -677,6 +677,51 @@ LOAD DATA INFILE '../../std_data/loaddata/mdev-11631.txt' INTO TABLE t1 CHARACTE SELECT HEX(a) FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-15744: Assertion `derived->table' failed in mysql_derived_merge_for_insert +--echo # + +create table t1 (a int, b int); +CREATE OR REPLACE VIEW t2 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT * FROM t2; +LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2 + FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; +select * from v2; +select * from t2; +DROP VIEW IF EXISTS v2,t2; +DROP TABLE IF EXISTS t1; + +--echo # +--echo # MDEV-15950: LOAD DATA INTO compex_view crashed +--echo # +create table t1 (a int, b int); +create table t0 (x int, y int); +CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1,t0; +CREATE VIEW v2 AS SELECT * FROM v1; + +--error ER_WRONG_USAGE +LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v1 +FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; + +--error ER_WRONG_USAGE +LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2 +FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; + +DROP VIEW IF EXISTS v2,v1; +DROP TABLE IF EXISTS t1,t0; + +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE(b)); +INSERT INTO t1 VALUES (1,1); +CREATE TABLE t2 (c INT); +CREATE VIEW v AS SELECT t1.* FROM t1 JOIN t2; +SELECT a, b FROM t1 INTO OUTFILE '15645.data'; +--error ER_WRONG_USAGE +LOAD DATA INFILE '15645.data' IGNORE INTO TABLE v (a,b); +--error ER_WRONG_USAGE +LOAD DATA INFILE '15645.data' REPLACE INTO TABLE v (a,b); +drop table t1,t2; +drop view v; + --echo # --echo # MDEV-15497 Wrong empty value in a GEOMETRY column on LOAD DATA diff --git a/mysql-test/t/log_slow.test b/mysql-test/t/log_slow.test index 56e35bd5a20..edb1865b5ae 100644 --- a/mysql-test/t/log_slow.test +++ b/mysql-test/t/log_slow.test @@ -58,3 +58,43 @@ set @@log_slow_filter=default; set @@log_slow_verbosity=default; set global log_output= default; truncate mysql.slow_log; + +--echo # +--echo # MDEV-18333 Slow_queries count doesn't increase when slow_query_log is turned off +--echo # + +SET SESSION slow_query_log=OFF; +SET GLOBAL slow_query_log=OFF; +SET long_query_time=0.1; + +--echo # Although this query is disallowed by slow_query_log, it should still increment Slow_queries + +SELECT VARIABLE_VALUE INTO @global_slow_queries + FROM INFORMATION_SCHEMA.GLOBAL_STATUS + WHERE VARIABLE_NAME='SLOW_QUERIES'; +SELECT sleep(0.2) INTO @tmp FROM DUAL; +SELECT + CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment + FROM + INFORMATION_SCHEMA.GLOBAL_STATUS + WHERE + VARIABLE_NAME='SLOW_QUERIES'; + +--echo # Although this query is disallowed by log_slow_filter, it should still increment Slow_queries + +SET log_slow_filter=filesort; +SELECT sleep(0.2) INTO @tmp FROM DUAL; +SELECT VARIABLE_VALUE INTO @global_slow_queries + FROM INFORMATION_SCHEMA.GLOBAL_STATUS + WHERE VARIABLE_NAME='SLOW_QUERIES'; +SELECT sleep(0.2) INTO @tmp FROM DUAL; +SELECT + CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment + FROM + INFORMATION_SCHEMA.GLOBAL_STATUS + WHERE + VARIABLE_NAME='SLOW_QUERIES'; +SET log_slow_filter=DEFAULT; + +SET @@long_query_time=default; +SET GLOBAL slow_query_log= @org_slow_query_log; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index c95496087d7..cafcee0391e 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9362,6 +9362,47 @@ CALL sp; DROP PROCEDURE sp; DROP TABLE t1; +--echo # +--echo # MDEV-17055: Server crashes in find_order_in_list upon +--echo # 2nd (3rd) execution of SP with UPDATE +--echo # + +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TABLE t2 (c INT); + +CREATE PROCEDURE sp() UPDATE v1 SET a = 1 ORDER BY a, b LIMIT 1; +LOCK TABLE t2 READ; +--error ER_TABLE_NOT_LOCKED +CALL sp; +UNLOCK TABLES; +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; + +# Cleanup +DROP PROCEDURE sp; + +CREATE PROCEDURE sp() UPDATE v1 SET a = 1 WHERE a=1 and b=2; +LOCK TABLE t2 READ; +--error ER_TABLE_NOT_LOCKED +CALL sp; +UNLOCK TABLES; +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; + +# Cleanup +DROP PROCEDURE sp; + +DROP VIEW v1; +DROP TABLE t1, t2; --echo # End of 5.5 test |