diff options
Diffstat (limited to 'mysql-test')
52 files changed, 983 insertions, 217 deletions
diff --git a/mysql-test/include/loaddata_autocom.inc b/mysql-test/include/loaddata_autocom.inc new file mode 100644 index 00000000000..cca56709331 --- /dev/null +++ b/mysql-test/include/loaddata_autocom.inc @@ -0,0 +1,23 @@ +# Test if the engine does autocommit in LOAD DATA INFILE, or not +# (NDB wants to do, others don't). + +eval SET SESSION STORAGE_ENGINE = $engine_type; + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# NDB does not support the create option 'Binlog of table with BLOB attribute and no PK' +# So use a dummy PK here. +create table t1 (id int unsigned not null auto_increment primary key, a text, b text); +start transaction; +load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''' (a, b); +commit; +select count(*) from t1; +truncate table t1; +start transaction; +load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''' (a, b); +rollback; +select count(*) from t1; + +drop table t1; diff --git a/mysql-test/include/not_valgrind.inc b/mysql-test/include/not_valgrind.inc new file mode 100644 index 00000000000..bdddaf534ca --- /dev/null +++ b/mysql-test/include/not_valgrind.inc @@ -0,0 +1,4 @@ +--require r/not_valgrind.require +--disable_query_log +eval select $VALGRIND_TEST as using_valgrind; +--enable_query_log diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index bbf2a9e2cca..4e3a8974002 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -3788,6 +3788,11 @@ sub run_mysqltest ($) { mtr_add_arg($args, "--big-test"); } + if ( $opt_valgrind ) + { + mtr_add_arg($args, "--valgrind"); + } + if ( $opt_compress ) { mtr_add_arg($args, "--compress"); diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 2b9602215fb..f5e4054a385 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1340,6 +1340,19 @@ select a from t1 group by a; a e drop table t1; +set names utf8; +grant select on test.* to юзер_юзер@localhost; +user() +юзер_юзер@localhost +revoke all on test.* from юзер_юзер@localhost; +drop user юзер_юзер@localhost; +create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; +use имя_базы_в_кодировке_утф8_длиной_больше_чем_45; +select database(); +database() +имя_базы_в_кодировке_утф8_длиной_больше_чем_45 +drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; +use test; CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8; INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa'); SELECT id FROM t1; diff --git a/mysql-test/r/events.result b/mysql-test/r/events.result index 13842ceef54..146f46edc2b 100644 --- a/mysql-test/r/events.result +++ b/mysql-test/r/events.result @@ -17,13 +17,13 @@ db_x SHOW TABLES FROM db_x; Tables_in_db_x x_table -SET GLOBAL event_scheduler=2; +SET GLOBAL event_scheduler=off; DROP EVENT e_x1; DROP EVENT e_x2; DROP DATABASE db_x; DROP USER pauline@localhost; USE events_test; -SET GLOBAL event_scheduler=2; +SET GLOBAL event_scheduler=off; drop event if exists event1; Warnings: Note 1305 Event event1 does not exist @@ -85,13 +85,24 @@ SHOW EVENTS; Db Name Definer Type Execute at Interval value Interval field Starts Ends Status events_test event_starts_test root@localhost RECURRING NULL 20 SECOND # # ENABLED DROP EVENT event_starts_test; +create table test_nested(a int); create event e_43 on schedule every 1 second do set @a = 5; -set global event_scheduler = 1; alter event e_43 do alter event e_43 do set @a = 4; +ERROR HY000: Recursivity of EVENT DDL statements is forbidden when body is present +alter event e_43 do +begin +alter event e_43 on schedule every 5 minute; +insert into test_nested values(1); +end| +set global event_scheduler = on; select db, name, body, status, interval_field, interval_value from mysql.event; db name body status interval_field interval_value -events_test e_43 set @a = 4 ENABLED SECOND 1 +events_test e_43 begin +alter event e_43 on schedule every 5 minute; +insert into test_nested values(1); +end ENABLED MINUTE 5 drop event e_43; +drop table test_nested; "Let's check whether we can use non-qualified names" create table non_qualif(a int); create event non_qualif_ev on schedule every 10 minute do insert into non_qualif values (800219); @@ -100,7 +111,15 @@ a 800219 drop event non_qualif_ev; drop table non_qualif; -set global event_scheduler = 2; +alter event non_existant rename to non_existant_too; +ERROR HY000: Unknown event 'non_existant' +set global event_scheduler = off; +create event existant on schedule at now() + interval 1 year do select 12; +alter event non_existant rename to existant; +ERROR HY000: Event 'existant' already exists +alter event existant rename to events_test.existant; +ERROR HY000: Same old and new event name +drop event existant; create table t_event3 (a int, b float); drop event if exists event3; Warnings: @@ -195,6 +214,10 @@ ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' SHOW EVENTS; ERROR 42000: This version of MySQL doesn't yet support 'MICROSECOND' drop event root22; +create event root23 on schedule every -100 year do select 1; +ERROR HY000: INTERVAL is either not positive or too big +create event root23 on schedule every 222222222222222222222 year do select 1; +ERROR HY000: INTERVAL is either not positive or too big drop event root6; drop event root7; drop event root8; @@ -285,19 +308,21 @@ select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_comp db name body definer convert_tz(execute_at, 'UTC', 'SYSTEM') on_completion events_test e_26 set @a = 5 root@localhost 2017-01-01 00:00:00 DROP drop event e_26; -create event e_26 on schedule at NULL disabled do set @a = 5; +create event e_26 on schedule at NULL disable do set @a = 5; ERROR HY000: Incorrect AT value: 'NULL' -create event e_26 on schedule at 'definitely not a datetime' disabled do set @a = 5; +create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5; ERROR HY000: Incorrect AT value: 'definitely not a datetime' set names utf8; create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1; drop event задачка; -set event_scheduler=2; +set event_scheduler=off; ERROR HY000: Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL set global event_scheduler=3; ERROR 42000: Variable 'event_scheduler' can't be set to the value of '3' +set global event_scheduler=disabled; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of 'disabled' "DISABLE the scheduler. Testing that it does not work when the variable is 0" -set global event_scheduler=2; +set global event_scheduler=off; select definer, name, db from mysql.event; definer name db select get_lock("test_lock1", 20); @@ -309,9 +334,8 @@ select definer, name, db from mysql.event; definer name db root@localhost закачка events_test "Should be only 1 process" -select /*1*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*1*/ user, host, db, command, state, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; user host db command state info -event_scheduler localhost NULL Connect Suspended NULL select release_lock("test_lock1"); release_lock("test_lock1") 1 @@ -321,7 +345,7 @@ select count(*) from mysql.event; count(*) 0 "ENABLE the scheduler and get a lock" -set global event_scheduler=1; +set global event_scheduler=on; select get_lock("test_lock2", 20); get_lock("test_lock2", 20) 1 @@ -329,9 +353,9 @@ get_lock("test_lock2", 20) create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20); "Let some time pass to the event starts" "Should have only 2 processes: the scheduler and the locked event" -select /*2*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*2*/ user, host, db, command, state, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; user host db command state info -event_scheduler localhost NULL Connect Sleeping NULL +event_scheduler localhost NULL Daemon Waiting for next activation NULL root localhost events_test Connect User lock select get_lock("test_lock2", 20) "Release the mutex, the event worker should finish." "Release the mutex, the event worker should finish." @@ -345,20 +369,19 @@ get_lock("test_lock2_1", 20) 1 create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20); "Should have only 3 processes: the scheduler, our conn and the locked event" -select /*3*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*3*/ user, host, db, command, state, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; user host db command state info -event_scheduler localhost NULL Connect Sleeping NULL +event_scheduler localhost NULL Daemon Waiting for next activation NULL root localhost events_test Connect User lock select get_lock("test_lock2_1", 20) -set global event_scheduler=2; +set global event_scheduler=off; "Should have only our process now:" -select /*4*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*4*/ user, host, db, command, state, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; user host db command state info -event_scheduler localhost NULL Connect Suspended NULL root localhost events_test Connect User lock select get_lock("test_lock2_1", 20) drop event закачка21; create table t_16 (s1 int); create trigger t_16_bi before insert on t_16 for each row create event e_16 on schedule every 1 second do set @a=5; -ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger. +ERROR HY000: Recursivity of EVENT DDL statements is forbidden when body is present drop table t_16; create event white_space on schedule every 10 hour diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index c8a352ef8dc..458fd151130 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -17,28 +17,28 @@ DROP EVENT ДОЛЕН_регистър_утф8; SET NAMES latin1; set @a=3; CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5; -call p_16(); -"Here we used to crash!" -call p_16(); -ERROR HY000: Event 'e_16' already exists -call p_16(); -ERROR HY000: Event 'e_16' already exists -DROP EVENT e_16; -CALL p_16(); -CALL p_16(); -ERROR HY000: Event 'e_16' already exists -DROP PROCEDURE p_16; -DROP EVENT e_16; +ERROR HY000: Recursivity of EVENT DDL statements is forbidden when body is present create event e_55 on schedule at 99990101000000 do drop table t; ERROR HY000: Incorrect AT value: '99990101000000' create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t; ERROR HY000: Incorrect STARTS value: '99990101000000' create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; ERROR HY000: ENDS is either invalid or before STARTS -set global event_scheduler=2; -"Wait a bit to settle down" +create event e_55 on schedule at 10000101000000 do drop table t; +ERROR HY000: Activation (AT) time is in the past +create event e_55 on schedule at 20000101000000 do drop table t; +ERROR HY000: Activation (AT) time is in the past +create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'starts 10000101000000 do drop table t' at line 1 +create event e_55 on schedule at 20200101000000 ends 10000101000000 do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ends 10000101000000 do drop table t' at line 1 +create event e_55 on schedule at 20200101000000 starts 10000101000000 ends 10000101000000 do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'starts 10000101000000 ends 10000101000000 do drop table t' at line 1 +create event e_55 on schedule every 10 hour starts 10000101000000 do drop table t; +ERROR HY000: Incorrect STARTS value: '10000101000000' +set global event_scheduler=off; delete from mysql.event; -set global event_scheduler= 1; +set global event_scheduler= on; set @old_sql_mode:=@@sql_mode; set sql_mode=ansi; select get_lock('test_bug16407', 60); @@ -50,14 +50,13 @@ select get_lock('test_bug16407', 60); drop table "hashed_num"; end| "Now if everything is fine the event has compiled and is locked -select /*1*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; -user host db command state info -event_scheduler localhost NULL Connect Sleeping NULL -root localhost events_test Connect User lock select get_lock('test_bug16407', 60) +select /*1*/ user, host, db, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +user host db info +root localhost events_test select get_lock('test_bug16407', 60) select release_lock('test_bug16407'); release_lock('test_bug16407') 1 -set global event_scheduler= 2; +set global event_scheduler= off; select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; event_schema event_name sql_mode events_test e_16407 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI @@ -68,6 +67,11 @@ select event_schema, event_name, sql_mode from information_schema.events order b event_schema event_name sql_mode events_test e_16407 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER drop event e_16407; +set sql_mode="ansi"; +select get_lock('ee_16407_2', 60); +get_lock('ee_16407_2', 60) +1 +set global event_scheduler= 1; "Another sql_mode test" set sql_mode="traditional"; create table events_smode_test(ev_name char(10), a date) engine=myisam; @@ -75,6 +79,7 @@ create table events_smode_test(ev_name char(10), a date) engine=myisam; create event ee_16407_2 on schedule every 60 second do begin select get_lock('ee_16407_2', 60) /*ee_16407_2*/; +select release_lock('ee_16407_2'); insert into events_test.events_smode_test values('ee_16407_2','1980-19-02'); end| insert into events_smode_test values ('test','1980-19-02')| @@ -83,6 +88,7 @@ ERROR 22007: Incorrect date value: '1980-19-02' for column 'a' at row 1 create event ee_16407_3 on schedule every 60 second do begin select get_lock('ee_16407_2', 60) /*ee_16407_3*/; +select release_lock('ee_16407_2'); insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19'); insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29'); end| @@ -91,6 +97,7 @@ set sql_mode=""| create event ee_16407_4 on schedule every 60 second do begin select get_lock('ee_16407_2', 60) /*ee_16407_4*/; +select release_lock('ee_16407_2'); insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956'); end| select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; @@ -98,24 +105,19 @@ event_schema event_name sql_mode events_test ee_16407_2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_3 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER events_test ee_16407_4 -set sql_mode="ansi"; -select get_lock('ee_16407_2', 60); -get_lock('ee_16407_2', 60) -1 -set global event_scheduler= 1; -select /*2*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; -user host db command state info -event_scheduler localhost NULL Connect Sleeping NULL -root localhost events_test Connect User lock select get_lock('ee_16407_2', 60) -root localhost events_test Connect User lock select get_lock('ee_16407_2', 60) -root localhost events_test Connect User lock select get_lock('ee_16407_2', 60) +select /*2*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +user host db info +event_scheduler localhost NULL NULL +root localhost events_test select get_lock('ee_16407_2', 60) +root localhost events_test select get_lock('ee_16407_2', 60) +root localhost events_test select get_lock('ee_16407_2', 60) select release_lock('ee_16407_2'); release_lock('ee_16407_2') 1 -select /*3*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; -user host db command state info -event_scheduler localhost NULL Connect Sleeping NULL -set global event_scheduler= 2; +select /*3*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +user host db info +event_scheduler localhost NULL NULL +set global event_scheduler= off; select * from events_smode_test order by ev_name, a; ev_name a ee_16407_3 1980-02-19 @@ -132,37 +134,39 @@ drop event ee_16407_3; drop event ee_16407_4; "And now one last test regarding sql_mode and call of SP from an event" delete from events_smode_test; +set sql_mode='ansi'; +select get_lock('ee_16407_5', 60); +get_lock('ee_16407_5', 60) +1 +set global event_scheduler= on; set sql_mode='traditional'; create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end| create procedure ee_16407_6_pendant() begin insert into events_test.events_smode_test values('ee_16407_6','2004-02-29'); end| create event ee_16407_5 on schedule every 60 second do begin select get_lock('ee_16407_5', 60) /*ee_16407_5*/; +select release_lock('ee_16407_5'); call events_test.ee_16407_5_pendant(); end| create event ee_16407_6 on schedule every 60 second do begin select get_lock('ee_16407_5', 60) /*ee_16407_6*/; +select release_lock('ee_16407_5'); call events_test.ee_16407_6_pendant(); end| -set sql_mode='ansi'; -select get_lock('ee_16407_5', 60); -get_lock('ee_16407_5', 60) -1 -set global event_scheduler= 1; "Should have 2 locked processes" -select /*4*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; -user host db command state info -event_scheduler localhost NULL Connect Sleeping NULL -root localhost events_test Connect User lock select get_lock('ee_16407_5', 60) -root localhost events_test Connect User lock select get_lock('ee_16407_5', 60) +select /*4*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +user host db info +event_scheduler localhost NULL NULL +root localhost events_test select get_lock('ee_16407_5', 60) +root localhost events_test select get_lock('ee_16407_5', 60) select release_lock('ee_16407_5'); release_lock('ee_16407_5') 1 "Should have 0 processes locked" -select /*5*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; -user host db command state info -event_scheduler localhost NULL Connect Sleeping NULL +select /*5*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +user host db info +event_scheduler localhost NULL NULL select * from events_smode_test order by ev_name, a; ev_name a ee_16407_6 2004-02-29 @@ -175,10 +179,10 @@ drop event ee_16407_5; drop event ee_16407_6; drop procedure ee_16407_5_pendant; drop procedure ee_16407_6_pendant; -set global event_scheduler= 2; +set global event_scheduler= off; drop table events_smode_test; set sql_mode=@old_sql_mode; -set global event_scheduler=2; +set global event_scheduler=off; delete from mysql.user where User like 'mysqltest_%'; delete from mysql.db where User like 'mysqltest_%'; flush privileges; @@ -201,4 +205,12 @@ events_test mysqltest_user1 mysqltest_user1@localhost RECURRING ENABLED drop event events_test.mysqltest_user1; drop user mysqltest_user1@localhost; drop database mysqltest_db1; +create event e_53 on schedule at (select s1 from ttx) do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select s1 from ttx) do drop table t' at line 1 +create event e_53 on schedule every (select s1 from ttx) second do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select s1 from ttx) second do drop table t' at line 1 +create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select s1 from ttx) do drop table t' at line 1 +create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select s1 from ttx) do drop table t' at line 1 drop database events_test; diff --git a/mysql-test/r/events_logs_tests.result b/mysql-test/r/events_logs_tests.result index 950090399d5..335ca848387 100644 --- a/mysql-test/r/events_logs_tests.result +++ b/mysql-test/r/events_logs_tests.result @@ -1,5 +1,5 @@ -create database if not exists events_test; -use events_test; +CREATE DATABASE IF NOT EXISTS events_test; +USE events_test; "We use procedure here because its statements won't be logged into the general log" "If we had used normal select that are logged in different ways depending on whether" "the test suite is run in normal mode or with --ps-protocol" @@ -8,21 +8,24 @@ BEGIN SELECT user_host, argument FROM mysql.general_log WHERE argument LIKE '%alabala%'; END| "Check General Query Log" -SET GLOBAL event_scheduler=2; -create event log_general on schedule every 1 minute do SELect 'alabala', sleep(3) from dual; -TRUNCATE mysql.general_log; -"1 row, the current statement!" -call select_general_log(); +CALL select_general_log(); user_host argument -SET GLOBAL event_scheduler=1; +USER_HOST CREATE procedure select_general_log() +BEGIN +SELECT user_host, argument FROM mysql.general_log WHERE argument LIKE '%alabala%'; +END +SET GLOBAL event_scheduler=on; +TRUNCATE mysql.general_log; +CREATE EVENT log_general ON SCHEDULE EVERY 1 MINUTE DO SELECT 'alabala', SLEEP(1) FROM DUAL; "Wait the scheduler to start" -"Should see 3 rows - the 'SELect' is in the middle. The other two are selects from general_log" -call select_general_log(); +"Should see 2 rows - the 'SELECT' is in the middle. The other two are selects from general_log" +CALL select_general_log(); user_host argument -USER_HOST SELect 'alabala', sleep(3) from dual +USER_HOST CREATE EVENT log_general ON SCHEDULE EVERY 1 MINUTE DO SELECT 'alabala', SLEEP(1) FROM DUAL +USER_HOST SELECT 'alabala', SLEEP(1) FROM DUAL DROP PROCEDURE select_general_log; DROP EVENT log_general; -SET GLOBAL event_scheduler=2; +SET GLOBAL event_scheduler=off; "Check slow query log" "Save the values" SET @old_global_long_query_time:=(select get_value()); @@ -52,19 +55,20 @@ TRUNCATE mysql.slow_log; CREATE TABLE slow_event_test (slo_val tinyint, val tinyint); SET SESSION long_query_time=1; "This won't go to the slow log" -CREATE EVENT long_event ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(3); SELECT * FROM slow_event_test; slo_val val -SET GLOBAL event_scheduler=1; +SET SESSION long_query_time=1; +SET GLOBAL event_scheduler=on; +CREATE EVENT long_event ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(1.5); "Sleep some more time than the actual event run will take" SHOW VARIABLES LIKE 'event_scheduler'; Variable_name Value -event_scheduler 1 +event_scheduler ON "Check our table. Should see 1 row" SELECT * FROM slow_event_test; slo_val val 4 0 -"Check slow log. Should not see anything because 3 is under the threshold of 4 for GLOBAL, though over SESSION which is 2" +"Check slow log. Should not see anything because 1.5 is under the threshold of 300 for GLOBAL, though over SESSION which is 2" SELECT user_host, query_time, db, sql_text FROM mysql.slow_log; user_host query_time db sql_text "This should go to the slow log" @@ -89,4 +93,4 @@ TRUNCATE mysql.slow_log; DROP TABLE slow_event_test; SET GLOBAL long_query_time =@old_global_long_query_time; SET SESSION long_query_time =@old_session_long_query_time; -drop database events_test; +DROP DATABASE events_test; diff --git a/mysql-test/r/events_restart_phase1.result b/mysql-test/r/events_restart_phase1.result new file mode 100644 index 00000000000..a7a46fa0ab1 --- /dev/null +++ b/mysql-test/r/events_restart_phase1.result @@ -0,0 +1,12 @@ +create database if not exists mysqltest_events_test; +use mysqltest_events_test; +set global event_scheduler=off; +create table execution_log(name char(10)); +create event abc1 on schedule every 1 second do insert into execution_log value('abc1'); +create event abc2 on schedule every 1 second do insert into execution_log value('abc2'); +create event abc3 on schedule every 1 second do insert into execution_log value('abc3'); +select name from execution_log; +name +insert into mysql.event values ('db1','bad','select 42','root@localhost',NULL,1000,'MICROSECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment1'); +insert into mysql.event values ('db1','bad2','sect','root@localhost',NULL,1000,'SECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment2'); +"Now we restart the server" diff --git a/mysql-test/r/events_restart_phase2.result b/mysql-test/r/events_restart_phase2.result new file mode 100644 index 00000000000..703cb92324f --- /dev/null +++ b/mysql-test/r/events_restart_phase2.result @@ -0,0 +1,6 @@ +use mysqltest_events_test; +"Should get 0 rows because the queue aborted run +select distinct name from execution_log order by name; +name +delete from mysql.event where name like 'bad%'; +"Now restart the server again" diff --git a/mysql-test/r/events_restart_phase3.result b/mysql-test/r/events_restart_phase3.result new file mode 100644 index 00000000000..e653d6a7c23 --- /dev/null +++ b/mysql-test/r/events_restart_phase3.result @@ -0,0 +1,12 @@ +use mysqltest_events_test; +"Should get 3 rows : abc1, abc2, abc3 +select distinct name from execution_log order by name; +name +abc1 +abc2 +abc3 +drop event abc1; +drop event abc2; +drop event abc3; +drop table execution_log; +drop database mysqltest_events_test; diff --git a/mysql-test/r/events_scheduling.result b/mysql-test/r/events_scheduling.result index eb44751c176..180c2e4883d 100644 --- a/mysql-test/r/events_scheduling.result +++ b/mysql-test/r/events_scheduling.result @@ -1,12 +1,47 @@ CREATE DATABASE IF NOT EXISTS events_test; USE events_test; +SET GLOBAL event_scheduler=OFF; +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler OFF +SET GLOBAL event_scheduler=1; +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=0; +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler OFF +SET GLOBAL event_scheduler=ON; +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=DISABLED; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of 'DISABLED' +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=-1; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of '-1' +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=2; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of '2' +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +SET GLOBAL event_scheduler=5; +ERROR 42000: Variable 'event_scheduler' can't be set to the value of '5' +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON CREATE TABLE table_1(a int); CREATE TABLE table_2(a int); CREATE TABLE table_3(a int); CREATE TABLE table_4(a int); CREATE TABLE T19170(s1 TIMESTAMP); -SET GLOBAL event_scheduler=1; -CREATE EVENT E19170 ON SCHEDULE EVERY 2 SECOND DO INSERT INTO T19170 VALUES(CURRENT_TIMESTAMP); +SET GLOBAL event_scheduler=ON; CREATE EVENT two_sec ON SCHEDULE EVERY 2 SECOND DO INSERT INTO table_1 VALUES(1); CREATE EVENT start_n_end ON SCHEDULE EVERY 1 SECOND @@ -39,17 +74,12 @@ DROP EVENT start_n_end; DROP EVENT only_one_time; ERROR HY000: Unknown event 'only_one_time' "Should be preserved" -SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS; +SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_NAME; EVENT_NAME STATUS -E19170 ENABLED two_time DISABLED DROP EVENT two_time; DROP TABLE table_1; DROP TABLE table_2; DROP TABLE table_3; DROP TABLE table_4; -"Checking for multiple executions in one second, should not happen -> 0 as result" -SELECT COUNT(*) FROM (SELECT s1, COUNT(*) AS cnt FROM T19170 GROUP BY s1) AS tmp WHERE tmp.cnt > 1; -COUNT(*) -0 DROP DATABASE events_test; diff --git a/mysql-test/r/events_stress.result b/mysql-test/r/events_stress.result index 050fe324426..17eb32b36b7 100644 --- a/mysql-test/r/events_stress.result +++ b/mysql-test/r/events_stress.result @@ -32,15 +32,15 @@ USE events_conn1_test2; SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; COUNT(*) 50 -SET GLOBAL event_scheduler=1; +SET GLOBAL event_scheduler=on; DROP DATABASE events_conn1_test2; -SET GLOBAL event_scheduler=2; +SET GLOBAL event_scheduler=off; SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; COUNT(*) 0 CREATE DATABASE events_conn1_test3; USE events_conn1_test3; -SET GLOBAL event_scheduler=1; +SET GLOBAL event_scheduler=on; SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test3'; COUNT(*) 50 @@ -55,9 +55,9 @@ DROP DATABASE events_conn2_db; DROP DATABASE events_conn3_db; DROP DATABASE events_conn1_test2; DROP DATABASE events_conn1_test3; -SET GLOBAL event_scheduler=2; +SET GLOBAL event_scheduler=off; DROP DATABASE events_conn1_test4; -SET GLOBAL event_scheduler=1; +SET GLOBAL event_scheduler=on; USE events_test; DROP TABLE fill_it1; DROP TABLE fill_it2; diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result index 1e38f762dd1..be2776ef533 100644 --- a/mysql-test/r/limit.result +++ b/mysql-test/r/limit.result @@ -76,3 +76,17 @@ a a 1 drop table t1; +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6),(7); +explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using temporary +select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +c +7 +explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using temporary +select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +c +28 diff --git a/mysql-test/r/loaddata_autocom_innodb.result b/mysql-test/r/loaddata_autocom_innodb.result new file mode 100644 index 00000000000..11a07cdb783 --- /dev/null +++ b/mysql-test/r/loaddata_autocom_innodb.result @@ -0,0 +1,21 @@ +SET SESSION STORAGE_ENGINE = InnoDB; +drop table if exists t1; +create table t1 (id int unsigned not null auto_increment primary key, a text, b text); +start transaction; +load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''' (a, b); +Warnings: +Warning 1261 Row 3 doesn't contain data for all columns +commit; +select count(*) from t1; +count(*) +4 +truncate table t1; +start transaction; +load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''' (a, b); +Warnings: +Warning 1261 Row 3 doesn't contain data for all columns +rollback; +select count(*) from t1; +count(*) +0 +drop table t1; diff --git a/mysql-test/r/loaddata_autocom_ndb.result b/mysql-test/r/loaddata_autocom_ndb.result new file mode 100644 index 00000000000..f98e6a76981 --- /dev/null +++ b/mysql-test/r/loaddata_autocom_ndb.result @@ -0,0 +1,23 @@ +SET SESSION STORAGE_ENGINE = ndbcluster; +drop table if exists t1; +create table t1 (id int unsigned not null auto_increment primary key, a text, b text); +start transaction; +load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''' (a, b); +Warnings: +Warning 1261 Row 3 doesn't contain data for all columns +commit; +select count(*) from t1; +count(*) +4 +truncate table t1; +start transaction; +load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''' (a, b); +Warnings: +Warning 1261 Row 3 doesn't contain data for all columns +rollback; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +select count(*) from t1; +count(*) +4 +drop table t1; diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 99633f5e12a..7dbff4beca5 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -85,6 +85,12 @@ c_cp932 | NULL | NULL | Τη γλώσσα | | NULL | NULL | ᛖᚴ ᚷᛖᛏ | +------+------+---------------------------+ +i j k +NULL 1 NULL +Field Type Null Key Default Extra +i int(11) YES NULL +j int(11) NO +k int(11) YES NULL +------+---+------+ | i | j | k | +------+---+------+ @@ -97,6 +103,10 @@ c_cp932 | j | int(11) | NO | | | | | k | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ +i s1 +1 x +2 NULL +3 +------+------+ | i | s1 | +------+------+ @@ -104,6 +114,13 @@ c_cp932 | 2 | NULL | | 3 | | +------+------+ +unhex('zz') +NULL ++-------------+ +| unhex('zz') | ++-------------+ +| NULL | ++-------------+ create table t1(a int, b varchar(255), c int); Field Type Null Key Default Extra a int(11) YES NULL diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 1e1456bd81a..2b564829541 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -2923,14 +2923,32 @@ drop user mysqltest_1@localhost; create database mysqldump_myDB; use mysqldump_myDB; create user myDB_User; -grant create view, select on mysqldump_myDB.* to myDB_User@localhost; +grant create, create view, select, insert on mysqldump_myDB.* to myDB_User@localhost; create table t1 (c1 int); insert into t1 values (3); use mysqldump_myDB; +create table u1 (f1 int); +insert into u1 values (4); create view v1 (c1) as select * from t1; use mysqldump_myDB; drop view v1; drop table t1; +drop table u1; +revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; +drop user myDB_User; +drop database mysqldump_myDB; +flush privileges; +use mysqldump_myDB; +select * from mysqldump_myDB.v1; +c1 +3 +select * from mysqldump_myDB.u1; +f1 +4 +use mysqldump_myDB; +drop view v1; +drop table t1; +drop table u1; revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; drop user myDB_User; drop database mysqldump_myDB; diff --git a/mysql-test/r/ndb_multi.result b/mysql-test/r/ndb_multi.result index 723712d99ad..1110fb9798c 100644 --- a/mysql-test/r/ndb_multi.result +++ b/mysql-test/r/ndb_multi.result @@ -60,3 +60,35 @@ t2 t3 t4 drop table t1, t2, t3, t4; +create table t1(c1 int key)ENGINE=MyISAM; +insert into t1 values(1),(3),(5); +select * from t1 order by c1; +c1 +1 +3 +5 +create table t1(c1 int key)ENGINE=MyISAM; +insert into t1 values(100),(344),(533); +select * from t1 order by c1; +c1 +100 +344 +533 +alter table t1 engine=ndb; +show tables; +Tables_in_test +t1 +Warnings: +Warning 1050 Local table test.t1 shadows ndb table +select * from t1 order by c1; +c1 +100 +344 +533 +drop table t1; +select * from t1 order by c1; +c1 +1 +3 +5 +drop table t1; diff --git a/mysql-test/r/not_embedded_server.result b/mysql-test/r/not_embedded_server.result index fc49469ae74..63351d187b6 100644 --- a/mysql-test/r/not_embedded_server.result +++ b/mysql-test/r/not_embedded_server.result @@ -2,5 +2,4 @@ prepare stmt1 from ' SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND! execute stmt1; ID USER HOST DB COMMAND TIME STATE INFO number root localhost test Query time preparing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND!='Daemon' -number event_scheduler localhost NULL Connect time Suspended NULL deallocate prepare stmt1; diff --git a/mysql-test/r/not_valgrind.require b/mysql-test/r/not_valgrind.require new file mode 100644 index 00000000000..5d779c6bfcb --- /dev/null +++ b/mysql-test/r/not_valgrind.require @@ -0,0 +1,2 @@ +using_valgrind +0 diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 703d92979f1..684d216f856 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -949,18 +949,24 @@ COUNT(*) Warnings: Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 +Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' +Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid'; COUNT(*) 0 Warnings: Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 +Warning 1292 Truncated incorrect DOUBLE value: '20050328 invalid' +Warning 1292 Truncated incorrect DOUBLE value: '20050328 invalid' SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'; COUNT(*) 0 Warnings: Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 +Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' +Warning 1292 Truncated incorrect DOUBLE value: '20050327 invalid' show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 776a86ad2d5..4141e6061d2 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -896,3 +896,48 @@ EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge idx1,idx2 idx1,idx2 4,4 NULL 4 Using sort_union(idx1,idx2); Using where DROP TABLE t1; +CREATE TABLE t1 ( +item char(20) NOT NULL default '', +started datetime NOT NULL default '0000-00-00 00:00:00', +price decimal(16,3) NOT NULL default '0.000', +PRIMARY KEY (item,started) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('A1','2005-11-01 08:00:00',1000), +('A1','2005-11-15 00:00:00',2000), +('A1','2005-12-12 08:00:00',3000), +('A2','2005-12-01 08:00:00',1000); +EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref PRIMARY PRIMARY 20 const 2 Using where +Warnings: +Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 +Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 +SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; +item started price +A1 2005-11-01 08:00:00 1000.000 +A1 2005-11-15 00:00:00 2000.000 +Warnings: +Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 +Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 +SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; +item started price +A1 2005-11-01 08:00:00 1000.000 +A1 2005-11-15 00:00:00 2000.000 +DROP INDEX `PRIMARY` ON t1; +EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +Warnings: +Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 +SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; +item started price +A1 2005-11-01 08:00:00 1000.000 +A1 2005-11-15 00:00:00 2000.000 +Warnings: +Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00' for column 'started' at row 1 +SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; +item started price +A1 2005-11-01 08:00:00 1000.000 +A1 2005-11-15 00:00:00 2000.000 +DROP TABLE t1; diff --git a/mysql-test/r/rpl_ndb_innodb_trans.result b/mysql-test/r/rpl_ndb_innodb_trans.result new file mode 100644 index 00000000000..148e6247b03 --- /dev/null +++ b/mysql-test/r/rpl_ndb_innodb_trans.result @@ -0,0 +1,103 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +create table t1 (a int, unique(a)) engine=ndbcluster; +create table t2 (a int, unique(a)) engine=innodb; +begin; +insert into t1 values(1); +insert into t2 values(1); +rollback; +select count(*) from t1; +count(*) +0 +select count(*) from t2; +count(*) +0 +select count(*) from t1; +count(*) +0 +select count(*) from t2; +count(*) +0 +begin; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t2; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +Warning 1262 Row 2 was truncated; it contained more data than there were input columns +load data infile '../std_data_ln/rpl_loaddata.dat' into table t1; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +Warning 1262 Row 2 was truncated; it contained more data than there were input columns +rollback; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +select count(*) from t1; +count(*) +2 +select count(*) from t2; +count(*) +0 +select count(*) from t1; +count(*) +2 +select count(*) from t2; +count(*) +0 +delete from t1; +delete from t2; +begin; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t2; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +Warning 1262 Row 2 was truncated; it contained more data than there were input columns +load data infile '../std_data_ln/rpl_loaddata.dat' into table t1; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +Warning 1262 Row 2 was truncated; it contained more data than there were input columns +rollback; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +select count(*) from t1; +count(*) +2 +select count(*) from t2; +count(*) +0 +select count(*) from t1; +count(*) +2 +select count(*) from t2; +count(*) +0 +delete from t1; +delete from t2; +begin; +insert into t2 values(3),(4); +insert into t1 values(3),(4); +load data infile '../std_data_ln/rpl_loaddata.dat' into table t2; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +Warning 1262 Row 2 was truncated; it contained more data than there were input columns +load data infile '../std_data_ln/rpl_loaddata.dat' into table t1; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +Warning 1262 Row 2 was truncated; it contained more data than there were input columns +rollback; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +select count(*) from t1; +count(*) +4 +select count(*) from t2; +count(*) +0 +select count(*) from t1; +count(*) +4 +select count(*) from t2; +count(*) +0 +drop table t1,t2; diff --git a/mysql-test/r/skip_name_resolve.result b/mysql-test/r/skip_name_resolve.result index 855876825ad..8ef52e75238 100644 --- a/mysql-test/r/skip_name_resolve.result +++ b/mysql-test/r/skip_name_resolve.result @@ -10,6 +10,5 @@ user() # show processlist; Id User Host db Command Time State Info -<id> event_scheduler <host> NULL <command> <time> <state> <info> <id> root <host> test <command> <time> <state> <info> <id> root <host> test <command> <time> <state> <info> diff --git a/mysql-test/r/sp-threads.result b/mysql-test/r/sp-threads.result index 3cba437e0a6..c516d7a643f 100644 --- a/mysql-test/r/sp-threads.result +++ b/mysql-test/r/sp-threads.result @@ -34,7 +34,6 @@ lock tables t2 write; call bug9486(); show processlist; Id User Host db Command Time State Info -# event_scheduler localhost NULL Connect # Suspended NULL # root localhost test Sleep # NULL # root localhost test Query # Locked update t1, t2 set val= 1 where id1=id2 # root localhost test Query # NULL show processlist diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result index a15f5013ef6..564b8031c35 100644 --- a/mysql-test/r/sp_notembedded.result +++ b/mysql-test/r/sp_notembedded.result @@ -18,11 +18,9 @@ show processlist; end| call bug4902_2()| Id User Host db Command Time State Info -# event_scheduler localhost NULL Connect # Suspended NULL # root localhost test Query # NULL show processlist call bug4902_2()| Id User Host db Command Time State Info -# event_scheduler localhost NULL Connect # Suspended NULL # root localhost test Query # NULL show processlist drop procedure bug4902_2| drop table if exists t1| diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index 83c6a6f5288..45b84219f2a 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -52,22 +52,22 @@ drop table t1; FLUSH STATUS; SHOW STATUS LIKE 'max_used_connections'; Variable_name Value -Max_used_connections 2 +Max_used_connections 1 SET @save_thread_cache_size=@@thread_cache_size; SET GLOBAL thread_cache_size=3; SHOW STATUS LIKE 'max_used_connections'; Variable_name Value -Max_used_connections 4 +Max_used_connections 3 FLUSH STATUS; SHOW STATUS LIKE 'max_used_connections'; Variable_name Value -Max_used_connections 3 +Max_used_connections 2 SHOW STATUS LIKE 'max_used_connections'; Variable_name Value -Max_used_connections 4 +Max_used_connections 3 SHOW STATUS LIKE 'max_used_connections'; Variable_name Value -Max_used_connections 5 +Max_used_connections 4 SET GLOBAL thread_cache_size=@save_thread_cache_size; show status like 'com_show_status'; Variable_name Value diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index c66adbe871d..08a1b229491 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -572,4 +572,34 @@ def test t1 t1 a a 16 7 1 Y 0 0 63 a ` drop table t1; +create table bug15583(b BIT(8), n INT); +insert into bug15583 values(128, 128); +insert into bug15583 values(null, null); +insert into bug15583 values(0, 0); +insert into bug15583 values(255, 255); +select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from bug15583; +hex(b) bin(b) oct(b) hex(n) bin(n) oct(n) +80 10000000 200 80 10000000 200 +NULL NULL NULL NULL NULL NULL +0 0 0 0 0 0 +FF 11111111 377 FF 11111111 377 +select hex(b)=hex(n) as should_be_onetrue, bin(b)=bin(n) as should_be_onetrue, oct(b)=oct(n) as should_be_onetrue from bug15583; +should_be_onetrue should_be_onetrue should_be_onetrue +1 1 1 +NULL NULL NULL +1 1 1 +1 1 1 +select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from bug15583; +hex(b + 0) bin(b + 0) oct(b + 0) hex(n) bin(n) oct(n) +80 10000000 200 80 10000000 200 +NULL NULL NULL NULL NULL NULL +0 0 0 0 0 0 +FF 11111111 377 FF 11111111 377 +select conv(b, 10, 2), conv(b + 0, 10, 2) from bug15583; +conv(b, 10, 2) conv(b + 0, 10, 2) +10000000 10000000 +NULL NULL +0 0 +11111111 11111111 +drop table bug15583; End of 5.0 tests diff --git a/mysql-test/t/case.test b/mysql-test/t/case.test index 1bdfc5cc10f..63baeebcf12 100644 --- a/mysql-test/t/case.test +++ b/mysql-test/t/case.test @@ -133,8 +133,6 @@ select min(a), min(case when 1=1 then a else NULL end), from t1 where b=3 group by b; drop table t1; -# End of 4.1 tests - # # Tests for bug #9939: conversion of the arguments for COALESCE and IFNULL @@ -154,3 +152,4 @@ SELECT IFNULL(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM, FROM t1 LEFT JOIN t2 ON t1.EMPNUM=t2.EMPNUM; DROP TABLE t1,t2; +# End of 4.1 tests diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 5e3a3f640b1..54ded650a1e 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1069,6 +1069,23 @@ explain select a from t1 group by a; select a from t1 group by a; drop table t1; +# +# Bug#20393: User name truncation in mysql client +# Bug#21432: Database/Table name limited to 64 bytes, not chars, problems with multi-byte +# +set names utf8; +#create user юзер_юзер@localhost; +grant select on test.* to юзер_юзер@localhost; +--exec $MYSQL --default-character-set=utf8 --user=юзер_юзер -e "select user()" +revoke all on test.* from юзер_юзер@localhost; +drop user юзер_юзер@localhost; + +create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; +use имя_базы_в_кодировке_утф8_длиной_больше_чем_45; +select database(); +drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; +use test; + # End of 4.1 tests # diff --git a/mysql-test/t/events.test b/mysql-test/t/events.test index 69c0df842eb..add5dbcaa24 100644 --- a/mysql-test/t/events.test +++ b/mysql-test/t/events.test @@ -18,10 +18,10 @@ CREATE EVENT e_x2 ON SCHEDULE EVERY 1 SECOND DO DROP TABLE x_table; connection default; SHOW DATABASES LIKE 'db_x'; SET GLOBAL event_scheduler=1; ---sleep 1.5 +--sleep 1.2 SHOW DATABASES LIKE 'db_x'; SHOW TABLES FROM db_x; -SET GLOBAL event_scheduler=2; +SET GLOBAL event_scheduler=off; connection priv_conn; DROP EVENT e_x1; DROP EVENT e_x2; @@ -33,7 +33,7 @@ USE events_test; # # END: BUG #17289 Events: missing privilege check for drop database # -SET GLOBAL event_scheduler=2; +SET GLOBAL event_scheduler=off; drop event if exists event1; create event event1 on schedule every 15 minute starts now() ends date_add(now(), interval 5 hour) DO begin end; alter event event1 rename to event2 enable; @@ -81,23 +81,42 @@ SHOW EVENTS; DROP EVENT event_starts_test; # # +create table test_nested(a int); create event e_43 on schedule every 1 second do set @a = 5; -set global event_scheduler = 1; ---sleep 2 +--error ER_EVENT_RECURSIVITY_FORBIDDEN alter event e_43 do alter event e_43 do set @a = 4; ---sleep 2 +delimiter |; +alter event e_43 do +begin + alter event e_43 on schedule every 5 minute; + insert into test_nested values(1); +end| +delimiter ;| +set global event_scheduler = on; +--sleep 3 select db, name, body, status, interval_field, interval_value from mysql.event; drop event e_43; ---sleep 1 +drop table test_nested; --echo "Let's check whether we can use non-qualified names" create table non_qualif(a int); create event non_qualif_ev on schedule every 10 minute do insert into non_qualif values (800219); ---sleep 1 +--sleep 1.1 select * from non_qualif; drop event non_qualif_ev; drop table non_qualif; -set global event_scheduler = 2; + +--error ER_EVENT_DOES_NOT_EXIST +alter event non_existant rename to non_existant_too; + +set global event_scheduler = off; +create event existant on schedule at now() + interval 1 year do select 12; +--error ER_EVENT_ALREADY_EXISTS +alter event non_existant rename to existant; +--error ER_EVENT_SAME_NAME +alter event existant rename to events_test.existant; +drop event existant; + create table t_event3 (a int, b float); drop event if exists event3; @@ -156,6 +175,10 @@ show create event root22; --error ER_NOT_SUPPORTED_YET SHOW EVENTS; drop event root22; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +create event root23 on schedule every -100 year do select 1; +--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG +create event root23 on schedule every 222222222222222222222 year do select 1; drop event root6; drop event root7; drop event root8; @@ -201,34 +224,34 @@ SHOW EVENTS; CREATE TABLE event_like LIKE mysql.event; INSERT INTO event_like SELECT * FROM mysql.event; #sleep a bit or we won't catch the change of time ---sleep 1 +--sleep 1.1 ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; --error ER_CANNOT_LOAD_FROM_TABLE SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ALTER TABLE mysql.event MODIFY db char(20) character set utf8 collate utf8_bin default ''; #wait a bit or we won't see the difference because of seconds resolution ---sleep 1 +--sleep 1.1 SHOW CREATE TABLE mysql.event; --error ER_CANNOT_LOAD_FROM_TABLE SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---sleep 1 +--sleep 1.1 ALTER TABLE mysql.event MODIFY db char(64) character set utf8 collate utf8_bin default ''; ---sleep 1 +--sleep 1.1 --echo "This should work" --replace_column 8 # 9 # SHOW EVENTS; ---sleep 1 +--sleep 1.1 ALTER TABLE mysql.event MODIFY db char(64) character set cp1251 default ''; --error ER_CANNOT_LOAD_FROM_TABLE SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---sleep 1 +--sleep 1.1 ALTER TABLE mysql.event MODIFY db varchar(64) character set utf8 collate utf8_bin default ''; ---sleep 1 +--sleep 1.1 --error ER_CANNOT_LOAD_FROM_TABLE SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; ---sleep 1 +--sleep 1.1 ALTER TABLE mysql.event DROP comment, DROP starts; ---sleep 1 +--sleep 1.1 --error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED SELECT event_name FROM INFORMATION_SCHEMA.EVENTS; DROP TABLE mysql.event; @@ -246,9 +269,9 @@ create event e_26 on schedule at '2017-01-01 00:00:00' disable do set @a = 5; select db, name, body, definer, convert_tz(execute_at, 'UTC', 'SYSTEM'), on_completion from mysql.event; drop event e_26; --error ER_WRONG_VALUE -create event e_26 on schedule at NULL disabled do set @a = 5; +create event e_26 on schedule at NULL disable do set @a = 5; --error ER_WRONG_VALUE -create event e_26 on schedule at 'definitely not a datetime' disabled do set @a = 5; +create event e_26 on schedule at 'definitely not a datetime' disable do set @a = 5; set names utf8; create event задачка on schedule every 123 minute starts now() ends now() + interval 1 month do select 1; @@ -256,13 +279,15 @@ drop event задачка; # event_scheduler is a global var --error ER_GLOBAL_VARIABLE -set event_scheduler=2; +set event_scheduler=off; # event_scheduler could be only either 1 or 2 --error ER_WRONG_VALUE_FOR_VAR set global event_scheduler=3; +--error ER_WRONG_VALUE_FOR_VAR +set global event_scheduler=disabled; --echo "DISABLE the scheduler. Testing that it does not work when the variable is 0" -set global event_scheduler=2; +set global event_scheduler=off; select definer, name, db from mysql.event; select get_lock("test_lock1", 20); create event закачка on schedule every 10 hour do select get_lock("test_lock1", 20); @@ -270,7 +295,7 @@ create event закачка on schedule every 10 hour do select get_lock("test_l select definer, name, db from mysql.event; --echo "Should be only 1 process" -select /*1*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*1*/ user, host, db, command, state, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; select release_lock("test_lock1"); drop event закачка; --echo "Should have 0 events" @@ -280,14 +305,14 @@ select count(*) from mysql.event; # # --echo "ENABLE the scheduler and get a lock" -set global event_scheduler=1; +set global event_scheduler=on; select get_lock("test_lock2", 20); --echo "Create an event which tries to acquire a mutex. The event locks on the mutex" create event закачка on schedule every 10 hour do select get_lock("test_lock2", 20); --echo "Let some time pass to the event starts" --sleep 1 --echo "Should have only 2 processes: the scheduler and the locked event" -select /*2*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info;--echo "Release the mutex, the event worker should finish." +select /*2*/ user, host, db, command, state, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info;--echo "Release the mutex, the event worker should finish." --echo "Release the mutex, the event worker should finish." select release_lock("test_lock2"); drop event закачка; @@ -305,17 +330,18 @@ select get_lock("test_lock2_1", 20); create event закачка21 on schedule every 10 hour do select get_lock("test_lock2_1", 20); --sleep 1 --echo "Should have only 3 processes: the scheduler, our conn and the locked event" -select /*3*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; -set global event_scheduler=2; +select /*3*/ user, host, db, command, state, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +set global event_scheduler=off; +--sleep 0.8 --echo "Should have only our process now:" -select /*4*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*4*/ user, host, db, command, state, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; drop event закачка21; #### # Bug #16410 Events: CREATE EVENT is legal in a CREATE TRIGGER statement # create table t_16 (s1 int); ---error 1422 +--error ER_EVENT_RECURSIVITY_FORBIDDEN create trigger t_16_bi before insert on t_16 for each row create event e_16 on schedule every 1 second do set @a=5; drop table t_16; # @@ -372,7 +398,7 @@ drop event e1; ##drop event закачка4; ##select release_lock("test_lock4"); -##set global event_scheduler=2; +##set global event_scheduler=off; ##select sleep(2); ##--replace_column 1 # 6 # ##show processlist; diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test index e3bbc927299..c9a8842f8f0 100644 --- a/mysql-test/t/events_bugs.test +++ b/mysql-test/t/events_bugs.test @@ -30,19 +30,8 @@ SET NAMES latin1; # START - BUG#16408: Events: crash for an event in a procedure # set @a=3; +--error ER_EVENT_RECURSIVITY_FORBIDDEN CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5; -call p_16(); ---echo "Here we used to crash!" ---error ER_EVENT_ALREADY_EXISTS -call p_16(); ---error ER_EVENT_ALREADY_EXISTS -call p_16(); -DROP EVENT e_16; -CALL p_16(); ---error ER_EVENT_ALREADY_EXISTS -CALL p_16(); -DROP PROCEDURE p_16; -DROP EVENT e_16; # # END - BUG#16408: Events: crash for an event in a procedure # @@ -56,6 +45,19 @@ create event e_55 on schedule at 99990101000000 do drop table t; create event e_55 on schedule every 10 hour starts 99990101000000 do drop table t; --error ER_EVENT_ENDS_BEFORE_STARTS create event e_55 on schedule every 10 minute ends 99990101000000 do drop table t; +--error ER_EVENT_EXEC_TIME_IN_THE_PAST +create event e_55 on schedule at 10000101000000 do drop table t; +--error ER_EVENT_EXEC_TIME_IN_THE_PAST +create event e_55 on schedule at 20000101000000 do drop table t; +--error ER_PARSE_ERROR +create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; +--error ER_PARSE_ERROR +create event e_55 on schedule at 20200101000000 ends 10000101000000 do drop table t; +--error ER_PARSE_ERROR +create event e_55 on schedule at 20200101000000 starts 10000101000000 ends 10000101000000 do drop table t; +--error ER_WRONG_VALUE +create event e_55 on schedule every 10 hour starts 10000101000000 do drop table t; + # # End - 16396: Events: Distant-future dates become past dates # @@ -63,11 +65,9 @@ create event e_55 on schedule every 10 minute ends 99990101000000 do drop table # # Start - 16407: Events: Changes in sql_mode won't be taken into account # -set global event_scheduler=2; ---echo "Wait a bit to settle down" ---sleep 1 +set global event_scheduler=off; delete from mysql.event; -set global event_scheduler= 1; +set global event_scheduler= on; set @old_sql_mode:=@@sql_mode; set sql_mode=ansi; select get_lock('test_bug16407', 60); @@ -78,11 +78,13 @@ begin drop table "hashed_num"; end| delimiter ;| ---sleep 1 +--sleep 0.8 --echo "Now if everything is fine the event has compiled and is locked -select /*1*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*1*/ user, host, db, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; select release_lock('test_bug16407'); -set global event_scheduler= 2; + +set global event_scheduler= off; + select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; --echo "Let's check whether we change the sql_mode on ALTER EVENT" set sql_mode='traditional'; @@ -90,6 +92,10 @@ alter event e_16407 do select 1; select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; drop event e_16407; +set sql_mode="ansi"; +select get_lock('ee_16407_2', 60); + +set global event_scheduler= 1; --echo "Another sql_mode test" set sql_mode="traditional"; create table events_smode_test(ev_name char(10), a date) engine=myisam; @@ -98,6 +104,7 @@ delimiter |; create event ee_16407_2 on schedule every 60 second do begin select get_lock('ee_16407_2', 60) /*ee_16407_2*/; + select release_lock('ee_16407_2'); insert into events_test.events_smode_test values('ee_16407_2','1980-19-02'); end| --error ER_TRUNCATED_WRONG_VALUE @@ -106,6 +113,7 @@ insert into events_smode_test values ('test','1980-19-02')| create event ee_16407_3 on schedule every 60 second do begin select get_lock('ee_16407_2', 60) /*ee_16407_3*/; + select release_lock('ee_16407_2'); insert into events_test.events_smode_test values ('ee_16407_3','1980-02-19'); insert into events_test.events_smode_test values ('ee_16407_3','1980-02-29'); end| @@ -114,19 +122,17 @@ set sql_mode=""| create event ee_16407_4 on schedule every 60 second do begin select get_lock('ee_16407_2', 60) /*ee_16407_4*/; + select release_lock('ee_16407_2'); insert into events_test.events_smode_test values ('ee_16407_4','10-11-1956'); end| delimiter ;| select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; -set sql_mode="ansi"; -select get_lock('ee_16407_2', 60); -set global event_scheduler= 1; --sleep 1 -select /*2*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*2*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; select release_lock('ee_16407_2'); ---sleep 2 -select /*3*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; -set global event_scheduler= 2; +--sleep 1.2 +select /*3*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; +set global event_scheduler= off; select * from events_smode_test order by ev_name, a; --echo "OK, last check before we drop them" select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; @@ -137,6 +143,11 @@ drop event ee_16407_4; --echo "And now one last test regarding sql_mode and call of SP from an event" delete from events_smode_test; +set sql_mode='ansi'; +select get_lock('ee_16407_5', 60); + +set global event_scheduler= on; + set sql_mode='traditional'; delimiter |; create procedure ee_16407_5_pendant() begin insert into events_test.events_smode_test values('ee_16407_5','2001-02-29'); end| @@ -144,24 +155,23 @@ create procedure ee_16407_6_pendant() begin insert into events_test.events_smode create event ee_16407_5 on schedule every 60 second do begin select get_lock('ee_16407_5', 60) /*ee_16407_5*/; + select release_lock('ee_16407_5'); call events_test.ee_16407_5_pendant(); end| create event ee_16407_6 on schedule every 60 second do begin select get_lock('ee_16407_5', 60) /*ee_16407_6*/; + select release_lock('ee_16407_5'); call events_test.ee_16407_6_pendant(); end| delimiter ;| -set sql_mode='ansi'; -select get_lock('ee_16407_5', 60); -set global event_scheduler= 1; --sleep 1 --echo "Should have 2 locked processes" -select /*4*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*4*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; select release_lock('ee_16407_5'); ---sleep 2 +--sleep 1.3 --echo "Should have 0 processes locked" -select /*5*/ user, host, db, command, state, info from information_schema.processlist where command!='Daemon' and (info is null or info not like '%processlist%') order by info; +select /*5*/ user, host, db, info from information_schema.processlist where (command!='Daemon' || user='event_scheduler') and (info is null or info not like '%processlist%') order by info; select * from events_smode_test order by ev_name, a; --echo "And here we check one more time before we drop the events" select event_schema, event_name, sql_mode from information_schema.events order by event_schema, event_name; @@ -169,7 +179,7 @@ drop event ee_16407_5; drop event ee_16407_6; drop procedure ee_16407_5_pendant; drop procedure ee_16407_6_pendant; -set global event_scheduler= 2; +set global event_scheduler= off; drop table events_smode_test; set sql_mode=@old_sql_mode; # @@ -179,7 +189,7 @@ set sql_mode=@old_sql_mode; # # START - 18897: Events: unauthorized action possible with alter event rename # -set global event_scheduler=2; +set global event_scheduler=off; --disable_warnings delete from mysql.user where User like 'mysqltest_%'; delete from mysql.db where User like 'mysqltest_%'; @@ -209,4 +219,19 @@ drop database mysqltest_db1; # END - 18897: Events: unauthorized action possible with alter event rename # +# +# START - BUG#16394: Events: Crash if schedule contains SELECT +# +--error ER_PARSE_ERROR +create event e_53 on schedule at (select s1 from ttx) do drop table t; +--error ER_PARSE_ERROR +create event e_53 on schedule every (select s1 from ttx) second do drop table t; +--error ER_PARSE_ERROR +create event e_53 on schedule every 5 second starts (select s1 from ttx) do drop table t; +--error ER_PARSE_ERROR +create event e_53 on schedule every 5 second ends (select s1 from ttx) do drop table t; +# +# END - BUG#16394: Events: Crash if schedule contains SELECT +# + drop database events_test; diff --git a/mysql-test/t/events_logs_tests.test b/mysql-test/t/events_logs_tests.test index 5c252b1174b..aee16595ef9 100644 --- a/mysql-test/t/events_logs_tests.test +++ b/mysql-test/t/events_logs_tests.test @@ -1,8 +1,8 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc -create database if not exists events_test; -use events_test; +CREATE DATABASE IF NOT EXISTS events_test; +USE events_test; --echo "We use procedure here because its statements won't be logged into the general log" --echo "If we had used normal select that are logged in different ways depending on whether" --echo "the test suite is run in normal mode or with --ps-protocol" @@ -13,22 +13,19 @@ BEGIN END| delimiter ;| --echo "Check General Query Log" -SET GLOBAL event_scheduler=2; -create event log_general on schedule every 1 minute do SELect 'alabala', sleep(3) from dual; -TRUNCATE mysql.general_log; ---echo "1 row, the current statement!" --replace_column 1 USER_HOST -call select_general_log(); -SET GLOBAL event_scheduler=1; +CALL select_general_log(); +SET GLOBAL event_scheduler=on; +TRUNCATE mysql.general_log; +CREATE EVENT log_general ON SCHEDULE EVERY 1 MINUTE DO SELECT 'alabala', SLEEP(1) FROM DUAL; --echo "Wait the scheduler to start" ---echo "Should see 3 rows - the 'SELect' is in the middle. The other two are selects from general_log" ---sleep 2 +--sleep 1.5 +--echo "Should see 2 rows - the 'SELECT' is in the middle. The other two are selects from general_log" --replace_column 1 USER_HOST -call select_general_log(); +CALL select_general_log(); DROP PROCEDURE select_general_log; DROP EVENT log_general; -SET GLOBAL event_scheduler=2; ---sleep 1 +SET GLOBAL event_scheduler=off; --echo "Check slow query log" --disable_query_log @@ -72,15 +69,16 @@ TRUNCATE mysql.slow_log; CREATE TABLE slow_event_test (slo_val tinyint, val tinyint); SET SESSION long_query_time=1; --echo "This won't go to the slow log" -CREATE EVENT long_event ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(3); SELECT * FROM slow_event_test; -SET GLOBAL event_scheduler=1; +SET SESSION long_query_time=1; +SET GLOBAL event_scheduler=on; +CREATE EVENT long_event ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(1.5); --echo "Sleep some more time than the actual event run will take" ---sleep 5 +--sleep 2 SHOW VARIABLES LIKE 'event_scheduler'; --echo "Check our table. Should see 1 row" SELECT * FROM slow_event_test; ---echo "Check slow log. Should not see anything because 3 is under the threshold of 4 for GLOBAL, though over SESSION which is 2" +--echo "Check slow log. Should not see anything because 1.5 is under the threshold of 300 for GLOBAL, though over SESSION which is 2" SELECT user_host, query_time, db, sql_text FROM mysql.slow_log; --echo "This should go to the slow log" DROP EVENT long_event; @@ -88,7 +86,7 @@ SET SESSION long_query_time=10; SET GLOBAL long_query_time=1; CREATE EVENT long_event2 ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(2); --echo "Sleep some more time than the actual event run will take" ---sleep 3 +--sleep 2.5 --echo "Check our table. Should see 2 rows" SELECT * FROM slow_event_test; --echo "Check slow log. Should see 1 row because 4 is over the threshold of 3 for GLOBAL, though under SESSION which is 10" @@ -102,4 +100,4 @@ DROP TABLE slow_event_test; SET GLOBAL long_query_time =@old_global_long_query_time; SET SESSION long_query_time =@old_session_long_query_time; -drop database events_test; +DROP DATABASE events_test; diff --git a/mysql-test/t/events_restart_phase1.test b/mysql-test/t/events_restart_phase1.test new file mode 100644 index 00000000000..92783ddaef7 --- /dev/null +++ b/mysql-test/t/events_restart_phase1.test @@ -0,0 +1,19 @@ +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +--disable_warnings +create database if not exists mysqltest_events_test; +--enable_warnings + +use mysqltest_events_test; +set global event_scheduler=off; +create table execution_log(name char(10)); +create event abc1 on schedule every 1 second do insert into execution_log value('abc1'); +create event abc2 on schedule every 1 second do insert into execution_log value('abc2'); +create event abc3 on schedule every 1 second do insert into execution_log value('abc3'); +--sleep 1.5 +select name from execution_log; + +insert into mysql.event values ('db1','bad','select 42','root@localhost',NULL,1000,'MICROSECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment1'); +insert into mysql.event values ('db1','bad2','sect','root@localhost',NULL,1000,'SECOND','2006-05-05 17:39:11','2006-05-05 17:39:20','2016-05-05 15:39:24','2016-05-05 15:39:11',NULL,'ENABLED','DROP','','comment2'); +--echo "Now we restart the server" diff --git a/mysql-test/t/events_restart_phase2-master.opt b/mysql-test/t/events_restart_phase2-master.opt new file mode 100644 index 00000000000..673e87f4357 --- /dev/null +++ b/mysql-test/t/events_restart_phase2-master.opt @@ -0,0 +1 @@ +--event-scheduler=on diff --git a/mysql-test/t/events_restart_phase2.test b/mysql-test/t/events_restart_phase2.test new file mode 100644 index 00000000000..845472377ba --- /dev/null +++ b/mysql-test/t/events_restart_phase2.test @@ -0,0 +1,9 @@ +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +use mysqltest_events_test; +--sleep 1.5 +--echo "Should get 0 rows because the queue aborted run +select distinct name from execution_log order by name; +delete from mysql.event where name like 'bad%'; +--echo "Now restart the server again" diff --git a/mysql-test/t/events_restart_phase3-master.opt b/mysql-test/t/events_restart_phase3-master.opt new file mode 100644 index 00000000000..656b9e1b017 --- /dev/null +++ b/mysql-test/t/events_restart_phase3-master.opt @@ -0,0 +1 @@ +--event-scheduler=1 diff --git a/mysql-test/t/events_restart_phase3.test b/mysql-test/t/events_restart_phase3.test new file mode 100644 index 00000000000..76cd9d22752 --- /dev/null +++ b/mysql-test/t/events_restart_phase3.test @@ -0,0 +1,14 @@ +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +use mysqltest_events_test; +--sleep 2 +--echo "Should get 3 rows : abc1, abc2, abc3 +select distinct name from execution_log order by name; + +drop event abc1; +drop event abc2; +drop event abc3; +drop table execution_log; + +drop database mysqltest_events_test; diff --git a/mysql-test/t/events_scheduling.test b/mysql-test/t/events_scheduling.test index 987939bc162..0002cf9f29f 100644 --- a/mysql-test/t/events_scheduling.test +++ b/mysql-test/t/events_scheduling.test @@ -1,17 +1,39 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc +-- source include/not_valgrind.inc CREATE DATABASE IF NOT EXISTS events_test; USE events_test; + +SET GLOBAL event_scheduler=OFF; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=1; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=0; +SHOW VARIABLES LIKE 'event_scheduler'; +SET GLOBAL event_scheduler=ON; +SHOW VARIABLES LIKE 'event_scheduler'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL event_scheduler=DISABLED; +SHOW VARIABLES LIKE 'event_scheduler'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL event_scheduler=-1; +SHOW VARIABLES LIKE 'event_scheduler'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL event_scheduler=2; +SHOW VARIABLES LIKE 'event_scheduler'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL event_scheduler=5; +SHOW VARIABLES LIKE 'event_scheduler'; + CREATE TABLE table_1(a int); CREATE TABLE table_2(a int); CREATE TABLE table_3(a int); CREATE TABLE table_4(a int); CREATE TABLE T19170(s1 TIMESTAMP); -SET GLOBAL event_scheduler=1; +SET GLOBAL event_scheduler=ON; # We need to have 2 to make it safe with valgrind. This is probably because # of when we calculate the timestamp value -CREATE EVENT E19170 ON SCHEDULE EVERY 2 SECOND DO INSERT INTO T19170 VALUES(CURRENT_TIMESTAMP); CREATE EVENT two_sec ON SCHEDULE EVERY 2 SECOND DO INSERT INTO table_1 VALUES(1); CREATE EVENT start_n_end ON SCHEDULE EVERY 1 SECOND @@ -34,12 +56,10 @@ DROP EVENT start_n_end; --error ER_EVENT_DOES_NOT_EXIST DROP EVENT only_one_time; --echo "Should be preserved" -SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS; +SELECT EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_NAME; DROP EVENT two_time; DROP TABLE table_1; DROP TABLE table_2; DROP TABLE table_3; DROP TABLE table_4; ---echo "Checking for multiple executions in one second, should not happen -> 0 as result" -SELECT COUNT(*) FROM (SELECT s1, COUNT(*) AS cnt FROM T19170 GROUP BY s1) AS tmp WHERE tmp.cnt > 1; DROP DATABASE events_test; diff --git a/mysql-test/t/events_stress.test b/mysql-test/t/events_stress.test index 6546bce3a76..fd1ae34ac3c 100644 --- a/mysql-test/t/events_stress.test +++ b/mysql-test/t/events_stress.test @@ -61,11 +61,11 @@ while ($1) } --enable_query_log SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; -SET GLOBAL event_scheduler=1; ---sleep 6 +SET GLOBAL event_scheduler=on; +--sleep 2.5 DROP DATABASE events_conn1_test2; -SET GLOBAL event_scheduler=2; +SET GLOBAL event_scheduler=off; SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; CREATE DATABASE events_conn1_test3; USE events_conn1_test3; @@ -77,7 +77,7 @@ while ($1) dec $1; } --enable_query_log -SET GLOBAL event_scheduler=1; +SET GLOBAL event_scheduler=on; SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test3'; CREATE DATABASE events_conn1_test4; USE events_conn1_test4; @@ -101,7 +101,7 @@ while ($1) } --enable_query_log SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='events_conn1_test2'; ---sleep 6 +--sleep 2.5 connection conn2; --send DROP DATABASE events_conn2_db; @@ -112,9 +112,9 @@ connection default; --send DROP DATABASE events_conn1_test2; DROP DATABASE events_conn1_test3; -SET GLOBAL event_scheduler=2; +SET GLOBAL event_scheduler=off; DROP DATABASE events_conn1_test4; -SET GLOBAL event_scheduler=1; +SET GLOBAL event_scheduler=on; connection conn2; reap; disconnect conn2; diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index f2ff47704c9..0ea89cd0913 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -108,9 +108,6 @@ select 5.1 mod 3, 5.1 mod -3, -5.1 mod 3, -5.1 mod -3; select 5 mod 3, 5 mod -3, -5 mod 3, -5 mod -3; -# End of 4.1 tests - -# # Bug#6726: NOT BETWEEN parse failure # create table t1 (a int, b int); @@ -127,3 +124,5 @@ SELECT GREATEST(1,NULL) FROM DUAL; SELECT LEAST('xxx','aaa',NULL,'yyy') FROM DUAL; SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL; SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL; + +# End of 4.1 tests diff --git a/mysql-test/t/limit.test b/mysql-test/t/limit.test index 6df865278f6..cf7789428b2 100644 --- a/mysql-test/t/limit.test +++ b/mysql-test/t/limit.test @@ -60,4 +60,14 @@ select 1 as a from t1 union all select 1 from dual limit 1; (select 1 as a from t1) union all (select 1 from dual) limit 1; drop table t1; +# +# Bug #21787: COUNT(*) + ORDER BY + LIMIT returns wrong result +# +create table t1 (a int); +insert into t1 values (1),(2),(3),(4),(5),(6),(7); +explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; +select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3; + # End of 4.1 tests diff --git a/mysql-test/t/loaddata_autocom_innodb.test b/mysql-test/t/loaddata_autocom_innodb.test new file mode 100644 index 00000000000..d7f152cb286 --- /dev/null +++ b/mysql-test/t/loaddata_autocom_innodb.test @@ -0,0 +1,4 @@ +--source include/have_innodb.inc +let $engine_type= InnoDB; + +--source include/loaddata_autocom.inc diff --git a/mysql-test/t/loaddata_autocom_ndb.test b/mysql-test/t/loaddata_autocom_ndb.test new file mode 100644 index 00000000000..f4a6743aabe --- /dev/null +++ b/mysql-test/t/loaddata_autocom_ndb.test @@ -0,0 +1,4 @@ +--source include/have_ndb.inc +let $engine_type=ndbcluster; + +--source include/loaddata_autocom.inc diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index cf4e6f4047c..57c4854bf06 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -70,14 +70,22 @@ drop table t1; # # "DESCRIBE" commands may return strange NULLness flags. # +--exec $MYSQL --default-character-set utf8 test -e "create table t1 (i int, j int not null, k int); insert into t1 values (null, 1, null); select * from t1; describe t1; drop table t1;" --exec $MYSQL -t --default-character-set utf8 test -e "create table t1 (i int, j int not null, k int); insert into t1 values (null, 1, null); select * from t1; describe t1; drop table t1;" # # Bug#19564: mysql displays NULL instead of space # +--exec $MYSQL test -e "create table b19564 (i int, s1 char(1)); insert into b19564 values (1, 'x'); insert into b19564 values (2, NULL); insert into b19564 values (3, ' '); select * from b19564 order by i; drop table b19564;" --exec $MYSQL -t test -e "create table b19564 (i int, s1 char(1)); insert into b19564 values (1, 'x'); insert into b19564 values (2, NULL); insert into b19564 values (3, ' '); select * from b19564 order by i; drop table b19564;" # +# Bug#21618: NULL shown as empty string in client +# +--exec $MYSQL test -e "select unhex('zz');" +--exec $MYSQL -t test -e "select unhex('zz');" + +# # Bug#19265 describe command does not work from mysql prompt # diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index f66b07d66ba..1334d5e57c8 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1325,28 +1325,59 @@ drop user mysqltest_1@localhost; # Bug #21527 mysqldump incorrectly tries to LOCK TABLES on the # information_schema database. # +# Bug #21424 mysqldump failing to export/import views +# + +# Do as root connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); connection root; create database mysqldump_myDB; use mysqldump_myDB; create user myDB_User; -grant create view, select on mysqldump_myDB.* to myDB_User@localhost; +grant create, create view, select, insert on mysqldump_myDB.* to myDB_User@localhost; create table t1 (c1 int); insert into t1 values (3); +# Do as a user connect (user1,localhost,myDB_User,,mysqldump_myDB,$MASTER_MYPORT,$MASTER_MYSOCK); connection user1; use mysqldump_myDB; +create table u1 (f1 int); +insert into u1 values (4); create view v1 (c1) as select * from t1; -# Backup should not fail. ---exec $MYSQL_DUMP --all-databases --add-drop-table > $MYSQLTEST_VARDIR/tmp/bug21527.sql +# Backup should not fail for Bug #21527. Flush priviliges test begins. +--exec $MYSQL_DUMP --skip-comments --add-drop-table --flush-privileges --ignore-table=mysql.general_log --ignore-table=mysql.slow_log --databases mysqldump_myDB mysql > $MYSQLTEST_VARDIR/tmp/bug21527.sql # Clean up connection root; use mysqldump_myDB; drop view v1; drop table t1; +drop table u1; +revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; +drop user myDB_User; +drop database mysqldump_myDB; +flush privileges; + +# Bug #21424 continues from here. +# Restore. Flush Privileges test ends. +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21527.sql; + +# Do as a user +connection user1; +use mysqldump_myDB; + +# Ultimate test for correct data. +select * from mysqldump_myDB.v1; +select * from mysqldump_myDB.u1; + +#Final cleanup. +connection root; +use mysqldump_myDB; +drop view v1; +drop table t1; +drop table u1; revoke all privileges on mysqldump_myDB.* from myDB_User@localhost; drop user myDB_User; drop database mysqldump_myDB; diff --git a/mysql-test/t/ndb_multi.test b/mysql-test/t/ndb_multi.test index 6ac37a2b25e..75558ba24fe 100644 --- a/mysql-test/t/ndb_multi.test +++ b/mysql-test/t/ndb_multi.test @@ -68,5 +68,26 @@ show tables like 't4'; show tables; drop table t1, t2, t3, t4; +# bug#21378 +connection server1; +create table t1(c1 int key)ENGINE=MyISAM; +insert into t1 values(1),(3),(5); +select * from t1 order by c1; + +connection server2; +create table t1(c1 int key)ENGINE=MyISAM; +insert into t1 values(100),(344),(533); +select * from t1 order by c1; +connection server1; +alter table t1 engine=ndb; + +connection server2; +show tables; +select * from t1 order by c1; +drop table t1; + +connection server1; +select * from t1 order by c1; +drop table t1; # End of 4.1 tests diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 735d3f11359..89508f468a7 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -709,5 +709,34 @@ EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c; DROP TABLE t1; +# +# Bug #16249: different results for a range with an without index +# when a range condition use an invalid datetime constant +# + +CREATE TABLE t1 ( + item char(20) NOT NULL default '', + started datetime NOT NULL default '0000-00-00 00:00:00', + price decimal(16,3) NOT NULL default '0.000', + PRIMARY KEY (item,started) +) ENGINE=MyISAM; + +INSERT INTO t1 VALUES +('A1','2005-11-01 08:00:00',1000), +('A1','2005-11-15 00:00:00',2000), +('A1','2005-12-12 08:00:00',3000), +('A2','2005-12-01 08:00:00',1000); + +EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; +SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; +SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; + +DROP INDEX `PRIMARY` ON t1; + +EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; +SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00'; +SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00'; + +DROP TABLE t1; # End of 5.0 tests diff --git a/mysql-test/t/rpl_ndb_innodb_trans-slave.opt b/mysql-test/t/rpl_ndb_innodb_trans-slave.opt new file mode 100644 index 00000000000..627becdbfb5 --- /dev/null +++ b/mysql-test/t/rpl_ndb_innodb_trans-slave.opt @@ -0,0 +1 @@ +--innodb diff --git a/mysql-test/t/rpl_ndb_innodb_trans.test b/mysql-test/t/rpl_ndb_innodb_trans.test new file mode 100644 index 00000000000..127c2464570 --- /dev/null +++ b/mysql-test/t/rpl_ndb_innodb_trans.test @@ -0,0 +1,66 @@ +# Test of a transaction mixing the two engines + +-- source include/have_ndb.inc +-- source include/have_innodb.inc +-- source include/master-slave.inc + +create table t1 (a int, unique(a)) engine=ndbcluster; +create table t2 (a int, unique(a)) engine=innodb; + + +begin; +insert into t1 values(1); +insert into t2 values(1); +rollback; + +select count(*) from t1; +select count(*) from t2; +sync_slave_with_master; +select count(*) from t1; +select count(*) from t2; +connection master; + +begin; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t2; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t1; +rollback; + +select count(*) from t1; +select count(*) from t2; +sync_slave_with_master; +select count(*) from t1; +select count(*) from t2; +connection master; + +delete from t1; +delete from t2; +begin; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t2; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t1; +rollback; + +select count(*) from t1; +select count(*) from t2; +sync_slave_with_master; +select count(*) from t1; +select count(*) from t2; +connection master; + +delete from t1; +delete from t2; +begin; +insert into t2 values(3),(4); +insert into t1 values(3),(4); +load data infile '../std_data_ln/rpl_loaddata.dat' into table t2; +load data infile '../std_data_ln/rpl_loaddata.dat' into table t1; +rollback; + +select count(*) from t1; +select count(*) from t2; +sync_slave_with_master; +select count(*) from t1; +select count(*) from t2; +connection master; + +drop table t1,t2; +sync_slave_with_master; diff --git a/mysql-test/t/type_bit.test b/mysql-test/t/type_bit.test index e028dbc51d9..998f8f18fbe 100644 --- a/mysql-test/t/type_bit.test +++ b/mysql-test/t/type_bit.test @@ -238,4 +238,19 @@ select * from t1; --disable_metadata drop table t1; +# +# Bug#15583: BIN()/OCT()/CONV() do not work with BIT values +# +create table bug15583(b BIT(8), n INT); +insert into bug15583 values(128, 128); +insert into bug15583 values(null, null); +insert into bug15583 values(0, 0); +insert into bug15583 values(255, 255); +select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from bug15583; +select hex(b)=hex(n) as should_be_onetrue, bin(b)=bin(n) as should_be_onetrue, oct(b)=oct(n) as should_be_onetrue from bug15583; +select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from bug15583; +select conv(b, 10, 2), conv(b + 0, 10, 2) from bug15583; +drop table bug15583; + + --echo End of 5.0 tests diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index b2a9728de00..644ca506eba 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -144,9 +144,6 @@ select @@version; --replace_column 1 # select @@global.version; -# End of 4.1 tests - -# # Bug #6598: problem with cast(NULL as signed integer); # @@ -212,3 +209,5 @@ insert into t1 values (1,2),(2,3),(3,1); select @var:=f2 from t1 group by f1 order by f2 desc limit 1; select @var; drop table t1; + +# End of 4.1 tests |