diff options
Diffstat (limited to 'mysql-test/t')
46 files changed, 1701 insertions, 158 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 57e2b7e1a5b..d0114a94dfd 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1100,4 +1100,31 @@ ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST; DROP TABLE t1; +--echo # +--echo # Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION +--echo # FIRST CAN CAUSE DATA TO BE CORRUPTED". +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +--echo # Use MyISAM engine as the fact that InnoDB doesn't support +--echo # in-place ALTER TABLE in cases when columns are being renamed +--echo # hides some bugs. +create table t1 (i int, j int) engine=myisam; +insert into t1 value (1, 2); +--echo # First, test for original problem described in the bug report. +select * from t1; +--echo # Change of column order by the below ALTER TABLE statement should +--echo # affect both column names and column contents. +alter table t1 modify column j int first; +select * from t1; +--echo # Now test for similar problem with the same root. +--echo # The below ALTER TABLE should change not only the name but +--echo # also the value for the last column of the table. +alter table t1 drop column i, add column k int default 0; +select * from t1; +--echo # Clean-up. +drop table t1; + + --echo End of 5.1 tests diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index 5b819620e29..9ecbee1ac9f 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1687,7 +1687,6 @@ SELECT * FROM t1; REPAIR TABLE t1 EXTENDED; SELECT * FROM t1; DROP TABLE t1; - # # Bug #47012 archive tables are not upgradeable, and server crashes on # any access @@ -1700,3 +1699,17 @@ select * from t1; repair table t1; select sum(length(a)),sum(b) from t1; drop table t1; +--echo # +--echo # BUG#57162 - valgrind errors, random data when returning +--echo # ordered data from archive tables +--echo # +SET sort_buffer_size=32804; +CREATE TABLE t1(a INT, b CHAR(255), c CHAR(255), d CHAR(255), + e CHAR(255), f INT) ENGINE=ARCHIVE DEFAULT CHARSET utf8; +INSERT INTO t1 VALUES(-1,'b','c','d','e',1); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT t1.* FROM t1,t1 t2,t1 t3,t1 t4,t1 t5,t1 t6; +SELECT * FROM t1 ORDER BY f LIMIT 1; +DROP TABLE t1; +SET sort_buffer_size=DEFAULT; diff --git a/mysql-test/t/archive_debug.test b/mysql-test/t/archive_debug.test new file mode 100644 index 00000000000..9cece254140 --- /dev/null +++ b/mysql-test/t/archive_debug.test @@ -0,0 +1,13 @@ +--source include/have_archive.inc +--source include/have_debug.inc + +--echo # +--echo # BUG#12402794 - 60976: CRASH, VALGRIND WARNING AND MEMORY LEAK +--echo # WITH PARTITIONED ARCHIVE TABLES +--echo # +CREATE TABLE t1(a INT) ENGINE=ARCHIVE; +INSERT INTO t1 VALUES(1); +SET SESSION debug='d,simulate_archive_open_failure'; +CHECK TABLE t1; +SET SESSION debug=DEFAULT; +DROP TABLE t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 8e60d548c2f..426d7c7fdf2 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -282,5 +282,19 @@ SELECT 1 FROM ) AS s LIMIT 1; DROP TABLE t1; +--echo # +--echo # Bug #11765023: 57934: DOS POSSIBLE SINCE BINARY CASTING +--echo # DOESN'T ADHERE TO MAX_ALLOWED_PACKET + +SET @@GLOBAL.max_allowed_packet=2048; +# reconnect to make the new max packet size take effect +--connect (newconn, localhost, root,,) + +SELECT CONVERT('a', BINARY(2049)); +SELECT CONVERT('a', CHAR(2049)); + +connection default; +disconnect newconn; +SET @@GLOBAL.max_allowed_packet=default; --echo End of 5.1 tests diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index c244d08e308..84c981c08c1 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -9,7 +9,7 @@ # Do not use any TAB characters for whitespace. # ############################################################################## -kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. -query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically -log_tables-big : Bug#48646 2010-11-15 mattiasj report already exists -read_many_rows_innodb : Bug#37635 2010-11-15 mattiasj report already exists +kill : Bug#11748945 2008-12-03 HHunger need some changes to be robust enough for pushbuild. +read_many_rows_innodb : Bug#11748886 2010-11-15 mattiasj report already exists +main.log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists + diff --git a/mysql-test/t/endspace.test b/mysql-test/t/endspace.test index b223c683cde..072000ce3cc 100644 --- a/mysql-test/t/endspace.test +++ b/mysql-test/t/endspace.test @@ -93,7 +93,9 @@ alter table t1 modify text1 text not null, pack_keys=1; select * from t1 where text1 like 'teststring_%'; # The following gives wrong result in InnoDB +--sorted_result select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%'; +--sorted_result select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t'; select concat('|', text1, '|') from t1 order by text1; drop table t1; diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test index f6edacfaa29..95ec2c5b21d 100644 --- a/mysql-test/t/error_simulation.test +++ b/mysql-test/t/error_simulation.test @@ -89,6 +89,20 @@ SET SESSION debug = DEFAULT; DROP TABLE t1, t2; + +--echo # +--echo # Bug#11747970 34660: CRASH WHEN FEDERATED TABLE LOSES CONNECTION DURING INSERT ... SELECT +--echo # +CREATE TABLE t1(f1 INT, KEY(f1)); +CREATE TABLE t2(f1 INT); +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2); +SET SESSION debug='d,bug11747970_simulate_error'; +INSERT IGNORE INTO t2 SELECT f1 FROM t1 a WHERE NOT EXISTS (SELECT 1 FROM t2 b WHERE a.f1 = b.f1); +SET SESSION debug = DEFAULT; +DROP TABLE t1,t2; + + --echo # --echo # End of 5.1 tests --echo # diff --git a/mysql-test/t/events_1.test b/mysql-test/t/events_1.test index ccdeb70d291..7f31e3fc881 100644 --- a/mysql-test/t/events_1.test +++ b/mysql-test/t/events_1.test @@ -4,6 +4,8 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc +call mtr.add_suppression("Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted"); + --disable_warnings drop database if exists events_test; drop database if exists db_x; @@ -270,23 +272,28 @@ SHOW EVENTS; --echo Try to alter mysql.event: the server should fail to load --echo event information after mysql.event was tampered with. --echo ---echo First, let's add a column to the end and make sure everything ---echo works as before +--echo First, let's add a column to the end and check the error is emitted. --echo ALTER TABLE mysql.event ADD dummy INT; ---replace_column 8 # 9 # +--error ER_EVENT_OPEN_TABLE_FAILED SHOW EVENTS; +--error ER_EVENT_OPEN_TABLE_FAILED SELECT event_name FROM INFORMATION_SCHEMA.events; ---replace_regex /STARTS '[^']+'/STARTS '#'/ +--error ER_EVENT_OPEN_TABLE_FAILED SHOW CREATE EVENT intact_check; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT no_such_event; +--error ER_EVENT_OPEN_TABLE_FAILED CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +--error ER_EVENT_OPEN_TABLE_FAILED ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; +--error ER_EVENT_OPEN_TABLE_FAILED ALTER EVENT intact_check_1 RENAME TO intact_check_2; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT intact_check_1; +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT intact_check_2; +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT intact_check; DROP DATABASE IF EXISTS mysqltest_no_such_database; CREATE DATABASE mysqltest_db2; @@ -296,6 +303,7 @@ SHOW VARIABLES LIKE 'event_scheduler'; SET GLOBAL event_scheduler=OFF; # Clean up ALTER TABLE mysql.event DROP dummy; +DROP EVENT intact_check; CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; --echo --echo Now let's add a column to the first position: the server @@ -303,24 +311,26 @@ CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing"; --echo ALTER TABLE mysql.event ADD dummy INT FIRST; --error ER_CANNOT_LOAD_FROM_TABLE +--error ER_EVENT_OPEN_TABLE_FAILED SHOW EVENTS; --error ER_CANNOT_LOAD_FROM_TABLE +--error ER_EVENT_OPEN_TABLE_FAILED SELECT event_name FROM INFORMATION_SCHEMA.events; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED SHOW CREATE EVENT intact_check; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT no_such_event; ---error ER_EVENT_STORE_FAILED +--error ER_EVENT_OPEN_TABLE_FAILED CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED ALTER EVENT intact_check_1 RENAME TO intact_check_2; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT intact_check_1; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT intact_check_2; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT intact_check; # Should work OK DROP DATABASE IF EXISTS mysqltest_no_such_database; @@ -341,25 +351,25 @@ INSERT INTO event_like SELECT * FROM mysql.event; --echo --echo ALTER TABLE mysql.event DROP comment, DROP starts; ---error ER_CANNOT_LOAD_FROM_TABLE +--error ER_EVENT_OPEN_TABLE_FAILED SHOW EVENTS; ---error ER_CANNOT_LOAD_FROM_TABLE +--error ER_EVENT_OPEN_TABLE_FAILED SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---error ER_CANNOT_LOAD_FROM_TABLE +--error ER_EVENT_OPEN_TABLE_FAILED SHOW CREATE EVENT intact_check; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT no_such_event; ---error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED +--error ER_EVENT_OPEN_TABLE_FAILED CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED ALTER EVENT intact_check_1 RENAME TO intact_check_2; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT intact_check_1; ---error ER_EVENT_DOES_NOT_EXIST +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT intact_check_2; -# Should succeed +--error ER_EVENT_OPEN_TABLE_FAILED DROP EVENT intact_check; DROP DATABASE IF EXISTS mysqltest_no_such_database; CREATE DATABASE mysqltest_db2; @@ -407,9 +417,54 @@ CREATE TABLE mysql.event like event_like; DROP TABLE event_like; --replace_column 8 # 9 # SHOW EVENTS; -# -# End of tests -# + +--echo +--echo # +--echo # Bug#12394306: the sever may crash if mysql.event is corrupted +--echo # + +--echo +CREATE EVENT ev1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; +ALTER EVENT ev1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8; + +--echo +CREATE TABLE event_original LIKE mysql.event; +INSERT INTO event_original SELECT * FROM mysql.event; + +--echo +ALTER TABLE mysql.event MODIFY modified CHAR(1); + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +SHOW EVENTS; + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +SELECT event_name, created, last_altered FROM information_schema.events; + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +CREATE EVENT ev2 ON SCHEDULE EVERY 5 HOUR DO SELECT 5; + +--echo +--error ER_EVENT_OPEN_TABLE_FAILED +ALTER EVENT ev1 ON SCHEDULE EVERY 9 HOUR DO SELECT 9; + +--echo +DROP TABLE mysql.event; +RENAME TABLE event_original TO mysql.event; + +--echo +DROP EVENT ev1; + +--echo +SHOW EVENTS; + + +--echo +--echo # +--echo # End of tests +--echo # let $wait_condition= select count(*) = 0 from information_schema.processlist diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test index e80d87d4047..85b51335329 100644 --- a/mysql-test/t/events_bugs.test +++ b/mysql-test/t/events_bugs.test @@ -857,6 +857,7 @@ DROP EVENT e2; DROP EVENT e1; SET TIME_ZONE=@save_time_zone; +SET TIMESTAMP=DEFAULT; # # START - BUG#28666 CREATE EVENT ... EVERY 0 SECOND let server crash @@ -1234,7 +1235,73 @@ SHOW EVENTS; DROP DATABASE event_test1; DROP DATABASE event_test12; +--echo # +--echo # Bug#12546938 (formerly known as bug#61005): +--echo # CREATE IF NOT EXIST EVENT WILL CREATE MULTIPLE RUNNING EVENTS +--echo # +USE events_test; +SET GLOBAL event_scheduler = ON; + +--disable_warnings +DROP TABLE IF EXISTS table_bug12546938; +DROP EVENT IF EXISTS event_Bug12546938; +--enable_warnings +CREATE TABLE table_bug12546938 (i INT); + +delimiter |; + +--echo # Create an event which will be executed with a small delay +--echo # and won't be automatically dropped after that. +CREATE EVENT event_Bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN + INSERT INTO table_bug12546938 VALUES(1); +END +| + +--echo # Now try to create the same event using CREATE EVENT IF NOT EXISTS. +--echo # A warning should be emitted. A new event should not be created nor +--echo # the old event should be re-executed. +CREATE EVENT IF NOT EXISTS event_bug12546938 +ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE +ENABLE DO +BEGIN + INSERT INTO table_bug12546938 VALUES (1); +END +| + +delimiter ;| +--echo # Wait until at least one instance of event is executed. +let $wait_condition= SELECT COUNT(*) FROM table_bug12546938; +--source include/wait_condition.inc + +--echo # Check that only one instance of our event was executed. +SELECT COUNT(*) FROM table_bug12546938; + +--echo # Clean-up. +DROP EVENT IF EXISTS event_Bug12546938; +DROP TABLE table_bug12546938; +SET GLOBAL EVENT_SCHEDULER = OFF; + +# +# Bug#11764334 - 57156: ALTER EVENT CHANGES THE EVENT STATUS +# +--disable_warnings +DROP DATABASE IF EXISTS event_test11764334; +--enable_warnings +CREATE DATABASE event_test11764334; +USE event_test11764334; +CREATE EVENT ev1 ON SCHEDULE EVERY 3 SECOND DISABLE DO SELECT 1; +--replace_column 9 # 10 # +SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; +ALTER EVENT ev1 ON SCHEDULE EVERY 4 SECOND; +--replace_column 9 # 10 # +SHOW EVENTS IN event_test11764334 WHERE NAME='ev1'; +DROP EVENT ev1; +DROP DATABASE event_test11764334; +USE test; ########################################################################### # # End of tests diff --git a/mysql-test/t/events_restart.test b/mysql-test/t/events_restart.test index e155fe2ea16..facf2912087 100644 --- a/mysql-test/t/events_restart.test +++ b/mysql-test/t/events_restart.test @@ -1,6 +1,8 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc +call mtr.add_suppression("Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted"); + # # Test that when the server is restarted, it checks mysql.event table, # and disables the scheduler if it's not up to date. diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 3318b3453c8..f516ec6ab03 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -1,5 +1,5 @@ # -# Test of different EXPLAIN's +# Test of different EXPLAINs --disable_warnings drop table if exists t1; @@ -157,11 +157,12 @@ CREATE TABLE t1 (f1 INT not null); SELECT @@session.sql_mode INTO @old_sql_mode; SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; -# EXPLAIN EXTENDED (with subselect). used to crash. should give NOTICE. ---error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +# EXPLAIN EXTENDED (with subselect). used to crash. +# This is actually a valid query for this sql_mode, +# but it was transformed in such a way that it failed, see +# Bug#12329653 - EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t ); -SHOW WARNINGS; SET SESSION sql_mode=@old_sql_mode; diff --git a/mysql-test/t/feedback_plugin_send.test b/mysql-test/t/feedback_plugin_send.test index b49c0d0e252..14765ee9543 100644 --- a/mysql-test/t/feedback_plugin_send.test +++ b/mysql-test/t/feedback_plugin_send.test @@ -1,5 +1,4 @@ source t/feedback_plugin_load.test; -source include/big_test.inc; if (!$MTR_FEEDBACK_PLUGIN) { skip MTR_FEEDBACK_PLUGIN is not set; diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 9d51a5c94f9..1906d2d347a 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -349,4 +349,10 @@ DROP TABLE t1; SELECT ROUND(LEAST(15, -4939092, 0.2704), STDDEV('a')); +--echo # +--echo # Bug#12392636 ASSERTION FAILED: SCALE >= 0 && PRECISION > 0 && SCALE <= PRECISION +--echo # + +SELECT SUM(DISTINCT (TRUNCATE((.1), NULL))); + --echo End of 5.1 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 98ecb649c94..2d4f50c8271 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -921,4 +921,17 @@ SELECT DATE_FORMAT('0000-00-11', '%W'); SELECT DATE_FORMAT('0000-00-11', '%a'); SELECT DATE_FORMAT('0000-00-11', '%w'); +--echo # +--echo # Bug#12403504 AFTER FIX FOR #11889186 : ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0 +--echo # + +SELECT MAKEDATE(11111111,1); +SELECT WEEK(DATE_ADD(FROM_DAYS(1),INTERVAL 1 MONTH), 1); + +--echo # +--echo # Bug#12584302 AFTER FIX FOR #12403504: ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0, +--echo # + +DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5); + --echo End of 5.1 tests diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 9e6002a1faf..b99d35ecd48 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -940,3 +940,31 @@ DROP TABLE t1; --echo End of 5.0 tests. + + +--echo # +--echo # Bug #57323/11764487: myisam corruption with insert ignore +--echo # and invalid spatial data +--echo # + +CREATE TABLE t1(a LINESTRING NOT NULL, b GEOMETRY NOT NULL, + SPATIAL KEY(a), SPATIAL KEY(b)) ENGINE=MyISAM; +INSERT INTO t1 VALUES(GEOMFROMTEXT("point (0 0)"), GEOMFROMTEXT("point (1 1)")); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +INSERT IGNORE INTO t1 SET a=GEOMFROMTEXT("point (-6 0)"), b=GEOMFROMTEXT("error"); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +INSERT IGNORE INTO t1 SET a=GEOMFROMTEXT("point (-6 0)"), b=NULL; +SELECT ASTEXT(a), ASTEXT(b) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(a INT NOT NULL, b GEOMETRY NOT NULL, + KEY(a), SPATIAL KEY(b)) ENGINE=MyISAM; +INSERT INTO t1 VALUES(0, GEOMFROMTEXT("point (1 1)")); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +INSERT IGNORE INTO t1 SET a=0, b=GEOMFROMTEXT("error"); +--error ER_CANT_CREATE_GEOMETRY_OBJECT +INSERT IGNORE INTO t1 SET a=1, b=NULL; +SELECT a, ASTEXT(b) FROM t1; +DROP TABLE t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 94cec60944a..f8cec14d9ae 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -760,10 +760,56 @@ drop table t1; --echo # on char > 31 bytes". --echo # create table t1(a char(32) not null) engine=myisam; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ ---error ER_CANT_CREATE_TABLE +--error ER_WRONG_ARGUMENTS create spatial index i on t1 (a); drop table t1; +# +# Bug#11767480 - SPATIAL INDEXES ON NON-SPATIAL COLUMNS CAUSE CRASHES. +# +CREATE TABLE t0 (a BINARY(32) NOT NULL); +--error ER_WRONG_ARGUMENTS +CREATE SPATIAL INDEX i on t0 (a); +INSERT INTO t0 VALUES (1); + +--error ER_WRONG_ARGUMENTS +CREATE TABLE t1( + col0 BINARY NOT NULL, + col2 TIMESTAMP, + SPATIAL INDEX i1 (col0) +) ENGINE=MyISAM; + +# Test other ways to add indices +CREATE TABLE t1 ( + col0 BINARY NOT NULL, + col2 TIMESTAMP +) ENGINE=MyISAM; + +--error ER_WRONG_ARGUMENTS +CREATE SPATIAL INDEX idx0 ON t1(col0); + +--error ER_WRONG_ARGUMENTS +ALTER TABLE t1 ADD SPATIAL INDEX i1 (col0); + +CREATE TABLE t2 ( + col0 INTEGER NOT NULL, + col1 POINT, + col2 POINT +); + +--error ER_WRONG_ARGUMENTS +CREATE SPATIAL INDEX idx0 ON t2 (col1, col2); + +--error ER_WRONG_ARGUMENTS +CREATE TABLE t3 ( + col0 INTEGER NOT NULL, + col1 POINT, + col2 LINESTRING, + SPATIAL INDEX i1 (col1, col2) +); + +# cleanup +DROP TABLE t0, t1, t2; + --echo End of 5.1 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 44af2ca27a2..8f38f0e9fd9 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1300,5 +1300,19 @@ FROM t1 GROUP BY a; DROP TABLE t1; +--echo # +--echo # Bug#11765254 (58200): Assertion failed: param.sort_length when grouping +--echo # by functions +--echo # + +SET SQL_BIG_TABLES=1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(0); +SELECT 1 FROM t1 GROUP BY IF(`a`,'',''); +SELECT 1 FROM t1 GROUP BY TRIM(LEADING RAND() FROM ''); +SELECT 1 FROM t1 GROUP BY SUBSTRING('',SLEEP(0),''); +SELECT 1 FROM t1 GROUP BY SUBSTRING(SYSDATE() FROM 'K' FOR 'jxW<'); +DROP TABLE t1; +SET SQL_BIG_TABLES=0; --echo # End of 5.1 tests diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test index e5700eaeeb8..7d56425799a 100644 --- a/mysql-test/t/heap.test +++ b/mysql-test/t/heap.test @@ -459,7 +459,7 @@ drop table t1; # create table t1 (c char(10)) engine=memory; create table t2 (c varchar(10)) engine=memory; ---replace_column 8 # +--replace_column 8 # 12 # show table status like 't_'; drop table t1, t2; diff --git a/mysql-test/t/implicit_char_to_num_conversion.test b/mysql-test/t/implicit_char_to_num_conversion.test new file mode 100644 index 00000000000..b4948fbc69f --- /dev/null +++ b/mysql-test/t/implicit_char_to_num_conversion.test @@ -0,0 +1,174 @@ +########### implicit_char_to_num_conversion.test ####################### +# # +# This test aims at using string/char literal in comparison operators # +# without explicit type-cast. This is a bug test for Bug#11766521 # +# - Incorrect result is returned if string/char literal is used with # +# comparision operator and bit data type column. Test is extended to # +# include numeric data type comparison with string/char literal # +# # +# # +# Creation: # +# 2011-05-10 vfisrekar Implement this test as part of Bug#11766521 # +# # +######################################################################## + +--disable_warnings +DROP TABLE IF EXISTS t5; +--enable_warnings + +let $default_engine = `select @@SESSION.storage_engine`; + +# Bug#11766521 - BIT Datatype comparison in where clause return incorrect +# result for '=' , '<=>' operators +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 BIT(2) PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (0), (1), (2); +SELECT HEX(c1) FROM t5 ORDER BY c1; +# Enable Following two select after Bug#11766521 fix +# SELECT HEX(c1) FROM t5 WHERE c1 = '1' ORDER BY c1; +# SELECT HEX(c1) FROM t5 WHERE c1 <=> '1' ORDER BY c1; +SELECT HEX(c1) FROM t5 WHERE c1 = b'1' ORDER BY c1; +SELECT HEX(c1) FROM t5 WHERE c1 <=> b'1' ORDER BY c1; +SELECT HEX(c1) FROM t5 WHERE c1 != b'1' ORDER BY c1; +SELECT HEX(c1) FROM t5 WHERE c1 >= '1' ORDER BY c1; +SELECT HEX(c1) FROM t5 WHERE c1 <= '1' ORDER BY c1; +SELECT HEX(c1) FROM t5 WHERE c1 < '1' ORDER BY c1; +SELECT HEX(c1) FROM t5 WHERE c1 > '0' ORDER BY c1; +DROP TABLE t5; + +# FLOAT Data-type +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 FLOAT(5,2) PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (95.95), (-10.10), (1), (0); +SELECT c1 FROM t5 ORDER BY c1; +# Compare with string literal +# Following two queries does not return result may be due to Bug#11766521. +# Enable them after Bug#11766521 fix. +# SELECT c1 FROM t5 WHERE c1 = '10.10' ORDER BY c1; +# SELECT c1 FROM t5 WHERE c2 <=> '11.11' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 >= '95' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <= '10.10' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 != '1' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 < '1' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 > '0' ORDER BY c1; +DROP TABLE t5; + +# TINYINT Datatype +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 TINYINT PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (95), (10),(11),(-8); +SELECT c1 FROM t5 ORDER BY c1; +# Compare with string literal +SELECT c1 FROM t5 WHERE c1 = '10' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <=> '10' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 >= '95' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <= '11' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 != '-8' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 < '11' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 > '10' ORDER BY c1; +DROP TABLE t5; + +# SMALLINT Datatype +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 SMALLINT PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (395), (-200), (100), (111); +SELECT c1 FROM t5 ORDER BY c1; +# Compare with string literal +SELECT c1 FROM t5 WHERE c1 = '100' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <=> '100' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 >= '395' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <= '-200' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 != '100' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 < '111' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 > '111' ORDER BY c1; +DROP TABLE t5; + +# MEDIUMINT Datatype +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 MEDIUMINT PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (-8388607), (311),(215),(88608); +SELECT c1 FROM t5 ORDER BY c1; +# Compare with string literal +SELECT c1 FROM t5 WHERE c1 = '311' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <=> '311' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 >= '215' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <= '88608' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 != '-8388607' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 < '215' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 > '215' ORDER BY c1; +DROP TABLE t5; + +# INT Datatype +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 INT PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (-2147483647), (1011),(15),(9388607); +SELECT c1 FROM t5 ORDER BY c1; +# Compare with string literal +SELECT c1 FROM t5 WHERE c1 = '9388607' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <=> '9388607' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 >= '15' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <= '1011' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 != '-2147483647' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 < '15' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 > '15' ORDER BY c1; +DROP TABLE t5; + +# BIGINT Data-type +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 BIGINT PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (-9223372036854775807), (12011),(500),(3372036854775808); +SELECT c1 FROM t5 ORDER BY c1; +# Compare with string literal +SELECT c1 FROM t5 WHERE c1 = '-9223372036854775807' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <=> '-9223372036854775807' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 >= '12011' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <= '500' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 != '3372036854775808' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 < '12011' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 > '12011' ORDER BY c1; +DROP TABLE t5; + +# DOUBLE Datatype +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 DOUBLE(5,2) PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (95.95), (11.11),(5),(-908.92); +SELECT c1 FROM t5 ORDER BY c1; +# Compare with string literal +SELECT c1 FROM t5 WHERE c1 = '11.11' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <=> '11.11' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 >= '5' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <= '95.95' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 != '-908.92' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 < '95.95' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 > '-908.92' ORDER BY c1; +DROP TABLE t5; + +# NUMERIC Datatype +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 NUMERIC(5,2) PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (95.95), (11.11),(5),(-908.92); +SELECT c1 FROM t5 ORDER BY c1; +# Compare with string literal +SELECT c1 FROM t5 WHERE c1 = '11.11' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <=> '11.11' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 >= '5' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <= '95.95' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 != '-908.92' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 < '95.95' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 > '-908.92' ORDER BY c1; +DROP TABLE t5; + +# DECIMAL Datatype +--replace_result $default_engine <default_engine> +eval CREATE TABLE t5(c1 DECIMAL(5,2) PRIMARY KEY) ENGINE = $default_engine; +INSERT INTO t5 VALUES (95.95), (11.11),(5),(-908.92); +SELECT c1 FROM t5 ORDER BY c1; +# Compare with string literal +SELECT c1 FROM t5 WHERE c1 = '11.11' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <=> '11.11' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 >= '5' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 <= '95.95' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 != '-908.92' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 < '95.95' ORDER BY c1; +SELECT c1 FROM t5 WHERE c1 > '-908.92' ORDER BY c1; +DROP TABLE t5; diff --git a/mysql-test/t/innodb_bug47167.test b/mysql-test/t/innodb_bug47167.test deleted file mode 100644 index 88e927c01bd..00000000000 --- a/mysql-test/t/innodb_bug47167.test +++ /dev/null @@ -1,45 +0,0 @@ -# This is the unit test for bug *47167. -# It tests setting the global variable -# "innodb_file_format_check" with a -# user-Defined Variable. - ---source include/have_innodb.inc - -# Save the value (Antelope) in 'innodb_file_format_check' to -# 'old_innodb_file_format_check' -set @old_innodb_file_format_check=@@innodb_file_format_check; - -# @old_innodb_file_format_check shall have the value of 'Antelope' -select @old_innodb_file_format_check; - -# Reset the value in 'innodb_file_format_check' to 'Barracuda' -set global innodb_file_format_check = Barracuda; - -select @@innodb_file_format_check; - -# Set 'innodb_file_format_check' to its default value, which -# is the latest file format supported in the current release. -set global innodb_file_format_check = DEFAULT; - -select @@innodb_file_format_check; - -# Put the saved value back to 'innodb_file_format_check' -set global innodb_file_format_check = @old_innodb_file_format_check; - -# Check whether 'innodb_file_format_check' get its original value. -select @@innodb_file_format_check; - -# Following are negative tests, all should fail. ---disable_warnings ---error ER_WRONG_ARGUMENTS -set global innodb_file_format_check = cheetah; - ---error ER_WRONG_ARGUMENTS -set global innodb_file_format_check = Bear; - ---error ER_WRONG_ARGUMENTS -set global innodb_file_format_check = on; - ---error ER_WRONG_ARGUMENTS -set global innodb_file_format_check = off; ---enable_warnings diff --git a/mysql-test/t/innodb_file_format.test b/mysql-test/t/innodb_file_format.test deleted file mode 100644 index 6de026ad97f..00000000000 --- a/mysql-test/t/innodb_file_format.test +++ /dev/null @@ -1,31 +0,0 @@ --- source include/have_innodb.inc - -call mtr.add_suppression("InnoDB: invalid innodb_file_format_check value"); - -select @@innodb_file_format; -select @@innodb_file_format_check; -set global innodb_file_format=antelope; -set global innodb_file_format=barracuda; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=cheetah; -select @@innodb_file_format; -set global innodb_file_format=default; -select @@innodb_file_format; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=on; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=off; -select @@innodb_file_format; -set global innodb_file_format_check=antelope; -set global innodb_file_format_check=barracuda; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format_check=cheetah; -select @@innodb_file_format_check; -set global innodb_file_format_check=default; -select @@innodb_file_format_check; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=on; ---error ER_WRONG_ARGUMENTS -set global innodb_file_format=off; -select @@innodb_file_format_check; -set global innodb_file_format_check=antelope; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 0b34f9fdbac..90386a97fc6 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -1814,6 +1814,51 @@ REPAIR TABLE m1; # DROP TABLE m1, t1; + +--echo # +--echo # BUG#11763712 - 56458: KILLING A FLUSH TABLE FOR A MERGE/CHILD +--echo # CRASHES SERVER +--echo # +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT); +CREATE TABLE t3(a INT, b INT); +CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2); + +--echo # Test reopen merge parent failure +LOCK TABLES m1 READ; +--echo # Remove 'm1' table using file operations. +remove_file $MYSQLD_DATADIR/test/m1.MRG; +remove_file $MYSQLD_DATADIR/test/m1.frm; +--error ER_NO_SUCH_TABLE +FLUSH TABLES; +UNLOCK TABLES; +CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2); + +--echo # Test reopen merge child failure +LOCK TABLES m1 READ; +--echo # Remove 't1' table using file operations. +remove_file $MYSQLD_DATADIR/test/t1.frm; +remove_file $MYSQLD_DATADIR/test/t1.MYI; +remove_file $MYSQLD_DATADIR/test/t1.MYD; +--error ER_NO_SUCH_TABLE +FLUSH TABLES; +UNLOCK TABLES; +CREATE TABLE t1(a INT); + +--echo # Test reattach merge failure +LOCK TABLES m1 READ; +--echo # Replace 't1' with 't3' table using file operations. +remove_file $MYSQLD_DATADIR/test/t1.frm; +remove_file $MYSQLD_DATADIR/test/t1.MYI; +remove_file $MYSQLD_DATADIR/test/t1.MYD; +copy_file $MYSQLD_DATADIR/test/t3.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file $MYSQLD_DATADIR/test/t3.MYI $MYSQLD_DATADIR/test/t1.MYI; +copy_file $MYSQLD_DATADIR/test/t3.MYD $MYSQLD_DATADIR/test/t1.MYD; +--error ER_CANT_REOPEN_TABLE +FLUSH TABLES; +UNLOCK TABLES; +DROP TABLE t1, t2, t3, m1; + --echo End of 5.1 tests --disable_result_log diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index e912eb754f4..27352ca3c6f 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1692,6 +1692,24 @@ DROP TABLE t1; SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; SET myisam_repair_threads=@@global.myisam_repair_threads; +--echo # +--echo # BUG#11757032 - 49030: OPTIMIZE TABLE BREAKS MYISAM TABLE WHEN +--echo # USING MYISAM_USE_MMAP ON WINDOWS +--echo # +SET GLOBAL myisam_use_mmap=1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1),(2); +DELETE FROM t1 WHERE a=1; +FLUSH TABLE t1; +LOCK TABLE t1 WRITE; +OPTIMIZE TABLE t1; +INSERT INTO t1 VALUES(3); +UNLOCK TABLES; +SELECT * FROM t1; +CHECK TABLE t1; +DROP TABLE t1; +SET GLOBAL myisam_use_mmap=default; + --echo End of 5.1 tests show variables like 'myisam_block_size'; diff --git a/mysql-test/t/myisampack.test b/mysql-test/t/myisampack.test index fcd342168c4..da8e9cca67b 100644 --- a/mysql-test/t/myisampack.test +++ b/mysql-test/t/myisampack.test @@ -109,3 +109,48 @@ SELECT COUNT(*) FROM mysql_db1.t1 WHERE c2 < 5; # DROP TABLE mysql_db1.t1; DROP DATABASE mysql_db1; + +--echo # +--echo # BUG#11761180 - 53646: MYISAMPACK CORRUPTS TABLES WITH FULLTEXT INDEXES +--echo # +CREATE TABLE t1(a CHAR(4), FULLTEXT(a)); +INSERT INTO t1 VALUES('aaaa'),('bbbb'),('cccc'); +FLUSH TABLE t1; +--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 +--exec $MYISAMCHK -srq $MYSQLD_DATADIR/test/t1 +CHECK TABLE t1; +SELECT * FROM t1 WHERE MATCH(a) AGAINST('aaaa' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(a) AGAINST('aaaa'); +DROP TABLE t1; + +--echo # Test table with key_reflength > rec_reflength +CREATE TABLE t1(a CHAR(30), FULLTEXT(a)); +--disable_query_log +--echo # Populating a table, so it's index file exceeds 65K +let $1=1700; +while ($1) +{ + eval INSERT INTO t1 VALUES('$1aaaaaaaaaaaaaaaaaaaaaaaaaa'); + dec $1; +} + +--echo # Populating a table, so index file has second level fulltext tree +let $1=60; +while ($1) +{ + eval INSERT INTO t1 VALUES('aaaa'),('aaaa'),('aaaa'),('aaaa'),('aaaa'); + dec $1; +} +--enable_query_log + +FLUSH TABLE t1; +--echo # Compressing table +--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 +--echo # Fixing index (repair by sort) +--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 +CHECK TABLE t1; +FLUSH TABLE t1; +--echo # Fixing index (repair with keycache) +--exec $MYISAMCHK -soq $MYSQLD_DATADIR/test/t1 +CHECK TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/t/mysqlbinlog_base64.test b/mysql-test/t/mysqlbinlog_base64.test index fb21e28fdcb..3d3444cea1c 100644 --- a/mysql-test/t/mysqlbinlog_base64.test +++ b/mysql-test/t/mysqlbinlog_base64.test @@ -71,3 +71,32 @@ select count(*) from t2; --remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_base64.sql drop table t1; drop table t2; + +# +# BUG#12354268 +# +# This test verifies that using --start-position with DECODE-ROWS +# does not make mysqlbinlog to output an error stating that it +# does not contain any FD event. +# + +RESET MASTER; +USE test; +SET @old_binlog_format= @@binlog_format; +SET SESSION binlog_format=ROW; +CREATE TABLE t1(c1 INT); +--let $master_binlog= query_get_value(SHOW MASTER STATUS, File, 1) +--let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1) +--let $MYSQLD_DATADIR= `SELECT @@datadir` + +INSERT INTO t1 VALUES (1); + +FLUSH LOGS; + +--disable_result_log +--exec $MYSQL_BINLOG --base64-output=DECODE-ROWS --start-position=$master_pos -v $MYSQLD_DATADIR/$master_binlog +--enable_result_log + +DROP TABLE t1; +SET SESSION binlog_format= @old_binlog_format; +RESET MASTER; diff --git a/mysql-test/t/mysqld_option_err.test b/mysql-test/t/mysqld_option_err.test index 9c02dec51e6..5e35f924b15 100644 --- a/mysql-test/t/mysqld_option_err.test +++ b/mysql-test/t/mysqld_option_err.test @@ -21,22 +21,22 @@ mkdir $MYSQLTEST_VARDIR/tmp/mysqld_option_err; --echo Test that unknown option is not silently ignored. --error 2 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --nonexistentoption >$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --nonexistentoption --loose-skip-innodb --loose-skip-pbxt >$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Test bad binlog format. --error 1 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --log-bin --binlog-format=badformat >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --log-bin --binlog-format=badformat >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Test bad default storage engine. --error 1 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --default-storage-engine=nonexistentengine >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --default-storage-engine=nonexistentengine >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Test non-numeric value passed to number option. --error 1 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --min-examined-row-limit=notanumber >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --min-examined-row-limit=notanumber >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 # Test for MBug#423035: error in parsing enum value for plugin @@ -44,16 +44,16 @@ mkdir $MYSQLTEST_VARDIR/tmp/mysqld_option_err; # See also Bug#32034. --echo Test that bad value for plugin enum option is rejected correctly. --error 7 ---exec $MYSQLD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --plugin-dir=$MYSQLTEST_VARDIR/plugins --plugin-load=example=ha_example.so --plugin-example-enum-var=noexist >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --skip-networking --datadir=$MYSQLTEST_VARDIR/tmp/mysqld_option_err --skip-grant-tables --plugin-dir=$MYSQLTEST_VARDIR/plugins --plugin-load=example=ha_example.so --plugin-example-enum-var=noexist >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 # # Test that an wrong option with --help --verbose gives an error # --echo Test that --help --verbose works ---exec $MYSQLD --help --verbose >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --help --verbose >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Test that --not-known-option --help --verbose gives error --error 2 ---exec $MYSQLD --not-known-option --help --verbose >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 +--exec $MYSQLD_SIMPLE_CMD --not-known-option --help --verbose >>$MYSQLTEST_VARDIR/tmp/mysqld_option_err/mysqltest.log 2>&1 --echo Done. diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 51d12130b13..40cb32909f1 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -343,6 +343,14 @@ eval select $mysql_errno as "after_!errno_masked_error" ; EOF # ---------------------------------------------------------------------------- +# Check backtick and query_get_value, result should be empty +# ---------------------------------------------------------------------------- +let $empty= `garbage`; +echo $empty is empty; +let $empty= query_get_value(nonsense, blabla, 1); +echo $empty is empty; + +# ---------------------------------------------------------------------------- # Switch the abort on error on and check the effect on $mysql_errno # ---------------------------------------------------------------------------- --error ER_PARSE_ERROR @@ -373,6 +381,23 @@ select 3 from t1 ; # ---------------------------------------------------------------------------- +# Test --error with backtick operator or query_get_value +# ---------------------------------------------------------------------------- + +--error 0,ER_NO_SUCH_TABLE +let $empty= `SELECT foo from bar`; +echo $empty is empty; + +--error 0,ER_BAD_FIELD_ERROR +let $empty= query_get_value(SELECT bar as foo, baz, 1); +echo $empty is empty; + +--error 0,ER_NO_SUCH_TABLE +if (!`SELECT foo from bar`) { + echo "Yes it's empty"; +} + +# ---------------------------------------------------------------------------- # Test comments # ---------------------------------------------------------------------------- diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 14b568f2b22..2a5f74a3a13 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1510,4 +1510,11 @@ SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; DROP TABLE t1; +--echo # +--echo # Bug#11765255 58201: +--echo # VALGRIND/CRASH WHEN ORDERING BY MULTIPLE AGGREGATE FUNCTIONS +--echo # + +select 1 order by max(1) + min(1); + --echo End of 5.1 tests diff --git a/mysql-test/t/parser_stack.test b/mysql-test/t/parser_stack.test index 3330ef41833..f8291ce8766 100644 --- a/mysql-test/t/parser_stack.test +++ b/mysql-test/t/parser_stack.test @@ -1,18 +1,3 @@ -# Copyright (C) 2008 Sun Microsystems, Inc -# -# This program is free software; you can redistribute it and/or modify -# it under the terms of the GNU General Public License as published by -# the Free Software Foundation; version 2 of the License. -# -# This program is distributed in the hope that it will be useful, -# but WITHOUT ANY WARRANTY; without even the implied warranty of -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -# GNU General Public License for more details. -# -# You should have received a copy of the GNU General Public License -# along with this program; if not, write to the Free Software -# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA - # # These tests are designed to cause an internal parser stack overflow, # and trigger my_yyoverflow(). diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index c4c17a6d2f1..61aab8233ff 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -15,6 +15,37 @@ drop table if exists t1, t2; --enable_warnings --echo # +--echo # Bug#11765667: bug#58655: ASSERTION FAILED, +--echo # SERVER CRASHES WITH MYSQLD GOT SIGNAL 6 +--echo # +CREATE TABLE t1 ( + id MEDIUMINT NOT NULL AUTO_INCREMENT, + dt DATE, st VARCHAR(255), uid INT, + id2nd LONGBLOB, filler VARCHAR(255), PRIMARY KEY(id, dt) +); +INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES + ('1991-03-14', 'Initial Insert', 200, 1234567, 'No Data'), + ('1991-02-26', 'Initial Insert', 201, 1234567, 'No Data'), + ('1992-03-16', 'Initial Insert', 234, 1234567, 'No Data'), + ('1992-07-02', 'Initial Insert', 287, 1234567, 'No Data'), + ('1991-05-26', 'Initial Insert', 256, 1234567, 'No Data'), + ('1991-04-25', 'Initial Insert', 222, 1234567, 'No Data'), + ('1993-03-12', 'Initial Insert', 267, 1234567, 'No Data'), + ('1993-03-14', 'Initial Insert', 291, 1234567, 'No Data'), + ('1991-12-20', 'Initial Insert', 298, 1234567, 'No Data'), + ('1994-10-31', 'Initial Insert', 220, 1234567, 'No Data'); +ALTER TABLE t1 PARTITION BY LIST (YEAR(dt)) ( + PARTITION d1 VALUES IN (1991, 1994), + PARTITION d2 VALUES IN (1993), + PARTITION d3 VALUES IN (1992, 1995, 1996) +); +INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES + ('1991-07-14', 'After Partitioning Insert', 299, 1234567, 'Insert row'); +UPDATE t1 SET filler='Updating the row' WHERE uid=298; + +DROP TABLE t1; + +--echo # --echo # Bug#59297: Can't find record in 'tablename' on update inner join --echo # diff --git a/mysql-test/t/partition_innodb_plugin.test b/mysql-test/t/partition_innodb_plugin.test index 626e5d19b99..60fe91ce920 100644 --- a/mysql-test/t/partition_innodb_plugin.test +++ b/mysql-test/t/partition_innodb_plugin.test @@ -1,15 +1,38 @@ --source include/have_partition.inc --source include/have_innodb_plugin.inc -# Remove the line below when bug#53307 is solved. ---source include/not_valgrind.inc let $MYSQLD_DATADIR= `SELECT @@datadir`; +--echo # +--echo # Bug#11766879/Bug#60106: DIFF BETWEEN # OF INDEXES IN MYSQL VS INNODB, +--echo # PARTITONING, ON INDEX CREATE +--echo # +call mtr.add_suppression("contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MySQL"); +CREATE TABLE t1 ( + id bigint NOT NULL AUTO_INCREMENT, + time date, + id2 bigint not null, + PRIMARY KEY (id,time) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +/*!50100 PARTITION BY RANGE(TO_DAYS(time)) +(PARTITION p10 VALUES LESS THAN (734708) ENGINE = InnoDB, + PARTITION p20 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */; + +INSERT INTO t1 (time,id2) VALUES ('2011-07-24',1); +INSERT INTO t1 (time,id2) VALUES ('2011-07-25',1); +INSERT INTO t1 (time,id2) VALUES ('2011-07-25',1); + +--error ER_DUP_ENTRY +CREATE UNIQUE INDEX uk_time_id2 on t1(time,id2); + +SELECT COUNT(*) FROM t1; + +DROP TABLE t1; + call mtr.add_suppression("nnoDB: Error: table `test`.`t1` .* Partition.* InnoDB internal"); --echo # --echo # Bug#55091: Server crashes on ADD PARTITION after a failed attempt --echo # -SET @old_innodb_file_format_check = @@global.innodb_file_format_check; SET @old_innodb_file_format = @@global.innodb_file_format; SET @old_innodb_file_per_table = @@global.innodb_file_per_table; SET @old_innodb_strict_mode = @@global.innodb_strict_mode; @@ -68,7 +91,6 @@ DROP TABLE t1; SET @@global.innodb_strict_mode = @old_innodb_strict_mode; SET @@global.innodb_file_format = @old_innodb_file_format; SET @@global.innodb_file_per_table = @old_innodb_file_per_table; -SET @@global.innodb_file_format_check = @old_innodb_file_format_check; # # Bug#32430 - show engine innodb status causes errors diff --git a/mysql-test/t/partition_not_embedded.test b/mysql-test/t/partition_myisam.test index 5c512085a9e..51f46aa71be 100644 --- a/mysql-test/t/partition_not_embedded.test +++ b/mysql-test/t/partition_myisam.test @@ -1,5 +1,4 @@ -- source include/have_partition.inc --- source include/not_embedded.inc --disable_warnings DROP TABLE IF EXISTS t1, t2; --enable_warnings @@ -51,3 +50,13 @@ DROP TABLE t1; --list_files $MYSQLD_DATADIR/test t1* --list_files $MYSQLD_DATADIR/test t2* --echo # End of bug#30102 test. + +--echo # Test of post-push fix for bug#11766249/59316 +CREATE TABLE t1 (a INT, b VARCHAR(255), PRIMARY KEY (a)) +ENGINE = MyISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (0) MAX_ROWS=100, + PARTITION p1 VALUES LESS THAN (100) MAX_ROWS=100, + PARTITION pMax VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1, "Partition p1, first row"); +DROP TABLE t1; diff --git a/mysql-test/t/secure_file_priv_win-master.opt b/mysql-test/t/secure_file_priv_win-master.opt new file mode 100644 index 00000000000..e9a43a5584d --- /dev/null +++ b/mysql-test/t/secure_file_priv_win-master.opt @@ -0,0 +1 @@ +--secure_file_priv=$MYSQL_TMP_DIR diff --git a/mysql-test/t/secure_file_priv_win.test b/mysql-test/t/secure_file_priv_win.test new file mode 100644 index 00000000000..a12510974ce --- /dev/null +++ b/mysql-test/t/secure_file_priv_win.test @@ -0,0 +1,79 @@ +# +# Bug58747 breaks secure_file_priv+not secure yet+still accesses other folders +# + +# we do the windows specific relative directory testing + +--source include/windows.inc + +CREATE TABLE t1 (c1 longtext); +INSERT INTO t1 values ('a'); + +LET $MYSQL_TMP_DIR_UCASE= `SELECT upper('$MYSQL_TMP_DIR')`; +LET $MYSQL_TMP_DIR_LCASE= `SELECT lower('$MYSQL_TMP_DIR')`; + +#create the file +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval SELECT * FROM t1 INTO OUTFILE '$MYSQL_TMP_DIR/B11764517.tmp'; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +show global variables like 'secure_file_priv'; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval SELECT load_file('$MYSQL_TMP_DIR\\\\B11764517.tmp') AS x; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval SELECT load_file('$MYSQL_TMP_DIR/B11764517.tmp') AS x; + +--replace_result $MYSQL_TMP_DIR_UCASE MYSQL_TMP_DIR_UCASE +eval SELECT load_file('$MYSQL_TMP_DIR_UCASE/B11764517.tmp') AS x; + +--replace_result $MYSQL_TMP_DIR_LCASE MYSQL_TMP_DIR_LCASE +eval SELECT load_file('$MYSQL_TMP_DIR_LCASE/B11764517.tmp') AS x; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval SELECT load_file('$MYSQL_TMP_DIR\\\\..a..\\\\..\\\\..\\\\B11764517.tmp') AS x; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval LOAD DATA INFILE '$MYSQL_TMP_DIR\\\\B11764517.tmp' INTO TABLE t1; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval LOAD DATA INFILE '$MYSQL_TMP_DIR/B11764517.tmp' INTO TABLE t1; + +--replace_result $MYSQL_TMP_DIR_UCASE MYSQL_TMP_DIR_UCASE +eval LOAD DATA INFILE '$MYSQL_TMP_DIR_UCASE/B11764517.tmp' INTO TABLE t1; + +--replace_result $MYSQL_TMP_DIR_LCASE MYSQL_TMP_DIR_LCASE +eval LOAD DATA INFILE '$MYSQL_TMP_DIR_LCASE/B11764517.tmp' INTO TABLE t1; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--error ER_OPTION_PREVENTS_STATEMENT +eval LOAD DATA INFILE "$MYSQL_TMP_DIR\\\\..a..\\\\..\\\\..\\\\B11764517.tmp" into table t1; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--error ER_OPTION_PREVENTS_STATEMENT +eval SELECT * FROM t1 INTO OUTFILE '$MYSQL_TMP_DIR\\\\..a..\\\\..\\\\..\\\\B11764517-2.tmp'; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval SELECT * FROM t1 INTO OUTFILE '$MYSQL_TMP_DIR\\\\B11764517-2.tmp'; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval SELECT * FROM t1 INTO OUTFILE '$MYSQL_TMP_DIR/B11764517-3.tmp'; + +--replace_result $MYSQL_TMP_DIR_UCASE MYSQL_TMP_DIR_UCASE +eval SELECT * FROM t1 INTO OUTFILE '$MYSQL_TMP_DIR_UCASE/B11764517-4.tmp'; + +--replace_result $MYSQL_TMP_DIR_LCASE MYSQL_TMP_DIR_LCASE +eval SELECT * FROM t1 INTO OUTFILE '$MYSQL_TMP_DIR_LCASE/B11764517-5.tmp'; + +--error 0,1 +--remove_file $MYSQL_TMP_DIR/B11764517.tmp; +--error 0,1 +--remove_file $MYSQL_TMP_DIR/B11764517-2.tmp; +--error 0,1 +--remove_file $MYSQL_TMP_DIR/B11764517-3.tmp; +--error 0,1 +--remove_file $MYSQL_TMP_DIR/B11764517-4.tmp; +--error 0,1 +--remove_file $MYSQL_TMP_DIR/B11764517-5.tmp; +DROP TABLE t1; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index e5ca35bda32..1c1bf9fa6d3 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -230,7 +230,7 @@ DROP TABLE """a"; #set names latin1; #create database `ä`; #create table `ä`.`ä` (a int) engine=heap; -#--replace_column 7 # 8 # 9 # +#--replace_column 7 # 8 # 9 # 12 # #show table status from `ä` LIKE 'ä'; #drop database `ä`; @@ -276,37 +276,37 @@ CREATE TABLE t3 ( insert into t1 values (1),(2); insert into t2 values (1),(2); insert into t3 values (1,1),(2,2); ---replace_column 6 # 7 # 8 # 9 # +--replace_column 6 # 7 # 8 # 9 # 12 # show table status; insert into t1 values (3),(4); insert into t2 values (3),(4); insert into t3 values (3,3),(4,4); ---replace_column 6 # 7 # 8 # 9 # +--replace_column 6 # 7 # 8 # 9 # 12 # show table status; insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); ---replace_column 6 # 7 # 8 # 9 # +--replace_column 6 # 7 # 8 # 9 # 12 # show table status; delete from t1 where a=3; delete from t2 where b=3; delete from t3 where a=3; ---replace_column 6 # 7 # 8 # 9 # 10 # +--replace_column 6 # 7 # 8 # 9 # 10 # 12 # show table status; truncate table t1; truncate table t2; truncate table t3; ---replace_column 6 # 7 # 8 # 9 # +--replace_column 6 # 7 # 8 # 9 # 12 # show table status; insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); ---replace_column 6 # 7 # 8 # 9 # +--replace_column 6 # 7 # 8 # 9 # 12 # show table status; delete from t1 where a=5; delete from t2 where b=5; delete from t3 where a=5; ---replace_column 6 # 7 # 8 # 9 # 10 # +--replace_column 6 # 7 # 8 # 9 # 10 # 12 # show table status; drop table t1, t2, t3; @@ -367,7 +367,7 @@ flush privileges; #set names latin1; #create database `ä`; #create table `ä`.`ä` (a int) engine=heap; -#--replace_column 7 # 8 # 9 # +#--replace_column 7 # 8 # 9 # 12 # #show table status from `ä` LIKE 'ä'; #drop database `ä`; @@ -430,7 +430,7 @@ flush tables; # Create a junk frm file on disk let $MYSQLD_DATADIR= `select @@datadir`; system echo "this is a junk file for test" >> $MYSQLD_DATADIR/test/t1.frm ; ---replace_column 6 # 7 # 8 # 9 # +--replace_column 6 # 7 # 8 # 9 # 12 # SHOW TABLE STATUS like 't1'; --error ER_NOT_FORM_FILE show create table t1; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 18a4a117939..7a04d89fdc2 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -2471,4 +2471,26 @@ DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # Bug#12428824 - PARSER STACK OVERFLOW AND CRASH IN SP_ADD_USED_ROUTINE +--echo # WITH OBSCURE QUERY +--echo # + +--error ER_TOO_LONG_IDENT +SELECT very_long_fn_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999(); + +--error ER_TOO_LONG_IDENT +CALL very_long_pr_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999(); + +--error ER_WRONG_DB_NAME +SELECT very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_func(); + +--error ER_WRONG_DB_NAME +CALL very_long_db_name_1111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222225555555555555555555555555577777777777777777777777777777777777777777777777777777777777777777777777788888888999999999999999999999.simple_proc(); + +--error ER_TOO_LONG_IDENT +SELECT db_name.very_long_fn_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999(); + +--error ER_TOO_LONG_IDENT +CALL db_name.very_long_pr_name_111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222999999999999999999999(); --echo End of 5.1 tests diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index 96f82c92248..b7a99ad4103 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -926,6 +926,43 @@ DROP DATABASE B48872; --echo End of 5.0 tests. +--echo # +--echo # Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE +--echo # DEFINITION OF ANY ROUTINE. +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +--enable_warnings + +CREATE DATABASE db1; +CREATE PROCEDURE db1.p1() SELECT 1; +CREATE USER user2@localhost IDENTIFIED BY ''; +GRANT SELECT(db) ON mysql.proc TO user2@localhost; + +--echo # Connection con2 as user2 +connect (con2, localhost, user2); +--echo # The statement below before disclosed info from body_utf8 column. +--error ER_SP_DOES_NOT_EXIST +SHOW CREATE PROCEDURE db1.p1; + +--echo # Check that SHOW works with SELECT grant on whole table +--echo # Connection default +connection default; +GRANT SELECT ON mysql.proc TO user2@localhost; + +--echo # Connection con2 +connection con2; +--echo # This should work +SHOW CREATE PROCEDURE db1.p1; + +--echo # Connection default +connection default; +disconnect con2; +DROP USER user2@localhost; +DROP DATABASE db1; + + # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index db8077bbbef..4fc48513652 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -8349,6 +8349,53 @@ SET @@GLOBAL.init_connect= @old_init_connect; DROP PROCEDURE p2; DROP PROCEDURE p5; + +--echo # +--echo # Bug#11840395 (formerly known as bug#60347): +--echo # The string "versiondata" seems +--echo # to be 'leaking' into the schema name space +--echo # +--disable_warnings +DROP DATABASE IF EXISTS mixedCaseDbName; +--enable_warnings +CREATE DATABASE mixedCaseDbName; +DELIMITER |; +CREATE PROCEDURE mixedCaseDbName.tryMyProc() begin end| +CREATE FUNCTION mixedCaseDbName.tryMyFunc() returns text begin return 'IT WORKS'; end +| +DELIMITER ;| +call mixedCaseDbName.tryMyProc(); +select mixedCaseDbName.tryMyFunc(); +DROP DATABASE mixedCaseDbName; + + +--echo # +--echo # Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C +--echo # + +CREATE TABLE t1 (a INT, b INT, KEY(b)); +CREATE TABLE t2 (c INT, d INT, KEY(c)); +INSERT INTO t1 VALUES (1,1),(1,1),(1,2); +INSERT INTO t2 VALUES (1,1),(1,2); + +DELIMITER $; + +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN + DECLARE a int; + -- SQL statement inside + SELECT 1 INTO a; + RETURN a; +END $ + +DELIMITER ;$ + +SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1(); + +DROP FUNCTION f1; +DROP TABLE t1, t2; + + --echo # ------------------------------------------------------------------ --echo # -- End of 5.1 tests --echo # ------------------------------------------------------------------ diff --git a/mysql-test/t/sp_sync.test b/mysql-test/t/sp_sync.test index f9dae17b039..d8458f69eef 100644 --- a/mysql-test/t/sp_sync.test +++ b/mysql-test/t/sp_sync.test @@ -1,7 +1,10 @@ # This test should work in embedded server after mysqltest is fixed -- source include/not_embedded.inc ---echo Tests of syncronization of stored procedure execution. +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--echo Tests of synchronization of stored procedure execution. --source include/have_debug_sync.inc @@ -54,5 +57,31 @@ connection default; DROP TABLE t1, t2; DROP PROCEDURE p1; +--echo # +--echo # test for bug#11756013 +--echo # +--disable_warnings +DROP SCHEMA IF EXISTS s1; +--enable_warnings +CREATE SCHEMA s1; +CREATE PROCEDURE s1.p1() BEGIN END; + +connect (con3, localhost, root); +SET DEBUG_SYNC='before_db_dir_check SIGNAL check_db WAIT_FOR dropped_schema'; +--send CALL s1.p1 + +connection default; +SET DEBUG_SYNC='now WAIT_FOR check_db'; +DROP SCHEMA s1; +SET DEBUG_SYNC='now SIGNAL dropped_schema'; + +connection con3; +--error ER_BAD_DB_ERROR +--reap +connection default; +disconnect con3; + SET DEBUG_SYNC = 'RESET'; +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 0b04b9d7668..2d59fb20bbd 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -594,6 +594,39 @@ drop table t3| # +# BUG#11758414: Default storage_engine not honored when set +# from within a stored procedure +# +SELECT @@GLOBAL.storage_engine INTO @old_engine| +SET @@GLOBAL.storage_engine=InnoDB| +SET @@SESSION.storage_engine=InnoDB| +# show defaults at define-time +SHOW GLOBAL VARIABLES LIKE 'storage_engine'| +SHOW SESSION VARIABLES LIKE 'storage_engine'| +CREATE PROCEDURE bug11758414() +BEGIN + SET @@GLOBAL.storage_engine="MyISAM"; + SET @@SESSION.storage_engine="MyISAM"; + # show defaults at execution time / that setting them worked + SHOW GLOBAL VARIABLES LIKE 'storage_engine'; + SHOW SESSION VARIABLES LIKE 'storage_engine'; + CREATE TABLE t1 (id int); + CREATE TABLE t2 (id int) ENGINE=InnoDB; + # show we're heeding the default (at run-time, not parse-time!) + SHOW CREATE TABLE t1; + # show that we didn't break explicit override with ENGINE=... + SHOW CREATE TABLE t2; +END; +| +CALL bug11758414| +# show that changing defaults within SP stuck +SHOW GLOBAL VARIABLES LIKE 'storage_engine'| +SHOW SESSION VARIABLES LIKE 'storage_engine'| +DROP PROCEDURE bug11758414| +DROP TABLE t1, t2| +SET @@GLOBAL.storage_engine=@old_engine| + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 03504050f65..b918e800dd5 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3447,6 +3447,39 @@ ORDER BY outr.pk; DROP TABLE t1,t2; +--echo # +--echo # Bug#12329653 +--echo # EXPLAIN, UNION, PREPARED STATEMENT, CRASH, SQL_FULL_GROUP_BY +--echo # + +CREATE TABLE t1(a1 int); +INSERT INTO t1 VALUES (1),(2); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +## First a simpler query, illustrating the transformation +## '1 < some (...)' => '1 < max(...)' +SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1); + +## The query which made the server crash. +PREPARE stmt FROM +'SELECT 1 UNION ALL +SELECT 1 FROM t1 +ORDER BY +(SELECT 1 FROM t1 AS t1_0 + WHERE 1 < SOME (SELECT a1 FROM t1) +)' ; + +--error ER_SUBQUERY_NO_1_ROW +EXECUTE stmt ; +--error ER_SUBQUERY_NO_1_ROW +EXECUTE stmt ; + +SET SESSION sql_mode=@old_sql_mode; + +DEALLOCATE PREPARE stmt; +DROP TABLE t1; --echo End of 5.0 tests. diff --git a/mysql-test/t/trigger-compat.test b/mysql-test/t/trigger-compat.test index 2cc86692d0d..216e64f8543 100644 --- a/mysql-test/t/trigger-compat.test +++ b/mysql-test/t/trigger-compat.test @@ -106,4 +106,178 @@ DROP TABLE t2; DROP USER mysqltest_dfn@localhost; DROP USER mysqltest_inv@localhost; DROP DATABASE mysqltest_db1; +USE test; + +--echo # +--echo # Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way +--echo # +let $MYSQLD_DATADIR=`SELECT @@datadir`; + +--disable_warnings +DROP TABLE IF EXISTS t1, t2, t3; +--enable_warnings + +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( a INT ); +CREATE TABLE t3 ( a INT ); +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1), (2), (3); +INSERT INTO t3 VALUES (1), (2), (3); + +--echo # We simulate importing a trigger from 5.0 by writing a .TRN file for +--echo # each trigger plus a .TRG file the way MySQL 5.0 would have done it, +--echo # with syntax allowed in 5.0 only. +--echo # +--echo # Note that in 5.0 the following lines are missing from t1.TRG: +--echo # +--echo # client_cs_names='latin1' +--echo # connection_cl_names='latin1_swedish_ci' +--echo # db_cl_names='latin1_swedish_ci' + +--write_file $MYSQLD_DATADIR/test/tr11.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr12.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr13.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr14.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr15.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/t1.TRG +TYPE=TRIGGERS +triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr12 AFTER INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr13 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr14 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM non_existing_table' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr15 BEFORE UPDATE ON t1 FOR EACH ROW DELETE FROM non_existing_table a USING non_existing_table a' +sql_modes=0 0 0 0 0 +definers='root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' +EOF + +--write_file $MYSQLD_DATADIR/test/t2.TRG +TYPE=TRIGGERS +triggers='Not allowed syntax here, and trigger name cant be extracted either.' +sql_modes=0 +definers='root@localhost' +EOF + +--echo # We will get parse errors for most DDL and DML statements when the table +--echo # has broken triggers. The parse error refers to the first broken +--echo # trigger. +--error ER_PARSE_ERROR +CREATE TRIGGER tr16 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t1 VALUES (1); +--error ER_PARSE_ERROR +CREATE TRIGGER tr22 BEFORE INSERT ON t2 FOR EACH ROW DELETE FROM non_existing_table; +SHOW TRIGGERS; +--error ER_PARSE_ERROR +INSERT INTO t1 VALUES (1); +--error ER_PARSE_ERROR +INSERT INTO t2 VALUES (1); +--error ER_PARSE_ERROR +DELETE FROM t1; +--error ER_PARSE_ERROR +UPDATE t1 SET a = 1 WHERE a = 1; +SELECT * FROM t1; +--error ER_PARSE_ERROR +RENAME TABLE t1 TO t1_2; +SHOW TRIGGERS; + +DROP TRIGGER tr11; +DROP TRIGGER tr12; +DROP TRIGGER tr13; +DROP TRIGGER tr14; +DROP TRIGGER tr15; + +SHOW TRIGGERS; + +--echo # Make sure there is no trigger file left. +--list_files $MYSQLD_DATADIR/test/ tr* + +--echo # We write the same trigger files one more time to test DROP TABLE. +--write_file $MYSQLD_DATADIR/test/tr11.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr12.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr13.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr14.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr15.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/t1.TRG +TYPE=TRIGGERS +triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr12 AFTER INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr13 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr14 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM non_existing_table' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr15 BEFORE UPDATE ON t1 FOR EACH ROW DELETE FROM non_existing_table a USING non_existing_table a' +sql_modes=0 0 0 0 0 +definers='root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' +EOF + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; + +--echo # Make sure there is no trigger file left. + +--list_files $MYSQLD_DATADIR/test/ tr* + +CREATE TABLE t1 ( a INT ); +CREATE TABLE t2 ( a INT ); +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1), (2), (3); + +--echo # We write three trigger files. First trigger is syntaxically incorrect, next trigger is correct +--echo # and last trigger is broken. +--echo # Next we try to execute SHOW CREATE TRGGIR command for broken trigger and then try to drop one. +--write_file $MYSQLD_DATADIR/test/tr11.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/tr12.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +--write_file $MYSQLD_DATADIR/test/t1.TRG +TYPE=TRIGGERS +triggers='CREATE the wrongest trigger_in_the_world' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr12 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t2' +sql_modes=0 0 0 +definers='root@localhost' 'root@localhost' 'root@localhost' +EOF + +FLUSH TABLE t1; + +SHOW CREATE TRIGGER tr12; +SHOW CREATE TRIGGER tr11; +DROP TRIGGER tr12; +DROP TRIGGER tr11; + +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/t/type_float.test b/mysql-test/t/type_float.test index 3b7b30db6f8..cb929702c0e 100644 --- a/mysql-test/t/type_float.test +++ b/mysql-test/t/type_float.test @@ -276,4 +276,21 @@ INSERT INTO t1 VALUES(-1.79769313486231e+308); SELECT f1 FROM t1; DROP TABLE t1; +--echo # +--echo # Bug#12406055 BUFFER OVERFLOW OF VARIABLE 'BUFF' IN STRING::SET_REAL +--echo # + +--echo # Ignoring output from misc. float operations +--disable_result_log + +let $nine_65= +99999999999999999999999999999999999999999999999999999999999999999; + +select format(-1.7976931348623157E+307,256) as foo; +select least(-1.1111111111111111111111111, + - group_concat(1.7976931348623157E+308)) as foo; +eval select concat((truncate((-1.7976931348623157E+307),(0x1e))), + ($nine_65)) into @a; +--enable_result_log + --echo End of 5.0 tests diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 2cf7ab8fbdf..475be355874 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1245,6 +1245,27 @@ show create table t1; select * from t1; DROP TABLE t1; +--echo # +--echo # Bug#12563865 +--echo # ROUNDED,TMP_BUF,DECIMAL_VALUE STACK CORRUPTION IN ALL VERSIONS >=5.0 +--echo # + +let $nine_81= +999999999999999999999999999999999999999999999999999999999999999999999999999999999; + +eval SELECT substring(('M') FROM ($nine_81)) AS foo; +eval SELECT min($nine_81) AS foo; +eval SELECT multipolygonfromtext(('4294967294.1'),($nine_81)) AS foo; +eval SELECT convert(($nine_81), decimal(30,30)) AS foo; +eval SELECT bit_xor($nine_81) AS foo; +eval SELECT -($nine_81) AS foo; +eval SELECT date_sub(($nine_81), + interval ((SELECT date_add((0x77500000), + interval ('Oml') second))) + day_minute) +AS foo; +eval SELECT truncate($nine_81, 28) AS foo; + --echo End of 5.0 tests # @@ -1510,5 +1531,33 @@ group by PAY.id + 1; DROP TABLE currencies, payments, sub_tasks; +--echo # +--echo # Bug#55436: buffer overflow in debug binary of dbug_buff in +--echo # Field_new_decimal::store_value +--echo # + +# this threw memory warnings on Windows. Also make sure future changes +# don't change these results, as per usual. +SET SQL_MODE=''; +CREATE TABLE t1(f1 DECIMAL(44,24)) ENGINE=MYISAM; +INSERT INTO t1 SET f1 = -64878E-85; +SELECT f1 FROM t1; +DROP TABLE IF EXISTS t1; + + --echo End of 5.1 tests + +--echo # +--echo # BUG#12911710 - VALGRIND FAILURE IN +--echo # ROW-DEBUG:PERFSCHEMA.SOCKET_SUMMARY_BY_INSTANCE_FUNC +--echo # + +CREATE TABLE t1(d1 DECIMAL(60,0) NOT NULL, + d2 DECIMAL(60,0) NOT NULL); + +INSERT INTO t1 (d1, d2) VALUES(0.0, 0.0); +SELECT d1 * d2 FROM t1; + +DROP TABLE t1; + diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 34bb4afc41c..507f0ffa09f 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1164,4 +1164,45 @@ insert into t1 values (10),(10),(10),(2),(3),(4),(5),(6),(7),(8),(9),(1),(10); select a from t1 where false UNION select a from t1 limit 8; drop table t1; +--echo # +--echo # Bug#11765255 58201: +--echo # VALGRIND/CRASH WHEN ORDERING BY MULTIPLE AGGREGATE FUNCTIONS +--echo # + +let $my_stmt= +select 1 as foo +union +select 2 +union +select 3 +union +select 4 +order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1) +; + +eval $my_stmt; + +eval prepare stmt1 from '$my_stmt'; +execute stmt1; +execute stmt1; + +let $my_stmt= +select 1 as foo +union +select 2 +union +select 3 +union +(select 4) +order by max(42) + max(1) + max(1) + max(1) + max(1) + max(1) +; + +eval $my_stmt; + +eval prepare stmt1 from '$my_stmt'; +execute stmt1; +execute stmt1; + +deallocate prepare stmt1; + --echo End of 5.1 tests diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 21c6f376f8a..8b91d6284aa 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -127,21 +127,26 @@ create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1; create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; +# v5: SHOW VIEW, but no SELECT +create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1; grant select on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.v2 to mysqltest_1@localhost; grant select on mysqltest.v3 to mysqltest_1@localhost; grant select on mysqltest.v4 to mysqltest_1@localhost; +grant show view on mysqltest.v5 to mysqltest_1@localhost; connection user1; -# all selects works +# all SELECTs works, except v5 which lacks SELECT privs select c from mysqltest.v1; select c from mysqltest.v2; select c from mysqltest.v3; select c from mysqltest.v4; +--error ER_TABLEACCESS_DENIED_ERROR +select c from mysqltest.v5; # test of show coluns show columns from mysqltest.v1; show columns from mysqltest.v2; -# but explain/show do not +# explain/show fail --error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v1; --error ER_TABLEACCESS_DENIED_ERROR @@ -158,15 +163,26 @@ show create view mysqltest.v3; explain select c from mysqltest.v4; --error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v4; +--error ER_TABLEACCESS_DENIED_ERROR +explain select c from mysqltest.v5; +show create view mysqltest.v5; +# missing SELECT on underlying t1, no SHOW VIEW on v1 either. +--error ER_VIEW_NO_EXPLAIN +explain select c from mysqltest.v1; +# missing SHOW VIEW +--error ER_TABLEACCESS_DENIED_ERROR +show create view mysqltest.v1; # allow to see one of underlying table connection root; +grant show view on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.t1 to mysqltest_1@localhost; connection user1; -# EXPLAIN of view on above table works +# EXPLAIN works explain select c from mysqltest.v1; ---error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v1; +# missing SHOW VIEW +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v2; --error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v2; @@ -179,6 +195,11 @@ show create view mysqltest.v3; explain select c from mysqltest.v4; --error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v4; +# we have SHOW VIEW on v5, and SELECT on t1 -- not enough +--error ER_TABLEACCESS_DENIED_ERROR +explain select c from mysqltest.v5; +# we can SHOW CREATE VIEW though +show create view mysqltest.v5; # allow to see any view in mysqltest database connection root; @@ -188,8 +209,12 @@ explain select c from mysqltest.v1; show create view mysqltest.v1; explain select c from mysqltest.v2; show create view mysqltest.v2; +# have SHOW VIEW | SELECT on v3, but no SELECT on t2 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v3; show create view mysqltest.v3; +# have SHOW VIEW | SELECT on v4, but no SELECT on t2 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v4; show create view mysqltest.v4; @@ -1237,6 +1262,334 @@ DROP VIEW db1.v1; DROP TABLE db1.t1; DROP DATABASE db1; +connection default; + + +--echo Bug #11765687/#58677: +--echo No privilege on table/view, but can know #rows / underlying table's name + +# As a root-like user +connect (root,localhost,root,,test); +connection root; + +create database mysqltest1; +create table mysqltest1.t1 (i int); +create table mysqltest1.t2 (j int); +create table mysqltest1.t3 (k int, secret int); + +create user alice@localhost; +create user bob@localhost; +create user cecil@localhost; +create user dan@localhost; +create user eugene@localhost; +create user fiona@localhost; +create user greg@localhost; +create user han@localhost; +create user inga@localhost; +create user jamie@localhost; +create user karl@localhost; +create user lena@localhost; +create user mhairi@localhost; +create user noam@localhost; +create user olga@localhost; +create user pjotr@localhost; +create user quintessa@localhost; + +grant all privileges on mysqltest1.* to alice@localhost with grant option; + +# +--echo ... as alice +connect (test11765687,localhost,alice,,mysqltest1); +connection test11765687; + +create view v1 as select * from t1; +create view v2 as select * from v1, t2; +create view v3 as select k from t3; + +grant select on mysqltest1.v1 to bob@localhost; + +grant show view on mysqltest1.v1 to cecil@localhost; + +grant select, show view on mysqltest1.v1 to dan@localhost; +grant select on mysqltest1.t1 to dan@localhost; + +grant select on mysqltest1.* to eugene@localhost; + +grant select, show view on mysqltest1.v2 to fiona@localhost; + +grant select, show view on mysqltest1.v2 to greg@localhost; +grant show view on mysqltest1.v1 to greg@localhost; + +grant select(k) on mysqltest1.t3 to han@localhost; +grant select, show view on mysqltest1.v3 to han@localhost; + +grant select on mysqltest1.t1 to inga@localhost; +grant select on mysqltest1.t2 to inga@localhost; +grant select on mysqltest1.v1 to inga@localhost; +grant select, show view on mysqltest1.v2 to inga@localhost; + +grant select on mysqltest1.t1 to jamie@localhost; +grant select on mysqltest1.t2 to jamie@localhost; +grant show view on mysqltest1.v1 to jamie@localhost; +grant select, show view on mysqltest1.v2 to jamie@localhost; + +grant select on mysqltest1.t1 to karl@localhost; +grant select on mysqltest1.t2 to karl@localhost; +grant select, show view on mysqltest1.v1 to karl@localhost; +grant select on mysqltest1.v2 to karl@localhost; + +grant select on mysqltest1.t1 to lena@localhost; +grant select on mysqltest1.t2 to lena@localhost; +grant select, show view on mysqltest1.v1 to lena@localhost; +grant show view on mysqltest1.v2 to lena@localhost; + +grant select on mysqltest1.t1 to mhairi@localhost; +grant select on mysqltest1.t2 to mhairi@localhost; +grant select, show view on mysqltest1.v1 to mhairi@localhost; +grant select, show view on mysqltest1.v2 to mhairi@localhost; + +grant select on mysqltest1.t1 to noam@localhost; +grant select, show view on mysqltest1.v1 to noam@localhost; +grant select, show view on mysqltest1.v2 to noam@localhost; + +grant select on mysqltest1.t2 to olga@localhost; +grant select, show view on mysqltest1.v1 to olga@localhost; +grant select, show view on mysqltest1.v2 to olga@localhost; + +grant select on mysqltest1.t1 to pjotr@localhost; +grant select on mysqltest1.t2 to pjotr@localhost; +grant select, show view on mysqltest1.v2 to pjotr@localhost; + +grant select, show view on mysqltest1.v1 to quintessa@localhost; + +disconnect test11765687; + +# +--echo ... as bob +connect (test11765687,localhost,bob,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +--error ER_VIEW_NO_EXPLAIN +explain select * from v1; # fail, no SHOW_VIEW + +disconnect test11765687; + +# +--echo ... as cecil +connect (test11765687,localhost,cecil,,mysqltest1); +connection test11765687; + +--error ER_TABLEACCESS_DENIED_ERROR +select * from v1; # fail, no SELECT +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from v1; # fail, no SELECT + +disconnect test11765687; + +# +--echo ... as dan +connect (test11765687,localhost,dan,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +explain select * from v1; # Should succeed. + +disconnect test11765687; + +# +--echo ... as eugene +connect (test11765687,localhost,eugene,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +--error ER_VIEW_NO_EXPLAIN +explain select * from v1; # fail, no SHOW_VIEW + +disconnect test11765687; + +# +--echo ... as fiona +connect (test11765687,localhost,fiona,,mysqltest1); +connection test11765687; + +select * from v2; # Should succeed. +show create view v2; # Should succeed, but... +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from t1; # fail, shouldn't see t1! +--error ER_TABLEACCESS_DENIED_ERROR +# err msg must give view name, no table names!! +explain select * from v1; # fail, have no privs on v1! +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from t2; # fail, have no privs on t2! +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; # fail, shouldn't see t2! + +disconnect test11765687; + +# +--echo ... as greg +connect (test11765687,localhost,greg,,mysqltest1); +connection test11765687; + +select * from v2; # Should succeed. +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from v1; # fail; no SELECT on v1! +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; # fail; no SELECT on v1! + +disconnect test11765687; + +# +--echo ... as han +connect (test11765687,localhost,han,,mysqltest1); +connection test11765687; + +--error ER_TABLEACCESS_DENIED_ERROR +select * from t3; # don't have privs on all columns, +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from t3; # so EXPLAIN on "forbidden" columns should fail. +select k from t3; # but we do have SELECT on column k though, +explain select k from t3; # so EXPLAIN just on k should work, +select * from v3; # and so should SELECT on view only using allowed columns +explain select * from v3; # as should the associated EXPLAIN + +disconnect test11765687; + +# +--echo ... as inga +connect (test11765687,localhost,inga,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel on t1/t2, only sel v1 +# fail: lacks show on v1 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as jamie +connect (test11765687,localhost,jamie,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel on t1/t2, only show v1 +# fail: lacks sel on v1 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as karl +connect (test11765687,localhost,karl,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel only on v2, sel on t1/t2, sel/show v1 +# fail: lacks show on v2 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as lena + +connect (test11765687,localhost,lena,,mysqltest1); +connection test11765687; +--error ER_TABLEACCESS_DENIED_ERROR +select * from v2; +# has show only on v2, sel on t1/t2, sel/show v1 +# fail: lacks sel on v2 +--error ER_TABLEACCESS_DENIED_ERROR +explain select * from v2; +disconnect test11765687; + +# +--echo ... as mhairi +connect (test11765687,localhost,mhairi,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel on t1/t2, sel/show v1 +explain select * from v2; +disconnect test11765687; + +# +--echo ... as noam +connect (test11765687,localhost,noam,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel only on t1, sel/show v1 (no sel on t2!) +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as olga +connect (test11765687,localhost,olga,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel only on t2, sel/show v1 (no sel on t1!) +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as pjotr +connect (test11765687,localhost,pjotr,,mysqltest1); +connection test11765687; + +select * from v2; +# has sel/show on v2, sel only on t2, nothing on v1 +# fail: lacks show on v1 +--error ER_VIEW_NO_EXPLAIN +explain select * from v2; +disconnect test11765687; + +# +--echo ... as quintessa +connect (test11765687,localhost,quintessa,,mysqltest1); +connection test11765687; + +select * from v1; # Should succeed. +--error ER_VIEW_NO_EXPLAIN +explain select * from v1; # fail: lacks select on t1 + +disconnect test11765687; + +# cleanup + +# +--echo ... as root again at last: clean-up time! +connection root; + +drop user alice@localhost; +drop user bob@localhost; +drop user cecil@localhost; +drop user dan@localhost; +drop user eugene@localhost; +drop user fiona@localhost; +drop user greg@localhost; +drop user han@localhost; +drop user inga@localhost; +drop user jamie@localhost; +drop user karl@localhost; +drop user lena@localhost; +drop user mhairi@localhost; +drop user noam@localhost; +drop user olga@localhost; +drop user pjotr@localhost; +drop user quintessa@localhost; + +drop database mysqltest1; + +disconnect root; + +connection default; + --echo End of 5.0 tests. |