diff options
Diffstat (limited to 'mysql-test')
105 files changed, 6211 insertions, 139 deletions
diff --git a/mysql-test/include/have_debug.inc b/mysql-test/include/have_debug.inc index 5df3080a6ed..a035031e49a 100644 --- a/mysql-test/include/have_debug.inc +++ b/mysql-test/include/have_debug.inc @@ -2,8 +2,3 @@ # suite.pm will make sure that all tests including this file # will be skipped unless this is a debug build. # -# The test below is redundant - -if (`select version() not like '%debug%'`) { - --skip Needs a debug build -} diff --git a/mysql-test/include/have_example_plugin.inc b/mysql-test/include/have_example_plugin.inc index 5571c345850..c0da490dde0 100644 --- a/mysql-test/include/have_example_plugin.inc +++ b/mysql-test/include/have_example_plugin.inc @@ -1,14 +1,4 @@ # -# Check if server has support for loading plugins +# suite.pm will make sure that all tests including this file +# will be skipped unless dynamic ha_example plugin is available # -if (`SELECT @@have_dynamic_loading != 'YES'`) { - --skip Example plugin requires dynamic loading -} - -# -# Check if the variable EXAMPLE_PLUGIN is set -# -if (!$HA_EXAMPLE_SO) { - --skip Need example plugin -} - diff --git a/mysql-test/include/have_innodb.inc b/mysql-test/include/have_innodb.inc index 5447d935f3c..69ffdb5b284 100644 --- a/mysql-test/include/have_innodb.inc +++ b/mysql-test/include/have_innodb.inc @@ -2,9 +2,3 @@ # suite.pm will make sure that all tests including this file # will be skipped unless innodb is enabled # -# The test below is redundant - -if (`SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'innodb' AND support IN ('YES', 'DEFAULT', 'ENABLED')`) -{ - --skip Test requires InnoDB. -} diff --git a/mysql-test/include/not_embedded.inc b/mysql-test/include/not_embedded.inc index 88185af3b15..4c168f71979 100644 --- a/mysql-test/include/not_embedded.inc +++ b/mysql-test/include/not_embedded.inc @@ -2,9 +2,3 @@ # suite.pm will make sure that all tests including this file # will be skipped unless this is an embedded test run # -# The test below is redundant - -if (`select version() like '%embedded%'`) { - This should never happen; -} - diff --git a/mysql-test/include/not_windows.inc b/mysql-test/include/not_windows.inc index 9240271077a..08373095438 100644 --- a/mysql-test/include/not_windows.inc +++ b/mysql-test/include/not_windows.inc @@ -1,4 +1,4 @@ ---require r/not_windows.require -disable_query_log; -select convert(@@version_compile_os using latin1) NOT IN ("Win32","Win64","Windows") as "TRUE"; -enable_query_log; +# +# suite.pm will make sure that all tests including this file +# will be skipped unless this is on Windows +# diff --git a/mysql-test/include/show_gtid_list.inc b/mysql-test/include/show_gtid_list.inc new file mode 100644 index 00000000000..96f813f180c --- /dev/null +++ b/mysql-test/include/show_gtid_list.inc @@ -0,0 +1,15 @@ +# ==== Purpose ==== +# +# Extract Gtid_list info from SHOW BINLOG EVENTS output masking +# non-deterministic fields. +# +# ==== Usage ==== +# +# [--let $binlog_file=filename +# +if ($binlog_file) +{ + --let $_in_binlog_file=in '$binlog_file' +} +--replace_column 2 # 5 # +--eval show binlog events $_in_binlog_file limit 1,1 diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 9f83d6c9d6e..7902c8770a8 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -2791,7 +2791,7 @@ sub mysql_server_start($) { # Some InnoDB options are incompatible with the default bootstrap. # If they are used, re-bootstrap if ( $extra_opts and - "@$extra_opts" =~ /--innodb[-_](?:page[-_]size|checksum[-_]algorithm|undo[-_]tablespaces|log[-_]group[-_]home[-_]dir|data[-_]home[-_]dir)/ ) + "@$extra_opts" =~ /--innodb[-_](?:page[-_]size|checksum[-_]algorithm|undo[-_]tablespaces|log[-_]group[-_]home[-_]dir|data[-_]home[-_]dir)|data[-_]file[-_]path/ ) { mysql_install_db($mysqld, undef, $extra_opts); } diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 3c9cbb18bcb..ca314573581 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -1168,3 +1168,112 @@ CAST('-1' AS UNSIGNED) 18446744073709551615 DROP TABLE t1; SET sql_mode=DEFAULT; +# +# MDEV-14376 Explicit CAST(CHAR(N)) erroneously escalates warnings to errors in STRICT_ALL_TABLES +# +SET sql_mode=STRICT_ALL_TABLES; +SELECT CAST('xxx' AS CHAR(1)); +CAST('xxx' AS CHAR(1)) +x +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +CREATE OR REPLACE TABLE t1 (a VARCHAR(1)); +INSERT INTO t1 VALUES (CAST('xxx' AS CHAR(1))); +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a VARCHAR(3)); +INSERT INTO t1 VALUES ('xxx'); +UPDATE t1 SET a=CAST(a AS CHAR(1)); +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +DROP TABLE t1; +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET latin1; +SET a=CAST('xxx' AS CHAR(1)); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET latin1; +SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET latin1; +SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST('xxx' AS CHAR(1)); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8); +END; +$$ +Warnings: +Warning 1292 Truncated incorrect CHAR(1) value: 'xxx' +# Conversion problems still escalate warnings to errors (without right truncation) +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST(_utf8 0xD18F AS CHAR(1) CHARACTER SET latin1); +END; +$$ +ERROR HY000: Cannot convert 'utf8' character 0xD18F to 'latin1' +# Conversion problems still escalate warnings to errors (with right truncation) +BEGIN NOT ATOMIC +DECLARE a VARCHAR(30) CHARACTER SET utf8; +SET a=CAST(_utf8 0xD18FD18F AS CHAR(1) CHARACTER SET latin1); +END; +$$ +ERROR HY000: Cannot convert 'utf8' character 0xD18F to 'latin1' +# CAST(number AS CHAR) escalates warnings to errors on truncation +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES (CAST(123 AS CHAR(1))); +ERROR 22007: Truncated incorrect CHAR(1) value: '123' +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('1'); +UPDATE t1 SET a=CAST(123 AS CHAR(1)); +ERROR 22007: Truncated incorrect CHAR(1) value: '123' +DROP TABLE t1; +BEGIN NOT ATOMIC +DECLARE a VARCHAR(10); +SET a=CAST(123 AS CHAR(1)); +END; +$$ +ERROR 22007: Truncated incorrect CHAR(1) value: '123' +# CAST(temporal AS CHAR) escalates warnings to errors on truncation +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES (CAST(TIME'10:20:30' AS CHAR(1))); +ERROR 22007: Truncated incorrect CHAR(1) value: '10:20:30' +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('1'); +UPDATE t1 SET a=CAST(TIME'10:20:30' AS CHAR(1)); +ERROR 22007: Truncated incorrect CHAR(1) value: '10:20:30' +DROP TABLE t1; +BEGIN NOT ATOMIC +DECLARE a VARCHAR(10); +SET a=CAST(TIME'10:20:30' AS CHAR(1)); +END; +$$ +ERROR 22007: Truncated incorrect CHAR(1) value: '10:20:30' +SET sql_mode=DEFAULT; diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index ade8a4f7549..e57d2ef1dcf 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -230,7 +230,7 @@ insert into t2 (a) values (1023); do (f2(23)); Warnings: Error 1062 Duplicate entry '23' for key 'a' -Note 4092 At line 4 in test.f2 +Note 4093 At line 4 in test.f2 select * from t2; a 1023 diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result index 79e0bdf5e20..c880df7b39f 100644 --- a/mysql-test/r/create_drop_binlog.result +++ b/mysql-test/r/create_drop_binlog.result @@ -160,7 +160,7 @@ Note 1050 Table 'v1' already exists DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' SHOW BINLOG EVENTS; Log_name Pos Event_type Server_id End_log_pos Info # # Format_desc 1 # VER diff --git a/mysql-test/r/create_drop_view.result b/mysql-test/r/create_drop_view.result index d23b9b713ad..8dc10297bdb 100644 --- a/mysql-test/r/create_drop_view.result +++ b/mysql-test/r/create_drop_view.result @@ -55,5 +55,5 @@ id DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' DROP TABLE t1; diff --git a/mysql-test/r/cte_grant.result b/mysql-test/r/cte_grant.result index 1282a98fdef..2ee31be3435 100644 --- a/mysql-test/r/cte_grant.result +++ b/mysql-test/r/cte_grant.result @@ -63,3 +63,61 @@ connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; drop user mysqltest_1@localhost; drop database mysqltest; +# +# MDEV-13453: privileges checking for CTE +# +create database db; +use db; +create table t1 (i int); +insert into t1 +values (3), (7), (1), (4), (2), (3), (1); +create table t2 (a int, b int); +insert into t2 +values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15); +create user foo@localhost; +grant SELECT on db.t1 to foo@localhost; +grant SELECT(a) on db.t2 to foo@localhost; +connect con1,localhost,foo,,; +use db; +with cte as (select * from t1 where i < 4) +select * from cte; +i +3 +1 +2 +3 +1 +with cte as (select * from t1 where i < 4 group by i) +select * from cte; +i +1 +2 +3 +with cte as (select * from t1 where i < 4) +select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; +i +1 +3 +with cte as (select * from t1 where i < 4 group by i) +select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; +i +1 +3 +with cte as (select b from t2 where a < 4) +select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'b' in table 't2' +with cte as (select a from t2 where a < 4) +select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2; +a +1 +3 +connection default; +revoke SELECT on db.t1 from foo@localhost; +connection con1; +with cte as (select * from t1 where i < 4) +select * from cte; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' +disconnect con1; +connection default; +drop database db; +drop user foo@localhost; diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result index 3fd5370f470..37005fb017e 100644 --- a/mysql-test/r/drop.result +++ b/mysql-test/r/drop.result @@ -209,10 +209,10 @@ Note 1051 Unknown table 'test.table1' Note 1051 Unknown table 'test.table2' DROP VIEW IF EXISTS view1,view2,view3,view4; Warnings: -Note 4090 Unknown VIEW: 'test.view1' -Note 4090 Unknown VIEW: 'test.view2' -Note 4090 Unknown VIEW: 'test.view3' -Note 4090 Unknown VIEW: 'test.view4' +Note 4091 Unknown VIEW: 'test.view1' +Note 4091 Unknown VIEW: 'test.view2' +Note 4091 Unknown VIEW: 'test.view3' +Note 4091 Unknown VIEW: 'test.view4' # Test error message when trigger does not find table CREATE TABLE table1(a int); diff --git a/mysql-test/r/func_hybrid_type.result b/mysql-test/r/func_hybrid_type.result index fe45338b36f..1bf8231f4dd 100644 --- a/mysql-test/r/func_hybrid_type.result +++ b/mysql-test/r/func_hybrid_type.result @@ -3443,8 +3443,8 @@ EXECUTE stmt USING @a,@a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` varchar(21) DEFAULT NULL, - `b` varchar(21) DEFAULT NULL + `a` varchar(20) DEFAULT NULL, + `b` varchar(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index 15e4fbec605..25d47574b59 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -28,6 +28,9 @@ NULL select json_value('{"key1": [1,2,3], "key1":123}', '$.key1'); json_value('{"key1": [1,2,3], "key1":123}', '$.key1') 123 +select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z'); +JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z') +Mon"t"y select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2'); json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2') NULL @@ -725,6 +728,9 @@ json_contains_path('{"foo":"bar"}', 'one', '$[]') NULL Warnings: Warning 4042 Syntax error in JSON path in argument 3 to function 'json_contains_path' at position 3 +select JSON_VALID(0x36f0c8dccd83c5eac156da); +JSON_VALID(0x36f0c8dccd83c5eac156da) +0 # # Start of 10.3 tests # diff --git a/mysql-test/r/get_diagnostics.result b/mysql-test/r/get_diagnostics.result index a75b775297c..63ed6867096 100644 --- a/mysql-test/r/get_diagnostics.result +++ b/mysql-test/r/get_diagnostics.result @@ -590,7 +590,7 @@ DROP PROCEDURE p1; SHOW WARNINGS; Level Code Message Error 54321 MESSAGE_TEXT text -Note 4092 At line 16 in test.p1 +Note 4093 At line 16 in test.p1 CREATE PROCEDURE p1() BEGIN DECLARE var INT; diff --git a/mysql-test/r/gis-json.result b/mysql-test/r/gis-json.result index d888b08351d..1d6e2193fc9 100644 --- a/mysql-test/r/gis-json.result +++ b/mysql-test/r/gis-json.result @@ -89,6 +89,21 @@ ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 1) SELECT ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 5); ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 5) {"bbox": [10, 11, 10, 11], "type": "Point", "coordinates": [10, 11]} +SELECT st_astext(st_geomfromgeojson('{"type": "MultiLineString","coordinates": []}')) as a; +a +NULL +Warnings: +Warning 4076 Incorrect GeoJSON format - empty 'coordinates' array. +SELECT st_astext(st_geomfromgeojson('{"type": "Polygon","coordinates": []}')) as a; +a +NULL +Warnings: +Warning 4076 Incorrect GeoJSON format - empty 'coordinates' array. +SELECT st_astext(st_geomfromgeojson('{"type": "MultiPolygon","coordinates": []}')) as a; +a +NULL +Warnings: +Warning 4076 Incorrect GeoJSON format - empty 'coordinates' array. # # End of 10.2 tests # diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index fe67da8001f..3f2e6d39db8 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -4390,7 +4390,7 @@ SELECT ST_BUFFER(Point(1,1), Point(1,1)); ERROR HY000: Illegal parameter data type geometry for operation 'st_buffer' PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ST_ENVELOPE(?) AS g'; EXECUTE stmt USING 1; -ERROR HY000: Illegal parameter data type bigint for operation 'st_envelope' +ERROR HY000: Illegal parameter data type int for operation 'st_envelope' EXECUTE stmt USING POINT(1,1); SHOW CREATE TABLE t1; Table Create Table @@ -4404,7 +4404,7 @@ DROP TABLE t1; DEALLOCATE PREPARE stmt; PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ST_BUFFER(?,?) AS g'; EXECUTE stmt USING 1,1; -ERROR HY000: Illegal parameter data type bigint for operation 'st_buffer' +ERROR HY000: Illegal parameter data type int for operation 'st_buffer' EXECUTE stmt USING POINT(1,1),POINT(1,1); ERROR HY000: Illegal parameter data type geometry for operation 'st_buffer' EXECUTE stmt USING POINT(1,1),0; @@ -4455,7 +4455,7 @@ SELECT POINT(1,POINT(1,1)); ERROR HY000: Illegal parameter data type geometry for operation 'point' PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ST_GEOMFROMTEXT(?,?) AS g'; EXECUTE stmt USING 1,1; -ERROR HY000: Illegal parameter data type bigint for operation 'st_geometryfromtext' +ERROR HY000: Illegal parameter data type int for operation 'st_geometryfromtext' EXECUTE stmt USING POINT(1,1),POINT(1,1); ERROR HY000: Illegal parameter data type geometry for operation 'st_geometryfromtext' EXECUTE stmt USING 'POINT(1 1)',1; diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 5b239f09172..d2118fb56fe 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1428,7 +1428,7 @@ Warnings: Note 1305 FUNCTION test.test_function does not exist drop view if exists v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' create table test (col1 varchar(30)); create function test_function() returns varchar(30) begin diff --git a/mysql-test/r/intersect.result b/mysql-test/r/intersect.result index 7a0301a23e6..05adaf160ed 100644 --- a/mysql-test/r/intersect.result +++ b/mysql-test/r/intersect.result @@ -689,4 +689,17 @@ View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (select 4 AS `4`,4 AS `4`) latin1 latin1_swedish_ci drop view v1; drop tables t1,t2,t3; +# +# MDEV-14346:incorrect result of intersect with ANY/ALL/IN subquery +# +CREATE TABLE t (i INT); +INSERT INTO t VALUES (1),(2); +SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT SELECT 3 ); +i +select i from t where +exists ((select 6 as r from dual having t.i <> 6) +intersect +(select 3 from dual having t.i <> 3)); +i +drop table t; # End of 10.3 tests diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 5ee31bbe227..bac33753d4c 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -1258,3 +1258,4 @@ DELIMITER ; ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; +mysqlbinlog Ver VER for OS at ARCH diff --git a/mysql-test/r/not_windows.require b/mysql-test/r/not_windows.require deleted file mode 100644 index 09aae1ed1d0..00000000000 --- a/mysql-test/r/not_windows.require +++ /dev/null @@ -1,2 +0,0 @@ -TRUE -1 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index f43e6ce18af..5a9f2fae1e0 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -3159,3 +3159,51 @@ pk 2 3 DROP TABLE t1; +# +# MDEV-13994: Bad join results with orderby_uses_equalities=on +# +CREATE TABLE books ( +id int(16) NOT NULL AUTO_INCREMENT, +library_id int(16) NOT NULL DEFAULT 0, +wings_id int(12) NOT NULL DEFAULT 0, +scheduled_for_removal int(1) DEFAULT 0, +PRIMARY KEY (id), +KEY library_idx (library_id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT INTO books VALUES (32625,8663,707,0),(32624,8663,505,1); +CREATE TABLE wings ( +id int(11) NOT NULL AUTO_INCREMENT, +department_id int(11) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT INTO wings VALUES (505,11745),(707,11768); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='orderby_uses_equalities=off'; +SELECT wings.id as wing_id, wings.department_id FROM wings +WHERE wings.id IN ( SELECT books.wings_id FROM books +WHERE books.library_id = 8663 AND +books.scheduled_for_removal=0 ) +ORDER BY wings.id; +wing_id department_id +707 11768 +SET optimizer_switch='orderby_uses_equalities=on'; +SELECT wings.id as wing_id, wings.department_id FROM wings +WHERE wings.id IN ( SELECT books.wings_id FROM books +WHERE books.library_id = 8663 AND +books.scheduled_for_removal=0 ) +ORDER BY wings.id; +wing_id department_id +707 11768 +explain extended SELECT wings.id as wing_id, wings.department_id FROM wings +WHERE wings.id IN ( SELECT books.wings_id FROM books +WHERE books.library_id = 8663 AND +books.scheduled_for_removal=0 ) +ORDER BY wings.id; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 Using temporary; Using filesort +1 PRIMARY wings eq_ref PRIMARY PRIMARY 4 test.books.wings_id 1 100.00 +2 MATERIALIZED books ref library_idx library_idx 4 const 1 100.00 Using where +Warnings: +Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id` +set optimizer_switch= @save_optimizer_switch; +DROP TABLE books, wings; diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 4f59a2f8c20..3ff1f92e94a 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -48,3 +48,76 @@ where key1<3 or key2<3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using sort_union(key1,key2); Using where drop table t0, t1; +# +# MDEV-14071: wrong results with orderby_uses_equalities=on +# (duplicate of MDEV-13994) +# +CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB; +CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB; +CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(127,0,1),(188,0,1),(206,0,1),(218,0,1),(292,0,1),(338,0,1),(375,0,1), +(381,0,1),(409,0,1),(466,0,1),(469,0,1),(498,0,1),(656,0,1); +INSERT INTO t1 VALUES +(77,4,0),(86,7,0),(96,6,0),(96,7,0),(99,9,0),(99,10,0),(99,11,0),(104,4,0), +(106,5,0),(148,6,0),(177,6,0),(181,5,0),(188,8,0),(218,8,0),(253,7,0), +(268,4,0),(338,4,0),(409,7,0),(466,8,0),(469,8,0),(498,8,0),(656,8,0); +INSERT INTO t2 VALUES +(127,7),(188,8),(188,9),(206,6),(218,8),(218,9),(292,7),(338,4),(338,5), +(375,6),(381,5),(409,7),(409,8),(466,8),(466,9),(469,8),(469,9),(498,8), +(498,9),(656,8),(656,9); +INSERT INTO t3 VALUES +(4,'four'),(5,'five'),(6,'six'),(7,'seven'),(8,'eight'),(9,'nine'); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='orderby_uses_equalities=off'; +SELECT i,n +FROM t1 INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE i IN (SELECT i FROM t1 WHERE z=1) AND z=0 ORDER BY i; +i n +188 eight +218 eight +338 four +409 seven +466 eight +469 eight +498 eight +656 eight +SELECT i,n +FROM t1 x INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE EXISTS (SELECT * FROM t1 WHERE i=x.i AND z=1) AND z=0 ORDER BY i; +i n +188 eight +218 eight +338 four +409 seven +466 eight +469 eight +498 eight +656 eight +SET optimizer_switch='orderby_uses_equalities=on'; +SELECT i,n +FROM t1 INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE i IN (SELECT i FROM t1 WHERE z=1) AND z=0 ORDER BY i; +i n +188 eight +218 eight +338 four +409 seven +466 eight +469 eight +498 eight +656 eight +SELECT i,n +FROM t1 x INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE EXISTS (SELECT * FROM t1 WHERE i=x.i AND z=1) AND z=0 ORDER BY i; +i n +188 eight +218 eight +338 four +409 seven +466 eight +469 eight +498 eight +656 eight +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index 9644a8afe8d..bc8dd162481 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -415,7 +415,7 @@ select @@profiling; drop table if exists t1, t2, t3; drop view if exists v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' drop function if exists f1; set session profiling = OFF; set global profiling_history_size= @start_value; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index ba52176e3bd..e10a859cbf9 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -3388,7 +3388,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` bigint(20) DEFAULT NULL + `c1` bigint(20) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = b'10100100101'; @a @a = b'10100100101' @@ -3478,7 +3478,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` bigint(20) DEFAULT NULL + `c1` bigint(20) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = 2010; @a @a = 2010 @@ -3550,7 +3550,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` longblob DEFAULT NULL + `c1` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = REPEAT('a', 16); @a @a = REPEAT('a', 16) @@ -3568,7 +3568,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` longblob DEFAULT NULL + `c1` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = REPEAT('b', 16); @a @a = REPEAT('b', 16) @@ -3586,7 +3586,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` longblob DEFAULT NULL + `c1` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = REPEAT('c', 16); @a @a = REPEAT('c', 16) @@ -3604,7 +3604,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` longblob DEFAULT NULL + `c1` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = REPEAT('d', 16); @a @a = REPEAT('d', 16) @@ -3622,7 +3622,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` longblob DEFAULT NULL + `c1` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = REPEAT('e', 16); @a @a = REPEAT('e', 16) @@ -3640,7 +3640,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` longblob DEFAULT NULL + `c1` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = REPEAT('f', 16); @a @a = REPEAT('f', 16) @@ -3766,7 +3766,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` longblob DEFAULT NULL + `c1` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = 'aaa'; @a @a = 'aaa' @@ -3784,7 +3784,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1; SHOW CREATE TABLE tmp1; Table Create Table tmp1 CREATE TEMPORARY TABLE `tmp1` ( - `c1` longblob DEFAULT NULL + `c1` longtext DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT @a, @a = 'aaa'; @a @a = 'aaa' @@ -4407,7 +4407,7 @@ EXECUTE stmt USING 10; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` bigint(21) NOT NULL + `c1` int(2) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; EXECUTE stmt USING 10.123; @@ -4614,10 +4614,10 @@ EXECUTE IMMEDIATE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(21) NOT NULL, + `a` bigint(20) NOT NULL, `b` decimal(3,1) DEFAULT NULL, `c` double NOT NULL, - `d` varchar(3) NOT NULL + `d` tinytext NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; EXECUTE IMMEDIATE @@ -4626,7 +4626,7 @@ EXECUTE IMMEDIATE SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(21) NOT NULL, + `a` int(2) NOT NULL, `b` decimal(3,1) DEFAULT NULL, `c` double NOT NULL, `d` varchar(3) NOT NULL @@ -4992,3 +4992,159 @@ ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NE EXECUTE IMMEDIATE 'CALL p1(?)' USING IGNORE; ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger DROP PROCEDURE p1; +# +# MDEV-14434 Wrong result for CHARSET(CONCAT(?,const)) +# +SET NAMES utf8; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(5,_latin1'a'))"; +CHARSET(CONCAT(5,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5e0; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIME'10:20:30'; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIMESTAMP'2001-01-01 10:20:30'; +CHARSET(CONCAT(?,_latin1'a')) +latin1 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5; +COERCIBILITY(?) +5 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5; +COERCIBILITY(?) +5 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5e0; +COERCIBILITY(?) +5 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIME'10:20:30'; +COERCIBILITY(?) +5 +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIMESTAMP'2001-01-01 10:20:30'; +COERCIBILITY(?) +5 +# +# MDEV-14435 Different UNSIGNED flag of out user variable for YEAR parameter for direct vs prepared CALL +# +CREATE PROCEDURE p1(OUT v INT UNSIGNED) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) unsigned DEFAULT NULL, + `b` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT v YEAR) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) unsigned DEFAULT NULL, + `b` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +CREATE PROCEDURE p1(OUT v BIT(16)) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(20) unsigned DEFAULT NULL, + `b` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# MDEV-14454 Binary protocol returns wrong collation ID for SP OUT parameters +# +CREATE PROCEDURE p1(OUT v CHAR(32) CHARACTER SET utf8) SET v='aaa'; +PREPARE stmt1 FROM 'CALL p1(?)'; +EXECUTE stmt1 USING @a; +CREATE TABLE t1 AS SELECT @a AS c1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` longtext CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +# +# MDEV-14467 Item_param: replace {INT|DECIMAL|REAL|STRING|TIME}_VALUE with Type_handler +# +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10; +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 select 1 AS `1` limit 10 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.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 select 1 AS `1` limit 10 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.1e0; +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 select 1 AS `1` limit 10 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING '10'; +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 select 1 AS `1` limit 10 +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING TIME'10:10:10'; +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 select 1 AS `1` limit 101010 +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a,? AS b' USING 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(1) NOT NULL, + `b` int(1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 10 AS a,? AS b' USING 10; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(2) NOT NULL, + `b` int(2) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 999999999 AS a,? AS b' USING 999999999; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(9) NOT NULL, + `b` int(9) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 2147483647 AS a,? AS b' USING 2147483647; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` bigint(10) NOT NULL, + `b` bigint(10) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/r/signal.result b/mysql-test/r/signal.result index 671df4b7f17..09493f0da30 100644 --- a/mysql-test/r/signal.result +++ b/mysql-test/r/signal.result @@ -1715,7 +1715,7 @@ show warnings $$ Level Code Message Warning 1012 Raising a warning Error 5555 RESIGNAL to not found -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1740,7 +1740,7 @@ show warnings $$ Level Code Message Warning 1012 Raising a warning Error 5555 RESIGNAL to error -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1789,7 +1789,7 @@ show warnings $$ Level Code Message Error 1012 Raising a not found Error 5555 RESIGNAL to not found -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1814,7 +1814,7 @@ show warnings $$ Level Code Message Error 1012 Raising a not found Error 5555 RESIGNAL to error -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1863,7 +1863,7 @@ show warnings $$ Level Code Message Error 1012 Raising an error Error 5555 RESIGNAL to not found -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1888,7 +1888,7 @@ show warnings $$ Level Code Message Error 1012 Raising an error Error 5555 RESIGNAL to error -Note 4092 At line 9 in test.test_resignal +Note 4093 At line 9 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1931,7 +1931,7 @@ show warnings $$ Level Code Message Warning 1264 Out of range value for column 'a' at row 1 Error 5555 RESIGNAL to a not found -Note 4092 At line 8 in test.test_resignal +Note 4093 At line 8 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1953,7 +1953,7 @@ show warnings $$ Level Code Message Warning 1264 Out of range value for column 'a' at row 1 Error 5555 RESIGNAL to an error -Note 4092 At line 8 in test.test_resignal +Note 4093 At line 8 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2004,7 +2004,7 @@ show warnings $$ Level Code Message Error 1329 No data - zero rows fetched, selected, or processed Error 5555 RESIGNAL to a not found -Note 4092 At line 10 in test.test_resignal +Note 4093 At line 10 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2030,7 +2030,7 @@ show warnings $$ Level Code Message Error 1329 No data - zero rows fetched, selected, or processed Error 5555 RESIGNAL to an error -Note 4092 At line 10 in test.test_resignal +Note 4093 At line 10 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2073,7 +2073,7 @@ show warnings $$ Level Code Message Error 1051 Unknown table 'test.no_such_table' Error 5555 RESIGNAL to a not found -Note 4092 At line 8 in test.test_resignal +Note 4093 At line 8 in test.test_resignal drop procedure test_resignal $$ create procedure test_resignal() begin @@ -2095,7 +2095,7 @@ show warnings $$ Level Code Message Error 1051 Unknown table 'test.no_such_table' Error 5555 RESIGNAL to an error -Note 4092 At line 8 in test.test_resignal +Note 4093 At line 8 in test.test_resignal drop procedure test_resignal $$ # # More complex cases @@ -2142,7 +2142,7 @@ ERROR 42000: Hi, I am a useless error message show warnings $$ Level Code Message Error 9999 Hi, I am a useless error message -Note 4092 At line 7 in test.peter_p2 +Note 4093 At line 7 in test.peter_p2 drop procedure peter_p1 $$ drop procedure peter_p2 $$ CREATE PROCEDURE peter_p1 () @@ -2198,16 +2198,16 @@ Level Code Message Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' Error 1232 Variable 'sql_mode' can't be set to the value of 'NULL' Error 9999 Variable 'sql_mode' can't be set to the value of 'NULL' -Note 4092 At line 8 in test.peter_p1 +Note 4093 At line 8 in test.peter_p1 ERROR 42000: Hi, I am a useless error message show warnings $$ Level Code Message Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' Error 1232 Variable 'sql_mode' can't be set to the value of 'NULL' Error 9999 Variable 'sql_mode' can't be set to the value of 'NULL' -Note 4092 At line 8 in test.peter_p1 +Note 4093 At line 8 in test.peter_p1 Error 9999 Hi, I am a useless error message -Note 4092 At line 10 in test.peter_p2 +Note 4093 At line 10 in test.peter_p2 drop procedure peter_p1 $$ drop procedure peter_p2 $$ drop procedure if exists peter_p3 $$ @@ -2225,7 +2225,7 @@ show warnings $$ Level Code Message Error 1 Original Error 2 Original -Note 4092 At line 4 in test.peter_p3 +Note 4093 At line 4 in test.peter_p3 drop procedure peter_p3 $$ drop table t_warn; drop table t_cursor; diff --git a/mysql-test/r/signal_demo3.result b/mysql-test/r/signal_demo3.result index a98d587937c..1d597aaf71c 100644 --- a/mysql-test/r/signal_demo3.result +++ b/mysql-test/r/signal_demo3.result @@ -79,23 +79,23 @@ show warnings; Level Code Message Error 1051 Unknown table 'demo.oops_it_is_not_here' Error 1644 Oops in proc_9 -Note 4092 At line 4 in demo.proc_9 +Note 4093 At line 4 in demo.proc_9 Error 1644 Oops in proc_8 -Note 4092 At line 4 in demo.proc_8 +Note 4093 At line 4 in demo.proc_8 Error 1644 Oops in proc_7 -Note 4092 At line 4 in demo.proc_7 +Note 4093 At line 4 in demo.proc_7 Error 1644 Oops in proc_6 -Note 4092 At line 4 in demo.proc_6 +Note 4093 At line 4 in demo.proc_6 Error 1644 Oops in proc_5 -Note 4092 At line 4 in demo.proc_5 +Note 4093 At line 4 in demo.proc_5 Error 1644 Oops in proc_4 -Note 4092 At line 4 in demo.proc_4 +Note 4093 At line 4 in demo.proc_4 Error 1644 Oops in proc_3 -Note 4092 At line 4 in demo.proc_3 +Note 4093 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4092 At line 4 in demo.proc_2 +Note 4093 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4092 At line 4 in demo.proc_1 +Note 4093 At line 4 in demo.proc_1 SET @@session.max_error_count = 5; SELECT @@session.max_error_count; @@session.max_error_count @@ -104,11 +104,11 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Note 4092 At line 4 in demo.proc_3 +Note 4093 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4092 At line 4 in demo.proc_2 +Note 4093 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4092 At line 4 in demo.proc_1 +Note 4093 At line 4 in demo.proc_1 SET @@session.max_error_count = 7; SELECT @@session.max_error_count; @@session.max_error_count @@ -117,13 +117,13 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Note 4092 At line 4 in demo.proc_4 +Note 4093 At line 4 in demo.proc_4 Error 1644 Oops in proc_3 -Note 4092 At line 4 in demo.proc_3 +Note 4093 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4092 At line 4 in demo.proc_2 +Note 4093 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4092 At line 4 in demo.proc_1 +Note 4093 At line 4 in demo.proc_1 SET @@session.max_error_count = 9; SELECT @@session.max_error_count; @@session.max_error_count @@ -132,15 +132,15 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Note 4092 At line 4 in demo.proc_5 +Note 4093 At line 4 in demo.proc_5 Error 1644 Oops in proc_4 -Note 4092 At line 4 in demo.proc_4 +Note 4093 At line 4 in demo.proc_4 Error 1644 Oops in proc_3 -Note 4092 At line 4 in demo.proc_3 +Note 4093 At line 4 in demo.proc_3 Error 1644 Oops in proc_2 -Note 4092 At line 4 in demo.proc_2 +Note 4093 At line 4 in demo.proc_2 Error 1644 Oops in proc_1 -Note 4092 At line 4 in demo.proc_1 +Note 4093 At line 4 in demo.proc_1 drop database demo; SET @@global.max_error_count = @start_global_value; SELECT @@global.max_error_count; diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index ddb2901b8a4..3a4dc9db6f8 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -998,3 +998,306 @@ Pos Instruction 7 set b.a@1["a"] a.a@0["a"] DROP PROCEDURE p1; DROP TABLE t1; +# +# MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT +# +# Integer range FOR loop +CREATE PROCEDURE p1() +BEGIN +FOR i IN 1..3 +DO +SELECT i; +END FOR; +END; +$$ +CALL p1; +i +1 +i +2 +i +3 +SHOW PROCEDURE CODE p1; +Pos Instruction +0 set i@0 1 +1 set [upper_bound]@1 3 +2 jump_if_not 6(6) i@0 <= [upper_bound]@1 +3 stmt 0 "SELECT i" +4 set i@0 i@0 + 1 +5 jump 2 +DROP PROCEDURE p1; +# Nested integer range FOR loops +CREATE PROCEDURE p1() +BEGIN +fori: +FOR i IN 1..3 +DO +forj: +FOR j IN 1..3 +DO +IF i = 3 THEN +LEAVE fori; +END IF; +IF j = 3 THEN +LEAVE forj; +END IF; +SELECT i,j; +END FOR; +END FOR; +END; +$$ +CALL p1; +i j +1 1 +i j +1 2 +i j +2 1 +i j +2 2 +SHOW PROCEDURE CODE p1; +Pos Instruction +0 set i@0 1 +1 set [upper_bound]@1 3 +2 jump_if_not 17(17) i@0 <= [upper_bound]@1 +3 set j@2 1 +4 set [upper_bound]@3 3 +5 jump_if_not 13(13) j@2 <= [upper_bound]@3 +6 jump_if_not 8(8) i@0 = 3 +7 jump 17 +8 jump_if_not 10(10) j@2 = 3 +9 jump 13 +10 stmt 0 "SELECT i,j" +11 set j@2 j@2 + 1 +12 jump 5 +13 set i@0 i@0 + 1 +14 jump 2 +DROP PROCEDURE p1; +# Explicit cursor FOR loops +CREATE PROCEDURE p1() +BEGIN +DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b; +DECLARE cur1 CURSOR FOR SELECT 10 AS a, 'b0' AS b; +DECLARE cur2 CURSOR FOR SELECT 10 AS a, 'b0' AS b; +FOR rec1 IN cur1 +DO +SELECT rec1.a, rec1.b; +SET rec1.a= 11; +SET rec1.b= 'b1'; +SELECT rec1.a, rec1.b; +END FOR; +FOR rec0 IN cur0 +DO +SET rec0.a= 10; +SET rec0.b='b0'; +END FOR; +FOR rec2 IN cur2 +DO +SET rec2.a= 10; +SET rec2.b='b0'; +END FOR; +END; +$$ +SHOW PROCEDURE CODE p1; +Pos Instruction +0 cpush cur0@0 +1 cpush cur1@1 +2 cpush cur2@2 +3 cursor_copy_struct cur1 rec1@0 +4 copen cur1@1 +5 cfetch cur1@1 rec1@0 +6 jump_if_not 13(13) `cur1`%FOUND +7 stmt 0 "SELECT rec1.a, rec1.b" +8 set rec1.a@0["a"] 11 +9 set rec1.b@0["b"] 'b1' +10 stmt 0 "SELECT rec1.a, rec1.b" +11 cfetch cur1@1 rec1@0 +12 jump 6 +13 cursor_copy_struct cur0 rec0@1 +14 copen cur0@0 +15 cfetch cur0@0 rec0@1 +16 jump_if_not 21(21) `cur0`%FOUND +17 set rec0.a@1["a"] 10 +18 set rec0.b@1["b"] 'b0' +19 cfetch cur0@0 rec0@1 +20 jump 16 +21 cursor_copy_struct cur2 rec2@2 +22 copen cur2@2 +23 cfetch cur2@2 rec2@2 +24 jump_if_not 29(29) `cur2`%FOUND +25 set rec2.a@2["a"] 10 +26 set rec2.b@2["b"] 'b0' +27 cfetch cur2@2 rec2@2 +28 jump 24 +29 cpop 3 +DROP PROCEDURE p1; +# Nested explicit cursor FOR loops +CREATE PROCEDURE p1() +BEGIN +DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b; +FOR rec0 IN cur0 +DO +BEGIN +DECLARE cur1 CURSOR FOR SELECT 11 AS a, 'b1' AS b; +SET rec0.a= 11; +SET rec0.b= 'b0'; +FOR rec1 IN cur1 +DO +SET rec1.a= 11; +SET rec1.b= 'b1'; +BEGIN +DECLARE cur2 CURSOR FOR SELECT 12 AS a, 'b2' AS b; +FOR rec2 IN cur2 +DO +SET rec2.a=12; +SET rec2.b='b2'; +END FOR; +END; +END FOR; +END; +END FOR; +END; +$$ +SHOW PROCEDURE CODE p1; +Pos Instruction +0 cpush cur0@0 +1 cursor_copy_struct cur0 rec0@0 +2 copen cur0@0 +3 cfetch cur0@0 rec0@0 +4 jump_if_not 29(29) `cur0`%FOUND +5 cpush cur1@1 +6 set rec0.a@0["a"] 11 +7 set rec0.b@0["b"] 'b0' +8 cursor_copy_struct cur1 rec1@1 +9 copen cur1@1 +10 cfetch cur1@1 rec1@1 +11 jump_if_not 26(26) `cur1`%FOUND +12 set rec1.a@1["a"] 11 +13 set rec1.b@1["b"] 'b1' +14 cpush cur2@2 +15 cursor_copy_struct cur2 rec2@2 +16 copen cur2@2 +17 cfetch cur2@2 rec2@2 +18 jump_if_not 23(23) `cur2`%FOUND +19 set rec2.a@2["a"] 12 +20 set rec2.b@2["b"] 'b2' +21 cfetch cur2@2 rec2@2 +22 jump 18 +23 cpop 1 +24 cfetch cur1@1 rec1@1 +25 jump 11 +26 cpop 1 +27 cfetch cur0@0 rec0@0 +28 jump 4 +29 cpop 1 +DROP PROCEDURE p1; +# Implicit cursor FOR loops +CREATE PROCEDURE p1() +BEGIN +FOR rec1 IN (SELECT 11 AS a, 'b1' AS b) +DO +SELECT rec1.a, rec1.b; +SET rec1.a= 11; +SET rec1.b= 'b1'; +SELECT rec1.a, rec1.b; +END FOR; +FOR rec0 IN (SELECT 10 AS a, 'b0' AS b) +DO +SET rec0.a= 10; +SET rec0.b='b0'; +END FOR; +FOR rec2 IN (SELECT 12 AS a, 'b2' AS b) +DO +SET rec2.a= 10; +SET rec2.b='b0'; +END FOR; +END; +$$ +SHOW PROCEDURE CODE p1; +Pos Instruction +0 cpush [implicit_cursor]@0 +1 cursor_copy_struct [implicit_cursor] rec1@0 +2 copen [implicit_cursor]@0 +3 cfetch [implicit_cursor]@0 rec1@0 +4 jump_if_not 11(11) `[implicit_cursor]`%FOUND +5 stmt 0 "SELECT rec1.a, rec1.b" +6 set rec1.a@0["a"] 11 +7 set rec1.b@0["b"] 'b1' +8 stmt 0 "SELECT rec1.a, rec1.b" +9 cfetch [implicit_cursor]@0 rec1@0 +10 jump 4 +11 cpop 1 +12 cpush [implicit_cursor]@0 +13 cursor_copy_struct [implicit_cursor] rec0@1 +14 copen [implicit_cursor]@0 +15 cfetch [implicit_cursor]@0 rec0@1 +16 jump_if_not 21(21) `[implicit_cursor]`%FOUND +17 set rec0.a@1["a"] 10 +18 set rec0.b@1["b"] 'b0' +19 cfetch [implicit_cursor]@0 rec0@1 +20 jump 16 +21 cpop 1 +22 cpush [implicit_cursor]@0 +23 cursor_copy_struct [implicit_cursor] rec2@2 +24 copen [implicit_cursor]@0 +25 cfetch [implicit_cursor]@0 rec2@2 +26 jump_if_not 31(31) `[implicit_cursor]`%FOUND +27 set rec2.a@2["a"] 10 +28 set rec2.b@2["b"] 'b0' +29 cfetch [implicit_cursor]@0 rec2@2 +30 jump 26 +31 cpop 1 +DROP PROCEDURE p1; +# Nested implicit cursor FOR loops +CREATE PROCEDURE p1() +BEGIN +FOR rec0 IN (SELECT 10 AS a, 'b0' AS b) +DO +SET rec0.a= 11; +SET rec0.b= 'b0'; +FOR rec1 IN (SELECT 11 AS a, 'b1' AS b) +DO +SET rec1.a= 11; +SET rec1.b= 'b1'; +FOR rec2 IN (SELECT 12 AS a, 'b2' AS b) +DO +SET rec2.a=12; +SET rec2.b='b2'; +END FOR; +END FOR; +END FOR; +END; +$$ +SHOW PROCEDURE CODE p1; +Pos Instruction +0 cpush [implicit_cursor]@0 +1 cursor_copy_struct [implicit_cursor] rec0@0 +2 copen [implicit_cursor]@0 +3 cfetch [implicit_cursor]@0 rec0@0 +4 jump_if_not 29(29) `[implicit_cursor]`%FOUND +5 set rec0.a@0["a"] 11 +6 set rec0.b@0["b"] 'b0' +7 cpush [implicit_cursor]@1 +8 cursor_copy_struct [implicit_cursor] rec1@1 +9 copen [implicit_cursor]@1 +10 cfetch [implicit_cursor]@1 rec1@1 +11 jump_if_not 26(26) `[implicit_cursor]`%FOUND +12 set rec1.a@1["a"] 11 +13 set rec1.b@1["b"] 'b1' +14 cpush [implicit_cursor]@2 +15 cursor_copy_struct [implicit_cursor] rec2@2 +16 copen [implicit_cursor]@2 +17 cfetch [implicit_cursor]@2 rec2@2 +18 jump_if_not 23(23) `[implicit_cursor]`%FOUND +19 set rec2.a@2["a"] 12 +20 set rec2.b@2["b"] 'b2' +21 cfetch [implicit_cursor]@2 rec2@2 +22 jump 18 +23 cpop 1 +24 cfetch [implicit_cursor]@1 rec1@1 +25 jump 11 +26 cpop 1 +27 cfetch [implicit_cursor]@0 rec0@0 +28 jump 4 +29 cpop 1 +DROP PROCEDURE p1; diff --git a/mysql-test/r/sp-cursor.result b/mysql-test/r/sp-cursor.result index d068c19b22b..1f8cb7f0635 100644 --- a/mysql-test/r/sp-cursor.result +++ b/mysql-test/r/sp-cursor.result @@ -480,3 +480,134 @@ DROP PROCEDURE p1; # # End of MDEV-12457 Cursors with parameters # +# +# MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT +# +# Explicit cursor +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3'); +BEGIN NOT ATOMIC +DECLARE cur CURSOR FOR SELECT * FROM t1; +FOR rec IN cur +DO +SELECT rec.a AS a, rec.b AS b; +END FOR; +END; +$$ +a b +1 b1 +a b +2 b2 +a b +3 b3 +DROP TABLE t1; +# Explicit cursor with parameters +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3'); +BEGIN NOT ATOMIC +DECLARE cur CURSOR(pa INT) FOR SELECT * FROM t1 WHERE a>=pa; +FOR rec IN cur(2) +DO +SELECT rec.a AS a, rec.b AS b; +END FOR; +END; +$$ +a b +2 b2 +a b +3 b3 +DROP TABLE t1; +# Explicit cursor + label +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); +BEGIN NOT ATOMIC +DECLARE cur CURSOR FOR SELECT * FROM t1; +forrec: +FOR rec IN cur +DO +SELECT rec.a AS a, rec.b AS b; +IF rec.a = 2 THEN +LEAVE forrec; +END IF; +END FOR forrec; +END; +$$ +a b +1 b1 +a b +2 b2 +DROP TABLE t1; +# Explicit cursor + FETCH inside the loop body produce an error on "NOT FOUND" +BEGIN NOT ATOMIC +DECLARE x INT; +DECLARE cur CURSOR FOR SELECT 1 AS x; +FOR rec IN cur +DO +FETCH cur INTO x; +END FOR; +END; +$$ +ERROR 02000: No data - zero rows fetched, selected, or processed +# Explicit cursor + FETCH inside the loop body are normally handled by "HANDLER FOR NOT FOUND" +BEGIN NOT ATOMIC +DECLARE done INT DEFAULT 0; +DECLARE cur CURSOR FOR SELECT 1 AS x, 'y1' AS y UNION +SELECT 2,'y2' UNION +SELECT 3,'y3'; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; +forrec: +FOR rec IN cur +DO +SELECT CONCAT(rec.x, ' ', rec.y) AS 'Implicit FETCH'; +FETCH cur INTO rec; +IF done THEN +SELECT 'NO DATA' AS `Explicit FETCH`; +LEAVE forrec; +ELSE +SELECT CONCAT(rec.x, ' ', rec.y) AS 'Explicit FETCH'; +END IF; +END FOR; +END; +$$ +Implicit FETCH +1 y1 +Explicit FETCH +2 y2 +Implicit FETCH +3 y3 +Explicit FETCH +NO DATA +# Implicit cursor +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); +BEGIN NOT ATOMIC +FOR rec IN (SELECT * FROM t1) +DO +SELECT rec.a AS a, rec.b AS b; +END FOR; +END; +$$ +a b +1 b1 +a b +2 b2 +DROP TABLE t1; +# Implicit cursor + label +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); +BEGIN NOT ATOMIC +forrec: +FOR rec IN (SELECT * FROM t1) +DO +SELECT rec.a AS a, rec.b AS b; +IF rec.a = 2 THEN +LEAVE forrec; +END IF; +END FOR; +END; +$$ +a b +1 b1 +a b +2 b2 +DROP TABLE t1; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index f0bc1874850..40643a97765 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1990,8 +1990,8 @@ Warning 1264 Out of range value for column 'a' at row 1 Note 1292 Truncated incorrect INTEGER value: '222222 ' Warning 1264 Out of range value for column 'b' at row 1 Error 1048 Column 'c' cannot be null -Note 4092 At line 6 in test.t1_bi -Note 4092 At line 2 in test.p1 +Note 4093 At line 6 in test.t1_bi +Note 4093 At line 2 in test.p1 DROP TABLE t1; DROP TABLE t2; diff --git a/mysql-test/r/sp-for-loop.result b/mysql-test/r/sp-for-loop.result new file mode 100644 index 00000000000..0da09586df5 --- /dev/null +++ b/mysql-test/r/sp-for-loop.result @@ -0,0 +1,208 @@ +# +# MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT +# +CREATE TABLE t1 (a INT); +FOR i IN 1..3 +DO +INSERT INTO t1 VALUES (i); +END FOR; +/ +SELECT * FROM t1; +a +1 +2 +3 +DROP TABLE t1; +CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURNS INT +BEGIN +DECLARE total INT DEFAULT 0; +FOR i IN lower_bound . . upper_bound +DO +NULL +END FOR; +RETURN total; +END; +/ +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '. upper_bound +DO +NULL +END FOR; +RETURN total; +END' at line 4 +CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURNS INT +BEGIN +DECLARE total INT DEFAULT 0; +lab: +FOR i IN lower_bound .. upper_bound +DO +SET total= total + i; +IF i = lim THEN +LEAVE lab; +END IF; +-- Bounds are calculated only once. +-- The below assignments have no effect on the loop condition +SET lower_bound= 900; +SET upper_bound= 1000; +END FOR; +RETURN total; +END; +/ +SELECT f1(1, 3, 100) FROM DUAL; +f1(1, 3, 100) +6 +SELECT f1(1, 3, 2) FROM DUAL; +f1(1, 3, 2) +3 +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS INT +BEGIN +DECLARE total INT DEFAULT 0; +FOR i IN 1 .. 5 +DO +SET total= total + 1000; +forj: +FOR j IN 1 .. 5 +DO +SET total= total + 1; +IF j = 3 THEN +LEAVE forj; -- End the internal loop +END IF; +END FOR; +END FOR; +RETURN total; +END; +/ +SELECT f1() FROM DUAL; +f1() +5015 +DROP FUNCTION f1; +CREATE FUNCTION f1 (a INT, b INT) RETURNS INT +BEGIN +DECLARE total INT DEFAULT 0; +fori: +FOR i IN REVERSE a..1 +DO +SET total= total + i; +IF i = b THEN +LEAVE fori; +END IF; +END FOR; +RETURN total; +END +/ +SELECT f1(3, 100) FROM DUAL; +f1(3, 100) +6 +SELECT f1(3, 2) FROM DUAL; +f1(3, 2) +5 +DROP FUNCTION f1; +# Testing labeled FOR LOOP statement +CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURNS INT +BEGIN +DECLARE total INT DEFAULT 0; +la: +FOR ia IN 1 .. a +DO +SET total= total + 1000; +lb: +FOR ib IN 1 .. b +DO +SET total= total + 1; +IF ib = limitb THEN +LEAVE lb; +END IF; +IF ia = limita THEN +LEAVE la; +END IF; +END FOR lb; +END FOR la; +RETURN total; +END; +/ +SELECT f1(1, 1, 1, 1) FROM DUAL; +f1(1, 1, 1, 1) +1001 +SELECT f1(1, 2, 1, 2) FROM DUAL; +f1(1, 2, 1, 2) +1001 +SELECT f1(2, 1, 2, 1) FROM DUAL; +f1(2, 1, 2, 1) +2002 +SELECT f1(2, 1, 2, 2) FROM DUAL; +f1(2, 1, 2, 2) +1001 +SELECT f1(2, 2, 2, 2) FROM DUAL; +f1(2, 2, 2, 2) +2003 +SELECT f1(2, 3, 2, 3) FROM DUAL; +f1(2, 3, 2, 3) +2004 +DROP FUNCTION f1; +# Testing labeled ITERATE in a labeled FOR LOOP statement +CREATE FUNCTION f1 (a INT, b INT, blim INT) RETURNS INT +BEGIN +DECLARE total INT DEFAULT 0; +la: +FOR ia IN 1 .. a +DO +SET total= total + 1000; +BEGIN +DECLARE ib INT DEFAULT 1; +WHILE ib <= b +DO +IF ib > blim THEN +ITERATE la; +END IF; +SET ib= ib + 1; +SET total= total + 1; +END WHILE; +END; +END FOR la; +RETURN total; +END; +/ +SELECT f1(3,3,0), f1(3,3,1), f1(3,3,2), f1(3,3,3), f1(3,3,4) FROM DUAL; +f1(3,3,0) f1(3,3,1) f1(3,3,2) f1(3,3,3) f1(3,3,4) +3000 3003 3006 3009 3009 +DROP FUNCTION f1; +# Testing INTERATE statement +CREATE FUNCTION f1(a INT) RETURNS INT +BEGIN +DECLARE total INT DEFAULT 0; +fori: +FOR i IN 1 .. a +DO +IF i=5 THEN +ITERATE fori; +END IF; +SET total= total + 1; +END FOR; +RETURN total; +END; +/ +SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL; +f1(3) f1(4) f1(5) f1(6) +3 4 4 5 +DROP FUNCTION f1; +CREATE FUNCTION f1(a INT) RETURNS INT +BEGIN +DECLARE total INT DEFAULT 0; +lj: +FOR j IN 1 .. 2 +DO +FOR i IN 1 .. a +DO +IF i=5 THEN +ITERATE lj; +END IF; +SET total= total + 1; +END FOR; +END FOR; +RETURN total; +END; +/ +SELECT f1(3), f1(4), f1(5) FROM DUAL; +f1(3) f1(4) f1(5) +6 8 8 +DROP FUNCTION f1; diff --git a/mysql-test/r/sp-group.result b/mysql-test/r/sp-group.result index 535e67046d8..800d83f1f74 100644 --- a/mysql-test/r/sp-group.result +++ b/mysql-test/r/sp-group.result @@ -3,7 +3,7 @@ Warnings: Note 1051 Unknown table 'test.t1' drop view if exists view_t1; Warnings: -Note 4090 Unknown VIEW: 'test.view_t1' +Note 4091 Unknown VIEW: 'test.view_t1' SET sql_mode=ONLY_FULL_GROUP_BY; CREATE TABLE t1 ( pk INT, diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index b66faec260f..2b15bd09cc4 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -3211,7 +3211,7 @@ drop procedure bug10961| DROP PROCEDURE IF EXISTS bug6866| DROP VIEW IF EXISTS tv| Warnings: -Note 4090 Unknown VIEW: 'test.tv' +Note 4091 Unknown VIEW: 'test.tv' DROP TABLE IF EXISTS tt1,tt2,tt3| Warnings: Note 1051 Unknown table 'test.tt1' @@ -7823,7 +7823,7 @@ ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show warnings; Level Code Message Error 1062 Duplicate entry '2' for key 'PRIMARY' -Note 4092 At line 5 in test.p1 +Note 4093 At line 5 in test.p1 select * from t1; id 1 @@ -8116,6 +8116,18 @@ CALL p(); drop procedure p; drop view v; drop table t, tmp_t; +# +# MDEV-13936: Server crashes in Time_and_counter_tracker::incr_loops +# +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1 WHERE RAND() > 0.5; +CREATE FUNCTION f1() RETURNS INT RETURN ( SELECT MAX(i) FROM v1 ); +REPLACE INTO v1 VALUES (f1()); +ERROR HY000: The target table v1 of the INSERT is not insertable-into +SET @aux = f1(); +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1; #End of 10.1 tests # # MDEV-11081: CURSOR for query with GROUP BY diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 2b843f68499..091cbec4c73 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -2329,6 +2329,23 @@ DROP TRIGGER t1_bi; DROP TABLE t1; SET TIMESTAMP=DEFAULT; set time_zone= @@global.time_zone; +# +# MDEV-13936: Server crashes in Time_and_counter_tracker::incr_loops +# +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1 WHERE RAND() > 0.5; +CREATE TABLE t2 (a int); +CREATE TABLE t3 (a int); +create trigger trg after insert on t2 for each row +INSERT INTO t3 SELECT MAX(i) FROM v1 UNION SELECT MAX(i) FROM v1; +drop table t1; +insert into t2 value (2); +ERROR 42S02: Table 'test.t1' doesn't exist +CREATE TABLE t1 (i INT); +insert into t2 value (2); +DROP VIEW v1; +DROP TABLE t1,t2,t3; +End of 10.1 tests. create table t1 (i int); create trigger tr1 after insert on t1 for each row set @a=@a+1; create trigger tr2 after insert on t1 for each row set @a=@a+1; diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result index 1258de317ec..ae72619b5ae 100644 --- a/mysql-test/r/type_set.result +++ b/mysql-test/r/type_set.result @@ -352,3 +352,9 @@ EXPLAIN SELECT * FROM t1 WHERE a='1.1'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; +# +# MDEV-11155 Bad error message when creating a SET column with comma and non-ASCII characters +# +SET NAMES utf8; +CREATE TABLE t1 (a SET('a,bü')); +ERROR 22007: Illegal set 'a,bü' value found during parsing diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 635323a5867..2d442fe90a9 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5166,7 +5166,7 @@ CREATE TABLE t4 (i4 INT); INSERT INTO t4 VALUES (1),(2); DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' CREATE VIEW v1 AS select coalesce(j1,i3) AS v1_field1 from t2 join t3 left join t1 on ( i1 = i2 ); CREATE VIEW v2 AS select v1_field1 from t4 join v1; prepare my_stmt from "select v1_field1 from v2"; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 5d805ac572a..d808479e8ef 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -353,7 +353,7 @@ ERROR 23000: Duplicate entry '11' for key 'a' SHOW WARNINGS; Level Code Message -Note 4092 At line 4 in test.f1 +Note 4093 At line 4 in test.f1 Error 1062 Duplicate entry '11' for key 'a' DROP TABLE t1; diff --git a/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result b/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result new file mode 100644 index 00000000000..99f2a57835f --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_flush_binlogs_delete_domain.result @@ -0,0 +1,78 @@ +RESET MASTER; +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (); +and the command execution is effective thence rotates binlog as usual +show binary logs; +Log_name File_size +master-bin.000001 # +master-bin.000002 # +Non-existed domain is warned, the command completes without rotation +but with a warning +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (99); +Warnings: +Warning 1076 The gtid domain being deleted ('99') is not in the current binlog state +show binary logs; +Log_name File_size +master-bin.000001 # +master-bin.000002 # +SET @@SESSION.gtid_domain_id=1; +SET @@SESSION.server_id=1; +CREATE TABLE t (a int); +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); +ERROR HY000: Could not delete gtid domain. Reason: binlog files may contain gtids from the domain ('1') being deleted. Make sure to first purge those files. +FLUSH BINARY LOGS; +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); +ERROR HY000: Could not delete gtid domain. Reason: binlog files may contain gtids from the domain ('1') being deleted. Make sure to first purge those files. +PURGE BINARY LOGS TO 'master-bin.000003';; +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); +Gtid_list of the current binlog does not contain '1': +show binlog events in 'master-bin.000004' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000004 # Gtid_list 1 # [] +But the previous log's Gtid_list may have it which explains a warning from the following command +show binlog events in 'master-bin.000003' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000003 # Gtid_list 1 # [1-1-1] +Already deleted domain in Gtid_list of the earliest log is benign +but may cause a warning +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); +Warnings: +Warning 1076 The current gtid binlog state is incompatible with a former one missing gtids from the '1-1' domain-server pair which is referred to in the gtid list describing an earlier state. Ignore if the domain ('1') was already explicitly deleted. +Warning 1076 The gtid domain being deleted ('1') is not in the current binlog state +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0); +ERROR HY000: Could not delete gtid domain. Reason: binlog files may contain gtids from the domain ('1') being deleted. Make sure to first purge those files. +FLUSH BINARY LOGS; +PURGE BINARY LOGS TO 'master-bin.000005'; +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0); +Warnings: +Warning 1076 The gtid domain being deleted ('0') is not in the current binlog state +Gtid_list of the current binlog does not contain 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 0: +show binlog events in 'master-bin.000006' limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000006 # Gtid_list 1 # [] +SET @@SESSION.gtid_domain_id=1;; +SET @@SESSION.server_id=1; +SET @@SESSION.gtid_seq_no=1; +INSERT INTO t SET a=1; +SET @@SESSION.server_id=2; +SET @@SESSION.gtid_seq_no=2; +INSERT INTO t SET a=2; +SET @@SESSION.gtid_domain_id=11; +SET @@SESSION.server_id=11; +SET @@SESSION.gtid_seq_no=11; +INSERT INTO t SET a=11; +SET @gtid_binlog_state_saved=@@GLOBAL.gtid_binlog_state; +FLUSH BINARY LOGS; +SET @@SESSION.gtid_domain_id=11; +SET @@SESSION.server_id=11; +SET @@SESSION.gtid_seq_no=1; +INSERT INTO t SET a=1; +SELECT @gtid_binlog_state_saved "as original state", @@GLOBAL.gtid_binlog_state as "out of order for 11 domain state"; +as original state out of order for 11 domain state +1-1-1,1-2-2,11-11-11 1-1-1,1-2-2,11-11-1 +PURGE BINARY LOGS TO 'master-bin.000007'; +the following command succeeds with warnings +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); +Warnings: +Warning 1076 The current gtid binlog state is incompatible with a former one having a gtid '11-11-1' which is less than the '11-11-11' of the gtid list describing an earlier state. The state may have been affected by manually injecting a lower sequence number gtid or via replication. +DROP TABLE t; +RESET MASTER; diff --git a/mysql-test/suite/binlog/r/binlog_gtid_delete_domain_debug.result b/mysql-test/suite/binlog/r/binlog_gtid_delete_domain_debug.result new file mode 100644 index 00000000000..b4627caceb2 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_gtid_delete_domain_debug.result @@ -0,0 +1,6 @@ +SET @@SESSION.debug_dbug='+d,inject_binlog_delete_domain_init_error'; +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (99); +ERROR HY000: Could not delete gtid domain. Reason: injected error. +SHOW WARNINGS; +Level Code Message +Error 1076 Could not delete gtid domain. Reason: injected error. diff --git a/mysql-test/suite/binlog/r/binlog_stm_ps.result b/mysql-test/suite/binlog/r/binlog_stm_ps.result index 0b7491e4364..75f64500878 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_ps.result +++ b/mysql-test/suite/binlog/r/binlog_stm_ps.result @@ -185,3 +185,46 @@ master-bin.000004 # Gtid # # GTID #-#-# master-bin.000004 # Query # # use `test`; DROP PROCEDURE p1 master-bin.000004 # Gtid # # GTID #-#-# master-bin.000004 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ +# +#MDEV-14467 Item_param: replace {INT|DECIMAL|REAL|STRING|TIME}_VALUE with Type_handler +# +FLUSH LOGS; +CREATE TABLE t1 (a INT); +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING 10; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING 10.1; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING 10.1e0; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING '10'; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING TIME'10:10:10'; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted +DROP TABLE t1; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000005 # Binlog_checkpoint # # master-bin.000005 +master-bin.000005 # Gtid # # GTID #-#-# +master-bin.000005 # Query # # use `test`; CREATE TABLE t1 (a INT) +master-bin.000005 # Gtid # # BEGIN GTID #-#-# +master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 10 +master-bin.000005 # Query # # COMMIT +master-bin.000005 # Gtid # # BEGIN GTID #-#-# +master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 10 +master-bin.000005 # Query # # COMMIT +master-bin.000005 # Gtid # # BEGIN GTID #-#-# +master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 10 +master-bin.000005 # Query # # COMMIT +master-bin.000005 # Gtid # # BEGIN GTID #-#-# +master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 10 +master-bin.000005 # Query # # COMMIT +master-bin.000005 # Gtid # # BEGIN GTID #-#-# +master-bin.000005 # Query # # use `test`; INSERT INTO t1 SELECT 1 LIMIT 101010 +master-bin.000005 # Query # # COMMIT +master-bin.000005 # Gtid # # GTID #-#-# +master-bin.000005 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ diff --git a/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test b/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test new file mode 100644 index 00000000000..0faafa35a1b --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_flush_binlogs_delete_domain.test @@ -0,0 +1,137 @@ +# Prove basic properties of +# +# FLUSH BINARY LOGS DELETE_DOMAIN_ID = (...) +# +# The command removes the supplied list of domains from the current +# @@global.gtid_binlog_state provided the binlog files do not contain +# events from such domains. + +# The test is not format specific. One format is chosen to run it. +--source include/have_binlog_format_mixed.inc + +# Reset binlog state +RESET MASTER; + +# Empty list is accepted +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (); +--echo and the command execution is effective thence rotates binlog as usual +--source include/show_binary_logs.inc + +--echo Non-existed domain is warned, the command completes without rotation +--echo but with a warning +--let $binlog_pre_flush=query_get_value(SHOW MASTER STATUS, Position, 1) +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (99); +--let $binlog_start=$binlog_pre_flush +--source include/show_binary_logs.inc + +# Log one event in a specified domain and try to delete the domain +SET @@SESSION.gtid_domain_id=1; +SET @@SESSION.server_id=1; +CREATE TABLE t (a int); + +--error ER_BINLOG_CANT_DELETE_GTID_DOMAIN +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); + +# the same error after log rotation +FLUSH BINARY LOGS; +--error ER_BINLOG_CANT_DELETE_GTID_DOMAIN +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); + +# the latest binlog does not really contain any events incl ones from 1-domain +--let $purge_to_binlog= query_get_value(SHOW MASTER STATUS, File, 1) +--eval PURGE BINARY LOGS TO '$purge_to_binlog'; +# So now it's safe to delete +--error 0 +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); +--echo Gtid_list of the current binlog does not contain '1': +--let $binlog_file=query_get_value(SHOW MASTER STATUS, File, 1) +--source include/show_gtid_list.inc +--echo But the previous log's Gtid_list may have it which explains a warning from the following command +--let $binlog_file=$purge_to_binlog +--source include/show_gtid_list.inc + +--echo Already deleted domain in Gtid_list of the earliest log is benign +--echo but may cause a warning +--error 0 +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (1); + +# Few domains delete. The chosen number verifies among others how +# expected overrun of the static buffers of underlying dynamic arrays is doing. +--let $domain_cnt=17 +--let $server_in_domain_cnt=3 +--let $domain_list= +--disable_query_log +while ($domain_cnt) +{ + --let servers=$server_in_domain_cnt + --eval SET @@SESSION.gtid_domain_id=$domain_cnt + while ($servers) + { + --eval SET @@SESSION.server_id=10*$domain_cnt + $servers + --eval INSERT INTO t SET a=@@SESSION.server_id + + --dec $servers + } + --let $domain_list= $domain_cnt, $domain_list + + --dec $domain_cnt +} +--enable_query_log +--let $zero=0 +--let $domain_list= $domain_list$zero + +--error ER_BINLOG_CANT_DELETE_GTID_DOMAIN +--eval FLUSH BINARY LOGS DELETE_DOMAIN_ID = ($domain_list) + +# Now satisfy the safety condtion to purge log files containing $domain list +FLUSH BINARY LOGS; +--let $purge_to_binlog= query_get_value(SHOW MASTER STATUS, File, 1) +--eval PURGE BINARY LOGS TO '$purge_to_binlog' +--error 0 +--eval FLUSH BINARY LOGS DELETE_DOMAIN_ID = ($domain_list) +--echo Gtid_list of the current binlog does not contain $domain_list: +--let $binlog_file=query_get_value(SHOW MASTER STATUS, File, 1) +--source include/show_gtid_list.inc + +# Show reaction on @@global.gtid_binlog_state not succeeding +# earlier state as described by the 1st binlog' Gtid_list. +# Now let it be out-order gtid logged to a domain unrelated to deletion. + +--let $del_d_id=1 +--eval SET @@SESSION.gtid_domain_id=$del_d_id; +SET @@SESSION.server_id=1; +SET @@SESSION.gtid_seq_no=1; +INSERT INTO t SET a=1; +SET @@SESSION.server_id=2; +SET @@SESSION.gtid_seq_no=2; +INSERT INTO t SET a=2; + +SET @@SESSION.gtid_domain_id=11; +SET @@SESSION.server_id=11; +SET @@SESSION.gtid_seq_no=11; +INSERT INTO t SET a=11; + +SET @gtid_binlog_state_saved=@@GLOBAL.gtid_binlog_state; +FLUSH BINARY LOGS; + +# Inject out of order for domain '11' before +SET @@SESSION.gtid_domain_id=11; +SET @@SESSION.server_id=11; +SET @@SESSION.gtid_seq_no=1; +INSERT INTO t SET a=1; + +SELECT @gtid_binlog_state_saved "as original state", @@GLOBAL.gtid_binlog_state as "out of order for 11 domain state"; + +# to delete '1', first to purge logs containing its events +--let $purge_to_binlog= query_get_value(SHOW MASTER STATUS, File, 1) +--eval PURGE BINARY LOGS TO '$purge_to_binlog' + +--echo the following command succeeds with warnings +--eval FLUSH BINARY LOGS DELETE_DOMAIN_ID = ($del_d_id) + +# +# Cleanup +# + +DROP TABLE t; +RESET MASTER; diff --git a/mysql-test/suite/binlog/t/binlog_gtid_delete_domain_debug.test b/mysql-test/suite/binlog/t/binlog_gtid_delete_domain_debug.test new file mode 100644 index 00000000000..5de549c45bb --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_gtid_delete_domain_debug.test @@ -0,0 +1,11 @@ +# Check "internal" error branches of +# FLUSH BINARY LOGS DELETE_DOMAIN_ID = (...) +# handler. +--source include/have_debug.inc +--source include/have_binlog_format_mixed.inc + +SET @@SESSION.debug_dbug='+d,inject_binlog_delete_domain_init_error'; +--error ER_BINLOG_CANT_DELETE_GTID_DOMAIN +FLUSH BINARY LOGS DELETE_DOMAIN_ID = (99); + +SHOW WARNINGS; diff --git a/mysql-test/suite/binlog/t/binlog_stm_ps.test b/mysql-test/suite/binlog/t/binlog_stm_ps.test index e6e54985f6f..b83991b1356 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_ps.test +++ b/mysql-test/suite/binlog/t/binlog_stm_ps.test @@ -99,3 +99,19 @@ DROP TABLE t1; --let $binlog_file = LAST source include/show_binlog_events.inc; + +--echo # +--echo #MDEV-14467 Item_param: replace {INT|DECIMAL|REAL|STRING|TIME}_VALUE with Type_handler +--echo # + +FLUSH LOGS; +CREATE TABLE t1 (a INT); +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING 10; +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING 10.1; +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING 10.1e0; +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING '10'; +EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT 1 LIMIT ?' USING TIME'10:10:10'; +DROP TABLE t1; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; diff --git a/mysql-test/suite/compat/oracle/r/ps.result b/mysql-test/suite/compat/oracle/r/ps.result index ed7cb4c51d5..158d15e9f90 100644 --- a/mysql-test/suite/compat/oracle/r/ps.result +++ b/mysql-test/suite/compat/oracle/r/ps.result @@ -247,3 +247,18 @@ DROP PROCEDURE p1; # # End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions # +# +# MDEV-12846 sql_mode=ORACLE: using Oracle-style placeholders in direct query execution makes the server crash +# +SELECT ? FROM DUAL; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? FROM DUAL' at line 1 +SELECT :a FROM DUAL; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':a FROM DUAL' at line 1 +SELECT :1 FROM DUAL; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':1 FROM DUAL' at line 1 +SELECT 1+? FROM DUAL; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? FROM DUAL' at line 1 +SELECT 1+:a FROM DUAL; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':a FROM DUAL' at line 1 +SELECT 1+:1 FROM DUAL; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':1 FROM DUAL' at line 1 diff --git a/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result index 3030a3dc658..a46daf30a8f 100644 --- a/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result +++ b/mysql-test/suite/compat/oracle/r/sp-cursor-rowtype.result @@ -1359,3 +1359,36 @@ t2 CREATE TABLE "t2" ( "b" varchar(3) DEFAULT NULL, "c" time DEFAULT NULL ) +# +# MDEV-14388 Server crashes in handle_select / val_uint in ORACLE mode +# +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (0),(1),(2),(3); +CREATE FUNCTION f1() RETURN INT is +BEGIN +FOR v1 in (SELECT id FROM t1) +LOOP +NULL; +END LOOP; +RETURN 1; +END; +$$ +SELECT f1(); +f1() +1 +DROP FUNCTION f1; +DROP TABLE t1; +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); +CREATE FUNCTION f1() RETURN INT IS +CURSOR cur IS SELECT id FROM t1; +rec cur%ROWTYPE; +BEGIN +RETURN 1; +END; +$$ +SELECT f1(); +f1() +1 +DROP FUNCTION f1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result index 53411e80251..2bf4f50e6d5 100644 --- a/mysql-test/suite/compat/oracle/r/sp.result +++ b/mysql-test/suite/compat/oracle/r/sp.result @@ -2404,3 +2404,31 @@ t1 CREATE TABLE "t1" ( "aa_timestamp5" timestamp(5) NULL DEFAULT NULL, "aa_date0" datetime DEFAULT NULL ) +# +# MDEV-11160 "Incorrect column name" when "CREATE TABLE t1 AS SELECT spvar" +# +CREATE TABLE t1 (x INT); +INSERT INTO t1 VALUES (10); +CREATE VIEW v1 AS SELECT x+1 AS a,x+1 AS b FROM t1; +CREATE PROCEDURE p1 +AS +a INT := 1; +b INT := 2; +BEGIN +CREATE TABLE t2 AS SELECT a,b FROM v1; +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; +END; +$$ +CALL p1(); +Table Create Table +t2 CREATE TABLE "t2" ( + "a" int(11) DEFAULT NULL, + "b" int(11) DEFAULT NULL +) +a b +1 2 +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/ps.test b/mysql-test/suite/compat/oracle/t/ps.test index 08bb957c33f..357b50e0eab 100644 --- a/mysql-test/suite/compat/oracle/t/ps.test +++ b/mysql-test/suite/compat/oracle/t/ps.test @@ -264,3 +264,27 @@ DROP PROCEDURE p1; --echo # --echo # End of MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions --echo # + + +--echo # +--echo # MDEV-12846 sql_mode=ORACLE: using Oracle-style placeholders in direct query execution makes the server crash +--echo # + +# When running with --ps, the below queries return +# CR_PARAMS_NOT_BOUND instead of ER_PARSE_ERROR + +--disable_ps_protocol +--error ER_PARSE_ERROR +SELECT ? FROM DUAL; +--error ER_PARSE_ERROR +SELECT :a FROM DUAL; +--error ER_PARSE_ERROR +SELECT :1 FROM DUAL; + +--error ER_PARSE_ERROR +SELECT 1+? FROM DUAL; +--error ER_PARSE_ERROR +SELECT 1+:a FROM DUAL; +--error ER_PARSE_ERROR +SELECT 1+:1 FROM DUAL; +--enable_ps_protocol diff --git a/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test b/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test index 19a50eacca1..fd148d1f261 100644 --- a/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test +++ b/mysql-test/suite/compat/oracle/t/sp-cursor-rowtype.test @@ -1444,3 +1444,39 @@ BEGIN END; $$ DELIMITER ;$$ + +--echo # +--echo # MDEV-14388 Server crashes in handle_select / val_uint in ORACLE mode +--echo # + +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (0),(1),(2),(3); +DELIMITER $$; +CREATE FUNCTION f1() RETURN INT is +BEGIN + FOR v1 in (SELECT id FROM t1) + LOOP + NULL; + END LOOP; + RETURN 1; +END; +$$ +DELIMITER ;$$ +SELECT f1(); +DROP FUNCTION f1; +DROP TABLE t1; + +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); +DELIMITER $$; +CREATE FUNCTION f1() RETURN INT IS + CURSOR cur IS SELECT id FROM t1; + rec cur%ROWTYPE; +BEGIN + RETURN 1; +END; +$$ +DELIMITER ;$$ +SELECT f1(); +DROP FUNCTION f1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test index 61a37459483..e7adbb5403a 100644 --- a/mysql-test/suite/compat/oracle/t/sp.test +++ b/mysql-test/suite/compat/oracle/t/sp.test @@ -2233,3 +2233,30 @@ BEGIN END; $$ DELIMITER ;$$ + + +--echo # +--echo # MDEV-11160 "Incorrect column name" when "CREATE TABLE t1 AS SELECT spvar" +--echo # + + +CREATE TABLE t1 (x INT); +INSERT INTO t1 VALUES (10); +CREATE VIEW v1 AS SELECT x+1 AS a,x+1 AS b FROM t1; +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a INT := 1; + b INT := 2; +BEGIN + CREATE TABLE t2 AS SELECT a,b FROM v1; + SHOW CREATE TABLE t2; + SELECT * FROM t2; + DROP TABLE t2; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result index 5a27cb65b3d..93fd45dc9f9 100644 --- a/mysql-test/suite/funcs_1/r/innodb_views.result +++ b/mysql-test/suite/funcs_1/r/innodb_views.result @@ -4314,7 +4314,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4090 Unknown VIEW: 'test.v2' +Note 4091 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -7566,7 +7566,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -21312,7 +21312,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result index a2af9082c72..1b167b611df 100644 --- a/mysql-test/suite/funcs_1/r/memory_views.result +++ b/mysql-test/suite/funcs_1/r/memory_views.result @@ -4315,7 +4315,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4090 Unknown VIEW: 'test.v2' +Note 4091 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -7567,7 +7567,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -21314,7 +21314,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/myisam_views-big.result b/mysql-test/suite/funcs_1/r/myisam_views-big.result index 3290b3dd36a..ff7d0a595ab 100644 --- a/mysql-test/suite/funcs_1/r/myisam_views-big.result +++ b/mysql-test/suite/funcs_1/r/myisam_views-big.result @@ -4784,7 +4784,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4090 Unknown VIEW: 'test.v2' +Note 4091 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -8387,7 +8387,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -22989,7 +22989,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4090 Unknown VIEW: 'test.v1' +Note 4091 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result index b36f995d32f..c86a73618df 100644 --- a/mysql-test/suite/funcs_1/r/storedproc.result +++ b/mysql-test/suite/funcs_1/r/storedproc.result @@ -4477,7 +4477,7 @@ CREATE PROCEDURE sp1() for:BEGIN SELECT @x; END// -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'for:BEGIN +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':BEGIN SELECT @x; END' at line 2 DROP PROCEDURE IF EXISTS sp1; diff --git a/mysql-test/suite/galera/r/MW-388.result b/mysql-test/suite/galera/r/MW-388.result new file mode 100644 index 00000000000..17d347a11fb --- /dev/null +++ b/mysql-test/suite/galera/r/MW-388.result @@ -0,0 +1,46 @@ +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(255)) Engine=InnoDB; +CREATE PROCEDURE insert_proc () +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO; +END; +INSERT INTO t1 VALUES (1, 'node 1'),(2, 'node 1'); +INSERT INTO t1 VALUES (3, 'node 1'); +END| +SET GLOBAL wsrep_slave_threads = 2; +SET GLOBAL DEBUG = "d,sync.wsrep_apply_cb"; +Warnings: +Warning 1287 '@@debug' is deprecated and will be removed in a future release. Please use '@@debug_dbug' instead +INSERT INTO t1 VALUES (1, 'node 2');; +SET SESSION DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_cb_reached"; +SET SESSION wsrep_sync_wait = 0; +SET SESSION DEBUG_SYNC = 'wsrep_after_replication SIGNAL wsrep_after_replication_reached WAIT_FOR wsrep_after_replication_continue'; +CALL insert_proc ();; +SET SESSION DEBUG_SYNC = "now WAIT_FOR wsrep_after_replication_reached"; +SET GLOBAL DEBUG = ""; +Warnings: +Warning 1287 '@@debug' is deprecated and will be removed in a future release. Please use '@@debug_dbug' instead +SET DEBUG_SYNC = "now SIGNAL wsrep_after_replication_continue"; +SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; +SELECT @errno = 1213; +@errno = 1213 +1 +SELECT * FROM t1; +f1 f2 +1 node 2 +3 node 1 +SELECT * FROM t1; +f1 f2 +1 node 2 +3 node 1 +SET GLOBAL wsrep_slave_threads = DEFAULT; +DROP TABLE t1; +DROP PROCEDURE insert_proc; +SET GLOBAL debug = NULL; +Warnings: +Warning 1287 '@@debug' is deprecated and will be removed in a future release. Please use '@@debug_dbug' instead +SET debug_sync='RESET'; +SELECT @@debug_sync; +@@debug_sync +ON - current signal: '' diff --git a/mysql-test/suite/galera/r/sql_log_bin.result b/mysql-test/suite/galera/r/sql_log_bin.result index 8b208ff82d5..c175a0a0e7a 100644 --- a/mysql-test/suite/galera/r/sql_log_bin.result +++ b/mysql-test/suite/galera/r/sql_log_bin.result @@ -7,6 +7,7 @@ INSERT INTO t1 VALUES (1); # Disable binary logging for current session SET SQL_LOG_BIN=OFF; INSERT INTO t1 VALUES (2); +FLUSH BINARY LOGS; CREATE TABLE t2(c1 INT PRIMARY KEY) ENGINE=INNODB; INSERT INTO t2 VALUES (1); CREATE TABLE test.t3 AS SELECT * from t1; diff --git a/mysql-test/suite/galera/t/MW-388.test b/mysql-test/suite/galera/t/MW-388.test new file mode 100644 index 00000000000..209695dca80 --- /dev/null +++ b/mysql-test/suite/galera/t/MW-388.test @@ -0,0 +1,76 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--connection node_1 +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(255)) Engine=InnoDB; + +DELIMITER |; +CREATE PROCEDURE insert_proc () +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION + BEGIN + GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO; + END; + INSERT INTO t1 VALUES (1, 'node 1'),(2, 'node 1'); + INSERT INTO t1 VALUES (3, 'node 1'); +END| +DELIMITER ;| + +# We need two slave threads here to guarantee progress. +# If we use only one thread the following could happen +# in node_1: +# We block the only slave thread in wsrep_apply_cb and we +# issue an INSERT (by calling the stored procedure) that will +# try to acquire galera's local monitor in pre_commit(). +# This usually works fine, except for when a commit cut event +# sneaks in the slave queue and gets a local seqno smaller than +# that of the INSERT. Because there is only one slave thread, +# commit cut is not processed and therefore does not advance +# local monitor, and our INSERT remains stuck there. +SET GLOBAL wsrep_slave_threads = 2; +SET GLOBAL DEBUG = "d,sync.wsrep_apply_cb"; + +--connection node_2 +--send INSERT INTO t1 VALUES (1, 'node 2'); + +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--connection node_1a +SET SESSION DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_cb_reached"; + +--connection node_1 +SET SESSION wsrep_sync_wait = 0; +SET SESSION DEBUG_SYNC = 'wsrep_after_replication SIGNAL wsrep_after_replication_reached WAIT_FOR wsrep_after_replication_continue'; +--send CALL insert_proc (); + +--connection node_1a +SET SESSION DEBUG_SYNC = "now WAIT_FOR wsrep_after_replication_reached"; + + +SET GLOBAL DEBUG = ""; +SET DEBUG_SYNC = "now SIGNAL wsrep_after_replication_continue"; +SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; + +--connection node_2 +--reap + +--connection node_1 +# We expect no errors here, because the handler in insert_proc() caught the deadlock error +--reap +SELECT @errno = 1213; +SELECT * FROM t1; + +--connection node_2 +SELECT * FROM t1; + +--connection node_1 +SET GLOBAL wsrep_slave_threads = DEFAULT; +DROP TABLE t1; +DROP PROCEDURE insert_proc; + +SET GLOBAL debug = NULL; +SET debug_sync='RESET'; + +# Make sure no pending signals are leftover to surprise subsequent tests. +SELECT @@debug_sync; diff --git a/mysql-test/suite/galera/t/galera_ftwrl.test b/mysql-test/suite/galera/t/galera_ftwrl.test index de8310e52d2..739255609ee 100644 --- a/mysql-test/suite/galera/t/galera_ftwrl.test +++ b/mysql-test/suite/galera/t/galera_ftwrl.test @@ -29,12 +29,11 @@ SELECT * FROM t1; UNLOCK TABLES; -SHOW TABLES; -SELECT COUNT(*) = 1 FROM t1; - --disable_query_log --eval SET GLOBAL wsrep_provider_options = "$wsrep_provider_options_orig"; --enable_query_log -DROP TABLE t1; +SHOW TABLES; +SELECT COUNT(*) = 1 FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_suspend_slave.test b/mysql-test/suite/galera/t/galera_suspend_slave.test index dcc4a8d14c3..aa4543cf81c 100644 --- a/mysql-test/suite/galera/t/galera_suspend_slave.test +++ b/mysql-test/suite/galera/t/galera_suspend_slave.test @@ -25,7 +25,7 @@ CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; my $pid_filename = $ENV{'NODE_2_PIDFILE'}; my $mysqld_pid = `cat $pid_filename`; chomp($mysqld_pid); - system("kill -19 $mysqld_pid"); + system("kill -SIGSTOP $mysqld_pid"); exit(0); EOF @@ -37,7 +37,7 @@ INSERT INTO t1 VALUES (1); my $pid_filename = $ENV{'NODE_2_PIDFILE'}; my $mysqld_pid = `cat $pid_filename`; chomp($mysqld_pid); - system("kill -18 $mysqld_pid"); + system("kill -SIGCONT $mysqld_pid"); exit(0); EOF diff --git a/mysql-test/suite/galera/t/sql_log_bin.test b/mysql-test/suite/galera/t/sql_log_bin.test index 615bc4c30af..9f8f7c84486 100644 --- a/mysql-test/suite/galera/t/sql_log_bin.test +++ b/mysql-test/suite/galera/t/sql_log_bin.test @@ -1,5 +1,13 @@ # Test to check the behavior of galera cluster with sql_log_bin=ON|OFF & binary # logging is disabled. sql_bin_log should not affect galera replication. +# +# The following bugfixes are tested: +# +# MDEV-9510: Segmentation fault in binlog thread. +# A scenario otherwise causing a similar segfault is replayed. +# The test must pass having no crashes. +# The sequence of sql statements is provided by original +# sql_log_bin.test augmented with a FLUSH BINLOG LOGS, below. --source include/galera_cluster.inc --source include/have_innodb.inc @@ -15,6 +23,10 @@ INSERT INTO t1 VALUES (1); --echo # Disable binary logging for current session SET SQL_LOG_BIN=OFF; INSERT INTO t1 VALUES (2); + +# MDEV-9510: the following binlog rotation due to FLUSH segfaults wo/ the fixes +FLUSH BINARY LOGS; + CREATE TABLE t2(c1 INT PRIMARY KEY) ENGINE=INNODB; INSERT INTO t2 VALUES (1); CREATE TABLE test.t3 AS SELECT * from t1; diff --git a/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc b/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc new file mode 100644 index 00000000000..3c10517933f --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc @@ -0,0 +1,185 @@ +# +# wl#7277: InnoDB: Bulk Load for Create Index +# + +# Create Insert Procedure +DELIMITER |; +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN + DECLARE i int DEFAULT 1; + + START TRANSACTION; + WHILE (i <= 10000) DO + IF i%2 = 0 AND load_even = 1 THEN + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + END IF; + IF i%2 != 0 AND load_even != 1 THEN + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + END IF; + SET i = i + 1; + END WHILE; + COMMIT; +END| +DELIMITER ;| + +SELECT @@innodb_fill_factor; + +if ($row_format != 'COMPRESSED') +{ + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') +{ + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +-- disable_query_log +# Load half records +CALL populate_t1(1); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +/* Create index. */ +CREATE INDEX idx_id ON t1(id); + +CREATE INDEX idx_title ON t1(title); + +/* Check table. */ +CHECK TABLE t1; + +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 5000; +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE id = 10000; +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE id = 10010; +SELECT * FROM t1 WHERE title = 'a10010'; + +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 4010 AND id > 3990; +INSERT INTO t1 VALUES(4000, 4000, 'b4000'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990; + +SELECT * FROM t1 WHERE id = 3000; +SELECT * FROM t1 WHERE title = 'a3000'; +SELECT * FROM t1 WHERE title = 'b3000'; + +SELECT * FROM t1 WHERE id = 4000; +SELECT * FROM t1 WHERE title = 'a4000'; +SELECT * FROM t1 WHERE title = 'b4000'; + +SELECT * FROM t1 WHERE id = 4001; +SELECT * FROM t1 WHERE title = 'a4001'; + +-- disable_query_log +# Load half records (follow up load) +CALL populate_t1(0); +-- enable_query_log +SELECT COUNT(*) FROM t1; + + +/* Add column. */ +ALTER TABLE t1 ADD COLUMN content TEXT; + +CHECK TABLE t1; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 5000; +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE id = 10000; +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE id = 10010; +SELECT * FROM t1 WHERE title = 'a10010'; + +/* Drop column. */ +ALTER TABLE t1 DROP COLUMN content; + +CHECK TABLE t1; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 5000; +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE id = 10000; +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE id = 10010; +SELECT * FROM t1 WHERE title = 'a10010'; + +DROP TABLE t1; + +# Test Blob +if ($row_format != 'COMPRESSED') { + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') { + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b BLOB, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +let $cnt= 5000; +-- disable_query_log +WHILE ($cnt>=4950) +{ +EVAL INSERT INTO t1 VALUES + ($cnt, REPEAT(CONCAT('a', $cnt),2000), CONCAT('a', $cnt)); +dec $cnt; +} +-- enable_query_log +ALTER TABLE t1 ADD INDEX `idx` (a,b(5)); + +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +DELETE FROM t1 WHERE a=4975 AND b like 'b4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; + +ALTER TABLE t1 DROP COLUMN c; + +CHECK TABLE t1; + +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; + +DROP TABLE t1; + +# Restore global variables +if ($row_format == 'COMPRESSED') +{ + SET GLOBAL innodb_file_per_table=default; +} + +DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc b/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc new file mode 100644 index 00000000000..48de3a1962d --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc @@ -0,0 +1,221 @@ +# +# wl#7277: InnoDB: Bulk Load for Create Index +# + +# Not supported in embedded +-- source include/not_embedded.inc + +# This test case needs to crash the server. Needs a debug server. +-- source include/have_debug.inc + +# Don't test this under valgrind, memory leaks will occur. +-- source include/not_valgrind.inc + +# Avoid CrashReporter popup on Mac +-- source include/not_crashrep.inc + +-- source include/have_innodb.inc + +# Create Insert Procedure +DELIMITER |; +CREATE PROCEDURE populate_t1() +BEGIN + DECLARE i int DEFAULT 1; + + START TRANSACTION; + WHILE (i <= 10000) DO + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + SET i = i + 1; + END WHILE; + COMMIT; +END| +DELIMITER ;| + +# Test scenarios: +# 1. Test restart; +# 2. Test crash recovery. + +# Test Restart +if ($row_format != 'COMPRESSED') +{ + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') +{ + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +-- disable_query_log +CALL populate_t1(); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +CREATE INDEX idx_title ON t1(title); + +--source include/restart_mysqld.inc + +CHECK TABLE t1; + +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE title = 'a10010'; + +DROP TABLE t1; + +-- echo # Test Blob + +if ($row_format != 'COMPRESSED') { + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') { + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +INSERT INTO t1 VALUES + (1, REPEAT('a',10000), 'a'), + (2, REPEAT('b',20000), 'b'), + (3, REPEAT('c',40000), 'c'), + (4, REPEAT('d',60000), 'd'); + +SELECT CHAR_LENGTH(b) FROM t1; + +ALTER TABLE t1 DROP COLUMN c; + +--source include/restart_mysqld.inc + +CHECK TABLE t1; + +SELECT CHAR_LENGTH(b) FROM t1; + +DROP TABLE t1; + +# Test Crash Recovery + +if ($row_format != 'COMPRESSED') +{ + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') +{ + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +-- disable_query_log +CALL populate_t1(); +-- enable_query_log + +SET debug_dbug='+d,crash_commit_before'; + +# Write file to make mysql-test-run.pl start up the server again +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +--error 2013 +CREATE INDEX idx_title ON t1(title); + +--enable_reconnect +--source include/wait_until_connected_again.inc +--disable_reconnect + +SELECT COUNT(*) FROM t1; + +CHECK TABLE t1; + +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE title = 'a10010'; + +DROP TABLE t1; + +-- echo # Test Blob + +if ($row_format != 'COMPRESSED') { + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') { + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +INSERT INTO t1 VALUES + (1, REPEAT('a',10000), 'a'), + (2, REPEAT('b',20000), 'b'), + (3, REPEAT('c',40000), 'c'), + (4, REPEAT('d',60000), 'd'); + +SELECT CHAR_LENGTH(b) FROM t1; + +SET debug_dbug='+d,crash_commit_before'; + +# Write file to make mysql-test-run.pl start up the server again +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +--error 2013 +ALTER TABLE t1 DROP COLUMN c; + +--enable_reconnect +--source include/wait_until_connected_again.inc +--disable_reconnect + +CHECK TABLE t1; + +SELECT CHAR_LENGTH(b) FROM t1; + +DROP TABLE t1; + +# Restore global variables +if ($row_format == 'COMPRESSED') +{ + SET GLOBAL innodb_file_per_table=default; +} + +DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result b/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result new file mode 100644 index 00000000000..474ebf33bbd --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-on-duplicate-update.result @@ -0,0 +1,60 @@ +set sql_mode=''; +set innodb_strict_mode=0; +CREATE TABLE `v` ( +`id` int(11) unsigned NOT NULL AUTO_INCREMENT, +PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT v values (1); +CREATE TABLE `vp` ( +`id` int(11) unsigned NOT NULL AUTO_INCREMENT, +`v_id` int(10) unsigned NOT NULL, +`p_id` int(10) unsigned NOT NULL, +`ppp` varchar(255) NOT NULL, +PRIMARY KEY (`id`), +UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`), +KEY `FK_vp_v` (`v_id`), +CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT vp VALUES (12, 1, 100, 'text12'); +INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`); +Warnings: +Warning 1364 Field 'v_id' doesn't have a default value +Warning 1364 Field 'p_id' doesn't have a default value +SELECT * FROM vp; +id v_id p_id ppp +12 1 100 test12-2 +DROP TABLE vp, v; +CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB; +INSERT into t1 values (1); +CREATE TABLE t2 ( +i int not null primary key, +vi int not null, +m int, +UNIQUE KEY (vi), +CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT into t2 VALUES (1, 1, 100); +INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3; +Warnings: +Warning 1364 Field 'vi' doesn't have a default value +SELECT * FROM t2; +i vi m +1 1 3 +DROP TABLE t2,t1; +CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB; +INSERT into t1 values (1); +CREATE TABLE t2 ( +i int not null primary key, +vi int not null, +m int, +KEY (vi), +CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; +INSERT into t2 VALUES (1, 1, 100); +INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3; +Warnings: +Warning 1364 Field 'vi' doesn't have a default value +SELECT * FROM t2; +i vi m +1 1 3 +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb/r/innodb-replace-debug.result b/mysql-test/suite/innodb/r/innodb-replace-debug.result new file mode 100644 index 00000000000..84bc9dc9769 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-replace-debug.result @@ -0,0 +1,13 @@ +# +# Bug#17604730 ASSERTION: *CURSOR->INDEX->NAME == TEMP_INDEX_PREFIX +# +create table t1 (f1 int primary key, f2 int, f3 int, unique key k1(f2), +key k2(f3)) engine=innodb; +insert into t1 values (14, 24, 34); +set @@debug_dbug = '+d,row_ins_sec_index_entry_timeout'; +replace into t1 values (14, 25, 34); +select * from t1; +f1 f2 f3 +14 25 34 +drop table t1; +set @@debug_dbug = '-d,row_ins_sec_index_entry_timeout'; diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index.result new file mode 100644 index 00000000000..ec7ce044cb7 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index.result @@ -0,0 +1,1037 @@ +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +IF i%2 = 0 AND load_even = 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +IF i%2 != 0 AND load_even != 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +SET i = i + 1; +END WHILE; +COMMIT; +END| +SELECT @@innodb_fill_factor; +@@innodb_fill_factor +100 +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +SELECT COUNT(*) FROM t1; +COUNT(*) +5000 +/* Create index. */ +CREATE INDEX idx_id ON t1(id); +CREATE INDEX idx_title ON t1(title); +/* Check table. */ +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_id idx_id 5 const 1 +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 4010 AND id > 3990; +INSERT INTO t1 VALUES(4000, 4000, 'b4000'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990; +SELECT * FROM t1 WHERE id = 3000; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE title = 'a3000'; +class id title +SELECT * FROM t1 WHERE title = 'b3000'; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE id = 4000; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE title = 'a4000'; +class id title +SELECT * FROM t1 WHERE title = 'b4000'; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE id = 4001; +class id title +SELECT * FROM t1 WHERE title = 'a4001'; +class id title +SELECT COUNT(*) FROM t1; +COUNT(*) +9992 +/* Add column. */ +ALTER TABLE t1 ADD COLUMN content TEXT; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE title = 'a10'; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE id = 5000; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE title = 'a5000'; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE id = 10000; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE title = 'a10000'; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE id = 10010; +class id title content +SELECT * FROM t1 WHERE title = 'a10010'; +class id title content +/* Drop column. */ +ALTER TABLE t1 DROP COLUMN content; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 ADD INDEX `idx` (a,b(5)); +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +10000 +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +1 +UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +1 +DELETE FROM t1 WHERE a=4975 AND b like 'b4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +DROP TABLE t1; +DROP PROCEDURE populate_t1; +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +IF i%2 = 0 AND load_even = 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +IF i%2 != 0 AND load_even != 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +SET i = i + 1; +END WHILE; +COMMIT; +END| +SELECT @@innodb_fill_factor; +@@innodb_fill_factor +100 +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPACT; +SELECT COUNT(*) FROM t1; +COUNT(*) +5000 +/* Create index. */ +CREATE INDEX idx_id ON t1(id); +CREATE INDEX idx_title ON t1(title); +/* Check table. */ +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_id idx_id 5 const 1 +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 4010 AND id > 3990; +INSERT INTO t1 VALUES(4000, 4000, 'b4000'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990; +SELECT * FROM t1 WHERE id = 3000; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE title = 'a3000'; +class id title +SELECT * FROM t1 WHERE title = 'b3000'; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE id = 4000; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE title = 'a4000'; +class id title +SELECT * FROM t1 WHERE title = 'b4000'; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE id = 4001; +class id title +SELECT * FROM t1 WHERE title = 'a4001'; +class id title +SELECT COUNT(*) FROM t1; +COUNT(*) +9992 +/* Add column. */ +ALTER TABLE t1 ADD COLUMN content TEXT; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE title = 'a10'; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE id = 5000; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE title = 'a5000'; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE id = 10000; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE title = 'a10000'; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE id = 10010; +class id title content +SELECT * FROM t1 WHERE title = 'a10010'; +class id title content +/* Drop column. */ +ALTER TABLE t1 DROP COLUMN content; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 ADD INDEX `idx` (a,b(5)); +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +10000 +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +1 +UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +1 +DELETE FROM t1 WHERE a=4975 AND b like 'b4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +DROP TABLE t1; +DROP PROCEDURE populate_t1; +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +IF i%2 = 0 AND load_even = 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +IF i%2 != 0 AND load_even != 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +SET i = i + 1; +END WHILE; +COMMIT; +END| +SELECT @@innodb_fill_factor; +@@innodb_fill_factor +100 +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +SELECT COUNT(*) FROM t1; +COUNT(*) +5000 +/* Create index. */ +CREATE INDEX idx_id ON t1(id); +CREATE INDEX idx_title ON t1(title); +/* Check table. */ +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_id idx_id 5 const 1 +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 4010 AND id > 3990; +INSERT INTO t1 VALUES(4000, 4000, 'b4000'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990; +SELECT * FROM t1 WHERE id = 3000; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE title = 'a3000'; +class id title +SELECT * FROM t1 WHERE title = 'b3000'; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE id = 4000; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE title = 'a4000'; +class id title +SELECT * FROM t1 WHERE title = 'b4000'; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE id = 4001; +class id title +SELECT * FROM t1 WHERE title = 'a4001'; +class id title +SELECT COUNT(*) FROM t1; +COUNT(*) +9992 +/* Add column. */ +ALTER TABLE t1 ADD COLUMN content TEXT; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE title = 'a10'; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE id = 5000; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE title = 'a5000'; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE id = 10000; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE title = 'a10000'; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE id = 10010; +class id title content +SELECT * FROM t1 WHERE title = 'a10010'; +class id title content +/* Drop column. */ +ALTER TABLE t1 DROP COLUMN content; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 ADD INDEX `idx` (a,b(5)); +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +10000 +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +1 +UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +1 +DELETE FROM t1 WHERE a=4975 AND b like 'b4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +DROP TABLE t1; +DROP PROCEDURE populate_t1; +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +IF i%2 = 0 AND load_even = 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +IF i%2 != 0 AND load_even != 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +SET i = i + 1; +END WHILE; +COMMIT; +END| +SELECT @@innodb_fill_factor; +@@innodb_fill_factor +100 +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SELECT COUNT(*) FROM t1; +COUNT(*) +5000 +/* Create index. */ +CREATE INDEX idx_id ON t1(id); +CREATE INDEX idx_title ON t1(title); +/* Check table. */ +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_id idx_id 5 const 1 +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 4010 AND id > 3990; +INSERT INTO t1 VALUES(4000, 4000, 'b4000'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990; +SELECT * FROM t1 WHERE id = 3000; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE title = 'a3000'; +class id title +SELECT * FROM t1 WHERE title = 'b3000'; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE id = 4000; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE title = 'a4000'; +class id title +SELECT * FROM t1 WHERE title = 'b4000'; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE id = 4001; +class id title +SELECT * FROM t1 WHERE title = 'a4001'; +class id title +SELECT COUNT(*) FROM t1; +COUNT(*) +9992 +/* Add column. */ +ALTER TABLE t1 ADD COLUMN content TEXT; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE title = 'a10'; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE id = 5000; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE title = 'a5000'; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE id = 10000; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE title = 'a10000'; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE id = 10010; +class id title content +SELECT * FROM t1 WHERE title = 'a10010'; +class id title content +/* Drop column. */ +ALTER TABLE t1 DROP COLUMN content; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b BLOB, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +ALTER TABLE t1 ADD INDEX `idx` (a,b(5)); +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +10000 +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +1 +UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +1 +DELETE FROM t1 WHERE a=4975 AND b like 'b4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +DROP TABLE t1; +SET GLOBAL innodb_file_per_table=default; +DROP PROCEDURE populate_t1; +SET GLOBAL innodb_fill_factor=10; +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +IF i%2 = 0 AND load_even = 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +IF i%2 != 0 AND load_even != 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +SET i = i + 1; +END WHILE; +COMMIT; +END| +SELECT @@innodb_fill_factor; +@@innodb_fill_factor +10 +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPACT; +SELECT COUNT(*) FROM t1; +COUNT(*) +5000 +/* Create index. */ +CREATE INDEX idx_id ON t1(id); +CREATE INDEX idx_title ON t1(title); +/* Check table. */ +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_id idx_id 5 const 1 +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 4010 AND id > 3990; +INSERT INTO t1 VALUES(4000, 4000, 'b4000'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990; +SELECT * FROM t1 WHERE id = 3000; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE title = 'a3000'; +class id title +SELECT * FROM t1 WHERE title = 'b3000'; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE id = 4000; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE title = 'a4000'; +class id title +SELECT * FROM t1 WHERE title = 'b4000'; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE id = 4001; +class id title +SELECT * FROM t1 WHERE title = 'a4001'; +class id title +SELECT COUNT(*) FROM t1; +COUNT(*) +9992 +/* Add column. */ +ALTER TABLE t1 ADD COLUMN content TEXT; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE title = 'a10'; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE id = 5000; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE title = 'a5000'; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE id = 10000; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE title = 'a10000'; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE id = 10010; +class id title content +SELECT * FROM t1 WHERE title = 'a10010'; +class id title content +/* Drop column. */ +ALTER TABLE t1 DROP COLUMN content; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 ADD INDEX `idx` (a,b(5)); +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +10000 +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +1 +UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +1 +DELETE FROM t1 WHERE a=4975 AND b like 'b4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +DROP TABLE t1; +DROP PROCEDURE populate_t1; +SET GLOBAL innodb_fill_factor=50; +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +IF i%2 = 0 AND load_even = 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +IF i%2 != 0 AND load_even != 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +SET i = i + 1; +END WHILE; +COMMIT; +END| +SELECT @@innodb_fill_factor; +@@innodb_fill_factor +50 +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPACT; +SELECT COUNT(*) FROM t1; +COUNT(*) +5000 +/* Create index. */ +CREATE INDEX idx_id ON t1(id); +CREATE INDEX idx_title ON t1(title); +/* Check table. */ +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_id idx_id 5 const 1 +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 4010 AND id > 3990; +INSERT INTO t1 VALUES(4000, 4000, 'b4000'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990; +SELECT * FROM t1 WHERE id = 3000; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE title = 'a3000'; +class id title +SELECT * FROM t1 WHERE title = 'b3000'; +class id title +3000 3000 b3000 +SELECT * FROM t1 WHERE id = 4000; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE title = 'a4000'; +class id title +SELECT * FROM t1 WHERE title = 'b4000'; +class id title +4000 4000 b4000 +SELECT * FROM t1 WHERE id = 4001; +class id title +SELECT * FROM t1 WHERE title = 'a4001'; +class id title +SELECT COUNT(*) FROM t1; +COUNT(*) +9992 +/* Add column. */ +ALTER TABLE t1 ADD COLUMN content TEXT; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE title = 'a10'; +class id title content +10 10 a10 NULL +SELECT * FROM t1 WHERE id = 5000; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE title = 'a5000'; +class id title content +5000 5000 a5000 NULL +SELECT * FROM t1 WHERE id = 10000; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE title = 'a10000'; +class id title content +10000 10000 a10000 NULL +SELECT * FROM t1 WHERE id = 10010; +class id title content +SELECT * FROM t1 WHERE title = 'a10010'; +class id title content +/* Drop column. */ +ALTER TABLE t1 DROP COLUMN content; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 5000; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE id = 10000; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE id = 10010; +class id title +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +ALTER TABLE t1 ADD INDEX `idx` (a,b(5)); +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +10000 +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +1 +UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +b=REPEAT(CONCAT('a',4975),2000) +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +1 +DELETE FROM t1 WHERE a=4975 AND b like 'b4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +b=REPEAT(CONCAT('b',4975),2000) +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +DROP TABLE t1; +DROP PROCEDURE populate_t1; +SET GLOBAL innodb_fill_factor=default; diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result new file mode 100644 index 00000000000..cd5a3c340da --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result @@ -0,0 +1,485 @@ +CREATE PROCEDURE populate_t1() +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +SET i = i + 1; +END WHILE; +COMMIT; +END| +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +CREATE INDEX idx_title ON t1(title); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +# Test Blob +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +DROP TABLE t1; +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +SET debug_dbug='+d,crash_commit_before'; +CREATE INDEX idx_title ON t1(title); +ERROR HY000: Lost connection to MySQL server during query +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +# Test Blob +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +SET debug_dbug='+d,crash_commit_before'; +ALTER TABLE t1 DROP COLUMN c; +ERROR HY000: Lost connection to MySQL server during query +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +DROP TABLE t1; +DROP PROCEDURE populate_t1; +CREATE PROCEDURE populate_t1() +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +SET i = i + 1; +END WHILE; +COMMIT; +END| +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPACT; +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +CREATE INDEX idx_title ON t1(title); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +# Test Blob +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +DROP TABLE t1; +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPACT; +SET debug_dbug='+d,crash_commit_before'; +CREATE INDEX idx_title ON t1(title); +ERROR HY000: Lost connection to MySQL server during query +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +# Test Blob +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +SET debug_dbug='+d,crash_commit_before'; +ALTER TABLE t1 DROP COLUMN c; +ERROR HY000: Lost connection to MySQL server during query +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +DROP TABLE t1; +DROP PROCEDURE populate_t1; +CREATE PROCEDURE populate_t1() +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +SET i = i + 1; +END WHILE; +COMMIT; +END| +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +CREATE INDEX idx_title ON t1(title); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +# Test Blob +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +DROP TABLE t1; +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +SET debug_dbug='+d,crash_commit_before'; +CREATE INDEX idx_title ON t1(title); +ERROR HY000: Lost connection to MySQL server during query +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +# Test Blob +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +SET debug_dbug='+d,crash_commit_before'; +ALTER TABLE t1 DROP COLUMN c; +ERROR HY000: Lost connection to MySQL server during query +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +DROP TABLE t1; +DROP PROCEDURE populate_t1; +CREATE PROCEDURE populate_t1() +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +SET i = i + 1; +END WHILE; +COMMIT; +END| +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +CREATE INDEX idx_title ON t1(title); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +# Test Blob +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +DROP TABLE t1; +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SET debug_dbug='+d,crash_commit_before'; +CREATE INDEX idx_title ON t1(title); +ERROR HY000: Lost connection to MySQL server during query +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a5000'; +class id title +5000 5000 a5000 +SELECT * FROM t1 WHERE title = 'a10000'; +class id title +10000 10000 a10000 +SELECT * FROM t1 WHERE title = 'a10010'; +class id title +DROP TABLE t1; +# Test Blob +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +SET debug_dbug='+d,crash_commit_before'; +ALTER TABLE t1 DROP COLUMN c; +ERROR HY000: Lost connection to MySQL server during query +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1; +CHAR_LENGTH(b) +10000 +20000 +40000 +60000 +DROP TABLE t1; +SET GLOBAL innodb_file_per_table=default; +DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index_flush.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index_flush.result new file mode 100644 index 00000000000..d2b812642f6 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index_flush.result @@ -0,0 +1,54 @@ +CREATE PROCEDURE populate_t1() +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 10000) DO +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +SET i = i + 1; +END WHILE; +COMMIT; +END| +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB; +SELECT COUNT(*) FROM t1; +COUNT(*) +10000 +SET @saved_dbug= @@SESSION.debug_dbug; +SET debug_dbug='+d,ib_index_build_fail_before_flush'; +CREATE INDEX idx_id ON t1(id); +ERROR 70100: Query execution was interrupted +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +CREATE INDEX idx_title ON t1(title); +ERROR 70100: Query execution was interrupted +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +CREATE FULLTEXT INDEX fidx_title ON t1(title); +ERROR 70100: Query execution was interrupted +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +ALTER TABLE t1 ADD COLUMN content TEXT, FORCE; +ERROR 70100: Query execution was interrupted +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SET debug_dbug= @saved_dbug; +INSERT INTO t1 VALUES(10001, 10001, 'a10000'); +ALTER TABLE t1 ADD UNIQUE INDEX idx_title(title); +ERROR 23000: Duplicate entry 'a10000' for key 'idx_title' +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +ALTER TABLE t1 ADD UNIQUE INDEX idx_id(id), ADD UNIQUE INDEX idx_title(title); +ERROR 23000: Duplicate entry 'a10000' for key 'idx_title' +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index_replication.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index_replication.result new file mode 100644 index 00000000000..ae050170b4f --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index_replication.result @@ -0,0 +1,222 @@ +include/master-slave.inc +[connection master] +connection master; +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 100) DO +IF i%2 = 0 AND load_even = 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +IF i%2 != 0 AND load_even != 1 THEN +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +END IF; +SET i = i + 1; +END WHILE; +COMMIT; +END| +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ; +SELECT COUNT(*) FROM t1; +COUNT(*) +50 +/* Create index. */ +CREATE INDEX idx_id ON t1(id); +CREATE INDEX idx_title ON t1(title); +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_id idx_id 5 const 1 +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 20; +class id title +20 20 a20 +SELECT * FROM t1 WHERE title = 'a20'; +class id title +20 20 a20 +SELECT * FROM t1 WHERE id = 30; +class id title +30 30 a30 +SELECT * FROM t1 WHERE title = 'a30'; +class id title +30 30 a30 +SELECT * FROM t1 WHERE id = 101; +class id title +SELECT * FROM t1 WHERE title = 'a101'; +class id title +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 40 AND id > 30; +INSERT INTO t1 VALUES(38, 38, 'b38'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 30 AND id > 20; +SELECT * FROM t1 WHERE id = 28; +class id title +28 28 b28 +SELECT * FROM t1 WHERE title = 'a28'; +class id title +SELECT * FROM t1 WHERE title = 'b28'; +class id title +28 28 b28 +SELECT * FROM t1 WHERE id = 38; +class id title +38 38 b38 +SELECT * FROM t1 WHERE title = 'a38'; +class id title +SELECT * FROM t1 WHERE title = 'b38'; +class id title +38 38 b38 +SELECT * FROM t1 WHERE id = 101; +class id title +SELECT * FROM t1 WHERE title = 'a101'; +class id title +SELECT COUNT(*) FROM t1; +COUNT(*) +97 +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 20; +class id title +20 20 a20 +SELECT * FROM t1 WHERE title = 'a20'; +class id title +20 20 a20 +SELECT * FROM t1 WHERE id = 30; +class id title +30 30 a30 +SELECT * FROM t1 WHERE title = 'a30'; +class id title +30 30 a30 +SELECT * FROM t1 WHERE id = 101; +class id title +SELECT * FROM t1 WHERE title = 'a101'; +class id title +CREATE TABLE t_part ( +class INT , +id INT , +title VARCHAR(30) +) ENGINE=InnoDB +PARTITION BY RANGE(id) +SUBPARTITION BY KEY(id) +SUBPARTITIONS 4 +( +PARTITION p0 VALUES LESS THAN (5000), +PARTITION p1 VALUES LESS THAN (MAXVALUE) +); +INSERT INTO t_part SELECT * FROM t1; +ALTER TABLE t_part ADD INDEX `idx` (class,id,title(10)); +SELECT * FROM t_part WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t_part WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t_part WHERE id = 20; +class id title +20 20 a20 +SELECT * FROM t_part WHERE title = 'a20'; +class id title +20 20 a20 +SELECT * FROM t_part WHERE id = 30; +class id title +30 30 a30 +SELECT * FROM t_part WHERE title = 'a30'; +class id title +30 30 a30 +SELECT * FROM t_part WHERE id = 101; +class id title +SELECT * FROM t_part WHERE title = 'a101'; +class id title +include/sync_slave_sql_with_master.inc +connection slave; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `class` int(11) DEFAULT NULL, + `id` int(11) DEFAULT NULL, + `title` varchar(100) DEFAULT NULL, + KEY `idx_id` (`id`), + KEY `idx_title` (`title`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SHOW CREATE TABLE t_part; +Table Create Table +t_part CREATE TABLE `t_part` ( + `class` int(11) DEFAULT NULL, + `id` int(11) DEFAULT NULL, + `title` varchar(30) DEFAULT NULL, + KEY `idx` (`class`,`id`,`title`(10)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY RANGE (`id`) +SUBPARTITION BY KEY (`id`) +SUBPARTITIONS 4 +(PARTITION `p0` VALUES LESS THAN (5000) ENGINE = InnoDB, + PARTITION `p1` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) +SELECT COUNT(*) FROM t1; +COUNT(*) +97 +SELECT COUNT(*) FROM t_part; +COUNT(*) +97 +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 20; +class id title +20 20 a20 +SELECT * FROM t1 WHERE title = 'a20'; +class id title +20 20 a20 +SELECT * FROM t1 WHERE id = 30; +class id title +30 30 a30 +SELECT * FROM t1 WHERE title = 'a30'; +class id title +30 30 a30 +SELECT * FROM t1 WHERE id = 101; +class id title +SELECT * FROM t1 WHERE title = 'a101'; +class id title +SELECT * FROM t_part WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t_part WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t_part WHERE id = 20; +class id title +20 20 a20 +SELECT * FROM t_part WHERE title = 'a20'; +class id title +20 20 a20 +SELECT * FROM t_part WHERE id = 30; +class id title +30 30 a30 +SELECT * FROM t_part WHERE title = 'a30'; +class id title +30 30 a30 +SELECT * FROM t_part WHERE id = 101; +class id title +SELECT * FROM t_part WHERE title = 'a101'; +class id title +connection master; +DROP PROCEDURE populate_t1; +DROP TABLE t1; +DROP TABLE t_part; +include/rpl_end.inc diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index_small.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index_small.result new file mode 100644 index 00000000000..b48207d4497 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index_small.result @@ -0,0 +1,139 @@ +CREATE PROCEDURE populate_t1() +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 1000) DO +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +SET i = i + 1; +END WHILE; +COMMIT; +END| +SELECT @@innodb_fill_factor; +@@innodb_fill_factor +100 +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPACT; +SELECT COUNT(*) FROM t1; +COUNT(*) +1000 +/* Create index. */ +CREATE INDEX idx_id ON t1(id); +CREATE INDEX idx_title ON t1(title); +/* Check table. */ +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_id idx_id 5 const 1 +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 500; +class id title +500 500 a500 +SELECT * FROM t1 WHERE title = 'a500'; +class id title +500 500 a500 +SELECT * FROM t1 WHERE id = 1000; +class id title +1000 1000 a1000 +SELECT * FROM t1 WHERE title = 'a1000'; +class id title +1000 1000 a1000 +SELECT * FROM t1 WHERE id = 1010; +class id title +SELECT * FROM t1 WHERE title = 'a1010'; +class id title +DROP TABLE t1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +DROP TABLE t1; +SET GLOBAL innodb_file_per_table=default; +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +SELECT COUNT(*) FROM t1; +COUNT(*) +1000 +/* Create index. */ +CREATE INDEX idx_id ON t1(id); +CREATE INDEX idx_title ON t1(title); +/* Check table. */ +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_id idx_id 5 const 1 +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx_title idx_title 103 const 1 Using index condition +SELECT * FROM t1 WHERE id = 10; +class id title +10 10 a10 +SELECT * FROM t1 WHERE title = 'a10'; +class id title +10 10 a10 +SELECT * FROM t1 WHERE id = 500; +class id title +500 500 a500 +SELECT * FROM t1 WHERE title = 'a500'; +class id title +500 500 a500 +SELECT * FROM t1 WHERE id = 1000; +class id title +1000 1000 a1000 +SELECT * FROM t1 WHERE title = 'a1000'; +class id title +1000 1000 a1000 +SELECT * FROM t1 WHERE id = 1010; +class id title +SELECT * FROM t1 WHERE title = 'a1010'; +class id title +DROP TABLE t1; +CREATE TABLE t1( +a INT PRIMARY KEY, +b TEXT, +c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; +INSERT INTO t1 VALUES +(1, REPEAT('a',10000), 'a'), +(2, REPEAT('b',20000), 'b'), +(3, REPEAT('c',40000), 'c'), +(4, REPEAT('d',60000), 'd'); +ALTER TABLE t1 DROP COLUMN c; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +CHAR_LENGTH(b) +DROP TABLE t1; +SET GLOBAL innodb_file_per_table=default; +DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/r/truncate_restart.result b/mysql-test/suite/innodb/r/truncate_restart.result new file mode 100644 index 00000000000..169a56a004e --- /dev/null +++ b/mysql-test/suite/innodb/r/truncate_restart.result @@ -0,0 +1,13 @@ +call mtr.add_suppression("InnoDB: Cannot save table statistics for table `test`\\.`t1`: Persistent statistics do not exist"); +SET GLOBAL innodb_stats_persistent= ON; +CREATE TABLE t1 (t TEXT) ENGINE=InnoDB; +connect con1,localhost,root,,test; +SET DEBUG_SYNC='ib_trunc_table_trunc_completing SIGNAL committed WAIT_FOR ever'; +TRUNCATE TABLE t1; +connection default; +SET DEBUG_SYNC='now WAIT_FOR committed'; +disconnect con1; +SELECT COUNT(*) FROM t1; +COUNT(*) +0 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/update_time.result b/mysql-test/suite/innodb/r/update_time.result new file mode 100644 index 00000000000..ab3eeb08dc7 --- /dev/null +++ b/mysql-test/suite/innodb/r/update_time.result @@ -0,0 +1,54 @@ +# +# Test that INFORMATION_SCHEMA.TABLES.UPDATE_TIME is filled +# correctly for InnoDB tables. +# +CREATE TABLE t (a INT) ENGINE=INNODB; +SELECT update_time FROM information_schema.tables WHERE table_name = 't'; +update_time +NULL +INSERT INTO t VALUES (1); +SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 't' +AND update_time IS NOT NULL; +COUNT(*) +1 +# We cant deterministically check that the saved value is correct, but +# at least we check that it is a timestamp not older than 2 minutes. +# Usually update_time and NOW() are equal below, but on heavily loaded +# machines NOW() could be younger. +SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 't' +AND TIMESTAMPDIFF(SECOND, update_time, NOW()) < 120; +COUNT(*) +1 +CREATE TEMPORARY TABLE big (a TEXT) ENGINE=INNODB; +SELECT COUNT(*) FROM information_schema.innodb_buffer_page +WHERE table_name = '`test`.`t`'; +COUNT(*) +1 +# INSERT lots of data in table 'big': begin +# INSERT lots of data in table 'big': end +SELECT COUNT(*) FROM information_schema.innodb_buffer_page +WHERE table_name = '`test`.`t`'; +COUNT(*) +0 +SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 't' +AND update_time IS NOT NULL; +COUNT(*) +1 +DROP TEMPORARY TABLE big; +# Test the behavior after restart with a prepared XA transaction +XA START 'xatrx'; +INSERT INTO t VALUES (5); +XA END 'xatrx'; +XA PREPARE 'xatrx'; +CONNECT con1,localhost,root,,; +call mtr.add_suppression("Found 1 prepared XA transactions"); +FLUSH TABLES; +# Kill and restart +SELECT update_time FROM information_schema.tables WHERE table_name = 't'; +update_time +NULL +XA COMMIT 'xatrx'; +SELECT COUNT(update_time) FROM information_schema.tables WHERE table_name='t'; +COUNT(update_time) +1 +DROP TABLE t; diff --git a/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test b/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test new file mode 100644 index 00000000000..cc80198d24a --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-on-duplicate-update.test @@ -0,0 +1,63 @@ +--source include/have_innodb.inc + +# +# MDEV-13206: INSERT ON DUPLICATE KEY UPDATE foreign key fail +# +set sql_mode=''; +set innodb_strict_mode=0; + +CREATE TABLE `v` ( + `id` int(11) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; + +INSERT v values (1); + +CREATE TABLE `vp` ( + `id` int(11) unsigned NOT NULL AUTO_INCREMENT, + `v_id` int(10) unsigned NOT NULL, + `p_id` int(10) unsigned NOT NULL, + `ppp` varchar(255) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`), + KEY `FK_vp_v` (`v_id`), + CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; + +INSERT vp VALUES (12, 1, 100, 'text12'); +INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`); +SELECT * FROM vp; +DROP TABLE vp, v; + +CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB; +INSERT into t1 values (1); + +CREATE TABLE t2 ( + i int not null primary key, + vi int not null, + m int, + UNIQUE KEY (vi), + CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; + +INSERT into t2 VALUES (1, 1, 100); +INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3; +SELECT * FROM t2; + +DROP TABLE t2,t1; + +CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB; +INSERT into t1 values (1); + +CREATE TABLE t2 ( + i int not null primary key, + vi int not null, + m int, + KEY (vi), + CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; + +INSERT into t2 VALUES (1, 1, 100); +INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3; +SELECT * FROM t2; +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb/t/innodb-replace-debug.test b/mysql-test/suite/innodb/t/innodb-replace-debug.test new file mode 100644 index 00000000000..5cec9e1febf --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-replace-debug.test @@ -0,0 +1,15 @@ +--source include/have_innodb.inc +--source include/have_debug.inc + +--echo # +--echo # Bug#17604730 ASSERTION: *CURSOR->INDEX->NAME == TEMP_INDEX_PREFIX +--echo # + +create table t1 (f1 int primary key, f2 int, f3 int, unique key k1(f2), + key k2(f3)) engine=innodb; +insert into t1 values (14, 24, 34); +set @@debug_dbug = '+d,row_ins_sec_index_entry_timeout'; +replace into t1 values (14, 25, 34); +select * from t1; +drop table t1; +set @@debug_dbug = '-d,row_ins_sec_index_entry_timeout'; diff --git a/mysql-test/suite/innodb/t/innodb_bulk_create_index.test b/mysql-test/suite/innodb/t/innodb_bulk_create_index.test new file mode 100644 index 00000000000..534b4de87f7 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bulk_create_index.test @@ -0,0 +1,46 @@ +######## suite/innodb/t/innodb_bulk_create_index.test ##### +# # +# Testcase for worklog WL#7277: InnoDB: Bulk Load for Create Index # +# The basic idea of bulk load is to build an index from bottom up # +# (also known as sorted index build). # +# Earlier index was create by repeatedly inserting records # +# Test scenario : # +# - Run bulk create index on 10K rows # +# - Run bulk create index on table with various row types # +# - Run DML and SELECT after bulk index creation # +# Creation: # +# 2014-06-19 Implemented this test as part of WL#7277 # +# # +###################################################################### + +-- source include/not_embedded.inc +-- source include/innodb_page_size_small.inc +-- source include/big_test.inc + +# Test Row Format: REDUNDANT. +let $row_format = REDUNDANT; +-- source suite/innodb/include/innodb_bulk_create_index.inc + +# Test Row Format: COMPACT. +let $row_format = COMPACT; +-- source suite/innodb/include/innodb_bulk_create_index.inc + +# Test Row Format: DYNAMIC. +let $row_format = DYNAMIC; +-- source suite/innodb/include/innodb_bulk_create_index.inc + +# Test Row Format: COMPRESSED. +let $row_format = COMPRESSED; +-- source suite/innodb/include/innodb_bulk_create_index.inc + +# Test Fill Factor: 10 +let $row_format = COMPACT; +SET GLOBAL innodb_fill_factor=10; +-- source suite/innodb/include/innodb_bulk_create_index.inc + +# Test Fill Factor: 50 +let $row_format = COMPACT; +SET GLOBAL innodb_fill_factor=50; +-- source suite/innodb/include/innodb_bulk_create_index.inc + +SET GLOBAL innodb_fill_factor=default; diff --git a/mysql-test/suite/innodb/t/innodb_bulk_create_index_debug.test b/mysql-test/suite/innodb/t/innodb_bulk_create_index_debug.test new file mode 100644 index 00000000000..83a12431802 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bulk_create_index_debug.test @@ -0,0 +1,23 @@ +# +# wl#7277: InnoDB: Bulk Load for Create Index +# + +# Test Restart & Crash Recovery. +-- source include/big_test.inc +-- source include/innodb_page_size_small.inc + +# Test Row Format: REDUNDANT. +let $row_format = REDUNDANT; +-- source suite/innodb/include/innodb_bulk_create_index_debug.inc + +# Test Row Format: COMPACT. +let $row_format = COMPACT; +-- source suite/innodb/include/innodb_bulk_create_index_debug.inc + +# Test Row Format: DYNAMIC. +let $row_format = DYNAMIC; +-- source suite/innodb/include/innodb_bulk_create_index_debug.inc + +# Test Row Format: COMPRESSED. +let $row_format = COMPRESSED; +-- source suite/innodb/include/innodb_bulk_create_index_debug.inc diff --git a/mysql-test/suite/innodb/t/innodb_bulk_create_index_flush.test b/mysql-test/suite/innodb/t/innodb_bulk_create_index_flush.test new file mode 100644 index 00000000000..cffca0cc773 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bulk_create_index_flush.test @@ -0,0 +1,75 @@ +# +# Test flush on error in bulk load to make sure we do a proper cleanup. +# Note: We flush all dirty pages before applying any online log in bulk load. +# + +-- source include/have_innodb.inc +-- source include/have_debug.inc + +# Create Insert Procedure +DELIMITER |; +CREATE PROCEDURE populate_t1() +BEGIN + DECLARE i int DEFAULT 1; + + START TRANSACTION; + WHILE (i <= 10000) DO + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + SET i = i + 1; + END WHILE; + COMMIT; +END| +DELIMITER ;| + +CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) +) ENGINE=InnoDB; + +-- disable_query_log +CALL populate_t1(); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +SET @saved_dbug= @@SESSION.debug_dbug; +SET debug_dbug='+d,ib_index_build_fail_before_flush'; + +-- error ER_QUERY_INTERRUPTED +CREATE INDEX idx_id ON t1(id); + +CHECK TABLE t1; + +-- error ER_QUERY_INTERRUPTED +CREATE INDEX idx_title ON t1(title); + +CHECK TABLE t1; + +-- error ER_QUERY_INTERRUPTED +CREATE FULLTEXT INDEX fidx_title ON t1(title); + +CHECK TABLE t1; + +-- error ER_QUERY_INTERRUPTED +ALTER TABLE t1 ADD COLUMN content TEXT, FORCE; + +CHECK TABLE t1; + +SET debug_dbug= @saved_dbug; + +INSERT INTO t1 VALUES(10001, 10001, 'a10000'); + +-- error ER_DUP_ENTRY +ALTER TABLE t1 ADD UNIQUE INDEX idx_title(title); + +CHECK TABLE t1; + +-- error ER_DUP_ENTRY +ALTER TABLE t1 ADD UNIQUE INDEX idx_id(id), ADD UNIQUE INDEX idx_title(title); + +CHECK TABLE t1; + +DROP TABLE t1; + +DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/t/innodb_bulk_create_index_replication.test b/mysql-test/suite/innodb/t/innodb_bulk_create_index_replication.test new file mode 100644 index 00000000000..5b4eaae7557 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bulk_create_index_replication.test @@ -0,0 +1,182 @@ +######## suite/innodb/t/innodb_wl7277_1.test ##### +# # +# Testcase for worklog WL#7277: InnoDB: Bulk Load for Create Index # +# The basic idea of bulk load is to build an index from bottom up # +# (also known as sorted index build). # +# Earlier index was create by repeatedly inserting records # +# Test scenario : # +# - Run bulk create index on replication setup # +# - Run bulk create on partitioned table and see its replictaed # +# to slave # +# Creation: # +# 2014-06-19 Implemented this test as part of WL#7277 # +# # +###################################################################### + +--source include/not_embedded.inc +-- source include/have_innodb.inc +-- source include/have_partition.inc +-- source include/master-slave.inc + +-- connection master +# Create Insert Procedure +DELIMITER |; +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN + DECLARE i int DEFAULT 1; + + START TRANSACTION; + WHILE (i <= 100) DO + IF i%2 = 0 AND load_even = 1 THEN + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + END IF; + IF i%2 != 0 AND load_even != 1 THEN + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + END IF; + SET i = i + 1; + END WHILE; + COMMIT; +END| +DELIMITER ;| + +CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) +) ENGINE=InnoDB ; + + + + +-- disable_query_log +# Load half records +CALL populate_t1(1); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +/* Create index. */ +CREATE INDEX idx_id ON t1(id); + +CREATE INDEX idx_title ON t1(title); + + +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 20; +SELECT * FROM t1 WHERE title = 'a20'; + +SELECT * FROM t1 WHERE id = 30; +SELECT * FROM t1 WHERE title = 'a30'; + +SELECT * FROM t1 WHERE id = 101; +SELECT * FROM t1 WHERE title = 'a101'; + +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 40 AND id > 30; +INSERT INTO t1 VALUES(38, 38, 'b38'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 30 AND id > 20; + +SELECT * FROM t1 WHERE id = 28; +SELECT * FROM t1 WHERE title = 'a28'; +SELECT * FROM t1 WHERE title = 'b28'; + +SELECT * FROM t1 WHERE id = 38; +SELECT * FROM t1 WHERE title = 'a38'; +SELECT * FROM t1 WHERE title = 'b38'; + +SELECT * FROM t1 WHERE id = 101; +SELECT * FROM t1 WHERE title = 'a101'; + +-- disable_query_log +# Load half records (follow up load) +CALL populate_t1(0); +-- enable_query_log +SELECT COUNT(*) FROM t1; + + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 20; +SELECT * FROM t1 WHERE title = 'a20'; + +SELECT * FROM t1 WHERE id = 30; +SELECT * FROM t1 WHERE title = 'a30'; + +SELECT * FROM t1 WHERE id = 101; +SELECT * FROM t1 WHERE title = 'a101'; + +# Create partition table +CREATE TABLE t_part ( + class INT , + id INT , + title VARCHAR(30) + ) ENGINE=InnoDB + PARTITION BY RANGE(id) + SUBPARTITION BY KEY(id) + SUBPARTITIONS 4 + ( + PARTITION p0 VALUES LESS THAN (5000), + PARTITION p1 VALUES LESS THAN (MAXVALUE) + ); +INSERT INTO t_part SELECT * FROM t1; +ALTER TABLE t_part ADD INDEX `idx` (class,id,title(10)); + +SELECT * FROM t_part WHERE id = 10; +SELECT * FROM t_part WHERE title = 'a10'; + +SELECT * FROM t_part WHERE id = 20; +SELECT * FROM t_part WHERE title = 'a20'; + +SELECT * FROM t_part WHERE id = 30; +SELECT * FROM t_part WHERE title = 'a30'; + +SELECT * FROM t_part WHERE id = 101; +SELECT * FROM t_part WHERE title = 'a101'; + + +--source include/sync_slave_sql_with_master.inc +-- connection slave +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t_part; +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t_part; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 20; +SELECT * FROM t1 WHERE title = 'a20'; + +SELECT * FROM t1 WHERE id = 30; +SELECT * FROM t1 WHERE title = 'a30'; + +SELECT * FROM t1 WHERE id = 101; +SELECT * FROM t1 WHERE title = 'a101'; + + + +SELECT * FROM t_part WHERE id = 10; +SELECT * FROM t_part WHERE title = 'a10'; + +SELECT * FROM t_part WHERE id = 20; +SELECT * FROM t_part WHERE title = 'a20'; + +SELECT * FROM t_part WHERE id = 30; +SELECT * FROM t_part WHERE title = 'a30'; + +SELECT * FROM t_part WHERE id = 101; +SELECT * FROM t_part WHERE title = 'a101'; + + +-- connection master +DROP PROCEDURE populate_t1; +DROP TABLE t1; +DROP TABLE t_part; +--source include/rpl_end.inc diff --git a/mysql-test/suite/innodb/t/innodb_bulk_create_index_small.test b/mysql-test/suite/innodb/t/innodb_bulk_create_index_small.test new file mode 100644 index 00000000000..d04dd59f7e7 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bulk_create_index_small.test @@ -0,0 +1,148 @@ +# +# wl#7277: InnoDB: Bulk Load for Create Index +# + +-- source include/innodb_page_size_small.inc + +# Create Insert Procedure +DELIMITER |; +CREATE PROCEDURE populate_t1() +BEGIN + DECLARE i int DEFAULT 1; + + START TRANSACTION; + WHILE (i <= 1000) DO + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + SET i = i + 1; + END WHILE; + COMMIT; +END| +DELIMITER ;| + +SELECT @@innodb_fill_factor; + +# Test Compact Table +CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPACT; + +-- disable_query_log +CALL populate_t1(); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +/* Create index. */ +CREATE INDEX idx_id ON t1(id); + +CREATE INDEX idx_title ON t1(title); + +/* Check table. */ +CHECK TABLE t1; + +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 500; +SELECT * FROM t1 WHERE title = 'a500'; + +SELECT * FROM t1 WHERE id = 1000; +SELECT * FROM t1 WHERE title = 'a1000'; + +SELECT * FROM t1 WHERE id = 1010; +SELECT * FROM t1 WHERE title = 'a1010'; + +DROP TABLE t1; + +# Test Blob +CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPACT; + +INSERT INTO t1 VALUES + (1, REPEAT('a',10000), 'a'), + (2, REPEAT('b',20000), 'b'), + (3, REPEAT('c',40000), 'c'), + (4, REPEAT('d',60000), 'd'); + +ALTER TABLE t1 DROP COLUMN c; + +CHECK TABLE t1; + +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; + +DROP TABLE t1; + +SET GLOBAL innodb_file_per_table=default; + +# Test Compressed Table +SET GLOBAL innodb_file_per_table=1; + +CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) +) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; + +-- disable_query_log +CALL populate_t1(); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +/* Create index. */ +CREATE INDEX idx_id ON t1(id); + +CREATE INDEX idx_title ON t1(title); + +/* Check table. */ +CHECK TABLE t1; + +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 500; +SELECT * FROM t1 WHERE title = 'a500'; + +SELECT * FROM t1 WHERE id = 1000; +SELECT * FROM t1 WHERE title = 'a1000'; + +SELECT * FROM t1 WHERE id = 1010; +SELECT * FROM t1 WHERE title = 'a1010'; + +DROP TABLE t1; + +# Test Compression & Blob +CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; + +INSERT INTO t1 VALUES + (1, REPEAT('a',10000), 'a'), + (2, REPEAT('b',20000), 'b'), + (3, REPEAT('c',40000), 'c'), + (4, REPEAT('d',60000), 'd'); + +ALTER TABLE t1 DROP COLUMN c; + +CHECK TABLE t1; + +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; + +DROP TABLE t1; + +SET GLOBAL innodb_file_per_table=default; + +DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/t/truncate_restart.test b/mysql-test/suite/innodb/t/truncate_restart.test new file mode 100644 index 00000000000..92f09ac89b1 --- /dev/null +++ b/mysql-test/suite/innodb/t/truncate_restart.test @@ -0,0 +1,18 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +call mtr.add_suppression("InnoDB: Cannot save table statistics for table `test`\\.`t1`: Persistent statistics do not exist"); + +SET GLOBAL innodb_stats_persistent= ON; +CREATE TABLE t1 (t TEXT) ENGINE=InnoDB; +--connect (con1,localhost,root,,test) +SET DEBUG_SYNC='ib_trunc_table_trunc_completing SIGNAL committed WAIT_FOR ever'; +--send +TRUNCATE TABLE t1; +--connection default +SET DEBUG_SYNC='now WAIT_FOR committed'; +--source include/restart_mysqld.inc +--disconnect con1 +SELECT COUNT(*) FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/update_time-master.opt b/mysql-test/suite/innodb/t/update_time-master.opt new file mode 100644 index 00000000000..9f283a9503f --- /dev/null +++ b/mysql-test/suite/innodb/t/update_time-master.opt @@ -0,0 +1 @@ +--innodb-buffer-pool-size=10M diff --git a/mysql-test/suite/innodb/t/update_time.test b/mysql-test/suite/innodb/t/update_time.test new file mode 100644 index 00000000000..e2213c5d9b7 --- /dev/null +++ b/mysql-test/suite/innodb/t/update_time.test @@ -0,0 +1,78 @@ +################################################################### +-- echo # +-- echo # Test that INFORMATION_SCHEMA.TABLES.UPDATE_TIME is filled +-- echo # correctly for InnoDB tables. +-- echo # + +-- source include/have_innodb.inc +-- source include/have_innodb_max_16k.inc +# restart does not work with embedded +-- source include/not_embedded.inc + +CREATE TABLE t (a INT) ENGINE=INNODB; + +SELECT update_time FROM information_schema.tables WHERE table_name = 't'; + +INSERT INTO t VALUES (1); + +SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 't' +AND update_time IS NOT NULL; + +-- echo # We cant deterministically check that the saved value is correct, but +-- echo # at least we check that it is a timestamp not older than 2 minutes. +-- echo # Usually update_time and NOW() are equal below, but on heavily loaded +-- echo # machines NOW() could be younger. +SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 't' +AND TIMESTAMPDIFF(SECOND, update_time, NOW()) < 120; + +CREATE TEMPORARY TABLE big (a TEXT) ENGINE=INNODB; + +SELECT COUNT(*) FROM information_schema.innodb_buffer_page +WHERE table_name = '`test`.`t`'; + +# evict table 't' by inserting as much data as the BP size itself +-- echo # INSERT lots of data in table 'big': begin +-- disable_query_log +BEGIN; +-- let $i = 10240 +while ($i) +{ + INSERT INTO big VALUES (REPEAT('a', 1024)); + dec $i; +} +COMMIT; +-- enable_query_log +-- echo # INSERT lots of data in table 'big': end + +# confirm that all pages for table 't' have been evicted +SELECT COUNT(*) FROM information_schema.innodb_buffer_page +WHERE table_name = '`test`.`t`'; + +# The result from this query will change once update_time becomes persistent +# (WL#6917). +SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 't' +AND update_time IS NOT NULL; + +DROP TEMPORARY TABLE big; + +-- echo # Test the behavior after restart with a prepared XA transaction + +XA START 'xatrx'; +INSERT INTO t VALUES (5); +XA END 'xatrx'; +XA PREPARE 'xatrx'; + +CONNECT (con1,localhost,root,,); + +call mtr.add_suppression("Found 1 prepared XA transactions"); +FLUSH TABLES; + +--source include/kill_and_restart_mysqld.inc + +SELECT update_time FROM information_schema.tables WHERE table_name = 't'; + +XA COMMIT 'xatrx'; + +SELECT COUNT(update_time) FROM information_schema.tables WHERE table_name='t'; + +DROP TABLE t; diff --git a/mysql-test/suite/mariabackup/mdev-14447.opt b/mysql-test/suite/mariabackup/mdev-14447.opt new file mode 100644 index 00000000000..5ac67e950c4 --- /dev/null +++ b/mysql-test/suite/mariabackup/mdev-14447.opt @@ -0,0 +1 @@ +--sequence --innodb-data-file-path=ibdata_first:3M;ibdata_second:1M:autoextend
\ No newline at end of file diff --git a/mysql-test/suite/mariabackup/mdev-14447.result b/mysql-test/suite/mariabackup/mdev-14447.result new file mode 100644 index 00000000000..3bca7eb5701 --- /dev/null +++ b/mysql-test/suite/mariabackup/mdev-14447.result @@ -0,0 +1,19 @@ +call mtr.add_suppression("InnoDB: New log files created"); +CREATE TABLE t(a varchar(40) PRIMARY KEY, b varchar(40), c varchar(40), d varchar(40), index(b,c,d)) ENGINE INNODB; +# Create full backup , modify table, then create incremental/differential backup +BEGIN; +INSERT INTO t select uuid(), uuid(), uuid(), uuid() from seq_1_to_100000; +COMMIT; +SELECT count(*) FROM t; +count(*) +100000 +# Prepare full backup, apply incremental one +# Restore and check results +# shutdown server +# remove datadir +# xtrabackup move back +# restart server +SELECT count(*) FROM t; +count(*) +100000 +DROP TABLE t; diff --git a/mysql-test/suite/mariabackup/mdev-14447.test b/mysql-test/suite/mariabackup/mdev-14447.test new file mode 100644 index 00000000000..48f37646231 --- /dev/null +++ b/mysql-test/suite/mariabackup/mdev-14447.test @@ -0,0 +1,46 @@ +call mtr.add_suppression("InnoDB: New log files created"); + +let $basedir=$MYSQLTEST_VARDIR/tmp/backup; +let $incremental_dir=$MYSQLTEST_VARDIR/tmp/backup_inc1; + +CREATE TABLE t(a varchar(40) PRIMARY KEY, b varchar(40), c varchar(40), d varchar(40), index(b,c,d)) ENGINE INNODB; + +echo # Create full backup , modify table, then create incremental/differential backup; +--disable_result_log +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$basedir; +--enable_result_log +BEGIN; +INSERT INTO t select uuid(), uuid(), uuid(), uuid() from seq_1_to_100000; +COMMIT; +SELECT count(*) FROM t; + +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$incremental_dir --incremental-basedir=$basedir; + +--disable_result_log +echo # Prepare full backup, apply incremental one; +exec $XTRABACKUP --prepare --apply-log-only --target-dir=$basedir; +exec $XTRABACKUP --prepare --apply-log-only --target-dir=$basedir --incremental-dir=$incremental_dir ; + +echo # Restore and check results; +let $targetdir=$basedir; +#-- source include/restart_and_restore.inc + +let $_datadir= `SELECT @@datadir`; +let $innodb_data_file_path=`SELECT @@innodb_data_file_path`; +echo # shutdown server; +--source include/shutdown_mysqld.inc +echo # remove datadir; +rmdir $_datadir; +echo # xtrabackup move back; +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --copy-back --datadir=$_datadir "--innodb_data_file_path=$innodb_data_file_path" --target-dir=$targetdir; +echo # restart server; +--source include/start_mysqld.inc + + +--enable_result_log +SELECT count(*) FROM t; +DROP TABLE t; + +# Cleanup +rmdir $basedir; +rmdir $incremental_dir; diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_view.result b/mysql-test/suite/rpl/r/rpl_create_drop_view.result index ebbe9efc9df..436aa7bc236 100644 --- a/mysql-test/suite/rpl/r/rpl_create_drop_view.result +++ b/mysql-test/suite/rpl/r/rpl_create_drop_view.result @@ -99,7 +99,7 @@ DROP VIEW v1; ERROR 42S02: Unknown VIEW: 'test.v1' DROP VIEW IF EXISTS v2; Warnings: -Note 4090 Unknown VIEW: 'test.v2' +Note 4091 Unknown VIEW: 'test.v2' # Syncing slave with master connection slave; SELECT * FROM v1; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_delete_domain.result b/mysql-test/suite/rpl/r/rpl_gtid_delete_domain.result new file mode 100644 index 00000000000..75a22b78a32 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_gtid_delete_domain.result @@ -0,0 +1,82 @@ +include/master-slave.inc +[connection master] +connection master; +SET @@SESSION.gtid_domain_id=0; +CREATE TABLE t (a INT); +connection slave; +connection slave; +call mtr.add_suppression("connecting slave requested to start from.*which is not in the master's binlog"); +include/stop_slave.inc +CHANGE MASTER TO master_use_gtid=slave_pos; +connection master; +SET @@SESSION.gtid_domain_id=11; +SET @@SESSION.server_id=111; +SET @@SESSION.gtid_seq_no=1; +INSERT INTO t SET a=1; +connection slave; +SET @save.gtid_slave_pos=@@global.gtid_slave_pos; +SET @@global.gtid_slave_pos=concat(@@global.gtid_slave_pos, ",", 11, "-", 111, "-", 1 + 1); +Warnings: +Warning 1947 Specified GTID 0-1-1 conflicts with the binary log which contains a more recent GTID 0-2-2. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos +START SLAVE IO_THREAD; +include/wait_for_slave_io_error.inc [errno=1236] +connection master; +FLUSH BINARY LOGS; +PURGE BINARY LOGS TO 'master-bin.000002';; +FLUSH BINARY LOGS DELETE_DOMAIN_ID=(11); +SELECT @@global.gtid_binlog_pos, @@global.gtid_binlog_state; +@@global.gtid_binlog_pos @@global.gtid_binlog_state +0-1-1 0-1-1 +connection slave; +SELECT @@global.gtid_slave_pos; +@@global.gtid_slave_pos +0-1-1,11-111-2 +include/start_slave.inc +==== BEGIN include/start_slave.inc ==== + con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +START SLAVE; +.==== BEGIN include/wait_for_slave_to_start.inc ==== +. con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +..==== BEGIN include/wait_for_slave_io_to_start.inc ==== +.. con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +...==== BEGIN include/wait_for_slave_param.inc [Slave_IO_Running] ==== +... con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +Waiting until 'Slave_IO_Running' = 'Yes' [timeout='300', $slave_error_param='Last_IO_Errno'] +[connection slave] +...==== END include/wait_for_slave_param.inc [Slave_IO_Running] ==== +... con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +[connection slave] +..==== END include/wait_for_slave_io_to_start.inc ==== +.. con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +..==== BEGIN include/wait_for_slave_sql_to_start.inc ==== +.. con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +...==== BEGIN include/wait_for_slave_param.inc [Slave_SQL_Running] ==== +... con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +Waiting until 'Slave_SQL_Running' = 'Yes' [timeout='300', $slave_error_param='1'] +[connection slave] +...==== END include/wait_for_slave_param.inc [Slave_SQL_Running] ==== +... con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +[connection slave] +..==== END include/wait_for_slave_sql_to_start.inc ==== +.. con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +[connection slave] +.==== END include/wait_for_slave_to_start.inc ==== +. con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +[connection slave] +==== END include/start_slave.inc ==== + con='slave' warn='1' qlog='1' rlog='1' aborterr='1' +connection master; +INSERT INTO t SET a=1; +connection slave; +include/wait_for_slave_io_error.inc [errno=1236] +connection master; +FLUSH BINARY LOGS; +PURGE BINARY LOGS TO 'master-bin.000004';; +FLUSH BINARY LOGS DELETE_DOMAIN_ID=(11); +connection slave; +include/start_slave.inc +connection master; +SET @@SESSION.gtid_domain_id=0; +DROP TABLE t; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_sp.result b/mysql-test/suite/rpl/r/rpl_sp.result index 2849e5b7ae3..fbd81cb7146 100644 --- a/mysql-test/suite/rpl/r/rpl_sp.result +++ b/mysql-test/suite/rpl/r/rpl_sp.result @@ -128,7 +128,7 @@ show warnings; Level Code Message Error 1062 Duplicate entry '20' for key 'a' Warning 1196 Some non-transactional changed tables couldn't be rolled back -Note 4092 At line 4 in mysqltest1.foo4 +Note 4093 At line 4 in mysqltest1.foo4 select * from t2; a 20 @@ -291,7 +291,7 @@ end| do fn1(100); Warnings: Error 1062 Duplicate entry '100' for key 'a' -Note 4092 At line 3 in mysqltest1.fn1 +Note 4093 At line 3 in mysqltest1.fn1 Warning 1196 Some non-transactional changed tables couldn't be rolled back select fn1(20); ERROR 23000: Duplicate entry '20' for key 'a' diff --git a/mysql-test/suite/rpl/t/rpl_gtid_delete_domain.test b/mysql-test/suite/rpl/t/rpl_gtid_delete_domain.test new file mode 100644 index 00000000000..622e66c5263 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_gtid_delete_domain.test @@ -0,0 +1,98 @@ +# In case master's gtid binlog state is divergent from the slave's gtid_slave_pos +# slave may not be able to connect. +# For instance when slave is more updated in some of domains, see +# MDEV-12012 as example, the master's state may require adjustment. +# In a specific case of an "old" divergent domain, that is there +# won't be no more event groups from it generated, the states can be +# made compatible with wiping the problematic domain away. After that slave +# becomes connectable. +# +# Notice that the slave applied gtid state is not really required to +# be similarly cleaned in order for replication to flow. +# However this could lead to an expected error when the master +# resumes binlogging of such domain which the test demonstrate. + +--source include/master-slave.inc + +--connection master +# enforce the default domain_id binlogging explicitly +SET @@SESSION.gtid_domain_id=0; +CREATE TABLE t (a INT); +--sync_slave_with_master + +--connection slave +call mtr.add_suppression("connecting slave requested to start from.*which is not in the master's binlog"); + +--source include/stop_slave.inc +CHANGE MASTER TO master_use_gtid=slave_pos; + +--connection master +# create extra gtid domains for binlog state +--let $extra_domain_id=11 +--let $extra_domain_server_id=111 +--let $extra_gtid_seq_no=1 +--eval SET @@SESSION.gtid_domain_id=$extra_domain_id +--eval SET @@SESSION.server_id=$extra_domain_server_id +--eval SET @@SESSION.gtid_seq_no=$extra_gtid_seq_no +INSERT INTO t SET a=1; + +# +# Set up the slave replication state as if slave knows more events from the extra +# domain. +# +--connection slave +SET @save.gtid_slave_pos=@@global.gtid_slave_pos; +--eval SET @@global.gtid_slave_pos=concat(@@global.gtid_slave_pos, ",", $extra_domain_id, "-", $extra_domain_server_id, "-", $extra_gtid_seq_no + 1) + +# unsuccessful attempt to start slave +START SLAVE IO_THREAD; +--let $slave_io_errno=1236 +--source include/wait_for_slave_io_error.inc + +--connection master +# adjust the master binlog state +FLUSH BINARY LOGS; +--let $purge_to_binlog= query_get_value(SHOW MASTER STATUS, File, 1) +--eval PURGE BINARY LOGS TO '$purge_to_binlog'; +# with final removal of the extra domain +--eval FLUSH BINARY LOGS DELETE_DOMAIN_ID=($extra_domain_id) +SELECT @@global.gtid_binlog_pos, @@global.gtid_binlog_state; +--connection slave +SELECT @@global.gtid_slave_pos; +# start the slave sucessfully +--let rpl_debug=1 +--source include/start_slave.inc +--let rpl_debug=0 + +--connection master +# but the following gtid from the *extra* domain will break replication +INSERT INTO t SET a=1; + +# take note of the slave io thread error due to being dismissed +# extra domain at connection to master which tried becoming active; +# slave is to stop. +--connection slave +--let $errno=1236 +--source include/wait_for_slave_io_error.inc + +# let's apply the very same medicine +--connection master +FLUSH BINARY LOGS; +--let $purge_to_binlog= query_get_value(SHOW MASTER STATUS, File, 1) +--eval PURGE BINARY LOGS TO '$purge_to_binlog'; +# with final removal of the extra domain +--eval FLUSH BINARY LOGS DELETE_DOMAIN_ID=($extra_domain_id) + +--connection slave +--source include/start_slave.inc + +# +# cleanup +# +--connection master +SET @@SESSION.gtid_domain_id=0; +DROP TABLE t; + +sync_slave_with_master; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result index 0400843c98a..a1789f02718 100644 --- a/mysql-test/suite/sql_sequence/alter.result +++ b/mysql-test/suite/sql_sequence/alter.result @@ -212,7 +212,7 @@ ERROR 42S02: 'test.t1' is not a SEQUENCE drop table t1; alter sequence if exists t1 minvalue=100; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t1' +Note 4090 Unknown SEQUENCE: 'test.t1' alter sequence t1 minvalue=100; ERROR 42S02: Table 'test.t1' doesn't exist create sequence t1; diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result index 0a44dfe8931..b9394da6fcd 100644 --- a/mysql-test/suite/sql_sequence/create.result +++ b/mysql-test/suite/sql_sequence/create.result @@ -165,7 +165,7 @@ drop sequence t1; ERROR 42S02: 'test.t1' is not a SEQUENCE drop sequence if exists t1; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t1' +Note 4090 Unknown SEQUENCE: 'test.t1' create sequence t1 start with 10 maxvalue=9; ERROR HY000: Sequence 'test.t1' values are conflicting create sequence t1 minvalue= 100 maxvalue=10; @@ -377,7 +377,7 @@ key key1 (next_not_cached_value) ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any keys) drop sequence if exists t1; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t1' +Note 4090 Unknown SEQUENCE: 'test.t1' create sequence t1; create sequence t2; create table t3 (a int) engine=myisam; @@ -387,8 +387,8 @@ CREATE SEQUENCE s1; drop sequence s1; drop sequence if exists t1,t2,t3,t4; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t3' -Note 4089 Unknown SEQUENCE: 'test.t4' +Note 4090 Unknown SEQUENCE: 'test.t3' +Note 4090 Unknown SEQUENCE: 'test.t4' drop table if exists t1,t2,t3; Warnings: Note 1051 Unknown table 'test.t1' @@ -414,9 +414,9 @@ CREATE TABLE t2 (a int); CREATE SEQUENCE s1; drop sequence if exists t1,t2,s1,s2; Warnings: -Note 4089 Unknown SEQUENCE: 'test.t1' -Note 4089 Unknown SEQUENCE: 'test.t2' -Note 4089 Unknown SEQUENCE: 'test.s2' +Note 4090 Unknown SEQUENCE: 'test.t1' +Note 4090 Unknown SEQUENCE: 'test.t2' +Note 4090 Unknown SEQUENCE: 'test.s2' drop table if exists t1,t2; CREATE TEMPORARY SEQUENCE s1; DROP SEQUENCE s1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 17e8be839a8..b514dbb5b2d 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -587,3 +587,140 @@ SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; SET sql_mode=DEFAULT; + + +--echo # +--echo # MDEV-14376 Explicit CAST(CHAR(N)) erroneously escalates warnings to errors in STRICT_ALL_TABLES +--echo # + +SET sql_mode=STRICT_ALL_TABLES; +SELECT CAST('xxx' AS CHAR(1)); + +CREATE OR REPLACE TABLE t1 (a VARCHAR(1)); +INSERT INTO t1 VALUES (CAST('xxx' AS CHAR(1))); +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(3)); +INSERT INTO t1 VALUES ('xxx'); +UPDATE t1 SET a=CAST(a AS CHAR(1)); +DROP TABLE t1; + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a VARCHAR(30) CHARACTER SET latin1; + SET a=CAST('xxx' AS CHAR(1)); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a VARCHAR(30) CHARACTER SET latin1; + SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a VARCHAR(30) CHARACTER SET latin1; + SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a VARCHAR(30) CHARACTER SET utf8; + SET a=CAST('xxx' AS CHAR(1)); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a VARCHAR(30) CHARACTER SET utf8; + SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET latin1); +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE a VARCHAR(30) CHARACTER SET utf8; + SET a=CAST(_latin1'xxx' AS CHAR(1) CHARACTER SET utf8); +END; +$$ +DELIMITER ;$$ + + +--echo # Conversion problems still escalate warnings to errors (without right truncation) + +DELIMITER $$; +--error ER_CANNOT_CONVERT_CHARACTER +BEGIN NOT ATOMIC + DECLARE a VARCHAR(30) CHARACTER SET utf8; + SET a=CAST(_utf8 0xD18F AS CHAR(1) CHARACTER SET latin1); +END; +$$ +DELIMITER ;$$ + + +--echo # Conversion problems still escalate warnings to errors (with right truncation) + +DELIMITER $$; +--error ER_CANNOT_CONVERT_CHARACTER +BEGIN NOT ATOMIC + DECLARE a VARCHAR(30) CHARACTER SET utf8; + SET a=CAST(_utf8 0xD18FD18F AS CHAR(1) CHARACTER SET latin1); +END; +$$ +DELIMITER ;$$ + + +--echo # CAST(number AS CHAR) escalates warnings to errors on truncation + +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (CAST(123 AS CHAR(1))); +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('1'); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET a=CAST(123 AS CHAR(1)); +DROP TABLE t1; + +DELIMITER $$; +--error ER_TRUNCATED_WRONG_VALUE +BEGIN NOT ATOMIC + DECLARE a VARCHAR(10); + SET a=CAST(123 AS CHAR(1)); +END; +$$ +DELIMITER ;$$ + + +--echo # CAST(temporal AS CHAR) escalates warnings to errors on truncation + +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (CAST(TIME'10:20:30' AS CHAR(1))); +DROP TABLE t1; + +CREATE OR REPLACE TABLE t1 (a VARCHAR(10)); +INSERT INTO t1 VALUES ('1'); +--error ER_TRUNCATED_WRONG_VALUE +UPDATE t1 SET a=CAST(TIME'10:20:30' AS CHAR(1)); +DROP TABLE t1; + +DELIMITER $$; +--error ER_TRUNCATED_WRONG_VALUE +BEGIN NOT ATOMIC + DECLARE a VARCHAR(10); + SET a=CAST(TIME'10:20:30' AS CHAR(1)); +END; +$$ +DELIMITER ;$$ + +SET sql_mode=DEFAULT; diff --git a/mysql-test/t/cte_grant.test b/mysql-test/t/cte_grant.test index 44fd4a0bc6e..c6627c05829 100644 --- a/mysql-test/t/cte_grant.test +++ b/mysql-test/t/cte_grant.test @@ -76,4 +76,55 @@ select * from mysqltest.v3; connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; drop user mysqltest_1@localhost; -drop database mysqltest;
\ No newline at end of file +drop database mysqltest; + +--echo # +--echo # MDEV-13453: privileges checking for CTE +--echo # + +create database db; +use db; +create table t1 (i int); +insert into t1 + values (3), (7), (1), (4), (2), (3), (1); + +create table t2 (a int, b int); +insert into t2 + values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15); + +create user foo@localhost; +grant SELECT on db.t1 to foo@localhost; +grant SELECT(a) on db.t2 to foo@localhost; + +--connect (con1,localhost,foo,,) +use db; +with cte as (select * from t1 where i < 4) + select * from cte; +with cte as (select * from t1 where i < 4 group by i) + select * from cte; +with cte as (select * from t1 where i < 4) + select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; +with cte as (select * from t1 where i < 4 group by i) + select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; + +--error ER_COLUMNACCESS_DENIED_ERROR +with cte as (select b from t2 where a < 4) + select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15; +with cte as (select a from t2 where a < 4) + select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2; + +--connection default +revoke SELECT on db.t1 from foo@localhost; + +--connection con1 + +--error ER_TABLEACCESS_DENIED_ERROR +with cte as (select * from t1 where i < 4) + select * from cte; + +# Cleanup +--disconnect con1 + +--connection default +drop database db; +drop user foo@localhost; diff --git a/mysql-test/t/func_json.test b/mysql-test/t/func_json.test index 47ed0c3ca75..5a5d06d456d 100644 --- a/mysql-test/t/func_json.test +++ b/mysql-test/t/func_json.test @@ -9,6 +9,7 @@ select json_value('{"key1":123}', '$.key2'); select json_value('{"key1":123}', '$.key1'); select json_value('{"key1":[1,2,3]}', '$.key1'); select json_value('{"key1": [1,2,3], "key1":123}', '$.key1'); +select JSON_VALUE('{ "x": [0,1], "y": "[0,1]", "z": "Mon\\\"t\\\"y" }','$.z'); select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key2'); select json_query('{"key1":{"a":1, "b":[1,2]}}', '$.key1'); @@ -377,6 +378,11 @@ SELECT JSON_OBJECT("user","Jožko Mrkvičká") as json_data; select json_contains_path('{"foo":"bar"}', 'one', '$[]'); +# +# MDEV-13971 crash in skip_num_constant. +# +select JSON_VALID(0x36f0c8dccd83c5eac156da); + --echo # --echo # Start of 10.3 tests --echo # diff --git a/mysql-test/t/gis-json.test b/mysql-test/t/gis-json.test index 5e695fbca9c..b91ef235fd0 100644 --- a/mysql-test/t/gis-json.test +++ b/mysql-test/t/gis-json.test @@ -40,6 +40,10 @@ SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(5.363 7.266)'),10); SELECT ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 1); SELECT ST_AsGeoJSON(ST_GeomFromText("POINT(10 11)"), 100, 5); +SELECT st_astext(st_geomfromgeojson('{"type": "MultiLineString","coordinates": []}')) as a; +SELECT st_astext(st_geomfromgeojson('{"type": "Polygon","coordinates": []}')) as a; +SELECT st_astext(st_geomfromgeojson('{"type": "MultiPolygon","coordinates": []}')) as a; + --echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/t/intersect.test b/mysql-test/t/intersect.test index 99a54606291..98b6d1eb1c6 100644 --- a/mysql-test/t/intersect.test +++ b/mysql-test/t/intersect.test @@ -190,4 +190,19 @@ show create view v1; drop view v1; drop tables t1,t2,t3; +--echo # +--echo # MDEV-14346:incorrect result of intersect with ANY/ALL/IN subquery +--echo # +CREATE TABLE t (i INT); +INSERT INTO t VALUES (1),(2); +SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT SELECT 3 ); + +select i from t where + exists ((select 6 as r from dual having t.i <> 6) + intersect + (select 3 from dual having t.i <> 3)); + +drop table t; + + --echo # End of 10.3 tests diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index e5bc36f6851..6988b2a8e9b 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -589,3 +589,9 @@ eval SET GLOBAL SERVER_ID = $old_server_id; --exec $MYSQL_BINLOG --hexdump std_data/mdev-4645-binlog_group_id.binlog --exec $MYSQL_BINLOG --hexdump std_data/mdev-4645-binlog_group_id_checksum.binlog --exec $MYSQL_BINLOG --hexdump std_data/mdev-4645-binlog_none.binlog + +# +# MDEV-12372 mysqlbinlog --version output is the same on 10.x as on 5.5.x, and contains not only version +# +replace_regex /.*mysqlbinlog(\.exe)? Ver .* for .* at [-_a-zA-Z0-9]+/mysqlbinlog Ver VER for OS at ARCH/; +exec $MYSQL_BINLOG --version; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 8722401ccae..914911648b2 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -2106,3 +2106,46 @@ INSERT INTO t1 VALUES (1),(2),(3); SELECT DISTINCT pk FROM t1 GROUP BY 'foo'; SELECT DISTINCT pk FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-13994: Bad join results with orderby_uses_equalities=on +--echo # + +CREATE TABLE books ( + id int(16) NOT NULL AUTO_INCREMENT, + library_id int(16) NOT NULL DEFAULT 0, + wings_id int(12) NOT NULL DEFAULT 0, + scheduled_for_removal int(1) DEFAULT 0, + PRIMARY KEY (id), + KEY library_idx (library_id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; + +INSERT INTO books VALUES (32625,8663,707,0),(32624,8663,505,1); + +CREATE TABLE wings ( + id int(11) NOT NULL AUTO_INCREMENT, + department_id int(11) DEFAULT NULL, + PRIMARY KEY (id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; + +INSERT INTO wings VALUES (505,11745),(707,11768); + +let $q= +SELECT wings.id as wing_id, wings.department_id FROM wings + WHERE wings.id IN ( SELECT books.wings_id FROM books + WHERE books.library_id = 8663 AND + books.scheduled_for_removal=0 ) +ORDER BY wings.id; + +SET @save_optimizer_switch=@@optimizer_switch; + +SET optimizer_switch='orderby_uses_equalities=off'; +eval $q; + +SET optimizer_switch='orderby_uses_equalities=on'; +eval $q; +eval explain extended $q; + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE books, wings; diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index 097eddd24f1..0debb777749 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -61,3 +61,50 @@ from t1 where key1<3 or key2<3; drop table t0, t1; + +--echo # +--echo # MDEV-14071: wrong results with orderby_uses_equalities=on +--echo # (duplicate of MDEV-13994) +--echo # + +CREATE TABLE t1 (i int, j int, z int,PRIMARY KEY (i,j), KEY (z)) ENGINE=InnoDB; +CREATE TABLE t2 (i int, j int, PRIMARY KEY (i,j)) ENGINE=InnoDB; +CREATE TABLE t3 (j int, n varchar(5), PRIMARY KEY (j)) ENGINE=InnoDB; + +INSERT INTO t1 VALUES + (127,0,1),(188,0,1),(206,0,1),(218,0,1),(292,0,1),(338,0,1),(375,0,1), + (381,0,1),(409,0,1),(466,0,1),(469,0,1),(498,0,1),(656,0,1); +INSERT INTO t1 VALUES + (77,4,0),(86,7,0),(96,6,0),(96,7,0),(99,9,0),(99,10,0),(99,11,0),(104,4,0), + (106,5,0),(148,6,0),(177,6,0),(181,5,0),(188,8,0),(218,8,0),(253,7,0), + (268,4,0),(338,4,0),(409,7,0),(466,8,0),(469,8,0),(498,8,0),(656,8,0); + +INSERT INTO t2 VALUES + (127,7),(188,8),(188,9),(206,6),(218,8),(218,9),(292,7),(338,4),(338,5), + (375,6),(381,5),(409,7),(409,8),(466,8),(466,9),(469,8),(469,9),(498,8), + (498,9),(656,8),(656,9); +INSERT INTO t3 VALUES + (4,'four'),(5,'five'),(6,'six'),(7,'seven'),(8,'eight'),(9,'nine'); + +let $q1= +SELECT i,n +FROM t1 INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE i IN (SELECT i FROM t1 WHERE z=1) AND z=0 ORDER BY i; +let $q2= +SELECT i,n +FROM t1 x INNER JOIN t2 USING (i,j) LEFT JOIN t3 USING (j) +WHERE EXISTS (SELECT * FROM t1 WHERE i=x.i AND z=1) AND z=0 ORDER BY i; + +SET @save_optimizer_switch=@@optimizer_switch; + +SET optimizer_switch='orderby_uses_equalities=off'; +eval $q1; +eval $q2; + +SET optimizer_switch='orderby_uses_equalities=on'; +eval $q1; +eval $q2; + +set optimizer_switch= @save_optimizer_switch; + +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index ec55b9c9d21..2a16a807542 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -4476,3 +4476,94 @@ EXECUTE IMMEDIATE 'CALL p1(?)' USING DEFAULT; --error ER_SP_NOT_VAR_ARG EXECUTE IMMEDIATE 'CALL p1(?)' USING IGNORE; DROP PROCEDURE p1; + + +--echo # +--echo # MDEV-14434 Wrong result for CHARSET(CONCAT(?,const)) +--echo # + +SET NAMES utf8; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(5,_latin1'a'))"; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING 5.5e0; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIME'10:20:30'; +EXECUTE IMMEDIATE "SELECT CHARSET(CONCAT(?,_latin1'a'))" USING TIMESTAMP'2001-01-01 10:20:30'; + +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING 5.5e0; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIME'10:20:30'; +EXECUTE IMMEDIATE "SELECT COERCIBILITY(?)" USING TIMESTAMP'2001-01-01 10:20:30'; + +--echo # +--echo # MDEV-14435 Different UNSIGNED flag of out user variable for YEAR parameter for direct vs prepared CALL +--echo # + +CREATE PROCEDURE p1(OUT v INT UNSIGNED) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +CREATE PROCEDURE p1(OUT v YEAR) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +CREATE PROCEDURE p1(OUT v BIT(16)) SET v = 2010; +CALL p1(@a); +PREPARE stmt FROM 'CALL p1(?)'; +EXECUTE stmt USING @b; +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 AS SELECT @a AS a, @b AS b; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +--echo # +--echo # MDEV-14454 Binary protocol returns wrong collation ID for SP OUT parameters +--echo # + +CREATE PROCEDURE p1(OUT v CHAR(32) CHARACTER SET utf8) SET v='aaa'; +PREPARE stmt1 FROM 'CALL p1(?)'; +EXECUTE stmt1 USING @a; +CREATE TABLE t1 AS SELECT @a AS c1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + +--echo # +--echo # MDEV-14467 Item_param: replace {INT|DECIMAL|REAL|STRING|TIME}_VALUE with Type_handler +--echo # + +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.1; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING 10.1e0; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING '10'; +EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT 1 FROM DUAL LIMIT ?' USING TIME'10:10:10'; + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 1 AS a,? AS b' USING 1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 10 AS a,? AS b' USING 10; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 999999999 AS a,? AS b' USING 999999999; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE t1 AS SELECT 2147483647 AS a,? AS b' USING 2147483647; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test index 29b6764ebc6..1f2f5191f0a 100644 --- a/mysql-test/t/sp-code.test +++ b/mysql-test/t/sp-code.test @@ -758,3 +758,172 @@ DELIMITER ;$$ SHOW PROCEDURE CODE p1; DROP PROCEDURE p1; DROP TABLE t1; + +--echo # +--echo # MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT +--echo # + +--echo # Integer range FOR loop + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + FOR i IN 1..3 + DO + SELECT i; + END FOR; +END; +$$ +DELIMITER ;$$ +CALL p1; +SHOW PROCEDURE CODE p1; +DROP PROCEDURE p1; + + +--echo # Nested integer range FOR loops + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + fori: + FOR i IN 1..3 + DO + forj: + FOR j IN 1..3 + DO + IF i = 3 THEN + LEAVE fori; + END IF; + IF j = 3 THEN + LEAVE forj; + END IF; + SELECT i,j; + END FOR; + END FOR; +END; +$$ +DELIMITER ;$$ +CALL p1; +SHOW PROCEDURE CODE p1; +DROP PROCEDURE p1; + + +--echo # Explicit cursor FOR loops + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b; + DECLARE cur1 CURSOR FOR SELECT 10 AS a, 'b0' AS b; + DECLARE cur2 CURSOR FOR SELECT 10 AS a, 'b0' AS b; + FOR rec1 IN cur1 + DO + SELECT rec1.a, rec1.b; + SET rec1.a= 11; + SET rec1.b= 'b1'; + SELECT rec1.a, rec1.b; + END FOR; + FOR rec0 IN cur0 + DO + SET rec0.a= 10; + SET rec0.b='b0'; + END FOR; + FOR rec2 IN cur2 + DO + SET rec2.a= 10; + SET rec2.b='b0'; + END FOR; +END; +$$ +DELIMITER ;$$ +SHOW PROCEDURE CODE p1; +DROP PROCEDURE p1; + + +--echo # Nested explicit cursor FOR loops + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b; + FOR rec0 IN cur0 + DO + BEGIN + DECLARE cur1 CURSOR FOR SELECT 11 AS a, 'b1' AS b; + SET rec0.a= 11; + SET rec0.b= 'b0'; + FOR rec1 IN cur1 + DO + SET rec1.a= 11; + SET rec1.b= 'b1'; + BEGIN + DECLARE cur2 CURSOR FOR SELECT 12 AS a, 'b2' AS b; + FOR rec2 IN cur2 + DO + SET rec2.a=12; + SET rec2.b='b2'; + END FOR; + END; + END FOR; + END; + END FOR; +END; +$$ +DELIMITER ;$$ +SHOW PROCEDURE CODE p1; +DROP PROCEDURE p1; + + +--echo # Implicit cursor FOR loops + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + FOR rec1 IN (SELECT 11 AS a, 'b1' AS b) + DO + SELECT rec1.a, rec1.b; + SET rec1.a= 11; + SET rec1.b= 'b1'; + SELECT rec1.a, rec1.b; + END FOR; + FOR rec0 IN (SELECT 10 AS a, 'b0' AS b) + DO + SET rec0.a= 10; + SET rec0.b='b0'; + END FOR; + FOR rec2 IN (SELECT 12 AS a, 'b2' AS b) + DO + SET rec2.a= 10; + SET rec2.b='b0'; + END FOR; +END; +$$ +DELIMITER ;$$ +SHOW PROCEDURE CODE p1; +DROP PROCEDURE p1; + +--echo # Nested implicit cursor FOR loops + +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + FOR rec0 IN (SELECT 10 AS a, 'b0' AS b) + DO + SET rec0.a= 11; + SET rec0.b= 'b0'; + FOR rec1 IN (SELECT 11 AS a, 'b1' AS b) + DO + SET rec1.a= 11; + SET rec1.b= 'b1'; + FOR rec2 IN (SELECT 12 AS a, 'b2' AS b) + DO + SET rec2.a=12; + SET rec2.b='b2'; + END FOR; + END FOR; + END FOR; +END; +$$ +DELIMITER ;$$ +SHOW PROCEDURE CODE p1; +DROP PROCEDURE p1; diff --git a/mysql-test/t/sp-cursor.test b/mysql-test/t/sp-cursor.test index 394dc56556a..2e7a72cf8d0 100644 --- a/mysql-test/t/sp-cursor.test +++ b/mysql-test/t/sp-cursor.test @@ -474,3 +474,136 @@ DROP PROCEDURE p1; --echo # --echo # End of MDEV-12457 Cursors with parameters --echo # + + +--echo # +--echo # MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT +--echo # + +--echo # Explicit cursor + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3'); +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE cur CURSOR FOR SELECT * FROM t1; + FOR rec IN cur + DO + SELECT rec.a AS a, rec.b AS b; + END FOR; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + +--echo # Explicit cursor with parameters + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3'); +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE cur CURSOR(pa INT) FOR SELECT * FROM t1 WHERE a>=pa; + FOR rec IN cur(2) + DO + SELECT rec.a AS a, rec.b AS b; + END FOR; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + +--echo # Explicit cursor + label + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE cur CURSOR FOR SELECT * FROM t1; + forrec: + FOR rec IN cur + DO + SELECT rec.a AS a, rec.b AS b; + IF rec.a = 2 THEN + LEAVE forrec; + END IF; + END FOR forrec; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + +--echo # Explicit cursor + FETCH inside the loop body produce an error on "NOT FOUND" + +DELIMITER $$; +--error ER_SP_FETCH_NO_DATA +BEGIN NOT ATOMIC + DECLARE x INT; + DECLARE cur CURSOR FOR SELECT 1 AS x; + FOR rec IN cur + DO + FETCH cur INTO x; + END FOR; +END; +$$ +DELIMITER ;$$ + + +--echo # Explicit cursor + FETCH inside the loop body are normally handled by "HANDLER FOR NOT FOUND" + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE done INT DEFAULT 0; + DECLARE cur CURSOR FOR SELECT 1 AS x, 'y1' AS y UNION + SELECT 2,'y2' UNION + SELECT 3,'y3'; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; + forrec: + FOR rec IN cur + DO + SELECT CONCAT(rec.x, ' ', rec.y) AS 'Implicit FETCH'; + FETCH cur INTO rec; + IF done THEN + SELECT 'NO DATA' AS `Explicit FETCH`; + LEAVE forrec; + ELSE + SELECT CONCAT(rec.x, ' ', rec.y) AS 'Explicit FETCH'; + END IF; + END FOR; +END; +$$ +DELIMITER ;$$ + + +--echo # Implicit cursor + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); +DELIMITER $$; +BEGIN NOT ATOMIC + FOR rec IN (SELECT * FROM t1) + DO + SELECT rec.a AS a, rec.b AS b; + END FOR; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + +--echo # Implicit cursor + label + +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 VALUES ('1','b1'), ('2','b2'); +DELIMITER $$; +BEGIN NOT ATOMIC + forrec: + FOR rec IN (SELECT * FROM t1) + DO + SELECT rec.a AS a, rec.b AS b; + IF rec.a = 2 THEN + LEAVE forrec; + END IF; + END FOR; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; diff --git a/mysql-test/t/sp-for-loop.test b/mysql-test/t/sp-for-loop.test new file mode 100644 index 00000000000..6350e9fb9d3 --- /dev/null +++ b/mysql-test/t/sp-for-loop.test @@ -0,0 +1,212 @@ +--echo # +--echo # MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT +--echo # + + +CREATE TABLE t1 (a INT); +DELIMITER /; +FOR i IN 1..3 +DO + INSERT INTO t1 VALUES (i); +END FOR; +/ +DELIMITER ;/ +SELECT * FROM t1; +DROP TABLE t1; + + +# Dots must have no delimiters in between + +DELIMITER /; +--error ER_PARSE_ERROR +CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURNS INT +BEGIN + DECLARE total INT DEFAULT 0; + FOR i IN lower_bound . . upper_bound + DO + NULL + END FOR; + RETURN total; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURNS INT +BEGIN + DECLARE total INT DEFAULT 0; + lab: + FOR i IN lower_bound .. upper_bound + DO + SET total= total + i; + IF i = lim THEN + LEAVE lab; + END IF; + -- Bounds are calculated only once. + -- The below assignments have no effect on the loop condition + SET lower_bound= 900; + SET upper_bound= 1000; + END FOR; + RETURN total; +END; +/ +DELIMITER ;/ +SELECT f1(1, 3, 100) FROM DUAL; +SELECT f1(1, 3, 2) FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +CREATE FUNCTION f1() RETURNS INT +BEGIN + DECLARE total INT DEFAULT 0; + FOR i IN 1 .. 5 + DO + SET total= total + 1000; + forj: + FOR j IN 1 .. 5 + DO + SET total= total + 1; + IF j = 3 THEN + LEAVE forj; -- End the internal loop + END IF; + END FOR; + END FOR; + RETURN total; +END; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +CREATE FUNCTION f1 (a INT, b INT) RETURNS INT +BEGIN + DECLARE total INT DEFAULT 0; + fori: + FOR i IN REVERSE a..1 + DO + SET total= total + i; + IF i = b THEN + LEAVE fori; + END IF; + END FOR; + RETURN total; +END +/ +DELIMITER ;/ +SELECT f1(3, 100) FROM DUAL; +SELECT f1(3, 2) FROM DUAL; +DROP FUNCTION f1; + + +--echo # Testing labeled FOR LOOP statement + +DELIMITER /; +CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURNS INT +BEGIN + DECLARE total INT DEFAULT 0; + la: + FOR ia IN 1 .. a + DO + SET total= total + 1000; + lb: + FOR ib IN 1 .. b + DO + SET total= total + 1; + IF ib = limitb THEN + LEAVE lb; + END IF; + IF ia = limita THEN + LEAVE la; + END IF; + END FOR lb; + END FOR la; + RETURN total; +END; +/ +DELIMITER ;/ +SELECT f1(1, 1, 1, 1) FROM DUAL; +SELECT f1(1, 2, 1, 2) FROM DUAL; +SELECT f1(2, 1, 2, 1) FROM DUAL; +SELECT f1(2, 1, 2, 2) FROM DUAL; +SELECT f1(2, 2, 2, 2) FROM DUAL; +SELECT f1(2, 3, 2, 3) FROM DUAL; +DROP FUNCTION f1; + + +--echo # Testing labeled ITERATE in a labeled FOR LOOP statement + +DELIMITER /; +CREATE FUNCTION f1 (a INT, b INT, blim INT) RETURNS INT +BEGIN + DECLARE total INT DEFAULT 0; + la: + FOR ia IN 1 .. a + DO + SET total= total + 1000; + BEGIN + DECLARE ib INT DEFAULT 1; + WHILE ib <= b + DO + IF ib > blim THEN + ITERATE la; + END IF; + SET ib= ib + 1; + SET total= total + 1; + END WHILE; + END; + END FOR la; + RETURN total; +END; +/ +DELIMITER ;/ +SELECT f1(3,3,0), f1(3,3,1), f1(3,3,2), f1(3,3,3), f1(3,3,4) FROM DUAL; +DROP FUNCTION f1; + + +--echo # Testing INTERATE statement + +DELIMITER /; +CREATE FUNCTION f1(a INT) RETURNS INT +BEGIN + DECLARE total INT DEFAULT 0; + fori: + FOR i IN 1 .. a + DO + IF i=5 THEN + ITERATE fori; + END IF; + SET total= total + 1; + END FOR; + RETURN total; +END; +/ +DELIMITER ;/ +SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +CREATE FUNCTION f1(a INT) RETURNS INT +BEGIN + DECLARE total INT DEFAULT 0; + lj: + FOR j IN 1 .. 2 + DO + FOR i IN 1 .. a + DO + IF i=5 THEN + ITERATE lj; + END IF; + SET total= total + 1; + END FOR; + END FOR; + RETURN total; +END; +/ +DELIMITER ;/ +SELECT f1(3), f1(4), f1(5) FROM DUAL; +DROP FUNCTION f1; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 3b615d29166..174419220e7 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9583,6 +9583,23 @@ drop procedure p; drop view v; drop table t, tmp_t; + +--echo # +--echo # MDEV-13936: Server crashes in Time_and_counter_tracker::incr_loops +--echo # +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1 WHERE RAND() > 0.5; +CREATE FUNCTION f1() RETURNS INT RETURN ( SELECT MAX(i) FROM v1 ); + +--error ER_NON_INSERTABLE_TABLE +REPLACE INTO v1 VALUES (f1()); +SET @aux = f1(); + +# Cleanup +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1; + --echo #End of 10.1 tests --echo # diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index aeab884670d..1557ef200e5 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2643,8 +2643,33 @@ DROP TABLE t1; SET TIMESTAMP=DEFAULT; set time_zone= @@global.time_zone; +--echo # +--echo # MDEV-13936: Server crashes in Time_and_counter_tracker::incr_loops +--echo # + +CREATE TABLE t1 (i INT); +CREATE VIEW v1 AS SELECT * FROM t1 WHERE RAND() > 0.5; +CREATE TABLE t2 (a int); +CREATE TABLE t3 (a int); + +create trigger trg after insert on t2 for each row + INSERT INTO t3 SELECT MAX(i) FROM v1 UNION SELECT MAX(i) FROM v1; + +drop table t1; + +--error ER_NO_SUCH_TABLE +insert into t2 value (2); +CREATE TABLE t1 (i INT); +insert into t2 value (2); + +DROP VIEW v1; +DROP TABLE t1,t2,t3; + + +--echo End of 10.1 tests. + # -# MDEV-10915 Count number of exceuted triggers +# MDEV-10915 Count number of executed triggers # create table t1 (i int); diff --git a/mysql-test/t/type_set.test b/mysql-test/t/type_set.test index 8c26d5a4366..637ad40c316 100644 --- a/mysql-test/t/type_set.test +++ b/mysql-test/t/type_set.test @@ -240,3 +240,11 @@ EXPLAIN SELECT * FROM t1 WHERE a='1x'; EXPLAIN SELECT * FROM t1 WHERE a='1.0'; EXPLAIN SELECT * FROM t1 WHERE a='1.1'; DROP TABLE t1; + +--echo # +--echo # MDEV-11155 Bad error message when creating a SET column with comma and non-ASCII characters +--echo # + +SET NAMES utf8; +--error ER_ILLEGAL_VALUE_FOR_TYPE +CREATE TABLE t1 (a SET('a,bü')); |