diff options
author | Sergei Golubchik <serg@mariadb.org> | 2021-02-12 17:44:22 +0100 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2021-02-12 17:44:22 +0100 |
commit | 00a313ecf329098d4b31a42030b9b5c6f80a6031 (patch) | |
tree | f4983a6629b04563c48fa7276686dfc2187efb5f /mysql-test/main | |
parent | ef5adf520760536c7396bdfe884fc509ac065694 (diff) | |
parent | 691f93d6d17603f11a0c90f64e94b7ce9d187db4 (diff) | |
download | mariadb-git-00a313ecf329098d4b31a42030b9b5c6f80a6031.tar.gz |
Merge branch 'bb-10.3-release' into bb-10.4-release
Note, the fix for "MDEV-23328 Server hang due to Galera lock conflict resolution"
was null-merged. 10.4 version of the fix is coming up separately
Diffstat (limited to 'mysql-test/main')
43 files changed, 1216 insertions, 143 deletions
diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index 2905103b707..39ede1f90b9 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -1,7 +1,4 @@ call mtr.add_suppression("table or database name 't-1'"); -drop table if exists t1,t2,t3,t4,t5; -drop database if exists mysqltest; -drop view if exists v1; create table t1 (b char(0)); insert into t1 values (""),(null); select * from t1; @@ -1225,7 +1222,7 @@ drop table if exists t1,t2,t3; # Fix modified for MariaDB: we support this syntax create table t1 (a int) transactional=0; Warnings: -Warning 1478 Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1' +Warning 1478 Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=0' create table t2 (a int) page_checksum=1; create table t3 (a int) row_format=page; drop table t1,t2,t3; @@ -2001,19 +1998,42 @@ alter table t1 add key xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx0064 (f64) comment 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy'; ERROR HY000: Cannot create table `t1`: index information is too long. Decrease number of indexes or use shorter index names or shorter comments. drop table t1; -End of 5.5 tests +# +# End of 5.5 tests +# +# +# MDEV-4880 Attempt to create a table without columns produces ER_ILLEGAL_HA instead of ER_TABLE_MUST_HAVE_COLUMNS +# create table t1; ERROR 42000: A table must have at least 1 column +# +# MDEV-11231 Server crashes in check_duplicate_key on CREATE TABLE ... SELECT +# create table t1 (i int, j int, key(i), key(i)) as select 1 as i, 2 as j; Warnings: Note 1831 Duplicate index `i_2`. This is deprecated and will be disallowed in a future release drop table t1; +# +# End of 10.0 tests +# +# +# MDEV-18428 Memory: If transactional=0 is specified in CREATE TABLE, it is not possible to ALTER TABLE +# +create table t1 (c int(10) unsigned) engine=memory transactional=0; +ERROR HY000: Table storage engine 'MEMORY' does not support the create option 'TRANSACTIONAL=0' +# +# End of 10.2 tests +# +# +# MDEV-17544 No warning when trying to name a primary key constraint. +# CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAINT `bar` UNIQUE KEY(id2)); Warnings: Warning 1280 Name 'foo' ignored for PRIMARY key. DROP TABLE t1; # -# 10.4 Test +# End of 10.3 tests +# # # MDEV-21017: Assertion `!is_set() || (m_status == DA_OK_BULK && # is_bulk_op())' failed or late ER_PERIOD_FIELD_WRONG_ATTRIBUTES @@ -2026,4 +2046,6 @@ e DATE, PERIOD FOR app(s,e)); ERROR HY000: Period field `s` cannot be GENERATED ALWAYS AS UNLOCK TABLES; DROP TABLE t1; -# End of 10.4 Test +# +# End of 10.4 tests +# diff --git a/mysql-test/main/create.test b/mysql-test/main/create.test index a6a6ba0c782..adac81f02c5 100644 --- a/mysql-test/main/create.test +++ b/mysql-test/main/create.test @@ -5,12 +5,6 @@ call mtr.add_suppression("table or database name 't-1'"); # Check some special create statements. # ---disable_warnings -drop table if exists t1,t2,t3,t4,t5; -drop database if exists mysqltest; -drop view if exists v1; ---enable_warnings - create table t1 (b char(0)); insert into t1 values (""),(null); select * from t1; @@ -29,30 +23,30 @@ drop table t1; # Test of some CREATE TABLE'S that should fail # ---error 1146 +--error ER_NO_SUCH_TABLE create table t2 engine=heap select * from t1; ---error 1146 +--error ER_NO_SUCH_TABLE create table t2 select auto+1 from t1; drop table if exists t1,t2; ---error 1167 +--error ER_WRONG_KEY_COLUMN create table t1 (b char(0) not null, index(b)); ---error 1163 +--error ER_TABLE_CANT_HANDLE_BLOB create table t1 (a int not null,b text) engine=heap; drop table if exists t1; ---error 1075 +--error ER_WRONG_AUTO_KEY create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=heap; --- error 1049 +--error ER_BAD_DB_ERROR create table not_existing_database.test (a int); create table `a/a` (a int); show create table `a/a`; create table t1 like `a/a`; drop table `a/a`; drop table `t1`; ---error 1103 +--error ER_WRONG_TABLE_NAME create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int); ---error 1059 +--error ER_TOO_LONG_IDENT create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int); # @@ -62,17 +56,17 @@ create table t1 (a datetime default now()); drop table t1; create table t1 (a datetime on update now()); drop table t1; ---error 1067 +--error ER_INVALID_DEFAULT create table t1 (a int default 100 auto_increment); ---error 1067 +--error ER_INVALID_DEFAULT create table t1 (a tinyint default 1000); ---error 1067 +--error ER_INVALID_DEFAULT create table t1 (a varchar(5) default 'abcdef'); create table t1 (a varchar(5) default 'abcde'); insert into t1 values(); select * from t1; ---error 1067 +--error ER_INVALID_DEFAULT SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR alter table t1 alter column a set default 'abcdef'; drop table t1; @@ -97,13 +91,13 @@ create table mysqltest.test2$ (a int); drop table mysqltest.test2$; drop database mysqltest; ---error 1103 +--error ER_WRONG_TABLE_NAME create table `` (a int); ---error 1103 +--error ER_WRONG_TABLE_NAME drop table if exists ``; ---error 1166 +--error ER_WRONG_COLUMN_NAME create table t1 (`` int); ---error 1280 +--error ER_WRONG_NAME_FOR_INDEX create table t1 (i int, index `` (i)); # @@ -158,13 +152,13 @@ create table t2 (a int) select * from t1; describe t1; describe t2; drop table if exists t2; ---error 1060 +--error ER_DUP_FIELDNAME create table t2 (a int, a float) select * from t1; drop table if exists t2; ---error 1060 +--error ER_DUP_FIELDNAME create table t2 (a int) select a as b, a+1 as b from t1; drop table if exists t2; ---error 1060 +--error ER_DUP_FIELDNAME create table t2 (b int) select a as b, a+1 as b from t1; drop table if exists t1,t2; @@ -176,7 +170,7 @@ CREATE TABLE t1 (a int not null); INSERT INTO t1 values (1),(2),(1); --error ER_DUP_ENTRY CREATE TABLE t2 (primary key(a)) SELECT * FROM t1; ---error 1146 +--error ER_NO_SUCH_TABLE SELECT * from t2; DROP TABLE t1; DROP TABLE IF EXISTS t2; @@ -202,7 +196,7 @@ SELECT @@storage_engine; CREATE TABLE t1 (a int not null); show create table t1; drop table t1; ---error 1286 +--error ER_UNKNOWN_STORAGE_ENGINE SET SESSION storage_engine="gemini"; SELECT @@storage_engine; CREATE TABLE t1 (a int not null); @@ -216,11 +210,11 @@ drop table t1; # create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2)); insert into t1 values ("a", 1), ("b", 2); ---error 1048 +--error ER_BAD_NULL_ERROR insert into t1 values ("c", NULL); ---error 1048 +--error ER_BAD_NULL_ERROR insert into t1 values (NULL, 3); ---error 1048 +--error ER_BAD_NULL_ERROR insert into t1 values (NULL, NULL); drop table t1; @@ -262,11 +256,11 @@ drop table t1; # "Table truncated when creating another table name with Spaces" # ---error 1103 +--error ER_WRONG_TABLE_NAME create table `t1 `(a int); ---error 1102 +--error ER_WRONG_DB_NAME create database `db1 `; ---error 1166 +--error ER_WRONG_COLUMN_NAME create table t1(`a ` int); # @@ -274,11 +268,11 @@ create table t1(`a ` int); # "Parser permits multiple commas without syntax error" # ---error 1064 +--error ER_PARSE_ERROR create table t1 (a int,); ---error 1064 +--error ER_PARSE_ERROR create table t1 (a int,,b int); ---error 1064 +--error ER_PARSE_ERROR create table t1 (,b int); # @@ -320,13 +314,13 @@ create table t2 like t3; show create table t2; select * from t2; create table t3 like t1; ---error 1050 +--error ER_TABLE_EXISTS_ERROR create table t3 like mysqltest.t3; ---error 1049 +--error ER_BAD_DB_ERROR create table non_existing_database.t1 like t1; --error ER_NO_SUCH_TABLE create table t4 like non_existing_table; ---error 1050 +--error ER_TABLE_EXISTS_ERROR create temporary table t3 like t1; drop table t1, t2, t3; drop table t3; @@ -360,7 +354,7 @@ SELECT @@storage_engine; CREATE TABLE t1 (a int not null); show create table t1; drop table t1; ---error 1286 +--error ER_UNKNOWN_STORAGE_ENGINE SET SESSION storage_engine="gemini"; SELECT @@storage_engine; CREATE TABLE t1 (a int not null); @@ -480,9 +474,9 @@ use test; # Test for Bug 856 'Naming a key "Primary" causes trouble' # ---error 1280 +--error ER_WRONG_NAME_FOR_INDEX create table t1 (a int, index `primary` (a)); ---error 1280 +--error ER_WRONG_NAME_FOR_INDEX create table t1 (a int, index `PRIMARY` (a)); create table t1 (`primary` int, index(`primary`)); @@ -491,9 +485,9 @@ create table t2 (`PRIMARY` int, index(`PRIMARY`)); show create table t2; create table t3 (a int); ---error 1280 +--error ER_WRONG_NAME_FOR_INDEX alter table t3 add index `primary` (a); ---error 1280 +--error ER_WRONG_NAME_FOR_INDEX alter table t3 add index `PRIMARY` (a); create table t4 (`primary` int); @@ -548,11 +542,11 @@ drop table t1; # # Bug#10413: Invalid column name is not rejected # ---error 1103 +--error ER_WRONG_TABLE_NAME create table t1(column.name int); ---error 1103 +--error ER_WRONG_TABLE_NAME create table t1(test.column.name int); ---error 1102 +--error ER_WRONG_DB_NAME create table t1(xyz.t1.name int); create table t1(t1.name int); create table t2(test.t2.name int); @@ -591,7 +585,7 @@ drop table if exists test.t1; create database mysqltest; use mysqltest; create view v1 as select 'foo' from dual; ---error 1347 +--error ER_WRONG_OBJECT create table t1 like v1; drop view v1; drop database mysqltest; @@ -712,7 +706,7 @@ drop table t1, t2; # # Bug #15316 SET value having comma not correctly handled # ---error 1367 +--error ER_ILLEGAL_VALUE_FOR_TYPE create table t1(a set("a,b","c,d") not null); # End of 4.1 tests @@ -914,9 +908,9 @@ INSERT IGNORE INTO t1 (b) VALUES (5); CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT a FROM t1; ---error 1062 +--error ER_DUP_ENTRY INSERT INTO t2 SELECT a FROM t1; ---error 1062 +--error ER_DUP_ENTRY INSERT INTO t2 SELECT a FROM t1; DROP TABLE t1, t2; @@ -976,24 +970,24 @@ drop table t1,t2; # Test incorrect database names # ---error 1102 +--error ER_WRONG_DB_NAME CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; ---error 1102 +--error ER_WRONG_DB_NAME DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; # TODO: enable these tests when RENAME DATABASE is implemented. -# --error 1049 +# --error ER_BAD_DB_ERROR # RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a; -# --error 1102 +# --error ER_WRONG_DB_NAME # RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; # create database mysqltest; -# --error 1102 +# --error ER_WRONG_DB_NAME # RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; # drop database mysqltest; ---error 1102 +--error ER_WRONG_DB_NAME USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; ---error 1102 +--error ER_WRONG_DB_NAME SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; # @@ -1853,28 +1847,46 @@ alter table t1 add key xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx0064 (f64) comment 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy'; drop table t1; ---echo End of 5.5 tests +--echo # +--echo # End of 5.5 tests +--echo # -# -# MDEV-4880 Attempt to create a table without columns produces ER_ILLEGAL_HA instead of ER_TABLE_MUST_HAVE_COLUMNS -# +--echo # +--echo # MDEV-4880 Attempt to create a table without columns produces ER_ILLEGAL_HA instead of ER_TABLE_MUST_HAVE_COLUMNS +--echo # --error ER_TABLE_MUST_HAVE_COLUMNS create table t1; -# -# MDEV-11231 Server crashes in check_duplicate_key on CREATE TABLE ... SELECT -# +--echo # +--echo # MDEV-11231 Server crashes in check_duplicate_key on CREATE TABLE ... SELECT +--echo # create table t1 (i int, j int, key(i), key(i)) as select 1 as i, 2 as j; drop table t1; -# -# MDEV-17544 No warning when trying to name a primary key constraint. -# +--echo # +--echo # End of 10.0 tests +--echo # + +--echo # +--echo # MDEV-18428 Memory: If transactional=0 is specified in CREATE TABLE, it is not possible to ALTER TABLE +--echo # +--error ER_ILLEGAL_HA_CREATE_OPTION +create table t1 (c int(10) unsigned) engine=memory transactional=0; + +--echo # +--echo # End of 10.2 tests +--echo # + +--echo # +--echo # MDEV-17544 No warning when trying to name a primary key constraint. +--echo # CREATE TABLE t1 ( id1 INT, id2 INT, CONSTRAINT `foo` PRIMARY KEY (id1), CONSTRAINT `bar` UNIQUE KEY(id2)); DROP TABLE t1; --echo # ---echo # 10.4 Test +--echo # End of 10.3 tests +--echo # + --echo # --echo # MDEV-21017: Assertion `!is_set() || (m_status == DA_OK_BULK && --echo # is_bulk_op())' failed or late ER_PERIOD_FIELD_WRONG_ATTRIBUTES @@ -1890,4 +1902,6 @@ e DATE, PERIOD FOR app(s,e)); UNLOCK TABLES; DROP TABLE t1; ---echo # End of 10.4 Test +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/ctype_utf32.result b/mysql-test/main/ctype_utf32.result index de1f4c3810f..da71f6eb59c 100644 --- a/mysql-test/main/ctype_utf32.result +++ b/mysql-test/main/ctype_utf32.result @@ -1306,7 +1306,7 @@ create table t1 (a varchar(334) character set utf32 primary key); ERROR 42000: Specified key was too long; max key length is 1000 bytes create table t1 (a varchar(333) character set utf32, key(a)); Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes insert into t1 values (repeat('a',333)), (repeat('b',333)); flush tables; check table t1; diff --git a/mysql-test/main/ctype_utf8mb4.result b/mysql-test/main/ctype_utf8mb4.result index bdcc07d590e..2762873b9c7 100644 --- a/mysql-test/main/ctype_utf8mb4.result +++ b/mysql-test/main/ctype_utf8mb4.result @@ -1478,7 +1478,7 @@ a varchar(255) NOT NULL default '', KEY a (a) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci; Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes insert into t1 values (_utf8mb4 0xe880bd); insert into t1 values (_utf8mb4 0x5b); select hex(a) from t1; @@ -1526,7 +1526,7 @@ Warnings: Note 1051 Unknown table 'test.t1' CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); INSERT INTO t1 VALUES('uu'); check table t1; @@ -2726,7 +2726,7 @@ DEFAULT CHARACTER SET utf8, MODIFY subject varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY p varchar(255) CHARACTER SET utf8; Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/main/ctype_utf8mb4_myisam.result b/mysql-test/main/ctype_utf8mb4_myisam.result index 8940d10fc78..ee2bd4431fc 100644 --- a/mysql-test/main/ctype_utf8mb4_myisam.result +++ b/mysql-test/main/ctype_utf8mb4_myisam.result @@ -1443,7 +1443,7 @@ a varchar(255) NOT NULL default '', KEY a (a) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci; Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes insert into t1 values (_utf8mb4 0xe880bd); insert into t1 values (_utf8mb4 0x5b); select hex(a) from t1; @@ -1491,7 +1491,7 @@ Warnings: Note 1051 Unknown table 'test.t1' CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); INSERT INTO t1 VALUES('uu'); check table t1; diff --git a/mysql-test/main/gis-json.result b/mysql-test/main/gis-json.result index 1d6e2193fc9..d507a9994ff 100644 --- a/mysql-test/main/gis-json.result +++ b/mysql-test/main/gis-json.result @@ -104,6 +104,9 @@ a NULL Warnings: Warning 4076 Incorrect GeoJSON format - empty 'coordinates' array. +SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"Feature\", \"geometry\": [10, 20] }"); +ST_GEOMFROMGEOJSON("{ \"type\": \"Feature\", \"geometry\": [10, 20] }") +NULL # # End of 10.2 tests # diff --git a/mysql-test/main/gis-json.test b/mysql-test/main/gis-json.test index b91ef235fd0..a97e9411e5c 100644 --- a/mysql-test/main/gis-json.test +++ b/mysql-test/main/gis-json.test @@ -44,6 +44,8 @@ SELECT st_astext(st_geomfromgeojson('{"type": "MultiLineString","coordinates": [ SELECT st_astext(st_geomfromgeojson('{"type": "Polygon","coordinates": []}')) as a; SELECT st_astext(st_geomfromgeojson('{"type": "MultiPolygon","coordinates": []}')) as a; +SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"Feature\", \"geometry\": [10, 20] }"); + --echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index ecd115c2dd0..47f399ecd3d 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -2884,6 +2884,52 @@ GROUP BY t.table_name; ERROR HY001: Out of sort memory, consider increasing server sort buffer size SET max_sort_length= @save_max_sort_length; # +# MDEV-23826: ORDER BY in view definition leads to wrong result with GROUP BY on query using view +# +CREATE TABLE t1 +( +id INT PRIMARY KEY AUTO_INCREMENT, +dt datetime, +INDEX(dt), +foo int +); +INSERT INTO t1 VALUES (1,'2020-09-26 12:00:00',1); +INSERT INTO t1 VALUES (2,'2020-09-26 13:00:00',1); +INSERT INTO t1 VALUES (3,'2020-09-27 13:00:00',1); +INSERT INTO t1 VALUES (4,'2020-09-27 12:00:00',1); +INSERT INTO t1 VALUES (5,'2020-09-28 12:00:00',1); +INSERT INTO t1 VALUES (6,'2020-09-28 13:00:00',1); +INSERT INTO t1 VALUES (7,'2020-09-25 12:00:00',1); +INSERT INTO t1 VALUES (8,'2020-09-25 13:00:00',1); +INSERT INTO t1 VALUES (9,'2020-09-26 13:00:00',1); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT * FROM t1 ORDER BY dt; +SELECT dt, sum(foo) AS foo FROM v1 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt; +dt foo +2020-09-25 12:00:00 1 +2020-09-25 13:00:00 1 +2020-09-26 12:00:00 1 +2020-09-26 13:00:00 2 +2020-09-27 12:00:00 1 +2020-09-27 13:00:00 1 +2020-09-28 12:00:00 1 +2020-09-28 13:00:00 1 +SELECT dt, sum(foo) AS foo FROM v2 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt; +dt foo +2020-09-25 12:00:00 1 +2020-09-25 13:00:00 1 +2020-09-26 12:00:00 1 +2020-09-26 13:00:00 2 +2020-09-27 12:00:00 1 +2020-09-27 13:00:00 1 +2020-09-28 12:00:00 1 +2020-09-28 13:00:00 1 +DROP TABLE t1; +DROP VIEW v1,v2; +# +# End of 10.2 tests +# +# # MDEV-16170 # Server crashes in Item_null_result::type_handler on SELECT with ROLLUP # @@ -2894,4 +2940,6 @@ f COUNT(*) 1 1 NULL 1 DROP TABLE t1; +# # End of 10.3 tests +# diff --git a/mysql-test/main/group_by.test b/mysql-test/main/group_by.test index 3dc0ee9f00b..16cb7cfb9fd 100644 --- a/mysql-test/main/group_by.test +++ b/mysql-test/main/group_by.test @@ -1987,7 +1987,6 @@ drop table t1; --echo # GROUP BY leads to crash --echo # - CALL mtr.add_suppression("Out of sort memory"); CALL mtr.add_suppression("Sort aborted"); SET @save_max_sort_length= @@max_sort_length; @@ -2000,6 +1999,40 @@ GROUP BY t.table_name; SET max_sort_length= @save_max_sort_length; --echo # +--echo # MDEV-23826: ORDER BY in view definition leads to wrong result with GROUP BY on query using view +--echo # + +CREATE TABLE t1 +( + id INT PRIMARY KEY AUTO_INCREMENT, + dt datetime, + INDEX(dt), + foo int +); + +INSERT INTO t1 VALUES (1,'2020-09-26 12:00:00',1); +INSERT INTO t1 VALUES (2,'2020-09-26 13:00:00',1); +INSERT INTO t1 VALUES (3,'2020-09-27 13:00:00',1); +INSERT INTO t1 VALUES (4,'2020-09-27 12:00:00',1); +INSERT INTO t1 VALUES (5,'2020-09-28 12:00:00',1); +INSERT INTO t1 VALUES (6,'2020-09-28 13:00:00',1); +INSERT INTO t1 VALUES (7,'2020-09-25 12:00:00',1); +INSERT INTO t1 VALUES (8,'2020-09-25 13:00:00',1); +INSERT INTO t1 VALUES (9,'2020-09-26 13:00:00',1); + +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT * FROM t1 ORDER BY dt; +SELECT dt, sum(foo) AS foo FROM v1 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt; +SELECT dt, sum(foo) AS foo FROM v2 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt; + +DROP TABLE t1; +DROP VIEW v1,v2; + +--echo # +--echo # End of 10.2 tests +--echo # + +--echo # --echo # MDEV-16170 --echo # Server crashes in Item_null_result::type_handler on SELECT with ROLLUP --echo # @@ -2009,4 +2042,6 @@ INSERT INTO t1 VALUES ('2032-10-08'); SELECT d != '2023-03-04' AS f, COUNT(*) FROM t1 GROUP BY d WITH ROLLUP; DROP TABLE t1; +--echo # --echo # End of 10.3 tests +--echo # diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index 55c997eee16..910a657e034 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -2228,6 +2228,18 @@ TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAUL Warnings: Warning 1931 Query execution was interrupted. The query examined at least ### rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete # +# MDEV-24179: AAssertion `m_status == DA_ERROR || m_status == DA_OK || +# m_status == DA_OK_BULK' failed in Diagnostics_area::message() +# +call mtr.add_suppression("Sort aborted.*"); +DROP DATABASE test; +CREATE DATABASE test; +USE test; +CREATE VIEW v AS SELECT table_schema AS object_schema, table_name AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema; +SELECT * FROM v LIMIT ROWS EXAMINED 9; +ERROR HY000: Sort aborted: +DROP VIEW v; +# # End of 10.2 Test # # diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test index fb9eaa02bff..4e05ac4e5db 100644 --- a/mysql-test/main/information_schema.test +++ b/mysql-test/main/information_schema.test @@ -1935,6 +1935,23 @@ replace_regex /at least \d+ rows/at least ### rows/; SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10; --echo # +--echo # MDEV-24179: AAssertion `m_status == DA_ERROR || m_status == DA_OK || +--echo # m_status == DA_OK_BULK' failed in Diagnostics_area::message() +--echo # + +call mtr.add_suppression("Sort aborted.*"); + +DROP DATABASE test; +CREATE DATABASE test; +USE test; +CREATE VIEW v AS SELECT table_schema AS object_schema, table_name AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema; + +--error ER_FILSORT_ABORT +SELECT * FROM v LIMIT ROWS EXAMINED 9; + +DROP VIEW v; + +--echo # --echo # End of 10.2 Test --echo # diff --git a/mysql-test/main/kill.result b/mysql-test/main/kill.result index 1ea06aee096..cbb0598485f 100644 --- a/mysql-test/main/kill.result +++ b/mysql-test/main/kill.result @@ -413,3 +413,8 @@ ALTER TABLE t2 DROP c; UNLOCK TABLES; DROP VIEW v1; DROP TABLE t1, t2; +# +# KILL QUERY ID USER +# +kill query id user 'foo'; +ERROR 42S22: Unknown column 'user' in 'field list' diff --git a/mysql-test/main/kill.test b/mysql-test/main/kill.test index c5bbd349574..05e70ff8651 100644 --- a/mysql-test/main/kill.test +++ b/mysql-test/main/kill.test @@ -664,3 +664,9 @@ ALTER TABLE t2 DROP c; UNLOCK TABLES; DROP VIEW v1; DROP TABLE t1, t2; + +--echo # +--echo # KILL QUERY ID USER +--echo # +--error ER_BAD_FIELD_ERROR +kill query id user 'foo'; diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result index efa0444fff5..eb61a080c96 100644 --- a/mysql-test/main/long_unique.result +++ b/mysql-test/main/long_unique.result @@ -1439,7 +1439,7 @@ drop table t1; create table t1(a varchar(4000)); alter table t1 add index(a); Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes show create table t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 72dab5bb181..5d6c0562c8a 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -148,7 +148,7 @@ ALTER TABLE t1 DROP KEY f, ADD INDEX idx1(f), ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY ALTER TABLE t1 ADD KEY idx2(f); Warnings: -Warning 1071 Specified key was too long; max key length is 3072 bytes +Note 1071 Specified key was too long; max key length is 3072 bytes DROP TABLE t1; CREATE TABLE t1(a blob , b blob , unique(a,b)); alter table t1 drop column b; diff --git a/mysql-test/main/mix2_myisam.result b/mysql-test/main/mix2_myisam.result index 5acec2616fa..220d14f3b0e 100644 --- a/mysql-test/main/mix2_myisam.result +++ b/mysql-test/main/mix2_myisam.result @@ -1990,7 +1990,7 @@ a b drop table t1; create table t1 (v varchar(65530), key(v)); Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes drop table t1; SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR create table t1 (v varchar(65536)); diff --git a/mysql-test/main/myisam.result b/mysql-test/main/myisam.result index 7556e64936a..32737bd8399 100644 --- a/mysql-test/main/myisam.result +++ b/mysql-test/main/myisam.result @@ -1706,7 +1706,7 @@ a b drop table t1; create table t1 (v varchar(65530), key(v)); Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes drop table if exists t1; set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for create table t1 (v varchar(65536)); @@ -1980,7 +1980,7 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 (a varchar(2048), key `a` (a)); Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -1990,7 +1990,7 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 (a varchar(2048), key `a` (a) key_block_size=1024); Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -2000,7 +2000,7 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=1024; Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -2046,7 +2046,7 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=8192; Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -2136,7 +2136,7 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) key_block_size=8192; Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -2166,7 +2166,7 @@ t1 CREATE TABLE `t1` ( drop table t1; create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000); Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -2412,6 +2412,7 @@ Key Start Len Index Type 1 2 30 multip. varchar 2 33 30 multip. char NULL DROP TABLE t1; +set statement sql_mode='' for create table t1 (n int not null, c char(1)) transactional=1; Warnings: Warning 1478 Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1' diff --git a/mysql-test/main/myisam.test b/mysql-test/main/myisam.test index 0b153514ecb..9b81ab197ae 100644 --- a/mysql-test/main/myisam.test +++ b/mysql-test/main/myisam.test @@ -1557,6 +1557,7 @@ DROP TABLE t1; # MariaDB: Note that the table will still have 'TRANSACTIONAL=1' attribute. # That's the intended behavior atm. # +set statement sql_mode='' for create table t1 (n int not null, c char(1)) transactional=1; show create table t1; drop table t1; diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result index 6e17d6cc333..937c9c6b8d6 100644 --- a/mysql-test/main/mysql_upgrade.result +++ b/mysql-test/main/mysql_upgrade.result @@ -688,8 +688,88 @@ root N root N root N aRole Y +DROP ROLE aRole; +# +# MDEV-24122 Fix previously MySQL-5.7 data directories that upgraded prior to MDEV-23201 +# +# switching from mysql.global_priv to mysql.user +drop view mysql.user_bak; +drop table mysql.user; +truncate table mysql.tables_priv; +FLUSH TABLES mysql.user; +ALTER TABLE mysql.user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; +ALTER TABLE mysql.user ADD default_role char(80) binary DEFAULT '' NOT NULL; +ALTER TABLE mysql.user ADD max_statement_time decimal(12,6) DEFAULT 0 NOT NULL; +FLUSH PRIVILEGES; +Phase 1/7: Checking and upgrading mysql database +Processing databases +mysql +mysql.column_stats OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.global_priv_bak OK +mysql.gtid_slave_pos OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.index_stats OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.roles_mapping OK +mysql.servers OK +mysql.table_stats OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.transaction_registry OK +mysql.user OK +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables +Processing databases +information_schema +mtr +mtr.global_suppressions OK +mtr.test_suppressions OK +performance_schema +test +Phase 7/7: Running 'FLUSH PRIVILEGES' +OK +SHOW CREATE TABLE mysql.user; +View Create View character_set_client collation_connection +user CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys`@`localhost` SQL SECURITY DEFINER VIEW `mysql`.`user` AS select `mysql`.`global_priv`.`Host` AS `Host`,`mysql`.`global_priv`.`User` AS `User`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`mysql`.`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,'N' AS `password_expired`,elt(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`mysql`.`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `mysql`.`global_priv` latin1 latin1_swedish_ci +CREATE ROLE `aRole`; +SET DEFAULT ROLE aRole; +SHOW GRANTS; +Grants for root@localhost +GRANT `aRole` TO `root`@`localhost` WITH ADMIN OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `aRole` +SET DEFAULT ROLE aRole FOR 'root'@'localhost' +SET DEFAULT ROLE NONE; +SHOW GRANTS; +Grants for root@localhost +GRANT `aRole` TO `root`@`localhost` WITH ADMIN OPTION +GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO `aRole` DROP ROLE `aRole`; FLUSH PRIVILEGES; +drop table mysql.global_priv; +rename table mysql.global_priv_bak to mysql.global_priv; # End of 10.2 tests # # Ensure that mysql_upgrade correctly sets truncate_versioning_priv diff --git a/mysql-test/main/mysql_upgrade.test b/mysql-test/main/mysql_upgrade.test index 3395dd7edad..7d673d2bb4d 100644 --- a/mysql-test/main/mysql_upgrade.test +++ b/mysql-test/main/mysql_upgrade.test @@ -243,10 +243,42 @@ FLUSH PRIVILEGES; SET ROLE `aRole`; SELECT `User`, `is_role` FROM `mysql`.`user`; +DROP ROLE aRole; + +--echo # +--echo # MDEV-24122 Fix previously MySQL-5.7 data directories that upgraded prior to MDEV-23201 +--echo # + +--source include/switch_to_mysql_user.inc +drop view mysql.user_bak; +drop table mysql.user; +truncate table mysql.tables_priv; +--copy_file std_data/mysql57user.frm $MYSQLD_DATADIR/mysql/user.frm +--copy_file std_data/mysql57user.MYI $MYSQLD_DATADIR/mysql/user.MYI +--copy_file std_data/mysql57user.MYD $MYSQLD_DATADIR/mysql/user.MYD +FLUSH TABLES mysql.user; + +# What prior to MDEV-23201 would of done: +ALTER TABLE mysql.user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; +ALTER TABLE mysql.user ADD default_role char(80) binary DEFAULT '' NOT NULL; +ALTER TABLE mysql.user ADD max_statement_time decimal(12,6) DEFAULT 0 NOT NULL; +FLUSH PRIVILEGES; + +--exec $MYSQL_UPGRADE --force 2>&1 +SHOW CREATE TABLE mysql.user; + +CREATE ROLE `aRole`; +SET DEFAULT ROLE aRole; +SHOW GRANTS; +SET DEFAULT ROLE NONE; +SHOW GRANTS; + DROP ROLE `aRole`; --exec $MYSQL mysql < $MYSQLTEST_VARDIR/tmp/user.sql FLUSH PRIVILEGES; --remove_file $MYSQLD_DATADIR/mysql_upgrade_info +drop table mysql.global_priv; +rename table mysql.global_priv_bak to mysql.global_priv; --echo # End of 10.2 tests diff --git a/mysql-test/main/mysqldump-system.result b/mysql-test/main/mysqldump-system.result index d887df81d2b..c443e250e4a 100644 --- a/mysql-test/main/mysqldump-system.result +++ b/mysql-test/main/mysqldump-system.result @@ -90,31 +90,31 @@ USE mysql; LOCK TABLES `column_stats` WRITE; /*!40000 ALTER TABLE `column_stats` DISABLE KEYS */; -INSERT INTO `column_stats` VALUES ('mysql','tz','Time_zone_id','1','5',0.0000,4.0000,98.2500,254,'DOUBLE_PREC_HB','\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿'); +REPLACE INTO `column_stats` VALUES ('mysql','tz','Time_zone_id','1','5',0.0000,4.0000,98.2500,254,'DOUBLE_PREC_HB','\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿ÿ¿'); /*!40000 ALTER TABLE `column_stats` ENABLE KEYS */; UNLOCK TABLES; LOCK TABLES `index_stats` WRITE; /*!40000 ALTER TABLE `index_stats` DISABLE KEYS */; -INSERT INTO `index_stats` VALUES ('mysql','tz','PRIMARY',1,98.2500); +REPLACE INTO `index_stats` VALUES ('mysql','tz','PRIMARY',1,98.2500); /*!40000 ALTER TABLE `index_stats` ENABLE KEYS */; UNLOCK TABLES; LOCK TABLES `table_stats` WRITE; /*!40000 ALTER TABLE `table_stats` DISABLE KEYS */; -INSERT INTO `table_stats` VALUES ('mysql','tz',393); +REPLACE INTO `table_stats` VALUES ('mysql','tz',393); /*!40000 ALTER TABLE `table_stats` ENABLE KEYS */; UNLOCK TABLES; LOCK TABLES `innodb_index_stats` WRITE; /*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */; -INSERT INTO `innodb_index_stats` VALUES ('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx01',4,1,'Time_zone_id'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx02',393,1,'Time_zone_id,Transition_time'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','size',1,NULL,'Number of pages in the index'); +REPLACE INTO `innodb_index_stats` VALUES ('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx01',4,1,'Time_zone_id'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_diff_pfx02',393,1,'Time_zone_id,Transition_time'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),('mysql','tz','PRIMARY','2019-12-31 21:00:00','size',1,NULL,'Number of pages in the index'); /*!40000 ALTER TABLE `innodb_index_stats` ENABLE KEYS */; UNLOCK TABLES; LOCK TABLES `innodb_table_stats` WRITE; /*!40000 ALTER TABLE `innodb_table_stats` DISABLE KEYS */; -INSERT INTO `innodb_table_stats` VALUES ('mysql','tz','2019-12-31 21:00:00',393,1,0); +REPLACE INTO `innodb_table_stats` VALUES ('mysql','tz','2019-12-31 21:00:00',393,1,0); /*!40000 ALTER TABLE `innodb_table_stats` ENABLE KEYS */; UNLOCK TABLES; @@ -122,31 +122,31 @@ USE mysql; LOCK TABLES `time_zone` WRITE; /*!40000 ALTER TABLE `time_zone` DISABLE KEYS */; -INSERT INTO `time_zone` VALUES (1,'N'),(2,'N'),(3,'N'),(4,'Y'),(5,'N'); +REPLACE INTO `time_zone` VALUES (1,'N'),(2,'N'),(3,'N'),(4,'Y'),(5,'N'); /*!40000 ALTER TABLE `time_zone` ENABLE KEYS */; UNLOCK TABLES; LOCK TABLES `time_zone_name` WRITE; /*!40000 ALTER TABLE `time_zone_name` DISABLE KEYS */; -INSERT INTO `time_zone_name` VALUES ('Europe/Moscow',3),('Japan',5),('leap/Europe/Moscow',4),('MET',1),('Universal',2),('UTC',2); +REPLACE INTO `time_zone_name` VALUES ('Europe/Moscow',3),('Japan',5),('leap/Europe/Moscow',4),('MET',1),('Universal',2),('UTC',2); /*!40000 ALTER TABLE `time_zone_name` ENABLE KEYS */; UNLOCK TABLES; LOCK TABLES `time_zone_leap_second` WRITE; /*!40000 ALTER TABLE `time_zone_leap_second` DISABLE KEYS */; -INSERT INTO `time_zone_leap_second` VALUES (78796800,1),(94694401,2),(126230402,3),(157766403,4),(189302404,5),(220924805,6),(252460806,7),(283996807,8),(315532808,9),(362793609,10),(394329610,11),(425865611,12),(489024012,13),(567993613,14),(631152014,15),(662688015,16),(709948816,17),(741484817,18),(773020818,19),(820454419,20),(867715220,21),(915148821,22); +REPLACE INTO `time_zone_leap_second` VALUES (78796800,1),(94694401,2),(126230402,3),(157766403,4),(189302404,5),(220924805,6),(252460806,7),(283996807,8),(315532808,9),(362793609,10),(394329610,11),(425865611,12),(489024012,13),(567993613,14),(631152014,15),(662688015,16),(709948816,17),(741484817,18),(773020818,19),(820454419,20),(867715220,21),(915148821,22); /*!40000 ALTER TABLE `time_zone_leap_second` ENABLE KEYS */; UNLOCK TABLES; LOCK TABLES `time_zone_transition` WRITE; /*!40000 ALTER TABLE `time_zone_transition` DISABLE KEYS */; -INSERT INTO `time_zone_transition` VALUES (1,-1693706400,0),(1,-1680483600,1),(1,-1663455600,2),(1,-1650150000,3),(1,-1632006000,2),(1,-1618700400,3),(1,-938905200,2),(1,-857257200,3),(1,-844556400,2),(1,-828226800,3),(1,-812502000,2),(1,-796777200,3),(1,228877200,2),(1,243997200,3),(1,260326800,2),(1,276051600,3),(1,291776400,2),(1,307501200,3),(1,323830800,2),(1,338950800,3),(1,354675600,2),(1,370400400,3),(1,386125200,2),(1,401850000,3),(1,417574800,2),(1,433299600,3),(1,449024400,2),(1,465354000,3),(1,481078800,2),(1,496803600,3),(1,512528400,2),(1,528253200,3),(1,543978000,2),(1,559702800,3),(1,575427600,2),(1,591152400,3),(1,606877200,2),(1,622602000,3),(1,638326800,2),(1,654656400,3),(1,670381200,2),(1,686106000,3),(1,701830800,2),(1,717555600,3),(1,733280400,2),(1,749005200,3),(1,764730000,2),(1,780454800,3),(1,796179600,2),(1,811904400,3),(1,828234000,2),(1,846378000,3),(1,859683600,2),(1,877827600,3),(1,891133200,2),(1,909277200,3),(1,922582800,2),(1,941331600,3),(1,954032400,2),(1,972781200,3),(1,985482000,2),(1,1004230800,3),(1,1017536400,2),(1,1035680400,3),(1,1048986000,2),(1,1067130000,3),(1,1080435600,2),(1,1099184400,3),(1,1111885200,2),(1,1130634000,3),(1,1143334800,2),(1,1162083600,3),(1,1174784400,2),(1,1193533200,3),(1,1206838800,2),(1,1224982800,3),(1,1238288400,2),(1,1256432400,3),(1,1269738000,2),(1,1288486800,3),(1,1301187600,2),(1,1319936400,3),(1,1332637200,2),(1,1351386000,3),(1,1364691600,2),(1,1382835600,3),(1,1396141200,2),(1,1414285200,3),(1,1427590800,2),(1,1445734800,3),(1,1459040400,2),(1,1477789200,3),(1,1490490000,2),(1,1509238800,3),(1,1521939600,2),(1,1540688400,3),(1,1553994000,2),(1,1572138000,3),(1,1585443600,2),(1,1603587600,3),(1,1616893200,2),(1,1635642000,3),(1,1648342800,2),(1,1667091600,3),(1,1679792400,2),(1,1698541200,3),(1,1711846800,2),(1,1729990800,3),(1,1743296400,2),(1,1761440400,3),(1,1774746000,2),(1,1792890000,3),(1,1806195600,2),(1,1824944400,3),(1,1837645200,2),(1,1856394000,3),(1,1869094800,2),(1,1887843600,3),(1,1901149200,2),(1,1919293200,3),(1,1932598800,2),(1,1950742800,3),(1,1964048400,2),(1,1982797200,3),(1,1995498000,2),(1,2014246800,3),(1,2026947600,2),(1,2045696400,3),(1,2058397200,2),(1,2077146000,3),(1,2090451600,2),(1,2108595600,3),(1,2121901200,2),(1,2140045200,3),(3,-1688265000,2),(3,-1656819048,1),(3,-1641353448,2),(3,-1627965048,3),(3,-1618716648,1),(3,-1596429048,3),(3,-1593829848,5),(3,-1589860800,4),(3,-1542427200,5),(3,-1539493200,6),(3,-1525323600,5),(3,-1522728000,4),(3,-1491188400,7),(3,-1247536800,4),(3,354920400,5),(3,370728000,4),(3,386456400,5),(3,402264000,4),(3,417992400,5),(3,433800000,4),(3,449614800,5),(3,465346800,8),(3,481071600,9),(3,496796400,8),(3,512521200,9),(3,528246000,8),(3,543970800,9),(3,559695600,8),(3,575420400,9),(3,591145200,8),(3,606870000,9),(3,622594800,8),(3,638319600,9),(3,654649200,8),(3,670374000,10),(3,686102400,11),(3,695779200,8),(3,701812800,5),(3,717534000,4),(3,733273200,9),(3,748998000,8),(3,764722800,9),(3,780447600,8),(3,796172400,9),(3,811897200,8),(3,828226800,9),(3,846370800,8),(3,859676400,9),(3,877820400,8),(3,891126000,9),(3,909270000,8),(3,922575600,9),(3,941324400,8),(3,954025200,9),(3,972774000,8),(3,985474800,9),(3,1004223600,8),(3,1017529200,9),(3,1035673200,8),(3,1048978800,9),(3,1067122800,8),(3,1080428400,9),(3,1099177200,8),(3,1111878000,9),(3,1130626800,8),(3,1143327600,9),(3,1162076400,8),(3,1174777200,9),(3,1193526000,8),(3,1206831600,9),(3,1224975600,8),(3,1238281200,9),(3,1256425200,8),(3,1269730800,9),(3,1288479600,8),(3,1301180400,9),(3,1319929200,8),(3,1332630000,9),(3,1351378800,8),(3,1364684400,9),(3,1382828400,8),(3,1396134000,9),(3,1414278000,8),(3,1427583600,9),(3,1445727600,8),(3,1459033200,9),(3,1477782000,8),(3,1490482800,9),(3,1509231600,8),(3,1521932400,9),(3,1540681200,8),(3,1553986800,9),(3,1572130800,8),(3,1585436400,9),(3,1603580400,8),(3,1616886000,9),(3,1635634800,8),(3,1648335600,9),(3,1667084400,8),(3,1679785200,9),(3,1698534000,8),(3,1711839600,9),(3,1729983600,8),(3,1743289200,9),(3,1761433200,8),(3,1774738800,9),(3,1792882800,8),(3,1806188400,9),(3,1824937200,8),(3,1837638000,9),(3,1856386800,8),(3,1869087600,9),(3,1887836400,8),(3,1901142000,9),(3,1919286000,8),(3,1932591600,9),(3,1950735600,8),(3,1964041200,9),(3,1982790000,8),(3,1995490800,9),(3,2014239600,8),(3,2026940400,9),(3,2045689200,8),(3,2058390000,9),(3,2077138800,8),(3,2090444400,9),(3,2108588400,8),(3,2121894000,9),(3,2140038000,8),(4,-1688265000,2),(4,-1656819048,1),(4,-1641353448,2),(4,-1627965048,3),(4,-1618716648,1),(4,-1596429048,3),(4,-1593829848,5),(4,-1589860800,4),(4,-1542427200,5),(4,-1539493200,6),(4,-1525323600,5),(4,-1522728000,4),(4,-1491188400,7),(4,-1247536800,4),(4,354920409,5),(4,370728010,4),(4,386456410,5),(4,402264011,4),(4,417992411,5),(4,433800012,4),(4,449614812,5),(4,465346812,8),(4,481071612,9),(4,496796413,8),(4,512521213,9),(4,528246013,8),(4,543970813,9),(4,559695613,8),(4,575420414,9),(4,591145214,8),(4,606870014,9),(4,622594814,8),(4,638319615,9),(4,654649215,8),(4,670374016,10),(4,686102416,11),(4,695779216,8),(4,701812816,5),(4,717534017,4),(4,733273217,9),(4,748998018,8),(4,764722818,9),(4,780447619,8),(4,796172419,9),(4,811897219,8),(4,828226820,9),(4,846370820,8),(4,859676420,9),(4,877820421,8),(4,891126021,9),(4,909270021,8),(4,922575622,9),(4,941324422,8),(4,954025222,9),(4,972774022,8),(4,985474822,9),(4,1004223622,8),(4,1017529222,9),(4,1035673222,8),(4,1048978822,9),(4,1067122822,8),(4,1080428422,9),(4,1099177222,8),(4,1111878022,9),(4,1130626822,8),(4,1143327622,9),(4,1162076422,8),(4,1174777222,9),(4,1193526022,8),(4,1206831622,9),(4,1224975622,8),(4,1238281222,9),(4,1256425222,8),(4,1269730822,9),(4,1288479622,8),(4,1301180422,9),(4,1319929222,8),(4,1332630022,9),(4,1351378822,8),(4,1364684422,9),(4,1382828422,8),(4,1396134022,9),(4,1414278022,8),(4,1427583622,9),(4,1445727622,8),(4,1459033222,9),(4,1477782022,8),(4,1490482822,9),(4,1509231622,8),(4,1521932422,9),(4,1540681222,8),(4,1553986822,9),(4,1572130822,8),(4,1585436422,9),(4,1603580422,8),(4,1616886022,9),(4,1635634822,8),(4,1648335622,9),(4,1667084422,8),(4,1679785222,9),(4,1698534022,8),(4,1711839622,9),(4,1729983622,8),(4,1743289222,9),(4,1761433222,8),(4,1774738822,9),(4,1792882822,8),(4,1806188422,9),(4,1824937222,8),(4,1837638022,9),(4,1856386822,8),(4,1869087622,9),(4,1887836422,8),(4,1901142022,9),(4,1919286022,8),(4,1932591622,9),(4,1950735622,8),(4,1964041222,9),(4,1982790022,8),(4,1995490822,9),(4,2014239622,8),(4,2026940422,9),(4,2045689222,8),(4,2058390022,9),(4,2077138822,8),(4,2090444422,9),(4,2108588422,8),(4,2121894022,9),(4,2140038022,8),(5,-1009875600,1); +REPLACE INTO `time_zone_transition` VALUES (1,-1693706400,0),(1,-1680483600,1),(1,-1663455600,2),(1,-1650150000,3),(1,-1632006000,2),(1,-1618700400,3),(1,-938905200,2),(1,-857257200,3),(1,-844556400,2),(1,-828226800,3),(1,-812502000,2),(1,-796777200,3),(1,228877200,2),(1,243997200,3),(1,260326800,2),(1,276051600,3),(1,291776400,2),(1,307501200,3),(1,323830800,2),(1,338950800,3),(1,354675600,2),(1,370400400,3),(1,386125200,2),(1,401850000,3),(1,417574800,2),(1,433299600,3),(1,449024400,2),(1,465354000,3),(1,481078800,2),(1,496803600,3),(1,512528400,2),(1,528253200,3),(1,543978000,2),(1,559702800,3),(1,575427600,2),(1,591152400,3),(1,606877200,2),(1,622602000,3),(1,638326800,2),(1,654656400,3),(1,670381200,2),(1,686106000,3),(1,701830800,2),(1,717555600,3),(1,733280400,2),(1,749005200,3),(1,764730000,2),(1,780454800,3),(1,796179600,2),(1,811904400,3),(1,828234000,2),(1,846378000,3),(1,859683600,2),(1,877827600,3),(1,891133200,2),(1,909277200,3),(1,922582800,2),(1,941331600,3),(1,954032400,2),(1,972781200,3),(1,985482000,2),(1,1004230800,3),(1,1017536400,2),(1,1035680400,3),(1,1048986000,2),(1,1067130000,3),(1,1080435600,2),(1,1099184400,3),(1,1111885200,2),(1,1130634000,3),(1,1143334800,2),(1,1162083600,3),(1,1174784400,2),(1,1193533200,3),(1,1206838800,2),(1,1224982800,3),(1,1238288400,2),(1,1256432400,3),(1,1269738000,2),(1,1288486800,3),(1,1301187600,2),(1,1319936400,3),(1,1332637200,2),(1,1351386000,3),(1,1364691600,2),(1,1382835600,3),(1,1396141200,2),(1,1414285200,3),(1,1427590800,2),(1,1445734800,3),(1,1459040400,2),(1,1477789200,3),(1,1490490000,2),(1,1509238800,3),(1,1521939600,2),(1,1540688400,3),(1,1553994000,2),(1,1572138000,3),(1,1585443600,2),(1,1603587600,3),(1,1616893200,2),(1,1635642000,3),(1,1648342800,2),(1,1667091600,3),(1,1679792400,2),(1,1698541200,3),(1,1711846800,2),(1,1729990800,3),(1,1743296400,2),(1,1761440400,3),(1,1774746000,2),(1,1792890000,3),(1,1806195600,2),(1,1824944400,3),(1,1837645200,2),(1,1856394000,3),(1,1869094800,2),(1,1887843600,3),(1,1901149200,2),(1,1919293200,3),(1,1932598800,2),(1,1950742800,3),(1,1964048400,2),(1,1982797200,3),(1,1995498000,2),(1,2014246800,3),(1,2026947600,2),(1,2045696400,3),(1,2058397200,2),(1,2077146000,3),(1,2090451600,2),(1,2108595600,3),(1,2121901200,2),(1,2140045200,3),(3,-1688265000,2),(3,-1656819048,1),(3,-1641353448,2),(3,-1627965048,3),(3,-1618716648,1),(3,-1596429048,3),(3,-1593829848,5),(3,-1589860800,4),(3,-1542427200,5),(3,-1539493200,6),(3,-1525323600,5),(3,-1522728000,4),(3,-1491188400,7),(3,-1247536800,4),(3,354920400,5),(3,370728000,4),(3,386456400,5),(3,402264000,4),(3,417992400,5),(3,433800000,4),(3,449614800,5),(3,465346800,8),(3,481071600,9),(3,496796400,8),(3,512521200,9),(3,528246000,8),(3,543970800,9),(3,559695600,8),(3,575420400,9),(3,591145200,8),(3,606870000,9),(3,622594800,8),(3,638319600,9),(3,654649200,8),(3,670374000,10),(3,686102400,11),(3,695779200,8),(3,701812800,5),(3,717534000,4),(3,733273200,9),(3,748998000,8),(3,764722800,9),(3,780447600,8),(3,796172400,9),(3,811897200,8),(3,828226800,9),(3,846370800,8),(3,859676400,9),(3,877820400,8),(3,891126000,9),(3,909270000,8),(3,922575600,9),(3,941324400,8),(3,954025200,9),(3,972774000,8),(3,985474800,9),(3,1004223600,8),(3,1017529200,9),(3,1035673200,8),(3,1048978800,9),(3,1067122800,8),(3,1080428400,9),(3,1099177200,8),(3,1111878000,9),(3,1130626800,8),(3,1143327600,9),(3,1162076400,8),(3,1174777200,9),(3,1193526000,8),(3,1206831600,9),(3,1224975600,8),(3,1238281200,9),(3,1256425200,8),(3,1269730800,9),(3,1288479600,8),(3,1301180400,9),(3,1319929200,8),(3,1332630000,9),(3,1351378800,8),(3,1364684400,9),(3,1382828400,8),(3,1396134000,9),(3,1414278000,8),(3,1427583600,9),(3,1445727600,8),(3,1459033200,9),(3,1477782000,8),(3,1490482800,9),(3,1509231600,8),(3,1521932400,9),(3,1540681200,8),(3,1553986800,9),(3,1572130800,8),(3,1585436400,9),(3,1603580400,8),(3,1616886000,9),(3,1635634800,8),(3,1648335600,9),(3,1667084400,8),(3,1679785200,9),(3,1698534000,8),(3,1711839600,9),(3,1729983600,8),(3,1743289200,9),(3,1761433200,8),(3,1774738800,9),(3,1792882800,8),(3,1806188400,9),(3,1824937200,8),(3,1837638000,9),(3,1856386800,8),(3,1869087600,9),(3,1887836400,8),(3,1901142000,9),(3,1919286000,8),(3,1932591600,9),(3,1950735600,8),(3,1964041200,9),(3,1982790000,8),(3,1995490800,9),(3,2014239600,8),(3,2026940400,9),(3,2045689200,8),(3,2058390000,9),(3,2077138800,8),(3,2090444400,9),(3,2108588400,8),(3,2121894000,9),(3,2140038000,8),(4,-1688265000,2),(4,-1656819048,1),(4,-1641353448,2),(4,-1627965048,3),(4,-1618716648,1),(4,-1596429048,3),(4,-1593829848,5),(4,-1589860800,4),(4,-1542427200,5),(4,-1539493200,6),(4,-1525323600,5),(4,-1522728000,4),(4,-1491188400,7),(4,-1247536800,4),(4,354920409,5),(4,370728010,4),(4,386456410,5),(4,402264011,4),(4,417992411,5),(4,433800012,4),(4,449614812,5),(4,465346812,8),(4,481071612,9),(4,496796413,8),(4,512521213,9),(4,528246013,8),(4,543970813,9),(4,559695613,8),(4,575420414,9),(4,591145214,8),(4,606870014,9),(4,622594814,8),(4,638319615,9),(4,654649215,8),(4,670374016,10),(4,686102416,11),(4,695779216,8),(4,701812816,5),(4,717534017,4),(4,733273217,9),(4,748998018,8),(4,764722818,9),(4,780447619,8),(4,796172419,9),(4,811897219,8),(4,828226820,9),(4,846370820,8),(4,859676420,9),(4,877820421,8),(4,891126021,9),(4,909270021,8),(4,922575622,9),(4,941324422,8),(4,954025222,9),(4,972774022,8),(4,985474822,9),(4,1004223622,8),(4,1017529222,9),(4,1035673222,8),(4,1048978822,9),(4,1067122822,8),(4,1080428422,9),(4,1099177222,8),(4,1111878022,9),(4,1130626822,8),(4,1143327622,9),(4,1162076422,8),(4,1174777222,9),(4,1193526022,8),(4,1206831622,9),(4,1224975622,8),(4,1238281222,9),(4,1256425222,8),(4,1269730822,9),(4,1288479622,8),(4,1301180422,9),(4,1319929222,8),(4,1332630022,9),(4,1351378822,8),(4,1364684422,9),(4,1382828422,8),(4,1396134022,9),(4,1414278022,8),(4,1427583622,9),(4,1445727622,8),(4,1459033222,9),(4,1477782022,8),(4,1490482822,9),(4,1509231622,8),(4,1521932422,9),(4,1540681222,8),(4,1553986822,9),(4,1572130822,8),(4,1585436422,9),(4,1603580422,8),(4,1616886022,9),(4,1635634822,8),(4,1648335622,9),(4,1667084422,8),(4,1679785222,9),(4,1698534022,8),(4,1711839622,9),(4,1729983622,8),(4,1743289222,9),(4,1761433222,8),(4,1774738822,9),(4,1792882822,8),(4,1806188422,9),(4,1824937222,8),(4,1837638022,9),(4,1856386822,8),(4,1869087622,9),(4,1887836422,8),(4,1901142022,9),(4,1919286022,8),(4,1932591622,9),(4,1950735622,8),(4,1964041222,9),(4,1982790022,8),(4,1995490822,9),(4,2014239622,8),(4,2026940422,9),(4,2045689222,8),(4,2058390022,9),(4,2077138822,8),(4,2090444422,9),(4,2108588422,8),(4,2121894022,9),(4,2140038022,8),(5,-1009875600,1); /*!40000 ALTER TABLE `time_zone_transition` ENABLE KEYS */; UNLOCK TABLES; LOCK TABLES `time_zone_transition_type` WRITE; /*!40000 ALTER TABLE `time_zone_transition_type` DISABLE KEYS */; -INSERT INTO `time_zone_transition_type` VALUES (1,0,7200,1,'MEST'),(1,1,3600,0,'MET'),(1,2,7200,1,'MEST'),(1,3,3600,0,'MET'),(2,0,0,0,'UTC'),(3,0,9000,0,'MMT'),(3,1,12648,1,'MST'),(3,2,9048,0,'MMT'),(3,3,16248,1,'MDST'),(3,4,10800,0,'MSK'),(3,5,14400,1,'MSD'),(3,6,18000,1,'MSD'),(3,7,7200,0,'EET'),(3,8,10800,0,'MSK'),(3,9,14400,1,'MSD'),(3,10,10800,1,'EEST'),(3,11,7200,0,'EET'),(4,0,9000,0,'MMT'),(4,1,12648,1,'MST'),(4,2,9048,0,'MMT'),(4,3,16248,1,'MDST'),(4,4,10800,0,'MSK'),(4,5,14400,1,'MSD'),(4,6,18000,1,'MSD'),(4,7,7200,0,'EET'),(4,8,10800,0,'MSK'),(4,9,14400,1,'MSD'),(4,10,10800,1,'EEST'),(4,11,7200,0,'EET'),(5,0,32400,0,'CJT'),(5,1,32400,0,'JST'); +REPLACE INTO `time_zone_transition_type` VALUES (1,0,7200,1,'MEST'),(1,1,3600,0,'MET'),(1,2,7200,1,'MEST'),(1,3,3600,0,'MET'),(2,0,0,0,'UTC'),(3,0,9000,0,'MMT'),(3,1,12648,1,'MST'),(3,2,9048,0,'MMT'),(3,3,16248,1,'MDST'),(3,4,10800,0,'MSK'),(3,5,14400,1,'MSD'),(3,6,18000,1,'MSD'),(3,7,7200,0,'EET'),(3,8,10800,0,'MSK'),(3,9,14400,1,'MSD'),(3,10,10800,1,'EEST'),(3,11,7200,0,'EET'),(4,0,9000,0,'MMT'),(4,1,12648,1,'MST'),(4,2,9048,0,'MMT'),(4,3,16248,1,'MDST'),(4,4,10800,0,'MSK'),(4,5,14400,1,'MSD'),(4,6,18000,1,'MSD'),(4,7,7200,0,'EET'),(4,8,10800,0,'MSK'),(4,9,14400,1,'MSD'),(4,10,10800,1,'EEST'),(4,11,7200,0,'EET'),(5,0,32400,0,'CJT'),(5,1,32400,0,'JST'); /*!40000 ALTER TABLE `time_zone_transition_type` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index 19ffca17b1e..d0d054b5473 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -1,14 +1,10 @@ call mtr.add_suppression("@003f.frm' \\(errno: 22\\)"); +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); # Bug#37938 Test "mysqldump" lacks various insert statements # Turn off concurrent inserts to avoid random errors # NOTE: We reset the variable back to saved value at the end of test SET @OLD_CONCURRENT_INSERT = @@GLOBAL.CONCURRENT_INSERT; SET @@GLOBAL.CONCURRENT_INSERT = 0; -DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa, t3; -drop database if exists mysqldump_test_db; -drop database if exists db1; -drop database if exists db2; -drop view if exists v1, v2, v3; CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024; INSERT INTO t1 VALUES (1), (2); <?xml version="1.0"?> @@ -5726,6 +5722,315 @@ DELIMITER ; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; +# +# MDEV-20939: Race condition between mysqldump import and InnoDB +# persistent statistics calculation +# +# +# Without --replace and --insert-ignore +# + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8mb4 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `innodb_index_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `innodb_index_stats` ( + `database_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(199) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, + `stat_value` bigint(20) unsigned NOT NULL, + `sample_size` bigint(20) unsigned DEFAULT NULL, + `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, + PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `innodb_table_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `innodb_table_stats` ( + `database_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(199) COLLATE utf8_bin NOT NULL, + `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `n_rows` bigint(20) unsigned NOT NULL, + `clustered_index_size` bigint(20) unsigned NOT NULL, + `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, + PRIMARY KEY (`database_name`,`table_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `general_log` ( + `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `slow_log` ( + `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `query_time` time(6) NOT NULL, + `lock_time` time(6) NOT NULL, + `rows_sent` int(11) NOT NULL, + `rows_examined` int(11) NOT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `rows_affected` int(11) NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `transaction_registry` ( + `transaction_id` bigint(20) unsigned NOT NULL, + `commit_id` bigint(20) unsigned NOT NULL, + `begin_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `commit_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `isolation_level` enum('READ-UNCOMMITTED','READ-COMMITTED','REPEATABLE-READ','SERIALIZABLE') COLLATE utf8_bin NOT NULL, + PRIMARY KEY (`transaction_id`), + UNIQUE KEY `commit_id` (`commit_id`), + KEY `begin_timestamp` (`begin_timestamp`), + KEY `commit_timestamp` (`commit_timestamp`,`transaction_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +# +# With --replace +# + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8mb4 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `innodb_index_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `innodb_index_stats` ( + `database_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(199) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, + `stat_value` bigint(20) unsigned NOT NULL, + `sample_size` bigint(20) unsigned DEFAULT NULL, + `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, + PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `innodb_index_stats` WRITE; +/*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */; +/*!40000 ALTER TABLE `innodb_index_stats` ENABLE KEYS */; +UNLOCK TABLES; +DROP TABLE IF EXISTS `innodb_table_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `innodb_table_stats` ( + `database_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(199) COLLATE utf8_bin NOT NULL, + `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `n_rows` bigint(20) unsigned NOT NULL, + `clustered_index_size` bigint(20) unsigned NOT NULL, + `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, + PRIMARY KEY (`database_name`,`table_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `innodb_table_stats` WRITE; +/*!40000 ALTER TABLE `innodb_table_stats` DISABLE KEYS */; +/*!40000 ALTER TABLE `innodb_table_stats` ENABLE KEYS */; +UNLOCK TABLES; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `general_log` ( + `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `slow_log` ( + `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `query_time` time(6) NOT NULL, + `lock_time` time(6) NOT NULL, + `rows_sent` int(11) NOT NULL, + `rows_examined` int(11) NOT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `rows_affected` int(11) NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `transaction_registry` ( + `transaction_id` bigint(20) unsigned NOT NULL, + `commit_id` bigint(20) unsigned NOT NULL, + `begin_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `commit_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `isolation_level` enum('READ-UNCOMMITTED','READ-COMMITTED','REPEATABLE-READ','SERIALIZABLE') COLLATE utf8_bin NOT NULL, + PRIMARY KEY (`transaction_id`), + UNIQUE KEY `commit_id` (`commit_id`), + KEY `begin_timestamp` (`begin_timestamp`), + KEY `commit_timestamp` (`commit_timestamp`,`transaction_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +# +# With --insert-ignore +# + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8mb4 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `innodb_index_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `innodb_index_stats` ( + `database_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(199) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, + `stat_value` bigint(20) unsigned NOT NULL, + `sample_size` bigint(20) unsigned DEFAULT NULL, + `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, + PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `innodb_index_stats` WRITE; +/*!40000 ALTER TABLE `innodb_index_stats` DISABLE KEYS */; +/*!40000 ALTER TABLE `innodb_index_stats` ENABLE KEYS */; +UNLOCK TABLES; +DROP TABLE IF EXISTS `innodb_table_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `innodb_table_stats` ( + `database_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(199) COLLATE utf8_bin NOT NULL, + `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `n_rows` bigint(20) unsigned NOT NULL, + `clustered_index_size` bigint(20) unsigned NOT NULL, + `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, + PRIMARY KEY (`database_name`,`table_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `innodb_table_stats` WRITE; +/*!40000 ALTER TABLE `innodb_table_stats` DISABLE KEYS */; +/*!40000 ALTER TABLE `innodb_table_stats` ENABLE KEYS */; +UNLOCK TABLES; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `general_log` ( + `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `slow_log` ( + `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `query_time` time(6) NOT NULL, + `lock_time` time(6) NOT NULL, + `rows_sent` int(11) NOT NULL, + `rows_examined` int(11) NOT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `rows_affected` int(11) NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `transaction_registry` ( + `transaction_id` bigint(20) unsigned NOT NULL, + `commit_id` bigint(20) unsigned NOT NULL, + `begin_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `commit_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', + `isolation_level` enum('READ-UNCOMMITTED','READ-COMMITTED','REPEATABLE-READ','SERIALIZABLE') COLLATE utf8_bin NOT NULL, + PRIMARY KEY (`transaction_id`), + UNIQUE KEY `commit_id` (`commit_id`), + KEY `begin_timestamp` (`begin_timestamp`), + KEY `commit_timestamp` (`commit_timestamp`,`transaction_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + # End of 10.2 tests # # Test for Invisible columns diff --git a/mysql-test/main/mysqldump.test b/mysql-test/main/mysqldump.test index f328bf32361..799f24665ee 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -19,12 +19,7 @@ let collation=utf8_unicode_ci; # There are tables in 'mysql' database of type innodb --source include/have_innodb.inc -# This test is slow on buildbot. ---source include/big_test.inc - -disable_query_log; call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); -enable_query_log; --echo # Bug#37938 Test "mysqldump" lacks various insert statements --echo # Turn off concurrent inserts to avoid random errors @@ -32,15 +27,6 @@ enable_query_log; SET @OLD_CONCURRENT_INSERT = @@GLOBAL.CONCURRENT_INSERT; SET @@GLOBAL.CONCURRENT_INSERT = 0; - ---disable_warnings -DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa, t3; -drop database if exists mysqldump_test_db; -drop database if exists db1; -drop database if exists db2; -drop view if exists v1, v2, v3; ---enable_warnings - # XML output CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024; @@ -2747,6 +2733,29 @@ INSERT INTO t1 (a) VALUES (1),(2),(3); --exec $MYSQL_DUMP --default-character-set=utf8mb4 --triggers --no-data --no-create-info --add-drop-trigger --skip-comments --databases test DROP TABLE t1; +--echo # +--echo # MDEV-20939: Race condition between mysqldump import and InnoDB +--echo # persistent statistics calculation +--echo # + +--let $ignore= --ignore-table=mysql.proxies_priv --ignore-table=mysql.user --ignore-table=mysql.global_priv --ignore-table=mysql.column_stats --ignore-table=mysql.columns_priv --ignore-table=mysql.db --ignore-table=mysql.event --ignore-table=mysql.func --ignore-table=mysql.gtid_slave_pos --ignore-table=mysql.help_category --ignore-table=mysql.help_keyword --ignore-table=mysql.help_relation --ignore-table=mysql.help_topic --ignore-table=mysql.host --ignore-table=mysql.index_stats --ignore-table=mysql.plugin --ignore-table=mysql.proc --ignore-table=mysql.procs_priv --ignore-table=mysql.roles_mapping --ignore-table=mysql.servers --ignore-table=mysql.table_stats --ignore-table=mysql.tables_priv --ignore-table=mysql.time_zone --ignore-table=mysql.time_zone_leap_second --ignore-table=mysql.time_zone_name --ignore-table=mysql.time_zone_transition --ignore-table=mysql.time_zone_transition_type --ignore-table=mysql.general_log --ignore-table=mysql.slow_log +--let $skip_opts= --skip-dump-date --skip-comments + +--echo # +--echo # Without --replace and --insert-ignore +--echo # +--exec $MYSQL_DUMP $ignore $skip_opts mysql + +--echo # +--echo # With --replace +--echo # +--exec $MYSQL_DUMP $ignore $skip_opts --replace mysql + +--echo # +--echo # With --insert-ignore +--echo # +--exec $MYSQL_DUMP $ignore $skip_opts --insert-ignore mysql + --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/ps_show_log.result b/mysql-test/main/ps_show_log.result new file mode 100644 index 00000000000..54eabaeded6 --- /dev/null +++ b/mysql-test/main/ps_show_log.result @@ -0,0 +1,65 @@ +# +# MDEV-24208 SHOW RELAYLOG EVENTS command is not supported in the prepared +# statement protocol yet +# +CREATE USER u1; +include/master-slave.inc +[connection master] +connection master; +CREATE TABLE t1(n INT); +DROP TABLE t1; +connection slave; +PREPARE stmt_1 FROM 'SHOW BINLOG EVENTS'; +EXECUTE stmt_1; +Log_name Pos Event_type Server_id End_log_pos Info +# # Format_desc # # # +# # Gtid_list # # [] +# # Binlog_checkpoint # # # +# # Gtid # # GTID 0-1-1 +# # Query # # use `test`; CREATE TABLE t1(n INT) +# # Gtid # # GTID 0-1-2 +# # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +# Execute the same prepared statement the second time to check that +# no internal structures used for handling the statement +# 'SHOW BINLOG EVENTS' were damaged. +EXECUTE stmt_1; +Log_name Pos Event_type Server_id End_log_pos Info +# # Format_desc # # # +# # Gtid_list # # [] +# # Binlog_checkpoint # # # +# # Gtid # # GTID 0-1-1 +# # Query # # use `test`; CREATE TABLE t1(n INT) +# # Gtid # # GTID 0-1-2 +# # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ +DEALLOCATE PREPARE stmt_1; +connection slave; +PREPARE stmt_1 FROM 'SHOW RELAYLOG EVENTS'; +EXECUTE stmt_1; +Log_name Pos Event_type Server_id End_log_pos Info +slave-relay-bin.000001 # Format_desc # # # +slave-relay-bin.000001 # Rotate # # # +# Execute the same prepared statement the second time to check that +# no internal structures used for handling the statement +# 'SHOW RELAYLOG EVENTS' were damaged. +EXECUTE stmt_1; +Log_name Pos Event_type Server_id End_log_pos Info +slave-relay-bin.000001 # Format_desc # # # +slave-relay-bin.000001 # Rotate # # # +DEALLOCATE PREPARE stmt_1; +# Create the user u1 without the REPLICATION SLAVE privilege required +# for running the statements SHOW BINLOG EVENTS/SHOW RELAYLOG EVENTS +# and check that attempt to execute the statements SHOW BINLOG EVENTS/ +# SHOW RELAYLOG EVENTS as a prepred statements by a user without required +# privileges results in error. +connect con2,localhost,u1,,test; +PREPARE stmt_1 FROM 'SHOW BINLOG EVENTS'; +EXECUTE stmt_1; +ERROR 42000: Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation +PREPARE stmt_1 FROM 'SHOW RELAYLOG EVENTS'; +EXECUTE stmt_1; +ERROR 42000: Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation +DEALLOCATE PREPARE stmt_1; +include/rpl_end.inc +connection default; +DROP USER u1; +# End of 10.2 tests diff --git a/mysql-test/main/ps_show_log.test b/mysql-test/main/ps_show_log.test new file mode 100644 index 00000000000..95000d2d7e0 --- /dev/null +++ b/mysql-test/main/ps_show_log.test @@ -0,0 +1,73 @@ +--echo # +--echo # MDEV-24208 SHOW RELAYLOG EVENTS command is not supported in the prepared +--echo # statement protocol yet +--echo # + +CREATE USER u1; + +--source include/have_binlog_format_statement.inc +--source include/master-slave.inc +--connection master +CREATE TABLE t1(n INT); + +DROP TABLE t1; + +--sync_slave_with_master +PREPARE stmt_1 FROM 'SHOW BINLOG EVENTS'; + +--replace_column 2 # 4 # 5 # +--replace_regex /Server ver:.*Binlog ver: .*/#/ /slave-bin.*/#/ +EXECUTE stmt_1; + +--echo # Execute the same prepared statement the second time to check that +--echo # no internal structures used for handling the statement +--echo # 'SHOW BINLOG EVENTS' were damaged. + +--replace_column 2 # 4 # 5 # +--replace_regex /Server ver:.*Binlog ver: .*/#/ /slave-bin.*/#/ +EXECUTE stmt_1; + +DEALLOCATE PREPARE stmt_1; + +--connection slave +PREPARE stmt_1 FROM 'SHOW RELAYLOG EVENTS'; +--replace_column 2 # 4 # 5 # +--replace_regex /Server ver:.*Binlog ver: .*/#/ /slave-relay-bin.*;pos=.*/#/ +EXECUTE stmt_1; + +--echo # Execute the same prepared statement the second time to check that +--echo # no internal structures used for handling the statement +--echo # 'SHOW RELAYLOG EVENTS' were damaged. + +--replace_column 2 # 4 # 5 # +--replace_regex /Server ver:.*Binlog ver: .*/#/ /slave-relay-bin.*;pos=.*/#/ +EXECUTE stmt_1; + +DEALLOCATE PREPARE stmt_1; + +--echo # Create the user u1 without the REPLICATION SLAVE privilege required +--echo # for running the statements SHOW BINLOG EVENTS/SHOW RELAYLOG EVENTS +--echo # and check that attempt to execute the statements SHOW BINLOG EVENTS/ +--echo # SHOW RELAYLOG EVENTS as a prepred statements by a user without required +--echo # privileges results in error. + +--connect (con2,localhost,u1,,test) +PREPARE stmt_1 FROM 'SHOW BINLOG EVENTS'; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +EXECUTE stmt_1; + +PREPARE stmt_1 FROM 'SHOW RELAYLOG EVENTS'; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +EXECUTE stmt_1; + +DEALLOCATE PREPARE stmt_1; + +--source include/rpl_end.inc + +--connection default +# Clean up +DROP USER u1; + +--echo # End of 10.2 tests diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index fca3e84f35d..60c79c02c44 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3244,6 +3244,23 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR id a b code num DROP TABLE t1, t2; # +# MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value +# +create table t1 (pk int, i int, v int, primary key (pk), key(v)); +insert into t1 (pk,i,v) values (1,1,2),(2,2,4),(3,3,6),(4,4,8),(5,5,10),(6,6,12),(7,7,14),(8,8,16); +create table t2 (a int, b int); +insert into t2 values (1,2),(2,4); +EXPLAIN +select * from t1 inner join t2 on ( t2.b = t1.v or t2.a = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ALL PRIMARY,v NULL NULL NULL 8 Range checked for each record (index map: 0x3) +select * from t1 inner join t2 on ( t2.b = t1.v or t2.a = t1.pk); +pk i v a b +1 1 2 1 2 +2 2 4 2 4 +drop table t1, t2; +# # End of 10.2 tests # # diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index a78b263f717..d06053d07e3 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2214,6 +2214,20 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR DROP TABLE t1, t2; + +--echo # +--echo # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value +--echo # + +create table t1 (pk int, i int, v int, primary key (pk), key(v)); +insert into t1 (pk,i,v) values (1,1,2),(2,2,4),(3,3,6),(4,4,8),(5,5,10),(6,6,12),(7,7,14),(8,8,16); +create table t2 (a int, b int); +insert into t2 values (1,2),(2,4); +EXPLAIN +select * from t1 inner join t2 on ( t2.b = t1.v or t2.a = t1.pk); +select * from t1 inner join t2 on ( t2.b = t1.v or t2.a = t1.pk); +drop table t1, t2; + --echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 736eaa741c0..257314539c5 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3241,6 +3241,23 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR id a b code num DROP TABLE t1, t2; # +# MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value +# +create table t1 (pk int, i int, v int, primary key (pk), key(v)); +insert into t1 (pk,i,v) values (1,1,2),(2,2,4),(3,3,6),(4,4,8),(5,5,10),(6,6,12),(7,7,14),(8,8,16); +create table t2 (a int, b int); +insert into t2 values (1,2),(2,4); +EXPLAIN +select * from t1 inner join t2 on ( t2.b = t1.v or t2.a = t1.pk); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ALL PRIMARY,v NULL NULL NULL 8 Range checked for each record (index map: 0x3) +select * from t1 inner join t2 on ( t2.b = t1.v or t2.a = t1.pk); +pk i v a b +1 1 2 1 2 +2 2 4 2 4 +drop table t1, t2; +# # End of 10.2 tests # # diff --git a/mysql-test/main/skip_grants-master.opt b/mysql-test/main/skip_grants.opt index 5699a3387b8..5699a3387b8 100644 --- a/mysql-test/main/skip_grants-master.opt +++ b/mysql-test/main/skip_grants.opt diff --git a/mysql-test/main/skip_grants.result b/mysql-test/main/skip_grants.result index 154f77fff76..a8633807571 100644 --- a/mysql-test/main/skip_grants.result +++ b/mysql-test/main/skip_grants.result @@ -48,10 +48,16 @@ DROP PROCEDURE p3; DROP FUNCTION f1; DROP FUNCTION f2; DROP FUNCTION f3; +# +# Bug #26807 "set global event_scheduler=1" and --skip-grant-tables crashes server +# set global event_scheduler=1; Warnings: Note 1408 Event Scheduler: Loaded 0 events set global event_scheduler=0; +# +# Bug#26285 Selecting information_schema crahes server +# select count(*) from information_schema.COLUMN_PRIVILEGES; count(*) 0 @@ -64,14 +70,21 @@ count(*) select count(*) from information_schema.USER_PRIVILEGES; count(*) 0 -End of 5.0 tests +# +# End of 5.0 tests +# # # Bug#29817 Queries with UDF fail with non-descriptive error # if mysql.proc is missing # select no_such_function(1); ERROR 42000: FUNCTION test.no_such_function does not exist -End of 5.1 tests +# +# End of 5.1 tests +# +# +# MDEV-8280 crash in 'show global status' with --skip-grant-tables +# show global status like 'Acl%'; Variable_name Value Acl_column_grants 0 @@ -85,6 +98,17 @@ Acl_role_grants 0 Acl_roles 0 Acl_table_grants 0 Acl_users 0 +# +# End of 10.1 tests +# +# +# MDEV-22966 Server crashes or hangs with SET ROLE when started with skip-grant-tables +# +set role x; +ERROR HY000: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement +# +# End of 10.2 tests +# show create user root@localhost; ERROR HY000: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement insert mysql.global_priv values ('foo', 'bar', '{}'); @@ -112,3 +136,6 @@ CREATE USER `baz`@`baz` IDENTIFIED BY PASSWORD '*E52096EF8EB0240275A7FE9E069101C drop user bar@foo; drop user baz@baz; # restart +# +# End of 10.3 tests +# diff --git a/mysql-test/main/skip_grants.test b/mysql-test/main/skip_grants.test index ccad3c2d13f..eb8d3c3df88 100644 --- a/mysql-test/main/skip_grants.test +++ b/mysql-test/main/skip_grants.test @@ -89,20 +89,23 @@ DROP FUNCTION f1; DROP FUNCTION f2; DROP FUNCTION f3; -# -# Bug #26807 "set global event_scheduler=1" and --skip-grant-tables crashes server -# +--echo # +--echo # Bug #26807 "set global event_scheduler=1" and --skip-grant-tables crashes server +--echo # set global event_scheduler=1; set global event_scheduler=0; -# -# Bug#26285 Selecting information_schema crahes server -# +--echo # +--echo # Bug#26285 Selecting information_schema crahes server +--echo # select count(*) from information_schema.COLUMN_PRIVILEGES; select count(*) from information_schema.SCHEMA_PRIVILEGES; select count(*) from information_schema.TABLE_PRIVILEGES; select count(*) from information_schema.USER_PRIVILEGES; ---echo End of 5.0 tests + +--echo # +--echo # End of 5.0 tests +--echo # --echo # --echo # Bug#29817 Queries with UDF fail with non-descriptive error @@ -111,13 +114,30 @@ select count(*) from information_schema.USER_PRIVILEGES; --error ER_SP_DOES_NOT_EXIST select no_such_function(1); ---echo End of 5.1 tests +--echo # +--echo # End of 5.1 tests +--echo # -# -# MDEV-8280 crash in 'show global status' with --skip-grant-tables -# +--echo # +--echo # MDEV-8280 crash in 'show global status' with --skip-grant-tables +--echo # show global status like 'Acl%'; +--echo # +--echo # End of 10.1 tests +--echo # + +--echo # +--echo # MDEV-22966 Server crashes or hangs with SET ROLE when started with skip-grant-tables +--echo # + +--error ER_OPTION_PREVENTS_STATEMENT +set role x; + +--echo # +--echo # End of 10.2 tests +--echo # + # # MDEV-18297 # How to reset a forgotten root password @@ -140,3 +160,7 @@ drop user bar@foo; drop user baz@baz; # need to restart the server to restore the --skip-grant state --source include/restart_mysqld.inc + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/main/sp-ucs2.result b/mysql-test/main/sp-ucs2.result index 389fa946ad5..047a64713af 100644 --- a/mysql-test/main/sp-ucs2.result +++ b/mysql-test/main/sp-ucs2.result @@ -100,20 +100,20 @@ RETURNS VARCHAR(64) CHARACTER SET ucs2 BEGIN RETURN 'str'; END| -ERROR 42000: COLLATION 'ucs2_unicode_ci' is not valid for CHARACTER SET 'latin1' +DROP FUNCTION f| CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2) RETURNS VARCHAR(64) COLLATE ucs2_unicode_ci BEGIN RETURN 'str'; END| -ERROR 42000: COLLATION 'ucs2_unicode_ci' is not valid for CHARACTER SET 'latin1' +DROP FUNCTION f| CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2) RETURNS VARCHAR(64) CHARACTER SET ucs2 BEGIN DECLARE f2 VARCHAR(64) COLLATE ucs2_unicode_ci; RETURN 'str'; END| -ERROR 42000: COLLATION 'ucs2_unicode_ci' is not valid for CHARACTER SET 'latin1' +DROP FUNCTION f| SET NAMES utf8; CREATE FUNCTION bug48766 () RETURNS ENUM( 'w' ) CHARACTER SET ucs2 diff --git a/mysql-test/main/sp-ucs2.test b/mysql-test/main/sp-ucs2.test index 3276da3e257..c6dbdaacb5e 100644 --- a/mysql-test/main/sp-ucs2.test +++ b/mysql-test/main/sp-ucs2.test @@ -114,35 +114,35 @@ DROP FUNCTION f1| # # COLLATE with no CHARACTER SET in IN param # ---error ER_COLLATION_CHARSET_MISMATCH CREATE FUNCTION f(f1 VARCHAR(64) COLLATE ucs2_unicode_ci) RETURNS VARCHAR(64) CHARACTER SET ucs2 BEGIN RETURN 'str'; END| +DROP FUNCTION f| # # COLLATE with no CHARACTER SET in RETURNS # ---error ER_COLLATION_CHARSET_MISMATCH CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2) RETURNS VARCHAR(64) COLLATE ucs2_unicode_ci BEGIN RETURN 'str'; END| +DROP FUNCTION f| # # COLLATE with no CHARACTER SET in DECLARE # ---error ER_COLLATION_CHARSET_MISMATCH CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2) RETURNS VARCHAR(64) CHARACTER SET ucs2 BEGIN DECLARE f2 VARCHAR(64) COLLATE ucs2_unicode_ci; RETURN 'str'; END| +DROP FUNCTION f| delimiter ;| diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 55d8bbf51c9..53557b39b05 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -829,6 +829,20 @@ length(a) set names latin1; set @@use_stat_tables=@save_use_stat_tables; drop table t1; +# +# MDEV-23753: SIGSEGV in Column_stat::store_stat_fields +# +CREATE TABLE t1 (a INT, b INT) PARTITION BY HASH (b) PARTITIONS 2; +LOCK TABLES t1 WRITE; +ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a) INDEXES (); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (nonexisting) INDEXES (nonexisting); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze error Invalid argument +DROP TABLE t1; # please keep this at the last set @@global.histogram_size=@save_histogram_size; # Start of 10.4 tests diff --git a/mysql-test/main/stat_tables.test b/mysql-test/main/stat_tables.test index ca341a93b81..9955908bd60 100644 --- a/mysql-test/main/stat_tables.test +++ b/mysql-test/main/stat_tables.test @@ -1,4 +1,5 @@ --source include/have_stat_tables.inc +--source include/have_partition.inc select @@global.use_stat_tables; select @@session.use_stat_tables; @@ -573,6 +574,17 @@ set names latin1; set @@use_stat_tables=@save_use_stat_tables; drop table t1; +--echo # +--echo # MDEV-23753: SIGSEGV in Column_stat::store_stat_fields +--echo # + +CREATE TABLE t1 (a INT, b INT) PARTITION BY HASH (b) PARTITIONS 2; +LOCK TABLES t1 WRITE; +ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a) INDEXES (); +ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (nonexisting) INDEXES (nonexisting); +DROP TABLE t1; + + --echo # please keep this at the last set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 26d4bff6be9..41fd303ed4c 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -861,6 +861,20 @@ length(a) set names latin1; set @@use_stat_tables=@save_use_stat_tables; drop table t1; +# +# MDEV-23753: SIGSEGV in Column_stat::store_stat_fields +# +CREATE TABLE t1 (a INT, b INT) PARTITION BY HASH (b) PARTITIONS 2; +LOCK TABLES t1 WRITE; +ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a) INDEXES (); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (nonexisting) INDEXES (nonexisting); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze error Invalid argument +DROP TABLE t1; # please keep this at the last set @@global.histogram_size=@save_histogram_size; # Start of 10.4 tests diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result index 41928f28963..b49e7b11ed6 100644 --- a/mysql-test/main/table_elim.result +++ b/mysql-test/main/table_elim.result @@ -544,7 +544,7 @@ drop table t0,t1,t2,t3,t4,t5,t6; CREATE TABLE t1 (f1 int(11), PRIMARY KEY (f1)) ; CREATE TABLE t2 (f4 varchar(1024), KEY (f4)) ; Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes +Note 1071 Specified key was too long; max key length is 1000 bytes INSERT IGNORE INTO t2 VALUES ('xcddwntkbxyorzdv'), ('cnxxcddwntkbxyor'),('r'),('r'), ('did'),('I'),('when'), ('hczkfqjeggivdvac'),('e'),('okay'),('up'); diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 86e8b9cf76d..5f176fcd3eb 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2622,6 +2622,62 @@ ERROR HY000: 'ignore' is not allowed in this context EXECUTE IMMEDIATE 'VALUES (?)' USING DEFAULT; ERROR HY000: 'default' is not allowed in this context # +# MDEV-24675: TVC using subqueries +# +values((select 1)); +(select 1) +1 +values (2), ((select 1)); +2 +2 +1 +values ((select 1)), (2), ((select 3)); +(select 1) +1 +2 +3 +values ((select 1), 2), (3,4), (5, (select 6)); +(select 1) 2 +1 2 +3 4 +5 6 +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3), (3,2), (1,2); +values((select max(a) from t1)); +(select max(a) from t1) +3 +values((select min(b) from t1)); +(select min(b) from t1) +2 +values ((select max(a) from t1), (select min(b) from t1)); +(select max(a) from t1) (select min(b) from t1) +3 2 +values((select * from (select max(b) from t1) as t)); +(select * from (select max(b) from t1) as t) +3 +drop table t1; +# +# MDEV-24618: TVC contains extra parenthesis for row expressions +# in value list +# +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3); +insert into t1 values ((5,4)); +ERROR 21000: Operand should contain 1 column(s) +values ((1,2)); +ERROR 21000: Operand should contain 1 column(s) +select * from (values ((1,2))) dt; +ERROR 21000: Operand should contain 1 column(s) +values (1,2); +1 2 +1 2 +values ((select min(a), max(b) from t1)); +ERROR 21000: Operand should contain 1 column(s) +drop table t1; +# +# End of 10.3 tests +# +# # MDEV-22610 Crash in INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)) # VALUES (DEFAULT) UNION VALUES (DEFAULT); @@ -2634,3 +2690,6 @@ ERROR HY000: 'default' is not allowed in this context INSERT INTO t1 (VALUES (IGNORE) UNION VALUES (IGNORE)); ERROR HY000: 'ignore' is not allowed in this context DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index bd5e4d75904..4cbca891e57 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1354,6 +1354,56 @@ EXECUTE IMMEDIATE 'VALUES (?)' USING IGNORE; --error ER_UNKNOWN_ERROR EXECUTE IMMEDIATE 'VALUES (?)' USING DEFAULT; +--echo # +--echo # MDEV-24675: TVC using subqueries +--echo # + +values((select 1)); + +values (2), ((select 1)); + +values ((select 1)), (2), ((select 3)); + +values ((select 1), 2), (3,4), (5, (select 6)); + +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3), (3,2), (1,2); + +values((select max(a) from t1)); + +values((select min(b) from t1)); + +values ((select max(a) from t1), (select min(b) from t1)); + +values((select * from (select max(b) from t1) as t)); + +drop table t1; + +--echo # +--echo # MDEV-24618: TVC contains extra parenthesis for row expressions +--echo # in value list +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3); +--error ER_OPERAND_COLUMNS +insert into t1 values ((5,4)); + +--error ER_OPERAND_COLUMNS +values ((1,2)); + +--error ER_OPERAND_COLUMNS +select * from (values ((1,2))) dt; + +values (1,2); +--error ER_OPERAND_COLUMNS +values ((select min(a), max(b) from t1)); + +drop table t1; + +--echo # +--echo # End of 10.3 tests +--echo # --echo # --echo # MDEV-22610 Crash in INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)) @@ -1369,3 +1419,7 @@ INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)); --error ER_UNKNOWN_ERROR INSERT INTO t1 (VALUES (IGNORE) UNION VALUES (IGNORE)); DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/user_limits.result b/mysql-test/main/user_limits.result index ffb8bb204a6..acb34754caa 100644 --- a/mysql-test/main/user_limits.result +++ b/mysql-test/main/user_limits.result @@ -186,3 +186,30 @@ connection default; drop user mysqltest_1@localhost; drop table t1; set global max_user_connections= @my_max_user_connections; +# +# End of 10.1 tests +# +# +# MDEV-17852 Altered connection limits for user have no effect +# +create user foo@'%' with max_user_connections 1; +connect con1,localhost,foo; +select current_user(); +current_user() +foo@% +connect(localhost,foo,,test,MYSQL_PORT,MYSQL_SOCK); +connect con2,localhost,foo; +ERROR 42000: User 'foo' has exceeded the 'max_user_connections' resource (current value: 1) +connection default; +alter user foo with max_user_connections 2; +connect con3,localhost,foo; +select current_user(); +current_user() +foo@% +disconnect con3; +disconnect con1; +connection default; +drop user foo@'%'; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/user_limits.test b/mysql-test/main/user_limits.test index ebb4fd4fb88..36524febd8d 100644 --- a/mysql-test/main/user_limits.test +++ b/mysql-test/main/user_limits.test @@ -216,3 +216,29 @@ drop table t1; --source include/wait_until_count_sessions.inc set global max_user_connections= @my_max_user_connections; + +--echo # +--echo # End of 10.1 tests +--echo # + +--echo # +--echo # MDEV-17852 Altered connection limits for user have no effect +--echo # +create user foo@'%' with max_user_connections 1; +--connect con1,localhost,foo +select current_user(); +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +--error ER_USER_LIMIT_REACHED +--connect con2,localhost,foo +--connection default +alter user foo with max_user_connections 2; +--connect con3,localhost,foo +select current_user(); +--disconnect con3 +--disconnect con1 +--connection default +drop user foo@'%'; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 7e9dd1837f6..49e57aca219 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6758,6 +6758,22 @@ drop database db1; create database test; use test; # +# MDEV-16940: update of multi-table view returning error used in SP +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2), (3); +CREATE VIEW v1 AS SELECT a, b FROM t1,t2; +CREATE PROCEDURE sp1() UPDATE v1 SET a = 8, b = 9; +CALL sp1; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +CALL sp1; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +DROP PROCEDURE sp1; +DROP VIEW v1; +DROP TABLE t1, t2; +# # End of 10.2 tests # # diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 9e84f6dc677..47ede54e1c7 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6463,6 +6463,28 @@ create database test; use test; --echo # +--echo # MDEV-16940: update of multi-table view returning error used in SP +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2), (3); + +CREATE VIEW v1 AS SELECT a, b FROM t1,t2; + +CREATE PROCEDURE sp1() UPDATE v1 SET a = 8, b = 9; + +--error ER_VIEW_MULTIUPDATE +CALL sp1; +--error ER_VIEW_MULTIUPDATE +CALL sp1; + +DROP PROCEDURE sp1; +DROP VIEW v1; +DROP TABLE t1, t2; + +--echo # --echo # End of 10.2 tests --echo # |