diff options
38 files changed, 865 insertions, 214 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..c6defefa986 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_truncate_helper.inc @@ -0,0 +1,40 @@ + +--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_regex /table_id: [0-9]+/table_id: #/ +SHOW BINLOG EVENTS; diff --git a/mysql-test/r/binlog_row_mix_innodb_myisam.result b/mysql-test/r/binlog_row_mix_innodb_myisam.result index 078a95d5abd..d136c710642 100644 --- a/mysql-test/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/r/binlog_row_mix_innodb_myisam.result @@ -263,26 +263,26 @@ 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 Table_map 1 # table_id: # (test.t2) +master-bin.000001 487 Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 516 Xid 1 # COMMIT /* xid= */ +master-bin.000001 543 Query 1 # use `test`; alter table t2 engine=MyISAM +master-bin.000001 634 Table_map 1 # table_id: # (test.t1) +master-bin.000001 673 Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 707 Xid 1 # COMMIT /* xid= */ +master-bin.000001 734 Query 1 # use `test`; BEGIN +master-bin.000001 802 Table_map 1 # table_id: # (test.t1) +master-bin.000001 841 Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 870 Xid 1 # COMMIT /* xid= */ +master-bin.000001 897 Query 1 # use `test`; drop table t1,t2 +master-bin.000001 976 Query 1 # use `test`; create table t0 (n int) +master-bin.000001 1062 Table_map 1 # table_id: # (test.t0) +master-bin.000001 1101 Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 1135 Table_map 1 # table_id: # (test.t0) +master-bin.000001 1174 Write_rows 1 # table_id: # flags: STMT_END_F +master-bin.000001 1208 Query 1 # use `test`; create table t2 (n int) engine=innodb do release_lock("lock1"); drop table t0,t2; reset master; diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index 5f735ebe926..7828994bceb 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/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_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..6cd8b267578 --- /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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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..8d5fbed0795 --- /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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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..40ca6409759 --- /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: 5.1.10-beta-debug-log, 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: 5.1.10-beta-debug-log, 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 a8af5c59b81..a3f6199706b 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 931fa2ee402..4ffd5086b0f 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 5aa984bfa4e..c5051637707 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 10f1e08eded..97ba38d8ba9 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 0ba8916f5b1..2543d54b8e1 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 c5446ac314e..be16493ca1c 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 7e091991475..f49c8f280f4 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; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 7cbe4419904..f355660d38e 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -35,6 +35,7 @@ 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 +rpl_sp_effects : BUG#19862 SELECT from view with ORDER BY... udf : BUG#18564 2006-03-27 ian (Permission by Brian) # 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 3c43fb1d1f9..3b26a556dc2 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/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_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..7a8101cbf55 --- /dev/null +++ b/mysql-test/t/rpl_truncate_7ndb.test @@ -0,0 +1,64 @@ + +--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: #/ +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: #/ +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 8ccd954eeea..6e4e403ccfb 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 67aec1496dd..55dc3be7835 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 6094382c094..0b07057a749 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 diff --git a/sql/sql_class.h b/sql/sql_class.h index 54f256997d0..93854c42500 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1389,6 +1389,10 @@ public: { current_stmt_binlog_row_based= 1; } + inline void clear_current_stmt_binlog_row_based() + { + current_stmt_binlog_row_based= 0; + } inline void reset_current_stmt_binlog_row_based() { current_stmt_binlog_row_based= test(variables.binlog_format == BINLOG_FORMAT_ROW); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 44b0fe1a2f1..92ddff58dd5 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -42,8 +42,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, ha_rows deleted= 0; uint usable_index= MAX_KEY; SELECT_LEX *select_lex= &thd->lex->select_lex; - bool ha_delete_all_rows= 0; - ulonglong const saved_options= thd->options; DBUG_ENTER("mysql_delete"); if (open_and_lock_tables(thd, table_list)) @@ -75,20 +73,19 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, Test if the user wants to delete all rows and deletion doesn't have any side-effects (because of triggers), so we can use optimized handler::delete_all_rows() method. + + If row-based replication is used, we also delete the table row by + row. */ if (!using_limit && const_cond && (!conds || conds->val_int()) && !(specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) && - !(table->triggers && table->triggers->has_delete_triggers())) + !(table->triggers && table->triggers->has_delete_triggers()) && + !thd->current_stmt_binlog_row_based) { /* Update the table->file->records number */ table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); ha_rows const maybe_deleted= table->file->records; - /* - If all rows shall be deleted, we (almost) always log this - statement-based (see [binlog], below), so we set this flag and - test it below. - */ - ha_delete_all_rows= 1; + DBUG_PRINT("debug", ("Trying to use delete_all_rows()")); if (!(error=table->file->delete_all_rows())) { error= -1; // ok @@ -218,14 +215,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, thd->proc_info="updating"; will_batch= !table->file->start_bulk_delete(); - /* - We saved the thread options above before clearing the - OPTION_BIN_LOG, and will restore below, effectively disabling the - binary log (unless it was already disabled, of course). - */ - if (ha_delete_all_rows) - thd->options&= ~static_cast<ulonglong>(OPTION_BIN_LOG); - while (!(error=info.read_record(&info)) && !thd->killed && !thd->net.report_error) { @@ -317,12 +306,6 @@ cleanup: delete select; transactional_table= table->file->has_transactions(); - /* - Restore the saved value of the OPTION_BIN_LOG bit in the thread - options before executing binlog_query() below. - */ - thd->options|= (saved_options & OPTION_BIN_LOG); - /* See similar binlogging code in sql_update.cc, for comments */ if ((error < 0) || (deleted && !transactional_table)) { @@ -337,11 +320,7 @@ cleanup: statement-based; otherwise, 'ha_delete_row()' was used to delete specific rows which we might log row-based. */ - THD::enum_binlog_query_type const - query_type(ha_delete_all_rows && !table->file->is_injective() ? - THD::STMT_QUERY_TYPE : - THD::ROW_QUERY_TYPE); - int log_result= thd->binlog_query(query_type, + int log_result= thd->binlog_query(THD::ROW_QUERY_TYPE, thd->query, thd->query_length, transactional_table, FALSE); @@ -998,6 +977,7 @@ trunc_by_del: thd->options&= ~(ulong) (OPTION_BEGIN | OPTION_NOT_AUTOCOMMIT); ha_enable_transaction(thd, FALSE); mysql_init_select(thd->lex); + thd->clear_current_stmt_binlog_row_based(); error= mysql_delete(thd, table_list, (COND*) 0, (SQL_LIST*) 0, HA_POS_ERROR, LL(0), TRUE); ha_enable_transaction(thd, TRUE); |