diff options
Diffstat (limited to 'mysql-test')
58 files changed, 995 insertions, 302 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_ddl.test b/mysql-test/extra/rpl_tests/rpl_ddl.test index 4aab45db18b..15794e5e035 100644 --- a/mysql-test/extra/rpl_tests/rpl_ddl.test +++ b/mysql-test/extra/rpl_tests/rpl_ddl.test @@ -228,15 +228,11 @@ let $my_master_commit= true; let $my_slave_commit= true; --source include/rpl_stmt_seq.inc SELECT * FROM mysqltest1.t7; -connection slave; ---disable_query_log -SELECT '-------- switch to slave --------' as ""; ---enable_query_log +--echo -------- switch to slave -------- +sync_slave_with_master; SELECT * FROM mysqltest1.t7; +--echo -------- switch to master ------- connection master; ---disable_query_log -SELECT '-------- switch to master -------' as ""; ---enable_query_log ############################################################### # Cases with LOCK/UNLOCK diff --git a/mysql-test/extra/rpl_tests/rpl_truncate.test b/mysql-test/extra/rpl_tests/rpl_truncate.test new file mode 100644 index 00000000000..982623dfff7 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_truncate.test @@ -0,0 +1,36 @@ +# +# Copyright 2006 MySQL. All rights reserved. +# +# Test to check for the different version of truncating a table. +# The statements are "TRUNCATE tbl" and "DELETE FROM tbl". We check +# the behaviour of each possible value for BINLOG_FORMAT. +# +# Author(s): Mats Kindahl + +--source include/have_row_based.inc +--source include/master-slave.inc + +let $format = STATEMENT; +let $stmt = TRUNCATE TABLE; +--source extra/rpl_tests/rpl_truncate_helper.inc + +let $format = MIXED; +let $stmt = TRUNCATE TABLE; +--source extra/rpl_tests/rpl_truncate_helper.inc + +let $format = ROW; +let $stmt = TRUNCATE TABLE; +--source extra/rpl_tests/rpl_truncate_helper.inc + +let $format = STATEMENT; +let $stmt = DELETE FROM; +--source extra/rpl_tests/rpl_truncate_helper.inc + +let $format = MIXED; +let $stmt = DELETE FROM; +--source extra/rpl_tests/rpl_truncate_helper.inc + +let $format = ROW; +let $stmt = DELETE FROM; +--source extra/rpl_tests/rpl_truncate_helper.inc + diff --git a/mysql-test/extra/rpl_tests/rpl_truncate_helper.inc b/mysql-test/extra/rpl_tests/rpl_truncate_helper.inc new file mode 100644 index 00000000000..2306b746a86 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_truncate_helper.inc @@ -0,0 +1,41 @@ + +--disable_query_log +--disable_warnings +connection slave; +STOP SLAVE; +connection master; +DROP TABLE IF EXISTS t1; +RESET MASTER; +connection slave; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; +--enable_warnings +--enable_query_log + +--echo **** On Master **** +connection master; +eval SET SESSION BINLOG_FORMAT=$format; +eval SET GLOBAL BINLOG_FORMAT=$format; + +eval CREATE TABLE t1 (a INT, b LONG) ENGINE=$engine; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +--echo **** On Slave **** +sync_slave_with_master; +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +--echo **** On Master **** +connection master; +eval $stmt t1; +SELECT * FROM t1; +--echo **** On Slave **** +sync_slave_with_master; +# Should be empty +SELECT * FROM t1; +--echo **** On Master **** +connection master; +DROP TABLE t1; +--replace_result $SERVER_VERSION SERVER_VERSION +--replace_regex /table_id: [0-9]+/table_id: #/ +SHOW BINLOG EVENTS; diff --git a/mysql-test/include/master-slave-reset.inc b/mysql-test/include/master-slave-reset.inc new file mode 100644 index 00000000000..204c6c5f5f6 --- /dev/null +++ b/mysql-test/include/master-slave-reset.inc @@ -0,0 +1,22 @@ +connection slave; +#we expect STOP SLAVE to produce a warning as the slave is stopped +#(the server was started with skip-slave-start) +--disable_warnings +stop slave; +--wait_for_slave_to_stop +--enable_warnings +connection master; +--disable_warnings +--disable_query_log +use test; +--enable_query_log +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +--enable_warnings +reset master; +connection slave; +reset slave; +# Clean up old test tables +--disable_warnings +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +--enable_warnings +start slave; diff --git a/mysql-test/include/master-slave.inc b/mysql-test/include/master-slave.inc index ea09f4e842b..fe1dacb433e 100644 --- a/mysql-test/include/master-slave.inc +++ b/mysql-test/include/master-slave.inc @@ -2,28 +2,8 @@ connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,); connect (master1,127.0.0.1,root,,test,$MASTER_MYPORT,); connect (slave,127.0.0.1,root,,test,$SLAVE_MYPORT,); connect (slave1,127.0.0.1,root,,test,$SLAVE_MYPORT,); -connection slave; -#we expect STOP SLAVE to produce a warning as the slave is stopped -#(the server was started with skip-slave-start) ---disable_warnings -stop slave; ---enable_warnings ---require r/slave-stopped.result -show status like 'Slave_running'; -connection master; ---disable_warnings -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; ---enable_warnings -reset master; -connection slave; -reset slave; -# Clean up old test tables ---disable_warnings -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; ---enable_warnings -start slave; ---require r/slave-running.result -show status like 'Slave_running'; + +-- source include/master-slave-reset.inc # Set the default connection to 'master' connection master; diff --git a/mysql-test/r/binlog_row_blackhole.result b/mysql-test/r/binlog_row_blackhole.result index 42bf7a10888..140d7d4da46 100644 --- a/mysql-test/r/binlog_row_blackhole.result +++ b/mysql-test/r/binlog_row_blackhole.result @@ -118,12 +118,6 @@ master-bin.000001 # Query 1 # use `test`; COMMIT master-bin.000001 # Table_map 1 # table_id: # (test.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # use `test`; COMMIT -master-bin.000001 # Table_map 1 # table_id: # (test.t1) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Query 1 # use `test`; COMMIT -master-bin.000001 # Table_map 1 # table_id: # (test.t1) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Query 1 # use `test`; COMMIT master-bin.000001 # Query 1 # use `test`; create table t2 (a varchar(200)) engine=blackhole master-bin.000001 # Table_map 1 # table_id: # (test.t2) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F @@ -131,12 +125,6 @@ master-bin.000001 # Query 1 # use `test`; COMMIT master-bin.000001 # Query 1 # use `test`; alter table t1 add b int master-bin.000001 # Query 1 # use `test`; alter table t1 drop b master-bin.000001 # Query 1 # use `test`; create table t3 like t1 -master-bin.000001 # Table_map 1 # table_id: # (test.t1) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Query 1 # use `test`; COMMIT -master-bin.000001 # Table_map 1 # table_id: # (test.t1) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 # Query 1 # use `test`; COMMIT drop table t1,t2,t3; reset master; create table t1 (a int) engine=blackhole; diff --git a/mysql-test/r/binlog_row_mix_innodb_myisam.result b/mysql-test/r/binlog_row_mix_innodb_myisam.result index 84959684c42..32c21a01f27 100644 --- a/mysql-test/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/r/binlog_row_mix_innodb_myisam.result @@ -261,26 +261,21 @@ master-bin.000001 243 Table_map 1 # table_id: # (test.t1) master-bin.000001 282 Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 316 Xid 1 # COMMIT /* xid= */ master-bin.000001 343 Table_map 1 # table_id: # (test.t1) -master-bin.000001 382 Query 1 # use `test`; delete from t1 -master-bin.000001 459 Xid 1 # COMMIT /* xid= */ -master-bin.000001 486 Table_map 1 # table_id: # (test.t2) -master-bin.000001 525 Query 1 # use `test`; delete from t2 -master-bin.000001 602 Xid 1 # COMMIT /* xid= */ -master-bin.000001 629 Query 1 # use `test`; alter table t2 engine=MyISAM -master-bin.000001 720 Table_map 1 # table_id: # (test.t1) -master-bin.000001 759 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 793 Xid 1 # COMMIT /* xid= */ -master-bin.000001 820 Query 1 # use `test`; BEGIN -master-bin.000001 888 Table_map 1 # table_id: # (test.t1) -master-bin.000001 927 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 956 Xid 1 # COMMIT /* xid= */ -master-bin.000001 983 Query 1 # use `test`; drop table t1,t2 -master-bin.000001 1062 Query 1 # use `test`; create table t0 (n int) -master-bin.000001 1148 Table_map 1 # table_id: # (test.t0) -master-bin.000001 1187 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 1221 Table_map 1 # table_id: # (test.t0) -master-bin.000001 1260 Write_rows 1 # table_id: # flags: STMT_END_F -master-bin.000001 1294 Query 1 # use `test`; create table t2 (n int) engine=innodb +master-bin.000001 382 Delete_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 421 Xid 1 # COMMIT /* xid= */ +master-bin.000001 448 Query 1 # use `test`; alter table t2 engine=MyISAM +master-bin.000001 539 Table_map 1 # table_id: # (test.t1) +master-bin.000001 578 Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 612 Xid 1 # COMMIT /* xid= */ +master-bin.000001 639 Table_map 1 # table_id: # (test.t2) +master-bin.000001 678 Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 712 Query 1 # use `test`; drop table t1,t2 +master-bin.000001 791 Query 1 # use `test`; create table t0 (n int) +master-bin.000001 877 Table_map 1 # table_id: # (test.t0) +master-bin.000001 916 Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 950 Table_map 1 # table_id: # (test.t0) +master-bin.000001 989 Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 1023 Query 1 # use `test`; create table t2 (n int) engine=innodb do release_lock("lock1"); drop table t0,t2; set autocommit=0; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 6f8c319eb6f..c3710865b15 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -780,12 +780,6 @@ t1 CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=4294967295 drop table t1; -create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb -character set utf8 collate utf8_general_ci; -Warnings: -Warning 1071 Specified key was too long; max key length is 765 bytes -insert into t1 values('aaa'); -drop table t1; CREATE TABLE t1 (a int, b int); insert into t1 values (1,1),(1,2); CREATE TABLE t2 (primary key (a)) select * from t1; diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index b3d6e10448b..5f5cd94c216 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -82,7 +82,7 @@ t2 CREATE TABLE `t2` ( ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1' INSERT INTO federated.t2 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t2 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t2; +SELECT * FROM federated.t2 ORDER BY id, name; id name 1 foo 2 fee @@ -107,7 +107,7 @@ ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1%'; INSERT INTO federated.t1 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t1 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t1; +SELECT * FROM federated.t1 ORDER BY id,name; id name 1 foo 2 fee @@ -121,7 +121,7 @@ ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1%'; INSERT INTO federated.`t1%` (id, name) VALUES (1, 'foo'); INSERT INTO federated.`t1%` (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.`t1%`; +SELECT * FROM federated.`t1%` ORDER BY id, name; id name 1 foo 2 fee diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index eca6d148567..72ae29185bc 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1378,9 +1378,9 @@ insert into `t2`values ( 1 ) ; create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb; insert into `t3`values ( 1 ) ; delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; ERROR 42S22: Unknown column 't1.id' in 'where clause' drop table t3,t2,t1; @@ -1392,7 +1392,7 @@ foreign key(pid) references t1(id) on delete cascade) engine=innodb; insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6), (8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14); delete from t1 where id=0; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `t1` (`id`) ON DELETE CASCADE) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `t1` (`id`) ON DELETE CASCADE) delete from t1 where id=15; delete from t1 where id=0; drop table t1; @@ -2633,18 +2633,18 @@ v INT, CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id) ) ENGINE=InnoDB; INSERT INTO t2 VALUES(2); -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) INSERT INTO t1 VALUES(1); INSERT INTO t2 VALUES(1); DELETE FROM t1 WHERE id = 1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) DROP TABLE t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails SET FOREIGN_KEY_CHECKS=0; DROP TABLE t1; SET FOREIGN_KEY_CHECKS=1; INSERT INTO t2 VALUES(3); -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) DROP TABLE t2; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; insert into t1 values (1),(2); @@ -2922,23 +2922,23 @@ create table t4(a int primary key,constraint foreign key(a)references t3(a)) row insert into t1 values(1); insert into t3 values(1); insert into t2 values(2); -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) insert into t4 values(2); -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test/t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) insert into t2 values(1); insert into t4 values(1); update t1 set a=2; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) update t2 set a=2; -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) update t3 set a=2; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) update t4 set a=2; -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test/t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) truncate t1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) truncate t3; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)) truncate t2; truncate t4; truncate t1; @@ -2993,7 +2993,7 @@ create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innod create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb; insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42); insert into t2 values(0x42); -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) insert into t2 values(0x41); select hex(s1) from t2; hex(s1) @@ -3003,11 +3003,11 @@ select hex(s1) from t2; hex(s1) 4100 update t1 set s1=0x12 where a=1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) update t1 set s1=0x12345678 where a=1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) update t1 set s1=0x123457 where a=1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) update t1 set s1=0x1220 where a=1; select hex(s1) from t2; hex(s1) @@ -3021,11 +3021,11 @@ select hex(s1) from t2; hex(s1) 4200 delete from t1 where a=1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) delete from t1 where a=2; update t2 set s1=0x4120; delete from t1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) delete from t1 where a!=3; select a,hex(s1) from t1; a hex(s1) @@ -3051,7 +3051,7 @@ hex(s1) 12 delete from t1 where a=1; delete from t1 where a=2; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test/t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) select a,hex(s1) from t1; a hex(s1) 2 12 diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 918f1c2c7c5..62e1c2f4019 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1,4 +1,10 @@ drop table if exists t1,t2,t1m,t1i,t2m,t2i,t4; +create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb +character set utf8 collate utf8_general_ci; +Warnings: +Warning 1071 Specified key was too long; max key length is 765 bytes +insert into t1 values('aaa'); +drop table t1; create table t1 ( c_id int(11) not null default '0', org_id int(11) default null, diff --git a/mysql-test/r/ndb_binlog_ignore_db.result b/mysql-test/r/ndb_binlog_ignore_db.result index 2f411fcd03d..8dc2c1ff1f8 100644 --- a/mysql-test/r/ndb_binlog_ignore_db.result +++ b/mysql-test/r/ndb_binlog_ignore_db.result @@ -7,6 +7,4 @@ insert into t1 values (1, 1); show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; drop table if exists t1 -master-bin.000001 # Table_map # # table_id: # (mysql.proc) -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F drop database mysqltest; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index f6b7409ea6a..f687ab7e2c6 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -406,7 +406,7 @@ insert into t2(uid, name) values (24, CHAR(64+24)), (25, CHAR(64+25)), (26, CHAR(64+26)); -insert into t1(uid, name) select uid, name from t2; +insert into t1(uid, name) select uid, name from t2 order by uid; delete from t2; insert into t2(id, uid, name) select id, uid, name from t1; select count(*) from t1; diff --git a/mysql-test/r/rpl_ddl.result b/mysql-test/r/rpl_ddl.result index cfa2b4dc76d..ace86532b12 100644 --- a/mysql-test/r/rpl_ddl.result +++ b/mysql-test/r/rpl_ddl.result @@ -646,11 +646,9 @@ flush logs; -------- switch to master ------- SELECT * FROM mysqltest1.t7; f1 - -------- switch to slave -------- SELECT * FROM mysqltest1.t7; f1 - -------- switch to master ------- ######## LOCK TABLES mysqltest1.t1 WRITE, mysqltest1.t8 READ ######## diff --git a/mysql-test/r/rpl_ndb_charset.result b/mysql-test/r/rpl_ndb_charset.result index 31a7e1368f3..0ce4446c8a5 100644 --- a/mysql-test/r/rpl_ndb_charset.result +++ b/mysql-test/r/rpl_ndb_charset.result @@ -112,16 +112,10 @@ drop database mysqltest3; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # drop database if exists mysqltest2 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # drop database if exists mysqltest3 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # create database mysqltest2 character set latin2 master-bin.000001 # Query 1 # create database mysqltest3 master-bin.000001 # Query 1 # drop database mysqltest3 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # create database mysqltest3 master-bin.000001 # Query 1 # use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100)) master-bin.000001 # Table_map 1 # table_id: # (mysqltest2.t1) @@ -147,11 +141,7 @@ master-bin.000001 # Query 1 # use `mysqltest2`; truncate table t1 master-bin.000001 # Table_map 1 # table_id: # (mysqltest2.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # drop database mysqltest2 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # drop database mysqltest3 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F select "--- --global--" as ""; --- --global-- diff --git a/mysql-test/r/rpl_row_basic_11bugs.result b/mysql-test/r/rpl_row_basic_11bugs.result index 4ed7d54647b..8c8a77b5084 100644 --- a/mysql-test/r/rpl_row_basic_11bugs.result +++ b/mysql-test/r/rpl_row_basic_11bugs.result @@ -44,3 +44,19 @@ t1 USE test_ignore; ERROR 42000: Unknown database 'test_ignore' DROP DATABASE test_ignore; +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); +DELETE FROM t1; +INSERT INTO t1 VALUES (1),(2); +DELETE FROM t1 WHERE a = 0; +UPDATE t1 SET a=99 WHERE a = 0; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: 5.1.11-beta-debug-log, Binlog ver: 4 +master-bin.000001 102 Query 1 188 use `test`; CREATE TABLE t1 (a INT) +master-bin.000001 188 Table_map 1 227 table_id: # (test.t1) +master-bin.000001 227 Write_rows 1 266 table_id: # flags: STMT_END_F diff --git a/mysql-test/r/rpl_row_charset.result b/mysql-test/r/rpl_row_charset.result index d80b5fa60de..79cf75c8cc1 100644 --- a/mysql-test/r/rpl_row_charset.result +++ b/mysql-test/r/rpl_row_charset.result @@ -112,16 +112,10 @@ drop database mysqltest3; show binlog events from 102; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # drop database if exists mysqltest2 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # drop database if exists mysqltest3 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # create database mysqltest2 character set latin2 master-bin.000001 # Query 1 # create database mysqltest3 master-bin.000001 # Query 1 # drop database mysqltest3 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # create database mysqltest3 master-bin.000001 # Query 1 # use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100)) master-bin.000001 # Table_map 1 # table_id: # (mysqltest2.t1) @@ -147,11 +141,7 @@ master-bin.000001 # Query 1 # use `mysqltest2`; truncate table t1 master-bin.000001 # Table_map 1 # table_id: # (mysqltest2.t1) master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # drop database mysqltest2 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F master-bin.000001 # Query 1 # drop database mysqltest3 -master-bin.000001 # Table_map 1 # table_id: # (mysql.proc) -master-bin.000001 # Write_rows 1 # table_id: # flags: STMT_END_F select "--- --global--" as ""; --- --global-- diff --git a/mysql-test/r/rpl_row_create_table.result b/mysql-test/r/rpl_row_create_table.result index b3a8f5e1a0f..f314aa39b81 100644 --- a/mysql-test/r/rpl_row_create_table.result +++ b/mysql-test/r/rpl_row_create_table.result @@ -127,7 +127,7 @@ NULL 5 10 NULL 6 12 CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3; ERROR 23000: Duplicate entry '2' for key 'b' -SHOW BINLOG EVENTS FROM 1326; +SHOW BINLOG EVENTS FROM 1256; Log_name Pos Event_type Server_id End_log_pos Info CREATE TABLE t7 (a INT, b INT UNIQUE); INSERT INTO t7 SELECT a,b FROM tt3; @@ -137,11 +137,11 @@ a b 1 2 2 4 3 6 -SHOW BINLOG EVENTS FROM 1326; +SHOW BINLOG EVENTS FROM 1256; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 1326 Query 1 1426 use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE) -master-bin.000001 1426 Table_map 1 1466 table_id: # (test.t7) -master-bin.000001 1466 Write_rows 1 1522 table_id: # flags: STMT_END_F +master-bin.000001 1256 Query 1 1356 use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE) +master-bin.000001 1356 Table_map 1 1396 table_id: # (test.t7) +master-bin.000001 1396 Write_rows 1 1452 table_id: # flags: STMT_END_F SELECT * FROM t7 ORDER BY a,b; a b 1 2 @@ -154,10 +154,10 @@ INSERT INTO t7 SELECT a,b FROM tt4; ROLLBACK; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back -SHOW BINLOG EVENTS FROM 1522; +SHOW BINLOG EVENTS FROM 1452; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 1522 Table_map 1 1562 table_id: # (test.t7) -master-bin.000001 1562 Write_rows 1 1618 table_id: # flags: STMT_END_F +master-bin.000001 1452 Table_map 1 1492 table_id: # (test.t7) +master-bin.000001 1492 Write_rows 1 1548 table_id: # flags: STMT_END_F SELECT * FROM t7 ORDER BY a,b; a b 1 2 @@ -191,10 +191,10 @@ Create Table CREATE TABLE `t9` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -SHOW BINLOG EVENTS FROM 1618; +SHOW BINLOG EVENTS FROM 1548; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 1618 Query 1 1704 use `test`; CREATE TABLE t8 LIKE t4 -master-bin.000001 1704 Query 1 1843 use `test`; CREATE TABLE `t9` ( +master-bin.000001 1548 Query 1 1634 use `test`; CREATE TABLE t8 LIKE t4 +master-bin.000001 1634 Query 1 1773 use `test`; CREATE TABLE `t9` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) diff --git a/mysql-test/r/rpl_row_inexist_tbl.result b/mysql-test/r/rpl_row_inexist_tbl.result index 1d18819fdd6..5f5a4556d76 100644 --- a/mysql-test/r/rpl_row_inexist_tbl.result +++ b/mysql-test/r/rpl_row_inexist_tbl.result @@ -5,9 +5,18 @@ reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; create table t1 (a int not null primary key); +insert into t1 values (1); +create table t2 (a int); +insert into t2 values (1); +update t1, t2 set t1.a = 0 where t1.a = t2.a; +show tables; +Tables_in_test +t1 +select * from t1; +a +0 drop table t1; insert into t1 values (1); -insert into t1 values (2); show slave status; Slave_IO_State # Master_Host 127.0.0.1 @@ -24,13 +33,13 @@ Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table -Replicate_Ignore_Table +Replicate_Ignore_Table test.t2 Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1146 Last_Error Error 'Table 'test.t1' doesn't exist' on opening table `test`.`t1` Skip_Counter 0 -Exec_Master_Log_Pos 209 +Exec_Master_Log_Pos 519 Relay_Log_Space # Until_Condition None Until_Log_File @@ -42,4 +51,4 @@ Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master # -drop table t1; +drop table t1, t2; diff --git a/mysql-test/r/rpl_sp_effects.result b/mysql-test/r/rpl_sp_effects.result index 26455b65beb..b42fe64e603 100644 --- a/mysql-test/r/rpl_sp_effects.result +++ b/mysql-test/r/rpl_sp_effects.result @@ -124,9 +124,9 @@ delete from t1; delete from t2; delete from t1; insert into t2 values(1),(2); -create view v1 as select f1(a) from t2; -select * from v1; -f1(a) +create view v1 as select f1(a) as f from t2; +select * from v1 order by f; +f 2 3 SELECT 'master:',a FROM t1 ORDER BY a; diff --git a/mysql-test/r/rpl_truncate_2myisam.result b/mysql-test/r/rpl_truncate_2myisam.result new file mode 100644 index 00000000000..41640a709b9 --- /dev/null +++ b/mysql-test/r/rpl_truncate_2myisam.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; +**** On Master **** +SET SESSION BINLOG_FORMAT=STATEMENT; +SET GLOBAL BINLOG_FORMAT=STATEMENT; +CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +TRUNCATE TABLE t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM +master-bin.000001 210 Query 1 307 use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +master-bin.000001 307 Query 1 387 use `test`; TRUNCATE TABLE t1 +master-bin.000001 387 Query 1 463 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=MIXED; +SET GLOBAL BINLOG_FORMAT=MIXED; +CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +TRUNCATE TABLE t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM +master-bin.000001 210 Query 1 307 use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +master-bin.000001 307 Query 1 387 use `test`; TRUNCATE TABLE t1 +master-bin.000001 387 Query 1 463 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=ROW; +SET GLOBAL BINLOG_FORMAT=ROW; +CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +TRUNCATE TABLE t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM +master-bin.000001 210 Table_map 1 250 table_id: # (test.t1) +master-bin.000001 250 Write_rows 1 297 table_id: # flags: STMT_END_F +master-bin.000001 297 Query 1 377 use `test`; TRUNCATE TABLE t1 +master-bin.000001 377 Query 1 453 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=STATEMENT; +SET GLOBAL BINLOG_FORMAT=STATEMENT; +CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +DELETE FROM t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM +master-bin.000001 210 Query 1 307 use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +master-bin.000001 307 Query 1 384 use `test`; DELETE FROM t1 +master-bin.000001 384 Query 1 460 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=MIXED; +SET GLOBAL BINLOG_FORMAT=MIXED; +CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +DELETE FROM t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM +master-bin.000001 210 Query 1 307 use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +master-bin.000001 307 Query 1 384 use `test`; DELETE FROM t1 +master-bin.000001 384 Query 1 460 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=ROW; +SET GLOBAL BINLOG_FORMAT=ROW; +CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +DELETE FROM t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +3 3 +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=MyISAM +master-bin.000001 210 Table_map 1 250 table_id: # (test.t1) +master-bin.000001 250 Write_rows 1 297 table_id: # flags: STMT_END_F +master-bin.000001 297 Table_map 1 337 table_id: # (test.t1) +master-bin.000001 337 Delete_rows 1 384 table_id: # flags: STMT_END_F +master-bin.000001 384 Query 1 460 use `test`; DROP TABLE t1 diff --git a/mysql-test/r/rpl_truncate_3innodb.result b/mysql-test/r/rpl_truncate_3innodb.result new file mode 100644 index 00000000000..062c9704ae0 --- /dev/null +++ b/mysql-test/r/rpl_truncate_3innodb.result @@ -0,0 +1,214 @@ +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; +**** On Master **** +SET SESSION BINLOG_FORMAT=STATEMENT; +SET GLOBAL BINLOG_FORMAT=STATEMENT; +CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +TRUNCATE TABLE t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB +master-bin.000001 210 Query 1 97 use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +master-bin.000001 307 Xid 1 334 COMMIT /* xid= */ +master-bin.000001 334 Query 1 80 use `test`; TRUNCATE TABLE t1 +master-bin.000001 414 Xid 1 441 COMMIT /* xid= */ +master-bin.000001 441 Query 1 517 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=MIXED; +SET GLOBAL BINLOG_FORMAT=MIXED; +CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +TRUNCATE TABLE t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB +master-bin.000001 210 Query 1 97 use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +master-bin.000001 307 Xid 1 334 COMMIT /* xid= */ +master-bin.000001 334 Query 1 80 use `test`; TRUNCATE TABLE t1 +master-bin.000001 414 Xid 1 441 COMMIT /* xid= */ +master-bin.000001 441 Query 1 517 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=ROW; +SET GLOBAL BINLOG_FORMAT=ROW; +CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +TRUNCATE TABLE t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB +master-bin.000001 210 Table_map 1 40 table_id: # (test.t1) +master-bin.000001 250 Write_rows 1 87 table_id: # flags: STMT_END_F +master-bin.000001 297 Xid 1 324 COMMIT /* xid= */ +master-bin.000001 324 Query 1 80 use `test`; TRUNCATE TABLE t1 +master-bin.000001 404 Xid 1 431 COMMIT /* xid= */ +master-bin.000001 431 Query 1 507 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=STATEMENT; +SET GLOBAL BINLOG_FORMAT=STATEMENT; +CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +DELETE FROM t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB +master-bin.000001 210 Query 1 97 use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +master-bin.000001 307 Xid 1 334 COMMIT /* xid= */ +master-bin.000001 334 Query 1 77 use `test`; DELETE FROM t1 +master-bin.000001 411 Xid 1 438 COMMIT /* xid= */ +master-bin.000001 438 Query 1 514 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=MIXED; +SET GLOBAL BINLOG_FORMAT=MIXED; +CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +DELETE FROM t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB +master-bin.000001 210 Query 1 97 use `test`; INSERT INTO t1 VALUES (1,1), (2,2) +master-bin.000001 307 Xid 1 334 COMMIT /* xid= */ +master-bin.000001 334 Query 1 77 use `test`; DELETE FROM t1 +master-bin.000001 411 Xid 1 438 COMMIT /* xid= */ +master-bin.000001 438 Query 1 514 use `test`; DROP TABLE t1 +**** On Master **** +SET SESSION BINLOG_FORMAT=ROW; +SET GLOBAL BINLOG_FORMAT=ROW; +CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +**** On Master **** +DELETE FROM t1; +SELECT * FROM t1; +a b +**** On Slave **** +SELECT * FROM t1; +a b +3 3 +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 210 use `test`; CREATE TABLE t1 (a INT, b LONG) ENGINE=InnoDB +master-bin.000001 210 Table_map 1 40 table_id: # (test.t1) +master-bin.000001 250 Write_rows 1 87 table_id: # flags: STMT_END_F +master-bin.000001 297 Xid 1 324 COMMIT /* xid= */ +master-bin.000001 324 Table_map 1 40 table_id: # (test.t1) +master-bin.000001 364 Delete_rows 1 87 table_id: # flags: STMT_END_F +master-bin.000001 411 Xid 1 438 COMMIT /* xid= */ +master-bin.000001 438 Query 1 514 use `test`; DROP TABLE t1 diff --git a/mysql-test/r/rpl_truncate_7ndb.result b/mysql-test/r/rpl_truncate_7ndb.result new file mode 100644 index 00000000000..b33f8f1ccaa --- /dev/null +++ b/mysql-test/r/rpl_truncate_7ndb.result @@ -0,0 +1,105 @@ +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; +**** On Master **** +CREATE TABLE t1 (a INT PRIMARY KEY, b LONG) ENGINE=NDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1 ORDER BY a,b; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1 ORDER BY a,b; +a b +1 1 +2 2 +3 3 +**** On Master **** +TRUNCATE TABLE t1; +SELECT * FROM t1 ORDER BY a,b; +a b +**** On Slave **** +SELECT * FROM t1 ORDER BY a,b; +a b +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 219 use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b LONG) ENGINE=NDB +master-bin.000001 219 Query 1 283 BEGIN +master-bin.000001 283 Table_map 1 40 table_id: # (test.t1) +master-bin.000001 323 Table_map 1 93 table_id: # (cluster.apply_status) +master-bin.000001 376 Write_rows 1 135 table_id: # +master-bin.000001 418 Write_rows 1 182 table_id: # flags: STMT_END_F +master-bin.000001 465 Query 1 530 COMMIT +master-bin.000001 530 Query 1 80 use `test`; TRUNCATE TABLE t1 +master-bin.000001 610 Query 1 679 use `test`; COMMIT +master-bin.000001 679 Query 1 743 BEGIN +master-bin.000001 743 Table_map 1 40 table_id: # (test.t1) +master-bin.000001 783 Table_map 1 93 table_id: # (cluster.apply_status) +master-bin.000001 836 Write_rows 1 135 table_id: # +master-bin.000001 878 Delete_rows 1 174 table_id: # flags: STMT_END_F +master-bin.000001 917 Query 1 982 COMMIT +master-bin.000001 982 Query 1 1058 use `test`; DROP TABLE t1 +**** On Master **** +CREATE TABLE t1 (a INT PRIMARY KEY, b LONG) ENGINE=NDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1 ORDER BY a,b; +a b +1 1 +2 2 +**** On Slave **** +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1 ORDER BY a,b; +a b +1 1 +2 2 +3 3 +**** On Master **** +DELETE FROM t1; +SELECT * FROM t1 ORDER BY a,b; +a b +**** On Slave **** +SELECT * FROM t1 ORDER BY a,b; +a b +3 3 +**** On Master **** +DROP TABLE t1; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 102 Server ver: SERVER_VERSION, Binlog ver: 4 +master-bin.000001 102 Query 1 219 use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b LONG) ENGINE=NDB +master-bin.000001 219 Query 1 283 BEGIN +master-bin.000001 283 Table_map 1 40 table_id: # (test.t1) +master-bin.000001 323 Table_map 1 93 table_id: # (cluster.apply_status) +master-bin.000001 376 Write_rows 1 135 table_id: # +master-bin.000001 418 Write_rows 1 182 table_id: # flags: STMT_END_F +master-bin.000001 465 Query 1 530 COMMIT +master-bin.000001 530 Query 1 80 use `test`; TRUNCATE TABLE t1 +master-bin.000001 610 Query 1 679 use `test`; COMMIT +master-bin.000001 679 Query 1 743 BEGIN +master-bin.000001 743 Table_map 1 40 table_id: # (test.t1) +master-bin.000001 783 Table_map 1 93 table_id: # (cluster.apply_status) +master-bin.000001 836 Write_rows 1 135 table_id: # +master-bin.000001 878 Delete_rows 1 174 table_id: # flags: STMT_END_F +master-bin.000001 917 Query 1 982 COMMIT +master-bin.000001 982 Query 1 1058 use `test`; DROP TABLE t1 +master-bin.000001 1058 Query 1 1175 use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b LONG) ENGINE=NDB +master-bin.000001 1175 Query 1 1239 BEGIN +master-bin.000001 1239 Table_map 1 40 table_id: # (test.t1) +master-bin.000001 1279 Table_map 1 93 table_id: # (cluster.apply_status) +master-bin.000001 1332 Write_rows 1 135 table_id: # +master-bin.000001 1374 Write_rows 1 182 table_id: # flags: STMT_END_F +master-bin.000001 1421 Query 1 1486 COMMIT +master-bin.000001 1486 Query 1 1550 BEGIN +master-bin.000001 1550 Table_map 1 40 table_id: # (test.t1) +master-bin.000001 1590 Table_map 1 93 table_id: # (cluster.apply_status) +master-bin.000001 1643 Write_rows 1 135 table_id: # +master-bin.000001 1685 Delete_rows 1 174 table_id: # flags: STMT_END_F +master-bin.000001 1724 Query 1 1789 COMMIT +master-bin.000001 1789 Query 1 1865 use `test`; DROP TABLE t1 diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 5dcb8b2afd6..267993d9354 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -382,9 +382,9 @@ Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length I t1 MEMORY 10 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL t2 MEMORY 10 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL t3 MEMORY 10 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL -delete from t1; -delete from t2; -delete from t3; +truncate table t1; +truncate table t2; +truncate table t3; show table status; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t1 MEMORY 10 Fixed 0 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index fde273c55ee..29206393b04 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -368,7 +368,7 @@ end| insert into t1 values (666, 51.3)| call bug2272()| ERROR 42S22: Unknown column 'v' in 'field list' -delete from t1| +truncate table t1| drop procedure bug2272| create procedure bug2329_1() begin @@ -520,7 +520,7 @@ declare exit handler for sqlexception select 'Error!'; open c; fetch c into v; end| -delete from t1| +truncate table t1| call bug7299()| ERROR 02000: No data - zero rows fetched, selected, or processed drop procedure bug7299| @@ -621,7 +621,7 @@ val x bug8408() 3 3.14 3 7 7 3 drop function bug8408| -delete from t1| +truncate table t1| drop procedure if exists bug10537| create procedure bug10537() load data local infile '/tmp/somefile' into table t1| diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 9bdd1d5645f..39ab34c86bb 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -78,7 +78,7 @@ replace t1 set data = data, id = "bar"; update t1 set id = "kaka", data = 3 where t1.data = data; end| call setcontext()| -select * from t1| +select * from t1 order by data| id data foo 1 kaka 3 @@ -191,7 +191,7 @@ drop procedure if exists inc| create procedure inc(inout io int) set io = io + 1| call iotest("io1", "io2", 1)| -select * from t1| +select * from t1 order by data desc| id data io2 2 io1 1 @@ -224,7 +224,7 @@ set y2 = 4711; insert into test.t1 values ("cbv2", y1); end| call cbv1()| -select * from t1| +select * from t1 order by data| id data cbv2 4 cbv1 4711 @@ -251,7 +251,7 @@ call sub1("sub1c", (select i,d from t2 limit 1))| ERROR 21000: Operand should contain 1 column(s) call sub1("sub1d", (select 1 from (select 1) a))| call sub2("sub2")| -select * from t1| +select * from t1 order by id| id data sub1a 7 sub1b 3 @@ -272,7 +272,7 @@ set x = x-1; insert into test.t1 values ("a0", x); end while| call a0(3)| -select * from t1| +select * from t1 order by data desc| id data a0 2 a0 1 @@ -286,7 +286,7 @@ set x = x-1; insert into test.t1 values ("a", x); end while| call a(3)| -select * from t1| +select * from t1 order by data desc| id data a 2 a 1 @@ -300,7 +300,7 @@ insert into test.t1 values (repeat("b",3), x); set x = x-1; until x = 0 end repeat| call b(3)| -select * from t1| +select * from t1 order by data desc| id data bbb 3 bbb 2 @@ -323,7 +323,7 @@ iterate hmm; insert into test.t1 values ("x", x); end while hmm| call c(3)| -select * from t1| +select * from t1 order by data desc| id data c 3 c 2 @@ -354,7 +354,7 @@ insert into test.t1 values ("e", x); set x = x-1; end loop foo| call e(3)| -select * from t1| +select * from t1 order by data desc| id data e 3 e 2 @@ -373,7 +373,7 @@ end if| call f(-2)| call f(0)| call f(4)| -select * from t1| +select * from t1 order by data| id data f 0 f 1 @@ -393,7 +393,7 @@ end case| call g(-42)| call g(0)| call g(1)| -select * from t1| +select * from t1 order by data| id data g 0 g 1 @@ -413,7 +413,7 @@ end case| call h(0)| call h(1)| call h(17)| -select * from t1| +select * from t1 order by data| id data h0 0 h1 1 @@ -441,7 +441,7 @@ insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)| drop procedure if exists sel1| create procedure sel1() begin -select * from t1; +select * from t1 order by data; end| call sel1()| id data @@ -451,8 +451,8 @@ drop procedure sel1| drop procedure if exists sel2| create procedure sel2() begin -select * from t1; -select * from t2; +select * from t1 order by data; +select * from t2 order by s; end| call sel2()| id data @@ -473,7 +473,7 @@ select id,data into x,y from test.t1 limit 1; insert into test.t1 values (concat(x, "2"), y+2); end| call into_test("into", 100)| -select * from t1| +select * from t1 order by data| id data into 100 into2 102 @@ -487,7 +487,7 @@ select id,data into x,@z from test.t1 limit 1; insert into test.t1 values (concat(x, "2"), y+2); end| call into_test2("into", 100)| -select id,data,@z from t1| +select id,data,@z from t1 order by data| id data @z into 100 100 into2 102 100 @@ -619,14 +619,14 @@ insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))| select * from t2 where s = append("a", "b")| s i d ab 24 1324.36598821719 -select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)| +select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2) order by i| s i d xxxyyy 12 2.71828182845905 ab 24 1324.36598821719 select * from t2 where d = e()| s i d xxxyyy 12 2.71828182845905 -select * from t2| +select * from t2 order by i| s i d xxxyyy 12 2.71828182845905 ab 24 1324.36598821719 @@ -745,8 +745,8 @@ drop procedure if exists cur2| create procedure cur2() begin declare done int default 0; -declare c1 cursor for select id,data from test.t1; -declare c2 cursor for select i from test.t2; +declare c1 cursor for select id,data from test.t1 order by id,data; +declare c2 cursor for select i from test.t2 order by i; declare continue handler for sqlstate '02000' set done = 1; open c1; open c2; @@ -769,10 +769,10 @@ close c1; close c2; end| call cur2()| -select * from t3| +select * from t3 order by i,s| s i -foo 40 bar 3 +foo 40 zap 663 delete from t1| delete from t2| @@ -935,16 +935,16 @@ return (select sum(data) from t1)| select f1()| f1() 3 -select id, f1() from t1| +select id, f1() from t1 order by id| id f1() a 3 b 3 create function f2() returns int -return (select data from t1 where data <= (select sum(data) from t1) limit 1)| +return (select data from t1 where data <= (select sum(data) from t1) order by data limit 1)| select f2()| f2() 1 -select id, f2() from t1| +select id, f2() from t1 order by id| id f2() a 1 b 1 @@ -959,14 +959,14 @@ end| select f3()| f3() 1 -select id, f3() from t1| +select id, f3() from t1 order by id| id f3() a 1 b 1 select f1(), f3()| f1() f3() 3 1 -select id, f1(), f3() from t1| +select id, f1(), f3() from t1 order by id| id f1() f3() a 3 1 b 3 1 @@ -975,7 +975,7 @@ return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| select f4()| f4() 2 -select s, f4() from t2| +select s, f4() from t2 order by s| s f4() a 2 b 2 @@ -1008,7 +1008,7 @@ return (select sum(data) from t1 where data <= f1())| select f6()| f6() 2 -select id, f6() from t1| +select id, f6() from t1 order by id| id f6() a 2 b 2 @@ -1016,7 +1016,7 @@ create view v1 (a) as select f1()| select * from v1| a 3 -select id, a from t1, v1| +select id, a from t1, v1 order by id| id a a 3 b 3 @@ -1027,7 +1027,7 @@ create view v2 (a) as select a*10 from v1| select * from v2| a 30 -select id, a from t1, v2| +select id, a from t1, v2 order by id| id a a 30 b 30 @@ -1072,7 +1072,7 @@ lock tables t1 read, t1 as t11 read| select f3()| f3() 1 -select id, f3() from t1 as t11| +select id, f3() from t1 as t11 order by id| id f3() a 1 b 1 @@ -1175,8 +1175,8 @@ drop function f12_2| drop view v0| drop view v1| drop view v2| -delete from t1 | -delete from t2 | +truncate table t1 | +truncate table t2 | drop table t4| drop table if exists t3| create table t3 (n int unsigned not null primary key, f bigint unsigned)| @@ -1355,7 +1355,7 @@ f 1 1 2 -delete from t3| +truncate table t3| insert into t3 values (0), (1)| call fib(10)| select * from t3 order by f asc| @@ -1814,7 +1814,7 @@ delete from t1| call bug822('foo', 42)| call bug822('foo', 42)| call bug822('bar', 666)| -select * from t1| +select * from t1 order by data| id data foo 42 bar 666 @@ -1837,7 +1837,7 @@ delete from t1 where id='foo'| insert into t1 values ('bar', 7)| call bug1495()| delete from t1 where id='bar'| -select * from t1| +select * from t1 order by data| id data less 2 more 17 @@ -1857,10 +1857,10 @@ end| insert into t1 values ("foo", 12), ("bar", 7)| call bug1547("foo")| call bug1547("bar")| -select * from t1| +select * from t1 order by id| id data -foo 12 bar 7 +foo 12 less 2 more 17 delete from t1| @@ -1908,12 +1908,12 @@ insert into t2 values ("avg", 0, y); end| insert into t1 (data) values (3), (1), (5), (9), (4)| call bug1874()| -select * from t2| +select * from t2 order by i| s i d -max 9 0 +avg 0 4.4 min 1 0 +max 9 0 sum 22 0 -avg 0 4.4 delete from t1| delete from t2| drop procedure bug1874| @@ -4466,7 +4466,7 @@ Error 1347 'test.v1' is not BASE TABLE Error 1347 'test.v1' is not BASE TABLE drop procedure bug13012| drop view v1; -select * from t1| +select * from t1 order by data| id data aa 0 aa 1 @@ -4819,7 +4819,7 @@ insert into t1 values ('c', 2), ('b', 3), ('a', 1)| create procedure bug16474_1() begin declare x int; -select id from t1 order by x; +select id from t1 order by x, id; end| drop procedure if exists bug14945| create table t3 (id int not null auto_increment primary key)| @@ -4833,30 +4833,30 @@ id drop table t3| drop procedure bug14945| create procedure bug16474_2(x int) -select id from t1 order by x| +select id from t1 order by x, id| call bug16474_1()| id -c -b a +b +c call bug16474_2(1)| id -c -b a +b +c call bug16474_2(2)| id -c -b a +b +c drop procedure bug16474_1| drop procedure bug16474_2| set @x = 2| -select * from t1 order by @x| +select * from t1 order by @x, data| id data +a 1 c 2 b 3 -a 1 delete from t1| drop function if exists bug15728| drop table if exists t3| diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result index fe9e971af2f..af8d52a017d 100644 --- a/mysql-test/r/timezone2.result +++ b/mysql-test/r/timezone2.result @@ -70,7 +70,7 @@ i ts 1067129999 2003-10-26 00:59:59 1067137200 2003-10-26 03:00:00 1067129999 2003-10-26 00:59:59 -delete from t1; +truncate table t1; set time_zone='Europe/Moscow'; insert into t1 (i, ts) values (unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'), @@ -85,7 +85,7 @@ i ts 1080428400 2004-03-28 03:00:00 1091304000 2003-08-01 00:00:00 1099175400 2004-10-31 02:30:00 -delete from t1; +truncate table t1; set time_zone='leap/Europe/Moscow'; insert into t1 (i, ts) values (unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'), @@ -100,7 +100,7 @@ i ts 1080428422 2004-03-28 03:00:00 1091304022 2003-08-01 00:00:00 1099175422 2004-10-31 02:30:00 -delete from t1; +truncate table t1; insert into t1 (i, ts) values (unix_timestamp('1981-07-01 03:59:59'),'1981-07-01 03:59:59'), (unix_timestamp('1981-07-01 04:00:00'),'1981-07-01 04:00:00'); @@ -129,7 +129,7 @@ ts 1970-01-01 00:00:01 2037-12-31 23:59:59 0000-00-00 00:00:00 -delete from t1; +truncate table t1; set time_zone='MET'; insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 00:30:00'), ('1970-01-01 01:00:00'),('1970-01-01 01:00:01'), @@ -146,7 +146,7 @@ ts 1970-01-01 01:00:01 2038-01-01 00:59:59 0000-00-00 00:00:00 -delete from t1; +truncate table t1; set time_zone='+01:30'; insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 01:00:00'), ('1970-01-01 01:30:00'),('1970-01-01 01:30:01'), diff --git a/mysql-test/r/trigger-grant.result b/mysql-test/r/trigger-grant.result index fec5d4812d9..eb211be0270 100644 --- a/mysql-test/r/trigger-grant.result +++ b/mysql-test/r/trigger-grant.result @@ -51,8 +51,8 @@ GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost; ---> connection: wl2818_definer_con INSERT INTO t1 VALUES(0); DROP TRIGGER trg1; -DELETE FROM t1; -DELETE FROM t2; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; ---> connection: default REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 5eba97a9eda..6f1a45f27c5 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -151,7 +151,7 @@ insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 Warnings: Warning 1264 Out of range value for column 't' at row 1 Warning 1264 Out of range value for column 't' at row 2 -select * from t1; +select * from t1 order by t; t 0000-00-00 00:00:00 2003-01-01 00:00:00 diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index ba401a22945..df3298bad1a 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -323,7 +323,7 @@ select * from t3; id_A id_B 1 1 2 NULL -delete from t3; +truncate table t3; insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id); select * from t3; id_A id_B diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 95ec7526473..6bec1e2b46b 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -59,7 +59,7 @@ ix+0 19990501000000 19991101000000 19990501000000 -delete from t1; +truncate table t1; insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"); select ix+0 from t1; ix+0 @@ -140,7 +140,7 @@ ix+0 0 0 0 -delete from t1; +truncate table t1; insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000"); Warnings: Warning 1265 Data truncated for column 'ix' at row 2 @@ -160,7 +160,7 @@ ix+0 0 0 0 -delete from t1; +truncate table t1; insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer"); Warnings: Warning 1265 Data truncated for column 'ix' at row 1 @@ -329,7 +329,7 @@ show columns from t1; Field Type Null Key Default Extra t1 timestamp YES CURRENT_TIMESTAMP t2 datetime YES NULL -delete from t1; +truncate table t1; insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00'); SET TIMESTAMP=1000000012; update t1 set t1= '2004-04-02 00:00:00'; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 9fabb0ad2d8..7f3c0f79520 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1106,21 +1106,21 @@ insert ignore into v1 SELECT a from t2; Warnings: Error 1369 CHECK OPTION failed 'test.v1' Error 1369 CHECK OPTION failed 'test.v1' -select * from t1; +select * from t1 order by a desc; a 1 0 update v1 set a=-1 where a=0; update v1 set a=2 where a=1; ERROR HY000: CHECK OPTION failed 'test.v1' -select * from t1; +select * from t1 order by a desc; a 1 -1 update v1 set a=0 where a=0; insert into t2 values (1); update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a; -select * from t1; +select * from t1 order by a desc; a 0 -1 @@ -1310,12 +1310,12 @@ Warning 1264 Out of range value for column 'a' at row 3 Error 1369 CHECK OPTION failed 'test.v1' Warning 1264 Out of range value for column 'a' at row 4 Error 1369 CHECK OPTION failed 'test.v1' -select * from t1; +select * from t1 order by a,b; a b 1 row 1 2 row 2 3 row 3 -select * from v1; +select * from v1 order by a,b; a b 1 row 1 2 row 2 diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index c89d31c69b9..4ff7c5e6cda 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -3,6 +3,7 @@ # Taken FROM the select test # -- source include/have_archive.inc +-- source include/have_binlog_format_statement.inc --disable_warnings drop table if exists t1,t2,t3; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index e4a7d1cd9af..714e3844e66 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -670,14 +670,6 @@ alter table t1 max_rows=100000000000; show create table t1; drop table t1; -# -# Bug#17530: Incorrect key truncation on table creation caused server crash. -# -create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb - character set utf8 collate utf8_general_ci; -insert into t1 values('aaa'); -drop table t1; - # End of 5.0 tests # diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 96f31133e65..a815d538e57 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -36,7 +36,6 @@ rpl_ndb_myisam2ndb : Bug #19710 Cluster replication to partition table fa rpl_switch_stm_row_mixed : BUG#18590 2006-03-28 brian rpl_row_blob_innodb : BUG#18980 2006-04-10 kent Test fails randomly rpl_row_func003 : BUG#19074 2006-13-04 andrei test failed -rpl_row_inexist_tbl : BUG#18948 2006-03-09 mats Disabled since patch makes this test wait forever rpl_sp : BUG#16456 2006-02-16 jmiller # the below testcase have been reworked to avoid the bug, test contains comment, keep bug open diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index f6799f3630a..5f5c8d44f35 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -83,7 +83,7 @@ eval SHOW CREATE TABLE federated.t2; INSERT INTO federated.t2 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t2 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t2; +SELECT * FROM federated.t2 ORDER BY id, name; DROP TABLE federated.t2; connection slave; @@ -110,7 +110,7 @@ eval CREATE TABLE federated.t1 ( INSERT INTO federated.t1 (id, name) VALUES (1, 'foo'); INSERT INTO federated.t1 (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.t1; +SELECT * FROM federated.t1 ORDER BY id,name; DELETE FROM federated.t1; DROP TABLE IF EXISTS federated.t1; @@ -125,7 +125,7 @@ eval CREATE TABLE federated.`t1%` ( INSERT INTO federated.`t1%` (id, name) VALUES (1, 'foo'); INSERT INTO federated.`t1%` (id, name) VALUES (2, 'fee'); -SELECT * FROM federated.`t1%`; +SELECT * FROM federated.`t1%` ORDER BY id, name; DELETE FROM federated.`t1%`; DROP TABLE IF EXISTS federated.`t1%`; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 92e060eed92..5b1b374e487 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -2510,3 +2510,16 @@ BEGIN; INSERT INTO t1 VALUES (1); OPTIMIZE TABLE t1; DROP TABLE t1; + +####################################################################### +# # +# Please, DO NOT TOUCH this file as well as the innodb.result file. # +# These files are to be modified ONLY BY INNOBASE guys. # +# # +# Use innodb_mysql.[test|result] files instead. # +# # +# If nevertheless you need to make some changes here, please, forward # +# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com # +# (otherwise your changes may be erased). # +# # +####################################################################### diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 0a9eeb049b0..65e2cc658f5 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -4,6 +4,14 @@ drop table if exists t1,t2,t1m,t1i,t2m,t2i,t4; --enable_warnings +# +# Bug#17530: Incorrect key truncation on table creation caused server crash. +# +create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb + character set utf8 collate utf8_general_ci; +insert into t1 values('aaa'); +drop table t1; + # BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer # (repeatable only w/innodb). create table t1 ( diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index f6493bac244..d1ce1104322 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -370,7 +370,7 @@ insert into t2(uid, name) values (25, CHAR(64+25)), (26, CHAR(64+26)); -insert into t1(uid, name) select uid, name from t2; +insert into t1(uid, name) select uid, name from t2 order by uid; delete from t2; insert into t2(id, uid, name) select id, uid, name from t1; diff --git a/mysql-test/t/rpl_rbr_to_sbr.test b/mysql-test/t/rpl_rbr_to_sbr.test index f04caf05057..c10199f8ff5 100644 --- a/mysql-test/t/rpl_rbr_to_sbr.test +++ b/mysql-test/t/rpl_rbr_to_sbr.test @@ -1,3 +1,4 @@ +-- source include/have_row_based.inc -- source include/have_binlog_format_statement.inc -- source include/master-slave.inc diff --git a/mysql-test/t/rpl_row_basic_11bugs.test b/mysql-test/t/rpl_row_basic_11bugs.test index 3a686ea6b3d..bd582ce349a 100644 --- a/mysql-test/t/rpl_row_basic_11bugs.test +++ b/mysql-test/t/rpl_row_basic_11bugs.test @@ -36,3 +36,18 @@ USE test_ignore; connection master; DROP DATABASE test_ignore; sync_slave_with_master; + +# Bug#19995: Extreneous table maps generated for statements that does +# not generate rows +--disable_query_log +--source include/master-slave-reset.inc +--enable_query_log + +connection master; +CREATE TABLE t1 (a INT); +DELETE FROM t1; +INSERT INTO t1 VALUES (1),(2); +DELETE FROM t1 WHERE a = 0; +UPDATE t1 SET a=99 WHERE a = 0; +--replace_regex /table_id: [0-9]+/table_id: #/ +SHOW BINLOG EVENTS; diff --git a/mysql-test/t/rpl_row_basic_8partition.test b/mysql-test/t/rpl_row_basic_8partition.test index 8cad2226d4a..640a420c10e 100644 --- a/mysql-test/t/rpl_row_basic_8partition.test +++ b/mysql-test/t/rpl_row_basic_8partition.test @@ -7,6 +7,7 @@ # partition tables with same engine (MyISAM) in both ends. # ############################################################ +--source include/have_row_based.inc --source include/have_partition.inc --source include/master-slave.inc connection master; diff --git a/mysql-test/t/rpl_row_create_table.test b/mysql-test/t/rpl_row_create_table.test index 0cb0fd766a3..8a8ea01d688 100644 --- a/mysql-test/t/rpl_row_create_table.test +++ b/mysql-test/t/rpl_row_create_table.test @@ -67,7 +67,7 @@ connection master; CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3; # Shouldn't be written to the binary log --replace_regex /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS FROM 1326; +SHOW BINLOG EVENTS FROM 1256; # Test that INSERT-SELECT works the same way as for SBR. CREATE TABLE t7 (a INT, b INT UNIQUE); @@ -76,7 +76,7 @@ INSERT INTO t7 SELECT a,b FROM tt3; SELECT * FROM t7 ORDER BY a,b; # Should be written to the binary log --replace_regex /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS FROM 1326; +SHOW BINLOG EVENTS FROM 1256; sync_slave_with_master; SELECT * FROM t7 ORDER BY a,b; @@ -87,7 +87,7 @@ BEGIN; INSERT INTO t7 SELECT a,b FROM tt4; ROLLBACK; --replace_regex /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS FROM 1522; +SHOW BINLOG EVENTS FROM 1452; SELECT * FROM t7 ORDER BY a,b; sync_slave_with_master; SELECT * FROM t7 ORDER BY a,b; @@ -101,7 +101,7 @@ CREATE TEMPORARY TABLE tt6 LIKE tt4; --query_vertical SHOW CREATE TABLE t8 --query_vertical SHOW CREATE TABLE t9 --replace_regex /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS FROM 1618; +SHOW BINLOG EVENTS FROM 1548; sync_slave_with_master; --echo **** On Slave **** --query_vertical SHOW CREATE TABLE t8 diff --git a/mysql-test/t/rpl_row_inexist_tbl-slave.opt b/mysql-test/t/rpl_row_inexist_tbl-slave.opt new file mode 100644 index 00000000000..abd717f8222 --- /dev/null +++ b/mysql-test/t/rpl_row_inexist_tbl-slave.opt @@ -0,0 +1 @@ +--replicate-ignore-table=test.t2 diff --git a/mysql-test/t/rpl_row_inexist_tbl.test b/mysql-test/t/rpl_row_inexist_tbl.test index e77ea216571..741cc8b7ba3 100644 --- a/mysql-test/t/rpl_row_inexist_tbl.test +++ b/mysql-test/t/rpl_row_inexist_tbl.test @@ -6,15 +6,23 @@ source include/master-slave.inc; connection master; create table t1 (a int not null primary key); +insert into t1 values (1); +create table t2 (a int); +insert into t2 values (1); +update t1, t2 set t1.a = 0 where t1.a = t2.a; + sync_slave_with_master; +# t2 should not have been replicated +# t1 should have been properly updated +show tables; +select * from t1; drop table t1; connection master; insert into t1 values (1); -insert into t1 values (2); connection slave; -# slave should have stopped because can't find table +# slave should have stopped because can't find table t1 wait_for_slave_to_stop; # see if we have a good error message: --replace_result $MASTER_MYPORT MASTER_MYPORT @@ -24,4 +32,4 @@ show slave status; # cleanup connection master; -drop table t1; +drop table t1, t2; diff --git a/mysql-test/t/rpl_sp_effects.test b/mysql-test/t/rpl_sp_effects.test index 40c9a5d0b74..e1746682b76 100644 --- a/mysql-test/t/rpl_sp_effects.test +++ b/mysql-test/t/rpl_sp_effects.test @@ -106,8 +106,8 @@ delete from t2; # 4. VIEWs delete from t1; insert into t2 values(1),(2); -create view v1 as select f1(a) from t2; -select * from v1; +create view v1 as select f1(a) as f from t2; +select * from v1 order by f; SELECT 'master:',a FROM t1 ORDER BY a; sync_slave_with_master; diff --git a/mysql-test/t/rpl_truncate_2myisam.test b/mysql-test/t/rpl_truncate_2myisam.test new file mode 100644 index 00000000000..1a2cb1d0fb3 --- /dev/null +++ b/mysql-test/t/rpl_truncate_2myisam.test @@ -0,0 +1,4 @@ + +let $engine=MyISAM; +--source extra/rpl_tests/rpl_truncate.test + diff --git a/mysql-test/t/rpl_truncate_3innodb.test b/mysql-test/t/rpl_truncate_3innodb.test new file mode 100644 index 00000000000..7f3145feb1b --- /dev/null +++ b/mysql-test/t/rpl_truncate_3innodb.test @@ -0,0 +1,5 @@ + +--source include/have_innodb.inc + +let $engine=InnoDB; +--source extra/rpl_tests/rpl_truncate.test diff --git a/mysql-test/t/rpl_truncate_7ndb.test b/mysql-test/t/rpl_truncate_7ndb.test new file mode 100644 index 00000000000..7a6151c6028 --- /dev/null +++ b/mysql-test/t/rpl_truncate_7ndb.test @@ -0,0 +1,66 @@ + +--source include/have_ndb.inc +--source include/master-slave.inc + +--disable_query_log +--disable_warnings +connection slave; +STOP SLAVE; +connection master; +DROP TABLE IF EXISTS t1; +RESET MASTER; +connection slave; +DROP TABLE IF EXISTS t1; +RESET SLAVE; +START SLAVE; +--enable_warnings +--enable_query_log + +--echo **** On Master **** +connection master; +CREATE TABLE t1 (a INT PRIMARY KEY, b LONG) ENGINE=NDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1 ORDER BY a,b; +--echo **** On Slave **** +sync_slave_with_master; +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1 ORDER BY a,b; +--echo **** On Master **** +connection master; +TRUNCATE TABLE t1; +SELECT * FROM t1 ORDER BY a,b; +--echo **** On Slave **** +sync_slave_with_master; +# Should be empty +SELECT * FROM t1 ORDER BY a,b; +--echo **** On Master **** +connection master; +DROP TABLE t1; +--replace_regex /table_id: [0-9]+/table_id: #/ +--replace_result $SERVER_VERSION SERVER_VERSION +SHOW BINLOG EVENTS; + +--echo **** On Master **** +connection master; +CREATE TABLE t1 (a INT PRIMARY KEY, b LONG) ENGINE=NDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1 ORDER BY a,b; +--echo **** On Slave **** +sync_slave_with_master; +INSERT INTO t1 VALUE (3,3); +SELECT * FROM t1 ORDER BY a,b; +--echo **** On Master **** +connection master; +DELETE FROM t1; +SELECT * FROM t1 ORDER BY a,b; +--echo **** On Slave **** +sync_slave_with_master; +# Should be empty +SELECT * FROM t1 ORDER BY a,b; +--echo **** On Master **** +connection master; +DROP TABLE t1; +--replace_regex /table_id: [0-9]+/table_id: #/ +--replace_result $SERVER_VERSION SERVER_VERSION +SHOW BINLOG EVENTS; + diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index a7b24a5e06c..2f44f4e97c5 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -255,9 +255,9 @@ delete from t2 where b=3; delete from t3 where a=3; --replace_column 6 # 7 # 8 # 9 # 10 # show table status; -delete from t1; -delete from t2; -delete from t3; +truncate table t1; +truncate table t2; +truncate table t3; --replace_column 6 # 7 # 8 # 9 # show table status; insert into t1 values (5); diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index c9ce26b6dda..24b0d4674c5 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -508,7 +508,7 @@ end| insert into t1 values (666, 51.3)| --error 1054 call bug2272()| -delete from t1| +truncate table t1| drop procedure bug2272| # @@ -742,7 +742,7 @@ begin fetch c into v; end| -delete from t1| +truncate table t1| --error ER_SP_FETCH_NO_DATA call bug7299()| drop procedure bug7299| @@ -871,7 +871,7 @@ insert into t1 value (2, 2.7), (3, 3.14), (7, 7.0)| select *,bug8408() from t1| drop function bug8408| -delete from t1| +truncate table t1| # diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 8f5fcdc9d52..e3d4e1c9834 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -143,7 +143,7 @@ begin end| call setcontext()| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure setcontext| @@ -287,7 +287,7 @@ create procedure inc(inout io int) set io = io + 1| call iotest("io1", "io2", 1)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure iotest| drop procedure inc2| @@ -333,7 +333,7 @@ begin end| call cbv1()| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure cbv1| drop procedure cbv2| @@ -371,7 +371,7 @@ call sub1("sub1b", (select max(i) from t2))| call sub1("sub1c", (select i,d from t2 limit 1))| call sub1("sub1d", (select 1 from (select 1) a))| call sub2("sub2")| -select * from t1| +select * from t1 order by id| select sub3((select max(i) from t2))| drop procedure sub1| drop procedure sub2| @@ -392,7 +392,7 @@ while x do end while| call a0(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure a0| @@ -408,7 +408,7 @@ while x > 0 do end while| call a(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure a| @@ -424,7 +424,7 @@ repeat until x = 0 end repeat| call b(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure b| @@ -456,7 +456,7 @@ hmm: while x > 0 do end while hmm| call c(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure c| @@ -493,7 +493,7 @@ foo: loop end loop foo| call e(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure e| @@ -514,7 +514,7 @@ end if| call f(-2)| call f(0)| call f(4)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure f| @@ -536,7 +536,7 @@ end case| call g(-42)| call g(0)| call g(1)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure g| @@ -558,7 +558,7 @@ end case| call h(0)| call h(1)| call h(17)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure h| @@ -592,7 +592,7 @@ drop procedure if exists sel1| --enable_warnings create procedure sel1() begin - select * from t1; + select * from t1 order by data; end| call sel1()| @@ -603,8 +603,8 @@ drop procedure if exists sel2| --enable_warnings create procedure sel2() begin - select * from t1; - select * from t2; + select * from t1 order by data; + select * from t2 order by s; end| call sel2()| @@ -624,7 +624,7 @@ begin end| call into_test("into", 100)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure into_test| @@ -641,7 +641,7 @@ begin end| call into_test2("into", 100)| -select id,data,@z from t1| +select id,data,@z from t1 order by data| delete from t1| drop procedure into_test2| @@ -819,9 +819,9 @@ insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))| # Disable PS because double's give a bit different values --disable_ps_protocol select * from t2 where s = append("a", "b")| -select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)| +select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2) order by i| select * from t2 where d = e()| -select * from t2| +select * from t2 order by i| --enable_ps_protocol delete from t2| @@ -976,8 +976,8 @@ drop procedure if exists cur2| create procedure cur2() begin declare done int default 0; - declare c1 cursor for select id,data from test.t1; - declare c2 cursor for select i from test.t2; + declare c1 cursor for select id,data from test.t1 order by id,data; + declare c2 cursor for select i from test.t2 order by i; declare continue handler for sqlstate '02000' set done = 1; open c1; @@ -1003,7 +1003,7 @@ begin end| call cur2()| -select * from t3| +select * from t3 order by i,s| delete from t1| delete from t2| drop table t3| @@ -1178,13 +1178,13 @@ create function f1() returns int return (select sum(data) from t1)| select f1()| # This should work too (and give 2 rows as result) -select id, f1() from t1| +select id, f1() from t1 order by id| # Function which uses two instances of table simultaneously create function f2() returns int - return (select data from t1 where data <= (select sum(data) from t1) limit 1)| + return (select data from t1 where data <= (select sum(data) from t1) order by data limit 1)| select f2()| -select id, f2() from t1| +select id, f2() from t1 order by id| # Function which uses the same table twice in different queries create function f3() returns int @@ -1196,17 +1196,17 @@ begin return n < m; end| select f3()| -select id, f3() from t1| +select id, f3() from t1 order by id| # Calling two functions using same table select f1(), f3()| -select id, f1(), f3() from t1| +select id, f1(), f3() from t1 order by id| # Function which uses two different tables create function f4() returns double return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| select f4()| -select s, f4() from t2| +select s, f4() from t2 order by s| # Recursive functions which due to this recursion require simultaneous # access to several instance of the same table won't work @@ -1239,7 +1239,7 @@ end| create function f7() returns int return (select sum(data) from t1 where data <= f1())| select f6()| -select id, f6() from t1| +select id, f6() from t1 order by id| # # Let us test how new locking work with views @@ -1247,12 +1247,12 @@ select id, f6() from t1| # The most trivial view create view v1 (a) as select f1()| select * from v1| -select id, a from t1, v1| +select id, a from t1, v1 order by id| select * from v1, v1 as v| # A bit more complex construction create view v2 (a) as select a*10 from v1| select * from v2| -select id, a from t1, v2| +select id, a from t1, v2 order by id| select * from v1, v2| # Nice example where the same view is used on @@ -1297,7 +1297,7 @@ select *, f0() from v0| lock tables t1 read, t1 as t11 read| # These should work well select f3()| -select id, f3() from t1 as t11| +select id, f3() from t1 as t11 order by id| # Degenerate cases work too :) select f0()| select * from v0| @@ -1407,8 +1407,8 @@ drop function f12_2| drop view v0| drop view v1| drop view v2| -delete from t1 | -delete from t2 | +truncate table t1 | +truncate table t2 | drop table t4| # End of non-bug tests @@ -1596,7 +1596,7 @@ call fib(3)| select * from t3 order by f asc| -delete from t3| +truncate table t3| # The original test, 20 levels, ran into memory limits on some machines # and builds. Try 10 instead... @@ -1693,7 +1693,6 @@ begin end if; return x; end| - select * from t1 where data = getcount("bar")| select * from t3| select getcount("zip")| @@ -2120,7 +2119,7 @@ delete from t1| call bug822('foo', 42)| call bug822('foo', 42)| call bug822('bar', 666)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure bug822| @@ -2148,7 +2147,7 @@ delete from t1 where id='foo'| insert into t1 values ('bar', 7)| call bug1495()| delete from t1 where id='bar'| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure bug1495| @@ -2173,7 +2172,7 @@ end| insert into t1 values ("foo", 12), ("bar", 7)| call bug1547("foo")| call bug1547("bar")| -select * from t1| +select * from t1 order by id| delete from t1| drop procedure bug1547| @@ -2240,7 +2239,7 @@ end| insert into t1 (data) values (3), (1), (5), (9), (4)| call bug1874()| -select * from t2| +select * from t2 order by i| delete from t1| delete from t2| drop procedure bug1874| @@ -5260,7 +5259,7 @@ call bug13012()| call bug13012()| drop procedure bug13012| drop view v1; -select * from t1| +select * from t1 order by data| # # A test case for Bug#15392 "Server crashes during prepared statement @@ -5669,7 +5668,7 @@ create procedure bug16474_1() begin declare x int; - select id from t1 order by x; + select id from t1 order by x, id; end| # @@ -5689,7 +5688,7 @@ drop procedure bug14945| # This does NOT order by column index; variable is an expression. create procedure bug16474_2(x int) - select id from t1 order by x| + select id from t1 order by x, id| call bug16474_1()| call bug16474_2(1)| @@ -5699,7 +5698,7 @@ drop procedure bug16474_2| # For reference: user variables are expressions too and do not affect ordering. set @x = 2| -select * from t1 order by @x| +select * from t1 order by @x, data| delete from t1| diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test index bfc909d6995..bad1df554d9 100644 --- a/mysql-test/t/timezone2.test +++ b/mysql-test/t/timezone2.test @@ -69,7 +69,7 @@ set time_zone='UTC'; select * from t1; -delete from t1; +truncate table t1; # Simple check for 'Europe/Moscow' time zone just for showing that it works set time_zone='Europe/Moscow'; @@ -79,7 +79,7 @@ insert into t1 (i, ts) values (unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'), (unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00'); select * from t1; -delete from t1; +truncate table t1; # @@ -94,7 +94,7 @@ insert into t1 (i, ts) values (unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'), (unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00'); select * from t1; -delete from t1; +truncate table t1; # Let us test leap jump insert into t1 (i, ts) values (unix_timestamp('1981-07-01 03:59:59'),'1981-07-01 03:59:59'), @@ -115,14 +115,14 @@ insert into t1 values ('0000-00-00 00:00:00'),('1969-12-31 23:59:59'), ('1970-01-01 00:00:00'),('1970-01-01 00:00:01'), ('2037-12-31 23:59:59'),('2038-01-01 00:00:00'); select * from t1; -delete from t1; +truncate table t1; # MET time zone has range shifted by one hour set time_zone='MET'; insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 00:30:00'), ('1970-01-01 01:00:00'),('1970-01-01 01:00:01'), ('2038-01-01 00:59:59'),('2038-01-01 01:00:00'); select * from t1; -delete from t1; +truncate table t1; # same for +01:30 time zone set time_zone='+01:30'; insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 01:00:00'), diff --git a/mysql-test/t/trigger-grant.test b/mysql-test/t/trigger-grant.test index 8ca8ffe904b..6dd0c83dc92 100644 --- a/mysql-test/t/trigger-grant.test +++ b/mysql-test/t/trigger-grant.test @@ -151,8 +151,8 @@ INSERT INTO t1 VALUES(0); # Cleanup for further tests. DROP TRIGGER trg1; -DELETE FROM t1; -DELETE FROM t2; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; --disconnect wl2818_definer_con diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 4b6741b4242..f8953686c89 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -98,7 +98,7 @@ insert into t1 values select * from t1; delete from t1; insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer"); -select * from t1; +select * from t1 order by t; drop table t1; # diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test index 03ee91f14d8..4a897c1e440 100644 --- a/mysql-test/t/type_ranges.test +++ b/mysql-test/t/type_ranges.test @@ -162,7 +162,7 @@ select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id); create table t3 (id_A integer unsigned not null, id_B integer unsigned null ); insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id ); select * from t3; -delete from t3; +truncate table t3; insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id); select * from t3; drop table t3; diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index f96beedbebc..9b09421dd1f 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -41,7 +41,7 @@ drop table t1; create table t1 (ix timestamp); insert into t1 values (19991101000000),(19990102030405),(19990630232922),(19990601000000),(19990930232922),(19990531232922),(19990501000000),(19991101000000),(19990501000000); select ix+0 from t1; -delete from t1; +truncate table t1; insert into t1 values ("19991101000000"),("19990102030405"),("19990630232922"),("19990601000000"); select ix+0 from t1; drop table t1; @@ -82,10 +82,10 @@ drop table t1; create table t1 (ix timestamp); insert into t1 values (0),(20030101010160),(20030101016001),(20030101240101),(20030132010101),(20031301010101),(20031200000000),(20030000000000); select ix+0 from t1; -delete from t1; +truncate table t1; insert into t1 values ("00000000000000"),("20030101010160"),("20030101016001"),("20030101240101"),("20030132010101"),("20031301010101"),("20031200000000"),("20030000000000"); select ix+0 from t1; -delete from t1; +truncate table t1; insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer"); select ix+0 from t1; drop table t1; @@ -186,7 +186,7 @@ insert into t1 (t1) values (default); select * from t1; show create table t1; show columns from t1; -delete from t1; +truncate table t1; # # Let us test some cases when auto-set should be disabled or influence diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 0db97f6d4af..b432a4c39ca 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1035,18 +1035,18 @@ create table t2 (a int); insert into t2 values (2),(3),(0); # INSERT SELECT with ignore test insert ignore into v1 SELECT a from t2; -select * from t1; +select * from t1 order by a desc; #simple UPDATE test update v1 set a=-1 where a=0; -- error 1369 update v1 set a=2 where a=1; -select * from t1; +select * from t1 order by a desc; # prepare data for next check update v1 set a=0 where a=0; insert into t2 values (1); # multiupdate test update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a; -select * from t1; +select * from t1 order by a desc; # prepare data for next check update v1 set a=a+1; # multiupdate with ignore test @@ -1226,8 +1226,8 @@ select * from t1; select * from v1; delete from t1; load data infile '../std_data_ln/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines; -select * from t1; -select * from v1; +select * from t1 order by a,b; +select * from v1 order by a,b; drop view v1; drop table t1; # variable length fields |