diff options
Diffstat (limited to 'mysql-test/t')
43 files changed, 1217 insertions, 360 deletions
diff --git a/mysql-test/t/ctype_filesystem-master.opt b/mysql-test/t/ctype_filesystem-master.opt new file mode 100644 index 00000000000..44127f68664 --- /dev/null +++ b/mysql-test/t/ctype_filesystem-master.opt @@ -0,0 +1,2 @@ +--character-sets-dir=$MYSQL_TEST_DIR/ß +--character-set-filesystem=latin1 diff --git a/mysql-test/t/ctype_filesystem.test b/mysql-test/t/ctype_filesystem.test new file mode 100644 index 00000000000..2b993c2b924 --- /dev/null +++ b/mysql-test/t/ctype_filesystem.test @@ -0,0 +1,6 @@ +SET CHARACTER SET utf8; +--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR +SHOW VARIABLES like 'character_sets_dir'; +SHOW VARIABLES like 'character_set_filesystem'; +SHOW VARIABLES like 'character_set_client'; +SET CHARACTER SET default; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 2e23ac3661c..f0c769251cf 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1439,3 +1439,20 @@ select hex(_utf8 X'616263FF'); select hex(_utf8 B'001111111111'); --error ER_INVALID_CHARACTER_STRING select (_utf8 X'616263FF'); + +# +# Bug #36772: When using UTF8, CONVERT with GROUP BY returns truncated results +# +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); +INSERT INTO t1 VALUES (70000, 1092), (70001, 1085), (70002, 1065); +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1; +ALTER TABLE t1 ADD UNIQUE (b); +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; +DROP INDEX b ON t1; +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; +ALTER TABLE t1 ADD INDEX (b); +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) from t1 GROUP BY b; +DROP TABLE t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index bf141cbfa41..cc02f1faba3 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -10,7 +10,6 @@ # ############################################################################## federated_transactions : Bug#29523 Transactions do not work -csv_alter_table : Bug#33696 2008-01-21 pcrews no .result file - bug allows NULL columns in CSV tables log_tables.test : Bug #37798: main.log_tables fails randomly on powermacg5 and windows wait_timeout_func : BUG#36873 2008-07-06 sven wait_timeout_func.test fails randomly delayed_insert_limit_func : BUG#37962 2008-07-08 sven *_func tests containing sleeps/race conditions @@ -25,3 +24,4 @@ timestamp_func : BUG#37962 2008-07-08 sven *_func tests c log_output_func : BUG#37766 2008-07-10 sven main.log_output_func randomly fails in pushbuild slow_query_log_func.test : Bug #37962: *_func tests containing sleeps/race conditions innodb_max_dirty_pages_pct_func : BUG#41018 BUG#39382 2008-12-02 sven test fails often. some failures fill up the disk, causing subsequent failures in many other tests +wait_timeout_func : Bug #41225 joro wait_timeout_func fails diff --git a/mysql-test/t/events_scheduling.test b/mysql-test/t/events_scheduling.test index a5133166495..87cfa42e283 100644 --- a/mysql-test/t/events_scheduling.test +++ b/mysql-test/t/events_scheduling.test @@ -1,11 +1,12 @@ -# Can't test with embedded server that doesn't support grants +# Can't test with embedded server that doesn't support events -- source include/not_embedded.inc CREATE DATABASE IF NOT EXISTS events_test; USE events_test; +SET @event_scheduler=@@global.event_scheduler; SET GLOBAL event_scheduler=OFF; ---echo Try agian to make sure it's allowed +--echo Try again to make sure it's allowed SET GLOBAL event_scheduler=OFF; SHOW VARIABLES LIKE 'event_scheduler'; SET GLOBAL event_scheduler=1; @@ -57,34 +58,44 @@ ON COMPLETION PRESERVE DO INSERT INTO table_4 VALUES (1); +# Wait for the events to fire and check the data afterwards + # Let event_1 insert at least 4 records into the table let $wait_condition=select count(*) >= 4 from table_1; --source include/wait_condition.inc +# Minimum of passed time is 6 seconds assuming +# - event executions starts immediate after creation +# - 4 times event_1 means an insert at ect, ect+2, ect+4, ect+6 +# ect = event creation time -# Let event_2 reach the end of its execution interval +# Let event_2 reach the end of its execution interval let $wait_condition=select count(*) = 0 from information_schema.events where event_name='event_2' and status='enabled'; --source include/wait_condition.inc +# Minimum of passed time is 6 seconds. +# See wait_condition for event_1 above and ENDS condition for event_2. -# Let event_3, which is ON COMPLETION NOT PRESERVE execute and drop itself +# Let event_3, which is ON COMPLETION NOT PRESERVE execute and drop itself let $wait_condition=select count(*) = 0 from information_schema.events where event_name='event_3'; --source include/wait_condition.inc -# Let event_4 reach the end of its execution interval +# Let event_4 reach the end of its execution interval let $wait_condition=select count(*) = 0 from information_schema.events where event_name='event_4' and status='enabled'; --source include/wait_condition.inc -# Wait for the events to fire and check the data afterwards - let $wait_condition=SELECT SUM(a) >= 4 FROM table_1; source include/wait_condition.inc; SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_1; -let $wait_condition=SELECT SUM(a) >= 5 FROM table_2; +# In case of a testing box under heavy load it cannot be guaranteed that +# it is really often enough checked if event_2 has to be executed. +# -> Bug#39854 events_scheduling fails sporadically on pushbuild +# Therefore we lowered here the original expectation of 5 to 4. +let $wait_condition=SELECT SUM(a) >= 4 FROM table_2; source include/wait_condition.inc; -SELECT IF(SUM(a) >= 5, 'OK', 'ERROR') FROM table_2; +SELECT IF(SUM(a) >= 4, 'OK', 'ERROR') FROM table_2; let $wait_condition=SELECT SUM(a) >= 1 FROM table_3; source include/wait_condition.inc; @@ -112,9 +123,9 @@ DROP TABLE table_2; DROP TABLE table_3; DROP TABLE table_4; DROP DATABASE events_test; -SET GLOBAL event_scheduler=OFF; +SET GLOBAL event_scheduler=@event_scheduler; -# +# # End of tests # diff --git a/mysql-test/t/events_time_zone.test b/mysql-test/t/events_time_zone.test index af3466a339c..ccae7847618 100644 --- a/mysql-test/t/events_time_zone.test +++ b/mysql-test/t/events_time_zone.test @@ -1,11 +1,40 @@ -# This test case is sensitive to execution timing. You may control -# this sensitivity by the parameter below. Small values will result -# in fast but more unstable execution, large values will improve -# stability at the cost of speed. Basically, N is a number of seconds -# to wait for operation to complete. Should be positive. Test runs -# about 25*N seconds (it sleeps most of the time, so CPU speed is not -# relevant). +# 1. This test case is sensitive to execution timing. You may control +# this sensitivity by the parameter below. Small values will result +# in fast but more unstable execution, large values will improve +# stability at the cost of speed. Basically, N is a number of seconds +# to wait for operation to complete. Should be positive. Test runs +# about 25*N seconds (it sleeps most of the time, so CPU speed is not +# relevant). let $N = 5; +# +# 2. Some subtests +# - create a new time zone +# - run some statements +# - delete the new time zone. +# But the time zone name used gets somewhere cached and it cannot be +# "reused" later in the same or another session for a new time zone. +# Experiments (2008-11 MySQL 5.1) showed that none of the available +# RESET/FLUSH commands removes these entries. +# 2008-11 MySQL 5.1 Bug#39979 main.events_time_zone does not clean up +# second bad effect +# Therefore we compute unique and unusual timezone names to minimize +# the likelihood that a later test uses the same name. +# +# 3. The subtests mentioned in 2. cause that the AUTO_INCREMENT value +# within "SHOW CREATE TABLE mysql.timezone" differ from the initial one. +# (Bug#39979 main.events_time_zone does not clean up) +# Therefore we reset this value after each of these subtests. +# +# Note(mleich): +# There is a significant likelihood that future improvements of the server +# cause that the solutions for the issues mentioned in 2. and 3. will no +# more work. +# A mysql-test-run.pl feature which allows to enforce +# 1. Server shutdown (-> Problem mentioned in 2. disappears) +# 2. Reset all data to initial state (-> Problem mentioned in 3. disappears) +# 3. Server start +# after a tests would be a perfect replacement. +# --source include/big_test.inc @@ -73,19 +102,15 @@ delimiter ;// SET @step3= @step * 3; SET @step6= @step * 6; -# Disable query log to hide current time. ---disable_query_log SET @unix_time= UNIX_TIMESTAMP() - 1; ---enable_query_log - SET @unix_time= @unix_time - @unix_time % @step6; INSERT INTO mysql.time_zone VALUES (NULL, 'N'); SET @tzid= LAST_INSERT_ID(); INSERT INTO mysql.time_zone_transition_type - VALUES (@tzid, 0, 0, 0, 'b16420_0'); + VALUES (@tzid, 0, 0, 0, 'b16420_0'); INSERT INTO mysql.time_zone_transition_type - VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1'); + VALUES (@tzid, 1, @step3 - @step, 1, 'b16420_1'); let $transition_unix_time= `SELECT @unix_time`; let $count= 30; @@ -99,7 +124,9 @@ while ($count) dec $count; } --enable_query_log -INSERT INTO mysql.time_zone_name VALUES ('bug16420', @tzid); +let $tz_name = `SELECT CONCAT('b16420_a',UNIX_TIMESTAMP())`; +--replace_result $tz_name <TZ_NAME_1> +eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid); CREATE TABLE t1 (count INT, unix_time INT, local_time INT, comment CHAR(80)); CREATE TABLE t2 (count INT); @@ -135,7 +162,8 @@ SET TIME_ZONE= '+00:00'; CREATE EVENT e1 ON SCHEDULE EVERY @step SECOND STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e1>"); -SET TIME_ZONE= 'bug16420'; +--replace_result $tz_name <TZ_NAME_1> +eval SET TIME_ZONE= '$tz_name'; CREATE EVENT e2 ON SCHEDULE EVERY @step SECOND STARTS FROM_UNIXTIME(@unix_time) DO SELECT f1("<e2>"); @@ -196,6 +224,8 @@ DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; +let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`; +eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc; #---------------------------------------------------------------------- @@ -216,13 +246,13 @@ SET @offset_month_03= UNIX_TIMESTAMP('2030-03-31 12:00:00') - @now - 5*@step; SET @offset_month_04= UNIX_TIMESTAMP('2030-04-30 12:00:00') - @now - 13*@step; INSERT INTO mysql.time_zone_transition_type - VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0'); + VALUES (@tzid, 0, @offset_month_01, 0, 'b16420_0'); INSERT INTO mysql.time_zone_transition_type - VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1'); + VALUES (@tzid, 1, @offset_month_02, 1, 'b16420_1'); INSERT INTO mysql.time_zone_transition_type - VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2'); + VALUES (@tzid, 2, @offset_month_03, 1, 'b16420_2'); INSERT INTO mysql.time_zone_transition_type - VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3'); + VALUES (@tzid, 3, @offset_month_04, 1, 'b16420_3'); INSERT INTO mysql.time_zone_transition VALUES (@tzid, @now, 0); INSERT INTO mysql.time_zone_transition @@ -231,11 +261,12 @@ INSERT INTO mysql.time_zone_transition VALUES (@tzid, @now + 7 * @step, 2); INSERT INTO mysql.time_zone_transition VALUES (@tzid, @now + 12 * @step, 3); -# We have to user a new time zone name, because 'bug16420' has been -# cached already. -INSERT INTO mysql.time_zone_name VALUES ('bug16420_2', @tzid); +let $tz_name = `SELECT CONCAT('b16420_b',UNIX_TIMESTAMP())`; +--replace_result $tz_name <TZ_NAME_2> +eval INSERT INTO mysql.time_zone_name VALUES ('$tz_name', @tzid); -SET TIME_ZONE= 'bug16420_2'; +--replace_result $tz_name <TZ_NAME_2> +eval SET TIME_ZONE= '$tz_name'; SET GLOBAL EVENT_SCHEDULER= ON; @@ -280,6 +311,8 @@ DELETE FROM mysql.time_zone_name WHERE time_zone_id = @tzid; DELETE FROM mysql.time_zone_transition_type WHERE time_zone_id = @tzid; DELETE FROM mysql.time_zone_transition WHERE time_zone_id = @tzid; DELETE FROM mysql.time_zone WHERE time_zone_id = @tzid; +let $time_zone_auto_inc = `SELECT MAX(Time_zone_id) + 1 FROM mysql.time_zone`; +eval ALTER TABLE mysql.time_zone AUTO_INCREMENT = $time_zone_auto_inc; DROP FUNCTION round_to_step; DROP TABLE t_step; @@ -291,8 +324,8 @@ eval USE $old_db; --enable_query_log let $wait_condition= - select count(*) = 0 from information_schema.processlist - where db='mysqltest_db1' and command = 'Connect' and user=current_user(); + SELECT COUNT(*) = 0 FROM information_schema.processlist + WHERE db='mysqltest_db1' AND command = 'Connect' AND user=current_user(); --source include/wait_condition.inc --echo End of 5.1 tests. diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 64f77b4f0b7..21b06ca0882 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -418,3 +418,30 @@ SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE); DROP TABLE t1; # End of 4.1 tests + +# +# BUG#38842 - Fix for 25951 seems incorrect +# +CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b)); +INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1), + ('test', 1),('test', 2),('test', 3),('test', 4); + +EXPLAIN SELECT * FROM t1 +WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; + +EXPLAIN SELECT * FROM t1 USE INDEX(a) +WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; + +EXPLAIN SELECT * FROM t1 FORCE INDEX(a) +WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; + +EXPLAIN SELECT * FROM t1 IGNORE INDEX(a) +WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; + +EXPLAIN SELECT * FROM t1 USE INDEX(b) +WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; + +EXPLAIN SELECT * FROM t1 FORCE INDEX(b) +WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; + +DROP TABLE t1; diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index 68f07f258bf..7f17fd2180f 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -43,8 +43,11 @@ drop table t1; # note that when LOW_MEMORY is set the "test" below is meaningless # -set @@max_allowed_packet=1048576*100; +set @@global.max_allowed_packet=1048576*100; --replace_result "''" XXX "'1'" XXX + +# reconnect to make the new max packet size take effect +--connect (newconn, localhost, root,,) eval select compress(repeat('aaaaaaaaaa', IF('$LOW_MEMORY', 10, 10000000))) is null; # diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 4eedd433d34..be4e9c32686 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -973,5 +973,34 @@ GROUP BY int_nokey LIMIT 1; DROP TABLE derived1; DROP TABLE D; +# +# Bug #39656: Behaviour different for agg functions with & without where - +# ONLY_FULL_GROUP_BY +# + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1), (1,2), (1,3); + +SET SQL_MODE='ONLY_FULL_GROUP_BY'; + +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t1 where a=1; + +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +SELECT COUNT(*),a FROM t1; + +SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a; + +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) + FROM t1 outr; + +SELECT COUNT(*) FROM t1 a JOIN t1 outr + ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a); + +SET SQL_MODE=default; +DROP TABLE t1; + + ### --echo End of 5.0 tests diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test index 5db6db70e8f..f37ee0c39f0 100644 --- a/mysql-test/t/func_sapdb.test +++ b/mysql-test/t/func_sapdb.test @@ -135,3 +135,20 @@ select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f"); --enable_ps_protocol # End of 4.1 tests + + + +# +# Bug#37553: MySql Error Compare TimeDiff & Time +# + +# calculations involving negative time values ignored sign +select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10'),time('00:00:00'); +select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00'); +select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00'); + +# show that conversion to DECIMAL no longer drops sign +SELECT CAST(time('-73:42:12') AS DECIMAL); + + +# End of 5.0 tests diff --git a/mysql-test/t/general_log_func.test b/mysql-test/t/general_log_func.test index 20b875388c1..24d535e88e5 100644 --- a/mysql-test/t/general_log_func.test +++ b/mysql-test/t/general_log_func.test @@ -79,7 +79,7 @@ INSERT into t1(name) values('Record_4'); --chmod 0777 $MYSQLD_LOGFILE.orig --echo ## There should be a difference ## -SET @@session.max_allowed_packet= 1024*1024*1024; +SET @@global.max_allowed_packet= 1024*1024*1024; --replace_result $MYSQLD_LOGFILE MYSQLD_LOGFILE eval SET @orig_file= load_file('$MYSQLD_LOGFILE.orig'); --replace_result $MYSQLD_LOGFILE MYSQLD_LOGFILE diff --git a/mysql-test/t/group_concat_max_len_func.test b/mysql-test/t/group_concat_max_len_func.test index d1dc8df43a0..29a4ff5becb 100644 --- a/mysql-test/t/group_concat_max_len_func.test +++ b/mysql-test/t/group_concat_max_len_func.test @@ -11,18 +11,23 @@ # Creation Date: 2008-03-07 # # Author: Salman Rawala # # # +# Last modification: # +# 2008-11-14 mleich Fix Bug#40644 main.group_concat_max_len_func random # +# failures # +# + minor improvements # +# # # Description: Test Cases of Dynamic System Variable group_concat_max_len # # that checks the functionality of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### SET @save = @@global.group_concat_max_len; --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings ######################### @@ -32,34 +37,38 @@ drop table if exists t1; --echo ## Creating new table t1 ## CREATE TABLE t1 ( -id INT NOT NULL auto_increment, +id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), -rollno int NOT NULL, +rollno INT NOT NULL, name VARCHAR(30) ); --echo '#--------------------FN_DYNVARS_034_01-------------------------#' ######################################################################## # Setting initial value of group_concat_max_len, inserting some rows -# & creating 2 new connections +# & creating 2 new connections ######################################################################## ---echo ## Setting initial value of variable to 4 ## +--echo ## Setting initial value of variable to 4 ## SET @@global.group_concat_max_len = 4; ---echo ## Inserting some rows in table ## -INSERT into t1(rollno, name) values(1, 'Record_1'); -INSERT into t1(rollno, name) values(2, 'Record_2'); -INSERT into t1(rollno, name) values(1, 'Record_3'); -INSERT into t1(rollno, name) values(3, 'Record_4'); -INSERT into t1(rollno, name) values(1, 'Record_5'); -INSERT into t1(rollno, name) values(3, 'Record_6'); -INSERT into t1(rollno, name) values(4, 'Record_7'); -INSERT into t1(rollno, name) values(4, 'Record_8'); - ---echo ## Creating two new connections ## -CONNECT (test_con1,localhost,root,,); -CONNECT (test_con2,localhost,root,,); +--echo ## Inserting some rows in table ## +INSERT INTO t1(rollno, name) VALUES(1, 'Record_1'); +INSERT INTO t1(rollno, name) VALUES(2, 'Record_2'); +INSERT INTO t1(rollno, name) VALUES(1, 'Record_3'); +INSERT INTO t1(rollno, name) VALUES(3, 'Record_4'); +INSERT INTO t1(rollno, name) VALUES(1, 'Record_5'); +INSERT INTO t1(rollno, name) VALUES(3, 'Record_6'); +INSERT INTO t1(rollno, name) VALUES(4, 'Record_7'); +INSERT INTO t1(rollno, name) VALUES(4, 'Record_8'); +# The following "auxiliary" select ensures that all records are on disk +# = result sets got by parallel sessions cannot suffer from effects +# caused by the MyISAM feature "concurrent_inserts". +SELECT * FROM t1 ORDER BY id; + +--echo ## Creating two new connections ## +connect (test_con1,localhost,root,,); +connect (test_con2,localhost,root,,); --echo '#--------------------FN_DYNVARS_034_02-------------------------#' @@ -68,16 +77,16 @@ CONNECT (test_con2,localhost,root,,); ############################################################################### --echo ## Connecting with test_con1 ## -CONNECTION test_con1; +connection test_con1; ---echo ## Accessing data and using group_concat on column whose value is greater than 4 ## -SELECT id, rollno, group_concat(name) FROM t1 GROUP BY rollno; +--echo ## Accessing data and using group_concat on column whose value is greater than 4 ## +SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; --echo ## Changing session value of variable and verifying its behavior, ## ---echo ## warning should come here ## +--echo ## warning should come here ## SET @@session.group_concat_max_len = 10; -SELECT id, rollno, group_concat(name) FROM t1 GROUP BY rollno; +SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; --echo '#--------------------FN_DYNVARS_034_03-------------------------#' @@ -85,19 +94,19 @@ SELECT id, rollno, group_concat(name) FROM t1 GROUP BY rollno; # Verifying behavior of variable by increasing session value of variable # ############################################################################## ---echo ## Connecting with new connection test_con2 ## +--echo ## Connecting with new connection test_con2 ## connection test_con2; ---echo ## Verifying initial value of variable. It should be 4 ## +--echo ## Verifying initial value of variable. It should be 4 ## SELECT @@session.group_concat_max_len = 4; ---echo ## Setting session value of variable to 20 and verifying variable is concating ## ---echo ## column's value to 20 or not ## +--echo ## Setting session value of variable to 20 and verifying variable is concating ## +--echo ## column's value to 20 or not ## SET @@session.group_concat_max_len = 20; --echo ## Verifying value of name column, it should not me more than 20 characters ## --echo ## Warning should come here ## -SELECT id, rollno, group_concat(name) FROM t1 GROUP BY rollno; +SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; --echo '#--------------------FN_DYNVARS_034_04-------------------------#' @@ -106,12 +115,12 @@ SELECT id, rollno, group_concat(name) FROM t1 GROUP BY rollno; # greater than the maximum concat length of name column # ############################################################################### ---echo ## Setting session value of variable to 26. No warning should appear here ## ---echo ## because the value after concatination is less than 30 ## +--echo ## Setting session value of variable to 26. No warning should appear here ## +--echo ## because the value after concatination is less than 30 ## SET @@session.group_concat_max_len = 26; ---echo ## Verifying value of name column, it should not give warning now ## -SELECT id, rollno, group_concat(name) FROM t1 GROUP BY rollno; +--echo ## Verifying value of name column, it should not give warning now ## +SELECT id, rollno, GROUP_CONCAT(name) FROM t1 GROUP BY rollno; ############################################################ @@ -119,11 +128,11 @@ SELECT id, rollno, group_concat(name) FROM t1 GROUP BY rollno; ############################################################ --echo ## Dropping table t1 ## -DROP table t1; +DROP TABLE t1; --echo ## Disconnecting both the connection ## -DISCONNECT test_con2; -DISCONNECT test_con1; +disconnect test_con2; +disconnect test_con1; connection default; diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 26da7aa2ce5..d475fb2adb9 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1350,4 +1350,41 @@ explain select count(*) from information_schema.tables; explain select count(*) from information_schema.columns; explain select count(*) from information_schema.views; +# +# Bug#39955 SELECT on INFORMATION_SCHEMA.GLOBAL_VARIABLES takes too long +# +set global init_connect="drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;\ +drop table if exists t1;drop table if exists t1;"; +select * from information_schema.global_variables where variable_name='init_connect'; +set global init_connect=""; + +# +# Bug #34517 SHOW GLOBAL STATUS does not work properly in embedded server. +# + +create table t0 select * from information_schema.global_status where VARIABLE_NAME='COM_SELECT'; +SELECT 1; +select a.VARIABLE_VALUE - b.VARIABLE_VALUE from t0 b, information_schema.global_status a + where a.VARIABLE_NAME = b.VARIABLE_NAME; +drop table t0; + --echo End of 5.1 tests. diff --git a/mysql-test/t/information_schema_part.test b/mysql-test/t/information_schema_part.test index 4cbf21ca1d3..f1415d12f79 100644 --- a/mysql-test/t/information_schema_part.test +++ b/mysql-test/t/information_schema_part.test @@ -121,3 +121,13 @@ SHOW CREATE TABLE t1; SELECT PARTITION_DESCRIPTION FROM information_schema.partitions WHERE table_schema = "test" AND table_name = "t1"; drop table t1; + +# +# Bug#38909 CREATE_OPTIONS in information_schema produces wrong results +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (f1 int key) partition by key(f1) partitions 2; +select create_options from information_schema.tables where table_schema="test"; +drop table t1; diff --git a/mysql-test/t/innodb_bug34300.test b/mysql-test/t/innodb_bug34300.test index 4b4a3fdc8a3..114bcf98c25 100644 --- a/mysql-test/t/innodb_bug34300.test +++ b/mysql-test/t/innodb_bug34300.test @@ -8,7 +8,9 @@ -- disable_query_log -- disable_result_log -SET @@max_allowed_packet=16777216; +# set packet size and reconnect +SET @@global.max_allowed_packet=16777216; +--connect (newconn, localhost, root,,) DROP TABLE IF EXISTS bug34300; CREATE TABLE bug34300 ( diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index ed8de208474..d47406697f8 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -43,3 +43,13 @@ CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; INSERT INTO t1 VALUES(1); DROP TABLE t1; +# +# Bug#37284 Crash in Field_string::type() +# +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; +CREATE INDEX i1 on t1 (a(3)); +SELECT * FROM t1 WHERE a = 'abcde'; +DROP TABLE t1; diff --git a/mysql-test/t/log_output_func.test b/mysql-test/t/log_output_func.test index 32026d8de59..007c4f38659 100644 --- a/mysql-test/t/log_output_func.test +++ b/mysql-test/t/log_output_func.test @@ -10,12 +10,13 @@ # # # Creation Date: 2008-03-08 # # Author: Rizwan # +# Modified: HHunger 2008-08-29 # # # Description: Test Cases of Dynamic System Variable log_output # # that checks the behavior of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### @@ -23,6 +24,10 @@ # ps-protocol. So, it is switched off. --disable_ps_protocol +SET @start_value= @@global.log_output; +SET @start_general_log= @@global.general_log; +SET @start_general_log_file= @@global.general_log_file; + --echo '#--------------------FN_DYNVARS_065_01-------------------------#' ################################################################## # Check if setting log_output is changed in every new connection # @@ -32,17 +37,12 @@ SET @@global.log_output = 'NONE'; # con1 will be default connection from now on --echo 'connect (con1,localhost,root,,,,)' connect (con1,localhost,root,,,,); ---echo 'connection con1' -connection con1; -SELECT @@global.log_output; SET @@global.log_output = 'TABLE,FILE'; + +# Test that the effect is global --echo 'connect (con2,localhost,root,,,,)' connect (con2,localhost,root,,,,); ---echo 'connection con2' -connection con2; SELECT @@global.log_output; -disconnect con2; - --echo '#--------------------FN_DYNVARS_065_02-------------------------#' #################################################### @@ -52,10 +52,9 @@ disconnect con2; --echo 'connection con1' connection con1; - -#======================================================================= +#=============================================================== --echo '---Checking general_log when log_output is NONE---' -#======================================================================= +#=============================================================== SET @@global.log_output = 'NONE'; TRUNCATE TABLE mysql.general_log; @@ -64,13 +63,13 @@ TRUNCATE TABLE mysql.general_log; DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1(a INT); -INSERT INTO t1 value(1); +INSERT INTO t1 VALUE(1); SELECT 'abc'; SELECT count(*) FROM mysql.general_log; -#============================================================================== +#=============================================================== --echo '---Checking general_log when log_output is TABLE---' -#============================================================================== +#=============================================================== SET @@global.log_output = 'TABLE'; TRUNCATE TABLE mysql.general_log; @@ -78,35 +77,49 @@ TRUNCATE TABLE mysql.general_log; --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings -create table t1(a int); -INSERT INTO t1 value(1); +CREATE TABLE t1(a int); +INSERT INTO t1 VALUE(1); SELECT 'abc'; -SELECT count(*) from mysql.general_log; +# At least the last 4 statement should be logged. +SELECT count(*)>4 FROM mysql.general_log; -#=========================================================== +#=============================================================== # Checking general_log when log_output is FILE -#=========================================================== - -#SET @log_file = "mytest.log"; -#SET @@global.general_log = 0; -#FLUSH LOGS; -#SET @@global.general_log_file = @log_file; ---echo 'Bug#35371: Changing general_log file is crashing server' ---echo 'SET @@global.general_log_file = @log_file;' +#=============================================================== +SET @@global.general_log = 'OFF'; +FLUSH LOGS; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval SET @@global.general_log_file = '$MYSQLTEST_VARDIR/run/mytest.log'; +SET @@global.general_log = 'ON'; SET @@global.log_output = 'FILE'; TRUNCATE TABLE mysql.general_log; --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings -create table t1(a int); -INSERT INTO t1 value(1); +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUE(1); SELECT 'abc'; -SELECT count(*) from mysql.general_log; +SELECT count(*) FROM mysql.general_log; DROP TABLE t1; +file_exists $MYSQLTEST_VARDIR/run/mytest.log ; + +#============================================================== +# Clean up +#============================================================== + +--echo connection default; +connection default; +SET @@global.general_log= 'OFF'; +SET @@global.general_log_file= @start_general_log_file; +SET @@global.log_output= @start_value; +SET @@global.general_log= @start_general_log; +SET @@global.general_log= 'ON'; + --enable_ps_protocol #################################################### -# Endo of functionality Testing for log_output # +# End of functionality Testing for log_output # #################################################### + diff --git a/mysql-test/t/max_allowed_packet_basic.test b/mysql-test/t/max_allowed_packet_basic.test index beb9b62d6de..8be0e5f670e 100644 --- a/mysql-test/t/max_allowed_packet_basic.test +++ b/mysql-test/t/max_allowed_packet_basic.test @@ -36,8 +36,13 @@ SET @start_global_value = @@global.max_allowed_packet; SELECT @start_global_value; -SET @start_session_value = @@session.max_allowed_packet; -SELECT @start_session_value; + +# give a known value to @@session.max_allowed_packet by assigning to +# @@global and setting up a new connection (for deterministic result +# file diffing) +SET @@global.max_allowed_packet = DEFAULT; +connect (conn1, localhost, root,,); + --echo '#--------------------FN_DYNVARS_070_01-------------------------#' @@ -49,7 +54,9 @@ SET @@global.max_allowed_packet = 1000; SET @@global.max_allowed_packet = DEFAULT; SELECT @@global.max_allowed_packet; +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 20000; +--Error ER_NO_DEFAULT SET @@session.max_allowed_packet = DEFAULT; SELECT @@session.max_allowed_packet; @@ -62,9 +69,6 @@ SELECT @@session.max_allowed_packet; SET @@global.max_allowed_packet = DEFAULT; SELECT @@global.max_allowed_packet = 1048576; -SET @@session.max_allowed_packet = DEFAULT; -SELECT @@session.max_allowed_packet = 1048576; - --echo '#--------------------FN_DYNVARS_070_03-------------------------#' ############################################################################ @@ -86,14 +90,19 @@ SELECT @@global.max_allowed_packet; # Change the value of max_allowed_packet to a valid value for SESSION Scope # ############################################################################# +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 1024; SELECT @@session.max_allowed_packet; +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 1025; SELECT @@session.max_allowed_packet; +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 65535; SELECT @@session.max_allowed_packet; +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 1073741824; SELECT @@session.max_allowed_packet; +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 1073741823; SELECT @@session.max_allowed_packet; @@ -118,14 +127,18 @@ SELECT @@global.max_allowed_packet; SET @@global.max_allowed_packet = test; SELECT @@global.max_allowed_packet; +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 0; SELECT @@session.max_allowed_packet; +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 1023; SELECT @@session.max_allowed_packet; +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = -2; SELECT @@session.max_allowed_packet; --Error ER_PARSE_ERROR SET @@session.max_allowed_packet = 65530.34.; +--Error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 10737418241; SELECT @@session.max_allowed_packet; --echo 'Bug # 34837: Errors are not coming on assigning invalid values to variable'; @@ -180,6 +193,7 @@ SELECT @@max_allowed_packet = @@global.max_allowed_packet; # Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable # ######################################################################################################## +--Error ER_VARIABLE_IS_READONLY SET @@max_allowed_packet = 100000; SELECT @@max_allowed_packet = @@local.max_allowed_packet; SELECT @@local.max_allowed_packet = @@session.max_allowed_packet; @@ -190,6 +204,7 @@ SELECT @@local.max_allowed_packet = @@session.max_allowed_packet; # Check if max_allowed_packet can be accessed with and without @@ sign # ############################################################################# +--Error ER_VARIABLE_IS_READONLY SET max_allowed_packet = 1024; SELECT @@max_allowed_packet; --Error ER_UNKNOWN_TABLE @@ -204,9 +219,9 @@ SELECT max_allowed_packet = @@session.max_allowed_packet; # Restore initial value # #################################### +connection default; SET @@global.max_allowed_packet = @start_global_value; SELECT @@global.max_allowed_packet; -SET @@session.max_allowed_packet = @start_session_value; SELECT @@session.max_allowed_packet; diff --git a/mysql-test/t/max_allowed_packet_func.test b/mysql-test/t/max_allowed_packet_func.test index c6a2a561234..0cfbeb4de5f 100644 --- a/mysql-test/t/max_allowed_packet_func.test +++ b/mysql-test/t/max_allowed_packet_func.test @@ -43,26 +43,19 @@ name BLOB --echo '#--------------------FN_DYNVARS_070_01-------------------------#' ############################################################################### -# Setting initial value of max_allowed_packet to 1024 at session level and # -# verifying its behavior after inserting data greater than 1024 bytes # +# Setting initial value of max_allowed_packet to 1024 at session level +# should result in an error (session variable is readonly) ############################################################################### ---echo ## Setting value of max_allowed packet and net_buffer_length to 1024 ## +--echo ## Setting value of max_allowed packet and net_buffer_length to 1024 ## +--error ER_VARIABLE_IS_READONLY SET @@session.max_allowed_packet = 1024; -SET @@session.net_buffer_length = 1024; SELECT @@session.max_allowed_packet; -SELECT @@session.net_buffer_length; ---echo ## Inserting and fetching data of length greater than 1024 ## -INSERT into t1(name) values("aaassssssssddddddddffffffgggggggg, askdlfjalsdkjfalksdjflaksdjfalkjdflaksjdflakjdflajsflajflajdfalsjfdlajfladjslfajdflajdsflajsflakjsdfla;kjflsdjkf;aljfa;lkdsfjla;sjlkajffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllakjsdffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffa;;;;;;;;;;;;;;;;;;;;;;;;;;;dsklfjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkljffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffdkskkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); - -SELECT length("aaaaaasssssssssssdddddddfffffgggg, askdlfjalsdkjfalksdjflaksdjfalkjdflaksjdflakjdflajsflajflajdfalsjfdlajfladjslfajdflajdsflajsflakjsdfla;kjflsdjkf;aljfa;lkdsfjla;sjlkajffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllakjsdffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffa;;;;;;;;;;;;;;;;;;;;;;;;;;;dsklfjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjkljffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffdkskkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk") as len; - ---echo ## Verifying record in table t1 ## -SELECT * from t1; +--error ER_VARIABLE_IS_READONLY +SET @@session.net_buffer_length = 1024; +SELECT @@session.net_buffer_length; ---echo 'Bug#35381: Error is not coming on inserting and fetching data of length' ---echo 'greater than max_allowed_packet size at session level'; --echo '#--------------------FN_DYNVARS_070_02-------------------------#' ############################################################################### diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 331663dceb5..0793ef15faf 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -10,9 +10,9 @@ source include/have_log_bin.inc; drop table if exists t1,t2,t3; drop database if exists mysqltest; drop view if exists v1; ---error 0,1141,1147 +--error 0,ER_NONEXISTING_GRANT,ER_NONEXISTING_TABLE_GRANT revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; ---error 0,1141,1147 +--error 0,ER_NONEXISTING_GRANT,ER_NONEXISTING_TABLE_GRANT revoke all privileges on mysqltest.* from mysqltest_1@localhost; delete from mysql.user where user=_binary'mysqltest_1'; --enable_warnings @@ -160,9 +160,9 @@ create table t2 (n int(10) not null primary key, d int(10)); insert into t1 values(1,1); insert into t2 values(1,10),(2,20); LOCK TABLES t1 write, t2 read; ---error 1099 +--error ER_TABLE_NOT_LOCKED_FOR_WRITE DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n; ---error 1099 +--error ER_TABLE_NOT_LOCKED_FOR_WRITE UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; unlock tables; @@ -183,7 +183,7 @@ create table t1 (n int(10), d int(10)); create table t2 (n int(10), d int(10)); insert into t1 values(1,1); insert into t2 values(1,10),(2,20); ---error 1175 +--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; set sql_safe_updates=0; drop table t1,t2; @@ -196,7 +196,7 @@ set timestamp=1038000000; UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n; select n,d,unix_timestamp(t) from t1; select n,d,unix_timestamp(t) from t2; ---error 1064 +--error ER_PARSE_ERROR UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n; drop table t1,t2; set timestamp=0; @@ -324,41 +324,6 @@ delete t1, t2 from t2,t1 drop table t1,t2; # -# Test for bug #1820. -# - -create table t1 ( a int not null, b int not null) ; ---disable_query_log -insert into t1 values (1,1),(2,2),(3,3),(4,4); -let $1=19; -set @d=4; -while ($1) -{ - eval insert into t1 select a+@d,b+@d from t1; - eval set @d=@d*2; - dec $1; -} - ---enable_query_log -alter table t1 add index i1(a); -delete from t1 where a > 2000000; -create table t2 like t1; -insert into t2 select * from t1; - -select 't2 rows before small delete', count(*) from t1; -delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 2; -select 't2 rows after small delete', count(*) from t2; -select 't1 rows after small delete', count(*) from t1; - -## Try deleting many rows - -delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000; -select 't2 rows after big delete', count(*) from t2; -select 't1 rows after big delete', count(*) from t1; - -drop table t1,t2; - -# # Test alias (this is not correct in 4.0) # @@ -367,7 +332,7 @@ CREATE TABLE t2 ( a int ); DELETE t1 FROM t1, t2 AS t3; DELETE t4 FROM t1, t1 AS t4; DELETE t3 FROM t1 AS t3, t1 AS t4; ---error 1109 +--error ER_UNKNOWN_TABLE DELETE t1 FROM t1 AS t3, t2 AS t4; INSERT INTO t1 values (1),(2); INSERT INTO t2 values (1),(2); @@ -422,7 +387,7 @@ drop database mysqltest; create table t1 (a int, primary key (a)); create table t2 (a int, primary key (a)); create table t3 (a int, primary key (a)); --- error 1109 +-- error ER_UNKNOWN_TABLE delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a); drop table t1, t2, t3; @@ -431,9 +396,9 @@ drop table t1, t2, t3; # create table t1 (col1 int); create table t2 (col1 int); --- error 1093 +-- error ER_UPDATE_TABLE_USED update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1; drop table t1,t2; @@ -458,7 +423,7 @@ drop table t1, t2; # create table t1(a int); create table t2(a int); ---error 1093 +--error ER_UPDATE_TABLE_USED delete from t1,t2 using t1,t2 where t1.a=(select a from t1); drop table t1, t2; # End of 4.1 tests diff --git a/mysql-test/t/multi_update2-master.opt b/mysql-test/t/multi_update2-master.opt new file mode 100644 index 00000000000..9f1a29461ff --- /dev/null +++ b/mysql-test/t/multi_update2-master.opt @@ -0,0 +1 @@ +--set-variable=tmp_table_size=1024 diff --git a/mysql-test/t/multi_update2.test b/mysql-test/t/multi_update2.test new file mode 100644 index 00000000000..47f9bc7bad7 --- /dev/null +++ b/mysql-test/t/multi_update2.test @@ -0,0 +1,43 @@ +# +# Test of update statement that uses many tables. +# + +--disable_warnings +DROP TABLE IF EXISTS t1,t2; +--enable_warnings + +# +# Bug#1820 Rows not deleted from second table on multi-table delete +# + +CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL) ; +--echo # The protocolling of many inserts into t1 is suppressed. +--disable_query_log +INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); +let $1=19; +set @d=4; +while ($1) +{ + eval INSERT INTO t1 SELECT a+@d,b+@d FROM t1; + eval SET @d=@d*2; + dec $1; +} + +--enable_query_log +ALTER TABLE t1 ADD INDEX i1(a); +DELETE FROM t1 WHERE a > 2000000; +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 SELECT * FROM t1; + +SELECT 't2 rows before small delete', COUNT(*) FROM t1; +DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 2; +SELECT 't2 rows after small delete', COUNT(*) FROM t2; +SELECT 't1 rows after small delete', COUNT(*) FROM t1; + +## Try deleting many rows + +DELETE t1,t2 FROM t1,t2 WHERE t1.b=t2.a AND t1.a < 100*1000; +SELECT 't2 rows after big delete', COUNT(*) FROM t2; +SELECT 't1 rows after big delete', COUNT(*) FROM t1; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/mysqlbinlog_row_big.test b/mysql-test/t/mysqlbinlog_row_big.test index bad39255bac..14e818e7214 100644 --- a/mysql-test/t/mysqlbinlog_row_big.test +++ b/mysql-test/t/mysqlbinlog_row_big.test @@ -38,7 +38,7 @@ SET timestamp=1000000000; --echo # --echo # We need big packets. --echo # -SET @@session.max_allowed_packet= 1024*1024*1024; +SET @@global.max_allowed_packet= 1024*1024*1024; --echo # --echo # Delete all existing binary logs. diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index 22b7f007b7e..da00ea0c2bf 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -75,6 +75,33 @@ drop table t_bug25347; drop database d_bug25347; use test; +# +# Bug#39541 CHECK TABLE on information_schema myisam tables produces error +# +create view v1 as select * from information_schema.routines; +check table v1, information_schema.routines; +drop view v1; + + +# +# Bug#37527: mysqlcheck fails to report entire database +# when frm file corruption +# +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT); +# backup then null t1.frm +--copy_file $MYSQLTEST_VARDIR/master-data/test/t1.frm $MYSQLTEST_VARDIR/master-data/test/t1.frm.bak +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm +--write_file $MYSQLTEST_VARDIR/master-data/test/t1.frm +EOF +--exec $MYSQL_CHECK test +# restore t1.frm +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm +--copy_file $MYSQLTEST_VARDIR/master-data/test/t1.frm.bak $MYSQLTEST_VARDIR/master-data/test/t1.frm +--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm.bak +DROP TABLE t1, t2; + + --echo End of 5.0 tests # diff --git a/mysql-test/t/net_buffer_length_basic.test b/mysql-test/t/net_buffer_length_basic.test index 80403bfec2b..6e1e0559c61 100644 --- a/mysql-test/t/net_buffer_length_basic.test +++ b/mysql-test/t/net_buffer_length_basic.test @@ -38,9 +38,13 @@ SET @start_global_value = @@global.net_buffer_length; # Due to differences in results of linux and windows #SELECT @start_global_value; -SET @start_session_value = @@session.net_buffer_length; -#SELECT @start_session_value; +# give a known value to @@session.net_buffer_length by assigning to +# @@global and setting up a new connection (for deterministic result +# file diffing) +SET @@global.net_buffer_length = DEFAULT; +connect(con1,localhost,root,,); +connection con1; --echo '#--------------------FN_DYNVARS_109_01-------------------------#' ################################################################# @@ -51,7 +55,9 @@ SET @@global.net_buffer_length = 10000; SET @@global.net_buffer_length = DEFAULT; SELECT @@global.net_buffer_length; +--Error ER_VARIABLE_IS_READONLY SET @@session.net_buffer_length = 20000; +--Error ER_NO_DEFAULT SET @@session.net_buffer_length = DEFAULT; SELECT @@session.net_buffer_length; @@ -64,9 +70,6 @@ SELECT @@session.net_buffer_length; SET @@global.net_buffer_length = DEFAULT; SELECT @@global.net_buffer_length = 16384; -SET @@session.net_buffer_length = DEFAULT; -SELECT @@session.net_buffer_length = 16384; - --echo '#--------------------FN_DYNVARS_109_03-------------------------#' ########################################################################### @@ -91,17 +94,7 @@ SELECT @@global.net_buffer_length; # Change the value of net_buffer_length to a valid value for SESSION Scope # ############################################################################ -SET @@session.net_buffer_length = 1024; -SELECT @@session.net_buffer_length; -SET @@session.net_buffer_length = 1025; -SELECT @@session.net_buffer_length; -SET @@session.net_buffer_length = 1048576; -SELECT @@session.net_buffer_length; -SET @@session.net_buffer_length = 1048575; -SELECT @@session.net_buffer_length; -SET @@session.net_buffer_length = 65535; -SELECT @@session.net_buffer_length; ---echo 'Bug# 34877: Invalid Values are coming in variable on assigning valid values'; +# Bug#22891: SESSION net_buffer_length is now read-only; assignments skipped --echo '#------------------FN_DYNVARS_109_05-----------------------#' @@ -126,18 +119,8 @@ SELECT @@global.net_buffer_length; SET @@global.net_buffer_length = test; SELECT @@global.net_buffer_length; -SET @@session.net_buffer_length = 0; -SELECT @@session.net_buffer_length; -SET @@session.net_buffer_length = -2; -SELECT @@session.net_buffer_length; -SET @@session.net_buffer_length = 1048577; -SELECT @@session.net_buffer_length; -SET @@session.net_buffer_length = 1048576002; -SELECT @@session.net_buffer_length; ---Error ER_PARSE_ERROR -SET @@session.net_buffer_length = 65530.34.; -SET @@session.net_buffer_length = 65550; -SELECT @@session.net_buffer_length; +# Bug#22891: SESSION net_buffer_length is now read-only; assignments skipped + --echo 'Bug # 34837: Errors are not coming on assigning invalid values to variable'; --Error ER_WRONG_TYPE_FOR_VAR @@ -190,9 +173,7 @@ SELECT @@net_buffer_length = @@global.net_buffer_length; # Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable # ######################################################################################################## -SET @@net_buffer_length = 100000; -SELECT @@net_buffer_length = @@local.net_buffer_length; -SELECT @@local.net_buffer_length = @@session.net_buffer_length; +# Bug#22891: SESSION net_buffer_length is now read-only; assignments skipped --echo '#---------------------FN_DYNVARS_109_11----------------------#' @@ -200,7 +181,7 @@ SELECT @@local.net_buffer_length = @@session.net_buffer_length; # Check if net_buffer_length can be accessed with and without @@ sign # ############################################################################ -SET net_buffer_length = 1024; +# Bug#22891: SESSION net_buffer_length is now read-only; assignments skipped SELECT @@net_buffer_length; --Error ER_UNKNOWN_TABLE SELECT local.net_buffer_length; @@ -214,11 +195,11 @@ SELECT net_buffer_length = @@session.net_buffer_length; # Restore initial value # #################################### +connection default; + SET @@global.net_buffer_length = @start_global_value; # Due to differences in results of linux and windows #SELECT @@global.net_buffer_length; -SET @@session.net_buffer_length = @start_session_value; -#SELECT @@session.net_buffer_length; ###################################################### diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index a76f62c9611..fb9622a4ed9 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -1,7 +1,7 @@ # Tests for SSL connections, only run if mysqld is compiled # with support for SSL. --- source include/have_ssl.inc +--source include/have_ssl.inc --disable_warnings drop table if exists t1; @@ -145,12 +145,12 @@ DELIMITER ;$$ let $wait_condition=select count(*) = 0 from information_schema.events where event_name='event_status'; --source include/wait_condition.inc +# The actual value doesn't matter and can vary based on test ordering and on ssl library. +--replace_column 2 # SELECT variable_name, variable_value FROM thread_status; DROP TABLE thread_status; SET GLOBAL event_scheduler=0; ---echo End of 5.1 tests - # # Test to connect using a list of ciphers @@ -190,3 +190,44 @@ INSERT INTO t1 VALUES (1), (2); --exec $MYSQL_DUMP --skip-create --skip-comments --ssl --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test 2>&1 DROP TABLE t1; + +# +# Bug#39172: Asking for DH+non-RSA key with server set to use other key caused +# YaSSL to crash the server. +# + +# Common ciphers to openssl and yassl +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl_cipher';" --ssl-cipher=DHE-RSA-AES256-SHA +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl_cipher';" --ssl-cipher=EDH-RSA-DES-CBC3-SHA +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl_cipher';" --ssl-cipher=EDH-RSA-DES-CBC-SHA +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl_cipher';" --ssl-cipher=RC4-SHA +--disable_query_log +--disable_result_log + +# Below here caused crashes. ################ +--error 1,0 +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl-cipher';" --ssl-cipher=NOT----EXIST +# These probably exist but the server's keys can't be used to accept these kinds of connections. +--error 1,0 +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl-cipher';" --ssl-cipher=DHE-DSS-AES128-RMD +--error 1,0 +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl-cipher';" --ssl-cipher=DHE-DSS-AES128-SHA +--error 1,0 +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl-cipher';" --ssl-cipher=DHE-DSS-AES256-RMD +--error 1,0 +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl-cipher';" --ssl-cipher=DHE-DSS-AES256-SHA +--error 1,0 +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl-cipher';" --ssl-cipher=DHE-DSS-DES-CBC3-RMD +--error 1,0 +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl-cipher';" --ssl-cipher=EDH-DSS-DES-CBC3-SHA +--error 1,0 +--exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl-cipher';" --ssl-cipher=EDH-DSS-DES-CBC-SHA +# End of crashers. ########################## + +# If this gives a result, then the bug is fixed. +--enable_result_log +--enable_query_log +select 'is still running; no cipher request crashed the server' as result from dual; + +## +--echo End of 5.1 tests diff --git a/mysql-test/t/packet.test b/mysql-test/t/packet.test index 4de284b7824..93b46766d99 100644 --- a/mysql-test/t/packet.test +++ b/mysql-test/t/packet.test @@ -8,30 +8,30 @@ # Check protocol handling # -connect (con1,localhost,root,,); - -connection con1; +# setting values below minimum threshold of 1024 will cause truncating set global max_allowed_packet=100; -set max_allowed_packet=100; set global net_buffer_length=100; -set net_buffer_length=100; -# Have to be > 1024 as min value of net_buffer_length is 1024 + +# is not yet in effect SELECT length("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa") as len; -# Should return NULL as 2000 is bigger than max_allowed_packet select repeat('a',2000); # -# Connection 2 should get error for too big packets +# Connection 1 should get error for too big packets # -connect (con2,localhost,root,,); -connection con2; +connect (con1,localhost,root,,); +connection con1; select @@net_buffer_length, @@max_allowed_packet; --error 1153 SELECT length("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa") as len; + +# +# Reset to default values and reconnect +# set global max_allowed_packet=default; -set max_allowed_packet=default; set global net_buffer_length=default; -set net_buffer_length=default; +connect (con2,localhost,root,,); +connection con2; SELECT length("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa") as len; select length(repeat('a',2000)); diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index efa7c5f4263..23a300de9e5 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -17,6 +17,21 @@ drop table if exists t1, t2; --enable_warnings # +# Bug#40954: Crash if range search and order by. +# +CREATE TABLE t1 ( + pk INT NOT NULL AUTO_INCREMENT, + PRIMARY KEY (pk) +) +/*!50100 PARTITION BY HASH (pk) +PARTITIONS 2 */; +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1 WHERE pk < 0 ORDER BY pk; +DROP TABLE t1; + +# # Bug#40494: Crash MYSQL server crashes on range access with partitioning # and order by # diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index c29b3458d19..2abbceffbb0 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -1,6 +1,46 @@ --source include/have_partition.inc --source include/have_innodb.inc +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Bug#40595: Non-matching rows not released with READ-COMMITTED on tables +# with partitions +CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB +PARTITION BY RANGE(id) ( + PARTITION p0 VALUES LESS THAN (5), + PARTITION p1 VALUES LESS THAN (10), + PARTITION p2 VALUES LESS THAN MAXVALUE +); + +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), + (9,9), (10,10), (11,11); + +SET @old_tx_isolation := @@session.tx_isolation; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; + +SET autocommit = 0; + +UPDATE t1 SET DATA = data*2 WHERE id = 3; + +# grouping/referencing in replace_regex is very slow on long strings, +# removing all before/after the interesting row before grouping/referencing +--replace_regex /.*---TRANSACTION [0-9]+ [0-9]+, .*, OS thread id [0-9]+// /MySQL thread id [0-9]+, query id [0-9]+ .*// /.*([0-9]+ lock struct\(s\)), heap size [0-9]+, ([0-9]+ row lock\(s\)).*/\1 \2/ +SHOW ENGINE InnoDB STATUS; + +UPDATE t1 SET data = data*2 WHERE data = 2; + +# grouping/referencing in replace_regex is very slow on long strings, +# removing all before/after the interesting row before grouping/referencing +--replace_regex /.*---TRANSACTION [0-9]+ [0-9]+, .*, OS thread id [0-9]+// /MySQL thread id [0-9]+, query id [0-9]+ .*// /.*([0-9]+ lock struct\(s\)), heap size [0-9]+, ([0-9]+ row lock\(s\)).*/\1 \2/ +SHOW ENGINE InnoDB STATUS; + +SET @@session.tx_isolation = @old_tx_isolation; + +DROP TABLE t1; + # # Bug37721: ORDER BY when WHERE contains non-partitioned index column # wrong order since it did not use pk as second compare diff --git a/mysql-test/t/perror-win.test b/mysql-test/t/perror-win.test new file mode 100644 index 00000000000..cc09b8527be --- /dev/null +++ b/mysql-test/t/perror-win.test @@ -0,0 +1,14 @@ +# Windows-specific tests +--source include/windows.inc +--require r/have_perror.require +disable_query_log; +eval select LENGTH("$MY_PERROR") > 0 as "have_perror"; +enable_query_log; + + +--exec $MY_PERROR 150 2>&1 +--exec $MY_PERROR 23 2>&1 +--exec $MY_PERROR 1062 2>&1 +--error 1 +--exec $MY_PERROR 30000 2>&1 + diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 5d6ce11edcf..d2b7394e979 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3356,4 +3356,40 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a; DROP TABLE t1,t2,t3; + +# +# Bug#37460 Assertion failed: +# !table->file || table->file->inited == handler::NONE +# +CREATE TABLE t1 (id int); +CREATE TABLE t2 (id int, c int); + +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); +INSERT INTO t1 (id) VALUES (1); +INSERT INTO t2 (id) VALUES (1); + +CREATE VIEW v1 AS + SELECT t2.c AS c FROM t1, t2 + WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; +UPDATE v1 SET c=1; + +CREATE VIEW v2 (a,b) AS + SELECT t2.id, t2.c AS c FROM t1, t2 + WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; + +--error 1369 +INSERT INTO v2(a,b) VALUES (2,2); +INSERT INTO v2(a,b) VALUES (1,2); +SELECT * FROM v1; + +CREATE VIEW v3 AS + SELECT t2.c AS c FROM t2 + WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION; + +DELETE FROM v3; + +DROP VIEW v1,v2,v3; +DROP TABLE t1,t2; + --echo End of 5.1 tests. diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index b1186b7438b..c805dd40fe8 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -195,32 +195,50 @@ DROP TABLE t1; # # Bug#32167: another privilege bypass with DATA/INDEX DIRECTORY # +# With Bug#41002 (symlink.test fails on symlinked datadir) it was +# decided that the below statements may also succeed if the data +# home directory is symlinked, e.g. mysql-test-run --mem. +# This will be fixed in 6.0 only. +# let $MYSQLD_DATADIR= `select @@datadir`; --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR ---error ER_WRONG_ARGUMENTS +--error 0,ER_WRONG_ARGUMENTS eval CREATE TABLE t1(a INT) INDEX DIRECTORY='$MYSQLD_DATADIR/mysql'; - +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +# --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR ---error ER_WRONG_ARGUMENTS +--error 0,ER_WRONG_ARGUMENTS eval CREATE TABLE t1(a INT) DATA DIRECTORY='$MYSQLD_DATADIR/test'; - +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +# --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR ---error ER_WRONG_ARGUMENTS +--error 0,ER_WRONG_ARGUMENTS eval CREATE TABLE t1(a INT) DATA DIRECTORY='$MYSQLD_DATADIR/'; - +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +# --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR ---error ER_WRONG_ARGUMENTS +--error 0,ER_WRONG_ARGUMENTS eval CREATE TABLE t1(a INT) INDEX DIRECTORY='$MYSQLD_DATADIR'; - +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +# --replace_result $MYSQLTEST_VARDIR TEST_DIR --error 1 eval CREATE TABLE t1(a INT) INDEX DIRECTORY='$MYSQLTEST_VARDIR/master-data_var'; +# # BUG#25677 - With --skip-symbolic-links option on, DATA DIRECTORY clause is # silently ignored # diff --git a/mysql-test/t/timestamp_func.test b/mysql-test/t/timestamp_func.test index ecca3c7daeb..e119f1b6253 100644 --- a/mysql-test/t/timestamp_func.test +++ b/mysql-test/t/timestamp_func.test @@ -9,6 +9,8 @@ # Creation Date: 2008-02-25 # # Author: Sharique Abdullah # # # +# Modified: HHunger 2008-08-28 Reimplemented the test completely. # +# # # Description: Test Cases of Dynamic System Variable "timestamp" # # that checks behavior of this variable in the following ways # # * Functionality based on different values # @@ -17,63 +19,32 @@ # # ############################################################################ ---echo ** Setup ** ---echo -# -# Setup -# ---echo ** Connecting con0 using root ** -connect (con0,localhost,root,,); ---echo ** Connecting con1 using root ** -connect (con1, localhost, root,,); - ---echo '#-----------------------------FN_DYNVARS_179_01------------------#' -# -# Checking for connection 1 -# +# Change timestamp which must have an effect on now(), but not on sysdate(). +# Use Unix epoch timestamp +# All comparisons must deliver true(1) +# Exception: --sysdate-is-now switches off this behaviour and must not be set. ---echo ** Connection con0 ** -connection con0; -SET @ts_old = @@SESSION.timestamp; ---echo waiting 1 sec ---sleep 1 -SET @ts_new = @@SESSION.timestamp; -SELECT @ts_new - @ts_old >= 1 AS 'Timestamp Difference'; ---echo 1 means >=1 expected is true +--echo ** Connecting con1 using root ** +connect (con1,localhost,root,,); +SELECT date(now()) = date(sysdate()); +SET @@session.timestamp = 1100000000; +SELECT date(now()) != date(sysdate()); +# Assure that setting of the variable has no effect on other session. +--echo ** Connecting con0 using root ** +connect (con0,localhost,root,,); +SELECT @@session.timestamp != 1100000000; +SET @@session.timestamp = 1000000000; +SELECT date(now()) != date(sysdate()); -# -# Checking for connection 2 -# --echo ** Connection con1 ** connection con1; -SET @ts_old = @@SESSION.timestamp; ---echo waiting 4 sec ---sleep 4 -SET @ts_new = @@SESSION.timestamp; -SELECT @ts_new - @ts_old >= 4 AS 'Timestamp Difference'; ---echo 1 means >=4 expected is true - ---echo '#-----------------------------FN_DYNVARS_179_02---------------------#' -# -# Testing timezone change effect -# - -SET @ts_old = @@SESSION.timestamp; ---sleep 1 ---echo Changing time zone -SET time_zone = 'MET'; -SET @ts_new = @@SESSION.timestamp; -SELECT @ts_new - @ts_old >= 1 AS 'Timestamp Difference'; ---echo 1 means >=1 expected is true - -# -# Cleanup -# ---echo ** Cleanup ** +SELECT @@session.timestamp != 1000000000; +SELECT @@session.timestamp = 1100000000; --echo ** Connection default ** connection default; --echo Disconnecting Connections con0, con1 disconnect con0; disconnect con1; + diff --git a/mysql-test/t/timestamp_sysdate_is_now_func-master.opt b/mysql-test/t/timestamp_sysdate_is_now_func-master.opt new file mode 100644 index 00000000000..97a58d28032 --- /dev/null +++ b/mysql-test/t/timestamp_sysdate_is_now_func-master.opt @@ -0,0 +1 @@ +--sysdate-is-now diff --git a/mysql-test/t/timestamp_sysdate_is_now_func.test b/mysql-test/t/timestamp_sysdate_is_now_func.test new file mode 100644 index 00000000000..7ca3b4cddac --- /dev/null +++ b/mysql-test/t/timestamp_sysdate_is_now_func.test @@ -0,0 +1,47 @@ +############################################################################ +# # +# Variable Name: timestamp with sysdate-is-now # +# Scope: GLOBAL # +# Access Type: Dynamic # +# Data Type: INTEGER # +# # +# # +# Creation Date: 2008-11-25 # +# Author: Horst Hunger # +# # +# Description: Test Cases of Dynamic System Variable "timestamp" # +# that checks behavior of this variable in the following ways # +# * Like timstamp_func, but with set "sysdate-is-now". # +# # +# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html # +# # +############################################################################ + +# Use Unix epoch timestamp +# Due to "--sysdate-is-now" timestamp must have an effect on both. +# See also timestamp_func.test. + +--echo ** Connecting con1 using root ** +connect (con1,localhost,root,,); +SELECT date(now()) = date(sysdate()); +SET @@session.timestamp = 1100000000; +SELECT date(now()) != date(sysdate()); + +# Assure that setting of the variable has no effect on other session. +--echo ** Connecting con0 using root ** +connect (con0,localhost,root,,); +SELECT @@session.timestamp != 1100000000; +SET @@session.timestamp = 1000000000; +SELECT date(now()) != date(sysdate()); + +--echo ** Connection con1 ** +connection con1; +SELECT @@session.timestamp != 1000000000; +SELECT @@session.timestamp = 1100000000; + +--echo ** Connection default ** +connection default; +--echo Disconnecting Connections con0, con1 +disconnect con0; +disconnect con1; + diff --git a/mysql-test/t/timezone3.test b/mysql-test/t/timezone3.test index 0aedbafcec4..8a9941f6383 100644 --- a/mysql-test/t/timezone3.test +++ b/mysql-test/t/timezone3.test @@ -45,6 +45,10 @@ insert into t1 values (unix_timestamp('1981-07-01 03:59:59'),'1981-07-01 03:59:59'), (unix_timestamp('1981-07-01 04:00:00'),'1981-07-01 04:00:00'); +insert into t1 values + (unix_timestamp('2009-01-01 02:59:59'),'2009-01-01 02:59:59'), + (unix_timestamp('2009-01-01 03:00:00'),'2009-01-01 03:00:00'); + select i, from_unixtime(i), c from t1; drop table t1; @@ -58,4 +62,12 @@ insert into t1 values (19730101235900), (20040101235900); select * from t1; drop table t1; +# +# Test Bug #39920: MySQL cannot deal with Leap Second expression in string +# literal +# + +# 2009-01-01 02:59:59, 2009-01-01 02:59:60 and 2009-01-01 03:00:00 +SELECT FROM_UNIXTIME(1230768022), FROM_UNIXTIME(1230768023), FROM_UNIXTIME(1230768024); + # End of 4.1 tests diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 4a6462a8e72..4cf9ea63dad 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1235,6 +1235,16 @@ DROP TABLE t1; select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000); +# +# Bug #31616 div_precision_increment description looks wrong +# + +create table t1 as select 5.05 / 0.014; +show warnings; +show create table t1; +select * from t1; +DROP TABLE t1; + --echo End of 5.0 tests # diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index f58cd54250b..ad4d57b7128 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -861,8 +861,10 @@ drop tables t1,t2,t3; # exceeds mediumtext maximum length # -SELECT @tmp_max:= @@max_allowed_packet; -SET max_allowed_packet=25000000; +SELECT @tmp_max:= @@global.max_allowed_packet; +SET @@global.max_allowed_packet=25000000; +# switching connection to allow the new max_allowed_packet take effect +--connect (newconn, localhost, root,,) CREATE TABLE t1 (a mediumtext); CREATE TABLE t2 (b varchar(20)); INSERT INTO t1 VALUES ('a'); @@ -884,7 +886,9 @@ INSERT INTO t1 VALUES ('a'); CREATE TABLE t3 SELECT REPEAT(a,2) AS a FROM t1 UNION SELECT b FROM t2; SHOW CREATE TABLE t3; DROP TABLES t1,t2,t3; -SET max_allowed_packet:= @tmp_max; +--connection default +SET @@global.max_allowed_packet:= @tmp_max; +--disconnect newconn # # Bug #10032 Bug in parsing UNION with ORDER BY when one node does not use FROM diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index f79c9e773aa..7d56df259ba 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -430,4 +430,25 @@ drop table t1,t2; connection default; disconnect con1; +# +# Bug #40745: Error during WHERE clause calculation in UPDATE +# leads to an assertion failure +# +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +CREATE FUNCTION f1() RETURNS INT RETURN f1(); +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1); + +--error ER_SP_NO_RECURSION +UPDATE t1 SET i = 3 WHERE f1(); +--error ER_SP_NO_RECURSION +UPDATE t1 SET i = f1(); + +DROP TABLE t1; +DROP FUNCTION f1; + --echo End of 5.0 tests diff --git a/mysql-test/t/variables-notembedded.test b/mysql-test/t/variables-notembedded.test index 20ce3a74532..7cc068c68c7 100644 --- a/mysql-test/t/variables-notembedded.test +++ b/mysql-test/t/variables-notembedded.test @@ -30,3 +30,82 @@ set global slave_net_timeout=default; set global sql_slave_skip_counter= 0; set @@global.slave_net_timeout= @my_slave_net_timeout; +# +# Bug#28234 - global/session scope - documentation vs implementation +# +--echo +# +# Additional variables fixed from sql_repl.cc. +# +--echo # +SHOW VARIABLES like 'log_slave_updates'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.log_slave_updates; +SELECT @@global.log_slave_updates; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.log_slave_updates= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.log_slave_updates= true; +# +--echo # +SHOW VARIABLES like 'relay_log'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.relay_log; +SELECT @@global.relay_log; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.relay_log= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.relay_log= 'x'; +# +--echo # +SHOW VARIABLES like 'relay_log_index'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.relay_log_index; +SELECT @@global.relay_log_index; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.relay_log_index= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.relay_log_index= 'x'; +# +--echo # +SHOW VARIABLES like 'relay_log_info_file'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.relay_log_info_file; +SELECT @@global.relay_log_info_file; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.relay_log_info_file= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.relay_log_info_file= 'x'; +# +--echo # +SHOW VARIABLES like 'relay_log_space_limit'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.relay_log_space_limit; +SELECT @@global.relay_log_space_limit; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.relay_log_space_limit= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.relay_log_space_limit= 7; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'slave_load_tmpdir'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.slave_load_tmpdir; +--replace_column 1 # +SELECT @@global.slave_load_tmpdir; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.slave_load_tmpdir= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.slave_load_tmpdir= 'x'; +# +--echo # +SHOW VARIABLES like 'slave_skip_errors'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.slave_skip_errors; +SELECT @@global.slave_skip_errors; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.slave_skip_errors= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.slave_skip_errors= 7; +# diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 7dbe72865d3..fb2b8adf4af 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -146,25 +146,23 @@ show global variables like 'myisam_max_sort_file_size'; --replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; +# bug#22891: modified to take read-only SESSION net_buffer_length into account set global net_retry_count=10, session net_retry_count=10; set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; -set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; show global variables like 'net_%'; select * from information_schema.global_variables where variable_name like 'net_%' order by 1; show session variables like 'net_%'; select * from information_schema.session_variables where variable_name like 'net_%' order by 1; -set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; +set global net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; show global variables like 'net_%'; select * from information_schema.global_variables where variable_name like 'net_%' order by 1; -show session variables like 'net_%'; -select * from information_schema.session_variables where variable_name like 'net_%' order by 1; -set net_buffer_length=1; -show variables like 'net_buffer_length'; -select * from information_schema.session_variables where variable_name like 'net_buffer_length'; +set global net_buffer_length=1; +show global variables like 'net_buffer_length'; +select * from information_schema.global_variables where variable_name like 'net_buffer_length'; #warning 1292 -set net_buffer_length=2000000000; -show variables like 'net_buffer_length'; -select * from information_schema.session_variables where variable_name like 'net_buffer_length'; +set global net_buffer_length=2000000000; +show global variables like 'net_buffer_length'; +select * from information_schema.global_variables where variable_name like 'net_buffer_length'; set character set cp1251_koi8; show variables like "character_set_client"; @@ -274,7 +272,7 @@ select @@long_query_time; set long_query_time=100.000001; select @@long_query_time; set low_priority_updates=1; -set max_allowed_packet=100; +set global max_allowed_packet=100; set global max_binlog_cache_size=100; set global max_binlog_size=100; set global max_connect_errors=100; @@ -288,7 +286,7 @@ set global max_user_connections=100; select @@max_user_connections; set global max_write_lock_count=100; set myisam_sort_buffer_size=100; -set net_buffer_length=100; +set global net_buffer_length=100; set net_read_timeout=100; set net_write_timeout=100; set global query_cache_limit=100; @@ -795,3 +793,287 @@ SET GLOBAL log_output = 0; --echo --echo # -- End of Bug#34820. +# +# Bug#28234 - global/session scope - documentation vs implementation +# +--echo +--echo # +SHOW VARIABLES like 'ft_max_word_len'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.ft_max_word_len; +SELECT @@global.ft_max_word_len; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.ft_max_word_len= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.ft_max_word_len= 7; +# +--echo # +SHOW VARIABLES like 'ft_min_word_len'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.ft_min_word_len; +SELECT @@global.ft_min_word_len; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.ft_min_word_len= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.ft_min_word_len= 7; +# +--echo # +SHOW VARIABLES like 'ft_query_expansion_limit'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.ft_query_expansion_limit; +SELECT @@global.ft_query_expansion_limit; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.ft_query_expansion_limit= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.ft_query_expansion_limit= 7; +# +--echo # +SHOW VARIABLES like 'ft_stopword_file'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.ft_stopword_file; +SELECT @@global.ft_stopword_file; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.ft_stopword_file= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.ft_stopword_file= 'x'; +# +# Additional variables fixed. +# +--echo # +SHOW VARIABLES like 'back_log'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.back_log; +SELECT @@global.back_log; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.back_log= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.back_log= 7; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'large_files_support'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.large_files_support; +--replace_column 1 # +SELECT @@global.large_files_support; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.large_files_support= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.large_files_support= true; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'character_sets_dir'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.character_sets_dir; +--replace_column 1 # +SELECT @@global.character_sets_dir; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.character_sets_dir= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.character_sets_dir= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'init_file'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.init_file; +--replace_column 1 # +SELECT @@global.init_file; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.init_file= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.init_file= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'language'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.language; +--replace_column 1 # +SELECT @@global.language; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.language= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.language= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'large_page_size'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.large_page_size; +--replace_column 1 # +SELECT @@global.large_page_size; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.large_page_size= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.large_page_size= 7; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'large_pages'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.large_pages; +--replace_column 1 # +SELECT @@global.large_pages; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.large_pages= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.large_pages= true; +# +--echo # +SHOW VARIABLES like 'log_bin'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.log_bin; +SELECT @@global.log_bin; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.log_bin= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.log_bin= true; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'log_error'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.log_error; +--replace_column 1 # +SELECT @@global.log_error; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.log_error= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.log_error= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'lower_case_file_system'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.lower_case_file_system; +--replace_column 1 # +SELECT @@global.lower_case_file_system; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.lower_case_file_system= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.lower_case_file_system= true; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'lower_case_table_names'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.lower_case_table_names; +--replace_column 1 # +SELECT @@global.lower_case_table_names; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.lower_case_table_names= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.lower_case_table_names= 7; +# +--echo # +SHOW VARIABLES like 'myisam_recover_options'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.myisam_recover_options; +SELECT @@global.myisam_recover_options; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.myisam_recover_options= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.myisam_recover_options= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'open_files_limit'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.open_files_limit; +--replace_column 1 # +SELECT @@global.open_files_limit; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.open_files_limit= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.open_files_limit= 7; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'pid_file'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.pid_file; +--replace_column 1 # +SELECT @@global.pid_file; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.pid_file= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.pid_file= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'plugin_dir'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.plugin_dir; +--replace_column 1 # +SELECT @@global.plugin_dir; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.plugin_dir= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.plugin_dir= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'port'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.port; +--replace_column 1 # +SELECT @@global.port; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.port= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.port= 7; +# +--echo # +SHOW VARIABLES like 'protocol_version'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.protocol_version; +SELECT @@global.protocol_version; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.protocol_version= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.protocol_version= 7; +# +--echo # +SHOW VARIABLES like 'skip_external_locking'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.skip_external_locking; +SELECT @@global.skip_external_locking; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.skip_external_locking= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.skip_external_locking= true; +# +--echo # +SHOW VARIABLES like 'skip_networking'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.skip_networking; +SELECT @@global.skip_networking; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.skip_networking= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.skip_networking= true; +# +--echo # +SHOW VARIABLES like 'skip_show_database'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.skip_show_database; +SELECT @@global.skip_show_database; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.skip_show_database= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.skip_show_database= true; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'thread_stack'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.thread_stack; +--replace_column 1 # +SELECT @@global.thread_stack; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.thread_stack= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.thread_stack= 7; +# diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 112fbdf1da7..9e22006ea67 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -510,7 +510,7 @@ drop table t1; # create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; ---error ER_WRONG_USAGE +--error ER_KEY_DOES_NOT_EXITS select b from v1 use index (some_index) where b=1; drop view v1; drop table t1; @@ -3421,11 +3421,11 @@ drop table t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); CREATE VIEW v1 AS SELECT * FROM t1; ---error ER_WRONG_USAGE +--error ER_KEY_DOES_NOT_EXITS SELECT * FROM v1 USE KEY(non_existant); ---error ER_WRONG_USAGE +--error ER_KEY_DOES_NOT_EXITS SELECT * FROM v1 FORCE KEY(non_existant); ---error ER_WRONG_USAGE +--error ER_KEY_DOES_NOT_EXITS SELECT * FROM v1 IGNORE KEY(non_existant); DROP VIEW v1; @@ -3564,6 +3564,32 @@ DROP VIEW v1; CREATE VIEW v1 AS SELECT 1; DROP VIEW v1; +# +# Bug #33461: SELECT ... FROM <view> USE INDEX (...) throws an error +# + +CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2)); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3); +SELECT * FROM t1 USE INDEX (PRIMARY) WHERE c1=2; +SELECT * FROM t1 USE INDEX (c2) WHERE c2=2; + +CREATE VIEW v1 AS SELECT c1, c2 FROM t1; +SHOW INDEX FROM v1; +--error ER_KEY_DOES_NOT_EXITS +SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2; +--error ER_KEY_DOES_NOT_EXITS +SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2; +--error ER_KEY_DOES_NOT_EXITS +SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2; +--error ER_KEY_DOES_NOT_EXITS +SELECT * FROM v1 USE INDEX (c2) WHERE c2=2; +--error ER_KEY_DOES_NOT_EXITS +SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2; +--error ER_KEY_DOES_NOT_EXITS +SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2; + +DROP VIEW v1; +DROP TABLE t1; --echo # ----------------------------------------------------------------- --echo # -- End of 5.0 tests. diff --git a/mysql-test/t/wait_timeout_func.test b/mysql-test/t/wait_timeout_func.test index f7467062b61..6b7c8d016d2 100644 --- a/mysql-test/t/wait_timeout_func.test +++ b/mysql-test/t/wait_timeout_func.test @@ -11,98 +11,87 @@ # Creation Date: 2008-03-07 # # Author: Salman Rawala # # # +# Modified: HHunger 2008-08-27 Simplified the test and replaced the sleeps. # +# # # Description: Test Cases of Dynamic System Variable wait_timeout # # that checks the functionality of this variable # # # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html#option_mysqld_wait_timeouts # +# Reference: # +# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html # # # ############################################################################### --source include/not_embedded.inc ---disable_warnings -drop table if exists t1; ---enable_warnings - -############################## -# Creating two new tables # -############################## - ---echo ## Creating new table t1 ## -CREATE TABLE t1 -( -id INT NOT NULL auto_increment, -PRIMARY KEY (id), -name VARCHAR(30) -); - -SET @old_wait_timeout = @@global.wait_timeout; +SET @start_value= @@global.wait_timeout; --echo '#--------------------FN_DYNVARS_186_01-------------------------#' ####################################################################### -# Setting initial value of interactive_timeout greater than sleep and -# verifying its behavior on session scope +# 1. test of scope session ####################################################################### ---echo ## Creating new connection test_con1 ## +SET @start_time= UNIX_TIMESTAMP(); +--echo connect (test_con1, localhost, root,,); connect (test_con1, localhost, root,,); connection test_con1; ---echo ## Setting value of variable to 5 ## -SET @@session.wait_timeout = 5; - ---echo ## Inserting record in table t1 ## -INSERT into t1(name) values('Record_1'); +# If not explicitly changed, @@session.wait_timeout equals @@global.wait_timeout. +SELECT @@session.wait_timeout = @@global.wait_timeout AS 'Expect 1'; ---echo ## Using sleep to check timeout ## -sleep 4; - - ---echo '#--------------------FN_DYNVARS_186_02-------------------------#' -####################################################################### -# Setting initial value of interactive_timeout greater than sleep and -# verifying its behavior on global scope -####################################################################### +# Find a small value <> @@global.wait_timeout. +let $session_value = +`SELECT IF(@@global.wait_timeout <> 2 OR @@global.wait_timeout IS NULL, 2, 3)`; +--replace_result $session_value <session_value> +eval SET @@session.wait_timeout = $session_value; ---echo ## Setting value of variable ## -SET @@global.wait_timeout = 5; - ---echo ## Creating new connection test_con2 ## +--echo connect (test_con2, localhost, root,,); connect (test_con2, localhost, root,,); connection test_con2; -INSERT into t1(name) values('Record_2'); - ---echo ## Using sleep to check timeout ## -sleep 4; - +--replace_result $session_value <session_value> +eval SET @@session.wait_timeout = $session_value - 1; +--echo connection default; +connection default; +--echo wait until connections ready +let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist; +--source include/wait_condition.inc +SELECT info FROM information_schema.processlist; --echo '#--------------------FN_DYNVARS_186_03-------------------------#' ####################################################################### -# Setting initial value of interactive_timeout less than sleep and -# verifying its behavior on global scope +# 2. test of scope global ####################################################################### ---echo ## Setting value of variable to 1 ## -SET @@global.wait_timeout = 1; +# Find a small value <> @@global.wait_timeout. +let $global_value = $session_value + 1; +--replace_result $global_value <global_value> +eval SET @@global.wait_timeout= $global_value; ---echo ## Creating new connection ## +# Changing the @@global.wait_timeout has no influence on the +# @@session.wait_timeout of already established sessions. +SELECT @@session.wait_timeout = @start_value AS 'Expect 1'; + +--echo connect (test_con3, localhost, root,,); connect (test_con3, localhost, root,,); connection test_con3; -INSERT into t1(name) values('Record_3'); - ---echo ## Using sleep to check timeout ## -sleep 5; - ---echo ## We cannot test it further because the server closes the connection due to wait_timeout ## ---Error 2006 -SELECT * from t1; +# If not explicitly changed, @@session.wait_timeout equals @@global.wait_timeout. +SELECT @@session.wait_timeout = @@global.wait_timeout AS 'Expect 1'; +--echo connection default; connection default; -DROP TABLE t1; -SET @@global.wait_timeout = @old_wait_timeout; +# We can be sure that the connections test_con1 and test_con2 must be +# established because both have already executed a SET @@session.wait_timeout. +# This means they are or at least were visible within the processlist. +# Therefore we can now simply wait till both disappear from the processlist. +let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist; +--source include/wait_condition.inc +SELECT info FROM information_schema.processlist; + +--replace_result $global_value <global_value> $session_value <session_value>; +eval SELECT UNIX_TIMESTAMP() - @start_time >= $global_value + $session_value; +SET @@global.wait_timeout= @start_value; + |