diff options
Diffstat (limited to 'mysql-test/t')
115 files changed, 3829 insertions, 324 deletions
diff --git a/mysql-test/t/archive_bitfield.test b/mysql-test/t/archive_bitfield.test index 1e4692270b5..2e90ce39708 100644 --- a/mysql-test/t/archive_bitfield.test +++ b/mysql-test/t/archive_bitfield.test @@ -94,5 +94,11 @@ INSERT INTO `t1` VALUES (NULL,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'100000',b'010010',b'011111',4,5,5,5,5,5,5,5,5,5,3,2,1), (NULL,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'000000',b'001100',b'111111',4,5,5,5,5,5,5,5,5,5,3,2,1), (NULL,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'111111',b'000000',b'000000',4,5,5,5,5,5,5,5,5,5,3,2,1); +# Determine the number of open sessions +--source include/count_sessions.inc --exec $MYSQL_DUMP --hex-blob --compact --order-by-primary --skip-extended-insert --no-create-info test t1 drop table t1; +# Wait till the number of open sessions is <= the number before the run with $MYSQL_DUMP +# = The session caused by mysqldump has finished its disconnect +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/bug40113-master.opt b/mysql-test/t/bug40113-master.opt new file mode 100644 index 00000000000..462f8fbe828 --- /dev/null +++ b/mysql-test/t/bug40113-master.opt @@ -0,0 +1 @@ +--innodb_lock_wait_timeout=1 diff --git a/mysql-test/t/bug40113.test b/mysql-test/t/bug40113.test new file mode 100644 index 00000000000..6d35d0b73d3 --- /dev/null +++ b/mysql-test/t/bug40113.test @@ -0,0 +1,46 @@ +--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 diff --git a/mysql-test/t/bug46080-master.opt b/mysql-test/t/bug46080-master.opt new file mode 100644 index 00000000000..f59740afe60 --- /dev/null +++ b/mysql-test/t/bug46080-master.opt @@ -0,0 +1 @@ +--skip-grant-tables --skip-name-resolve --safemalloc-mem-limit=4000000 diff --git a/mysql-test/t/bug46080.test b/mysql-test/t/bug46080.test new file mode 100644 index 00000000000..7e56e3ce421 --- /dev/null +++ b/mysql-test/t/bug46080.test @@ -0,0 +1,22 @@ +--echo # +--echo # Bug #46080: group_concat(... order by) crashes server when +--echo # sort_buffer_size cannot allocate +--echo # + +CREATE TABLE t1(a CHAR(255)); +INSERT INTO t1 VALUES ('a'); + +SET @@SESSION.sort_buffer_size=5*16*1000000; +SET @@SESSION.max_heap_table_size=5*1000000; + +--echo # Must not crash. +--disable_result_log +--error 0,5 +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a; +--enable_result_log + +DROP TABLE t1; +SET @@SESSION.sort_buffer_size=default; +SET @@SESSION.max_heap_table_size=default; + +--echo End of 5.0 tests diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 50865215944..8e60d548c2f 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -269,3 +269,18 @@ SELECT HOUR(NULL), DROP TABLE t1; --echo End of 5.0 tests + +--echo # +--echo # Bug #44766: valgrind error when using convert() in a subquery +--echo # + +CREATE TABLE t1(a tinyint); +INSERT INTO t1 VALUES (127); +SELECT 1 FROM +( + SELECT CONVERT(t2.a USING UTF8) FROM t1, t1 t2 LIMIT 1 +) AS s LIMIT 1; +DROP TABLE t1; + + +--echo End of 5.1 tests diff --git a/mysql-test/t/client_xml.test b/mysql-test/t/client_xml.test index 739b56f5ab1..0847e2b366b 100644 --- a/mysql-test/t/client_xml.test +++ b/mysql-test/t/client_xml.test @@ -18,6 +18,10 @@ create table t1 ( `a>b` text ); insert into t1 values (1, 2, 'a&b a<b a>b'); + +# Determine the number of open sessions +--source include/count_sessions.inc + --exec $MYSQL --xml test -e "select * from t1" --exec $MYSQL_DUMP --xml --skip-create test @@ -33,3 +37,8 @@ drop table t1; # Restore global concurrent_insert value set @@global.concurrent_insert= @old_concurrent_insert; + +# Wait till the number of open sessions is <= the number before the runs with +# $MYSQL and $MYSQL_DUMP +# = The session caused by mysql and mysqldump have finished their disconnect +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/connect.test b/mysql-test/t/connect.test index 3e78da29b08..255a4634bca 100644 --- a/mysql-test/t/connect.test +++ b/mysql-test/t/connect.test @@ -55,7 +55,8 @@ disconnect con4; connect (fail_con,localhost,test,,test2); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT --error ER_ACCESS_DENIED_ERROR -connect (fail_con,localhost,test,,""); +# Need to protect "" within '' so it's interpreted literally +connect (fail_con,localhost,test,,'""'); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT --error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,test,zorro,test2); diff --git a/mysql-test/t/consistent_snapshot.test b/mysql-test/t/consistent_snapshot.test index 82edf2e22b2..fb1f3bc007c 100644 --- a/mysql-test/t/consistent_snapshot.test +++ b/mysql-test/t/consistent_snapshot.test @@ -12,9 +12,9 @@ connect (con1,localhost,root,,); --echo # Establish connection con2 (user=root) connect (con2,localhost,root,,); -### Test 1: -### - While a consistent snapshot transaction is executed, -### no external inserts should be visible to the transaction. +--echo ### Test 1: +--echo ### - While a consistent snapshot transaction is executed, +--echo ### no external inserts should be visible to the transaction. --echo # Switch to connection con1 connection con1; @@ -31,9 +31,9 @@ SELECT * FROM t1; # if consistent snapshot was set as expected, we # should see nothing. COMMIT; -### Test 2: -### - For any non-consistent snapshot transaction, external -### committed inserts should be visible to the transaction. +--echo ### Test 2: +--echo ### - For any non-consistent snapshot transaction, external +--echo ### committed inserts should be visible to the transaction. DELETE FROM t1; START TRANSACTION; # Now we omit WITH CONSISTENT SNAPSHOT @@ -48,6 +48,24 @@ SELECT * FROM t1; # if consistent snapshot was not set, as expected, we # should see 1. COMMIT; +--echo ### Test 3: +--echo ### - Bug#44664: valgrind warning for COMMIT_AND_CHAIN and ROLLBACK_AND_CHAIN +--echo ### Chaining a transaction does not retain consistency level. + +START TRANSACTION WITH CONSISTENT SNAPSHOT; +DELETE FROM t1; +COMMIT WORK AND CHAIN; + +--echo # Switch to connection con2 +connection con2; +INSERT INTO t1 VALUES(1); + +--echo # Switch to connection con1 +connection con1; +SELECT * FROM t1; # if consistent snapshot was not set, as expected, we +# should see 1. +COMMIT; + --echo # Switch to connection default + close connections con1 and con2 connection default; disconnect con1; diff --git a/mysql-test/t/count_distinct3.test b/mysql-test/t/count_distinct3.test index 86f91991664..9de45baf320 100644 --- a/mysql-test/t/count_distinct3.test +++ b/mysql-test/t/count_distinct3.test @@ -1,10 +1,10 @@ -# +# Bug #958 a big table without indices and select with group by doesnt work # this is a test for error 1032 in count(distinct) + group by, introduced in # mysql-4.1 # # Slow test --- source include/big_test.inc +--source include/big_test.inc --disable_warnings DROP TABLE IF EXISTS t1, t2; --enable_warnings @@ -23,27 +23,16 @@ while ($1) INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); dec $1; } -set @@read_buffer_size=2*1024*1024; -CREATE TABLE t2 SELECT * FROM t1; -INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; -INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; -INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; -INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; -INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; -INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; -INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; -INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; -INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; -INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; -INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; -INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; -INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; -INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; -INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; -INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; -INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2; -INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1; -DROP TABLE t2; + +# We increase the size of t1 here. +SET @orig_myisam_sort_buffer_size = @@session.myisam_sort_buffer_size; +SET session myisam_sort_buffer_size=20000000; +INSERT INTO t1 +SELECT A.id, A.grp, A.id_rev +FROM + t1 A, + (SELECT * FROM t1 B LIMIT 100) B, + (SELECT * FROM t1 Z LIMIT 42) Z; --enable_query_log SELECT COUNT(*) FROM t1; @@ -51,12 +40,12 @@ SELECT COUNT(*) FROM t1; # As t1 contains random numbers, results are different from test to test. # That's okay, because we test only that select doesn't yield an # error. Note, that --disable_result_log doesn't suppress error output. - --disable_result_log SELECT COUNT(DISTINCT id) FROM t1 GROUP BY grp; --enable_result_log -DROP TABLE t1; -set @@read_buffer_size=default; +--echo # Begin cleanup +SET session myisam_sort_buffer_size = @orig_myisam_sort_buffer_size; +DROP TABLE t1; # End of 4.1 tests diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 98365ea0402..63968caed94 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -1541,5 +1541,28 @@ DROP TABLE t1; create table `me:i`(id int); drop table `me:i`; +########################################################################### + +# +# Bug#45829 CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing +# + +--echo +--echo # -- +--echo # -- Bug#45829: CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing +--echo # -- +--echo + +--disable_warnings +drop table if exists t1,t2,t3; +--enable_warnings +--echo # Fix modified for MariaDB: we support this syntax +create table t1 (a int) transactional=0; +create table t2 (a int) page_checksum=1; +create table t3 (a int) row_format=page; +drop table t1,t2,t3; +--echo +--echo # -- End of Bug#45829 + --echo --echo End of 5.1 tests diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index abc161d014c..cdf274190dd 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1800,10 +1800,11 @@ connect (con1,localhost,root,,); # EE_FILENOTFOUND 29 --error 29 select * from t1; +--disconnect con1 +--source include/wait_until_disconnected.inc connection default; unlock tables; drop table t1; ---disconnect con1 # # Bug#41441 repair csv table crashes debug server diff --git a/mysql-test/t/ctype_cp932_binlog_stm.test b/mysql-test/t/ctype_cp932_binlog_stm.test index 383009ae7c3..89df33a6df5 100644 --- a/mysql-test/t/ctype_cp932_binlog_stm.test +++ b/mysql-test/t/ctype_cp932_binlog_stm.test @@ -22,7 +22,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 369| +SHOW BINLOG EVENTS FROM 370| delimiter ;| --echo End of 5.0 tests @@ -31,7 +31,14 @@ delimiter ;| # #28436: Incorrect position in SHOW BINLOG EVENTS causes server coredump # Note: 364 is a magic position (found experimentally, depends on # the log's contents) that caused the server crash. + --error 1220 -SHOW BINLOG EVENTS FROM 364; +SHOW BINLOG EVENTS FROM 365; + +--echo Bug#44352 UPPER/LOWER function doesn't work correctly on cp932 and sjis environment. +CREATE TABLE t1 (a varchar(16)) character set cp932; +INSERT INTO t1 VALUES (0x8372835E),(0x8352835E); +SELECT hex(a), hex(lower(a)), hex(upper(a)) FROM t1 ORDER BY binary(a); +DROP TABLE t1; --echo End of 5.1 tests diff --git a/mysql-test/t/ctype_euckr.test b/mysql-test/t/ctype_euckr.test index 0aba830e725..fcb94e7b6d1 100644 --- a/mysql-test/t/ctype_euckr.test +++ b/mysql-test/t/ctype_euckr.test @@ -56,3 +56,54 @@ select hex(s1), hex(convert(s1 using utf8)) from t1 order by binary s1; drop table t1; --echo End of 5.0 tests + + +--echo Start of 5.4 tests + +--echo # +--echo # WL#3997 New euckr characters +--echo # +SET NAMES utf8; +CREATE TABLE t1 (a varchar(10) character set euckr); +INSERT INTO t1 VALUES (0xA2E6), (0xA2E7); +SELECT hex(a), hex(@utf8:=convert(a using utf8)), hex(convert(@utf8 using euckr)) FROM t1; +DROP TABLE t1; + +--echo # +--echo # WL#3332 Korean Enhancements +--echo # euckr valid codes are now [81..FE][41..5A,61..7A,81..FE] +--echo # + +CREATE TABLE t1 (a binary(1), key(a)); +--disable_query_log +let $1=255; +while($1) +{ + eval INSERT INTO t1 VALUES (unhex(hex($1))); + dec $1; +} +--enable_query_log + +CREATE TABLE t2 (s VARCHAR(4), a VARCHAR(1) CHARACTER SET euckr); +--disable_warnings +INSERT INTO t2 +SELECT hex(concat(t11.a, t12.a)), concat(t11.a, t12.a) +FROM t1 t11, t1 t12 +WHERE t11.a >= 0x81 AND t11.a <= 0xFE +AND t12.a >= 0x41 AND t12.a <= 0xFE +ORDER BY t11.a, t12.a; +--enable_warnings +SELECT s as bad_code FROM t2 WHERE a='' ORDER BY s; +DELETE FROM t2 WHERE a=''; +ALTER TABLE t2 ADD u VARCHAR(1) CHARACTER SET utf8, ADD a2 VARCHAR(1) CHARACTER SET euckr; +--disable_warnings +UPDATE t2 SET u=a, a2=u; +--enable_warnings +SELECT s as unassigned_code FROM t2 WHERE u='?'; +DELETE FROM t2 WHERE u='?'; +# Make sure there are no euckr->utf8->euckr roundtrip problems +SELECT count(*) as roundtrip_problem_chars FROM t2 WHERE hex(a) <> hex(a2); +SELECT s, hex(a), hex(u), hex(a2) FROM t2 ORDER BY s; +DROP TABLE t1, t2; + +--echo End of 5.4 tests diff --git a/mysql-test/t/ctype_gbk_binlog.test b/mysql-test/t/ctype_gbk_binlog.test new file mode 100644 index 00000000000..a8f653d1b1e --- /dev/null +++ b/mysql-test/t/ctype_gbk_binlog.test @@ -0,0 +1,36 @@ +-- source include/have_binlog_format_mixed_or_statement.inc +-- source include/have_gbk.inc + +SET NAMES gbk; +--character_set gbk + +CREATE TABLE t1 ( + f1 BLOB +) ENGINE=MyISAM DEFAULT CHARSET=gbk; + +delimiter |; +CREATE PROCEDURE p1(IN val BLOB) +BEGIN + SET @tval = val; + SET @sql_cmd = CONCAT_WS(' ', 'insert into t1(f1) values(?)'); + PREPARE stmt FROM @sql_cmd; + EXECUTE stmt USING @tval; + DEALLOCATE PREPARE stmt; +END| +delimiter ;| + +SET @`tcontent`:=_binary 0x50434B000900000000000000E9000000 COLLATE `binary`/*!*/; +CALL p1(@`tcontent`); + +FLUSH LOGS; +DROP PROCEDURE p1; +RENAME TABLE t1 to t2; + +let $MYSQLD_DATADIR= `select @@datadir`; +--exec $MYSQL_BINLOG --force-if-open --short-form $MYSQLD_DATADIR/master-bin.000001 | $MYSQL +SELECT hex(f1) FROM t2; +SELECT hex(f1) FROM t1; + +DROP PROCEDURE p1; +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/t/ctype_ldml.test b/mysql-test/t/ctype_ldml.test index 73a23a751e8..db9461bfbf7 100644 --- a/mysql-test/t/ctype_ldml.test +++ b/mysql-test/t/ctype_ldml.test @@ -37,6 +37,15 @@ UPDATE t1 SET col2=col1; SELECT * FROM t1 WHERE col1=col2 ORDER BY col1; DROP TABLE t1; +--echo # +--echo # Bug#43827 Server closes connections and restarts +--echo # +# Crash happened with a user-defined utf8 collation, +# on attempt to insert a string longer than the column can store. +CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci); +INSERT INTO t1 SELECT REPEAT('a',11); +DROP TABLE t1; + # # Vietnamese experimental collation # diff --git a/mysql-test/t/ctype_sjis.test b/mysql-test/t/ctype_sjis.test index 27cbdff451b..7de94e34dea 100644 --- a/mysql-test/t/ctype_sjis.test +++ b/mysql-test/t/ctype_sjis.test @@ -83,3 +83,13 @@ SET NAMES sjis; SELECT HEX('²“‘@Œ\') FROM DUAL; # End of 4.1 tests + +--echo # Start of 5.1 tests + +--echo Bug#44352 UPPER/LOWER function doesn't work correctly on cp932 and sjis environment. +CREATE TABLE t1 (a varchar(16)) character set sjis; +INSERT INTO t1 VALUES (0x8372835E),(0x8352835E); +SELECT hex(a), hex(lower(a)), hex(upper(a)) FROM t1 ORDER BY binary(a); +DROP TABLE t1; + +--echo # End of 5.1 tests diff --git a/mysql-test/t/ddl_i18n_koi8r.test b/mysql-test/t/ddl_i18n_koi8r.test index 2d94a899aad..fecef2f95d5 100644 --- a/mysql-test/t/ddl_i18n_koi8r.test +++ b/mysql-test/t/ddl_i18n_koi8r.test @@ -1128,15 +1128,22 @@ SHOW CREATE TABLE mysqltest2.t2| # # Cleanup. # +delimiter ;| ---connection default +--connection con2 --echo ---echo ---> connection: default - +--echo ---> connection: con2 --disconnect con2 +--source include/wait_until_disconnected.inc +--connection con3 +--echo +--echo ---> connection: con3 --disconnect con3 +--source include/wait_until_disconnected.inc +--connection default +--echo +--echo ---> connection: default +USE test; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; -use test| - -DROP DATABASE mysqltest1| -DROP DATABASE mysqltest2| diff --git a/mysql-test/t/ddl_i18n_utf8.test b/mysql-test/t/ddl_i18n_utf8.test index 1d5415d9373..8788d0604f2 100644 --- a/mysql-test/t/ddl_i18n_utf8.test +++ b/mysql-test/t/ddl_i18n_utf8.test @@ -1128,15 +1128,22 @@ SHOW CREATE TABLE mysqltest2.t2| # # Cleanup. # +delimiter ;| ---connection default +--connection con2 --echo ---echo ---> connection: default - +--echo ---> connection: con2 --disconnect con2 +--source include/wait_until_disconnected.inc +--connection con3 +--echo +--echo ---> connection: con3 --disconnect con3 +--source include/wait_until_disconnected.inc +--connection default +--echo +--echo ---> connection: default +USE test; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; -use test| - -DROP DATABASE mysqltest1| -DROP DATABASE mysqltest2| diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 4e79fac584f..d28c19bbd18 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -273,8 +273,32 @@ select t2.* from ((select * from t1) as A inner join t2 on A.ID = t2.FID); select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID; drop table t1, t2; +connection con1; disconnect con1; +--source include/wait_until_disconnected.inc connection default; drop user mysqltest_1; -# End of 4.1 tests +--echo # End of 4.1 tests + +# +# Bug #41156: List of derived tables acts like a chain of +# mutually-nested subqueries +# + +SELECT 0 FROM +(SELECT 0) t01, (SELECT 0) t02, (SELECT 0) t03, (SELECT 0) t04, (SELECT 0) t05, +(SELECT 0) t06, (SELECT 0) t07, (SELECT 0) t08, (SELECT 0) t09, (SELECT 0) t10, +(SELECT 0) t11, (SELECT 0) t12, (SELECT 0) t13, (SELECT 0) t14, (SELECT 0) t15, +(SELECT 0) t16, (SELECT 0) t17, (SELECT 0) t18, (SELECT 0) t19, (SELECT 0) t20, +(SELECT 0) t21, (SELECT 0) t22, (SELECT 0) t23, (SELECT 0) t24, (SELECT 0) t25, +(SELECT 0) t26, (SELECT 0) t27, (SELECT 0) t28, (SELECT 0) t29, (SELECT 0) t30, +(SELECT 0) t31, (SELECT 0) t32, (SELECT 0) t33, (SELECT 0) t34, (SELECT 0) t35, +(SELECT 0) t36, (SELECT 0) t37, (SELECT 0) t38, (SELECT 0) t39, (SELECT 0) t40, +(SELECT 0) t41, (SELECT 0) t42, (SELECT 0) t43, (SELECT 0) t44, (SELECT 0) t45, +(SELECT 0) t46, (SELECT 0) t47, (SELECT 0) t48, (SELECT 0) t49, (SELECT 0) t50, +(SELECT 0) t51, (SELECT 0) t52, (SELECT 0) t53, (SELECT 0) t54, (SELECT 0) t55, +(SELECT 0) t56, (SELECT 0) t57, (SELECT 0) t58, (SELECT 0) t59, (SELECT 0) t60, +(SELECT 0) t61; # 61 == MAX_TABLES + +--echo # End of 5.0 tests diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index eab0542314a..5436b7166f4 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -10,6 +10,7 @@ # ############################################################################## kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. -innodb_bug39438 : BUG#42383 2009-01-28 lsoares "This fails in embedded and on windows. Note that this test is not run on windows and on embedded in PB for main trees currently" -#concurrent_innodb_safelog: disabled for embedded server due to bug#43733 Select on processlist let the embedded server crash (concurrent_innodb_safelog). -#concurrent_innodb_unsafelog: disabled for embedded server due to bug#43733. +innodb_bug39438 : Bug#42383 2009-01-28 lsoares "This fails in embedded and on windows. Note that this test is not run on windows and on embedded in PB for main trees currently" +query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically +init_connect : Bug#44920 2009-07-06 pcrews MTR not processing master.opt input properly on Windows. *Must be done this way due to the nature of the bug* + diff --git a/mysql-test/t/drop.test b/mysql-test/t/drop.test index 91345886e93..bb4dd3e11f9 100644 --- a/mysql-test/t/drop.test +++ b/mysql-test/t/drop.test @@ -117,8 +117,11 @@ connection addconroot1; --reap connection addconroot2; --reap -disconnect addconroot1; disconnect addconroot2; +--source include/wait_until_disconnected.inc +connection addconroot1; +disconnect addconroot1; +--source include/wait_until_disconnected.inc connection default; --echo End of 5.0 tests diff --git a/mysql-test/t/events_grant.test b/mysql-test/t/events_grant.test index cff2475c5aa..8db4333cc03 100644 --- a/mysql-test/t/events_grant.test +++ b/mysql-test/t/events_grant.test @@ -97,7 +97,9 @@ DROP EVENT one_event; connection default; --echo "One event should not be there" SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; +connection ev_con1; disconnect ev_con1; +--source include/wait_until_disconnected.inc connection default; DROP USER ev_test@localhost; DROP DATABASE events_test2; @@ -106,9 +108,6 @@ DROP DATABASE events_test2; # End of tests # -let $wait_condition= - select count(*) = 0 from information_schema.processlist - where db='events_test' and command = 'Connect' and user=current_user(); ---source include/wait_condition.inc +--source include/check_events_off.inc DROP DATABASE events_test; diff --git a/mysql-test/t/events_stress.test b/mysql-test/t/events_stress.test index 4776bcfa6e3..8ea7633877b 100644 --- a/mysql-test/t/events_stress.test +++ b/mysql-test/t/events_stress.test @@ -110,7 +110,7 @@ connection conn3; --send DROP DATABASE events_conn3_db; connection default; ---send +# --send DROP DATABASE events_conn1_test2; DROP DATABASE events_conn1_test3; SET GLOBAL event_scheduler=off; @@ -139,3 +139,4 @@ DROP DATABASE events_test; # Cleanup SET GLOBAL event_scheduler=@old_event_scheduler; +--source include/check_events_off.inc diff --git a/mysql-test/t/events_trans_notembedded.test b/mysql-test/t/events_trans_notembedded.test index 3c151dd18b1..0353d183386 100644 --- a/mysql-test/t/events_trans_notembedded.test +++ b/mysql-test/t/events_trans_notembedded.test @@ -50,6 +50,7 @@ delete from t1; commit work; # Cleanup disconnect conn1; +--source include/wait_until_disconnected.inc connection default; drop user mysqltest_user1@localhost; drop database mysqltest_db2; diff --git a/mysql-test/t/fix_priv_tables.test b/mysql-test/t/fix_priv_tables.test index c7cd500f8d2..eeda9bc8d15 100644 --- a/mysql-test/t/fix_priv_tables.test +++ b/mysql-test/t/fix_priv_tables.test @@ -51,8 +51,13 @@ echo; -- disable_query_log # Run the mysql_fix_privilege_tables.sql using "mysql --force" +# Determine the number of open sessions +--source include/count_sessions.inc --exec $MYSQL --force mysql < $MYSQL_FIX_PRIVILEGE_TABLES > $MYSQLTEST_VARDIR/tmp/fix_priv_tables.log 2>&1 --remove_file $MYSQLTEST_VARDIR/tmp/fix_priv_tables.log +# Wait till the number of open sessions is <= the number before the run with $MYSQL +# = The session caused by mysql has finished its disconnect +--source include/wait_until_count_sessions.inc -- enable_query_log -- enable_result_log diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test index c832cb79158..f27d4cf2fad 100644 --- a/mysql-test/t/flush.test +++ b/mysql-test/t/flush.test @@ -171,6 +171,7 @@ set session low_priority_updates=default; connect (con1,localhost,root,,); send select benchmark(200, (select sin(1))) > 1000; disconnect con1; +--source include/wait_until_disconnected.inc connection default; --echo End of 5.0 tests diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 063356d66b0..9db55f6adaa 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -471,3 +471,25 @@ CREATE TABLE t1(a TEXT); --error ER_WRONG_ARGUMENTS SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE); DROP TABLE t1; + +# +# BUG#42907 - Multi-term boolean fulltext query containing a single +# quote fails in 5.1.x +# +CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a)); +INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd'); +SELECT * FROM t1 WHERE MATCH(a) AGAINST('+awrd bwrd* +cwrd*' IN BOOLEAN MODE); +DROP TABLE t1; + +# +# BUG#37740 Server crashes on execute statement with full text search and match against +# +CREATE TABLE t1 (col text, FULLTEXT KEY full_text (col)); + +PREPARE s FROM + "SELECT MATCH (col) AGAINST('findme') FROM t1 ORDER BY MATCH (col) AGAINST('findme')" + ; + +EXECUTE s; +DEALLOCATE PREPARE s; +DROP TABLE t1; diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index dc17be219e3..207f3a436d0 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -50,6 +50,7 @@ set @@global.max_allowed_packet=1048576*100; --connect (newconn, localhost, root,,) eval select compress(repeat('aaaaaaaaaa', IF('$LOW_MEMORY', 10, 10000000))) is null; disconnect newconn; +--source include/wait_until_disconnected.inc connection default; set @@global.max_allowed_packet=default; @@ -88,6 +89,26 @@ select *, uncompress(a) from t1; select *, uncompress(a), uncompress(a) is null from t1; drop table t1; +# +# Bug #44796: valgrind: too many my_longlong10_to_str_8bit warnings after +# uncompressed_length +# + +CREATE TABLE t1 (c1 INT); +INSERT INTO t1 VALUES (1), (1111), (11111); + +# Disable warnings to avoid dependency on max_allowed_packet value +--disable_warnings +SELECT UNCOMPRESS(c1), UNCOMPRESSED_LENGTH(c1) FROM t1; +--enable_warnings + +# We do not need the results, just make sure there are no valgrind errors +--disable_result_log +EXPLAIN EXTENDED SELECT * FROM (SELECT UNCOMPRESSED_LENGTH(c1) FROM t1) AS s; +--enable_result_log + +DROP TABLE t1; + --echo End of 5.0 tests --disable_result_log --disable_query_log diff --git a/mysql-test/t/func_concat.test b/mysql-test/t/func_concat.test index f2aa0d004e5..1c7e5823fb2 100644 --- a/mysql-test/t/func_concat.test +++ b/mysql-test/t/func_concat.test @@ -78,3 +78,37 @@ SELECT * FROM t1 WHERE CONCAT(c1,' ',c2) REGEXP 'First.*'; DROP TABLE t1; --echo # End of 5.0 tests + + +--echo # +--echo # Bug #44743: Join in combination with concat does not always work +--echo # +CREATE TABLE t1 ( + a VARCHAR(100) NOT NULL DEFAULT '0', + b VARCHAR(2) NOT NULL DEFAULT '', + c VARCHAR(2) NOT NULL DEFAULT '', + d TEXT NOT NULL, + PRIMARY KEY (a, b, c), + KEY (a) +) DEFAULT CHARSET=utf8; + +INSERT INTO t1 VALUES ('gui_A', 'a', 'b', 'str1'), + ('gui_AB', 'a', 'b', 'str2'), ('gui_ABC', 'a', 'b', 'str3'); + +CREATE TABLE t2 ( + a VARCHAR(100) NOT NULL DEFAULT '', + PRIMARY KEY (a) +) DEFAULT CHARSET=latin1; + +INSERT INTO t2 VALUES ('A'), ('AB'), ('ABC'); + +SELECT CONCAT('gui_', t2.a), t1.d FROM t2 + LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; + +EXPLAIN SELECT CONCAT('gui_', t2.a), t1.d FROM t2 + LEFT JOIN t1 ON t1.a = CONCAT('gui_', t2.a) AND t1.b = 'a' AND t1.c = 'b'; + +DROP TABLE t1, t2; + + +--echo # End of 5.1 tests diff --git a/mysql-test/t/func_crypt.test b/mysql-test/t/func_crypt.test index cc3cdb9564d..6dedeaa0fef 100644 --- a/mysql-test/t/func_crypt.test +++ b/mysql-test/t/func_crypt.test @@ -56,3 +56,15 @@ explain extended select password('idkfa '), old_password('idkfa'); select encrypt('1234','_.'); # End of 4.1 tests + +--echo # +--echo # Bug #44767: invalid memory reads in password() and old_password() +--echo # functions +--echo # + +CREATE TABLE t1(c1 MEDIUMBLOB); +INSERT INTO t1 VALUES (REPEAT('a', 1024)); +SELECT OLD_PASSWORD(c1), PASSWORD(c1) FROM t1; +DROP TABLE t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/func_des_encrypt.test b/mysql-test/t/func_des_encrypt.test index b757a632adf..2c364a40090 100644 --- a/mysql-test/t/func_des_encrypt.test +++ b/mysql-test/t/func_des_encrypt.test @@ -9,3 +9,31 @@ select des_encrypt('hello'); # End of 4.1 tests + +--echo # +--echo # Bug #11643: des_encrypt() causes server to die +--echo # + +CREATE TABLE t1 (des VARBINARY(200) NOT NULL DEFAULT '') ENGINE=MyISAM; + +INSERT INTO t1 VALUES ('1234'), ('12345'), ('123456'), ('1234567'); + +UPDATE t1 SET des=DES_ENCRYPT('1234'); + +SELECT LENGTH(des) FROM t1; +SELECT DES_DECRYPT(des) FROM t1; + +SELECT + LENGTH(DES_ENCRYPT('1234')), + LENGTH(DES_ENCRYPT('12345')), + LENGTH(DES_ENCRYPT('123456')), + LENGTH(DES_ENCRYPT('1234567')); +SELECT + DES_DECRYPT(DES_ENCRYPT('1234')), + DES_DECRYPT(DES_ENCRYPT('12345')), + DES_DECRYPT(DES_ENCRYPT('123456')), + DES_DECRYPT(DES_ENCRYPT('1234567')); + +DROP TABLE t1; + +--Echo End of 5.0 tests diff --git a/mysql-test/t/func_encrypt.test b/mysql-test/t/func_encrypt.test index bcf1e5a77f4..879732fc81f 100644 --- a/mysql-test/t/func_encrypt.test +++ b/mysql-test/t/func_encrypt.test @@ -88,3 +88,18 @@ select hex(des_decrypt(des_encrypt("hello","hidden"))); explain extended select des_decrypt(des_encrypt("hello",4),'password2'), des_decrypt(des_encrypt("hello","hidden")); # End of 4.1 tests + +# +# Bug#44365 valgrind warnings with encrypt() function +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (f1 smallint(6) default null, f2 mediumtext character set utf8) +engine=myisam default charset=latin1; +insert into t1 values (null,'contraction\'s'); +insert into t1 values (-15818,'requirement\'s'); +--disable_result_log +select encrypt(f1,f2) as a from t1,(select encrypt(f1,f2) as b from t1) a; +--enable_result_log +drop table t1; diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 3fc1697f146..adc074259ad 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -439,4 +439,21 @@ SELECT CASE c1 WHEN c1 + 1 THEN 1 END, ABS(AVG(c0)) FROM t1; DROP TABLE t1; +# +# Bug #44399: crash with statement using TEXT columns, aggregates, GROUP BY, +# and HAVING +# + +CREATE TABLE t1(a TEXT, b INT, c INT UNSIGNED, d DECIMAL(12,2), e REAL); +INSERT INTO t1 VALUES('iynfj', 1, 1, 1, 1); +INSERT INTO t1 VALUES('innfj', 2, 2, 2, 2); +SELECT SUM( DISTINCT a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a); +SELECT SUM( DISTINCT b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b); +SELECT SUM( DISTINCT c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c); +SELECT SUM( DISTINCT d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d); +SELECT SUM( DISTINCT e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e); +SELECT SUM( DISTINCT e ) FROM t1 GROUP BY b,c,d HAVING (b,c,d) IN + ((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d)); +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index e67f5f29e3a..91fdce8addb 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -269,6 +269,15 @@ SELECT a, ROUND(a) FROM t1; DROP TABLE t1; +# +# Bug#45152 crash with round() function on longtext column in a derived table +# +CREATE TABLE t1(f1 LONGTEXT) engine=myisam; +INSERT INTO t1 VALUES ('a'); +SELECT 1 FROM (SELECT ROUND(f1) AS a FROM t1) AS s WHERE a LIKE 'a'; +SELECT 1 FROM (SELECT ROUND(f1, f1) AS a FROM t1) AS s WHERE a LIKE 'a'; +DROP TABLE t1; + --echo End of 5.0 tests # @@ -282,4 +291,22 @@ SELECT 1e300 / 1e-300; SELECT EXP(750); SELECT POW(10, 309); +--echo # +--echo # Bug #44768: SIGFPE crash when selecting rand from a view +--echo # containing null +--echo # + +CREATE OR REPLACE VIEW v1 AS SELECT NULL AS a; +SELECT RAND(a) FROM v1; +DROP VIEW v1; + +SELECT RAND(a) FROM (SELECT NULL AS a) b; + +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (NULL); +SELECT RAND(i) FROM t1; +DROP TABLE t1; + +--echo # + --echo End of 5.1 tests diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index eb243d38238..6590b43f2dc 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -454,6 +454,11 @@ SELECT * FROM t1 WHERE a = NAME_CONST('reportDate', _binary'2009-01-09' COLLATE 'binary'); DROP TABLE t1; +# +# Bug#35515: Aliases of variables in binary log are ignored with NAME_CONST +# +select NAME_CONST('_id',1234) as id; + --echo End of 5.0 tests # diff --git a/mysql-test/t/func_set.test b/mysql-test/t/func_set.test index e4fde6e0e0e..294efa8caf1 100644 --- a/mysql-test/t/func_set.test +++ b/mysql-test/t/func_set.test @@ -72,3 +72,28 @@ SELECT INTERVAL(0.0, CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL), CAST(DATE(NULL) AS DECIMAL)); --echo End of 5.0 tests + +# +# Bug#44367 valgrind warnings with find_in_set() functions +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (f1 set('test1','test2','test3') character set utf8 default null) +engine=myisam default charset=latin1; +insert into t1 values (''),(null),(null),(''),(''),(''); +select find_in_set(f1,f1) as a from t1,(select find_in_set(f1,f1) as b from t1) a; +drop table t1; +# +# Bug#45168: assertion with convert() and empty set value +# +CREATE TABLE t1( a SET('a', 'b', 'c') ); +CREATE TABLE t2( a SET('a', 'b', 'c') ); + +INSERT INTO t1 VALUES ('d'); +INSERT INTO t2 VALUES (''); + +SELECT CONVERT( a USING latin1 ) FROM t1; +SELECT CONVERT( a USING latin1 ) FROM t2; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index b71dbe91467..7cb7f7f72d2 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1282,4 +1282,26 @@ INSERT INTO t1 VALUES ('2008-12-31','aaaaaa'); SELECT DATE_FORMAT(c, GET_FORMAT(DATE, 'eur')) h, CONCAT(UPPER(aa),', ', aa) i FROM t1; DROP TABLE t1; + +--echo # +--echo # BUG#44774: load_file function produces valgrind warnings +--echo # +CREATE TABLE t1 (a TINYBLOB); +INSERT INTO t1 VALUES ('aaaaaaaa'); +SELECT LOAD_FILE(a) FROM t1; +DROP TABLE t1; + + --echo End of 5.0 tests + +# +# Bug#44358 valgrind errors with decode() function +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1(f1 tinyint default null)engine=myisam; +insert into t1 values (-1),(null); +explain select 1 as a from t1,(select decode(f1,f1) as b from t1) a; +explain select 1 as a from t1,(select encode(f1,f1) as b from t1) a; +drop table t1; diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index dad22f42571..19bbcf19cca 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -41,7 +41,7 @@ while ($1) let $2=10; while ($2) { - eval INSERT INTO t2 (g) VALUES (GeometryFromWKB(LineString(Point($1 * 10 - 9, $2 * 10 - 9), Point($1 * 10, $2 * 10)))); + eval INSERT INTO t2 (g) VALUES (LineString(Point($1 * 10 - 9, $2 * 10 - 9), Point($1 * 10, $2 * 10))); dec $2; } dec $1; @@ -61,7 +61,7 @@ while ($1) let $2=10; while ($2) { - eval DELETE FROM t2 WHERE Within(g, Envelope(GeometryFromWKB(LineString(Point($1 * 10 - 9, $2 * 10 - 9), Point($1 * 10, $2 * 10))))); + eval DELETE FROM t2 WHERE Within(g, Envelope(GeometryFromWKB(Point($1 * 10 - 9, $2 * 10 - 9), Point($1 * 10, $2 * 10)))); SELECT count(*) FROM t2; dec $2; } @@ -235,11 +235,11 @@ DROP TABLE t1; # Bug #21888: Query on GEOMETRY field using PointFromWKB() results in lost connection # CREATE TABLE t1 (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); -INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,1))); -INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(1,0))); -INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,1))); -INSERT INTO t1 (foo) VALUES (PointFromWKB(POINT(0,0))); -SELECT 1 FROM t1 WHERE foo != PointFromWKB(POINT(0,0)); +INSERT INTO t1 (foo) VALUES (POINT(1,1)); +INSERT INTO t1 (foo) VALUES (POINT(1,0)); +INSERT INTO t1 (foo) VALUES (POINT(0,1)); +INSERT INTO t1 (foo) VALUES (POINT(0,0)); +SELECT 1 FROM t1 WHERE foo != POINT(0,0); DROP TABLE t1; # @@ -802,35 +802,35 @@ DROP TABLE t1; # create table t1 (a geometry not null, spatial index(a)); -insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 131072))); -insert into t1 values (PointFromWKB(POINT(9.1248812352444e+192, 2.9740338169556e+284))); -insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, -0))); -insert into t1 values (PointFromWKB(POINT(1.49166814624e-154, 2.0880974297595e-53))); -insert into t1 values (PointFromWKB(POINT(4.0917382598702e+149, 1.2024538023802e+111))); -insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 2.9993936277913e-241))); -insert into t1 values (PointFromWKB(POINT(2.5243548967072e-29, 1.2024538023802e+111))); -insert into t1 values (PointFromWKB(POINT(0, 6.9835074892995e-251))); -insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 3.1050361846014e+231))); -insert into t1 values (PointFromWKB(POINT(2.8728483499323e-188, 2.4600631144627e+260))); -insert into t1 values (PointFromWKB(POINT(3.0517578125e-05, 2.0349165139404e+236))); -insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 1.1818212630766e-125))); -insert into t1 values (PointFromWKB(POINT(2.481040258324e-265, 5.7766220027675e-275))); -insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 2.5243548967072e-29))); -insert into t1 values (PointFromWKB(POINT(5.7766220027675e-275, 9.9464647281957e+86))); -insert into t1 values (PointFromWKB(POINT(2.2181357552967e+130, 3.7857669957337e-270))); -insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.6893488147419e+19))); -insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.7537584144024e+255))); -insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 1.8033161362863e-130))); -insert into t1 values (PointFromWKB(POINT(0, 5.8774717541114e-39))); -insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 2.2761049594727e-159))); -insert into t1 values (PointFromWKB(POINT(6.243497100632e+144, 3.7857669957337e-270))); -insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 2.6355494858076e-82))); -insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 3.8518598887745e-34))); -insert into t1 values (PointFromWKB(POINT(4.6566128730774e-10, 2.0880974297595e-53))); -insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 1.8827498946116e-183))); -insert into t1 values (PointFromWKB(POINT(1.8033161362863e-130, 9.1248812352444e+192))); -insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, 2.2761049594727e-159))); -insert into t1 values (PointFromWKB(POINT(1.94906280228e+289, 1.2338789709327e-178))); +insert into t1 values (POINT(1.1517219314031e+164, 131072)); +insert into t1 values (POINT(9.1248812352444e+192, 2.9740338169556e+284)); +insert into t1 values (POINT(4.7783097267365e-299, -0)); +insert into t1 values (POINT(1.49166814624e-154, 2.0880974297595e-53)); +insert into t1 values (POINT(4.0917382598702e+149, 1.2024538023802e+111)); +insert into t1 values (POINT(2.0349165139404e+236, 2.9993936277913e-241)); +insert into t1 values (POINT(2.5243548967072e-29, 1.2024538023802e+111)); +insert into t1 values (POINT(0, 6.9835074892995e-251)); +insert into t1 values (POINT(2.0880974297595e-53, 3.1050361846014e+231)); +insert into t1 values (POINT(2.8728483499323e-188, 2.4600631144627e+260)); +insert into t1 values (POINT(3.0517578125e-05, 2.0349165139404e+236)); +insert into t1 values (POINT(1.1517219314031e+164, 1.1818212630766e-125)); +insert into t1 values (POINT(2.481040258324e-265, 5.7766220027675e-275)); +insert into t1 values (POINT(2.0880974297595e-53, 2.5243548967072e-29)); +insert into t1 values (POINT(5.7766220027675e-275, 9.9464647281957e+86)); +insert into t1 values (POINT(2.2181357552967e+130, 3.7857669957337e-270)); +insert into t1 values (POINT(4.5767114681874e-246, 3.6893488147419e+19)); +insert into t1 values (POINT(4.5767114681874e-246, 3.7537584144024e+255)); +insert into t1 values (POINT(3.7857669957337e-270, 1.8033161362863e-130)); +insert into t1 values (POINT(0, 5.8774717541114e-39)); +insert into t1 values (POINT(1.1517219314031e+164, 2.2761049594727e-159)); +insert into t1 values (POINT(6.243497100632e+144, 3.7857669957337e-270)); +insert into t1 values (POINT(3.7857669957337e-270, 2.6355494858076e-82)); +insert into t1 values (POINT(2.0349165139404e+236, 3.8518598887745e-34)); +insert into t1 values (POINT(4.6566128730774e-10, 2.0880974297595e-53)); +insert into t1 values (POINT(2.0880974297595e-53, 1.8827498946116e-183)); +insert into t1 values (POINT(1.8033161362863e-130, 9.1248812352444e+192)); +insert into t1 values (POINT(4.7783097267365e-299, 2.2761049594727e-159)); +insert into t1 values (POINT(1.94906280228e+289, 1.2338789709327e-178)); drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index a9c66855f96..4a60e777cc7 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -37,32 +37,32 @@ INSERT INTO gis_point VALUES INSERT INTO gis_line VALUES (105, LineFromText('LINESTRING(0 0,0 10,10 0)')), (106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), -(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10)))); +(107, LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10))))); INSERT INTO gis_polygon VALUES (108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), (109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')), -(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))); +(110, PolyFromWKB(AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))))); INSERT INTO gis_multi_point VALUES (111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), (112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), -(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10)))); +(113, MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10))))); INSERT INTO gis_multi_line VALUES (114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), (115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), -(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))); +(116, MLineFromWKB(AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))))); INSERT INTO gis_multi_polygon VALUES (117, MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), (118, MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), -(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))); +(119, MPolyFromWKB(AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))))); INSERT INTO gis_geometrycollection VALUES (120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), -(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))); +(121, GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))); INSERT into gis_geometry SELECT * FROM gis_point; INSERT into gis_geometry SELECT * FROM gis_line; @@ -667,4 +667,28 @@ desc v1; drop view v1; drop table t1; +# +# Bug#44684: valgrind reports invalid reads in +# Item_func_spatial_collection::val_str +# +SELECT MultiPoint(12345,''); +SELECT MultiPoint(123451,''); +SELECT MultiPoint(1234512,''); +SELECT MultiPoint(12345123,''); + +SELECT MultiLineString(12345,''); +SELECT MultiLineString(123451,''); +SELECT MultiLineString(1234512,''); +SELECT MultiLineString(12345123,''); + +SELECT LineString(12345,''); +SELECT LineString(123451,''); +SELECT LineString(1234512,''); +SELECT LineString(12345123,''); + +SELECT Polygon(12345,''); +SELECT Polygon(123451,''); +SELECT Polygon(1234512,''); +SELECT Polygon(12345123,''); + --echo End of 5.1 tests diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 2e42bdbf06c..bcd393bd6ab 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -1471,5 +1471,59 @@ DROP DATABASE dbbug33464; SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; +# +# Bug#44658 Create procedure makes server crash when user does not have ALL privilege +# +CREATE USER user1; +CREATE USER user2; +GRANT CREATE ON db1.* TO 'user1'@'localhost'; +GRANT CREATE ROUTINE ON db1.* TO 'user1'@'localhost'; +GRANT CREATE ON db1.* TO 'user2'@'%'; +GRANT CREATE ROUTINE ON db1.* TO 'user2'@'%'; +FLUSH PRIVILEGES; +SHOW GRANTS FOR 'user1'@'localhost'; +connect (con1,localhost,user1,,); +--echo ** Connect as user1 and create a procedure. +--echo ** The creation will imply implicitly assigned +--echo ** EXECUTE and ALTER ROUTINE privileges to +--echo ** the current user user1@localhost. +SELECT @@GLOBAL.sql_mode; +SELECT @@SESSION.sql_mode; +CREATE DATABASE db1; +DELIMITER ||; +CREATE PROCEDURE db1.proc1(p1 INT) + BEGIN + SET @x = 0; + REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; + END ;|| +DELIMITER ;|| + +connect (con2,localhost,user2,,); +--echo ** Connect as user2 and create a procedure. +--echo ** Implicitly assignment of privileges will +--echo ** fail because the user2@localhost is an +--echo ** unknown user. +DELIMITER ||; +CREATE PROCEDURE db1.proc2(p1 INT) + BEGIN + SET @x = 0; + REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; + END ;|| +DELIMITER ;|| + +connection default; +SHOW GRANTS FOR 'user1'@'localhost'; +SHOW GRANTS FOR 'user2'; +disconnect con1; +disconnect con2; +DROP PROCEDURE db1.proc1; +DROP PROCEDURE db1.proc2; +REVOKE ALL ON db1.* FROM 'user1'@'localhost'; +REVOKE ALL ON db1.* FROM 'user2'@'%'; +DROP USER 'user1'; +DROP USER 'user1'@'localhost'; +DROP USER 'user2'; +DROP DATABASE db1; + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index adfa77c881c..981be3efece 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -982,4 +982,39 @@ SELECT DISTINCT c FROM t1 WHERE d=4; DROP TABLE t1; +--echo # +--echo # Bug #45386: Wrong query result with MIN function in field list, +--echo # WHERE and GROUP BY clause +--echo # + +CREATE TABLE t (a INT, b INT, INDEX (a,b)); +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; + +--echo # test MIN +--echo #should use range with index for group by +EXPLAIN +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; +--echo #should return 1 row +SELECT a, MIN(b) FROM t WHERE b <> 0 GROUP BY a; + +--echo # test MAX +--echo #should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; +--echo #should return 1 row +SELECT a, MAX(b) FROM t WHERE b <> 1 GROUP BY a; + +--echo # test 3 ranges and use the middle one +INSERT INTO t SELECT a, 2 FROM t; + +--echo #should use range with index for group by +EXPLAIN +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; +--echo #should return 1 row +SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; + +DROP TABLE t; + + --echo End of 5.0 tests diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test index b51eeb27331..637c6ba1c81 100644 --- a/mysql-test/t/heap_btree.test +++ b/mysql-test/t/heap_btree.test @@ -253,5 +253,13 @@ insert into t1 values (1, 1), (3, 3), (2, 2), (NULL, 1), (NULL, NULL), (0, 0); select * from t1 where a is null; drop table t1; +-- echo # +-- echo # bug#39918 - memory (heap) engine crashing while executing self join with delete +-- echo # + +CREATE TABLE t1(a INT, KEY USING BTREE (a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(1),(1); +DELETE a1 FROM t1 AS a1, t1 AS a2 WHERE a1.a=a2.a; +DROP TABLE t1; --echo End of 5.0 tests diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index 2850c794393..59cd0fba024 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -50,7 +50,7 @@ order by table_name; end| delimiter ;| -create table t1 +create table t1 (f1 int(10) unsigned not null, f2 varchar(100) not null, primary key (f1), unique key (f2)); @@ -102,8 +102,8 @@ drop function f2; drop view v1, v2; # -# Bug#20543: select on information_schema strange warnings, view, different -# schemas/users +# Bug#20543 select on information_schema strange warnings, view, different +# schemas/users # # create database testdb_1; @@ -122,7 +122,7 @@ grant insert on v1 to testdb_2@localhost; create view v5 as select f1 from t1; grant show view on v5 to testdb_2@localhost; ---error 1227 +--error ER_SPECIFIC_ACCESS_DENIED_ERROR create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1; connection default; @@ -166,46 +166,53 @@ use testdb_1; revoke show view on v6 from testdb_2@localhost; connection testdb_2; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show fields from testdb_1.v5; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view testdb_1.v5; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show fields from testdb_1.v6; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view testdb_1.v6; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show fields from testdb_1.v7; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view testdb_1.v7; ---error 1345 +--error ER_VIEW_NO_EXPLAIN show create view v4; -#--error 1345 +#--error ER_VIEW_NO_EXPLAIN show fields from v4; show fields from v2; show fields from testdb_1.v1; show create view v2; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view testdb_1.v1; -select table_name from information_schema.columns a +select table_name from information_schema.columns a where a.table_name = 'v2'; -select view_definition from information_schema.views a +select view_definition from information_schema.views a where a.table_name = 'v2'; -select view_definition from information_schema.views a +select view_definition from information_schema.views a where a.table_name = 'testdb_1.v1'; ---error 1356 +--error ER_VIEW_INVALID select * from v2; connection default; use test; drop view testdb_1.v1, v2, testdb_1.v3, v4; drop database testdb_1; +connection testdb_1; +disconnect testdb_1; +--source include/wait_until_disconnected.inc +connection testdb_2; +disconnect testdb_2; +--source include/wait_until_disconnected.inc +connection default; drop user testdb_1@localhost; drop user testdb_2@localhost; @@ -236,4 +243,7 @@ show create view testdb_1.v1; connection default; drop user mysqltest_1@localhost; drop database testdb_1; +connection user1; disconnect user1; +--source include/wait_until_disconnected.inc +connection default; diff --git a/mysql-test/t/init_file.test b/mysql-test/t/init_file.test index ec74a5ff5ef..343f320550c 100644 --- a/mysql-test/t/init_file.test +++ b/mysql-test/t/init_file.test @@ -14,7 +14,7 @@ SELECT * INTO @X FROM init_file.startup limit 0,1; SELECT * INTO @Y FROM init_file.startup limit 1,1; SELECT YEAR(@X)-YEAR(@Y); # Enable this DROP DATABASE only after resolving bug #42507 -# DROP DATABASE init_file; +DROP DATABASE init_file; --echo ok --echo end of 4.1 tests @@ -28,4 +28,9 @@ select * from t1; # 30, 3, 11, 13 select * from t2; # Enable this DROP TABLE only after resolving bug #42507 -#drop table t1, t2; +drop table t1, t2; + +# MTR will restart server anyway, but by forcing it we avoid being warned +# about the apparent side effect + +call mtr.force_restart(); diff --git a/mysql-test/t/innodb-semi-consistent.test b/mysql-test/t/innodb-semi-consistent.test index 6d3020bb560..f5ed4677c69 100644 --- a/mysql-test/t/innodb-semi-consistent.test +++ b/mysql-test/t/innodb-semi-consistent.test @@ -2,7 +2,7 @@ -- source include/have_innodb.inc --disable_warnings -drop table if exists t1; +drop table if exists t1,t2; --enable_warnings # basic tests of semi-consistent reads @@ -53,3 +53,16 @@ drop table t1; connection default; disconnect a; disconnect b; + +# Bug 39320 +create table t1 (a int, b int) engine=myisam; +create table t2 (c int, d int, key (c)) engine=innodb; +insert into t1 values (1,1); +insert into t2 values (1,2); +connect (a,localhost,root,,); +connection a; +set session transaction isolation level read committed; +delete from t1 using t1 join t2 on t1.a = t2.c where t2.d in (1); +connection default; +disconnect a; +drop table t1, t2; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index b4ba75dd0d1..bb201d8993c 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -6,22 +6,45 @@ # Use innodb_mysql.[test|result] files instead. # # # # If nevertheless you need to make some changes here, please, forward # -# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com # +# your commit message # +# To: innodb_dev_ww@oracle.com # +# Cc: dev-innodb@mysql.com # # (otherwise your changes may be erased). # # # ####################################################################### -- source include/have_innodb.inc -# -# Small basic test with ignore -# +# Save the original values of some variables in order to be able to +# estimate how much they have changed during the tests. Previously this +# test assumed that e.g. rows_deleted is 0 here and after deleting 23 +# rows it expected that rows_deleted will be 23. Now we do not make +# assumptions about the values of the variables at the beginning, e.g. +# rows_deleted should be 23 + "rows_deleted before the test". This allows +# the test to be run multiple times without restarting the mysqld server. +# See Bug#43309 Test main.innodb can't be run twice +-- disable_query_log +SET @innodb_thread_concurrency_orig = @@innodb_thread_concurrency; + +SET @innodb_rows_deleted_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'); +SET @innodb_rows_inserted_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'); +SET @innodb_rows_updated_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'); +SET @innodb_row_lock_waits_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits'); +SET @innodb_row_lock_current_waits_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits'); +SET @innodb_row_lock_time_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time'); +SET @innodb_row_lock_time_max_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max'); +SET @innodb_row_lock_time_avg_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg'); +-- enable_query_log --disable_warnings drop table if exists t1,t2,t3,t4; drop database if exists mysqltest; --enable_warnings +# +# Small basic test with ignore +# + create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); @@ -1126,7 +1149,7 @@ show create table t2; create index id2 on t2 (id); show create table t2; drop index id2 on t2; ---error ER_DROP_INDEX_FK +--error ER_DROP_INDEX_FK,ER_DROP_INDEX_FK drop index id on t2; show create table t2; drop table t2; @@ -1295,18 +1318,18 @@ drop table t1; # Test for testable InnoDB status variables. This test # uses previous ones(pages_created, rows_deleted, ...). --replace_result 512 511 -show status like "Innodb_buffer_pool_pages_total"; -show status like "Innodb_page_size"; -show status like "Innodb_rows_deleted"; -show status like "Innodb_rows_inserted"; -show status like "Innodb_rows_updated"; +SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total'; +SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; +SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'; +SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; +SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; # Test for row locks InnoDB status variables. -show status like "Innodb_row_lock_waits"; -show status like "Innodb_row_lock_current_waits"; -show status like "Innodb_row_lock_time"; -show status like "Innodb_row_lock_time_max"; -show status like "Innodb_row_lock_time_avg"; +SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits'; +SELECT variable_value - @innodb_row_lock_current_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits'; +SELECT variable_value - @innodb_row_lock_time_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time'; +SELECT variable_value - @innodb_row_lock_time_max_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max'; +SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg'; # Test for innodb_sync_spin_loops variable show variables like "innodb_sync_spin_loops"; @@ -2526,6 +2549,8 @@ DROP TABLE bug35537; DISCONNECT c1; CONNECTION default; +SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig; + ####################################################################### # # # Please, DO NOT TOUCH this file as well as the innodb.result file. # @@ -2534,7 +2559,9 @@ CONNECTION default; # Use innodb_mysql.[test|result] files instead. # # # # If nevertheless you need to make some changes here, please, forward # -# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com # +# your commit message # +# To: innodb_dev_ww@oracle.com # +# Cc: dev-innodb@mysql.com # # (otherwise your changes may be erased). # # # ####################################################################### diff --git a/mysql-test/t/innodb_bug21704.test b/mysql-test/t/innodb_bug21704.test new file mode 100644 index 00000000000..c649b61034c --- /dev/null +++ b/mysql-test/t/innodb_bug21704.test @@ -0,0 +1,96 @@ +-- source include/have_innodb.inc + +--echo # +--echo # Bug#21704: Renaming column does not update FK definition. +--echo # + +--echo +--echo # Test that it's not possible to rename columns participating in a +--echo # foreign key (either in the referencing or referenced table). +--echo + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t3; +--enable_warnings + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT ENGINE=INNODB; + +CREATE TABLE t2 (a INT PRIMARY KEY, b INT, + CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1(a)) +ROW_FORMAT=COMPACT ENGINE=INNODB; + +CREATE TABLE t3 (a INT PRIMARY KEY, b INT, KEY(b), C INT, + CONSTRAINT fk2 FOREIGN KEY (b) REFERENCES t3 (a)) +ROW_FORMAT=COMPACT ENGINE=INNODB; + +INSERT INTO t1 VALUES (1,1),(2,2),(3,3); +INSERT INTO t2 VALUES (1,1),(2,2),(3,3); +INSERT INTO t3 VALUES (1,1,1),(2,2,2),(3,3,3); + +--echo +--echo # Test renaming the column in the referenced table. +--echo + +# mysqltest first does replace_regex, then replace_result +--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/ +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' +--error ER_ERROR_ON_RENAME +ALTER TABLE t1 CHANGE a c INT; + +--echo # Ensure that online column rename works. + +--enable_info +ALTER TABLE t1 CHANGE b c INT; +--disable_info + +--echo +--echo # Test renaming the column in the referencing table +--echo + +# mysqltest first does replace_regex, then replace_result +--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/ +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' +--error ER_ERROR_ON_RENAME +ALTER TABLE t2 CHANGE a c INT; + +--echo # Ensure that online column rename works. + +--enable_info +ALTER TABLE t2 CHANGE b c INT; +--disable_info + +--echo +--echo # Test with self-referential constraints +--echo + +# mysqltest first does replace_regex, then replace_result +--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/ +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' +--error ER_ERROR_ON_RENAME +ALTER TABLE t3 CHANGE a d INT; + +# mysqltest first does replace_regex, then replace_result +--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/ +# Embedded server doesn't chdir to data directory +--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' +--error ER_ERROR_ON_RENAME +ALTER TABLE t3 CHANGE b d INT; + +--echo # Ensure that online column rename works. + +--enable_info +ALTER TABLE t3 CHANGE c d INT; +--disable_info + +--echo +--echo # Cleanup. +--echo + +DROP TABLE t3; +DROP TABLE t2; +DROP TABLE t1; diff --git a/mysql-test/t/innodb_bug40565.test b/mysql-test/t/innodb_bug40565.test new file mode 100644 index 00000000000..d7aa0fd514a --- /dev/null +++ b/mysql-test/t/innodb_bug40565.test @@ -0,0 +1,10 @@ +# Bug #40565 Update Query Results in "1 Row Affected" But Should Be "Zero Rows" +-- source include/have_innodb.inc + +create table bug40565(value decimal(4,2)) engine=innodb; +insert into bug40565 values (1), (null); +--enable_info +update bug40565 set value=NULL; +update bug40565 set value=NULL; +--disable_info +drop table bug40565; diff --git a/mysql-test/t/innodb_bug42101-nonzero-master.opt b/mysql-test/t/innodb_bug42101-nonzero-master.opt new file mode 100644 index 00000000000..d71dbe17d5b --- /dev/null +++ b/mysql-test/t/innodb_bug42101-nonzero-master.opt @@ -0,0 +1 @@ +--innodb_commit_concurrency=1 diff --git a/mysql-test/t/innodb_bug42101-nonzero.test b/mysql-test/t/innodb_bug42101-nonzero.test new file mode 100644 index 00000000000..685fdf20489 --- /dev/null +++ b/mysql-test/t/innodb_bug42101-nonzero.test @@ -0,0 +1,21 @@ +# +# Bug#42101 Race condition in innodb_commit_concurrency +# http://bugs.mysql.com/42101 +# + +-- source include/have_innodb.inc + +--error ER_WRONG_ARGUMENTS +set global innodb_commit_concurrency=0; +select @@innodb_commit_concurrency; +set global innodb_commit_concurrency=1; +select @@innodb_commit_concurrency; +set global innodb_commit_concurrency=42; +select @@innodb_commit_concurrency; +set global innodb_commit_concurrency=DEFAULT; +select @@innodb_commit_concurrency; +--error ER_WRONG_ARGUMENTS +set global innodb_commit_concurrency=0; +select @@innodb_commit_concurrency; +set global innodb_commit_concurrency=1; +select @@innodb_commit_concurrency; diff --git a/mysql-test/t/innodb_bug42101.test b/mysql-test/t/innodb_bug42101.test new file mode 100644 index 00000000000..b6536490d48 --- /dev/null +++ b/mysql-test/t/innodb_bug42101.test @@ -0,0 +1,19 @@ +# +# Bug#42101 Race condition in innodb_commit_concurrency +# http://bugs.mysql.com/42101 +# + +-- source include/have_innodb.inc + +set global innodb_commit_concurrency=0; +select @@innodb_commit_concurrency; +--error ER_WRONG_ARGUMENTS +set global innodb_commit_concurrency=1; +select @@innodb_commit_concurrency; +--error ER_WRONG_ARGUMENTS +set global innodb_commit_concurrency=42; +select @@innodb_commit_concurrency; +set global innodb_commit_concurrency=0; +select @@innodb_commit_concurrency; +set global innodb_commit_concurrency=DEFAULT; +select @@innodb_commit_concurrency; diff --git a/mysql-test/t/innodb_bug45357.test b/mysql-test/t/innodb_bug45357.test new file mode 100644 index 00000000000..81727f352dd --- /dev/null +++ b/mysql-test/t/innodb_bug45357.test @@ -0,0 +1,10 @@ +-- source include/have_innodb.inc + +set session transaction isolation level read committed; + +create table bug45357(a int, b int,key(b))engine=innodb; +insert into bug45357 values (25170,6122); +update bug45357 set a=1 where b=30131; +delete from bug45357 where b < 20996; +delete from bug45357 where b < 7001; +drop table bug45357; diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 3e099167fcf..d604bcfe769 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -187,4 +187,280 @@ 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 End of 5.1 tests diff --git a/mysql-test/t/innodb_notembedded.test b/mysql-test/t/innodb_notembedded.test index 564b947c75e..c74dc931505 100644 --- a/mysql-test/t/innodb_notembedded.test +++ b/mysql-test/t/innodb_notembedded.test @@ -36,9 +36,15 @@ 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; -disconnect a; -disconnect b; SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index a5eb31496b1..155ae6937ef 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -490,5 +490,14 @@ SELECT * FROM t2; DROP TABLE t1, t2; +# +# Bug#43833 Simple INSERT crashes the server +# +CREATE TABLE t1(f1 FLOAT); +INSERT INTO t1 VALUES (1.23); +CREATE TABLE t2(f1 CHAR(1)); +INSERT INTO t2 SELECT f1 FROM t1; +DROP TABLE t1, t2; + --echo End of 5.0 tests. diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 499db086877..50c40e38b5b 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -324,6 +324,16 @@ SELECT * FROM t2; DROP TABLE t1, t2; # +# Bug#44306: Assertion fail on duplicate key error in 'INSERT ... SELECT' +# statements +# +CREATE TABLE t1 ( a INT KEY, b INT ); +INSERT INTO t1 VALUES ( 0, 1 ); +--error ER_DUP_ENTRY +INSERT INTO t1 ( b ) SELECT MAX( b ) FROM t1 WHERE b = 2; +DROP TABLE t1; + +# # Bug #26207: inserts don't work with shortened index # SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; @@ -382,5 +392,4 @@ insert into t1 values(1),(2),(3); create table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1; check table t2 extended; drop table t1,t2; ---echo ################################################################## - +--echo End of 5.0 tests diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index e93153f005c..47b5aa0292b 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -317,6 +317,134 @@ reap; connection locker; drop table t1; +# +# Bug#43230: SELECT ... FOR UPDATE can hang with FLUSH TABLES WITH READ LOCK indefinitely +# + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connect (con3,localhost,root,,); +connect (con4,localhost,root,,); +connect (con5,localhost,root,,); + +create table t1 (a int); +create table t2 like t1; + +connection con1; +--echo # con1 +lock tables t1 write; +connection con2; +--echo # con2 +send flush tables with read lock; +connection con5; +--echo # con5 +let $show_statement= SHOW PROCESSLIST; +let $field= State; +let $condition= = 'Flushing tables'; +--source include/wait_show_condition.inc +--echo # global read lock is taken +connection con3; +--echo # con3 +send select * from t2 for update; +connection con5; +let $show_statement= SHOW PROCESSLIST; +let $field= State; +let $condition= = 'Waiting for release of readlock'; +--source include/wait_show_condition.inc +--echo # waiting for release of read lock +connection con4; +--echo # con4 +--echo # would hang and later cause a deadlock +flush tables t2; +connection con1; +--echo # clean up +unlock tables; +connection con2; +--reap +unlock tables; +connection con3; +--reap +connection default; +disconnect con5; +disconnect con4; +disconnect con3; +disconnect con2; +disconnect con1; + +drop table t1,t2; + +--echo # +--echo # Lightweight version: +--echo # Ensure that the wait for a GRL is done before opening tables. +--echo # + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +create table t1 (a int); +create table t2 like t1; + +--echo # +--echo # UPDATE +--echo # + +connection default; +--echo # default +flush tables with read lock; +connection con1; +--echo # con1 +send update t2 set a = 1; +connection default; +--echo # default +let $show_statement= SHOW PROCESSLIST; +let $field= State; +let $condition= = 'Waiting for release of readlock'; +--source include/wait_show_condition.inc +--echo # statement is waiting for release of read lock +connection con2; +--echo # con2 +flush table t2; +connection default; +--echo # default +unlock tables; +connection con1; +--echo # con1 +--reap + +--echo # +--echo # LOCK TABLES .. WRITE +--echo # + +connection default; +--echo # default +flush tables with read lock; +connection con1; +--echo # con1 +send lock tables t2 write; +connection default; +--echo # default +let $show_statement= SHOW PROCESSLIST; +let $field= State; +let $condition= = 'Waiting for release of readlock'; +--source include/wait_show_condition.inc +--echo # statement is waiting for release of read lock +connection con2; +--echo # con2 +flush table t2; +connection default; +--echo # default +unlock tables; +connection con1; +--echo # con1 +--reap +unlock tables; + +connection default; +disconnect con2; +disconnect con1; + +drop table t1,t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/log_tables_debug.test b/mysql-test/t/log_tables_debug.test new file mode 100644 index 00000000000..19a62614608 --- /dev/null +++ b/mysql-test/t/log_tables_debug.test @@ -0,0 +1,94 @@ +### t/log_tables_debug.test +# +# Log-related tests requiring a debug-build server. +# + +# extra clean-up required due to Bug#38124, set to 1 when behavior has +# changed (see explanation in log_state.test) +let $fixed_bug38124 = 0; + +--source include/have_debug.inc + +# Several subtests modify global variables. Save the initial values only here, +# but reset to the initial values per subtest. +SET @old_general_log= @@global.general_log; +SET @old_general_log_file= @@global.general_log_file; +SET @old_slow_query_log= @@global.slow_query_log; +SET @old_slow_query_log_file= @@global.slow_query_log_file; + + +--echo # +--echo # Bug#45387 Information about statement id for prepared +--echo # statements missed from general log +--echo # + +let MYSQLD_DATADIR= `SELECT @@datadir`; + +# set logging to our specific bug log to control the entries added +SET @@global.general_log = ON; +SET @@global.general_log_file = 'bug45387_general.log'; + +# turn on output of timestamps on all log file entries +SET SESSION debug='+d,reset_log_last_time'; + +let CONN_ID= `SELECT CONNECTION_ID()`; +FLUSH LOGS; + +# reset log settings +SET @@global.general_log = @old_general_log; +SET @@global.general_log_file = @old_general_log_file; +SET SESSION debug='-d'; + +perl; + # get the relevant info from the surrounding perl invocation + $datadir= $ENV{'MYSQLD_DATADIR'}; + $conn_id= $ENV{'CONN_ID'}; + + # loop through the log file looking for the stmt querying for conn id + open(FILE, "$datadir/bug45387_general.log") or + die("Unable to read log file $datadir/bug45387_general.log: $!\n"); + while(<FILE>) { + if (/\d{6}\s+\d+:\d+:\d+[ \t]+(\d+)[ \t]+Query[ \t]+SELECT CONNECTION_ID/) { + $found= $1; + break; + } + } + + # print the result + if ($found == $conn_id) { + print "Bug#45387: ID match.\n"; + } else { + print "Bug#45387: Expected ID '$conn_id', found '$found' in log file.\n"; + print "Contents of log file:\n"; + seek(FILE, 0, 0); + while($line= <FILE>) { + print $line; + } + } + + close(FILE); +EOF + +--remove_file $MYSQLD_DATADIR/bug45387_general.log + +--echo End of 5.1 tests + + +--echo # +--echo # Cleanup +--echo # + +# Reset global system variables to initial values if forgotten somewhere above. +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; +SET global slow_query_log_file = @old_slow_query_log_file; +if(!$fixed_bug38124) +{ + --disable_query_log + let $my_var = `SELECT @old_general_log_file`; + eval SET @@global.general_log_file = '$my_var'; + let $my_var = `SELECT @old_slow_query_log_file`; + eval SET @@global.slow_query_log_file = '$my_var'; + --enable_query_log +} diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 414027cb485..878564c32ab 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -14,16 +14,18 @@ flush privileges; connect (sample,localhost,sample,password,d1); connection sample; select database(); ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create database d2; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create database D1; disconnect sample; +--source include/wait_until_disconnected.inc connection master; drop user 'sample'@'localhost'; drop database if exists d1; disconnect master; +--source include/wait_until_disconnected.inc connection default; # End of 4.1 tests diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index fd4937be488..43267870cb7 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -1520,6 +1520,114 @@ insert into m1 (col1) values (1); insert into m1 (col1) values (1); drop table m1, t1; + +--echo # +--echo # Bug#45800 crash when replacing into a merge table and there is a duplicate +--echo # + +--echo # Replace duplicate value in child table when merge table doesn't have key +CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM; +CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); +INSERT INTO m1 VALUES (666); +SELECT * FROM m1; +--echo # insert the duplicate value into the merge table +REPLACE INTO m1 VALUES (666); +SELECT * FROM m1; +DROP TABLE m1, t1; + +--echo # Insert... on duplicate key update (with duplicate values in the table) +CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM; +CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); +INSERT INTO m1 VALUES (666); +SELECT * FROM m1; +--echo # insert the duplicate value into the merge table +INSERT INTO m1 VALUES (666) ON DUPLICATE KEY UPDATE c1=c1+1; +SELECT * FROM m1; +DROP TABLE m1, t1; + +--echo # Insert duplicate value on MERGE table, where, MERGE has a key but MyISAM has more keys +CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1), UNIQUE (c2)); +CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c1)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); +INSERT INTO m1 VALUES (1,2); +--echo # insert the duplicate value into the merge table +--error ER_DUP_ENTRY +INSERT INTO m1 VALUES (3,2); +DROP TABLE m1,t1; + +--echo # Try to define MERGE and MyISAM with keys on different columns +CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1)); +CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c2)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1); +--echo # Try accessing the merge table for inserts (error occurs) +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (1,2); +--error ER_WRONG_MRG_TABLE +INSERT INTO m1 VALUES (1,4); +DROP TABLE m1,t1; + +# +#Bug #44040 MySQL allows creating a MERGE table upon VIEWs but crashes +#when using it +# + +CREATE TABLE t1 ( + col1 INT(10) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +CREATE VIEW v1 as SELECT * FROM t1; +CREATE TABLE m1 ( + col1 INT(10) +)ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(v1); + +--echo #Select should detect that the child table is a view and fail. +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; + +DROP VIEW v1; +DROP TABLE m1, t1; + +--echo # +--echo # Bug #45796: invalid memory reads and writes when altering merge and +--echo # base tables +--echo # + +CREATE TABLE t1(c1 INT) ENGINE=MyISAM; +CREATE TABLE m1(c1 INT) ENGINE=MERGE UNION=(t1); +ALTER TABLE m1 ADD INDEX idx_c1(c1); +# Open the MERGE table and allocate buffers based on children's definition. +--error ER_WRONG_MRG_TABLE +SELECT * FROM m1; +# Change the child table definition. +ALTER TABLE t1 ADD INDEX idx_c1(c1); +# Check that old buffers are not reused +SELECT * FROM m1; + +DROP TABLE m1; +DROP TABLE t1; + +--echo # +--echo # Bug45781 infinite hang/crash in "opening tables" after handler tries to +--echo # open merge table +--echo # + +--disable_warnings +DROP TABLE IF EXISTS m1,t1; +--enable_warnings + +CREATE TABLE t1(a int)engine=myisam; +CREATE TABLE t2(a int)engine=myisam; +CREATE TABLE t3(a int)engine=myisam; +CREATE TABLE t4(a int)engine=myisam; +CREATE TABLE t5(a int)engine=myisam; +CREATE TABLE t6(a int)engine=myisam; +CREATE TABLE t7(a int)engine=myisam; +CREATE TABLE m1(a int)engine=merge union=(t1,t2,t3,t4,t5,t6,t7); +SELECT 1 FROM m1; +--error ER_ILLEGAL_HA +HANDLER m1 OPEN; +DROP TABLE m1,t1,t2,t3,t4,t5,t6,t7; +--error ER_NO_SUCH_TABLE +SELECT 1 FROM m1; # Should not hang! + --echo End of 5.1 tests --disable_result_log diff --git a/mysql-test/t/multi_update2.test b/mysql-test/t/multi_update2.test index b82f442b6ce..a04518f4964 100644 --- a/mysql-test/t/multi_update2.test +++ b/mysql-test/t/multi_update2.test @@ -2,17 +2,46 @@ # Test of update statement that uses many tables. # -# This is a big test. ---source include/big_test.inc +# +# If we are running with +# - Valgrind -> $VALGRIND_TEST <> 0 +# - debug tracing -> @@session.debug LIKE '%trace%' +# the resource consumption (storage space needed, runtime) will be extreme. +# Therefore we require that the option "--big-test" is also set. +# ---disable_warnings -DROP TABLE IF EXISTS t1,t2; ---enable_warnings +let $need_big= 0; +--disable_query_log +--error 0,ER_UNKNOWN_SYSTEM_VARIABLE +SET @aux = @@session.debug; +if (!$mysql_errno) +{ + # We have returncode 0 = the server system variable @@session.debug exists. + # But we only need "--big-test" in case of tracing. + if (`SELECT @@session.debug LIKE '%trace%'`) + { + let $need_big= 1; + } +} +--enable_query_log +if ($VALGRIND_TEST) +{ + # We are running with Valgrind + inc $need_big; +} +if (`SELECT '$BIG_TEST' = '' AND $need_big = 1`) +{ + --skip Need "--big-test" when running with the option "--debug" or "--valgrind" +} # # Bug#1820 Rows not deleted from second table on multi-table delete # +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +--enable_warnings + CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL) ; --echo # The protocolling of many inserts into t1 is suppressed. --disable_query_log diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 2b235b01c5e..c3e236933c1 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1511,4 +1511,29 @@ drop table t1; create table t1 (a1 int,a2 int,a3 int,a4 int,a5 int,a6 int,a7 int,a8 int,a9 int,a10 int,a11 int,a12 int,a13 int,a14 int,a15 int,a16 int,a17 int,a18 int,a19 int,a20 int,a21 int,a22 int,a23 int,a24 int,a25 int,a26 int,a27 int,a28 int,a29 int,a30 int,a31 int,a32 int, a33 int, key(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21,a22,a23,a24,a25,a26,a27,a28,a29,a30,a31,a32,a33)) engine=myisam; +# +# Bug#43737: Select query return bad result +# +CREATE TABLE t1 ( + c INT, + d bit(1), + e INT, + f VARCHAR(1), + g BIT(1), + h BIT(1), + KEY (h, d, e, g) +); +INSERT INTO t1 VALUES + ( 3, 1, 1, 'a', 0, 0 ), + ( 3, 1, 5, 'a', 0, 0 ), + ( 10, 1, 2, 'a', 0, 1 ), + ( 10, 1, 3, 'a', 0, 1 ), + ( 10, 1, 4, 'a', 0, 1 ); + +SELECT f FROM t1 WHERE d = 1 AND e = 2 AND g = 0 AND h = 1; + +SELECT h+0, d + 0, e, g + 0 FROM t1; + +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/myisam_crash_before_flush_keys-master.opt b/mysql-test/t/myisam_crash_before_flush_keys-master.opt new file mode 100644 index 00000000000..425fda95086 --- /dev/null +++ b/mysql-test/t/myisam_crash_before_flush_keys-master.opt @@ -0,0 +1 @@ +--skip-stack-trace --skip-core-file diff --git a/mysql-test/t/myisam_crash_before_flush_keys.test b/mysql-test/t/myisam_crash_before_flush_keys.test new file mode 100644 index 00000000000..d6559f7760d --- /dev/null +++ b/mysql-test/t/myisam_crash_before_flush_keys.test @@ -0,0 +1,49 @@ +--echo # +--echo # BUG#41330 - Myisam table open count set to zero before index blocks are written. +--echo # +--source include/not_embedded.inc +--echo # Don't test this under valgrind, memory leaks will occur +--source include/not_valgrind.inc + +--echo # Binary must be compiled with debug for crash to occur +--source include/have_debug.inc + +let $MYSQLD_DATADIR= `select @@datadir`; +SET GLOBAL delay_key_write=ALL; +CREATE TABLE t1(a INT, + b INT, + PRIMARY KEY(a , b), + KEY(b)) ENGINE=MyISAM DELAY_KEY_WRITE = 1; +INSERT INTO t1 VALUES (1,2),(2,3),(3,4),(4,5),(5,6); + +--echo # Setup the mysqld to crash at certain point +SET SESSION debug="d,crash_before_flush_keys"; + +--echo # Write file to make mysql-test-run.pl expect crash +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +--echo # Run the crashing query +--error 2013 +FLUSH TABLE t1; + +--echo # Run MYISAMCHK tool to check the table t1 and repair +--replace_result $MYISAMCHK MYISAMCHK $MYSQLD_DATADIR MYSQLD_DATADIR +--error 255 +--exec $MYISAMCHK -cs $MYSQLD_DATADIR/test/t1 2>&1 +--exec $MYISAMCHK -rs $MYSQLD_DATADIR/test/t1 + +--echo # Write file to make mysql-test-run.pl start the server +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +--echo # Turn on reconnect +--enable_reconnect + +--echo # Call script that will poll the server waiting for +--echo # it to be back online again +--source include/wait_until_connected_again.inc + +SHOW CREATE TABLE t1; + +SELECT * FROM t1 FORCE INDEX (PRIMARY); + +DROP TABLE t1; diff --git a/mysql-test/t/myisam_debug.test b/mysql-test/t/myisam_debug.test new file mode 100644 index 00000000000..913668c5f22 --- /dev/null +++ b/mysql-test/t/myisam_debug.test @@ -0,0 +1,57 @@ +--source include/have_debug.inc + +# We disable this test in embedded mode because of BUG#43733 +--source include/not_embedded.inc + +--echo # +--echo # BUG#40827 - Killing insert-select to MyISAM can cause table corruption +--echo # + +CONNECT (insertConn, localhost, root,,); + +CREATE TABLE `t1` ( +`id` BIGINT(20) , +`id1` BIGINT(20) AUTO_INCREMENT, + KEY(id1), KEY(id) +) ENGINE=MyISAM; + +CREATE TABLE `t2` ( +`id` BIGINT(20) , +`id1` BIGINT(20) AUTO_INCREMENT, + KEY (id1), KEY(id) +) ENGINE=MyISAM; + +INSERT INTO t2 (id) VALUES (123); + +let $i = 10; +while ($i) +{ + INSERT INTO t2 (id) SELECT id FROM t2; + dec $i; +} + +--echo # Switch to insert Connection +CONNECTION insertConn; +SET SESSION debug='+d,wait_in_enable_indexes'; +--echo # Send insert data +SEND INSERT INTO t1(id) SELECT id FROM t2; + +--echo # Switch to default Connection +CONNECTION default; +--echo # Wait for insert data to reach the debug point + +let $wait_condition= + SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE STATE = "wait_in_enable_indexes" AND + INFO = "INSERT INTO t1(id) SELECT id FROM t2"; +--source include/wait_condition.inc + +SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE STATE = 'wait_in_enable_indexes' AND +INFO = "INSERT INTO t1(id) SELECT id FROM t2" +INTO @thread_id; + +KILL QUERY @thread_id; +CHECK TABLE t1; +DROP TABLE t1,t2; +DISCONNECT insertConn; diff --git a/mysql-test/t/myisampack.test b/mysql-test/t/myisampack.test index f554e7d0184..9d27ed53254 100644 --- a/mysql-test/t/myisampack.test +++ b/mysql-test/t/myisampack.test @@ -61,3 +61,49 @@ let $MYSQLD_DATADIR= `select @@datadir`; optimize table t1; repair table t1; drop table t1; + +--echo # +--echo # BUG#41541 - Valgrind warnings on packed MyISAM table +--echo # +CREATE TABLE t1(f1 VARCHAR(200), f2 TEXT); +INSERT INTO t1 VALUES ('foo', 'foo1'), ('bar', 'bar1'); +let $i=9; +--disable_query_log +while ($i) +{ + INSERT INTO t1 SELECT * FROM t1; + dec $i; +} +--enable_query_log +FLUSH TABLE t1; +--echo # Compress the table using MYISAMPACK tool +let $MYSQLD_DATADIR= `select @@datadir`; +--exec $MYISAMPACK $MYSQLD_DATADIR/test/t1 +SELECT COUNT(*) FROM t1; +DROP TABLE t1; + +--echo # +--echo # Bug #43973 - backup_myisam.test fails on 6.0-bugteam +--echo # +CREATE DATABASE mysql_db1; +CREATE TABLE mysql_db1.t1 (c1 VARCHAR(5), c2 int); +CREATE INDEX i1 ON mysql_db1.t1 (c1, c2); +INSERT INTO mysql_db1.t1 VALUES ('A',1); +INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; +INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; +INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; +INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; +INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; +INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; +INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; +FLUSH TABLE mysql_db1.t1; +# +--echo # Compress the table using MYISAMPACK tool +let $MYSQLD_DATADIR= `select @@datadir`; +--exec $MYISAMPACK -s $MYSQLD_DATADIR/mysql_db1/t1 +--echo # Run MYISAMCHK tool on the compressed table +--exec $MYISAMCHK -srq $MYSQLD_DATADIR/mysql_db1/t1 +SELECT COUNT(*) FROM mysql_db1.t1 WHERE c2 < 5; +# +DROP TABLE mysql_db1.t1; +DROP DATABASE mysql_db1; diff --git a/mysql-test/t/mysql-bug45236.test b/mysql-test/t/mysql-bug45236.test new file mode 100644 index 00000000000..efc10ed19ea --- /dev/null +++ b/mysql-test/t/mysql-bug45236.test @@ -0,0 +1,45 @@ +# +# Bug #45236: large blob inserts from mysqldump fail, possible memory issue ? +# +# This test consumes a significant amount of resources. +# Therefore it should be kept separated from other tests. +# Otherwise we might suffer from problems like +# Bug#43801 mysql.test takes too long, fails due to expired timeout +# on debx86-b in PB +# + +-- source include/not_embedded.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +# Have to change the global variable as the session variable is +# read-only. +SET @old_max_allowed_packet= @@global.max_allowed_packet; +# ~1 MB blob length + some space for the rest of INSERT query +SET @@global.max_allowed_packet = 1024 * 1024 + 1024; + +# Create a new connection since the global max_allowed_packet +# has no effect onr the current one +connect (con1, localhost, root,,); + +CREATE TABLE t1(data LONGBLOB); +INSERT INTO t1 SELECT CONCAT(REPEAT('1', 1024*1024 - 27), + "\'\r dummydb dummyhost"); + +let $outfile= $MYSQLTEST_VARDIR/tmp/bug41486.sql; +--error 0,1 +remove_file $outfile; +--exec $MYSQL_DUMP --compact -t test t1 > $outfile +# Check that the mysql client does not interpret the "\r" sequence as a command +--exec $MYSQL --max_allowed_packet=1M test < $outfile 2>&1 + +DROP TABLE t1; + +# Cleanup +disconnect con1; +--source include/wait_until_disconnected.inc +remove_file $outfile; +connection default; +SET @@global.max_allowed_packet = @old_max_allowed_packet; diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 7e970d5b104..cffa6392fa3 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -342,6 +342,21 @@ EOF remove_file $MYSQLTEST_VARDIR/tmp/bug31060.sql; +# +# Bug #39101: client -i (--ignore-spaces) option does not seem to work +# +--exec $MYSQL -i -e "SELECT COUNT (*)" +--exec $MYSQL --ignore-spaces -e "SELECT COUNT (*)" +--exec $MYSQL -b -i -e "SELECT COUNT (*)" + +# +# Bug#37268 'binary' character set makes CLI-internal commands case sensitive +# +--replace_regex /\([0-9]*\)/(errno)/ +--error 1 +--exec $MYSQL --default-character-set=binary test -e "CONNECT test invalid_hostname" 2>&1 +--exec $MYSQL --default-character-set=binary test -e "DELIMITER //" 2>&1 + --echo End of 5.0 tests # @@ -367,4 +382,24 @@ remove_file $MYSQLTEST_VARDIR/tmp/bug31060.sql; drop tables t1, t2; +# +# Bug #27884: mysql --html does not quote HTML special characters in output +# +--exec $MYSQL --html test -e "select '< & >' as '<'" + +# +# Bug #27884: mysql client + null byte +# +create table t1 (a char(5)); +insert into t1 values ('\0b\0'); +--exec $MYSQL test -e "select a from t1" +--exec $MYSQL -r test -e "select a from t1" +--exec $MYSQL -s test -e "select a from t1" +--exec $MYSQL --table test -e "select a from t1" +--exec $MYSQL --vertical test -e "select a from t1" +--exec $MYSQL --html test -e "select a from t1" +--exec $MYSQL --xml test -e "select a from t1" +drop table t1; + +--echo --echo End of tests diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index 6b2ce0cd2d6..d1f97d7287e 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -8,6 +8,26 @@ select LENGTH("$MYSQL_UPGRADE")>0 as have_mysql_upgrade; --enable_query_log # +# Hack: +# +# If running with Valgrind ($VALGRIND_TEST <> 0) then the resource +# consumption (CPU) for upgrading a large log table will be intense. +# Therefore, truncate the log table in advance and issue a statement +# that should be logged. +# +if (`SELECT $VALGRIND_TEST`) +{ + --disable_query_log + --disable_result_log + --disable_abort_on_error + TRUNCATE TABLE mysql.general_log; + SELECT 1; + --enable_abort_on_error + --enable_result_log + --enable_query_log +} + +# # Basic test that we can run mysql_upgrde and that it finds the # expected binaries it uses. # diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 46060649784..7767abe43d0 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -367,4 +367,16 @@ echo *** Unsigned server_id $s_id_max is found: $s_id_unsigned ***; eval SET @@global.server_id= $save_server_id; --remove_file $binlog_file +# +# Bug #41943: mysqlbinlog.exe crashes if --hexdump option is used +# + +RESET MASTER; +FLUSH LOGS; + +# We do not need the results, just make sure that mysqlbinlog does not crash +--exec $MYSQL_BINLOG --hexdump --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 >/dev/null + +--echo End of 5.0 tests + --echo End of 5.1 tests diff --git a/mysql-test/t/mysqlbinlog_row_big.test b/mysql-test/t/mysqlbinlog_row_big.test index f4e095af021..2a274d217dc 100644 --- a/mysql-test/t/mysqlbinlog_row_big.test +++ b/mysql-test/t/mysqlbinlog_row_big.test @@ -46,6 +46,29 @@ DROP TABLE IF EXISTS t1; SET timestamp=1000000000; --echo # +--echo # We need big packets. +--echo # +--echo # Capture initial value to reset at the end of the test +# use let $<var> = query_get_value as FLUSH statements +# in the test will set @<var> values to NULL +let $orig_max_allowed_packet = +query_get_value(SELECT @@global.max_allowed_packet, @@global.max_allowed_packet, 1); + +--echo # Now adjust max_allowed_packet +SET @@global.max_allowed_packet= 1024*1024*1024; + +--echo max_allowed_packet is a global variable. +--echo In order for the preceding change in max_allowed_packets' value +--echo to be seen and used, we must start a new connection. +--echo The change does not take effect with the current one. +--echo For simplicity, we just disconnect / reconnect connection default here. +--echo Disconnecting default connection... +disconnect default; +--echo Reconnecting default connection... +connect (default, localhost,root,,); +--echo default connection established, continuing with the test + +--echo # --echo # Delete all existing binary logs. --echo # RESET MASTER; @@ -58,34 +81,35 @@ eval CREATE TABLE t1 ( ) ENGINE=$engine_type DEFAULT CHARSET latin1; --echo # ---echo # Show how much rows are affected by each statement. +--echo # Show how many rows are affected by each statement. --echo # --enable_info --echo # ---echo # Insert a big row. +--echo # Insert some big rows. --echo # -# -# 256MB -#INSERT INTO t1 VALUES (REPEAT('ManyMegaByteBlck', 16777216)); -# -# 32MB + +--echo 256MB +INSERT INTO t1 VALUES (REPEAT('ManyMegaByteBlck', 16777216)); + +--echo 32MB INSERT INTO t1 VALUES (REPEAT('ManyMegaByteBlck', 2097152)); -# -# 4MB -#INSERT INTO t1 VALUES (REPEAT('ManyMegaByteBlck', 262144)); -# -# 512KB -#INSERT INTO t1 VALUES (REPEAT('ManyMegaByteBlck', 32768)); + +--echo 4MB +INSERT INTO t1 VALUES (REPEAT('ManyMegaByteBlck', 262144)); + +--echo 512KB +INSERT INTO t1 VALUES (REPEAT('ManyMegaByteBlck', 32768)); --echo # --echo # Show what we have in the table. --echo # Do not display the column value itself, just its length. --echo # +--sorted_result query_vertical SELECT LENGTH(c1) FROM t1; --echo # ---echo # Grow the row by updating. +--echo # Grow the rows by updating. --echo # UPDATE t1 SET c1 = CONCAT(c1, c1); @@ -93,15 +117,16 @@ UPDATE t1 SET c1 = CONCAT(c1, c1); --echo # Show what we have in the table. --echo # Do not display the column value itself, just its length. --echo # +--sorted_result query_vertical SELECT LENGTH(c1) FROM t1; --echo # ---echo # Delete the row. +--echo # Delete the rows. --echo # DELETE FROM t1 WHERE c1 >= 'ManyMegaByteBlck'; --echo # ---echo # Hide how much rows are affected by each statement. +--echo # Hide how many rows are affected by each statement. --echo # --disable_info @@ -118,13 +143,15 @@ FLUSH LOGS; --echo # at the bottom of the test script. --echo # let $MYSQLD_DATADIR= `select @@datadir`; ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ --exec $MYSQL_BINLOG -v -v $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/$mysqlbinlog_output --echo # --echo # Cleanup. --echo # +--echo # reset variable value to pass testcase checks +eval SET @@global.max_allowed_packet = $orig_max_allowed_packet; DROP TABLE t1; connection con1; diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index e834c60dcb5..986b5aba385 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -193,5 +193,22 @@ DROP DATABASE `a@b`; USE test; +--echo # +--echo # Bug #31821: --all-in-1 and --fix-table-names don't work together +--echo # + +--disable_warnings +drop table if exists `#mysql50#t1-1`; +--enable_warnings + +create table `#mysql50#t1-1` (a int); +--exec $MYSQL_CHECK --all-in-1 --fix-table-names --databases test +show tables like 't1-1'; +drop table `t1-1`; + +create table `#mysql50#t1-1` (a int); +--exec $MYSQL_CHECK --all-in-1 --fix-table-names test "#mysql50#t1-1" +show tables like 't1-1'; +drop table `t1-1`; --echo End of 5.1 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 6ebb001efb4..5eb0d627247 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1,6 +1,5 @@ # Embedded server doesn't support external clients --source include/not_embedded.inc ---source include/have_log_bin.inc # Binlog is required --source include/have_log_bin.inc @@ -1396,9 +1395,6 @@ drop user mysqltest_1@localhost; --echo # ---echo # Bug#21527 mysqldump incorrectly tries to LOCK TABLES on the ---echo # information_schema database. ---echo # --echo # Bug#21424 mysqldump failing to export/import views --echo # @@ -1464,6 +1460,13 @@ disconnect root; --remove_file $MYSQLTEST_VARDIR/tmp/bug21527.sql use test; +--echo # +--echo # Bug #21527 mysqldump incorrectly tries to LOCK TABLES on the +--echo # information_schema database. +--echo # +--echo # Bug #33762: mysqldump can not dump INFORMATION_SCHEMA +--echo # +--exec $MYSQL_DUMP --compact --opt -d information_schema TABLES --echo # --echo # Bug#19745 mysqldump --xml produces invalid xml @@ -1493,8 +1496,11 @@ INSERT INTO t1 VALUES (1), (2); --exec $MYSQL_DUMP --tab=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa test 2>&1 --exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ --fields-terminated-by=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa test +--error 2 --exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ --fields-enclosed-by=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa test +--error 2 --exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ --fields-optionally-enclosed-by=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa test +--error 2 --exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ --fields-escaped-by=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa test --exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ --lines-terminated-by=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa test @@ -1646,9 +1652,6 @@ DROP TABLE t1,t2; --replace_regex /-- [^D][^u][^m][^p].*// /\/\*!.*// / on [0-9 :-]+/ on DATE/ --exec $MYSQL_DUMP test -# We reset concurrent_inserts value to whatever it was at the start of the test -SET @@GLOBAL.CONCURRENT_INSERT = @OLD_CONCURRENT_INSERT; - --echo # --echo # Bug #42635: mysqldump includes views that were excluded using --echo # the --ignore-table option @@ -1680,7 +1683,6 @@ CREATE TABLE `כדשגכחךלדגכחשךדגחכךלדגכ` ( f1 INT ); DROP TABLE `כדשגכחךלדגכחשךדגחכךלדגכ`; SET NAMES latin1; - --echo # --echo # End of 5.0 tests --echo # @@ -1702,9 +1704,6 @@ DROP TABLE t1; # Added for use-thread option # -# THIS PART OF THE TEST IS DISABLED UNTIL Bug#32991 IS FIXED -if ($bug32991_fixed) { - create table t1 (a text , b text); create table t2 (a text , b text); insert t1 values ("Duck, Duck", "goose"); @@ -1742,8 +1741,6 @@ drop table t2; drop table words2; -} - --echo # --echo # Bug#16853 mysqldump doesn't show events --echo # @@ -1952,12 +1949,190 @@ DROP DATABASE mysqldump_test_db; --echo # -- End of test case for Bug#32538. --echo -# We reset concurrent_inserts value to whatever it was at the start of the test +--echo # +--echo # Bug#37377 Incorrect DROP TABLE statement in dump of a VIEW using --tab +--echo # + +create table t1 (a int); +create view v1 as select a from t1; + +--exec $MYSQL_DUMP --skip-comments --tab=$MYSQLTEST_VARDIR/tmp/ test t1 v1 + +drop view v1; +drop table t1; + +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/t1.sql +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/v1.sql + +drop view v1; +drop table t1; + +--remove_file $MYSQLTEST_VARDIR/tmp/t1.sql +--remove_file $MYSQLTEST_VARDIR/tmp/t1.txt +--remove_file $MYSQLTEST_VARDIR/tmp/v1.sql + + +--echo # +--echo # Bug#28071 mysqlimport does not quote or escape table name +--echo # + +--disable_warnings +drop table if exists `load`; +--enable_warnings +create table `load` (a varchar(255)); + +--copy_file std_data/words.dat $MYSQLTEST_VARDIR/tmp/load.txt + +--exec $MYSQL_IMPORT --ignore test $MYSQLTEST_VARDIR/tmp/load.txt + +select count(*) from `load`; + +--remove_file $MYSQLTEST_VARDIR/tmp/load.txt + +drop table `load`; + +# We reset concurrent_inserts value to whatever it was at the start of the +# test This line must be executed _after_ all test cases. SET @@GLOBAL.CONCURRENT_INSERT = @OLD_CONCURRENT_INSERT; ########################################################################### +--echo +--echo Bug #34861 - mysqldump with --tab gives weird output for triggers. +--echo + +CREATE TABLE t1 (f1 INT); +CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW SET @f1 = 1; +CREATE PROCEDURE pr1 () SELECT "Meow"; +CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SELECT "Meow"; + +--echo +SHOW TRIGGERS; +SHOW EVENTS; +SELECT name,body FROM mysql.proc WHERE NAME = 'pr1'; + +--echo +--echo dump table; if anything goes to stdout, it ends up here: --------------- +--exec $MYSQL_DUMP --compact --routines --triggers --events --result-file=$MYSQLTEST_VARDIR/tmp/test_34861.sql --tab=$MYSQLTEST_VARDIR/tmp/ test + +--echo +--echo drop everything +DROP EVENT ev1; +DROP TRIGGER tr1; +DROP TABLE t1; +DROP PROCEDURE pr1; + +--echo +--echo reload table; this should restore table and trigger +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/t1.sql +SHOW TRIGGERS; +SHOW EVENTS; +SELECT name,body FROM mysql.proc WHERE NAME = 'pr1'; + +--echo +--echo reload db; this should restore routines and events +--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/test_34861.sql +SHOW TRIGGERS; +SHOW EVENTS; +SELECT name,body FROM mysql.proc WHERE NAME = 'pr1'; + +--echo +--echo cleanup +--remove_file $MYSQLTEST_VARDIR/tmp/t1.txt +--remove_file $MYSQLTEST_VARDIR/tmp/t1.sql +--remove_file $MYSQLTEST_VARDIR/tmp/test_34861.sql +--disable_warnings +DROP EVENT IF EXISTS ev1; +DROP PROCEDURE IF EXISTS pr1; +DROP TRIGGER IF EXISTS tr1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +########################################################################### + +--echo # +--echo # Bug #30946: mysqldump silently ignores --default-character-set +--echo # when used with --tab +--echo # +--echo # Also see outfile_loaddata.test +--echo # + +SET NAMES utf8; +CREATE TABLE t1 (a INT, b CHAR(10) CHARSET koi8r, c CHAR(10) CHARSET latin1); +CREATE TABLE t2 LIKE t1; +INSERT INTO t1 VALUES (1, 'ABC-ÐБВ', 'DEF-ÂÃÄ'), (2, NULL, NULL); + +--let $file=$MYSQLTEST_VARDIR/tmp/t1.txt + + +--echo # error on multi-character ENCLOSED/ESCAPED BY + +--error 2 +--exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ --fields-enclosed-by='12345' test t1 +--remove_file $file + +--error 2 +--exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ --fields-escaped-by='12345' test t1 +--remove_file $file + +--echo # default '--default-charset' (binary): + +--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --tab=$MYSQLTEST_VARDIR/tmp/ test t1 +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + + +--echo # utf8: + +--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --default-character-set=utf8 --tab=$MYSQLTEST_VARDIR/tmp/ test t1 +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET utf8 +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + + +--echo # latin1 (data corruption is expected): + +--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --default-character-set=latin1 --tab=$MYSQLTEST_VARDIR/tmp/ test t1 +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET latin1 +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + + +--echo # koi8r (data corruption is expected): + +--exec $MYSQL_DUMP --character-sets-dir=$CHARSETSDIR --default-character-set=koi8r --tab=$MYSQLTEST_VARDIR/tmp/ test t1 +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET koi8r +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + + +SET NAMES default; + +DROP TABLE t1, t2; + +########################################################################### --echo # --echo # End of 5.1 tests --echo # diff --git a/mysql-test/t/mysqldump_restore.test b/mysql-test/t/mysqldump_restore.test new file mode 100644 index 00000000000..835ee3ee9e9 --- /dev/null +++ b/mysql-test/t/mysqldump_restore.test @@ -0,0 +1,111 @@ +############################################################################### +# mysqldump_restore.test +# +# Purpose: Tests if mysqldump output can be used to successfully restore +# tables and data. +# We CREATE a table, mysqldump it to a file, ALTER the original +# table's name, recreate the table from the mysqldump file, then +# utilize include/diff_tables to compare the original and recreated +# tables. +# +# We use several examples from mysqldump.test here and include +# the relevant bug numbers and headers from that test. +# +# NOTE: This test is not currently complete and offers only basic +# cases of mysqldump output being restored. +# Also, does NOT work with -X (xml) output! +# +# Author: pcrews +# Created: 2009-05-21 +# Last Change: +# Change date: +############################################################################### + +# Embedded server doesn't support external clients +--source include/not_embedded.inc +--source include/have_log_bin.inc + +--echo # Set concurrent_insert = 0 to prevent random errors +--echo # will reset to original value at the end of the test +SET @old_concurrent_insert = @@global.concurrent_insert; +SET @@global.concurrent_insert = 0; + +# Define mysqldumpfile here. It is used to capture mysqldump output +# in order to test the output's ability to restore an exact copy of the table +let $mysqldumpfile = $MYSQLTEST_VARDIR/tmp/mysqldumpfile.sql; + +--echo # Pre-test cleanup +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +--echo # Begin tests +--echo # +--echo # Bug#2005 Long decimal comparison bug. +--echo # +CREATE TABLE t1 (a DECIMAL(64, 20)); +INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"), +("0987654321098765432109876543210987654321"); +--exec $MYSQL_DUMP --compact test t1 > $mysqldumpfile +let $table_name = test.t1; +--source include/mysqldump.inc + +--echo # +--echo # Bug#3361 mysqldump quotes DECIMAL values inconsistently +--echo # +CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT); +# Check at first how mysql work with quoted decimal +INSERT INTO t1 VALUES (1.2345, 2.3456); +INSERT INTO t1 VALUES ('1.2345', 2.3456); +INSERT INTO t1 VALUES ("1.2345", 2.3456); +SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES'; +INSERT INTO t1 VALUES (1.2345, 2.3456); +INSERT INTO t1 VALUES ('1.2345', 2.3456); +--error ER_BAD_FIELD_ERROR +INSERT INTO t1 VALUES ("1.2345", 2.3456); +SET SQL_MODE=@OLD_SQL_MODE; + +# check how mysqldump make quoting +--exec $MYSQL_DUMP --compact test t1 > $mysqldumpfile +let $table_name = test.t1; +--source include/mysqldump.inc + +--echo # +--echo # Bug#1994 mysqldump does not correctly dump UCS2 data +--echo # Bug#4261 mysqldump 10.7 (mysql 4.1.2) --skip-extended-insert drops NULL from inserts +--echo # +CREATE TABLE t1 (a VARCHAR(255)) DEFAULT CHARSET koi8r; +INSERT INTO t1 VALUES (_koi8r x'C1C2C3C4C5'), (NULL); +--exec $MYSQL_DUMP --skip-comments --skip-extended-insert test t1 > $mysqldumpfile +let $table_name = test.t1; +--source include/mysqldump.inc + +--echo # +--echo # WL#2319 Exclude Tables from dump +--echo # +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (4),(5),(6); +--exec $MYSQL_DUMP --skip-comments --ignore-table=test.t1 test > $mysqldumpfile +let $table_name = test.t2; +--source include/mysqldump.inc +DROP TABLE t1; + +--echo # +--echo # Bug#8830 mysqldump --skip-extended-insert causes --hex-blob to dump wrong values +--echo # +CREATE TABLE t1 (`b` blob); +INSERT INTO `t1` VALUES (0x602010000280100005E71A); +--exec $MYSQL_DUMP --skip-extended-insert --hex-blob test --skip-comments t1 > $mysqldumpfile +let $table_name = test.t1; +--source include/mysqldump.inc + +--echo # End tests + +--echo # Cleanup +--echo # Reset concurrent_insert to its original value +SET @@global.concurrent_insert = @old_concurrent_insert; +--echo # remove mysqldumpfile +--error 0,1 +--remove_file $mysqldumpfile diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 55cd041aaf5..578b2bf5c6c 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -1780,6 +1780,56 @@ remove_file $MYSQLTEST_VARDIR/tmp/file2.tmp; --error 1 --exec echo "copy_file from_file;" | $MYSQL_TEST 2>&1 + +# ---------------------------------------------------------------------------- +# test for move_file +# ---------------------------------------------------------------------------- + +# - Check that if source file does not exist, nothing will be created. + +--error 1 +file_exists $MYSQLTEST_VARDIR/tmp/file1.tmp; +--error 1 +file_exists $MYSQLTEST_VARDIR/tmp/file2.tmp; +--error 1 +move_file $MYSQLTEST_VARDIR/tmp/file1.tmp $MYSQLTEST_VARDIR/tmp/file2.tmp; +--error 1 +file_exists $MYSQLTEST_VARDIR/tmp/file1.tmp; +--error 1 +file_exists $MYSQLTEST_VARDIR/tmp/file2.tmp; + +# - Check that if source file exists, everything works properly. + +--write_file $MYSQLTEST_VARDIR/tmp/file1.tmp +file1 +EOF + +move_file $MYSQLTEST_VARDIR/tmp/file1.tmp $MYSQLTEST_VARDIR/tmp/file2.tmp; +--error 1 +file_exists $MYSQLTEST_VARDIR/tmp/file1.tmp; +file_exists $MYSQLTEST_VARDIR/tmp/file2.tmp; + +# - Check that if destination file exists, everything works properly. +# (file2.tmp exists from the previous check; file1.tmp needs to be created) + +--write_file $MYSQLTEST_VARDIR/tmp/file1.tmp +file1 +EOF + +move_file $MYSQLTEST_VARDIR/tmp/file1.tmp $MYSQLTEST_VARDIR/tmp/file2.tmp; +--error 1 +file_exists $MYSQLTEST_VARDIR/tmp/file1.tmp; +file_exists $MYSQLTEST_VARDIR/tmp/file2.tmp; +remove_file $MYSQLTEST_VARDIR/tmp/file2.tmp; + +# - Check usage. + +--error 1 +--exec echo "move_file ;" | $MYSQL_TEST 2>&1 + +--error 1 +--exec echo "move_file from_file;" | $MYSQL_TEST 2>&1 + # ---------------------------------------------------------------------------- # test for chmod # ---------------------------------------------------------------------------- @@ -2037,6 +2087,10 @@ let $value= query_get_value(SELECT 'A B' AS "MyColumn", MyColumn, 1); let $value= query_get_value(SELECT 1 AS "My Column", My Column, 1); --echo value= $value # +# 4.1 Query containing , protected by quotes, quotes also on column +let $value= query_get_value('SELECT 1 as a, 2 as b', "b", 1); +--echo value= $value +# #------------ Negative tests ------------ # 5. Incomplete statement including missing parameters # 5.1 incomplete statement diff --git a/mysql-test/t/not_embedded_server.test b/mysql-test/t/not_embedded_server.test index 233ac3edfbb..fa2b659ec57 100644 --- a/mysql-test/t/not_embedded_server.test +++ b/mysql-test/t/not_embedded_server.test @@ -36,4 +36,10 @@ select 1; #execute stmt1; #deallocate prepare stmt1; +# +# Bug#43835: SHOW VARIABLES does not include 0 for slave_skip_errors +# + +SHOW VARIABLES like 'slave_skip_errors'; + # End of 5.1 tests diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index 240a977fdca..baa1603faab 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -238,7 +238,18 @@ DROP TABLE t1; --enable_query_log select 'is still running; no cipher request crashed the server' as result from dual; -## +# +# Bug#42158: leak: SSL_get_peer_certificate() doesn't have matching X509_free() +# + +GRANT SELECT ON test.* TO bug42158@localhost REQUIRE X509; +FLUSH PRIVILEGES; +connect(con1,localhost,bug42158,,,,,SSL); +SHOW STATUS LIKE 'Ssl_cipher'; +disconnect con1; +connection default; +DROP USER bug42158@localhost; + --echo End of 5.1 tests # Wait till we reached the initial number of concurrent sessions diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index f09c1aa7bd4..cca1e3209cc 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1361,3 +1361,44 @@ DROP TABLE t1; +# +# Bug#46454: MySQL wrong index optimisation leads to incorrect result & crashes +# +CREATE TABLE t1 ( + a INT, + b INT NOT NULL, + c char(100), + KEY (b, c), + KEY (b, a, c) +) +DEFAULT CHARSET = utf8; + +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); + +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; + +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; + +EXPLAIN +SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; +SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; + +DROP TABLE t1; diff --git a/mysql-test/t/outfile_loaddata.test b/mysql-test/t/outfile_loaddata.test index 2a120871e7d..3f62acbd214 100644 --- a/mysql-test/t/outfile_loaddata.test +++ b/mysql-test/t/outfile_loaddata.test @@ -111,3 +111,146 @@ SELECT HEX(c1) FROM t1; DROP TABLE t1; --echo # End of 5.0 tests. + +########################################################################### + +--echo # +--echo # Bug #30946: mysqldump silently ignores --default-character-set +--echo # when used with --tab +--echo # +--echo # Also see mysqldump.test +--echo # + +SET NAMES utf8; +CREATE TABLE t1 (a INT, b CHAR(10) CHARSET koi8r, c CHAR(10) CHARSET latin1); +CREATE TABLE t2 LIKE t1; +INSERT INTO t1 VALUES (1, 'ABC-ÐБВ', 'DEF-ÂÃÄ'), (2, NULL, NULL); + +--let $file=$MYSQLTEST_VARDIR/tmp/t1.txt + + +--echo # Error on multi-character ENCLOSED/ESCAPED BY + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--error 1083 +--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS ENCLOSED BY '12345' +--remove_file $file + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--error 1083 +--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS ESCAPED BY '12345' +--remove_file $file + + +--echo # "Not implemented" warning on multibyte ENCLOSED/ESCAPED BY character, +--echo # LOAD DATA rises error or has unpredictable result -- to be fixed later + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS ENCLOSED BY 'ÑŠ' +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--error 1083 # backward compatibility +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary FIELDS ENCLOSED BY 'ÑŠ' +--remove_file $file + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS ESCAPED BY 'ÑŠ' +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--error 1083 # backward compatibility +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary FIELDS ESCAPED BY 'ÑŠ' +--remove_file $file + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS TERMINATED BY 'ÑŠ' +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary FIELDS TERMINATED BY 'ÑŠ' +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * FROM t1 INTO OUTFILE '$file' LINES STARTING BY 'ÑŠ' +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary LINES STARTING BY 'ÑŠ' +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * FROM t1 INTO OUTFILE '$file' LINES TERMINATED BY 'ÑŠ' +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary LINES TERMINATED BY 'ÑŠ' +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + +--echo # Default (binary) charset: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' FROM t1 +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + + +--echo # latin1 charset (INTO OUTFILE warning is expected): + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' CHARACTER SET latin1 FROM t1 +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET latin1 +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + + +--echo # KOI8-R charset (INTO OUTFILE warning is expected): + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' CHARACTER SET koi8r FROM t1 +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET koi8r +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + + +--echo # UTF-8 charset: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT * INTO OUTFILE '$file' CHARACTER SET utf8 FROM t1 +--echo ################################################## +--cat_file $file +--echo ################################################## +TRUNCATE t2; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET utf8 +--remove_file $file +SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; + + +SET NAMES default; + +DROP TABLE t1, t2; + +########################################################################### +--echo # End of 5.1 tests. diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test index 7efc519e441..e579e371184 100644 --- a/mysql-test/t/parser.test +++ b/mysql-test/t/parser.test @@ -724,3 +724,7 @@ SELECT {fn CONCAT(a1,a2)} FROM t1; UPDATE t3 SET a4={d '1789-07-14'} WHERE a1=0; SELECT a1, a4 FROM t2 WHERE a4 LIKE {fn UCASE('1789-07-14')}; DROP TABLE t1, t2, t3; + +--echo # +--echo # End of 5.1 tests +--echo # diff --git a/mysql-test/t/parser_not_embedded.test b/mysql-test/t/parser_not_embedded.test new file mode 100644 index 00000000000..8ebeb9a8301 --- /dev/null +++ b/mysql-test/t/parser_not_embedded.test @@ -0,0 +1,26 @@ +--source include/not_embedded.inc + +########################################################################### +--echo # +--echo # Bug#39559: dump of stored procedures / functions with C-style +--echo # comment can't be read back +--echo # + +--write_file $MYSQLTEST_VARDIR/tmp/bug39559.sql +select 2 as expected, /*!01000/**/*/ 2 as result; +select 1 as expected, /*!99998/**/*/ 1 as result; +select 3 as expected, /*!01000 1 + */ 2 as result; +select 2 as expected, /*!99990 1 + */ 2 as result; +select 7 as expected, /*!01000 1 + /* 8 + */ 2 + */ 4 as result; +select 8 as expected, /*!99998 1 + /* 2 + */ 4 + */ 8 as result; +select 7 as expected, /*!01000 1 + /*!01000 8 + */ 2 + */ 4 as result; +select 7 as expected, /*!01000 1 + /*!99998 8 + */ 2 + */ 4 as result; +select 4 as expected, /*!99998 1 + /*!99998 8 + */ 2 + */ 4 as result; +select 4 as expected, /*!99998 1 + /*!01000 8 + */ 2 + */ 4 as result; +select 7 as expected, /*!01000 1 + /*!01000 8 + /*!01000 error */ 16 + */ 2 + */ 4 as result; +select 4 as expected, /* 1 + /*!01000 8 + */ 2 + */ 4; +EOF + +--exec $MYSQL --comment --force --table test <$MYSQLTEST_VARDIR/tmp/bug39559.sql +--remove_file $MYSQLTEST_VARDIR/tmp/bug39559.sql + diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index fc6a33819d6..0b497d86623 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -10,8 +10,6 @@ # --source include/have_partition.inc -SET @old_general_log= @@global.general_log; - --disable_warnings drop table if exists t1, t2; --enable_warnings @@ -1020,6 +1018,17 @@ subpartition by hash(a) partition p1 values less than (1) (subpartition sp0)); # +# Bug 46354 Crash with subpartition +# +--error ER_PARSE_ERROR +create table t1 (a int, b int) +partition by list (a) +subpartition by hash(a) +(partition p0 values in (0), + partition p1 values in (1) (subpartition sp0)); + + +# # BUG 15961 No error when subpartition defined without subpartition by clause # --error ER_SUBPARTITION_ERROR @@ -1641,23 +1650,6 @@ insert into t values (1); drop table t; # -# Bug #27816: Log tables ran with partitions crashes the server when logging -# is enabled. -# - -USE mysql; -TRUNCATE TABLE general_log; -SET @old_general_log_state = @@global.general_log; -SET GLOBAL general_log = 0; -ALTER TABLE general_log ENGINE = MyISAM; ---error ER_WRONG_USAGE -ALTER TABLE general_log PARTITION BY RANGE (TO_DAYS(event_time)) - (PARTITION p0 VALUES LESS THAN (733144), PARTITION p1 VALUES LESS THAN (3000000)); -ALTER TABLE general_log ENGINE = CSV; -SET GLOBAL general_log = @old_general_log_state; -use test; - -# # Bug #27084 partitioning by list seems failing when using case # BUG #18198: Case no longer supported, test case removed # @@ -1922,7 +1914,88 @@ insert into t1 select s1 from t1 where s1=3; select count(*) from t1; drop table t1; ---echo End of 5.1 tests +--echo # +--echo # Bug#42944: partition not pruned correctly +--echo # +CREATE TABLE t1 (a int) PARTITION BY RANGE (a) + (PARTITION p0 VALUES LESS THAN (100), + PARTITION p1 VALUES LESS THAN (200), + PARTITION p2 VALUES LESS THAN (300), + PARTITION p3 VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (10), (100), (200), (300), (400); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a>=200; +DROP TABLE t1; + +# +# Bug#44821: select distinct on partitioned table returns wrong results +# +CREATE TABLE t1 ( a INT, b INT, c INT, KEY bc(b, c) ) +PARTITION BY KEY (a, b) PARTITIONS 3 +; -SET @@global.general_log= @old_general_log; +INSERT INTO t1 VALUES +(17, 1, -8), +(3, 1, -7), +(23, 1, -6), +(22, 1, -5), +(11, 1, -4), +(21, 1, -3), +(19, 1, -2), +(30, 1, -1), + +(20, 1, 1), +(16, 1, 2), +(18, 1, 3), +(9, 1, 4), +(15, 1, 5), +(28, 1, 6), +(29, 1, 7), +(25, 1, 8), +(10, 1, 9), +(13, 1, 10), +(27, 1, 11), +(24, 1, 12), +(12, 1, 13), +(26, 1, 14), +(14, 1, 15) +; + +SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; + +EXPLAIN +SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c; + +DROP TABLE t1; + +--echo # +--echo # Bug #45807: crash accessing partitioned table and sql_mode +--echo # contains ONLY_FULL_GROUP_BY +--echo # + +SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; +CREATE TABLE t1(id INT,KEY(id)) ENGINE=MYISAM + PARTITION BY HASH(id) PARTITIONS 2; +DROP TABLE t1; +SET SESSION SQL_MODE=DEFAULT; + + +--echo # +--echo # BUG#45816 - assertion failure with index containing double +--echo # column on partitioned table +--echo # + +CREATE TABLE t1 ( + a INT DEFAULT NULL, + b DOUBLE DEFAULT NULL, + c INT DEFAULT NULL, + KEY idx2(b,a) +) PARTITION BY HASH(c) PARTITIONS 3; + +INSERT INTO t1 VALUES (6,8,9); +INSERT INTO t1 VALUES (6,8,10); + +SELECT 1 FROM t1 JOIN t1 AS t2 USING (a) FOR UPDATE; + +DROP TABLE t1; +--echo End of 5.1 tests diff --git a/mysql-test/t/partition_csv.test b/mysql-test/t/partition_csv.test index aa3d9d67c26..dd2ef7c1d1f 100644 --- a/mysql-test/t/partition_csv.test +++ b/mysql-test/t/partition_csv.test @@ -24,15 +24,68 @@ partition by list (a) (partition p0 values in (null)); # -# Bug#27816: Log tables ran with partitions crashes the server when logging -# is enabled. +# Bug #27816: Log tables ran with partitions crashes the server when logging +# is enabled. # + USE mysql; +TRUNCATE TABLE general_log; +SET @old_general_log_state = @@global.general_log; SET GLOBAL general_log = 0; ALTER TABLE general_log ENGINE = MyISAM; --error ER_WRONG_USAGE ALTER TABLE general_log PARTITION BY RANGE (TO_DAYS(event_time)) - (PARTITION p0 VALUES LESS THAN (733144), - PARTITION p1 VALUES LESS THAN (3000000)); + (PARTITION p0 VALUES LESS THAN (733144), PARTITION p1 VALUES LESS THAN (3000000)); ALTER TABLE general_log ENGINE = CSV; +SET GLOBAL general_log = @old_general_log_state; +use test; + +--echo # +--echo # Bug#40281: partitioning the general log table crashes the server +--echo # + +--echo # set up partitioned log, and switch to it + +USE mysql; +SET @old_general_log_state = @@global.general_log; +SET GLOBAL general_log = 0; +CREATE TABLE gl_partitioned LIKE general_log; +ALTER TABLE gl_partitioned ENGINE=myisam; +ALTER TABLE gl_partitioned PARTITION BY HASH (thread_id) PARTITIONS 10; +ALTER TABLE general_log RENAME TO gl_nonpartitioned; +ALTER TABLE gl_partitioned RENAME TO general_log; + +SELECT @@global.log_output INTO @old_glo; +SET GLOBAL log_output='table'; +SET GLOBAL general_log =1; + +--echo # do some things to be logged to partitioned log, should fail +USE /* 1 */ test; + +CREATE TABLE t1 (i INT); + +connect (con1,localhost,root,,); +INSERT INTO t1 VALUES (1); +SELECT * FROM t1; +disconnect con1; + +connection default; +USE mysql; +SET GLOBAL general_log =0; +ALTER TABLE general_log RENAME TO gl_partitioned; +ALTER TABLE gl_nonpartitioned RENAME TO general_log; + +--echo # show whether we actually logged anything (no) to general_log +SELECT COUNT(argument) FROM gl_partitioned; + +DROP TABLE gl_partitioned; + +SET GLOBAL log_output = @old_glo; SET GLOBAL general_log = 1; + +USE /* 2 */ test; +DROP TABLE t1; + +SET GLOBAL general_log = @old_general_log_state; + +--echo End of 5.1 tests diff --git a/mysql-test/t/partition_mgm.test b/mysql-test/t/partition_mgm.test index e17edf0a4e5..bf9b5ed174e 100644 --- a/mysql-test/t/partition_mgm.test +++ b/mysql-test/t/partition_mgm.test @@ -67,13 +67,14 @@ drop table t1; # # Verification tests for bug#14326 # ---error ER_PARSE_ERROR CREATE TABLE t1 (a INT) /*!50100 PARTITION BY HASH (a) /* Test of multi-line comment */ PARTITIONS 5 */; +SHOW CREATE TABLE t1; +DROP TABLE t1; CREATE TABLE t1 (a INT) /*!50100 PARTITION BY HASH (a) -- with a single line comment embedded diff --git a/mysql-test/t/partition_not_embedded.test b/mysql-test/t/partition_not_embedded.test new file mode 100644 index 00000000000..5c512085a9e --- /dev/null +++ b/mysql-test/t/partition_not_embedded.test @@ -0,0 +1,53 @@ +-- source include/have_partition.inc +-- source include/not_embedded.inc +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings +let $MYSQLD_DATADIR= `SELECT @@datadir`; + +# +# Bug#30102: rename table does corrupt tables with partition files on failure +# +--echo # Bug#30102 test +CREATE TABLE t1 (a INT) +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (6), + PARTITION `p1....................` VALUES LESS THAN (9), + PARTITION p2 VALUES LESS THAN MAXVALUE); +# partition p1 is 't1#P#p1' + @002e * 20 = 107 characters + file ending +# total path lenght of './test/t1#P#p1@002e@002e<...>@002e.MY[ID]' is 118 chars +--echo # List of files in database `test`, all original t1-files here +--list_files $MYSQLD_DATADIR/test t1* +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +--echo # Renaming to a file name where the first partition is 250 chars +--echo # and the second partition is 350 chars +# 7,7 avoids the error message, which is not deterministic. +--error 7,7 +RENAME TABLE t1 TO `t2_new..............................................end`; +# 1234567890123456789012345678901234567890123456 +--echo # List of files in database `test`, should not be any t2-files here +--list_files $MYSQLD_DATADIR/test t2* +--echo # List of files in database `test`, should be all t1-files here +--list_files $MYSQLD_DATADIR/test t1* +--sorted_result +SELECT * FROM t1; +--echo # List of files in database `test`, should be all t1-files here +--list_files $MYSQLD_DATADIR/test t1* +--echo # Renaming to a file name where the first partition is 156 chars +--echo # and the second partition is 256 chars +# 7,7 avoids the error message, which is not deterministic. +--error 7,7 +RENAME TABLE t1 TO `t2_............................_end`; +# 1234567890123456789012345678 +# 7 + 4 + 5 + 28 * 5 = 16 + 140 = 156 +--echo # List of files in database `test`, should not be any t2-files here +--list_files $MYSQLD_DATADIR/test t2* +--echo # List of files in database `test`, should be all t1-files here +--list_files $MYSQLD_DATADIR/test t1* +--sorted_result +SELECT * FROM t1; +DROP TABLE t1; +--echo # Should not be any files left here +--list_files $MYSQLD_DATADIR/test t1* +--list_files $MYSQLD_DATADIR/test t2* +--echo # End of bug#30102 test. diff --git a/mysql-test/t/partition_rename_longfilename.test b/mysql-test/t/partition_rename_longfilename.test new file mode 100644 index 00000000000..5e454f01da7 --- /dev/null +++ b/mysql-test/t/partition_rename_longfilename.test @@ -0,0 +1,50 @@ +-- source include/not_windows.inc +-- source include/have_partition.inc +-- source include/not_embedded.inc +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings +let $MYSQLD_DATADIR= `SELECT @@datadir`; + +# +# Bug#30102: rename table does corrupt tables with partition files on failure +# This test case renames the table such that the partition file name +# is 255 chars long. Due the restriction of 260 char path name (including drive label) +# this will fail in windows. +# Other tests related to this bug can be found in partition_not_embedded.test +# +CREATE TABLE t1 (a INT) +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (6), + PARTITION `p1....................` VALUES LESS THAN (9), + PARTITION p2 VALUES LESS THAN MAXVALUE); +# partition p1 is 't1#P#p1' + @002e * 20 = 107 characters + file ending +# total path lenght of './test/t1#P#p1@002e@002e<...>@002e.MY[ID]' is 118 chars +--echo # List of files in database `test`, all original t1-files here +--list_files $MYSQLD_DATADIR/test t1* +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +--echo # Renaming to a file name where the first partition is 155 chars +--echo # and the second partition is 255 chars +RENAME TABLE t1 TO `t2_............................end`; +# 1234567890123456789012345678 +# t2_ + end +# .MY[ID] or .frm +# #P#p[012] +# 28 * @002e +# 6 + 4 + 5 + 28 * 5 = 155 +--echo # List of files in database `test`, should not be any t1-files here +--list_files $MYSQLD_DATADIR/test t1* +--echo # List of files in database `test`, should be all t2-files here +--list_files $MYSQLD_DATADIR/test t2* +--sorted_result +SELECT * FROM `t2_............................end`; +RENAME TABLE `t2_............................end` to t1; +--echo # List of files in database `test`, should be all t1-files here +--list_files $MYSQLD_DATADIR/test t1* +--sorted_result +SELECT * FROM t1; +DROP TABLE t1; +--echo # Should not be any files left here +--list_files $MYSQLD_DATADIR/test t1* +--list_files $MYSQLD_DATADIR/test t2* +--echo # End of bug#30102 test. diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index 0635a58a4a6..7fc62b445c9 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -3,13 +3,16 @@ CREATE TABLE t1(a int) ENGINE=EXAMPLE; DROP TABLE t1; -INSTALL PLUGIN example SONAME 'ha_example.so'; +--replace_regex /\.dll/.so/ +eval INSTALL PLUGIN example SONAME $HA_EXAMPLE_SO; +--replace_regex /\.dll/.so/ --error 1125 -INSTALL PLUGIN EXAMPLE SONAME 'ha_example.so'; +eval INSTALL PLUGIN EXAMPLE SONAME $HA_EXAMPLE_SO; UNINSTALL PLUGIN example; -INSTALL PLUGIN example SONAME 'ha_example.so'; +--replace_regex /\.dll/.so/ +eval INSTALL PLUGIN example SONAME $HA_EXAMPLE_SO; CREATE TABLE t1(a int) ENGINE=EXAMPLE; @@ -30,8 +33,8 @@ UNINSTALL PLUGIN non_exist; --echo # Bug#32034: check_func_enum() does not check correct values but set it --echo # to impossible int val --echo # - -INSTALL PLUGIN example SONAME 'ha_example.so'; +--replace_regex /\.dll/.so/ +eval INSTALL PLUGIN example SONAME $HA_EXAMPLE_SO; SET GLOBAL example_enum_var= e1; SET GLOBAL example_enum_var= e2; @@ -45,7 +48,8 @@ UNINSTALL PLUGIN example; # # Bug #32757 hang with sql_mode set when setting some global variables # -INSTALL PLUGIN example SONAME 'ha_example.so'; +--replace_regex /\.dll/.so/ +eval INSTALL PLUGIN example SONAME $HA_EXAMPLE_SO; select @@session.sql_mode into @old_sql_mode; diff --git a/mysql-test/t/plugin_load-master.opt b/mysql-test/t/plugin_load-master.opt index 66637841f16..bb7831c5769 100644 --- a/mysql-test/t/plugin_load-master.opt +++ b/mysql-test/t/plugin_load-master.opt @@ -1,3 +1,3 @@ $EXAMPLE_PLUGIN_OPT -"--plugin-load=;EXAMPLE=ha_example.so;" +$EXAMPLE_PLUGIN_LOAD --loose-plugin-example-enum-var=e2 diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index 3ad3f394840..387b3ebdd05 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -70,71 +70,71 @@ prepare stmt1 from ' select 1 as my_col ' ; # prepare with parameter prepare stmt1 from ' select ? as my_col ' ; # prepare must fail (incomplete statements/wrong syntax) ---error 1064 +--error ER_PARSE_ERROR prepare ; ---error 1064 +--error ER_PARSE_ERROR prepare stmt1 ; ---error 1064 +--error ER_PARSE_ERROR prepare stmt1 from ; ---error 1064 +--error ER_PARSE_ERROR prepare_garbage stmt1 from ' select 1 ' ; ---error 1064 +--error ER_PARSE_ERROR prepare stmt1 from_garbage ' select 1 ' ; ---error 1064 +--error ER_PARSE_ERROR prepare stmt1 from ' select_garbage 1 ' ; ---error 1064 +--error ER_PARSE_ERROR prepare from ' select 1 ' ; ---error 1064 +--error ER_PARSE_ERROR prepare stmt1 ' select 1 ' ; ---error 1064 +--error ER_PARSE_ERROR prepare ? from ' select ? as my_col ' ; # statement in variable set @arg00='select 1 as my_col'; prepare stmt1 from @arg00; # prepare must fail (query variable is empty) set @arg00=''; ---error 1065 +--error ER_EMPTY_QUERY prepare stmt1 from @arg00; set @arg00=NULL; # prepare must fail (query variable is NULL) ---error 1064 +--error ER_PARSE_ERROR prepare stmt1 from @arg01; prepare stmt1 from ' select * from t1 where a <= 2 ' ; # prepare must fail (column x does not exist) ---error 1054 +--error ER_BAD_FIELD_ERROR prepare stmt1 from ' select * from t1 where x <= 2 ' ; # cases derived from client_test.c: test_null() # prepare must fail (column x does not exist) ---error 1054 +--error ER_BAD_FIELD_ERROR prepare stmt1 from ' insert into t1(a,x) values(?,?) ' ; ---error 1054 +--error ER_BAD_FIELD_ERROR prepare stmt1 from ' insert into t1(x,a) values(?,?) ' ; --disable_warnings drop table if exists not_exist ; --enable_warnings # prepare must fail (table does not exist) ---error 1146 +--error ER_NO_SUCH_TABLE prepare stmt1 from ' select * from not_exist where a <= 2 ' ; # case derived from client_test.c: test_prepare_syntax() # prepare must fail (incomplete statement) ---error 1064 +--error ER_PARSE_ERROR prepare stmt1 from ' insert into t1 values(? ' ; ---error 1064 +--error ER_PARSE_ERROR prepare stmt1 from ' select a, b from t1 where a=? and where ' ; ################ EXECUTE ################ # execute must fail (statement never_prepared never prepared) ---error 1243 +--error ER_UNKNOWN_STMT_HANDLER execute never_prepared ; # execute must fail (prepare stmt1 just failed, # but there was a successful prepare of stmt1 before) prepare stmt1 from ' select * from t1 where a <= 2 ' ; ---error 1146 +--error ER_NO_SUCH_TABLE prepare stmt1 from ' select * from not_exist where a <= 2 ' ; ---error 1243 +--error ER_UNKNOWN_STMT_HANDLER execute stmt1 ; # drop the table between prepare and execute @@ -149,7 +149,7 @@ prepare stmt2 from ' select * from t5 ' ; execute stmt2 ; drop table t5 ; # execute must fail (table was dropped after prepare) ---error 1146 +--error ER_NO_SUCH_TABLE execute stmt2 ; # cases derived from client_test.c: test_select_prepare() # 1. drop + create table (same column names/types/order) @@ -230,24 +230,24 @@ set @arg01='two' ; prepare stmt1 from ' select * from t1 where a <= ? ' ; execute stmt1 using @arg00; # execute must fail (too small number of parameters) ---error 1210 +--error ER_WRONG_ARGUMENTS execute stmt1 ; # execute must fail (too big number of parameters) ---error 1210 +--error ER_WRONG_ARGUMENTS execute stmt1 using @arg00, @arg01; # execute must fail (parameter is not set) execute stmt1 using @not_set; ################ DEALLOCATE ################ # deallocate must fail (the statement 'never_prepared' was never prepared) ---error 1243 +--error ER_UNKNOWN_STMT_HANDLER deallocate prepare never_prepared ; # deallocate must fail (prepare stmt1 just failed, # but there was a successful prepare before) prepare stmt1 from ' select * from t1 where a <= 2 ' ; ---error 1146 +--error ER_NO_SUCH_TABLE prepare stmt1 from ' select * from not_exist where a <= 2 ' ; ---error 1243 +--error ER_UNKNOWN_STMT_HANDLER deallocate prepare stmt1; create table t5 ( @@ -345,7 +345,7 @@ drop table if exists t5; prepare stmt1 from ' drop table if exists t5 ' ; execute stmt1 ; prepare stmt1 from ' drop table t5 ' ; ---error 1051 +--error ER_BAD_TABLE_ERROR execute stmt1 ; ## SELECT @@version @@ -432,7 +432,7 @@ drop database mysqltest ; prepare stmt3 from ' describe t2 '; execute stmt3; drop table t2 ; ---error 1146 +--error ER_NO_SUCH_TABLE execute stmt3; ## lock/unlock --error ER_UNSUPPORTED_PS @@ -440,18 +440,29 @@ prepare stmt3 from ' lock tables t1 read ' ; --error ER_UNSUPPORTED_PS prepare stmt3 from ' unlock tables ' ; ## Load/Unload table contents + +--let $datafile = $MYSQLTEST_VARDIR/tmp/data.txt +--error 0,1 +--remove_file $datafile + +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> --error ER_UNSUPPORTED_PS -prepare stmt1 from ' load data infile ''data.txt'' -into table t1 fields terminated by ''\t'' '; -prepare stmt1 from ' select * into outfile ''data.txt'' from t1 '; -execute stmt1 ; +eval prepare stmt1 from ' load data infile ''$datafile'' + into table t1 fields terminated by ''\t'' '; +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval prepare stmt1 from ' select * into outfile ''$datafile'' from t1 '; + execute stmt1 ; ## prepare stmt1 from ' optimize table t1 ' ; prepare stmt1 from ' analyze table t1 ' ; prepare stmt1 from ' checksum table t1 ' ; prepare stmt1 from ' repair table t1 ' ; + +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> --error ER_UNSUPPORTED_PS -prepare stmt1 from ' restore table t1 from ''data.txt'' ' ; +eval prepare stmt1 from ' restore table t1 from ''$datafile'' ' ; +--remove_file $datafile + ## handler --error ER_UNSUPPORTED_PS prepare stmt1 from ' handler t1 open '; @@ -566,7 +577,7 @@ drop table if exists new_t2; --enable_warnings prepare stmt3 from ' rename table t2 to new_t2 '; execute stmt3; ---error 1050 +--error ER_TABLE_EXISTS_ERROR execute stmt3; rename table new_t2 to t2; drop table t2; @@ -577,13 +588,13 @@ create table t5 (a int) ; # rename must fail, t7 does not exist # Clean up the filename here because embedded server reports whole path --replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ '' t7.frm t7 ---error 1017 +--error ER_FILE_NOT_FOUND execute stmt1 ; create table t7 (a int) ; # rename, t5 -> t6 and t7 -> t8 execute stmt1 ; # rename must fail, t5 and t7 does not exist t6 and t8 already exist ---error 1050 +--error ER_TABLE_EXISTS_ERROR execute stmt1 ; rename table t6 to t5, t8 to t7 ; # rename, t5 -> t6 and t7 -> t8 diff --git a/mysql-test/t/query_cache_debug.test b/mysql-test/t/query_cache_debug.test index 217dadf0ae8..87dcba6d5b8 100644 --- a/mysql-test/t/query_cache_debug.test +++ b/mysql-test/t/query_cache_debug.test @@ -113,3 +113,148 @@ DROP TABLE t1,t2; SET GLOBAL concurrent_insert= DEFAULT; SET GLOBAL query_cache_size= DEFAULT; SET GLOBAL query_cache_type= DEFAULT; + + +--echo # +--echo # Bug43758 Query cache can lock up threads in 'freeing items' state +--echo # +FLUSH STATUS; +SET GLOBAL query_cache_type=DEMAND; +SET GLOBAL query_cache_size= 1024*768; +--disable_warnings +DROP TABLE IF EXISTS t1,t2,t3,t4,t5; +--enable_warnings +CREATE TABLE t1 (a VARCHAR(100)); +CREATE TABLE t2 (a VARCHAR(100)); +CREATE TABLE t3 (a VARCHAR(100)); +CREATE TABLE t4 (a VARCHAR(100)); +CREATE TABLE t5 (a VARCHAR(100)); + +INSERT INTO t1 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +INSERT INTO t2 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +INSERT INTO t3 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +INSERT INTO t4 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +INSERT INTO t5 VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); + +connect (thd2, localhost, root, ,test); +connect (thd3, localhost, root, ,test); +connect (thd1, localhost, root, ,test); + +connection thd1; +--echo =================================== Connection thd1 +--echo ** +--echo ** Load Query Cache with a result set and one table. +--echo ** +SELECT SQL_CACHE * FROM t1; +--echo ************************************************************************* +--echo ** We want to accomplish the following state: +--echo ** - Query cache status: TABLE_FLUSH_IN_PROGRESS +--echo ** - THD1: invalidate_table_internal (iterating query blocks) +--echo ** - THD2: query_cache_insert (cond_wait) +--echo ** - THD3: query_cache_insert (cond_wait) +--echo ** - No thread should be holding the structure_guard_mutex. +--echo ** +--echo ** First step is to place a DELETE-statement on the debug hook just +--echo ** before the mutex lock in invalidate_table_internal. +--echo ** This will allow new result sets to be written into the QC. +--echo ** +SET SESSION debug='+d,wait_in_query_cache_invalidate1'; +SET SESSION debug='+d,wait_in_query_cache_invalidate2'; +--send DELETE FROM t1 WHERE a like '%a%'; + +connection default; +--echo =================================== Connection default +--echo ** Assert that the expect process status is obtained. +LET $wait_condition= SELECT SQL_NO_CACHE COUNT(*)= 1 FROM information_schema.processlist WHERE state= 'wait_in_query_cache_invalidate1'; +--source include/wait_condition.inc +-- echo ** + +connection thd2; +--echo =================================== Connection thd2 +--echo ** On THD2: Insert a result into the cache. This attempt will be blocked +--echo ** because of a debug hook placed just before the mutex lock after which +--echo ** the first part of the result set is written. +SET SESSION debug='+d,wait_in_query_cache_insert'; +--send SELECT SQL_CACHE * FROM t2 UNION SELECT * FROM t3 + +connection thd3; +--echo =================================== Connection thd3 +--echo ** On THD3: Insert another result into the cache and block on the same +--echo ** debug hook. +SET SESSION debug='+d,wait_in_query_cache_insert'; +--send SELECT SQL_CACHE * FROM t4 UNION SELECT * FROM t5; + +connection default; +--echo =================================== Connection default +--echo ** Assert that the two SELECT-stmt threads to reach the hook. +LET $wait_condition= SELECT SQL_NO_CACHE COUNT(*)= 2 FROM information_schema.processlist WHERE state='wait_in_query_cache_insert'; +--source include/wait_condition.inc +--echo ** +--echo ** + +--echo ** Signal the DELETE thread, THD1, to continue. It will enter the mutex +--echo ** lock and set query cache status to TABLE_FLUSH_IN_PROGRESS and then +--echo ** unlock the mutex before stopping on the next debug hook. +SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate1' LIMIT 1 INTO @flush_thread_id; +KILL QUERY @flush_thread_id; +--echo ** Assert that we reach the next debug hook. +LET $wait_condition= SELECT SQL_NO_CACHE COUNT(*)= 1 FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate2'; +--source include/wait_condition.inc + +--echo ** +--echo ** Signal the remaining debug hooks blocking THD2 and THD3. +--echo ** The threads will grab the guard mutex enter the wait condition and +--echo ** and finally release the mutex. The threads will continue to wait +--echo ** until a broadcast signal reaches them causing both threads to +--echo ** come alive and check the condition. +SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_insert' ORDER BY id ASC LIMIT 1 INTO @thread_id; +KILL QUERY @thread_id; +SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_insert' ORDER BY id DESC LIMIT 1 INTO @thread_id; +KILL QUERY @thread_id; + +--echo ** +--echo ** Finally signal the DELETE statement on THD1 one last time. +--echo ** The stmt will complete the query cache invalidation and return +--echo ** cache status to NO_FLUSH_IN_PROGRESS. On the status change +--echo ** One signal will be sent to the thread group waiting for executing +--echo ** invalidations and a broadcast signal will be sent to the thread +--echo ** group holding result set writers. +SELECT SQL_NO_CACHE id FROM information_schema.processlist WHERE state='wait_in_query_cache_invalidate2' LIMIT 1 INTO @flush_thread_id; +KILL QUERY @flush_thread_id; + +--echo ** +--echo ************************************************************************* +--echo ** No tables should be locked +connection thd2; +--echo =================================== Connection thd2 +reap; +DELETE FROM t1; +DELETE FROM t2; +DELETE FROM t3; + +connection thd3; +--echo =================================== Connection thd3 +reap; +DELETE FROM t4; +DELETE FROM t5; + +connection thd1; +--echo =================================== Connection thd1 +reap; + +--echo ** Done. + +connection default; +disconnect thd1; +disconnect thd2; +disconnect thd3; +SET GLOBAL query_cache_size= 0; + +connection default; +--echo # Restore defaults +RESET QUERY CACHE; +FLUSH STATUS; +DROP TABLE t1,t2,t3,t4,t5; +SET GLOBAL query_cache_size= DEFAULT; +SET GLOBAL query_cache_type= DEFAULT; +exit; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 8981ddbe2e4..7d3785ecccc 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3726,6 +3726,20 @@ cr.f4 = cr2.f4 GROUP BY a.f3, cr.f4; drop table t1, t2, t3; + + +# +# Bug #40925: Equality propagation takes non indexed attribute +# + +CREATE TABLE t1 (a INT KEY, b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4); + +EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; +EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; + +DROP TABLE t1; + --echo End of 5.0 tests # @@ -3785,4 +3799,90 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a AND b=b) OR b > 20; EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND b=b AND a=a) OR b > 20; DROP TABLE t1; + +--echo # +--echo # Bug#45266: Uninitialized variable lead to an empty result. +--echo # +--disable_warnings +drop table if exists A,AA,B,BB; +CREATE TABLE `A` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `date_key` date NOT NULL, + `date_nokey` date NOT NULL, + `datetime_key` datetime NOT NULL, + `int_nokey` int(11) NOT NULL, + `time_key` time NOT NULL, + `time_nokey` time NOT NULL, + PRIMARY KEY (`pk`), + KEY `date_key` (`date_key`), + KEY `time_key` (`time_key`), + KEY `datetime_key` (`datetime_key`) +); + +CREATE TABLE `AA` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `int_nokey` int(11) NOT NULL, + `time_key` time NOT NULL, + KEY `time_key` (`time_key`), + PRIMARY KEY (`pk`) +); + +CREATE TABLE `B` ( + `date_nokey` date NOT NULL, + `date_key` date NOT NULL, + `time_key` time NOT NULL, + `datetime_nokey` datetime NOT NULL, + `varchar_key` varchar(1) NOT NULL, + KEY `date_key` (`date_key`), + KEY `time_key` (`time_key`), + KEY `varchar_key` (`varchar_key`) +); + +INSERT INTO `B` VALUES ('2003-07-28','2003-07-28','15:13:38','0000-00-00 00:00:00','f'),('0000-00-00','0000-00-00','00:05:48','2004-07-02 14:34:13','x'); + +CREATE TABLE `BB` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `int_nokey` int(11) NOT NULL, + `date_key` date NOT NULL, + `varchar_nokey` varchar(1) NOT NULL, + `date_nokey` date NOT NULL, + PRIMARY KEY (`pk`), + KEY `date_key` (`date_key`) +); + +INSERT INTO `BB` VALUES (10,8,'0000-00-00','i','0000-00-00'),(11,0,'2005-08-18','','2005-08-18'); +# Test #1 +SELECT table1 . `pk` AS field1 + FROM + (BB AS table1 INNER JOIN + (AA AS table2 STRAIGHT_JOIN A AS table3 + ON ( table3 . `date_key` = table2 . `pk` )) + ON ( table3 . `datetime_key` = table2 . `int_nokey` )) + WHERE ( table3 . `date_key` <= 4 AND table2 . `pk` = table1 . `varchar_nokey`) + GROUP BY field1 ; + +SELECT table3 .`date_key` field1 + FROM + B table1 LEFT JOIN B table3 JOIN + (BB table6 JOIN A table7 ON table6 .`varchar_nokey`) + ON table6 .`int_nokey` ON table6 .`date_key` + WHERE NOT ( table1 .`varchar_key` AND table7 .`pk`) GROUP BY field1; + +# Test #2 +SELECT table4 . `time_nokey` AS field1 FROM + (AA AS table1 CROSS JOIN + (AA AS table2 STRAIGHT_JOIN + (B AS table3 STRAIGHT_JOIN A AS table4 + ON ( table4 . `date_key` = table3 . `time_key` )) + ON ( table4 . `pk` = table3 . `date_nokey` )) + ON ( table4 . `time_key` = table3 . `datetime_nokey` )) + WHERE ( table4 . `time_key` < table1 . `time_key` AND + table1 . `int_nokey` != 'f') + GROUP BY field1 ORDER BY field1 , field1; + +SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON table5 .`date_nokey` ON table5 .`int_nokey` GROUP BY field2; +--enable_warnings + +drop table A,AA,B,BB; +--echo #end of test for bug#45266 --echo End of 5.1 tests diff --git a/mysql-test/t/shm.test b/mysql-test/t/shm.test index 380607d8ebf..88e96ae7b45 100644 --- a/mysql-test/t/shm.test +++ b/mysql-test/t/shm.test @@ -16,4 +16,23 @@ if (`SELECT '$shm' != 'ON'`){ # --exec $MYSQLADMIN --no-defaults --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --shared-memory-base-name=HeyMrBaseNameXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ping +# +# Bug #33899: Deadlock in mysql_real_query with shared memory connections +# + +let $name= query_get_value("SHOW GLOBAL VARIABLES LIKE 'shared_memory_base_name'", Value, 1); +let $stmt= `SELECT REPEAT('a', 2048)`; + +SET @max_allowed_packet= @@global.max_allowed_packet; +SET @net_buffer_length= @@global.net_buffer_length; + +SET GLOBAL max_allowed_packet= 1024; +SET GLOBAL net_buffer_length= 1024; + +--error 1 +--exec echo SELECT '$stmt'| $MYSQL --protocol=memory --shared-memory-base-name=$name 2>&1 + +SET GLOBAL max_allowed_packet= @max_allowed_packet; +SET GLOBAL net_buffer_length= @net_buffer_length; + --echo End of 5.0 tests. diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 8d7c6d75a34..66b960c938f 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -2435,3 +2435,16 @@ delimiter ;$$ # LOAD DATA INFILE '../../tmp/proc.txt' INTO TABLE mysql.proc; remove_file $MYSQLTEST_VARDIR/tmp/proc.txt; + +# +# Bug #38159: Function parsing problem generates misleading error message +# + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +--error ER_FUNC_INEXISTENT_NAME_COLLISION +SELECT MAX (a) FROM t1 WHERE b = 999999; +SELECT AVG (a) FROM t1 WHERE b = 999999; +--error ER_SP_DOES_NOT_EXIST +SELECT non_existent (a) FROM t1 WHERE b = 999999; +DROP TABLE t1; diff --git a/mysql-test/t/sp-fib.test b/mysql-test/t/sp-fib.test new file mode 100644 index 00000000000..24a51b99c2d --- /dev/null +++ b/mysql-test/t/sp-fib.test @@ -0,0 +1,54 @@ +# Fibonacci, for recursion test. (Yet Another Numerical series :) +# Split from main.sp due to problems reported in Bug#15866 + +--disable_warnings +drop table if exists t3; +--enable_warnings +create table t3 ( f bigint unsigned not null ); + +# We deliberately do it the awkward way, fetching the last two +# values from the table, in order to exercise various statements +# and table accesses at each turn. +--disable_warnings +drop procedure if exists fib; +--enable_warnings + +# Now for multiple statements... +delimiter |; + +create procedure fib(n int unsigned) +begin + if n > 1 then + begin + declare x, y bigint unsigned; + declare c cursor for select f from t3 order by f desc limit 2; + open c; + fetch c into y; + fetch c into x; + insert into t3 values (x+y); + call fib(n-1); + ## Close the cursor AFTER the recursion to ensure that the stack + ## frame is somewhat intact. + close c; + end; + end if; +end| + +# Enable recursion +set @@max_sp_recursion_depth= 20| + +insert into t3 values (0), (1)| + +# The small number of recursion levels is intentional. +# We need to avoid +# Bug#15866 main.sp fails (thread stack limit +# insufficient for recursive call "fib(20)") +# which affects some platforms. +call fib(4)| + +select * from t3 order by f asc| + +drop table t3| +drop procedure fib| +set @@max_sp_recursion_depth= 0| + diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index d57fe777953..5eeac457958 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -1272,7 +1272,7 @@ select *, f8() from v1| # Let us test what will happen if function is missing drop function f1| ---error 1356 +--error ER_VIEW_INVALID select * from v1| # And what will happen if we have recursion which involves @@ -1311,9 +1311,9 @@ select f0()| select * from v0| select *, f0() from v0, (select 123) as d1| # But these should not ! ---error 1100 +--error ER_TABLE_NOT_LOCKED select id, f3() from t1| ---error 1100 +--error ER_TABLE_NOT_LOCKED select f4()| unlock tables| @@ -1323,9 +1323,9 @@ lock tables v2 read, mysql.proc read| select * from v2| select * from v1| # These should not work as we have too little instances of tables locked ---error 1100 +--error ER_TABLE_NOT_LOCKED select * from v1, t1| ---error 1100 +--error ER_TABLE_NOT_LOCKED select f4()| unlock tables| @@ -1561,61 +1561,6 @@ drop procedure ip| show procedure status where name like '%p%' and db='test'| -# Fibonacci, for recursion test. (Yet Another Numerical series :) -# ---disable_warnings -drop table if exists t3| ---enable_warnings -create table t3 ( f bigint unsigned not null )| - -# We deliberately do it the awkward way, fetching the last two -# values from the table, in order to exercise various statements -# and table accesses at each turn. ---disable_warnings -drop procedure if exists fib| ---enable_warnings -create procedure fib(n int unsigned) -begin - if n > 1 then - begin - declare x, y bigint unsigned; - declare c cursor for select f from t3 order by f desc limit 2; - - open c; - fetch c into y; - fetch c into x; - close c; - insert into t3 values (x+y); - call fib(n-1); - end; - end if; -end| - -# Enable recursion -set @@max_sp_recursion_depth= 20| - -# Minimum test: recursion of 3 levels - -insert into t3 values (0), (1)| - -call fib(3)| - -select * from t3 order by f asc| - -truncate table t3| - -# The original test, 20 levels, ran into memory limits on some machines -# and builds. Try 10 instead... - -insert into t3 values (0), (1)| - -call fib(10)| - -select * from t3 order by f asc| -drop table t3| -drop procedure fib| -set @@max_sp_recursion_depth= 0| - # # Comment & suid # @@ -5255,19 +5200,31 @@ drop procedure bug5967| # # Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server" # +--let $backupdir = $MYSQLTEST_VARDIR/tmp/ +--error 0,1 +--remove_file $backupdir/t1.frm +--error 0,1 +--remove_file $backupdir/t1.MYD + --disable_warnings drop procedure if exists bug13012| # Disable warnings also for BACKUP/RESTORE: they are deprecated. -create procedure bug13012() -BEGIN - REPAIR TABLE t1; - BACKUP TABLE t1 to '../../tmp'; - DROP TABLE t1; - RESTORE TABLE t1 FROM '../../tmp'; -END| +--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> +eval create procedure bug13012() + BEGIN + REPAIR TABLE t1; + BACKUP TABLE t1 to '$backupdir'; + DROP TABLE t1; + RESTORE TABLE t1 FROM '$backupdir'; + END| call bug13012()| + --enable_warnings +--remove_file $backupdir/t1.frm +--remove_file $backupdir/t1.MYD + drop procedure bug13012| + create view v1 as select * from t1| create procedure bug13012() BEGIN @@ -8258,6 +8215,33 @@ select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mo select name from mysql.proc where name = 'p' and sql_mode = @full_mode; drop procedure p; +# +# Bug#43962 "Packets out of order" calling a SHOW TABLE STATUS +# +DELIMITER //; +CREATE DEFINER = 'root'@'localhost' PROCEDURE p1() +NOT DETERMINISTIC +CONTAINS SQL +SQL SECURITY DEFINER +COMMENT '' +BEGIN + SHOW TABLE STATUS like 't1'; +END;// +DELIMITER ;// + + +CREATE TABLE t1 (f1 INT); +--disable_result_log +let $tab_count= 4; +while ($tab_count) +{ + EVAL CALL p1(); + dec $tab_count ; +} +--enable_result_log +DROP PROCEDURE p1; +DROP TABLE t1; + --echo # ------------------------------------------------------------------ --echo # -- End of 5.1 tests --echo # ------------------------------------------------------------------ diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index f540126c405..f593e184ad2 100644 --- a/mysql-test/t/sp_notembedded.test +++ b/mysql-test/t/sp_notembedded.test @@ -346,6 +346,32 @@ drop table t1; set session low_priority_updates=default; # +# Bug#44798 MySQL engine crashes when creating stored procedures with execute_priv=N +# +INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, +Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, +Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, +Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, +Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, +Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, +max_updates, max_connections, max_user_connections) +VALUES('%', 'mysqltest_1', password(''), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', +'N', 'N', 'N', 'Y', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y', 'Y', 'N', '', +'', '', '', '0', '0', '0', '0'); +FLUSH PRIVILEGES; + +connect (con1, localhost, mysqltest_1,,); +connection con1; +CREATE PROCEDURE p1(i INT) BEGIN END; +disconnect con1; +connection default; +DROP PROCEDURE p1; + +DELETE FROM mysql.user WHERE User='mysqltest_1'; +FLUSH PRIVILEGES; + + +# # Restore global concurrent_insert value. Keep in the end of the test file. # @@ -354,3 +380,39 @@ set @@global.concurrent_insert= @old_concurrent_insert; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc + +--echo # +--echo # Bug#44521 Prepared Statement: CALL p() - crashes: `! thd->main_da.is_sent' failed et.al. +--echo # +SELECT GET_LOCK('Bug44521', 0); +--connect (con1,localhost,root,,) +--echo ** Connection con1 +delimiter $; +CREATE PROCEDURE p() +BEGIN + SELECT 1; + SELECT GET_LOCK('Bug44521', 100); + SELECT 2; +END$ +delimiter ;$ +--send CALL p(); +--connection default +--echo ** Default connection +let $wait_condition= + SELECT count(*) = 1 FROM information_schema.processlist + WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)"; +--source include/wait_condition.inc +let $conid = + `SELECT id FROM information_schema.processlist + WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)"`; +dirty_close con1; +SELECT RELEASE_LOCK('Bug44521'); +let $wait_condition= + SELECT count(*) = 0 FROM information_schema.processlist + WHERE id = $conid; +--source include/wait_condition.inc +DROP PROCEDURE p; + +--echo # ------------------------------------------------------------------ +--echo # -- End of 5.1 tests +--echo # ------------------------------------------------------------------ diff --git a/mysql-test/t/sp_trans_log.test b/mysql-test/t/sp_trans_log.test index 447ec19b132..2f2b84a9bef 100644 --- a/mysql-test/t/sp_trans_log.test +++ b/mysql-test/t/sp_trans_log.test @@ -8,17 +8,17 @@ delimiter |; # -# Bug #13270 INSERT,UPDATE,etc that calls func with side-effect does not binlog -# Bug #23333 stored function + non-transac table + transac table = -# breaks stmt-based binlog -# Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() +# Bug#13270 INSERT,UPDATE,etc that calls func with side-effect does not binlog +# Bug#23333 stored function + non-transac table + transac table = +# breaks stmt-based binlog +# Bug#27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() # --disable_warnings drop function if exists bug23333| drop table if exists t1,t2| --enable_warnings - CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM| - CREATE TABLE t2 (a int NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB| +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM| +CREATE TABLE t2 (a int NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB| insert into t2 values (1,1)| diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index acc9cc7979e..4a9f34443cb 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -308,3 +308,39 @@ flush privileges; --connection default drop user mysqltest_32753@localhost; + +# +# Bug#45100: Incomplete DROP USER in case of SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH' +# + +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +--enable_warnings + +# Generate some prerequisites +CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD'; +CREATE USER 'user_no_PCTFL'@'localhost' identified by 'PWD'; + +CREATE TABLE t1 (f1 BIGINT); +CREATE TABLE t2 (f1 CHAR(3) NOT NULL, f2 CHAR(20)); + +# Grant privilege on a TABLE +GRANT ALL ON t1 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; +# Grant privilege on some COLUMN of a table +GRANT SELECT(f1) ON t2 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; + +SET @OLD_SQL_MODE = @@SESSION.SQL_MODE; +SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'; +DROP USER 'user_PCTFL'@'localhost'; +SET SESSION SQL_MODE = @OLD_SQL_MODE; +DROP USER 'user_no_PCTFL'@'localhost'; + +FLUSH PRIVILEGES; + +SELECT * FROM mysql.db WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; +SELECT * FROM mysql.tables_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; +SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; + +# Cleanup +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 5842f59af5c..5da210f5a69 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -12,6 +12,12 @@ set @old_concurrent_insert= @@global.concurrent_insert; set @@global.concurrent_insert= 0; +# Disable logging to table, since this will also cause table locking and unlocking, which will +# show up in SHOW STATUS and may cause sporadic failures + +SET @old_log_output = @@global.log_output; +SET GLOBAL LOG_OUTPUT = 'FILE'; + # PS causes different statistics --disable_ps_protocol @@ -350,6 +356,7 @@ DROP FUNCTION f1; # Restore global concurrent_insert value. Keep in the end of the test file. --connection default set @@global.concurrent_insert= @old_concurrent_insert; +SET GLOBAL log_output = @old_log_output; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index a04152836be..a9edc831fdc 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3315,6 +3315,27 @@ EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); DROP TABLE t1; +--echo # +--echo # Bug#45061: Incorrectly market field caused wrong result. +--echo # +CREATE TABLE `C` ( + `int_nokey` int(11) NOT NULL, + `int_key` int(11) NOT NULL, + KEY `int_key` (`int_key`) +); + +INSERT INTO `C` VALUES (9,9), (0,0), (8,6), (3,6), (7,6), (0,4), +(1,7), (9,4), (0,8), (9,4), (0,7), (5,5), (0,0), (8,5), (8,7), +(5,2), (1,8), (7,0), (0,9), (9,5); + +--disable_warnings +SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`); +EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`); +--enable_warnings + +DROP TABLE C; +--echo # End of test for bug#45061. + --echo End of 5.0 tests. # diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index bf461f83a20..7a2a9f328ef 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -669,6 +669,25 @@ SELECT ROW(1,2) = (SELECT NULL, 1), ROW(1,2) IN (SELECT NULL, 1); SELECT ROW(1,2) = (SELECT 1, 1), ROW(1,2) IN (SELECT 1, 1); SELECT ROW(1,2) = (SELECT 1, 2), ROW(1,2) IN (SELECT 1, 2); +# +# Bug #37362 Crash in do_field_eq +# +CREATE TABLE t1 (a INT, b INT, c INT); +INSERT INTO t1 VALUES (1,1,1), (1,1,1); + +--error 1054 +EXPLAIN EXTENDED + SELECT c FROM + ( SELECT + (SELECT COUNT(a) FROM + (SELECT COUNT(b) FROM t1) AS x GROUP BY c + ) FROM t1 GROUP BY b + ) AS y; +SHOW WARNINGS; + +DROP TABLE t1; + + --echo End of 5.0 tests # diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 6974a4cc5da..1e55f9d5993 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2218,6 +2218,39 @@ select * from t1; select * from t2; drop table t1; drop temporary table t2; + +--echo #------------------------------------------------------------------------ +--echo # Bug#39953 Triggers are not working properly with multi table updates +--echo #------------------------------------------------------------------------ + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TRIGGER IF EXISTS t_insert; +DROP TABLE IF EXISTS t2; +--enable_warnings + +CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a)); +INSERT INTO t1 (a) VALUES (2),(5); +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +DELIMITER |; +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END | +DELIMITER ;| +INSERT INTO t2 (a,b) VALUES (1,2); + +DROP TRIGGER t_insert; + +DELIMITER |; +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END | +DELIMITER ;| +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO t2 (a,b) VALUES (3,5); + +DROP TABLE t1; +DROP TRIGGER t_insert; +DROP TABLE t2; + --echo End of 5.0 tests # @@ -2337,4 +2370,30 @@ drop trigger trg1; drop trigger trg2; drop table t1, t2; +# +# Bug#44653: Server crash noticed when executing random queries with partitions. +# +CREATE TABLE t1 ( a INT, b INT ); +CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY, b INT ); + +INSERT INTO t1 (a) VALUES (1); + +delimiter //; +CREATE TRIGGER tr1 +BEFORE INSERT ON t2 +FOR EACH ROW +BEGIN + UPDATE a_nonextisting_table SET a = 1; +END// +delimiter ;// + +--disable_abort_on_error +CREATE TABLE IF NOT EXISTS t2 ( a INT, b INT ) SELECT a, b FROM t1; +--enable_abort_on_error + +# Caused failed assertion +SELECT * FROM t2; + +DROP TABLE t1, t2; + --echo End of 5.1 tests. diff --git a/mysql-test/t/trigger_notembedded.test b/mysql-test/t/trigger_notembedded.test index 9588ec6e3ed..7a7e6c6bc85 100644 --- a/mysql-test/t/trigger_notembedded.test +++ b/mysql-test/t/trigger_notembedded.test @@ -909,4 +909,27 @@ select * from t1; drop table t1; disconnect flush; +# +# Bug#45412 SHOW CREATE TRIGGER does not require privileges to disclose trigger data +# +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a char(30)) ENGINE=MEMORY; +CREATE TRIGGER db1.trg AFTER INSERT ON db1.t1 FOR EACH ROW + INSERT INTO db1.t1 VALUES('Some very sensitive data goes here'); + +CREATE USER 'no_rights'@'localhost'; +REVOKE ALL ON *.* FROM 'no_rights'@'localhost'; +FLUSH PRIVILEGES; + +connect (con1,localhost,no_rights,,); +SELECT trigger_name FROM INFORMATION_SCHEMA.TRIGGERS + WHERE trigger_schema = 'db1'; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +SHOW CREATE TRIGGER db1.trg; + +connection default; +disconnect con1; +DROP USER 'no_rights'@'localhost'; +DROP DATABASE db1; + --echo End of 5.1 tests. diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 4cf9ea63dad..cd3c3f81510 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1257,3 +1257,32 @@ select cast(-3.4 as decimal(2,1)); select cast(99.6 as decimal(2,0)); select cast(-13.4 as decimal(2,1)); select cast(98.6 as decimal(2,0)); + +--echo # +--echo # Bug #45262: Bad effects with CREATE TABLE and DECIMAL +--echo # + +CREATE TABLE t1 SELECT .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +DESCRIBE t1; +SELECT my_col FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT 1 + .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +DESCRIBE t1; +SELECT my_col FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT 1 * .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +DESCRIBE t1; +SELECT my_col FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT 1 / .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +DESCRIBE t1; +SELECT my_col FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS my_col; +DESCRIBE t1; +SELECT my_col FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index 5fc763be7fe..5bb521601e5 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -77,3 +77,16 @@ insert into t1 values('2007-07-02', 1); insert into t1 values('2007-07-02', 2); SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2; drop table t1; + + +--echo # +--echo # Bug #44792: valgrind warning when casting from time to time +--echo # + +CREATE TABLE t1 (c TIME); +INSERT INTO t1 VALUES ('0:00:00'); +SELECT CAST(c AS TIME) FROM t1; +DROP TABLE t1; + + +--echo End of 5.0 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index ee4c174d76b..ec169838d59 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1074,4 +1074,31 @@ SELECT * FROM (SELECT * FROM (SELECT NULL)a) b UNION SELECT a FROM t1; DESC t6; DROP TABLE t1, t2, t3, t4, t5, t6; + +# +# Bug #43432: Union on floats does unnecessary rounding +# + +CREATE TABLE t1 (f FLOAT(9,6)); +CREATE TABLE t2 AS SELECT f FROM t1 UNION SELECT f FROM t1; +SHOW FIELDS FROM t2; +DROP TABLE t1, t2; + +CREATE TABLE t1(d DOUBLE(9,6)); +CREATE TABLE t2 AS SELECT d FROM t1 UNION SELECT d FROM t1; +SHOW FIELDS FROM t2; +DROP TABLE t1, t2; + +# +# Bug#43612 crash with explain extended, union, order by +# +CREATE TABLE t1(a INT); +EXPLAIN EXTENDED +SELECT a FROM t1 +UNION +SELECT a FROM t1 +ORDER BY a; +DROP TABLE t1; + + --echo End of 5.0 tests diff --git a/mysql-test/t/upgrade.test b/mysql-test/t/upgrade.test index 437b0f47cc0..d571a2efc7c 100644 --- a/mysql-test/t/upgrade.test +++ b/mysql-test/t/upgrade.test @@ -48,6 +48,20 @@ select * from `txu#p#p1`; drop table `txu@0023p@0023p1`; drop table `txu#p#p1`; +--echo # +--echo # Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1 +--echo # +--echo # copy table created using mysql4.0 into the data dir +let $MYSQLD_DATADIR= `SELECT @@datadir`; +copy_file std_data/bug37631.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file std_data/bug37631.MYD $MYSQLD_DATADIR/test/t1.MYD; +copy_file std_data/bug37631.MYI $MYSQLD_DATADIR/test/t1.MYI; +--echo # check the table created using mysql 4.0 +CHECK TABLE t1; +--echo # query the table created using mysql 4.0 +SELECT * FROM t1; +DROP TABLE t1; + # # Check if old tables work # @@ -89,3 +103,35 @@ show create table `a-b-c`.`t1`; drop database `a-b-c`; drop database `tabc`; +# +# Bug#43385 Cannot ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME when Views exist +# +let $MYSQLD_DATADIR= `select @@datadir`; +--mkdir $MYSQLD_DATADIR/a-b-c +use `#mysql50#a-b-c`; +create table t1(f1 char(10)); + +--write_file $MYSQLD_DATADIR/a-b-c/v1.frm +TYPE=VIEW +query=select `a`.`f1` AS `f1` from `a-b-c`.`t1` `a` join `information_schema`.`tables` `b` where (convert(`a`.`f1` using utf8) = `b`.`TABLE_NAME`) +md5=068271f1c657fe115e497856ca0fa493 +updatable=0 +algorithm=0 +definer_user=root +definer_host=localhost +suid=2 +with_check_option=0 +timestamp=2009-04-10 11:53:37 +create-version=1 +source=select f1 from `a-b-c`.t1 a, information_schema.tables b\nwhere a.f1 = b.table_name +EOF + +show databases like '%a-b-c%'; +ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; +show databases like '%a-b-c%'; +show create view `a-b-c`.v1; +--disable_ps_protocol +select * from `a-b-c`.v1; +--enable_ps_protocol +drop database `a-b-c`; +use test; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index fd4e538ea6c..c0740458a88 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -286,6 +286,18 @@ select @lastid != id, @lastid, @lastid := id from t1; drop table t1; # +# Bug#42009: SELECT into variable gives different results to direct SELECT +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (0, 0), (2, 1), (2, 3), (1, 1), (30, 20); +SELECT a, b INTO @a, @b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b; +SELECT @a, @b; +SELECT a, b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b; +DROP TABLE t1; + +--echo End of 5.0 tests + +# # Bug#42188: crash and/or memory corruption with user variables in trigger # diff --git a/mysql-test/t/variables-big.test b/mysql-test/t/variables-big.test index efefa055eab..b82fe784abd 100644 --- a/mysql-test/t/variables-big.test +++ b/mysql-test/t/variables-big.test @@ -7,6 +7,33 @@ # # Bug #27322 failure to allocate transaction_prealloc_size causes crash # +# +# Manual (6.0): +# Platform Bit Size Range Default +# 32 1024-4294967295 (4 Gi - 1) 4096 +# 64 1024-18446744073709547520 4096 +# +# Observation(mleich): +# 1. - Linux 64 Bit, MySQL 64 Bit, 4 GiB RAM, 8 GiB swap +# - SET SESSION transaction_prealloc_size=1099511627776; +# SHOW PROCESSLIST; +# Id User ... Info +# <Id> root ... SHOW PROCESSLIST +# SELECT @@session.transaction_prealloc_size; +# @@session.transaction_prealloc_size +# 1099511627776 +# very short runtime in 5.0 +# excessive resource consumption + long runtime in 5.1 and 6.0 +# 2. - Win in VM, slightly older version of this test, MySQL 5.0 +# - testcase timeout after 900s +# analyze-timeout-mysqld.1.err : +# Id User ... Time Info +# 83 root ... 542 set session transaction_prealloc_size=1024*1024*1024*2 +# 84 root ... 1 SHOW PROCESSLIST +# +# There is a significant probablitity that this tests fails with testcase +# timeout if the testing box is not powerful enough. +# set @pid_temp = (select ID from information_schema.processlist); set session transaction_prealloc_size=1024*1024*1024*1; diff --git a/mysql-test/t/variables-notembedded-master.opt b/mysql-test/t/variables-notembedded-master.opt index a684e591d10..8a173a043ac 100644 --- a/mysql-test/t/variables-notembedded-master.opt +++ b/mysql-test/t/variables-notembedded-master.opt @@ -1 +1 @@ ---loose-slave-skip-errors=3,100,137,643,1752 +--loose-slave-skip-errors=3,100,137,0,643,1752 diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index db0657d9bde..c8ee633dab3 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -1203,4 +1203,29 @@ SET GLOBAL server_id = -1; SELECT @@GLOBAL.server_id; SET GLOBAL server_id = @old_server_id; +# +# Bug #42778: delete order by null global variable causes +# assertion .\filesort.cc, line 797 +# + +SELECT @@GLOBAL.INIT_FILE, @@GLOBAL.INIT_FILE IS NULL; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (); +SET @bug42778= @@sql_safe_updates; +SET @@sql_safe_updates= 0; +DELETE FROM t1 ORDER BY (@@GLOBAL.INIT_FILE) ASC LIMIT 10; +SET @@sql_safe_updates= @bug42778; + +DROP TABLE t1; + +--echo # +--echo # BUG#10206 - InnoDB: Transaction requiring Max_BinLog_Cache_size > 4GB always rollsback +--echo # + +SET @old_max_binlog_cache_size = @@GLOBAL.max_binlog_cache_size; +--echo # Set the max_binlog_cache_size to size more than 4GB. +SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024; +SELECT @@GLOBAL.max_binlog_cache_size; +SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size; --echo End of 5.1 tests diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index c9d01266e9e..7bec02e6fb6 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3681,6 +3681,61 @@ DROP VIEW v1; DROP TABLE t1; --echo # ----------------------------------------------------------------- +--echo # -- Bug#40825: Error 1356 while selecting from a view +--echo # -- with a "HAVING" clause though query works +--echo # ----------------------------------------------------------------- +--echo + +CREATE TABLE t1 (c INT); + +--echo + +CREATE VIEW v1 (view_column) AS SELECT c AS alias FROM t1 HAVING alias; +SHOW CREATE VIEW v1; +SELECT * FROM v1; + +--echo + +DROP VIEW v1; +DROP TABLE t1; + +--echo +--echo # -- End of test case for Bug#40825 +--echo + +--echo # +--echo # Bug #45806 crash when replacing into a view with a join! +--echo # +CREATE TABLE t1(a INT UNIQUE); +CREATE VIEW v1 AS SELECT t1.a FROM t1, t1 AS a; +INSERT INTO t1 VALUES (1), (2); + +REPLACE INTO v1(a) SELECT 1 FROM t1,t1 AS c; +SELECT * FROM v1; +REPLACE INTO v1(a) SELECT 3 FROM t1,t1 AS c; +SELECT * FROM v1; +DELETE FROM t1 WHERE a=3; +INSERT INTO v1(a) SELECT 1 FROM t1,t1 AS c +ON DUPLICATE KEY UPDATE `v1`.`a`= 1; +SELECT * FROM v1; + +CREATE VIEW v2 AS SELECT t1.a FROM t1, v1 AS a; + +REPLACE INTO v2(a) SELECT 1 FROM t1,t1 AS c; +SELECT * FROM v2; +REPLACE INTO v2(a) SELECT 3 FROM t1,t1 AS c; +SELECT * FROM v2; +INSERT INTO v2(a) SELECT 1 FROM t1,t1 AS c +ON DUPLICATE KEY UPDATE `v2`.`a`= 1; +SELECT * FROM v2; + +DROP VIEW v1; +DROP VIEW v2; +DROP TABLE t1; + +--echo # -- End of test case for Bug#45806 + +--echo # ----------------------------------------------------------------- --echo # -- End of 5.0 tests. --echo # ----------------------------------------------------------------- @@ -3836,6 +3891,17 @@ drop procedure p; ########################################################################### + +--echo # +--echo # Bug #44860: ALTER TABLE on view crashes server +--echo # +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT a FROM t1; +ALTER TABLE v1; +DROP VIEW v1; +DROP TABLE t1; + + --echo # ----------------------------------------------------------------- --echo # -- End of 5.1 tests. --echo # ----------------------------------------------------------------- diff --git a/mysql-test/t/xa.test b/mysql-test/t/xa.test index 04ecf518577..7b1c6a268d5 100644 --- a/mysql-test/t/xa.test +++ b/mysql-test/t/xa.test @@ -124,6 +124,31 @@ drop table t1; --echo End of 5.0 tests +# +# Bug#44672: Assertion failed: thd->transaction.xid_state.xid.is_null() +# + +xa start 'a'; +xa end 'a'; +xa rollback 'a'; +xa start 'a'; +xa end 'a'; +xa rollback 'a'; + +# +# Bug#45548: XA transaction without access to InnoDB tables crashes the server +# + +xa start 'a'; +xa end 'a'; +xa prepare 'a'; +xa commit 'a'; + +xa start 'a'; +xa end 'a'; +xa prepare 'a'; +xa commit 'a'; + # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/xml.test b/mysql-test/t/xml.test index 74bce8dc962..6e7d38cdfca 100644 --- a/mysql-test/t/xml.test +++ b/mysql-test/t/xml.test @@ -590,4 +590,31 @@ select extractvalue('<a></a>','"b"/a'); --error ER_UNKNOWN_ERROR select extractvalue('<a></a>','(1)/a'); +# +# Bug#43183 ExctractValue() brings result list in missorder +# +CREATE TABLE IF NOT EXISTS t1 ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + xml text, + PRIMARY KEY (id) +) ENGINE=MyISAM; + +INSERT INTO t1 (id, xml) VALUES +(15, '<?xml version="1.0"?><bla name="blubb"></bla>'), +(14, '<xml version="kaputt">'); + + +SELECT +extractvalue( xml, '/bla/@name' ), +extractvalue( xml, '/bla/@name' ) +FROM t1 ORDER BY t1.id; + + +SELECT +UpdateXML(xml, '/bla/@name', 'test'), +UpdateXML(xml, '/bla/@name', 'test') +FROM t1 ORDER BY t1.id; + +DROP TABLE t1; + --echo End of 5.1 tests |