diff options
Diffstat (limited to 'mysql-test')
55 files changed, 2505 insertions, 17 deletions
diff --git a/mysql-test/collections/default.experimental b/mysql-test/collections/default.experimental index 41d5282a6c9..1e811ecd8c2 100644 --- a/mysql-test/collections/default.experimental +++ b/mysql-test/collections/default.experimental @@ -2,6 +2,7 @@ # in alphabetical order. This also helps with merge conflict resolution. binlog.binlog_tmp_table* # Bug#45578:2009-07-10 alik Test binlog_tmp_table fails ramdonly on PB2: Unknown table 't2' +binlog.binlog_multi_engine # joro : NDB tests marked as experimental as agreed with bochklin funcs_1.charset_collation_1 # depends on compile-time decisions diff --git a/mysql-test/extra/binlog_tests/binlog.test b/mysql-test/extra/binlog_tests/binlog.test index 5d898d41a54..b819996acb0 100644 --- a/mysql-test/extra/binlog_tests/binlog.test +++ b/mysql-test/extra/binlog_tests/binlog.test @@ -270,3 +270,42 @@ INSERT INTO test.t1 VALUES (1), (2); CREATE TABLE test.t2 SELECT * FROM test.t1; USE test; DROP TABLES t1, t2; + +# +# Bug#46640 +# This test verifies if the server_id stored in the "format +# description BINLOG statement" will override the server_id +# of the server executing the statements. +# + +connect (fresh,localhost,root,,test); +connection fresh; + +RESET MASTER; +CREATE TABLE t1 (a INT PRIMARY KEY); + +# Format description event, with server_id = 10; +BINLOG ' +3u9kSA8KAAAAZgAAAGoAAAABAAQANS4xLjM1LW1hcmlhLWJldGExLWRlYnVnLWxvZwAAAAAAAAAA +AAAAAAAAAAAAAAAAAADe72RIEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC +'; + +# What server_id is logged for a statement? Should be our own, not the +# one from the format description event. +INSERT INTO t1 VALUES (1); + +# INSERT INTO t1 VALUES (2), with server_id=20. Check that this is logged +# with our own server id, not the 20 from the BINLOG statement. +BINLOG ' +3u9kSBMUAAAAKQAAAJEBAAAAABoAAAAAAAAABHRlc3QAAnQxAAEDAAA= +3u9kSBcUAAAAIgAAALMBAAAQABoAAAAAAAEAAf/+AgAAAA== +'; + +# Show binlog events to check that server ids are correct. +--replace_column 1 # 2 # 5 # +--replace_regex /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/ +SHOW BINLOG EVENTS; + +DROP TABLE t1; +disconnect fresh; + diff --git a/mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test b/mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test new file mode 100644 index 00000000000..54f3c538c79 --- /dev/null +++ b/mysql-test/extra/binlog_tests/binlog_failure_mixing_engines.test @@ -0,0 +1,300 @@ +################################################################################ +# Let +# - B be begin, C commit and R rollback. +# - T a statement that accesses and changes only transactional tables, i.e. +# T-tables +# - N a statement that accesses and changes only non-transactional tables, +# i.e, N-tables. +# - M be a mixed statement, i.e. a statement that updates both T- and +# N-tables. +# - M* be a mixed statement that fails while updating either a T +# or N-table. +# - N* be a statement that fails while updating a N-table. +# +# In this test case, when changes are logged as rows either in the RBR or MIXED +# modes, we check if a M* statement that happens early in a transaction is +# written to the binary log outside the boundaries of the transaction and +# wrapped up in a BEGIN/ROLLBACK. This is done to keep the slave consistent with +# the master as the rollback will keep the changes on N-tables and undo them on +# T-tables. In particular, we expect the following behavior: +# +# 1. B M* T C would generate in the binlog B M* R B T C. +# 2. B M M* C would generate in the binlog B M M* C. +# 3. B M* M* T C would generate in the binlog B M* R B M* R B T C. +# +# SBR is not considered in this test because a failing statement is written to +# the binary along with the error code such that a slave executes and rolls it +# back, thus undoing the effects on T-tables. +# +# Note that, in the first case, we are not preserving history from the master as +# we are introducing a rollback that never happened. However, this seems to be +# more acceptable than making the slave diverge. In the second case, the slave +# will diverge as the changes on T-tables that originated from the M statement +# are rolled back on the master but not on the slave. Unfortunately, we cannot +# simply roll the transaction back as this would undo any uncommitted changes +# on T-tables. +# +# We check two more cases. First, INSERT...SELECT* which produces the following +# results: +# +# 1. B T INSERT M...SELECT* C" with an error in INSERT M...SELECT* generates in +# the binlog the following entries: "Nothing". +# 2. B INSERT M...SELECT* C" with an error in INSERT M...SELECT* generates in +# the binlog the following entries: B INSERT M...SELECT* R. +# +# Finally, we also check if any N statement that happens early in a transaction +# (i.e. before any T or M statement) is written to the binary log outside the +# boundaries of the transaction. In particular, we expect the following +# behavior: +# +# 1. B N N T C would generate in the binlog B N C B N C B T C. +# 2. B N N T R would generate in the binlog B N C B N C B T R. +# 3. B N* N* T C would generate in the binlog B N R B N R B T C. +# 4. B N* N* T R would generate in the binlog B N R B N R B T R. +# 5. B N N T N T C would generate in the binlog B N C B N C B T N T C. +# 6. B N N T N T R would generate in the binlog the B N C B N C B T N T R. +# +# Such issues do not happen in SBR. In RBR and MBR, a full-fledged fix will be +# pushed after the WL#2687. +# +# Please, remove this test case after pushing WL#2687. +################################################################################ + + +--echo ################################################################################### +--echo # CONFIGURATION +--echo ################################################################################### +CREATE TABLE nt_1 (a text, b int PRIMARY KEY) ENGINE = MyISAM; +CREATE TABLE nt_2 (a text, b int PRIMARY KEY) ENGINE = MyISAM; +CREATE TABLE tt_1 (a text, b int PRIMARY KEY) ENGINE = Innodb; +CREATE TABLE tt_2 (a text, b int PRIMARY KEY) ENGINE = Innodb; + +DELIMITER |; + +CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW +BEGIN + INSERT INTO nt_1 VALUES (NEW.a, NEW.b); +END| + +CREATE TRIGGER tr_i_nt_2_to_tt_2 BEFORE INSERT ON nt_2 FOR EACH ROW +BEGIN + INSERT INTO tt_2 VALUES (NEW.a, NEW.b); +END| + +DELIMITER ;| + +--echo ################################################################################### +--echo # CHECK HISTORY IN BINLOG +--echo ################################################################################### +--echo +--echo +--echo +--echo *** "B M* T C" with error in M* generates in the binlog the "B M* R B T C" entries +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +INSERT INTO nt_1 VALUES ("new text 1", 1); +BEGIN; +--error ER_DUP_ENTRY +INSERT INTO tt_1 VALUES (USER(), 2), (USER(), 1); +INSERT INTO tt_2 VALUES ("new text 3", 3); +COMMIT; +--source include/show_binlog_events.inc + +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +INSERT INTO tt_2 VALUES ("new text 4", 4); +BEGIN; +--error ER_DUP_ENTRY +INSERT INTO nt_2 VALUES (USER(), 5), (USER(), 4); +INSERT INTO tt_2 VALUES ("new text 6", 6); +COMMIT; +--source include/show_binlog_events.inc + +--echo +--echo +--echo +--echo *** "B M M* T C" with error in M* generates in the binlog the "B M M* T C" entries +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +INSERT INTO nt_1 VALUES ("new text 10", 10); +BEGIN; +INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8); +--error ER_DUP_ENTRY +INSERT INTO tt_1 VALUES (USER(), 9), (USER(), 10); +INSERT INTO tt_2 VALUES ("new text 11", 11); +COMMIT; +--source include/show_binlog_events.inc + +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +INSERT INTO tt_2 VALUES ("new text 15", 15); +BEGIN; +INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13); +--error ER_DUP_ENTRY +INSERT INTO nt_2 VALUES (USER(), 14), (USER(), 15); +INSERT INTO tt_2 VALUES ("new text 16", 16); +COMMIT; +--source include/show_binlog_events.inc + + +--echo +--echo +--echo +--echo *** "B M* M* T C" with error in M* generates in the binlog the "B M* R B M* R B T C" entries +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +INSERT INTO nt_1 VALUES ("new text 18", 18); +INSERT INTO nt_1 VALUES ("new text 20", 20); +BEGIN; +--error ER_DUP_ENTRY +INSERT INTO tt_1 VALUES (USER(), 17), (USER(), 18); +--error ER_DUP_ENTRY +INSERT INTO tt_1 VALUES (USER(), 19), (USER(), 20); +INSERT INTO tt_2 VALUES ("new text 21", 21); +COMMIT; +--source include/show_binlog_events.inc + +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +INSERT INTO tt_2 VALUES ("new text 23", 23); +INSERT INTO tt_2 VALUES ("new text 25", 25); +BEGIN; +--error ER_DUP_ENTRY +INSERT INTO nt_2 VALUES (USER(), 22), (USER(), 23); +--error ER_DUP_ENTRY +INSERT INTO nt_2 VALUES (USER(), 24), (USER(), 25); +INSERT INTO tt_2 VALUES ("new text 26", 26); +COMMIT; +--source include/show_binlog_events.inc + +--echo +--echo +--echo +--echo *** "B T INSERT M...SELECT* C" with an error in INSERT M...SELECT* generates +--echo *** in the binlog the following entries: "Nothing". +--echo *** There is a bug in that will be fixed after WL#2687. Please, check BUG#47175 for further details. +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +TRUNCATE TABLE nt_2; +TRUNCATE TABLE tt_2; +INSERT INTO tt_2 VALUES ("new text 7", 7); +BEGIN; +INSERT INTO tt_2 VALUES ("new text 27", 27); +--error ER_DUP_ENTRY +INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1; +INSERT INTO tt_2 VALUES ("new text 28", 28); +ROLLBACK; +--source include/show_binlog_events.inc + +--echo +--echo +--echo +--echo *** "B INSERT M..SELECT* C" with an error in INSERT M...SELECT* generates +--echo *** in the binlog the following entries: "B INSERT M..SELECT* R". +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +TRUNCATE TABLE nt_2; +TRUNCATE TABLE tt_2; +INSERT INTO tt_2 VALUES ("new text 7", 7); +BEGIN; +--error ER_DUP_ENTRY +INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1; +COMMIT; +--source include/show_binlog_events.inc + +--echo +--echo +--echo +--echo *** "B N N T C" generates in the binlog the "B N C B N C B T C" entries +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +TRUNCATE TABLE nt_1; +TRUNCATE TABLE tt_2; +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 1); +INSERT INTO nt_1 VALUES (USER(), 2); +INSERT INTO tt_2 VALUES (USER(), 3); +COMMIT; +--source include/show_binlog_events.inc + +--echo +--echo +--echo +--echo *** "B N N T R" generates in the binlog the "B N C B N C B T R" entries +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 4); +INSERT INTO nt_1 VALUES (USER(), 5); +INSERT INTO tt_2 VALUES (USER(), 6); +ROLLBACK; +--source include/show_binlog_events.inc + +--echo +--echo +--echo +--echo *** "B N* N* T C" with error in N* generates in the binlog the "B N R B N R B T C" entries +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +BEGIN; +--error ER_DUP_ENTRY +INSERT INTO nt_1 VALUES (USER(), 7), (USER(), 1); +--error ER_DUP_ENTRY +INSERT INTO nt_1 VALUES (USER(), 8), (USER(), 1); +INSERT INTO tt_2 VALUES (USER(), 9); +COMMIT; +--source include/show_binlog_events.inc + +--echo +--echo +--echo +--echo *** "B N* N* T R" with error in N* generates in the binlog the "B N R B N R B T R" entries +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +BEGIN; +--error ER_DUP_ENTRY +INSERT INTO nt_1 VALUES (USER(), 10), (USER(), 1); +--error ER_DUP_ENTRY +INSERT INTO nt_1 VALUES (USER(), 11), (USER(), 1); +INSERT INTO tt_2 VALUES (USER(), 12); +ROLLBACK; +--source include/show_binlog_events.inc + +--echo +--echo +--echo +--echo *** "B N N T N T C" generates in the binlog the "B N C B N C B T N T C" entries +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 13); +INSERT INTO nt_1 VALUES (USER(), 14); +INSERT INTO tt_2 VALUES (USER(), 15); +INSERT INTO nt_1 VALUES (USER(), 16); +INSERT INTO tt_2 VALUES (USER(), 17); +COMMIT; +--source include/show_binlog_events.inc + +--echo +--echo +--echo +--echo *** "B N N T N T R" generates in the binlog the "B N C B N C B T N T R" entries +--echo +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 18); +INSERT INTO nt_1 VALUES (USER(), 19); +INSERT INTO tt_2 VALUES (USER(), 20); +INSERT INTO nt_1 VALUES (USER(), 21); +INSERT INTO tt_2 VALUES (USER(), 22); +ROLLBACK; +--source include/show_binlog_events.inc + +--echo ################################################################################### +--echo # CLEAN +--echo ################################################################################### + +DROP TABLE tt_1; +DROP TABLE tt_2; +DROP TABLE nt_1; +DROP TABLE nt_2; diff --git a/mysql-test/extra/rpl_tests/rpl_failed_optimize.test b/mysql-test/extra/rpl_tests/rpl_failed_optimize.test index 0c537ee188d..cd81f2497b8 100644 --- a/mysql-test/extra/rpl_tests/rpl_failed_optimize.test +++ b/mysql-test/extra/rpl_tests/rpl_failed_optimize.test @@ -22,3 +22,4 @@ connection master; select * from t1; commit; drop table t1; +-- sync_slave_with_master diff --git a/mysql-test/r/ctype_ldml.result b/mysql-test/r/ctype_ldml.result index 711921eb526..fe870f82743 100644 --- a/mysql-test/r/ctype_ldml.result +++ b/mysql-test/r/ctype_ldml.result @@ -321,3 +321,11 @@ Vv Xx YyÝýỲỳỴỵỶỷỸỹ drop table t1; +Bug#46448 trailing spaces are not ignored when user collation maps space != 0x20 +set names latin1; +show collation like 'latin1_test'; +Collation Charset Id Default Compiled Sortlen +latin1_test latin1 99 Yes 1 +select "foo" = "foo " collate latin1_test; +"foo" = "foo " collate latin1_test +1 diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 3d989ad1730..94147640cde 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1477,3 +1477,47 @@ COUNT(*) SET SQL_MODE=default; DROP TABLE t1; End of 5.0 tests +# +# BUG#47280 - strange results from count(*) with order by multiple +# columns without where/group +# +# +# Initialize test +# +CREATE TABLE t1 ( +pk INT NOT NULL, +i INT, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1,11),(2,12),(3,13); +# +# Start test +# All the following queries shall return 1 record +# + +# Masking all correct values {11...13} for column i in this result. +SELECT MAX(pk) as max, i +FROM t1 +ORDER BY max; +max i +3 # + +EXPLAIN +SELECT MAX(pk) as max, i +FROM t1 +ORDER BY max; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary + +# Only 11 is correct for collumn i in this result +SELECT MAX(pk) as max, i +FROM t1 +WHERE pk<2 +ORDER BY max; +max i +1 11 +# +# Cleanup +# +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index c4841ee57a0..5b1699686c9 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -876,10 +876,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by @@ -924,7 +924,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by +1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by diff --git a/mysql-test/r/innodb-consistent.result b/mysql-test/r/innodb-consistent.result new file mode 100644 index 00000000000..9115791b99c --- /dev/null +++ b/mysql-test/r/innodb-consistent.result @@ -0,0 +1,35 @@ +drop table if exists t1; +set session transaction isolation level read committed; +create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; +create table t2 like t1; +insert into t2 values (1),(2),(3),(4),(5),(6),(7); +set autocommit=0; +begin; +replace into t1 select * from t2; +set session transaction isolation level read committed; +set autocommit=0; +delete from t2 where a=5; +commit; +delete from t2; +commit; +commit; +begin; +insert into t1 select * from t2; +set session transaction isolation level read committed; +set autocommit=0; +delete from t2 where a=5; +commit; +delete from t2; +commit; +commit; +select * from t1; +a +1 +2 +3 +4 +5 +6 +7 +drop table t1; +drop table t2; diff --git a/mysql-test/r/innodb_bug34300.result b/mysql-test/r/innodb_bug34300.result index 03ae355de7b..4e3d3c17040 100644 --- a/mysql-test/r/innodb_bug34300.result +++ b/mysql-test/r/innodb_bug34300.result @@ -6,4 +6,5 @@ SELECT f4, f8 FROM bug34300; f4 f8 xxx zzz DROP TABLE bug34300; +SET @@global.max_allowed_packet=1048576; SET @@global.max_allowed_packet=default; diff --git a/mysql-test/r/innodb_bug44369.result b/mysql-test/r/innodb_bug44369.result new file mode 100644 index 00000000000..e4b84ecac19 --- /dev/null +++ b/mysql-test/r/innodb_bug44369.result @@ -0,0 +1,14 @@ +create table bug44369 (DB_ROW_ID int) engine=innodb; +ERROR HY000: Can't create table 'test.bug44369' (errno: -1) +create table bug44369 (db_row_id int) engine=innodb; +ERROR HY000: Can't create table 'test.bug44369' (errno: -1) +show errors; +Level Code Message +Error 1005 Error creating table 'test/bug44369' with column name 'db_row_id'. 'db_row_id' is a reserved name. Please try to re-create the table with a different column name. +Error 1005 Can't create table 'test.bug44369' (errno: -1) +create table bug44369 (db_TRX_Id int) engine=innodb; +ERROR HY000: Can't create table 'test.bug44369' (errno: -1) +show errors; +Level Code Message +Error 1005 Error creating table 'test/bug44369' with column name 'db_TRX_Id'. 'db_TRX_Id' is a reserved name. Please try to re-create the table with a different column name. +Error 1005 Can't create table 'test.bug44369' (errno: -1) diff --git a/mysql-test/r/innodb_bug44571.result b/mysql-test/r/innodb_bug44571.result new file mode 100644 index 00000000000..36374edcb3e --- /dev/null +++ b/mysql-test/r/innodb_bug44571.result @@ -0,0 +1,9 @@ +CREATE TABLE bug44571 (foo INT) ENGINE=InnoDB; +ALTER TABLE bug44571 CHANGE foo bar INT; +ALTER TABLE bug44571 ADD INDEX bug44571b (foo); +ERROR 42000: Key column 'foo' doesn't exist in table +ALTER TABLE bug44571 ADD INDEX bug44571b (bar); +ERROR HY000: Incorrect key file for table 'bug44571'; try to repair it +CREATE INDEX bug44571b ON bug44571 (bar); +ERROR HY000: Incorrect key file for table 'bug44571'; try to repair it +DROP TABLE bug44571; diff --git a/mysql-test/r/innodb_bug46000.result b/mysql-test/r/innodb_bug46000.result new file mode 100644 index 00000000000..ccff888a48d --- /dev/null +++ b/mysql-test/r/innodb_bug46000.result @@ -0,0 +1,17 @@ +create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb; +ERROR HY000: Can't create table 'test.bug46000' (errno: -1) +create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb; +ERROR HY000: Can't create table 'test.bug46000' (errno: -1) +show errors; +Level Code Message +Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Error 1005 Can't create table 'test.bug46000' (errno: -1) +create table bug46000(id int) engine=innodb; +create index GEN_CLUST_INDEX on bug46000(id); +ERROR HY000: Can't create table '#sql-temporary' (errno: -1) +show errors; +Level Code Message +Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Error 1005 Can't create table '#sql-temporary' (errno: -1) +create index idx on bug46000(id); +drop table bug46000; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 58e2e451a0d..f5050ccf1c0 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -2271,4 +2271,25 @@ checksum table t3; Table Checksum test.t3 326284887 drop table t1,t2,t3; +CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); +INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), +(6,'0'),(7,'0'); +INSERT INTO t1 SELECT a+10,b FROM t1; +INSERT INTO t1 SELECT a+20,b FROM t1; +INSERT INTO t1 SELECT a+40,b FROM t1; +INSERT INTO t1 SELECT a+80,b FROM t1; +INSERT INTO t1 SELECT a+160,b FROM t1; +INSERT INTO t1 SELECT a+320,b FROM t1; +INSERT INTO t1 SELECT a+640,b FROM t1; +INSERT INTO t1 SELECT a+1280,b FROM t1; +INSERT INTO t1 SELECT a+2560,b FROM t1; +INSERT INTO t1 SELECT a+5120,b FROM t1; +SET myisam_sort_buffer_size=4; +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair error myisam_sort_buffer_size is too small +test.t1 repair warning Number of rows changed from 0 to 7168 +test.t1 repair status OK +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 306fce1f3c2..0c72f816c21 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1557,3 +1557,34 @@ a 2001 1991 DROP TABLE t1; +# +# Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering +# is used +# +CREATE TABLE t1 (a INT, b INT, KEY (a)); +INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL); +INSERT INTO t1 SELECT a+4, b FROM t1; +INSERT INTO t1 SELECT a+8, b FROM t1; +CREATE TABLE t2 (a INT, b INT); +INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL); +INSERT INTO t2 SELECT a+4, b FROM t2; +# shouldn't have "using filesort" +EXPLAIN +SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 2 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 +# should have "using filesort" +EXPLAIN +SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer +# should have "using filesort" +EXPLAIN +SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer +DROP TABLE t1, t2; +End of 5.1 tests diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index a76cb2ba225..065c427bfd3 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -50,6 +50,21 @@ t1 CREATE TABLE `t1` ( PARTITION p3 VALUES LESS THAN (733969) ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ DROP TABLE t1; +create table t1 (a int, b int, key(a)) +partition by list (a) +( partition p0 values in (1), +partition p1 values in (2)); +insert into t1 values (1,1),(2,1),(2,2),(2,3); +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A 1 NULL NULL YES BTREE +drop table t1; CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a)) ENGINE=MyISAM PARTITION BY HASH (a); diff --git a/mysql-test/r/partition_open_files_limit.result b/mysql-test/r/partition_open_files_limit.result new file mode 100644 index 00000000000..1441ba4e78e --- /dev/null +++ b/mysql-test/r/partition_open_files_limit.result @@ -0,0 +1,22 @@ +DROP TABLE IF EXISTS `t1`; +# Bug#46922: crash when adding partitions and open_files_limit is reached +CREATE TABLE t1 (a INT PRIMARY KEY) +ENGINE=MyISAM PARTITION BY KEY () PARTITIONS 1; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); +# if the bug exists, then crash will happen here +ALTER TABLE t1 ADD PARTITION PARTITIONS 511; +ERROR HY000: Out of resources when opening file '<partition file>' (Errcode: 24) +SELECT * FROM t1; +a +1 +10 +11 +2 +3 +4 +5 +6 +7 +8 +9 +DROP TABLE t1; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index cc5e8d2be96..c98a7696ea6 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1219,3 +1219,182 @@ explain select * from t2 where a=1000 and b<11; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 5 const 502 Using where drop table t1, t2; +CREATE TABLE t1( a INT, b INT, KEY( a, b ) ); +CREATE TABLE t2( a INT, b INT, KEY( a, b ) ); +CREATE TABLE t3( a INT, b INT, KEY( a, b ) ); +INSERT INTO t1( a, b ) +VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7); +INSERT INTO t2( a, b ) +VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1), +( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1), +(11, 1), (12, 1), (13, 1), (14, 1), (15, 1), +(16, 1), (17, 1), (18, 1), (19, 1), (20, 1); +INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1; +INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1; +INSERT INTO t2 SELECT -1, -1 FROM t2; +INSERT INTO t2 SELECT -1, -1 FROM t2; +INSERT INTO t2 SELECT -1, -1 FROM t2; +INSERT INTO t3 +VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0), +(6, 0), (7, 0), (8, 0), (9, 0), (10, 0); +INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; +INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 < a AND b = 3 OR +3 <= a; +a b +5 0 +9 7 +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 < a AND b = 3 OR +3 <= a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 <= a AND b = 3 OR +3 <= a; +a b +5 0 +9 7 +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 <= a AND b = 3 OR +3 <= a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +5 <= a AND b = 3 OR +3 <= a; +a b +5 0 +9 7 +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +5 <= a AND b = 3 OR +3 <= a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +3 <= a; +a b +5 0 +9 7 +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +3 <= a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 1 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; +a b +1 1 +2 1 +3 1 +4 1 +5 1 +6 1 +7 1 +8 1 +9 1 +10 1 +11 1 +12 1 +13 1 +14 1 +15 1 +15 3 +16 1 +16 3 +17 1 +17 3 +18 1 +18 3 +19 1 +19 3 +20 1 +EXPLAIN +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 1 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a a 10 NULL 50 Using where; Using index +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 2 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; +a b +1 1 +2 1 +3 1 +4 1 +5 1 +5 2 +6 1 +6 2 +7 1 +7 2 +8 1 +8 2 +9 1 +9 2 +10 1 +11 1 +12 1 +13 1 +14 1 +15 1 +15 3 +16 1 +16 3 +17 1 +17 3 +18 1 +18 3 +19 1 +19 3 +20 1 +EXPLAIN +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 2 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a a 10 NULL 50 Using where; Using index +SELECT * FROM t3 WHERE +5 <= a AND a < 10 AND b = 3 OR +a < 5 OR +a < 10; +a b +1 0 +2 0 +3 0 +4 0 +5 0 +6 0 +7 0 +8 0 +9 0 +EXPLAIN +SELECT * FROM t3 WHERE +5 <= a AND a < 10 AND b = 3 OR +a < 5 OR +a < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range a a 5 NULL 8 Using where; Using index +DROP TABLE t1, t2, t3; diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result index b831771d9c5..51feab0a421 100644 --- a/mysql-test/r/type_bit.result +++ b/mysql-test/r/type_bit.result @@ -749,6 +749,16 @@ bin(a1) 110000111111111 110001011111111 drop table t1bit7, t2bit7; +# +# Bug42803: Field_bit does not have unsigned_flag field, +# can lead to bad memory access +# +CREATE TABLE t1 (a BIT(7), b BIT(9), KEY(a, b)); +INSERT INTO t1 VALUES(0, 0), (5, 3), (5, 6), (6, 4), (7, 0); +EXPLAIN SELECT a+0, b+0 FROM t1 WHERE a > 4 and b < 7 ORDER BY 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 2 NULL 4 Using where; Using index; Using filesort +DROP TABLE t1; End of 5.0 tests create table t1(a bit(7)); insert into t1 values(0x40); diff --git a/mysql-test/std_data/Index.xml b/mysql-test/std_data/Index.xml index 988dddcc68a..3dc647d8195 100644 --- a/mysql-test/std_data/Index.xml +++ b/mysql-test/std_data/Index.xml @@ -68,4 +68,17 @@ </charset> + <charset name="latin1"> + <family>Western</family> + <description>cp1252 West European</description> + <alias>csisolatin1</alias> + <alias>iso-8859-1</alias> + <alias>iso-ir-100</alias> + <alias>iso_8859-1</alias> + <alias>iso_8859-1:1987</alias> + <alias>l1</alias> + <alias>latin1</alias> + <collation name="latin1_test" id="99" order="test"/> + </charset> + </charsets> diff --git a/mysql-test/std_data/latin1.xml b/mysql-test/std_data/latin1.xml new file mode 100644 index 00000000000..458b1c34da1 --- /dev/null +++ b/mysql-test/std_data/latin1.xml @@ -0,0 +1,135 @@ +<?xml version='1.0' encoding="utf-8"?> + +<charsets> + +<copyright> + Copyright (C) 2009 Sun Microsystems, Inc + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA +</copyright> + +<charset name="latin1"> + +<ctype> +<map> + 00 + 20 20 20 20 20 20 20 20 20 28 28 28 28 28 20 20 + 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 + 48 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 + 84 84 84 84 84 84 84 84 84 84 10 10 10 10 10 10 + 10 81 81 81 81 81 81 01 01 01 01 01 01 01 01 01 + 01 01 01 01 01 01 01 01 01 01 01 10 10 10 10 10 + 10 82 82 82 82 82 82 02 02 02 02 02 02 02 02 02 + 02 02 02 02 02 02 02 02 02 02 02 10 10 10 10 20 + 10 00 10 02 10 10 10 10 10 10 01 10 01 00 01 00 + 00 10 10 10 10 10 10 10 10 10 02 10 02 00 02 01 + 48 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 + 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 10 + 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 + 01 01 01 01 01 01 01 10 01 01 01 01 01 01 01 02 + 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 + 02 02 02 02 02 02 02 10 02 02 02 02 02 02 02 02 +</map> +</ctype> + + +<lower> +<map> + 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F + 10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F + 20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F + 30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F + 40 61 62 63 64 65 66 67 68 69 6A 6B 6C 6D 6E 6F + 70 71 72 73 74 75 76 77 78 79 7A 5B 5C 5D 5E 5F + 60 61 62 63 64 65 66 67 68 69 6A 6B 6C 6D 6E 6F + 70 71 72 73 74 75 76 77 78 79 7A 7B 7C 7D 7E 7F + 80 81 82 83 84 85 86 87 88 89 8A 8B 8C 8D 8E 8F + 90 91 92 93 94 95 96 97 98 99 9A 9B 9C 9D 9E 9F + A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 AA AB AC AD AE AF + B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 BA BB BC BD BE BF + E0 E1 E2 E3 E4 E5 E6 E7 E8 E9 EA EB EC ED EE EF + F0 F1 F2 F3 F4 F5 F6 D7 F8 F9 FA FB FC FD FE DF + E0 E1 E2 E3 E4 E5 E6 E7 E8 E9 EA EB EC ED EE EF + F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 FA FB FC FD FE FF +</map> +</lower> + + +<upper> +<map> + 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F + 10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F + 20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F + 30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F + 40 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F + 50 51 52 53 54 55 56 57 58 59 5A 5B 5C 5D 5E 5F + 60 41 42 43 44 45 46 47 48 49 4A 4B 4C 4D 4E 4F + 50 51 52 53 54 55 56 57 58 59 5A 7B 7C 7D 7E 7F + 80 81 82 83 84 85 86 87 88 89 8A 8B 8C 8D 8E 8F + 90 91 92 93 94 95 96 97 98 99 9A 9B 9C 9D 9E 9F + A0 A1 A2 A3 A4 A5 A6 A7 A8 A9 AA AB AC AD AE AF + B0 B1 B2 B3 B4 B5 B6 B7 B8 B9 BA BB BC BD BE BF + C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 CA CB CC CD CE CF + D0 D1 D2 D3 D4 D5 D6 D7 D8 D9 DA DB DC DD DE DF + C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 CA CB CC CD CE CF + D0 D1 D2 D3 D4 D5 D6 F7 D8 D9 DA DB DC DD DE FF +</map> +</upper> + + +<unicode> +<map> + 0000 0001 0002 0003 0004 0005 0006 0007 0008 0009 000A 000B 000C 000D 000E 000F + 0010 0011 0012 0013 0014 0015 0016 0017 0018 0019 001A 001B 001C 001D 001E 001F + 0020 0021 0022 0023 0024 0025 0026 0027 0028 0029 002A 002B 002C 002D 002E 002F + 0030 0031 0032 0033 0034 0035 0036 0037 0038 0039 003A 003B 003C 003D 003E 003F + 0040 0041 0042 0043 0044 0045 0046 0047 0048 0049 004A 004B 004C 004D 004E 004F + 0050 0051 0052 0053 0054 0055 0056 0057 0058 0059 005A 005B 005C 005D 005E 005F + 0060 0061 0062 0063 0064 0065 0066 0067 0068 0069 006A 006B 006C 006D 006E 006F + 0070 0071 0072 0073 0074 0075 0076 0077 0078 0079 007A 007B 007C 007D 007E 007F + 20AC 0081 201A 0192 201E 2026 2020 2021 02C6 2030 0160 2039 0152 008D 017D 008F + 0090 2018 2019 201C 201D 2022 2013 2014 02DC 2122 0161 203A 0153 009D 017E 0178 + 00A0 00A1 00A2 00A3 00A4 00A5 00A6 00A7 00A8 00A9 00AA 00AB 00AC 00AD 00AE 00AF + 00B0 00B1 00B2 00B3 00B4 00B5 00B6 00B7 00B8 00B9 00BA 00BB 00BC 00BD 00BE 00BF + 00C0 00C1 00C2 00C3 00C4 00C5 00C6 00C7 00C8 00C9 00CA 00CB 00CC 00CD 00CE 00CF + 00D0 00D1 00D2 00D3 00D4 00D5 00D6 00D7 00D8 00D9 00DA 00DB 00DC 00DD 00DE 00DF + 00E0 00E1 00E2 00E3 00E4 00E5 00E6 00E7 00E8 00E9 00EA 00EB 00EC 00ED 00EE 00EF + 00F0 00F1 00F2 00F3 00F4 00F5 00F6 00F7 00F8 00F9 00FA 00FB 00FC 00FD 00FE 00FF +</map> +</unicode> + +<collation name="latin1_test"> +<map> + 00 01 02 03 37 2D 2E 2F 16 05 25 0B 0C 0D 0E 0F + 10 11 12 13 3C 3D 32 26 18 19 3F 27 1C 1D 1E 1F + 40 4F 7F 7B 5B 6C 50 7D 4D 5D 5C 4E 6B 60 4B 61 + F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 7A 5E 4C 7E 6E 6F + 7C C1 C2 C3 C4 C5 C6 C7 C8 C9 D1 D2 D3 D4 D5 D6 + D7 D8 D9 E2 E3 E4 E5 E6 E7 E8 E9 4A E0 5A 5F 6D + 79 81 82 83 84 85 86 87 88 89 91 92 93 94 95 96 + 97 98 99 A2 A3 A4 A5 A6 A7 A8 A9 C0 6A D0 A1 07 + 20 21 22 23 24 15 06 17 28 29 2A 2B 2C 09 0A 1B + 30 31 1A 33 34 35 36 08 38 39 3A 3B 04 14 3E E1 + 41 42 43 44 45 46 47 48 49 51 52 53 54 55 56 57 + 58 59 62 63 64 65 66 67 68 69 70 71 72 73 74 75 + 76 77 78 80 8A 8B 8C 8D 8E 8F 90 9A 9B 9C 9D 9E + 9F A0 AA AB AC AD AE AF B0 B1 B2 B3 B4 B5 B6 B7 + B8 B9 BA BB BC BD BE BF CA CB CC CD CE CF DA DB + DC DD DE DF EA EB EC ED EE EF FA FB FC FD FE FF +</map> +</collation> + +</charset> + +</charsets> diff --git a/mysql-test/suite/binlog/r/binlog_mixed_failure_mixing_engines.result b/mysql-test/suite/binlog/r/binlog_mixed_failure_mixing_engines.result new file mode 100644 index 00000000000..dfc08d76a6a --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_mixed_failure_mixing_engines.result @@ -0,0 +1,406 @@ +################################################################################### +# CONFIGURATION +################################################################################### +CREATE TABLE nt_1 (a text, b int PRIMARY KEY) ENGINE = MyISAM; +CREATE TABLE nt_2 (a text, b int PRIMARY KEY) ENGINE = MyISAM; +CREATE TABLE tt_1 (a text, b int PRIMARY KEY) ENGINE = Innodb; +CREATE TABLE tt_2 (a text, b int PRIMARY KEY) ENGINE = Innodb; +CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW +BEGIN +INSERT INTO nt_1 VALUES (NEW.a, NEW.b); +END| +CREATE TRIGGER tr_i_nt_2_to_tt_2 BEFORE INSERT ON nt_2 FOR EACH ROW +BEGIN +INSERT INTO tt_2 VALUES (NEW.a, NEW.b); +END| +################################################################################### +# CHECK HISTORY IN BINLOG +################################################################################### + + + +*** "B M* T C" with error in M* generates in the binlog the "B M* R B T C" entries + +INSERT INTO nt_1 VALUES ("new text 1", 1); +BEGIN; +INSERT INTO tt_1 VALUES (USER(), 2), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 3", 3); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 1", 1) +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_1) +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 3", 3) +master-bin.000001 # Xid # # COMMIT /* XID */ + +INSERT INTO tt_2 VALUES ("new text 4", 4); +BEGIN; +INSERT INTO nt_2 VALUES (USER(), 5), (USER(), 4); +ERROR 23000: Duplicate entry '4' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 6", 6); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 4", 4) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 6", 6) +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B M M* T C" with error in M* generates in the binlog the "B M M* T C" entries + +INSERT INTO nt_1 VALUES ("new text 10", 10); +BEGIN; +INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8); +INSERT INTO tt_1 VALUES (USER(), 9), (USER(), 10); +ERROR 23000: Duplicate entry '10' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 11", 11); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 10", 10) +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8) +master-bin.000001 # Table_map # # table_id: # (test.tt_1) +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 11", 11) +master-bin.000001 # Xid # # COMMIT /* XID */ + +INSERT INTO tt_2 VALUES ("new text 15", 15); +BEGIN; +INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13); +INSERT INTO nt_2 VALUES (USER(), 14), (USER(), 15); +ERROR 23000: Duplicate entry '15' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 16", 16); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 15", 15) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13) +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 16", 16) +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B M* M* T C" with error in M* generates in the binlog the "B M* R B M* R B T C" entries + +INSERT INTO nt_1 VALUES ("new text 18", 18); +INSERT INTO nt_1 VALUES ("new text 20", 20); +BEGIN; +INSERT INTO tt_1 VALUES (USER(), 17), (USER(), 18); +ERROR 23000: Duplicate entry '18' for key 'PRIMARY' +INSERT INTO tt_1 VALUES (USER(), 19), (USER(), 20); +ERROR 23000: Duplicate entry '20' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 21", 21); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 18", 18) +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 20", 20) +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_1) +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_1) +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 21", 21) +master-bin.000001 # Xid # # COMMIT /* XID */ + +INSERT INTO tt_2 VALUES ("new text 23", 23); +INSERT INTO tt_2 VALUES ("new text 25", 25); +BEGIN; +INSERT INTO nt_2 VALUES (USER(), 22), (USER(), 23); +ERROR 23000: Duplicate entry '23' for key 'PRIMARY' +INSERT INTO nt_2 VALUES (USER(), 24), (USER(), 25); +ERROR 23000: Duplicate entry '25' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 26", 26); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 23", 23) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 25", 25) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 26", 26) +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B T INSERT M...SELECT* C" with an error in INSERT M...SELECT* generates +*** in the binlog the following entries: "Nothing". +*** There is a bug in that will be fixed after WL#2687. Please, check BUG#47175 for further details. + +TRUNCATE TABLE nt_2; +TRUNCATE TABLE tt_2; +INSERT INTO tt_2 VALUES ("new text 7", 7); +BEGIN; +INSERT INTO tt_2 VALUES ("new text 27", 27); +INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1; +ERROR 23000: Duplicate entry '7' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 28", 28); +ROLLBACK; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE nt_2 +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE tt_2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 7", 7) +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B INSERT M..SELECT* C" with an error in INSERT M...SELECT* generates +*** in the binlog the following entries: "B INSERT M..SELECT* R". + +TRUNCATE TABLE nt_2; +TRUNCATE TABLE tt_2; +INSERT INTO tt_2 VALUES ("new text 7", 7); +BEGIN; +INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1; +ERROR 23000: Duplicate entry '7' for key 'PRIMARY' +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE nt_2 +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE tt_2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 7", 7) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK + + + +*** "B N N T C" generates in the binlog the "B N C B N C B T C" entries + +TRUNCATE TABLE nt_1; +TRUNCATE TABLE tt_2; +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 1); +INSERT INTO nt_1 VALUES (USER(), 2); +INSERT INTO tt_2 VALUES (USER(), 3); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE nt_1 +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE tt_2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B N N T R" generates in the binlog the "B N C B N C B T R" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 4); +INSERT INTO nt_1 VALUES (USER(), 5); +INSERT INTO tt_2 VALUES (USER(), 6); +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK + + + +*** "B N* N* T C" with error in N* generates in the binlog the "B N R B N R B T C" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 7), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO nt_1 VALUES (USER(), 8), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO tt_2 VALUES (USER(), 9); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B N* N* T R" with error in N* generates in the binlog the "B N R B N R B T R" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 10), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO nt_1 VALUES (USER(), 11), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO tt_2 VALUES (USER(), 12); +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK + + + +*** "B N N T N T C" generates in the binlog the "B N C B N C B T N T C" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 13); +INSERT INTO nt_1 VALUES (USER(), 14); +INSERT INTO tt_2 VALUES (USER(), 15); +INSERT INTO nt_1 VALUES (USER(), 16); +INSERT INTO tt_2 VALUES (USER(), 17); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B N N T N T R" generates in the binlog the "B N C B N C B T N T R" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 18); +INSERT INTO nt_1 VALUES (USER(), 19); +INSERT INTO tt_2 VALUES (USER(), 20); +INSERT INTO nt_1 VALUES (USER(), 21); +INSERT INTO tt_2 VALUES (USER(), 22); +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +################################################################################### +# CLEAN +################################################################################### +DROP TABLE tt_1; +DROP TABLE tt_2; +DROP TABLE nt_1; +DROP TABLE nt_2; diff --git a/mysql-test/suite/binlog/r/binlog_row_binlog.result b/mysql-test/suite/binlog/r/binlog_row_binlog.result index f6b5392dbc8..4d32a4f4739 100644 --- a/mysql-test/suite/binlog/r/binlog_row_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_row_binlog.result @@ -1309,3 +1309,27 @@ INSERT INTO test.t1 VALUES (1), (2); CREATE TABLE test.t2 SELECT * FROM test.t1; USE test; DROP TABLES t1, t2; +RESET MASTER; +CREATE TABLE t1 (a INT PRIMARY KEY); +BINLOG ' +3u9kSA8KAAAAZgAAAGoAAAABAAQANS4xLjM1LW1hcmlhLWJldGExLWRlYnVnLWxvZwAAAAAAAAAA +AAAAAAAAAAAAAAAAAADe72RIEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC +'; +INSERT INTO t1 VALUES (1); +BINLOG ' +3u9kSBMUAAAAKQAAAJEBAAAAABoAAAAAAAAABHRlc3QAAnQxAAEDAAA= +3u9kSBcUAAAAIgAAALMBAAAQABoAAAAAAAEAAf/+AgAAAA== +'; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +# # Format_desc 1 # Server ver: #, Binlog ver: # +# # Query 1 # use `test`; CREATE TABLE t1 (a INT PRIMARY KEY) +# # Query 1 # BEGIN +# # Table_map 1 # table_id: # (test.t1) +# # Write_rows 1 # table_id: # flags: STMT_END_F +# # Query 1 # COMMIT +# # Query 1 # BEGIN +# # Table_map 1 # table_id: # (test.t1) +# # Write_rows 1 # table_id: # flags: STMT_END_F +# # Query 1 # COMMIT +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/r/binlog_row_failure_mixing_engines.result b/mysql-test/suite/binlog/r/binlog_row_failure_mixing_engines.result new file mode 100644 index 00000000000..45c8640d3e3 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_row_failure_mixing_engines.result @@ -0,0 +1,440 @@ +################################################################################### +# CONFIGURATION +################################################################################### +CREATE TABLE nt_1 (a text, b int PRIMARY KEY) ENGINE = MyISAM; +CREATE TABLE nt_2 (a text, b int PRIMARY KEY) ENGINE = MyISAM; +CREATE TABLE tt_1 (a text, b int PRIMARY KEY) ENGINE = Innodb; +CREATE TABLE tt_2 (a text, b int PRIMARY KEY) ENGINE = Innodb; +CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW +BEGIN +INSERT INTO nt_1 VALUES (NEW.a, NEW.b); +END| +CREATE TRIGGER tr_i_nt_2_to_tt_2 BEFORE INSERT ON nt_2 FOR EACH ROW +BEGIN +INSERT INTO tt_2 VALUES (NEW.a, NEW.b); +END| +################################################################################### +# CHECK HISTORY IN BINLOG +################################################################################### + + + +*** "B M* T C" with error in M* generates in the binlog the "B M* R B T C" entries + +INSERT INTO nt_1 VALUES ("new text 1", 1); +BEGIN; +INSERT INTO tt_1 VALUES (USER(), 2), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 3", 3); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_1) +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + +INSERT INTO tt_2 VALUES ("new text 4", 4); +BEGIN; +INSERT INTO nt_2 VALUES (USER(), 5), (USER(), 4); +ERROR 23000: Duplicate entry '4' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 6", 6); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B M M* T C" with error in M* generates in the binlog the "B M M* T C" entries + +INSERT INTO nt_1 VALUES ("new text 10", 10); +BEGIN; +INSERT INTO tt_1 VALUES ("new text 7", 7), ("new text 8", 8); +INSERT INTO tt_1 VALUES (USER(), 9), (USER(), 10); +ERROR 23000: Duplicate entry '10' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 11", 11); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_1) +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.tt_1) +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + +INSERT INTO tt_2 VALUES ("new text 15", 15); +BEGIN; +INSERT INTO nt_2 VALUES ("new text 12", 12), ("new text 13", 13); +INSERT INTO nt_2 VALUES (USER(), 14), (USER(), 15); +ERROR 23000: Duplicate entry '15' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 16", 16); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B M* M* T C" with error in M* generates in the binlog the "B M* R B M* R B T C" entries + +INSERT INTO nt_1 VALUES ("new text 18", 18); +INSERT INTO nt_1 VALUES ("new text 20", 20); +BEGIN; +INSERT INTO tt_1 VALUES (USER(), 17), (USER(), 18); +ERROR 23000: Duplicate entry '18' for key 'PRIMARY' +INSERT INTO tt_1 VALUES (USER(), 19), (USER(), 20); +ERROR 23000: Duplicate entry '20' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 21", 21); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_1) +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_1) +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + +INSERT INTO tt_2 VALUES ("new text 23", 23); +INSERT INTO tt_2 VALUES ("new text 25", 25); +BEGIN; +INSERT INTO nt_2 VALUES (USER(), 22), (USER(), 23); +ERROR 23000: Duplicate entry '23' for key 'PRIMARY' +INSERT INTO nt_2 VALUES (USER(), 24), (USER(), 25); +ERROR 23000: Duplicate entry '25' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 26", 26); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B T INSERT M...SELECT* C" with an error in INSERT M...SELECT* generates +*** in the binlog the following entries: "Nothing". +*** There is a bug in that will be fixed after WL#2687. Please, check BUG#47175 for further details. + +TRUNCATE TABLE nt_2; +TRUNCATE TABLE tt_2; +INSERT INTO tt_2 VALUES ("new text 7", 7); +BEGIN; +INSERT INTO tt_2 VALUES ("new text 27", 27); +INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1; +ERROR 23000: Duplicate entry '7' for key 'PRIMARY' +INSERT INTO tt_2 VALUES ("new text 28", 28); +ROLLBACK; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE nt_2 +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE tt_2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B INSERT M..SELECT* C" with an error in INSERT M...SELECT* generates +*** in the binlog the following entries: "B INSERT M..SELECT* R". + +TRUNCATE TABLE nt_2; +TRUNCATE TABLE tt_2; +INSERT INTO tt_2 VALUES ("new text 7", 7); +BEGIN; +INSERT INTO nt_2(a, b) SELECT USER(), b FROM nt_1; +ERROR 23000: Duplicate entry '7' for key 'PRIMARY' +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE nt_2 +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE tt_2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_2) +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK + + + +*** "B N N T C" generates in the binlog the "B N C B N C B T C" entries + +TRUNCATE TABLE nt_1; +TRUNCATE TABLE tt_2; +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 1); +INSERT INTO nt_1 VALUES (USER(), 2); +INSERT INTO tt_2 VALUES (USER(), 3); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE nt_1 +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; TRUNCATE TABLE tt_2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B N N T R" generates in the binlog the "B N C B N C B T R" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 4); +INSERT INTO nt_1 VALUES (USER(), 5); +INSERT INTO tt_2 VALUES (USER(), 6); +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK + + + +*** "B N* N* T C" with error in N* generates in the binlog the "B N R B N R B T C" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 7), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO nt_1 VALUES (USER(), 8), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO tt_2 VALUES (USER(), 9); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B N* N* T R" with error in N* generates in the binlog the "B N R B N R B T R" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 10), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO nt_1 VALUES (USER(), 11), (USER(), 1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +INSERT INTO tt_2 VALUES (USER(), 12); +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK + + + +*** "B N N T N T C" generates in the binlog the "B N C B N C B T N T C" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 13); +INSERT INTO nt_1 VALUES (USER(), 14); +INSERT INTO tt_2 VALUES (USER(), 15); +INSERT INTO nt_1 VALUES (USER(), 16); +INSERT INTO tt_2 VALUES (USER(), 17); +COMMIT; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ + + + +*** "B N N T N T R" generates in the binlog the "B N C B N C B T N T R" entries + +BEGIN; +INSERT INTO nt_1 VALUES (USER(), 18); +INSERT INTO nt_1 VALUES (USER(), 19); +INSERT INTO tt_2 VALUES (USER(), 20); +INSERT INTO nt_1 VALUES (USER(), 21); +INSERT INTO tt_2 VALUES (USER(), 22); +ROLLBACK; +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.nt_1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.tt_2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK +################################################################################### +# CLEAN +################################################################################### +DROP TABLE tt_1; +DROP TABLE tt_2; +DROP TABLE nt_1; +DROP TABLE nt_2; diff --git a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result index 2d11fec5787..4ccc3b5e797 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result @@ -133,6 +133,10 @@ master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT insert into t1 values(11); commit; show binlog events from <binlog_start>; @@ -144,6 +148,8 @@ master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ @@ -272,6 +278,10 @@ master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; drop table t1,t2 master-bin.000001 # Query # # use `test`; create table t0 (n int) master-bin.000001 # Query # # BEGIN @@ -372,7 +382,7 @@ master-bin.000001 # Query # # use `test`; DROP TABLE if exists t2 master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t2 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb master-bin.000001 # Query # # BEGIN @@ -390,9 +400,11 @@ master-bin.000001 # Query # # use `test`; DROP TABLE t2 master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F @@ -400,7 +412,7 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F -master-bin.000001 # Query # # ROLLBACK +master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; TRUNCATE table t2 master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) diff --git a/mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_verbose.result b/mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_verbose.result new file mode 100644 index 00000000000..2687b21213a --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_row_mysqlbinlog_verbose.result @@ -0,0 +1,161 @@ +Verbose statements from : write-partial-row.binlog +select replace(txt,'\r', '') as stmt from raw_binlog_rows where txt like '###%'; +stmt +### INSERT INTO mysql.ndb_apply_status +### SET +### @1=1 +### @2=25769803786 +### @3='' +### @4=0 +### @5=0 +### INSERT INTO test.ba +### SET +### @1=3 +### @2=3 +### @3=3 +### INSERT INTO test.ba +### SET +### @1=1 +### @2=1 +### @3=1 +### INSERT INTO test.ba +### SET +### @1=2 +### @2=2 +### @3=2 +### INSERT INTO test.ba +### SET +### @1=4 +### @2=4 +### @3=4 +### INSERT INTO test.ba +### SET +### @1=4 +### @3=40 +### DELETE FROM test.ba +### WHERE +### @1=2 +drop table raw_binlog_rows; +Verbose statements from : write-full-row.binlog +select replace(txt,'\r', '') as stmt from raw_binlog_rows where txt like '###%'; +stmt +### INSERT INTO mysql.ndb_apply_status +### SET +### @1=2 +### @2=25769803786 +### @3='' +### @4=0 +### @5=0 +### INSERT INTO test.ba +### SET +### @1=3 +### @2=3 +### @3=3 +### INSERT INTO test.ba +### SET +### @1=1 +### @2=1 +### @3=1 +### INSERT INTO test.ba +### SET +### @1=2 +### @2=2 +### @3=2 +### INSERT INTO test.ba +### SET +### @1=4 +### @2=4 +### @3=4 +### INSERT INTO test.ba +### SET +### @1=4 +### @2=4 +### @3=40 +### DELETE FROM test.ba +### WHERE +### @1=2 +drop table raw_binlog_rows; +Verbose statements from : update-partial-row.binlog +select replace(txt,'\r', '') as stmt from raw_binlog_rows where txt like '###%'; +stmt +### INSERT INTO mysql.ndb_apply_status +### SET +### @1=3 +### @2=25769803786 +### @3='' +### @4=0 +### @5=0 +### INSERT INTO test.ba +### SET +### @1=3 +### @2=3 +### @3=3 +### INSERT INTO test.ba +### SET +### @1=1 +### @2=1 +### @3=1 +### INSERT INTO test.ba +### SET +### @1=2 +### @2=2 +### @3=2 +### INSERT INTO test.ba +### SET +### @1=4 +### @2=4 +### @3=4 +### UPDATE test.ba +### WHERE +### @1=4 +### @3=4 +### SET +### @1=4 +### @3=40 +### DELETE FROM test.ba +### WHERE +### @1=2 +drop table raw_binlog_rows; +Verbose statements from : update-full-row.binlog +select replace(txt,'\r', '') as stmt from raw_binlog_rows where txt like '###%'; +stmt +### INSERT INTO mysql.ndb_apply_status +### SET +### @1=4 +### @2=25769803786 +### @3='' +### @4=0 +### @5=0 +### INSERT INTO test.ba +### SET +### @1=3 +### @2=3 +### @3=3 +### INSERT INTO test.ba +### SET +### @1=1 +### @2=1 +### @3=1 +### INSERT INTO test.ba +### SET +### @1=2 +### @2=2 +### @3=2 +### INSERT INTO test.ba +### SET +### @1=4 +### @2=4 +### @3=4 +### UPDATE test.ba +### WHERE +### @1=4 +### @2=4 +### @3=4 +### SET +### @1=4 +### @2=4 +### @3=40 +### DELETE FROM test.ba +### WHERE +### @1=2 +drop table raw_binlog_rows; diff --git a/mysql-test/suite/binlog/r/binlog_stm_binlog.result b/mysql-test/suite/binlog/r/binlog_stm_binlog.result index d05d3ccdb7a..eebcfceaa25 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_binlog.result +++ b/mysql-test/suite/binlog/r/binlog_stm_binlog.result @@ -784,3 +784,24 @@ INSERT INTO test.t1 VALUES (1), (2); CREATE TABLE test.t2 SELECT * FROM test.t1; USE test; DROP TABLES t1, t2; +RESET MASTER; +CREATE TABLE t1 (a INT PRIMARY KEY); +BINLOG ' +3u9kSA8KAAAAZgAAAGoAAAABAAQANS4xLjM1LW1hcmlhLWJldGExLWRlYnVnLWxvZwAAAAAAAAAA +AAAAAAAAAAAAAAAAAADe72RIEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC +'; +INSERT INTO t1 VALUES (1); +BINLOG ' +3u9kSBMUAAAAKQAAAJEBAAAAABoAAAAAAAAABHRlc3QAAnQxAAEDAAA= +3u9kSBcUAAAAIgAAALMBAAAQABoAAAAAAAEAAf/+AgAAAA== +'; +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +# # Format_desc 1 # Server ver: #, Binlog ver: # +# # Query 1 # use `test`; CREATE TABLE t1 (a INT PRIMARY KEY) +# # Query 1 # use `test`; INSERT INTO t1 VALUES (1) +# # Query 1 # BEGIN +# # Table_map 1 # table_id: # (test.t1) +# # Write_rows 1 # table_id: # flags: STMT_END_F +# # Query 1 # COMMIT +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/std_data/update-full-row.binlog b/mysql-test/suite/binlog/std_data/update-full-row.binlog Binary files differnew file mode 100644 index 00000000000..866a351033e --- /dev/null +++ b/mysql-test/suite/binlog/std_data/update-full-row.binlog diff --git a/mysql-test/suite/binlog/std_data/update-partial-row.binlog b/mysql-test/suite/binlog/std_data/update-partial-row.binlog Binary files differnew file mode 100644 index 00000000000..67e3611aa3a --- /dev/null +++ b/mysql-test/suite/binlog/std_data/update-partial-row.binlog diff --git a/mysql-test/suite/binlog/std_data/write-full-row.binlog b/mysql-test/suite/binlog/std_data/write-full-row.binlog Binary files differnew file mode 100644 index 00000000000..b9edb89e91a --- /dev/null +++ b/mysql-test/suite/binlog/std_data/write-full-row.binlog diff --git a/mysql-test/suite/binlog/std_data/write-partial-row.binlog b/mysql-test/suite/binlog/std_data/write-partial-row.binlog Binary files differnew file mode 100644 index 00000000000..7424ec4e940 --- /dev/null +++ b/mysql-test/suite/binlog/std_data/write-partial-row.binlog diff --git a/mysql-test/suite/binlog/t/binlog_mixed_failure_mixing_engines.test b/mysql-test/suite/binlog/t/binlog_mixed_failure_mixing_engines.test new file mode 100644 index 00000000000..09b168c2882 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_mixed_failure_mixing_engines.test @@ -0,0 +1,4 @@ +--source include/have_binlog_format_mixed.inc +--source include/have_innodb.inc + +--source extra/binlog_tests/binlog_failure_mixing_engines.test diff --git a/mysql-test/suite/binlog/t/binlog_row_failure_mixing_engines.test b/mysql-test/suite/binlog/t/binlog_row_failure_mixing_engines.test new file mode 100644 index 00000000000..e25af7a7e10 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_row_failure_mixing_engines.test @@ -0,0 +1,4 @@ +--source include/have_binlog_format_row.inc +--source include/have_innodb.inc + +--source extra/binlog_tests/binlog_failure_mixing_engines.test diff --git a/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_verbose.test b/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_verbose.test new file mode 100644 index 00000000000..42d92e1a44d --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_verbose.test @@ -0,0 +1,86 @@ +######################################################## +# Test mysqlbinlog command with Ndb produced Binlog +# variants +# +# WHAT +# ==== +# This test aims to check that the mysqlbinlog --verbose +# command can output binlogs in 4 format variants, currently +# used by Ndb +# +# 1) Updates logged as write_row events +# Only primary key and updated columns included in the +# event +# 2) Updates logged as write_row_events +# All columns included in the event +# 3) Updates logged as update_row events +# Only primary key and updated columns included in the +# event +# 4) Updates logged as update_row events +# All columns included in the event +# +# Format variant (1) is the Ndb default. +# Bug#47323 resulted in binlogs generated in format (1) +# being incorrectly parsed by the mysqlbinlog --verbose +# option +# +# HOW +# === +# Row-based binlog files in each format have been +# captured from an Ndb cluster +# These are output using the mysqlbinlog --verbose +# tool and the output is checked. +# +######################################################## + +# We require binlog_format_row as we're independent of binlog format +# and there's no point running the same test 3 times +-- source include/have_binlog_format_row.inc + +--disable_query_log +--let $binlog_file=write-partial-row.binlog +--exec $MYSQL_BINLOG --verbose suite/binlog/std_data/$binlog_file > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql +create table raw_binlog_rows (txt varchar(1000)); +--eval load data local infile '$MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql' into table raw_binlog_rows columns terminated by '\n'; +--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql +--enable_query_log +--echo Verbose statements from : $binlog_file +# Output --verbose lines, with extra Windows CR's trimmed +select replace(txt,'\r', '') as stmt from raw_binlog_rows where txt like '###%'; +drop table raw_binlog_rows; + +--disable_query_log +--let $binlog_file=write-full-row.binlog +--exec $MYSQL_BINLOG --verbose suite/binlog/std_data/$binlog_file > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql +create table raw_binlog_rows (txt varchar(1000)); +--eval load data local infile '$MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql' into table raw_binlog_rows columns terminated by '\n'; +--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql +--enable_query_log +--echo Verbose statements from : $binlog_file +# Output --verbose lines, with extra Windows CR's trimmed +select replace(txt,'\r', '') as stmt from raw_binlog_rows where txt like '###%'; +drop table raw_binlog_rows; + +--disable_query_log +--let $binlog_file=update-partial-row.binlog +--exec $MYSQL_BINLOG --verbose suite/binlog/std_data/$binlog_file > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql +create table raw_binlog_rows (txt varchar(1000)); +--eval load data local infile '$MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql' into table raw_binlog_rows columns terminated by '\n'; +--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql +--enable_query_log +--echo Verbose statements from : $binlog_file +# Output --verbose lines, with extra Windows CR's trimmed +select replace(txt,'\r', '') as stmt from raw_binlog_rows where txt like '###%'; +drop table raw_binlog_rows; + +--disable_query_log +--let $binlog_file=update-full-row.binlog +--exec $MYSQL_BINLOG --verbose suite/binlog/std_data/$binlog_file > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql +create table raw_binlog_rows (txt varchar(1000)); +--eval load data local infile '$MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql' into table raw_binlog_rows columns terminated by '\n'; +--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_verbose.sql +--enable_query_log +--echo Verbose statements from : $binlog_file +# Output --verbose lines, with extra Windows CR's trimmed +select replace(txt,'\r', '') as stmt from raw_binlog_rows where txt like '###%'; +drop table raw_binlog_rows; diff --git a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test index a5472952f08..21c11d5a3df 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test +++ b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test @@ -94,15 +94,24 @@ DROP TABLE t1; SET GLOBAL log_warnings = @old_log_warnings; -let LOG_ERROR= `SELECT @@GLOBAL.log_error`; +let $log_error_= `SELECT @@GLOBAL.log_error`; +if(!`select LENGTH('$log_error_')`) +{ + # MySQL Server on windows is started with --console and thus + # does not know the location of its .err log, use default location + let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.1.err; +} +# Assign env variable LOG_ERROR +let LOG_ERROR=$log_error_; --echo # Count the number of times the "Unsafe" message was printed --echo # to the error log. perl; - $log_error= $ENV{'LOG_ERROR'}; + use strict; + my $log_error= $ENV{'LOG_ERROR'} or die "LOG_ERROR not set"; open(FILE, "$log_error") or die("Unable to open $log_error: $!\n"); - $count = () = grep(/Bug#46265/g,<FILE>); + my $count = () = grep(/Bug#46265/g,<FILE>); print "Occurrences: $count\n"; close(FILE); EOF diff --git a/mysql-test/suite/innodb/t/innodb-zip.test b/mysql-test/suite/innodb/t/innodb-zip.test index bba5ccfbcd3..5bcd0e3c824 100644 --- a/mysql-test/suite/innodb/t/innodb-zip.test +++ b/mysql-test/suite/innodb/t/innodb-zip.test @@ -105,12 +105,6 @@ drop table t1; --error ER_TOO_BIG_ROWSIZE CREATE TABLE t1(c TEXT, PRIMARY KEY (c(440))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; -# 439 throws error with certain system zlib (ubuntu "intrepid") -# but not with zlib bundled with MySQL, because zlib's compressBound() -# are different (and used by InnoDB's page_zip_empty_size()); see -# http://www.linux-archive.org/archlinux-development/119356-zlib-1-2-3-3-1-a.html -# "Fix compressBound(), was low for some pathological cases [Fearnley]". -# 438 works with both zlib-s. CREATE TABLE t1(c TEXT, PRIMARY KEY (c(438))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512)); diff --git a/mysql-test/suite/rpl/r/rpl_row_create_table.result b/mysql-test/suite/rpl/r/rpl_row_create_table.result index 5bed9106009..b7122adea2a 100644 --- a/mysql-test/suite/rpl/r/rpl_row_create_table.result +++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result @@ -176,7 +176,7 @@ Log_name Pos Event_type Server_id End_log_pos Info # 106 Query # 174 BEGIN # 174 Table_map # 216 table_id: # (test.t7) # 216 Write_rows # 272 table_id: # flags: STMT_END_F -# 272 Query # 343 ROLLBACK +# 272 Query # 341 COMMIT SELECT * FROM t7 ORDER BY a,b; a b 1 2 @@ -327,7 +327,7 @@ Log_name Pos Event_type Server_id End_log_pos Info # 1329 Query # 1397 BEGIN # 1397 Table_map # 1438 table_id: # (test.t1) # 1438 Write_rows # 1482 table_id: # flags: STMT_END_F -# 1482 Query # 1553 ROLLBACK +# 1482 Query # 1551 COMMIT SHOW TABLES; Tables_in_test t1 diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_circular_simplex.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_circular_simplex.result index 01f8d94da48..b6f32668c42 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_circular_simplex.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_circular_simplex.result @@ -102,3 +102,4 @@ Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 0 Last_SQL_Error +DROP TABLE t1; diff --git a/mysql-test/suite/rpl_ndb/t/rpl_ndb_circular.test b/mysql-test/suite/rpl_ndb/t/rpl_ndb_circular.test index 2cc46e2420e..7b8497d8dab 100644 --- a/mysql-test/suite/rpl_ndb/t/rpl_ndb_circular.test +++ b/mysql-test/suite/rpl_ndb/t/rpl_ndb_circular.test @@ -58,3 +58,4 @@ STOP SLAVE; # cleanup --connection master DROP TABLE t1; +-- sync_slave_with_master diff --git a/mysql-test/suite/rpl_ndb/t/rpl_ndb_circular_simplex.test b/mysql-test/suite/rpl_ndb/t/rpl_ndb_circular_simplex.test index d5ddfc2b739..eb04dc2e260 100644 --- a/mysql-test/suite/rpl_ndb/t/rpl_ndb_circular_simplex.test +++ b/mysql-test/suite/rpl_ndb/t/rpl_ndb_circular_simplex.test @@ -78,3 +78,7 @@ SELECT * FROM t1 ORDER BY a; --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 # 35 # 36 # query_vertical SHOW SLAVE STATUS; + +-- connection master +DROP TABLE t1; +-- sync_slave_with_master diff --git a/mysql-test/t/ctype_ldml.test b/mysql-test/t/ctype_ldml.test index db9461bfbf7..bc04b93a935 100644 --- a/mysql-test/t/ctype_ldml.test +++ b/mysql-test/t/ctype_ldml.test @@ -86,3 +86,8 @@ select hex(c1) as h, c1 from t1 order by c1, h; select group_concat(hex(c1) order by hex(c1)) from t1 group by c1; select group_concat(c1 order by hex(c1) SEPARATOR '') from t1 group by c1; drop table t1; + +--echo Bug#46448 trailing spaces are not ignored when user collation maps space != 0x20 +set names latin1; +show collation like 'latin1_test'; +select "foo" = "foo " collate latin1_test; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 6f0b1716d38..8b34bd03faa 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -14,3 +14,4 @@ innodb_bug39438 : Bug#42383 2009-01-28 lsoares "This fails in embedded query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically partition_innodb_builtin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes partition_innodb_plugin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes +innodb_bug46000 : Bug#47860 2009-10-16 satyab Test fails for innodb plugin 1.0.5 diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index b0a3d0feb79..6e39795a5d6 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1006,3 +1006,51 @@ DROP TABLE t1; ### --echo End of 5.0 tests + +--echo # +--echo # BUG#47280 - strange results from count(*) with order by multiple +--echo # columns without where/group +--echo # + +--echo # +--echo # Initialize test +--echo # + +CREATE TABLE t1 ( + pk INT NOT NULL, + i INT, + PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1,11),(2,12),(3,13); + +--echo # +--echo # Start test +--echo # All the following queries shall return 1 record +--echo # + +--echo +--echo # Masking all correct values {11...13} for column i in this result. +--replace_column 2 # +SELECT MAX(pk) as max, i +FROM t1 +ORDER BY max; + +--echo +EXPLAIN +SELECT MAX(pk) as max, i +FROM t1 +ORDER BY max; + +--echo +--echo # Only 11 is correct for collumn i in this result +SELECT MAX(pk) as max, i +FROM t1 +WHERE pk<2 +ORDER BY max; + +--echo # +--echo # Cleanup +--echo # +DROP TABLE t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/innodb-consistent-master.opt b/mysql-test/t/innodb-consistent-master.opt new file mode 100644 index 00000000000..8cca44767da --- /dev/null +++ b/mysql-test/t/innodb-consistent-master.opt @@ -0,0 +1 @@ +--innodb_lock_wait_timeout=2
diff --git a/mysql-test/t/innodb-consistent.test b/mysql-test/t/innodb-consistent.test new file mode 100644 index 00000000000..791600fc8a7 --- /dev/null +++ b/mysql-test/t/innodb-consistent.test @@ -0,0 +1,58 @@ +-- source include/not_embedded.inc
+-- source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+# REPLACE INTO ... SELECT and INSERT INTO ... SELECT should do
+# a consistent read of the source table.
+
+connect (a,localhost,root,,);
+connect (b,localhost,root,,);
+connection a;
+set session transaction isolation level read committed;
+create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
+create table t2 like t1;
+insert into t2 values (1),(2),(3),(4),(5),(6),(7);
+set autocommit=0;
+
+# REPLACE INTO ... SELECT case
+begin;
+# this should not result in any locks on t2.
+replace into t1 select * from t2;
+
+connection b;
+set session transaction isolation level read committed;
+set autocommit=0;
+# should not cuase a lock wait.
+delete from t2 where a=5;
+commit;
+delete from t2;
+commit;
+connection a;
+commit;
+
+# INSERT INTO ... SELECT case
+begin;
+# this should not result in any locks on t2.
+insert into t1 select * from t2;
+
+connection b;
+set session transaction isolation level read committed;
+set autocommit=0;
+# should not cuase a lock wait.
+delete from t2 where a=5;
+commit;
+delete from t2;
+commit;
+connection a;
+commit;
+
+select * from t1;
+drop table t1;
+drop table t2;
+
+connection default;
+disconnect a;
+disconnect b;
diff --git a/mysql-test/t/innodb_bug34300.test b/mysql-test/t/innodb_bug34300.test index 432ddd03547..0bd50abcabf 100644 --- a/mysql-test/t/innodb_bug34300.test +++ b/mysql-test/t/innodb_bug34300.test @@ -9,6 +9,7 @@ -- disable_result_log # set packet size and reconnect +let $max_packet=`select @@global.max_allowed_packet`; SET @@global.max_allowed_packet=16777216; --connect (newconn, localhost, root,,) @@ -32,6 +33,7 @@ SELECT f4, f8 FROM bug34300; DROP TABLE bug34300; +EVAL SET @@global.max_allowed_packet=$max_packet; disconnect newconn; connection default; SET @@global.max_allowed_packet=default; diff --git a/mysql-test/t/innodb_bug44369.test b/mysql-test/t/innodb_bug44369.test new file mode 100644 index 00000000000..495059eb5e6 --- /dev/null +++ b/mysql-test/t/innodb_bug44369.test @@ -0,0 +1,21 @@ +# This is the test for bug 44369. We should +# block table creation with columns match +# some innodb internal reserved key words, +# both case sensitively and insensitely. + +--source include/have_innodb.inc + +# This create table operation should fail. +--error ER_CANT_CREATE_TABLE +create table bug44369 (DB_ROW_ID int) engine=innodb; + +# This create should fail as well +--error ER_CANT_CREATE_TABLE +create table bug44369 (db_row_id int) engine=innodb; + +show errors; + +--error ER_CANT_CREATE_TABLE +create table bug44369 (db_TRX_Id int) engine=innodb; + +show errors; diff --git a/mysql-test/t/innodb_bug44571.test b/mysql-test/t/innodb_bug44571.test new file mode 100644 index 00000000000..685463ceff9 --- /dev/null +++ b/mysql-test/t/innodb_bug44571.test @@ -0,0 +1,17 @@ +# +# Bug#44571 InnoDB Plugin crashes on ADD INDEX +# http://bugs.mysql.com/44571 +# +-- source include/have_innodb.inc + +CREATE TABLE bug44571 (foo INT) ENGINE=InnoDB; +ALTER TABLE bug44571 CHANGE foo bar INT; +-- error ER_KEY_COLUMN_DOES_NOT_EXITS +ALTER TABLE bug44571 ADD INDEX bug44571b (foo); +# The following will fail, because the CHANGE foo bar was +# not communicated to InnoDB. +--error ER_NOT_KEYFILE +ALTER TABLE bug44571 ADD INDEX bug44571b (bar); +--error ER_NOT_KEYFILE +CREATE INDEX bug44571b ON bug44571 (bar); +DROP TABLE bug44571; diff --git a/mysql-test/t/innodb_bug46000.test b/mysql-test/t/innodb_bug46000.test new file mode 100644 index 00000000000..80c18c58ef0 --- /dev/null +++ b/mysql-test/t/innodb_bug46000.test @@ -0,0 +1,34 @@ +# This is the test for bug 46000. We shall +# block any index creation with the name of +# "GEN_CLUST_INDEX", which is the reserved +# name for innodb default primary index. + +--source include/have_innodb.inc + +# This 'create table' operation should fail because of +# using the reserve name as its index name. +--error ER_CANT_CREATE_TABLE +create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb; + +# Mixed upper/lower case of the reserved key words +--error ER_CANT_CREATE_TABLE +create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb; + +show errors; + +create table bug46000(id int) engine=innodb; + +# This 'create index' operation should fail. +--replace_regex /'[^']*test.#sql-[0-9a-f_]*'/'#sql-temporary'/ +--error ER_CANT_CREATE_TABLE +create index GEN_CLUST_INDEX on bug46000(id); + +--replace_regex /'[^']*test.#sql-[0-9a-f_]*'/'#sql-temporary'/ +show errors; + +# This 'create index' operation should succeed, no +# temp table left from last failed create index +# operation. +create index idx on bug46000(id); + +drop table bug46000; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 5de7c997a24..e4ea939f348 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1518,5 +1518,36 @@ CREATE TABLE t3 select * from t1; checksum table t3; drop table t1,t2,t3; + +# +# BUG#47073 - valgrind errs, corruption,failed repair of partition, +# low myisam_sort_buffer_size +# +CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); +INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), + (6,'0'),(7,'0'); +INSERT INTO t1 SELECT a+10,b FROM t1; +INSERT INTO t1 SELECT a+20,b FROM t1; +INSERT INTO t1 SELECT a+40,b FROM t1; +INSERT INTO t1 SELECT a+80,b FROM t1; +INSERT INTO t1 SELECT a+160,b FROM t1; +INSERT INTO t1 SELECT a+320,b FROM t1; +INSERT INTO t1 SELECT a+640,b FROM t1; +INSERT INTO t1 SELECT a+1280,b FROM t1; +INSERT INTO t1 SELECT a+2560,b FROM t1; +INSERT INTO t1 SELECT a+5120,b FROM t1; +SET myisam_sort_buffer_size=4; +REPAIR TABLE t1; + +# !!! Disabled until additional fix for BUG#47073 is pushed. +#SET myisam_repair_threads=2; +# May report different values depending on threads activity. +#--replace_regex /changed from [0-9]+/changed from #/ +#REPAIR TABLE t1; +#SET myisam_repair_threads=@@global.myisam_repair_threads; + +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index cca1e3209cc..ac2bbaaeeac 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1402,3 +1402,35 @@ SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9; DROP TABLE t1; + +--echo # +--echo # Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering +--echo # is used +--echo # + +CREATE TABLE t1 (a INT, b INT, KEY (a)); + +INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL); +INSERT INTO t1 SELECT a+4, b FROM t1; +INSERT INTO t1 SELECT a+8, b FROM t1; + +CREATE TABLE t2 (a INT, b INT); + +INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL); +INSERT INTO t2 SELECT a+4, b FROM t2; + +--echo # shouldn't have "using filesort" +EXPLAIN +SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; + +--echo # should have "using filesort" +EXPLAIN +SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a; + +--echo # should have "using filesort" +EXPLAIN +SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a; + +DROP TABLE t1, t2; + +--echo End of 5.1 tests diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index c5ed098b678..1dfc53c6232 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -62,6 +62,19 @@ SHOW CREATE TABLE t1; DROP TABLE t1; # +# Bug#44059: rec_per_key on empty partition gives weird optimiser results +# +create table t1 (a int, b int, key(a)) +partition by list (a) +( partition p0 values in (1), + partition p1 values in (2)); +insert into t1 values (1,1),(2,1),(2,2),(2,3); +show indexes from t1; +analyze table t1; +show indexes from t1; +drop table t1; + +# # Bug#36001: Partitions: spelling and using some error messages # --error ER_FOREIGN_KEY_ON_PARTITIONED diff --git a/mysql-test/t/partition_open_files_limit-master.opt b/mysql-test/t/partition_open_files_limit-master.opt new file mode 100644 index 00000000000..4c1ed0c3da3 --- /dev/null +++ b/mysql-test/t/partition_open_files_limit-master.opt @@ -0,0 +1 @@ +--open-files-limit=5 --max_connections=2 --table_open_cache=1 diff --git a/mysql-test/t/partition_open_files_limit.test b/mysql-test/t/partition_open_files_limit.test new file mode 100644 index 00000000000..e62ebd0ade7 --- /dev/null +++ b/mysql-test/t/partition_open_files_limit.test @@ -0,0 +1,26 @@ +--source include/have_partition.inc + +--disable_warnings +DROP TABLE IF EXISTS `t1`; +--enable_warnings + +# On some platforms the lowest possible open_files_limit is too high... +let $max_open_files_limit= `SELECT @@open_files_limit > 511`; +if ($max_open_files_limit) +{ + skip Need open_files_limit to be lower than 512; +} + +# +--echo # Bug#46922: crash when adding partitions and open_files_limit is reached +# +CREATE TABLE t1 (a INT PRIMARY KEY) +ENGINE=MyISAM PARTITION BY KEY () PARTITIONS 1; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); +--echo # if the bug exists, then crash will happen here +--replace_regex /file '.*'/file '<partition file>'/ +--error 23 +ALTER TABLE t1 ADD PARTITION PARTITIONS 511; +--sorted_result +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index e1411e7fd46..dc119b6a77e 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1046,3 +1046,128 @@ explain select * from t2 where a=1000 and b<11; drop table t1, t2; +# +# Bug#42846: wrong result returned for range scan when using covering index +# +CREATE TABLE t1( a INT, b INT, KEY( a, b ) ); + +CREATE TABLE t2( a INT, b INT, KEY( a, b ) ); + +CREATE TABLE t3( a INT, b INT, KEY( a, b ) ); + +INSERT INTO t1( a, b ) +VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7); + +INSERT INTO t2( a, b ) +VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1), + ( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1), + (11, 1), (12, 1), (13, 1), (14, 1), (15, 1), + (16, 1), (17, 1), (18, 1), (19, 1), (20, 1); + +INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1; +INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1; + +# To make range scan compelling to the optimizer +INSERT INTO t2 SELECT -1, -1 FROM t2; +INSERT INTO t2 SELECT -1, -1 FROM t2; +INSERT INTO t2 SELECT -1, -1 FROM t2; + +INSERT INTO t3 +VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0), + (6, 0), (7, 0), (8, 0), (9, 0), (10, 0); + +# To make range scan compelling to the optimizer +INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; +INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; + + +# +# Problem#1 Test queries. Will give missing results unless Problem#1 is fixed. +# With one exception, they are independent of Problem#2. +# +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 < a AND b = 3 OR +3 <= a; + +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 < a AND b = 3 OR +3 <= a; + +# Query below: Tests both Problem#1 and Problem#2 (EXPLAIN differs as well) +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 <= a AND b = 3 OR +3 <= a; + +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a < 5 OR +5 <= a AND b = 3 OR +3 <= a; + +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +5 <= a AND b = 3 OR +3 <= a; + +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +5 <= a AND b = 3 OR +3 <= a; + +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +3 <= a; + +EXPLAIN +SELECT * FROM t1 WHERE +3 <= a AND a <= 5 OR +3 <= a; + +# +# Problem#2 Test queries. +# These queries will give missing results if Problem#1 is fixed. +# But Problem#1 also hides this bug. +# +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 1 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; + +EXPLAIN +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 1 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; + +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 2 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; + +EXPLAIN +SELECT * FROM t2 WHERE +5 <= a AND a < 10 AND b = 2 OR +15 <= a AND a < 20 AND b = 3 +OR +1 <= a AND b = 1; + +SELECT * FROM t3 WHERE +5 <= a AND a < 10 AND b = 3 OR +a < 5 OR +a < 10; + +EXPLAIN +SELECT * FROM t3 WHERE +5 <= a AND a < 10 AND b = 3 OR +a < 5 OR +a < 10; + +DROP TABLE t1, t2, t3; diff --git a/mysql-test/t/type_bit.test b/mysql-test/t/type_bit.test index dc5120db430..530389b3ab9 100644 --- a/mysql-test/t/type_bit.test +++ b/mysql-test/t/type_bit.test @@ -397,6 +397,17 @@ insert into t2bit7 values (b'110011011111111'); select bin(a1) from t1bit7, t2bit7 where t1bit7.a1=t2bit7.b1; drop table t1bit7, t2bit7; + +--echo # +--echo # Bug42803: Field_bit does not have unsigned_flag field, +--echo # can lead to bad memory access +--echo # +CREATE TABLE t1 (a BIT(7), b BIT(9), KEY(a, b)); +INSERT INTO t1 VALUES(0, 0), (5, 3), (5, 6), (6, 4), (7, 0); +EXPLAIN SELECT a+0, b+0 FROM t1 WHERE a > 4 and b < 7 ORDER BY 2; +DROP TABLE t1; + + --echo End of 5.0 tests # |