diff options
| author | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2017-12-22 12:23:39 +0200 |
|---|---|---|
| committer | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2017-12-22 12:23:39 +0200 |
| commit | 985d2d393c285ecd4539cb579b680f7b91fdaa1a (patch) | |
| tree | 8cabf6c09758c30df04de96ca98c7827d5665493 /mysql-test | |
| parent | a76a2522ec4f8c069827e038997387a8b8eb3a3a (diff) | |
| parent | 461cf3e5a3c2d346d75b1407b285f8daf9d01f67 (diff) | |
| download | mariadb-git-985d2d393c285ecd4539cb579b680f7b91fdaa1a.tar.gz | |
Merge remote-tracking branch 'origin/10.1' into 10.2
Diffstat (limited to 'mysql-test')
31 files changed, 1503 insertions, 50 deletions
diff --git a/mysql-test/r/func_set.result b/mysql-test/r/func_set.result index 46be54ca6a4..73e121f5f2b 100644 --- a/mysql-test/r/func_set.result +++ b/mysql-test/r/func_set.result @@ -221,21 +221,11 @@ NULL DROP TABLE t1; # -# Start of 5.3 tests -# -# -# MDEV-4512 Valgrind warnings in my_long10_to_str_8bit on INTERVAL and DATE_ADD with incorrect types -# -CREATE TABLE t1 (pk INT PRIMARY KEY); -INSERT INTO t1 VALUES (10),(11); -SELECT INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) FROM t1; -INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) -8 -8 -Warnings: -Warning 1292 Incorrect datetime value: '10' for column 'pk' at row 1 -Warning 1292 Incorrect datetime value: '11' for column 'pk' at row 2 -DROP TABLE t1; -# -# End of 5.3 tests +# MDEV-14596 Crash in INTERVAL(ROW(..),ROW(..)) # +SELECT INTERVAL(ROW(1,1),ROW(1,2)); +ERROR 21000: Operand should contain 1 column(s) +SELECT INTERVAL(1,ROW(1,2)); +ERROR 21000: Operand should contain 1 column(s) +SELECT INTERVAL(ROW(1,2),1); +ERROR 21000: Operand should contain 1 column(s) diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index f893bb23f6b..9fee03877c5 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2667,6 +2667,31 @@ create table t2 (c1 int, c2 int); select t1.c1 as c1, t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1, t2.c2 having t1.c1 < 3; c1 c1 drop table t1, t2; +SET @old_sort_buff_size = @@sort_buffer_size; +SET @@sort_buffer_size=256*1024; +CREATE TABLE t1 (c INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(2011),(1977),(1982),(2027),(2023),(NULL),(NULL),(2004),(1974),(2032), +(1993),(NULL),(1995),(2034),(NULL),(2009),(1900),(NULL),(2025),(1900), +(2033),(1900),(2012),(NULL),(2009),(1992),(1974),(1974),(2012),(2028), +(2007),(2012),(1900),(1983),(1900),(2010),(1987),(1994),(1981),(2032), +(2010),(1989),(2014),(1900),(1900),(1976),(1978),(2007),(2030),(NULL), +(2002),(1997),(1900),(NULL),(2000),(2027),(1975),(2026),(1975),(2026), +(2029),(1977),(1900),(1900),(2031),(1993),(1986),(2012),(1979),(2013), +(1994),(2014),(2025),(2006),(1971),(1974),(2021),(2011),(NULL),(1991), +(2001),(1977),(2023),(2012),(1900),(1978),(1998),(NULL),(1988),(1999), +(2017),(2008),(1976),(1900),(2005),(2030),(2023),(1900),(1978),(1990), +(1978),(1987),(2030),(1900),(2034),(2006),(2015),(2001),(2019),(2024), +(2030),(1989),(1997),(2007),(2023),(1994),(1971),(2011),(2011),(2015), +(1984),(1978),(1979),(1989),(2008),(2030); +SELECT ExtractValue('<a></a>','/a') AS f1, SPACE(c) AS f2 FROM t1 GROUP BY f1, f2 WITH ROLLUP; +f1 f2 + NULL + + NULL +NULL NULL +SET @@sort_buffer_size = @old_sort_buff_size; +DROP TABLE t1; # # Bug #58782 # Missing rows with SELECT .. WHERE .. IN subquery diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result index b3d0f281415..fca4c43ed20 100644 --- a/mysql-test/r/having.result +++ b/mysql-test/r/having.result @@ -696,6 +696,21 @@ id column_1 expected -- 1 row(s) returned not ER_BAD_FIELD_ERROR drop table t1; # +# mdev-14368: grouping query with alias for aggregate function in HAVING +# when sql_mode = 'ONLY_FULL_GROUP_BY' +set @save_sql_mode= @@sql_mode; +set sql_mode = 'ONLY_FULL_GROUP_BY'; +create table t1(a int); +insert t1 values (4),(1),(2),(1), (3),(4); +SELECT a, COUNT(a) as ct FROM t1 GROUP BY a HAVING ct>0; +a ct +1 2 +2 1 +3 1 +4 2 +set sql_mode=@save_sql_mode; +drop table t1; +# # Bug mdev-5160: two-way join with HAVING over the second table # CREATE TABLE t1 (c1 varchar(6)) ENGINE=MyISAM; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 571dedfd77d..5072d3f781d 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2431,5 +2431,18 @@ Warnings: Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where `f1`(`test`.`t2`.`col3`,0) = 0 DROP FUNCTION f1; DROP TABLE t1,t2; +# +# MDEV-10397: Server crashes in key_copy with join_cache_level > 2 and join on BIT fields +# +CREATE TABLE t1 (b1 BIT NOT NULL); +INSERT INTO t1 VALUES (0),(1); +CREATE TABLE t2 (b2 BIT NOT NULL); +INSERT INTO t2 VALUES (0),(1); +SET SESSION JOIN_CACHE_LEVEL = 3; +SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2; +t1.b1+'0' t2.b2 + '0' +0 0 +1 1 +DROP TABLE t1, t2; # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index fac12729498..fbabff4a022 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2442,6 +2442,19 @@ Warnings: Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where `f1`(`test`.`t2`.`col3`,0) = 0 DROP FUNCTION f1; DROP TABLE t1,t2; +# +# MDEV-10397: Server crashes in key_copy with join_cache_level > 2 and join on BIT fields +# +CREATE TABLE t1 (b1 BIT NOT NULL); +INSERT INTO t1 VALUES (0),(1); +CREATE TABLE t2 (b2 BIT NOT NULL); +INSERT INTO t2 VALUES (0),(1); +SET SESSION JOIN_CACHE_LEVEL = 3; +SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2; +t1.b1+'0' t2.b2 + '0' +0 0 +1 1 +DROP TABLE t1, t2; # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 36b27871c95..5d154fb59f9 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5677,6 +5677,20 @@ PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3'; ERROR HY000: Can not insert into join view 'test.v2' without fields list drop view v1,v2; drop table t3; +# +# MDEV-14619: VIEW and GROUP_CONCAT +# +CREATE TABLE t1 (str text); +INSERT INTO t1 VALUES ("My"),("SQL"); +CREATE VIEW v1 AS SELECT GROUP_CONCAT(str SEPARATOR '\\') FROM t1; +SELECT * FROM v1; +GROUP_CONCAT(str SEPARATOR '\\') +My\SQL +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select group_concat(`t1`.`str` separator '\\') AS `GROUP_CONCAT(str SEPARATOR '\\')` from `t1` latin1 latin1_swedish_ci +drop view v1; +drop table t1; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/suite/innodb/r/innodb-autoinc.result b/mysql-test/suite/innodb/r/innodb-autoinc.result index 1d61079dbd8..ee1adc07661 100644 --- a/mysql-test/suite/innodb/r/innodb-autoinc.result +++ b/mysql-test/suite/innodb/r/innodb-autoinc.result @@ -1363,3 +1363,9 @@ SELECT * FROM t1; c1 1e19 DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-1); +SELECT * FROM t1; +a +-1 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-index-debug.result b/mysql-test/suite/innodb/r/innodb-index-debug.result index 172e4ebf454..b1f8c9ad3cc 100644 --- a/mysql-test/suite/innodb/r/innodb-index-debug.result +++ b/mysql-test/suite/innodb/r/innodb-index-debug.result @@ -112,6 +112,35 @@ t1 CREATE TABLE `t1` ( ) ENGINE=InnoDB AUTO_INCREMENT=1023 DEFAULT CHARSET=latin1 drop table t1; drop table t480; +# +# MDEV-12827 Assertion failure when reporting duplicate key error +# in online table rebuild +# +CREATE TABLE t1 (j INT UNIQUE, i INT UNIQUE) ENGINE=InnoDB; +connect con1,localhost,root,,test; +SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL built WAIT_FOR log'; +ALTER TABLE t1 DROP j, FORCE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR built'; +INSERT INTO t1 (i) VALUES (0),(0); +ERROR 23000: Duplicate entry '0' for key 'i' +SET DEBUG_SYNC='now SIGNAL log'; +connection con1; +ERROR 23000: Duplicate entry '0' for key 'i' +SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL built2 WAIT_FOR log2'; +ALTER TABLE t1 DROP j, FORCE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR built2'; +INSERT INTO t1 (i) VALUES (0),(1); +UPDATE t1 SET i=0; +ERROR 23000: Duplicate entry '0' for key 'i' +SET DEBUG_SYNC='now SIGNAL log2'; +connection con1; +ERROR 23000: Duplicate entry '0' for key 'i' +disconnect con1; +connection default; +SET DEBUG_SYNC='RESET'; +DROP TABLE t1; SET DEBUG_SYNC='RESET'; # # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test index fe7ac2c9657..db265ff8f67 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc.test @@ -695,3 +695,8 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES (); SELECT * FROM t1; DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-1); +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-index-debug.test b/mysql-test/suite/innodb/t/innodb-index-debug.test index 6927120fd5b..adbde0c982f 100644 --- a/mysql-test/suite/innodb/t/innodb-index-debug.test +++ b/mysql-test/suite/innodb/t/innodb-index-debug.test @@ -114,6 +114,45 @@ connection default; show create table t1; drop table t1; drop table t480; +--echo # +--echo # MDEV-12827 Assertion failure when reporting duplicate key error +--echo # in online table rebuild +--echo # + +CREATE TABLE t1 (j INT UNIQUE, i INT UNIQUE) ENGINE=InnoDB; +--connect (con1,localhost,root,,test) +SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL built WAIT_FOR log'; +--send +ALTER TABLE t1 DROP j, FORCE; + +--connection default +SET DEBUG_SYNC='now WAIT_FOR built'; +--error ER_DUP_ENTRY +INSERT INTO t1 (i) VALUES (0),(0); +SET DEBUG_SYNC='now SIGNAL log'; + +--connection con1 +--error ER_DUP_ENTRY +reap; +SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL built2 WAIT_FOR log2'; +--send +ALTER TABLE t1 DROP j, FORCE; + +--connection default +SET DEBUG_SYNC='now WAIT_FOR built2'; +INSERT INTO t1 (i) VALUES (0),(1); +--error ER_DUP_ENTRY +UPDATE t1 SET i=0; +SET DEBUG_SYNC='now SIGNAL log2'; + +--connection con1 +--error ER_DUP_ENTRY +reap; +--disconnect con1 +--connection default +SET DEBUG_SYNC='RESET'; +DROP TABLE t1; + SET DEBUG_SYNC='RESET'; --source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/mariabackup/apply-log-only-incr.result b/mysql-test/suite/mariabackup/apply-log-only-incr.result index 2baed8c1db9..114a471aa2c 100644 --- a/mysql-test/suite/mariabackup/apply-log-only-incr.result +++ b/mysql-test/suite/mariabackup/apply-log-only-incr.result @@ -3,6 +3,13 @@ CREATE TABLE t(a INT UNSIGNED PRIMARY KEY) ENGINE INNODB; INSERT INTO t VALUES(0); COMMIT; start transaction; +connect flush_log,localhost,root,,; +BEGIN; +DELETE FROM t LIMIT 1; +SET GLOBAL innodb_flush_log_at_trx_commit = 1; +ROLLBACK; +disconnect flush_log; +connection default; NOT FOUND /Rollback of trx with id/ in current_test # expect NOT FOUND NOT FOUND /Rollback of trx with id/ in current_test diff --git a/mysql-test/suite/mariabackup/apply-log-only-incr.test b/mysql-test/suite/mariabackup/apply-log-only-incr.test index aa5110cc51e..81c91d3c452 100644 --- a/mysql-test/suite/mariabackup/apply-log-only-incr.test +++ b/mysql-test/suite/mariabackup/apply-log-only-incr.test @@ -26,6 +26,13 @@ eval INSERT t VALUES(201-$n); dec $n; } --enable_query_log +connect (flush_log,localhost,root,,); +BEGIN; +DELETE FROM t LIMIT 1; +SET GLOBAL innodb_flush_log_at_trx_commit = 1; +ROLLBACK; +disconnect flush_log; +connection default; --disable_result_log exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --ftwrl-wait-timeout=5 --ftwrl-wait-threshold=300 --ftwrl-wait-query-type=all --target-dir=$incremental_dir --incremental-basedir=$basedir ; diff --git a/mysql-test/suite/parts/inc/part_alter_values.inc b/mysql-test/suite/parts/inc/part_alter_values.inc new file mode 100644 index 00000000000..0d4929d9820 --- /dev/null +++ b/mysql-test/suite/parts/inc/part_alter_values.inc @@ -0,0 +1,37 @@ +--echo # +--echo # MDEV-14641 Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine +--echo # + +--eval CREATE TABLE t1 (i INT) ENGINE=$engine PARTITION BY LIST(i) (PARTITION p0 VALUES IN (1), PARTITION p1 VALUES IN (2)); +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +ALTER TABLE t1 DROP PARTITION p1; +SELECT * FROM t1; + +# Cleanup +DROP TABLE t1; + + +--echo # +--echo # MDEV-13788 Server crash when issuing bad SQL partition syntax +--echo # + +--eval CREATE TABLE t1 (id int, d date) ENGINE=$engine PARTITION BY RANGE COLUMNS(d) (PARTITION p1 VALUES LESS THAN (MAXVALUE)) +SHOW CREATE TABLE t1; +--error ER_PARTITION_REQUIRES_VALUES_ERROR +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO +( + PARTITION p2, /* Notice no values */ + PARTITION p3 VALUES LESS THAN (MAXVALUE) +); +DROP TABLE t1; + + +--eval CREATE TABLE t1 (id int, d date) ENGINE=$engine PARTITION BY LIST (id) (PARTITION p1 VALUES IN (1,2,3)) +SHOW CREATE TABLE t1; +--error ER_PARTITION_REQUIRES_VALUES_ERROR +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO +( + PARTITION p2, /* Notice no values */ + PARTITION p3 VALUES IN (4,5,6) +); +DROP TABLE t1; diff --git a/mysql-test/suite/parts/r/partition_alter_innodb.result b/mysql-test/suite/parts/r/partition_alter_innodb.result new file mode 100644 index 00000000000..99697086170 --- /dev/null +++ b/mysql-test/suite/parts/r/partition_alter_innodb.result @@ -0,0 +1,44 @@ +# +# MDEV-14641 Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine +# +CREATE TABLE t1 (i INT) ENGINE=InnoDB PARTITION BY LIST(i) (PARTITION p0 VALUES IN (1), PARTITION p1 VALUES IN (2));; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +ALTER TABLE t1 DROP PARTITION p1; +SELECT * FROM t1; +i +DROP TABLE t1; +# +# MDEV-13788 Server crash when issuing bad SQL partition syntax +# +CREATE TABLE t1 (id int, d date) ENGINE=InnoDB PARTITION BY RANGE COLUMNS(d) (PARTITION p1 VALUES LESS THAN (MAXVALUE)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL, + `d` date DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY RANGE COLUMNS(`d`) +(PARTITION `p1` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO +( +PARTITION p2, /* Notice no values */ +PARTITION p3 VALUES LESS THAN (MAXVALUE) +); +ERROR HY000: Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition +DROP TABLE t1; +CREATE TABLE t1 (id int, d date) ENGINE=InnoDB PARTITION BY LIST (id) (PARTITION p1 VALUES IN (1,2,3)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL, + `d` date DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY LIST (`id`) +(PARTITION `p1` VALUES IN (1,2,3) ENGINE = InnoDB) +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO +( +PARTITION p2, /* Notice no values */ +PARTITION p3 VALUES IN (4,5,6) +); +ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition +DROP TABLE t1; diff --git a/mysql-test/suite/parts/r/partition_alter_maria.result b/mysql-test/suite/parts/r/partition_alter_maria.result index 6343566e408..7fed69bb5a5 100644 --- a/mysql-test/suite/parts/r/partition_alter_maria.result +++ b/mysql-test/suite/parts/r/partition_alter_maria.result @@ -16,3 +16,47 @@ select * from t1; pk dt 1 2017-09-28 15:12:00 drop table t1; +# +# MDEV-14641 Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine +# +CREATE TABLE t1 (i INT) ENGINE=Aria PARTITION BY LIST(i) (PARTITION p0 VALUES IN (1), PARTITION p1 VALUES IN (2));; +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +ALTER TABLE t1 DROP PARTITION p1; +SELECT * FROM t1; +i +DROP TABLE t1; +# +# MDEV-13788 Server crash when issuing bad SQL partition syntax +# +CREATE TABLE t1 (id int, d date) ENGINE=Aria PARTITION BY RANGE COLUMNS(d) (PARTITION p1 VALUES LESS THAN (MAXVALUE)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL, + `d` date DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 + PARTITION BY RANGE COLUMNS(`d`) +(PARTITION `p1` VALUES LESS THAN (MAXVALUE) ENGINE = Aria) +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO +( +PARTITION p2, /* Notice no values */ +PARTITION p3 VALUES LESS THAN (MAXVALUE) +); +ERROR HY000: Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition +DROP TABLE t1; +CREATE TABLE t1 (id int, d date) ENGINE=Aria PARTITION BY LIST (id) (PARTITION p1 VALUES IN (1,2,3)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL, + `d` date DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 + PARTITION BY LIST (`id`) +(PARTITION `p1` VALUES IN (1,2,3) ENGINE = Aria) +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO +( +PARTITION p2, /* Notice no values */ +PARTITION p3 VALUES IN (4,5,6) +); +ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition +DROP TABLE t1; diff --git a/mysql-test/suite/parts/r/partition_alter_myisam.result b/mysql-test/suite/parts/r/partition_alter_myisam.result index 514593fd4ef..12308e259e0 100644 --- a/mysql-test/suite/parts/r/partition_alter_myisam.result +++ b/mysql-test/suite/parts/r/partition_alter_myisam.result @@ -1,10 +1,44 @@ -CREATE TABLE t1 (i INT) ENGINE=MYISAM -PARTITION BY LIST(i) ( -PARTITION p0 VALUES IN (1), -PARTITION p1 VALUES IN (2) -); +# +# MDEV-14641 Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine +# +CREATE TABLE t1 (i INT) ENGINE=MyISAM PARTITION BY LIST(i) (PARTITION p0 VALUES IN (1), PARTITION p1 VALUES IN (2));; ALTER TABLE t1 ROW_FORMAT=COMPRESSED; ALTER TABLE t1 DROP PARTITION p1; SELECT * FROM t1; i DROP TABLE t1; +# +# MDEV-13788 Server crash when issuing bad SQL partition syntax +# +CREATE TABLE t1 (id int, d date) ENGINE=MyISAM PARTITION BY RANGE COLUMNS(d) (PARTITION p1 VALUES LESS THAN (MAXVALUE)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL, + `d` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY RANGE COLUMNS(`d`) +(PARTITION `p1` VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO +( +PARTITION p2, /* Notice no values */ +PARTITION p3 VALUES LESS THAN (MAXVALUE) +); +ERROR HY000: Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition +DROP TABLE t1; +CREATE TABLE t1 (id int, d date) ENGINE=MyISAM PARTITION BY LIST (id) (PARTITION p1 VALUES IN (1,2,3)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) DEFAULT NULL, + `d` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY LIST (`id`) +(PARTITION `p1` VALUES IN (1,2,3) ENGINE = MyISAM) +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO +( +PARTITION p2, /* Notice no values */ +PARTITION p3 VALUES IN (4,5,6) +); +ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition +DROP TABLE t1; diff --git a/mysql-test/suite/parts/t/partition_alter_innodb.test b/mysql-test/suite/parts/t/partition_alter_innodb.test new file mode 100644 index 00000000000..451bec05adc --- /dev/null +++ b/mysql-test/suite/parts/t/partition_alter_innodb.test @@ -0,0 +1,4 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--let $engine=InnoDB +--source inc/part_alter_values.inc diff --git a/mysql-test/suite/parts/t/partition_alter_maria.test b/mysql-test/suite/parts/t/partition_alter_maria.test index db249591158..e21f0dfab82 100644 --- a/mysql-test/suite/parts/t/partition_alter_maria.test +++ b/mysql-test/suite/parts/t/partition_alter_maria.test @@ -16,3 +16,6 @@ select * from t1; alter table t1 drop partition p20181231; select * from t1; drop table t1; + +--let $engine=Aria +--source inc/part_alter_values.inc diff --git a/mysql-test/suite/parts/t/partition_alter_myisam.test b/mysql-test/suite/parts/t/partition_alter_myisam.test index 91ce8d21327..a53fa333abd 100644 --- a/mysql-test/suite/parts/t/partition_alter_myisam.test +++ b/mysql-test/suite/parts/t/partition_alter_myisam.test @@ -1,17 +1,3 @@ -# -# MDEV-14641 Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine -# - --source include/have_partition.inc - -CREATE TABLE t1 (i INT) ENGINE=MYISAM -PARTITION BY LIST(i) ( - PARTITION p0 VALUES IN (1), - PARTITION p1 VALUES IN (2) -); -ALTER TABLE t1 ROW_FORMAT=COMPRESSED; -ALTER TABLE t1 DROP PARTITION p1; -SELECT * FROM t1; - -# Cleanup -DROP TABLE t1; +--let $engine=MyISAM +--source inc/part_alter_values.inc diff --git a/mysql-test/suite/plugins/r/binlog-simple_plugin_check.result b/mysql-test/suite/plugins/r/binlog-simple_plugin_check.result new file mode 100644 index 00000000000..6c960ee325c --- /dev/null +++ b/mysql-test/suite/plugins/r/binlog-simple_plugin_check.result @@ -0,0 +1,19 @@ +INSTALL SONAME "simple_password_check"; +SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS +WHERE PLUGIN_NAME='simple_password_check'; +PLUGIN_NAME +simple_password_check +# +# MDEV-14031 Password policy causes replication failure +# +CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85'; +CREATE USER user2@localhost IDENTIFIED BY 'bsg9#d.cem#!85'; +ERROR HY000: Your password does not satisfy the current policy requirements +DROP USER user1@localhost; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85' +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP USER user1@localhost +UNINSTALL PLUGIN simple_password_check; diff --git a/mysql-test/suite/plugins/t/binlog-simple_plugin_check.test b/mysql-test/suite/plugins/t/binlog-simple_plugin_check.test new file mode 100644 index 00000000000..773dafe8a81 --- /dev/null +++ b/mysql-test/suite/plugins/t/binlog-simple_plugin_check.test @@ -0,0 +1,31 @@ +--source include/not_embedded.inc +--source include/have_binlog_format_statement.inc + +if (!$SIMPLE_PASSWORD_CHECK_SO) { + skip No SIMPLE_PASSWORD_CHECK plugin; +} + +INSTALL SONAME "simple_password_check"; +SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS +WHERE PLUGIN_NAME='simple_password_check'; + + +--echo # +--echo # MDEV-14031 Password policy causes replication failure +--echo # + +--disable_query_log +RESET MASTER; # get rid of previous tests binlog +--enable_query_log + +CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85'; + +--error ER_NOT_VALID_PASSWORD +CREATE USER user2@localhost IDENTIFIED BY 'bsg9#d.cem#!85'; + +DROP USER user1@localhost; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; + +UNINSTALL PLUGIN simple_password_check; diff --git a/mysql-test/suite/roles/flush_roles-12366.result b/mysql-test/suite/roles/flush_roles-12366.result new file mode 100644 index 00000000000..f65c97e0ee3 --- /dev/null +++ b/mysql-test/suite/roles/flush_roles-12366.result @@ -0,0 +1,543 @@ +# +# MDEV-12366: FLUSH PRIVILEGES can break hierarchy of roles +# +# This testcase contains a user, who is granted a master role +# operations_cluster. operations_cluster is granted 8 different roles +# who in turn each have 4 different roles granted to them. +# +# Only the leaf roles contain privileges to access databases. +# Make sure the user has access to all databases if the master role +# is granted to him. +# +CREATE USER u; +CREATE ROLE operations_cluster; +GRANT operations_cluster TO u; +CREATE DATABASE bob_live_sg; +CREATE TABLE bob_live_sg.a (i INT(10)); +CREATE TABLE bob_live_sg.b (i INT(10)); +CREATE TABLE bob_live_sg.c (i INT(10)); +CREATE TABLE bob_live_sg.d (i INT(10)); +CREATE DATABASE oms_live_sg; +CREATE TABLE oms_live_sg.a (i INT(10)); +CREATE TABLE oms_live_sg.b (i INT(10)); +CREATE TABLE oms_live_sg.c (i INT(10)); +CREATE TABLE oms_live_sg.d (i INT(10)); +CREATE DATABASE bob_live_ph; +CREATE TABLE bob_live_ph.a (i INT(10)); +CREATE TABLE bob_live_ph.b (i INT(10)); +CREATE TABLE bob_live_ph.c (i INT(10)); +CREATE TABLE bob_live_ph.d (i INT(10)); +CREATE DATABASE oms_live_ph; +CREATE TABLE oms_live_ph.a (i INT(10)); +CREATE TABLE oms_live_ph.b (i INT(10)); +CREATE TABLE oms_live_ph.c (i INT(10)); +CREATE TABLE oms_live_ph.d (i INT(10)); +CREATE DATABASE bob_live_id; +CREATE TABLE bob_live_id.a (i INT(10)); +CREATE TABLE bob_live_id.b (i INT(10)); +CREATE TABLE bob_live_id.c (i INT(10)); +CREATE TABLE bob_live_id.d (i INT(10)); +CREATE DATABASE oms_live_id; +CREATE TABLE oms_live_id.a (i INT(10)); +CREATE TABLE oms_live_id.b (i INT(10)); +CREATE TABLE oms_live_id.c (i INT(10)); +CREATE TABLE oms_live_id.d (i INT(10)); +CREATE DATABASE bob_live_hk; +CREATE TABLE bob_live_hk.a (i INT(10)); +CREATE TABLE bob_live_hk.b (i INT(10)); +CREATE TABLE bob_live_hk.c (i INT(10)); +CREATE TABLE bob_live_hk.d (i INT(10)); +CREATE DATABASE oms_live_hk; +CREATE TABLE oms_live_hk.a (i INT(10)); +CREATE TABLE oms_live_hk.b (i INT(10)); +CREATE TABLE oms_live_hk.c (i INT(10)); +CREATE TABLE oms_live_hk.d (i INT(10)); +CREATE DATABASE bob_live_vn; +CREATE TABLE bob_live_vn.a (i INT(10)); +CREATE TABLE bob_live_vn.b (i INT(10)); +CREATE TABLE bob_live_vn.c (i INT(10)); +CREATE TABLE bob_live_vn.d (i INT(10)); +CREATE DATABASE oms_live_vn; +CREATE TABLE oms_live_vn.a (i INT(10)); +CREATE TABLE oms_live_vn.b (i INT(10)); +CREATE TABLE oms_live_vn.c (i INT(10)); +CREATE TABLE oms_live_vn.d (i INT(10)); +CREATE DATABASE bob_live_tw; +CREATE TABLE bob_live_tw.a (i INT(10)); +CREATE TABLE bob_live_tw.b (i INT(10)); +CREATE TABLE bob_live_tw.c (i INT(10)); +CREATE TABLE bob_live_tw.d (i INT(10)); +CREATE DATABASE oms_live_tw; +CREATE TABLE oms_live_tw.a (i INT(10)); +CREATE TABLE oms_live_tw.b (i INT(10)); +CREATE TABLE oms_live_tw.c (i INT(10)); +CREATE TABLE oms_live_tw.d (i INT(10)); +CREATE DATABASE bob_live_my; +CREATE TABLE bob_live_my.a (i INT(10)); +CREATE TABLE bob_live_my.b (i INT(10)); +CREATE TABLE bob_live_my.c (i INT(10)); +CREATE TABLE bob_live_my.d (i INT(10)); +CREATE DATABASE oms_live_my; +CREATE TABLE oms_live_my.a (i INT(10)); +CREATE TABLE oms_live_my.b (i INT(10)); +CREATE TABLE oms_live_my.c (i INT(10)); +CREATE TABLE oms_live_my.d (i INT(10)); +CREATE DATABASE bob_live_th; +CREATE TABLE bob_live_th.a (i INT(10)); +CREATE TABLE bob_live_th.b (i INT(10)); +CREATE TABLE bob_live_th.c (i INT(10)); +CREATE TABLE bob_live_th.d (i INT(10)); +CREATE DATABASE oms_live_th; +CREATE TABLE oms_live_th.a (i INT(10)); +CREATE TABLE oms_live_th.b (i INT(10)); +CREATE TABLE oms_live_th.c (i INT(10)); +CREATE TABLE oms_live_th.d (i INT(10)); +CREATE ROLE a_sg; +CREATE ROLE b_sg; +CREATE ROLE c_sg; +CREATE ROLE d_sg; +CREATE ROLE operations_sg; +GRANT a_sg TO operations_sg; +GRANT b_sg TO operations_sg; +GRANT c_sg TO operations_sg; +GRANT d_sg TO operations_sg; +GRANT SELECT ON bob_live_sg.a TO a_sg; +GRANT SELECT ON bob_live_sg.b TO b_sg; +GRANT SELECT ON bob_live_sg.c TO c_sg; +GRANT SELECT ON bob_live_sg.d TO d_sg; +GRANT SELECT ON oms_live_sg.a TO a_sg; +GRANT SELECT ON oms_live_sg.b TO b_sg; +GRANT SELECT ON oms_live_sg.c TO c_sg; +GRANT SELECT ON oms_live_sg.d TO d_sg; +CREATE ROLE a_ph; +CREATE ROLE b_ph; +CREATE ROLE c_ph; +CREATE ROLE d_ph; +CREATE ROLE operations_ph; +GRANT a_ph TO operations_ph; +GRANT b_ph TO operations_ph; +GRANT c_ph TO operations_ph; +GRANT d_ph TO operations_ph; +GRANT SELECT ON bob_live_ph.a TO a_ph; +GRANT SELECT ON bob_live_ph.b TO b_ph; +GRANT SELECT ON bob_live_ph.c TO c_ph; +GRANT SELECT ON bob_live_ph.d TO d_ph; +GRANT SELECT ON oms_live_ph.a TO a_ph; +GRANT SELECT ON oms_live_ph.b TO b_ph; +GRANT SELECT ON oms_live_ph.c TO c_ph; +GRANT SELECT ON oms_live_ph.d TO d_ph; +CREATE ROLE a_id; +CREATE ROLE b_id; +CREATE ROLE c_id; +CREATE ROLE d_id; +CREATE ROLE operations_id; +GRANT a_id TO operations_id; +GRANT b_id TO operations_id; +GRANT c_id TO operations_id; +GRANT d_id TO operations_id; +GRANT SELECT ON bob_live_id.a TO a_id; +GRANT SELECT ON bob_live_id.b TO b_id; +GRANT SELECT ON bob_live_id.c TO c_id; +GRANT SELECT ON bob_live_id.d TO d_id; +GRANT SELECT ON oms_live_id.a TO a_id; +GRANT SELECT ON oms_live_id.b TO b_id; +GRANT SELECT ON oms_live_id.c TO c_id; +GRANT SELECT ON oms_live_id.d TO d_id; +CREATE ROLE a_hk; +CREATE ROLE b_hk; +CREATE ROLE c_hk; +CREATE ROLE d_hk; +CREATE ROLE operations_hk; +GRANT a_hk TO operations_hk; +GRANT b_hk TO operations_hk; +GRANT c_hk TO operations_hk; +GRANT d_hk TO operations_hk; +GRANT SELECT ON bob_live_hk.a TO a_hk; +GRANT SELECT ON bob_live_hk.b TO b_hk; +GRANT SELECT ON bob_live_hk.c TO c_hk; +GRANT SELECT ON bob_live_hk.d TO d_hk; +GRANT SELECT ON oms_live_hk.a TO a_hk; +GRANT SELECT ON oms_live_hk.b TO b_hk; +GRANT SELECT ON oms_live_hk.c TO c_hk; +GRANT SELECT ON oms_live_hk.d TO d_hk; +CREATE ROLE a_vn; +CREATE ROLE b_vn; +CREATE ROLE c_vn; +CREATE ROLE d_vn; +CREATE ROLE operations_vn; +GRANT a_vn TO operations_vn; +GRANT b_vn TO operations_vn; +GRANT c_vn TO operations_vn; +GRANT d_vn TO operations_vn; +GRANT SELECT ON bob_live_vn.a TO a_vn; +GRANT SELECT ON bob_live_vn.b TO b_vn; +GRANT SELECT ON bob_live_vn.c TO c_vn; +GRANT SELECT ON bob_live_vn.d TO d_vn; +GRANT SELECT ON oms_live_vn.a TO a_vn; +GRANT SELECT ON oms_live_vn.b TO b_vn; +GRANT SELECT ON oms_live_vn.c TO c_vn; +GRANT SELECT ON oms_live_vn.d TO d_vn; +CREATE ROLE a_tw; +CREATE ROLE b_tw; +CREATE ROLE c_tw; +CREATE ROLE d_tw; +CREATE ROLE operations_tw; +GRANT a_tw TO operations_tw; +GRANT b_tw TO operations_tw; +GRANT c_tw TO operations_tw; +GRANT d_tw TO operations_tw; +GRANT SELECT ON bob_live_tw.a TO a_tw; +GRANT SELECT ON bob_live_tw.b TO b_tw; +GRANT SELECT ON bob_live_tw.c TO c_tw; +GRANT SELECT ON bob_live_tw.d TO d_tw; +GRANT SELECT ON oms_live_tw.a TO a_tw; +GRANT SELECT ON oms_live_tw.b TO b_tw; +GRANT SELECT ON oms_live_tw.c TO c_tw; +GRANT SELECT ON oms_live_tw.d TO d_tw; +CREATE ROLE a_my; +CREATE ROLE b_my; +CREATE ROLE c_my; +CREATE ROLE d_my; +CREATE ROLE operations_my; +GRANT a_my TO operations_my; +GRANT b_my TO operations_my; +GRANT c_my TO operations_my; +GRANT d_my TO operations_my; +GRANT SELECT ON bob_live_my.a TO a_my; +GRANT SELECT ON bob_live_my.b TO b_my; +GRANT SELECT ON bob_live_my.c TO c_my; +GRANT SELECT ON bob_live_my.d TO d_my; +GRANT SELECT ON oms_live_my.a TO a_my; +GRANT SELECT ON oms_live_my.b TO b_my; +GRANT SELECT ON oms_live_my.c TO c_my; +GRANT SELECT ON oms_live_my.d TO d_my; +CREATE ROLE a_th; +CREATE ROLE b_th; +CREATE ROLE c_th; +CREATE ROLE d_th; +CREATE ROLE operations_th; +GRANT a_th TO operations_th; +GRANT b_th TO operations_th; +GRANT c_th TO operations_th; +GRANT d_th TO operations_th; +GRANT SELECT ON bob_live_th.a TO a_th; +GRANT SELECT ON bob_live_th.b TO b_th; +GRANT SELECT ON bob_live_th.c TO c_th; +GRANT SELECT ON bob_live_th.d TO d_th; +GRANT SELECT ON oms_live_th.a TO a_th; +GRANT SELECT ON oms_live_th.b TO b_th; +GRANT SELECT ON oms_live_th.c TO c_th; +GRANT SELECT ON oms_live_th.d TO d_th; +GRANT operations_sg TO operations_cluster; +GRANT operations_ph TO operations_cluster; +GRANT operations_id TO operations_cluster; +GRANT operations_hk TO operations_cluster; +GRANT operations_vn TO operations_cluster; +GRANT operations_tw TO operations_cluster; +GRANT operations_my TO operations_cluster; +GRANT operations_th TO operations_cluster; +connect con1,localhost,u,,; +SHOW DATABASES; +Database +information_schema +test +SET ROLE operations_cluster; +SHOW DATABASES; +Database +bob_live_hk +bob_live_id +bob_live_my +bob_live_ph +bob_live_sg +bob_live_th +bob_live_tw +bob_live_vn +information_schema +oms_live_hk +oms_live_id +oms_live_my +oms_live_ph +oms_live_sg +oms_live_th +oms_live_tw +oms_live_vn +test +SELECT COUNT(1) FROM oms_live_sg.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_sg.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_sg.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_sg.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_ph.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_ph.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_ph.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_ph.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_id.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_id.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_id.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_id.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_hk.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_hk.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_hk.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_hk.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_vn.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_vn.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_vn.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_vn.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_tw.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_tw.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_tw.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_tw.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_my.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_my.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_my.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_my.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_th.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_th.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_th.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_th.d; +COUNT(1) +0 +connect con2,localhost,root,,; +FLUSH PRIVILEGES; +connect con3,localhost,u,,; +SHOW DATABASES; +Database +information_schema +test +SET ROLE operations_cluster; +SHOW DATABASES; +Database +bob_live_hk +bob_live_id +bob_live_my +bob_live_ph +bob_live_sg +bob_live_th +bob_live_tw +bob_live_vn +information_schema +oms_live_hk +oms_live_id +oms_live_my +oms_live_ph +oms_live_sg +oms_live_th +oms_live_tw +oms_live_vn +test +SELECT COUNT(1) FROM oms_live_sg.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_sg.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_sg.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_sg.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_ph.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_ph.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_ph.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_ph.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_id.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_id.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_id.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_id.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_hk.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_hk.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_hk.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_hk.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_vn.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_vn.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_vn.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_vn.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_tw.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_tw.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_tw.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_tw.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_my.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_my.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_my.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_my.d; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_th.a; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_th.b; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_th.c; +COUNT(1) +0 +SELECT COUNT(1) FROM oms_live_th.d; +COUNT(1) +0 +connect con4,localhost,root,,; +DROP DATABASE bob_live_sg; +DROP DATABASE oms_live_sg; +DROP DATABASE bob_live_ph; +DROP DATABASE oms_live_ph; +DROP DATABASE bob_live_id; +DROP DATABASE oms_live_id; +DROP DATABASE bob_live_hk; +DROP DATABASE oms_live_hk; +DROP DATABASE bob_live_vn; +DROP DATABASE oms_live_vn; +DROP DATABASE bob_live_tw; +DROP DATABASE oms_live_tw; +DROP DATABASE bob_live_my; +DROP DATABASE oms_live_my; +DROP DATABASE bob_live_th; +DROP DATABASE oms_live_th; +DROP ROLE operations_sg; +DROP ROLE a_sg; +DROP ROLE b_sg; +DROP ROLE c_sg; +DROP ROLE d_sg; +DROP ROLE operations_ph; +DROP ROLE a_ph; +DROP ROLE b_ph; +DROP ROLE c_ph; +DROP ROLE d_ph; +DROP ROLE operations_id; +DROP ROLE a_id; +DROP ROLE b_id; +DROP ROLE c_id; +DROP ROLE d_id; +DROP ROLE operations_hk; +DROP ROLE a_hk; +DROP ROLE b_hk; +DROP ROLE c_hk; +DROP ROLE d_hk; +DROP ROLE operations_vn; +DROP ROLE a_vn; +DROP ROLE b_vn; +DROP ROLE c_vn; +DROP ROLE d_vn; +DROP ROLE operations_tw; +DROP ROLE a_tw; +DROP ROLE b_tw; +DROP ROLE c_tw; +DROP ROLE d_tw; +DROP ROLE operations_my; +DROP ROLE a_my; +DROP ROLE b_my; +DROP ROLE c_my; +DROP ROLE d_my; +DROP ROLE operations_th; +DROP ROLE a_th; +DROP ROLE b_th; +DROP ROLE c_th; +DROP ROLE d_th; +DROP USER u; +DROP ROLE operations_cluster; diff --git a/mysql-test/suite/roles/flush_roles-12366.test b/mysql-test/suite/roles/flush_roles-12366.test new file mode 100644 index 00000000000..343ac4abf72 --- /dev/null +++ b/mysql-test/suite/roles/flush_roles-12366.test @@ -0,0 +1,379 @@ +--source include/not_embedded.inc +--echo # +--echo # MDEV-12366: FLUSH PRIVILEGES can break hierarchy of roles +--echo # +--echo # This testcase contains a user, who is granted a master role +--echo # operations_cluster. operations_cluster is granted 8 different roles +--echo # who in turn each have 4 different roles granted to them. +--echo # +--echo # Only the leaf roles contain privileges to access databases. +--echo # Make sure the user has access to all databases if the master role +--echo # is granted to him. +--echo # +CREATE USER u; +CREATE ROLE operations_cluster; +GRANT operations_cluster TO u; +CREATE DATABASE bob_live_sg; +CREATE TABLE bob_live_sg.a (i INT(10)); +CREATE TABLE bob_live_sg.b (i INT(10)); +CREATE TABLE bob_live_sg.c (i INT(10)); +CREATE TABLE bob_live_sg.d (i INT(10)); +CREATE DATABASE oms_live_sg; +CREATE TABLE oms_live_sg.a (i INT(10)); +CREATE TABLE oms_live_sg.b (i INT(10)); +CREATE TABLE oms_live_sg.c (i INT(10)); +CREATE TABLE oms_live_sg.d (i INT(10)); +CREATE DATABASE bob_live_ph; +CREATE TABLE bob_live_ph.a (i INT(10)); +CREATE TABLE bob_live_ph.b (i INT(10)); +CREATE TABLE bob_live_ph.c (i INT(10)); +CREATE TABLE bob_live_ph.d (i INT(10)); +CREATE DATABASE oms_live_ph; +CREATE TABLE oms_live_ph.a (i INT(10)); +CREATE TABLE oms_live_ph.b (i INT(10)); +CREATE TABLE oms_live_ph.c (i INT(10)); +CREATE TABLE oms_live_ph.d (i INT(10)); +CREATE DATABASE bob_live_id; +CREATE TABLE bob_live_id.a (i INT(10)); +CREATE TABLE bob_live_id.b (i INT(10)); +CREATE TABLE bob_live_id.c (i INT(10)); +CREATE TABLE bob_live_id.d (i INT(10)); +CREATE DATABASE oms_live_id; +CREATE TABLE oms_live_id.a (i INT(10)); +CREATE TABLE oms_live_id.b (i INT(10)); +CREATE TABLE oms_live_id.c (i INT(10)); +CREATE TABLE oms_live_id.d (i INT(10)); +CREATE DATABASE bob_live_hk; +CREATE TABLE bob_live_hk.a (i INT(10)); +CREATE TABLE bob_live_hk.b (i INT(10)); +CREATE TABLE bob_live_hk.c (i INT(10)); +CREATE TABLE bob_live_hk.d (i INT(10)); +CREATE DATABASE oms_live_hk; +CREATE TABLE oms_live_hk.a (i INT(10)); +CREATE TABLE oms_live_hk.b (i INT(10)); +CREATE TABLE oms_live_hk.c (i INT(10)); +CREATE TABLE oms_live_hk.d (i INT(10)); +CREATE DATABASE bob_live_vn; +CREATE TABLE bob_live_vn.a (i INT(10)); +CREATE TABLE bob_live_vn.b (i INT(10)); +CREATE TABLE bob_live_vn.c (i INT(10)); +CREATE TABLE bob_live_vn.d (i INT(10)); +CREATE DATABASE oms_live_vn; +CREATE TABLE oms_live_vn.a (i INT(10)); +CREATE TABLE oms_live_vn.b (i INT(10)); +CREATE TABLE oms_live_vn.c (i INT(10)); +CREATE TABLE oms_live_vn.d (i INT(10)); +CREATE DATABASE bob_live_tw; +CREATE TABLE bob_live_tw.a (i INT(10)); +CREATE TABLE bob_live_tw.b (i INT(10)); +CREATE TABLE bob_live_tw.c (i INT(10)); +CREATE TABLE bob_live_tw.d (i INT(10)); +CREATE DATABASE oms_live_tw; +CREATE TABLE oms_live_tw.a (i INT(10)); +CREATE TABLE oms_live_tw.b (i INT(10)); +CREATE TABLE oms_live_tw.c (i INT(10)); +CREATE TABLE oms_live_tw.d (i INT(10)); +CREATE DATABASE bob_live_my; +CREATE TABLE bob_live_my.a (i INT(10)); +CREATE TABLE bob_live_my.b (i INT(10)); +CREATE TABLE bob_live_my.c (i INT(10)); +CREATE TABLE bob_live_my.d (i INT(10)); +CREATE DATABASE oms_live_my; +CREATE TABLE oms_live_my.a (i INT(10)); +CREATE TABLE oms_live_my.b (i INT(10)); +CREATE TABLE oms_live_my.c (i INT(10)); +CREATE TABLE oms_live_my.d (i INT(10)); +CREATE DATABASE bob_live_th; +CREATE TABLE bob_live_th.a (i INT(10)); +CREATE TABLE bob_live_th.b (i INT(10)); +CREATE TABLE bob_live_th.c (i INT(10)); +CREATE TABLE bob_live_th.d (i INT(10)); +CREATE DATABASE oms_live_th; +CREATE TABLE oms_live_th.a (i INT(10)); +CREATE TABLE oms_live_th.b (i INT(10)); +CREATE TABLE oms_live_th.c (i INT(10)); +CREATE TABLE oms_live_th.d (i INT(10)); +CREATE ROLE a_sg; +CREATE ROLE b_sg; +CREATE ROLE c_sg; +CREATE ROLE d_sg; +CREATE ROLE operations_sg; +GRANT a_sg TO operations_sg; +GRANT b_sg TO operations_sg; +GRANT c_sg TO operations_sg; +GRANT d_sg TO operations_sg; +GRANT SELECT ON bob_live_sg.a TO a_sg; +GRANT SELECT ON bob_live_sg.b TO b_sg; +GRANT SELECT ON bob_live_sg.c TO c_sg; +GRANT SELECT ON bob_live_sg.d TO d_sg; +GRANT SELECT ON oms_live_sg.a TO a_sg; +GRANT SELECT ON oms_live_sg.b TO b_sg; +GRANT SELECT ON oms_live_sg.c TO c_sg; +GRANT SELECT ON oms_live_sg.d TO d_sg; +CREATE ROLE a_ph; +CREATE ROLE b_ph; +CREATE ROLE c_ph; +CREATE ROLE d_ph; +CREATE ROLE operations_ph; +GRANT a_ph TO operations_ph; +GRANT b_ph TO operations_ph; +GRANT c_ph TO operations_ph; +GRANT d_ph TO operations_ph; +GRANT SELECT ON bob_live_ph.a TO a_ph; +GRANT SELECT ON bob_live_ph.b TO b_ph; +GRANT SELECT ON bob_live_ph.c TO c_ph; +GRANT SELECT ON bob_live_ph.d TO d_ph; +GRANT SELECT ON oms_live_ph.a TO a_ph; +GRANT SELECT ON oms_live_ph.b TO b_ph; +GRANT SELECT ON oms_live_ph.c TO c_ph; +GRANT SELECT ON oms_live_ph.d TO d_ph; +CREATE ROLE a_id; +CREATE ROLE b_id; +CREATE ROLE c_id; +CREATE ROLE d_id; +CREATE ROLE operations_id; +GRANT a_id TO operations_id; +GRANT b_id TO operations_id; +GRANT c_id TO operations_id; +GRANT d_id TO operations_id; +GRANT SELECT ON bob_live_id.a TO a_id; +GRANT SELECT ON bob_live_id.b TO b_id; +GRANT SELECT ON bob_live_id.c TO c_id; +GRANT SELECT ON bob_live_id.d TO d_id; +GRANT SELECT ON oms_live_id.a TO a_id; +GRANT SELECT ON oms_live_id.b TO b_id; +GRANT SELECT ON oms_live_id.c TO c_id; +GRANT SELECT ON oms_live_id.d TO d_id; +CREATE ROLE a_hk; +CREATE ROLE b_hk; +CREATE ROLE c_hk; +CREATE ROLE d_hk; +CREATE ROLE operations_hk; +GRANT a_hk TO operations_hk; +GRANT b_hk TO operations_hk; +GRANT c_hk TO operations_hk; +GRANT d_hk TO operations_hk; +GRANT SELECT ON bob_live_hk.a TO a_hk; +GRANT SELECT ON bob_live_hk.b TO b_hk; +GRANT SELECT ON bob_live_hk.c TO c_hk; +GRANT SELECT ON bob_live_hk.d TO d_hk; +GRANT SELECT ON oms_live_hk.a TO a_hk; +GRANT SELECT ON oms_live_hk.b TO b_hk; +GRANT SELECT ON oms_live_hk.c TO c_hk; +GRANT SELECT ON oms_live_hk.d TO d_hk; +CREATE ROLE a_vn; +CREATE ROLE b_vn; +CREATE ROLE c_vn; +CREATE ROLE d_vn; +CREATE ROLE operations_vn; +GRANT a_vn TO operations_vn; +GRANT b_vn TO operations_vn; +GRANT c_vn TO operations_vn; +GRANT d_vn TO operations_vn; +GRANT SELECT ON bob_live_vn.a TO a_vn; +GRANT SELECT ON bob_live_vn.b TO b_vn; +GRANT SELECT ON bob_live_vn.c TO c_vn; +GRANT SELECT ON bob_live_vn.d TO d_vn; +GRANT SELECT ON oms_live_vn.a TO a_vn; +GRANT SELECT ON oms_live_vn.b TO b_vn; +GRANT SELECT ON oms_live_vn.c TO c_vn; +GRANT SELECT ON oms_live_vn.d TO d_vn; +CREATE ROLE a_tw; +CREATE ROLE b_tw; +CREATE ROLE c_tw; +CREATE ROLE d_tw; +CREATE ROLE operations_tw; +GRANT a_tw TO operations_tw; +GRANT b_tw TO operations_tw; +GRANT c_tw TO operations_tw; +GRANT d_tw TO operations_tw; +GRANT SELECT ON bob_live_tw.a TO a_tw; +GRANT SELECT ON bob_live_tw.b TO b_tw; +GRANT SELECT ON bob_live_tw.c TO c_tw; +GRANT SELECT ON bob_live_tw.d TO d_tw; +GRANT SELECT ON oms_live_tw.a TO a_tw; +GRANT SELECT ON oms_live_tw.b TO b_tw; +GRANT SELECT ON oms_live_tw.c TO c_tw; +GRANT SELECT ON oms_live_tw.d TO d_tw; +CREATE ROLE a_my; +CREATE ROLE b_my; +CREATE ROLE c_my; +CREATE ROLE d_my; +CREATE ROLE operations_my; +GRANT a_my TO operations_my; +GRANT b_my TO operations_my; +GRANT c_my TO operations_my; +GRANT d_my TO operations_my; +GRANT SELECT ON bob_live_my.a TO a_my; +GRANT SELECT ON bob_live_my.b TO b_my; +GRANT SELECT ON bob_live_my.c TO c_my; +GRANT SELECT ON bob_live_my.d TO d_my; +GRANT SELECT ON oms_live_my.a TO a_my; +GRANT SELECT ON oms_live_my.b TO b_my; +GRANT SELECT ON oms_live_my.c TO c_my; +GRANT SELECT ON oms_live_my.d TO d_my; +CREATE ROLE a_th; +CREATE ROLE b_th; +CREATE ROLE c_th; +CREATE ROLE d_th; +CREATE ROLE operations_th; +GRANT a_th TO operations_th; +GRANT b_th TO operations_th; +GRANT c_th TO operations_th; +GRANT d_th TO operations_th; +GRANT SELECT ON bob_live_th.a TO a_th; +GRANT SELECT ON bob_live_th.b TO b_th; +GRANT SELECT ON bob_live_th.c TO c_th; +GRANT SELECT ON bob_live_th.d TO d_th; +GRANT SELECT ON oms_live_th.a TO a_th; +GRANT SELECT ON oms_live_th.b TO b_th; +GRANT SELECT ON oms_live_th.c TO c_th; +GRANT SELECT ON oms_live_th.d TO d_th; +GRANT operations_sg TO operations_cluster; +GRANT operations_ph TO operations_cluster; +GRANT operations_id TO operations_cluster; +GRANT operations_hk TO operations_cluster; +GRANT operations_vn TO operations_cluster; +GRANT operations_tw TO operations_cluster; +GRANT operations_my TO operations_cluster; +GRANT operations_th TO operations_cluster; + +connect(con1,localhost,u,,); +SHOW DATABASES; +SET ROLE operations_cluster; +SHOW DATABASES; +SELECT COUNT(1) FROM oms_live_sg.a; +SELECT COUNT(1) FROM oms_live_sg.b; +SELECT COUNT(1) FROM oms_live_sg.c; +SELECT COUNT(1) FROM oms_live_sg.d; +SELECT COUNT(1) FROM oms_live_ph.a; +SELECT COUNT(1) FROM oms_live_ph.b; +SELECT COUNT(1) FROM oms_live_ph.c; +SELECT COUNT(1) FROM oms_live_ph.d; +SELECT COUNT(1) FROM oms_live_id.a; +SELECT COUNT(1) FROM oms_live_id.b; +SELECT COUNT(1) FROM oms_live_id.c; +SELECT COUNT(1) FROM oms_live_id.d; +SELECT COUNT(1) FROM oms_live_hk.a; +SELECT COUNT(1) FROM oms_live_hk.b; +SELECT COUNT(1) FROM oms_live_hk.c; +SELECT COUNT(1) FROM oms_live_hk.d; +SELECT COUNT(1) FROM oms_live_vn.a; +SELECT COUNT(1) FROM oms_live_vn.b; +SELECT COUNT(1) FROM oms_live_vn.c; +SELECT COUNT(1) FROM oms_live_vn.d; +SELECT COUNT(1) FROM oms_live_tw.a; +SELECT COUNT(1) FROM oms_live_tw.b; +SELECT COUNT(1) FROM oms_live_tw.c; +SELECT COUNT(1) FROM oms_live_tw.d; +SELECT COUNT(1) FROM oms_live_my.a; +SELECT COUNT(1) FROM oms_live_my.b; +SELECT COUNT(1) FROM oms_live_my.c; +SELECT COUNT(1) FROM oms_live_my.d; +SELECT COUNT(1) FROM oms_live_th.a; +SELECT COUNT(1) FROM oms_live_th.b; +SELECT COUNT(1) FROM oms_live_th.c; +SELECT COUNT(1) FROM oms_live_th.d; + + +connect(con2,localhost,root,,); +FLUSH PRIVILEGES; + +connect(con3,localhost,u,,); +SHOW DATABASES; +SET ROLE operations_cluster; +SHOW DATABASES; +SELECT COUNT(1) FROM oms_live_sg.a; +SELECT COUNT(1) FROM oms_live_sg.b; +SELECT COUNT(1) FROM oms_live_sg.c; +SELECT COUNT(1) FROM oms_live_sg.d; +SELECT COUNT(1) FROM oms_live_ph.a; +SELECT COUNT(1) FROM oms_live_ph.b; +SELECT COUNT(1) FROM oms_live_ph.c; +SELECT COUNT(1) FROM oms_live_ph.d; +SELECT COUNT(1) FROM oms_live_id.a; +SELECT COUNT(1) FROM oms_live_id.b; +SELECT COUNT(1) FROM oms_live_id.c; +SELECT COUNT(1) FROM oms_live_id.d; +SELECT COUNT(1) FROM oms_live_hk.a; +SELECT COUNT(1) FROM oms_live_hk.b; +SELECT COUNT(1) FROM oms_live_hk.c; +SELECT COUNT(1) FROM oms_live_hk.d; +SELECT COUNT(1) FROM oms_live_vn.a; +SELECT COUNT(1) FROM oms_live_vn.b; +SELECT COUNT(1) FROM oms_live_vn.c; +SELECT COUNT(1) FROM oms_live_vn.d; +SELECT COUNT(1) FROM oms_live_tw.a; +SELECT COUNT(1) FROM oms_live_tw.b; +SELECT COUNT(1) FROM oms_live_tw.c; +SELECT COUNT(1) FROM oms_live_tw.d; +SELECT COUNT(1) FROM oms_live_my.a; +SELECT COUNT(1) FROM oms_live_my.b; +SELECT COUNT(1) FROM oms_live_my.c; +SELECT COUNT(1) FROM oms_live_my.d; +SELECT COUNT(1) FROM oms_live_th.a; +SELECT COUNT(1) FROM oms_live_th.b; +SELECT COUNT(1) FROM oms_live_th.c; +SELECT COUNT(1) FROM oms_live_th.d; + + +connect(con4,localhost,root,,); + +DROP DATABASE bob_live_sg; +DROP DATABASE oms_live_sg; +DROP DATABASE bob_live_ph; +DROP DATABASE oms_live_ph; +DROP DATABASE bob_live_id; +DROP DATABASE oms_live_id; +DROP DATABASE bob_live_hk; +DROP DATABASE oms_live_hk; +DROP DATABASE bob_live_vn; +DROP DATABASE oms_live_vn; +DROP DATABASE bob_live_tw; +DROP DATABASE oms_live_tw; +DROP DATABASE bob_live_my; +DROP DATABASE oms_live_my; +DROP DATABASE bob_live_th; +DROP DATABASE oms_live_th; +DROP ROLE operations_sg; +DROP ROLE a_sg; +DROP ROLE b_sg; +DROP ROLE c_sg; +DROP ROLE d_sg; +DROP ROLE operations_ph; +DROP ROLE a_ph; +DROP ROLE b_ph; +DROP ROLE c_ph; +DROP ROLE d_ph; +DROP ROLE operations_id; +DROP ROLE a_id; +DROP ROLE b_id; +DROP ROLE c_id; +DROP ROLE d_id; +DROP ROLE operations_hk; +DROP ROLE a_hk; +DROP ROLE b_hk; +DROP ROLE c_hk; +DROP ROLE d_hk; +DROP ROLE operations_vn; +DROP ROLE a_vn; +DROP ROLE b_vn; +DROP ROLE c_vn; +DROP ROLE d_vn; +DROP ROLE operations_tw; +DROP ROLE a_tw; +DROP ROLE b_tw; +DROP ROLE c_tw; +DROP ROLE d_tw; +DROP ROLE operations_my; +DROP ROLE a_my; +DROP ROLE b_my; +DROP ROLE c_my; +DROP ROLE d_my; +DROP ROLE operations_th; +DROP ROLE a_th; +DROP ROLE b_th; +DROP ROLE c_th; +DROP ROLE d_th; +DROP USER u; +DROP ROLE operations_cluster; diff --git a/mysql-test/suite/roles/set_role-13655.result b/mysql-test/suite/roles/set_role-13655.result new file mode 100644 index 00000000000..c30e4115953 --- /dev/null +++ b/mysql-test/suite/roles/set_role-13655.result @@ -0,0 +1,52 @@ +# +# MDEV-13655: SET ROLE does not properly grant privileges. +# +# We must test that if aditional db privileges get granted to a role +# which previously inherited privileges from another granted role +# keep the internal memory structures intact. +# +create role simple; +# +# First we create an entry with privileges for databases for the simple role. +# +grant select, insert, update, delete, lock tables, execute on t.* to simple; +create role admin; +# +# Now we grant the simple role to admin. This means that db privileges +# should propagate to admin. +# +grant simple to admin; +show grants for admin; +Grants for admin +GRANT simple TO 'admin' +GRANT USAGE ON *.* TO 'admin' +GRANT USAGE ON *.* TO 'simple' +GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 'simple' +# +# Finally, we give the admin all the available privileges for the db. +# +grant all on t.* to admin; +# +# Create a user to test out the new roles; +# +create user foo; +grant admin to foo; +connect foo,localhost,foo,,,,,; +create database t; +ERROR 42000: Access denied for user 'foo'@'%' to database 't' +set role admin; +show grants; +Grants for foo@% +GRANT admin TO 'foo'@'%' +GRANT USAGE ON *.* TO 'foo'@'%' +GRANT simple TO 'admin' +GRANT USAGE ON *.* TO 'admin' +GRANT ALL PRIVILEGES ON `t`.* TO 'admin' +GRANT USAGE ON *.* TO 'simple' +GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES, EXECUTE ON `t`.* TO 'simple' +create database t; +drop database t; +connection default; +drop role simple; +drop role admin; +drop user foo; diff --git a/mysql-test/suite/roles/set_role-13655.test b/mysql-test/suite/roles/set_role-13655.test new file mode 100644 index 00000000000..97a82109276 --- /dev/null +++ b/mysql-test/suite/roles/set_role-13655.test @@ -0,0 +1,49 @@ +source include/not_embedded.inc; + +--echo # +--echo # MDEV-13655: SET ROLE does not properly grant privileges. +--echo # +--echo # We must test that if aditional db privileges get granted to a role +--echo # which previously inherited privileges from another granted role +--echo # keep the internal memory structures intact. +--echo # + +create role simple; + +--echo # +--echo # First we create an entry with privileges for databases for the simple role. +--echo # +grant select, insert, update, delete, lock tables, execute on t.* to simple; +create role admin; + +--echo # +--echo # Now we grant the simple role to admin. This means that db privileges +--echo # should propagate to admin. +--echo # +grant simple to admin; +show grants for admin; + +--echo # +--echo # Finally, we give the admin all the available privileges for the db. +--echo # +grant all on t.* to admin; + +--echo # +--echo # Create a user to test out the new roles; +--echo # +create user foo; +grant admin to foo; + +connect (foo,localhost,foo,,,,,); +--error ER_DBACCESS_DENIED_ERROR +create database t; +set role admin; +show grants; +create database t; +drop database t; + +connection default; + +drop role simple; +drop role admin; +drop user foo; diff --git a/mysql-test/t/func_set.test b/mysql-test/t/func_set.test index 47072ba5ffe..3509e225a34 100644 --- a/mysql-test/t/func_set.test +++ b/mysql-test/t/func_set.test @@ -136,18 +136,14 @@ SELECT * FROM t1 WHERE FIND_IN_SET(NULL, NULL) IS UNKNOWN; --echo DROP TABLE t1; ---echo # ---echo # Start of 5.3 tests ---echo # --echo # ---echo # MDEV-4512 Valgrind warnings in my_long10_to_str_8bit on INTERVAL and DATE_ADD with incorrect types +--echo # MDEV-14596 Crash in INTERVAL(ROW(..),ROW(..)) --echo # -CREATE TABLE t1 (pk INT PRIMARY KEY); -INSERT INTO t1 VALUES (10),(11); -SELECT INTERVAL( 9, 1, DATE_ADD( pk, INTERVAL pk MINUTE_SECOND ), 9, 8, 3, 5, 2, 1 ) FROM t1; -DROP TABLE t1; ---echo # ---echo # End of 5.3 tests ---echo # +--error ER_OPERAND_COLUMNS +SELECT INTERVAL(ROW(1,1),ROW(1,2)); +--error ER_OPERAND_COLUMNS +SELECT INTERVAL(1,ROW(1,2)); +--error ER_OPERAND_COLUMNS +SELECT INTERVAL(ROW(1,2),1); diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index a2df97949d8..275939df5c5 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1790,6 +1790,32 @@ select t1.c1 as c1, t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group drop table t1, t2; # +# MDEV-12350: Heap corruption, overrun buffer, ASAN errors, server crash in my_fill_8bit / filesort +# + +SET @old_sort_buff_size = @@sort_buffer_size; +SET @@sort_buffer_size=256*1024; +CREATE TABLE t1 (c INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (2011),(1977),(1982),(2027),(2023),(NULL),(NULL),(2004),(1974),(2032), + (1993),(NULL),(1995),(2034),(NULL),(2009),(1900),(NULL),(2025),(1900), + (2033),(1900),(2012),(NULL),(2009),(1992),(1974),(1974),(2012),(2028), + (2007),(2012),(1900),(1983),(1900),(2010),(1987),(1994),(1981),(2032), + (2010),(1989),(2014),(1900),(1900),(1976),(1978),(2007),(2030),(NULL), + (2002),(1997),(1900),(NULL),(2000),(2027),(1975),(2026),(1975),(2026), + (2029),(1977),(1900),(1900),(2031),(1993),(1986),(2012),(1979),(2013), + (1994),(2014),(2025),(2006),(1971),(1974),(2021),(2011),(NULL),(1991), + (2001),(1977),(2023),(2012),(1900),(1978),(1998),(NULL),(1988),(1999), + (2017),(2008),(1976),(1900),(2005),(2030),(2023),(1900),(1978),(1990), + (1978),(1987),(2030),(1900),(2034),(2006),(2015),(2001),(2019),(2024), + (2030),(1989),(1997),(2007),(2023),(1994),(1971),(2011),(2011),(2015), + (1984),(1978),(1979),(1989),(2008),(2030); + +SELECT ExtractValue('<a></a>','/a') AS f1, SPACE(c) AS f2 FROM t1 GROUP BY f1, f2 WITH ROLLUP; +SET @@sort_buffer_size = @old_sort_buff_size; +DROP TABLE t1; + +# # End of MariaDB 5.5 tests # diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index 3d8f7dc42b7..3675a09d82f 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -729,6 +729,23 @@ HAVING UPPER(`column_1`) LIKE '8%'; drop table t1; --echo # +--echo # mdev-14368: grouping query with alias for aggregate function in HAVING +--echo # when sql_mode = 'ONLY_FULL_GROUP_BY' + + +set @save_sql_mode= @@sql_mode; +set sql_mode = 'ONLY_FULL_GROUP_BY'; + +create table t1(a int); +insert t1 values (4),(1),(2),(1), (3),(4); + +SELECT a, COUNT(a) as ct FROM t1 GROUP BY a HAVING ct>0; + +set sql_mode=@save_sql_mode; + +drop table t1; + +--echo # --echo # Bug mdev-5160: two-way join with HAVING over the second table --echo # diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 88861511d79..acbe19b5e87 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1962,6 +1962,20 @@ DROP FUNCTION f1; DROP TABLE t1,t2; +--echo # +--echo # MDEV-10397: Server crashes in key_copy with join_cache_level > 2 and join on BIT fields +--echo # + +CREATE TABLE t1 (b1 BIT NOT NULL); +INSERT INTO t1 VALUES (0),(1); + +CREATE TABLE t2 (b2 BIT NOT NULL); +INSERT INTO t2 VALUES (0),(1); + +SET SESSION JOIN_CACHE_LEVEL = 3; +SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2; +DROP TABLE t1, t2; + --echo # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 9e8e483929f..ebb00ed718e 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -5583,6 +5583,18 @@ PREPARE stmt FROM 'REPLACE INTO v2 SELECT a FROM t3'; drop view v1,v2; drop table t3; +--echo # +--echo # MDEV-14619: VIEW and GROUP_CONCAT +--echo # + +CREATE TABLE t1 (str text); +INSERT INTO t1 VALUES ("My"),("SQL"); +CREATE VIEW v1 AS SELECT GROUP_CONCAT(str SEPARATOR '\\') FROM t1; +SELECT * FROM v1; +SHOW CREATE VIEW v1; +drop view v1; +drop table t1; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.5 tests. --echo # ----------------------------------------------------------------- diff --git a/mysql-test/t/xml.test b/mysql-test/t/xml.test index 76625bd4e92..bbb2ca7948f 100644 --- a/mysql-test/t/xml.test +++ b/mysql-test/t/xml.test @@ -713,7 +713,7 @@ FROM t1; SELECT UPDATEXML(txt, CONCAT('//', REPEAT('b', 63)), '63/63+') FROM t1; DROP TABLE t1; -# This will call my_str_realloc_mysqld() +# This will call realloc() CREATE TABLE t1 (a TEXT); INSERT INTO t1 VALUES (CONCAT('<a><', REPEAT('b',128),'>b128</',REPEAT('b',128),'><',REPEAT('c',512),'>c512</',REPEAT('c',512),'></a>')); SELECT ExtractValue (a, CONCAT('//',REPEAT('c',512))) AS c512 FROM t1; |