diff options
Diffstat (limited to 'mysql-test')
55 files changed, 1271 insertions, 206 deletions
diff --git a/mysql-test/main/alter_table_errors.result b/mysql-test/main/alter_table_errors.result index 1e2a8100e84..5a3ecb6727d 100644 --- a/mysql-test/main/alter_table_errors.result +++ b/mysql-test/main/alter_table_errors.result @@ -1,6 +1,6 @@ create table t (a int, v int as (a)) engine=innodb; alter table t change column a b tinyint, algorithm=inplace; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY show create table t; Table Create Table t CREATE TABLE `t` ( diff --git a/mysql-test/main/check_constraint_innodb.result b/mysql-test/main/check_constraint_innodb.result index 45af3f512b7..4b412491204 100644 --- a/mysql-test/main/check_constraint_innodb.result +++ b/mysql-test/main/check_constraint_innodb.result @@ -2,7 +2,7 @@ create table t1 (a int, b smallint) engine=innodb; connect con1,localhost,root,,test; alter table t1 add constraint check (b < 8); alter table t1 modify column b int, algorithm=inplace; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY connection default; alter table t1 add primary key (a); drop table t1; diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index 85ca0342dee..82a4813b156 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4657,3 +4657,122 @@ GROUP BY v1.pk HAVING (v1.pk = 1); DROP TABLE t1,t2,tmp1; DROP VIEW v1; +# +# MDEV-19164: pushdown of condition with cached items +# +create table t1 (d1 date); +insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08'); +select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +d1 +explain extended select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26') group by `test`.`t1`.`d1` having 1 +explain format=json select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.d1", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t1.d1 between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26')" + } + } + } + } +} +delete from t1; +insert into t1 values ('2018-01-15'),('2018-02-20'); +select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +d1 +2018-01-15 +2018-02-20 +Warnings: +Warning 1292 Truncated incorrect datetime value: '1' +explain extended select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` not between <cache>(0) and <cache>(exp(0)) group by `test`.`t1`.`d1` having 1 +explain format=json select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.d1", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.d1 not between <cache>(0) and <cache>(exp(0))" + } + } + } + } +} +drop table t1; +# +# MDEV-19245: Impossible WHERE should be noticed earlier +# after HAVING pushdown +# +CREATE TABLE t1 (a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1); +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +DROP TABLE t1; diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index 257e5cb4875..f1bf70627f6 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -1340,3 +1340,64 @@ HAVING (v1.pk = 1); DROP TABLE t1,t2,tmp1; DROP VIEW v1; + +--echo # +--echo # MDEV-19164: pushdown of condition with cached items +--echo # + +create table t1 (d1 date); +insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08'); + +let $q1= +select d1 from t1 + group by d1 + having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; + +eval $q1; +eval explain extended $q1; +eval explain format=json $q1; + +delete from t1; +insert into t1 values ('2018-01-15'),('2018-02-20'); + +let $q2= +select d1 from t1 + group by d1 + having d1 not between 0 AND exp(0); + +eval $q2; +eval explain extended $q2; +eval explain format=json $q2; + +drop table t1; + +--echo # +--echo # MDEV-19245: Impossible WHERE should be noticed earlier +--echo # after HAVING pushdown +--echo # + +CREATE TABLE t1 (a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,2,1),(3,2,2),(5,6,4),(3,4,1); + +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a > 3 GROUP BY t1.a HAVING t1.a = 3; +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING t1.a > 3; +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a > 3 AND t1.a = 3 GROUP BY t1.a ; + +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE (t1.a < 2 OR t1.c > 1) GROUP BY t1.a HAVING t1.a = 3; +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 OR t1.a > 3); +EXPLAIN SELECT t1.a,MAX(t1.b) FROM t1 +WHERE t1.a = 3 AND (t1.a < 2 OR t1.a > 3) GROUP BY t1.a; + +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE (t1.a < 2 AND t1.c > 1) GROUP BY t1.a HAVING t1.a = 3; +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE t1.a = 3 GROUP BY t1.a HAVING (t1.a < 2 AND t1.c > 1); +EXPLAIN SELECT t1.a,MAX(t1.b),t1.c FROM t1 +WHERE t1.a = 3 AND (t1.a < 2 AND t1.b > 3) GROUP BY t1.a; + +DROP TABLE t1; diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 1f1d80f5460..150e2af1fbc 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -1207,8 +1207,10 @@ The following specify which files/extra groups are read (specified before remain --slave-transaction-retry-errors=name Tells the slave thread to retry transaction for replication when a query event returns an error from the - provided list. Deadlock and elapsed lock wait timeout - errors are automatically added to this list + provided list. Deadlock error, elapsed lock wait timeout, + net read error, net read timeout, net write error, net + write timeout, connect error and 2 types of lost + connection error are automatically added to this list --slave-transaction-retry-interval=# Interval of the slave SQL thread will retry a transaction in case it failed with a deadlock or elapsed lock wait @@ -1712,7 +1714,7 @@ slave-run-triggers-for-rbr NO slave-skip-errors OFF slave-sql-verify-checksum TRUE slave-transaction-retries 10 -slave-transaction-retry-errors 1213,1205 +slave-transaction-retry-errors 1158,1159,1160,1161,1205,1213,1429,2013,12701 slave-transaction-retry-interval 0 slave-type-conversions slow-launch-time 2 diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 2bb817c8286..c9f89b94e41 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -5388,3 +5388,21 @@ drop table t1; # # End of 10.2 tests # +# +# MDEV-19263: Server crashes in mysql_handle_single_derived +# upon 2nd execution of PS +# +CREATE TABLE t1 (f INT); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO v1 SELECT * FROM x; +PREPARE stmt FROM "INSERT INTO v1 VALUES (1)"; +EXECUTE stmt; +ERROR 42S02: Table 'test.x' doesn't exist +EXECUTE stmt; +ERROR 42S02: Table 'test.x' doesn't exist +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index 86ae11ccb61..4254c7c41eb 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -4825,3 +4825,27 @@ drop table t1; --echo # --echo # End of 10.2 tests --echo # + +--echo # +--echo # MDEV-19263: Server crashes in mysql_handle_single_derived +--echo # upon 2nd execution of PS +--echo # + +CREATE TABLE t1 (f INT); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO v1 SELECT * FROM x; +PREPARE stmt FROM "INSERT INTO v1 VALUES (1)"; + +--error ER_NO_SUCH_TABLE +EXECUTE stmt; +--error ER_NO_SUCH_TABLE +EXECUTE stmt; + +# Cleanup +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 280ced71bba..efe914faba7 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -2012,4 +2012,96 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; +# +# MDEV-19255: rowid range filter built for range condition +# that uses in expensive subquery +# +CREATE TABLE t1 ( +pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +pk1 a1 b1 pk2 a2 b2 +65 2 a 109 65 NULL +EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where +1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` +EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 101, + "filtered": 100, + "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "b1"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk1"], + "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 87, + "selectivity_pct": 87 + }, + "rows": 1, + "filtered": 87, + "attached_condition": "t1.b1 <= (subquery#2)" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t2.pk2 <= 1" + } + } + } + ] + } +} +DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 9c533674fb1..6f26e81db92 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -300,4 +300,43 @@ SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); DROP TABLE t1,t2; +--echo # +--echo # MDEV-19255: rowid range filter built for range condition +--echo # that uses in expensive subquery +--echo # + +CREATE TABLE t1 ( + pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); + +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; + +let $q= +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) + WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval EXPLAIN FORMAT=JSON $q; + +DROP TABLE t1,t2; + set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index d19aca16bfd..54c7e03f13a 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1941,6 +1941,98 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; +# +# MDEV-19255: rowid range filter built for range condition +# that uses in expensive subquery +# +CREATE TABLE t1 ( +pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +pk1 a1 b1 pk2 a2 b2 +65 2 a 109 65 NULL +EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where +1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` +EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 101, + "filtered": 100, + "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "b1"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk1"], + "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 87, + "selectivity_pct": 87 + }, + "rows": 1, + "filtered": 87, + "attached_condition": "t1.b1 <= (subquery#2)" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t2.pk2 <= 1" + } + } + } + ] + } +} +DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables; # # MDEV-18755: possible RORI-plan and possible plan with range filter @@ -1977,8 +2069,8 @@ union ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref|filter f1,f2 f2|f1 33|13 const 1 (2%) Using index condition; Using where; Using rowid filter -2 UNION t1 ref|filter f1,f2 f2|f1 33|13 const 1 (2%) Using index condition; Using where; Using rowid filter +1 PRIMARY t1 index_merge f1,f2 f1,f2 13,33 NULL 1 Using intersect(f1,f2); Using where +2 UNION t1 index_merge f1,f2 f1,f2 13,33 NULL 1 Using intersect(f1,f2); Using where NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL explain format=json ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) @@ -1997,24 +2089,24 @@ EXPLAIN "select_id": 1, "table": { "table_name": "t1", - "access_type": "ref", + "access_type": "index_merge", "possible_keys": ["f1", "f2"], - "key": "f2", - "key_length": "33", - "used_key_parts": ["f2"], - "ref": ["const"], - "rowid_filter": { - "range": { - "key": "f1", - "used_key_parts": ["f1"] - }, - "rows": 1, - "selectivity_pct": 1.5873 + "key_length": "13,33", + "index_merge": { + "intersect": { + "range": { + "key": "f1", + "used_key_parts": ["f1"] + }, + "range": { + "key": "f2", + "used_key_parts": ["f2"] + } + } }, "rows": 1, - "filtered": 100, - "index_condition": "t1.f2 is null", - "attached_condition": "t1.f1 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" + "filtered": 1.5873, + "attached_condition": "t1.f1 is null and t1.f2 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" } } }, @@ -2024,24 +2116,24 @@ EXPLAIN "operation": "UNION", "table": { "table_name": "t1", - "access_type": "ref", + "access_type": "index_merge", "possible_keys": ["f1", "f2"], - "key": "f2", - "key_length": "33", - "used_key_parts": ["f2"], - "ref": ["const"], - "rowid_filter": { - "range": { - "key": "f1", - "used_key_parts": ["f1"] - }, - "rows": 1, - "selectivity_pct": 1.5873 + "key_length": "13,33", + "index_merge": { + "intersect": { + "range": { + "key": "f1", + "used_key_parts": ["f1"] + }, + "range": { + "key": "f2", + "used_key_parts": ["f2"] + } + } }, "rows": 1, - "filtered": 100, - "index_condition": "t1.f2 is null", - "attached_condition": "t1.f1 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" + "filtered": 1.5873, + "attached_condition": "t1.f1 is null and t1.f2 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" } } } @@ -2050,4 +2142,24 @@ EXPLAIN } } drop table t1; +# +# MDEV-19195: possible RORI-plan and possible plan with range filter +# for not first joined table +# +create table t1 (id int not null primary key) engine=innodb; +insert into t1 values (2),(1); +create table t2 (y int,x int,index (x),index (y)) engine=innodb; +insert into t2 values +(4,1),(4,777),(2,1),(2,888),(111,1),(222,1),(333,345),(444,1), +(555,555),(666,1); +select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; +id y x +1 2 1 +explain extended select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 Using index +1 SIMPLE t2 index_merge x,y y,x 5,5 NULL 1 100.00 Using intersect(y,x); Using where; Using index +Warnings: +Note 1003 select 1 AS `id`,`test`.`t2`.`y` AS `y`,`test`.`t2`.`x` AS `x` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`y` = 2 and `test`.`t2`.`x` = 1 +drop table t1, t2; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 803f2846de9..173ba15f10d 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -43,5 +43,26 @@ eval explain format=json $q; drop table t1; +--echo # +--echo # MDEV-19195: possible RORI-plan and possible plan with range filter +--echo # for not first joined table +--echo # + +create table t1 (id int not null primary key) engine=innodb; +insert into t1 values (2),(1); + +create table t2 (y int,x int,index (x),index (y)) engine=innodb; +insert into t2 values + (4,1),(4,777),(2,1),(2,888),(111,1),(222,1),(333,345),(444,1), + (555,555),(666,1); + +let $q= +select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; + +eval $q; +eval explain extended $q; + +drop table t1, t2; + SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index 0eb40c9be00..8e09be9b705 100644 --- a/mysql-test/main/subselect_innodb.result +++ b/mysql-test/main/subselect_innodb.result @@ -616,3 +616,36 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` join `test`.`t2` semi join (`test`.`t4`) join `test`.`t3` where `test`.`t4`.`f4` = 1 and `test`.`t1`.`f1` >= `test`.`t2`.`f2` DROP TABLE t1,t2,t3,t4; +# +# MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0' +# failed in Item_equal::fix_fields, server crashes after 2nd execution +# of PS +# +create table t1 (a int, b int); +create table t2 (x int, y int); +insert into t1 values (1,1),(2,2); +insert into t2 values (1,1),(2,2),(2,3); +# here we can see conditions pushdown (see HAVING): +prepare stmt from " +explain extended +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; +execute stmt; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select `test`.`t2`.`x`,count(`test`.`t2`.`y`) from `test`.`t2` where 0 group by `test`.`t2`.`x` having `COUNT(t2.y)` = `test`.`t2`.`x`) join `test`.`t1` where 0 +# here re-execution of the pushdown does not crash: +prepare stmt from " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; +execute stmt; +a b +execute stmt; +a b +execute stmt; +a b +drop table t1,t2; diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test index 544bcd994ed..b8d12d04a5e 100644 --- a/mysql-test/main/subselect_innodb.test +++ b/mysql-test/main/subselect_innodb.test @@ -611,3 +611,35 @@ FROM t1 DROP TABLE t1,t2,t3,t4; +--echo # +--echo # MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0' +--echo # failed in Item_equal::fix_fields, server crashes after 2nd execution +--echo # of PS +--echo # + +create table t1 (a int, b int); +create table t2 (x int, y int); + +insert into t1 values (1,1),(2,2); +insert into t2 values (1,1),(2,2),(2,3); + +--echo # here we can see conditions pushdown (see HAVING): +prepare stmt from " +explain extended +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; + +execute stmt; + +--echo # here re-execution of the pushdown does not crash: +prepare stmt from " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; + +execute stmt; +execute stmt; +execute stmt; + +drop table t1,t2; diff --git a/mysql-test/main/type_blob.result b/mysql-test/main/type_blob.result index 94741ddbc0e..cfb47f7b850 100644 --- a/mysql-test/main/type_blob.result +++ b/mysql-test/main/type_blob.result @@ -1093,3 +1093,34 @@ drop table t1; # # End of 10.2 test # +# +# Start of 10.4 test +# +# +# MDEV-19317 TEXT column accepts too long literals as a default value +# +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 (a TINYTEXT DEFAULT ?)' USING REPEAT('a', 255); +INSERT INTO t1 VALUES (); +SELECT LENGTH(a), LENGTH(DEFAULT(a)) FROM t1; +LENGTH(a) LENGTH(DEFAULT(a)) +255 255 +DROP TABLE t1; +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 (a TINYTEXT DEFAULT ?)' USING REPEAT('a', 256); +ERROR 42000: Invalid default value for 'a' +CREATE OR REPLACE TABLE t1 (a TINYTEXT DEFAULT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +ERROR 42000: Invalid default value for 'a' +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 (a TEXT DEFAULT ?)' USING REPEAT('a', 256); +INSERT INTO t1 VALUES (); +SELECT LENGTH(a), LENGTH(DEFAULT(a)) FROM t1; +LENGTH(a) LENGTH(DEFAULT(a)) +256 256 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a TEXT DEFAULT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +INSERT INTO t1 VALUES (); +SELECT LENGTH(a), LENGTH(DEFAULT(a)) FROM t1; +LENGTH(a) LENGTH(DEFAULT(a)) +256 256 +DROP TABLE t1; +# +# End of 10.4 test +# diff --git a/mysql-test/main/type_blob.test b/mysql-test/main/type_blob.test index 910050f067a..df565b187b4 100644 --- a/mysql-test/main/type_blob.test +++ b/mysql-test/main/type_blob.test @@ -702,3 +702,39 @@ drop table t1; --echo # --echo # End of 10.2 test --echo # + + +--echo # +--echo # Start of 10.4 test +--echo # + +--echo # +--echo # MDEV-19317 TEXT column accepts too long literals as a default value +--echo # + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 (a TINYTEXT DEFAULT ?)' USING REPEAT('a', 255); +INSERT INTO t1 VALUES (); +SELECT LENGTH(a), LENGTH(DEFAULT(a)) FROM t1; +DROP TABLE t1; + +--error ER_INVALID_DEFAULT +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 (a TINYTEXT DEFAULT ?)' USING REPEAT('a', 256); + +--error ER_INVALID_DEFAULT +CREATE OR REPLACE TABLE t1 (a TINYTEXT DEFAULT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); + + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 (a TEXT DEFAULT ?)' USING REPEAT('a', 256); +INSERT INTO t1 VALUES (); +SELECT LENGTH(a), LENGTH(DEFAULT(a)) FROM t1; +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a TEXT DEFAULT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +INSERT INTO t1 VALUES (); +SELECT LENGTH(a), LENGTH(DEFAULT(a)) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # End of 10.4 test +--echo # diff --git a/mysql-test/main/unique.result b/mysql-test/main/unique.result new file mode 100644 index 00000000000..e982e1c4163 --- /dev/null +++ b/mysql-test/main/unique.result @@ -0,0 +1,9 @@ +CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, a varchar(30000), UNIQUE (a)) ENGINE=innodb; +INSERT INTO t1 (a) VALUES (20),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +SELECT * FROM t1 WHERE a BETWEEN '1' AND '100'; +pk a +DROP TABLE t1; +CREATE TABLE t2 (n BLOB, UNIQUE(n)); +INSERT INTO t2 VALUES (1); +DELETE FROM t2 WHERE n = 1; +DROP TABLE t2; diff --git a/mysql-test/main/unique.test b/mysql-test/main/unique.test new file mode 100644 index 00000000000..677ceb15a79 --- /dev/null +++ b/mysql-test/main/unique.test @@ -0,0 +1,22 @@ +--source include/have_innodb.inc + +# +# MDEV-19224 Assertion `marked_for_read()' failed in various places with long +# unique key +# + +CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, a varchar(30000), UNIQUE (a)) ENGINE=innodb; +INSERT INTO t1 (a) VALUES (20),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +SELECT * FROM t1 WHERE a BETWEEN '1' AND '100'; +DROP TABLE t1; + +# +# MDEV-19252 Warning about assertion failure marked_for_write_or_computed() +# printed by release build with DBUG_ASSERT_AS_PRINTF, but no failure on debug +# build +# + +CREATE TABLE t2 (n BLOB, UNIQUE(n)); +INSERT INTO t2 VALUES (1); +DELETE FROM t2 WHERE n = 1; +DROP TABLE t2; diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 0df7f08db0a..24bf9b40109 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -174,6 +174,7 @@ my @DEFAULT_SUITES= qw( binlog_encryption- csv- compat/oracle- + compat/mssql- encryption- federated- funcs_1- diff --git a/mysql-test/suite/compat/mssql/parser.result b/mysql-test/suite/compat/mssql/parser.result new file mode 100644 index 00000000000..817439a826c --- /dev/null +++ b/mysql-test/suite/compat/mssql/parser.result @@ -0,0 +1,87 @@ +SET sql_mode=MSSQL; +# +# Start of 10.4 tests +# +# +# MDEV-19142 sql_mode=MSSQL: Bracket identifiers +# +SELECT 'test' AS [[]; +[ +test +SELECT 'test' AS []]]; +] +test +SELECT 'test' AS [[a]]]; +[a] +test +SELECT 'test' AS [\n]; +\n +test +CREATE TABLE [t 1] ([a b] INT); +SHOW CREATE TABLE [t 1]; +Table Create Table +t 1 CREATE TABLE "t 1" ( + "a b" int(11) DEFAULT NULL +) +INSERT INTO [t 1] VALUES (10); +SELECT [a b] FROM [t 1]; +a b +10 +SELECT [a b] [a b alias] FROM [t 1] [t 1 alias]; +a b alias +10 +SELECT [a b] FROM [test].[t 1]; +a b +10 +SELECT [a b], COUNT(*) FROM [t 1] GROUP BY [a b]; +a b COUNT(*) +10 1 +SELECT [a b], COUNT(*) FROM [t 1] GROUP BY [a b] HAVING [a b]>0; +a b COUNT(*) +10 1 +DROP TABLE [t 1]; +CREATE TABLE [t[1]]] (a INT); +SHOW CREATE TABLE [t[1]]]; +Table Create Table +t[1] CREATE TABLE "t[1]" ( + "a" int(11) DEFAULT NULL +) +DROP TABLE [t[1]]]; +CREATE TABLE [t 1] ([a b] INT); +CREATE VIEW [v 1] AS SELECT [a b] FROM [t 1]; +SHOW CREATE VIEW [v 1]; +View Create View character_set_client collation_connection +v 1 CREATE VIEW "v 1" AS select "t 1"."a b" AS "a b" from "t 1" latin1 latin1_swedish_ci +SELECT * FROM [v 1]; +a b +DROP VIEW [v 1]; +DROP TABLE [t 1]; +CREATE PROCEDURE [p 1]() +BEGIN +SELECT 'test' [a b]; +END; +$$ +SHOW CREATE PROCEDURE [p 1]; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p 1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS CREATE DEFINER="root"@"localhost" PROCEDURE "p 1"() +BEGIN +SELECT 'test' [a b]; +END latin1 latin1_swedish_ci latin1_swedish_ci +CALL [p 1]; +a b +test +DROP PROCEDURE [p 1]; +CREATE TABLE [t1] ([a] INT); +INSERT INTO t1 VALUES (10); +PREPARE [stmt] FROM 'SELECT [a] FROM [test].[t1]'; +EXECUTE [stmt]; +a +10 +DEALLOCATE PREPARE [stmt]; +EXECUTE IMMEDIATE 'SELECT [a] FROM [test].[t1]'; +a +10 +DROP TABLE [t1]; +# +# End of 10.4 tests +# diff --git a/mysql-test/suite/compat/mssql/parser.test b/mysql-test/suite/compat/mssql/parser.test new file mode 100644 index 00000000000..59c6735c6c2 --- /dev/null +++ b/mysql-test/suite/compat/mssql/parser.test @@ -0,0 +1,68 @@ +SET sql_mode=MSSQL; + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-19142 sql_mode=MSSQL: Bracket identifiers +--echo # + +# Brackets inside bracket identifiers: +# - When we want a left bracket inside a bracket identifier, +# we just add a single left bracket: [ +# - When we want a right bracket inside a bracket identifier, +# we add two right brackets: ]] + + +SELECT 'test' AS [[]; +SELECT 'test' AS []]]; +SELECT 'test' AS [[a]]]; + +# Backslash has no special meaning +SELECT 'test' AS [\n]; + + +CREATE TABLE [t 1] ([a b] INT); +SHOW CREATE TABLE [t 1]; +INSERT INTO [t 1] VALUES (10); +SELECT [a b] FROM [t 1]; +SELECT [a b] [a b alias] FROM [t 1] [t 1 alias]; +SELECT [a b] FROM [test].[t 1]; +SELECT [a b], COUNT(*) FROM [t 1] GROUP BY [a b]; +SELECT [a b], COUNT(*) FROM [t 1] GROUP BY [a b] HAVING [a b]>0; +DROP TABLE [t 1]; + +CREATE TABLE [t[1]]] (a INT); +SHOW CREATE TABLE [t[1]]]; +DROP TABLE [t[1]]]; + +CREATE TABLE [t 1] ([a b] INT); +CREATE VIEW [v 1] AS SELECT [a b] FROM [t 1]; +SHOW CREATE VIEW [v 1]; +SELECT * FROM [v 1]; +DROP VIEW [v 1]; +DROP TABLE [t 1]; + +DELIMITER $$; +CREATE PROCEDURE [p 1]() +BEGIN + SELECT 'test' [a b]; +END; +$$ +DELIMITER ;$$ +SHOW CREATE PROCEDURE [p 1]; +CALL [p 1]; +DROP PROCEDURE [p 1]; + +CREATE TABLE [t1] ([a] INT); +INSERT INTO t1 VALUES (10); +PREPARE [stmt] FROM 'SELECT [a] FROM [test].[t1]'; +EXECUTE [stmt]; +DEALLOCATE PREPARE [stmt]; +EXECUTE IMMEDIATE 'SELECT [a] FROM [test].[t1]'; +DROP TABLE [t1]; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/encryption/r/corrupted_during_recovery.result b/mysql-test/suite/encryption/r/corrupted_during_recovery.result new file mode 100644 index 00000000000..41c0d7d75a8 --- /dev/null +++ b/mysql-test/suite/encryption/r/corrupted_during_recovery.result @@ -0,0 +1,22 @@ +CREATE TABLE t1(a BIGINT PRIMARY KEY) ENGINE=InnoDB, ENCRYPTED=YES; +INSERT INTO t1 VALUES(1); +CREATE TABLE t2(a BIGINT PRIMARY KEY) ENGINE=InnoDB, ENCRYPTED=YES; +INSERT INTO t1 VALUES(2); +SET GLOBAL innodb_flush_log_at_trx_commit=1; +INSERT INTO t2 VALUES(2); +# Kill the server +# Corrupt the pages +SELECT * FROM t1; +ERROR 42000: Unknown storage engine 'InnoDB' +SELECT * FROM t1; +a +1 +2 +SELECT * FROM t2; +a +2 +CHECK TABLE t1,t2; +Table Op Msg_type Msg_text +test.t1 check status OK +test.t2 check status OK +DROP TABLE t1, t2; diff --git a/mysql-test/suite/encryption/t/corrupted_during_recovery.test b/mysql-test/suite/encryption/t/corrupted_during_recovery.test new file mode 100644 index 00000000000..44cd03e9f8a --- /dev/null +++ b/mysql-test/suite/encryption/t/corrupted_during_recovery.test @@ -0,0 +1,61 @@ +--source include/have_innodb.inc +--source include/have_file_key_management_plugin.inc + +--disable_query_log +call mtr.add_suppression("InnoDB: Plugin initialization aborted"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error"); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed"); +call mtr.add_suppression("InnoDB: Database page corruption on disk or a failed file read of tablespace test/t1 page"); +call mtr.add_suppression("InnoDB: Failed to read file '.*test.t1\\.ibd' at offset 3: Table is encrypted but decrypt failed"); +call mtr.add_suppression("InnoDB: The page \\[page id: space=\\d+, page number=3\\] in file '.*test.t1\\.ibd' cannot be decrypted"); +call mtr.add_suppression("InnoDB: Table in tablespace \\d+ encrypted. However key management plugin or used key_version \\d+ is not found or used encryption algorithm or method does not match. Can't continue opening the table."); +--enable_query_log + +let INNODB_PAGE_SIZE=`select @@innodb_page_size`; +CREATE TABLE t1(a BIGINT PRIMARY KEY) ENGINE=InnoDB, ENCRYPTED=YES; +INSERT INTO t1 VALUES(1); +# Force a redo log checkpoint. +let $restart_noprint=2; +--source include/restart_mysqld.inc +--source ../../suite/innodb/include/no_checkpoint_start.inc +CREATE TABLE t2(a BIGINT PRIMARY KEY) ENGINE=InnoDB, ENCRYPTED=YES; +INSERT INTO t1 VALUES(2); +SET GLOBAL innodb_flush_log_at_trx_commit=1; +INSERT INTO t2 VALUES(2); + +--let CLEANUP_IF_CHECKPOINT=DROP TABLE t1,t2; +--source ../../suite/innodb/include/no_checkpoint_end.inc + +--echo # Corrupt the pages + +perl; +my $ps = $ENV{INNODB_PAGE_SIZE}; + +my $file = "$ENV{MYSQLD_DATADIR}/test/t1.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +binmode FILE; +seek (FILE, $ENV{INNODB_PAGE_SIZE} * 3, SEEK_SET) or die "seek"; +print FILE "junk"; +close FILE or die "close"; + +$file = "$ENV{MYSQLD_DATADIR}/test/t2.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +binmode FILE; +# Corrupt pages 1 to 3. MLOG_INIT_FILE_PAGE2 should protect us! +# Unfortunately, we are not immune to page 0 corruption. +seek (FILE, $ps, SEEK_SET) or die "seek"; +print FILE chr(0xff) x ($ps * 3); +close FILE or die "close"; +EOF + +--source include/start_mysqld.inc +--error ER_UNKNOWN_STORAGE_ENGINE +SELECT * FROM t1; +let $restart_parameters=--innodb_force_recovery=1; +--source include/restart_mysqld.inc + +SELECT * FROM t1; +SELECT * FROM t2; +CHECK TABLE t1,t2; + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/r/corrupted_during_recovery.result b/mysql-test/suite/innodb/r/corrupted_during_recovery.result new file mode 100644 index 00000000000..788f17e3284 --- /dev/null +++ b/mysql-test/suite/innodb/r/corrupted_during_recovery.result @@ -0,0 +1,22 @@ +CREATE TABLE t1(a BIGINT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); +CREATE TABLE t2(a BIGINT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(2); +SET GLOBAL innodb_flush_log_at_trx_commit=1; +INSERT INTO t2 VALUES(1); +# Kill the server +# Corrupt the pages +SELECT * FROM t1; +ERROR 42000: Unknown storage engine 'InnoDB' +SELECT * FROM t1; +a +0 +2 +SELECT * FROM t2; +a +1 +CHECK TABLE t1,t2; +Table Op Msg_type Msg_text +test.t1 check status OK +test.t2 check status OK +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result index 516ac333a87..56a1df7ce5d 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result +++ b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result @@ -8,7 +8,7 @@ ALGORITHM=COPY; ERROR 01000: Data truncated for column 'i1' at row 1 ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id), ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY ALTER IGNORE TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Creating unique indexes with IGNORE requires COPY algorithm to remove duplicate rows. Try ALGORITHM=COPY SET @old_sql_mode = @@sql_mode; diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result index a65760828be..56f80a4de3e 100644 --- a/mysql-test/suite/innodb/r/innodb-alter.result +++ b/mysql-test/suite/innodb/r/innodb-alter.result @@ -515,7 +515,7 @@ ERROR 42000: Incorrect column name 'FTS_DOC_ID' ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY CREATE TABLE t1n LIKE t1o; ALTER TABLE t1n ADD FULLTEXT INDEX(ct); ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=INPLACE; @@ -588,7 +588,7 @@ t1n CREATE TABLE `t1n` ( DROP TABLE t1n; ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct, ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct; ALTER TABLE t1o CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE; diff --git a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result index daa3ffc0a9f..0855d6b5148 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result +++ b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result @@ -781,7 +781,7 @@ ERROR 42000: Incorrect column name 'FTS_Doc_ID' ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY CREATE TABLE t1n LIKE t1o; ALTER TABLE t1n ADD FULLTEXT INDEX(ct); ### files in MYSQL_DATA_DIR/test diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result index 0eb66fa22b5..30eddd51bfb 100644 --- a/mysql-test/suite/innodb/r/instant_alter.result +++ b/mysql-test/suite/innodb/r/instant_alter.result @@ -830,7 +830,7 @@ SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t1'; affected rows: 1 ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY ALTER TABLE t1 CHANGE m i INT; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 @@ -1654,7 +1654,7 @@ SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t1'; affected rows: 1 ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY ALTER TABLE t1 CHANGE m i INT; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 @@ -2478,7 +2478,7 @@ SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t1'; affected rows: 1 ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY ALTER TABLE t1 CHANGE m i INT; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 diff --git a/mysql-test/suite/innodb/r/instant_alter_charset,redundant.rdiff b/mysql-test/suite/innodb/r/instant_alter_charset,redundant.rdiff index 82a5ca95986..935b5dbf78c 100644 --- a/mysql-test/suite/innodb/r/instant_alter_charset,redundant.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter_charset,redundant.rdiff @@ -4,7 +4,7 @@ alter table boundary_255 modify b varchar(200) charset utf8mb3, algorithm=instant; --ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +-ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table boundary_255 modify c varchar(300) charset utf8mb3, algorithm=instant; diff --git a/mysql-test/suite/innodb/r/instant_alter_charset.result b/mysql-test/suite/innodb/r/instant_alter_charset.result index 268848f31ec..6242b167412 100644 --- a/mysql-test/suite/innodb/r/instant_alter_charset.result +++ b/mysql-test/suite/innodb/r/instant_alter_charset.result @@ -54,7 +54,7 @@ algorithm=inplace; alter table various_cases change a a varchar(222), algorithm=inplace; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table various_cases change b b varchar(150) as (a) virtual, algorithm=inplace; @@ -65,7 +65,7 @@ ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITH alter table various_cases modify a char(150) charset utf8mb4, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table various_cases; create table all_texts ( a tinytext charset ascii, @@ -87,17 +87,17 @@ e varbinary(150), f binary(150) ) engine=innodb; alter table all_binaries modify a tinytext, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_binaries modify b text, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_binaries modify c mediumtext, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_binaries modify d longtext, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_binaries modify e varchar(150), algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_binaries modify f char(150), algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table all_binaries; create table all_strings ( a tinytext, @@ -108,29 +108,29 @@ e varchar(150), f char(150) ) engine=innodb; alter table all_strings modify a tinyblob, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify b blob, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify c mediumblob, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify d longblob, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify e varbinary(150), algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify f binary(150), algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify a tinytext charset binary, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify b text charset binary, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify c mediumtext charset binary, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify d longtext charset binary, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify e varchar(150) charset binary, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table all_strings modify f char(150) charset binary, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table all_strings; create table key_part_change ( a char(150) charset ascii, @@ -143,7 +143,7 @@ modify a char(150) charset utf8mb4, drop index ab, add unique key ab(a,c), algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table key_part_change; create table key_part_change_and_rename ( a char(100) charset ascii, @@ -156,7 +156,7 @@ change b a char(100) charset utf8mb4, drop index ab, add unique key ab(a,b), algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table key_part_change_and_rename; create table enum_and_set ( a enum('one', 'two') charset utf8mb3, @@ -165,11 +165,11 @@ b set('three', 'four') charset utf8mb3 alter table enum_and_set modify a enum('one', 'two') charset utf8mb4, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table enum_and_set modify b enum('three', 'four') charset utf8mb4, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table enum_and_set; create table compressed ( a varchar(255) charset utf8mb3 compressed @@ -254,7 +254,7 @@ algorithm=instant; alter table boundary_255 modify b varchar(200) charset utf8mb3, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table boundary_255 modify c varchar(300) charset utf8mb3, algorithm=instant; @@ -1080,11 +1080,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb3 collate utf8mb3_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb3 collate utf8mb3_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_bin, @@ -1097,11 +1097,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset latin1 collate latin1_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset latin1 collate latin1_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_nopad_ci, @@ -1114,11 +1114,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset latin1 collate latin1_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset latin1 collate latin1_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_nopad_bin, @@ -1131,11 +1131,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset latin1 collate latin1_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset latin1 collate latin1_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1148,11 +1148,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset koi8u collate koi8u_bin, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset koi8u collate koi8u_bin, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_nopad_ci, @@ -1165,11 +1165,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset koi8u collate koi8u_bin, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset koi8u collate koi8u_bin, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_nopad_bin, @@ -1182,11 +1182,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset koi8u collate koi8u_bin, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset koi8u collate koi8u_bin, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1199,11 +1199,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset latin1 collate latin1_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset latin1 collate latin1_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_bin, @@ -1216,11 +1216,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb3 collate utf8mb3_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb3 collate utf8mb3_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_nopad_ci, @@ -1233,11 +1233,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb3 collate utf8mb3_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb3 collate utf8mb3_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_nopad_bin, @@ -1250,11 +1250,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb3 collate utf8mb3_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb3 collate utf8mb3_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1267,11 +1267,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_danish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_danish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_bin, @@ -1284,11 +1284,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_danish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_danish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_nopad_ci, @@ -1301,11 +1301,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_danish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_danish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_nopad_bin, @@ -1318,11 +1318,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_danish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_danish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset utf8mb3 collate utf8mb3_general_ci, @@ -1335,11 +1335,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_vietnamese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_vietnamese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset utf8mb3 collate utf8mb3_bin, @@ -1352,11 +1352,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_vietnamese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_vietnamese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset utf8mb3 collate utf8mb3_general_nopad_ci, @@ -1369,11 +1369,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_vietnamese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_vietnamese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset utf8mb3 collate utf8mb3_nopad_bin, @@ -1386,11 +1386,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_vietnamese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_vietnamese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1403,11 +1403,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset gbk collate gbk_chinese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset gbk collate gbk_chinese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1420,11 +1420,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset gbk collate gbk_chinese_nopad_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset gbk collate gbk_chinese_nopad_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ucs2 collate ucs2_myanmar_ci, @@ -1437,11 +1437,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf16 collate utf16_thai_520_w2, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf16 collate utf16_thai_520_w2, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ucs2 collate ucs2_general_ci, @@ -1454,11 +1454,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf16 collate utf16_unicode_nopad_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf16 collate utf16_unicode_nopad_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ucs2 collate ucs2_general_mysql500_ci, @@ -1471,11 +1471,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf16 collate utf16_spanish2_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf16 collate utf16_spanish2_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1488,11 +1488,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset ascii collate ascii_bin, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset ascii collate ascii_bin, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset utf8mb3 collate utf8mb3_roman_ci, @@ -1505,11 +1505,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb3 collate utf8mb3_lithuanian_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb3 collate utf8mb3_lithuanian_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset utf8mb4 collate utf8mb4_thai_520_w2, @@ -1522,11 +1522,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_persian_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_persian_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset utf8mb3 collate utf8mb3_myanmar_ci, @@ -1539,11 +1539,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb4 collate utf8mb4_german2_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb4 collate utf8mb4_german2_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset utf8mb3 collate utf8mb3_general_ci, @@ -1556,11 +1556,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf8mb3 collate utf8mb3_unicode_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf8mb3 collate utf8mb3_unicode_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset latin1 collate latin1_general_cs, @@ -1573,11 +1573,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset latin1 collate latin1_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset latin1 collate latin1_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1590,11 +1590,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset ujis collate ujis_japanese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset ujis collate ujis_japanese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1607,11 +1607,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset big5 collate big5_chinese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset big5 collate big5_chinese_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1624,11 +1624,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset latin2 collate latin2_croatian_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset latin2 collate latin2_croatian_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset ascii collate ascii_general_ci, @@ -1641,11 +1641,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset latin7 collate latin7_estonian_cs, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset latin7 collate latin7_estonian_cs, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(50) charset utf16 collate utf16_general_ci, @@ -1658,11 +1658,11 @@ algorithm=instant; alter table tmp modify b varchar(50) charset utf16 collate utf16_german2_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify c varchar(50) charset utf16 collate utf16_german2_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; drop table compatible_without_index; create table fully_incompatible ( @@ -1691,11 +1691,11 @@ unique key b_idx (b(150)) alter table tmp change a a varchar(150) charset utf8mb3 collate utf8mb3_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify b text charset utf8mb3 collate utf8mb3_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(150) charset utf8mb4 collate utf8mb4_general_ci, @@ -1705,11 +1705,11 @@ unique key b_idx (b(150)) alter table tmp change a a varchar(150) charset ascii collate ascii_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify b text charset ascii collate ascii_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(150) charset utf8mb3 collate utf8mb3_general_ci, @@ -1719,11 +1719,11 @@ unique key b_idx (b(150)) alter table tmp change a a varchar(150) charset ascii collate ascii_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify b text charset ascii collate ascii_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(150) charset utf8mb3 collate utf8mb3_general_ci, @@ -1733,11 +1733,11 @@ unique key b_idx (b(150)) alter table tmp change a a varchar(150) charset latin1 collate latin1_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify b text charset latin1 collate latin1_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(150) charset utf16 collate utf16_general_ci, @@ -1747,11 +1747,11 @@ unique key b_idx (b(150)) alter table tmp change a a varchar(150) charset utf32 collate utf32_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify b text charset utf32 collate utf32_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(150) charset latin1 collate latin1_general_ci, @@ -1761,11 +1761,11 @@ unique key b_idx (b(150)) alter table tmp change a a varchar(150) charset ascii collate ascii_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify b text charset ascii collate ascii_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(150) charset ascii collate ascii_general_ci, @@ -1775,11 +1775,11 @@ unique key b_idx (b(150)) alter table tmp change a a varchar(150) charset swe7 collate swe7_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify b text charset swe7 collate swe7_swedish_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(150) charset eucjpms collate eucjpms_japanese_nopad_ci, @@ -1789,11 +1789,11 @@ unique key b_idx (b(150)) alter table tmp change a a varchar(150) charset geostd8 collate geostd8_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify b text charset geostd8 collate geostd8_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; create table tmp ( a varchar(150) charset latin1 collate latin1_general_ci, @@ -1803,10 +1803,10 @@ unique key b_idx (b(150)) alter table tmp change a a varchar(150) charset utf16 collate utf16_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY alter table tmp modify b text charset utf16 collate utf16_general_ci, algorithm=instant; -ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY drop table tmp; drop table fully_incompatible; diff --git a/mysql-test/suite/innodb/r/instant_alter_extend,utf8.rdiff b/mysql-test/suite/innodb/r/instant_alter_extend,utf8.rdiff index ad7ad0e1c66..596dfe43ab8 100644 --- a/mysql-test/suite/innodb/r/instant_alter_extend,utf8.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter_extend,utf8.rdiff @@ -17,7 +17,7 @@ +a 13 2100FE 660 # Convert from VARCHAR to a bigger CHAR alter table t modify a varchar(200), algorithm=instant; - ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY + ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY @@ -72,7 +72,7 @@ test.t check status OK call check_table('t'); diff --git a/mysql-test/suite/innodb/r/instant_alter_extend.result b/mysql-test/suite/innodb/r/instant_alter_extend.result Binary files differindex ca8e566f19c..fb03ef9a182 100644 --- a/mysql-test/suite/innodb/r/instant_alter_extend.result +++ b/mysql-test/suite/innodb/r/instant_alter_extend.result diff --git a/mysql-test/suite/innodb/r/instant_alter_limit,32k.rdiff b/mysql-test/suite/innodb/r/instant_alter_limit,32k.rdiff new file mode 100644 index 00000000000..5e46c66ce73 --- /dev/null +++ b/mysql-test/suite/innodb/r/instant_alter_limit,32k.rdiff @@ -0,0 +1,9 @@ +--- instant_alter_limit.result ++++ instant_alter_limit.result +@@ -42,5 +42,5 @@ + FROM information_schema.global_status + WHERE variable_name = 'innodb_instant_alter_column'; + instants +-502 ++506 + DROP TABLE t; diff --git a/mysql-test/suite/innodb/r/instant_alter_limit,4k.rdiff b/mysql-test/suite/innodb/r/instant_alter_limit,4k.rdiff new file mode 100644 index 00000000000..795116ffae4 --- /dev/null +++ b/mysql-test/suite/innodb/r/instant_alter_limit,4k.rdiff @@ -0,0 +1,9 @@ +--- instant_alter_limit.result ++++ instant_alter_limit.result +@@ -42,5 +42,5 @@ + FROM information_schema.global_status + WHERE variable_name = 'innodb_instant_alter_column'; + instants +-502 ++474 + DROP TABLE t; diff --git a/mysql-test/suite/innodb/r/instant_alter_limit,64k.rdiff b/mysql-test/suite/innodb/r/instant_alter_limit,64k.rdiff new file mode 100644 index 00000000000..5e46c66ce73 --- /dev/null +++ b/mysql-test/suite/innodb/r/instant_alter_limit,64k.rdiff @@ -0,0 +1,9 @@ +--- instant_alter_limit.result ++++ instant_alter_limit.result +@@ -42,5 +42,5 @@ + FROM information_schema.global_status + WHERE variable_name = 'innodb_instant_alter_column'; + instants +-502 ++506 + DROP TABLE t; diff --git a/mysql-test/suite/innodb/r/instant_alter_limit,8k.rdiff b/mysql-test/suite/innodb/r/instant_alter_limit,8k.rdiff new file mode 100644 index 00000000000..37d2ae67c4e --- /dev/null +++ b/mysql-test/suite/innodb/r/instant_alter_limit,8k.rdiff @@ -0,0 +1,9 @@ +--- instant_alter_limit.result ++++ instant_alter_limit.result +@@ -42,5 +42,5 @@ + FROM information_schema.global_status + WHERE variable_name = 'innodb_instant_alter_column'; + instants +-502 ++492 + DROP TABLE t; diff --git a/mysql-test/suite/innodb/r/instant_alter_limit.result b/mysql-test/suite/innodb/r/instant_alter_limit.result index e66579e4a94..e169c40d462 100644 --- a/mysql-test/suite/innodb/r/instant_alter_limit.result +++ b/mysql-test/suite/innodb/r/instant_alter_limit.result @@ -32,4 +32,15 @@ instants SELECT * FROM t; a b 1 0 +ALTER TABLE t ADD COLUMN (c CHAR(255) NOT NULL, d BIGINT NOT NULL), +ALGORITHM=INSTANT; +UPDATE t SET b=b+1,d=d+1,c='foo'; +SELECT * FROM t; +a b c d +1 1 foo 1 +SELECT variable_value-@old_instant instants +FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'; +instants +502 DROP TABLE t; diff --git a/mysql-test/suite/innodb/r/log_alter_table.result b/mysql-test/suite/innodb/r/log_alter_table.result index 55e6b84de16..9de89bebaa6 100644 --- a/mysql-test/suite/innodb/r/log_alter_table.result +++ b/mysql-test/suite/innodb/r/log_alter_table.result @@ -8,12 +8,12 @@ # CREATE TABLE t1 (a INT NOT NULL, b INT UNIQUE) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,2); -ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; -ALTER TABLE t1 DROP INDEX b, ADD INDEX (b); +ALTER TABLE t1 ADD PRIMARY KEY(a), LOCK=SHARED, ALGORITHM=INPLACE; +ALTER TABLE t1 DROP INDEX b, ADD INDEX (b), LOCK=SHARED; # Kill the server # restart: --debug=d,ib_log -FOUND 1 /scan .*: multi-log rec MLOG_FILE_CREATE2.*page .*:0/ in mysqld.1.err -FOUND 1 /scan .*: log rec MLOG_INDEX_LOAD/ in mysqld.1.err +FOUND 2 /scan \d+: multi-log rec MLOG_FILE_CREATE2 len \d+ page \d+:0/ in mysqld.1.err +FOUND 3 /scan \d+: log rec MLOG_INDEX_LOAD/ in mysqld.1.err CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK diff --git a/mysql-test/suite/innodb/t/corrupted_during_recovery.opt b/mysql-test/suite/innodb/t/corrupted_during_recovery.opt new file mode 100644 index 00000000000..6051f4cd1fa --- /dev/null +++ b/mysql-test/suite/innodb/t/corrupted_during_recovery.opt @@ -0,0 +1 @@ +--innodb_doublewrite=0 diff --git a/mysql-test/suite/innodb/t/corrupted_during_recovery.test b/mysql-test/suite/innodb/t/corrupted_during_recovery.test new file mode 100644 index 00000000000..697d6e2dce0 --- /dev/null +++ b/mysql-test/suite/innodb/t/corrupted_during_recovery.test @@ -0,0 +1,61 @@ +--source include/have_innodb.inc + +--disable_query_log +call mtr.add_suppression("InnoDB: Plugin initialization aborted"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error"); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed"); +call mtr.add_suppression("InnoDB: Database page corruption on disk or a failed file read of tablespace test/t1 page"); +call mtr.add_suppression("InnoDB: Failed to read file '.*test.t1\\.ibd' at offset 3: Page read from tablespace is corrupted."); +--enable_query_log + +let INNODB_PAGE_SIZE=`select @@innodb_page_size`; +CREATE TABLE t1(a BIGINT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); +# Force a redo log checkpoint. +let $restart_noprint=2; +--source include/restart_mysqld.inc +--source ../include/no_checkpoint_start.inc +CREATE TABLE t2(a BIGINT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(2); +SET GLOBAL innodb_flush_log_at_trx_commit=1; +INSERT INTO t2 VALUES(1); + +--let CLEANUP_IF_CHECKPOINT=DROP TABLE t1,t2; +--source ../include/no_checkpoint_end.inc + +--echo # Corrupt the pages + +perl; +my $ps = $ENV{INNODB_PAGE_SIZE}; + +my $file = "$ENV{MYSQLD_DATADIR}/test/t1.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +binmode FILE; +sysseek(FILE, 3*$ps, 0) || die "Unable to seek $file\n"; +die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; +# Replace the a=1 with a=0. +$page =~ s/\x80\x0\x0\x0\x0\x0\x0\x1/\x80\x0\x0\x0\x0\x0\x0\x0/; +sysseek(FILE, 3*$ps, 0) || die "Unable to seek $file\n"; +syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; +close FILE or die "close"; + +$file = "$ENV{MYSQLD_DATADIR}/test/t2.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +binmode FILE; +# Corrupt pages 1 to 3. MLOG_INIT_FILE_PAGE2 should protect us! +# Unfortunately, we are not immune to page 0 corruption. +seek (FILE, $ps, SEEK_SET) or die "seek"; +print FILE chr(0xff) x ($ps * 3); +close FILE or die "close"; +EOF + +--source include/start_mysqld.inc +--error ER_UNKNOWN_STORAGE_ENGINE +SELECT * FROM t1; +let $restart_parameters=--innodb_force_recovery=1; +--source include/restart_mysqld.inc +SELECT * FROM t1; +SELECT * FROM t2; +CHECK TABLE t1,t2; + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/t/instant_alter_extend.test b/mysql-test/suite/innodb/t/instant_alter_extend.test index 6b528c1d30c..4320d9bae05 100644 --- a/mysql-test/suite/innodb/t/instant_alter_extend.test +++ b/mysql-test/suite/innodb/t/instant_alter_extend.test @@ -8,18 +8,18 @@ -- echo # # Use character-set-server in test db -create or replace database test; -use test; +create database best; +use best; set default_storage_engine=innodb; set @bigval= repeat('0123456789', 30); delimiter ~~; -create or replace procedure check_table(table_name varchar(255)) +create procedure check_table(table_name varchar(255)) begin select table_id into @table_id from information_schema.innodb_sys_tables - where name = concat('test/', table_name); + where name = concat('best/', table_name); select name, mtype, hex(prtype) as prtype, len from information_schema.innodb_sys_columns where table_id = @table_id; @@ -30,7 +30,7 @@ delimiter ;~~ --echo # VARCHAR -> CHAR, VARBINARY -> BINARY conversion set @bigval= repeat('0123456789', 20); -create or replace table t (a varchar(300)); +create table t (a varchar(300)); --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON alter table t modify a char(255), algorithm=instant; alter table t modify a char(255), algorithm=copy; @@ -166,8 +166,8 @@ create or replace table t1 (x tinyint); insert into t1 set x= 42; alter table t1 modify x int; flush tables t1 for export; ---move_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg ---copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd +--move_file $MYSQLD_DATADIR/best/t1.cfg $MYSQLD_DATADIR/best/t2.cfg +--copy_file $MYSQLD_DATADIR/best/t1.ibd $MYSQLD_DATADIR/best/t2.ibd unlock tables; alter table t2 import tablespace; @@ -246,4 +246,4 @@ alter table t1 modify a char(20); select * from t1; check table t1; -create or replace database test charset latin1; +drop database best; diff --git a/mysql-test/suite/innodb/t/instant_alter_limit.test b/mysql-test/suite/innodb/t/instant_alter_limit.test index ded14eee89b..b50a1b15295 100644 --- a/mysql-test/suite/innodb/t/instant_alter_limit.test +++ b/mysql-test/suite/innodb/t/instant_alter_limit.test @@ -1,4 +1,4 @@ ---source include/have_innodb.inc +--source include/innodb_page_size.inc SET @old_instant= (SELECT variable_value FROM information_schema.global_status @@ -38,5 +38,23 @@ FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; SELECT * FROM t; +ALTER TABLE t ADD COLUMN (c CHAR(255) NOT NULL, d BIGINT NOT NULL), +ALGORITHM=INSTANT; + +--disable_query_log +let $n=253; +while ($n) { +dec $n; +ALTER TABLE t DROP b, DROP c, DROP d, +ADD COLUMN (b INT NOT NULL, c CHAR(255) NOT NULL, d BIGINT NOT NULL); +} +--enable_query_log + +UPDATE t SET b=b+1,d=d+1,c='foo'; +SELECT * FROM t; + +SELECT variable_value-@old_instant instants +FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'; DROP TABLE t; diff --git a/mysql-test/suite/innodb/t/log_alter_table.test b/mysql-test/suite/innodb/t/log_alter_table.test index 6f12dfaf0b9..c92953f16a1 100644 --- a/mysql-test/suite/innodb/t/log_alter_table.test +++ b/mysql-test/suite/innodb/t/log_alter_table.test @@ -19,9 +19,9 @@ CREATE TABLE t1 (a INT NOT NULL, b INT UNIQUE) ENGINE=InnoDB; # MLOG_INDEX_LOAD will not be emitted for empty tables. Insert a row. INSERT INTO t1 VALUES (1,2); # We should get two MLOG_INDEX_LOAD for this. -ALTER TABLE t1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; +ALTER TABLE t1 ADD PRIMARY KEY(a), LOCK=SHARED, ALGORITHM=INPLACE; # And one MLOG_INDEX_LOAD for this. -ALTER TABLE t1 DROP INDEX b, ADD INDEX (b); +ALTER TABLE t1 DROP INDEX b, ADD INDEX (b), LOCK=SHARED; --let CLEANUP_IF_CHECKPOINT=DROP TABLE t1; --source include/no_checkpoint_end.inc @@ -32,10 +32,10 @@ ALTER TABLE t1 DROP INDEX b, ADD INDEX (b); let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err; let SEARCH_ABORT=NOT FOUND; # ensure that we have exactly 2 records there. -let SEARCH_PATTERN=scan .*: multi-log rec MLOG_FILE_CREATE2.*page .*:0; +let SEARCH_PATTERN=scan \d+: multi-log rec MLOG_FILE_CREATE2 len \d+ page \d+:0; --source include/search_pattern_in_file.inc # ensure that we have exactly 3 records there. -let SEARCH_PATTERN=scan .*: log rec MLOG_INDEX_LOAD; +let SEARCH_PATTERN=scan \d+: log rec MLOG_INDEX_LOAD; --source include/search_pattern_in_file.inc CHECK TABLE t1; diff --git a/mysql-test/suite/period/r/alter.result b/mysql-test/suite/period/r/alter.result index c487b4a0d7f..e202ba2698f 100644 --- a/mysql-test/suite/period/r/alter.result +++ b/mysql-test/suite/period/r/alter.result @@ -1,6 +1,6 @@ set @s= '1992-01-01'; set @e= '1999-12-31'; -create or replace table t (s date, e date); +create table t (s date, e date); # period start/end columns are implicit NOT NULL alter table t add period for a(s, e); show create table t; @@ -96,9 +96,11 @@ t1 CREATE TABLE `t1` ( `s1` date NOT NULL, PERIOD FOR `b` (`s1`, `e`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; create table t2 (period for b(s,e)) select * from t; ERROR 23000: CONSTRAINT `b` failed for `test`.`t2` create table t2 (period for b(s1,e)) select * from t; +drop table t2; # SQL16 11.27 <add table period definition>, Syntax Rules, 5)g) # The declared type of BC1 shall be either DATE or a timestamp type # and shall be equivalent to the declared type of BC2. @@ -171,4 +173,4 @@ ERROR 23000: CONSTRAINT `mytime_1` failed for `test`.`t` alter table t add constraint mytime_1 check (x > 2); insert t values (3, @e, @s); ERROR 23000: CONSTRAINT `mytime_2` failed for `test`.`t` -create or replace database test; +drop table t; diff --git a/mysql-test/suite/period/r/create.result b/mysql-test/suite/period/r/create.result index 7f06a433ad2..8cedb23465d 100644 --- a/mysql-test/suite/period/r/create.result +++ b/mysql-test/suite/period/r/create.result @@ -1,5 +1,4 @@ -create or replace table t (id int primary key, s date, e date, -period for mytime(s,e)); +create table t (id int primary key, s date, e date, period for mytime(s,e)); # CONSTRAINT CHECK (s < e) is added implicitly, and shouldn't be shown # this is important for correct command-based replication show create table t; @@ -96,4 +95,4 @@ ERROR 23000: CONSTRAINT `mytime_1` failed for `test`.`t` show status like "Feature_application_time_periods"; Variable_name Value Feature_application_time_periods 6 -create or replace database test; +drop table t; diff --git a/mysql-test/suite/period/r/delete.result b/mysql-test/suite/period/r/delete.result index 7aa8ed455a7..428200a4564 100644 --- a/mysql-test/suite/period/r/delete.result +++ b/mysql-test/suite/period/r/delete.result @@ -1,4 +1,4 @@ -create or replace table t (id int, s date, e date, period for apptime(s,e)); +create table t (id int, s date, e date, period for apptime(s,e)); insert into t values(1, '1999-01-01', '2018-12-12'); insert into t values(1, '1999-01-01', '2017-01-01'); insert into t values(1, '2017-01-01', '2019-01-01'); @@ -353,4 +353,6 @@ id s e datediff(e, s) 1 1999-01-01 1999-01-03 2 1 2018-12-10 2018-12-12 2 2 1999-01-01 1999-01-03 2 -create or replace database test; +drop table t,t2,t3,log_tbl; +drop view v; +drop procedure log; diff --git a/mysql-test/suite/period/r/update.result b/mysql-test/suite/period/r/update.result index f9aeda170df..b86537fc9fb 100644 --- a/mysql-test/suite/period/r/update.result +++ b/mysql-test/suite/period/r/update.result @@ -1,4 +1,4 @@ -create or replace table t (id int, s date, e date, period for apptime(s,e)); +create table t (id int, s date, e date, period for apptime(s,e)); insert into t values(1, '1999-01-01', '2018-12-12'); insert into t values(1, '1999-01-01', '2017-01-01'); insert into t values(1, '2017-01-01', '2019-01-01'); @@ -268,4 +268,9 @@ x s e xs xe xs=s and xe=e create or replace table t1 (f int, s date, e date, period for app(s,e)); insert into t1 values (1,'2016-09-21','2019-06-14'); update ignore t1 for portion of app from '2019-03-13' to '2019-03-14' set f = 1; -create or replace database test; +drop table t,t1,t2,log_tbl; +drop view v1; +drop function f; +drop function g; +drop function h; +drop procedure log; diff --git a/mysql-test/suite/period/r/versioning.result b/mysql-test/suite/period/r/versioning.result index 779bf5cb152..efb7a646e87 100644 --- a/mysql-test/suite/period/r/versioning.result +++ b/mysql-test/suite/period/r/versioning.result @@ -1,5 +1,5 @@ # DELETE -create or replace table t ( +create table t ( s date, e date, row_start SYS_TYPE as row start invisible, row_end SYS_TYPE as row end invisible, @@ -86,4 +86,9 @@ x s e if(row_start = @ins_time, "OLD", "NEW") check_row(row_start, row_end) 1 2018-01-01 2018-12-12 NEW CURRENT ROW 2 1999-01-01 1999-12-12 OLD CURRENT ROW 6 2000-01-01 2018-01-01 NEW CURRENT ROW -create or replace database test; +drop table t,log_tbl; +drop function check_row; +drop function current_row; +drop procedure verify_trt; +drop procedure verify_trt_dummy; +drop procedure log; diff --git a/mysql-test/suite/period/t/alter.test b/mysql-test/suite/period/t/alter.test index 4d504369859..3f45d68cd61 100644 --- a/mysql-test/suite/period/t/alter.test +++ b/mysql-test/suite/period/t/alter.test @@ -1,7 +1,7 @@ set @s= '1992-01-01'; set @e= '1999-12-31'; -create or replace table t (s date, e date); +create table t (s date, e date); --echo # period start/end columns are implicit NOT NULL alter table t add period for a(s, e); @@ -58,11 +58,13 @@ insert t(s, s1, e) values(@e, @e, @s); create table t1 like t; show create table t1; +drop table t1; --error ER_CONSTRAINT_FAILED create table t2 (period for b(s,e)) select * from t; create table t2 (period for b(s1,e)) select * from t; +drop table t2; --echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)g) --echo # The declared type of BC1 shall be either DATE or a timestamp type @@ -128,4 +130,4 @@ alter table t add constraint mytime_1 check (x > 2); --error ER_CONSTRAINT_FAILED insert t values (3, @e, @s); -create or replace database test; +drop table t; diff --git a/mysql-test/suite/period/t/create.test b/mysql-test/suite/period/t/create.test index c55f8fe31f7..2e3de795698 100644 --- a/mysql-test/suite/period/t/create.test +++ b/mysql-test/suite/period/t/create.test @@ -1,5 +1,4 @@ -create or replace table t (id int primary key, s date, e date, - period for mytime(s,e)); +create table t (id int primary key, s date, e date, period for mytime(s,e)); --echo # CONSTRAINT CHECK (s < e) is added implicitly, and shouldn't be shown --echo # this is important for correct command-based replication show create table t; @@ -79,4 +78,4 @@ insert t values (2, '2001-01-01', '2001-01-01'); show status like "Feature_application_time_periods"; -create or replace database test; +drop table t; diff --git a/mysql-test/suite/period/t/delete.test b/mysql-test/suite/period/t/delete.test index 105aebed22c..00bc314160f 100644 --- a/mysql-test/suite/period/t/delete.test +++ b/mysql-test/suite/period/t/delete.test @@ -1,7 +1,7 @@ source suite/period/engines.inc; source include/have_log_bin.inc; -create or replace table t (id int, s date, e date, period for apptime(s,e)); +create table t (id int, s date, e date, period for apptime(s,e)); insert into t values(1, '1999-01-01', '2018-12-12'); insert into t values(1, '1999-01-01', '2017-01-01'); @@ -181,4 +181,6 @@ delete from t for portion of apptime from '1999-01-03' to '2018-12-10'; --sorted_result select *, datediff(e, s) from t; -create or replace database test; +drop table t,t2,t3,log_tbl; +drop view v; +drop procedure log; diff --git a/mysql-test/suite/period/t/update.test b/mysql-test/suite/period/t/update.test index 66590872583..5730387dfda 100644 --- a/mysql-test/suite/period/t/update.test +++ b/mysql-test/suite/period/t/update.test @@ -1,7 +1,7 @@ source suite/period/engines.inc; source include/have_log_bin.inc; -create or replace table t (id int, s date, e date, period for apptime(s,e)); +create table t (id int, s date, e date, period for apptime(s,e)); insert into t values(1, '1999-01-01', '2018-12-12'); insert into t values(1, '1999-01-01', '2017-01-01'); @@ -157,4 +157,9 @@ create or replace table t1 (f int, s date, e date, period for app(s,e)); insert into t1 values (1,'2016-09-21','2019-06-14'); update ignore t1 for portion of app from '2019-03-13' to '2019-03-14' set f = 1; -create or replace database test; +drop table t,t1,t2,log_tbl; +drop view v1; +drop function f; +drop function g; +drop function h; +drop procedure log; diff --git a/mysql-test/suite/period/t/versioning.test b/mysql-test/suite/period/t/versioning.test index 298f49efe13..ea20344515d 100644 --- a/mysql-test/suite/period/t/versioning.test +++ b/mysql-test/suite/period/t/versioning.test @@ -3,7 +3,7 @@ source suite/versioning/common.inc; --echo # DELETE --replace_result $sys_datatype_expl SYS_TYPE -eval create or replace table t ( +eval create table t ( s date, e date, row_start $sys_datatype_expl as row start invisible, row_end $sys_datatype_expl as row end invisible, @@ -56,4 +56,9 @@ select *, if(row_start = @ins_time, "OLD", "NEW"), check_row(row_start, row_end) from t for system_time all order by x, s, e, row_start; -create or replace database test; +drop table t,log_tbl; +drop function check_row; +drop function current_row; +drop procedure verify_trt; +drop procedure verify_trt_dummy; +drop procedure log; diff --git a/mysql-test/suite/storage_engine/alter_table_online.result b/mysql-test/suite/storage_engine/alter_table_online.result index 574c46f8cda..2a27c5d108e 100644 --- a/mysql-test/suite/storage_engine/alter_table_online.result +++ b/mysql-test/suite/storage_engine/alter_table_online.result @@ -24,7 +24,7 @@ CREATE TABLE t1 (a <INT_COLUMN>, b <INT_COLUMN>, c <CHAR_COLUMN>) ENGINE=<STORAG INSERT INTO t1 (a,b,c) VALUES (1,100,'a'),(2,200,'b'),(3,300,'c'); ALTER ONLINE TABLE t1 DROP COLUMN b, ADD b <INT_COLUMN>; ALTER ONLINE TABLE t1 MODIFY b BIGINT <CUSTOM_COL_OPTIONS>; -ERROR 0A000: LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED. +ERROR 0A000: LOCK=NONE is not supported. Reason: Cannot change column type. Try LOCK=SHARED. ALTER ONLINE TABLE t1 ENGINE=MEMORY; ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. DROP TABLE t1; diff --git a/mysql-test/suite/sys_vars/r/slave_transaction_retry_errors.result b/mysql-test/suite/sys_vars/r/slave_transaction_retry_errors.result index a7815bb3f78..1e2f21ddcd1 100644 --- a/mysql-test/suite/sys_vars/r/slave_transaction_retry_errors.result +++ b/mysql-test/suite/sys_vars/r/slave_transaction_retry_errors.result @@ -1,20 +1,20 @@ select @@global.slave_transaction_retry_errors; @@global.slave_transaction_retry_errors -1213,1205,10,20,400 +1158,1159,1160,1161,1205,1213,1429,2013,12701,10,20,400 select @@session.slave_transaction_retry_errors; ERROR HY000: Variable 'slave_transaction_retry_errors' is a GLOBAL variable show global variables like 'slave_transaction_retry_errors'; Variable_name Value -slave_transaction_retry_errors 1213,1205,10,20,400 +slave_transaction_retry_errors 1158,1159,1160,1161,1205,1213,1429,2013,12701,10,20,400 show session variables like 'slave_transaction_retry_errors'; Variable_name Value -slave_transaction_retry_errors 1213,1205,10,20,400 +slave_transaction_retry_errors 1158,1159,1160,1161,1205,1213,1429,2013,12701,10,20,400 select * from information_schema.global_variables where variable_name='slave_transaction_retry_errors'; VARIABLE_NAME VARIABLE_VALUE -SLAVE_TRANSACTION_RETRY_ERRORS 1213,1205,10,20,400 +SLAVE_TRANSACTION_RETRY_ERRORS 1158,1159,1160,1161,1205,1213,1429,2013,12701,10,20,400 select * from information_schema.session_variables where variable_name='slave_transaction_retry_errors'; VARIABLE_NAME VARIABLE_VALUE -SLAVE_TRANSACTION_RETRY_ERRORS 1213,1205,10,20,400 +SLAVE_TRANSACTION_RETRY_ERRORS 1158,1159,1160,1161,1205,1213,1429,2013,12701,10,20,400 set global slave_transaction_retry_errors=1; ERROR HY000: Variable 'slave_transaction_retry_errors' is a read only variable set session slave_transaction_retry_errors=1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index bbe01baa380..6a1dacfe67b 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -4676,12 +4676,12 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SLAVE_TRANSACTION_RETRY_ERRORS SESSION_VALUE NULL -GLOBAL_VALUE 1213,1205 +GLOBAL_VALUE 1158,1159,1160,1161,1205,1213,1429,2013,12701 GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE VARIABLE_SCOPE GLOBAL VARIABLE_TYPE VARCHAR -VARIABLE_COMMENT Tells the slave thread to retry transaction for replication when a query event returns an error from the provided list. Deadlock and elapsed lock wait timeout errors are automatically added to this list +VARIABLE_COMMENT Tells the slave thread to retry transaction for replication when a query event returns an error from the provided list. Deadlock error, elapsed lock wait timeout, net read error, net read timeout, net write error, net write timeout, connect error and 2 types of lost connection error are automatically added to this list NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL |