diff options
Diffstat (limited to 'mysql-test/suite')
77 files changed, 1330 insertions, 108 deletions
diff --git a/mysql-test/suite/binlog/r/binlog_mdev717.result b/mysql-test/suite/binlog/r/binlog_mdev717.result index 5fe80be651a..f9ec3a32845 100644 --- a/mysql-test/suite/binlog/r/binlog_mdev717.result +++ b/mysql-test/suite/binlog/r/binlog_mdev717.result @@ -21,6 +21,8 @@ ERROR 42000: Unknown database 'mysqltest' connection default; CREATE DATABASE mysqltest; CREATE EVENT mysqltest.e1 ON SCHEDULE EVERY 15 MINUTE DO BEGIN END; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. SET DEBUG_SYNC= "after_wait_locked_schema_name SIGNAL locked WAIT_FOR release"; DROP DATABASE mysqltest;; connection con1; diff --git a/mysql-test/suite/binlog/r/binlog_sql_mode.result b/mysql-test/suite/binlog/r/binlog_sql_mode.result index 1aea77c4a4b..27ba30b5e4f 100644 --- a/mysql-test/suite/binlog/r/binlog_sql_mode.result +++ b/mysql-test/suite/binlog/r/binlog_sql_mode.result @@ -21,6 +21,8 @@ DO BEGIN UPDATE t1 SET id = id +1; END;| +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. Check Result select (@a:=load_file("MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug39526.binlog")) diff --git a/mysql-test/suite/compat/oracle/r/events.result b/mysql-test/suite/compat/oracle/r/events.result index 7b4ad5679eb..ef9c50115ae 100644 --- a/mysql-test/suite/compat/oracle/r/events.result +++ b/mysql-test/suite/compat/oracle/r/events.result @@ -8,6 +8,8 @@ CREATE TABLE t1 (a TIMESTAMP); CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MICROSECOND DO INSERT INTO t1 VALUES(NOW()); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. SET GLOBAL event_scheduler=on; SELECT COUNT(*) FROM t1; COUNT(*) diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result index 18fce086f6e..3e72167d43d 100644 --- a/mysql-test/suite/compat/oracle/r/table_value_constr.result +++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result @@ -2183,3 +2183,324 @@ VALUES(1 + 1,2,'abc'); SELECT * FROM (VALUES(1 + 1,2,'abc')) t; 1 + 1 2 abc 2 2 abc +# +# MDEV-17894: tvc with ORDER BY ... LIMIT +# +values (5), (7), (1), (3), (4) limit 2; +5 +5 +7 +explain extended values (5), (7), (1), (3), (4) limit 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 values (5),(7),(1),(3),(4) limit 2 +values (5), (7), (1), (3), (4) limit 2 offset 1; +5 +7 +1 +explain extended values (5), (7), (1), (3), (4) limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 values (5),(7),(1),(3),(4) limit 1,2 +values (5), (7), (1), (3), (4) order by 1 limit 2; +5 +1 +3 +explain extended values (5), (7), (1), (3), (4) order by 1 limit 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 2 +values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +5 +3 +4 +explain extended values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 1,2 +values (5), (7), (1), (3), (4) order by 1; +5 +1 +3 +4 +5 +7 +explain extended values (5), (7), (1), (3), (4) order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; +5 90 +4 10 +7 20 +3 50 +1 70 +5 90 +explain extended values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5,90),(7,20),(1,70),(3,50),(4,10) order by 2 +select 2 union (values (5), (7), (1), (3), (4) limit 2); +2 +2 +5 +7 +explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS "2" union (values (5),(7),(1),(3),(4) limit 2) +select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +2 +2 +7 +1 +explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS "2" union (values (5),(7),(1),(3),(4) limit 1,2) +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +2 +2 +1 +3 +explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS "2" union (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +2 +2 +3 +4 +explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS "2" union (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,2) +(values (5), (7), (1), (3), (4) limit 2) union select 2; +5 +5 +7 +2 +explain extended (values (5), (7), (1), (3), (4) limit 2) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 2) union /* select#2 */ select 2 AS "2" +(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +5 +7 +1 +2 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union /* select#2 */ select 2 AS "2" +(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +5 +1 +3 +2 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) union /* select#2 */ select 2 AS "2" +(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +5 +3 +4 +2 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,2) union /* select#2 */ select 2 AS "2" +select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +3 +3 +3 +4 +explain extended select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select 3 AS "3" union all (values (5),(7),(1),(3),(4) limit 3,2) +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +5 +3 +4 +3 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS "3" +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +3 +3 +1 +3 +explain extended select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 3 AS "3" union all (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) +(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +5 +1 +3 +3 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) union all /* select#2 */ select 3 AS "3" +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +5 +7 +1 +3 +explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +5 +7 +1 +1 +3 +explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union all (/* select#3 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 2) +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +5 +3 +3 +4 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS "3" order by 1 +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +5 +3 +3 +4 +5 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,3) union all /* select#2 */ select 3 AS "3" order by 1 +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 +order by 1 limit 2 offset 1; +5 +3 +4 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 +order by 1 limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select "tvc_0"."5" AS "5" from (values (5),(7),(1),(3),(4)) "tvc_0" order by 1 limit 1,3) union all /* select#2 */ select 3 AS "3" order by 1 limit 1,2 +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; +ERROR 42S22: Unknown column '3' in 'order clause' +create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE VIEW "v1" AS values (5),(7),(1),(3),(4) order by 1 limit 2 latin1 latin1_swedish_ci +select * from v1; +5 +1 +3 +drop view v1; +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE VIEW "v1" AS (values (5),(7),(1),(3),(4) limit 1,2) union (values (5),(7),(1),(3),(4) order by 1 limit 2) latin1 latin1_swedish_ci +select * from v1; +5 +7 +1 +3 +drop view v1; +create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; +ERROR 42S22: Unknown column '3' in 'order clause' +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 2 limit 2 ); +ERROR 42S22: Unknown column '2' in 'order clause' diff --git a/mysql-test/suite/compat/oracle/t/table_value_constr.test b/mysql-test/suite/compat/oracle/t/table_value_constr.test index 37d25218664..4e0dcc05514 100644 --- a/mysql-test/suite/compat/oracle/t/table_value_constr.test +++ b/mysql-test/suite/compat/oracle/t/table_value_constr.test @@ -1125,3 +1125,154 @@ DROP VIEW v1; VALUES(1 + 1,2,'abc'); SELECT * FROM (VALUES(1 + 1,2,'abc')) t; + +--echo # +--echo # MDEV-17894: tvc with ORDER BY ... LIMIT +--echo # + +let $q= +values (5), (7), (1), (3), (4) limit 2; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) limit 2 offset 1; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) order by 1 limit 2; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) order by 1; +eval $q; +eval explain extended $q; + +let $q= +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) limit 2); +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +eval $q; +eval explain extended $q; + + +let $q= +(values (5), (7), (1), (3), (4) limit 2) union select 2; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +eval $q; +eval explain extended $q; + + +let $q= +select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +eval $q; +eval explain extended $q; + +let $q= +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +eval $q; +eval explain extended $q; + +let $q= +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +eval $q; +eval explain extended $q; + +let $q= +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 + order by 1 limit 2 offset 1; +eval $q; +eval explain extended $q; + +--error ER_BAD_FIELD_ERROR +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; + +create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2; +show create view v1; +select * from v1; +drop view v1; + +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +show create view v1; +select * from v1; +drop view v1; + +--error ER_BAD_FIELD_ERROR +create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; + +--error ER_BAD_FIELD_ERROR +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 2 limit 2 ); diff --git a/mysql-test/suite/engines/iuds/r/insert_decimal.result b/mysql-test/suite/engines/iuds/r/insert_decimal.result index 860fa8a2c4c..1e6b7a08558 100644 --- a/mysql-test/suite/engines/iuds/r/insert_decimal.result +++ b/mysql-test/suite/engines/iuds/r/insert_decimal.result @@ -1151,7 +1151,7 @@ total_rows min_value max_value sum avg 7 -100000.00000 100000.00000 -99.15000 -16.525000509 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t1; total_rows min_value max_value sum avg -7 -0.10000000149011612 111111112 111211212.95000306 18535202.15833384 +7 -0.1 111111000 111211212.95000306 18535202.15833384 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t2; total_rows min_value max_value sum avg 30 -10000000000 10000000000 31322222339 1044074077.9667 @@ -1160,7 +1160,7 @@ total_rows min_value max_value sum avg 30 0 10000000000 43444444564 1448148152.1333 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t2; total_rows min_value max_value sum avg -30 -3.4028234663852886e38 3.4028234663852886e38 1.0208470399155866e39 3.4028234663852886e37 +30 -3.40282e38 3.40282e38 1.0208470399155866e39 3.4028234663852886e37 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 diff --git a/mysql-test/suite/funcs_1/r/is_routines_embedded.result b/mysql-test/suite/funcs_1/r/is_routines_embedded.result index ec375e9c5f6..1ac3651f254 100644 --- a/mysql-test/suite/funcs_1/r/is_routines_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_routines_embedded.result @@ -199,6 +199,10 @@ END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.plugin, mysql.proc, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.global_priv; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci +AddGeometryColumn def mysql AddGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin + set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +DropGeometryColumn def mysql DropGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin + set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci connect testuser2, localhost, testuser2, , db_datadict; SELECT * FROM information_schema.routines; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION @@ -211,6 +215,10 @@ END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.plugin, mysql.proc, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.global_priv; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci +AddGeometryColumn def mysql AddGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin + set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +DropGeometryColumn def mysql DropGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin + set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci connect testuser3, localhost, testuser3, , test; SELECT * FROM information_schema.routines; SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION @@ -223,6 +231,10 @@ END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert'); SELECT * FROM INFORMATION_SCHEMA.ROUTINES; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.plugin, mysql.proc, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.global_priv; SELECT * FROM INFORMATION_SCHEMA.PLUGINS; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8 utf8_general_ci latin1_swedish_ci +AddGeometryColumn def mysql AddGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin + set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +DropGeometryColumn def mysql DropGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin + set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci latin1_swedish_ci connection default; disconnect testuser1; disconnect testuser2; diff --git a/mysql-test/suite/galera/r/galera_events.result b/mysql-test/suite/galera/r/galera_events.result index 791b0be729d..373f063c2bd 100644 --- a/mysql-test/suite/galera/r/galera_events.result +++ b/mysql-test/suite/galera/r/galera_events.result @@ -2,6 +2,8 @@ connection node_2; connection node_1; connection node_1; CREATE EVENT event1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. connection node_2; SELECT DEFINER= 'root@localhost', ORIGINATOR = 1, STATUS = 'SLAVESIDE_DISABLED', EVENT_TYPE = 'ONE TIME', ON_COMPLETION = 'NOT PRESERVE' FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'event1'; DEFINER= 'root@localhost' ORIGINATOR = 1 STATUS = 'SLAVESIDE_DISABLED' EVENT_TYPE = 'ONE TIME' ON_COMPLETION = 'NOT PRESERVE' diff --git a/mysql-test/suite/galera/r/galera_parallel_autoinc_largetrx.result b/mysql-test/suite/galera/r/galera_parallel_autoinc_largetrx.result index d2e09d7084f..36f676349a3 100644 --- a/mysql-test/suite/galera/r/galera_parallel_autoinc_largetrx.result +++ b/mysql-test/suite/galera/r/galera_parallel_autoinc_largetrx.result @@ -5,6 +5,7 @@ CREATE TABLE ten (f1 INTEGER) engine=InnoDB; INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT PRIMARY KEY, f2 INTEGER) Engine=InnoDB; connection node_2; +set session wsrep_sync_wait=15; SET GLOBAL wsrep_slave_threads = 4; connection node_1; INSERT INTO t1 (f2) SELECT 1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4;; diff --git a/mysql-test/suite/galera/r/galera_parallel_autoinc_manytrx.result b/mysql-test/suite/galera/r/galera_parallel_autoinc_manytrx.result index 09a415d47eb..88cb6cacc07 100644 --- a/mysql-test/suite/galera/r/galera_parallel_autoinc_manytrx.result +++ b/mysql-test/suite/galera/r/galera_parallel_autoinc_manytrx.result @@ -1,22 +1,35 @@ connection node_2; connection node_1; connection node_1; -CREATE TABLE ten (f1 INTEGER); +CREATE TABLE ten (f1 INTEGER) Engine=InnoDB; INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT PRIMARY KEY, f2 INTEGER) Engine=InnoDB; connection node_2; +set session wsrep_sync_wait=15; SET GLOBAL wsrep_slave_threads = 4; connection node_1; +CREATE PROCEDURE p1 (repeat_count int) +BEGIN +DECLARE current_num int; +SET current_num = 0; +WHILE current_num < repeat_count do +INSERT INTO t1 (f2) SELECT 1 FROM ten AS a1; +COMMIT; +SET current_num = current_num + 1; +END WHILE; +END| +connection node_1a; +connection node_1b; connection node_2; -SELECT COUNT(*) = 20000 FROM t1; -COUNT(*) = 20000 -1 -SELECT COUNT(DISTINCT f1) = 20000 FROM t1; -COUNT(DISTINCT f1) = 20000 -1 -SELECT COUNT(*) = 4 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE LIKE 'wsrep applier committed%'; -COUNT(*) = 4 -1 +SELECT COUNT(*) FROM t1; +COUNT(*) +40000 +SELECT COUNT(DISTINCT f1) FROM t1; +COUNT(DISTINCT f1) +40000 +disconnect node_1a; +disconnect node_1b; connection default; DROP TABLE t1; DROP TABLE ten; +DROP PROCEDURE p1; diff --git a/mysql-test/suite/galera/t/galera_parallel_autoinc_largetrx.test b/mysql-test/suite/galera/t/galera_parallel_autoinc_largetrx.test index 203d18b85a6..31fbb6914c9 100644 --- a/mysql-test/suite/galera/t/galera_parallel_autoinc_largetrx.test +++ b/mysql-test/suite/galera/t/galera_parallel_autoinc_largetrx.test @@ -12,12 +12,13 @@ --source include/galera_connect.inc --connection node_1 -CREATE TABLE ten (f1 INTEGER) engine=InnoDB; +CREATE TABLE ten (f1 INTEGER) Engine=InnoDB; INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT PRIMARY KEY, f2 INTEGER) Engine=InnoDB; --connection node_2 +set session wsrep_sync_wait=15; --let $wsrep_slave_threads_orig = `SELECT @@wsrep_slave_threads` SET GLOBAL wsrep_slave_threads = 4; --let $wait_condition = SELECT VARIABLE_VALUE = @@wsrep_slave_threads + 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_thread_count'; @@ -44,6 +45,7 @@ SELECT COUNT(DISTINCT f1) FROM t1; --connection node_2 --reap +--disconnect node_1a SELECT COUNT(*) FROM t1; SELECT COUNT(DISTINCT f1) FROM t1; diff --git a/mysql-test/suite/galera/t/galera_parallel_autoinc_manytrx.test b/mysql-test/suite/galera/t/galera_parallel_autoinc_manytrx.test index d2156cb3577..d04603891db 100644 --- a/mysql-test/suite/galera/t/galera_parallel_autoinc_manytrx.test +++ b/mysql-test/suite/galera/t/galera_parallel_autoinc_manytrx.test @@ -6,43 +6,78 @@ --source include/have_innodb.inc --source include/big_test.inc -# Create a second connection to node1 so that we can run transactions concurrently +--connection node_1 +CREATE TABLE ten (f1 INTEGER) Engine=InnoDB; +INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); + +CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT PRIMARY KEY, f2 INTEGER) Engine=InnoDB; + +# Create few connections to node1 so that we can run transactions concurrently --let $galera_connection_name = node_1a --let $galera_server_number = 1 --source include/galera_connect.inc ---connection node_1 -CREATE TABLE ten (f1 INTEGER); -INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +--let $galera_connection_name = node_1b +--let $galera_server_number = 1 +--source include/galera_connect.inc -CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT PRIMARY KEY, f2 INTEGER) Engine=InnoDB; --connection node_2 +set session wsrep_sync_wait=15; --let $wsrep_slave_threads_orig = `SELECT @@wsrep_slave_threads` SET GLOBAL wsrep_slave_threads = 4; +--let $wait_condition = SELECT COUNT(*) = @@wsrep_slave_threads + 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); +--source include/wait_condition.inc + +--connection node_1 +DELIMITER |; +CREATE PROCEDURE p1 (repeat_count int) +BEGIN + DECLARE current_num int; + SET current_num = 0; + WHILE current_num < repeat_count do + INSERT INTO t1 (f2) SELECT 1 FROM ten AS a1; + COMMIT; + SET current_num = current_num + 1; + END WHILE; +END| +DELIMITER ;| + +--disable_query_log +send call p1(1000); + +--connection node_1a +--disable_query_log +send call p1(1000); + +--connection node_1b +--disable_query_log +send call p1(1000); + +--connection node_2 +--disable_query_log +send call p1(1000); --connection node_1 ---let $count = 1000 -while ($count) -{ - --disable_query_log - INSERT INTO t1 (f2) SELECT 1 FROM ten AS a1; - --enable_query_log - --dec $count -} +reap; +--enable_query_log + +--connection node_1a +reap; +--enable_query_log + +--connection node_1b +reap; +--enable_query_log --connection node_2 ---let $count = 1000 -while ($count) -{ - --disable_query_log - INSERT INTO t1 (f2) SELECT 1 FROM ten AS a1; - --enable_query_log - --dec $count -} - -SELECT COUNT(*) = 20000 FROM t1; -SELECT COUNT(DISTINCT f1) = 20000 FROM t1; -SELECT COUNT(*) = 4 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE LIKE 'wsrep applier committed%'; +reap; +--enable_query_log + +SELECT COUNT(*) FROM t1; +SELECT COUNT(DISTINCT f1) FROM t1; + +--disconnect node_1a +--disconnect node_1b --disable_query_log --eval SET GLOBAL wsrep_slave_threads = $wsrep_slave_threads_orig; @@ -51,4 +86,6 @@ SELECT COUNT(*) = 4 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system use --connection default DROP TABLE t1; DROP TABLE ten; +DROP PROCEDURE p1; + diff --git a/mysql-test/suite/gcol/r/innodb_virtual_purge.result b/mysql-test/suite/gcol/r/innodb_virtual_purge.result index 308b01ded25..ee88527ec2e 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_purge.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_purge.result @@ -142,4 +142,21 @@ CREATE TABLE t1 (a VARCHAR(30), b INT, a2 VARCHAR(30) GENERATED ALWAYS AS (a) VI CREATE INDEX idx ON t1(a2(10), b, a2(20)); ERROR 42S21: Duplicate column name 'a2' DROP TABLE t1; +# +# MDEV-17540 Server crashes in row_purge after TRUNCATE TABLE +# +CREATE TABLE t1 (a BIT(14)) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(b'01110110101011'),(b'01100111111000'),(b'00001011110100'), +(b'01110110111010'),(b'10001010101011'),(b'01100111001111'); +CREATE TABLE t2 ( +pk INT DEFAULT 1, +b YEAR, +c BIT(14), +d YEAR AS (b), +e BIT(14) AS (c), +UNIQUE(pk), +KEY(e) +) ENGINE=InnoDB; +DROP TABLE t1, t2; SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_purge.test b/mysql-test/suite/gcol/t/innodb_virtual_purge.test index 4eb5d8c65b8..c79a817dd4e 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_purge.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_purge.test @@ -137,5 +137,40 @@ CREATE INDEX idx ON t1(a2(10), b, a2(20)); DROP TABLE t1; +--echo # +--echo # MDEV-17540 Server crashes in row_purge after TRUNCATE TABLE +--echo # + +# Note: this test case is nondeterministic and should depend on +# MDEV-12288 to trigger the needed purge activity. +# The test does not seem to repeat the bug on MariaDB 10.2. + +CREATE TABLE t1 (a BIT(14)) ENGINE=InnoDB; +INSERT INTO t1 VALUES + (b'01110110101011'),(b'01100111111000'),(b'00001011110100'), + (b'01110110111010'),(b'10001010101011'),(b'01100111001111'); + +CREATE TABLE t2 ( + pk INT DEFAULT 1, + b YEAR, + c BIT(14), + d YEAR AS (b), + e BIT(14) AS (c), + UNIQUE(pk), + KEY(e) +) ENGINE=InnoDB; + +# Run a few times in order to improve the chances of triggering the bug. +--disable_query_log +let $n=10; +while ($n) { +REPLACE INTO t2 (c) SELECT a FROM t1; +TRUNCATE TABLE t2; +dec $n; +} +--enable_query_log + +DROP TABLE t1, t2; + --source include/wait_until_count_sessions.inc SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result index 447013d408d..9dee6efcb04 100644 --- a/mysql-test/suite/innodb/r/foreign-keys.result +++ b/mysql-test/suite/innodb/r/foreign-keys.result @@ -100,6 +100,30 @@ CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB; INSERT INTO t2 VALUES(2); ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE; DROP TABLE t2, t1; +# +# MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name) +# +CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 LIKE t1; +FLUSH TABLES; +SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go'; +ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE; +connect con1, localhost, root; +SET debug_sync='now WAIT_FOR ready'; +SET lock_wait_timeout=1; +UPDATE t2 SET pk=10 WHERE pk=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1'; +DEALLOCATE PREPARE stmt; +SET debug_sync='now SIGNAL go'; +connection default; +disconnect con1; +connection default; +SET debug_sync='reset'; +SHOW OPEN TABLES FROM test; +Database Table In_use Name_locked +test t2 0 0 +DROP TABLE t1, t2; create table t1 (a int primary key, b int) engine=innodb; create table t2 (c int primary key, d int, foreign key (d) references t1 (a) on update cascade) engine=innodb; diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result index fff2a9c9d3f..4eba99950c7 100644 --- a/mysql-test/suite/innodb/r/innodb-index.result +++ b/mysql-test/suite/innodb/r/innodb-index.result @@ -1216,6 +1216,33 @@ Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; # +# Bug#19811005 ALTER TABLE ADD INDEX DOES NOT UPDATE INDEX_LENGTH +# IN I_S TABLES +# +CREATE TABLE t1(a INT, b INT) ENGINE=INNODB, STATS_PERSISTENT=1; +SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D, +cast(INDEX_LENGTH/@@innodb_page_size as int) I +FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'; +D I +1 0 +ALTER TABLE t1 ADD INDEX (a); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D, +cast(INDEX_LENGTH/@@innodb_page_size as int) I +FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'; +D I +1 1 +ALTER TABLE t1 ADD INDEX (b); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D, +cast(INDEX_LENGTH/@@innodb_page_size as int) I +FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'; +D I +1 2 +DROP TABLE t1; +# # Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE # SET GLOBAL innodb_monitor_enable = module_ddl; diff --git a/mysql-test/suite/innodb/r/innodb-truncate.result b/mysql-test/suite/innodb/r/innodb-truncate.result index a606868ae52..8610a892cc6 100644 --- a/mysql-test/suite/innodb/r/innodb-truncate.result +++ b/mysql-test/suite/innodb/r/innodb-truncate.result @@ -78,3 +78,16 @@ a 1 2 DROP TABLE t1; +call mtr.add_suppression('InnoDB: in RENAME TABLE table `test`.`t3`'); +SET FOREIGN_KEY_CHECKS= OFF; +CREATE TABLE t1 (f2 INT, f4 INT, KEY(f2), FOREIGN KEY (f4) REFERENCES t3 (f4)) ENGINE=InnoDB; +SET FOREIGN_KEY_CHECKS= ON; +CREATE TABLE t2 (f2 INT, FOREIGN KEY(f2) REFERENCES t1 (f2)) ENGINE=InnoDB; +CREATE TABLE t3 (a INT) ENGINE=InnoDB; +ERROR HY000: Can't create table `test`.`t3` (errno: 150 "Foreign key constraint is incorrectly formed") +ALTER TABLE t1 RENAME TO t3; +ERROR HY000: Error on rename of './test/t1' to './test/t3' (errno: 150 "Foreign key constraint is incorrectly formed") +ALTER TABLE t1 FORCE; +TRUNCATE TABLE t1; +ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `test`.`t3` (`f2`)) +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result index 6b69d2d9ee4..0c2c456c77c 100644 --- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result +++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result @@ -202,9 +202,9 @@ log_lsn_checkpoint_age recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL log_lsn_buf_pool_oldest recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value The oldest modified block LSN in the buffer pool log_max_modified_age_async recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Maximum LSN difference; when exceeded, start asynchronous preflush log_max_modified_age_sync recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Maximum LSN difference; when exceeded, start synchronous preflush -log_pending_log_flushes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Pending log flushes -log_pending_checkpoint_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Pending checkpoints -log_num_log_io recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of log I/Os +log_pending_log_flushes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Pending log flushes +log_pending_checkpoint_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Pending checkpoints +log_num_log_io recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled value Number of log I/Os log_waits recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log waits due to small log buffer (innodb_log_waits) log_write_requests recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log write requests (innodb_log_write_requests) log_writes recovery 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled status_counter Number of log writes (innodb_log_writes) @@ -326,14 +326,24 @@ the www select * from information_schema.innodb_ft_deleted; DOC_ID +Warnings: +Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_ft_deleted but the InnoDB storage engine is not installed select * from information_schema.innodb_ft_being_deleted; DOC_ID +Warnings: +Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_ft_being_deleted but the InnoDB storage engine is not installed select * from information_schema.innodb_ft_index_cache; WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +Warnings: +Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_ft_index_cache but the InnoDB storage engine is not installed select * from information_schema.innodb_ft_index_table; WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +Warnings: +Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_ft_index_table but the InnoDB storage engine is not installed select * from information_schema.innodb_ft_config; KEY VALUE +Warnings: +Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_ft_config but the InnoDB storage engine is not installed select * from information_schema.innodb_buffer_page; POOL_ID BLOCK_ID SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE PAGE_STATE IO_FIX IS_OLD FREE_PAGE_CLOCK Warnings: diff --git a/mysql-test/suite/innodb/r/instant_alter_crash.result b/mysql-test/suite/innodb/r/instant_alter_crash.result index 528bd9a905a..cfcb24f8bb2 100644 --- a/mysql-test/suite/innodb/r/instant_alter_crash.result +++ b/mysql-test/suite/innodb/r/instant_alter_crash.result @@ -6,16 +6,17 @@ CREATE TABLE t1(id INT PRIMARY KEY, c2 INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; CREATE TABLE t2 LIKE t1; INSERT INTO t1 VALUES(0,2); -BEGIN; INSERT INTO t2 VALUES(2,1); ALTER TABLE t2 ADD COLUMN (c3 TEXT NOT NULL DEFAULT 'De finibus bonorum'); +BEGIN; +INSERT INTO t2 VALUES(3,4,'accusantium doloremque laudantium'); connect ddl, localhost, root; SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever'; ALTER TABLE t1 ADD COLUMN (c3 TEXT NOT NULL DEFAULT ' et malorum'); connection default; SET DEBUG_SYNC='now WAIT_FOR ddl'; SET GLOBAL innodb_flush_log_at_trx_commit=1; -INSERT INTO t2 VALUES(3,4,'accusantium doloremque laudantium'); +COMMIT; # Kill the server disconnect ddl; # restart diff --git a/mysql-test/suite/innodb/r/xa_debug.result b/mysql-test/suite/innodb/r/xa_debug.result index 902166f51c8..7c2bd9f92c2 100644 --- a/mysql-test/suite/innodb/r/xa_debug.result +++ b/mysql-test/suite/innodb/r/xa_debug.result @@ -252,6 +252,7 @@ insert into t1 values(1); xa end 'test1'; xa prepare 'test1'; connection default; +FLUSH TABLES; xa recover; formatID gtrid_length bqual_length data 1 5 0 test1 diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test index 442467b7dbe..e5950e01a11 100644 --- a/mysql-test/suite/innodb/t/foreign-keys.test +++ b/mysql-test/suite/innodb/t/foreign-keys.test @@ -127,6 +127,37 @@ INSERT INTO t2 VALUES(2); ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE; DROP TABLE t2, t1; + +--echo # +--echo # MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name) +--echo # +CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 LIKE t1; +FLUSH TABLES; + +SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go'; +send ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE; + +connect con1, localhost, root; +SET debug_sync='now WAIT_FOR ready'; +SET lock_wait_timeout=1; # change to 0 in 10.3 +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t2 SET pk=10 WHERE pk=1; +PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1'; +DEALLOCATE PREPARE stmt; +SET debug_sync='now SIGNAL go'; + +connection default; +reap; + +# Cleanup +disconnect con1; + +connection default; +SET debug_sync='reset'; +SHOW OPEN TABLES FROM test; +DROP TABLE t1, t2; + # # FK and prelocking: # child table accesses (reads and writes) wait for locks. diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index b3c095c9b33..9350672bee9 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -570,6 +570,26 @@ CHECK TABLE t1; DROP TABLE t1; --echo # +--echo # Bug#19811005 ALTER TABLE ADD INDEX DOES NOT UPDATE INDEX_LENGTH +--echo # IN I_S TABLES +--echo # +let $i_s_query=SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D, +cast(INDEX_LENGTH/@@innodb_page_size as int) I +FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'; + +CREATE TABLE t1(a INT, b INT) ENGINE=INNODB, STATS_PERSISTENT=1; +eval $i_s_query; +--enable_info +ALTER TABLE t1 ADD INDEX (a); +--disable_info +eval $i_s_query; +--enable_info +ALTER TABLE t1 ADD INDEX (b); +--disable_info +eval $i_s_query; +DROP TABLE t1; + +--echo # --echo # Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE --echo # diff --git a/mysql-test/suite/innodb/t/innodb-truncate.test b/mysql-test/suite/innodb/t/innodb-truncate.test index 8f9b1f1f0e9..71c0fcfea8b 100644 --- a/mysql-test/suite/innodb/t/innodb-truncate.test +++ b/mysql-test/suite/innodb/t/innodb-truncate.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +let $datadir=`select @@datadir`; --echo # --echo # TRUNCATE TABLE --echo # @@ -73,3 +74,21 @@ SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1'; INSERT INTO t1 () VALUES (), (); SELECT a FROM t1 ORDER BY a; DROP TABLE t1; + +# +# MDEV-18923 Assertion `!lex_string_cmp(system_charset_info, fk_info->referenced_table, &table->s->table_name)' failed in fk_truncate_illegal_if_parent +# +call mtr.add_suppression('InnoDB: in RENAME TABLE table `test`.`t3`'); +SET FOREIGN_KEY_CHECKS= OFF; +CREATE TABLE t1 (f2 INT, f4 INT, KEY(f2), FOREIGN KEY (f4) REFERENCES t3 (f4)) ENGINE=InnoDB; +SET FOREIGN_KEY_CHECKS= ON; +CREATE TABLE t2 (f2 INT, FOREIGN KEY(f2) REFERENCES t1 (f2)) ENGINE=InnoDB; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t3 (a INT) ENGINE=InnoDB; +--replace_result $datadir ./ +--error ER_ERROR_ON_RENAME +ALTER TABLE t1 RENAME TO t3; +ALTER TABLE t1 FORCE; +--error ER_TRUNCATE_ILLEGAL_FK +TRUNCATE TABLE t1; +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_crash.test b/mysql-test/suite/innodb/t/instant_alter_crash.test index d16ee6c929a..13ff292d9ff 100644 --- a/mysql-test/suite/innodb/t/instant_alter_crash.test +++ b/mysql-test/suite/innodb/t/instant_alter_crash.test @@ -17,9 +17,10 @@ CREATE TABLE t1(id INT PRIMARY KEY, c2 INT UNIQUE) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; CREATE TABLE t2 LIKE t1; INSERT INTO t1 VALUES(0,2); -BEGIN; INSERT INTO t2 VALUES(2,1); ALTER TABLE t2 ADD COLUMN (c3 TEXT NOT NULL DEFAULT 'De finibus bonorum'); +BEGIN; +INSERT INTO t2 VALUES(3,4,'accusantium doloremque laudantium'); connect ddl, localhost, root; SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever'; @@ -29,7 +30,7 @@ ALTER TABLE t1 ADD COLUMN (c3 TEXT NOT NULL DEFAULT ' et malorum'); connection default; SET DEBUG_SYNC='now WAIT_FOR ddl'; SET GLOBAL innodb_flush_log_at_trx_commit=1; -INSERT INTO t2 VALUES(3,4,'accusantium doloremque laudantium'); +COMMIT; --source include/kill_mysqld.inc disconnect ddl; diff --git a/mysql-test/suite/innodb/t/xa_debug.test b/mysql-test/suite/innodb/t/xa_debug.test index 5724891bb65..5e38ba1124b 100644 --- a/mysql-test/suite/innodb/t/xa_debug.test +++ b/mysql-test/suite/innodb/t/xa_debug.test @@ -20,6 +20,7 @@ dec $trial; connection default; # Kill and restart the server. +FLUSH TABLES; -- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect -- shutdown_server 0 -- source include/wait_until_disconnected.inc diff --git a/mysql-test/suite/innodb_fts/r/innodb_ft_aux_table.result b/mysql-test/suite/innodb_fts/r/innodb_ft_aux_table.result new file mode 100644 index 00000000000..a1ffb0a5d09 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/innodb_ft_aux_table.result @@ -0,0 +1,121 @@ +CREATE TABLE t1 (v VARCHAR(100), FULLTEXT INDEX (v)) ENGINE=InnoDB; +insert into t1 VALUES('First record'),('Second record'),('Third record'); +SET @save_ft_aux_table = @@GLOBAL.innodb_ft_aux_table; +connect con1,localhost,root,,; +SET GLOBAL innodb_ft_aux_table = 'test/t0'; +ERROR 42000: Variable 'innodb_ft_aux_table' can't be set to the value of 'test/t0' +connection default; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD; +value +a +about +an +are +as +at +be +by +com +de +en +for +from +how +i +in +is +it +la +of +on +or +that +the +this +to +was +what +when +where +who +will +with +und +the +www +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +KEY VALUE +connection con1; +SET GLOBAL innodb_ft_aux_table = 'test/t1'; +disconnect con1; +connection default; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +first 1 1 1 1 0 +record 1 3 3 1 6 +record 1 3 3 2 7 +record 1 3 3 3 6 +second 2 2 1 2 0 +third 3 3 1 3 0 +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +KEY VALUE +optimize_checkpoint_limit 180 +synced_doc_id 0 +stopword_table_name +use_stopword 1 +SELECT @@GLOBAL.innodb_ft_aux_table; +@@GLOBAL.innodb_ft_aux_table +test/t1 +RENAME TABLE t1 TO t2; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +first 1 1 1 1 0 +record 1 3 3 1 6 +record 1 3 3 2 7 +record 1 3 3 3 6 +second 2 2 1 2 0 +third 3 3 1 3 0 +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +KEY VALUE +optimize_checkpoint_limit 180 +synced_doc_id 0 +stopword_table_name +use_stopword 1 +SELECT @@GLOBAL.innodb_ft_aux_table; +@@GLOBAL.innodb_ft_aux_table +test/t1 +DROP TABLE t2; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; +DOC_ID +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +KEY VALUE +SELECT @@GLOBAL.innodb_ft_aux_table; +@@GLOBAL.innodb_ft_aux_table +test/t1 +SET GLOBAL innodb_ft_aux_table = @save_ft_aux_table; diff --git a/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.opt b/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.opt new file mode 100644 index 00000000000..f85581275ed --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.opt @@ -0,0 +1,6 @@ +--innodb_ft_default_stopword +--innodb_ft_deleted +--innodb_ft_being_deleted +--innodb_ft_index_cache +--innodb_ft_index_table +--innodb_ft_config diff --git a/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.test b/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.test new file mode 100644 index 00000000000..48964aef4fd --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.test @@ -0,0 +1,43 @@ +--source include/have_innodb.inc + +CREATE TABLE t1 (v VARCHAR(100), FULLTEXT INDEX (v)) ENGINE=InnoDB; + +insert into t1 VALUES('First record'),('Second record'),('Third record'); + +SET @save_ft_aux_table = @@GLOBAL.innodb_ft_aux_table; + +connect (con1,localhost,root,,); +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL innodb_ft_aux_table = 'test/t0'; +connection default; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +connection con1; +SET GLOBAL innodb_ft_aux_table = 'test/t1'; +disconnect con1; +connection default; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +SELECT @@GLOBAL.innodb_ft_aux_table; +RENAME TABLE t1 TO t2; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +SELECT @@GLOBAL.innodb_ft_aux_table; +DROP TABLE t2; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +SELECT @@GLOBAL.innodb_ft_aux_table; +SET GLOBAL innodb_ft_aux_table = @save_ft_aux_table; diff --git a/mysql-test/suite/json/r/json_no_table.result b/mysql-test/suite/json/r/json_no_table.result index 41150032e51..b8ac19bd09e 100644 --- a/mysql-test/suite/json/r/json_no_table.result +++ b/mysql-test/suite/json/r/json_no_table.result @@ -821,13 +821,13 @@ select json_merge( '[1, 2]', '[3, 4' ); json_merge( '[1, 2]', '[3, 4' ) NULL Warnings: -Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_merge' +Warning 4037 Unexpected end of JSON text in argument 2 to function 'json_merge_preserve' error ER_INVALID_JSON_TEXT_IN_PARAM select json_merge( '[1, 2', '[3, 4]' ); json_merge( '[1, 2', '[3, 4]' ) NULL Warnings: -Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_merge' +Warning 4037 Unexpected end of JSON text in argument 1 to function 'json_merge_preserve' select json_merge( '1', '2' ); json_merge( '1', '2' ) [1, 2] diff --git a/mysql-test/suite/perfschema/r/pfs_upgrade_event.result b/mysql-test/suite/perfschema/r/pfs_upgrade_event.result index 2bcebe06e96..cbaeec201e0 100644 --- a/mysql-test/suite/perfschema/r/pfs_upgrade_event.result +++ b/mysql-test/suite/perfschema/r/pfs_upgrade_event.result @@ -1,6 +1,8 @@ "Testing mysql_upgrade with EVENT performance_schema.user_event" create event test.user_event on schedule every 1 day do select "not supposed to be here"; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. update mysql.event set db='performance_schema' where name='user_event'; select name from mysql.event where db='performance_schema'; name diff --git a/mysql-test/suite/perfschema/t/ddl_esms_by_digest.test b/mysql-test/suite/perfschema/t/ddl_esms_by_digest.test index b674dc8d385..cea7ea27299 100644 --- a/mysql-test/suite/perfschema/t/ddl_esms_by_digest.test +++ b/mysql-test/suite/perfschema/t/ddl_esms_by_digest.test @@ -11,7 +11,7 @@ # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software Foundation, -# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA # Tests for PERFORMANCE_SCHEMA diff --git a/mysql-test/suite/perfschema/t/dml_esms_by_digest.test b/mysql-test/suite/perfschema/t/dml_esms_by_digest.test index 6fdc8ec9fdc..88729e6a7a8 100644 --- a/mysql-test/suite/perfschema/t/dml_esms_by_digest.test +++ b/mysql-test/suite/perfschema/t/dml_esms_by_digest.test @@ -11,7 +11,7 @@ # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software Foundation, -# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA # Tests for PERFORMANCE_SCHEMA diff --git a/mysql-test/suite/perfschema_stress/README b/mysql-test/suite/perfschema_stress/README index 25101f429e5..b21de197246 100644 --- a/mysql-test/suite/perfschema_stress/README +++ b/mysql-test/suite/perfschema_stress/README @@ -12,7 +12,7 @@ # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA +# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA WL#4818 - Stress testing of PERFORMANCE_SCHEMA diff --git a/mysql-test/suite/plugins/r/feedback_plugin_load.result b/mysql-test/suite/plugins/r/feedback_plugin_load.result index 2b8fc03b931..d76625bd3e2 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_load.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_load.result @@ -2,9 +2,9 @@ select plugin_status from information_schema.plugins where plugin_name='feedback plugin_status ACTIVE SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; -SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback where variable_name = 'FEEDBACK used'; -variable_value = @feedback_used + 1 -0 +SELECT variable_value = @feedback_used + 1 as 'MUST BE 1' FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +MUST BE 1 +1 select * from information_schema.feedback where variable_name like 'feed%' and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' and variable_name not like '%debug%'; diff --git a/mysql-test/suite/plugins/r/feedback_plugin_send.result b/mysql-test/suite/plugins/r/feedback_plugin_send.result index 0bdf94335d5..5a48c703ec4 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_send.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_send.result @@ -2,9 +2,9 @@ select plugin_status from information_schema.plugins where plugin_name='feedback plugin_status ACTIVE SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; -SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback where variable_name = 'FEEDBACK used'; -variable_value = @feedback_used + 1 -0 +SELECT variable_value = @feedback_used + 1 as 'MUST BE 1' FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +MUST BE 1 +1 select * from information_schema.feedback where variable_name like 'feed%' and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' and variable_name not like '%debug%'; diff --git a/mysql-test/suite/plugins/r/pam.result b/mysql-test/suite/plugins/r/pam.result index a16cd7f3d43..1d70f530969 100644 --- a/mysql-test/suite/plugins/r/pam.result +++ b/mysql-test/suite/plugins/r/pam.result @@ -29,4 +29,24 @@ Now, the magic number! PIN: *** drop user test_pam; drop user pam_test; +create user PAM_TEST identified via pam using 'mariadb_mtr'; +# +# athentication is unsuccessful +# +Challenge input first. +Enter: not very secret challenge +Now, the magic number! +PIN: **** +set global pam_winbind_workaround=1; +# +# athentication is successful +# +Challenge input first. +Enter: not very secret challenge +Now, the magic number! +PIN: **** +select user(), current_user(), database(); +user() current_user() database() +PAM_TEST@localhost PAM_TEST@% test +drop user PAM_TEST; uninstall plugin pam; diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.test b/mysql-test/suite/plugins/t/feedback_plugin_load.test index cfaf68ce96d..165b7d4c3b8 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_load.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.test @@ -17,7 +17,7 @@ select plugin_status from information_schema.plugins where plugin_name='feedback SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; # Now $feedback_used == X+1, and 'FEEDBACK used' is also X+1. And variable_value is increased again when we run the next SELECT -SELECT variable_value = @feedback_used + 1 FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +SELECT variable_value = @feedback_used + 1 as 'MUST BE 1' FROM information_schema.feedback where variable_name = 'FEEDBACK used'; # Now when we are happy with 'FEEDBACK used', we can check everything else diff --git a/mysql-test/suite/plugins/t/pam.test b/mysql-test/suite/plugins/t/pam.test index 6bb282f68c0..040b26ef8b8 100644 --- a/mysql-test/suite/plugins/t/pam.test +++ b/mysql-test/suite/plugins/t/pam.test @@ -23,13 +23,13 @@ EOF --echo # athentication is successful, challenge/pin are ok --echo # note that current_user() differs from user() --echo # ---exec $MYSQL_TEST -u test_pam --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/pam_good.txt +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_good.txt --echo # --echo # athentication is unsuccessful --echo # --error 1 ---exec $MYSQL_TEST -u test_pam --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/pam_bad.txt +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_bad.txt --echo # --echo # athentication is unsuccessful @@ -37,10 +37,27 @@ EOF --error 1 --exec $MYSQL_TEST -u test_pam --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/pam_ugly.txt ---remove_file $MYSQLTEST_VARDIR/tmp/pam_good.txt ---remove_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt drop user test_pam; drop user pam_test; +create user PAM_TEST identified via pam using 'mariadb_mtr'; + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u PAM_TEST < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +set global pam_winbind_workaround=1; +--echo # +--echo # athentication is successful +--echo # +--exec $MYSQL_TEST -u PAM_TEST < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_ugly.txt +drop user PAM_TEST; + let $count_sessions= 1; --source include/wait_until_count_sessions.inc uninstall plugin pam; diff --git a/mysql-test/suite/rpl/disabled.def b/mysql-test/suite/rpl/disabled.def index 9f43bc3c339..e5f708051ed 100644 --- a/mysql-test/suite/rpl/disabled.def +++ b/mysql-test/suite/rpl/disabled.def @@ -15,7 +15,6 @@ rpl_get_master_version_and_clock : Bug#11766137 Jan 05 2011 joro Valgrind warnin rpl_partition_archive : MDEV-5077 2013-09-27 svoj Cannot exchange partition with archive table rpl_row_binlog_max_cache_size : MDEV-11092 rpl_blackhole : MDEV-11094 -rpl_row_mysqlbinlog : MDEV-11095 rpl_row_index_choice : MDEV-11666 rpl_parallel2 : fails after MDEV-16172 rpl_semi_sync_after_sync : fails after MDEV-16172 diff --git a/mysql-test/suite/rpl/extension/checksum.pl b/mysql-test/suite/rpl/extension/checksum.pl index 60dca18c8f8..f94341446cd 100755 --- a/mysql-test/suite/rpl/extension/checksum.pl +++ b/mysql-test/suite/rpl/extension/checksum.pl @@ -13,7 +13,7 @@ # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA +# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA use File::Basename; use File::Copy qw(copy); diff --git a/mysql-test/suite/rpl/r/kill_race_condition.result b/mysql-test/suite/rpl/r/kill_race_condition.result new file mode 100644 index 00000000000..87ee3214b8e --- /dev/null +++ b/mysql-test/suite/rpl/r/kill_race_condition.result @@ -0,0 +1,18 @@ +include/master-slave.inc +[connection master] +connection slave; +set global debug_dbug='d,rows_log_event_before_open_table'; +set debug_sync='now WAIT_FOR before_open_table'; +connection master; +create table t1 (a int); +insert t1 values (1),(2),(3); +connection slave; +kill slave_sql_thread; +set debug_sync='now SIGNAL go_ahead_sql'; +set global debug_dbug=''; +set debug_sync='RESET'; +connection master; +drop table t1; +connection slave; +start slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_binlog_dup_entry.result b/mysql-test/suite/rpl/r/rpl_binlog_dup_entry.result new file mode 100644 index 00000000000..108a65df07f --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_binlog_dup_entry.result @@ -0,0 +1,29 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 (id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, someLabel varchar(30) NOT NULL, flag tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id)) Engine=MyISAM; +CREATE TABLE t2 (id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, data varchar(30) NOT NULL, status tinyint(1) NOT NULL, PRIMARY KEY (id)) Engine=MyISAM; +CREATE TABLE t3 (id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, t1id mediumint(8) unsigned NOT NULL, flag tinyint(1) NOT NULL DEFAULT 0, status tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id)) Engine=MyISAM; +INSERT INTO t1 ( id, someLabel, flag ) VALUES ( 1, 'ABC', 0 ); +CREATE OR REPLACE TRIGGER doNothing +BEFORE UPDATE ON t1 +FOR EACH ROW +BEGIN +IF +new.someLabel != old.someLabel +THEN +UPDATE t3 SET t3.flag = 0; +END IF; +END| +FLUSH LOGS; +LOCK TABLES t1 WRITE, t2 WRITE; +INSERT INTO t2 (data, status) VALUES ('1', 4); +UPDATE t1 SET flag = 1 WHERE id = 1; +INSERT INTO t2 (data, status) VALUES ('2', 4); +UNLOCK TABLES; +connection slave; +include/diff_tables.inc [master:t1, slave:t1] +include/diff_tables.inc [master:t2, slave:t2] +include/diff_tables.inc [master:t3, slave:t3] +connection master; +DROP TABLE t1, t2, t3; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_event.result b/mysql-test/suite/rpl/r/rpl_create_drop_event.result index 250e7894c78..f055c4f1291 100644 --- a/mysql-test/suite/rpl/r/rpl_create_drop_event.result +++ b/mysql-test/suite/rpl/r/rpl_create_drop_event.result @@ -4,9 +4,13 @@ connection master; SET GLOBAL event_scheduler=off; CREATE TABLE t1 (a INT); CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (10); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (11); ERROR HY000: Event 'ev1' already exists CREATE OR REPLACE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (11); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. SELECT EVENT_NAME,STATUS,EVENT_DEFINITION FROM INFORMATION_SCHEMA.EVENTS; EVENT_NAME STATUS EVENT_DEFINITION ev1 ENABLED INSERT INTO t1 VALUES (11) diff --git a/mysql-test/suite/rpl/r/rpl_current_user.result b/mysql-test/suite/rpl/r/rpl_current_user.result index ba5269bef22..efb036023e9 100644 --- a/mysql-test/suite/rpl/r/rpl_current_user.result +++ b/mysql-test/suite/rpl/r/rpl_current_user.result @@ -90,6 +90,8 @@ include/diff_tables.inc [server_1:v_user, server_2:v_user, server_3:v_user] # Verify 'ALTER EVENT...' statement connection master; CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY DO SELECT * FROM t1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. # Explicitly assign CURRENT_USER() to definer ALTER DEFINER=CURRENT_USER() EVENT e1 ENABLE; include/rpl_sync.inc diff --git a/mysql-test/suite/rpl/r/rpl_events.result b/mysql-test/suite/rpl/r/rpl_events.result index 4b2226109d9..a84bc6b67f5 100644 --- a/mysql-test/suite/rpl/r/rpl_events.result +++ b/mysql-test/suite/rpl/r/rpl_events.result @@ -31,6 +31,8 @@ test justonce SLAVESIDE_DISABLED 1 DROP EVENT IF EXISTS test.slave_once; CREATE EVENT test.slave_once ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT IGNORE INTO t1(id, c) VALUES (3, 'from slave_once'); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. "Checking event status on the slave for originator value = slave's server_id" SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_once'; db name status originator @@ -78,6 +80,8 @@ db name status originator "Creating event test.slave_terminate on the slave" CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO INSERT IGNORE INTO t1(id, c) VALUES (6, 'from slave_terminate'); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. "Checking event status on the slave" SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate'; db name status originator @@ -87,6 +91,8 @@ DROP EVENT test.slave_terminate; "Creating event test.slave_terminate with DISABLE ON SLAVE on the slave" CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND DISABLE ON SLAVE DO INSERT IGNORE INTO t1(c) VALUES (7, 'from slave_terminate'); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. "Checking event status on the slave" SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate'; db name status originator diff --git a/mysql-test/suite/rpl/r/rpl_heartbeat_basic.result b/mysql-test/suite/rpl/r/rpl_heartbeat_basic.result index 4ffa8fc9883..1db69ea4b83 100644 --- a/mysql-test/suite/rpl/r/rpl_heartbeat_basic.result +++ b/mysql-test/suite/rpl/r/rpl_heartbeat_basic.result @@ -237,6 +237,8 @@ DO BEGIN UPDATE test.t1 SET a = a + 1 WHERE a < 10; END| +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. connection slave; RESET SLAVE; CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=MASTER_PORT, MASTER_USER='root', MASTER_CONNECT_RETRY=20, MASTER_HEARTBEAT_PERIOD=5; diff --git a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result index 89f59deae73..7dd3907f102 100644 --- a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result +++ b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result @@ -679,6 +679,8 @@ DROP TRIGGER tr1; ******************** EVENTS ******************** INSERT INTO t1 VALUES(1, 'test1'); CREATE EVENT e1 ON SCHEDULE EVERY '1' SECOND COMMENT 'e_second_comment' DO DELETE FROM t1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation test_rpl e1 root@localhost SYSTEM RECURRING NULL 1 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci diff --git a/mysql-test/suite/rpl/r/rpl_invoked_features.result b/mysql-test/suite/rpl/r/rpl_invoked_features.result index fcd79136e09..43a8e8625de 100644 --- a/mysql-test/suite/rpl/r/rpl_invoked_features.result +++ b/mysql-test/suite/rpl/r/rpl_invoked_features.result @@ -50,11 +50,15 @@ BEGIN ALTER EVENT e1 DISABLE; CALL p1(10, ''); END| +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. CREATE EVENT e11 ON SCHEDULE EVERY 1 SECOND DISABLE DO BEGIN ALTER EVENT e11 DISABLE; CALL p11(10, ''); END| +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. CREATE FUNCTION f1 (x INT) RETURNS VARCHAR(64) BEGIN IF x > 5 THEN diff --git a/mysql-test/suite/rpl/r/rpl_killed_ddl.result b/mysql-test/suite/rpl/r/rpl_killed_ddl.result index 66309432efe..2c0f27a3218 100644 --- a/mysql-test/suite/rpl/r/rpl_killed_ddl.result +++ b/mysql-test/suite/rpl/r/rpl_killed_ddl.result @@ -32,6 +32,8 @@ CREATE DATABASE d1; CREATE EVENT e1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO INSERT INTO test.t1 VALUES (1); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. CREATE FUNCTION f1 () RETURNS INT DETERMINISTIC RETURN 1; CREATE PROCEDURE p1 (OUT rows_cnt INT) diff --git a/mysql-test/suite/rpl/r/rpl_mixed_implicit_commit_binlog.result b/mysql-test/suite/rpl/r/rpl_mixed_implicit_commit_binlog.result index e768c5c6f7c..9fbb7b0ab19 100644 --- a/mysql-test/suite/rpl/r/rpl_mixed_implicit_commit_binlog.result +++ b/mysql-test/suite/rpl/r/rpl_mixed_implicit_commit_binlog.result @@ -62,6 +62,8 @@ INSERT INTO tt_1(ddl_case) VALUES (28); DROP USER 'user_new'@'localhost'; INSERT INTO tt_1(ddl_case) VALUES (27); CREATE EVENT evt ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO SELECT * FROM tt_1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. INSERT INTO tt_1(ddl_case) VALUES (26); ALTER EVENT evt COMMENT 'evt'; INSERT INTO tt_1(ddl_case) VALUES (25); diff --git a/mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result b/mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result index a2f3bb44ae1..c97f8968136 100644 --- a/mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result +++ b/mysql-test/suite/rpl/r/rpl_row_implicit_commit_binlog.result @@ -62,6 +62,8 @@ INSERT INTO tt_1(ddl_case) VALUES (28); DROP USER 'user_new'@'localhost'; INSERT INTO tt_1(ddl_case) VALUES (27); CREATE EVENT evt ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO SELECT * FROM tt_1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. INSERT INTO tt_1(ddl_case) VALUES (26); ALTER EVENT evt COMMENT 'evt'; INSERT INTO tt_1(ddl_case) VALUES (25); diff --git a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result index b86743507d8..42d7fc39b5c 100644 --- a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result +++ b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result @@ -169,7 +169,7 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1342177280/*!*/; +SET @@session.sql_mode=1411383296/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; @@ -192,7 +192,7 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1342177280/*!*/; +SET @@session.sql_mode=1411383296/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; @@ -307,7 +307,7 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1342177280/*!*/; +SET @@session.sql_mode=1411383296/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; @@ -336,7 +336,7 @@ use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; -SET @@session.sql_mode=1342177280/*!*/; +SET @@session.sql_mode=1411383296/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; diff --git a/mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result b/mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result index e768c5c6f7c..9fbb7b0ab19 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result +++ b/mysql-test/suite/rpl/r/rpl_stm_implicit_commit_binlog.result @@ -62,6 +62,8 @@ INSERT INTO tt_1(ddl_case) VALUES (28); DROP USER 'user_new'@'localhost'; INSERT INTO tt_1(ddl_case) VALUES (27); CREATE EVENT evt ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO SELECT * FROM tt_1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. INSERT INTO tt_1(ddl_case) VALUES (26); ALTER EVENT evt COMMENT 'evt'; INSERT INTO tt_1(ddl_case) VALUES (25); diff --git a/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result b/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result index 45070949f79..0eef8fa7371 100644 --- a/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result +++ b/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result @@ -4,6 +4,8 @@ CREATE TEMPORARY TABLE t1 (a INT); CREATE TABLE t2 (a INT, b INT) ENGINE= MyISAM; INSERT INTO t1 VALUES (1); CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. INSERT INTO t1 VALUES (1); ALTER EVENT e1 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; INSERT INTO t1 VALUES (1); @@ -125,6 +127,8 @@ ERROR HY000: Can't execute the given command because you have active locked tabl INSERT INTO t2 VALUES ("CREATE EVENT e1 with table locked"); UNLOCK TABLE; CREATE EVENT e2 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. LOCK TABLE t1 WRITE; ALTER EVENT e2 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction diff --git a/mysql-test/suite/rpl/t/kill_race_condition.test b/mysql-test/suite/rpl/t/kill_race_condition.test new file mode 100644 index 00000000000..4268c12cdbf --- /dev/null +++ b/mysql-test/suite/rpl/t/kill_race_condition.test @@ -0,0 +1,28 @@ +source include/have_debug_sync.inc; +source include/have_binlog_format_row.inc; +source include/master-slave.inc; + +connection slave; +set global debug_dbug='d,rows_log_event_before_open_table'; +send set debug_sync='now WAIT_FOR before_open_table'; + +connection master; +create table t1 (a int); +insert t1 values (1),(2),(3); + +connection slave; +reap; +let $a=`select id from information_schema.processlist where state='debug sync point: now'`; +replace_result $a slave_sql_thread; +eval kill $a; +set debug_sync='now SIGNAL go_ahead_sql'; +set global debug_dbug=''; +set debug_sync='RESET'; + +connection master; +drop table t1; + +connection slave; +start slave; + +source include/rpl_end.inc; diff --git a/mysql-test/suite/rpl/t/rpl_binlog_dup_entry.test b/mysql-test/suite/rpl/t/rpl_binlog_dup_entry.test new file mode 100644 index 00000000000..869c715f407 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_binlog_dup_entry.test @@ -0,0 +1,72 @@ +# ==== Purpose ==== +# +# Test verifies that there are no duplicate entries in binlog (i.e a safe +# statement which follows an unsafe statement gets logged in both row format +# and statement format resulting in duplicate entry) when binlog-format=MIXED +# and LOCK TABLES are enabled. +# +# ==== Implementation ==== +# +# Steps: +# 1 - Create three tables t1,t2 and t3 with AUTO_INCREMENT on. +# 2 - Create a trigger on table t3, so that trigger execution results in +# unsafe statement. Note query that modifies autoinc column in +# sub-statement can make the master and slave inconsistent. Hence they +# are logged in row format. +# 3 - Lock tables t1,t2 and t3. +# 4 - Execute an unsafe update which modifies tables t1 and t3. But since t2 +# table is also locked its table map event also gets written into the +# binary log during the execution of update. +# 5 - Execute a safe DML operation using table 't2' and verify that master +# doesn't report any assert. +# 6 - Ensure that slave is in sync with master and data is consistent. +# +# ==== References ==== +# +# MDEV-19158: MariaDB 10.2.22 is writing duplicate entries into binary log + +--source include/have_binlog_format_mixed.inc +--source include/master-slave.inc + +CREATE TABLE t1 (id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, someLabel varchar(30) NOT NULL, flag tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id)) Engine=MyISAM; +CREATE TABLE t2 (id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, data varchar(30) NOT NULL, status tinyint(1) NOT NULL, PRIMARY KEY (id)) Engine=MyISAM; +CREATE TABLE t3 (id mediumint(8) unsigned NOT NULL AUTO_INCREMENT, t1id mediumint(8) unsigned NOT NULL, flag tinyint(1) NOT NULL DEFAULT 0, status tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id)) Engine=MyISAM; + +INSERT INTO t1 ( id, someLabel, flag ) VALUES ( 1, 'ABC', 0 ); + +DELIMITER |; + +CREATE OR REPLACE TRIGGER doNothing +BEFORE UPDATE ON t1 +FOR EACH ROW + BEGIN + IF + new.someLabel != old.someLabel + THEN + UPDATE t3 SET t3.flag = 0; + END IF; + END| + +DELIMITER ;| + +FLUSH LOGS; + +LOCK TABLES t1 WRITE, t2 WRITE; +INSERT INTO t2 (data, status) VALUES ('1', 4); +UPDATE t1 SET flag = 1 WHERE id = 1; +INSERT INTO t2 (data, status) VALUES ('2', 4); +UNLOCK TABLES; + +sync_slave_with_master; + +let $diff_tables= master:t1, slave:t1; +--source include/diff_tables.inc +let $diff_tables= master:t2, slave:t2; +--source include/diff_tables.inc +let $diff_tables= master:t3, slave:t3; +--source include/diff_tables.inc + +--connection master +DROP TABLE t1, t2, t3; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test index 678679f0cf1..a249043fa19 100644 --- a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test +++ b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test @@ -151,8 +151,7 @@ remove_file $MYSQLTEST_VARDIR/tmp/master.sql; --exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --stop-position=$stop_position --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 --echo --- Test 4 Second Remote test -- ---exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 > $MYSQLTEST_VARDIR/tmp/remote.sql ---exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 >> $MYSQLTEST_VARDIR/tmp/remote.sql +--exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --to-last-log master-bin.000001 > $MYSQLTEST_VARDIR/tmp/remote.sql # Now that we have our file, lets get rid of the current database. # Cleanup the master and the slave and try to recreate. diff --git a/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit,32bit.rdiff b/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit,32bit.rdiff new file mode 100644 index 00000000000..cd9a004a686 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit,32bit.rdiff @@ -0,0 +1,11 @@ +--- mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit.result 2019-05-07 15:09:57.220599318 +0530 ++++ mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit.reject 2019-05-07 15:10:20.012718538 +0530 +@@ -1,5 +1,7 @@ + set global innodb_ft_result_cache_limit=5000000000; ++Warnings: ++Warning 1292 Truncated incorrect innodb_ft_result_cache_limit value: '5000000000' + select @@innodb_ft_result_cache_limit; + @@innodb_ft_result_cache_limit +-5000000000 ++4294967295 + set global innodb_ft_result_cache_limit=2000000000; diff --git a/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit_64.result b/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit.result index c86331a8a1c..c86331a8a1c 100644 --- a/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit_64.result +++ b/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit.result diff --git a/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit_32.result b/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit_32.result deleted file mode 100644 index b3bec1eecdd..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_ft_result_cache_limit_32.result +++ /dev/null @@ -1,7 +0,0 @@ -set global innodb_ft_result_cache_limit=5000000000; -Warnings: -Warning 1292 Truncated incorrect innodb_ft_result_cache_limit value: '5000000000' -select @@innodb_ft_result_cache_limit; -@@innodb_ft_result_cache_limit -4294967295 -set global innodb_ft_result_cache_limit=2000000000; diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit,xtradb.rdiff-disabled b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit,xtradb.rdiff-disabled index 2019c4dc616..794e93a108b 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit,xtradb.rdiff-disabled +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit,xtradb.rdiff-disabled @@ -1214,8 +1214,8 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_VERSION SESSION_VALUE NULL --GLOBAL_VALUE 5.6.43 -+GLOBAL_VALUE 5.6.42-84.2 +-GLOBAL_VALUE 5.6.44 ++GLOBAL_VALUE 5.6.43-84.3 GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE NULL VARIABLE_SCOPE GLOBAL diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff-disabled b/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff-disabled index 998d95e523f..195eda13c52 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff-disabled +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,xtradb.rdiff-disabled @@ -684,8 +684,8 @@ COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_VERSION SESSION_VALUE NULL --GLOBAL_VALUE 5.6.43 -+GLOBAL_VALUE 5.6.42-84.2 +-GLOBAL_VALUE 5.6.44 ++GLOBAL_VALUE 5.6.43-84.3 GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE NULL VARIABLE_SCOPE GLOBAL diff --git a/mysql-test/suite/sys_vars/t/innodb_ft_result_cache_limit_32.test b/mysql-test/suite/sys_vars/t/innodb_ft_result_cache_limit.test index d9defc7447b..b1ad2cd3821 100644 --- a/mysql-test/suite/sys_vars/t/innodb_ft_result_cache_limit_32.test +++ b/mysql-test/suite/sys_vars/t/innodb_ft_result_cache_limit.test @@ -1,4 +1,4 @@ ---source include/have_32bit.inc +--source include/word_size.inc --source include/have_innodb.inc let $innodb_ft_result_cache_limit_orig=`select @@innodb_ft_result_cache_limit`; diff --git a/mysql-test/suite/sys_vars/t/innodb_ft_result_cache_limit_64.test b/mysql-test/suite/sys_vars/t/innodb_ft_result_cache_limit_64.test deleted file mode 100644 index 2606d2b5ca8..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_ft_result_cache_limit_64.test +++ /dev/null @@ -1,9 +0,0 @@ ---source include/have_64bit.inc ---source include/have_innodb.inc - -let $innodb_ft_result_cache_limit_orig=`select @@innodb_ft_result_cache_limit`; - -set global innodb_ft_result_cache_limit=5000000000; -select @@innodb_ft_result_cache_limit; - -eval set global innodb_ft_result_cache_limit=$innodb_ft_result_cache_limit_orig; diff --git a/mysql-test/suite/sys_vars/t/max_digest_length_basic.test b/mysql-test/suite/sys_vars/t/max_digest_length_basic.test index 38b493b3bab..92a7417568e 100644 --- a/mysql-test/suite/sys_vars/t/max_digest_length_basic.test +++ b/mysql-test/suite/sys_vars/t/max_digest_length_basic.test @@ -11,7 +11,7 @@ # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111-1301 USA +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA --source include/not_embedded.inc diff --git a/mysql-test/suite/sys_vars/t/pfs_digests_size_basic.test b/mysql-test/suite/sys_vars/t/pfs_digests_size_basic.test index 31692081053..6b48ba95102 100644 --- a/mysql-test/suite/sys_vars/t/pfs_digests_size_basic.test +++ b/mysql-test/suite/sys_vars/t/pfs_digests_size_basic.test @@ -11,7 +11,7 @@ # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111-1301 USA +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA --source include/not_embedded.inc --source include/have_perfschema.inc diff --git a/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test b/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test index cf87c1a7c68..9a89f9aff9e 100644 --- a/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test +++ b/mysql-test/suite/sys_vars/t/pfs_max_digest_length_basic.test @@ -11,7 +11,7 @@ # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111-1301 USA +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA --source include/not_embedded.inc --source include/have_perfschema.inc diff --git a/mysql-test/suite/sys_vars/t/pfs_session_connect_attrs_size_basic.test b/mysql-test/suite/sys_vars/t/pfs_session_connect_attrs_size_basic.test index c10700b8903..378646b0fc9 100644 --- a/mysql-test/suite/sys_vars/t/pfs_session_connect_attrs_size_basic.test +++ b/mysql-test/suite/sys_vars/t/pfs_session_connect_attrs_size_basic.test @@ -11,7 +11,7 @@ # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA --source include/not_embedded.inc --source include/have_perfschema.inc diff --git a/mysql-test/suite/sys_vars/t/transaction_prealloc_size_bug27322.test b/mysql-test/suite/sys_vars/t/transaction_prealloc_size_bug27322.test index c83f3dc2507..6e3c330ce06 100644 --- a/mysql-test/suite/sys_vars/t/transaction_prealloc_size_bug27322.test +++ b/mysql-test/suite/sys_vars/t/transaction_prealloc_size_bug27322.test @@ -44,22 +44,23 @@ SET @def_var= @@session.transaction_prealloc_size; SET SESSION transaction_prealloc_size=1024*1024*1024*1; # Embedded server is shows "cleaning up" as STATE, while non-embedded shows "init" --replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> ---replace_regex /localhost[:0-9]*/localhost/ +--replace_result Execute Query SHOW PROCESSLIST; SET SESSION transaction_prealloc_size=1024*1024*1024*2; --replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> ---replace_regex /localhost[:0-9]*/localhost/ +--replace_result Execute Query SHOW PROCESSLIST; SET SESSION transaction_prealloc_size=1024*1024*1024*3; --replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> ---replace_regex /localhost[:0-9]*/localhost/ +--replace_result Execute Query SHOW PROCESSLIST; SET SESSION transaction_prealloc_size=1024*1024*1024*4; --replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> ---replace_regex /localhost[:0-9]*/localhost/ +--replace_result Execute Query SHOW PROCESSLIST; SET SESSION transaction_prealloc_size=1024*1024*1024*5; --replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> +--replace_result Execute Query SHOW PROCESSLIST; --enable_warnings diff --git a/mysql-test/suite/versioning/common.inc b/mysql-test/suite/versioning/common.inc index 137480e1f62..bf31cd38771 100644 --- a/mysql-test/suite/versioning/common.inc +++ b/mysql-test/suite/versioning/common.inc @@ -46,6 +46,10 @@ if ($MTR_COMBINATION_MYISAM) { --let $MTR_COMBINATION_TIMESTAMP= 1 } +if ($MTR_COMBINATION_HEAP) +{ + --let $MTR_COMBINATION_TIMESTAMP= 1 +} if ($MTR_COMBINATION_TRX_ID) { let $sys_datatype_expl= bigint(20) unsigned; diff --git a/mysql-test/suite/versioning/engines.combinations b/mysql-test/suite/versioning/engines.combinations index 561c5656929..26b5bab23f1 100644 --- a/mysql-test/suite/versioning/engines.combinations +++ b/mysql-test/suite/versioning/engines.combinations @@ -6,3 +6,6 @@ default-storage-engine=innodb [myisam] default-storage-engine=myisam + +[heap] +default-storage-engine=memory diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index 7fa38bc893e..895b2c3b66a 100644 --- a/mysql-test/suite/versioning/r/create.result +++ b/mysql-test/suite/versioning/r/create.result @@ -13,14 +13,13 @@ t1 CREATE TABLE `t1` ( `Sys_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END INVISIBLE COMMENT 'end', PERIOD FOR SYSTEM_TIME (`Sys_start`, `Sys_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING -select table_catalog,table_schema,table_name,table_type,version,table_rows,avg_row_length,data_free,auto_increment,check_time,table_collation,checksum,create_options,table_comment from information_schema.tables where table_name='t1'; +select table_catalog,table_schema,table_name,table_type,version,table_rows,data_free,auto_increment,check_time,table_collation,checksum,create_options,table_comment from information_schema.tables where table_name='t1'; table_catalog def table_schema test table_name t1 table_type SYSTEM VERSIONED version 10 table_rows 0 -avg_row_length 0 data_free 0 auto_increment NULL check_time NULL @@ -157,7 +156,7 @@ Sys_start bigint as row start invisible, Sys_end bigint unsigned as row end invisible, period for system_time (Sys_start, Sys_end) ) with system versioning engine innodb; -ERROR HY000: `Sys_start` must be of type TIMESTAMP(6) for system-versioned table `t1` +ERROR HY000: `Sys_start` must be of type BIGINT(20) UNSIGNED for system-versioned table `t1` create or replace table t1 ( x14 int unsigned, Sys_start bigint unsigned as row start invisible, @@ -507,5 +506,19 @@ Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +create or replace table t1 ( +a int, +row_start bigint as row start, +row_end bigint as row end, +period for system_time (row_start, row_end) +) engine=innodb with system versioning; +ERROR HY000: `row_start` must be of type BIGINT(20) UNSIGNED for system-versioned table `t1` +create or replace table t1 ( +a int, +row_start bigint as row start, +row_end bigint as row end, +period for system_time (row_start, row_end) +) engine=myisam with system versioning; +ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1` drop database test; create database test; diff --git a/mysql-test/suite/versioning/r/rpl.result b/mysql-test/suite/versioning/r/rpl.result index fd62a65f473..627f3991499 100644 --- a/mysql-test/suite/versioning/r/rpl.result +++ b/mysql-test/suite/versioning/r/rpl.result @@ -128,7 +128,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL -) ENGINE=INNODB_OR_MYISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +) ENGINE=ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING ## Case 2: ERROR on the master, it'll fail on the master, the slave won't see it connection master; set system_versioning_alter_history= ERROR; @@ -140,7 +140,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL -) ENGINE=INNODB_OR_MYISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +) ENGINE=ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING ## Case 3: table is not versioned on the master, ALTER will work on the slave connection master; create or replace table t1 (a int); @@ -154,7 +154,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL -) ENGINE=INNODB_OR_MYISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +) ENGINE=ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING connection master; drop table t1, t2; create table t1 (i int) with system versioning partition by system_time limit 8 ( partition p1 history, partition p2 history, partition pn current ); diff --git a/mysql-test/suite/versioning/r/update-big.result b/mysql-test/suite/versioning/r/update-big.result new file mode 100644 index 00000000000..89297fe5d89 --- /dev/null +++ b/mysql-test/suite/versioning/r/update-big.result @@ -0,0 +1,25 @@ +# +# MDEV-15458 Segfault in heap_scan() upon UPDATE after ADD SYSTEM VERSIONING +# +create or replace table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +connect con1,localhost,root,,test; +alter table t1 add system versioning; +connection default; +update t1 set a= 7 where a = 3; +update t1 set a= 2 where a = 7; +update t1 set a= 5 where a = 2; +update t1 set a= 1 where a = 5; +update t1 set a= 8 where a = 1; +update t1 set a= 4 where a = 8; +update t1 set a= 6; +drop table t1; diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test index 8103330c722..733b5057c20 100644 --- a/mysql-test/suite/versioning/t/create.test +++ b/mysql-test/suite/versioning/t/create.test @@ -15,7 +15,7 @@ eval create table t1 ( --replace_result $default_engine DEFAULT_ENGINE $sys_datatype_expl SYS_DATATYPE show create table t1; ---query_vertical select table_catalog,table_schema,table_name,table_type,version,table_rows,avg_row_length,data_free,auto_increment,check_time,table_collation,checksum,create_options,table_comment from information_schema.tables where table_name='t1' +--query_vertical select table_catalog,table_schema,table_name,table_type,version,table_rows,data_free,auto_increment,check_time,table_collation,checksum,create_options,table_comment from information_schema.tables where table_name='t1' --query_vertical select table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,character_maximum_length,character_octet_length,character_set_name,collation_name,column_key,extra,column_comment,is_generated,generation_expression from information_schema.columns where table_name='t1' --echo # Implicit fields test @@ -371,5 +371,21 @@ create or replace table t1 (i int) with system versioning as select 1 as i; --replace_result $default_engine DEFAULT_ENGINE show create table t1; +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t1 ( + a int, + row_start bigint as row start, + row_end bigint as row end, + period for system_time (row_start, row_end) +) engine=innodb with system versioning; + +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t1 ( + a int, + row_start bigint as row start, + row_end bigint as row end, + period for system_time (row_start, row_end) +) engine=myisam with system versioning; + drop database test; create database test; diff --git a/mysql-test/suite/versioning/t/rpl.test b/mysql-test/suite/versioning/t/rpl.test index e59d41c38a3..b5be68feece 100644 --- a/mysql-test/suite/versioning/t/rpl.test +++ b/mysql-test/suite/versioning/t/rpl.test @@ -96,7 +96,7 @@ create or replace table t1 (a int) with system versioning; set system_versioning_alter_history= KEEP; alter table t1 add column b int; sync_slave_with_master; ---replace_result InnoDB INNODB_OR_MYISAM MyISAM INNODB_OR_MYISAM +--replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE show create table t1; --echo ## Case 2: ERROR on the master, it'll fail on the master, the slave won't see it @@ -105,7 +105,7 @@ set system_versioning_alter_history= ERROR; --error ER_VERS_ALTER_NOT_ALLOWED alter table t1 drop column b; sync_slave_with_master; ---replace_result InnoDB INNODB_OR_MYISAM MyISAM INNODB_OR_MYISAM +--replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE show create table t1; --echo ## Case 3: table is not versioned on the master, ALTER will work on the slave @@ -116,7 +116,7 @@ create or replace table t1 (a int) with system versioning; connection master; alter table t1 add column b int; sync_slave_with_master; ---replace_result InnoDB INNODB_OR_MYISAM MyISAM INNODB_OR_MYISAM +--replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE show create table t1; connection master; diff --git a/mysql-test/suite/versioning/t/update-big.test b/mysql-test/suite/versioning/t/update-big.test new file mode 100644 index 00000000000..175bfc79a48 --- /dev/null +++ b/mysql-test/suite/versioning/t/update-big.test @@ -0,0 +1,34 @@ +source include/big_test.inc; +source suite/versioning/engines.inc; +source suite/versioning/common.inc; + +--echo # +--echo # MDEV-15458 Segfault in heap_scan() upon UPDATE after ADD SYSTEM VERSIONING +--echo # +create or replace table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; + +--connect (con1,localhost,root,,test) +alter table t1 add system versioning; + +--connection default +update t1 set a= 7 where a = 3; +update t1 set a= 2 where a = 7; +update t1 set a= 5 where a = 2; +update t1 set a= 1 where a = 5; +update t1 set a= 8 where a = 1; +update t1 set a= 4 where a = 8; +update t1 set a= 6; + +drop table t1; + +source suite/versioning/common_finish.inc; |