diff options
| author | Michael Widenius <monty@askmonty.org> | 2010-08-02 12:01:24 +0300 |
|---|---|---|
| committer | Michael Widenius <monty@askmonty.org> | 2010-08-02 12:01:24 +0300 |
| commit | e0a6b02c5d0a311e7167295494786077009743d1 (patch) | |
| tree | 72c934fe42261ad5de3139961e092f57e9d147df /mysql-test/t | |
| parent | d2f8b7d04503478ab6b6998194a2070891f0c2bb (diff) | |
| parent | 6ad06b15222300e4eed4fe3972d1ad249c4c42a2 (diff) | |
| download | mariadb-git-e0a6b02c5d0a311e7167295494786077009743d1.tar.gz | |
Merge with MySQL 5.1.49
Fixed Bug#52005 'JOIN_TAB->dependent' may be incorrectly propageted for multilevel outer joins' in a better way (patch from Sergey Petrunya)
Diffstat (limited to 'mysql-test/t')
55 files changed, 2347 insertions, 1476 deletions
diff --git a/mysql-test/t/alter_table-big.test b/mysql-test/t/alter_table-big.test index 1dcc1f1c9bd..e007a3a55e0 100644 --- a/mysql-test/t/alter_table-big.test +++ b/mysql-test/t/alter_table-big.test @@ -31,7 +31,9 @@ create table t2 (i int); # statement execution, so we don't need many rows in 't1' to make # this test repeatable. alter table t1 disable keys; +--disable_warnings insert into t1 values (RAND()*1000, RAND()*1000, RAND()*1000); +--enable_warnings # Later we use binlog to check the order in which statements are # executed so let us reset it first. @@ -50,8 +52,7 @@ connection default; --reap set session debug="-d,sleep_alter_enable_indexes"; # Check that statements were executed/binlogged in correct order. ---replace_column 2 # 5 # -show binlog events in 'master-bin.000001' from 106; +source include/show_binlog_events.inc; # Clean up drop tables t1, t2; @@ -111,8 +112,7 @@ drop table t3; set session debug="-d,sleep_alter_before_main_binlog"; # Check that all statements were logged in correct order ---replace_column 2 # 5 # -show binlog events in 'master-bin.000001' from 106; +source include/show_binlog_events.inc; --echo End of 5.1 tests diff --git a/mysql-test/t/bug39022.test b/mysql-test/t/bug39022.test index 268b207e0e5..6056dbf0e7b 100644 --- a/mysql-test/t/bug39022.test +++ b/mysql-test/t/bug39022.test @@ -24,7 +24,7 @@ START TRANSACTION; connection thread2; --echo # in thread2 REPLACE INTO t2 VALUES (-17); -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; connection thread1; --echo # in thread1 @@ -37,14 +37,14 @@ START TRANSACTION; REPLACE INTO t1(a,b) VALUES (65,-50); REPLACE INTO t2 VALUES (-91); send; -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); #waits +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; #waits connection thread1; --echo # in thread1 --echo # should not crash --error ER_LOCK_DEADLOCK -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); #crashes +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; #crashes connection thread2; --echo # in thread2 diff --git a/mysql-test/t/create-big.test b/mysql-test/t/create-big.test index 6cd6326cdb8..e1dfbbd4ac4 100644 --- a/mysql-test/t/create-big.test +++ b/mysql-test/t/create-big.test @@ -305,8 +305,7 @@ connection default; show create table t2; drop table t2; # Let us check that statements were executed/binlogged in correct order ---replace_column 2 # 5 # -show binlog events in 'master-bin.000001' from 106; +source include/show_binlog_events.inc; # Now let us check the gap between check for target table # existance and copying of .frm file. @@ -330,8 +329,7 @@ drop table t1; connection default; --reap drop table t2; ---replace_column 2 # 5 # -show binlog events in 'master-bin.000001' from 106; +source include/show_binlog_events.inc; # And now he gap between copying of .frm file and ha_create_table() call. create table t1 (i int); @@ -359,8 +357,7 @@ drop table t1; connection default; --reap drop table t2; ---replace_column 2 # 5 # -show binlog events in 'master-bin.000001' from 106; +source include/show_binlog_events.inc; # Finally we check the gap between ha_create_table() and binlogging create table t1 (i int); @@ -386,7 +383,6 @@ drop table t1; connection default; --reap drop table t2; ---replace_column 2 # 5 # -show binlog events in 'master-bin.000001' from 106; +source include/show_binlog_events.inc; set session debug="-d,sleep_create_like_before_binlogging"; diff --git a/mysql-test/t/ctype_cp932_binlog_stm.test b/mysql-test/t/ctype_cp932_binlog_stm.test index 89df33a6df5..f3038ccfa61 100644 --- a/mysql-test/t/ctype_cp932_binlog_stm.test +++ b/mysql-test/t/ctype_cp932_binlog_stm.test @@ -7,6 +7,7 @@ # # Bug#18293: Values in stored procedure written to binlog unescaped # +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); delimiter |; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, @@ -22,7 +23,7 @@ CALL bug18293("Foo's a Bar", _cp932 0xED40ED41ED42, 47.93)| SELECT HEX(s1),HEX(s2),d FROM t4| DROP PROCEDURE bug18293| DROP TABLE t4| -SHOW BINLOG EVENTS FROM 370| +source include/show_binlog_events.inc| delimiter ;| --echo End of 5.0 tests diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 7bbc470137a..5a0e86568f3 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -374,5 +374,17 @@ DELETE IGNORE FROM t1; DROP TABLE t1; +--echo # +--echo # Bug #53450: Crash/assertion +--echo # "virtual int ha_myisam::index_first(uchar*)") at assert.c:81 +--echo # + +CREATE TABLE t1 (a INT, b INT, c INT, + INDEX(a), INDEX(b), INDEX(c)); +INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9); + +DELETE FROM t1 WHERE a = 10 OR b = 20 ORDER BY c LIMIT 1; + +DROP TABLE t1; --echo End of 5.1 tests diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index c1a22adf091..cede26f555a 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -11,5 +11,6 @@ ############################################################################## kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically -# partition_innodb_plugin : Bug#53307 2010-04-30 VasilDimov valgrind warnings -# ps_3innodb : Bug#53309 2010-04-30 VasilDimov valgrind warnings +partition_innodb_plugin : Bug#53307 2010-04-30 VasilDimov valgrind warnings +main.mysqlhotcopy_myisam : bug#54129 2010-06-04 Horst +main.mysqlhotcopy_archive: bug#54129 2010-06-04 Horst diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test index 7cd16a6bc5a..f6edacfaa29 100644 --- a/mysql-test/t/error_simulation.test +++ b/mysql-test/t/error_simulation.test @@ -45,6 +45,49 @@ SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # Bug#42064: low memory crash when importing hex strings, in Item_hex_string::Item_hex_string +--echo # + +CREATE TABLE t1(a BLOB); + +SET SESSION debug="+d,bug42064_simulate_oom"; +# May fail with either ER_OUT_OF_RESOURCES or EE_OUTOFMEMORY +--error ER_OUT_OF_RESOURCES, 5 +INSERT INTO t1 VALUES(""); +SET SESSION debug=DEFAULT; + +DROP TABLE t1; + +-- echo # +-- echo # Bug#41660: Sort-index_merge for non-first join table may require +-- echo # O(#scans) memory +-- echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9); + +CREATE TABLE t2 (a INT, b INT, filler CHAR(100), KEY(a), KEY(b)); +INSERT INTO t2 SELECT 1000, 1000, 'filler' FROM t1 A, t1 B, t1 C; +INSERT INTO t2 VALUES (1, 1, 'data'); + +--echo # the example query uses LEFT JOIN only for the sake of being able to +--echo # demonstrate the issue with a very small dataset. (left outer join +--echo # disables the use of join buffering, so we get the second table +--echo # re-scanned for every record in the outer table. if we used inner join, +--echo # we would need to have thousands of records and/or more columns in both +--echo # tables so that the join buffer is filled and re-scans are triggered). + +SET SESSION debug = '+d,only_one_Unique_may_be_created'; + +--replace_column 1 x 2 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON ( t2.a < 10 OR t2.b < 10 ); +SELECT * FROM t1 LEFT JOIN t2 ON ( t2.a < 10 OR t2.b < 10 ); + +SET SESSION debug = DEFAULT; + +DROP TABLE t1, t2; --echo # --echo # End of 5.1 tests diff --git a/mysql-test/t/events_scheduling.test b/mysql-test/t/events_scheduling.test index 041a2def490..5f16f8bea6a 100644 --- a/mysql-test/t/events_scheduling.test +++ b/mysql-test/t/events_scheduling.test @@ -108,6 +108,32 @@ DROP TABLE table_1; DROP TABLE table_2; DROP TABLE table_3; DROP TABLE table_4; + +-- echo +-- echo Bug #50087 Interval arithmetic for Event_queue_element is not portable. +-- echo + +CREATE TABLE t1(a int); + +CREATE EVENT e1 ON SCHEDULE EVERY 1 MONTH +STARTS NOW() - INTERVAL 1 MONTH +ENDS NOW() + INTERVAL 2 MONTH +ON COMPLETION PRESERVE +DO + INSERT INTO t1 VALUES (1); + +CREATE EVENT e2 ON SCHEDULE EVERY 1 MONTH +STARTS NOW() +ENDS NOW() + INTERVAL 11 MONTH +ON COMPLETION PRESERVE +DO + INSERT INTO t1 VALUES (1); + +DROP TABLE t1; +DROP EVENT e1; +DROP EVENT e2; + + DROP DATABASE events_test; SET GLOBAL event_scheduler=@event_scheduler; diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index ba6be72dbdc..b635a1b2968 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -213,4 +213,19 @@ EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t1 t JOIN t2 WHERE b <= 1 AND DROP TABLE t1, t2; +--echo # +--echo # Bug #48573: difference of index selection between rpm binary and +--echo # .tar.gz, windows vs linux.. +--echo # + +CREATE TABLE t1(c1 INT, c2 INT, c4 INT, c5 INT, KEY(c2, c5), KEY(c2, c4, c5)); +INSERT INTO t1 VALUES(4, 1, 1, 1); +INSERT INTO t1 VALUES(3, 1, 1, 1); +INSERT INTO t1 VALUES(2, 1, 1, 1); +INSERT INTO t1 VALUES(1, 1, 1, 1); + +EXPLAIN SELECT c1 FROM t1 WHERE c2 = 1 AND c4 = 1 AND c5 = 1; + +DROP TABLE t1; + --echo End of 5.1 tests. diff --git a/mysql-test/t/flush_block_commit_notembedded.test b/mysql-test/t/flush_block_commit_notembedded.test index aea38250218..b774d7ec069 100644 --- a/mysql-test/t/flush_block_commit_notembedded.test +++ b/mysql-test/t/flush_block_commit_notembedded.test @@ -28,14 +28,14 @@ INSERT t1 VALUES (1); --echo # Switch to connection con2 connection con2; FLUSH TABLES WITH READ LOCK; -SHOW MASTER STATUS; +--source include/show_binlog_events.inc --echo # Switch to connection con1 connection con1; send COMMIT; --echo # Switch to connection con2 connection con2; sleep 1; -SHOW MASTER STATUS; +--source include/show_binlog_events.inc UNLOCK TABLES; --echo # Switch to connection con1 connection con1; diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 61ae812d874..6efeb2866e6 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -540,5 +540,20 @@ EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL); DROP TABLE t1; --echo # +--echo # Bug#54477: Crash on IN / CASE with NULL arguments +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); + +SELECT 1 IN (NULL, a) FROM t1; + +SELECT a IN (a, a) FROM t1 GROUP BY a WITH ROLLUP; + +SELECT CASE a WHEN a THEN a END FROM t1 GROUP BY a WITH ROLLUP; + +DROP TABLE t1; + +--echo # --echo End of 5.1 tests diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test index 6218efb882f..d3ad4614998 100644 --- a/mysql-test/t/func_isnull.test +++ b/mysql-test/t/func_isnull.test @@ -13,3 +13,18 @@ select * from t1 where isnull(to_days(mydate)); drop table t1; # End of 4.1 tests + +--echo # +--echo # Bug#53933 crash when using uncacheable subquery in the having clause of outer query +--echo # + +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (0),(0); + +SELECT ISNULL((SELECT GET_LOCK('Bug#53933', 0) FROM t1 GROUP BY f1)) AS f2 +FROM t1 GROUP BY f1 HAVING f2 = f2; +SELECT RELEASE_LOCK('Bug#53933'); + +DROP TABLE t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/func_like.test b/mysql-test/t/func_like.test index 741ea5533da..1204d04d9a0 100644 --- a/mysql-test/t/func_like.test +++ b/mysql-test/t/func_like.test @@ -112,5 +112,19 @@ select 'andre%' like 'andreÊ%' escape 'Ê'; # select _cp1251'andre%' like convert('andreÊ%' using cp1251) escape 'Ê'; -# -# End of 4.1 tests + +--echo End of 4.1 tests + + +--echo # +--echo # Bug #54575: crash when joining tables with unique set column +--echo # +CREATE TABLE t1(a SET('a') NOT NULL, UNIQUE KEY(a)); +CREATE TABLE t2(b INT PRIMARY KEY); +INSERT INTO t1 VALUES (); +INSERT INTO t2 VALUES (1), (2), (3); +SELECT 1 FROM t2 JOIN t1 ON 1 LIKE a GROUP BY a; +DROP TABLE t1, t2; + + +--echo End of 5.1 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index e6ea5ecc7f6..c5b27ee1a62 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1205,6 +1205,21 @@ SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) DROP TABLE t1, t2; + +--echo # +--echo # Bug#52051: Aggregate functions incorrectly returns NULL from outer +--echo # join query +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY); +CREATE TABLE t2 (a INT PRIMARY KEY); +INSERT INTO t2 VALUES (1), (2); +EXPLAIN SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; +SELECT MIN(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; +EXPLAIN SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; +SELECT MAX(t2.a) FROM t2 LEFT JOIN t1 ON t2.a = t1.a; +DROP TABLE t1, t2; + + --echo # --echo # End of 5.1 tests diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index b06f0c09fc5..8ab7e1c9cb4 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1085,4 +1085,19 @@ INSERT INTO t1 VALUES (1), (2), (3); --source include/min_null_cond.inc DROP TABLE t1; +--echo # +--echo # Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&, Item*) at +--echo # opt_sum.cc:305 +--echo # +CREATE TABLE t1 ( a INT, KEY (a) ); +INSERT INTO t1 VALUES (1), (2), (3); + +SELECT MIN( a ) AS min_a +FROM t1 +WHERE a > 1 AND a IS NULL +ORDER BY min_a; + +DROP TABLE t1; + + --echo End of 5.1 tests diff --git a/mysql-test/t/handler_myisam.test b/mysql-test/t/handler_myisam.test index 868ba14480a..e78072ef8a0 100644 --- a/mysql-test/t/handler_myisam.test +++ b/mysql-test/t/handler_myisam.test @@ -48,4 +48,53 @@ HANDLER t1 READ a NEXT; HANDLER t1 CLOSE; DROP TABLE t1; + +--echo # +--echo # Bug #54007: assert in ha_myisam::index_next , HANDLER +--echo # +CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a), KEY b(b), KEY ab(a, b)); + +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ `PRIMARY` NEXT; +HANDLER t1 READ ab NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 CLOSE; + +INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30); +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ NEXT; +HANDLER t1 READ `PRIMARY` NEXT; +HANDLER t1 READ `PRIMARY` NEXT; +HANDLER t1 READ ab NEXT; +HANDLER t1 READ ab NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ b NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 READ NEXT; +HANDLER t1 CLOSE; + +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ `PRIMARY` PREV; +HANDLER t1 READ `PRIMARY` PREV; +HANDLER t1 READ b PREV; +HANDLER t1 READ b PREV; +HANDLER t1 CLOSE; + +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +HANDLER t1 READ `PRIMARY` PREV LIMIT 3; +HANDLER t1 READ b NEXT LIMIT 5; +HANDLER t1 CLOSE; + +DROP TABLE t1; + + --echo End of 5.1 tests diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 10bc3645898..4cef8615cd2 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1413,6 +1413,15 @@ DROP USER nonpriv; DROP TABLE db1.t1; DROP DATABASE db1; +--echo +--echo Bug#54422 query with = 'variables' +--echo + +CREATE TABLE variables(f1 INT); +SELECT COLUMN_DEFAULT, TABLE_NAME +FROM INFORMATION_SCHEMA.COLUMNS +WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables'; +DROP TABLE variables; --echo End of 5.1 tests. diff --git a/mysql-test/t/innodb-autoinc-optimize.test b/mysql-test/t/innodb-autoinc-optimize.test deleted file mode 100644 index 0f0cb57f92f..00000000000 --- a/mysql-test/t/innodb-autoinc-optimize.test +++ /dev/null @@ -1,19 +0,0 @@ --- source include/have_innodb.inc -# embedded server ignores 'delayed', so skip this --- source include/not_embedded.inc - ---disable_warnings -drop table if exists t1; ---enable_warnings - -# -# Bug 34286 -# -create table t1(a int not null auto_increment primary key) engine=innodb; -insert into t1 set a = -1; -# NOTE: The database needs to be shutdown and restarted (here) for -# the test to work. It's included for reference only. -optimize table t1; - ---echo ==== clean up ==== -DROP TABLE t1; diff --git a/mysql-test/t/innodb-ucs2.test b/mysql-test/t/innodb-ucs2.test deleted file mode 100644 index 7b91ef37d3f..00000000000 --- a/mysql-test/t/innodb-ucs2.test +++ /dev/null @@ -1,230 +0,0 @@ --- source include/have_innodb.inc --- source include/have_ucs2.inc - ---disable_warnings -drop table if exists t1, t2; ---enable_warnings - -# -# BUG 14056 Column prefix index on UTF-8 primary key column causes: Can't find record.. -# - -create table t1 ( - a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2)) -) character set utf8 engine = innodb; -create table t2 ( - a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2)) -) character set ucs2 engine = innodb; -insert into t1 values (1,'abcdefg','abcdefg','one'); -insert into t1 values (2,'ijkilmn','ijkilmn','two'); -insert into t1 values (3,'qrstuvw','qrstuvw','three'); -insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); -insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); -insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six'); -insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven'); -insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); -insert into t2 values (1,'abcdefg','abcdefg','one'); -insert into t2 values (2,'ijkilmn','ijkilmn','two'); -insert into t2 values (3,'qrstuvw','qrstuvw','three'); -insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); -insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); -insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); -insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); -insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); -insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); -insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven'); -insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point'); -insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); -update t1 set filler = 'boo' where a = 1; -update t2 set filler ='email' where a = 4; -select a,hex(b),hex(c),filler from t1 order by filler; -select a,hex(b),hex(c),filler from t2 order by filler; -drop table t1; -drop table t2; - -create table t1 ( - a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2)) -) character set utf8 engine = innodb; -create table t2 ( - a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2)) -) character set ucs2 engine = innodb; -insert into t1 values (1,'abcdefg','abcdefg','one'); -insert into t1 values (2,'ijkilmn','ijkilmn','two'); -insert into t1 values (3,'qrstuvw','qrstuvw','three'); -insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); -insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); -insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six'); -insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven'); -insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); -insert into t2 values (1,'abcdefg','abcdefg','one'); -insert into t2 values (2,'ijkilmn','ijkilmn','two'); -insert into t2 values (3,'qrstuvw','qrstuvw','three'); -insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); -insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); -insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); -insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); -insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); -insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); -insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven'); -insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point'); -insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); -update t1 set filler = 'boo' where a = 1; -update t2 set filler ='email' where a = 4; -select a,hex(b),hex(c),filler from t1 order by filler; -select a,hex(b),hex(c),filler from t2 order by filler; -drop table t1; -drop table t2; - -create table t1 ( - a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2)) -) character set utf8 engine = innodb; -create table t2 ( - a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2)) -) character set ucs2 engine = innodb; -insert into t1 values (1,'abcdefg','abcdefg','one'); -insert into t1 values (2,'ijkilmn','ijkilmn','two'); -insert into t1 values (3,'qrstuvw','qrstuvw','three'); -insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); -insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); -insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six'); -insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven'); -insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); -insert into t2 values (1,'abcdefg','abcdefg','one'); -insert into t2 values (2,'ijkilmn','ijkilmn','two'); -insert into t2 values (3,'qrstuvw','qrstuvw','three'); -insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); -insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); -insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); -insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); -insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); -insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); -insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven'); -insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point'); -insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); -update t1 set filler = 'boo' where a = 1; -update t2 set filler ='email' where a = 4; -select a,hex(b),hex(c),filler from t1 order by filler; -select a,hex(b),hex(c),filler from t2 order by filler; -drop table t1; -drop table t2; - -create table t1 ( - a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2)) -) character set utf8 engine = innodb; -create table t2 ( - a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2)) -) character set ucs2 engine = innodb; -insert into t1 values (1,'abcdefg','abcdefg','one'); -insert into t1 values (2,'ijkilmn','ijkilmn','two'); -insert into t1 values (3,'qrstuvw','qrstuvw','three'); -insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); -insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); -insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); -insert into t2 values (1,'abcdefg','abcdefg','one'); -insert into t2 values (2,'ijkilmn','ijkilmn','two'); -insert into t2 values (3,'qrstuvw','qrstuvw','three'); -insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); -insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); -insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); -insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); -insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); -insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); -insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); -update t1 set filler = 'boo' where a = 1; -update t2 set filler ='email' where a = 4; -select a,hex(b),hex(c),filler from t1 order by filler; -select a,hex(b),hex(c),filler from t2 order by filler; -drop table t1; -drop table t2; -commit; - -# -# Test cases for bug #15308 Problem of Order with Enum Column in Primary Key -# -CREATE TABLE t1 ( - ind enum('0','1','2') NOT NULL default '0', - string1 varchar(250) NOT NULL, - PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; -CREATE TABLE t2 ( - ind enum('0','1','2') NOT NULL default '0', - string1 varchar(250) NOT NULL, - PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=ucs2; - -INSERT INTO t1 VALUES ('1', ''),('2', ''); -INSERT INTO t2 VALUES ('1', ''),('2', ''); -SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1; -SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1; -drop table t1,t2; - -CREATE TABLE t1 ( - ind set('0','1','2') NOT NULL default '0', - string1 varchar(250) NOT NULL, - PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; -CREATE TABLE t2 ( - ind set('0','1','2') NOT NULL default '0', - string1 varchar(250) NOT NULL, - PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=ucs2; - -INSERT INTO t1 VALUES ('1', ''),('2', ''); -INSERT INTO t2 VALUES ('1', ''),('2', ''); -SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1; -SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1; -drop table t1,t2; - -CREATE TABLE t1 ( - ind bit not null, - string1 varchar(250) NOT NULL, - PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; -CREATE TABLE t2 ( - ind bit not null, - string1 varchar(250) NOT NULL, - PRIMARY KEY (ind) -) ENGINE=InnoDB DEFAULT CHARSET=ucs2; -insert into t1 values(0,''),(1,''); -insert into t2 values(0,''),(1,''); -select hex(ind),hex(string1) from t1 order by string1; -select hex(ind),hex(string1) from t2 order by string1; -drop table t1,t2; - -# tests for bug #14056 Column prefix index on UTF-8 primary key column causes 'Can't find record..' - -create table t2 ( - a int, b char(10), filler char(10), primary key(a, b(2)) -) character set utf8 engine = innodb; - -insert into t2 values (1,'abcdefg','one'); -insert into t2 values (2,'ijkilmn','two'); -insert into t2 values (3, 'qrstuvw','three'); -update t2 set a=5, filler='booo' where a=1; -drop table t2; -create table t2 ( - a int, b char(10), filler char(10), primary key(a, b(2)) -) character set ucs2 engine = innodb; - -insert into t2 values (1,'abcdefg','one'); -insert into t2 values (2,'ijkilmn','two'); -insert into t2 values (3, 'qrstuvw','three'); -update t2 set a=5, filler='booo' where a=1; -drop table t2; - -create table t1(a int not null, b char(110),primary key(a,b(100))) engine=innodb default charset=utf8; -insert into t1 values(1,'abcdefg'),(2,'defghijk'); -insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1); -insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2); -select a,hex(b) from t1 order by b; -update t1 set b = 'three' where a = 6; -drop table t1; -create table t1(a int not null, b text(110),primary key(a,b(100))) engine=innodb default charset=utf8; -insert into t1 values(1,'abcdefg'),(2,'defghijk'); -insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1); -insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2); -select a,hex(b) from t1 order by b; -update t1 set b = 'three' where a = 6; -drop table t1; - ---echo End of 5.0 tests diff --git a/mysql-test/t/innodb_autoinc_lock_mode_zero-master.opt b/mysql-test/t/innodb_autoinc_lock_mode_zero-master.opt deleted file mode 100644 index fad0da2ac2e..00000000000 --- a/mysql-test/t/innodb_autoinc_lock_mode_zero-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb-autoinc-lock-mode=0 diff --git a/mysql-test/t/innodb_autoinc_lock_mode_zero.test b/mysql-test/t/innodb_autoinc_lock_mode_zero.test deleted file mode 100644 index 96f748673c0..00000000000 --- a/mysql-test/t/innodb_autoinc_lock_mode_zero.test +++ /dev/null @@ -1,44 +0,0 @@ -# This test runs with old-style locking, as: -# --innodb-autoinc-lock-mode=0 - --- source include/have_innodb.inc - ---disable_warnings -drop table if exists t1; ---enable_warnings - - -# -# Search on unique key -# - -CREATE TABLE t1 ( - id int(11) NOT NULL auto_increment, - ggid varchar(32) binary DEFAULT '' NOT NULL, - email varchar(64) DEFAULT '' NOT NULL, - passwd varchar(32) binary DEFAULT '' NOT NULL, - PRIMARY KEY (id), - UNIQUE ggid (ggid) -) ENGINE=innodb; - -insert into t1 (ggid,passwd) values ('test1','xxx'); -insert into t1 (ggid,passwd) values ('test2','yyy'); --- error ER_DUP_ENTRY -insert into t1 (ggid,passwd) values ('test2','this will fail'); --- error ER_DUP_ENTRY -insert into t1 (ggid,id) values ('this will fail',1); - -select * from t1 where ggid='test1'; -select * from t1 where passwd='xxx'; -select * from t1 where id=2; - -replace into t1 (ggid,id) values ('this will work',1); -replace into t1 (ggid,passwd) values ('test2','this will work'); --- error ER_DUP_ENTRY -update t1 set id=100,ggid='test2' where id=1; -select * from t1; -select * from t1 where id=1; -select * from t1 where id=999; -drop table t1; - ---echo End of tests diff --git a/mysql-test/t/innodb_bug30919-master.opt b/mysql-test/t/innodb_bug30919-master.opt deleted file mode 100644 index 8636d2d8734..00000000000 --- a/mysql-test/t/innodb_bug30919-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb --innodb_autoinc_lock_mode=0 diff --git a/mysql-test/t/innodb_bug30919.test b/mysql-test/t/innodb_bug30919.test deleted file mode 100644 index 56b2c7bc03d..00000000000 --- a/mysql-test/t/innodb_bug30919.test +++ /dev/null @@ -1,68 +0,0 @@ ---source include/have_innodb.inc ---source include/have_partition.inc ---vertical_results -let $engine_type= 'innodb'; - -######## Creat Table Section ######### -use test; - -eval CREATE TABLE test.part_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, - dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, - fkid MEDIUMINT, filler VARCHAR(255), - PRIMARY KEY(id)) ENGINE=$engine_type - PARTITION BY RANGE(id) - SUBPARTITION BY hash(id) subpartitions 2 - (PARTITION pa3 values less than (42), - PARTITION pa6 values less than (60), - PARTITION pa7 values less than (70), - PARTITION pa8 values less than (80), - PARTITION pa9 values less than (90), - PARTITION pa10 values less than (100), - PARTITION pa11 values less than MAXVALUE); - -######## Create SPs, Functions, Views and Triggers Section ############## - -delimiter |; - -CREATE PROCEDURE test.proc_part() -BEGIN - DECLARE ins_count INT DEFAULT 1000; - DECLARE del_count INT; - DECLARE cur_user VARCHAR(255); - DECLARE local_uuid VARCHAR(255); - DECLARE local_time TIMESTAMP; - - SET local_time= NOW(); - SET cur_user= CURRENT_USER(); - SET local_uuid= UUID(); - - WHILE ins_count > 0 DO - INSERT INTO test.part_tbl VALUES (NULL, NOW(), USER() , UUID(), - ins_count,'Going to test MBR for MySQL'); - SET ins_count = ins_count - 1; - END WHILE; - SELECT MAX(id) FROM test.part_tbl INTO del_count; - WHILE del_count > 0 DO - DELETE FROM test.part_tbl WHERE id = del_count; - select count(*) as internal_count, del_count -- these two lines are for - FROM test.part_tbl; -- debug to show the problem - SET del_count = del_count - 2; - END WHILE; -END| - -delimiter ;| - -############ Finish Setup Section ################### - -############ Test Section ################### ---horizontal_results - -CALL test.proc_part(); - -select count(*) as Part from test.part_tbl; - -###### CLEAN UP SECTION ############## - -DROP PROCEDURE test.proc_part; -DROP TABLE test.part_tbl; - diff --git a/mysql-test/t/innodb_bug42419.test b/mysql-test/t/innodb_bug42419.test deleted file mode 100644 index 93c4764252a..00000000000 --- a/mysql-test/t/innodb_bug42419.test +++ /dev/null @@ -1,78 +0,0 @@ -# -# Testcase for InnoDB -# Bug#42419 Server crash with "Pure virtual method called" on two concurrent connections -# - ---source include/not_embedded.inc ---source include/have_innodb.inc - -let $innodb_lock_wait_timeout= query_get_value(SHOW VARIABLES LIKE 'innodb_lock_wait_timeout%', Value, 1); -if (`SELECT $innodb_lock_wait_timeout < 10`) -{ - --echo # innodb_lock_wait_timeout must be >= 10 seconds - --echo # so that this test can work all time fine on an overloaded testing box - SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; - exit; -} - -# Save the initial number of concurrent sessions ---source include/count_sessions.inc - -# First session -connection default; - - ---enable_warnings -CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b INT) ENGINE = InnoDB; - -INSERT INTO t1 VALUES (1,1),(2,2),(3,3); -COMMIT; -SET AUTOCOMMIT = 0; - -CREATE TEMPORARY TABLE t1_tmp ( b INT ); - -INSERT INTO t1_tmp (b) SELECT b FROM t1 WHERE a = 3; -INSERT INTO t1_tmp (b) SELECT b FROM t1 WHERE a = 2; - -# Second session -connect (user2,localhost,root,,,$MASTER_MYPORT,$MASTER_MYSOCK); - -SET AUTOCOMMIT = 0; - -CREATE TEMPORARY TABLE t2_tmp ( a int, new_a int ); -INSERT INTO t2_tmp VALUES (1,51),(2,52),(3,53); - -UPDATE t1 SET a = (SELECT new_a FROM t2_tmp WHERE t2_tmp.a = t1.a) WHERE a = 1; -send -UPDATE t1 SET a = (SELECT new_a FROM t2_tmp WHERE t2_tmp.a = t1.a) WHERE a = 2; - -# The last update will wait for a lock held by the first session - -# First session -connection default; - -# Poll till the UPDATE of the second session waits for lock -let $show_statement= SHOW PROCESSLIST; -let $field= State; -let $condition= = 'Updating'; ---source include/wait_show_condition.inc - -# If the testing box is overloadeded and innodb_lock_wait_timeout is too small -# we might get here ER_LOCK_WAIT_TIMEOUT. ---error ER_LOCK_DEADLOCK -INSERT INTO t1_tmp (b) SELECT b FROM t1 WHERE a = 1; - -# Second session -connection user2; ---echo Reap the server message for connection user2 UPDATE t1 ... -reap; - -# The server crashed when executing this UPDATE or the succeeding SQL command. -UPDATE t1 SET a = (SELECT new_a FROM t2_tmp WHERE t2_tmp.a = t1.a) WHERE a = 3; - -connection default; -disconnect user2; -DROP TABLE t1; - -# Wait till all disconnects are completed ---source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/innodb_gis.test b/mysql-test/t/innodb_gis.test deleted file mode 100644 index 1adb14ea482..00000000000 --- a/mysql-test/t/innodb_gis.test +++ /dev/null @@ -1,10 +0,0 @@ ---source include/have_innodb.inc -SET storage_engine=innodb; ---source include/gis_generic.inc ---source include/gis_keys.inc - -# -# Bug #15680 (SPATIAL key in innodb) -# ---error ER_TABLE_CANT_HANDLE_SPKEYS -create table t1 (g geometry not null, spatial gk(g)) engine=innodb; diff --git a/mysql-test/t/innodb_lock_wait_timeout_1-master.opt b/mysql-test/t/innodb_lock_wait_timeout_1-master.opt deleted file mode 100644 index 462f8fbe828..00000000000 --- a/mysql-test/t/innodb_lock_wait_timeout_1-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb_lock_wait_timeout=1 diff --git a/mysql-test/t/innodb_lock_wait_timeout_1.test b/mysql-test/t/innodb_lock_wait_timeout_1.test deleted file mode 100644 index fcbf2b1cfc7..00000000000 --- a/mysql-test/t/innodb_lock_wait_timeout_1.test +++ /dev/null @@ -1,264 +0,0 @@ ---source include/have_innodb.inc - ---echo # ---echo # Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout ---echo # without error ---echo # - -CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)) ENGINE=InnoDB; - -INSERT INTO t1 (a,b) VALUES (1070109,99); - -CREATE TABLE t2 (b int, a int, PRIMARY KEY (b)) ENGINE=InnoDB; - -INSERT INTO t2 (b,a) VALUES (7,1070109); - -SELECT * FROM t1; - -BEGIN; - -SELECT b FROM t2 WHERE b=7 FOR UPDATE; - -CONNECT (addconroot, localhost, root,,); -CONNECTION addconroot; - -BEGIN; - ---error ER_LOCK_WAIT_TIMEOUT -SELECT b FROM t2 WHERE b=7 FOR UPDATE; - ---error ER_LOCK_WAIT_TIMEOUT -INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7)); - ---error ER_LOCK_WAIT_TIMEOUT -UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7); - ---error ER_LOCK_WAIT_TIMEOUT -DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7); - -SELECT * FROM t1; - -CONNECTION default; -DISCONNECT addconroot; - -DROP TABLE t2, t1; - ---echo # End of 5.0 tests - ---echo # ---echo # Bug#46539 Various crashes on INSERT IGNORE SELECT + SELECT ---echo # FOR UPDATE ---echo # ---disable_warnings -drop table if exists t1; ---enable_warnings -create table t1 (a int primary key auto_increment, - b int, index(b)) engine=innodb; -insert into t1 (b) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); -set autocommit=0; -begin; -select * from t1 where b=5 for update; -connect (con1, localhost, root,,); -connection con1; ---error ER_LOCK_WAIT_TIMEOUT -insert ignore into t1 (b) select a as b from t1; -connection default; ---echo # Cleanup ---echo # -disconnect con1; -commit; -set autocommit=default; -drop table t1; - ---echo # ---echo # Bug #37183 insert ignore into .. select ... hangs ---echo # after deadlock was encountered ---echo # -connect (con1,localhost,root,,); -create table t1(id int primary key,v int)engine=innodb; -insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); -create table t2 like t1; - ---connection con1 -begin; -update t1 set v=id*2 where id=1; - ---connection default -begin; -update t1 set v=id*2 where id=2; - ---connection con1 ---error 1205 -update t1 set v=id*2 where id=2; - ---connection default ---error 1205 -insert ignore into t2 select * from t1 where id=1; -rollback; - ---connection con1 -rollback; - ---connection default -disconnect con1; -drop table t1, t2; - - ---echo # ---echo # Bug#41756 Strange error messages about locks from InnoDB ---echo # ---disable_warnings -drop table if exists t1; ---enable_warnings ---echo # In the default transaction isolation mode, and/or with ---echo # innodb_locks_unsafe_for_binlog=OFF, handler::unlock_row() ---echo # in InnoDB does nothing. ---echo # Thus in order to reproduce the condition that led to the ---echo # warning, one needs to relax isolation by either ---echo # setting a weaker tx_isolation value, or by turning on ---echo # the unsafe replication switch. ---echo # For testing purposes, choose to tweak the isolation level, ---echo # since it's settable at runtime, unlike ---echo # innodb_locks_unsafe_for_binlog, which is ---echo # only a command-line switch. ---echo # -set @@session.tx_isolation="read-committed"; - ---echo # Prepare data. We need a table with a unique index, ---echo # for join_read_key to be used. The other column ---echo # allows to control what passes WHERE clause filter. -create table t1 (a int primary key, b int) engine=innodb; ---echo # Let's make sure t1 has sufficient amount of rows ---echo # to exclude JT_ALL access method when reading it, ---echo # i.e. make sure that JT_EQ_REF(a) is always preferred. -insert into t1 values (1,1), (2,null), (3,1), (4,1), - (5,1), (6,1), (7,1), (8,1), (9,1), (10,1), - (11,1), (12,1), (13,1), (14,1), (15,1), - (16,1), (17,1), (18,1), (19,1), (20,1); ---echo # ---echo # Demonstrate that for the SELECT statement ---echo # used later in the test JT_EQ_REF access method is used. ---echo # ---vertical_results -explain -select 1 from t1 natural join (select 2 as a, 1 as b union all - select 2 as a, 2 as b) as t2 for update; ---horizontal_results ---echo # ---echo # Demonstrate that the reported SELECT statement ---echo # no longer produces warnings. ---echo # -select 1 from t1 natural join (select 2 as a, 1 as b union all - select 2 as a, 2 as b) as t2 for update; -commit; ---echo # ---echo # Demonstrate that due to lack of inter-sweep "reset" function, ---echo # we keep some non-matching records locked, even though we know ---echo # we could unlock them. ---echo # To do that, show that if there is only one distinct value ---echo # for a in t2 (a=2), we will keep record (2,null) in t1 locked. ---echo # But if we add another value for "a" to t2, say 6, ---echo # join_read_key cache will be pruned at least once, ---echo # and thus record (2, null) in t1 will get unlocked. ---echo # -begin; -select 1 from t1 natural join (select 2 as a, 1 as b union all - select 2 as a, 2 as b) as t2 for update; -connect (con1,localhost,root,,); ---echo # ---echo # Switching to connection con1 -connection con1; ---echo # We should be able to delete all records from t1 except (2, null), ---echo # since they were not locked. -begin; ---echo # Delete in series of 3 records so that full scan ---echo # is not used and we're not blocked on record (2,null) -delete from t1 where a in (1,3,4); -delete from t1 where a in (5,6,7); -delete from t1 where a in (8,9,10); -delete from t1 where a in (11,12,13); -delete from t1 where a in (14,15,16); -delete from t1 where a in (17,18); -delete from t1 where a in (19,20); ---echo # ---echo # Record (2, null) is locked. This is actually unnecessary, ---echo # because the previous select returned no rows. ---echo # Just demonstrate the effect. ---echo # ---error ER_LOCK_WAIT_TIMEOUT -delete from t1; -rollback; ---echo # ---echo # Switching to connection default -connection default; ---echo # ---echo # Show that the original contents of t1 is intact: -select * from t1; -commit; ---echo # ---echo # Have a one more record in t2 to show that ---echo # if join_read_key cache is purned, the current ---echo # row under the cursor is unlocked (provided, this row didn't ---echo # match the partial WHERE clause, of course). ---echo # Sic: the result of this test dependent on the order of retrieval ---echo # of records --echo # from the derived table, if ! ---echo # We use DELETE to disable the JOIN CACHE. This DELETE modifies no ---echo # records. It also should leave no InnoDB row locks. ---echo # -begin; -delete t1.* from t1 natural join (select 2 as a, 2 as b union all - select 0 as a, 0 as b) as t2; ---echo # Demonstrate that nothing was deleted form t1 -select * from t1; ---echo # ---echo # Switching to connection con1 -connection con1; -begin; ---echo # Since there is another distinct record in the derived table ---echo # the previous matching record in t1 -- (2,null) -- was unlocked. -delete from t1; ---echo # We will need the contents of the table again. -rollback; -select * from t1; -commit; ---echo # ---echo # Switching to connection default -connection default; -rollback; -begin; ---echo # ---echo # Before this patch, we could wrongly unlock a record ---echo # that was cached and later used in a join. Demonstrate that ---echo # this is no longer the case. ---echo # Sic: this test is also order-dependent (i.e. the ---echo # the bug would show up only if the first record in the union ---echo # is retreived and processed first. ---echo # ---echo # Verify that JT_EQ_REF is used. ---vertical_results -explain -select 1 from t1 natural join (select 3 as a, 2 as b union all - select 3 as a, 1 as b) as t2 for update; ---horizontal_results ---echo # Lock the record. -select 1 from t1 natural join (select 3 as a, 2 as b union all - select 3 as a, 1 as b) as t2 for update; ---echo # Switching to connection con1 -connection con1; ---echo # ---echo # We should not be able to delete record (3,1) from t1, ---echo # (previously it was possible). ---echo # ---error ER_LOCK_WAIT_TIMEOUT -delete from t1 where a=3; ---echo # Switching to connection default -connection default; -commit; - -disconnect con1; -set @@session.tx_isolation=default; -drop table t1; - ---echo # ---echo # End of 5.1 tests ---echo # diff --git a/mysql-test/t/innodb_mysql-master.opt b/mysql-test/t/innodb_mysql-master.opt deleted file mode 100644 index 205c733455d..00000000000 --- a/mysql-test/t/innodb_mysql-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb-lock-wait-timeout=2 diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test deleted file mode 100644 index ada4323dcee..00000000000 --- a/mysql-test/t/innodb_mysql.test +++ /dev/null @@ -1,636 +0,0 @@ -# t/innodb_mysql.test -# -# Last update: -# 2006-07-26 ML test refactored (MySQL 5.1) -# main testing code t/innodb_mysql.test -> include/mix1.inc -# - -# Slow test, don't run during staging part --- source include/not_staging.inc --- source include/have_innodb.inc -let $engine_type= InnoDB; -let $other_engine_type= MEMORY; -# InnoDB does support FOREIGN KEYFOREIGN KEYs -let $test_foreign_keys= 1; -set global innodb_support_xa=default; -set session innodb_support_xa=default; ---source include/mix1.inc - ---disable_warnings -drop table if exists t1, t2, t3; ---enable_warnings -# -# BUG#35850: Performance regression in 5.1.23/5.1.24 -# -create table t1(a int); -insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb; -insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C; ---echo this must use key 'a', not PRIMARY: ---replace_column 9 # -explain select a from t2 where a=b; -drop table t1, t2; - -# -# Bug #40360: Binlog related errors with binlog off -# -# This bug is triggered when the binlog format is STATEMENT and the -# binary log is turned off. In this case, no error should be shown for -# the statement since there are no replication issues. - -SET SESSION BINLOG_FORMAT=STATEMENT; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -query_vertical select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation; -CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; -INSERT INTO t1 VALUES(1); -DROP TABLE t1; - -# -# Bug#37284 Crash in Field_string::type() -# ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings -CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; -CREATE INDEX i1 on t1 (a(3)); -SELECT * FROM t1 WHERE a = 'abcde'; -DROP TABLE t1; - -# -# Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of -# requested column -# - -CREATE TABLE foo (a int, b int, c char(10), - PRIMARY KEY (c(3)), - KEY b (b) -) engine=innodb; - -CREATE TABLE foo2 (a int, b int, c char(10), - PRIMARY KEY (c), - KEY b (b) -) engine=innodb; - -CREATE TABLE bar (a int, b int, c char(10), - PRIMARY KEY (c(3)), - KEY b (b) -) engine=myisam; - -INSERT INTO foo VALUES - (1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'), - (4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe'); - -INSERT INTO bar SELECT * FROM foo; -INSERT INTO foo2 SELECT * FROM foo; - ---query_vertical EXPLAIN SELECT c FROM bar WHERE b>2; ---query_vertical EXPLAIN SELECT c FROM foo WHERE b>2; ---query_vertical EXPLAIN SELECT c FROM foo2 WHERE b>2; - ---query_vertical EXPLAIN SELECT c FROM bar WHERE c>2; ---query_vertical EXPLAIN SELECT c FROM foo WHERE c>2; ---query_vertical EXPLAIN SELECT c FROM foo2 WHERE c>2; - -DROP TABLE foo, bar, foo2; - - -# -# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table -# - ---disable_warnings -DROP TABLE IF EXISTS t1,t3,t2; -DROP FUNCTION IF EXISTS f1; ---enable_warnings - -DELIMITER |; -CREATE FUNCTION f1() RETURNS VARCHAR(250) - BEGIN - return 'hhhhhhh' ; - END| -DELIMITER ;| - -CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB; - -BEGIN WORK; - -CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB; -CREATE TEMPORARY TABLE t3 LIKE t2; - -INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL); - -SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl'); -PREPARE stmt1 FROM @stmt; - -SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2'); -PREPARE stmt3 FROM @stmt; - -EXECUTE stmt1; - -COMMIT; - -DEALLOCATE PREPARE stmt1; -DEALLOCATE PREPARE stmt3; - -DROP TABLE t1,t3,t2; -DROP FUNCTION f1; - -# -# Bug#37016: TRUNCATE TABLE removes some rows but not all -# - ---disable_warnings -DROP TABLE IF EXISTS t1,t2; ---enable_warnings - -CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; -CREATE TABLE t2 (id INT PRIMARY KEY, - t1_id INT, INDEX par_ind (t1_id), - FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB; -INSERT INTO t1 VALUES (1),(2); -INSERT INTO t2 VALUES (3,2); - -SET AUTOCOMMIT = 0; - -START TRANSACTION; ---error ER_ROW_IS_REFERENCED_2 -TRUNCATE TABLE t1; -SELECT * FROM t1; -COMMIT; -SELECT * FROM t1; - -START TRANSACTION; ---error ER_ROW_IS_REFERENCED_2 -TRUNCATE TABLE t1; -SELECT * FROM t1; -ROLLBACK; -SELECT * FROM t1; - -SET AUTOCOMMIT = 1; - -START TRANSACTION; -SELECT * FROM t1; -COMMIT; - ---error ER_ROW_IS_REFERENCED_2 -TRUNCATE TABLE t1; -SELECT * FROM t1; -DELETE FROM t2 WHERE id = 3; - -START TRANSACTION; -SELECT * FROM t1; -TRUNCATE TABLE t1; -ROLLBACK; -SELECT * FROM t1; -TRUNCATE TABLE t2; - -DROP TABLE t2; -DROP TABLE t1; - ---echo # ---echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0 ---echo # -CREATE TABLE t1 ( - id INT UNSIGNED NOT NULL AUTO_INCREMENT, - PRIMARY KEY (id) -) ENGINE=InnoDB; - -CREATE TABLE t2 ( - id INT UNSIGNED NOT NULL AUTO_INCREMENT, - aid INT UNSIGNED NOT NULL, - PRIMARY KEY (id), - FOREIGN KEY (aid) REFERENCES t1 (id) -) ENGINE=InnoDB; - -CREATE TABLE t3 ( - bid INT UNSIGNED NOT NULL, - FOREIGN KEY (bid) REFERENCES t2 (id) -) ENGINE=InnoDB; - -CREATE TABLE t4 ( - a INT -) ENGINE=InnoDB; - -CREATE TABLE t5 ( - a INT -) ENGINE=InnoDB; - -INSERT INTO t1 (id) VALUES (1); -INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1); -INSERT INTO t3 (bid) VALUES (1); - -INSERT INTO t4 VALUES (1),(2),(3),(4),(5); -INSERT INTO t5 VALUES (1); - -DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a; - ---error ER_ROW_IS_REFERENCED_2 -DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; ---error ER_ROW_IS_REFERENCED_2 -DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; - -DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; - -DROP TABLE t3; -DROP TABLE t2; -DROP TABLE t1; -DROP TABLES t4,t5; - ---echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0 ---echo # Testing for any side effects of IGNORE on AFTER DELETE triggers used with ---echo # transactional tables. ---echo # -CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; -CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB; -CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; -CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT, - FOREIGN KEY (t1i) REFERENCES t1(i)) - ENGINE=InnoDB; -delimiter ||; -CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW -BEGIN - SET @b:='EXECUTED TRIGGER'; - INSERT INTO t2 VALUES (@b); - SET @a:= error_happens_here; -END|| -delimiter ;|| - -SET @b:=""; -SET @a:=""; -INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t3 SELECT * FROM t1; ---echo ** An error in a trigger causes rollback of the statement. ---error ER_BAD_FIELD_ERROR -DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; -SELECT @a,@b; -SELECT * FROM t2; -SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; - ---echo ** Same happens with the IGNORE option ---error ER_BAD_FIELD_ERROR -DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; -SELECT * FROM t2; -SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; - ---echo ** ---echo ** The following is an attempt to demonstrate ---echo ** error handling inside a row iteration. ---echo ** -DROP TRIGGER trg; -TRUNCATE TABLE t1; -TRUNCATE TABLE t2; -TRUNCATE TABLE t3; - -INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t3 VALUES (1),(2),(3),(4); -INSERT INTO t4 VALUES (3,3),(4,4); - -delimiter ||; -CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW -BEGIN - SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR)); - INSERT INTO t2 VALUES (@b); -END|| -delimiter ;|| - ---echo ** DELETE is prevented by foreign key constrains but errors are silenced. ---echo ** The AFTER trigger isn't fired. -DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; ---echo ** Tables are modified by best effort: -SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; ---echo ** The AFTER trigger was only executed on successful rows: -SELECT * FROM t2; - -DROP TRIGGER trg; - ---echo ** ---echo ** Induce an error midway through an AFTER-trigger ---echo ** -TRUNCATE TABLE t4; -TRUNCATE TABLE t1; -TRUNCATE TABLE t3; -INSERT INTO t1 VALUES (1),(2),(3),(4); -INSERT INTO t3 VALUES (1),(2),(3),(4); -delimiter ||; -CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW -BEGIN - SET @a:= @a+1; - IF @a > 2 THEN - INSERT INTO t4 VALUES (5,5); - END IF; -END|| -delimiter ;|| - -SET @a:=0; ---echo ** Errors in the trigger causes the statement to abort. ---error ER_NO_REFERENCED_ROW_2 -DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i; -SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i; -SELECT * FROM t4; - -DROP TRIGGER trg; -DROP TABLE t4; -DROP TABLE t1; -DROP TABLE t2; -DROP TABLE t3; - -# -# Bug#43580: Issue with Innodb on multi-table update -# -CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB; -CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB; - -CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB; -CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB; - -INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6); -INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); - -INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106); -INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); - -UPDATE t1, t2 SET t1.a = t1.a + 100, t2.b = t1.a + 10 -WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b; ---sorted_result -SELECT * FROM t2; - -UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10 -WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100; ---sorted_result -SELECT * FROM t4; - -DROP TABLE t1, t2, t3, t4; - ---echo # ---echo # Bug#44886: SIGSEGV in test_if_skip_sort_order() - ---echo # uninitialized variable used as subscript ---echo # - -CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c)) - ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,1,1,0); - -CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB; -INSERT INTO t2 VALUES (1,1,2); - -CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM; -INSERT INTO t3 VALUES (1, 1); - -SELECT * FROM t1, t2, t3 - WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2 - GROUP BY t1.b; - -DROP TABLE t1, t2, t3; - ---echo # ---echo # Bug #45828: Optimizer won't use partial primary key if another ---echo # index can prevent filesort ---echo # - -# Create the table -CREATE TABLE `t1` ( - c1 int NOT NULL, - c2 int NOT NULL, - c3 int NOT NULL, - PRIMARY KEY (c1,c2), - KEY (c3) -) ENGINE=InnoDB; - -# populate with data -INSERT INTO t1 VALUES (5,2,1246276747); -INSERT INTO t1 VALUES (2,1,1246281721); -INSERT INTO t1 VALUES (7,3,1246281756); -INSERT INTO t1 VALUES (4,2,1246282139); -INSERT INTO t1 VALUES (3,1,1246282230); -INSERT INTO t1 VALUES (1,0,1246282712); -INSERT INTO t1 VALUES (8,3,1246282765); -INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1; -INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1; -INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1; -INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1; -INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1; -INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1; - -# query and no rows will match the c1 condition, whereas all will match c3 -SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; - -# SHOULD use the pk. -# index on c3 will be used instead of primary key -EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; - -# if we force the primary key, we can see the estimate is 1 -EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; - - -CREATE TABLE t2 ( - c1 int NOT NULL, - c2 int NOT NULL, - c3 int NOT NULL, - KEY (c1,c2), - KEY (c3) -) ENGINE=InnoDB; - -# SHOULD use the pk. -# if we switch it from a primary key to a regular index, it works correctly as well -explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3; - -DROP TABLE t1,t2; - - ---echo # ---echo # 36259: Optimizing with ORDER BY ---echo # - -CREATE TABLE t1 ( - a INT NOT NULL AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL, - d VARCHAR(5), - e INT NOT NULL, - PRIMARY KEY (a), KEY i2 (b,c,d) -) ENGINE=InnoDB; - -INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2); -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1; -EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a; -EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a; -EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a; - -DROP TABLE t1; - ---echo # ---echo # Bug #47963: Wrong results when index is used ---echo # -CREATE TABLE t1( - a VARCHAR(5) NOT NULL, - b VARCHAR(5) NOT NULL, - c DATETIME NOT NULL, - KEY (c) -) ENGINE=InnoDB; -INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00'); -SELECT * FROM t1 WHERE a = 'TEST' AND - c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00'; -SELECT * FROM t1 WHERE a = 'TEST' AND - c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0'; -SELECT * FROM t1 WHERE a = 'TEST' AND - c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00'; -SELECT * FROM t1 WHERE a = 'TEST' AND - c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0'; -SELECT * FROM t1 WHERE a = 'TEST' AND - c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000'; -SELECT * FROM t1 WHERE a = 'TEST' AND - c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001'; -SELECT * FROM t1 WHERE a = 'TEST' AND - c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; -EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND - c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; -DROP TABLE t1; - ---echo # ---echo # Bug #46175: NULL read_view and consistent read assertion ---echo # - -CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb; -CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb; -INSERT INTO t1 VALUES (),(); -INSERT INTO t2 VALUES (),(); -CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2 - WHERE b =(SELECT a FROM t1 LIMIT 1); - ---disable_query_log ---disable_result_log -CONNECT (con1, localhost, root,,); ---enable_query_log ---enable_result_log -CONNECTION default; - -DELIMITER |; -CREATE PROCEDURE p1(num INT) -BEGIN - DECLARE i INT DEFAULT 0; - REPEAT - SHOW CREATE VIEW v1; - SET i:=i+1; - UNTIL i>num END REPEAT; -END| -DELIMITER ;| - ---echo # Should not crash ---disable_query_log ---disable_result_log ---send CALL p1(1000) -CONNECTION con1; ---echo # Should not crash -CALL p1(1000); - -CONNECTION default; ---reap ---enable_query_log ---enable_result_log - -DISCONNECT con1; -DROP PROCEDURE p1; -DROP VIEW v1; -DROP TABLE t1,t2; - - ---echo # ---echo # Bug #49324: more valgrind errors in test_if_skip_sort_order ---echo # -CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ; ---echo #should not cause valgrind warnings -SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a; -DROP TABLE t1; - ---echo # ---echo # Bug#50843: Filesort used instead of clustered index led to ---echo # performance degradation. ---echo # -create table t1(f1 int not null primary key, f2 int) engine=innodb; -create table t2(f1 int not null, key (f1)) engine=innodb; -insert into t1 values (1,1),(2,2),(3,3); -insert into t2 values (1),(2),(3); -explain select t1.* from t1 left join t2 using(f1) group by t1.f1; -drop table t1,t2; ---echo # - - ---echo # ---echo # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may ---echo # corrupt definition at engine ---echo # - -CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b)) - ENGINE=InnoDB; - -ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b); - ---query_vertical SHOW INDEXES FROM t1; - -DROP TABLE t1; - ---echo # ---echo # Bug #53334: wrong result for outer join with impossible ON condition ---echo # (see the same test case for MyISAM in join.test) ---echo # - -create table t1 (id int primary key); -create table t2 (id int); - -insert into t1 values (75); -insert into t1 values (79); -insert into t1 values (78); -insert into t1 values (77); -replace into t1 values (76); -replace into t1 values (76); -insert into t1 values (104); -insert into t1 values (103); -insert into t1 values (102); -insert into t1 values (101); -insert into t1 values (105); -insert into t1 values (106); -insert into t1 values (107); - -insert into t2 values (107),(75),(1000); - -select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 - where t2.id=75 and t1.id is null; -explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 - where t2.id=75 and t1.id is null; - -drop table t1,t2; - ---echo # ---echo # Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when ---echo # JOINed during an UPDATE ---echo # - -CREATE TABLE t1 (d INT) ENGINE=InnoDB; -CREATE TABLE t2 (a INT, b INT, - c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP - ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB; - ---echo set up our data elements -INSERT INTO t1 (d) VALUES (1); -INSERT INTO t2 (a,b) VALUES (1,1); -SELECT SECOND(c) INTO @bug47453 FROM t2; - -SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; -UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1; -SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; - -SELECT SLEEP(1); - -UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1; - ---echo #should be 0 -SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a; - -DROP TABLE t1, t2; - ---echo End of 5.1 tests diff --git a/mysql-test/t/innodb_mysql_lock2.test b/mysql-test/t/innodb_mysql_lock2.test new file mode 100644 index 00000000000..79698bcd898 --- /dev/null +++ b/mysql-test/t/innodb_mysql_lock2.test @@ -0,0 +1,803 @@ +# This test covers behavior for InnoDB tables. +--source include/have_innodb.inc +# This test requires statement/mixed mode binary logging. +# Row-based mode puts weaker serializability requirements +# so weaker locks are acquired for it. +--source include/have_binlog_format_mixed_or_statement.inc +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + +--echo # +--echo # Test how do we handle locking in various cases when +--echo # we read data from InnoDB tables. +--echo # +--echo # In fact by performing this test we check two things: +--echo # 1) That SQL-layer correctly determine type of thr_lock.c +--echo # lock to be acquired/passed to InnoDB engine. +--echo # 2) That InnoDB engine correctly interprets this lock +--echo # type and takes necessary row locks or does not +--echo # take them if they are not necessary. +--echo # + +--echo # This test makes sense only in REPEATABLE-READ mode as +--echo # in SERIALIZABLE mode all statements that read data take +--echo # shared lock on them to enforce its semantics. +select @@session.tx_isolation; + +--echo # Prepare playground by creating tables, views, +--echo # routines and triggers used in tests. +connect (con1, localhost, root,,); +connection default; +--disable_warnings +drop table if exists t0, t1, t2, t3, t4, t5, te; +drop view if exists v1, v2; +drop procedure if exists p1; +drop procedure if exists p2; +drop function if exists f1; +drop function if exists f2; +drop function if exists f3; +drop function if exists f4; +drop function if exists f5; +drop function if exists f6; +drop function if exists f7; +drop function if exists f8; +drop function if exists f9; +drop function if exists f10; +drop function if exists f11; +drop function if exists f12; +drop function if exists f13; +drop function if exists f14; +drop function if exists f15; +--enable_warnings +create table t1 (i int primary key) engine=innodb; +insert into t1 values (1), (2), (3), (4), (5); +create table t2 (j int primary key) engine=innodb; +insert into t2 values (1), (2), (3), (4), (5); +create table t3 (k int primary key) engine=innodb; +insert into t3 values (1), (2), (3); +create table t4 (l int primary key) engine=innodb; +insert into t4 values (1); +create table t5 (l int primary key) engine=innodb; +insert into t5 values (1); +create table te(e int primary key); +insert into te values (1); +create view v1 as select i from t1; +create view v2 as select j from t2 where j in (select i from t1); +create procedure p1(k int) insert into t2 values (k); +delimiter |; +create function f1() returns int +begin + declare j int; + select i from t1 where i = 1 into j; + return j; +end| +create function f2() returns int +begin + declare k int; + select i from t1 where i = 1 into k; + insert into t2 values (k + 5); + return 0; +end| +create function f3() returns int +begin + return (select i from t1 where i = 3); +end| +create function f4() returns int +begin + if (select i from t1 where i = 3) then + return 1; + else + return 0; + end if; +end| +create function f5() returns int +begin + insert into t2 values ((select i from t1 where i = 1) + 5); + return 0; +end| +create function f6() returns int +begin + declare k int; + select i from v1 where i = 1 into k; + return k; +end| +create function f7() returns int +begin + declare k int; + select j from v2 where j = 1 into k; + return k; +end| +create function f8() returns int +begin + declare k int; + select i from v1 where i = 1 into k; + insert into t2 values (k+5); + return k; +end| +create function f9() returns int +begin + update v2 set j=j+10 where j=1; + return 1; +end| +create function f10() returns int +begin + return f1(); +end| +create function f11() returns int +begin + declare k int; + set k= f1(); + insert into t2 values (k+5); + return k; +end| +create function f12(p int) returns int +begin + insert into t2 values (p); + return p; +end| +create function f13(p int) returns int +begin + return p; +end| +create procedure p2(inout p int) +begin + select i from t1 where i = 1 into p; +end| +create function f14() returns int +begin + declare k int; + call p2(k); + insert into t2 values (k+5); + return k; +end| +create function f15() returns int +begin + declare k int; + call p2(k); + return k; +end| +create trigger t4_bi before insert on t4 for each row +begin + declare k int; + select i from t1 where i=1 into k; + set new.l= k+1; +end| +create trigger t4_bu before update on t4 for each row +begin + if (select i from t1 where i=1) then + set new.l= 2; + end if; +end| +--echo # Trigger below uses insertion of duplicate key in 'te' +--echo # table as a way to abort delete operation. +create trigger t4_bd before delete on t4 for each row +begin + if !(select i from v1 where i=1) then + insert into te values (1); + end if; +end| +create trigger t5_bi before insert on t5 for each row +begin + set new.l= f1()+1; +end| +create trigger t5_bu before update on t5 for each row +begin + declare j int; + call p2(j); + set new.l= j + 1; +end| +delimiter ;| + +--echo # +--echo # Set common variables to be used by scripts called below. +--echo # +let $con_aux= con1; +let $table= t1; + + +--echo # +--echo # 1. Statements that read tables and do not use subqueries. +--echo # + +--echo # +--echo # 1.1 Simple SELECT statement. +--echo # +--echo # No locks are necessary as this statement won't be written +--echo # to the binary log and InnoDB supports snapshots. +let $statement= select * from t1; +--source include/check_no_row_lock.inc + +--echo # +--echo # 1.2 Multi-UPDATE statement. +--echo # +--echo # Has to take shared locks on rows in the table being read as this +--echo # statement will be written to the binary log and therefore should +--echo # be serialized with concurrent statements. +let $statement= update t2, t1 set j= j - 1 where i = j; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 1.3 Multi-DELETE statement. +--echo # +--echo # The above is true for this statement as well. +let $statement= delete t2 from t1, t2 where i = j; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 1.4 DESCRIBE statement. +--echo # +--echo # This statement does not really read data from the +--echo # target table and thus does not take any lock on it. +--echo # We check this for completeness of coverage. +let $statement= describe t1; +--source include/check_no_row_lock.inc + +--echo # +--echo # 1.5 SHOW statements. +--echo # +--echo # The above is true for SHOW statements as well. +let $statement= show create table t1; +--source include/check_no_row_lock.inc +let $statement= show keys from t1; +--source include/check_no_row_lock.inc + + +--echo # +--echo # 2. Statements which read tables through subqueries. +--echo # + +--echo # +--echo # 2.1 CALL with a subquery. +--echo # +--echo # A strong lock is not necessary as this statement is not +--echo # written to the binary log as a whole (it is written +--echo # statement-by-statement) and thanks to MVCC we can always get +--echo # versions of rows prior to the update that has locked them. +--echo # But in practice InnoDB does locking reads for all statements +--echo # other than SELECT (unless it is a READ-COMITTED mode or +--echo # innodb_locks_unsafe_for_binlog is ON). +let $statement= call p1((select i + 5 from t1 where i = 1)); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.2 CREATE TABLE with a subquery. +--echo # +--echo # Has to take shared locks on rows in the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent statements. +let $statement= create table t0 engine=innodb select * from t1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +drop table t0; +let $statement= create table t0 engine=innodb select j from t2 where j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +drop table t0; + +--echo # +--echo # 2.3 DELETE with a subquery. +--echo # +--echo # The above is true for this statement as well. +let $statement= delete from t2 where j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.4 MULTI-DELETE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= delete t2 from t3, t2 where k = j and j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.5 DO with a subquery. +--echo # +--echo # In theory should not take row locks as it is not logged. +--echo # In practice InnoDB takes shared row locks. +let $statement= do (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.6 INSERT with a subquery. +--echo # +--echo # Has to take shared locks on rows in the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent statements. +let $statement= insert into t2 select i+5 from t1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= insert into t2 values ((select i+5 from t1 where i = 4)); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.7 LOAD DATA with a subquery. +--echo # +--echo # The above is true for this statement as well. +let $statement= load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.8 REPLACE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= replace into t2 select i+5 from t1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= replace into t2 values ((select i+5 from t1 where i = 4)); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.9 SELECT with a subquery. +--echo # +--echo # Locks are not necessary as this statement is not written +--echo # to the binary log and thanks to MVCC we can always get +--echo # versions of rows prior to the update that has locked them. +--echo # +--echo # Also serves as a test case for bug #46947 "Embedded SELECT +--echo # without FOR UPDATE is causing a lock". +let $statement= select * from t2 where j in (select i from t1); +--source include/check_no_row_lock.inc + +--echo # +--echo # 2.10 SET with a subquery. +--echo # +--echo # In theory should not require locking as it is not written +--echo # to the binary log. In practice InnoDB acquires shared row +--echo # locks. +let $statement= set @a:= (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.11 SHOW with a subquery. +--echo # +--echo # Similarly to the previous case, in theory should not require locking +--echo # as it is not written to the binary log. In practice InnoDB +--echo # acquires shared row locks. +let $statement= show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= show columns from t2 where (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.12 UPDATE with a subquery. +--echo # +--echo # Has to take shared locks on rows in the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent statements. +let $statement= update t2 set j= j-10 where j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.13 MULTI-UPDATE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= update t2, t3 set j= j -10 where j=k and j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + + +--echo # +--echo # 3. Statements which read tables through a view. +--echo # + +--echo # +--echo # 3.1 SELECT statement which uses some table through a view. +--echo # +--echo # Since this statement is not written to the binary log +--echo # and old version of rows are accessible thanks to MVCC, +--echo # no locking is necessary. +let $statement= select * from v1; +--source include/check_no_row_lock.inc +let $statement= select * from v2; +--source include/check_no_row_lock.inc +let $statement= select * from t2 where j in (select i from v1); +--source include/check_no_row_lock.inc +let $statement= select * from t3 where k in (select j from v2); +--source include/check_no_row_lock.inc + +--echo # +--echo # 3.2 Statements which modify a table and use views. +--echo # +--echo # Since such statements are going to be written to the binary +--echo # log they need to be serialized against concurrent statements +--echo # and therefore should take shared row locks on data read. +let $statement= update t2 set j= j-10 where j in (select i from v1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= update t3 set k= k-10 where k in (select j from v2); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= update t2, v1 set j= j-10 where j = i; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= update v2 set j= j-10 where j = 3; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + + +--echo # +--echo # 4. Statements which read tables through stored functions. +--echo # + +--echo # +--echo # 4.1 SELECT/SET with a stored function which does not +--echo # modify data and uses SELECT in its turn. +--echo # +--echo # Calls to such functions won't get into the binary log and +--echo # thus don't need to acquire strong locks. +--echo # In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +--echo # used stored functions may lead to broken SBR" strong locks +--echo # are taken (we accepted it as a trade-off for this fix). +let $statement= select f1(); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_no_row_lock.inc +let $statement= set @a:= f1(); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_no_row_lock.inc + +--echo # +--echo # 4.2 INSERT (or other statement which modifies data) with +--echo # a stored function which does not modify data and uses +--echo # SELECT. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data +--echo # it uses. Therefore it should take row locks on the data +--echo # it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken. +let $statement= insert into t2 values (f1() + 5); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_no_row_lock.inc + +--echo # +--echo # 4.3 SELECT/SET with a stored function which +--echo # reads and modifies data. +--echo # +--echo # Since a call to such function is written to the binary log, +--echo # it should be serialized with concurrent statements affecting +--echo # the data it uses. Hence, row locks on the data read +--echo # should be taken. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken. +let $statement= select f2(); +let $wait_statement= select i from t1 where i = 1 into k; +--source include/check_no_row_lock.inc +let $statement= set @a:= f2(); +let $wait_statement= select i from t1 where i = 1 into k; +--source include/check_no_row_lock.inc + +--echo # +--echo # 4.4. SELECT/SET with a stored function which does not +--echo # modify data and reads a table through subselect +--echo # in a control construct. +--echo # +--echo # Again, in theory a call to this function won't get to the +--echo # binary log and thus no locking is needed. But in practice +--echo # we don't detect this fact early enough (get_lock_type_for_table()) +--echo # to avoid taking row locks. +let $statement= select f3(); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= set @a:= f3(); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= select f4(); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= set @a:= f4(); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.5. INSERT (or other statement which modifies data) with +--echo # a stored function which does not modify data and reads +--echo # the table through a subselect in one of its control +--echo # constructs. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting data it +--echo # uses. Therefore it should take row locks on the data +--echo # it reads. +let $statement= insert into t2 values (f3() + 5); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= insert into t2 values (f4() + 6); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.6 SELECT/SET which uses a stored function with +--echo # DML which reads a table via a subquery. +--echo # +--echo # Since call to such function is written to the binary log +--echo # it should be serialized with concurrent statements. +--echo # Hence reads should take row locks. +let $statement= select f5(); +let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5); +--source include/check_shared_row_lock.inc +let $statement= set @a:= f5(); +let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5); +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.7 SELECT/SET which uses a stored function which +--echo # doesn't modify data and reads tables through +--echo # a view. +--echo # +--echo # Once again, in theory, calls to such functions won't +--echo # get into the binary log and thus don't need row +--echo # locks. In practice this fact is discovered +--echo # too late to have any effect. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken +--echo # in case of simple SELECT. +let $statement= select f6(); +let $wait_statement= select i from v1 where i = 1 into k; +--source include/check_no_row_lock.inc +let $statement= set @a:= f6(); +let $wait_statement= select i from v1 where i = 1 into k; +--source include/check_no_row_lock.inc +let $statement= select f7(); +let $wait_statement= select j from v2 where j = 1 into k; +--source include/check_shared_row_lock.inc +let $statement= set @a:= f7(); +let $wait_statement= select j from v2 where j = 1 into k; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.8 INSERT which uses stored function which +--echo # doesn't modify data and reads a table +--echo # through a view. +--echo # +--echo # Since such statement is written to the binary log and +--echo # should be serialized with concurrent statements affecting +--echo # the data it uses. Therefore it should take row locks on +--echo # the rows it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken +--echo # in case of simple SELECT. +let $statement= insert into t3 values (f6() + 5); +let $wait_statement= select i from v1 where i = 1 into k; +--source include/check_no_row_lock.inc +let $statement= insert into t3 values (f7() + 5); +let $wait_statement= select j from v2 where j = 1 into k; +--source include/check_shared_row_lock.inc + + +--echo # +--echo # 4.9 SELECT which uses a stored function which +--echo # modifies data and reads tables through a view. +--echo # +--echo # Since a call to such function is written to the binary log +--echo # it should be serialized with concurrent statements. +--echo # Hence, reads should take row locks. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken +--echo # in case of simple SELECT. +let $statement= select f8(); +let $wait_statement= select i from v1 where i = 1 into k; +--source include/check_no_row_lock.inc +let $statement= select f9(); +let $wait_statement= update v2 set j=j+10 where j=1; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.10 SELECT which uses stored function which doesn't modify +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Calls to such functions won't get into the binary log and +--echo # thus don't need to acquire strong locks. +--echo # In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +--echo # used stored functions may lead to broken SBR" strong locks +--echo # are taken (we accepted it as a trade-off for this fix). +let $statement= select f10(); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_no_row_lock.inc + +--echo # +--echo # 4.11 INSERT which uses a stored function which doesn't modify +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Since such statement is written to the binary log, it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take row locks on data it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken. +let $statement= insert into t2 values (f10() + 5); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_no_row_lock.inc + +--echo # +--echo # 4.12 SELECT which uses a stored function which modifies +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Since a call to such function is written to the binary log +--echo # it should be serialized from concurrent statements. +--echo # Hence, reads should take row locks. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken. +let $statement= select f11(); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_no_row_lock.inc + +--echo # +--echo # 4.13 SELECT that reads a table through a subquery passed +--echo # as a parameter to a stored function which modifies +--echo # data. +--echo # +--echo # Even though a call to this function is written to the +--echo # binary log, values of its parameters are written as literals. +--echo # So there is no need to acquire row locks on rows used in +--echo # the subquery. +--echo # But due to the fact that in 5.1 for prelocked statements +--echo # THD::in_lock_tables is set to TRUE we acquire strong locks +--echo # (see also bug#44613 "SELECT statement inside FUNCTION takes +--echo # a shared lock" [sic!!!]). +let $statement= select f12((select i+10 from t1 where i=1)); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.14 INSERT that reads a table via a subquery passed +--echo # as a parameter to a stored function which doesn't +--echo # modify data. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take row locks on the data it reads. +let $statement= insert into t2 values (f13((select i+10 from t1 where i=1))); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + + +--echo # +--echo # 5. Statements that read tables through stored procedures. +--echo # + +--echo # +--echo # 5.1 CALL statement which reads a table via SELECT. +--echo # +--echo # Since neither this statement nor its components are +--echo # written to the binary log, there is no need to take +--echo # row locks on the data it reads. +let $statement= call p2(@a); +--source include/check_no_row_lock.inc + +--echo # +--echo # 5.2 Function that modifies data and uses CALL, +--echo # which reads a table through SELECT. +--echo # +--echo # Since a call to such function is written to the binary +--echo # log, it should be serialized with concurrent statements. +--echo # Hence, in this case reads should take row locks on data. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken. +let $statement= select f14(); +let $wait_statement= select i from t1 where i = 1 into p; +--source include/check_no_row_lock.inc + +--echo # +--echo # 5.3 SELECT that calls a function that doesn't modify data and +--echo # uses a CALL statement that reads a table via SELECT. +--echo # +--echo # Calls to such functions won't get into the binary log and +--echo # thus don't need to acquire strong locks. +--echo # In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +--echo # used stored functions may lead to broken SBR" strong locks +--echo # are taken (we accepted it as a trade-off for this fix). +let $statement= select f15(); +let $wait_statement= select i from t1 where i = 1 into p; +--source include/check_no_row_lock.inc + +--echo # +--echo # 5.4 INSERT which calls function which doesn't modify data and +--echo # uses CALL statement which reads table through SELECT. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting data it +--echo # uses. Therefore it should take row locks on data it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken. +let $statement= insert into t2 values (f15()+5); +let $wait_statement= select i from t1 where i = 1 into p; +--source include/check_no_row_lock.inc + + +--echo # +--echo # 6. Statements that use triggers. +--echo # + +--echo # +--echo # 6.1 Statement invoking a trigger that reads table via SELECT. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data +--echo # it uses. Therefore, it should take row locks on the data +--echo # it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken. +let $statement= insert into t4 values (2); +let $wait_statement= select i from t1 where i=1 into k; +--source include/check_no_row_lock.inc + +--echo # +--echo # 6.2 Statement invoking a trigger that reads table through +--echo # a subquery in a control construct. +--echo # +--echo # The above is true for this statement as well. +let $statement= update t4 set l= 2 where l = 1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 6.3 Statement invoking a trigger that reads a table through +--echo # a view. +--echo # +--echo # And for this statement. +let $statement= delete from t4 where l = 1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 6.4 Statement invoking a trigger that reads a table through +--echo # a stored function. +--echo # +--echo # And for this statement. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken. +let $statement= insert into t5 values (2); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_no_row_lock.inc + +--echo # +--echo # 6.5 Statement invoking a trigger that reads a table through +--echo # stored procedure. +--echo # +--echo # And for this statement. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" no lock is taken. +let $statement= update t5 set l= 2 where l = 1; +let $wait_statement= select i from t1 where i = 1 into p; +--source include/check_no_row_lock.inc + +--echo # Clean-up. +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop function f8; +drop function f9; +drop function f10; +drop function f11; +drop function f12; +drop function f13; +drop function f14; +drop function f15; +drop view v1, v2; +drop procedure p1; +drop procedure p2; +drop table t1, t2, t3, t4, t5, te; +disconnect con1; + +# Check that all connections opened by test cases in this file are really +# gone so execution of other tests won't be affected by their presence. +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/innodb_mysql_rbk-master.opt b/mysql-test/t/innodb_mysql_rbk-master.opt deleted file mode 100644 index 0e400f9c36b..00000000000 --- a/mysql-test/t/innodb_mysql_rbk-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb_lock_wait_timeout=1 --innodb_rollback_on_timeout=1 diff --git a/mysql-test/t/innodb_mysql_rbk.test b/mysql-test/t/innodb_mysql_rbk.test deleted file mode 100644 index d2368c81f95..00000000000 --- a/mysql-test/t/innodb_mysql_rbk.test +++ /dev/null @@ -1,35 +0,0 @@ --- source include/have_innodb.inc - -# -# Bug #41453: Assertion `m_status == DA_ERROR' failed in -# Diagnostics_area::sql_errno -# - -CREATE TABLE t1(a INT, b INT NOT NULL, PRIMARY KEY (a)) ENGINE=innodb -DEFAULT CHARSET=latin1; -INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); -CONNECT (con1,localhost,root,,); -CONNECT (con2,localhost,root,,); - -CONNECTION con1; -START TRANSACTION; -SELECT * FROM t1 WHERE b=3 LIMIT 1 FOR UPDATE; -CONNECTION con2; -START TRANSACTION; ---error ER_LOCK_WAIT_TIMEOUT -UPDATE t1 SET b=b+12 WHERE a > 2 ORDER BY a; -ROLLBACK; - -CONNECTION con1; -START TRANSACTION; -SELECT * FROM t1 WHERE b=3 LIMIT 1 FOR UPDATE; -CONNECTION con2; -START TRANSACTION; ---error ER_LOCK_WAIT_TIMEOUT -UPDATE t1 SET b=10 WHERE a > 1 ORDER BY a; -SELECT * FROM t1 WHERE b = 10; - -CONNECTION default; -DISCONNECT con1; -DISCONNECT con2; -DROP TABLE t1; diff --git a/mysql-test/t/innodb_notembedded.test b/mysql-test/t/innodb_notembedded.test deleted file mode 100644 index c74dc931505..00000000000 --- a/mysql-test/t/innodb_notembedded.test +++ /dev/null @@ -1,50 +0,0 @@ --- source include/not_embedded.inc --- source include/have_innodb.inc - ---disable_warnings -drop table if exists t1; ---enable_warnings - -SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; - -connect (a,localhost,root,,); -connect (b,localhost,root,,); - - -# -# BUG#11238 - in prelocking mode SELECT .. FOR UPDATE is changed to -# non-blocking SELECT -# -SET GLOBAL log_bin_trust_function_creators = 1; -create table t1 (col1 integer primary key, col2 integer) engine=innodb; -insert t1 values (1,100); -delimiter |; -create function f1 () returns integer begin -declare var1 int; -select col2 into var1 from t1 where col1=1 for update; -return var1; -end| -delimiter ;| -start transaction; -select f1(); -connection b; -send update t1 set col2=0 where col1=1; -connection default; -select * from t1; -connection a; -rollback; -connection b; -reap; -rollback; - -# Cleanup -connection a; -disconnect a; ---source include/wait_until_disconnected.inc -connection b; -disconnect b; ---source include/wait_until_disconnected.inc -connection default; -drop table t1; -drop function f1; -SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; diff --git a/mysql-test/t/innodb_timeout_rollback-master.opt b/mysql-test/t/innodb_timeout_rollback-master.opt deleted file mode 100644 index 50921bb4df0..00000000000 --- a/mysql-test/t/innodb_timeout_rollback-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb_lock_wait_timeout=2 --innodb_rollback_on_timeout diff --git a/mysql-test/t/innodb_timeout_rollback.test b/mysql-test/t/innodb_timeout_rollback.test deleted file mode 100644 index 99890971064..00000000000 --- a/mysql-test/t/innodb_timeout_rollback.test +++ /dev/null @@ -1,5 +0,0 @@ --- source include/have_innodb.inc - ---source include/innodb_rollback_on_timeout.inc - ---echo End of 5.0 tests diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index e3d68d71603..cf881e6aaa2 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -913,4 +913,72 @@ WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); DROP TABLE t1, t2; +--echo # +--echo # Bug#52357: Assertion failed: join->best_read in greedy_search +--echo # optimizer_search_depth=0 +--echo # +CREATE TABLE t1( a INT ); + +INSERT INTO t1 VALUES (1),(2); +SET optimizer_search_depth = 0; + +--echo # Should not core dump on query preparation +EXPLAIN +SELECT 1 +FROM t1 tt3 LEFT OUTER JOIN t1 tt4 ON 1 + LEFT OUTER JOIN t1 tt5 ON 1 + LEFT OUTER JOIN t1 tt6 ON 1 + LEFT OUTER JOIN t1 tt7 ON 1 + LEFT OUTER JOIN t1 tt8 ON 1 + RIGHT OUTER JOIN t1 tt2 ON 1 + RIGHT OUTER JOIN t1 tt1 ON 1 + STRAIGHT_JOIN t1 tt9 ON 1; + +SET optimizer_search_depth = DEFAULT; +DROP TABLE t1; + +--echo # +--echo # Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result +--echo # +CREATE TABLE t1 (f1 INT NOT NULL); +INSERT INTO t1 VALUES (9),(0); + +CREATE TABLE t2 (f1 INT NOT NULL); +INSERT INTO t2 VALUES +(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1); + +SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 +RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; + +EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 +RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990 +--echo # +CREATE TABLE t1(f1 INT, PRIMARY KEY (f1)); +INSERT INTO t1 VALUES (1),(2); + +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 + LEFT JOIN t1 AS jt2 + RIGHT JOIN t1 AS jt3 + JOIN t1 AS jt4 ON 1 + LEFT JOIN t1 AS jt5 ON 1 + ON 1 + RIGHT JOIN t1 AS jt6 ON jt6.f1 + ON 1; + +EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 + RIGHT JOIN t1 AS jt2 + RIGHT JOIN t1 AS jt3 + JOIN t1 AS jt4 ON 1 + LEFT JOIN t1 AS jt5 ON 1 + ON 1 + RIGHT JOIN t1 AS jt6 ON jt6.f1 + ON 1; + +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 126bd5c8838..e24f0b16705 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -560,4 +560,24 @@ let $MYSQLD_DATADIR= `select @@datadir`; remove_file $MYSQLD_DATADIR/test/t1.txt; +--echo # +--echo # Bug #52512 : Assertion `! is_set()' in +--echo # Diagnostics_area::set_ok_status on LOAD DATA +--echo # + +connect (con1,localhost,root,,test); + +CREATE TABLE t1 (id INT NOT NULL); +--send LOAD DATA LOCAL INFILE 'tb.txt' INTO TABLE t1 +# please keep this is a spearate test file : it's important to have no +# commands after this one + +connection default; +dirty_close con1; + +connect (con1,localhost,root,,test); +DROP TABLE t1; +connection default; +disconnect con1; + --echo End of 5.1 tests diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test new file mode 100644 index 00000000000..17f8abb75f3 --- /dev/null +++ b/mysql-test/t/lock_sync.test @@ -0,0 +1,867 @@ +# +# Locking related tests which use DEBUG_SYNC facility. +# +--source include/have_debug_sync.inc +# This test requires statement/mixed mode binary logging. +# Row-based mode puts weaker serializability requirements +# so weaker locks are acquired for it. +--source include/have_binlog_format_mixed_or_statement.inc + +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + + +--echo # +--echo # Test how we handle locking in various cases when +--echo # we read data from MyISAM tables. +--echo # +--echo # In this test we mostly check that the SQL-layer correctly +--echo # determines the type of thr_lock.c lock for a table being +--echo # read. +--echo # I.e. that it disallows concurrent inserts when the statement +--echo # is going to be written to the binary log and therefore +--echo # should be serialized, and allows concurrent inserts when +--echo # such serialization is not necessary (e.g. when +--echo # the statement is not written to binary log). +--echo # + +--echo # Force concurrent inserts to be performed even if the table +--echo # has gaps. This allows to simplify clean up in scripts +--echo # used below (instead of backing up table being inserted +--echo # into and then restoring it from backup at the end of the +--echo # script we can simply delete rows which were inserted). +set @old_concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert= 2; +select @@global.concurrent_insert; + +--echo # Prepare playground by creating tables, views, +--echo # routines and triggers used in tests. +connect (con1, localhost, root,,); +connect (con2, localhost, root,,); +connection default; +--disable_warnings +drop table if exists t0, t1, t2, t3, t4, t5, te; +drop view if exists v1, v2; +drop procedure if exists p1; +drop procedure if exists p2; +drop function if exists f1; +drop function if exists f2; +drop function if exists f3; +drop function if exists f4; +drop function if exists f5; +drop function if exists f6; +drop function if exists f7; +drop function if exists f8; +drop function if exists f9; +drop function if exists f10; +drop function if exists f11; +drop function if exists f12; +drop function if exists f13; +drop function if exists f14; +drop function if exists f15; +--enable_warnings +create table t1 (i int primary key); +insert into t1 values (1), (2), (3), (4), (5); +create table t2 (j int primary key); +insert into t2 values (1), (2), (3), (4), (5); +create table t3 (k int primary key); +insert into t3 values (1), (2), (3); +create table t4 (l int primary key); +insert into t4 values (1); +create table t5 (l int primary key); +insert into t5 values (1); +create table te(e int primary key); +insert into te values (1); +create view v1 as select i from t1; +create view v2 as select j from t2 where j in (select i from t1); +create procedure p1(k int) insert into t2 values (k); +delimiter |; +create function f1() returns int +begin + declare j int; + select i from t1 where i = 1 into j; + return j; +end| +create function f2() returns int +begin + declare k int; + select i from t1 where i = 1 into k; + insert into t2 values (k + 5); + return 0; +end| +create function f3() returns int +begin + return (select i from t1 where i = 3); +end| +create function f4() returns int +begin + if (select i from t1 where i = 3) then + return 1; + else + return 0; + end if; +end| +create function f5() returns int +begin + insert into t2 values ((select i from t1 where i = 1) + 5); + return 0; +end| +create function f6() returns int +begin + declare k int; + select i from v1 where i = 1 into k; + return k; +end| +create function f7() returns int +begin + declare k int; + select j from v2 where j = 1 into k; + return k; +end| +create function f8() returns int +begin + declare k int; + select i from v1 where i = 1 into k; + insert into t2 values (k+5); + return k; +end| +create function f9() returns int +begin + update v2 set j=j+10 where j=1; + return 1; +end| +create function f10() returns int +begin + return f1(); +end| +create function f11() returns int +begin + declare k int; + set k= f1(); + insert into t2 values (k+5); + return k; +end| +create function f12(p int) returns int +begin + insert into t2 values (p); + return p; +end| +create function f13(p int) returns int +begin + return p; +end| +create procedure p2(inout p int) +begin + select i from t1 where i = 1 into p; +end| +create function f14() returns int +begin + declare k int; + call p2(k); + insert into t2 values (k+5); + return k; +end| +create function f15() returns int +begin + declare k int; + call p2(k); + return k; +end| +create trigger t4_bi before insert on t4 for each row +begin + declare k int; + select i from t1 where i=1 into k; + set new.l= k+1; +end| +create trigger t4_bu before update on t4 for each row +begin + if (select i from t1 where i=1) then + set new.l= 2; + end if; +end| +--echo # Trigger below uses insertion of duplicate key in 'te' +--echo # table as a way to abort delete operation. +create trigger t4_bd before delete on t4 for each row +begin + if !(select i from v1 where i=1) then + insert into te values (1); + end if; +end| +create trigger t5_bi before insert on t5 for each row +begin + set new.l= f1()+1; +end| +create trigger t5_bu before update on t5 for each row +begin + declare j int; + call p2(j); + set new.l= j + 1; +end| +delimiter ;| + +--echo # +--echo # Set common variables to be used by the scripts +--echo # called below. +--echo # +let $con_aux1= con1; +let $con_aux2= con2; +let $table= t1; + +--echo # Switch to connection 'con1'. +connection con1; +--echo # Cache all functions used in the tests below so statements +--echo # calling them won't need to open and lock mysql.proc table +--echo # and we can assume that each statement locks its tables +--echo # once during its execution. +--disable_result_log +show create procedure p1; +show create procedure p2; +show create function f1; +show create function f2; +show create function f3; +show create function f4; +show create function f5; +show create function f6; +show create function f7; +show create function f8; +show create function f9; +show create function f10; +show create function f11; +show create function f12; +show create function f13; +show create function f14; +show create function f15; +--enable_result_log +--echo # Switch back to connection 'default'. +connection default; + +--echo # +--echo # 1. Statements that read tables and do not use subqueries. +--echo # + +--echo # +--echo # 1.1 Simple SELECT statement. +--echo # +--echo # No locks are necessary as this statement won't be written +--echo # to the binary log and thanks to how MyISAM works SELECT +--echo # will see version of the table prior to concurrent insert. +let $statement= select * from t1; +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 1.2 Multi-UPDATE statement. +--echo # +--echo # Has to take shared locks on rows in the table being read as this +--echo # statement will be written to the binary log and therefore should +--echo # be serialized with concurrent statements. +let $statement= update t2, t1 set j= j - 1 where i = j; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 1.3 Multi-DELETE statement. +--echo # +--echo # The above is true for this statement as well. +let $statement= delete t2 from t1, t2 where i = j; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 1.4 DESCRIBE statement. +--echo # +--echo # This statement does not really read data from the +--echo # target table and thus does not take any lock on it. +--echo # We check this for completeness of coverage. +lock table t1 write; +--echo # Switching to connection 'con1'. +connection con1; +--echo # This statement should not be blocked. +--disable_result_log +describe t1; +--enable_result_log +--echo # Switching to connection 'default'. +connection default; +unlock tables; + +--echo # +--echo # 1.5 SHOW statements. +--echo # +--echo # The above is true for SHOW statements as well. +lock table t1 write; +--echo # Switching to connection 'con1'. +connection con1; +--echo # These statements should not be blocked. +# The below test for SHOW CREATE TABLE is disabled until bug 52593 +# "SHOW CREATE TABLE is blocked if table is locked for write by another +# connection" is fixed. +--disable_parsing +show create table t1; +--enable_parsing +--disable_result_log +show keys from t1; +--enable_result_log +--echo # Switching to connection 'default'. +connection default; +unlock tables; + + +--echo # +--echo # 2. Statements which read tables through subqueries. +--echo # + +--echo # +--echo # 2.1 CALL with a subquery. +--echo # +--echo # In theory strong lock is not necessary as this statement +--echo # is not written to the binary log as a whole (it is written +--echo # statement-by-statement). But in practice in 5.1 for +--echo # almost everything except SELECT we take strong lock. +let $statement= call p1((select i + 5 from t1 where i = 1)); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.2 CREATE TABLE with a subquery. +--echo # +--echo # Has to take a strong lock on the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent statements. +let $statement= create table t0 select * from t1; +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +drop table t0; +let $statement= create table t0 select j from t2 where j in (select i from t1); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +drop table t0; + +--echo # +--echo # 2.3 DELETE with a subquery. +--echo # +--echo # The above is true for this statement as well. +let $statement= delete from t2 where j in (select i from t1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.4 MULTI-DELETE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= delete t2 from t3, t2 where k = j and j in (select i from t1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 2.5 DO with a subquery. +--echo # +--echo # In theory strong lock is not necessary as it is not logged. +--echo # But in practice in 5.1 for almost everything except SELECT +--echo # we take strong lock. +let $statement= do (select i from t1 where i = 1); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.6 INSERT with a subquery. +--echo # +--echo # Has to take a strong lock on the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent inserts. +let $statement= insert into t2 select i+5 from t1; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= insert into t2 values ((select i+5 from t1 where i = 4)); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.7 LOAD DATA with a subquery. +--echo # +--echo # The above is true for this statement as well. +let $statement= load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.8 REPLACE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= replace into t2 select i+5 from t1; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= replace into t2 values ((select i+5 from t1 where i = 4)); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.9 SELECT with a subquery. +--echo # +--echo # Strong locks are not necessary as this statement is not written +--echo # to the binary log and thanks to how MyISAM works this statement +--echo # sees a version of the table prior to the concurrent insert. +let $statement= select * from t2 where j in (select i from t1); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 2.10 SET with a subquery. +--echo # +--echo # In theory the same is true for this statement as well. +--echo # But in practice in 5.1 we acquire strong lock in this +--echo # case as well. +let $statement= set @a:= (select i from t1 where i = 1); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.11 SHOW with a subquery. +--echo # +--echo # The same is true for this statement too. +let $statement= show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= show columns from t2 where (select i from t1 where i = 1); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.12 UPDATE with a subquery. +--echo # +--echo # Has to take a strong lock on the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent inserts. +let $statement= update t2 set j= j-10 where j in (select i from t1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.13 MULTI-UPDATE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= update t2, t3 set j= j -10 where j=k and j in (select i from t1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 3. Statements which read tables through a view. +--echo # + +--echo # +--echo # 3.1 SELECT statement which uses some table through a view. +--echo # +--echo # Since this statement is not written to the binary log and +--echo # an old version of the table is accessible thanks to how MyISAM +--echo # handles concurrent insert, no locking is necessary. +let $statement= select * from v1; +let $restore_table= ; +--source include/check_concurrent_insert.inc +let $statement= select * from v2; +let $restore_table= ; +--source include/check_concurrent_insert.inc +let $statement= select * from t2 where j in (select i from v1); +let $restore_table= ; +--source include/check_concurrent_insert.inc +let $statement= select * from t3 where k in (select j from v2); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 3.2 Statements which modify a table and use views. +--echo # +--echo # Since such statements are going to be written to the binary +--echo # log they need to be serialized against concurrent statements +--echo # and therefore should take strong locks on the data read. +let $statement= update t2 set j= j-10 where j in (select i from v1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= update t3 set k= k-10 where k in (select j from v2); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= update t2, v1 set j= j-10 where j = i; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= update v2 set j= j-10 where j = 3; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 4. Statements which read tables through stored functions. +--echo # + +--echo # +--echo # 4.1 SELECT/SET with a stored function which does not +--echo # modify data and uses SELECT in its turn. +--echo # +--echo # Calls to such functions won't get into the binary log and +--echo # thus don't need to acquire strong locks. +--echo # In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +--echo # used stored functions may lead to broken SBR" strong locks +--echo # are taken (we accepted it as a trade-off for this fix). +let $statement= select f1(); +let $restore_table= ; +--source include/check_concurrent_insert.inc +let $statement= set @a:= f1(); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 4.2 INSERT (or other statement which modifies data) with +--echo # a stored function which does not modify data and uses +--echo # SELECT. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data +--echo # it uses. Therefore it should take strong lock on the data +--echo # it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken. +let $statement= insert into t2 values (f1() + 5); +let $restore_table= t2; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 4.3 SELECT/SET with a stored function which +--echo # reads and modifies data. +--echo # +--echo # Since a call to such function is written to the binary log, +--echo # it should be serialized with concurrent statements affecting +--echo # the data it uses. Hence, a strong lock on the data read +--echo # should be taken. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken. +let $statement= select f2(); +let $restore_table= t2; +--source include/check_concurrent_insert.inc +let $statement= set @a:= f2(); +let $restore_table= t2; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 4.4. SELECT/SET with a stored function which does not +--echo # modify data and reads a table through subselect +--echo # in a control construct. +--echo # +--echo # Again, in theory a call to this function won't get to the +--echo # binary log and thus no strong lock is needed. But in practice +--echo # we don't detect this fact early enough (get_lock_type_for_table()) +--echo # to avoid taking a strong lock. +let $statement= select f3(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f3(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= select f4(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f4(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.5. INSERT (or other statement which modifies data) with +--echo # a stored function which does not modify data and reads +--echo # the table through a subselect in one of its control +--echo # constructs. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting data it +--echo # uses. Therefore it should take a strong lock on the data +--echo # it reads. +let $statement= insert into t2 values (f3() + 5); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= insert into t2 values (f4() + 6); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.6 SELECT/SET which uses a stored function with +--echo # DML which reads a table via a subquery. +--echo # +--echo # Since call to such function is written to the binary log +--echo # it should be serialized with concurrent statements. +--echo # Hence reads should take a strong lock. +let $statement= select f5(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f5(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.7 SELECT/SET which uses a stored function which +--echo # doesn't modify data and reads tables through +--echo # a view. +--echo # +--echo # Once again, in theory, calls to such functions won't +--echo # get into the binary log and thus don't need strong +--echo # locks. In practice this fact is discovered +--echo # too late to have any effect. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken +--echo # in case when simple SELECT is used. +let $statement= select f6(); +let $restore_table= t2; +--source include/check_concurrent_insert.inc +let $statement= set @a:= f6(); +let $restore_table= t2; +--source include/check_concurrent_insert.inc +let $statement= select f7(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f7(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.8 INSERT which uses stored function which +--echo # doesn't modify data and reads a table +--echo # through a view. +--echo # +--echo # Since such statement is written to the binary log and +--echo # should be serialized with concurrent statements affecting +--echo # the data it uses. Therefore it should take a strong lock on +--echo # the table it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken +--echo # in case when simple SELECT is used. +let $statement= insert into t3 values (f6() + 5); +let $restore_table= t3; +--source include/check_concurrent_insert.inc +let $statement= insert into t3 values (f7() + 5); +let $restore_table= t3; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 4.9 SELECT which uses a stored function which +--echo # modifies data and reads tables through a view. +--echo # +--echo # Since a call to such function is written to the binary log +--echo # it should be serialized with concurrent statements. +--echo # Hence, reads should take strong locks. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken +--echo # in case when simple SELECT is used. +let $statement= select f8(); +let $restore_table= t2; +--source include/check_concurrent_insert.inc +let $statement= select f9(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.10 SELECT which uses a stored function which doesn't modify +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Calls to such functions won't get into the binary log and +--echo # thus don't need to acquire strong locks. +--echo # In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +--echo # used stored functions may lead to broken SBR" strong locks +--echo # are taken (we accepted it as a trade-off for this fix). +let $statement= select f10(); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 4.11 INSERT which uses a stored function which doesn't modify +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Since such statement is written to the binary log, it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take strong locks on data it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken. +let $statement= insert into t2 values (f10() + 5); +let $restore_table= t2; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 4.12 SELECT which uses a stored function which modifies +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Since a call to such function is written to the binary log +--echo # it should be serialized from concurrent statements. +--echo # Hence, read should take a strong lock. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken. +let $statement= select f11(); +let $restore_table= t2; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 4.13 SELECT that reads a table through a subquery passed +--echo # as a parameter to a stored function which modifies +--echo # data. +--echo # +--echo # Even though a call to this function is written to the +--echo # binary log, values of its parameters are written as literals. +--echo # So there is no need to acquire strong locks for tables used in +--echo # the subquery. +let $statement= select f12((select i+10 from t1 where i=1)); +let $restore_table= t2; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 4.14 INSERT that reads a table via a subquery passed +--echo # as a parameter to a stored function which doesn't +--echo # modify data. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take strong locks on the data it reads. +let $statement= insert into t2 values (f13((select i+10 from t1 where i=1))); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 5. Statements that read tables through stored procedures. +--echo # + +--echo # +--echo # 5.1 CALL statement which reads a table via SELECT. +--echo # +--echo # Since neither this statement nor its components are +--echo # written to the binary log, there is no need to take +--echo # strong locks on the data it reads. +let $statement= call p2(@a); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 5.2 Function that modifies data and uses CALL, +--echo # which reads a table through SELECT. +--echo # +--echo # Since a call to such function is written to the binary +--echo # log, it should be serialized with concurrent statements. +--echo # Hence, in this case reads should take strong locks on data. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken. +let $statement= select f14(); +let $restore_table= t2; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 5.3 SELECT that calls a function that doesn't modify data and +--echo # uses a CALL statement that reads a table via SELECT. +--echo # +--echo # Calls to such functions won't get into the binary log and +--echo # thus don't need to acquire strong locks. +--echo # In 5.5 due to fix for bug #53921 "Wrong locks for SELECTs +--echo # used stored functions may lead to broken SBR" strong locks +--echo # are taken (we accepted it as a trade-off for this fix). +let $statement= select f15(); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 5.4 INSERT which calls function which doesn't modify data and +--echo # uses CALL statement which reads table through SELECT. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting data it +--echo # uses. Therefore it should take strong locks on data it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken. +let $statement= insert into t2 values (f15()+5); +let $restore_table= t2; +--source include/check_concurrent_insert.inc + + +--echo # +--echo # 6. Statements that use triggers. +--echo # + +--echo # +--echo # 6.1 Statement invoking a trigger that reads table via SELECT. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data +--echo # it uses. Therefore, it should take strong locks on the data +--echo # it reads. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken. +let $statement= insert into t4 values (2); +let $restore_table= t4; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 6.2 Statement invoking a trigger that reads table through +--echo # a subquery in a control construct. +--echo # +--echo # The above is true for this statement as well. +let $statement= update t4 set l= 2 where l = 1; +let $restore_table= t4; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 6.3 Statement invoking a trigger that reads a table through +--echo # a view. +--echo # +--echo # And for this statement. +let $statement= delete from t4 where l = 1; +let $restore_table= t4; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 6.4 Statement invoking a trigger that reads a table through +--echo # a stored function. +--echo # +--echo # And for this statement. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken. +let $statement= insert into t5 values (2); +let $restore_table= t5; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 6.5 Statement invoking a trigger that reads a table through +--echo # stored procedure. +--echo # +--echo # And for this statement. +--echo # But due to bug #53921 "Wrong locks for SELECTs used stored +--echo # functions may lead to broken SBR" weak locks are taken. +let $statement= update t5 set l= 2 where l = 1; +let $restore_table= t5; +--source include/check_concurrent_insert.inc + + +--echo # Clean-up. +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop function f8; +drop function f9; +drop function f10; +drop function f11; +drop function f12; +drop function f13; +drop function f14; +drop function f15; +drop view v1, v2; +drop procedure p1; +drop procedure p2; +drop table t1, t2, t3, t4, t5, te; + +disconnect con1; +disconnect con2; + +set @@global.concurrent_insert= @old_concurrent_insert; + + +# Check that all connections opened by test cases in this file are really +# gone so execution of other tests won't be affected by their presence. +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/log_state.test b/mysql-test/t/log_state.test index e40dd1e3491..05e17dc9fa7 100644 --- a/mysql-test/t/log_state.test +++ b/mysql-test/t/log_state.test @@ -362,6 +362,42 @@ if(!$fixed_bug38124) } +########################################################################### + +--echo # +--echo # Bug #49756 Rows_examined is always 0 in the slow query log +--echo # for update statements +--echo # + +SET @old_log_output = @@global.log_output; +SET GLOBAL log_output = "TABLE"; +SET GLOBAL slow_query_log = ON; +SET GLOBAL long_query_time = 0.001; + +# clear slow_log of any residual slow queries +TRUNCATE TABLE mysql.slow_log; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, PRIMARY KEY (b)); +INSERT INTO t2 VALUES (3),(4); + +connect (con2,localhost,root,,); +INSERT INTO t1 VALUES (1+sleep(.01)),(2); +INSERT INTO t1 SELECT b+sleep(.01) from t2; +UPDATE t1 SET a=a+sleep(.01) WHERE a>2; +UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC; +UPDATE t2 set b=b+sleep(.01) limit 1; +UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2); +DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2; + +SELECT rows_examined,sql_text FROM mysql.slow_log; +disconnect con2; +connection default; +DROP TABLE t1,t2; +TRUNCATE TABLE mysql.slow_log; + +--echo # end of bug#49756 + + --echo End of 5.1 tests --enable_ps_protocol @@ -376,6 +412,8 @@ disconnect con1; connection default; # Reset global system variables to initial values if forgotten somewhere above. +SET GLOBAL long_query_time = DEFAULT; +SET GLOBAL log_output = @old_log_output; SET global general_log = @old_general_log; SET global general_log_file = @old_general_log_file; SET global slow_query_log = @old_slow_query_log; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index b32a329f827..87e952dd069 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -589,7 +589,8 @@ reset master; UPDATE t2,t1 SET t2.a=t1.a+2; # check select * from t2 /* must be (3,1), (4,4) */; -show master status /* there must be the UPDATE query event */; +let wait_binlog_event= UPDATE; +source include/wait_for_binlog_event.inc; # B. testing multi_update::send_error() ineffective update # (as there is a policy described at mysql_update() still go to binlog) @@ -600,7 +601,8 @@ insert into t2 values (1,2),(3,4),(4,4); reset master; --error ER_DUP_ENTRY UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; -show master status /* there must be the UPDATE query event */; +let wait_binlog_event= UPDATE; +source include/wait_for_binlog_event.inc; # cleanup drop table t1, t2; diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 02923bd6e6f..f1a00368e12 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -2134,6 +2134,35 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; SET NAMES default; DROP TABLE t1, t2; +########################################################################### + +--echo # +--echo # Bug #53088: mysqldump with -T & --default-character-set set +--echo # truncates text/blob to 766 chars +--echo # +--echo # Also see outfile_loaddata.test +--echo # + +CREATE TABLE t1 (a BLOB) CHARSET latin1; +CREATE TABLE t2 LIKE t1; + +let $table= t1; +let $dir= $MYSQLTEST_VARDIR/tmp; +let $file= $dir/$table.txt; +let $length= 800; + +--eval INSERT INTO t1 VALUES (REPEAT('.', $length)) + +--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --default-character-set=latin1 --tab=$dir/ test $table +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR + +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET latin1 +--remove_file $file + +--echo # should be $length +SELECT LENGTH(a) FROM t2; + +DROP TABLE t1, t2; ########################################################################### --echo # diff --git a/mysql-test/t/mysqlhotcopy_archive.test b/mysql-test/t/mysqlhotcopy_archive.test new file mode 100644 index 00000000000..4bfad3ce138 --- /dev/null +++ b/mysql-test/t/mysqlhotcopy_archive.test @@ -0,0 +1,8 @@ +# Test of mysqlhotcopy (perl script) +# Author: Horst Hunger +# Created: 2010-05-10 + +--source include/have_archive.inc +let $engine= archive; +--source include/mysqlhotcopy.inc +--exit diff --git a/mysql-test/t/mysqlhotcopy_myisam.test b/mysql-test/t/mysqlhotcopy_myisam.test new file mode 100644 index 00000000000..adf26e42245 --- /dev/null +++ b/mysql-test/t/mysqlhotcopy_myisam.test @@ -0,0 +1,7 @@ +# Test of mysqlhotcopy (perl script) +# Author: Horst Hunger +# Created: 2010-05-10 + +let $engine= MyISAM; +--source include/mysqlhotcopy.inc +--exit diff --git a/mysql-test/t/outfile_loaddata.test b/mysql-test/t/outfile_loaddata.test index 3f62acbd214..26760f9a1b2 100644 --- a/mysql-test/t/outfile_loaddata.test +++ b/mysql-test/t/outfile_loaddata.test @@ -251,6 +251,40 @@ SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; SET NAMES default; DROP TABLE t1, t2; +########################################################################### + +--echo # +--echo # Bug #53088: mysqldump with -T & --default-character-set set +--echo # truncates text/blob to 766 chars +--echo # +--echo # Also see mysqldump.test +--echo # + +CREATE TABLE t1 (a BLOB) CHARSET latin1; +CREATE TABLE t2 LIKE t1; + +let $file= '$MYSQLTEST_VARDIR/tmp/bug53088.txt'; +let $length= 800; + +--eval INSERT INTO t1 VALUES (REPEAT('.', $length)) + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE $file CHARACTER SET latin1 FROM t1 + +--echo # should be greater than $length +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LENGTH(LOAD_FILE($file)) + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE $file INTO TABLE t2 + +--remove_file $MYSQLTEST_VARDIR/tmp/bug53088.txt + +--echo # should be $length +SELECT LENGTH(a) FROM t2; + +DROP TABLE t1, t2; + ########################################################################### --echo # End of 5.1 tests. diff --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test index 8da8f54b774..434392c2e28 100644 --- a/mysql-test/t/partition_error.test +++ b/mysql-test/t/partition_error.test @@ -7,7 +7,36 @@ --disable_warnings drop table if exists t1; --enable_warnings - + +let $MYSQLD_DATADIR= `SELECT @@datadir`; + +--echo # +--echo # Bug#49161: Out of memory; restart server and try again (needed 2 bytes) +--echo # +CREATE TABLE t1 (a INT) PARTITION BY HASH (a); +FLUSH TABLES; +--remove_file $MYSQLD_DATADIR/test/t1.par +--replace_result $MYSQLD_DATADIR ./ +CHECK TABLE t1; +--error ER_UNKNOWN_ERROR +SELECT * FROM t1; +--echo # Note that it is currently impossible to drop a partitioned table +--echo # without the .par file +--error ER_BAD_TABLE_ERROR +DROP TABLE t1; +--remove_file $MYSQLD_DATADIR/test/t1.frm +--remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYI +--remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYD + +--echo # +--echo # Bug#49477: Assertion `0' failed in ha_partition.cc:5530 +--echo # with temporary table and partitions +--echo # +CREATE TABLE t1 (a INT) PARTITION BY HASH(a); +--error ER_PARTITION_NO_TEMPORARY +CREATE TEMPORARY TABLE tmp_t1 LIKE t1; +DROP TABLE t1; + --echo # --echo # Bug#50392: insert_id is not reset for partitioned tables --echo # auto_increment on duplicate entry @@ -158,7 +187,6 @@ partitions 3 partition x2 tablespace ts2, partition x3 tablespace ts3); -let $MYSQLD_DATADIR= `select @@datadir`; select load_file('$MYSQLD_DATADIR/test/t1.par'); # # Partition by hash, invalid field in function diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 5d5ad180f1a..2d2a6f75d73 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1313,4 +1313,45 @@ SELECT * FROM t1 FORCE INDEX (PRIMARY) DROP TABLE t1; +--echo # +--echo # Bug#50939: Loose Index Scan unduly relies on engine to remember range +--echo # endpoints +--echo # +CREATE TABLE t1 ( + a INT, + b INT, + KEY ( a, b ) +) PARTITION BY HASH (a) PARTITIONS 1; + +CREATE TABLE t2 ( + a INT, + b INT, + KEY ( a, b ) +); + +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); + +INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; +INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; +INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; +INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; + +INSERT INTO t2 SELECT * FROM t1; + +--echo # plans should be identical +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; +EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; + +FLUSH status; +SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; +--echo # Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; + +FLUSH status; +SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; +--echo # Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; + +DROP TABLE t1, t2; + --echo End of 5.1 tests diff --git a/mysql-test/t/renamedb.test b/mysql-test/t/renamedb.test index 84315090b7a..71d0c127058 100644 --- a/mysql-test/t/renamedb.test +++ b/mysql-test/t/renamedb.test @@ -44,7 +44,7 @@ ALTER DATABASE `#mysql41#not-supported` UPGRADE DATA DIRECTORY NAME; --error ER_WRONG_USAGE ALTER DATABASE `#mysql51#not-yet` UPGRADE DATA DIRECTORY NAME; ---error ER_WRONG_USAGE +--error ER_WRONG_DB_NAME ALTER DATABASE `#mysql50#` UPGRADE DATA DIRECTORY NAME; --error ER_BAD_DB_ERROR diff --git a/mysql-test/t/schema.test b/mysql-test/t/schema.test index a08d9b38935..a63402bbb83 100644 --- a/mysql-test/t/schema.test +++ b/mysql-test/t/schema.test @@ -4,6 +4,9 @@ # Drop mysqltest1 database, as it can left from the previous tests. # +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + --disable_warnings drop database if exists mysqltest1; --enable_warnings @@ -12,3 +15,47 @@ create schema foo; show create schema foo; show schemas; drop schema foo; + + +--echo # +--echo # Bug#54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER +--echo # + +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +INSERT INTO db1.t1 VALUES (1), (2); + +--echo # Connection con1 +connect (con1, localhost, root); +HANDLER db1.t1 OPEN; + +--echo # Connection default +connection default; +--echo # Sending: +--send DROP DATABASE db1 + +--echo # Connection con2 +connect (con2, localhost, root); +--echo # Waiting for 'DROP DATABASE db1' to sync in. +let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist + WHERE state='Waiting for table' AND info='DROP DATABASE db1'; +--source include/wait_condition.inc + +--echo # Connection con1 +connection con1; +# All these statements before resulted in deadlock. +CREATE DATABASE db2; +ALTER DATABASE db2 DEFAULT CHARACTER SET utf8; +DROP DATABASE db2; + +--echo # Connection default +connection default; +--echo # Reaping: DROP DATABASE db1 +--reap +disconnect con1; +disconnect con2; + + +# Check that all connections opened by test cases in this file are really +# gone so execution of other tests won't be affected by their presence. +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index f61db538fb4..db08aad0df0 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4077,4 +4077,15 @@ SELECT * FROM t1 WHERE 102 < c; DROP TABLE t1; +--echo # +--echo # Bug #54459: Assertion failed: param.sort_length, +--echo # file .\filesort.cc, line 149 (part II) +--echo # +CREATE TABLE t1(a ENUM('') NOT NULL); +INSERT INTO t1 VALUES (), (), (); +EXPLAIN SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; +SELECT 1 FROM t1 ORDER BY a COLLATE latin1_german2_ci; +DROP TABLE t1; + + --echo End of 5.1 tests diff --git a/mysql-test/t/sp_trans_log.test b/mysql-test/t/sp_trans_log.test index 2f2b84a9bef..5eccbf81fef 100644 --- a/mysql-test/t/sp_trans_log.test +++ b/mysql-test/t/sp_trans_log.test @@ -34,8 +34,8 @@ end| reset master| --error ER_DUP_ENTRY insert into t2 values (bug23333(),1)| ---replace_column 2 # 5 # 6 # -show binlog events from 106 /* with fixes for #23333 will show there is the query */| +# with fixes for 23333 will show there is the query */| +--source include/show_binlog_events.inc select count(*),@a from t1 /* must be 1,1 */| delimiter ;| diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index ffbd56c198d..89be351312d 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3711,3 +3711,28 @@ GROUP BY DROP TABLE t3; DROP TABLE t2; DROP TABLE t1; + + +--echo # +--echo # Bug #52711: Segfault when doing EXPLAIN SELECT with +--echo # union...order by (select... where...) +--echo # + +CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1),(2); + +--echo # Should not crash +--disable_result_log +EXPLAIN +SELECT * FROM t2 UNION SELECT * FROM t2 + ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); + +--echo # Should not crash +SELECT * FROM t2 UNION SELECT * FROM t2 + ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); +DROP TABLE t1,t2; +--enable_result_log + +--echo End of 5.1 tests diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index 5bb521601e5..34331b72688 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -88,5 +88,15 @@ INSERT INTO t1 VALUES ('0:00:00'); SELECT CAST(c AS TIME) FROM t1; DROP TABLE t1; - --echo End of 5.0 tests + +--echo # +--echo # Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values +--echo # + +CREATE TABLE t1(f1 TIME); +INSERT INTO t1 VALUES ('23:38:57'); +SELECT TIMESTAMP(f1,'1') FROM t1; +DROP TABLE t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/upgrade.test b/mysql-test/t/upgrade.test index e390e8a1253..a7b9a1531ff 100644 --- a/mysql-test/t/upgrade.test +++ b/mysql-test/t/upgrade.test @@ -137,3 +137,37 @@ select * from `a-b-c`.v1; --enable_ps_protocol drop database `a-b-c`; use test; + +--echo # End of 5.0 tests + +--echo # +--echo # Bug #53804: serious flaws in the alter database .. upgrade data +--echo # directory name command +--echo # + +--error ER_BAD_DB_ERROR +ALTER DATABASE `#mysql50#:` UPGRADE DATA DIRECTORY NAME; +--error ER_WRONG_DB_NAME +ALTER DATABASE `#mysql50#.` UPGRADE DATA DIRECTORY NAME; +--error ER_WRONG_DB_NAME +ALTER DATABASE `#mysql50#../` UPGRADE DATA DIRECTORY NAME; +--error ER_WRONG_DB_NAME +ALTER DATABASE `#mysql50#../..` UPGRADE DATA DIRECTORY NAME; +--error ER_WRONG_DB_NAME +ALTER DATABASE `#mysql50#../../` UPGRADE DATA DIRECTORY NAME; +--error ER_WRONG_DB_NAME +ALTER DATABASE `#mysql50#./blablabla` UPGRADE DATA DIRECTORY NAME; +--error ER_WRONG_DB_NAME +ALTER DATABASE `#mysql50#../blablabla` UPGRADE DATA DIRECTORY NAME; +--error ER_WRONG_DB_NAME +ALTER DATABASE `#mysql50#/` UPGRADE DATA DIRECTORY NAME; +--error ER_WRONG_DB_NAME +ALTER DATABASE `#mysql50#/.` UPGRADE DATA DIRECTORY NAME; + +--error ER_WRONG_DB_NAME +USE `#mysql50#.`; +--error ER_WRONG_DB_NAME +USE `#mysql50#../blablabla`; + +--echo # End of 5.1 tests + diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 94dba1e8ff6..3e9dc5e39d3 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -362,7 +362,7 @@ set global table_open_cache=100; set storage_engine=myisam; set global thread_cache_size=100; set timestamp=1, timestamp=default; -set tmp_table_size=100; +set tmp_table_size=1024; set tx_isolation="READ-COMMITTED"; set wait_timeout=100; set log_warnings=1; @@ -778,6 +778,21 @@ show variables like 'hostname'; --echo # Test 'myisam_mmap_size' option is not dynamic --error ER_INCORRECT_GLOBAL_LOCAL_VAR SET @@myisam_mmap_size= 500M; + + +--echo # +--echo # Bug #52315: utc_date() crashes when system time > year 2037 +--echo # + +--error 0, ER_UNKNOWN_ERROR +SET TIMESTAMP=2*1024*1024*1024; +--echo #Should not crash +--disable_result_log +SELECT UTC_DATE(); +--enable_result_log +SET TIMESTAMP=DEFAULT; + + --echo End of 5.0 tests # diff --git a/mysql-test/t/variables_debug.test b/mysql-test/t/variables_debug.test index 8f2bde7ae42..12f5d2e6ca5 100644 --- a/mysql-test/t/variables_debug.test +++ b/mysql-test/t/variables_debug.test @@ -1,5 +1,7 @@ --source include/have_debug.inc +SET @old_debug = @@GLOBAL.debug; + # # Bug#34678 @@debug variable's incremental mode # @@ -21,5 +23,6 @@ SELECT @@global.debug; SET GLOBAL debug=''; SELECT @@global.debug; +SET GLOBAL debug=@old_debug; --echo End of 5.1 tests |