diff options
Diffstat (limited to 'mysql-test/t')
41 files changed, 1292 insertions, 71 deletions
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/count_distinct3.test b/mysql-test/t/count_distinct3.test index 2f7cf7e5260..ad2bbee95a3 100644 --- a/mysql-test/t/count_distinct3.test +++ b/mysql-test/t/count_distinct3.test @@ -3,6 +3,8 @@ # mysql-4.1 # +--source include/big_test.inc + --disable_warnings DROP TABLE IF EXISTS t1, t2; --enable_warnings diff --git a/mysql-test/t/ctype_cp932_binlog_stm.test b/mysql-test/t/ctype_cp932_binlog_stm.test index 9e9716e5ddb..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,8 +31,9 @@ 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; 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/derived.test b/mysql-test/t/derived.test index bf5cd4aa8d8..d28c19bbd18 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -279,4 +279,26 @@ disconnect con1; 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 af4eb44b464..5436b7166f4 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -12,4 +12,5 @@ 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" query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically -init_connect : Bug#44920 2009-05-18 pcrews MTR2 is not processing master.opt input properly on Windows +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/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_math.test b/mysql-test/t/func_math.test index f9369943d6c..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 # diff --git a/mysql-test/t/func_set.test b/mysql-test/t/func_set.test index 5f37cd2a13e..294efa8caf1 100644 --- a/mysql-test/t/func_set.test +++ b/mysql-test/t/func_set.test @@ -84,3 +84,16 @@ 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/gis.test b/mysql-test/t/gis.test index 0dae4509518..4a60e777cc7 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -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/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.test b/mysql-test/t/innodb_bug42101-nonzero.test index c691a234c51..685fdf20489 100644 --- a/mysql-test/t/innodb_bug42101-nonzero.test +++ b/mysql-test/t/innodb_bug42101-nonzero.test @@ -12,6 +12,8 @@ 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; diff --git a/mysql-test/t/innodb_bug42101.test b/mysql-test/t/innodb_bug42101.test index 13d531ecde7..b6536490d48 100644 --- a/mysql-test/t/innodb_bug42101.test +++ b/mysql-test/t/innodb_bug42101.test @@ -15,3 +15,5 @@ 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 1b2b861dffb..c643465b2f3 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -359,4 +359,106 @@ 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/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/merge.test b/mysql-test/t/merge.test index 5315c91daa6..8760876b7ee 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -1535,4 +1535,24 @@ 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 End of 5.1 tests diff --git a/mysql-test/t/multi_update2.test b/mysql-test/t/multi_update2.test index 47f9bc7bad7..a04518f4964 100644 --- a/mysql-test/t/multi_update2.test +++ b/mysql-test/t/multi_update2.test @@ -2,14 +2,46 @@ # Test of update statement that uses many tables. # ---disable_warnings -DROP TABLE IF EXISTS t1,t2; ---enable_warnings +# +# 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. +# + +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/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 ba5cc243c01..2bb9a02e9d7 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -349,6 +349,14 @@ remove_file $MYSQLTEST_VARDIR/tmp/bug31060.sql; --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 # diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 4edf887a6b7..ec96124e14b 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1395,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 # @@ -1463,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 @@ -1698,9 +1702,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"); @@ -1738,8 +1739,6 @@ drop table t2; drop table words2; -} - --echo # --echo # Bug#16853 mysqldump doesn't show events --echo # @@ -1955,6 +1954,59 @@ 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 # End of 5.1 tests --echo # diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index ce89609de39..8b4af201af2 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1935,7 +1935,58 @@ INSERT INTO t1 VALUES (10), (100), (200), (300), (400); EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a>=200; DROP TABLE t1; ---echo End of 5.1 tests +# +# 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 +; + +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 End of 5.1 tests SET @@global.general_log= @old_general_log; diff --git a/mysql-test/t/query_cache_debug.test b/mysql-test/t/query_cache_debug.test index 8cf5e9d4b16..d30cd458e99 100644 --- a/mysql-test/t/query_cache_debug.test +++ b/mysql-test/t/query_cache_debug.test @@ -112,3 +112,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 715bdf0e667..7d3785ecccc 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3799,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/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 fdf6ed8f382..5eeac457958 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -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 # diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index f540126c405..ecb37c1299c 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. # 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/trigger.test b/mysql-test/t/trigger.test index 9a5556c518d..1e55f9d5993 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2370,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/variables.test b/mysql-test/t/variables.test index c568b79185d..1580d7f36d7 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -1217,4 +1217,13 @@ 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 a788b5ab41e..7bec02e6fb6 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3703,6 +3703,38 @@ DROP TABLE t1; --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 # ----------------------------------------------------------------- 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 |