From deea727fced84c96e5f3f2f55d3a1a9fb5e475d0 Mon Sep 17 00:00:00 2001 From: Alfranio Correia Date: Thu, 22 Oct 2009 01:10:42 +0100 Subject: BUG#48091 valgrind errors when slave has double not null and master has double null Backporting BUG#38173 to mysql-5.1-bugteam The reason of the bug was incompatibile with the master side behaviour. INSERT query on the master is allowed to insert into a table without specifying values of DEFAULT-less fields if sql_mode is not strict. Fixed with checking sql_mode by the sql thread to decide how to react. Non-strict sql_mode should allow Write_rows event to complete. todo: warnings can be shown via show slave status, still this is a separate rather general issue how to show warnings for the slave threads. --- mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test | 35 +++++++++++---- mysql-test/extra/rpl_tests/rpl_row_tabledefs.test | 15 +++---- mysql-test/suite/rpl/r/rpl_extraCol_innodb.result | 52 +++++----------------- mysql-test/suite/rpl/r/rpl_extraCol_myisam.result | 52 +++++----------------- .../suite/rpl/r/rpl_row_tabledefs_2myisam.result | 44 ++---------------- .../suite/rpl/r/rpl_row_tabledefs_3innodb.result | 44 ++---------------- 6 files changed, 58 insertions(+), 184 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test b/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test index a7b02065144..1eaefa661f9 100644 --- a/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test +++ b/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test @@ -407,13 +407,18 @@ sync_slave_with_master; ########################################### # Bug#22234, Bug#23907 Extra Slave Col is not # erroring on extra col with no default values. -######################################################## +############################################################### +# Error reaction is up to sql_mode of the slave sql (bug#38173) #--echo *** Create t9 on slave *** STOP SLAVE; RESET SLAVE; eval CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), d TIMESTAMP, - e INT NOT NULL) ENGINE=$engine_type; + e INT NOT NULL, + f text not null, + g text, + h blob not null, + i blob) ENGINE=$engine_type; --echo *** Create t9 on Master *** connection master; @@ -431,13 +436,25 @@ set @b1 = 'b1b1b1b1'; set @b1 = concat(@b1,@b1); INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); -connection slave; ---source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # ---query_vertical SHOW SLAVE STATUS -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; -START SLAVE; +# the test would stop slave if @@sql_mode for the sql thread +# was set to strict. Otherwise, as with this tests setup, +# the implicit defaults will be inserted into fields even though +# they are declared without DEFAULT clause. + +sync_slave_with_master; +select * from t9; + +# todo: fix Bug #43992 slave sql thread can't tune own sql_mode ... +# and add/restore waiting for stop test + +#--source include/wait_for_slave_sql_to_stop.inc +#--replace_result $MASTER_MYPORT MASTER_PORT +#--replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # +#--query_vertical SHOW SLAVE STATUS +#SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; +#START SLAVE; + + #--echo *** Drop t9 *** #connection master; diff --git a/mysql-test/extra/rpl_tests/rpl_row_tabledefs.test b/mysql-test/extra/rpl_tests/rpl_row_tabledefs.test index 3b03caee35c..083088f12ff 100644 --- a/mysql-test/extra/rpl_tests/rpl_row_tabledefs.test +++ b/mysql-test/extra/rpl_tests/rpl_row_tabledefs.test @@ -111,21 +111,18 @@ SELECT a,b,x FROM t1_int ORDER BY a; SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit ORDER BY a; SELECT a,b,x FROM t1_char ORDER BY a; -# Each of these inserts should generate an error and stop the slave - connection master; INSERT INTO t9 VALUES (2); sync_slave_with_master; # Now slave is guaranteed to be running connection master; INSERT INTO t1_nodef VALUES (1,2); -connection slave; ---source include/wait_for_slave_sql_to_stop.inc ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 4 # 7 # 8 # 9 # 20 22 # 23 # 33 # 35 36 38 ---query_vertical SHOW SLAVE STATUS -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; -START SLAVE; + +# Last insert on wider slave table succeeds while slave sql sql_mode permits. +# The previous version of the above test expected slave sql to stop. +# bug#38173 relaxed conditions to stop only with the strict mode. +sync_slave_with_master; +select count(*) from t1_nodef; # # Replicating to tables with fewer columns at the end works as of WL#3228 diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result index e2ec78e7adc..63154383e8c 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result @@ -404,7 +404,11 @@ STOP SLAVE; RESET SLAVE; CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), d TIMESTAMP, -e INT NOT NULL) ENGINE='InnoDB'; +e INT NOT NULL, +f text not null, +g text, +h blob not null, +i blob) ENGINE='InnoDB'; *** Create t9 on Master *** CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5) ) ENGINE='InnoDB'; @@ -415,47 +419,11 @@ START SLAVE; set @b1 = 'b1b1b1b1'; set @b1 = concat(@b1,@b1); INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); -SHOW SLAVE STATUS; -Slave_IO_State # -Master_Host 127.0.0.1 -Master_User root -Master_Port # -Connect_Retry 1 -Master_Log_File master-bin.000001 -Read_Master_Log_Pos # -Relay_Log_File # -Relay_Log_Pos # -Relay_Master_Log_File master-bin.000001 -Slave_IO_Running Yes -Slave_SQL_Running No -Replicate_Do_DB -Replicate_Ignore_DB -Replicate_Do_Table -Replicate_Ignore_Table # -Replicate_Wild_Do_Table -Replicate_Wild_Ignore_Table -Last_Errno 1364 -Last_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 330 -Skip_Counter 0 -Exec_Master_Log_Pos # -Relay_Log_Space # -Until_Condition None -Until_Log_File -Until_Log_Pos 0 -Master_SSL_Allowed No -Master_SSL_CA_File -Master_SSL_CA_Path -Master_SSL_Cert -Master_SSL_Cipher -Master_SSL_Key -Seconds_Behind_Master # -Master_SSL_Verify_Server_Cert No -Last_IO_Errno # -Last_IO_Error # -Last_SQL_Errno 1364 -Last_SQL_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 330 -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; -START SLAVE; +select * from t9; +a b c d e f g h i +1 b1b1b1b1b1b1b1b1 Kyle 0000-00-00 00:00:00 0 NULL NULL +2 b1b1b1b1b1b1b1b1 JOE 0000-00-00 00:00:00 0 NULL NULL +3 b1b1b1b1b1b1b1b1 QA 0000-00-00 00:00:00 0 NULL NULL *** Create t10 on slave *** STOP SLAVE; RESET SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result index ed5b4eac27d..d80ac5eea2c 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result @@ -404,7 +404,11 @@ STOP SLAVE; RESET SLAVE; CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), d TIMESTAMP, -e INT NOT NULL) ENGINE='MyISAM'; +e INT NOT NULL, +f text not null, +g text, +h blob not null, +i blob) ENGINE='MyISAM'; *** Create t9 on Master *** CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5) ) ENGINE='MyISAM'; @@ -415,47 +419,11 @@ START SLAVE; set @b1 = 'b1b1b1b1'; set @b1 = concat(@b1,@b1); INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); -SHOW SLAVE STATUS; -Slave_IO_State # -Master_Host 127.0.0.1 -Master_User root -Master_Port # -Connect_Retry 1 -Master_Log_File master-bin.000001 -Read_Master_Log_Pos # -Relay_Log_File # -Relay_Log_Pos # -Relay_Master_Log_File master-bin.000001 -Slave_IO_Running Yes -Slave_SQL_Running No -Replicate_Do_DB -Replicate_Ignore_DB -Replicate_Do_Table -Replicate_Ignore_Table # -Replicate_Wild_Do_Table -Replicate_Wild_Ignore_Table -Last_Errno 1364 -Last_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 330 -Skip_Counter 0 -Exec_Master_Log_Pos # -Relay_Log_Space # -Until_Condition None -Until_Log_File -Until_Log_Pos 0 -Master_SSL_Allowed No -Master_SSL_CA_File -Master_SSL_CA_Path -Master_SSL_Cert -Master_SSL_Cipher -Master_SSL_Key -Seconds_Behind_Master # -Master_SSL_Verify_Server_Cert No -Last_IO_Errno # -Last_IO_Error # -Last_SQL_Errno 1364 -Last_SQL_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 330 -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; -START SLAVE; +select * from t9; +a b c d e f g h i +1 b1b1b1b1b1b1b1b1 Kyle 0000-00-00 00:00:00 0 NULL NULL +2 b1b1b1b1b1b1b1b1 JOE 0000-00-00 00:00:00 0 NULL NULL +3 b1b1b1b1b1b1b1b1 QA 0000-00-00 00:00:00 0 NULL NULL *** Create t10 on slave *** STOP SLAVE; RESET SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result index a6a2181cd2a..bb9865ab2d1 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result @@ -105,47 +105,9 @@ a b x 2 10 Foo is a bar INSERT INTO t9 VALUES (2); INSERT INTO t1_nodef VALUES (1,2); -SHOW SLAVE STATUS; -Slave_IO_State # -Master_Host 127.0.0.1 -Master_User root -Master_Port # -Connect_Retry 1 -Master_Log_File master-bin.000001 -Read_Master_Log_Pos # -Relay_Log_File # -Relay_Log_Pos # -Relay_Master_Log_File master-bin.000001 -Slave_IO_Running Yes -Slave_SQL_Running No -Replicate_Do_DB -Replicate_Ignore_DB -Replicate_Do_Table -Replicate_Ignore_Table -Replicate_Wild_Do_Table -Replicate_Wild_Ignore_Table -Last_Errno 1364 -Last_Error -Skip_Counter 0 -Exec_Master_Log_Pos # -Relay_Log_Space # -Until_Condition None -Until_Log_File -Until_Log_Pos 0 -Master_SSL_Allowed No -Master_SSL_CA_File -Master_SSL_CA_Path -Master_SSL_Cert -Master_SSL_Cipher -Master_SSL_Key -Seconds_Behind_Master # -Master_SSL_Verify_Server_Cert No -Last_IO_Errno -Last_IO_Error -Last_SQL_Errno 1364 -Last_SQL_Error -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; -START SLAVE; +select count(*) from t1_nodef; +count(*) +1 INSERT INTO t9 VALUES (2); **** On Master **** INSERT INTO t2 VALUES (2,4); diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result index 02e8c074354..f606a28c2d9 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result @@ -105,47 +105,9 @@ a b x 2 10 Foo is a bar INSERT INTO t9 VALUES (2); INSERT INTO t1_nodef VALUES (1,2); -SHOW SLAVE STATUS; -Slave_IO_State # -Master_Host 127.0.0.1 -Master_User root -Master_Port # -Connect_Retry 1 -Master_Log_File master-bin.000001 -Read_Master_Log_Pos # -Relay_Log_File # -Relay_Log_Pos # -Relay_Master_Log_File master-bin.000001 -Slave_IO_Running Yes -Slave_SQL_Running No -Replicate_Do_DB -Replicate_Ignore_DB -Replicate_Do_Table -Replicate_Ignore_Table -Replicate_Wild_Do_Table -Replicate_Wild_Ignore_Table -Last_Errno 1364 -Last_Error -Skip_Counter 0 -Exec_Master_Log_Pos # -Relay_Log_Space # -Until_Condition None -Until_Log_File -Until_Log_Pos 0 -Master_SSL_Allowed No -Master_SSL_CA_File -Master_SSL_CA_Path -Master_SSL_Cert -Master_SSL_Cipher -Master_SSL_Key -Seconds_Behind_Master # -Master_SSL_Verify_Server_Cert No -Last_IO_Errno -Last_IO_Error -Last_SQL_Errno 1364 -Last_SQL_Error -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; -START SLAVE; +select count(*) from t1_nodef; +count(*) +1 INSERT INTO t9 VALUES (2); **** On Master **** INSERT INTO t2 VALUES (2,4); -- cgit v1.2.1 From 7e5cf52c3e0a52960a1f564bbb78c1ab42d1a0a1 Mon Sep 17 00:00:00 2001 From: Alfranio Correia Date: Thu, 22 Oct 2009 01:15:45 +0100 Subject: BUG#48091 valgrind errors when slave has double not null and master has double null Backporting BUG#43789 to mysql-5.1-bugteam The replication was generating corrupted data, warning messages on Valgrind and aborting on debug mode while replicating a "null" to "not null" field. Specifically the unpack_row routine, was considering the slave's table definition and trying to retrieve a field value, where there was nothing to be retrieved, ignoring the fact that the value was defined as "null" by the master. To fix the problem, we proceed as follows: 1 - If it is not STRICT sql_mode, implicit default values are used, regardless if it is multi-row or single-row statement. 2 - However, if it is STRICT mode, then a we do what follows: 2.1 If it is a transactional engine, we do a rollback on the first NULL that is to be set into a NOT NULL column and return an error. 2.2 If it is a non-transactional engine and it is the first row to be inserted with multi-row, we also return the error. Otherwise, we proceed with the execution, use implicit default values and print out warning messages. Unfortunately, the current patch cannot mimic the behavior showed by the master for updates on multi-tables and multi-row inserts. This happens because such statements are unfolded in different row events. For instance, considering the following updates and strict mode: (master) create table t1 (a int); create table t2 (a int not null); insert into t1 values (1); insert into t2 values (2); update t1, t2 SET t1.a=10, t2.a=NULL; t1 would have (10) and t2 would have (0) as this would be handled as a multi-row update. On the other hand, if we had the following updates: (master) create table t1 (a int); create table t2 (a int); (slave) create table t1 (a int); create table t2 (a int not null); (master) insert into t1 values (1); insert into t2 values (2); update t1, t2 SET t1.a=10, t2.a=NULL; On the master t1 would have (10) and t2 would have (NULL). On the slave, t1 would have (10) but the update on t1 would fail. --- mysql-test/extra/rpl_tests/rpl_not_null.test | 364 ++++++++++++++++++++++ mysql-test/suite/rpl/r/rpl_not_null_innodb.result | 202 ++++++++++++ mysql-test/suite/rpl/r/rpl_not_null_myisam.result | 202 ++++++++++++ mysql-test/suite/rpl/t/rpl_not_null_innodb.test | 19 ++ mysql-test/suite/rpl/t/rpl_not_null_myisam.test | 18 ++ 5 files changed, 805 insertions(+) create mode 100644 mysql-test/extra/rpl_tests/rpl_not_null.test create mode 100644 mysql-test/suite/rpl/r/rpl_not_null_innodb.result create mode 100644 mysql-test/suite/rpl/r/rpl_not_null_myisam.result create mode 100644 mysql-test/suite/rpl/t/rpl_not_null_innodb.test create mode 100644 mysql-test/suite/rpl/t/rpl_not_null_myisam.test (limited to 'mysql-test') diff --git a/mysql-test/extra/rpl_tests/rpl_not_null.test b/mysql-test/extra/rpl_tests/rpl_not_null.test new file mode 100644 index 00000000000..88f37f9a95e --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_not_null.test @@ -0,0 +1,364 @@ +################################################################################# +# This test checks if the replication between "null" fields to either "null" +# fields or "not null" fields works properly. In the first case, the execution +# should work fine. In the second case, it may fail according to the sql_mode +# being used. +# +# The test is devided in three main parts: +# +# 1 - NULL --> NULL (no failures) +# 2 - NULL --> NOT NULL ( sql-mode = STRICT and failures) +# 3 - NULL --> NOT NULL ( sql-mode != STRICT and no failures) +# +################################################################################# +connection master; + +SET SQL_LOG_BIN= 0; +eval CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, +`c` INT DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; + +eval CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; + +eval CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; + +eval CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL, +`c` INT DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +SET SQL_LOG_BIN= 1; + +connection slave; + +eval CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, +`c` INT DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; + +eval CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; + +eval CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00', +`c` INT DEFAULT 500, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; + +eval CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00', +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; + +--echo ************* EXECUTION WITH INSERTS ************* +connection master; +INSERT INTO t1(a,b,c) VALUES (1, null, 1); +INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); +INSERT INTO t1(a,b) VALUES (3, null); +INSERT INTO t1(a,c) VALUES (4, 4); +INSERT INTO t1(a) VALUES (5); + +INSERT INTO t2(a,b) VALUES (1, null); +INSERT INTO t2(a,b) VALUES (2,'1111-11-11'); +INSERT INTO t2(a) VALUES (3); + +INSERT INTO t3(a,b) VALUES (1, null); +INSERT INTO t3(a,b) VALUES (2,'1111-11-11'); +INSERT INTO t3(a) VALUES (3); + +INSERT INTO t4(a,b,c) VALUES (1, null, 1); +INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2); +INSERT INTO t4(a,b) VALUES (3, null); +INSERT INTO t4(a,c) VALUES (4, 4); +INSERT INTO t4(a) VALUES (5); + +--echo ************* SHOWING THE RESULT SETS WITH INSERTS ************* +sync_slave_with_master; + +--echo TABLES t1 and t2 must be equal otherwise an error will be thrown. +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; + +let $diff_table_1=master:test.t2; +let $diff_table_2=slave:test.t2; +source include/diff_tables.inc; + +--echo TABLES t2 and t3 must be different. +connection master; +SELECT * FROM t3; +connection slave; +SELECT * FROM t3; +connection master; +SELECT * FROM t4; +connection slave; +SELECT * FROM t4; + +--echo ************* EXECUTION WITH UPDATES and REPLACES ************* +connection master; +DELETE FROM t1; +INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1); +REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); +UPDATE t1 set b= NULL, c= 300 where a= 1; +REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300); + +--echo ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* +sync_slave_with_master; + +--echo TABLES t1 and t2 must be equal otherwise an error will be thrown. +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; + +--echo ************* CLEANING ************* +connection master; + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; + +sync_slave_with_master; + +connection master; + +SET SQL_LOG_BIN= 0; +eval CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL, +PRIMARY KEY (`a`)) ENGINE= $engine; +SET SQL_LOG_BIN= 1; + +connection slave; + +eval CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL, +PRIMARY KEY (`a`)) ENGINE= $engine; + +--echo ************* EXECUTION WITH INSERTS ************* +connection master; +INSERT INTO t1(a,b,c) VALUES (1, null, b'01'); +INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01'); +INSERT INTO t1(a,b) VALUES (3, null); +INSERT INTO t1(a,c) VALUES (4, b'01'); +INSERT INTO t1(a) VALUES (5); + +--echo ************* SHOWING THE RESULT SETS WITH INSERTS ************* +--echo TABLES t1 and t2 must be different. +sync_slave_with_master; +connection master; +SELECT a,b+0,c+0 FROM t1; +connection slave; +SELECT a,b+0,c+0 FROM t1; + +--echo ************* EXECUTION WITH UPDATES and REPLACES ************* +connection master; +DELETE FROM t1; +INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01'); +REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01'); +UPDATE t1 set b= NULL, c= b'00' where a= 1; +REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00'); + +--echo ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* +--echo TABLES t1 and t2 must be equal otherwise an error will be thrown. +sync_slave_with_master; +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; + +connection master; + +DROP TABLE t1; + +sync_slave_with_master; + +--echo ################################################################################ +--echo # NULL ---> NOT NULL (STRICT MODE) +--echo # UNCOMMENT THIS AFTER FIXING BUG#43992 +--echo ################################################################################ +#connection slave; +#SET GLOBAL sql_mode="TRADITIONAL"; +# +#STOP SLAVE; +#--source include/wait_for_slave_to_stop.inc +#START SLAVE; +#--source include/wait_for_slave_to_start.inc +# +#let $y=0; +#while (`select $y < 6`) +#{ +# connection master; +# +# SET SQL_LOG_BIN= 0; +# eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT, +# PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +# eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT, +# PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +# eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT, +# PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +# SET SQL_LOG_BIN= 1; +# +# connection slave; +# +# eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL, +# `c` INT NOT NULL, +# PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +# eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL, +# `c` INT, +# PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +# eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL, +# `c` INT DEFAULT 500, +# PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +# +# if (`select $y=0`) +# { +# --echo ************* EXECUTION WITH INSERTS ************* +# connection master; +# INSERT INTO t1(a) VALUES (1); +# } +# +# if (`select $y=1`) +# { +# --echo ************* EXECUTION WITH INSERTS ************* +# connection master; +# INSERT INTO t1(a, b) VALUES (1, NULL); +# } +# +# if (`select $y=2`) +# { +# --echo ************* EXECUTION WITH UPDATES ************* +# connection master; +# INSERT INTO t3(a, b) VALUES (1, 1); +# INSERT INTO t3(a, b) VALUES (2, 1); +# UPDATE t3 SET b = NULL where a= 1; +# } +# +# if (`select $y=3`) +# { +# --echo ************* EXECUTION WITH INSERTS/REPLACES ************* +# connection master; +# REPLACE INTO t3(a, b) VALUES (1, null); +# } +# +# if (`select $y=4`) +# { +# --echo ************* EXECUTION WITH UPDATES/REPLACES ************* +# connection master; +# INSERT INTO t3(a, b) VALUES (1, 1); +# REPLACE INTO t3(a, b) VALUES (1, null); +# } +# +# if (`select $y=5`) +# { +# --echo ************* EXECUTION WITH MULTI-ROW INSERTS ************* +# connection master; +# +# SET SQL_LOG_BIN= 0; +# INSERT INTO t2(a, b) VALUES (1, 1); +# INSERT INTO t2(a, b) VALUES (2, 1); +# INSERT INTO t2(a, b) VALUES (3, null); +# INSERT INTO t2(a, b) VALUES (4, 1); +# INSERT INTO t2(a, b) VALUES (5, 1); +# SET SQL_LOG_BIN= 1; +# +# INSERT INTO t2 SELECT a + 10, b from t2; +# --echo The statement below is just executed to stop processing +# INSERT INTO t1(a) VALUES (1); +# } +# +# --echo ************* SHOWING THE RESULT SETS ************* +# connection slave; +# --source include/wait_for_slave_sql_to_stop.inc +# connection master; +# SELECT * FROM t1; +# connection slave; +# SELECT * FROM t1; +# connection master; +# SELECT * FROM t2; +# connection slave; +# SELECT * FROM t2; +# connection master; +# SELECT * FROM t3; +# connection slave; +# SELECT * FROM t3; +# --source include/reset_master_and_slave.inc +# +# connection master; +# +# DROP TABLE t1; +# DROP TABLE t2; +# DROP TABLE t3; +# +# sync_slave_with_master; +# +# inc $y; +#} +#connection slave; +#SET GLOBAL sql_mode=""; +# +#STOP SLAVE; +#source include/wait_for_slave_to_stop.inc; +#START SLAVE; +#--source include/wait_for_slave_to_start.inc + +--echo ################################################################################ +--echo # NULL ---> NOT NULL (NON-STRICT MODE) +--echo ################################################################################ +connection master; + +SET SQL_LOG_BIN= 0; +eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +SET SQL_LOG_BIN= 1; + +connection slave; + +eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL, +`c` INT NOT NULL, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL, +`c` INT, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; +eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL, +`c` INT DEFAULT 500, +PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1; + +--echo ************* EXECUTION WITH INSERTS ************* +connection master; +INSERT INTO t1(a) VALUES (1); +INSERT INTO t1(a, b) VALUES (2, NULL); +INSERT INTO t1(a, b) VALUES (3, 1); + +INSERT INTO t2(a) VALUES (1); +INSERT INTO t2(a, b) VALUES (2, NULL); +INSERT INTO t2(a, b) VALUES (3, 1); + +INSERT INTO t3(a) VALUES (1); +INSERT INTO t3(a, b) VALUES (2, NULL); +INSERT INTO t3(a, b) VALUES (3, 1); +INSERT INTO t3(a, b) VALUES (4, 1); +REPLACE INTO t3(a, b) VALUES (5, null); + +REPLACE INTO t3(a, b) VALUES (3, null); +UPDATE t3 SET b = NULL where a = 4; + +--echo ************* SHOWING THE RESULT SETS ************* +connection master; +sync_slave_with_master; + +connection master; +SELECT * FROM t1; +connection slave; +SELECT * FROM t1; +connection master; +SELECT * FROM t2; +connection slave; +SELECT * FROM t2; +connection master; +SELECT * FROM t3; +connection slave; +SELECT * FROM t3; + +connection master; + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; + +sync_slave_with_master; diff --git a/mysql-test/suite/rpl/r/rpl_not_null_innodb.result b/mysql-test/suite/rpl/r/rpl_not_null_innodb.result new file mode 100644 index 00000000000..7717beb0a47 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_not_null_innodb.result @@ -0,0 +1,202 @@ +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; +SET SQL_LOG_BIN= 0; +CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, +`c` INT DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL, +`c` INT DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +SET SQL_LOG_BIN= 1; +CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, +`c` INT DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00', +`c` INT DEFAULT 500, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00', +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +************* EXECUTION WITH INSERTS ************* +INSERT INTO t1(a,b,c) VALUES (1, null, 1); +INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); +INSERT INTO t1(a,b) VALUES (3, null); +INSERT INTO t1(a,c) VALUES (4, 4); +INSERT INTO t1(a) VALUES (5); +INSERT INTO t2(a,b) VALUES (1, null); +INSERT INTO t2(a,b) VALUES (2,'1111-11-11'); +INSERT INTO t2(a) VALUES (3); +INSERT INTO t3(a,b) VALUES (1, null); +INSERT INTO t3(a,b) VALUES (2,'1111-11-11'); +INSERT INTO t3(a) VALUES (3); +INSERT INTO t4(a,b,c) VALUES (1, null, 1); +INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2); +INSERT INTO t4(a,b) VALUES (3, null); +INSERT INTO t4(a,c) VALUES (4, 4); +INSERT INTO t4(a) VALUES (5); +************* SHOWING THE RESULT SETS WITH INSERTS ************* +TABLES t1 and t2 must be equal otherwise an error will be thrown. +Comparing tables master:test.t1 and slave:test.t1 +Comparing tables master:test.t2 and slave:test.t2 +TABLES t2 and t3 must be different. +SELECT * FROM t3; +a b +1 NULL +2 1111-11-11 +3 NULL +SELECT * FROM t3; +a b c +1 NULL 500 +2 1111-11-11 500 +3 NULL 500 +SELECT * FROM t4; +a b c +1 NULL 1 +2 1111-11-11 2 +3 NULL NULL +4 NULL 4 +5 NULL NULL +SELECT * FROM t4; +a b +1 NULL +2 1111-11-11 +3 NULL +4 NULL +5 NULL +************* EXECUTION WITH UPDATES and REPLACES ************* +DELETE FROM t1; +INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1); +REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); +UPDATE t1 set b= NULL, c= 300 where a= 1; +REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300); +************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* +TABLES t1 and t2 must be equal otherwise an error will be thrown. +Comparing tables master:test.t1 and slave:test.t1 +************* CLEANING ************* +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +SET SQL_LOG_BIN= 0; +CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL, +PRIMARY KEY (`a`)) ENGINE= Innodb; +SET SQL_LOG_BIN= 1; +CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL, +PRIMARY KEY (`a`)) ENGINE= Innodb; +************* EXECUTION WITH INSERTS ************* +INSERT INTO t1(a,b,c) VALUES (1, null, b'01'); +INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01'); +INSERT INTO t1(a,b) VALUES (3, null); +INSERT INTO t1(a,c) VALUES (4, b'01'); +INSERT INTO t1(a) VALUES (5); +************* SHOWING THE RESULT SETS WITH INSERTS ************* +TABLES t1 and t2 must be different. +SELECT a,b+0,c+0 FROM t1; +a b+0 c+0 +1 NULL 1 +2 0 1 +3 NULL NULL +4 NULL 1 +5 NULL NULL +SELECT a,b+0,c+0 FROM t1; +a b+0 c+0 +1 NULL 1 +2 0 1 +3 NULL NULL +4 NULL 1 +5 NULL NULL +************* EXECUTION WITH UPDATES and REPLACES ************* +DELETE FROM t1; +INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01'); +REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01'); +UPDATE t1 set b= NULL, c= b'00' where a= 1; +REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00'); +************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* +TABLES t1 and t2 must be equal otherwise an error will be thrown. +Comparing tables master:test.t1 and slave:test.t1 +DROP TABLE t1; +################################################################################ +# NULL ---> NOT NULL (STRICT MODE) +# UNCOMMENT THIS AFTER FIXING BUG#43992 +################################################################################ +################################################################################ +# NULL ---> NOT NULL (NON-STRICT MODE) +################################################################################ +SET SQL_LOG_BIN= 0; +CREATE TABLE t1(`a` INT NOT NULL, `b` INT, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t2(`a` INT NOT NULL, `b` INT, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t3(`a` INT NOT NULL, `b` INT, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +SET SQL_LOG_BIN= 1; +CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL, +`c` INT NOT NULL, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL, +`c` INT, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL, +`c` INT DEFAULT 500, +PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; +************* EXECUTION WITH INSERTS ************* +INSERT INTO t1(a) VALUES (1); +INSERT INTO t1(a, b) VALUES (2, NULL); +INSERT INTO t1(a, b) VALUES (3, 1); +INSERT INTO t2(a) VALUES (1); +INSERT INTO t2(a, b) VALUES (2, NULL); +INSERT INTO t2(a, b) VALUES (3, 1); +INSERT INTO t3(a) VALUES (1); +INSERT INTO t3(a, b) VALUES (2, NULL); +INSERT INTO t3(a, b) VALUES (3, 1); +INSERT INTO t3(a, b) VALUES (4, 1); +REPLACE INTO t3(a, b) VALUES (5, null); +REPLACE INTO t3(a, b) VALUES (3, null); +UPDATE t3 SET b = NULL where a = 4; +************* SHOWING THE RESULT SETS ************* +SELECT * FROM t1; +a b +1 NULL +2 NULL +3 1 +SELECT * FROM t1; +a b c +1 0 0 +2 0 0 +3 1 0 +SELECT * FROM t2; +a b +1 NULL +2 NULL +3 1 +SELECT * FROM t2; +a b c +1 0 NULL +2 0 NULL +3 1 NULL +SELECT * FROM t3; +a b +1 NULL +2 NULL +3 NULL +4 NULL +5 NULL +SELECT * FROM t3; +a b c +1 0 500 +2 0 500 +3 0 500 +4 0 500 +5 0 500 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; diff --git a/mysql-test/suite/rpl/r/rpl_not_null_myisam.result b/mysql-test/suite/rpl/r/rpl_not_null_myisam.result new file mode 100644 index 00000000000..57a015367bb --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_not_null_myisam.result @@ -0,0 +1,202 @@ +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; +SET SQL_LOG_BIN= 0; +CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, +`c` INT DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL, +`c` INT DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +SET SQL_LOG_BIN= 1; +CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, +`c` INT DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00', +`c` INT DEFAULT 500, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00', +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +************* EXECUTION WITH INSERTS ************* +INSERT INTO t1(a,b,c) VALUES (1, null, 1); +INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); +INSERT INTO t1(a,b) VALUES (3, null); +INSERT INTO t1(a,c) VALUES (4, 4); +INSERT INTO t1(a) VALUES (5); +INSERT INTO t2(a,b) VALUES (1, null); +INSERT INTO t2(a,b) VALUES (2,'1111-11-11'); +INSERT INTO t2(a) VALUES (3); +INSERT INTO t3(a,b) VALUES (1, null); +INSERT INTO t3(a,b) VALUES (2,'1111-11-11'); +INSERT INTO t3(a) VALUES (3); +INSERT INTO t4(a,b,c) VALUES (1, null, 1); +INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2); +INSERT INTO t4(a,b) VALUES (3, null); +INSERT INTO t4(a,c) VALUES (4, 4); +INSERT INTO t4(a) VALUES (5); +************* SHOWING THE RESULT SETS WITH INSERTS ************* +TABLES t1 and t2 must be equal otherwise an error will be thrown. +Comparing tables master:test.t1 and slave:test.t1 +Comparing tables master:test.t2 and slave:test.t2 +TABLES t2 and t3 must be different. +SELECT * FROM t3; +a b +1 NULL +2 1111-11-11 +3 NULL +SELECT * FROM t3; +a b c +1 NULL 500 +2 1111-11-11 500 +3 NULL 500 +SELECT * FROM t4; +a b c +1 NULL 1 +2 1111-11-11 2 +3 NULL NULL +4 NULL 4 +5 NULL NULL +SELECT * FROM t4; +a b +1 NULL +2 1111-11-11 +3 NULL +4 NULL +5 NULL +************* EXECUTION WITH UPDATES and REPLACES ************* +DELETE FROM t1; +INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1); +REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); +UPDATE t1 set b= NULL, c= 300 where a= 1; +REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300); +************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* +TABLES t1 and t2 must be equal otherwise an error will be thrown. +Comparing tables master:test.t1 and slave:test.t1 +************* CLEANING ************* +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +SET SQL_LOG_BIN= 0; +CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL, +PRIMARY KEY (`a`)) ENGINE= MyISAM; +SET SQL_LOG_BIN= 1; +CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL, +PRIMARY KEY (`a`)) ENGINE= MyISAM; +************* EXECUTION WITH INSERTS ************* +INSERT INTO t1(a,b,c) VALUES (1, null, b'01'); +INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01'); +INSERT INTO t1(a,b) VALUES (3, null); +INSERT INTO t1(a,c) VALUES (4, b'01'); +INSERT INTO t1(a) VALUES (5); +************* SHOWING THE RESULT SETS WITH INSERTS ************* +TABLES t1 and t2 must be different. +SELECT a,b+0,c+0 FROM t1; +a b+0 c+0 +1 NULL 1 +2 0 1 +3 NULL NULL +4 NULL 1 +5 NULL NULL +SELECT a,b+0,c+0 FROM t1; +a b+0 c+0 +1 NULL 1 +2 0 1 +3 NULL NULL +4 NULL 1 +5 NULL NULL +************* EXECUTION WITH UPDATES and REPLACES ************* +DELETE FROM t1; +INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01'); +REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01'); +UPDATE t1 set b= NULL, c= b'00' where a= 1; +REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00'); +************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* +TABLES t1 and t2 must be equal otherwise an error will be thrown. +Comparing tables master:test.t1 and slave:test.t1 +DROP TABLE t1; +################################################################################ +# NULL ---> NOT NULL (STRICT MODE) +# UNCOMMENT THIS AFTER FIXING BUG#43992 +################################################################################ +################################################################################ +# NULL ---> NOT NULL (NON-STRICT MODE) +################################################################################ +SET SQL_LOG_BIN= 0; +CREATE TABLE t1(`a` INT NOT NULL, `b` INT, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t2(`a` INT NOT NULL, `b` INT, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t3(`a` INT NOT NULL, `b` INT, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +SET SQL_LOG_BIN= 1; +CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL, +`c` INT NOT NULL, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL, +`c` INT, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL, +`c` INT DEFAULT 500, +PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1; +************* EXECUTION WITH INSERTS ************* +INSERT INTO t1(a) VALUES (1); +INSERT INTO t1(a, b) VALUES (2, NULL); +INSERT INTO t1(a, b) VALUES (3, 1); +INSERT INTO t2(a) VALUES (1); +INSERT INTO t2(a, b) VALUES (2, NULL); +INSERT INTO t2(a, b) VALUES (3, 1); +INSERT INTO t3(a) VALUES (1); +INSERT INTO t3(a, b) VALUES (2, NULL); +INSERT INTO t3(a, b) VALUES (3, 1); +INSERT INTO t3(a, b) VALUES (4, 1); +REPLACE INTO t3(a, b) VALUES (5, null); +REPLACE INTO t3(a, b) VALUES (3, null); +UPDATE t3 SET b = NULL where a = 4; +************* SHOWING THE RESULT SETS ************* +SELECT * FROM t1; +a b +1 NULL +2 NULL +3 1 +SELECT * FROM t1; +a b c +1 0 0 +2 0 0 +3 1 0 +SELECT * FROM t2; +a b +1 NULL +2 NULL +3 1 +SELECT * FROM t2; +a b c +1 0 NULL +2 0 NULL +3 1 NULL +SELECT * FROM t3; +a b +1 NULL +2 NULL +3 NULL +4 NULL +5 NULL +SELECT * FROM t3; +a b c +1 0 500 +2 0 500 +3 0 500 +4 0 500 +5 0 500 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; diff --git a/mysql-test/suite/rpl/t/rpl_not_null_innodb.test b/mysql-test/suite/rpl/t/rpl_not_null_innodb.test new file mode 100644 index 00000000000..dca0ea6589c --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_not_null_innodb.test @@ -0,0 +1,19 @@ +################################################################################# +# This test checks if the replication between "null" fields to either "null" +# fields or "not null" fields works properly. In the first case, the execution +# should work fine. In the second case, it may fail according to the sql_mode +# being used. +# +# The test is devided in three main parts: +# +# 1 - NULL --> NULL (no failures) +# 2 - NULL --> NOT NULL ( sql-mode = STRICT and failures) +# 3 - NULL --> NOT NULL ( sql-mode != STRICT and no failures) +# +################################################################################# +--source include/master-slave.inc +--source include/have_innodb.inc +--source include/have_binlog_format_row.inc + +let $engine=Innodb; +--source extra/rpl_tests/rpl_not_null.test diff --git a/mysql-test/suite/rpl/t/rpl_not_null_myisam.test b/mysql-test/suite/rpl/t/rpl_not_null_myisam.test new file mode 100644 index 00000000000..0c036f5bfd7 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_not_null_myisam.test @@ -0,0 +1,18 @@ +################################################################################# +# This test checks if the replication between "null" fields to either "null" +# fields or "not null" fields works properly. In the first case, the execution +# should work fine. In the second case, it may fail according to the sql_mode +# being used. +# +# The test is devided in three main parts: +# +# 1 - NULL --> NULL (no failures) +# 2 - NULL --> NOT NULL ( sql-mode = STRICT and failures) +# 3 - NULL --> NOT NULL ( sql-mode != STRICT and no failures) +# +################################################################################# +--source include/master-slave.inc +--source include/have_binlog_format_row.inc + +let $engine=MyISAM; +--source extra/rpl_tests/rpl_not_null.test -- cgit v1.2.1 From 925ac7f4eb3672b09b3414b257750345e80022b6 Mon Sep 17 00:00:00 2001 From: Alfranio Correia Date: Thu, 22 Oct 2009 01:19:52 +0100 Subject: BUG#48091 valgrind errors when slave has double not null and master has double null Backporting BUG#43789 to mysql-5.1-bugteam Post-fix for BUG#43789. --- mysql-test/extra/rpl_tests/rpl_not_null.test | 36 +++++++++++------------ mysql-test/suite/rpl/r/rpl_not_null_innodb.result | 24 +++++++-------- mysql-test/suite/rpl/r/rpl_not_null_myisam.result | 24 +++++++-------- 3 files changed, 42 insertions(+), 42 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/extra/rpl_tests/rpl_not_null.test b/mysql-test/extra/rpl_tests/rpl_not_null.test index 88f37f9a95e..58dbd9ce29f 100644 --- a/mysql-test/extra/rpl_tests/rpl_not_null.test +++ b/mysql-test/extra/rpl_tests/rpl_not_null.test @@ -81,13 +81,13 @@ source include/diff_tables.inc; --echo TABLES t2 and t3 must be different. connection master; -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; connection slave; -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; connection master; -SELECT * FROM t4; +SELECT * FROM t4 ORDER BY a; connection slave; -SELECT * FROM t4; +SELECT * FROM t4 ORDER BY a; --echo ************* EXECUTION WITH UPDATES and REPLACES ************* connection master; @@ -139,9 +139,9 @@ INSERT INTO t1(a) VALUES (5); --echo TABLES t1 and t2 must be different. sync_slave_with_master; connection master; -SELECT a,b+0,c+0 FROM t1; +SELECT a,b+0,c+0 FROM t1 ORDER BY a; connection slave; -SELECT a,b+0,c+0 FROM t1; +SELECT a,b+0,c+0 FROM t1 ORDER BY a; --echo ************* EXECUTION WITH UPDATES and REPLACES ************* connection master; @@ -262,17 +262,17 @@ sync_slave_with_master; # connection slave; # --source include/wait_for_slave_sql_to_stop.inc # connection master; -# SELECT * FROM t1; +# SELECT * FROM t1 ORDER BY a; # connection slave; -# SELECT * FROM t1; +# SELECT * FROM t1 ORDER BY a; # connection master; -# SELECT * FROM t2; +# SELECT * FROM t2 ORDER BY a; # connection slave; -# SELECT * FROM t2; +# SELECT * FROM t2 ORDER BY a; # connection master; -# SELECT * FROM t3; +# SELECT * FROM t3 ORDER BY a; # connection slave; -# SELECT * FROM t3; +# SELECT * FROM t3 ORDER BY a; # --source include/reset_master_and_slave.inc # # connection master; @@ -343,17 +343,17 @@ connection master; sync_slave_with_master; connection master; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; connection slave; -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; connection master; -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; connection slave; -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; connection master; -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; connection slave; -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; connection master; diff --git a/mysql-test/suite/rpl/r/rpl_not_null_innodb.result b/mysql-test/suite/rpl/r/rpl_not_null_innodb.result index 7717beb0a47..b09fbab905a 100644 --- a/mysql-test/suite/rpl/r/rpl_not_null_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_not_null_innodb.result @@ -48,24 +48,24 @@ TABLES t1 and t2 must be equal otherwise an error will be thrown. Comparing tables master:test.t1 and slave:test.t1 Comparing tables master:test.t2 and slave:test.t2 TABLES t2 and t3 must be different. -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; a b 1 NULL 2 1111-11-11 3 NULL -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; a b c 1 NULL 500 2 1111-11-11 500 3 NULL 500 -SELECT * FROM t4; +SELECT * FROM t4 ORDER BY a; a b c 1 NULL 1 2 1111-11-11 2 3 NULL NULL 4 NULL 4 5 NULL NULL -SELECT * FROM t4; +SELECT * FROM t4 ORDER BY a; a b 1 NULL 2 1111-11-11 @@ -100,14 +100,14 @@ INSERT INTO t1(a,c) VALUES (4, b'01'); INSERT INTO t1(a) VALUES (5); ************* SHOWING THE RESULT SETS WITH INSERTS ************* TABLES t1 and t2 must be different. -SELECT a,b+0,c+0 FROM t1; +SELECT a,b+0,c+0 FROM t1 ORDER BY a; a b+0 c+0 1 NULL 1 2 0 1 3 NULL NULL 4 NULL 1 5 NULL NULL -SELECT a,b+0,c+0 FROM t1; +SELECT a,b+0,c+0 FROM t1 ORDER BY a; a b+0 c+0 1 NULL 1 2 0 1 @@ -163,34 +163,34 @@ REPLACE INTO t3(a, b) VALUES (5, null); REPLACE INTO t3(a, b) VALUES (3, null); UPDATE t3 SET b = NULL where a = 4; ************* SHOWING THE RESULT SETS ************* -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 NULL 2 NULL 3 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b c 1 0 0 2 0 0 3 1 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 NULL 2 NULL 3 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b c 1 0 NULL 2 0 NULL 3 1 NULL -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; a b 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; a b c 1 0 500 2 0 500 diff --git a/mysql-test/suite/rpl/r/rpl_not_null_myisam.result b/mysql-test/suite/rpl/r/rpl_not_null_myisam.result index 57a015367bb..09611dc6480 100644 --- a/mysql-test/suite/rpl/r/rpl_not_null_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_not_null_myisam.result @@ -48,24 +48,24 @@ TABLES t1 and t2 must be equal otherwise an error will be thrown. Comparing tables master:test.t1 and slave:test.t1 Comparing tables master:test.t2 and slave:test.t2 TABLES t2 and t3 must be different. -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; a b 1 NULL 2 1111-11-11 3 NULL -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; a b c 1 NULL 500 2 1111-11-11 500 3 NULL 500 -SELECT * FROM t4; +SELECT * FROM t4 ORDER BY a; a b c 1 NULL 1 2 1111-11-11 2 3 NULL NULL 4 NULL 4 5 NULL NULL -SELECT * FROM t4; +SELECT * FROM t4 ORDER BY a; a b 1 NULL 2 1111-11-11 @@ -100,14 +100,14 @@ INSERT INTO t1(a,c) VALUES (4, b'01'); INSERT INTO t1(a) VALUES (5); ************* SHOWING THE RESULT SETS WITH INSERTS ************* TABLES t1 and t2 must be different. -SELECT a,b+0,c+0 FROM t1; +SELECT a,b+0,c+0 FROM t1 ORDER BY a; a b+0 c+0 1 NULL 1 2 0 1 3 NULL NULL 4 NULL 1 5 NULL NULL -SELECT a,b+0,c+0 FROM t1; +SELECT a,b+0,c+0 FROM t1 ORDER BY a; a b+0 c+0 1 NULL 1 2 0 1 @@ -163,34 +163,34 @@ REPLACE INTO t3(a, b) VALUES (5, null); REPLACE INTO t3(a, b) VALUES (3, null); UPDATE t3 SET b = NULL where a = 4; ************* SHOWING THE RESULT SETS ************* -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b 1 NULL 2 NULL 3 1 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY a; a b c 1 0 0 2 0 0 3 1 0 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b 1 NULL 2 NULL 3 1 -SELECT * FROM t2; +SELECT * FROM t2 ORDER BY a; a b c 1 0 NULL 2 0 NULL 3 1 NULL -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; a b 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL -SELECT * FROM t3; +SELECT * FROM t3 ORDER BY a; a b c 1 0 500 2 0 500 -- cgit v1.2.1 From 0c2d74c60be4604d08b7893178da69fbf1105146 Mon Sep 17 00:00:00 2001 From: Alfranio Correia Date: Thu, 22 Oct 2009 01:21:50 +0100 Subject: BUG#48091 valgrind errors when slave has double not null and master has double null Backporting BUG#47741 to mysql-5.1-bugteam --- mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test | 77 +++++++++++----------- mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result | 56 ---------------- 2 files changed, 40 insertions(+), 93 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test b/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test index 1eaefa661f9..46168d6b97a 100644 --- a/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test +++ b/mysql-test/extra/rpl_tests/rpl_extraSlave_Col.test @@ -410,51 +410,54 @@ sync_slave_with_master; ############################################################### # Error reaction is up to sql_mode of the slave sql (bug#38173) #--echo *** Create t9 on slave *** -STOP SLAVE; -RESET SLAVE; -eval CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), - d TIMESTAMP, - e INT NOT NULL, - f text not null, - g text, - h blob not null, - i blob) ENGINE=$engine_type; - ---echo *** Create t9 on Master *** -connection master; -eval CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5) +# Please, check BUG#47741 to see why you are not testing NDB. +if (`SELECT $engine_type != 'NDB'`) +{ + STOP SLAVE; + RESET SLAVE; + eval CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), + d TIMESTAMP, + e INT NOT NULL, + f text not null, + g text, + h blob not null, + i blob) ENGINE=$engine_type; + + --echo *** Create t9 on Master *** + connection master; + eval CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5) ) ENGINE=$engine_type; -RESET MASTER; + RESET MASTER; ---echo *** Start Slave *** -connection slave; -START SLAVE; + --echo *** Start Slave *** + connection slave; + START SLAVE; ---echo *** Master Data Insert *** -connection master; -set @b1 = 'b1b1b1b1'; -set @b1 = concat(@b1,@b1); -INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); + --echo *** Master Data Insert *** + connection master; + set @b1 = 'b1b1b1b1'; -# the test would stop slave if @@sql_mode for the sql thread -# was set to strict. Otherwise, as with this tests setup, -# the implicit defaults will be inserted into fields even though -# they are declared without DEFAULT clause. + set @b1 = concat(@b1,@b1); + INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); -sync_slave_with_master; -select * from t9; + # the test would stop slave if @@sql_mode for the sql thread + # was set to strict. Otherwise, as with this tests setup, + # the implicit defaults will be inserted into fields even though + # they are declared without DEFAULT clause. -# todo: fix Bug #43992 slave sql thread can't tune own sql_mode ... -# and add/restore waiting for stop test - -#--source include/wait_for_slave_sql_to_stop.inc -#--replace_result $MASTER_MYPORT MASTER_PORT -#--replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # -#--query_vertical SHOW SLAVE STATUS -#SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; -#START SLAVE; + sync_slave_with_master; + select * from t9; + # todo: fix Bug #43992 slave sql thread can't tune own sql_mode ... + # and add/restore waiting for stop test + #--source include/wait_for_slave_sql_to_stop.inc + #--replace_result $MASTER_MYPORT MASTER_PORT + #--replace_column 1 # 4 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 # + #--query_vertical SHOW SLAVE STATUS + #SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; + #START SLAVE; +} #--echo *** Drop t9 *** #connection master; diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result index f812509de6f..f514bf7a75b 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result @@ -400,62 +400,6 @@ set @b1 = concat(@b1,@b1); INSERT INTO t8 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); *** Drop t8 *** DROP TABLE t8; -STOP SLAVE; -RESET SLAVE; -CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5), -d TIMESTAMP, -e INT NOT NULL) ENGINE='NDB'; -*** Create t9 on Master *** -CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5) -) ENGINE='NDB'; -RESET MASTER; -*** Start Slave *** -START SLAVE; -*** Master Data Insert *** -set @b1 = 'b1b1b1b1'; -set @b1 = concat(@b1,@b1); -INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA'); -SHOW SLAVE STATUS; -Slave_IO_State # -Master_Host 127.0.0.1 -Master_User root -Master_Port # -Connect_Retry 1 -Master_Log_File master-bin.000001 -Read_Master_Log_Pos # -Relay_Log_File # -Relay_Log_Pos # -Relay_Master_Log_File master-bin.000001 -Slave_IO_Running Yes -Slave_SQL_Running No -Replicate_Do_DB -Replicate_Ignore_DB -Replicate_Do_Table -Replicate_Ignore_Table # -Replicate_Wild_Do_Table -Replicate_Wild_Ignore_Table -Last_Errno 1364 -Last_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 447 -Skip_Counter 0 -Exec_Master_Log_Pos # -Relay_Log_Space # -Until_Condition None -Until_Log_File -Until_Log_Pos 0 -Master_SSL_Allowed No -Master_SSL_CA_File -Master_SSL_CA_Path -Master_SSL_Cert -Master_SSL_Cipher -Master_SSL_Key -Seconds_Behind_Master # -Master_SSL_Verify_Server_Cert No -Last_IO_Errno # -Last_IO_Error # -Last_SQL_Errno 1364 -Last_SQL_Error Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 447 -SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; -START SLAVE; *** Create t10 on slave *** STOP SLAVE; RESET SLAVE; -- cgit v1.2.1 From 313c5a01eea6fe4ab528066a7b14fe6b63e2c633 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Tue, 27 Oct 2009 14:43:12 +0200 Subject: Bug #47930: MATCH IN BOOLEAN MODE returns too many results inside subquery Re-setting a fulltext index was a no-operation if not all the matches of a search were consumed by reading them. This was preventing a joined table using a fulltext index in a subquery that requires only 1 row of output (e.g. EXISTS) from working correctly because the second execution of the sub-query has the fulltext index cursor in a wrong state and was not finding results. Fixed by making the re-init code _ftb_init_index_search() to re-set open cursors in addition to depleted ones. --- mysql-test/r/fulltext.result | 39 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/fulltext.test | 41 +++++++++++++++++++++++++++++++++++++++++ 2 files changed, 80 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index a5216189d9a..6022231907b 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -551,3 +551,42 @@ EXECUTE s; MATCH (col) AGAINST('findme') DEALLOCATE PREPARE s; DROP TABLE t1; +# +# Bug #47930: MATCH IN BOOLEAN MODE returns too many results +# inside subquery +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)); +INSERT INTO t2 VALUES (1,'Scargill'); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (1,1), (2,1); +# t2 should use full text index +EXPLAIN +SELECT count(*) FROM t1 WHERE +not exists( +SELECT 1 FROM t2, t3 +WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 fulltext b2 b2 0 1 Using where +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where +# should return 0 +SELECT count(*) FROM t1 WHERE +not exists( +SELECT 1 FROM t2, t3 +WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) +); +count(*) +0 +# should return 0 +SELECT count(*) FROM t1 WHERE +not exists( +SELECT 1 FROM t2 IGNORE INDEX (b2), t3 +WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) +); +count(*) +0 +DROP TABLE t1,t2,t3; +End of 5.1 tests diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 80c8658d35c..4537559509d 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -484,3 +484,44 @@ PREPARE s FROM EXECUTE s; DEALLOCATE PREPARE s; DROP TABLE t1; + +--echo # +--echo # Bug #47930: MATCH IN BOOLEAN MODE returns too many results +--echo # inside subquery +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1), (2); + +CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)); +INSERT INTO t2 VALUES (1,'Scargill'); + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (1,1), (2,1); + +--echo # t2 should use full text index +EXPLAIN +SELECT count(*) FROM t1 WHERE + not exists( + SELECT 1 FROM t2, t3 + WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) + ); + +--echo # should return 0 +SELECT count(*) FROM t1 WHERE + not exists( + SELECT 1 FROM t2, t3 + WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) + ); + +--echo # should return 0 +SELECT count(*) FROM t1 WHERE + not exists( + SELECT 1 FROM t2 IGNORE INDEX (b2), t3 + WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) + ); + +DROP TABLE t1,t2,t3; + + +--echo End of 5.1 tests -- cgit v1.2.1 From be04d1a47953188ced936a81824fdf871b8e9656 Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Thu, 29 Oct 2009 22:06:10 -0700 Subject: Bug#48319: Server crashes on "GRANT/REVOKE ... TO CURRENT_USER" CURRENT_USER() in GRANT ... TO CURRENT_USER() only gave us a definer, not a full user (i.e., password-element was not initiliazed). Hence dereferencing the password led to a crash. Properly initializes definers now, just so there are no misunderstandings. Also does some magic so IDENTIFIED BY ... works with CURRENT_USER(). mysql-test/r/grant2.result: Show GRANT ... TO CURRENT_USER() no longer crashes. Show it to work with IDENTIFIED BY to boot. mysql-test/t/grant2.test: Show GRANT ... TO CURRENT_USER() no longer crashes. Show it to work with IDENTIFIED BY to boot. sql/sql_acl.cc: Make IDENTIFIED BY ... work with CURRENT_USER() sql/sql_parse.cc: Zero password-part of definer just in case somebody mistakes this for a complete LEX_USER! --- mysql-test/r/grant2.result | 27 +++++++++++++++++++++++++++ mysql-test/t/grant2.test | 35 +++++++++++++++++++++++++++++++++++ 2 files changed, 62 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index 7c2023127f0..12269f0cb1c 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -443,3 +443,30 @@ DROP TABLE db1.t1, db1.t2; DROP USER mysqltest1@localhost; DROP DATABASE db1; End of 5.0 tests +USE mysql; +SELECT LEFT(CURRENT_USER(),INSTR(CURRENT_USER(),'@')-1) INTO @u; +SELECT MID(CURRENT_USER(),INSTR(CURRENT_USER(),'@')+1) INTO @h; +SELECT password FROM user WHERE user=@u AND host=@h INTO @pwd; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost Y +UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost N +GRANT INSERT ON *.* TO CURRENT_USER(); +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost Y +UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; +GRANT INSERT ON *.* TO CURRENT_USER() IDENTIFIED BY 'keksdose'; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost *0BB7188CF0DE9B403BA66E9DD810D82652D002EB Y +UPDATE user SET password=@pwd WHERE user=@u AND host=@h; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +user host password insert_priv +root localhost Y +FLUSH PRIVILEGES; +USE test; +End of 5.1 tests diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index 54cdf8d6cc1..447848013f9 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -632,5 +632,40 @@ DROP DATABASE db1; --echo End of 5.0 tests +# +# Bug #48319: Server crashes on "GRANT/REVOKE ... TO CURRENT_USER" +# + +# work out who we are. +USE mysql; +SELECT LEFT(CURRENT_USER(),INSTR(CURRENT_USER(),'@')-1) INTO @u; +SELECT MID(CURRENT_USER(),INSTR(CURRENT_USER(),'@')+1) INTO @h; +SELECT password FROM user WHERE user=@u AND host=@h INTO @pwd; + +# show current privs. +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; + +# toggle INSERT +UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; + +# show that GRANT ... TO CURRENT_USER() no longer crashes +GRANT INSERT ON *.* TO CURRENT_USER(); +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; +UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h; + +# show that GRANT ... TO CURRENT_USER() IDENTIFIED BY ... works now +GRANT INSERT ON *.* TO CURRENT_USER() IDENTIFIED BY 'keksdose'; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; + +UPDATE user SET password=@pwd WHERE user=@u AND host=@h; +SELECT user,host,password,insert_priv FROM user WHERE user=@u AND host=@h; + +FLUSH PRIVILEGES; + +USE test; + +--echo End of 5.1 tests + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc -- cgit v1.2.1 From b35feb1ed0aa934ea244163ecea54b5e45157138 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Tue, 3 Nov 2009 09:22:01 +0100 Subject: Bug#46923: select count(*) from partitioned table fails with ONLY_FULL_GROUP_BY Problem was that during checking and preparation of the partitioining function as a side effect in fix_fields the full_group_by_flag was changed. Solution was to set it back to its original value after calling fix_fields. Updated patch, to also exclude allow_sum_func from being affected of fix_fields, as requested by reviewer. mysql-test/r/partition.result: Bug#46923: select count(*) from partitioned table fails with ONLY_FULL_GROUP_BY Updated result file mysql-test/t/partition.test: Bug#46923: select count(*) from partitioned table fails with ONLY_FULL_GROUP_BY Extended test case to cover this bug sql/sql_partition.cc: Bug#46923: select count(*) from partitioned table fails with ONLY_FULL_GROUP_BY Resetting full_group_by_flag and allow_sum_func back to their original values, not conflicting with the sql_mode 'ONLY_FULL_GROUP_BY' --- mysql-test/r/partition.result | 5 +++++ mysql-test/t/partition.test | 3 +++ 2 files changed, 8 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 2d54a66fe11..abdeb3a77e1 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -2030,10 +2030,15 @@ DROP TABLE t1; # # Bug #45807: crash accessing partitioned table and sql_mode # contains ONLY_FULL_GROUP_BY +# Bug#46923: select count(*) from partitioned table fails with +# ONLY_FULL_GROUP_BY # SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; CREATE TABLE t1(id INT,KEY(id)) ENGINE=MYISAM PARTITION BY HASH(id) PARTITIONS 2; +SELECT COUNT(*) FROM t1; +COUNT(*) +0 DROP TABLE t1; SET SESSION SQL_MODE=DEFAULT; # diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index c5ed098b678..6486b56a551 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -2018,11 +2018,14 @@ DROP TABLE t1; --echo # --echo # Bug #45807: crash accessing partitioned table and sql_mode --echo # contains ONLY_FULL_GROUP_BY +--echo # Bug#46923: select count(*) from partitioned table fails with +--echo # ONLY_FULL_GROUP_BY --echo # SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; CREATE TABLE t1(id INT,KEY(id)) ENGINE=MYISAM PARTITION BY HASH(id) PARTITIONS 2; +SELECT COUNT(*) FROM t1; DROP TABLE t1; SET SESSION SQL_MODE=DEFAULT; -- cgit v1.2.1 From 27028272f31a8b79ef708a68ccf90b03f9907688 Mon Sep 17 00:00:00 2001 From: Luis Soares Date: Wed, 4 Nov 2009 01:56:36 +0000 Subject: BUG#47743: rpl.rpl_log_pos fails sporadically BUG#47983: rpl_extraColmaster_myisam failed in PB2 with "Found warnings!!" BUG 45214 fixed the case when get_master_version_and_clock function, used by the slave, would not report errors. The slave now detects them and if related to transient network failures, it prints some warnings and retries to connect. On the other hand, if not network related, it just gives up and fails. As such, sometimes, in PB2, the slave comes across some transient communication issues between master and slave, while calling get_master_version_and_clock, causing warnings print outs to the error log. Nevertheless, in such cases slave retries to connect, in which it succeeds, and the test case continues as it normally would. But then, at the end of a successful test run, MTR checks the error log, finds the unexpected warnings and considers them harmful. This causes MTR to report error and, consequently, PB2 to report a failing test. We fix this by adding to the global warnings suppress list the warnings related to transient network failures only, which are reported while in function get_master_version_and_clock. --- mysql-test/include/mtr_warnings.sql | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/include/mtr_warnings.sql b/mysql-test/include/mtr_warnings.sql index 134e448953a..5ff6fa5cf11 100644 --- a/mysql-test/include/mtr_warnings.sql +++ b/mysql-test/include/mtr_warnings.sql @@ -175,6 +175,15 @@ INSERT INTO global_suppressions VALUES ("Can't find file: '.\\\\test\\\\\\?{8}.frm'"), ("Slave: Unknown table 't1' Error_code: 1051"), + /* + Transient network failures that cause warnings on reconnect. + BUG#47743 and BUG#47983. + */ + ("Slave I/O: Get master SERVER_ID failed with error:.*"), + ("Slave I/O: Get master clock failed with error:.*"), + ("Slave I/O: Get master COLLATION_SERVER failed with error:.*"), + ("Slave I/O: Get master TIME_ZONE failed with error:.*"), + ("THE_LAST_SUPPRESSION")|| -- cgit v1.2.1 From 43d7fb43cd965ae8e53d8253388b6ac58a7ebddc Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Wed, 4 Nov 2009 13:54:28 +0200 Subject: Bug #46175: NULL read_view and consistent read assertion The SE API requires mysql to notify the storage engine that it's going to read certain tables at the beginning of the statement (by calling start_stmt(), store_lock() or external_lock()). These are typically called by the lock_tables(). However SHOW CREATE TABLE is not pre-locking the tables because it's not expected to access the data at all. But for some view definitions (that include comparing a date/datetime/timestamp column to a string returning scalar subquery) the JOIN::prepare may still access data when materializing the scalar non-correlated subquery in Arg_comparator::can_compare_as_dates(). Fixed by not materializing the subquery when the function is called in a SHOW/EXPLAIN/CREATE VIEW --- mysql-test/r/innodb_mysql.result | 22 +++++++++++++++++++ mysql-test/t/innodb_mysql.test | 46 ++++++++++++++++++++++++++++++++++++++++ 2 files changed, 68 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index c882d2af1ed..402ab3c1b16 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -2251,4 +2251,26 @@ c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; +# +# Bug #46175: NULL read_view and consistent read assertion +# +CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb; +CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb; +INSERT INTO t1 VALUES (),(); +INSERT INTO t2 VALUES (),(); +CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2 +WHERE b =(SELECT a FROM t1 LIMIT 1); +CREATE PROCEDURE p1(num INT) +BEGIN +DECLARE i INT DEFAULT 0; +REPEAT +SHOW CREATE VIEW v1; +SET i:=i+1; +UNTIL i>num END REPEAT; +END| +# Should not crash +# Should not crash +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1,t2; End of 5.1 tests diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 7055879ce1a..a2a1113598d 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -489,5 +489,51 @@ EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; DROP TABLE t1; +--echo # +--echo # Bug #46175: NULL read_view and consistent read assertion +--echo # + +CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb; +CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb; +INSERT INTO t1 VALUES (),(); +INSERT INTO t2 VALUES (),(); +CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2 + WHERE b =(SELECT a FROM t1 LIMIT 1); + +--disable_query_log +--disable_result_log +CONNECT (con1, localhost, root,,); +--enable_query_log +--enable_result_log +CONNECTION default; + +DELIMITER |; +CREATE PROCEDURE p1(num INT) +BEGIN + DECLARE i INT DEFAULT 0; + REPEAT + SHOW CREATE VIEW v1; + SET i:=i+1; + UNTIL i>num END REPEAT; +END| +DELIMITER ;| + +--echo # Should not crash +--disable_query_log +--disable_result_log +--send CALL p1(1000) +CONNECTION con1; +--echo # Should not crash +CALL p1(1000); + +CONNECTION default; +--reap +--enable_query_log +--enable_result_log + +DISCONNECT con1; +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1,t2; --echo End of 5.1 tests -- cgit v1.2.1 From 39f9a3ffd0de8828be2a43b9674a19fa58c52f98 Mon Sep 17 00:00:00 2001 From: Alexey Kopytov Date: Fri, 6 Nov 2009 09:44:01 +0300 Subject: Bug #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP and only const tables The problem was caused by two shortcuts in the optimizer that are inapplicable in the ROLLUP case. Normally in a case when only const tables are involved in a query, DISTINCT clause can be safely optimized away since there may be only one row produced by the join. Similarly, we don't need to create a temporary table to resolve DISTINCT/GROUP BY/ORDER BY. Both of these are inapplicable when the WITH ROLLUP modifier is present. Fixed by disabling the said optimizations for the WITH ROLLUP case. mysql-test/r/olap.result: Added a test case for bug #48475. mysql-test/t/olap.test: Added a test case for bug #48475. sql/sql_select.cc: Disabled const-only table optimizations for the WITH ROLLUP case. --- mysql-test/r/olap.result | 12 ++++++++++++ mysql-test/t/olap.test | 13 +++++++++++++ 2 files changed, 25 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 56e06f03378..eabe3a50309 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -753,4 +753,16 @@ b 100 NULL DROP TABLE t1, t2; +# +# Bug #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP +# and only const tables +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); +SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP; +b +1 +NULL +DROP TABLE t1, t2; End of 5.0 tests diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 8f672af40a3..fec5df1a1c7 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -390,4 +390,17 @@ SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP; DROP TABLE t1, t2; +--echo # +--echo # Bug #48475: DISTINCT is ignored with GROUP BY WITH ROLLUP +--echo # and only const tables + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); + +SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP; + +DROP TABLE t1, t2; + --echo End of 5.0 tests -- cgit v1.2.1 From 785b5c4e7a06445d2b23b57c3dce120828844c69 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Mon, 9 Nov 2009 11:26:01 +0100 Subject: Bug#48276: can't add column if subpartition exists Bug when setting up default partitioning, used an uninitialized variabe. mysql-test/r/partition.result: Bug#48276: can't add column if subpartition exists Added result mysql-test/t/partition.test: Bug#48276: can't add column if subpartition exists Added test sql/sql_partition.cc: Bug#48276: can't add column if subpartition exists even if is_create_table_ind was set, one tried to set no_subparts with the unitialized no_parts local variable. Fixed by rearrange the code to be to only execute the statements when is_create_table_ind was not set. --- mysql-test/r/partition.result | 6 ++++++ mysql-test/t/partition.test | 9 +++++++++ 2 files changed, 15 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 6611d39628f..9ee62c978d0 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1,4 +1,10 @@ drop table if exists t1, t2; +CREATE TABLE t1 (a INT, b INT) +PARTITION BY LIST (a) +SUBPARTITION BY HASH (b) +(PARTITION p1 VALUES IN (1)); +ALTER TABLE t1 ADD COLUMN c INT; +DROP TABLE t1; CREATE TABLE t1 ( a int NOT NULL, b int NOT NULL); diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 1dfc53c6232..3fc67a1f147 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -14,6 +14,15 @@ drop table if exists t1, t2; --enable_warnings +# +# Bug#48276: can't add column if subpartition exists +CREATE TABLE t1 (a INT, b INT) +PARTITION BY LIST (a) +SUBPARTITION BY HASH (b) +(PARTITION p1 VALUES IN (1)); +ALTER TABLE t1 ADD COLUMN c INT; +DROP TABLE t1; + # # Bug#46639: 1030 (HY000): Got error 124 from storage engine on # INSERT ... SELECT ... -- cgit v1.2.1 From ddd90017e75cf1241da12cdc3fd55b44da0c4a9d Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Tue, 10 Nov 2009 10:21:41 +0200 Subject: Bug #42760: Select doesn't return desired results when we have null values We should re-set the access method functions when changing the access method when switching to another index to avoid sorting. Fixed by doing a little re-engineering : encapsulating all the function assignment into a special function and calling it when flipping the indexes. --- mysql-test/r/order_by.result | 21 +++++++++++++++++++++ mysql-test/t/order_by.test | 26 ++++++++++++++++++++++++++ 2 files changed, 47 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index f64bbc79cbd..f0e5b5fde3d 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1092,3 +1092,24 @@ FROM t3; 2 NULL DROP TABLE t1, t2, t3; +# +# Bug #42760: Select doesn't return desired results when we have null +# values +# +CREATE TABLE t1 ( +a INT, +c INT, +UNIQUE KEY a_c (a,c), +KEY (a)); +INSERT INTO t1 VALUES (1, 10), (2, NULL); +# Must use ref-or-null on the a_c index +EXPLAIN +SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null a_c,a a_c 10 const,const 1 Using where +# Must return 1 row +SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; +col +1 +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 6d7ee1c1ca7..d9a6c0f844d 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -756,3 +756,29 @@ SELECT FROM t3; DROP TABLE t1, t2, t3; + + + +--echo # +--echo # Bug #42760: Select doesn't return desired results when we have null +--echo # values +--echo # + +CREATE TABLE t1 ( + a INT, + c INT, + UNIQUE KEY a_c (a,c), + KEY (a)); + +INSERT INTO t1 VALUES (1, 10), (2, NULL); + +--echo # Must use ref-or-null on the a_c index +EXPLAIN +SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; +--echo # Must return 1 row +SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c; + +DROP TABLE t1; + + +--echo End of 5.0 tests -- cgit v1.2.1 From 4519d5e4f1ce1c9081f711d88d7508f58453d219 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Mon, 9 Nov 2009 16:09:46 +0200 Subject: Bug #48458: simple query tries to allocate enormous amount of memory The server was doing a bad class typecast causing setting of wrong value for the maximum number of items in an internal structure used in equality propagation. Fixed by not doing the wrong typecast and asserting the type of the Item where it should be done. --- mysql-test/r/select.result | 14 ++++++++++++++ mysql-test/t/select.test | 13 +++++++++++++ 2 files changed, 27 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 5d07c97149f..ff59eadab0c 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4442,4 +4442,18 @@ ROW(a,a) <=> ROW((SELECT 1 FROM t1 WHERE 1=2),(SELECT 1 FROM t1)) INTO @var0; ERROR 21000: Subquery returns more than 1 row DROP TABLE t1; +# +# Bug #48458: simple query tries to allocate enormous amount of +# memory +# +CREATE TABLE t1(a INT NOT NULL, b YEAR); +INSERT INTO t1 VALUES (); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +CREATE TABLE t2(c INT); +# Should not err out because of out-of-memory +SELECT 1 FROM t2 JOIN t1 ON 1=1 +WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a); +1 +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index ceb67215614..a4d3056b66e 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3783,5 +3783,18 @@ INTO @var0; DROP TABLE t1; +--echo # +--echo # Bug #48458: simple query tries to allocate enormous amount of +--echo # memory +--echo # + +CREATE TABLE t1(a INT NOT NULL, b YEAR); +INSERT INTO t1 VALUES (); +CREATE TABLE t2(c INT); +--echo # Should not err out because of out-of-memory +SELECT 1 FROM t2 JOIN t1 ON 1=1 + WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a); +DROP TABLE t1,t2; + --echo End of 5.0 tests -- cgit v1.2.1 From c87375150b922526f9699aee86614481ac711ad0 Mon Sep 17 00:00:00 2001 From: Anurag Shekhar Date: Wed, 11 Nov 2009 13:33:29 +0530 Subject: Bug #47012 archive tables are not upgradeable, and server crashes on any access Archive engine for 5.1 (and latter) version uses a modified version of zlib (azlib). These two version are incompatible so a proper upgrade is needed before tables created in 5.0 can be used reliable. This upgrade can be performed using repair. But due to lack of test its risky to allow upgrade for now. This patch addresses only the crashing issue. Any attempt to repair will be blocked. Eventually repair can be allowed to run through (which will also cause an upgrade from older version to newer) but only after a thorough testing. mysql-test/r/archive.result: Updated result file for test case for bug#47012 mysql-test/std_data/bug47012.ARM: part of archive table (t1) created in mysql 5.0 mysql-test/std_data/bug47012.ARZ: part of archive table (t1) created in mysql 5.0 mysql-test/std_data/bug47012.frm: part of archive table (t1) created in mysql 5.0 mysql-test/t/archive.test: Added test case for bug#47012. storage/archive/azio.c: Fixed a minor issues (minor version overwriting version in stream structure) Removed assertion when an older version is found. Instead setting the correct version (2) in s->version If an unknown version is found marked it as corrupt. storage/archive/ha_archive.cc: Detecting the archive version in getShare and marking it as need to upgrade. Blocking open if the archive needs an upgrade. This can be allowed in case of open for repair to upgrade the archive but needs to tested. --- mysql-test/r/archive.result | 11 +++++++++++ mysql-test/std_data/bug47012.ARM | Bin 0 -> 19 bytes mysql-test/std_data/bug47012.ARZ | Bin 0 -> 22 bytes mysql-test/std_data/bug47012.frm | Bin 0 -> 8590 bytes mysql-test/t/archive.test | 21 +++++++++++++++++++++ 5 files changed, 32 insertions(+) create mode 100644 mysql-test/std_data/bug47012.ARM create mode 100644 mysql-test/std_data/bug47012.ARZ create mode 100644 mysql-test/std_data/bug47012.frm (limited to 'mysql-test') diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index e865d775c6a..c620eb97cab 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -12717,3 +12717,14 @@ COUNT(t1.a) 729 DROP TABLE t1; SET @@join_buffer_size= @save_join_buffer_size; +SHOW CREATE TABLE t1; +ERROR HY000: Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! +SELECT * FROM t1; +ERROR HY000: Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! +INSERT INTO t1 (col1, col2) VALUES (1, "value"); +ERROR HY000: Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair Error Table upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! +test.t1 repair error Corrupt +DROP TABLE t1; diff --git a/mysql-test/std_data/bug47012.ARM b/mysql-test/std_data/bug47012.ARM new file mode 100644 index 00000000000..86bbaf829d2 Binary files /dev/null and b/mysql-test/std_data/bug47012.ARM differ diff --git a/mysql-test/std_data/bug47012.ARZ b/mysql-test/std_data/bug47012.ARZ new file mode 100644 index 00000000000..f2aa19ffea6 Binary files /dev/null and b/mysql-test/std_data/bug47012.ARZ differ diff --git a/mysql-test/std_data/bug47012.frm b/mysql-test/std_data/bug47012.frm new file mode 100644 index 00000000000..6c0a535cce7 Binary files /dev/null and b/mysql-test/std_data/bug47012.frm differ diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index aad3d19455d..90f5b1b0b53 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1623,3 +1623,24 @@ INSERT INTO t1 VALUES('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), SELECT COUNT(t1.a) FROM t1, t1 a, t1 b, t1 c, t1 d, t1 e; DROP TABLE t1; SET @@join_buffer_size= @save_join_buffer_size; + +# +# BUG#47012 archive tables are not upgradeable, and server crashes on any access +# +let $MYSQLD_DATADIR= `SELECT @@datadir`; +copy_file std_data/bug47012.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file std_data/bug47012.ARZ $MYSQLD_DATADIR/test/t1.ARZ; +copy_file std_data/bug47012.ARM $MYSQLD_DATADIR/test/t1.ARM; + +--error ER_TABLE_NEEDS_UPGRADE +SHOW CREATE TABLE t1; + +--error ER_TABLE_NEEDS_UPGRADE +SELECT * FROM t1; + +--error ER_TABLE_NEEDS_UPGRADE +INSERT INTO t1 (col1, col2) VALUES (1, "value"); + +REPAIR TABLE t1; +DROP TABLE t1; +remove_file $MYSQLD_DATADIR/test/t1.ARM; -- cgit v1.2.1 From 552965eff927dff2f0248169e5c0510ef3783ccf Mon Sep 17 00:00:00 2001 From: Magne Mahre Date: Thu, 12 Nov 2009 12:43:33 +0100 Subject: Bug #37183 insert ignore into .. select ... hangs after deadlock was encountered The bug is caused by an inconsistent handling of the IGNORE clause. A read from a const table caused a lock timeout (ER_LOCK_TIMEOUT) in innodb. Since the IGNORE clause was given, the timeout was converted into a warning instead of an error, thus not populating the diagnostics area. When innodb subsequently marked the transaction for rollback, mysql asserted since the diag.area was empty. This patch consists of only a test case, as the bug itself was fixed by the patch for Bug #46539 --- mysql-test/r/innodb_lock_wait_timeout_1.result | 18 ++++++++++++++ mysql-test/t/innodb_lock_wait_timeout_1.test | 34 ++++++++++++++++++++++++++ 2 files changed, 52 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_lock_wait_timeout_1.result b/mysql-test/r/innodb_lock_wait_timeout_1.result index a635b0d527a..bd8760b8f79 100644 --- a/mysql-test/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/r/innodb_lock_wait_timeout_1.result @@ -48,6 +48,24 @@ commit; set autocommit=default; drop table t1; # +# Bug #37183 insert ignore into .. select ... hangs +# after deadlock was encountered +# +create table t1(id int primary key,v int)engine=innodb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); +create table t2 like t1; +begin; +update t1 set v=id*2 where id=1; +begin; +update t1 set v=id*2 where id=2; +update t1 set v=id*2 where id=2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +insert ignore into t2 select * from t1 where id=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +rollback; +drop table t1, t2; +# # Bug#41756 Strange error messages about locks from InnoDB # drop table if exists t1; diff --git a/mysql-test/t/innodb_lock_wait_timeout_1.test b/mysql-test/t/innodb_lock_wait_timeout_1.test index e42e9f3e37c..fcbf2b1cfc7 100644 --- a/mysql-test/t/innodb_lock_wait_timeout_1.test +++ b/mysql-test/t/innodb_lock_wait_timeout_1.test @@ -70,6 +70,40 @@ commit; set autocommit=default; drop table t1; +--echo # +--echo # Bug #37183 insert ignore into .. select ... hangs +--echo # after deadlock was encountered +--echo # +connect (con1,localhost,root,,); +create table t1(id int primary key,v int)engine=innodb; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); +create table t2 like t1; + +--connection con1 +begin; +update t1 set v=id*2 where id=1; + +--connection default +begin; +update t1 set v=id*2 where id=2; + +--connection con1 +--error 1205 +update t1 set v=id*2 where id=2; + +--connection default +--error 1205 +insert ignore into t2 select * from t1 where id=1; +rollback; + +--connection con1 +rollback; + +--connection default +disconnect con1; +drop table t1, t2; + + --echo # --echo # Bug#41756 Strange error messages about locks from InnoDB --echo # -- cgit v1.2.1 From 356b3df7430a46b3472682a827e357b3f7482257 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Thu, 12 Nov 2009 23:03:26 -0200 Subject: Bug#47627 SET @@{global.session}.local_variable in stored routine causes crash This patch borrows ideas, text and code from Kristofer Pettersson's patch. An assignment of a system variable sharing the same base name as a declared stored procedure variable in the same context could lead to a crash. The reason was that during the parsing of the syntactic rule 'option_value' an uninitialized set_var object was pushed to the parameter stack of the SET statement. The parent rule 'option_type_value' interpreted the existence of variables on the parameter stack as an assignment and wrapped it in a sp_instr_set object. As the procedure later was executed an attempt was made to run the method 'check()' on an uninitialized member object (NULL value) belonging to the previously created but uninitialized object. This patch refactors the 'internal_variable_name' rule and copies the semantic analysis part to the depending parent rule: 'option_value'. This makes it possible to account for any prefixes affecting the interpretation of the internal_variable_name. mysql-test/r/sp.result: Add test case result. mysql-test/t/sp.test: Add test case for bug. sql/sql_yacc.yy: - Reduce churn in rule sys_option_value by moving to specialized functions. - Comment the the lookup in the rule internel_variable_name is a best effort operation. - Lookup for a system variable in the option_value if one was not found (the variable could have been shadowed) --- mysql-test/r/sp.result | 58 +++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/sp.test | 67 ++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 125 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 67514c314f4..83ad7545685 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6979,6 +6979,64 @@ CALL p1; ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' DROP PROCEDURE p1; DROP TABLE t1, t2; +# +# Bug#47627: SET @@{global.session}.local_variable in stored routine causes crash +# Bug#48626: Crash or lost connection using SET for declared variables with @@ +# +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +CREATE PROCEDURE p1() +BEGIN +DECLARE v INT DEFAULT 0; +SET @@SESSION.v= 10; +END// +ERROR HY000: Unknown system variable 'v' +CREATE PROCEDURE p2() +BEGIN +DECLARE v INT DEFAULT 0; +SET v= 10; +END// +call p2()// +CREATE PROCEDURE p3() +BEGIN +DECLARE v INT DEFAULT 0; +SELECT @@SESSION.v; +END// +ERROR HY000: Unknown system variable 'v' +CREATE PROCEDURE p4() +BEGIN +DECLARE v INT DEFAULT 0; +SET @@GLOBAL.v= 10; +END// +ERROR HY000: Unknown system variable 'v' +CREATE PROCEDURE p5() +BEGIN +DECLARE init_connect INT DEFAULT 0; +SET init_connect= 10; +SET @@GLOBAL.init_connect= 'SELECT 1'; +SET @@SESSION.IDENTITY= 1; +SELECT @@SESSION.IDENTITY; +SELECT @@GLOBAL.init_connect; +SELECT init_connect; +END// +CREATE PROCEDURE p6() +BEGIN +DECLARE v INT DEFAULT 0; +SET @@v= 0; +END// +ERROR HY000: Unknown system variable 'v' +SET @old_init_connect= @@GLOBAL.init_connect; +CALL p5(); +@@SESSION.IDENTITY +1 +@@GLOBAL.init_connect +SELECT 1 +init_connect +10 +SET @@GLOBAL.init_connect= @old_init_connect; +DROP PROCEDURE p2; +DROP PROCEDURE p5; # ------------------------------------------------------------------ # -- End of 5.1 tests # ------------------------------------------------------------------ diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 44c4556340e..73ba62612b8 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -8263,6 +8263,73 @@ CALL p1; DROP PROCEDURE p1; DROP TABLE t1, t2; +--echo # +--echo # Bug#47627: SET @@{global.session}.local_variable in stored routine causes crash +--echo # Bug#48626: Crash or lost connection using SET for declared variables with @@ +--echo # + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +--enable_warnings + +delimiter //; + +--error ER_UNKNOWN_SYSTEM_VARIABLE +CREATE PROCEDURE p1() +BEGIN + DECLARE v INT DEFAULT 0; + SET @@SESSION.v= 10; +END// + +CREATE PROCEDURE p2() +BEGIN + DECLARE v INT DEFAULT 0; + SET v= 10; +END// +call p2()// + +--error ER_UNKNOWN_SYSTEM_VARIABLE +CREATE PROCEDURE p3() +BEGIN + DECLARE v INT DEFAULT 0; + SELECT @@SESSION.v; +END// + +--error ER_UNKNOWN_SYSTEM_VARIABLE +CREATE PROCEDURE p4() +BEGIN + DECLARE v INT DEFAULT 0; + SET @@GLOBAL.v= 10; +END// + +CREATE PROCEDURE p5() +BEGIN + DECLARE init_connect INT DEFAULT 0; + SET init_connect= 10; + SET @@GLOBAL.init_connect= 'SELECT 1'; + SET @@SESSION.IDENTITY= 1; + SELECT @@SESSION.IDENTITY; + SELECT @@GLOBAL.init_connect; + SELECT init_connect; +END// + +--error ER_UNKNOWN_SYSTEM_VARIABLE +CREATE PROCEDURE p6() +BEGIN + DECLARE v INT DEFAULT 0; + SET @@v= 0; +END// + +delimiter ;// + +SET @old_init_connect= @@GLOBAL.init_connect; +CALL p5(); +SET @@GLOBAL.init_connect= @old_init_connect; + +DROP PROCEDURE p2; +DROP PROCEDURE p5; --echo # ------------------------------------------------------------------ --echo # -- End of 5.1 tests -- cgit v1.2.1 From a120e969a83128f075b54a06155ccee0bb46b312 Mon Sep 17 00:00:00 2001 From: Jorgen Loland Date: Fri, 13 Nov 2009 12:22:39 +0100 Subject: Bug#48052: Valgrind warning - uninitialized value in init_read_record() - (records.cc:274) Item_cond::used_tables_cache was accessed in records.cc#init_read_record() without being initialized. It had not been initialized because it was wrongly assumed that the Item's variables would not be accessed, and hence quick_fix_field() was used instead of fix_fields() to save a few CPU cycles at creation time. The fix is to properly initilize the Item by replacing quick_fix_field() with fix_fields(). mysql-test/r/select.result: Add test for BUG#48052 mysql-test/t/select.test: Add test for BUG#48052 sql/sql_select.cc: Properly initialize Item_cond_and by calling fix_fields (instead of quick_fix_field) when the Item that "ANDs" WHERE clause conditions with HAVING clause conditions is created. --- mysql-test/r/select.result | 18 ++++++++++++++++++ mysql-test/t/select.test | 33 +++++++++++++++++++++++++++++++++ 2 files changed, 51 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index e528b63ddb7..d0b2a575a32 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4591,4 +4591,22 @@ field2 15:13:38 drop table A,AA,B,BB; #end of test for bug#45266 +# +# BUG#48052: Valgrind warning - uninitialized value in init_read_record() +# +CREATE TABLE t1 ( +pk int(11) NOT NULL, +i int(11) DEFAULT NULL, +v varchar(1) DEFAULT NULL, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (2,7,'m'); +INSERT INTO t1 VALUES (3,9,'m'); +SELECT v +FROM t1 +WHERE NOT pk > 0 +HAVING v <= 't' +ORDER BY pk; +v +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 7502f451b69..ac65e5cbaf5 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3931,4 +3931,37 @@ SELECT table1 .`time_key` field2 FROM B table1 LEFT JOIN BB JOIN A table5 ON drop table A,AA,B,BB; --echo #end of test for bug#45266 + +--echo # +--echo # BUG#48052: Valgrind warning - uninitialized value in init_read_record() +--echo # + +# Needed in 6.0 codebase +#--echo # Disable Index condition pushdown +#--replace_column 1 # +#SELECT @old_icp:=@@engine_condition_pushdown; +#SET SESSION engine_condition_pushdown = 'OFF'; + +CREATE TABLE t1 ( + pk int(11) NOT NULL, + i int(11) DEFAULT NULL, + v varchar(1) DEFAULT NULL, + PRIMARY KEY (pk) +); + +INSERT INTO t1 VALUES (2,7,'m'); +INSERT INTO t1 VALUES (3,9,'m'); + +SELECT v +FROM t1 +WHERE NOT pk > 0 +HAVING v <= 't' +ORDER BY pk; + +# Needed in 6.0 codebase +#--echo # Restore old value for Index condition pushdown +#SET SESSION engine_condition_pushdown=@old_icp; + +DROP TABLE t1; + --echo End of 5.1 tests -- cgit v1.2.1 From bc43bff7edf79095c243cf6858acb29213fb152b Mon Sep 17 00:00:00 2001 From: Evgeny Potemkin Date: Tue, 17 Nov 2009 17:06:46 +0300 Subject: Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) MySQL manual describes values of the YEAR(2) field type as follows: values 00 - 69 mean 2000 - 2069 years and values 70 - 99 mean 1970 - 1999 years. MIN/MAX and comparison functions was comparing them as int values thus producing wrong result. Now the Arg_comparator class is extended with compare_year function which performs correct comparison of the YEAR type. The Item_sum_hybrid class now uses Item_cache and Arg_comparator objects to correctly calculate its value. To allow Arg_comparator to use func_name() function for Item_func and Item_sum objects the func_name declaration is moved to the Item_result_field class. A helper function is_owner_equal_func is added to the Arg_comparator class. It checks whether the Arg_comparator object owner is the <=> function or not. A helper function setup is added to the Item_sum_hybrid class. It sets up cache item and comparator. mysql-test/r/func_group.result: Added a test case for the bug#43668. mysql-test/t/func_group.test: Added a test case for the bug#43668. sql/item.cc: Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) Now Item_cache_int returns the type of cached item. sql/item.h: Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) To allow Arg_comparator to use func_name() function for Item_func and Item_sum objects the func_name declaration is moved to the Item_result_field class. sql/item_cmpfunc.cc: Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) The Arg_comparator class is extended with compare_year function which performs correct comparison of the YEAR type. sql/item_cmpfunc.h: Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) The year_as_datetime variable is added to the Arg_comparator class. It's set to TRUE when YEAR value should be converted to the YYYY-00-00 00:00:00 format for correct YEAR-DATETIME comparison. sql/item_geofunc.cc: Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) Item_func_spatial_rel::val_int chenged to use Arg_comparator's string buffers. sql/item_subselect.h: Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) Added an implementation of the virtual func_name function. sql/item_sum.cc: Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) The Item_sum_hybrid class now uses Item_cache and Arg_comparator objects to correctly calculate its value. A helper function setup is added to the Item_sum_hybrid class. It sets up cache item and comparator. sql/item_sum.h: Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) The Item_sum_hybrid class now uses Item_cache and Arg_comparator objects to correctly calculate its value. Added an implementation of the virtual func_name function. --- mysql-test/r/func_group.result | 195 ++++++++++++++++++++++++++++++++++++++++- mysql-test/t/func_group.test | 32 +++++++ 2 files changed, 226 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 3d989ad1730..b0877991367 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -885,7 +885,7 @@ cast(sum(distinct df) as signed) 3 select cast(min(df) as signed) from t1; cast(min(df) as signed) -0 +1 select 1e8 * sum(distinct df) from t1; 1e8 * sum(distinct df) 330000000 @@ -1477,3 +1477,196 @@ COUNT(*) SET SQL_MODE=default; DROP TABLE t1; End of 5.0 tests +# +# Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) +# +create table t1 (f1 year(2), f2 year(4), f3 date, f4 datetime); +insert into t1 values +(98,1998,19980101,"1998-01-01 00:00:00"), +(00,2000,20000101,"2000-01-01 00:00:01"), +(02,2002,20020101,"2002-01-01 23:59:59"), +(60,2060,20600101,"2060-01-01 11:11:11"), +(70,1970,19700101,"1970-11-11 22:22:22"), +(NULL,NULL,NULL,NULL); +select min(f1),max(f1) from t1; +min(f1) max(f1) +70 60 +select min(f2),max(f2) from t1; +min(f2) max(f2) +1970 2060 +select min(f3),max(f3) from t1; +min(f3) max(f3) +1970-01-01 2060-01-01 +select min(f4),max(f4) from t1; +min(f4) max(f4) +1970-11-11 22:22:22 2060-01-01 11:11:11 +select a.f1 as a, b.f1 as b, a.f1 > b.f1 as gt, +a.f1 < b.f1 as lt, a.f1<=>b.f1 as eq +from t1 a, t1 b; +a b gt lt eq +98 98 0 0 1 +00 98 1 0 0 +02 98 1 0 0 +60 98 1 0 0 +70 98 0 1 0 +NULL 98 NULL NULL 0 +98 00 0 1 0 +00 00 0 0 1 +02 00 1 0 0 +60 00 1 0 0 +70 00 0 1 0 +NULL 00 NULL NULL 0 +98 02 0 1 0 +00 02 0 1 0 +02 02 0 0 1 +60 02 1 0 0 +70 02 0 1 0 +NULL 02 NULL NULL 0 +98 60 0 1 0 +00 60 0 1 0 +02 60 0 1 0 +60 60 0 0 1 +70 60 0 1 0 +NULL 60 NULL NULL 0 +98 70 1 0 0 +00 70 1 0 0 +02 70 1 0 0 +60 70 1 0 0 +70 70 0 0 1 +NULL 70 NULL NULL 0 +98 NULL NULL NULL 0 +00 NULL NULL NULL 0 +02 NULL NULL NULL 0 +60 NULL NULL NULL 0 +70 NULL NULL NULL 0 +NULL NULL NULL NULL 1 +select a.f1 as a, b.f2 as b, a.f1 > b.f2 as gt, +a.f1 < b.f2 as lt, a.f1<=>b.f2 as eq +from t1 a, t1 b; +a b gt lt eq +98 1998 0 0 1 +00 1998 1 0 0 +02 1998 1 0 0 +60 1998 1 0 0 +70 1998 0 1 0 +NULL 1998 NULL NULL 0 +98 2000 0 1 0 +00 2000 0 0 1 +02 2000 1 0 0 +60 2000 1 0 0 +70 2000 0 1 0 +NULL 2000 NULL NULL 0 +98 2002 0 1 0 +00 2002 0 1 0 +02 2002 0 0 1 +60 2002 1 0 0 +70 2002 0 1 0 +NULL 2002 NULL NULL 0 +98 2060 0 1 0 +00 2060 0 1 0 +02 2060 0 1 0 +60 2060 0 0 1 +70 2060 0 1 0 +NULL 2060 NULL NULL 0 +98 1970 1 0 0 +00 1970 1 0 0 +02 1970 1 0 0 +60 1970 1 0 0 +70 1970 0 0 1 +NULL 1970 NULL NULL 0 +98 NULL NULL NULL 0 +00 NULL NULL NULL 0 +02 NULL NULL NULL 0 +60 NULL NULL NULL 0 +70 NULL NULL NULL 0 +NULL NULL NULL NULL 1 +select a.f1 as a, b.f3 as b, a.f1 > b.f3 as gt, +a.f1 < b.f3 as lt, a.f1<=>b.f3 as eq +from t1 a, t1 b; +a b gt lt eq +98 1998-01-01 0 1 0 +00 1998-01-01 1 0 0 +02 1998-01-01 1 0 0 +60 1998-01-01 1 0 0 +70 1998-01-01 0 1 0 +NULL 1998-01-01 NULL NULL 0 +98 2000-01-01 0 1 0 +00 2000-01-01 0 1 0 +02 2000-01-01 1 0 0 +60 2000-01-01 1 0 0 +70 2000-01-01 0 1 0 +NULL 2000-01-01 NULL NULL 0 +98 2002-01-01 0 1 0 +00 2002-01-01 0 1 0 +02 2002-01-01 0 1 0 +60 2002-01-01 1 0 0 +70 2002-01-01 0 1 0 +NULL 2002-01-01 NULL NULL 0 +98 2060-01-01 0 1 0 +00 2060-01-01 0 1 0 +02 2060-01-01 0 1 0 +60 2060-01-01 0 1 0 +70 2060-01-01 0 1 0 +NULL 2060-01-01 NULL NULL 0 +98 1970-01-01 1 0 0 +00 1970-01-01 1 0 0 +02 1970-01-01 1 0 0 +60 1970-01-01 1 0 0 +70 1970-01-01 0 1 0 +NULL 1970-01-01 NULL NULL 0 +98 NULL NULL NULL 0 +00 NULL NULL NULL 0 +02 NULL NULL NULL 0 +60 NULL NULL NULL 0 +70 NULL NULL NULL 0 +NULL NULL NULL NULL 1 +select a.f1 as a, b.f4 as b, a.f1 > b.f4 as gt, +a.f1 < b.f4 as lt, a.f1<=>b.f4 as eq +from t1 a, t1 b; +a b gt lt eq +98 1998-01-01 00:00:00 0 1 0 +00 1998-01-01 00:00:00 1 0 0 +02 1998-01-01 00:00:00 1 0 0 +60 1998-01-01 00:00:00 1 0 0 +70 1998-01-01 00:00:00 0 1 0 +NULL 1998-01-01 00:00:00 NULL NULL 0 +98 2000-01-01 00:00:01 0 1 0 +00 2000-01-01 00:00:01 0 1 0 +02 2000-01-01 00:00:01 1 0 0 +60 2000-01-01 00:00:01 1 0 0 +70 2000-01-01 00:00:01 0 1 0 +NULL 2000-01-01 00:00:01 NULL NULL 0 +98 2002-01-01 23:59:59 0 1 0 +00 2002-01-01 23:59:59 0 1 0 +02 2002-01-01 23:59:59 0 1 0 +60 2002-01-01 23:59:59 1 0 0 +70 2002-01-01 23:59:59 0 1 0 +NULL 2002-01-01 23:59:59 NULL NULL 0 +98 2060-01-01 11:11:11 0 1 0 +00 2060-01-01 11:11:11 0 1 0 +02 2060-01-01 11:11:11 0 1 0 +60 2060-01-01 11:11:11 0 1 0 +70 2060-01-01 11:11:11 0 1 0 +NULL 2060-01-01 11:11:11 NULL NULL 0 +98 1970-11-11 22:22:22 1 0 0 +00 1970-11-11 22:22:22 1 0 0 +02 1970-11-11 22:22:22 1 0 0 +60 1970-11-11 22:22:22 1 0 0 +70 1970-11-11 22:22:22 0 1 0 +NULL 1970-11-11 22:22:22 NULL NULL 0 +98 NULL NULL NULL 0 +00 NULL NULL NULL 0 +02 NULL NULL NULL 0 +60 NULL NULL NULL 0 +70 NULL NULL NULL 0 +NULL NULL NULL NULL 1 +select *, f1 = f2 from t1; +f1 f2 f3 f4 f1 = f2 +98 1998 1998-01-01 1998-01-01 00:00:00 1 +00 2000 2000-01-01 2000-01-01 00:00:01 1 +02 2002 2002-01-01 2002-01-01 23:59:59 1 +60 2060 2060-01-01 2060-01-01 11:11:11 1 +70 1970 1970-01-01 1970-11-11 22:22:22 1 +NULL NULL NULL NULL NULL +drop table t1; +# diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index b0a3d0feb79..02d2676a3c2 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1006,3 +1006,35 @@ DROP TABLE t1; ### --echo End of 5.0 tests + +--echo # +--echo # Bug#43668: Wrong comparison and MIN/MAX for YEAR(2) +--echo # +create table t1 (f1 year(2), f2 year(4), f3 date, f4 datetime); +insert into t1 values + (98,1998,19980101,"1998-01-01 00:00:00"), + (00,2000,20000101,"2000-01-01 00:00:01"), + (02,2002,20020101,"2002-01-01 23:59:59"), + (60,2060,20600101,"2060-01-01 11:11:11"), + (70,1970,19700101,"1970-11-11 22:22:22"), + (NULL,NULL,NULL,NULL); +select min(f1),max(f1) from t1; +select min(f2),max(f2) from t1; +select min(f3),max(f3) from t1; +select min(f4),max(f4) from t1; +select a.f1 as a, b.f1 as b, a.f1 > b.f1 as gt, + a.f1 < b.f1 as lt, a.f1<=>b.f1 as eq +from t1 a, t1 b; +select a.f1 as a, b.f2 as b, a.f1 > b.f2 as gt, + a.f1 < b.f2 as lt, a.f1<=>b.f2 as eq +from t1 a, t1 b; +select a.f1 as a, b.f3 as b, a.f1 > b.f3 as gt, + a.f1 < b.f3 as lt, a.f1<=>b.f3 as eq +from t1 a, t1 b; +select a.f1 as a, b.f4 as b, a.f1 > b.f4 as gt, + a.f1 < b.f4 as lt, a.f1<=>b.f4 as eq +from t1 a, t1 b; +select *, f1 = f2 from t1; +drop table t1; +--echo # + -- cgit v1.2.1 From 8cfa50e6771f1dbd6f265be44d9340a7d53084c2 Mon Sep 17 00:00:00 2001 From: Alexey Kopytov Date: Tue, 17 Nov 2009 17:07:14 +0300 Subject: Bug #48472: Loose index scan inappropriately chosen for some WHERE conditions check_group_min_max() checks if the loose index scan optimization is applicable for a given WHERE condition, that is if the MIN/MAX attribute participates only in range predicates comparing the corresponding field with constants. The problem was that it considered the whole predicate suitable for the loose index scan optimization as soon as it encountered a constant as a predicate argument. This is obviously wrong for cases when a constant is the first argument of a predicate which does not satisfy the above condition. Fixed check_group_min_max() so that all arguments of the input predicate are considered to decide if it passes the test, even though a constant has already been encountered. mysql-test/r/group_min_max.result: Added a test case for bug #48472. mysql-test/t/group_min_max.test: Added a test case for bug #48472. sql/opt_range.cc: Fixed check_group_min_max() so that all arguments of the input predicate are considered to decide if it passes the test, even though a constant has already been encountered. --- mysql-test/r/group_min_max.result | 11 +++++++++++ mysql-test/t/group_min_max.test | 12 ++++++++++++ 2 files changed, 23 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 9acbbaac499..46076fb288d 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2480,4 +2480,15 @@ SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; a MAX(b) 2 1 DROP TABLE t; +# +# Bug #48472: Loose index scan inappropriately chosen for some WHERE +# conditions +# +CREATE TABLE t (a INT, b INT, INDEX (a,b)); +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; +SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a; +a MAX(b) +2 0 +DROP TABLE t; End of 5.0 tests diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index c81babb42e6..0d1cd076fb7 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -991,5 +991,17 @@ SELECT a, MAX(b) FROM t WHERE b > 0 AND b < 2 GROUP BY a; DROP TABLE t; +--echo # +--echo # Bug #48472: Loose index scan inappropriately chosen for some WHERE +--echo # conditions +--echo # + +CREATE TABLE t (a INT, b INT, INDEX (a,b)); +INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); +INSERT INTO t SELECT * FROM t; + +SELECT a, MAX(b) FROM t WHERE 0=b+0 GROUP BY a; + +DROP TABLE t; --echo End of 5.0 tests -- cgit v1.2.1 From 98fd304278ca029b56671412e8bec72ed4f17537 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Tue, 17 Nov 2009 22:47:34 +0100 Subject: backport of bug#45904 from mysql-pe to 5.1 sql/sql_partition.cc: Bug#45904 Used list_of_part_fields instead of list_of_subpart_fields to discover if KEY subpartitioning => caused failure when charset=utf8 even for subpartitioning by key, would also allow for subpartitioning by hash with utf8 erroneously --- mysql-test/r/partition.result | 7 +++++++ mysql-test/t/partition.test | 11 +++++++++++ 2 files changed, 18 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 6611d39628f..425f1e85d00 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -50,6 +50,13 @@ t1 CREATE TABLE `t1` ( PARTITION p3 VALUES LESS THAN (733969) ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ DROP TABLE t1; +create table t1 (a int NOT NULL, b varchar(5) NOT NULL) +default charset=utf8 +partition by list (a) +subpartition by key (b) +(partition p0 values in (1), +partition p1 values in (2)); +drop table t1; create table t1 (a int, b int, key(a)) partition by list (a) ( partition p0 values in (1), diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 1dfc53c6232..1d77128d33b 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -61,6 +61,17 @@ SELECT * FROM t1; SHOW CREATE TABLE t1; DROP TABLE t1; +# +# Bug#45904: Error when CHARSET=utf8 and subpartitioning +# +create table t1 (a int NOT NULL, b varchar(5) NOT NULL) +default charset=utf8 +partition by list (a) +subpartition by key (b) +(partition p0 values in (1), + partition p1 values in (2)); +drop table t1; + # # Bug#44059: rec_per_key on empty partition gives weird optimiser results # -- cgit v1.2.1 From 41ba42928628fc555cb2f222fbacefa178f63f0a Mon Sep 17 00:00:00 2001 From: Jon Olav Hauglid Date: Wed, 18 Nov 2009 10:02:21 +0100 Subject: Bug #47682 strange behaviour of INSERT DELAYED The problem was a "self-deadlock" if the connection issuing INSERT DELAYED had both the global read lock (FLUSH TABLES WITH READ LOCK) and LOCK TABLES mode active. The table being inserted into had to be different from the table(s) locked by LOCK TABLES. For INSERT DELAYED, the connection thread waits until the handler thread has opened and locked its table before returning. But since the global read lock was active, the handler thread would be unable to lock and would wait for the global read lock to go away. So the handler thread would be waiting for the connection thread to release the global read lock while the connection thread was waiting for the handler thread to lock the table. This gave a "self-deadlock" (same connection, different threads). The deadlock would only happen if we also had LOCK TABLES mode since the INSERT otherwise will try to get protection against global read lock before starting the handler thread. It will then notice that the global read lock is owned by the same connection and report ER_CANT_UPDATE_WITH_READLOCK. This patch removes the deadlock by reporting ER_CANT_UPDATE_WITH_READLOCK also if we are inside LOCK TABLES mode. Test case added to delayed.test. --- mysql-test/r/delayed.result | 12 ++++++++++++ mysql-test/t/delayed.test | 22 ++++++++++++++++++++++ 2 files changed, 34 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index 4d5d656f3ce..11156b0f883 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -310,4 +310,16 @@ a b 2 2 drop table t1; set global low_priority_updates = @old_delayed_updates; +# +# Bug #47682 strange behaviour of INSERT DELAYED +# +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (f1 integer); +CREATE TABLE t2 (f1 integer); +FLUSH TABLES WITH READ LOCK; +LOCK TABLES t1 READ; +INSERT DELAYED INTO t2 VALUES (1); +ERROR HY000: Can't execute the query because you have a conflicting read lock +UNLOCK TABLES; +DROP TABLE t1, t2; End of 5.1 tests diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test index 94ad22b80d0..99ec99803a7 100644 --- a/mysql-test/t/delayed.test +++ b/mysql-test/t/delayed.test @@ -328,4 +328,26 @@ drop table t1; set global low_priority_updates = @old_delayed_updates; + +--echo # +--echo # Bug #47682 strange behaviour of INSERT DELAYED +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (f1 integer); +CREATE TABLE t2 (f1 integer); + +FLUSH TABLES WITH READ LOCK; +LOCK TABLES t1 READ; + +--error ER_CANT_UPDATE_WITH_READLOCK +INSERT DELAYED INTO t2 VALUES (1); + +UNLOCK TABLES; +DROP TABLE t1, t2; + + --echo End of 5.1 tests -- cgit v1.2.1 From 3987c7ac4b7ecfde471c10386bb413205809dfe1 Mon Sep 17 00:00:00 2001 From: Magne Mahre Date: Wed, 18 Nov 2009 10:32:03 +0100 Subject: Bug #46425 crash in Diagnostics_area::set_ok_status , empty statement, DELETE IGNORE The ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG error was set in the diagnostics area when it happened, but the DELETE cleanup code never checked for a non-fatal error condition, thus trying to set diag.area to "ok". This triggered an assert checking that the diag.area was empty. The fix was to test if there existed a non-fatal error condition (thd->is_error() before ok'ing the operation. --- mysql-test/r/delete.result | 13 +++++++++++++ mysql-test/t/delete.test | 22 ++++++++++++++++++++++ 2 files changed, 35 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 0124a7da35a..1df19a75854 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -324,3 +324,16 @@ a 1 2 DROP TABLE t1, t2, t3; +# +# Bug #46425 crash in Diagnostics_area::set_ok_status, +# empty statement, DELETE IGNORE +# +CREATE table t1 (i INTEGER); +INSERT INTO t1 VALUES (1); +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +BEGIN +INSERT INTO t1 SELECT * FROM t1 AS A; +END | +DELETE IGNORE FROM t1; +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +DROP TABLE t1; diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index d77f5eb128b..a5dff38c078 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -336,3 +336,25 @@ SELECT * FROM t2; SELECT * FROM t3; DROP TABLE t1, t2, t3; + +--echo # +--echo # Bug #46425 crash in Diagnostics_area::set_ok_status, +--echo # empty statement, DELETE IGNORE +--echo # + +CREATE table t1 (i INTEGER); + +INSERT INTO t1 VALUES (1); + +--delimiter | + +CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW +BEGIN + INSERT INTO t1 SELECT * FROM t1 AS A; +END | + +--delimiter ; +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +DELETE IGNORE FROM t1; + +DROP TABLE t1; \ No newline at end of file -- cgit v1.2.1 From 7524b96102bcfeb7366b88d307bb53e9fe8c309f Mon Sep 17 00:00:00 2001 From: Jon Olav Hauglid Date: Wed, 18 Nov 2009 13:49:45 +0100 Subject: Postfix for Bug #47682 strange behaviour of INSERT DELAYED Fixed a problem with the test case when executed with ps-protocol. There the conflicing lock would be noticed during prepare, not during execution of the insert - leading to a different (but equally appropriate) error message. --- mysql-test/r/delayed.result | 2 +- mysql-test/t/delayed.test | 4 +++- 2 files changed, 4 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index 11156b0f883..d8048a703a2 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -319,7 +319,7 @@ CREATE TABLE t2 (f1 integer); FLUSH TABLES WITH READ LOCK; LOCK TABLES t1 READ; INSERT DELAYED INTO t2 VALUES (1); -ERROR HY000: Can't execute the query because you have a conflicting read lock +Got one of the listed errors UNLOCK TABLES; DROP TABLE t1, t2; End of 5.1 tests diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test index 99ec99803a7..689341391c9 100644 --- a/mysql-test/t/delayed.test +++ b/mysql-test/t/delayed.test @@ -343,7 +343,9 @@ CREATE TABLE t2 (f1 integer); FLUSH TABLES WITH READ LOCK; LOCK TABLES t1 READ; ---error ER_CANT_UPDATE_WITH_READLOCK +# ER_CANT_UPDATE_WITH_READLOCK with normal execution +# ER_TABLE_NOT_LOCKED when executed as prepared statement +--error ER_CANT_UPDATE_WITH_READLOCK, ER_TABLE_NOT_LOCKED INSERT DELAYED INTO t2 VALUES (1); UNLOCK TABLES; -- cgit v1.2.1 From 2f78abd21b11aa53fc8426c018b78621f77157f7 Mon Sep 17 00:00:00 2001 From: Sven Sandberg Date: Wed, 18 Nov 2009 15:50:31 +0100 Subject: BUG#47995: Mark system functions as unsafe Problem: Some system functions that could return different values on master and slave were not marked unsafe. In particular: GET_LOCK IS_FREE_LOCK IS_USED_LOCK MASTER_POS_WAIT RELEASE_LOCK SLEEP SYSDATE VERSION Fix: Mark these functions unsafe. mysql-test/extra/rpl_tests/rpl_stm_000001.test: - The test does not work in mixed mode any more, since it tries to simulate an error in the sql thread in a query that uses get_lock. Since get_lock now causes the query to be logged in row format, the error didn't happen. Hence, we now force statement mode. - Warnings must be disabled when the unsafe query is issued. - Replaced some save_master_pos+connection slave+sync_with_master by sync_slave_with_master. mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result: updated result file mysql-test/suite/binlog/r/binlog_stm_row.result: updated result file mysql-test/suite/binlog/r/binlog_unsafe.result: updated result file mysql-test/suite/binlog/t/binlog_killed.test: binlog_killed only works in statement format now, since it switches to row mode in mixed mode. mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test: suppress warnings for unsafe statements mysql-test/suite/binlog/t/binlog_stm_row.test: - Suppress warnings in test that causes warnings. - The test sets binlog format explicitly, so no need to execute it twice. mysql-test/suite/binlog/t/binlog_unsafe.test: Added test for all unsafe system functions. This test also includes system functions that were unsafe prior to BUG#47995. mysql-test/suite/rpl/r/rpl_err_ignoredtable.result: updated result file mysql-test/suite/rpl/r/rpl_get_lock.result: updated result file mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result: new result file mysql-test/suite/rpl/r/rpl_stm_000001.result: updated result file mysql-test/suite/rpl/r/rpl_trigger.result: updated result file mysql-test/suite/rpl/t/rpl_err_ignoredtable.test: - suppress warnings for unsafe statement - replaced save_master_pos+connection slave+sync_with_master with sync_slave_with_master mysql-test/suite/rpl/t/rpl_get_lock.test: update test case that causes new warnings mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test: Added new test case for nondeterministic functions. mysql-test/suite/rpl/t/rpl_trigger.test: update test case that causes new warnings sql/item_create.cc: Marked some system functions unsafe. sql/item_strfunc.cc: Clarified comment related to this bug. sql/sql_yacc.yy: Marked sysdate unsafe. --- mysql-test/extra/rpl_tests/rpl_stm_000001.test | 33 +++++---- .../binlog/r/binlog_stm_mix_innodb_myisam.result | 3 + mysql-test/suite/binlog/r/binlog_stm_row.result | 3 + mysql-test/suite/binlog/r/binlog_unsafe.result | 82 ++++++++++++++++++++++ mysql-test/suite/binlog/t/binlog_killed.test | 2 +- .../binlog/t/binlog_stm_mix_innodb_myisam.test | 3 + mysql-test/suite/binlog/t/binlog_stm_row.test | 5 +- mysql-test/suite/binlog/t/binlog_unsafe.test | 52 ++++++++++++++ mysql-test/suite/rpl/r/rpl_err_ignoredtable.result | 1 + mysql-test/suite/rpl/r/rpl_get_lock.result | 1 + .../rpl/r/rpl_nondeterministic_functions.result | 26 +++++++ mysql-test/suite/rpl/r/rpl_stm_000001.result | 1 + mysql-test/suite/rpl/r/rpl_trigger.result | 1 + mysql-test/suite/rpl/t/rpl_err_ignoredtable.test | 21 +++--- mysql-test/suite/rpl/t/rpl_get_lock.test | 5 ++ .../rpl/t/rpl_nondeterministic_functions.test | 53 ++++++++++++++ mysql-test/suite/rpl/t/rpl_trigger.test | 10 +++ 17 files changed, 271 insertions(+), 31 deletions(-) create mode 100644 mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result create mode 100644 mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test (limited to 'mysql-test') diff --git a/mysql-test/extra/rpl_tests/rpl_stm_000001.test b/mysql-test/extra/rpl_tests/rpl_stm_000001.test index 869a9e3b07c..4397e81aadf 100644 --- a/mysql-test/extra/rpl_tests/rpl_stm_000001.test +++ b/mysql-test/extra/rpl_tests/rpl_stm_000001.test @@ -1,6 +1,11 @@ --- source include/have_binlog_format_mixed_or_statement.inc +# Requires binlog_format=statement format since query involving +# get_lock() is logged in row format if binlog_format=mixed or row. +-- source include/have_binlog_format_statement.inc -- source include/master-slave.inc +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); + +# Load some data into t1 create table t1 (word char(20) not null); load data infile '../../std_data/words.dat' into table t1; --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR @@ -10,9 +15,7 @@ select * from t1 limit 10; # # Test slave with wrong password # -save_master_pos; -connection slave; -sync_with_master; +sync_slave_with_master; stop slave; connection master; set password for root@"localhost" = password('foo'); @@ -29,16 +32,12 @@ sleep 2; create table t3(n int); insert into t3 values(1),(2); -save_master_pos; -connection slave; -sync_with_master; +sync_slave_with_master; select * from t3; select sum(length(word)) from t1; connection master; drop table t1,t3; -save_master_pos; -connection slave; -sync_with_master; +sync_slave_with_master; # Test if the slave SQL thread can be more than 16K behind the slave # I/O thread (> IO_SIZE) @@ -77,12 +76,13 @@ unlock tables; connection master; create table t2(id int); insert into t2 values(connection_id()); -save_master_pos; connection master1; # Avoid generating result create temporary table t3(n int); +--disable_warnings insert into t3 select get_lock('crash_lock%20C', 1) from t2; +--enable_warnings connection master; send update t1 set n = n + get_lock('crash_lock%20C', 2); @@ -93,8 +93,11 @@ kill @id; # We don't drop t3 as this is a temporary table drop table t2; connection master; +# The get_lock function causes warning for unsafe statement. +--disable_warnings --error 1317,2013 reap; +--enable_warnings connection slave; # The SQL slave thread should now have stopped because the query was killed on # the master (so it has a non-zero error code in the binlog). @@ -117,16 +120,12 @@ insert into mysql.user (Host, User, Password) select select_priv,user from mysql.user where user = _binary'blafasel2'; update mysql.user set Select_priv = "Y" where User= _binary"blafasel2"; select select_priv,user from mysql.user where user = _binary'blafasel2'; -save_master_pos; -connection slave; -sync_with_master; +sync_slave_with_master; select n from t1; select select_priv,user from mysql.user where user = _binary'blafasel2'; connection master1; drop table t1; delete from mysql.user where user="blafasel2"; -save_master_pos; -connection slave; -sync_with_master; +sync_slave_with_master; # End of 4.1 tests diff --git a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result index 06c57fba2e7..de83c7a873d 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result @@ -1,3 +1,4 @@ +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); drop table if exists t1, t2; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; @@ -224,6 +225,8 @@ create table t0 (n int); insert t0 select * from t1; set autocommit=1; insert into t0 select GET_LOCK("lock1",null); +Warnings: +Note 1592 Statement may not be safe to log in statement format. set autocommit=0; create table t2 (n int) engine=innodb; insert into t2 values (3); diff --git a/mysql-test/suite/binlog/r/binlog_stm_row.result b/mysql-test/suite/binlog/r/binlog_stm_row.result index f96073a2b92..e74a05e8827 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_row.result +++ b/mysql-test/suite/binlog/r/binlog_stm_row.result @@ -1,3 +1,4 @@ +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; set @saved_global_binlog_format = @@global.binlog_format; @@ -29,6 +30,8 @@ SELECT RELEASE_LOCK('Bug#34306'); RELEASE_LOCK('Bug#34306') 1 # con2 +Warnings: +Note 1592 Statement may not be safe to log in statement format. SELECT RELEASE_LOCK('Bug#34306'); RELEASE_LOCK('Bug#34306') 1 diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result index 4c2c32ad8f1..8bbf993a727 100644 --- a/mysql-test/suite/binlog/r/binlog_unsafe.result +++ b/mysql-test/suite/binlog/r/binlog_unsafe.result @@ -327,4 +327,86 @@ Warnings: Note 1592 Statement may not be safe to log in statement format. DROP TABLE t1, t2; SET @@SESSION.SQL_MODE = @save_sql_mode; +CREATE TABLE t1 (a VARCHAR(1000)); +INSERT INTO t1 VALUES (CURRENT_USER()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (FOUND_ROWS()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (GET_LOCK('tmp', 1)); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp')); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (IS_USED_LOCK('tmp')); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1)); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (RELEASE_LOCK('tmp')); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (ROW_COUNT()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (SESSION_USER()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (SLEEP(1)); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (SYSDATE()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (SYSTEM_USER()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (USER()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (UUID()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (UUID_SHORT()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +INSERT INTO t1 VALUES (VERSION()); +Warnings: +Note 1592 Statement may not be safe to log in statement format. +DELETE FROM t1; +SET TIMESTAMP=1000000; +INSERT INTO t1 VALUES +(CURDATE()), +(CURRENT_DATE()), +(CURRENT_TIME()), +(CURRENT_TIMESTAMP()), +(CURTIME()), +(LOCALTIME()), +(LOCALTIMESTAMP()), +(NOW()), +(UNIX_TIMESTAMP()), +(UTC_DATE()), +(UTC_TIME()), +(UTC_TIMESTAMP()); +SELECT * FROM t1; +a +1970-01-12 +1970-01-12 +16:46:40 +1970-01-12 16:46:40 +16:46:40 +1970-01-12 16:46:40 +1970-01-12 16:46:40 +1970-01-12 16:46:40 +1000000 +1970-01-12 +13:46:40 +1970-01-12 13:46:40 +DROP TABLE t1; "End of tests" diff --git a/mysql-test/suite/binlog/t/binlog_killed.test b/mysql-test/suite/binlog/t/binlog_killed.test index 9b30ec4a0db..e2db326129d 100644 --- a/mysql-test/suite/binlog/t/binlog_killed.test +++ b/mysql-test/suite/binlog/t/binlog_killed.test @@ -1,5 +1,5 @@ -- source include/have_innodb.inc --- source include/have_binlog_format_mixed_or_statement.inc +-- source include/have_binlog_format_statement.inc # You cannot use `KILL' with the Embedded MySQL Server library, # because the embedded server merely runs inside the threads of the host diff --git a/mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test b/mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test index e4661526982..ba3da73dead 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test +++ b/mysql-test/suite/binlog/t/binlog_stm_mix_innodb_myisam.test @@ -2,6 +2,9 @@ # For both statement and row based bin logs 9/19/2005 [jbm] -- source include/have_binlog_format_statement.inc + +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); + -- source extra/binlog_tests/mix_innodb_myisam_binlog.test set @@session.binlog_format=statement; diff --git a/mysql-test/suite/binlog/t/binlog_stm_row.test b/mysql-test/suite/binlog/t/binlog_stm_row.test index e923faae940..29b0a69330d 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_row.test +++ b/mysql-test/suite/binlog/t/binlog_stm_row.test @@ -1,5 +1,8 @@ --source include/have_log_bin.inc ---source include/have_binlog_format_row_or_statement.inc +# Test sets its own binlog_format, so we restrict it to run only once +--source include/have_binlog_format_row.inc + +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); # Get rid of previous tests binlog --disable_query_log diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test index c4e1f31cbce..1acb4d090ca 100644 --- a/mysql-test/suite/binlog/t/binlog_unsafe.test +++ b/mysql-test/suite/binlog/t/binlog_unsafe.test @@ -388,4 +388,56 @@ DELETE FROM t1 LIMIT 1; DROP TABLE t1, t2; SET @@SESSION.SQL_MODE = @save_sql_mode; + +# +# BUG#47995: Mark user functions as unsafe +# +# Test that the system functions that are supposed to be marked unsafe +# generate a warning. Each INSERT statement below should generate a +# warning. +# + +CREATE TABLE t1 (a VARCHAR(1000)); +INSERT INTO t1 VALUES (CURRENT_USER()); #marked unsafe before BUG#47995 +INSERT INTO t1 VALUES (FOUND_ROWS()); #marked unsafe before BUG#47995 +INSERT INTO t1 VALUES (GET_LOCK('tmp', 1)); +INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp')); +INSERT INTO t1 VALUES (IS_USED_LOCK('tmp')); +INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe before BUG#47995 +INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1)); +INSERT INTO t1 VALUES (RELEASE_LOCK('tmp')); +INSERT INTO t1 VALUES (ROW_COUNT()); #marked unsafe before BUG#47995 +INSERT INTO t1 VALUES (SESSION_USER()); #marked unsafe before BUG#47995 +INSERT INTO t1 VALUES (SLEEP(1)); +INSERT INTO t1 VALUES (SYSDATE()); +INSERT INTO t1 VALUES (SYSTEM_USER()); #marked unsafe before BUG#47995 +INSERT INTO t1 VALUES (USER()); #marked unsafe before BUG#47995 +INSERT INTO t1 VALUES (UUID()); #marked unsafe before BUG#47995 +INSERT INTO t1 VALUES (UUID_SHORT()); #marked unsafe before BUG#47995 +INSERT INTO t1 VALUES (VERSION()); +DELETE FROM t1; + +# Since we replicate the TIMESTAMP variable, functions affected by the +# TIMESTAMP variable are safe to replicate. So we check that the +# following following functions depend on the TIMESTAMP variable and +# don't generate a warning. + +SET TIMESTAMP=1000000; +INSERT INTO t1 VALUES + (CURDATE()), + (CURRENT_DATE()), + (CURRENT_TIME()), + (CURRENT_TIMESTAMP()), + (CURTIME()), + (LOCALTIME()), + (LOCALTIMESTAMP()), + (NOW()), + (UNIX_TIMESTAMP()), + (UTC_DATE()), + (UTC_TIME()), + (UTC_TIMESTAMP()); +SELECT * FROM t1; + +DROP TABLE t1; + --echo "End of tests" diff --git a/mysql-test/suite/rpl/r/rpl_err_ignoredtable.result b/mysql-test/suite/rpl/r/rpl_err_ignoredtable.result index f211d5d9a2f..75fefdad2a2 100644 --- a/mysql-test/suite/rpl/r/rpl_err_ignoredtable.result +++ b/mysql-test/suite/rpl/r/rpl_err_ignoredtable.result @@ -4,6 +4,7 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); create table t1 (a int primary key); create table t4 (a int primary key); insert into t1 values (1),(1); diff --git a/mysql-test/suite/rpl/r/rpl_get_lock.result b/mysql-test/suite/rpl/r/rpl_get_lock.result index f7c9541bd9f..cbad759b17f 100644 --- a/mysql-test/suite/rpl/r/rpl_get_lock.result +++ b/mysql-test/suite/rpl/r/rpl_get_lock.result @@ -4,6 +4,7 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); create table t1(n int); insert into t1 values(get_lock("lock",2)); select get_lock("lock",2); diff --git a/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result b/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result new file mode 100644 index 00000000000..c4842a284cd --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result @@ -0,0 +1,26 @@ +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 VARCHAR(1000)); +INSERT INTO t1 VALUES (CONNECTION_ID()); +INSERT INTO t1 VALUES (CONNECTION_ID()); +INSERT INTO t1 VALUES +(CURDATE()), +(CURRENT_DATE()), +(CURRENT_TIME()), +(CURRENT_TIMESTAMP()), +(CURTIME()), +(LOCALTIME()), +(LOCALTIMESTAMP()), +(NOW()), +(UNIX_TIMESTAMP()), +(UTC_DATE()), +(UTC_TIME()), +(UTC_TIMESTAMP()); +INSERT INTO t1 VALUES (RAND()); +INSERT INTO t1 VALUES (LAST_INSERT_ID()); +Comparing tables master:test.t1 and slave:test.t1 +DROP TABLE t1; diff --git a/mysql-test/suite/rpl/r/rpl_stm_000001.result b/mysql-test/suite/rpl/r/rpl_stm_000001.result index 24e454eb876..c2a24cbaa82 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_000001.result +++ b/mysql-test/suite/rpl/r/rpl_stm_000001.result @@ -4,6 +4,7 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); create table t1 (word char(20) not null); load data infile '../../std_data/words.dat' into table t1; load data local infile 'MYSQL_TEST_DIR/std_data/words.dat' into table t1; diff --git a/mysql-test/suite/rpl/r/rpl_trigger.result b/mysql-test/suite/rpl/r/rpl_trigger.result index 3d7757613a7..86534fa8f7d 100644 --- a/mysql-test/suite/rpl/r/rpl_trigger.result +++ b/mysql-test/suite/rpl/r/rpl_trigger.result @@ -4,6 +4,7 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; diff --git a/mysql-test/suite/rpl/t/rpl_err_ignoredtable.test b/mysql-test/suite/rpl/t/rpl_err_ignoredtable.test index e77cd308f39..f358c0ee356 100644 --- a/mysql-test/suite/rpl/t/rpl_err_ignoredtable.test +++ b/mysql-test/suite/rpl/t/rpl_err_ignoredtable.test @@ -7,6 +7,8 @@ -- source include/master-slave.inc +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); + connection master; create table t1 (a int primary key); create table t4 (a int primary key); @@ -14,19 +16,15 @@ create table t4 (a int primary key); --error 1022, ER_DUP_ENTRY insert into t1 values (1),(1); insert into t4 values (1),(2); -save_master_pos; -connection slave; # as the t1 table is ignored on the slave, the slave should be able to sync -sync_with_master; +sync_slave_with_master; # check that the table has been ignored, because otherwise the test is nonsense show tables like 't1'; show tables like 't4'; SELECT * FROM test.t4 ORDER BY a; connection master; drop table t1; -save_master_pos; -connection slave; -sync_with_master; +sync_slave_with_master; # Now test that even critical errors (connection killed) # are ignored if rules allow it. @@ -50,18 +48,17 @@ kill @id; drop table t2,t3; insert into t4 values (3),(4); connection master; +# The get_lock function causes warning for unsafe statement. +--disable_warnings --error 0,1317,2013 reap; +--enable_warnings connection master1; -save_master_pos; -connection slave; -sync_with_master; +sync_slave_with_master; SELECT * FROM test.t4 ORDER BY a; connection master1; DROP TABLE test.t4; -save_master_pos; -connection slave; -sync_with_master; +sync_slave_with_master; # End of 4.1 tests # Adding comment for force manual merge 5.0 -> wl1012. delete me if needed diff --git a/mysql-test/suite/rpl/t/rpl_get_lock.test b/mysql-test/suite/rpl/t/rpl_get_lock.test index 945bd98c993..87366f41ba2 100644 --- a/mysql-test/suite/rpl/t/rpl_get_lock.test +++ b/mysql-test/suite/rpl/t/rpl_get_lock.test @@ -1,7 +1,12 @@ source include/master-slave.inc; +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); + create table t1(n int); +# Use of get_lock gives a warning for unsafeness if binlog_format=statement +--disable_warnings insert into t1 values(get_lock("lock",2)); +--enable_warnings dirty_close master; connection master1; select get_lock("lock",2); diff --git a/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test b/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test new file mode 100644 index 00000000000..a30eb3be374 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test @@ -0,0 +1,53 @@ +# ==== Purpose ==== +# +# Test that nondeterministic system functions are correctly replicated. +# +# (Some functions are only correctly replicated if binlog_format=MIXED +# or ROW. See binlog_unsafe.test for a test that those variables are +# indeed unsafe.) +# +# ==== Implementation ==== +# +# We insert the values of each unsafe function into a table. Then we +# replicate and check that the table is identical on slave. +# +# ==== Related bugs ==== +# +# BUG#47995 + +--source include/master-slave.inc + +CREATE TABLE t1 (a VARCHAR(1000)); + +# We replicate the connection_id in the query_log_event +INSERT INTO t1 VALUES (CONNECTION_ID()); +--connection master1 +INSERT INTO t1 VALUES (CONNECTION_ID()); + +# We replicate the TIMESTAMP variable, so the following functions that +# are affected by the TIMESTAMP variable should be safe to replicate. +INSERT INTO t1 VALUES + (CURDATE()), + (CURRENT_DATE()), + (CURRENT_TIME()), + (CURRENT_TIMESTAMP()), + (CURTIME()), + (LOCALTIME()), + (LOCALTIMESTAMP()), + (NOW()), + (UNIX_TIMESTAMP()), + (UTC_DATE()), + (UTC_TIME()), + (UTC_TIMESTAMP()); + +# We replicate the random seed in a rand_log_event +INSERT INTO t1 VALUES (RAND()); +# We replicate the last_insert_id in an intvar_log_event +INSERT INTO t1 VALUES (LAST_INSERT_ID()); + +--sync_slave_with_master +--let $diff_table_1= master:test.t1 +--let $diff_table_2= slave:test.t1 +--source include/diff_tables.inc + +DROP TABLE t1; diff --git a/mysql-test/suite/rpl/t/rpl_trigger.test b/mysql-test/suite/rpl/t/rpl_trigger.test index a3929458026..4b40fbea719 100644 --- a/mysql-test/suite/rpl/t/rpl_trigger.test +++ b/mysql-test/suite/rpl/t/rpl_trigger.test @@ -5,6 +5,8 @@ --source include/have_binlog_format_mixed_or_statement.inc --source include/master-slave.inc +CALL mtr.add_suppression("Statement may not be safe to log in statement format."); + --disable_warnings DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; @@ -89,7 +91,11 @@ end | delimiter ;| +# The trigger causes a warning for unsafe statement when +# binlog_format=statement since it uses get_lock. +--disable_warnings insert into t1 set a = now(); +--enable_warnings select a=b && a=c from t1; let $time=`select a from t1`; @@ -135,7 +141,11 @@ disconnect con2; truncate table t1; drop trigger t1_first; +# The trigger causes a warning for unsafe statement when +# binlog_format=statement since it uses get_lock. +--disable_warnings insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now()); +--enable_warnings select a=b && a=c from t1; drop function bug12480; -- cgit v1.2.1 From 405c9310cfe2cf4673f5fb66dc221016b4e92ed6 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Thu, 19 Nov 2009 18:26:19 +0200 Subject: Bug #48665: sql-bench's insert test fails due to wrong result When merging ranges during calculation of the result of OR to two range sets the current range may be obsoleted by the resulting merged range. The first overlapping range can be obsoleted as well. Fixed by moving the pointer to the first overlapping range to the pointer of the resulting union range. Added few comments at key places in key_or(). --- mysql-test/r/range.result | 17 +++++++++++++++++ mysql-test/t/range.test | 21 +++++++++++++++++++++ 2 files changed, 38 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 2306f8b501e..ab40eefdc82 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1603,4 +1603,21 @@ SELECT str_to_date('', '%Y-%m-%d'); str_to_date('', '%Y-%m-%d') 0000-00-00 DROP TABLE t1, t2; +# +# Bug #48665: sql-bench's insert test fails due to wrong result +# +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (0,0), (1,1); +EXPLAIN +SELECT * FROM t1 FORCE INDEX (PRIMARY) +WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); +id select_type table type possible_keys key key_len ref rows Extra +@ @ @ range @ @ @ @ @ @ +# Should return 2 rows +SELECT * FROM t1 FORCE INDEX (PRIMARY) +WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); +a b +0 0 +1 1 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 3a845471cd0..736d65792c5 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1260,4 +1260,25 @@ SELECT str_to_date('', '%Y-%m-%d'); DROP TABLE t1, t2; + +--echo # +--echo # Bug #48665: sql-bench's insert test fails due to wrong result +--echo # + +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)); + +INSERT INTO t1 VALUES (0,0), (1,1); + +--replace_column 1 @ 2 @ 3 @ 5 @ 6 @ 7 @ 8 @ 9 @ 10 @ +EXPLAIN +SELECT * FROM t1 FORCE INDEX (PRIMARY) + WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); + +--echo # Should return 2 rows +SELECT * FROM t1 FORCE INDEX (PRIMARY) + WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10); + +DROP TABLE t1; + + --echo End of 5.1 tests -- cgit v1.2.1 From a21cd97c3506a70297afc3dd3f64cdfd2dc0a4e6 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 20 Nov 2009 12:10:47 +0200 Subject: Bug #45261 : Crash, stored procedure + decimal Bug #48370 Absolutely wrong calculations with GROUP BY and decimal fields when using IF Added the test cases in the above two bugs for regression testing. Added additional tests that demonstrate a incomplete fix. Added a new factory method for Field_new_decimal to create a field from an (decimal returning) Item. In the new method made sure that all the precision and length variables are capped in a proper way. This is required because Item's can have larger precision than the decimal fields and thus need to be capped when creating a field based on an Item type. Fixed the wrong typecast to Item_decimal. --- mysql-test/r/type_newdecimal.result | 284 ++++++++++++++++++++++++++++++++++++ mysql-test/t/type_newdecimal.test | 226 ++++++++++++++++++++++++++++ 2 files changed, 510 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 748aadee4fb..70ee3a56cf3 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1630,3 +1630,287 @@ SELECT my_col FROM t1; my_col 0.012345687012345687012345687012 DROP TABLE t1; +# +# Bug#45261: Crash, stored procedure + decimal +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 SELECT +/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001 +AS c1; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,0) NO 0 +SELECT * FROM t1; +c1 +99999999999999999999999999999999999999999999999999999999999999999 +DROP TABLE t1; +CREATE TABLE t1 SELECT +/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001. +AS c1; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,0) NO 0 +SELECT * FROM t1; +c1 +99999999999999999999999999999999999999999999999999999999999999999 +DROP TABLE t1; +CREATE TABLE t1 SELECT +/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001.1 /* 1 */ +AS c1; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,0) NO 0 +SELECT * FROM t1; +c1 +99999999999999999999999999999999999999999999999999999999999999999 +DROP TABLE t1; +CREATE TABLE t1 SELECT +/* 82 */ 1000000000000000000000000000000000000000000000000000000000000000000000000000000001 +AS c1; +Warnings: +Error 1292 Truncated incorrect DECIMAL value: '' +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,0) NO 0 +SELECT * FROM t1; +c1 +99999999999999999999999999999999999999999999999999999999999999999 +DROP TABLE t1; +CREATE TABLE t1 SELECT +/* 40 */ 1000000000000000000000000000000000000001.1000000000000000000000000000000000000001 /* 40 */ +AS c1; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,30) NO 0.000000000000000000000000000000 +SELECT * FROM t1; +c1 +99999999999999999999999999999999999.999999999999999999999999999999 +DROP TABLE t1; +CREATE TABLE t1 SELECT +/* 1 */ 1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80 */ +AS c1; +DESC t1; +Field Type Null Key Default Extra +c1 decimal(31,30) NO 0.000000000000000000000000000000 +SELECT * FROM t1; +c1 +1.100000000000000000000000000000 +DROP TABLE t1; +CREATE TABLE t1 SELECT +/* 1 */ 1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ +AS c1; +DESC t1; +Field Type Null Key Default Extra +c1 decimal(31,30) NO 0.000000000000000000000000000000 +SELECT * FROM t1; +c1 +1.100000000000000000000000000000 +DROP TABLE t1; +CREATE TABLE t1 SELECT +.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ +AS c1; +Warnings: +Note 1265 Data truncated for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(30,30) NO 0.000000000000000000000000000000 +SELECT * FROM t1; +c1 +0.100000000000000000000000000000 +DROP TABLE t1; +CREATE TABLE t1 SELECT +/* 45 */ 123456789012345678901234567890123456789012345.123456789012345678901234567890123456789012345 /* 45 */ +AS c1; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,30) NO 0.000000000000000000000000000000 +SELECT * FROM t1; +c1 +99999999999999999999999999999999999.999999999999999999999999999999 +DROP TABLE t1; +CREATE TABLE t1 SELECT +/* 65 */ 12345678901234567890123456789012345678901234567890123456789012345.1 /* 1 */ +AS c1; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,1) NO 0.0 +SELECT * FROM t1; +c1 +9999999999999999999999999999999999999999999999999999999999999999.9 +DROP TABLE t1; +CREATE TABLE t1 SELECT +/* 66 */ 123456789012345678901234567890123456789012345678901234567890123456.1 /* 1 */ +AS c1; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,1) NO 0.0 +SELECT * FROM t1; +c1 +9999999999999999999999999999999999999999999999999999999999999999.9 +DROP TABLE t1; +CREATE TABLE t1 SELECT +.123456789012345678901234567890123456789012345678901234567890123456 /* 66 */ +AS c1; +Warnings: +Note 1265 Data truncated for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(30,30) NO 0.000000000000000000000000000000 +SELECT * FROM t1; +c1 +0.123456789012345678901234567890 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT 123.1234567890123456789012345678901 /* 31 */ AS c1; +Warnings: +Note 1265 Data truncated for column 'c1' at row 1 +DESC t1; +Field Type Null Key Default Extra +c1 decimal(33,30) NO 0.000000000000000000000000000000 +SELECT * FROM t1; +c1 +123.123456789012345678901234567890 +DROP TABLE t1; +CREATE TABLE t1 SELECT 1.1 + CAST(1 AS DECIMAL(65,30)) AS c1; +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,30) NO 0.000000000000000000000000000000 +SELECT * FROM t1; +c1 +2.100000000000000000000000000000 +DROP TABLE t1; +# +# Test that the integer and decimal parts are properly calculated. +# +CREATE TABLE t1 (a DECIMAL(30,30)); +INSERT INTO t1 VALUES (0.1),(0.2),(0.3); +CREATE TABLE t2 SELECT MIN(a + 0.0000000000000000000000000000001) AS c1 FROM t1; +Warnings: +Note 1265 Data truncated for column 'c1' at row 3 +DESC t2; +Field Type Null Key Default Extra +c1 decimal(32,30) YES NULL +DROP TABLE t1,t2; +CREATE TABLE t1 (a DECIMAL(30,30)); +INSERT INTO t1 VALUES (0.1),(0.2),(0.3); +CREATE TABLE t2 SELECT IFNULL(a + 0.0000000000000000000000000000001, NULL) AS c1 FROM t1; +Warnings: +Note 1265 Data truncated for column 'c1' at row 1 +Note 1265 Data truncated for column 'c1' at row 2 +Note 1265 Data truncated for column 'c1' at row 3 +DESC t2; +Field Type Null Key Default Extra +c1 decimal(34,0) YES NULL +DROP TABLE t1,t2; +CREATE TABLE t1 (a DECIMAL(30,30)); +INSERT INTO t1 VALUES (0.1),(0.2),(0.3); +CREATE TABLE t2 SELECT CASE a WHEN 0.1 THEN 0.0000000000000000000000000000000000000000000000000000000000000000001 END AS c1 FROM t1; +Warnings: +Note 1265 Data truncated for column 'c1' at row 1 +DESC t2; +Field Type Null Key Default Extra +c1 decimal(65,30) YES NULL +DROP TABLE t1,t2; +# +# Test that variables get maximum precision. +# +SET @decimal= 1.1; +CREATE TABLE t1 SELECT @decimal AS c1; +DESC t1; +Field Type Null Key Default Extra +c1 decimal(65,30) YES NULL +SELECT * FROM t1; +c1 +1.100000000000000000000000000000 +DROP TABLE t1; +# +# Bug #45261 : Crash, stored procedure + decimal +# Original test by the reporter. +# +# should not crash +CREATE TABLE t1 +SELECT .123456789012345678901234567890123456789012345678901234567890123456 AS a; +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +DROP TABLE t1; +CREATE PROCEDURE test_proc() +BEGIN +# The las non critical CUSER definition is: +# DECLARE mycursor CURSOR FOR SELECT 1 % +# .12345678912345678912345678912345678912345678912345678912345678912 AS my_col; +DECLARE mycursor CURSOR FOR +SELECT 1 % +.123456789123456789123456789123456789123456789123456789123456789123456789123456789 +AS my_col; +OPEN mycursor; +CLOSE mycursor; +END| +# should not crash +CALL test_proc(); +DROP PROCEDURE test_proc; +# +# Bug #48370 Absolutely wrong calculations with GROUP BY and +# decimal fields when using IF +# +CREATE TABLE currencies (id int, rate decimal(16,4), +PRIMARY KEY (id), KEY (rate)); +INSERT INTO currencies VALUES (11,0.7028); +INSERT INTO currencies VALUES (1,1); +CREATE TABLE payments ( +id int, +supplier_id int, +status int, +currency_id int, +vat decimal(7,4), +PRIMARY KEY (id), +KEY currency_id (currency_id), +KEY supplier_id (supplier_id) +); +INSERT INTO payments (id,status,vat,supplier_id,currency_id) VALUES +(3001,2,0.0000,344,11), (1,2,0.0000,1,1); +CREATE TABLE sub_tasks ( +id int, +currency_id int, +price decimal(16,4), +discount decimal(10,4), +payment_id int, +PRIMARY KEY (id), +KEY currency_id (currency_id), +KEY payment_id (payment_id) +) ; +INSERT INTO sub_tasks (id, price, discount, payment_id, currency_id) VALUES +(52, 12.60, 0, 3001, 11), (56, 14.58, 0, 3001, 11); +# should return 1 and the same values in col 2 and 3 +select STRAIGHT_JOIN +(1 + PAY.vat) AS mult, +SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 2)) * +CUR.rate / CUR.rate, 2) +) v_net_with_discount, +SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 1)) * +CUR.rate / CUR.rate , 2) +* (1 + PAY.vat) +) v_total +from +currencies CUR, payments PAY, sub_tasks SUB +where +SUB.payment_id = PAY.id and +PAY.currency_id = CUR.id and +PAY.id > 2 +group by PAY.id + 1; +mult v_net_with_discount v_total +1.0000 27.18 27.180000 +DROP TABLE currencies, payments, sub_tasks; +End of 5.1 tests diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index cd3c3f81510..2cf7ab8fbdf 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1286,3 +1286,229 @@ CREATE TABLE t1 SELECT 1 % .1234567891234567891234567891234567891234567891234567 DESCRIBE t1; SELECT my_col FROM t1; DROP TABLE t1; + +--echo # +--echo # Bug#45261: Crash, stored procedure + decimal +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 SELECT + /* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001 + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + /* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001. + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + /* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001.1 /* 1 */ + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + /* 82 */ 1000000000000000000000000000000000000000000000000000000000000000000000000000000001 + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + /* 40 */ 1000000000000000000000000000000000000001.1000000000000000000000000000000000000001 /* 40 */ + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + /* 1 */ 1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80 */ + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + /* 1 */ 1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + .100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + /* 45 */ 123456789012345678901234567890123456789012345.123456789012345678901234567890123456789012345 /* 45 */ + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + /* 65 */ 12345678901234567890123456789012345678901234567890123456789012345.1 /* 1 */ + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + /* 66 */ 123456789012345678901234567890123456789012345678901234567890123456.1 /* 1 */ + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT + .123456789012345678901234567890123456789012345678901234567890123456 /* 66 */ + AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT 123.1234567890123456789012345678901 /* 31 */ AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 SELECT 1.1 + CAST(1 AS DECIMAL(65,30)) AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Test that the integer and decimal parts are properly calculated. +--echo # + +CREATE TABLE t1 (a DECIMAL(30,30)); +INSERT INTO t1 VALUES (0.1),(0.2),(0.3); +CREATE TABLE t2 SELECT MIN(a + 0.0000000000000000000000000000001) AS c1 FROM t1; +DESC t2; +DROP TABLE t1,t2; + +CREATE TABLE t1 (a DECIMAL(30,30)); +INSERT INTO t1 VALUES (0.1),(0.2),(0.3); +CREATE TABLE t2 SELECT IFNULL(a + 0.0000000000000000000000000000001, NULL) AS c1 FROM t1; +DESC t2; +DROP TABLE t1,t2; + +CREATE TABLE t1 (a DECIMAL(30,30)); +INSERT INTO t1 VALUES (0.1),(0.2),(0.3); +CREATE TABLE t2 SELECT CASE a WHEN 0.1 THEN 0.0000000000000000000000000000000000000000000000000000000000000000001 END AS c1 FROM t1; +DESC t2; +DROP TABLE t1,t2; + +--echo # +--echo # Test that variables get maximum precision. +--echo # + +SET @decimal= 1.1; +CREATE TABLE t1 SELECT @decimal AS c1; +DESC t1; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Bug #45261 : Crash, stored procedure + decimal +--echo # Original test by the reporter. +--echo # + +--echo # should not crash +CREATE TABLE t1 +SELECT .123456789012345678901234567890123456789012345678901234567890123456 AS a; +DROP TABLE t1; + +delimiter |; +CREATE PROCEDURE test_proc() +BEGIN + # The las non critical CUSER definition is: + # DECLARE mycursor CURSOR FOR SELECT 1 % + # .12345678912345678912345678912345678912345678912345678912345678912 AS my_col; + DECLARE mycursor CURSOR FOR +SELECT 1 % +.123456789123456789123456789123456789123456789123456789123456789123456789123456789 + AS my_col; + + OPEN mycursor; + CLOSE mycursor; +END| +delimiter ;| +--echo # should not crash +CALL test_proc(); +DROP PROCEDURE test_proc; + +--echo # +--echo # Bug #48370 Absolutely wrong calculations with GROUP BY and +--echo # decimal fields when using IF +--echo # + +CREATE TABLE currencies (id int, rate decimal(16,4), + PRIMARY KEY (id), KEY (rate)); + +INSERT INTO currencies VALUES (11,0.7028); +INSERT INTO currencies VALUES (1,1); + +CREATE TABLE payments ( + id int, + supplier_id int, + status int, + currency_id int, + vat decimal(7,4), + PRIMARY KEY (id), + KEY currency_id (currency_id), + KEY supplier_id (supplier_id) +); + +INSERT INTO payments (id,status,vat,supplier_id,currency_id) VALUES +(3001,2,0.0000,344,11), (1,2,0.0000,1,1); + +CREATE TABLE sub_tasks ( + id int, + currency_id int, + price decimal(16,4), + discount decimal(10,4), + payment_id int, + PRIMARY KEY (id), + KEY currency_id (currency_id), + KEY payment_id (payment_id) +) ; + +INSERT INTO sub_tasks (id, price, discount, payment_id, currency_id) VALUES +(52, 12.60, 0, 3001, 11), (56, 14.58, 0, 3001, 11); + +--echo # should return 1 and the same values in col 2 and 3 +select STRAIGHT_JOIN + (1 + PAY.vat) AS mult, + SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 2)) * + CUR.rate / CUR.rate, 2) + ) v_net_with_discount, + + SUM(ROUND((SUB.price - ROUND(ROUND(SUB.price, 2) * SUB.discount, 1)) * + CUR.rate / CUR.rate , 2) + * (1 + PAY.vat) + ) v_total +from + currencies CUR, payments PAY, sub_tasks SUB +where + SUB.payment_id = PAY.id and + PAY.currency_id = CUR.id and + PAY.id > 2 +group by PAY.id + 1; + +DROP TABLE currencies, payments, sub_tasks; + + +--echo End of 5.1 tests -- cgit v1.2.1 From c70a9fa1e3c459e030546b0ca1ac916970bb489b Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Sat, 21 Nov 2009 09:18:21 -0200 Subject: Bug#41726: upgrade from 5.0 to 5.1.30 crashes if you didn't run mysql_upgrade The problem is that the server could crash when attempting to access a non-conformant proc system table. One such case was a crash when invoking stored procedure related statements on a 5.1 server with a proc system table in the 5.0 format. The solution is to validate the proc system table format before attempts to access it are made. If the table is not in the format that the server expects, a message is written to the error log and the statement that caused the table to be accessed fails. mysql-test/r/sp-destruct.result: Add test case result for Bug#41726 mysql-test/t/sp-destruct.test: Add test case for Bug#41726 sql/event_db_repository.cc: Update code to use new structures. sql/sp.cc: Describe the proc table format and use it to validate when opening a instance of the table. Add a check to insure that a error message is written to the error log only once. sql/sql_acl.cc: Remove unused variable and use new structure. sql/sql_acl.h: Export field definition. sql/table.cc: Accept the field count and definition in a single structure. sql/table.h: Combine the field count and definition in a single structure. Transform function into a class in order to support different ways of reporting a error. Add a pointer cache to TABLE_SHARE. --- mysql-test/r/sp-destruct.result | 34 +++++++++++++++++++++++--- mysql-test/t/sp-destruct.test | 54 ++++++++++++++++++++++++++++++++++++----- 2 files changed, 79 insertions(+), 9 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/sp-destruct.result b/mysql-test/r/sp-destruct.result index d7d44061b76..b6891df2420 100644 --- a/mysql-test/r/sp-destruct.result +++ b/mysql-test/r/sp-destruct.result @@ -1,3 +1,4 @@ +call mtr.add_suppression("Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted"); use test; drop procedure if exists bug14233; drop function if exists bug14233; @@ -11,11 +12,13 @@ create table t1 (id int); create trigger t1_ai after insert on t1 for each row call bug14233(); alter table mysql.proc drop type; call bug14233(); -ERROR HY000: Failed to load routine test.bug14233. The table mysql.proc is missing, corrupt, or contains bad data (internal code -5) +ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted create view v1 as select bug14233_f(); -ERROR HY000: Failed to load routine test.bug14233_f. The table mysql.proc is missing, corrupt, or contains bad data (internal code -5) +ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted insert into t1 values (0); -ERROR HY000: Failed to load routine test.bug14233. The table mysql.proc is missing, corrupt, or contains bad data (internal code -5) +ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted +show procedure status; +ERROR HY000: Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted flush table mysql.proc; call bug14233(); ERROR HY000: Incorrect information in file: './mysql/proc.frm' @@ -88,3 +91,28 @@ show procedure status where db=DATABASE(); Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation show function status where db=DATABASE(); Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +DROP TABLE IF EXISTS proc_backup; +DROP PROCEDURE IF EXISTS p1; +# Backup the proc table +RENAME TABLE mysql.proc TO proc_backup; +CREATE TABLE mysql.proc LIKE proc_backup; +FLUSH TABLE mysql.proc; +# Test with a valid table. +CREATE PROCEDURE p1() +SET @foo = 10; +CALL p1(); +SHOW PROCEDURE STATUS; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test p1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +# Modify a field of the table. +ALTER TABLE mysql.proc MODIFY comment CHAR (32); +CREATE PROCEDURE p2() +SET @foo = 10; +ERROR HY000: Cannot load from mysql.proc. The table is probably corrupted +# Procedure loaded from the cache +CALL p1(); +SHOW PROCEDURE STATUS; +ERROR HY000: Cannot load from mysql.proc. The table is probably corrupted +DROP TABLE mysql.proc; +RENAME TABLE proc_backup TO mysql.proc; +FLUSH TABLE mysql.proc; diff --git a/mysql-test/t/sp-destruct.test b/mysql-test/t/sp-destruct.test index 14c38a2fdb4..720c24b2c24 100644 --- a/mysql-test/t/sp-destruct.test +++ b/mysql-test/t/sp-destruct.test @@ -12,6 +12,9 @@ # mysqltest should be fixed to allow REPLACE_RESULT in error message -- source include/not_embedded.inc +# Supress warnings written to the log file +call mtr.add_suppression("Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted"); + # Backup proc table let $MYSQLD_DATADIR= `select @@datadir`; --copy_file $MYSQLD_DATADIR/mysql/proc.frm $MYSQLTEST_VARDIR/tmp/proc.frm @@ -38,15 +41,14 @@ create trigger t1_ai after insert on t1 for each row call bug14233(); # Unsupported tampering with the mysql.proc definition alter table mysql.proc drop type; ---replace_result $MYSQL_TEST_DIR . ---error ER_SP_PROC_TABLE_CORRUPT +--error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED call bug14233(); ---replace_result $MYSQL_TEST_DIR . ---error ER_SP_PROC_TABLE_CORRUPT +--error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED create view v1 as select bug14233_f(); ---replace_result $MYSQL_TEST_DIR . ---error ER_SP_PROC_TABLE_CORRUPT +--error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED insert into t1 values (0); +--error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED +show procedure status; flush table mysql.proc; @@ -155,3 +157,43 @@ drop procedure bug14233_3; # Assert: These should show nothing. show procedure status where db=DATABASE(); show function status where db=DATABASE(); + +# +# Bug#41726 upgrade from 5.0 to 5.1.30 crashes if you didn't run mysql_upgrade +# + + +--disable_warnings +DROP TABLE IF EXISTS proc_backup; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +--echo # Backup the proc table + +RENAME TABLE mysql.proc TO proc_backup; +CREATE TABLE mysql.proc LIKE proc_backup; +FLUSH TABLE mysql.proc; + +--echo # Test with a valid table. + +CREATE PROCEDURE p1() + SET @foo = 10; +CALL p1(); +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +SHOW PROCEDURE STATUS; + +--echo # Modify a field of the table. + +ALTER TABLE mysql.proc MODIFY comment CHAR (32); + +--error ER_CANNOT_LOAD_FROM_TABLE +CREATE PROCEDURE p2() + SET @foo = 10; +--echo # Procedure loaded from the cache +CALL p1(); +--error ER_CANNOT_LOAD_FROM_TABLE +SHOW PROCEDURE STATUS; + +DROP TABLE mysql.proc; +RENAME TABLE proc_backup TO mysql.proc; +FLUSH TABLE mysql.proc; -- cgit v1.2.1 From a2ed682967bdc29d249d0f607f4eea4d84a81761 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 22 Nov 2009 13:10:33 +0800 Subject: Bug #48350 truncate temporary table crashes replication In RBR, All statements operating on temporary tables should not be binlogged. Despite this fact, after executing 'TRUNCATE... ' on a temporary table, the command is still logged, even if in row-based mode. Consequently, this raises problems in the slave as the table may not exist, resulting in an execution failure. Ultimately, this causes the slave to report an error and abort. After this patch, 'TRUNCATE ...' statement on a temporary table will not be binlogged in RBR. --- mysql-test/suite/rpl/r/rpl_row_trunc_temp.result | 29 ++++++++++++++++++++ mysql-test/suite/rpl/t/rpl_row_trunc_temp.test | 35 ++++++++++++++++++++++++ 2 files changed, 64 insertions(+) create mode 100644 mysql-test/suite/rpl/r/rpl_row_trunc_temp.result create mode 100644 mysql-test/suite/rpl/t/rpl_row_trunc_temp.test (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/r/rpl_row_trunc_temp.result b/mysql-test/suite/rpl/r/rpl_row_trunc_temp.result new file mode 100644 index 00000000000..f9181be9bc0 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_trunc_temp.result @@ -0,0 +1,29 @@ +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 TEMPORARY TABLE t1(c1 INTEGER); +CREATE TABLE t2(c1 INTEGER); +CREATE TABLE t1(c1 INTEGER); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2 VALUES(1), (2); +SELECT * FROM t1; +c1 +1 +2 +SELECT * FROM t2; +c1 +1 +2 +TRUNCATE t1; +TRUNCATE t2; +SELECT * FROM t1; +c1 +1 +2 +SELECT * FROM t2; +c1 +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/suite/rpl/t/rpl_row_trunc_temp.test b/mysql-test/suite/rpl/t/rpl_row_trunc_temp.test new file mode 100644 index 00000000000..60e1cd73200 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_trunc_temp.test @@ -0,0 +1,35 @@ +# +# Bug#48350 truncate temporary table crashes replication +# +# All statements operating on temporary tables should not be binlogged in RBR. +# However, before fix of bug#48350, 'TRUNCATE ...' statement on a temporary +# table was binlogged in RBR. +# + +--source include/master-slave.inc +--source include/have_binlog_format_row.inc + +#This statement is not binlogged in RBR. +CREATE TEMPORARY TABLE t1(c1 INTEGER); +CREATE TABLE t2(c1 INTEGER); +sync_slave_with_master; + +CREATE TABLE t1(c1 INTEGER); +INSERT INTO t1 VALUES(1), (2); +INSERT INTO t2 VALUES(1), (2); +SELECT * FROM t1; +SELECT * FROM t2; + +connection master; +TRUNCATE t1; +TRUNCATE t2; +sync_slave_with_master; +# t1 will have nothing, if 'TRUNCATE t1' has been replicate from master to +# slave. +SELECT * FROM t1; +SELECT * FROM t2; + +DROP TABLE t1; +connection master; +DROP TABLE t2; +--source include/master-slave-end.inc -- cgit v1.2.1 From 84c5abbd33856ba730b1e9091146d986f1923bfa Mon Sep 17 00:00:00 2001 From: Jim Winstead Date: Mon, 23 Nov 2009 14:38:08 -0800 Subject: Backport fix for Bug #27884. --- mysql-test/r/mysql.result | 1 + mysql-test/t/mysql.test | 6 ++++++ 2 files changed, 7 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 4affd884753..1945f893f04 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -199,4 +199,5 @@ COUNT (*) COUNT (*) 1 ERROR 2005 (HY000) at line 1: Unknown MySQL server host 'invalid_hostname' (errno) +
<
< & >
End of 5.0 tests diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index b2d7400d156..0cdb0ddb653 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -356,4 +356,10 @@ remove_file $MYSQLTEST_VARDIR/tmp/bug31060.sql; --exec $MYSQL --default-character-set=binary test -e "CONNECT test invalid_hostname" 2>&1 --exec $MYSQL --default-character-set=binary test -e "DELIMITER //" 2>&1 +# +# Bug #27884: mysql --html does not quote HTML special characters in output +# +--exec $MYSQL --html test -e "select '< & >' as \`<\`" +--echo + --echo End of 5.0 tests -- cgit v1.2.1 From 82b115ec1619b7cf6dc1f308d875e154530b16a3 Mon Sep 17 00:00:00 2001 From: Satya B Date: Wed, 25 Nov 2009 12:25:49 +0530 Subject: Fix for BUG#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 mysql client displays wrong character-set of server. When a user changes the charset of a server, mysql client 'status' command displays wrong charset but the command "SHOW VARIABLES LIKE "%charset%" displayed correct charset results. The problem is only with the mysql client's 'status' command output. In mysql client, the method mysql_store_lazy_result() returns 0 for success and non-zero for failure. The method com_status() was using this method wrongly. Fixed all such instances according to return value of the method mysql_store_lazy_result(). client/mysql.cc: Fix for BUG#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 Fix com_status() method to use mysql_store_lazy_result() properly. mysql-test/r/bug47671.result: Fix for BUG#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 Testcase for BUG#47671 mysql-test/t/bug47671-master.opt: Fix for BUG#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 Testcase for BUG#47671 mysql-test/t/bug47671.test: Fix for BUG#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 Testcase for BUG#47671 --- mysql-test/r/bug47671.result | 13 +++++++++++++ mysql-test/t/bug47671-master.opt | 1 + mysql-test/t/bug47671.test | 6 ++++++ 3 files changed, 20 insertions(+) create mode 100644 mysql-test/r/bug47671.result create mode 100644 mysql-test/t/bug47671-master.opt create mode 100644 mysql-test/t/bug47671.test (limited to 'mysql-test') diff --git a/mysql-test/r/bug47671.result b/mysql-test/r/bug47671.result new file mode 100644 index 00000000000..2cff6f1b59c --- /dev/null +++ b/mysql-test/r/bug47671.result @@ -0,0 +1,13 @@ +# +# Bug#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 +# +# Extract only charset information from 'status' command output using regex +-------------- + +Server characterset: utf8 +Db characterset: utf8 +Client characterset: utf8 +Conn. characterset: utf8 + +-------------- + diff --git a/mysql-test/t/bug47671-master.opt b/mysql-test/t/bug47671-master.opt new file mode 100644 index 00000000000..0afdf49e022 --- /dev/null +++ b/mysql-test/t/bug47671-master.opt @@ -0,0 +1 @@ +--default-character-set=utf8 --skip-character-set-client-handshake diff --git a/mysql-test/t/bug47671.test b/mysql-test/t/bug47671.test new file mode 100644 index 00000000000..3efff39ff58 --- /dev/null +++ b/mysql-test/t/bug47671.test @@ -0,0 +1,6 @@ +--echo # +--echo # Bug#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 +--echo # +--echo # Extract only charset information from 'status' command output using regex +--replace_regex /.*mysql.*// /Connection.*// /Current.*// /SSL.*// /Using.*// /Server version.*// /Protocol.*// /UNIX.*// /Uptime.*// /Threads.*// +--exec $MYSQL -u root test -e "status"; -- cgit v1.2.1 From 2bf2e8f9dd619ba7ecb635a0eb51a7a4f9db977b Mon Sep 17 00:00:00 2001 From: Satya B Date: Wed, 25 Nov 2009 15:08:52 +0530 Subject: Applying InnoDB snapshot 5.0-ss6230, Part 1. Fixes BUG#47777 BUG#47777 - innodb dies with spatial pk: Failing assertion: buf <= original_buf + buf_len Detailed revision comments: r6178 | jyang | 2009-11-17 08:52:11 +0200 (Tue, 17 Nov 2009) | 6 lines branches/5.0: Merge fix for bug #47777 from branches/5.1 -r6045 to bracnches/5.0. Treat the Geometry data same as Binary BLOB in ha_innobase::store_key_val_for_row(), since the Geometry data is stored as Binary BLOB in Innodb. --- mysql-test/r/innodb_bug47777.result | 13 +++++++++++++ mysql-test/t/innodb_bug47777.test | 24 ++++++++++++++++++++++++ 2 files changed, 37 insertions(+) create mode 100644 mysql-test/r/innodb_bug47777.result create mode 100644 mysql-test/t/innodb_bug47777.test (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_bug47777.result b/mysql-test/r/innodb_bug47777.result new file mode 100644 index 00000000000..fbba47edcfc --- /dev/null +++ b/mysql-test/r/innodb_bug47777.result @@ -0,0 +1,13 @@ +create table bug47777(c2 linestring not null, primary key (c2(1))) engine=innodb; +insert into bug47777 values (geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)')); +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); +count(*) +1 +update bug47777 set c2=GeomFromText('POINT(1 1)'); +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); +count(*) +0 +select count(*) from bug47777 where c2 = GeomFromText('POINT(1 1)'); +count(*) +1 +drop table bug47777; diff --git a/mysql-test/t/innodb_bug47777.test b/mysql-test/t/innodb_bug47777.test new file mode 100644 index 00000000000..8f2985b2cf0 --- /dev/null +++ b/mysql-test/t/innodb_bug47777.test @@ -0,0 +1,24 @@ +# This is the test for bug 47777. GEOMETRY +# data is treated as BLOB data in innodb. +# Consequently, its key value generation/storing +# should follow the process for the BLOB +# datatype as well. + +--source include/have_innodb.inc + +create table bug47777(c2 linestring not null, primary key (c2(1))) engine=innodb; + +insert into bug47777 values (geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)')); + +# Verify correct row get inserted. +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); + +# Update table bug47777 should be successful. +update bug47777 set c2=GeomFromText('POINT(1 1)'); + +# Verify the row get updated successfully. The original +# c2 value should be changed to GeomFromText('POINT(1 1)'). +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); +select count(*) from bug47777 where c2 = GeomFromText('POINT(1 1)'); + +drop table bug47777; -- cgit v1.2.1 From 97d74332c2edd81754f3771f4212fa653f8c7864 Mon Sep 17 00:00:00 2001 From: Satya B Date: Wed, 25 Nov 2009 15:29:25 +0530 Subject: Applying InnoDB snapshot 5.0-ss6230, part 2. Fixes BUG#46000 BUG#46000 - using index called GEN_CLUST_INDEX crashes server Detailed revision comments: r6180 | jyang | 2009-11-17 10:54:57 +0200 (Tue, 17 Nov 2009) | 7 lines branches/5.0: Merge/Port fix for bug #46000 from branches/5.1 -r5895 to branches/5.0. Disallow creating index with the name of "GEN_CLUST_INDEX" which is reserved for the default system primary index. Minor adjusts on table name screening format for added tests. --- mysql-test/r/innodb_bug46000.result | 13 +++++++++++++ mysql-test/t/innodb_bug46000.test | 34 ++++++++++++++++++++++++++++++++++ 2 files changed, 47 insertions(+) create mode 100644 mysql-test/r/innodb_bug46000.result create mode 100644 mysql-test/t/innodb_bug46000.test (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_bug46000.result b/mysql-test/r/innodb_bug46000.result new file mode 100644 index 00000000000..8a92fa973c0 --- /dev/null +++ b/mysql-test/r/innodb_bug46000.result @@ -0,0 +1,13 @@ +create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb; +ERROR HY000: Can't create table bug46000.frm' (errno: -1) +show warnings; +Level Code Message +Warning 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Error 1005 Can't create table bug46000.frm' (errno: -1) +create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb; +ERROR HY000: Can't create table bug46000.frm' (errno: -1) +create table bug46000(id int) engine=innodb; +create index GEN_CLUST_INDEX on bug46000(id); +ERROR HY000: Can't create table #sql-temporary' (errno: -1) +create index idx on bug46000(id); +drop table bug46000; diff --git a/mysql-test/t/innodb_bug46000.test b/mysql-test/t/innodb_bug46000.test new file mode 100644 index 00000000000..d8b4ed6a638 --- /dev/null +++ b/mysql-test/t/innodb_bug46000.test @@ -0,0 +1,34 @@ +# This is the test for bug 46000. We shall +# block any index creation with the name of +# "GEN_CLUST_INDEX", which is the reserved +# name for innodb default primary index. + +--source include/have_innodb.inc + +# This 'create table' operation should fail because of +# using the reserve name as its index name. +--replace_regex /'[^']*test\/// +--error ER_CANT_CREATE_TABLE +create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb; + +--replace_regex /'[^']*test\/// +show warnings; + +# Mixed upper/lower case of the reserved key words +--replace_regex /'[^']*test\/// +--error ER_CANT_CREATE_TABLE +create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb; + +create table bug46000(id int) engine=innodb; + +# This 'create index' operation should fail. +--replace_regex /'[^']*test\/#sql-[0-9a-f_]*.frm/#sql-temporary/ +--error ER_CANT_CREATE_TABLE +create index GEN_CLUST_INDEX on bug46000(id); + +# This 'create index' operation should succeed, no +# temp table left from last failed create index +# operation. +create index idx on bug46000(id); + +drop table bug46000; -- cgit v1.2.1 From 14f2eb124448d69922c10fb818cbdf9b57f8ff63 Mon Sep 17 00:00:00 2001 From: Martin Hansson Date: Wed, 25 Nov 2009 11:02:25 +0100 Subject: Bug#48459: valgrind errors with query using 'Range checked for each record' There was an error in an internal structure in the range optimizer (SEL_ARG). Bad design causes parts of a data structure not to be initialized when it is in a certain state. All client code must check that this state is not present before trying to access the structure's data. Fixed by - Checking the state before trying to access data (in several places, most of which not covered by test case.) - Copying the keypart id when cloning SEL_ARGs mysql-test/r/range.result: Bug#48459: Test result. mysql-test/t/range.test: Bug#48459: Test case. sql/opt_range.cc: Bug#48459: Fix + doxygenated count_key_part_usage comment. --- mysql-test/r/range.result | 33 +++++++++++++++++++++++++++++++++ mysql-test/t/range.test | 34 +++++++++++++++++++++++++++++++++- 2 files changed, 66 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index ab40eefdc82..64e00521cd2 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1604,6 +1604,39 @@ str_to_date('', '%Y-%m-%d') 0000-00-00 DROP TABLE t1, t2; # +# Bug#48459: valgrind errors with query using 'Range checked for each +# record' +# +CREATE TABLE t1 ( +a INT, +b CHAR(2), +c INT, +d INT, +KEY ( c ), +KEY ( d, a, b ( 2 ) ), +KEY ( b ( 1 ) ) +); +INSERT INTO t1 VALUES ( NULL, 'a', 1, 2 ), ( NULL, 'a', 1, 2 ), +( 1, 'a', 1, 2 ), ( 1, 'a', 1, 2 ); +CREATE TABLE t2 ( +a INT, +c INT, +e INT, +KEY ( e ) +); +INSERT INTO t2 VALUES ( 1, 1, NULL ), ( 1, 1, NULL ); +# Should not give Valgrind warnings +SELECT 1 +FROM t1, t2 +WHERE t1.d <> '1' AND t1.b > '1' +AND t1.a = t2.a AND t1.c = t2.c; +1 +1 +1 +1 +1 +DROP TABLE t1, t2; +# # Bug #48665: sql-bench's insert test fails due to wrong result # CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)); diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 736d65792c5..5d5ad180f1a 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1260,6 +1260,39 @@ SELECT str_to_date('', '%Y-%m-%d'); DROP TABLE t1, t2; +--echo # +--echo # Bug#48459: valgrind errors with query using 'Range checked for each +--echo # record' +--echo # +CREATE TABLE t1 ( + a INT, + b CHAR(2), + c INT, + d INT, + KEY ( c ), + KEY ( d, a, b ( 2 ) ), + KEY ( b ( 1 ) ) +); + +INSERT INTO t1 VALUES ( NULL, 'a', 1, 2 ), ( NULL, 'a', 1, 2 ), + ( 1, 'a', 1, 2 ), ( 1, 'a', 1, 2 ); + +CREATE TABLE t2 ( + a INT, + c INT, + e INT, + KEY ( e ) +); + +INSERT INTO t2 VALUES ( 1, 1, NULL ), ( 1, 1, NULL ); + +--echo # Should not give Valgrind warnings +SELECT 1 +FROM t1, t2 +WHERE t1.d <> '1' AND t1.b > '1' +AND t1.a = t2.a AND t1.c = t2.c; + +DROP TABLE t1, t2; --echo # --echo # Bug #48665: sql-bench's insert test fails due to wrong result @@ -1280,5 +1313,4 @@ SELECT * FROM t1 FORCE INDEX (PRIMARY) DROP TABLE t1; - --echo End of 5.1 tests -- cgit v1.2.1 From 2ac344ecf662f6b5d901825850e3b5568ab91174 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 27 Nov 2009 11:59:44 +0200 Subject: Bug #48872 : Privileges for stored functions ignored if function name is mixed case Transcode the procedure name to lowercase when searching for it in the hash. This is the missing part of the fix for bug #41049. --- mysql-test/r/sp-security.result | 61 +++++++++++++++++++++++++++++++++++++++++ mysql-test/t/sp-security.test | 57 ++++++++++++++++++++++++++++++++++++++ 2 files changed, 118 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 106d08c8c12..17758218e35 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -519,4 +519,65 @@ DROP USER mysqltest_u1@localhost; DROP PROCEDURE p_suid; DROP FUNCTION f_suid; DROP TABLE t1; +# +# Bug #48872 : Privileges for stored functions ignored if function name +# is mixed case +# +CREATE DATABASE B48872; +USE B48872; +CREATE TABLE `TestTab` (id INT); +INSERT INTO `TestTab` VALUES (1),(2); +CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123; +CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123; +CREATE USER 'tester'; +CREATE USER 'Tester'; +GRANT SELECT ON TABLE `TestTab` TO 'tester'; +GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester'; +GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester'; +SELECT f_Test(); +f_Test() +123 +SELECT * FROM TestTab; +id +1 +2 +SELECT * FROM TestTab; +id +1 +2 +SELECT `f_Test`(); +`f_Test`() +123 +SELECT `F_TEST`(); +`F_TEST`() +123 +SELECT f_Test(); +f_Test() +123 +SELECT F_TEST(); +F_TEST() +123 +SELECT * FROM TestTab; +ERROR 42000: SELECT command denied to user 'Tester'@'localhost' for table 'TestTab' +SELECT `f_Test`(); +ERROR 42000: execute command denied to user 'Tester'@'%' for routine 'B48872.f_Test' +SELECT `F_TEST`(); +ERROR 42000: execute command denied to user 'Tester'@'%' for routine 'B48872.f_Test' +SELECT f_Test(); +ERROR 42000: execute command denied to user 'Tester'@'%' for routine 'B48872.f_Test' +SELECT F_TEST(); +ERROR 42000: execute command denied to user 'Tester'@'%' for routine 'B48872.f_Test' +SELECT `f_Test_denied`(); +`f_Test_denied`() +123 +SELECT `F_TEST_DENIED`(); +`F_TEST_DENIED`() +123 +DROP TABLE `TestTab`; +DROP FUNCTION `f_Test`; +DROP FUNCTION `f_Test_denied`; +USE test; +DROP USER 'tester'; +DROP USER 'Tester'; +DROP DATABASE B48872; End of 5.0 tests. diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index b8181fcb89b..42c8ecd85ec 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -889,6 +889,63 @@ DROP PROCEDURE p_suid; DROP FUNCTION f_suid; DROP TABLE t1; +--echo # +--echo # Bug #48872 : Privileges for stored functions ignored if function name +--echo # is mixed case +--echo # + +CREATE DATABASE B48872; +USE B48872; +CREATE TABLE `TestTab` (id INT); +INSERT INTO `TestTab` VALUES (1),(2); +CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123; +CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123; +CREATE USER 'tester'; +CREATE USER 'Tester'; +GRANT SELECT ON TABLE `TestTab` TO 'tester'; +GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester'; +GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester'; + +SELECT f_Test(); +SELECT * FROM TestTab; + +CONNECT (con_tester,localhost,tester,,B48872); +CONNECT (con_tester_denied,localhost,Tester,,B48872); +CONNECTION con_tester; + +SELECT * FROM TestTab; +SELECT `f_Test`(); +SELECT `F_TEST`(); +SELECT f_Test(); +SELECT F_TEST(); + +CONNECTION con_tester_denied; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM TestTab; +--error ER_PROCACCESS_DENIED_ERROR +SELECT `f_Test`(); +--error ER_PROCACCESS_DENIED_ERROR +SELECT `F_TEST`(); +--error ER_PROCACCESS_DENIED_ERROR +SELECT f_Test(); +--error ER_PROCACCESS_DENIED_ERROR +SELECT F_TEST(); +SELECT `f_Test_denied`(); +SELECT `F_TEST_DENIED`(); + +CONNECTION default; +DISCONNECT con_tester; +DISCONNECT con_tester_denied; +DROP TABLE `TestTab`; +DROP FUNCTION `f_Test`; +DROP FUNCTION `f_Test_denied`; + +USE test; +DROP USER 'tester'; +DROP USER 'Tester'; +DROP DATABASE B48872; + --echo End of 5.0 tests. # Wait till all disconnects are completed -- cgit v1.2.1 From 95e420c636978d3788830500d46efcb2f180ce73 Mon Sep 17 00:00:00 2001 From: Alfranio Correia Date: Fri, 27 Nov 2009 13:34:39 +0000 Subject: BUG#48506 crash in CREATE TABLE IF NOT EXISTS LIKE with RBL When binlogging the statement, the server always handle the existing object as a table, even though it is a view. However a view is handled differently in other parts of the code thus leading the statement to crash in RBL if the view exists. This happens because the underlying tables for the view are not opened when we try to call store_create_info() on the view in order to build a CREATE TABLE statement. This patch will only address the crash problem, other binlogging problems related to CREATE TABLE IF NOT EXISTS LIKE when the existing object is a view will be solved by BUG 47442. --- mysql-test/suite/rpl/r/rpl_row_create_table.result | 26 ++++++++++++++++ mysql-test/suite/rpl/t/rpl_row_create_table.test | 36 ++++++++++++++++++++++ 2 files changed, 62 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/r/rpl_row_create_table.result b/mysql-test/suite/rpl/r/rpl_row_create_table.result index b7122adea2a..4c2b9ebeaee 100644 --- a/mysql-test/suite/rpl/r/rpl_row_create_table.result +++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result @@ -476,4 +476,30 @@ master-bin.000001 # Table_map # # table_id: # (mysqltest1.with_select) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT DROP DATABASE mysqltest1; +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 TEMPORARY TABLE t7(c1 INT); +CREATE TABLE t5(c1 INT); +CREATE TABLE t4(c1 INT); +CREATE VIEW bug48506_t1 AS SELECT 1; +CREATE VIEW bug48506_t2 AS SELECT * FROM t4; +CREATE VIEW bug48506_t3 AS SELECT t5.c1 AS A, t4.c1 AS B FROM t5, t4; +CREATE TABLE bug48506_t4(c1 INT); +DROP VIEW bug48506_t1, bug48506_t2, bug48506_t3; +DROP TABLE bug48506_t4; +CREATE TABLE IF NOT EXISTS bug48506_t1 LIKE t7; +CREATE TABLE IF NOT EXISTS bug48506_t2 LIKE t7; +CREATE TABLE IF NOT EXISTS bug48506_t3 LIKE t7; +CREATE TABLE IF NOT EXISTS bug48506_t4 LIKE t7; +SHOW TABLES LIKE 'bug48506%'; +Tables_in_test (bug48506%) +bug48506_t4 +DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3; +DROP TEMPORARY TABLES t7; +DROP TABLES t4, t5; +DROP TABLES IF EXISTS bug48506_t4; end of the tests diff --git a/mysql-test/suite/rpl/t/rpl_row_create_table.test b/mysql-test/suite/rpl/t/rpl_row_create_table.test index 319f9546a81..54dcbd24419 100644 --- a/mysql-test/suite/rpl/t/rpl_row_create_table.test +++ b/mysql-test/suite/rpl/t/rpl_row_create_table.test @@ -292,4 +292,40 @@ connection master; DROP DATABASE mysqltest1; sync_slave_with_master; +# +# BUG#48506: crash in CREATE TABLE IF NOT EXISTS LIKE +# with RBL +# + +source include/master-slave-reset.inc; + +connection master; +CREATE TEMPORARY TABLE t7(c1 INT); +CREATE TABLE t5(c1 INT); +CREATE TABLE t4(c1 INT); +CREATE VIEW bug48506_t1 AS SELECT 1; +CREATE VIEW bug48506_t2 AS SELECT * FROM t4; +CREATE VIEW bug48506_t3 AS SELECT t5.c1 AS A, t4.c1 AS B FROM t5, t4; +CREATE TABLE bug48506_t4(c1 INT); +--disable_warnings +sync_slave_with_master; +DROP VIEW bug48506_t1, bug48506_t2, bug48506_t3; +DROP TABLE bug48506_t4; + +connection master; +CREATE TABLE IF NOT EXISTS bug48506_t1 LIKE t7; +CREATE TABLE IF NOT EXISTS bug48506_t2 LIKE t7; +CREATE TABLE IF NOT EXISTS bug48506_t3 LIKE t7; +CREATE TABLE IF NOT EXISTS bug48506_t4 LIKE t7; +--enable_warnings +sync_slave_with_master; + +SHOW TABLES LIKE 'bug48506%'; + +connection master; +DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3; +DROP TEMPORARY TABLES t7; +DROP TABLES t4, t5; +DROP TABLES IF EXISTS bug48506_t4; +source include/master-slave-end.inc; --echo end of the tests -- cgit v1.2.1 From 514d33c866f598c506d9dcf067ce2be164167fe9 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 27 Nov 2009 15:46:54 +0200 Subject: Addendum to Bug #27884: fixed test incompatibility on windows. --- mysql-test/t/mysql.test | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 0cdb0ddb653..dcdf3eeaeb6 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -359,7 +359,10 @@ remove_file $MYSQLTEST_VARDIR/tmp/bug31060.sql; # # Bug #27884: mysql --html does not quote HTML special characters in output # ---exec $MYSQL --html test -e "select '< & >' as \`<\`" +--write_file $MYSQLTEST_VARDIR/tmp/bug27884.sql +SELECT '< & >' AS `<`; +EOF +--exec $MYSQL --html test < $MYSQLTEST_VARDIR/tmp/bug27884.sql --echo --echo End of 5.0 tests -- cgit v1.2.1 From 88893ddfe5b598321ea67892df61ff6d728bf526 Mon Sep 17 00:00:00 2001 From: Sergey Vojtovich Date: Fri, 27 Nov 2009 17:55:08 +0400 Subject: Fixes for bug47671.test: - embedded server doesn't support external clients - removed unnecessary command line options --- mysql-test/t/bug47671.test | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/t/bug47671.test b/mysql-test/t/bug47671.test index 3efff39ff58..df5b4586dc0 100644 --- a/mysql-test/t/bug47671.test +++ b/mysql-test/t/bug47671.test @@ -1,6 +1,9 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc + --echo # --echo # Bug#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 --echo # --echo # Extract only charset information from 'status' command output using regex --replace_regex /.*mysql.*// /Connection.*// /Current.*// /SSL.*// /Using.*// /Server version.*// /Protocol.*// /UNIX.*// /Uptime.*// /Threads.*// ---exec $MYSQL -u root test -e "status"; +--exec $MYSQL -e "status"; -- cgit v1.2.1 From d877f0a04575982c4a813b831b8c74b80cccf770 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 27 Nov 2009 16:36:22 +0200 Subject: added the missing remove of the temp file --- mysql-test/t/mysql.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index dcdf3eeaeb6..d213e031d18 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -364,5 +364,6 @@ SELECT '< & >' AS `<`; EOF --exec $MYSQL --html test < $MYSQLTEST_VARDIR/tmp/bug27884.sql --echo +remove_file $MYSQLTEST_VARDIR/tmp/bug27884.sql; --echo End of 5.0 tests -- cgit v1.2.1 From b7ed14638fd36d68d2fbabb861e5467d6751cbab Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Fri, 27 Nov 2009 18:07:31 +0200 Subject: Addendum to bug #48872: disable output in the test case because errors are dependent on the case mode --- mysql-test/r/sp-security.result | 5 ----- mysql-test/t/sp-security.test | 2 ++ 2 files changed, 2 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 17758218e35..1451f8e88fd 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -558,15 +558,10 @@ SELECT F_TEST(); F_TEST() 123 SELECT * FROM TestTab; -ERROR 42000: SELECT command denied to user 'Tester'@'localhost' for table 'TestTab' SELECT `f_Test`(); -ERROR 42000: execute command denied to user 'Tester'@'%' for routine 'B48872.f_Test' SELECT `F_TEST`(); -ERROR 42000: execute command denied to user 'Tester'@'%' for routine 'B48872.f_Test' SELECT f_Test(); -ERROR 42000: execute command denied to user 'Tester'@'%' for routine 'B48872.f_Test' SELECT F_TEST(); -ERROR 42000: execute command denied to user 'Tester'@'%' for routine 'B48872.f_Test' SELECT `f_Test_denied`(); `f_Test_denied`() 123 diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index 42c8ecd85ec..3d41d90404d 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -921,6 +921,7 @@ SELECT F_TEST(); CONNECTION con_tester_denied; +--disable_result_log --error ER_TABLEACCESS_DENIED_ERROR SELECT * FROM TestTab; --error ER_PROCACCESS_DENIED_ERROR @@ -931,6 +932,7 @@ SELECT `F_TEST`(); SELECT f_Test(); --error ER_PROCACCESS_DENIED_ERROR SELECT F_TEST(); +--enable_result_log SELECT `f_Test_denied`(); SELECT `F_TEST_DENIED`(); -- cgit v1.2.1 From b9f9fe2e1a8ce599140d38cfd8f7d58b5344e36e Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 28 Nov 2009 12:43:16 +0800 Subject: Bug #43913 rpl_cross_version can't pass on conflicts complainig clash with --slave-load-tm The 'slave_patternload_file' is assigned to the real path of the load data file when initializing the object of Relay_log_info. But the path of the load data file is not formatted to real path when executing event from relay log. So the error will be encountered if the path of the load data file is a symbolic link. Actually the global 'opt_secure_file_priv' is not formatted to real path when loading data from file. So the same thing will happen too. To fix these errors, the path of the load data file should be formatted to real path when executing event from relay log. And the 'opt_secure_file_priv' should be formatted to real path when loading data infile. mysql-test/suite/rpl/r/rpl_loaddata_symlink.result: Test result for bug#43913. mysql-test/suite/rpl/t/rpl_loaddata_symlink-master.sh: Added the test file to create a link from $MYSQLTEST_VARDIR/std_data to $MYSQLTEST_VARDIR/std_data_master_link mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.sh: Added the test file to create a link from $MYSQLTEST_VARDIR/std_data to $MYSQLTEST_VARDIR/std_data_slave_link mysql-test/suite/rpl/t/rpl_loaddata_symlink.test: Added the test file to verify if loading data infile will work fine if the path of the load data file is a symbolic link. sql/rpl_rli.cc: Added call 'my_realpath' function for avoiding sometimes the 'fn_format' function can't format real path rightly. --- mysql-test/suite/rpl/r/rpl_loaddata_symlink.result | 17 +++++++++++++++++ .../suite/rpl/t/rpl_loaddata_symlink-master.opt | 1 + .../suite/rpl/t/rpl_loaddata_symlink-master.sh | 1 + .../suite/rpl/t/rpl_loaddata_symlink-slave.opt | 1 + mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.sh | 1 + mysql-test/suite/rpl/t/rpl_loaddata_symlink.test | 20 ++++++++++++++++++++ 6 files changed, 41 insertions(+) create mode 100644 mysql-test/suite/rpl/r/rpl_loaddata_symlink.result create mode 100644 mysql-test/suite/rpl/t/rpl_loaddata_symlink-master.opt create mode 100644 mysql-test/suite/rpl/t/rpl_loaddata_symlink-master.sh create mode 100644 mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.opt create mode 100644 mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.sh create mode 100644 mysql-test/suite/rpl/t/rpl_loaddata_symlink.test (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/r/rpl_loaddata_symlink.result b/mysql-test/suite/rpl/r/rpl_loaddata_symlink.result new file mode 100644 index 00000000000..c7806f5ecce --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_loaddata_symlink.result @@ -0,0 +1,17 @@ +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 not null auto_increment, b int, primary key(a) ); +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +select * from t1; +a b +1 10 +2 15 +select * from t1; +a b +1 10 +2 15 +drop table t1; diff --git a/mysql-test/suite/rpl/t/rpl_loaddata_symlink-master.opt b/mysql-test/suite/rpl/t/rpl_loaddata_symlink-master.opt new file mode 100644 index 00000000000..719832a2862 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_loaddata_symlink-master.opt @@ -0,0 +1 @@ +--secure-file-priv=$MYSQLTEST_VARDIR/std_data_master_link diff --git a/mysql-test/suite/rpl/t/rpl_loaddata_symlink-master.sh b/mysql-test/suite/rpl/t/rpl_loaddata_symlink-master.sh new file mode 100644 index 00000000000..066f72926af --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_loaddata_symlink-master.sh @@ -0,0 +1 @@ +ln -s $MYSQLTEST_VARDIR/std_data $MYSQLTEST_VARDIR/std_data_master_link diff --git a/mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.opt b/mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.opt new file mode 100644 index 00000000000..a112e81a714 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.opt @@ -0,0 +1 @@ +--slave-load-tmpdir=$MYSQLTEST_VARDIR/std_data_slave_link diff --git a/mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.sh b/mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.sh new file mode 100644 index 00000000000..218209a2542 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_loaddata_symlink-slave.sh @@ -0,0 +1 @@ +ln -s $MYSQLTEST_VARDIR/std_data $MYSQLTEST_VARDIR/std_data_slave_link diff --git a/mysql-test/suite/rpl/t/rpl_loaddata_symlink.test b/mysql-test/suite/rpl/t/rpl_loaddata_symlink.test new file mode 100644 index 00000000000..d3ee2766314 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_loaddata_symlink.test @@ -0,0 +1,20 @@ +# +# BUG#43913 +# This test verifies if loading data infile will work fine +# if the path of the load data file is a symbolic link. +# +--source include/master-slave.inc +--source include/have_binlog_format_statement.inc + +create table t1(a int not null auto_increment, b int, primary key(a) ); +load data infile '../../std_data/rpl_loaddata.dat' into table t1; +select * from t1; + +sync_slave_with_master; +connection slave; +select * from t1; + +connection master; +drop table t1; +sync_slave_with_master; + -- cgit v1.2.1 From 181a29f20ac077aca69bee493eab946c6fb444f2 Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 10:54:26 +0530 Subject: Additional Fix for BUG#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 Fix for failure on windows PB2 because of the result content mismatch. In windows additional information about TCP port was causing failures. Added to regex. --- mysql-test/t/bug47671.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/t/bug47671.test b/mysql-test/t/bug47671.test index df5b4586dc0..c3f66a9f502 100644 --- a/mysql-test/t/bug47671.test +++ b/mysql-test/t/bug47671.test @@ -5,5 +5,5 @@ --echo # Bug#47671 - wrong character-set after upgrade from 5.1.34 to 5.1.39 --echo # --echo # Extract only charset information from 'status' command output using regex ---replace_regex /.*mysql.*// /Connection.*// /Current.*// /SSL.*// /Using.*// /Server version.*// /Protocol.*// /UNIX.*// /Uptime.*// /Threads.*// +--replace_regex /.*mysql.*// /Connection.*// /Current.*// /SSL.*// /Using.*// /Server version.*// /Protocol.*// /UNIX.*// /Uptime.*// /Threads.*// /TCP.*// --exec $MYSQL -e "status"; -- cgit v1.2.1 From 2faa75ed26bbd0644775907e9a959a17ad4e4ff6 Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 14:38:41 +0530 Subject: Applying InnoDB snapshot 5.1-ss6242, part 6. Fixes BUG#48482 1. BUG#48482 - innodb-autoinc.test fails with results difference 2. enable the disabled test Detailed revision comments: r6230 | sunny | 2009-11-24 23:52:43 +0200 (Tue, 24 Nov 2009) | 3 lines branches/5.1: Fix autoinc failing test results. (this should be skipped when merging 5.1 into zip) --- mysql-test/r/innodb-autoinc.result | 8 ++++---- mysql-test/t/disabled.def | 1 - 2 files changed, 4 insertions(+), 5 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index a40a13dbe9f..abb8f3da072 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -875,11 +875,11 @@ ALTER TABLE t1 CHANGE c1 d1 INT NOT NULL AUTO_INCREMENT; SELECT * FROM t1; d1 1 -3 +2 SELECT * FROM t1; d1 1 -3 +2 INSERT INTO t1 VALUES(null); Got one of the listed errors ALTER TABLE t1 AUTO_INCREMENT = 3; @@ -888,13 +888,13 @@ Table Create Table t1 CREATE TABLE `t1` ( `d1` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`d1`) -) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES(null); SELECT * FROM t1; d1 1 +2 3 -4 DROP TABLE t1; SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; SHOW VARIABLES LIKE "%auto_inc%"; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index ad7617b9403..ac89c1493cd 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -13,5 +13,4 @@ kill : Bug#37780 2008-12-03 HHunger need some changes to be query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically partition_innodb_builtin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes partition_innodb_plugin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes -innodb-autoinc : Bug#48482 2009-11-02 svoj innodb-autoinc.test fails with results difference rpl_killed_ddl : Bug#45520: rpl_killed_ddl fails sporadically in pb2 -- cgit v1.2.1 From 4b3114b60d10e4cedb0c98fe06f7e2ff023c3dbd Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 15:11:38 +0530 Subject: Applying InnoDB snapshot 5.1-ss6242, part 7. Fixes BUG#49032 1. BUG#49032 - auto_increment field does not initialize to last value in InnoDB Storage Engine 2. Fix whitespace issues and fix tests and make read float/double arg const Detailed revision comments: r6231 | sunny | 2009-11-25 10:26:27 +0200 (Wed, 25 Nov 2009) | 7 lines branches/5.1: Fix BUG#49032 - auto_increment field does not initialize to last value in InnoDB Storage Engine. We use the appropriate function to read the column value for non-integer autoinc column types, namely float and double. rb://208. Approved by Marko. r6232 | sunny | 2009-11-25 10:27:39 +0200 (Wed, 25 Nov 2009) | 2 lines branches/5.1: This is an interim fix, fix white space errors. r6233 | sunny | 2009-11-25 10:28:35 +0200 (Wed, 25 Nov 2009) | 2 lines branches/5.1: This is an interim fix, fix tests and make read float/double arg const. r6234 | sunny | 2009-11-25 10:29:03 +0200 (Wed, 25 Nov 2009) | 2 lines branches/5.1: This is an interim fix, fix whitepsace issues. --- mysql-test/r/innodb-autoinc.result | 25 +++++++++++++++++++++++++ mysql-test/t/innodb-autoinc.test | 19 +++++++++++++++++++ 2 files changed, 44 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index abb8f3da072..ea759ec328c 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -1126,3 +1126,28 @@ SELECT * FROM T1; c1 c2 10 0 DROP TABLE T1; +DROP TABLE IF EXISTS T1; +Warnings: +Note 1051 Unknown table 'T1' +CREATE TABLE T1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO T1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); +INSERT INTO T1(C2) VALUES ('innodb'); +SHOW CREATE TABLE T1; +Table Create Table +T1 CREATE TABLE `T1` ( + `C1` double NOT NULL AUTO_INCREMENT, + `C2` char(10) DEFAULT NULL, + PRIMARY KEY (`C1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +DROP TABLE T1; +CREATE TABLE T1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO T1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); +INSERT INTO T1(C2) VALUES ('innodb'); +SHOW CREATE TABLE T1; +Table Create Table +T1 CREATE TABLE `T1` ( + `C1` float NOT NULL AUTO_INCREMENT, + `C2` char(10) DEFAULT NULL, + PRIMARY KEY (`C1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +DROP TABLE T1; diff --git a/mysql-test/t/innodb-autoinc.test b/mysql-test/t/innodb-autoinc.test index 3f45bb9d003..468f345724f 100644 --- a/mysql-test/t/innodb-autoinc.test +++ b/mysql-test/t/innodb-autoinc.test @@ -620,3 +620,22 @@ SHOW CREATE TABLE T1; INSERT INTO T1 (c2) values (0); SELECT * FROM T1; DROP TABLE T1; + +## +# 49032: Use the correct function to read the AUTOINC column value +# +DROP TABLE IF EXISTS T1; +CREATE TABLE T1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO T1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); +# Restart the server +-- source include/restart_mysqld.inc +INSERT INTO T1(C2) VALUES ('innodb'); +SHOW CREATE TABLE T1; +DROP TABLE T1; +CREATE TABLE T1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO T1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); +# Restart the server +-- source include/restart_mysqld.inc +INSERT INTO T1(C2) VALUES ('innodb'); +SHOW CREATE TABLE T1; +DROP TABLE T1; -- cgit v1.2.1 From cde05f0645cb3912bf7739c1bc8e0c8595b8a6b9 Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 15:16:45 +0530 Subject: Applying InnoDB snapshot 5.1-ss6242, part 8. Fixes BUG#47720 1. BUG#47720 - REPLACE INTO Autoincrement column with negative values. Detailed revision comments: r6235 | sunny | 2009-11-26 01:14:42 +0200 (Thu, 26 Nov 2009) | 9 lines branches/5.1: Fix Bug#47720 - REPLACE INTO Autoincrement column with negative values. This bug is similiar to the negative autoinc filter patch from earlier, with the additional handling of filtering out the negative column values set explicitly by the user. rb://184 Approved by Heikki. --- mysql-test/r/innodb-autoinc.result | 39 ++++++++++++++++++++++++++++++++++++++ mysql-test/t/innodb-autoinc.test | 18 ++++++++++++++++++ 2 files changed, 57 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index ea759ec328c..2e0d2c1d776 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -1151,3 +1151,42 @@ T1 CREATE TABLE `T1` ( PRIMARY KEY (`C1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 DROP TABLE T1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 SET c1 = 1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 +INSERT INTO t1 SET c1 = 2; +INSERT INTO t1 SET c1 = -1; +SELECT * FROM t1; +c1 +-1 +1 +2 +INSERT INTO t1 SET c1 = -1; +Got one of the listed errors +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +REPLACE INTO t1 VALUES (-1); +SELECT * FROM t1; +c1 +-1 +1 +2 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/t/innodb-autoinc.test b/mysql-test/t/innodb-autoinc.test index 468f345724f..4fa5ab022ad 100644 --- a/mysql-test/t/innodb-autoinc.test +++ b/mysql-test/t/innodb-autoinc.test @@ -639,3 +639,21 @@ INSERT INTO T1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); INSERT INTO T1(C2) VALUES ('innodb'); SHOW CREATE TABLE T1; DROP TABLE T1; + +## +# 47720: REPLACE INTO Autoincrement column with negative values +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 SET c1 = 1; +SHOW CREATE TABLE t1; +INSERT INTO t1 SET c1 = 2; +INSERT INTO t1 SET c1 = -1; +SELECT * FROM t1; +-- error ER_DUP_ENTRY,1062 +INSERT INTO t1 SET c1 = -1; +SHOW CREATE TABLE t1; +REPLACE INTO t1 VALUES (-1); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; -- cgit v1.2.1 From cefd968dddcbd333fad186daeebeb17ef907d154 Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 16:24:20 +0530 Subject: Applying Innodb Snapshot 5.1-ss6242, part 9. 1. Enabling the disabled test for BUG#32430 for builtin innodb 2. Disabling the innodb-autoinc.test for innodb plugin. --- mysql-test/lib/mtr_cases.pm | 2 ++ mysql-test/t/disabled.def | 1 - 2 files changed, 2 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/lib/mtr_cases.pm b/mysql-test/lib/mtr_cases.pm index bf73b45ecdf..1f018f27f7b 100644 --- a/mysql-test/lib/mtr_cases.pm +++ b/mysql-test/lib/mtr_cases.pm @@ -520,6 +520,8 @@ sub collect_one_suite($) next if ($test->{'name'} eq 'sys_vars.innodb_thread_concurrency_basic'); # Can't work with InnoPlug. Test framework needs to be re-designed. next if ($test->{'name'} eq 'main.innodb_bug46000'); + # Fails with innodb plugin + next if ($test->{'name'} eq 'main.innodb-autoinc'); # Copy test options my $new_test= My::Test->new(); while (my ($key, $value) = each(%$test)) diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index ac89c1493cd..c136a2d5162 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -11,6 +11,5 @@ ############################################################################## kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically -partition_innodb_builtin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes partition_innodb_plugin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes rpl_killed_ddl : Bug#45520: rpl_killed_ddl fails sporadically in pb2 -- cgit v1.2.1 From 732e5a263444178370ab0b4c6410e138d09218e7 Mon Sep 17 00:00:00 2001 From: Magne Mahre Date: Mon, 30 Nov 2009 12:30:28 +0100 Subject: Bug #20837 Apparent change of isolation level during transaction SET TRANSACTION ISOLATION LEVEL is used to temporarily set the trans.iso.level for the next transaction. After the transaction, the iso.level is (re-)set to value of the session variable 'tx_isolation'. The bug is caused by setting the thd->variables.tx_isolation field to the value of the session variable upon each statement commit. It should only be set at the end of the full transaction. The fix has been to remove the setting of the variable in ha_autocommit_or_rollback if we're in a transaction, as it will be correctly set in either ha_rollback or ha_commit_one_phase. If, on the other hand, we're in autocommit mode, tx_isolation will be explicitly set here. mysql-test/t/innodb_mysql.test: "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" sets the trans.isolation for the next transaction. We test that @@tx_isolation keeps this value during the transaction, and is only reset back to the session value at the end of the transaction. --- mysql-test/r/innodb_mysql.result | 31 +++++++++++++++++++++++++++++++ mysql-test/t/innodb_mysql.test | 26 ++++++++++++++++++++++++++ 2 files changed, 57 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 402ab3c1b16..2d93cbb47b3 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -2273,4 +2273,35 @@ END| DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1,t2; +# +# Bug #20837 Apparent change of isolation +# level during transaction +# +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +CREATE TABLE t1 (s1 INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2); +COMMIT; +SET @@autocommit = 0; +COMMIT; +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +START TRANSACTION; +SELECT * FROM t1; +s1 +1 +2 +Should be READ UNCOMMITTED +SELECT @@tx_isolation; +@@tx_isolation +READ-UNCOMMITTED +INSERT INTO t1 VALUES (-1); +Should be READ UNCOMMITTED +SELECT @@tx_isolation; +@@tx_isolation +READ-UNCOMMITTED +COMMIT; +Should now be REPEATABLE READ +SELECT @@tx_isolation; +@@tx_isolation +REPEATABLE-READ +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index a2a1113598d..0462536371e 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -536,4 +536,30 @@ DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1,t2; + +--echo # +--echo # Bug #20837 Apparent change of isolation +--echo # level during transaction +--echo # + +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +CREATE TABLE t1 (s1 INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2); +COMMIT; + +SET @@autocommit = 0; +COMMIT; +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +START TRANSACTION; +SELECT * FROM t1; +--echo Should be READ UNCOMMITTED +SELECT @@tx_isolation; +INSERT INTO t1 VALUES (-1); +--echo Should be READ UNCOMMITTED +SELECT @@tx_isolation; +COMMIT; +--echo Should now be REPEATABLE READ +SELECT @@tx_isolation; +DROP TABLE t1; + --echo End of 5.1 tests -- cgit v1.2.1 From 54e01f3ce12e33db9a77e4ff98d5ee33550af57d Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 17:12:51 +0530 Subject: Applying InnoDB Plugin 1.0.6 snapshot, part 2. Fixes BUG#32430 Enable partition_innodb_plugin.test for BUG#32430 applied revisions: r6134, r6137, r6153 Detailed revision comments: r6134 | marko | 2009-11-04 09:57:29 +0200 (Wed, 04 Nov 2009) | 5 lines branches/zip: innobase_convert_identifier(): Convert table names with explain_filename() to address Bug #32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs. rb://134 approved by Sunny Bains r6137 | marko | 2009-11-04 15:24:28 +0200 (Wed, 04 Nov 2009) | 1 line branches/zip: dict_index_too_big_for_undo(): Correct a typo. r6153 | vasil | 2009-11-10 15:33:22 +0200 (Tue, 10 Nov 2009) | 145 lines branches/zip: Merge r6125:6152 from branches/5.1: (everything except the last white-space change was skipped as it is already in branches/zip) ------------------------------------------------------------------------ r6127 | vasil | 2009-10-30 11:18:25 +0200 (Fri, 30 Oct 2009) | 18 lines Changed paths: M /branches/5.1/Makefile.am M /branches/5.1/mysql-test/innodb-autoinc.result M /branches/5.1/mysql-test/innodb-autoinc.test branches/5.1: Backport c6121 from branches/zip: ------------------------------------------------------------------------ r6121 | sunny | 2009-10-30 01:42:11 +0200 (Fri, 30 Oct 2009) | 7 lines Changed paths: M /branches/zip/mysql-test/innodb-autoinc.result branches/zip: This test has been problematic for sometime now. The underlying bug is that the data dictionaries get out of sync. In the AUTOINC code we try and apply salve to the symptoms. In the past MySQL made some unrelated change and the dictionaries stopped getting out of sync and this test started to fail. Now, it seems they have reverted that changed and the test is passing again. I suspect this is not he last time that this test will change. ------------------------------------------------------------------------ ------------------------------------------------------------------------ r6129 | vasil | 2009-10-30 17:14:22 +0200 (Fri, 30 Oct 2009) | 4 lines Changed paths: M /branches/5.1/Makefile.am branches/5.1: Revert a change to Makefile.am that sneaked unnoticed in c6127. ------------------------------------------------------------------------ r6136 | marko | 2009-11-04 12:28:10 +0200 (Wed, 04 Nov 2009) | 15 lines Changed paths: M /branches/5.1/handler/ha_innodb.cc M /branches/5.1/include/ha_prototypes.h M /branches/5.1/ut/ut0ut.c branches/5.1: Port r6134 from branches/zip: ------------------------------------------------------------------------ r6134 | marko | 2009-11-04 07:57:29 +0000 (Wed, 04 Nov 2009) | 5 lines branches/zip: innobase_convert_identifier(): Convert table names with explain_filename() to address Bug #32430: 'show innodb status' causes errors Invalid (old?) table or database name in logs. rb://134 approved by Sunny Bains ------------------------------------------------------------------------ innobase_print_identifier(): Replace with innobase_convert_name(). innobase_convert_identifier(): New function, called by innobase_convert_name(). ------------------------------------------------------------------------ r6149 | vasil | 2009-11-09 11:15:01 +0200 (Mon, 09 Nov 2009) | 5 lines Changed paths: M /branches/5.1/CMakeLists.txt branches/5.1: Followup to r5700: Adjust the changes so they are the same as in the BZR repository. ------------------------------------------------------------------------ r6150 | vasil | 2009-11-09 11:43:31 +0200 (Mon, 09 Nov 2009) | 58 lines Changed paths: M /branches/5.1/handler/ha_innodb.cc branches/5.1: Merge a part of r2911.5.5 from MySQL: (the other part of this was merged in c5700) ------------------------------------------------------------ revno: 2911.5.5 committer: Vladislav Vaintroub branch nick: 5.1-innodb_plugin timestamp: Wed 2009-06-10 10:59:49 +0200 message: Backport WL#3653 to 5.1 to enable bundled innodb plugin. Remove custom DLL loader code from innodb plugin code, use symbols exported from mysqld. removed: storage/innodb_plugin/handler/handler0vars.h storage/innodb_plugin/handler/win_delay_loader.cc added: storage/mysql_storage_engine.cmake win/create_def_file.js modified: CMakeLists.txt include/m_ctype.h include/my_global.h include/my_sys.h include/mysql/plugin.h libmysqld/CMakeLists.txt mysql-test/mysql-test-run.pl mysql-test/t/plugin.test mysql-test/t/plugin_load-master.opt mysys/charset.c sql/CMakeLists.txt sql/handler.h sql/mysql_priv.h sql/mysqld.cc sql/sql_class.cc sql/sql_class.h sql/sql_list.h sql/sql_profile.h storage/Makefile.am storage/archive/CMakeLists.txt storage/blackhole/CMakeLists.txt storage/csv/CMakeLists.txt storage/example/CMakeLists.txt storage/federated/CMakeLists.txt storage/heap/CMakeLists.txt storage/innobase/CMakeLists.txt storage/innobase/handler/ha_innodb.cc storage/innodb_plugin/CMakeLists.txt storage/innodb_plugin/handler/ha_innodb.cc storage/innodb_plugin/handler/handler0alter.cc storage/innodb_plugin/handler/i_s.cc storage/innodb_plugin/plug.in storage/myisam/CMakeLists.txt storage/myisammrg/CMakeLists.txt win/Makefile.am win/configure.js ------------------------------------------------------------------------ r6152 | vasil | 2009-11-10 15:30:20 +0200 (Tue, 10 Nov 2009) | 4 lines Changed paths: M /branches/5.1/handler/ha_innodb.cc branches/5.1: White space fixup. ------------------------------------------------------------------------ --- mysql-test/t/disabled.def | 1 - 1 file changed, 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index c136a2d5162..06d3dbb4e57 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -11,5 +11,4 @@ ############################################################################## kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically -partition_innodb_plugin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes rpl_killed_ddl : Bug#45520: rpl_killed_ddl fails sporadically in pb2 -- cgit v1.2.1 From e50e69409b2e6bdbe0cde89d6ab464446d48e320 Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 17:26:21 +0530 Subject: Applying InnoDB Plugin 1.0.6 snapshot, part 3. Fixes BUG#47167 applied revisions: r6157 Detailed revision comments: r6157 | jyang | 2009-11-11 14:27:09 +0200 (Wed, 11 Nov 2009) | 10 lines branches/zip: Fix an issue that a local variable defined in innodb_file_format_check_validate() is being referenced across function in innodb_file_format_check_update(). In addition, fix "set global innodb_file_format_check = DEFAULT" call. Bug #47167: "set global innodb_file_format_check" cannot set value by User-Defined Variable." rb://169 approved by Sunny Bains and Marko. --- mysql-test/suite/innodb/r/innodb_bug47167.result | 24 +++++++++++ .../suite/innodb/r/innodb_file_format.result | 2 - mysql-test/suite/innodb/t/innodb_bug47167.test | 46 ++++++++++++++++++++++ 3 files changed, 70 insertions(+), 2 deletions(-) create mode 100644 mysql-test/suite/innodb/r/innodb_bug47167.result create mode 100644 mysql-test/suite/innodb/t/innodb_bug47167.test (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/innodb_bug47167.result b/mysql-test/suite/innodb/r/innodb_bug47167.result new file mode 100644 index 00000000000..cbec363d78f --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug47167.result @@ -0,0 +1,24 @@ +set @old_innodb_file_format_check=@@innodb_file_format_check; +select @old_innodb_file_format_check; +@old_innodb_file_format_check +Antelope +set global innodb_file_format_check = Barracuda; +select @@innodb_file_format_check; +@@innodb_file_format_check +Barracuda +set global innodb_file_format_check = DEFAULT; +select @@innodb_file_format_check; +@@innodb_file_format_check +Barracuda +set global innodb_file_format_check = @old_innodb_file_format_check; +select @@innodb_file_format_check; +@@innodb_file_format_check +Antelope +set global innodb_file_format_check = cheetah; +ERROR HY000: Incorrect arguments to SET +set global innodb_file_format_check = Bear; +ERROR HY000: Incorrect arguments to SET +set global innodb_file_format_check = on; +ERROR HY000: Incorrect arguments to SET +set global innodb_file_format_check = off; +ERROR HY000: Incorrect arguments to SET diff --git a/mysql-test/suite/innodb/r/innodb_file_format.result b/mysql-test/suite/innodb/r/innodb_file_format.result index fbc67ada1bb..36f176c616a 100644 --- a/mysql-test/suite/innodb/r/innodb_file_format.result +++ b/mysql-test/suite/innodb/r/innodb_file_format.result @@ -31,8 +31,6 @@ select @@innodb_file_format_check; @@innodb_file_format_check Barracuda set global innodb_file_format_check=default; -Warnings: -Warning 1210 Ignoring SET innodb_file_format=on select @@innodb_file_format_check; @@innodb_file_format_check Barracuda diff --git a/mysql-test/suite/innodb/t/innodb_bug47167.test b/mysql-test/suite/innodb/t/innodb_bug47167.test new file mode 100644 index 00000000000..df056b91ff9 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug47167.test @@ -0,0 +1,46 @@ +# This is the unit test for bug *47167. +# It tests setting the global variable +# "innodb_file_format_check" with a +# user-Defined Variable. + +--source include/have_innodb.inc +-- source suite/innodb/include/have_innodb_plugin.inc + +# Save the value (Antelope) in 'innodb_file_format_check' to +# 'old_innodb_file_format_check' +set @old_innodb_file_format_check=@@innodb_file_format_check; + +# @old_innodb_file_format_check shall have the value of 'Antelope' +select @old_innodb_file_format_check; + +# Reset the value in 'innodb_file_format_check' to 'Barracuda' +set global innodb_file_format_check = Barracuda; + +select @@innodb_file_format_check; + +# Set 'innodb_file_format_check' to its default value, which +# is the latest file format supported in the current release. +set global innodb_file_format_check = DEFAULT; + +select @@innodb_file_format_check; + +# Put the saved value back to 'innodb_file_format_check' +set global innodb_file_format_check = @old_innodb_file_format_check; + +# Check whether 'innodb_file_format_check' get its original value. +select @@innodb_file_format_check; + +# Following are negative tests, all should fail. +--disable_warnings +--error ER_WRONG_ARGUMENTS +set global innodb_file_format_check = cheetah; + +--error ER_WRONG_ARGUMENTS +set global innodb_file_format_check = Bear; + +--error ER_WRONG_ARGUMENTS +set global innodb_file_format_check = on; + +--error ER_WRONG_ARGUMENTS +set global innodb_file_format_check = off; +--enable_warnings -- cgit v1.2.1 From 444c6d0d73afae6efb1f7d8976b34f5b9a711bfc Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 17:54:54 +0530 Subject: Applying InnoDB Plugin 1.0.6 snapshot, part 6. Add Testcase for BUG#46676 1. add testcase for BUG#46676 2. Allow CREATE INDEX to be interrupted 3. ha_innobase::change_active_index(): When the history is missing, report it to the client, not to the error log 4. ChangeLog entries appplied revisions:r6169, r6170, r6175, r6177, r6179 Detailed revision comments: r6169 | calvin | 2009-11-12 14:40:43 +0200 (Thu, 12 Nov 2009) | 6 lines branches/zip: add test case for bug#46676 This crash is reproducible with InnoDB plugin 1.0.4 + MySQL 5.1.37. But no longer reproducible after MySQL 5.1.38 (with plugin 1.0.5). Add test case to catch future regression. r6170 | marko | 2009-11-12 15:49:08 +0200 (Thu, 12 Nov 2009) | 4 lines branches/zip: Allow CREATE INDEX to be interrupted. (Issue #354) rb://183 approved by Heikki Tuuri r6175 | vasil | 2009-11-16 20:07:39 +0200 (Mon, 16 Nov 2009) | 4 lines branches/zip: Wrap line at 78th char in the ChangeLog r6177 | calvin | 2009-11-16 20:20:38 +0200 (Mon, 16 Nov 2009) | 2 lines branches/zip: add an entry to ChangeLog for r6065 r6179 | marko | 2009-11-17 10:19:34 +0200 (Tue, 17 Nov 2009) | 2 lines branches/zip: ha_innobase::change_active_index(): When the history is missing, report it to the client, not to the error log. --- mysql-test/suite/innodb/r/innodb_bug46676.result | 9 +++++++++ mysql-test/suite/innodb/t/innodb_bug46676.test | 16 ++++++++++++++++ 2 files changed, 25 insertions(+) create mode 100644 mysql-test/suite/innodb/r/innodb_bug46676.result create mode 100644 mysql-test/suite/innodb/t/innodb_bug46676.test (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/innodb_bug46676.result b/mysql-test/suite/innodb/r/innodb_bug46676.result new file mode 100644 index 00000000000..996799ce931 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug46676.result @@ -0,0 +1,9 @@ +SET foreign_key_checks=0; +CREATE TABLE t1 (id int, foreign key (id) references t2(id)) ENGINE=INNODB; +CREATE TABLE t2 (id int, foreign key (id) references t1(id)) ENGINE=INNODB; +SET foreign_key_checks=1; +SELECT COUNT(*) FROM information_schema.key_column_usage WHERE REFERENCED_TABLE_NAME in ('t1', 't2'); +COUNT(*) +2 +SET foreign_key_checks=0; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/t/innodb_bug46676.test b/mysql-test/suite/innodb/t/innodb_bug46676.test new file mode 100644 index 00000000000..440666c4226 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug46676.test @@ -0,0 +1,16 @@ +# This is the test for bug 46676: mysqld got exception 0xc0000005 +# It is reproducible with InnoDB plugin 1.0.4 + MySQL 5.1.37. +# But no longer reproducible after MySQL 5.1.38 (with plugin 1.0.5). + +--source include/have_innodb.inc + +SET foreign_key_checks=0; +CREATE TABLE t1 (id int, foreign key (id) references t2(id)) ENGINE=INNODB; +CREATE TABLE t2 (id int, foreign key (id) references t1(id)) ENGINE=INNODB; +SET foreign_key_checks=1; + +# Server crashes +SELECT COUNT(*) FROM information_schema.key_column_usage WHERE REFERENCED_TABLE_NAME in ('t1', 't2'); + +SET foreign_key_checks=0; +DROP TABLE t1, t2; -- cgit v1.2.1 From d813b4304844a9bf430dc08c550b3f91cb8af18b Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 18:19:13 +0530 Subject: Applying InnoDB Plugin 1.0.6 snapshot, part 7 Fixes several test cases. applied revisions: r6181, r6182, r6183, r6184 r6183 - changes are made only to tests in innodb suite which is only innodb-consistent-master.opt Detailed revision comments: r6181 | vasil | 2009-11-17 12:21:41 +0200 (Tue, 17 Nov 2009) | 33 lines branches/zip: At the end of innodb-index.test: restore the environment as it was before the test was started to silence this warning: MTR's internal check of the test case 'main.innodb-index' failed. This means that the test case does not preserve the state that existed before the test case was executed. Most likely the test case did not do a proper clean-up. This is the diff of the states of the servers before and after the test case was executed: mysqltest: Logging to '/tmp/autotest.sh-20091117_033000-zip.btyZwu/mysql-5.1/mysql-test/var/tmp/check-mysqld_1.log'. mysqltest: Results saved in '/tmp/autotest.sh-20091117_033000-zip.btyZwu/mysql-5.1/mysql-test/var/tmp/check-mysqld_1.result'. mysqltest: Connecting to server localhost:13000 (socket /tmp/autotest.sh-20091117_033000-zip.btyZwu/mysql-5.1/mysql-test/var/tmp/mysqld.1.sock) as 'root', connection 'default', attempt 0 ... mysqltest: ... Connected. mysqltest: Start processing test commands from './include/check-testcase.test' ... mysqltest: ... Done processing test commands. --- /tmp/autotest.sh-20091117_033000-zip.btyZwu/mysql-5.1/mysql-test/var/tmp/check-mysqld_1.result 2009-11-17 13:10:40.000000000 +0300 +++ /tmp/autotest.sh-20091117_033000-zip.btyZwu/mysql-5.1/mysql-test/var/tmp/check-mysqld_1.reject 2009-11-17 13:10:54.000000000 +0300 @@ -84,7 +84,7 @@ INNODB_DOUBLEWRITE ON INNODB_FAST_SHUTDOWN 1 INNODB_FILE_FORMAT Antelope -INNODB_FILE_FORMAT_CHECK Antelope +INNODB_FILE_FORMAT_CHECK Barracuda INNODB_FILE_PER_TABLE OFF INNODB_FLUSH_LOG_AT_TRX_COMMIT 1 INNODB_FLUSH_METHOD mysqltest: Result content mismatch not ok r6182 | marko | 2009-11-17 13:49:15 +0200 (Tue, 17 Nov 2009) | 1 line branches/zip: Set svn:eol-style on mysql-test files. r6183 | marko | 2009-11-17 13:51:16 +0200 (Tue, 17 Nov 2009) | 1 line branches/zip: Prepend loose_ to plugin-only mysql-test options. r6184 | marko | 2009-11-17 13:52:01 +0200 (Tue, 17 Nov 2009) | 1 line branches/zip: innodb-index.test: Restore innodb_file_format_check. --- mysql-test/suite/innodb/r/innodb-index.result | 1 + mysql-test/suite/innodb/t/innodb-consistent-master.opt | 2 +- mysql-test/suite/innodb/t/innodb-index.test | 10 ++++++++++ 3 files changed, 12 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result index a7d66b15300..67fbe0dce02 100644 --- a/mysql-test/suite/innodb/r/innodb-index.result +++ b/mysql-test/suite/innodb/r/innodb-index.result @@ -968,6 +968,7 @@ create index t1u on t1 (u(1)); drop table t1; set global innodb_file_per_table=0; set global innodb_file_format=Antelope; +set global innodb_file_format_check=Antelope; SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; CREATE TABLE t1( diff --git a/mysql-test/suite/innodb/t/innodb-consistent-master.opt b/mysql-test/suite/innodb/t/innodb-consistent-master.opt index e76299453d3..cb48f1aaf60 100644 --- a/mysql-test/suite/innodb/t/innodb-consistent-master.opt +++ b/mysql-test/suite/innodb/t/innodb-consistent-master.opt @@ -1 +1 @@ ---innodb_lock_wait_timeout=2 +--loose-innodb_lock_wait_timeout=2 diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index 54aff3a42c0..b5dd2e037e7 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -1,5 +1,7 @@ -- source include/have_innodb.inc +let $innodb_file_format_check_orig=`select @@innodb_file_format_check`; + create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb; insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak'); commit; @@ -398,6 +400,7 @@ create index t1u on t1 (u(1)); drop table t1; eval set global innodb_file_per_table=$per_table; eval set global innodb_file_format=$format; +eval set global innodb_file_format_check=$format; # # Test to check whether CREATE INDEX handles implicit foreign key @@ -532,3 +535,10 @@ disconnect a; disconnect b; DROP TABLE t1; + +# +# restore environment to the state it was before this test execution +# + +-- disable_query_log +eval SET GLOBAL innodb_file_format_check=$innodb_file_format_check_orig; -- cgit v1.2.1 From 1e26b2c26b4eccf359f7e3cb325604a087f5b9d3 Mon Sep 17 00:00:00 2001 From: Satya B Date: Mon, 30 Nov 2009 18:43:34 +0530 Subject: Applying InnoDB Plugin 1.0.6 snapshot, part 8. Fixes BUG#48782 applied revisions: r6185, r6186, r6189, r6194 r6185 - only code changes incorporated, changesets which change innodb tests in the main mysql suite are discarded r61889 - Fixes BUG#48782 Detailed revision comments: r6185 | marko | 2009-11-17 16:44:20 +0200 (Tue, 17 Nov 2009) | 16 lines branches/zip: Report duplicate table names to the client connection, not to the error log. This change will allow innodb-index.test to be re-enabled. It was previously disabled, because mysql-test-run does not like output in the error log. row_create_table_for_mysql(): Do not output anything to the error log when reporting DB_DUPLICATE_KEY. Let the caller report the error. Add a TODO comment that the dict_table_t object is apparently not freed when an error occurs. create_table_def(): Convert InnoDB table names to the character set of the client connection for reporting. Use my_error(ER_WRONG_COLUMN_NAME) for reporting reserved column names. Report my_error(ER_TABLE_EXISTS_ERROR) when row_create_table_for_mysql() returns DB_DUPLICATE_KEY. rb://206 r6186 | vasil | 2009-11-17 16:48:14 +0200 (Tue, 17 Nov 2009) | 4 lines branches/zip: Add ChangeLog entry for r6185. r6189 | marko | 2009-11-18 11:36:18 +0200 (Wed, 18 Nov 2009) | 5 lines branches/zip: ha_innobase::add_index(): When creating the primary key and the table is being locked by another transaction, do not attempt to drop the table. (Bug #48782) Approved by Sunny Bains over IM r6194 | vasil | 2009-11-19 09:24:45 +0200 (Thu, 19 Nov 2009) | 5 lines branches/zip: Increment version number from 1.0.5 to 1.0.6 since 1.0.5 was just released by MySQL and we will soon release 1.0.6. --- mysql-test/lib/mtr_cases.pm | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/lib/mtr_cases.pm b/mysql-test/lib/mtr_cases.pm index 1f018f27f7b..ef3c2a43e87 100644 --- a/mysql-test/lib/mtr_cases.pm +++ b/mysql-test/lib/mtr_cases.pm @@ -522,6 +522,8 @@ sub collect_one_suite($) next if ($test->{'name'} eq 'main.innodb_bug46000'); # Fails with innodb plugin next if ($test->{'name'} eq 'main.innodb-autoinc'); + # Fails with innodb plugin: r6185 Testcases changes not included + next if ($test->{'name'} eq 'main.innodb_bug44369'); # Copy test options my $new_test= My::Test->new(); while (my ($key, $value) = each(%$test)) -- cgit v1.2.1 From f5b71849196e8d95a8688d31180179597ffae3ec Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 1 Dec 2009 16:27:21 +0800 Subject: Updated experimental test list for rpl.rpl_cross_version and rpl.rpl_get_master_version_and_clock. --- mysql-test/collections/default.experimental | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/collections/default.experimental b/mysql-test/collections/default.experimental index a0fa2a30a69..9fc39a8d780 100644 --- a/mysql-test/collections/default.experimental +++ b/mysql-test/collections/default.experimental @@ -18,8 +18,8 @@ main.plugin_load @solaris # Bug#42144 ndb.* # joro : NDB tests marked as experimental as agreed with bochklin -rpl.rpl_cross_version* # Bug #43913 2009-10-26 joro rpl_cross_version can't pass on conflicts complainig clash with --slave-load-tm -rpl.rpl_get_master_version_and_clock* # Bug#46931 2009-08-26 alik rpl.rpl_get_master_version_and_clock fails on hpux11.31 +rpl.rpl_cross_version* # Bug#48340 2009-12-01 Daogang rpl_cross_version: Found warnings/errors in server log file! +rpl.rpl_get_master_version_and_clock* # Bug #49191 2009-12-01 Daogang rpl_get_master_version_and_clock failed on PB2: COM_REGISTER_SLAVE failed rpl.rpl_innodb_bug28430* @solaris # Bug#46029 rpl.rpl_row_create_table* # Bug#45576: rpl_row_create_table fails on PB2 rpl.rpl_trigger* # Bug#47810 2009-10-04 joro rpl.rpl_trigger.test fails with valgrind errors with the innodb plugin -- cgit v1.2.1 From 248a74eaa10738a0e71e1fe1b11d7c236fe0f521 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Tue, 1 Dec 2009 11:19:51 +0200 Subject: Revert of the push of bug #20837 due to failing regression tests. --- mysql-test/r/innodb_mysql.result | 31 ------------------------------- mysql-test/t/innodb_mysql.test | 26 -------------------------- 2 files changed, 57 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 2d93cbb47b3..402ab3c1b16 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -2273,35 +2273,4 @@ END| DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1,t2; -# -# Bug #20837 Apparent change of isolation -# level during transaction -# -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -CREATE TABLE t1 (s1 INT) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1),(2); -COMMIT; -SET @@autocommit = 0; -COMMIT; -SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -START TRANSACTION; -SELECT * FROM t1; -s1 -1 -2 -Should be READ UNCOMMITTED -SELECT @@tx_isolation; -@@tx_isolation -READ-UNCOMMITTED -INSERT INTO t1 VALUES (-1); -Should be READ UNCOMMITTED -SELECT @@tx_isolation; -@@tx_isolation -READ-UNCOMMITTED -COMMIT; -Should now be REPEATABLE READ -SELECT @@tx_isolation; -@@tx_isolation -REPEATABLE-READ -DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 0462536371e..a2a1113598d 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -536,30 +536,4 @@ DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1,t2; - ---echo # ---echo # Bug #20837 Apparent change of isolation ---echo # level during transaction ---echo # - -SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -CREATE TABLE t1 (s1 INT) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1),(2); -COMMIT; - -SET @@autocommit = 0; -COMMIT; -SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -START TRANSACTION; -SELECT * FROM t1; ---echo Should be READ UNCOMMITTED -SELECT @@tx_isolation; -INSERT INTO t1 VALUES (-1); ---echo Should be READ UNCOMMITTED -SELECT @@tx_isolation; -COMMIT; ---echo Should now be REPEATABLE READ -SELECT @@tx_isolation; -DROP TABLE t1; - --echo End of 5.1 tests -- cgit v1.2.1 From f1eb8d937e26c660a76ba4e32f709f614edfb102 Mon Sep 17 00:00:00 2001 From: Georgi Kodinov Date: Tue, 1 Dec 2009 11:21:15 +0200 Subject: Bug #45576: moving the test from experimental to disabled. --- mysql-test/collections/default.experimental | 1 - mysql-test/suite/rpl/t/disabled.def | 1 + 2 files changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/collections/default.experimental b/mysql-test/collections/default.experimental index a0fa2a30a69..0ee9fd81d1c 100644 --- a/mysql-test/collections/default.experimental +++ b/mysql-test/collections/default.experimental @@ -21,7 +21,6 @@ ndb.* # joro : NDB tests marked as experiment rpl.rpl_cross_version* # Bug #43913 2009-10-26 joro rpl_cross_version can't pass on conflicts complainig clash with --slave-load-tm rpl.rpl_get_master_version_and_clock* # Bug#46931 2009-08-26 alik rpl.rpl_get_master_version_and_clock fails on hpux11.31 rpl.rpl_innodb_bug28430* @solaris # Bug#46029 -rpl.rpl_row_create_table* # Bug#45576: rpl_row_create_table fails on PB2 rpl.rpl_trigger* # Bug#47810 2009-10-04 joro rpl.rpl_trigger.test fails with valgrind errors with the innodb plugin rpl_ndb.* # joro : NDB tests marked as experimental as agreed with bochklin diff --git a/mysql-test/suite/rpl/t/disabled.def b/mysql-test/suite/rpl/t/disabled.def index 8cae44a3607..d2cb1ce9518 100644 --- a/mysql-test/suite/rpl/t/disabled.def +++ b/mysql-test/suite/rpl/t/disabled.def @@ -10,3 +10,4 @@ # ############################################################################## +rpl_row_create_table : Bug#45576 2009-12-01 joro rpl_row_create_table fails on PB2 -- cgit v1.2.1 From d24b879474e16e99b738d831ec9a3f2a4a341fd6 Mon Sep 17 00:00:00 2001 From: Satya B Date: Wed, 2 Dec 2009 15:17:49 +0530 Subject: Disable the test case innodb-autonic.test until BUG#49267 is fixed Add the test to disabled list and remove from the experimental list. --- mysql-test/collections/default.experimental | 1 - mysql-test/t/disabled.def | 1 + 2 files changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/collections/default.experimental b/mysql-test/collections/default.experimental index 49a0fd8c58c..5f3b7251ce1 100644 --- a/mysql-test/collections/default.experimental +++ b/mysql-test/collections/default.experimental @@ -13,7 +13,6 @@ funcs_1.ndb* # joro : NDB tests marked as experiment funcs_2.ndb_charset # joro : NDB tests marked as experimental as agreed with bochklin main.ctype_gbk_binlog @solaris # Bug#46010: main.ctype_gbk_binlog fails sporadically : Table 't2' already exists -main.innodb-autoinc* # Bug#47809 2009-10-04 joro innodb-autoinc.test fails with valgrind errors with the innodb plugin main.plugin_load @solaris # Bug#42144 ndb.* # joro : NDB tests marked as experimental as agreed with bochklin diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 06d3dbb4e57..378b810fcbb 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -12,3 +12,4 @@ kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically rpl_killed_ddl : Bug#45520: rpl_killed_ddl fails sporadically in pb2 +innodb-autoinc : Bug#49267 2009-12-02 test fails on windows because of different case mode -- cgit v1.2.1