diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
| commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
| tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/binlog/include | |
| parent | Initial commit. (diff) | |
| download | mariadb-upstream.tar.xz mariadb-upstream.zip | |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/binlog/include')
31 files changed, 7185 insertions, 0 deletions
diff --git a/mysql-test/suite/binlog/include/binlog.test b/mysql-test/suite/binlog/include/binlog.test new file mode 100644 index 00000000..9f3288b5 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog.test @@ -0,0 +1,427 @@ +# +# misc binlogging tests that do not require a slave running +# + +-- source include/have_log_bin.inc +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings +reset master; + +create table t1 (a int) engine=innodb; +create table t2 (a int) engine=innodb; +begin; +insert t1 values (5); +commit; +begin; +insert t2 values (5); +commit; +# first COMMIT must be Query_log_event, second - Xid_log_event +source include/show_binlog_events.inc; +drop table t1,t2; + +# +# binlog rotation after one big transaction +# +reset master; +let $1=100; + +create table t1 (n int) engine=innodb; +begin; +--disable_query_log +while ($1) +{ + eval insert into t1 values($1 + 4); + dec $1; +} +--enable_query_log +commit; +# Make sure binlog checkpoint is logged before DROP TABLE to avoid random +# result difference. +--source include/wait_for_binlog_checkpoint.inc +drop table t1; +--source include/show_binlog_events.inc +--let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) +--source include/show_binlog_events.inc +--let $binlog_file= + +# +# Bug#22540 - Incorrect value in column End_log_pos of +# SHOW BINLOG EVENTS using InnoDB +# + +# the following tests will show that certain queries now return +# absolute offsets (from binlog start, rather than relative to +# the beginning of the current transaction). under what +# conditions it should be allowed / is sensible to put the +# slider into the middle of a transaction is not our concern +# here; we just guarantee that if and when it's done, the +# user has valid offsets to use. if the setter function still +# wants to throw a "positioning into middle of transaction" +# warning, that's its prerogative and handled elsewhere. + +set @ac = @@autocommit; + +# first show this to work for SHOW BINLOG EVENTS + +set autocommit= 0; +reset master; +create table t1(n int) engine=innodb; +begin; +insert into t1 values (1); +insert into t1 values (2); +insert into t1 values (3); +commit; +drop table t1; +--source include/show_binlog_events.inc + +# now show that nothing breaks if we need to read from the cache more +# than once, resulting in split event-headers + +set @bcs = @@binlog_cache_size; +set global binlog_cache_size=4096; +reset master; + +create table t1 (a int, b char(255)) engine=innodb; + +flush status; +show status like "binlog_cache_use"; + +let $1=100; +disable_query_log; +begin; +while ($1) +{ + eval insert into t1 values( $1, 'just to fill void to make transaction occupying at least two buffers of the trans cache' ); + dec $1; +} +commit; +--echo *** the following must show the counter value = 1 *** +show status like "binlog_cache_use"; +enable_query_log; + +--source include/show_binlog_events.inc + +drop table t1; + +set global binlog_cache_size=@bcs; +set session autocommit = @ac; + +# +# Bug#33798: prepared statements improperly handle large unsigned ints +# +--disable_warnings +drop table if exists t1; +--enable_warnings +reset master; +create table t1 (a bigint unsigned, b bigint(20) unsigned); +prepare stmt from "insert into t1 values (?,?)"; +set @a= 9999999999999999; +set @b= 14632475938453979136; +execute stmt using @a, @b; +deallocate prepare stmt; +drop table t1; +--source include/show_binlog_events.inc + + +# +# Bug #39182: Binary log producing incompatible character set query from +# stored procedure. +# +reset master; +CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +USE bug39182; +CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci) + DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; + +DELIMITER //; + +CREATE PROCEDURE p1() +BEGIN + DECLARE s1 VARCHAR(255); + SET s1= "test"; + CREATE TEMPORARY TABLE tmp1 + SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%"); + SELECT + COLLATION(NAME_CONST('s1', _utf8'test')) c1, + COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2, + COLLATION(s1) c3, + COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1, + COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2, + COERCIBILITY(s1) d3; + DROP TEMPORARY TABLE tmp1; +END// + +DELIMITER ;// + +CALL p1(); +source include/show_binlog_events.inc; + +DROP PROCEDURE p1; +DROP TABLE t1; +DROP DATABASE bug39182; +USE test; + +# +# Bug#35383: binlog playback and replication breaks due to +# name_const substitution +# +DELIMITER //; +CREATE PROCEDURE p1(IN v1 INT) +BEGIN + CREATE TABLE t1 SELECT v1; + DROP TABLE t1; +END// +CREATE PROCEDURE p2() +BEGIN + DECLARE v1 INT; + CREATE TABLE t1 SELECT v1+1; + DROP TABLE t1; +END// +CREATE PROCEDURE p3(IN v1 INT) +BEGIN + CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0; + DROP TABLE t1; +END// +CREATE PROCEDURE p4(IN v1 INT) +BEGIN + DECLARE v2 INT; + CREATE TABLE t1 SELECT 1, v1, v2; + DROP TABLE t1; + CREATE TABLE t1 SELECT 1, v1+1, v2; + DROP TABLE t1; +END// +DELIMITER ;// + +CALL p1(1); +CALL p2(); +CALL p3(0); +CALL p4(0); +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; + +--echo End of 5.0 tests + +# Test of a too big SET INSERT_ID. +# This should generate an error and should not be put in binlog +# We look at the binlog further down with SHOW BINLOG EVENTS. + +reset master; +create table t1 (id tinyint auto_increment primary key); +insert into t1 values(5); +set insert_id=128; +--error HA_ERR_AUTOINC_ERANGE +insert into t1 values(null) /* Not binlogged */; + +# The followin insert ignore will be put in binlog +set insert_id=128; +insert ignore into t1 values(null) /* Insert 128 */; + +# Insert with duplicate key error should not go into binglo +set insert_id=5; +--error ER_DUP_ENTRY +insert into t1 values(null) /* Not binlogged */; + +# Insert with autogenerated key + duplicate key error should go into binlog +set insert_id=5; +insert ignore into t1 values(null) /* Insert 5 */; +select * from t1; +drop table t1; + +# Same tests but with 2 rows inserted at a time + +create table t1 (id tinyint auto_increment primary key) engine=myisam; +set insert_id=128; +--error HA_ERR_AUTOINC_ERANGE +insert into t1 values(5),(null) /* Insert_id 128 */; + +# The followin insert ignore will be put in binlog +set insert_id=128; +insert ignore into t1 values (4),(null) /* Insert_id 128 */; + +# Insert with duplicate key error should not go into binglo +set insert_id=5; +--error ER_DUP_ENTRY +insert into t1 values(3),(null) /* Insert_id 5 */; + +# Insert with autogenerated key + duplicate key error should go into binlog +set insert_id=5; +insert ignore into t1 values(2),(null) /* Insert_id 5 */; +select * from t1 order by id; +drop table t1; + + +# bug#22027 +create table t1 (a int); +create table if not exists t2 select * from t1; + +# bug#22762 +create temporary table tt1 (a int); +create table if not exists t3 like tt1; + +# BUG#25091 (A DELETE statement to mysql database is not logged with +# ROW mode format): Checking that some basic operations on tables in +# the mysql database is replicated even when the current database is +# 'mysql'. + +USE mysql; +INSERT db SET host='localhost', user='@#@', db='Just a test'; +UPDATE db SET db='Another db' WHERE host='localhost' AND user='@#@'; +DELETE FROM db WHERE host='localhost' AND user='@#@'; + +use test; +source include/show_binlog_events.inc; +drop table t1,t2,t3,tt1; + +-- source suite/binlog/include/binlog_insert_delayed.test + +#Bug #26079 max_binlog_size + innodb = not make new binlog and hang server +# server should not hang, binlog must rotate in the end +reset master; +--disable_warnings +drop table if exists t3; +--enable_warnings +create table t3 (a int(11) NOT NULL AUTO_INCREMENT, b text, PRIMARY KEY (a) ) engine=innodb; +--let $binlog_file1= query_get_value(SHOW MASTER STATUS, File, 1) +--echo File $binlog_file1 +let $it=4; +while ($it) +{ +insert into t3(b) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +dec $it; +} +--let $binlog_file2= query_get_value(SHOW MASTER STATUS, File, 1) +--echo *** show new binlog index after rotating *** +--echo File $binlog_file2 +drop table t3; + +--echo # +--echo # Bug #45998: database crashes when running "create as select" +--echo # +CREATE DATABASE test1; +USE test1; +DROP DATABASE test1; +CREATE TABLE test.t1(a int); +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; + +--echo +--echo # BUG#54903 BINLOG statement toggles session variables +--echo # ---------------------------------------------------------------------- +--echo # This test verify that BINLOG statement doesn't change current session's +--echo # variables foreign_key_checks and unique_checks. +--echo +CREATE TABLE t1 (c1 INT KEY); + +SET @@SESSION.foreign_key_checks= ON; +SET @@SESSION.unique_checks= ON; + +--echo # INSERT INTO t1 VALUES (1) +--echo # foreign_key_checks=0 and unique_checks=0 +BINLOG ' +dfLtTBMBAAAAKQAAANcAAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE= +dfLtTBcBAAAAIgAAAPkAAAAAABcAAAAAAAcAAf/+AQAAAA== +'; + +SELECT * FROM t1; +--echo # Their values should be ON +SHOW SESSION VARIABLES LIKE "foreign_key_checks"; +SHOW SESSION VARIABLES LIKE "unique_checks"; + +--echo +SET @@SESSION.foreign_key_checks= OFF; +SET @@SESSION.unique_checks= OFF; + +--echo # INSERT INTO t1 VALUES(2) +--echo # foreign_key_checks=1 and unique_checks=1 +BINLOG ' +dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE= +dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA== +'; + +SELECT * FROM t1; +--echo # Their values should be OFF +SHOW SESSION VARIABLES LIKE "foreign_key_checks"; +SHOW SESSION VARIABLES LIKE "unique_checks"; + +--echo # INSERT INTO t1 VALUES(2) +--echo # foreign_key_checks=1 and unique_checks=1 +--echo # It should not change current session's variables, even error happens +call mtr.add_suppression("BINLOG_BASE64_EVENT.*Could not execute Write_rows_v1 event on table test.t1; Duplicate entry .2. for key .PRIMARY., Error_code: 1062"); +--error 1062 +BINLOG ' +dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE= +dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA== +'; + +SELECT * FROM t1; +--echo # Their values should be OFF +SHOW SESSION VARIABLES LIKE "foreign_key_checks"; +SHOW SESSION VARIABLES LIKE "unique_checks"; + +DROP TABLE t1; + +disconnect fresh; +connection default; + +--echo # +--echo # MDEV-25595 DROP part of failed CREATE OR REPLACE is not written into binary log +--echo # +reset master; +--error ER_DUP_FIELDNAME +create table t as select 1 as b, 2 as b; +create table t (old_table_field int); +--error ER_DUP_FIELDNAME +create or replace table t as select 1 as b, 2 as b; +--error ER_DUP_FIELDNAME +create or replace temporary table t as select 1 as b, 2 as b; +create table t (new_table_field int); + +--source include/show_binlog_events.inc + +# cleanup +drop table t; diff --git a/mysql-test/suite/binlog/include/binlog_cache_stat.test b/mysql-test/suite/binlog/include/binlog_cache_stat.test new file mode 100644 index 00000000..c6287a6b --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_cache_stat.test @@ -0,0 +1,258 @@ +# Embedded server doesn't support binlog +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +# Creating tables +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +create table t1 (a int) engine=innodb; +create table t2 (a int) engine=myisam; + +# +# This test checks binlog_cache_use and binlog_cache_disk_use when +# transactions are committed and after when they are aborted. +# + +set @save_binlog_stmt_cache_size=@@global.binlog_stmt_cache_size; +set @save_binlog_cache_size=@@global.binlog_cache_size; +set @@global.binlog_stmt_cache_size=32768; +set @@global.binlog_cache_size=32768; +# +# Checking commit. +# +--echo **** Preparing the enviroment to check commit and its effect on status variables. +--echo **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. +flush status; +let $exp_cache= 0; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 0; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} + +--echo **** Transactional changes which are long enough so they will be flushed to disk... +--echo **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. +let $1=2000; +disable_query_log; +begin; +while ($1) +{ + eval insert into t1 values( $1 ); + dec $1; +} +commit; +enable_query_log; +let $exp_cache= 1; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 1; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} + +--echo **** Transactional changes which should not be flushed to disk and so should not +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. +begin; +insert into t1 values( 1 ); +commit; +let $exp_cache= 2; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 1; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} + +--echo **** Non-Transactional changes which should not be flushed to disk and so should not +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. +begin; +insert into t2 values( 1 ); +commit; +let $exp_cache= 2; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 1; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 1; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} + +--echo **** Mixed changes which should not be flushed to disk and so should not +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. +begin; +insert into t1 values( 1 ); +insert into t2 values( 1 ); +commit; +let $exp_cache= 3; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 1; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 2; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} + +# +# Checking abort. +# +--echo **** Preparing the enviroment to check abort and its effect on the status variables. +--echo **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. +flush status; +let $exp_cache= 0; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 0; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} + +--echo **** Transactional changes which are long enough so they will be flushed to disk... +--echo **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. +let $1=2000; +disable_query_log; +begin; +while ($1) +{ + eval insert into t1 values( $1 ); + dec $1; +} +rollback; +enable_query_log; +let $exp_cache= 1; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 1; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} + +--echo **** Transactional changes which should not be flushed to disk and so should not +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. +begin; +insert into t1 values( 1 ); +rollback; +let $exp_cache= 2; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 1; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} + +--echo **** Non-Transactional changes which should not be flushed to disk and so should not +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. +begin; +insert into t2 values( 1 ); +rollback; +let $exp_cache= 2; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 1; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 1; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} + +--echo **** Mixed changes which should not be flushed to disk and so should not +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. +begin; +insert into t1 values( 1 ); +insert into t2 values( 1 ); +rollback; +let $exp_cache= 3; +let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); +let $exp_disk= 1; +let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); +let $exp_stmt_cache= 2; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) +{ + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" + -- die +} +drop table t1, t2; + +set @@global.binlog_stmt_cache_size=@save_binlog_stmt_cache_size; +set @@global.binlog_cache_size=@save_binlog_cache_size; diff --git a/mysql-test/suite/binlog/include/binlog_expire_logs_seconds.inc b/mysql-test/suite/binlog/include/binlog_expire_logs_seconds.inc new file mode 100644 index 00000000..3b3fef55 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_expire_logs_seconds.inc @@ -0,0 +1,109 @@ +# +# WL#9237: Add a new variable binlog_expire_logs_seconds + +# Here we will test purging of binary logs when either one or both of these variables are set +# - binlog_expire_logs_seconds +# - expire_logs_days + +# The three scenarios being tested for are: +# 1. FLUSH LOGS +# 2. Rotation of logs because of binlog growing bigger than max_binlog_size +# 3. Server restart +# +# Usuage: --let $binlog_expire_logs_seconds= +# --let $expire_logs_days= +# +# --source suite/binlog/include/binlog_expire_logs_seconds.inc + +--let $expire_logs_seconds= `SELECT @@global.binlog_expire_logs_seconds` + +CREATE TABLE t1(s LONGBLOB ); + +--let $max_binlog_size_save= `SELECT @@GLOBAL.MAX_BINLOG_SIZE` + +--let $case= 0 + +while ($case < 3) +{ + --echo Case:$case + --let $first_binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) + + # rotates the log, thence the first log will be closed, and depending upon + # the expire time the purge will/will not happen. + + FLUSH LOGS; + + INSERT INTO t1 VALUES('a'); + + --let $second_binlog_file= query_get_value(SHOW MASTER STATUS, File, 1) + + FLUSH LOGS; + + + # This is done to avoid time out in cases where the expire time is more. + # What we do is in those cases modify the timestamp of the oldest log file + # to be the same as expire time so when we execute the next flush log command + # the oldest log will be purged. + + # Only change the timestamp of binlog file when the expire_logs_seconds which is the total + # time for expiring log is greater than 30 seconds + + if (`SELECT $expire_logs_seconds > 30`) + { + --let _EXPIRE_TIME= `SELECT $expire_logs_seconds + 60` + --let _FIRST_BINLOG_FILE= $MYSQLD_DATADIR/$first_binlog_file +--perl + use strict; + use warnings; + my $expire_time = $ENV{'_EXPIRE_TIME'}; + my $first_binlog_file = $ENV{'_FIRST_BINLOG_FILE'}; + my $epoch = (stat($first_binlog_file))[9]; + my $mtime = $epoch - $expire_time; + utime $mtime, $mtime, $first_binlog_file; + EOF + } + + # Checking this ensures that nothing is purged so far. + --file_exists $MYSQLD_DATADIR/$first_binlog_file + + if ($case == 0) + { + --echo #### 1. FLUSH LOGS + + FLUSH LOGS; + } + if ($case == 1) + { + --echo #### 2. Binlog_size > max_binlog_size + + SET @@GLOBAL.MAX_BINLOG_SIZE= 4096; + + INSERT INTO t1 (s) VALUES (REPEAT('s',50000)); + } + if ($case == 2) + { + --echo #### 3. Server restart + + --let $restart_parameters=--binlog_expire_logs_seconds=$expire_logs_seconds + --source include/restart_mysqld.inc + } + + if (`SELECT $expire_logs_seconds != 0`) + { + --error 1 + --file_exists $MYSQLD_DATADIR/$first_binlog_file + } + if ($expire_logs_seconds == 0) + { + --file_exists $MYSQLD_DATADIR/$first_binlog_file + } + --file_exists $MYSQLD_DATADIR/$second_binlog_file + + --inc $case + RESET MASTER; +} +--echo ##### Cleanup ##### +--eval SET @@GLOBAL.MAX_BINLOG_SIZE= $max_binlog_size_save; +DROP TABLE t1; +RESET MASTER; + diff --git a/mysql-test/suite/binlog/include/binlog_expire_warnings.inc b/mysql-test/suite/binlog/include/binlog_expire_warnings.inc new file mode 100644 index 00000000..3f879bff --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_expire_warnings.inc @@ -0,0 +1,103 @@ +# ==== Purpose ==== +# +# Test one scenario using a combination of --expire-logs-days and +# --binlog-expire-logs-seconds. Verify that there are/aren't warnings +# as expected, and verify that the values of the global variables are +# as expected. +# +# ==== Usage ==== +# +# --let $ofile = FILE +# --let $options = SERVER_OPTIONS +# --let $days = VALUE +# --let $seconds = VALUE +# --let $expect_binlog_off_days_and_seconds_warning = [0|1] +# --let $expect_seconds = VALUE +# --let $option_invoke_order = [days_then_seconds|seconds_then_days] +# --let $expect_days = VALUE +# --source include/binlog_expire_warnings.inc +# +# Parameters: +# +# $ofile +# Temporary file to use for the error log. +# +# $options +# Any additional options passed to mysqld during server start. +# +# $days +# The value to set for --expire-logs-days +# +# $seconds +# The value to set for --binlog-expire-logs-seconds +# +# $option_invoke_order +# In which order options --expire-logs-days and +# --binlog-expire-logs-seconds should be set. +# values are: days_then_seconds, seconds_then_days +# +# $expect_binlog_off_days_and_seconds_warning +# If zero, assert that there is no warning due to using +# --expire_logs_days or --binlog_expire_logs_seconds binlog disabled. +# If nonzero, assert that there is a warning due to using +# --expire_logs_days or --binlog_expire_logs_seconds binlog disabled. +# +# $expect_days +# Assert that @@global.expire_logs_days has this value. +# +# $expect_seconds +# Assert that @@global.binlog_expire_logs_seconds has this value. + + +--let $restart_parameters = --log-error=$ofile $options +if ($option_invoke_order == 'days_then_seconds') +{ + if ($days != '') { + --let $restart_parameters = $restart_parameters --expire_logs_days=$days + } + if ($seconds != '') { + --let $restart_parameters = $restart_parameters --binlog-expire-logs-seconds=$seconds + } +} + +if ($option_invoke_order == 'seconds_then_days') +{ + if ($seconds != '') { + --let $restart_parameters = $restart_parameters --binlog-expire-logs-seconds=$seconds + } + if ($days != '') { + --let $restart_parameters = $restart_parameters --expire_logs_days=$days + } +} + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--source include/restart_mysqld.inc + +# For all the assert_grep.inc +--let $extra_debug_eval = LOAD_FILE("$ofile") + +if (!$expect_binlog_off_days_and_seconds_warning) { + --let $assert_text = There shall be no binlog_off+seconds warning + --let $assert_count = 0 +} + +if ($expect_binlog_off_days_and_seconds_warning) { + --let $assert_text = There shall be a binlog_off+seconds warning + --let $assert_count = 1 +} + +--let $assert_file = $ofile +--let $assert_select = You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work. +--let $assert_only_after = Shutdown complete +--source include/assert_grep.inc + +# For all the assert.inc +--let $extra_debug_eval = CONCAT("days: ", @@global.expire_logs_days, " seconds: ", @@global.binlog_expire_logs_seconds) + +--let $assert_text = binlog_expire_logs_seconds shall be $expect_seconds +--let $assert_cond = @@global.binlog_expire_logs_seconds = $expect_seconds +--source include/assert.inc + +--let $assert_text = expire_logs_days shall be $expect_days +--let $assert_cond = @@global.expire_logs_days = $expect_days +--source include/assert.inc diff --git a/mysql-test/suite/binlog/include/binlog_incident-master.opt b/mysql-test/suite/binlog/include/binlog_incident-master.opt new file mode 100644 index 00000000..57ce0081 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_incident-master.opt @@ -0,0 +1 @@ +--loose-debug=+d,incident_database_resync_on_replace
\ No newline at end of file diff --git a/mysql-test/suite/binlog/include/binlog_incident.inc b/mysql-test/suite/binlog/include/binlog_incident.inc new file mode 100644 index 00000000..dd37a586 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_incident.inc @@ -0,0 +1,68 @@ +# +# This include file is used by more than one test suite +# (currently rpl and binlog_encryption). +# Please check all dependent tests after modifying it +# +# Usage: +# +# --let $use_remote_mysqlbinlog= 1 # optional +# --let $binlog_start_pos= <binlog position> # optional +# --let $binlog_file= <binlog filename> # optional +# +# --source suite/binlog/include/binlog_incident.inc +# +# The script uses MYSQLBINLOG to verify certain results. +# By default, it uses binary logs directly. If it is undesirable, +# this behavior can be overridden by setting $use_remote_binlog +# as shown above. +# +# All values will be unset after every execution of the script, +# so if they are needed, they should be set explicitly before each call. +# + +# The purpose of this test is to provide a reference for how the +# incident log event is represented in the output from the mysqlbinlog +# program. + +source include/have_log_bin.inc; +source include/have_debug.inc; +source include/binlog_start_pos.inc; + +let $MYSQLD_DATADIR= `select @@datadir`; +RESET MASTER; + +CREATE TABLE t1 (a INT); + +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1; + +# This will generate an incident log event and store it in the binary +# log before the replace statement. +REPLACE INTO t1 VALUES (4); + +DROP TABLE t1; +FLUSH LOGS; + +if ($binlog_start_pos) +{ + --let $startpos= --start-position=$binlog_start_pos + --let $binlog_start_pos= +} +--let $filename= master-bin.000001 +if ($binlog_file) +{ + --let $filename= $binlog_file + --let $binlog_file= +} +--let $mysqlbinlog_args= $MYSQLD_DATADIR/$filename +if ($use_remote_mysqlbinlog) +{ + --let $mysqlbinlog_args= --read-from-remote-server --protocol=tcp --host=127.0.0.1 --port=$MASTER_MYPORT -uroot $filename + --let $use_remote_mysqlbinlog= 0 +} +exec $MYSQL_BINLOG $startpos $mysqlbinlog_args >$MYSQLTEST_VARDIR/tmp/binlog_incident-bug44442.sql; +--disable_query_log +eval SELECT cont LIKE '%RELOAD DATABASE; # Shall generate syntax error%' AS `Contain RELOAD DATABASE` FROM (SELECT load_file('$MYSQLTEST_VARDIR/tmp/binlog_incident-bug44442.sql') AS cont) AS tbl; +--enable_query_log + +remove_file $MYSQLTEST_VARDIR/tmp/binlog_incident-bug44442.sql; diff --git a/mysql-test/suite/binlog/include/binlog_index.inc b/mysql-test/suite/binlog/include/binlog_index.inc new file mode 100644 index 00000000..da6cac18 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_index.inc @@ -0,0 +1,278 @@ +# +# This include file is used by more than one test suite +# (currently binlog and binlog_encryption). +# Please check all dependent tests after modifying it +# + +# +# testing of purging of binary log files bug#18199/Bug#18453 +# +source include/have_log_bin.inc; +source include/not_embedded.inc; +# Don't test this under valgrind, memory leaks will occur +--source include/not_valgrind.inc +source include/have_debug.inc; +# Avoid CrashReporter popup on Mac +--source include/not_crashrep.inc +call mtr.add_suppression('Attempting backtrace'); +call mtr.add_suppression('MYSQL_BIN_LOG::purge_logs failed to process registered files that would be purged.'); +call mtr.add_suppression('MYSQL_BIN_LOG::open failed to sync the index file'); +call mtr.add_suppression('Turning logging off for the whole duration of the MariaDB server process.'); +call mtr.add_suppression('Could not use .*'); +call mtr.add_suppression('MYSQL_BIN_LOG::purge_logs failed to clean registers before purging logs.'); +flush tables; + +SET @saved_dbug = @@SESSION.debug_dbug; + +RESET MASTER; + +let $MYSQLD_DATADIR= `select @@datadir`; +let $INDEX=$MYSQLD_DATADIR/master-bin.index; + +# +# testing purge binary logs TO +# + +flush logs; +flush logs; +flush logs; + +source include/show_binary_logs.inc; +remove_file $MYSQLD_DATADIR/master-bin.000001; +flush tables; + +# there must be a warning with file names +replace_regex /\.[\\\/]master/master/; +--source include/wait_for_binlog_checkpoint.inc +purge binary logs TO 'master-bin.000004'; + +--echo *** must show a list starting from the 'TO' argument of PURGE *** +source include/show_binary_logs.inc; + +# +# testing purge binary logs BEFORE +# + +reset master; + +flush logs; +flush logs; +flush logs; +remove_file $MYSQLD_DATADIR/master-bin.000001; + +--echo *** must be a warning master-bin.000001 was not found *** +let $date=`select NOW() + INTERVAL 1 MINUTE`; +--disable_query_log +replace_regex /\.[\\\/]master/master/; +--source include/wait_for_binlog_checkpoint.inc +eval purge binary logs BEFORE '$date'; +--enable_query_log + +--echo *** must show one record, of the active binlog, left in the index file after PURGE *** +source include/show_binary_logs.inc; + +# +# testing a fatal error +# Turning a binlog file into a directory must be a portable setup +# + +reset master; + +flush logs; +flush logs; +flush logs; + +remove_file $MYSQLD_DATADIR/master-bin.000001; +mkdir $MYSQLD_DATADIR/master-bin.000001; + +--source include/wait_for_binlog_checkpoint.inc +--error ER_BINLOG_PURGE_FATAL_ERR +purge binary logs TO 'master-bin.000002'; +replace_regex /\.[\\\/]master/master/; +show warnings; +rmdir $MYSQLD_DATADIR/master-bin.000001; +--disable_warnings +reset master; +--enable_warnings + +--echo # crash_purge_before_update_index +flush logs; + +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +SET SESSION debug_dbug="+d,crash_purge_before_update_index"; +--source include/wait_for_binlog_checkpoint.inc +--error 2013 +purge binary logs TO 'master-bin.000002'; + +--enable_reconnect +--source include/wait_until_connected_again.inc + +file_exists $MYSQLD_DATADIR/master-bin.000001; +file_exists $MYSQLD_DATADIR/master-bin.000002; +file_exists $MYSQLD_DATADIR/master-bin.000003; +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--echo # crash_purge_non_critical_after_update_index +flush logs; + +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +SET SESSION debug_dbug="+d,crash_purge_non_critical_after_update_index"; +--source include/wait_for_binlog_checkpoint.inc +--error 2013 +purge binary logs TO 'master-bin.000004'; + +--enable_reconnect +--source include/wait_until_connected_again.inc + +--error 1 +file_exists $MYSQLD_DATADIR/master-bin.000001; +--error 1 +file_exists $MYSQLD_DATADIR/master-bin.000002; +--error 1 +file_exists $MYSQLD_DATADIR/master-bin.000003; +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--echo # crash_purge_critical_after_update_index +flush logs; + +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +SET SESSION debug_dbug="+d,crash_purge_critical_after_update_index"; +--source include/wait_for_binlog_checkpoint.inc +--error 2013 +purge binary logs TO 'master-bin.000006'; + +--enable_reconnect +--source include/wait_until_connected_again.inc + +--error 1 +file_exists $MYSQLD_DATADIR/master-bin.000004; +--error 1 +file_exists $MYSQLD_DATADIR/master-bin.000005; +file_exists $MYSQLD_DATADIR/master-bin.000006; +file_exists $MYSQLD_DATADIR/master-bin.000007; +--error 1 +file_exists $MYSQLD_DATADIR/master-bin.000008; +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--echo # crash_create_non_critical_before_update_index +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +SET SESSION debug_dbug="+d,crash_create_non_critical_before_update_index"; +--error 2013 +flush logs; + +--enable_reconnect +--source include/wait_until_connected_again.inc + +file_exists $MYSQLD_DATADIR/master-bin.000008; +--error 1 +file_exists $MYSQLD_DATADIR/master-bin.000009; +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--echo # crash_create_critical_before_update_index +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +SET SESSION debug_dbug="+d,crash_create_critical_before_update_index"; +--error 2013 +flush logs; + +--enable_reconnect +--source include/wait_until_connected_again.inc + +file_exists $MYSQLD_DATADIR/master-bin.000009; +--error 1 +file_exists $MYSQLD_DATADIR/master-bin.000010; +--error 1 +file_exists $MYSQLD_DATADIR/master-bin.000011; +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--echo # crash_create_after_update_index +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +SET SESSION debug_dbug="+d,crash_create_after_update_index"; +--error 2013 +flush logs; + +--enable_reconnect +--source include/wait_until_connected_again.inc + +file_exists $MYSQLD_DATADIR/master-bin.000010; +file_exists $MYSQLD_DATADIR/master-bin.000011; +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--echo # +--echo # This should put the server in unsafe state and stop +--echo # accepting any command. If we inject a fault at this +--echo # point and continue the execution the server crashes. +--echo # + +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--echo # fault_injection_registering_index +SET SESSION debug_dbug="+d,fault_injection_registering_index"; +-- replace_regex /\.[\\\/]master/master/ +-- error ER_CANT_OPEN_FILE +flush logs; + +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--source include/restart_mysqld.inc + +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--echo # fault_injection_updating_index +SET SESSION debug_dbug="+d,fault_injection_updating_index"; +-- replace_regex /\.[\\\/]master/master/ +-- error ER_CANT_OPEN_FILE +flush logs; + +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +--source include/restart_mysqld.inc + +--chmod 0644 $INDEX +-- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +-- eval SET @index=LOAD_FILE('$index') +-- replace_regex /\.[\\\/]master/master/ +SELECT @index; + +SET @@SESSION.debug_dbug = @saved_dbug; + +--echo End of tests diff --git a/mysql-test/suite/binlog/include/binlog_insert_delayed.test b/mysql-test/suite/binlog/include/binlog_insert_delayed.test new file mode 100644 index 00000000..8f669fc1 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_insert_delayed.test @@ -0,0 +1,71 @@ +# ==== Purpose ==== +# +# Verify that INSERT DELAYED in mixed or row mode writes events to the +# binlog, and that AUTO_INCREMENT works correctly. +# +# ==== Method ==== +# +# Insert both single and multiple rows into an autoincrement column, +# both with specified value and with NULL. +# +# With INSERT DELAYED, the rows do not show up in the table +# immediately, so we must do source include/wait_until_rows_count.inc +# between any two INSERT DELAYED statements. Moreover, if mixed or +# row-based logging is used, there is also a delay between when rows +# show up in the table and when they show up in the binlog. To ensure +# that the rows show up in the binlog, we call FLUSH TABLES, which +# waits until the delayed_insert thread has finished. +# +# We cannot read the binlog after executing INSERT DELAYED statements +# that insert multiple rows, because that is nondeterministic. More +# precisely, rows may be written in batches to the binlog, where each +# batch has one Table_map_log_event and one or more +# Write_rows_log_event. The number of rows included in each batch is +# nondeterministic. +# +# ==== Related bugs ==== +# +# BUG#20627: INSERT DELAYED does not honour auto_increment_* variables +# Bug in this test: BUG#38068: binlog_stm_binlog fails sporadically in pushbuild + +reset master; + +create table t1 (a int not null auto_increment, primary key (a)) engine=myisam; + +let $table=t1; +let $count=0; + +insert /* before delayed */ delayed /* after delayed */ into t1 values (207); +inc $count; +--source include/wait_until_rows_count.inc + +insert /*! delayed */ into t1 values (null); +inc $count; +--source include/wait_until_rows_count.inc + +insert delayed into t1 values (300); +inc $count; +--source include/wait_until_rows_count.inc + +# It is not enough to wait until all rows have been inserted into the +# table. FLUSH TABLES ensures that they are in the binlog too. See +# comment above. +FLUSH TABLES; +source include/show_binlog_events.inc; + +RESET MASTER; +insert /* before delayed */ delayed /* after delayed */ into t1 values (null),(null),(null),(null); +inc $count; inc $count; inc $count; inc $count; +--source include/wait_until_rows_count.inc + +insert /*! delayed */ into t1 values (null),(null),(400),(null); +inc $count; inc $count; inc $count; inc $count; +--source include/wait_until_rows_count.inc + +if (`SELECT @@SESSION.BINLOG_FORMAT = 'STATEMENT'`) { + FLUSH TABLES; + source include/show_binlog_events.inc; +} + +select * from t1; +drop table t1; diff --git a/mysql-test/suite/binlog/include/binlog_ioerr.inc b/mysql-test/suite/binlog/include/binlog_ioerr.inc new file mode 100644 index 00000000..da6fb5ac --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_ioerr.inc @@ -0,0 +1,37 @@ +# +# This include file is used by more than one test suite +# (currently binlog and binlog_encryption). +# Please check all dependent tests after modifying it +# + +source include/have_debug.inc; +source include/have_innodb.inc; +source include/have_log_bin.inc; +source include/have_binlog_format_mixed_or_statement.inc; + +CALL mtr.add_suppression("Error writing file 'master-bin'"); + +RESET MASTER; + +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb; +INSERT INTO t1 VALUES(0); +SET @saved_dbug = @@SESSION.debug_dbug; +SET SESSION debug_dbug='+d,fail_binlog_write_1'; +--error ER_ERROR_ON_WRITE +INSERT INTO t1 VALUES(1); +--error ER_ERROR_ON_WRITE +INSERT INTO t1 VALUES(2); +SET SESSION debug_dbug=@saved_dbug; +INSERT INTO t1 VALUES(3); +SELECT * FROM t1; + +# Actually the output from this currently shows a bug. +# The injected IO error leaves partially written transactions in the binlog in +# the form of stray "BEGIN" events. +# These should disappear from the output if binlog error handling is improved +# (see MySQL Bug#37148 and WL#1790). +--replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/ +--replace_column 1 BINLOG 2 POS 5 ENDPOS +SHOW BINLOG EVENTS; + +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/include/binlog_mysqlbinlog-cp932.inc b/mysql-test/suite/binlog/include/binlog_mysqlbinlog-cp932.inc new file mode 100644 index 00000000..3646c34b --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_mysqlbinlog-cp932.inc @@ -0,0 +1,50 @@ +# +# This include file is used by more than one test suite +# (currently binlog and binlog_encryption). +# Please check all dependent tests after modifying it +# +# Usage: +# --let $use_remote_mysqlbinlog= 1 # optional +# --source suite/binlog/include/binlog_mysqlbinlog-cp932.inc +# +# By default, the script calls mysqlbinlog to read binary logs directly. +# If it is undesirable, this behavior can be overridden by setting +# $use_remote_binlog as shown above. +# The value will be unset after every execution of the script, +# so if it is needed, it should be set explicitly before each call. + + +# disabled in embedded until tools running is fixed with embedded +--source include/not_embedded.inc + +-- source include/have_binlog_format_mixed_or_statement.inc +-- source include/have_cp932.inc +-- source include/have_log_bin.inc + +RESET MASTER; + +# Bug#16217 (mysql client did not know how not switch its internal charset) +create table t3 (f text character set utf8); +create table t4 (f text character set cp932); +--exec $MYSQL --default-character-set=utf8 test -e "insert into t3 values(_utf8'ソ')" +--exec $MYSQL --default-character-set=cp932 test -e "insert into t4 values(_cp932'ƒ\');" +flush logs; +rename table t3 to t03, t4 to t04; +let $MYSQLD_DATADIR= `select @@datadir`; + +--let $mysqlbinlog_args= $MYSQLD_DATADIR/master-bin.000001 +if ($use_remote_mysqlbinlog) +{ + --let $mysqlbinlog_args= --read-from-remote-server --protocol=tcp --host=127.0.0.1 --port=$MASTER_MYPORT -uroot master-bin.000001 + --let $use_remote_mysqlbinlog= 0 +} + +--exec $MYSQL_BINLOG --short-form $mysqlbinlog_args | $MYSQL --default-character-set=utf8 +# original and recovered data must be equal +select HEX(f) from t03; +select HEX(f) from t3; +select HEX(f) from t04; +select HEX(f) from t4; + +drop table t3, t4, t03, t04; +--echo End of 5.0 tests diff --git a/mysql-test/suite/binlog/include/binlog_row_annotate.inc b/mysql-test/suite/binlog/include/binlog_row_annotate.inc new file mode 100644 index 00000000..b827c17a --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_row_annotate.inc @@ -0,0 +1,193 @@ +# +# This include file is used by more than one test suite +# (currently binlog and binlog_encryption). +# Please check all dependent tests after modifying it +# +# Usage: +# --let use_remote_mysqlbinlog= 1 # optional +# --source suite/binlog/include/binlog_row_annotate.inc +# +# By default, the script uses mysqlbinlog both with direct access to files +# and via connection to the server. In some cases, direct access to files +# might be impossible (e.g. with encryption). If use_remote_mysqlbinlog +# flag is set, this part of the logic will be omitted. +# + +############################################################################### +# WL47: Store in binlog text of statements that caused RBR events +# new event: ANNOTATE_ROWS_EVENT +# new master option: --binlog-annotate-row-events +# new mysqlbinlog option: --skip-annotate-row-events +# +# Intended to test that: +# *** If the --binlog-annotate-row-events option is switched on on master +# then Annotate_rows events: +# - are generated; +# - are generated only once for "multi-table-maps" rbr queries; +# - are not generated when the corresponding queries are filtered away; +# - are generated when the corresponding queries are filtered away partialy +# (e.g. in case of multi-delete). +# *** Annotate_rows events are printed by mysqlbinlog started without +# --skip-annotate-row-events options both in remote and local cases. +# *** Annotate_rows events are not printed by mysqlbinlog started with +# --skip-annotate-row-events options both in remote and local cases. +############################################################################### + +set @old_binlog_checksum=@@binlog_checksum; +set global binlog_checksum=NONE; +--let datadir= `select @@datadir` + +--source include/have_log_bin.inc +--source include/binlog_start_pos.inc +--source include/have_binlog_format_row.inc + +set sql_mode=""; +let $collation_server=`select @@collation_server`; + +# Fix timestamp to avoid varying results +SET timestamp=1000000000; + +# Delete all existing binary logs +RESET MASTER; + +CREATE DATABASE test1; +CREATE TABLE test1.t1(a int); + +CREATE DATABASE test2; +CREATE TABLE test2.t2(a int); +CREATE VIEW test2.v2 AS SELECT * FROM test2.t2; + +CREATE DATABASE test3; +CREATE TABLE test3.t3(a int); + +CREATE DATABASE xtest1; +CREATE TABLE xtest1.xt1(a int); + +CREATE DATABASE xtest2; +CREATE TABLE xtest2.xt2(a int); + +# By default SESSION binlog_annotate_row_events = OFF + +INSERT INTO test1.t1 VALUES (1), (2), (3); + +SET SESSION binlog_annotate_row_events = ON; + +INSERT INTO test2.t2 VALUES (1), (2), (3); +INSERT INTO test3.t3 VALUES (1), (2), (3); + +# This query generates two Table maps but the Annotate +# event should appear only once before the first Table map +DELETE test1.t1, test2.t2 + FROM test1.t1 INNER JOIN test2.t2 INNER JOIN test3.t3 + WHERE test1.t1.a=test2.t2.a AND test2.t2.a=test3.t3.a; + +# This event should be filtered out together with Annotate event +INSERT INTO xtest1.xt1 VALUES (1), (2), (3); + +# This event should pass the filter +INSERT INTO test2.v2 VALUES (1), (2), (3); + +# This event should pass the filter only for test2.t2 part +DELETE xtest1.xt1, test2.t2 + FROM xtest1.xt1 INNER JOIN test2.t2 INNER JOIN test3.t3 + WHERE xtest1.xt1.a=test2.t2.a AND test2.t2.a=test3.t3.a; + +# These events should be filtered out together with Annotate events +INSERT INTO xtest1.xt1 VALUES (1), (2), (3); +INSERT INTO xtest2.xt2 VALUES (1), (2), (3); +DELETE xtest1.xt1, xtest2.xt2 + FROM xtest1.xt1 INNER JOIN xtest2.xt2 INNER JOIN test3.t3 + WHERE xtest1.xt1.a=xtest2.xt2.a AND xtest2.xt2.a=test3.t3.a; + +FLUSH LOGS; + +--echo ##################################################################################### +--echo # The following Annotate_rows events should appear below: +--echo # - INSERT INTO test2.t2 VALUES (1), (2), (3) +--echo # - INSERT INTO test3.t3 VALUES (1), (2), (3) +--echo # - DELETE test1.t1, test2.t2 FROM <...> +--echo # - INSERT INTO test2.t2 VALUES (1), (2), (3) +--echo # - DELETE xtest1.xt1, test2.t2 FROM <...> +--echo ##################################################################################### + +--source include/show_binlog_events.inc + +if (!$use_remote_mysqlbinlog) +{ + --echo # + --echo ##################################################################################### + --echo # mysqlbinlog + --echo # The following Annotates should appear in this output: + --echo # - INSERT INTO test2.t2 VALUES (1), (2), (3) + --echo # - INSERT INTO test3.t3 VALUES (1), (2), (3) + --echo # - DELETE test1.t1, test2.t2 FROM <...> (with two subsequent Table maps) + --echo # - INSERT INTO test2.t2 VALUES (1), (2), (3) + --echo # - DELETE xtest1.xt1, test2.t2 FROM <...> (with one subsequent Table map) + --echo ##################################################################################### + + --replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/ + --exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $datadir/master-bin.000001 + + --echo # + --echo ##################################################################################### + --echo # mysqlbinlog --database=test1 + --echo # The following Annotate should appear in this output: + --echo # - DELETE test1.t1, test2.t2 FROM <...> + --echo ##################################################################################### + + --replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/ + --exec $MYSQL_BINLOG --base64-output=decode-rows --database=test1 -v -v $datadir/master-bin.000001 + + --echo # + --echo ##################################################################################### + --echo # mysqlbinlog --skip-annotate-row-events + --echo # No Annotates should appear in this output + --echo ##################################################################################### + + --replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/ + --exec $MYSQL_BINLOG --base64-output=decode-rows --skip-annotate-row-events -v -v $datadir/master-bin.000001 + + --let use_remote_mysqlbinlog= 0 +} + +--echo # +--echo ##################################################################################### +--echo # mysqlbinlog --read-from-remote-server +--echo # The following Annotates should appear in this output: +--echo # - INSERT INTO test2.t2 VALUES (1), (2), (3) +--echo # - INSERT INTO test3.t3 VALUES (1), (2), (3) +--echo # - DELETE test1.t1, test2.t2 FROM <...> (with two subsequent Table maps) +--echo # - INSERT INTO test2.t2 VALUES (1), (2), (3) +--echo # - DELETE xtest1.xt1, test2.t2 FROM <...> (with one subsequent Table map) +--echo ##################################################################################### + +--replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/ +--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v --read-from-remote-server --user=root --host=localhost --port=$MASTER_MYPORT master-bin.000001 + +--echo # +--echo ##################################################################################### +--echo # mysqlbinlog --read-from-remote-server --database=test1 +--echo # The following Annotate should appear in this output: +--echo # - DELETE test1.t1, test2.t2 FROM <...> +--echo ##################################################################################### + +--replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/ +--exec $MYSQL_BINLOG --base64-output=decode-rows --database=test1 -v -v --read-from-remote-server --user=root --host=localhost --port=$MASTER_MYPORT master-bin.000001 + +--echo # +--echo ##################################################################################### +--echo # mysqlbinlog --read-from-remote-server --skip-annotate-row-events +--echo # No Annotates should appear in this output +--echo ##################################################################################### + +--replace_regex /server id [0-9]*/server id #/ /server v [^ ]*/server v #.##.##/ /exec_time=[0-9]*/exec_time=#/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/ +--exec $MYSQL_BINLOG --base64-output=decode-rows --skip-annotate-row-events -v -v --read-from-remote-server --user=root --host=localhost --port=$MASTER_MYPORT master-bin.000001 + +# Clean-up + +set global binlog_checksum=@old_binlog_checksum; +DROP DATABASE test1; +DROP DATABASE test2; +DROP DATABASE test3; +DROP DATABASE xtest1; +DROP DATABASE xtest2; diff --git a/mysql-test/suite/binlog/include/binlog_truncate.test b/mysql-test/suite/binlog/include/binlog_truncate.test new file mode 100644 index 00000000..4d297cd2 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_truncate.test @@ -0,0 +1,63 @@ +# BUG #36763: TRUNCATE TABLE fails to replicate when stmt-based +# binlogging is not supported. + +# This should always be logged as a statement, even when executed as a +# row-by-row deletion. + +# $before_truncate A statement to execute (just) before issuing the +# TRUNCATE TABLE + + +eval CREATE TABLE t1 (a INT) ENGINE=$engine; +eval CREATE TABLE t2 (a INT) ENGINE=$engine; +INSERT INTO t2 VALUES (1),(2),(3); +let $binlog_start = query_get_value("SHOW MASTER STATUS", Position, 1); +if ($before_truncate) { + eval $before_truncate; +} +--echo **** Truncate of empty table shall be logged +TRUNCATE TABLE t1; + +if ($before_truncate) { + eval $before_truncate; +} +TRUNCATE TABLE t2; +source include/show_binlog_events.inc; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE +--echo # + +eval CREATE TABLE t1 (a INT) ENGINE=$engine; +eval CREATE TABLE t2 (a INT) ENGINE=$engine; +INSERT INTO t1 VALUES (1),(2); + +let $binlog_start = query_get_value("SHOW MASTER STATUS", Position, 1); +if ($before_truncate) { + eval $before_truncate; +} + +BEGIN; +INSERT INTO t2 SELECT * FROM t1; + +connect (truncate,localhost,root,,); +send TRUNCATE TABLE t1; + +connection default; +INSERT INTO t2 SELECT * FROM t1; +SELECT COUNT(*) FROM t2; +COMMIT; + +connection truncate; +--echo # Reaping TRUNCATE TABLE +--reap +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t2; + +connection default; + +source include/show_binlog_events.inc; +disconnect truncate; +DROP TABLE t1,t2; diff --git a/mysql-test/suite/binlog/include/binlog_xa_prepare_connection.inc b/mysql-test/suite/binlog/include/binlog_xa_prepare_connection.inc new file mode 100644 index 00000000..c0041af1 --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_xa_prepare_connection.inc @@ -0,0 +1,31 @@ +# +# This file initiate connections to run XA transactions up to +# their prepare. +# Connection name, transaction name and its content depends on +# supplied parameters. +# +# param $type type of transaction +# param $index index identifies the connection with those of type $type +# param $sql_init1 a query to execute once connection is established +# param $sql_init2 a query to execute once connection is established +# param $sql_doit a query to execute inside transaction +# Note, the query may depend on tables created by caller +# + +--connect (conn$index$type, 127.0.0.1,root,,test,$MASTER_MYPORT,) +if ($sql_init1) +{ + --eval $sql_init1 +} +if ($sql_init2) +{ + --eval $sql_init2 +} + +--eval XA START 'trx$index$type' +if ($sql_doit) +{ + --eval $sql_doit +} +--eval XA END 'trx$index$type' +--eval XA PREPARE 'trx$index$type' diff --git a/mysql-test/suite/binlog/include/binlog_xa_prepare_disconnect.inc b/mysql-test/suite/binlog/include/binlog_xa_prepare_disconnect.inc new file mode 100644 index 00000000..4a83aa5c --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_xa_prepare_disconnect.inc @@ -0,0 +1,37 @@ +# +# This file disconnects two connections. One actively and one through +# kill. It is included by binlog_xa_prepared_do_and_restart. +# +# param $type type of transaction +# param $terminate_with how to conclude actively disconnecte: +# XA COMMIT or XA ROLLBACK +# param $conn3_id connection id of the being killed. +# param $num_trx_prepared number of transactions prepared so far +# +--connection default + +--echo *** $num_trx_prepared prepared transactions must be in the list *** +--replace_column 2 LEN1 3 LEN2 4 TRX_N +XA RECOVER; + +--connection conn1$type +--let $conn1_id=`SELECT connection_id()` +--disconnect conn1$type + +--connection default +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn1_id +--source include/wait_condition.inc + +# It will conclude now +--error 0,1402 +--eval $terminate_with 'trx1$type' + +--replace_result $conn3_id CONN_ID +--eval KILL connection $conn3_id + +--let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn3_id +--source include/wait_condition.inc + +# It will conclude now +--error 0,1402 +--eval $terminate_with 'trx3$type' diff --git a/mysql-test/suite/binlog/include/binlog_xa_prepared_do_and_restart.inc b/mysql-test/suite/binlog/include/binlog_xa_prepared_do_and_restart.inc new file mode 100644 index 00000000..cbd740fd --- /dev/null +++ b/mysql-test/suite/binlog/include/binlog_xa_prepared_do_and_restart.inc @@ -0,0 +1,323 @@ +# +# This file creates various kinds of prepared XA transactions, +# manipulates their connection state and examines how their prepared +# status behave while the transaction is disconnected, killed or +# the server kisses it shutdown. +# The file can be sourced multiple times +# param $restart_number (as the number of inclusion) adjusts +# verification logics. +# +# param [in] $conn_number Total number of connection each performing +# one insert into table. +# param [in] $commit_number Number of commits from either. +# side of the server restart. +# param [in] $rollback_number The same as the above just for rollback. +# param [in] $term_number Number of transaction that are terminated +# before server restarts +# param [in] $killed_number Instead of disconnect make some +# connections killed when their +# transactions got prepared. +# param [in] $server_disconn_number Make some connections disconnected +# by shutdown rather than actively +# param [in] $post_restart_conn_number Number a "warmup" connection +# after server restart, they all commit +# param [out] restart_number Counter to be incremented at the end of the test +# + +# The test consists of three sections: +# I. Corner cases check +# II. Regular case check +# III. Post server-restart verification + + +# +# I. Corner cases of +# +# A. XA with an update to a temp table +# B. XA with SELECT +# C. XA empty +# Demonstrate their XA status upon prepare and how they react on disconnect and +# shutdown. +# In each of A,B,C three prepared transactions are set up. +# trx1 is for disconnection, trx2 for shutdown, trx3 for being killed. +# The A case additionally contains some XA prohibited state transaction check. +# +# D. Prove that not prepared XA remains to be cleared out by disconnection. +# + +# +# A. The temp table only prepared XA recovers only formally to +# let post recovery XA COMMIT or XA ROLLBACK with no effect. + +--let $type = tmp +--let $index = 1 +--let $sql_init1 = SET @@sql_log_bin = OFF +--let $sql_init2 = CREATE TEMPORARY TABLE tmp$index (a int) ENGINE=innodb +--let $sql_doit = INSERT INTO tmp$index SET a=$index +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 2 +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 3 +--source suite/binlog/include/binlog_xa_prepare_connection.inc +--let $conn3_id=`SELECT connection_id()` + +# +# Various prohibited XA state changes to test here: +# + +--connection default +# Stealing is not allowed +--error ER_XAER_NOTA +--eval XA COMMIT 'trx1$type' +--error ER_XAER_NOTA +--eval XA ROLLBACK 'trx1$type' + +# Before disconnect: creating a duplicate is not allowed +--error ER_XAER_DUPID +--eval XA START 'trx1$type' + +# Manipulate now the prepared transactions. +# Two to terminate, one to leave out. +--let $terminate_with = XA COMMIT +--let $num_trx_prepared = $index +--source suite/binlog/include/binlog_xa_prepare_disconnect.inc + +# +# B. "Read-only" (select) prepared XA recovers only formally to +# let post recovery XA COMMIT or XA ROLLBACK with no effect. +# +--let $type=ro +--let $index = 1 +--let $sql_init1 = +--let $sql_init2 = +--let $sql_doit = SELECT * from t ORDER BY a +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 2 +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 3 +--source suite/binlog/include/binlog_xa_prepare_connection.inc +--let $conn3_id=`SELECT connection_id()` + +--let $terminate_with = XA ROLLBACK +# two three above section prepared transaction were terminated. +--inc $num_trx_prepared +--source suite/binlog/include/binlog_xa_prepare_disconnect.inc + +# +# C. Empty prepared XA recovers only formally to +# let post recovery XA COMMIT or XA ROLLBACK with no effect. +# +--let $type=empty +--let $index = 1 +--let $sql_init1 = +--let $sql_init2 = +--let $sql_doit = +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 2 +--source suite/binlog/include/binlog_xa_prepare_connection.inc + +--let $index = 3 +--source suite/binlog/include/binlog_xa_prepare_connection.inc +--let $conn3_id=`SELECT connection_id()` + +--let $terminate_with = XA COMMIT +--inc $num_trx_prepared +--source suite/binlog/include/binlog_xa_prepare_disconnect.inc + +# +# D. Not prepared XA disconnects to be cleared out, +# no effect on data left as well. +# Few more prohibited XA state transactions is checked out. +# +--let $type=unprepared +--let $prev_count=`SELECT count(*) from t` + +--connect(conn1$type, 127.0.0.1,root,,test,$MASTER_MYPORT,) +--eval XA START 'trx1$type' +INSERT INTO t set a=0; +--eval XA END 'trx1$type' + +--error ER_XAER_RMFAIL +INSERT INTO t set a=0; +--error ER_XAER_RMFAIL +--eval XA START 'trx1$type' +--error ER_XAER_RMFAIL +--eval XA START 'trx1$type' + +--disconnect conn1$type + +--connection default +# No such transactions +--error ER_XAER_NOTA +--eval XA COMMIT 'trx1$type' +if (`SELECT count(*) > $prev_count from t`) +{ + --echo *** Unexpected commit to the table. *** + --die +} + +# +# II. Regular case. +# +# Prepared transactions get disconnected in three ways: +# actively, being killed and by the server shutdown. +# +--let $i=0 +while ($i < $conn_number) +{ + --connect (conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,) + --let $conn_id=`SELECT connection_id()` + --disable_reconnect + SET @@binlog_format = STATEMENT; + if (`SELECT $i % 2`) + { + SET @@binlog_format = ROW; + } + --eval XA START 'trx_$i' + --eval INSERT INTO t SET a=$i + --eval XA END 'trx_$i' + --eval XA PREPARE 'trx_$i' + + --let $disc_via_kill=`SELECT $conn_number - $i <= $killed_number` + if (!$disc_via_kill) + { + --let $disc_via_shutdown=`SELECT $conn_number - $i <= $killed_number + $server_disconn_number` + if (!$disc_via_shutdown) + { + --disconnect conn$i + } + } + if ($disc_via_kill) + { + --connection default + --replace_result $conn_id CONN_ID + --eval KILL CONNECTION $conn_id + } + + if (!$disc_via_shutdown) + { + --connection default + --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id + --source include/wait_condition.inc + } + --inc $i +} + +# [0, $rollback_number - 1] are rolled back now +--connection default + +--let $i=0 +while ($i < $rollback_number) +{ + --eval XA ROLLBACK 'trx_$i' + + --inc $i +} + +# [$rollback_number, $rollback_number + $commit_number - 1] get committed +while ($i < $term_number) +{ + --eval XA COMMIT 'trx_$i' + + --inc $i +} + +--source include/$how_to_restart + +# +# III. Post server-restart verification. +# It concludes survived XA:s with a number of commits and rollbacks +# as configured in the 1st part to check expected results in the end. +# Cleanup section consists of explicit disconnect (for killed, or +# not disconnected before shutdown). +# + +# New XA can be prepared and committed +--let $k = 0 +while ($k < $post_restart_conn_number) +{ + --connect (conn_restart_$k, 127.0.0.1,root,,test,$MASTER_MYPORT,) + --let $conn_id=`SELECT connection_id()` + --eval XA START 'new_trx_$k' + --eval INSERT INTO t SET a=$k + --eval XA END 'new_trx_$k' + --eval XA PREPARE 'new_trx_$k' + + --disconnect conn_restart_$k + + --connection default + --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id + --source include/wait_condition.inc + + --inc $k +} + +--connection default +--let $k = 0 +while ($k < $post_restart_conn_number) +{ + --eval XA COMMIT 'new_trx_$k' + --inc $k +} + +# +# Symmetrically to the pre-restart, the resurrected trx:s are committed +# [$term_number, $term_number + $commit_number - 1] +# and the rest is rolled back. +# +--let $i = $term_number + +while ($i < `SELECT $term_number + $commit_number`) +{ + # Expected to fail + --error ER_XAER_DUPID + --eval XA START 'trx_$i' + --eval XA COMMIT 'trx_$i' + --inc $i +} + +while ($i < $conn_number) +{ + # Expected to fail + --error ER_XAER_DUPID + --eval XA START 'trx_$i' + --eval XA ROLLBACK 'trx_$i' + --inc $i +} + +# +# Verification of correct results of recovered XA transaction handling: +# +SELECT * FROM t; + +--let $type=tmp +--disconnect conn2$type +--disconnect conn3$type +--let $type=ro +--disconnect conn2$type +--disconnect conn3$type +--let $type=empty +--disconnect conn2$type +--disconnect conn3$type + +--let $i= $conn_number +--let $k= 0 +--let $expl_disconn_number = `SELECT $killed_number + $server_disconn_number` +while ($k < $expl_disconn_number) +{ + --connection default + --error ER_XAER_NOTA + --eval XA ROLLBACK 'trx_$i' + + --dec $i + --disconnect conn$i + + --inc $k +} + +--inc $restart_number diff --git a/mysql-test/suite/binlog/include/blackhole.test b/mysql-test/suite/binlog/include/blackhole.test new file mode 100644 index 00000000..90146d41 --- /dev/null +++ b/mysql-test/suite/binlog/include/blackhole.test @@ -0,0 +1,208 @@ +# +# Simple test for blackhole example +# Taken from the select test +# +-- source include/not_embedded.inc +-- source include/have_blackhole.inc +-- source include/have_log_bin.inc + +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); + +# The server need to be started in $MYSQLTEST_VARDIR since it +# uses ../../std_data/ +-- source include/uses_vardir.inc + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +CREATE TABLE t1 ( + Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, + Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL +) ENGINE=blackhole; + +INSERT INTO t1 VALUES (9410,9412); + +select period from t1; +select * from t1; +select t1.* from t1; + +# +# Create test table +# + +CREATE TABLE t2 ( + auto int NOT NULL auto_increment, + fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, + companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, + fld3 char(30) DEFAULT '' NOT NULL, + fld4 char(35) DEFAULT '' NOT NULL, + fld5 char(35) DEFAULT '' NOT NULL, + fld6 char(4) DEFAULT '' NOT NULL, + primary key (auto) +) ENGINE=blackhole; + +INSERT INTO t2 VALUES (1192,068305,00,'Colombo','hardware','colicky',''); +INSERT INTO t2 VALUES (1193,000000,00,'nondecreasing','implant','thrillingly',''); +--enable_query_log + +# +# Search with a key +# + +select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; +select fld3 from t2 where fld3 like "%cultivation" ; + +# +# Search with a key using sorting and limit the same time +# + +select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; +select fld3,companynr from t2 where companynr = 58 order by fld3; + +select fld3 from t2 order by fld3 desc limit 10; +select fld3 from t2 order by fld3 desc limit 5; +select fld3 from t2 order by fld3 desc limit 5,5; + +# +# Search with a key having a constant with each unique key. +# The table is read directly with read-next on fld3 +# + +select t2.fld3 from t2 where fld3 = 'honeysuckle'; +select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; +select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; +select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; +select t2.fld3 from t2 where fld3 LIKE 'h%le'; + +select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; +select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; + +# +# Test sorting with a used key (there is no need for sorting) +# + +select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; +select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; + + +# Test for fulltext +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); +INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), + ('Full-text indexes', 'are called collections'), + ('Only MyISAM tables','support collections'), + ('Function MATCH ... AGAINST()','is used to do a search'), + ('Full-text search in MySQL', 'implements vector space model'); +SHOW INDEX FROM t1; + +# nl search + +select * from t1 where MATCH(a,b) AGAINST ("collections"); +explain extended select * from t1 where MATCH(a,b) AGAINST ("collections"); +select * from t1 where MATCH(a,b) AGAINST ("indexes"); +select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); +select * from t1 where MATCH(a,b) AGAINST ("only"); + +# Test that every DML (except SELECT) and DDL gets into binlog +# so that blackhole can be used as "binlog propagator" + +reset master; +drop table t1,t2; +create table t1 (a int) engine=blackhole; +delete from t1 where a=10; +update t1 set a=11 where a=15; +insert into t1 values(1); +insert ignore into t1 values(1); +replace into t1 values(100); +create table t2 (a varchar(200)) engine=blackhole; +eval load data infile '../../std_data/words.dat' into table t2; +alter table t1 add b int; +alter table t1 drop b; +create table t3 like t1; +insert into t1 select * from t3; +replace into t1 select * from t3; +# Just to verify +select * from t1; +select * from t2; +select * from t3; + +--source include/show_binlog_events.inc + +drop table t1,t2,t3; + +# +# BUG#27998 - mysqld crashed when executing INSERT DELAYED on a BLACKHOLE +# table +# +CREATE TABLE t1(a INT) ENGINE=BLACKHOLE; +INSERT DELAYED INTO t1 VALUES(1); +DROP TABLE t1; + + +# +#Bug#19717: DELETE Query Error on BLACKHOLE when using WHERE on column with UNIQUE INDEX +# +CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE; +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 ADD INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD UNIQUE INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD PRIMARY KEY(a); +DELETE FROM t1 WHERE a=10; +DROP TABLE t1; + + +# Test that a transaction which is rolled back does not go into binlog +# and that a transaction which is committed does + +reset master; +create table t1 (a int) engine=blackhole; +set autocommit=0; +start transaction; +insert into t1 values(1); +commit; + +let $master_log_pos_1= query_get_value(SHOW MASTER STATUS, Position, 1); + +start transaction; +insert into t1 values(2); +rollback; + +let $master_log_pos_2= query_get_value(SHOW MASTER STATUS, Position, 1); +if ($master_log_pos_2 != $master_log_pos_1) +{ + echo $master_log_pos_1 $master_log_pos_2; + die Rollbacked transaction has been binlogged; +} + +set autocommit=1; +drop table if exists t1; + +# +# Bug#35178 INSERT_ID not written to binary log for inserts against BLACKHOLE backed tables +# +# +# the test checks that explicitly prescribed with set insert_id= value +# preceeds the following autoincrement insert in a blachhole +# + +reset master; +create table t1 (a int auto_increment, primary key (a)) engine=blackhole; + +# not insert_id prescribed insert binlogs with the default set insert_id 1 +insert into t1 values (11), (NULL), (NULL), (NULL); +set insert_id= 3; +insert into t1 values (NULL), (33), (NULL); +set insert_id= 5; +insert into t1 values (55), (NULL); +source include/show_binlog_events2.inc; + +# cleanup +drop table t1; + + +# End of tests diff --git a/mysql-test/suite/binlog/include/check_binlog_size.inc b/mysql-test/suite/binlog/include/check_binlog_size.inc new file mode 100644 index 00000000..9df161ec --- /dev/null +++ b/mysql-test/suite/binlog/include/check_binlog_size.inc @@ -0,0 +1,31 @@ +# This file runs the query and checks +# whether the size of binlog is increased or not +# If size is changed it issue die command +# Parameters +# $sql_query = query to run + +#Only last row of show binlog events matter +--let $tmp= 0 +--let $counter= 1 +while ($tmp != "No such row") +{ + --let $initial_binlog_size= $tmp + --let $tmp= query_get_value(show binary logs, File_size, $counter) + --inc $counter +} + +--eval $sql_query + +--let $tmp= 0 +--let $counter= 1 +while ($tmp != "No such row") +{ + --let $current_binlog_size= $tmp + --let $tmp= query_get_value(show binary logs, File_size, $counter) + --inc $counter +} + +if ($initial_binlog_size != $current_binlog_size) +{ + die "Binlog size changed"; +} diff --git a/mysql-test/suite/binlog/include/ctype_cp932.test b/mysql-test/suite/binlog/include/ctype_cp932.test new file mode 100644 index 00000000..f42bffd3 --- /dev/null +++ b/mysql-test/suite/binlog/include/ctype_cp932.test @@ -0,0 +1,468 @@ +-- source include/have_cp932.inc + +--character_set cp932 +--disable_warnings +drop table if exists t1; +drop table if exists t2; +drop table if exists t3; +drop table if exists t4; +--enable_warnings + +SET @test_character_set= 'cp932'; +SET @test_collation= 'cp932_japanese_ci'; +-- source include/ctype_common.inc + +set names cp932; +set character_set_database = cp932; + +CREATE TABLE t1(c1 CHAR(1)) DEFAULT CHARACTER SET = cp932; + +#Characters which are converted to Unicode ambiguously +INSERT INTO t1 VALUES +(0x05),(0x7E),(0x815C),(0x815F),(0x8160),(0x8161),(0x817C),(0x8191),(0x8192),(0x81CA); + +#NEC ROW 13 characters (0x8740 - 0x879C) +INSERT INTO t1 VALUES +(0x8740),(0x8741),(0x8742),(0x8743),(0x8744),(0x8745),(0x8746),(0x8747), +(0x8748),(0x8749),(0x874A),(0x874B),(0x874C),(0x874D),(0x874E),(0x874F), +(0x8750),(0x8751),(0x8752),(0x8753),(0x8754),(0x8755),(0x8756),(0x8757), +(0x8758),(0x8759),(0x875A),(0x875B),(0x875C),(0x875D),(0x875F), +(0x8760),(0x8761),(0x8762),(0x8763),(0x8764),(0x8765),(0x8766),(0x8767), +(0x8768),(0x8769),(0x876A),(0x876B),(0x876C),(0x876D),(0x876E),(0x876F), +(0x8770),(0x8771),(0x8772),(0x8773),(0x8774),(0x8775),(0x877E), +(0x8780),(0x8781),(0x8782),(0x8783),(0x8784),(0x8785),(0x8786),(0x8787), +(0x8788),(0x8789),(0x878A),(0x878B),(0x878C),(0x878D),(0x878E),(0x878F), +(0x8790),(0x8791),(0x8792),(0x8793),(0x8794),(0x8795),(0x8796),(0x8797), +(0x8798),(0x8799),(0x879A),(0x879B),(0x879C); + +#IBM selected characters kanji & non-kanji, NEC implementation (0xED40 - 0xEEFC) +INSERT INTO t1 VALUES +(0xED40),(0xED41),(0xED42),(0xED43),(0xED44),(0xED45),(0xED46),(0xED47), +(0xED48),(0xED49),(0xED4A),(0xED4B),(0xED4C),(0xED4D),(0xED4E),(0xED4F), +(0xED50),(0xED51),(0xED52),(0xED53),(0xED54),(0xED55),(0xED56),(0xED57), +(0xED58),(0xED59),(0xED5A),(0xED5B),(0xED5C),(0xED5D),(0xED5E),(0xED5F), +(0xED60),(0xED61),(0xED62),(0xED63),(0xED64),(0xED65),(0xED66),(0xED67), +(0xED68),(0xED69),(0xED6A),(0xED6B),(0xED6C),(0xED6D),(0xED6E),(0xED6F), +(0xED70),(0xED71),(0xED72),(0xED73),(0xED74),(0xED75),(0xED76),(0xED77), +(0xED78),(0xED79),(0xED7A),(0xED7B),(0xED7C),(0xED7D),(0xED7E), +(0xED80),(0xED81),(0xED82),(0xED83),(0xED84),(0xED85),(0xED86),(0xED87), +(0xED88),(0xED89),(0xED8A),(0xED8B),(0xED8C),(0xED8D),(0xED8E),(0xED8F), +(0xED90),(0xED91),(0xED92),(0xED93),(0xED94),(0xED95),(0xED96),(0xED97), +(0xED98),(0xED99),(0xED9A),(0xED9B),(0xED9C),(0xED9D),(0xED9E),(0xED9F), +(0xEDA0),(0xEDA1),(0xEDA2),(0xEDA3),(0xEDA4),(0xEDA5),(0xEDA6),(0xEDA7), +(0xEDA8),(0xEDA9),(0xEDAA),(0xEDAB),(0xEDAC),(0xEDAD),(0xEDAE),(0xEDAF), +(0xEDB0),(0xEDB1),(0xEDB2),(0xEDB3),(0xEDB4),(0xEDB5),(0xEDB6),(0xEDB7), +(0xEDB8),(0xEDB9),(0xEDBA),(0xEDBB),(0xEDBC),(0xEDBD),(0xEDBE),(0xEDBF), +(0xEDC0),(0xEDC1),(0xEDC2),(0xEDC3),(0xEDC4),(0xEDC5),(0xEDC6),(0xEDC7), +(0xEDC8),(0xEDC9),(0xEDCA),(0xEDCB),(0xEDCC),(0xEDCD),(0xEDCE),(0xEDCF), +(0xEDD0),(0xEDD1),(0xEDD2),(0xEDD3),(0xEDD4),(0xEDD5),(0xEDD6),(0xEDD7), +(0xEDD8),(0xEDD9),(0xEDDA),(0xEDDB),(0xEDDC),(0xEDDD),(0xEDDE),(0xEDDF), +(0xEDE0),(0xEDE1),(0xEDE2),(0xEDE3),(0xEDE4),(0xEDE5),(0xEDE6),(0xEDE7), +(0xEDE8),(0xEDE9),(0xEDEA),(0xEDEB),(0xEDEC),(0xEDED),(0xEDEE),(0xEDEF), +(0xEDF0),(0xEDF1),(0xEDF2),(0xEDF3),(0xEDF4),(0xEDF5),(0xEDF6),(0xEDF7), +(0xEDF8),(0xEDF9),(0xEDFA),(0xEDFB),(0xEDFC), +(0xEE40),(0xEE41),(0xEE42),(0xEE43),(0xEE44),(0xEE45),(0xEE46),(0xEE47), +(0xEE48),(0xEE49),(0xEE4A),(0xEE4B),(0xEE4C),(0xEE4D),(0xEE4E),(0xEE4F), +(0xEE50),(0xEE51),(0xEE52),(0xEE53),(0xEE54),(0xEE55),(0xEE56),(0xEE57), +(0xEE58),(0xEE59),(0xEE5A),(0xEE5B),(0xEE5C),(0xEE5D),(0xEE5E),(0xEE5F), +(0xEE60),(0xEE61),(0xEE62),(0xEE63),(0xEE64),(0xEE65),(0xEE66),(0xEE67), +(0xEE68),(0xEE69),(0xEE6A),(0xEE6B),(0xEE6C),(0xEE6D),(0xEE6E),(0xEE6F), +(0xEE70),(0xEE71),(0xEE72),(0xEE73),(0xEE74),(0xEE75),(0xEE76),(0xEE77), +(0xEE78),(0xEE79),(0xEE7A),(0xEE7B),(0xEE7C),(0xEE7D),(0xEE7E), +(0xEE80),(0xEE81),(0xEE82),(0xEE83),(0xEE84),(0xEE85),(0xEE86),(0xEE87), +(0xEE88),(0xEE89),(0xEE8A),(0xEE8B),(0xEE8C),(0xEE8D),(0xEE8E),(0xEE8F), +(0xEE90),(0xEE91),(0xEE92),(0xEE93),(0xEE94),(0xEE95),(0xEE96),(0xEE97), +(0xEE98),(0xEE99),(0xEE9A),(0xEE9B),(0xEE9C),(0xEE9D),(0xEE9E),(0xEE9F), +(0xEEA0),(0xEEA1),(0xEEA2),(0xEEA3),(0xEEA4),(0xEEA5),(0xEEA6),(0xEEA7), +(0xEEA8),(0xEEA9),(0xEEAA),(0xEEAB),(0xEEAC),(0xEEAD),(0xEEAE),(0xEEAF), +(0xEEB0),(0xEEB1),(0xEEB2),(0xEEB3),(0xEEB4),(0xEEB5),(0xEEB6),(0xEEB7), +(0xEEB8),(0xEEB9),(0xEEBA),(0xEEBB),(0xEEBC),(0xEEBD),(0xEEBE),(0xEEBF), +(0xEEC0),(0xEEC1),(0xEEC2),(0xEEC3),(0xEEC4),(0xEEC5),(0xEEC6),(0xEEC7), +(0xEEC8),(0xEEC9),(0xEECA),(0xEECB),(0xEECC),(0xEECD),(0xEECE),(0xEECF), +(0xEED0),(0xEED1),(0xEED2),(0xEED3),(0xEED4),(0xEED5),(0xEED6),(0xEED7), +(0xEED8),(0xEED9),(0xEEDA),(0xEEDB),(0xEEDC),(0xEEDD),(0xEEDE),(0xEEDF), +(0xEEE0),(0xEEE1),(0xEEE2),(0xEEE3),(0xEEE4),(0xEEE5),(0xEEE6),(0xEEE7), +(0xEEE8),(0xEEE9),(0xEEEA),(0xEEEB),(0xEEEC),(0xEEEF), +(0xEEF0),(0xEEF1),(0xEEF2),(0xEEF3),(0xEEF4),(0xEEF5),(0xEEF6),(0xEEF7), +(0xEEF8),(0xEEF9),(0xEEFA),(0xEEFB),(0xEEFC); + +#IBM selected kanji & non-kanji (0xFA40 - 0xFC4B) +INSERT INTO t1 VALUES +(0xFA40),(0xFA41),(0xFA42),(0xFA43),(0xFA44),(0xFA45),(0xFA46),(0xFA47), +(0xFA48),(0xFA49),(0xFA4A),(0xFA4B),(0xFA4C),(0xFA4D),(0xFA4E),(0xFA4F), +(0xFA50),(0xFA51),(0xFA52),(0xFA53),(0xFA54),(0xFA55),(0xFA56),(0xFA57), +(0xFA58),(0xFA59),(0xFA5A),(0xFA5B),(0xFA5C),(0xFA5D),(0xFA5E),(0xFA5F), +(0xFA60),(0xFA61),(0xFA62),(0xFA63),(0xFA64),(0xFA65),(0xFA66),(0xFA67), +(0xFA68),(0xFA69),(0xFA6A),(0xFA6B),(0xFA6C),(0xFA6D),(0xFA6E),(0xFA6F), +(0xFA70),(0xFA71),(0xFA72),(0xFA73),(0xFA74),(0xFA75),(0xFA76),(0xFA77), +(0xFA78),(0xFA79),(0xFA7A),(0xFA7B),(0xFA7C),(0xFA7D),(0xFA7E), +(0xFA80),(0xFA81),(0xFA82),(0xFA83),(0xFA84),(0xFA85),(0xFA86),(0xFA87), +(0xFA88),(0xFA89),(0xFA8A),(0xFA8B),(0xFA8C),(0xFA8D),(0xFA8E),(0xFA8F), +(0xFA90),(0xFA91),(0xFA92),(0xFA93),(0xFA94),(0xFA95),(0xFA96),(0xFA97), +(0xFA98),(0xFA99),(0xFA9A),(0xFA9B),(0xFA9C),(0xFA9D),(0xFA9E),(0xFA9F), +(0xFAA0),(0xFAA1),(0xFAA2),(0xFAA3),(0xFAA4),(0xFAA5),(0xFAA6),(0xFAA7), +(0xFAA8),(0xFAA9),(0xFAAA),(0xFAAB),(0xFAAC),(0xFAAD),(0xFAAE),(0xFAAF), +(0xFAB0),(0xFAB1),(0xFAB2),(0xFAB3),(0xFAB4),(0xFAB5),(0xFAB6),(0xFAB7), +(0xFAB8),(0xFAB9),(0xFABA),(0xFABB),(0xFABC),(0xFABD),(0xFABE),(0xFABF), +(0xFAC0),(0xFAC1),(0xFAC2),(0xFAC3),(0xFAC4),(0xFAC5),(0xFAC6),(0xFAC7), +(0xFAC8),(0xFAC9),(0xFACA),(0xFACB),(0xFACC),(0xFACD),(0xFACE),(0xFACF), +(0xFAD0),(0xFAD1),(0xFAD2),(0xFAD3),(0xFAD4),(0xFAD5),(0xFAD6),(0xFAD7), +(0xFAD8),(0xFAD9),(0xFADA),(0xFADB),(0xFADC),(0xFADD),(0xFADE),(0xFADF), +(0xFAE0),(0xFAE1),(0xFAE2),(0xFAE3),(0xFAE4),(0xFAE5),(0xFAE6),(0xFAE7), +(0xFAE8),(0xFAE9),(0xFAEA),(0xFAEB),(0xFAEC),(0xFAED),(0xFAEE),(0xFAEF), +(0xFAF0),(0xFAF1),(0xFAF2),(0xFAF3),(0xFAF4),(0xFAF5),(0xFAF6),(0xFAF7), +(0xFAF8),(0xFAF9),(0xFAFA),(0xFAFB),(0xFAFC), +(0xFB40),(0xFB41),(0xFB42),(0xFB43),(0xFB44),(0xFB45),(0xFB46),(0xFB47), +(0xFB48),(0xFB49),(0xFB4A),(0xFB4B),(0xFB4C),(0xFB4D),(0xFB4E),(0xFB4F), +(0xFB50),(0xFB51),(0xFB52),(0xFB53),(0xFB54),(0xFB55),(0xFB56),(0xFB57), +(0xFB58),(0xFB59),(0xFB5A),(0xFB5B),(0xFB5C),(0xFB5D),(0xFB5E),(0xFB5F), +(0xFB60),(0xFB61),(0xFB62),(0xFB63),(0xFB64),(0xFB65),(0xFB66),(0xFB67), +(0xFB68),(0xFB69),(0xFB6A),(0xFB6B),(0xFB6C),(0xFB6D),(0xFB6E),(0xFB6F), +(0xFB70),(0xFB71),(0xFB72),(0xFB73),(0xFB74),(0xFB75),(0xFB76),(0xFB77), +(0xFB78),(0xFB79),(0xFB7A),(0xFB7B),(0xFB7C),(0xFB7D),(0xFB7E), +(0xFB80),(0xFB81),(0xFB82),(0xFB83),(0xFB84),(0xFB85),(0xFB86),(0xFB87), +(0xFB88),(0xFB89),(0xFB8A),(0xFB8B),(0xFB8C),(0xFB8D),(0xFB8E),(0xFB8F), +(0xFB90),(0xFB91),(0xFB92),(0xFB93),(0xFB94),(0xFB95),(0xFB96),(0xFB97), +(0xFB98),(0xFB99),(0xFB9A),(0xFB9B),(0xFB9C),(0xFB9D),(0xFB9E),(0xFB9F), +(0xFBA0),(0xFBA1),(0xFBA2),(0xFBA3),(0xFBA4),(0xFBA5),(0xFBA6),(0xFBA7), +(0xFBA8),(0xFBA9),(0xFBAA),(0xFBAB),(0xFBAC),(0xFBAD),(0xFBAE),(0xFBAF), +(0xFBB0),(0xFBB1),(0xFBB2),(0xFBB3),(0xFBB4),(0xFBB5),(0xFBB6),(0xFBB7), +(0xFBB8),(0xFBB9),(0xFBBA),(0xFBBB),(0xFBBC),(0xFBBD),(0xFBBE),(0xFBBF), +(0xFBC0),(0xFBC1),(0xFBC2),(0xFBC3),(0xFBC4),(0xFBC5),(0xFBC6),(0xFBC7), +(0xFBC8),(0xFBC9),(0xFBCA),(0xFBCB),(0xFBCC),(0xFBCD),(0xFBCE),(0xFBCF), +(0xFBD0),(0xFBD1),(0xFBD2),(0xFBD3),(0xFBD4),(0xFBD5),(0xFBD6),(0xFBD7), +(0xFBD8),(0xFBD9),(0xFBDA),(0xFBDB),(0xFBDC),(0xFBDD),(0xFBDE),(0xFBDF), +(0xFBE0),(0xFBE1),(0xFBE2),(0xFBE3),(0xFBE4),(0xFBE5),(0xFBE6),(0xFBE7), +(0xFBE8),(0xFBE9),(0xFBEA),(0xFBEB),(0xFBEC),(0xFBED),(0xFBEE),(0xFBEF), +(0xFBF0),(0xFBF1),(0xFBF2),(0xFBF3),(0xFBF4),(0xFBF5),(0xFBF6),(0xFBF7), +(0xFBF8),(0xFBF9),(0xFBFA),(0xFBFB),(0xFBFC), +(0xFC40),(0xFC41),(0xFC42),(0xFC43),(0xFC44),(0xFC45),(0xFC46),(0xFC47), +(0xFC48),(0xFC49),(0xFC4A),(0xFC4B); + +#User defined characters (0xF040-0xF9FC) +INSERT INTO t1 VALUES +(0xF040),(0xF041),(0xF042),(0xF043),(0xF044),(0xF045),(0xF046),(0xF047), +(0xF048),(0xF049),(0xF04A),(0xF04B),(0xF04C),(0xF04D),(0xF04E),(0xF04F), +(0xF050),(0xF051),(0xF052),(0xF053),(0xF054),(0xF055),(0xF056),(0xF057), +(0xF058),(0xF059),(0xF05A),(0xF05B),(0xF05C),(0xF05D),(0xF05E),(0xF05F), +(0xF060),(0xF061),(0xF062),(0xF063),(0xF064),(0xF065),(0xF066),(0xF067), +(0xF068),(0xF069),(0xF06A),(0xF06B),(0xF06C),(0xF06D),(0xF06E),(0xF06F), +(0xF070),(0xF071),(0xF072),(0xF073),(0xF074),(0xF075),(0xF076),(0xF077), +(0xF078),(0xF079),(0xF07A),(0xF07B),(0xF07C),(0xF07D),(0xF07E), +(0xF080),(0xF081),(0xF082),(0xF083),(0xF084),(0xF085),(0xF086),(0xF087), +(0xF088),(0xF089),(0xF08A),(0xF08B),(0xF08C),(0xF08D),(0xF08E),(0xF08F), +(0xF090),(0xF091),(0xF092),(0xF093),(0xF094),(0xF095),(0xF096),(0xF097), +(0xF098),(0xF099),(0xF09A),(0xF09B),(0xF09C),(0xF09D),(0xF09E),(0xF09F), +(0xF0A0),(0xF0A1),(0xF0A2),(0xF0A3),(0xF0A4),(0xF0A5),(0xF0A6),(0xF0A7), +(0xF0A8),(0xF0A9),(0xF0AA),(0xF0AB),(0xF0AC),(0xF0AD),(0xF0AE),(0xF0AF), +(0xF0B0),(0xF0B1),(0xF0B2),(0xF0B3),(0xF0B4),(0xF0B5),(0xF0B6),(0xF0B7), +(0xF0B8),(0xF0B9),(0xF0BA),(0xF0BB),(0xF0BC),(0xF0BD),(0xF0BE),(0xF0BF), +(0xF0C0),(0xF0C1),(0xF0C2),(0xF0C3),(0xF0C4),(0xF0C5),(0xF0C6),(0xF0C7), +(0xF0C8),(0xF0C9),(0xF0CA),(0xF0CB),(0xF0CC),(0xF0CD),(0xF0CE),(0xF0CF), +(0xF0D0),(0xF0D1),(0xF0D2),(0xF0D3),(0xF0D4),(0xF0D5),(0xF0D6),(0xF0D7), +(0xF0D8),(0xF0D9),(0xF0DA),(0xF0DB),(0xF0DC),(0xF0DD),(0xF0DE),(0xF0DF), +(0xF0E0),(0xF0E1),(0xF0E2),(0xF0E3),(0xF0E4),(0xF0E5),(0xF0E6),(0xF0E7), +(0xF0E8),(0xF0E9),(0xF0EA),(0xF0EB),(0xF0EC),(0xF0ED),(0xF0EE),(0xF0EF), +(0xF0F0),(0xF0F1),(0xF0F2),(0xF0F3),(0xF0F4),(0xF0F5),(0xF0F6),(0xF0F7), +(0xF0F8),(0xF0F9),(0xF0FA),(0xF0FB),(0xF0FC), +(0xF140),(0xF141),(0xF142),(0xF143),(0xF144),(0xF145),(0xF146),(0xF147), +(0xF148),(0xF149),(0xF14A),(0xF14B),(0xF14C),(0xF14D),(0xF14E),(0xF14F), +(0xF150),(0xF151),(0xF152),(0xF153),(0xF154),(0xF155),(0xF156),(0xF157), +(0xF158),(0xF159),(0xF15A),(0xF15B),(0xF15C),(0xF15D),(0xF15E),(0xF15F), +(0xF160),(0xF161),(0xF162),(0xF163),(0xF164),(0xF165),(0xF166),(0xF167), +(0xF168),(0xF169),(0xF16A),(0xF16B),(0xF16C),(0xF16D),(0xF16E),(0xF16F), +(0xF170),(0xF171),(0xF172),(0xF173),(0xF174),(0xF175),(0xF176),(0xF177), +(0xF178),(0xF179),(0xF17A),(0xF17B),(0xF17C),(0xF17D),(0xF17E), +(0xF180),(0xF181),(0xF182),(0xF183),(0xF184),(0xF185),(0xF186),(0xF187), +(0xF188),(0xF189),(0xF18A),(0xF18B),(0xF18C),(0xF18D),(0xF18E),(0xF18F), +(0xF190),(0xF191),(0xF192),(0xF193),(0xF194),(0xF195),(0xF196),(0xF197), +(0xF198),(0xF199),(0xF19A),(0xF19B),(0xF19C),(0xF19D),(0xF19E),(0xF19F), +(0xF1A0),(0xF1A1),(0xF1A2),(0xF1A3),(0xF1A4),(0xF1A5),(0xF1A6),(0xF1A7), +(0xF1A8),(0xF1A9),(0xF1AA),(0xF1AB),(0xF1AC),(0xF1AD),(0xF1AE),(0xF1AF), +(0xF1B0),(0xF1B1),(0xF1B2),(0xF1B3),(0xF1B4),(0xF1B5),(0xF1B6),(0xF1B7), +(0xF1B8),(0xF1B9),(0xF1BA),(0xF1BB),(0xF1BC),(0xF1BD),(0xF1BE),(0xF1BF), +(0xF1C0),(0xF1C1),(0xF1C2),(0xF1C3),(0xF1C4),(0xF1C5),(0xF1C6),(0xF1C7), +(0xF1C8),(0xF1C9),(0xF1CA),(0xF1CB),(0xF1CC),(0xF1CD),(0xF1CE),(0xF1CF), +(0xF1D0),(0xF1D1),(0xF1D2),(0xF1D3),(0xF1D4),(0xF1D5),(0xF1D6),(0xF1D7), +(0xF1D8),(0xF1D9),(0xF1DA),(0xF1DB),(0xF1DC),(0xF1DD),(0xF1DE),(0xF1DF), +(0xF1E0),(0xF1E1),(0xF1E2),(0xF1E3),(0xF1E4),(0xF1E5),(0xF1E6),(0xF1E7), +(0xF1E8),(0xF1E9),(0xF1EA),(0xF1EB),(0xF1EC),(0xF1ED),(0xF1EE),(0xF1EF), +(0xF1F0),(0xF1F1),(0xF1F2),(0xF1F3),(0xF1F4),(0xF1F5),(0xF1F6),(0xF1F7), +(0xF1F8),(0xF1F9),(0xF1FA),(0xF1FB),(0xF1FC), +(0xF240),(0xF241),(0xF242),(0xF243),(0xF244),(0xF245),(0xF246),(0xF247), +(0xF248),(0xF249),(0xF24A),(0xF24B),(0xF24C),(0xF24D),(0xF24E),(0xF24F), +(0xF250),(0xF251),(0xF252),(0xF253),(0xF254),(0xF255),(0xF256),(0xF257), +(0xF258),(0xF259),(0xF25A),(0xF25B),(0xF25C),(0xF25D),(0xF25E),(0xF25F), +(0xF260),(0xF261),(0xF262),(0xF263),(0xF264),(0xF265),(0xF266),(0xF267), +(0xF268),(0xF269),(0xF26A),(0xF26B),(0xF26C),(0xF26D),(0xF26E),(0xF26F), +(0xF270),(0xF271),(0xF272),(0xF273),(0xF274),(0xF275),(0xF276),(0xF277), +(0xF278),(0xF279),(0xF27A),(0xF27B),(0xF27C),(0xF27D),(0xF27E), +(0xF280),(0xF281),(0xF282),(0xF283),(0xF284),(0xF285),(0xF286),(0xF287), +(0xF288),(0xF289),(0xF28A),(0xF28B),(0xF28C),(0xF28D),(0xF28E),(0xF28F), +(0xF290),(0xF291),(0xF292),(0xF293),(0xF294),(0xF295),(0xF296),(0xF297), +(0xF298),(0xF299),(0xF29A),(0xF29B),(0xF29C),(0xF29D),(0xF29E),(0xF29F), +(0xF2A0),(0xF2A1),(0xF2A2),(0xF2A3),(0xF2A4),(0xF2A5),(0xF2A6),(0xF2A7), +(0xF2A8),(0xF2A9),(0xF2AA),(0xF2AB),(0xF2AC),(0xF2AD),(0xF2AE),(0xF2AF), +(0xF2B0),(0xF2B1),(0xF2B2),(0xF2B3),(0xF2B4),(0xF2B5),(0xF2B6),(0xF2B7), +(0xF2B8),(0xF2B9),(0xF2BA),(0xF2BB),(0xF2BC),(0xF2BD),(0xF2BE),(0xF2BF), +(0xF2C0),(0xF2C1),(0xF2C2),(0xF2C3),(0xF2C4),(0xF2C5),(0xF2C6),(0xF2C7), +(0xF2C8),(0xF2C9),(0xF2CA),(0xF2CB),(0xF2CC),(0xF2CD),(0xF2CE),(0xF2CF), +(0xF2D0),(0xF2D1),(0xF2D2),(0xF2D3),(0xF2D4),(0xF2D5),(0xF2D6),(0xF2D7), +(0xF2D8),(0xF2D9),(0xF2DA),(0xF2DB),(0xF2DC),(0xF2DD),(0xF2DE),(0xF2DF), +(0xF2E0),(0xF2E1),(0xF2E2),(0xF2E3),(0xF2E4),(0xF2E5),(0xF2E6),(0xF2E7), +(0xF2E8),(0xF2E9),(0xF2EA),(0xF2EB),(0xF2EC),(0xF2ED),(0xF2EE),(0xF2EF), +(0xF2F0),(0xF2F1),(0xF2F2),(0xF2F3),(0xF2F4),(0xF2F5),(0xF2F6),(0xF2F7), +(0xF2F8),(0xF2F9),(0xF2FA),(0xF2FB),(0xF2FC), +(0xF340),(0xF341),(0xF342),(0xF343),(0xF344),(0xF345),(0xF346),(0xF347), +(0xF348),(0xF349),(0xF34A),(0xF34B),(0xF34C),(0xF34D),(0xF34E),(0xF34F), +(0xF350),(0xF351),(0xF352),(0xF353),(0xF354),(0xF355),(0xF356),(0xF357), +(0xF358),(0xF359),(0xF35A),(0xF35B),(0xF35C),(0xF35D),(0xF35E),(0xF35F), +(0xF360),(0xF361),(0xF362),(0xF363),(0xF364),(0xF365),(0xF366),(0xF367), +(0xF368),(0xF369),(0xF36A),(0xF36B),(0xF36C),(0xF36D),(0xF36E),(0xF36F), +(0xF370),(0xF371),(0xF372),(0xF373),(0xF374),(0xF375),(0xF376),(0xF377), +(0xF378),(0xF379),(0xF37A),(0xF37B),(0xF37C),(0xF37D),(0xF37E), +(0xF380),(0xF381),(0xF382),(0xF383),(0xF384),(0xF385),(0xF386),(0xF387), +(0xF388),(0xF389),(0xF38A),(0xF38B),(0xF38C),(0xF38D),(0xF38E),(0xF38F), +(0xF390),(0xF391),(0xF392),(0xF393),(0xF394),(0xF395),(0xF396),(0xF397), +(0xF398),(0xF399),(0xF39A),(0xF39B),(0xF39C),(0xF39D),(0xF39E),(0xF39F), +(0xF3A0),(0xF3A1),(0xF3A2),(0xF3A3),(0xF3A4),(0xF3A5),(0xF3A6),(0xF3A7), +(0xF3A8),(0xF3A9),(0xF3AA),(0xF3AB),(0xF3AC),(0xF3AD),(0xF3AE),(0xF3AF), +(0xF3B0),(0xF3B1),(0xF3B2),(0xF3B3),(0xF3B4),(0xF3B5),(0xF3B6),(0xF3B7), +(0xF3B8),(0xF3B9),(0xF3BA),(0xF3BB),(0xF3BC),(0xF3BD),(0xF3BE),(0xF3BF), +(0xF3C0),(0xF3C1),(0xF3C2),(0xF3C3),(0xF3C4),(0xF3C5),(0xF3C6),(0xF3C7), +(0xF3C8),(0xF3C9),(0xF3CA),(0xF3CB),(0xF3CC),(0xF3CD),(0xF3CE),(0xF3CF), +(0xF3D0),(0xF3D1),(0xF3D2),(0xF3D3),(0xF3D4),(0xF3D5),(0xF3D6),(0xF3D7), +(0xF3D8),(0xF3D9),(0xF3DA),(0xF3DB),(0xF3DC),(0xF3DD),(0xF3DE),(0xF3DF), +(0xF3E0),(0xF3E1),(0xF3E2),(0xF3E3),(0xF3E4),(0xF3E5),(0xF3E6),(0xF3E7), +(0xF3E8),(0xF3E9),(0xF3EA),(0xF3EB),(0xF3EC),(0xF3ED),(0xF3EE),(0xF3EF), +(0xF3F0),(0xF3F1),(0xF3F2),(0xF3F3),(0xF3F4),(0xF3F5),(0xF3F6),(0xF3F7), +(0xF3F8),(0xF3F9),(0xF3FA),(0xF3FB),(0xF3FC), +(0xF440),(0xF441),(0xF442),(0xF443),(0xF444),(0xF445),(0xF446),(0xF447), +(0xF448),(0xF449),(0xF44A),(0xF44B),(0xF44C),(0xF44D),(0xF44E),(0xF44F), +(0xF450),(0xF451),(0xF452),(0xF453),(0xF454),(0xF455),(0xF456),(0xF457), +(0xF458),(0xF459),(0xF45A),(0xF45B),(0xF45C),(0xF45D),(0xF45E),(0xF45F), +(0xF460),(0xF461),(0xF462),(0xF463),(0xF464),(0xF465),(0xF466),(0xF467), +(0xF468),(0xF469),(0xF46A),(0xF46B),(0xF46C),(0xF46D),(0xF46E),(0xF46F), +(0xF470),(0xF471),(0xF472),(0xF473),(0xF474),(0xF475),(0xF476),(0xF477), +(0xF478),(0xF479),(0xF47A),(0xF47B),(0xF47C),(0xF47D),(0xF47E), +(0xF480),(0xF481),(0xF482),(0xF483),(0xF484),(0xF485),(0xF486),(0xF487), +(0xF488),(0xF489),(0xF48A),(0xF48B),(0xF48C),(0xF48D),(0xF48E),(0xF48F), +(0xF490),(0xF491),(0xF492),(0xF493),(0xF494),(0xF495),(0xF496),(0xF497), +(0xF498),(0xF499),(0xF49A),(0xF49B),(0xF49C),(0xF49D),(0xF49E),(0xF49F), +(0xF4A0),(0xF4A1),(0xF4A2),(0xF4A3),(0xF4A4),(0xF4A5),(0xF4A6),(0xF4A7), +(0xF4A8),(0xF4A9),(0xF4AA),(0xF4AB),(0xF4AC),(0xF4AD),(0xF4AE),(0xF4AF), +(0xF4B0),(0xF4B1),(0xF4B2),(0xF4B3),(0xF4B4),(0xF4B5),(0xF4B6),(0xF4B7), +(0xF4B8),(0xF4B9),(0xF4BA),(0xF4BB),(0xF4BC),(0xF4BD),(0xF4BE),(0xF4BF), +(0xF4C0),(0xF4C1),(0xF4C2),(0xF4C3),(0xF4C4),(0xF4C5),(0xF4C6),(0xF4C7), +(0xF4C8),(0xF4C9),(0xF4CA),(0xF4CB),(0xF4CC),(0xF4CD),(0xF4CE),(0xF4CF), +(0xF4D0),(0xF4D1),(0xF4D2),(0xF4D3),(0xF4D4),(0xF4D5),(0xF4D6),(0xF4D7), +(0xF4D8),(0xF4D9),(0xF4DA),(0xF4DB),(0xF4DC),(0xF4DD),(0xF4DE),(0xF4DF), +(0xF4E0),(0xF4E1),(0xF4E2),(0xF4E3),(0xF4E4),(0xF4E5),(0xF4E6),(0xF4E7), +(0xF4E8),(0xF4E9),(0xF4EA),(0xF4EB),(0xF4EC),(0xF4ED),(0xF4EE),(0xF4EF), +(0xF4F0),(0xF4F1),(0xF4F2),(0xF4F3),(0xF4F4),(0xF4F5),(0xF4F6),(0xF4F7), +(0xF4F8),(0xF4F9),(0xF4FA),(0xF4FB),(0xF4FC), +(0xF540),(0xF541),(0xF542),(0xF543),(0xF544),(0xF545),(0xF546),(0xF547), +(0xF548),(0xF549),(0xF54A),(0xF54B),(0xF54C),(0xF54D),(0xF54E),(0xF54F), +(0xF550),(0xF551),(0xF552),(0xF553),(0xF554),(0xF555),(0xF556),(0xF557), +(0xF558),(0xF559),(0xF55A),(0xF55B),(0xF55C),(0xF55D),(0xF55E),(0xF55F), +(0xF560),(0xF561),(0xF562),(0xF563),(0xF564),(0xF565),(0xF566),(0xF567), +(0xF568),(0xF569),(0xF56A),(0xF56B),(0xF56C),(0xF56D),(0xF56E),(0xF56F), +(0xF570),(0xF571),(0xF572),(0xF573),(0xF574),(0xF575),(0xF576),(0xF577), +(0xF578),(0xF579),(0xF57A),(0xF57B),(0xF57C),(0xF57D),(0xF57E), +(0xF580),(0xF581),(0xF582),(0xF583),(0xF584),(0xF585),(0xF586),(0xF587), +(0xF588),(0xF589),(0xF58A),(0xF58B),(0xF58C),(0xF58D),(0xF58E),(0xF58F), +(0xF590),(0xF591),(0xF592),(0xF593),(0xF594),(0xF595),(0xF596),(0xF597), +(0xF598),(0xF599),(0xF59A),(0xF59B),(0xF59C),(0xF59D),(0xF59E),(0xF59F), +(0xF5A0),(0xF5A1),(0xF5A2),(0xF5A3),(0xF5A4),(0xF5A5),(0xF5A6),(0xF5A7), +(0xF5A8),(0xF5A9),(0xF5AA),(0xF5AB),(0xF5AC),(0xF5AD),(0xF5AE),(0xF5AF), +(0xF5B0),(0xF5B1),(0xF5B2),(0xF5B3),(0xF5B4),(0xF5B5),(0xF5B6),(0xF5B7), +(0xF5B8),(0xF5B9),(0xF5BA),(0xF5BB),(0xF5BC),(0xF5BD),(0xF5BE),(0xF5BF), +(0xF5C0),(0xF5C1),(0xF5C2),(0xF5C3),(0xF5C4),(0xF5C5),(0xF5C6),(0xF5C7), +(0xF5C8),(0xF5C9),(0xF5CA),(0xF5CB),(0xF5CC),(0xF5CD),(0xF5CE),(0xF5CF), +(0xF5D0),(0xF5D1),(0xF5D2),(0xF5D3),(0xF5D4),(0xF5D5),(0xF5D6),(0xF5D7), +(0xF5D8),(0xF5D9),(0xF5DA),(0xF5DB),(0xF5DC),(0xF5DD),(0xF5DE),(0xF5DF), +(0xF5E0),(0xF5E1),(0xF5E2),(0xF5E3),(0xF5E4),(0xF5E5),(0xF5E6),(0xF5E7), +(0xF5E8),(0xF5E9),(0xF5EA),(0xF5EB),(0xF5EC),(0xF5ED),(0xF5EE),(0xF5EF), +(0xF5F0),(0xF5F1),(0xF5F2),(0xF5F3),(0xF5F4),(0xF5F5),(0xF5F6),(0xF5F7), +(0xF5F8),(0xF5F9),(0xF5FA),(0xF5FB),(0xF5FC), +(0xF640),(0xF641),(0xF642),(0xF643),(0xF644),(0xF645),(0xF646),(0xF647), +(0xF648),(0xF649),(0xF64A),(0xF64B),(0xF64C),(0xF64D),(0xF64E),(0xF64F), +(0xF650),(0xF651),(0xF652),(0xF653),(0xF654),(0xF655),(0xF656),(0xF657), +(0xF658),(0xF659),(0xF65A),(0xF65B),(0xF65C),(0xF65D),(0xF65E),(0xF65F), +(0xF660),(0xF661),(0xF662),(0xF663),(0xF664),(0xF665),(0xF666),(0xF667), +(0xF668),(0xF669),(0xF66A),(0xF66B),(0xF66C),(0xF66D),(0xF66E),(0xF66F), +(0xF670),(0xF671),(0xF672),(0xF673),(0xF674),(0xF675),(0xF676),(0xF677), +(0xF678),(0xF679),(0xF67A),(0xF67B),(0xF67C),(0xF67D),(0xF67E), +(0xF680),(0xF681),(0xF682),(0xF683),(0xF684),(0xF685),(0xF686),(0xF687), +(0xF688),(0xF689),(0xF68A),(0xF68B),(0xF68C),(0xF68D),(0xF68E),(0xF68F), +(0xF690),(0xF691),(0xF692),(0xF693),(0xF694),(0xF695),(0xF696),(0xF697), +(0xF698),(0xF699),(0xF69A),(0xF69B),(0xF69C),(0xF69D),(0xF69E),(0xF69F), +(0xF6A0),(0xF6A1),(0xF6A2),(0xF6A3),(0xF6A4),(0xF6A5),(0xF6A6),(0xF6A7), +(0xF6A8),(0xF6A9),(0xF6AA),(0xF6AB),(0xF6AC),(0xF6AD),(0xF6AE),(0xF6AF), +(0xF6B0),(0xF6B1),(0xF6B2),(0xF6B3),(0xF6B4),(0xF6B5),(0xF6B6),(0xF6B7), +(0xF6B8),(0xF6B9),(0xF6BA),(0xF6BB),(0xF6BC),(0xF6BD),(0xF6BE),(0xF6BF), +(0xF6C0),(0xF6C1),(0xF6C2),(0xF6C3),(0xF6C4),(0xF6C5),(0xF6C6),(0xF6C7), +(0xF6C8),(0xF6C9),(0xF6CA),(0xF6CB),(0xF6CC),(0xF6CD),(0xF6CE),(0xF6CF), +(0xF6D0),(0xF6D1),(0xF6D2),(0xF6D3),(0xF6D4),(0xF6D5),(0xF6D6),(0xF6D7), +(0xF6D8),(0xF6D9),(0xF6DA),(0xF6DB),(0xF6DC),(0xF6DD),(0xF6DE),(0xF6DF), +(0xF6E0),(0xF6E1),(0xF6E2),(0xF6E3),(0xF6E4),(0xF6E5),(0xF6E6),(0xF6E7), +(0xF6E8),(0xF6E9),(0xF6EA),(0xF6EB),(0xF6EC),(0xF6ED),(0xF6EE),(0xF6EF), +(0xF6F0),(0xF6F1),(0xF6F2),(0xF6F3),(0xF6F4),(0xF6F5),(0xF6F6),(0xF6F7), +(0xF6F8),(0xF6F9),(0xF6FA),(0xF6FB),(0xF6FC), +(0xF740),(0xF741),(0xF742),(0xF743),(0xF744),(0xF745),(0xF746),(0xF747), +(0xF748),(0xF749),(0xF74A),(0xF74B),(0xF74C),(0xF74D),(0xF74E),(0xF74F), +(0xF750),(0xF751),(0xF752),(0xF753),(0xF754),(0xF755),(0xF756),(0xF757), +(0xF758),(0xF759),(0xF75A),(0xF75B),(0xF75C),(0xF75D),(0xF75E),(0xF75F), +(0xF760),(0xF761),(0xF762),(0xF763),(0xF764),(0xF765),(0xF766),(0xF767), +(0xF768),(0xF769),(0xF76A),(0xF76B),(0xF76C),(0xF76D),(0xF76E),(0xF76F), +(0xF770),(0xF771),(0xF772),(0xF773),(0xF774),(0xF775),(0xF776),(0xF777), +(0xF778),(0xF779),(0xF77A),(0xF77B),(0xF77C),(0xF77D),(0xF77E), +(0xF780),(0xF781),(0xF782),(0xF783),(0xF784),(0xF785),(0xF786),(0xF787), +(0xF788),(0xF789),(0xF78A),(0xF78B),(0xF78C),(0xF78D),(0xF78E),(0xF78F), +(0xF790),(0xF791),(0xF792),(0xF793),(0xF794),(0xF795),(0xF796),(0xF797), +(0xF798),(0xF799),(0xF79A),(0xF79B),(0xF79C),(0xF79D),(0xF79E),(0xF79F), +(0xF7A0),(0xF7A1),(0xF7A2),(0xF7A3),(0xF7A4),(0xF7A5),(0xF7A6),(0xF7A7), +(0xF7A8),(0xF7A9),(0xF7AA),(0xF7AB),(0xF7AC),(0xF7AD),(0xF7AE),(0xF7AF), +(0xF7B0),(0xF7B1),(0xF7B2),(0xF7B3),(0xF7B4),(0xF7B5),(0xF7B6),(0xF7B7), +(0xF7B8),(0xF7B9),(0xF7BA),(0xF7BB),(0xF7BC),(0xF7BD),(0xF7BE),(0xF7BF), +(0xF7C0),(0xF7C1),(0xF7C2),(0xF7C3),(0xF7C4),(0xF7C5),(0xF7C6),(0xF7C7), +(0xF7C8),(0xF7C9),(0xF7CA),(0xF7CB),(0xF7CC),(0xF7CD),(0xF7CE),(0xF7CF), +(0xF7D0),(0xF7D1),(0xF7D2),(0xF7D3),(0xF7D4),(0xF7D5),(0xF7D6),(0xF7D7), +(0xF7D8),(0xF7D9),(0xF7DA),(0xF7DB),(0xF7DC),(0xF7DD),(0xF7DE),(0xF7DF), +(0xF7E0),(0xF7E1),(0xF7E2),(0xF7E3),(0xF7E4),(0xF7E5),(0xF7E6),(0xF7E7), +(0xF7E8),(0xF7E9),(0xF7EA),(0xF7EB),(0xF7EC),(0xF7ED),(0xF7EE),(0xF7EF), +(0xF7F0),(0xF7F1),(0xF7F2),(0xF7F3),(0xF7F4),(0xF7F5),(0xF7F6),(0xF7F7), +(0xF7F8),(0xF7F9),(0xF7FA),(0xF7FB),(0xF7FC), +(0xF840),(0xF841),(0xF842),(0xF843),(0xF844),(0xF845),(0xF846),(0xF847), +(0xF848),(0xF849),(0xF84A),(0xF84B),(0xF84C),(0xF84D),(0xF84E),(0xF84F), +(0xF850),(0xF851),(0xF852),(0xF853),(0xF854),(0xF855),(0xF856),(0xF857), +(0xF858),(0xF859),(0xF85A),(0xF85B),(0xF85C),(0xF85D),(0xF85E),(0xF85F), +(0xF860),(0xF861),(0xF862),(0xF863),(0xF864),(0xF865),(0xF866),(0xF867), +(0xF868),(0xF869),(0xF86A),(0xF86B),(0xF86C),(0xF86D),(0xF86E),(0xF86F), +(0xF870),(0xF871),(0xF872),(0xF873),(0xF874),(0xF875),(0xF876),(0xF877), +(0xF878),(0xF879),(0xF87A),(0xF87B),(0xF87C),(0xF87D),(0xF87E), +(0xF880),(0xF881),(0xF882),(0xF883),(0xF884),(0xF885),(0xF886),(0xF887), +(0xF888),(0xF889),(0xF88A),(0xF88B),(0xF88C),(0xF88D),(0xF88E),(0xF88F), +(0xF890),(0xF891),(0xF892),(0xF893),(0xF894),(0xF895),(0xF896),(0xF897), +(0xF898),(0xF899),(0xF89A),(0xF89B),(0xF89C),(0xF89D),(0xF89E),(0xF89F), +(0xF8A0),(0xF8A1),(0xF8A2),(0xF8A3),(0xF8A4),(0xF8A5),(0xF8A6),(0xF8A7), +(0xF8A8),(0xF8A9),(0xF8AA),(0xF8AB),(0xF8AC),(0xF8AD),(0xF8AE),(0xF8AF), +(0xF8B0),(0xF8B1),(0xF8B2),(0xF8B3),(0xF8B4),(0xF8B5),(0xF8B6),(0xF8B7), +(0xF8B8),(0xF8B9),(0xF8BA),(0xF8BB),(0xF8BC),(0xF8BD),(0xF8BE),(0xF8BF), +(0xF8C0),(0xF8C1),(0xF8C2),(0xF8C3),(0xF8C4),(0xF8C5),(0xF8C6),(0xF8C7), +(0xF8C8),(0xF8C9),(0xF8CA),(0xF8CB),(0xF8CC),(0xF8CD),(0xF8CE),(0xF8CF), +(0xF8D0),(0xF8D1),(0xF8D2),(0xF8D3),(0xF8D4),(0xF8D5),(0xF8D6),(0xF8D7), +(0xF8D8),(0xF8D9),(0xF8DA),(0xF8DB),(0xF8DC),(0xF8DD),(0xF8DE),(0xF8DF), +(0xF8E0),(0xF8E1),(0xF8E2),(0xF8E3),(0xF8E4),(0xF8E5),(0xF8E6),(0xF8E7), +(0xF8E8),(0xF8E9),(0xF8EA),(0xF8EB),(0xF8EC),(0xF8ED),(0xF8EE),(0xF8EF), +(0xF8F0),(0xF8F1),(0xF8F2),(0xF8F3),(0xF8F4),(0xF8F5),(0xF8F6),(0xF8F7), +(0xF8F8),(0xF8F9),(0xF8FA),(0xF8FB),(0xF8FC), +(0xF940),(0xF941),(0xF942),(0xF943),(0xF944),(0xF945),(0xF946),(0xF947), +(0xF948),(0xF949),(0xF94A),(0xF94B),(0xF94C),(0xF94D),(0xF94E),(0xF94F), +(0xF950),(0xF951),(0xF952),(0xF953),(0xF954),(0xF955),(0xF956),(0xF957), +(0xF958),(0xF959),(0xF95A),(0xF95B),(0xF95C),(0xF95D),(0xF95E),(0xF95F), +(0xF960),(0xF961),(0xF962),(0xF963),(0xF964),(0xF965),(0xF966),(0xF967), +(0xF968),(0xF969),(0xF96A),(0xF96B),(0xF96C),(0xF96D),(0xF96E),(0xF96F), +(0xF970),(0xF971),(0xF972),(0xF973),(0xF974),(0xF975),(0xF976),(0xF977), +(0xF978),(0xF979),(0xF97A),(0xF97B),(0xF97C),(0xF97D),(0xF97E), +(0xF980),(0xF981),(0xF982),(0xF983),(0xF984),(0xF985),(0xF986),(0xF987), +(0xF988),(0xF989),(0xF98A),(0xF98B),(0xF98C),(0xF98D),(0xF98E),(0xF98F), +(0xF990),(0xF991),(0xF992),(0xF993),(0xF994),(0xF995),(0xF996),(0xF997), +(0xF998),(0xF999),(0xF99A),(0xF99B),(0xF99C),(0xF99D),(0xF99E),(0xF99F), +(0xF9A0),(0xF9A1),(0xF9A2),(0xF9A3),(0xF9A4),(0xF9A5),(0xF9A6),(0xF9A7), +(0xF9A8),(0xF9A9),(0xF9AA),(0xF9AB),(0xF9AC),(0xF9AD),(0xF9AE),(0xF9AF), +(0xF9B0),(0xF9B1),(0xF9B2),(0xF9B3),(0xF9B4),(0xF9B5),(0xF9B6),(0xF9B7), +(0xF9B8),(0xF9B9),(0xF9BA),(0xF9BB),(0xF9BC),(0xF9BD),(0xF9BE),(0xF9BF), +(0xF9C0),(0xF9C1),(0xF9C2),(0xF9C3),(0xF9C4),(0xF9C5),(0xF9C6),(0xF9C7), +(0xF9C8),(0xF9C9),(0xF9CA),(0xF9CB),(0xF9CC),(0xF9CD),(0xF9CE),(0xF9CF), +(0xF9D0),(0xF9D1),(0xF9D2),(0xF9D3),(0xF9D4),(0xF9D5),(0xF9D6),(0xF9D7), +(0xF9D8),(0xF9D9),(0xF9DA),(0xF9DB),(0xF9DC),(0xF9DD),(0xF9DE),(0xF9DF), +(0xF9E0),(0xF9E1),(0xF9E2),(0xF9E3),(0xF9E4),(0xF9E5),(0xF9E6),(0xF9E7), +(0xF9E8),(0xF9E9),(0xF9EA),(0xF9EB),(0xF9EC),(0xF9ED),(0xF9EE),(0xF9EF), +(0xF9F0),(0xF9F1),(0xF9F2),(0xF9F3),(0xF9F4),(0xF9F5),(0xF9F6),(0xF9F7), +(0xF9F8),(0xF9F9),(0xF9FA),(0xF9FB),(0xF9FC); + +#Test that all the characters are stored correctly +SELECT HEX(c1) FROM t1; + +#Test conversion to ucs2 +CREATE TABLE t2 SELECT CONVERT(c1 USING ucs2) AS c1 FROM t1; +SELECT HEX(c1) FROM t2; + +#Test round trip conversion +CREATE TABLE t3 SELECT CONVERT(c1 USING cp932) AS c1 FROM t2; +SELECT HEX(c1) FROM t3; + +#Test conversion to eucjpms +CREATE TABLE t4 SELECT CONVERT(c1 USING eucjpms) AS c1 FROM t1; +SELECT HEX(c1) FROM t4; + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; + +SET collation_connection='cp932_japanese_ci'; +-- source include/ctype_filesort.inc +-- source include/ctype_like_range_f1f2.inc +SET collation_connection='cp932_bin'; +-- source include/ctype_filesort.inc +-- source include/ctype_like_range_f1f2.inc + +# +# Bug#29333 myisam corruption with +# character set cp932 collate cp932_japanese_ci +# +create table t2 (a char(1)); +insert into t2 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'); +insert into t2 values ('8'),('9'),('A'),('B'),('C'),('D'),('E'),('F'); +create table t1 ( + a varchar(2) character set cp932 +) engine=myisam; +--disable_warnings +insert into t1 +select unhex(concat(t24.a, t23.a, t22.a, t21.a)) +from t2 t21, t2 t22, t2 t23, t2 t24; +--enable_warnings +delete from t1 where a=''; +alter table t1 add key(a); +check table t1; +drop table t1; +drop table t2; + + +# +# Bug#12547: Inserting long string into varchar causes table crash in cp932 +# +create table t1 (col1 varchar(1)) character set cp932; +insert into t1 values ('a'); +insert ignore into t1 values ('ab'); +select * from t1; +insert ignore into t1 values ('abc'); +select * from t1; +drop table t1; + +# +# Bug#25815 Data truncated for column TEXT +# +set names utf8; +create table t1 (a text) default character set cp932; +insert into t1 values (_utf8 0xE38182); +show warnings; +select * from t1; +select hex(a) from t1; +drop table t1; + +# +# BUG#16217 - MySQL client misinterpretes multi-byte char as escape `\' +# + +# new command \C or charset +--exec $MYSQL --default-character-set=utf8 test -e "\C cp932 \g" +--exec $MYSQL --default-character-set=cp932 test -e "charset utf8;" + +# its usage to switch internally in mysql to requested charset +--exec $MYSQL --default-character-set=utf8 test -e "charset cp932; select 'ƒ\'; create table t1 (c_cp932 TEXT CHARACTER SET cp932); insert into t1 values('ƒ\'); select * from t1; drop table t1;" +--exec $MYSQL --default-character-set=utf8 test -e "charset cp932; select 'ƒ\'" +--exec $MYSQL --default-character-set=utf8 test -e "/*charset cp932 */; set character_set_client= cp932; select 'ƒ\'" +--exec $MYSQL --default-character-set=utf8 test -e "/*!\C cp932 */; set character_set_client= cp932; select 'ƒ\'" diff --git a/mysql-test/suite/binlog/include/ctype_cp932_binlog.test b/mysql-test/suite/binlog/include/ctype_cp932_binlog.test new file mode 100644 index 00000000..5c172afd --- /dev/null +++ b/mysql-test/suite/binlog/include/ctype_cp932_binlog.test @@ -0,0 +1,35 @@ +-- source include/not_embedded.inc +-- source include/have_cp932.inc +-- source include/have_log_bin.inc + +--character_set cp932 +--disable_warnings +drop table if exists t1; +--enable_warnings + +set names cp932; +set character_set_database = cp932; + +# Test prepared statement with 0x8300 sequence in parameter while +# running with cp932 client character set. +RESET MASTER; +CREATE TABLE t1(f1 blob); +PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)'; +SET @var1= x'8300'; +# TODO: Note that this doesn't actually test the code which was added for +# bug#11338 because this syntax for prepared statements causes the PS to +# be replicated differently than if we executed the PS from C or Java. +# Using this syntax, variable names are inserted into the binlog instead +# of values. The real goal of this test is to check the code that was +# added to Item_param::query_val_str() in order to do hex encoding of +# PS parameters when the client character set is cp932; +# Bug#11338 has an example java program which can be used to verify this +# code (and I have used it to test the fix) until there is some way to +# exercise this code from mysql-test-run. +EXECUTE stmt1 USING @var1; +source include/show_binlog_events.inc; +SELECT HEX(f1) FROM t1; +DROP table t1; +# end test for bug#11338 + +# End of 4.1 tests diff --git a/mysql-test/suite/binlog/include/ctype_ucs_binlog.test b/mysql-test/suite/binlog/include/ctype_ucs_binlog.test new file mode 100644 index 00000000..cc04d98c --- /dev/null +++ b/mysql-test/suite/binlog/include/ctype_ucs_binlog.test @@ -0,0 +1,64 @@ +--source include/not_embedded.inc +--source include/have_ucs2.inc +--source include/have_log_bin.inc + +# +# Check correct binlogging of UCS2 user variables (BUG#3875) +# +SET TIMESTAMP=10000; +create table t2 (c char(30)) charset=ucs2; +set @v=convert('abc' using ucs2); +reset master; +insert into t2 values (@v); +source include/show_binlog_events.inc; +# more important than SHOW BINLOG EVENTS, mysqlbinlog (where we +# absolutely need variables names to be quoted and strings to be +# escaped). +flush logs; +let $MYSQLD_DATADIR= `select @@datadir`; +--replace_regex /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/ +--exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000001 +drop table t2; + +# End of 4.1 tests + + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions +--echo # + +SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-01 06:46:40'); + +FLUSH LOGS; +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); +EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (''ä(i1)'')'; +EXECUTE IMMEDIATE CONVERT('INSERT INTO t1 VALUES (''ä(i2)'')' USING ucs2); +SET @stmt=CONVERT('INSERT INTO t1 VALUES (''ä(i3)'')' USING ucs2); +EXECUTE IMMEDIATE @stmt; + +PREPARE stmt FROM 'INSERT INTO t1 VALUES (''ä(p1)'')'; +EXECUTE stmt; +PREPARE stmt FROM CONVERT('INSERT INTO t1 VALUES (''ä(p2)'')' USING ucs2); +EXECUTE stmt; +SET @stmt=CONVERT('INSERT INTO t1 VALUES (''ä(p3)'')' USING ucs2); +PREPARE stmt FROM @stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +SELECT * FROM t1; +DROP TABLE t1; +FLUSH LOGS; +let $MYSQLD_DATADIR= `select @@datadir`; +--replace_regex /TIMESTAMP=[0-9]*/TIMESTAMP=XXX/ /# at [0-9]*/# at #/ /(exec_time=|end_log_pos |Xid = |thread_id=|server id |table id |mapped to number )[0-9]+/\1#/ /server v [^ ]*/server v #.##.##/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Gtid list [[][0-9]+-[0-9]+-[0-9]+[\]]/Gtid list [#-#-#]/ /session[.](gtid_domain_id|server_id|gtid_seq_no)=[0-9]+/session.\1=#/ /(^#|created )[0-9]{6} [ 12][0-9]:[0-9]{2}:[0-9]{2}/\1YYMMDD HH:MM:SS/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/ +--exec $MYSQL_BINLOG --base64-output=decode-rows -vv $MYSQLD_DATADIR/master-bin.000003 + +SET TIMESTAMP=DEFAULT; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/suite/binlog/include/database.test b/mysql-test/suite/binlog/include/database.test new file mode 100644 index 00000000..e61198b2 --- /dev/null +++ b/mysql-test/suite/binlog/include/database.test @@ -0,0 +1,96 @@ +source include/have_log_bin.inc; +source include/not_embedded.inc; + +# Checking that the drop of a database does not replicate anything in +# addition to the drop of the database + +reset master; +create database testing_1; +use testing_1; +create table t1 (a int); +create function sf1 (a int) returns int return a+1; +create trigger tr1 before insert on t1 for each row insert into t2 values (2*new.a); +create procedure sp1 (a int) insert into t1 values(a); +drop database testing_1; +source include/show_binlog_events.inc; + +# BUG#38773: DROP DATABASE cause switch to stmt-mode when there are +# temporary tables open + +use test; +reset master; +create temporary table tt1 (a int); +create table t1 (a int); +insert into t1 values (1); +disable_warnings; +drop database if exists mysqltest1; +enable_warnings; +insert into t1 values (1); +drop table tt1, t1; +source include/show_binlog_events.inc; + +FLUSH STATUS; + +--echo +--echo # 'DROP TABLE IF EXISTS <deleted tables>' is binlogged +--echo # when 'DROP DATABASE' fails and at least one table is deleted +--echo # from the database. +RESET MASTER; +CREATE DATABASE testing_1; +USE testing_1; +CREATE TABLE t1(c1 INT); +CREATE TABLE t2(c1 INT); + +let $prefix= `SELECT UUID()`; +--echo # Create a file in the database directory +--replace_result $prefix FAKE_FILE +--disable_ps2_protocol +eval SELECT 'hello' INTO OUTFILE 'fake_file.$prefix'; +--enable_ps2_protocol + +--echo +--echo # 'DROP DATABASE' will fail if there is any other file in the the +--echo # database directory + +# Use '/' instead of '\' in the error message. On windows platform, dir is +# formed with '\'. +--replace_regex /\\testing_1\\*/\/testing_1\// /66/39/ /93/39/ /17/39/ /247/39/ /File exists/Directory not empty/ +--error 1010 +DROP DATABASE testing_1; +let $wait_binlog_event= DROP TABLE IF EXIST; +source include/wait_for_binlog_event.inc; +let $MYSQLD_DATADIR= `SELECT @@datadir`; + +--echo +--echo # Remove the fake file. +--remove_file $MYSQLD_DATADIR/testing_1/fake_file.$prefix +--echo # Now we can drop the database. +DROP DATABASE testing_1; + + +--echo # +--echo # Bug#11765416 58381: FAILED DROP DATABASE CAN BREAK STATEMENT +--echo # BASED REPLICATION +--echo # + +USE test; +--disable_warnings +DROP DATABASE IF EXISTS db1; +DROP TABLE IF EXISTS t3; +--enable_warnings + +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT); +CREATE TABLE db1.t2 (b INT, KEY(b)) engine=innodb; +CREATE TABLE t3 (a INT, KEY (a), FOREIGN KEY(a) REFERENCES db1.t2(b)) + engine=innodb; +RESET MASTER; + +--error ER_ROW_IS_REFERENCED_2 +DROP DATABASE db1; # Fails because of the fk +SHOW TABLES FROM db1; # t1 was dropped, t2 remains +--source include/show_binlog_events.inc # Check that the binlog drops t1 + +# Cleanup +DROP TABLE t3; +DROP DATABASE db1; diff --git a/mysql-test/suite/binlog/include/drop_table.test b/mysql-test/suite/binlog/include/drop_table.test new file mode 100644 index 00000000..c55cbb67 --- /dev/null +++ b/mysql-test/suite/binlog/include/drop_table.test @@ -0,0 +1,34 @@ +# +# Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +connection con1; +RESET MASTER; +CREATE TABLE t1 (a INT); +SET AUTOCOMMIT=OFF; +BEGIN; +INSERT INTO t1 VALUES(1); + +connection con2; +--send DROP TABLE t1; + +connection con1; +COMMIT; + +connection con2; +--reap + +connection default; + +--disconnect con1 +--disconnect con2 + +let $VERSION=`select version()`; +source include/show_binlog_events.inc; diff --git a/mysql-test/suite/binlog/include/drop_temp_table.test b/mysql-test/suite/binlog/include/drop_temp_table.test new file mode 100644 index 00000000..7c95195e --- /dev/null +++ b/mysql-test/suite/binlog/include/drop_temp_table.test @@ -0,0 +1,176 @@ +--source include/have_innodb.inc + +--disable_warnings +DROP DATABASE IF EXISTS `drop-temp+table-test`; +--enable_warnings + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connection con1; +RESET MASTER; +CREATE DATABASE `drop-temp+table-test`; +USE `drop-temp+table-test`; +CREATE TEMPORARY TABLE shortn1 (a INT); +CREATE TEMPORARY TABLE `table:name` (a INT); +CREATE TEMPORARY TABLE shortn2 (a INT); + +############################################################################## +# MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written +# to binlog only if the corresponding temporary table exists. +############################################################################## +CREATE TEMPORARY TABLE tmp(c1 int); +CREATE TEMPORARY TABLE tmp1(c1 int); +CREATE TEMPORARY TABLE tmp2(c1 int); +CREATE TEMPORARY TABLE tmp3(c1 int); +CREATE TABLE t(c1 int); + +DROP TEMPORARY TABLE IF EXISTS tmp; + +--disable_warnings +# Post MDEV-20091: Following DROP TEMPORARY TABLE statement should not be +# logged as the table is already dropped above. +DROP TEMPORARY TABLE IF EXISTS tmp; + +# Post MDEV-20091: Only DROP TEMPORARY TABLE statement should be written only +# for 'tmp1' table. +DROP TEMPORARY TABLE IF EXISTS tmp, tmp1; +DROP TEMPORARY TABLE tmp3; + +#In RBR, tmp2 will NOT be binlogged, because it is a temporary table. +DROP TABLE IF EXISTS tmp2, t; + +#In RBR, tmp2 will be binlogged, because it does not exist and master do not know +# whether it is a temporary table or not. +DROP TABLE IF EXISTS tmp2, t; +--enable_warnings + +SELECT GET_LOCK("a",10); + +# +# BUG48216 Replication fails on all slaves after upgrade to 5.0.86 on master +# +# When the session is closed, any temporary tables of the session are dropped +# and are binlogged. But it will be binlogged with a wrong database name when +# the length of the database name('drop-temp-table-test') is greater than the +# current database name('test'). +# +USE test; +disconnect con1; + +connection con2; +# We want to SHOW BINLOG EVENTS, to know what was logged. But there is no +# guarantee that logging of the terminated con1 has been done yet. +# To be sure that logging has been done, we use a user lock. +SELECT GET_LOCK("a",10); +let $VERSION=`SELECT VERSION()`; +source include/show_binlog_events.inc; +DROP DATABASE `drop-temp+table-test`; + + +# +# Bug #54842: DROP TEMPORARY TABLE not binlogged after manual switching binlog format to ROW +# +# Sanity test. Checking that implicit DROP event is logged. +# +# After BUG#52616, the switch to ROW mode becomes effective even +# if there are open temporary tables. As such the implicit drop +# for temporary tables on session closing must be logged. +# +# MDEV-20091: DROP TEMPORARY TABLE IF EXISTS statements will be written to +# binlog only if the corresponding temporary table exists. In row based +# replication temporary tables are not replicated hence their corresponding +# DROP TEMPORARY TABLE statement will be not be written to binary log upon +# session closure. +# + +RESET MASTER; + +CREATE TABLE t1 ( i text ); + +--connect(con1,localhost,root,,) +CREATE TEMPORARY TABLE ttmp1 ( i text ); +SET @@session.binlog_format=ROW; +INSERT INTO t1 VALUES ('1'); +SELECT @@session.binlog_format; +--disconnect con1 + +-- connection default +if (!`SELECT @@BINLOG_FORMAT = 'ROW'`) { +--let $wait_binlog_event= DROP +--source include/wait_for_binlog_event.inc +} +-- source include/show_binlog_events.inc +RESET MASTER; + +DROP TABLE t1; + +# End of 4.1 tests + + +--echo # +--echo # BUG#28642318: POINT IN TIME RECOVERY USING MYSQLBINLOG BROKEN +--echo # WITH TEMPORARY TABLE -> ERRORS + +--echo # Test case for DELETE query. + +RESET MASTER; +connect (con1,localhost,root,,); + +--echo # Set up. +--connection default +--disable_warnings +SET @save_binlog_format= @@session.binlog_format; +SET @@session.binlog_format=STATEMENT; +let $MYSQLD_DATADIR= `select @@datadir`; +CREATE TABLE t1 (a INT) ENGINE=INNODB; + +--connection con1 +SET @@session.binlog_format=STATEMENT; +CREATE TEMPORARY TABLE t1 (b BLOB) ENGINE=INNODB; + +--connection default +DELETE d1, d2 FROM t1 AS d1, t1 AS d2 WHERE d1.a<>d2.a; + +--exec $MYSQL_BINLOG --force-if-open $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/bug28642318.sql + +--connection default +DROP TABLE t1; + +--echo # DELETE query fails with table re-open error without patch. +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug28642318.sql + +--echo # Clean up. +--connection con1 +DROP TABLE IF EXISTS t1; + +--connection default +DROP TABLE IF EXISTS t1; +RESET MASTER; + +--echo # Test case for DROP query. + +--connection default +CREATE TABLE t2 (a INT) ENGINE=INNODB; + +--connection con1 +CREATE TEMPORARY TABLE t2 (b BLOB) ENGINE=INNODB; + +--connection default +DROP TABLE t2; + +--connection con1 +DROP TABLE t2; + +--connection default +--exec $MYSQL_BINLOG --force-if-open $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/bug28642318.sql + +--echo # DROP table query fails with unknown table error without patch. +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug28642318.sql + +--echo # Clean up +--connection default +SET @@session.binlog_format= @save_binlog_format; +RESET MASTER; + +--disconnect con1 +--enable_warnings diff --git a/mysql-test/suite/binlog/include/implicit.test b/mysql-test/suite/binlog/include/implicit.test new file mode 100644 index 00000000..de906fc0 --- /dev/null +++ b/mysql-test/suite/binlog/include/implicit.test @@ -0,0 +1,28 @@ +# First part: outside a transaction +RESET MASTER; +eval $prepare; + +INSERT INTO t1 VALUES (1); +source include/show_binlog_events.inc; +eval $statement; +source include/show_binlog_events.inc; +if ($cleanup) { + eval $cleanup; +} + +# Second part: inside a transaction +RESET MASTER; +eval $prepare; +BEGIN; +INSERT INTO t1 VALUES (2); +source include/show_binlog_events.inc; +eval $statement; +source include/show_binlog_events.inc; +INSERT INTO t1 VALUES (3); +source include/show_binlog_events.inc; +COMMIT; +source include/show_binlog_events.inc; +if ($cleanup) { + eval $cleanup; +} + diff --git a/mysql-test/suite/binlog/include/insert_select-binlog.test b/mysql-test/suite/binlog/include/insert_select-binlog.test new file mode 100644 index 00000000..ef22373a --- /dev/null +++ b/mysql-test/suite/binlog/include/insert_select-binlog.test @@ -0,0 +1,38 @@ +# Embedded server doesn't support binlog +-- source include/not_embedded.inc +-- source include/have_log_bin.inc + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +# Check if a partly-completed INSERT SELECT in a MyISAM table goes into the +# binlog + +create table t1(a int, unique(a)); +insert into t1 values(2); +create table t2(a int); +insert into t2 values(1),(2); +reset master; +--error ER_DUP_ENTRY +insert into t1 select * from t2; +# The above should produce an error, but still be in the binlog; +# verify the binlog : +let $VERSION=`select version()`; +source include/show_binlog_events.inc; +select * from t1; +drop table t1, t2; + +# Verify that a partly-completed CREATE TABLE .. SELECT does not +# get into the binlog (Bug #6682) +create table t1(a int); +insert into t1 values(1),(1); +reset master; +--error ER_DUP_ENTRY +create table t2(unique(a)) select a from t1; +# The above should produce an error, *and* not appear in the binlog +let $VERSION=`select version()`; +source include/show_binlog_events.inc; +drop table t1; + +# End of 4.1 tests diff --git a/mysql-test/suite/binlog/include/mix_innodb_myisam_binlog.test b/mysql-test/suite/binlog/include/mix_innodb_myisam_binlog.test new file mode 100644 index 00000000..74fbe6b4 --- /dev/null +++ b/mysql-test/suite/binlog/include/mix_innodb_myisam_binlog.test @@ -0,0 +1,654 @@ +# Check that binlog is ok when a transaction mixes updates to InnoDB and +# MyISAM. +# It would be nice to make this a replication test, but in 4.0 the +# slave is always with --skip-innodb in the testsuite. I (Guilhem) however +# did some tests manually on a slave; tables are replicated fine and +# Exec_Master_Log_Pos advances as expected. + +-- source include/have_log_bin.inc +-- source include/have_innodb.inc + +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); + +connection con1; +create table t1 (a int) engine=innodb; +create table t2 (a int) engine=myisam; + +reset master; + +begin; +insert into t1 values(1); +insert into t2 select * from t1; +commit; + +source include/show_binlog_events.inc; + +delete from t1; +delete from t2; +reset master; + +begin; +insert into t1 values(2); +insert into t2 select * from t1; +# should say some changes to non-transact1onal tables couldn't be rolled back +rollback; + +source include/show_binlog_events.inc; + +delete from t1; +delete from t2; +reset master; + +begin; +insert into t1 values(3); +savepoint my_savepoint; +insert into t1 values(4); +insert into t2 select * from t1; +rollback to savepoint my_savepoint; +commit; + +source include/show_binlog_events.inc; + +delete from t1; +delete from t2; +reset master; + +begin; +insert into t1 values(5); +savepoint my_savepoint; +insert into t1 values(6); +insert into t2 select * from t1; +rollback to savepoint my_savepoint; +insert into t1 values(7); +commit; +select a from t1 order by a; # check that savepoints work :) + +source include/show_binlog_events.inc; + +# and when ROLLBACK is not explicit? +delete from t1; +delete from t2; +reset master; + +select get_lock("a",10); +begin; +insert into t1 values(8); +insert into t2 select * from t1; +disconnect con1; + +connection con2; +# We want to SHOW BINLOG EVENTS, to know what was logged. But there is no +# guarantee that logging of the terminated con1 has been done yet (it may not +# even be started, so con1 may have not even attempted to lock the binlog yet; +# so SHOW BINLOG EVENTS may come before con1 does the loggin. To be sure that +# logging has been done, we use a user lock. +select get_lock("a",10); +source include/show_binlog_events.inc; + +# and when not in a transact1on? +delete from t1; +delete from t2; +reset master; + +insert into t1 values(9); +insert into t2 select * from t1; + +source include/show_binlog_events.inc; + +# Check that when the query updat1ng the MyISAM table is the first in the +# transaction, we log it immediately. +delete from t1; +delete from t2; +reset master; + +insert into t1 values(10); # first make t1 non-empty +begin; +insert into t2 select * from t1; +source include/show_binlog_events.inc; +insert into t1 values(11); +commit; + +source include/show_binlog_events.inc; + +# Check that things work like before this BEGIN/ROLLBACK code was added, +# when t2 is INNODB + +alter table t2 engine=INNODB; + +delete from t1; +delete from t2; +reset master; + +begin; +insert into t1 values(12); +insert into t2 select * from t1; +commit; + +source include/show_binlog_events.inc; + +delete from t1; +delete from t2; +reset master; + +begin; +insert into t1 values(13); +insert into t2 select * from t1; +rollback; + +source include/show_binlog_events.inc; + +delete from t1; +delete from t2; +reset master; + +begin; +insert into t1 values(14); +savepoint my_savepoint; +insert into t1 values(15); +insert into t2 select * from t1; +rollback to savepoint my_savepoint; +commit; + +source include/show_binlog_events.inc; + +delete from t1; +delete from t2; +reset master; + +begin; +insert into t1 values(16); +savepoint my_savepoint; +insert into t1 values(17); +insert into t2 select * from t1; +rollback to savepoint my_savepoint; +insert into t1 values(18); +commit; +select a from t1 order by a; # check that savepoints work :) + +source include/show_binlog_events.inc; + +# Test for BUG#5714, where a MyISAM update in the transaction used to +# release row-level locks in InnoDB + +connect (con3,localhost,root,,); + +connection con3; +delete from t1; +delete from t2; +--disable_warnings +alter table t2 engine=MyISAM; +--enable_warnings +insert into t1 values (1); +begin; +select * from t1 for update; + +connection con2; +select (@before:=unix_timestamp())*0; # always give repeatable output +begin; +send select * from t1 for update; + +connection con3; +insert into t2 values (20); + +connection con2; +--error 1205 +reap; +select (@after:=unix_timestamp())*0; # always give repeatable output +# verify that innodb_lock_wait_timeout was exceeded. When there was +# the bug, the reap would return immediately after the insert into t2. +select (@after-@before) >= 2; + +connection con3; +commit; + +connection con2; +drop table t1,t2; +commit; + +# test for BUG#7947 - DO RELEASE_LOCK() not written to binlog on rollback in +# the middle of a transaction + +connection con2; +begin; +create temporary table ti (a int) engine=innodb; +rollback; +insert into ti values(1); +set autocommit=0; +create temporary table t1 (a int) engine=myisam; +commit; +insert t1 values (1); +rollback; +create table t0 (n int); +insert t0 select * from t1; +set autocommit=1; +insert into t0 select GET_LOCK("lock1",0); +set autocommit=0; +create table t2 (n int) engine=innodb; +insert into t2 values (3); +disconnect con2; +connection con3; +select get_lock("lock1",60); +source include/show_binlog_events.inc; +do release_lock("lock1"); +drop table t0,t2; + +# End of 4.1 tests + +# +# Test behaviour of CREATE ... SELECT when mixing MyISAM and InnoDB tables +# + +set autocommit=0; +CREATE TABLE t1 (a int, b int) engine=myisam; +reset master; +INSERT INTO t1 values (1,1),(1,2); +--error ER_DUP_ENTRY +CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1; +# This should give warning +DROP TABLE if exists t2; +INSERT INTO t1 values (3,3); +--error ER_DUP_ENTRY +CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1; +ROLLBACK; +# This should give warning +DROP TABLE IF EXISTS t2; + +CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb; +INSERT INTO t1 VALUES (4,4); +CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +SELECT * from t2; +TRUNCATE table t2; +INSERT INTO t1 VALUES (5,5); +--error ER_DUP_ENTRY +INSERT INTO t2 select * from t1; +SELECT * FROM t2; +DROP TABLE t2; + +INSERT INTO t1 values (6,6); +CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ; +INSERT INTO t1 values (7,7); +ROLLBACK; +INSERT INTO t1 values (8,8); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +COMMIT; +INSERT INTO t1 values (9,9); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ROLLBACK; +SELECT * from t2; +TRUNCATE table t2; +INSERT INTO t1 values (10,10); +--error ER_DUP_ENTRY +INSERT INTO t2 select * from t1; +SELECT * from t1; +INSERT INTO t2 values (100,100); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +COMMIT; +INSERT INTO t2 values (101,101); +CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1; +ROLLBACK; +SELECT * from t2; +DROP TABLE t1,t2; +source include/show_binlog_events.inc; + +# Test for BUG#16559 (ROLLBACK should always have a zero error code in +# binlog). Has to be here and not earlier, as the SELECTs influence +# XIDs differently between normal and ps-protocol (and SHOW BINLOG +# EVENTS above read XIDs). + +connect (con4,localhost,root,,); +connection con3; +reset master; +create table t1 (a int) engine=innodb; +create table t2 (a int) engine=myisam; +select get_lock("a",10); +begin; +insert into t1 values(8); +insert into t2 select * from t1; + +disconnect con3; + +connection con4; +select get_lock("a",10); # wait for rollback to finish +if (`select @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`) +{ + --let $binlog_rollback= query_get_value(SHOW BINLOG EVENTS, Pos, 11) + --let $binlog_query= query_get_value(SHOW BINLOG EVENTS, Info, 11) + if ($binlog_query != ROLLBACK) { + --echo Wrong query from SHOW BINLOG EVENTS. Expected ROLLBACK, got '$binlog_query' + --source include/show_rpl_debug_info.inc + --die Wrong value for slave parameter + } +} +flush logs; + +let $MYSQLD_DATADIR= `select @@datadir`; +# we check that the error code of the "ROLLBACK" event is 0 and not +# ER_SERVER_SHUTDOWN (i.e. disconnection just rolls back transaction +# and does not make slave to stop) + +-- source include/binlog_start_pos.inc + +if (`select @@binlog_format = 'ROW'`) +{ + --echo There is nothing to roll back; transactional changes are removed from the trans cache. +} + +if (`select @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`) +{ + --exec $MYSQL_BINLOG --start-position=$binlog_rollback $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output + + --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR + eval select + (@a:=load_file("$MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output")) + is not null; + --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR + eval select + @a like "%#%error_code=0%ROLLBACK\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR + @a like "%#%error_code=0%ROLLBACK\\r\\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%", + @a not like "%#%error_code=%error_code=%"; +} +drop table t1, t2; + +# +# Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack +# bug #28960 non-trans temp table changes with insert .. select +# not binlogged after rollback +# +# testing appearence of insert into temp_table in binlog. +# There are two branches of execution that require different setup. + +## send_eof() branch + +# prepare + +create temporary table tt (a int unique); +create table ti (a int) engine=innodb; +reset master; + +# action + +begin; +insert into ti values (1); +insert into ti values (2) ; +# This is SAFE because --binlog-direct-non-transactional-updates=FALSE +insert into tt select * from ti; +rollback; + +# check + +select count(*) from tt /* 2 */; +source include/show_binlog_events.inc; +select count(*) from ti /* zero */; +insert into ti select * from tt; +select * from ti /* that is what slave would miss - a bug */; + + +## send_error() branch +delete from ti; +delete from tt where a=1; +reset master; + +# action + +begin; +insert into ti values (1); +insert into ti values (2) /* to make the dup error in the following */; +--error ER_DUP_ENTRY +insert into tt select * from ti /* one affected and error */; +rollback; + +# check + +source include/show_binlog_events.inc; +select count(*) from ti /* zero */; +insert into ti select * from tt; +select * from tt /* that is what otherwise slave missed - the bug */; + +drop table ti, tt; + + +# +# Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack +# +# Testing asserts: if there is a side effect of modifying non-transactional +# table thd->no_trans_update.stmt must be TRUE; +# the assert is active with debug build +# + +--disable_warnings +drop function if exists bug27417; +drop table if exists t1,t2; +--enable_warnings +# side effect table +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; +# target tables +CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); + +delimiter |; +create function bug27417(n int) +RETURNS int(11) +begin + insert into t1 values (null); + return n; +end| +delimiter ;| + +reset master; + +# execute + +insert into t2 values (bug27417(1)); +insert into t2 select bug27417(2); +reset master; + +--error ER_DUP_ENTRY +insert into t2 values (bug27417(2)); +source include/show_binlog_events.inc; /* only (!) with fixes for #23333 will show there is the query */; +select count(*) from t1 /* must be 3 */; + +reset master; +select count(*) from t2; +delete from t2 where a=bug27417(3); +select count(*) from t2 /* nothing got deleted */; +source include/show_binlog_events.inc; /* the query must be in regardless of #23333 */; +select count(*) from t1 /* must be 5 */; + +--enable_info +delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */; +--disable_info +select count(*) from t1 /* must be 7 */; + +# function bug27417 remains for the following testing of bug#23333 +drop table t1,t2; + +# +# Bug#23333 using the patch (and the test) for bug#27471 +# +# throughout the bug tests +# t1 - non-trans side effects gatherer; +# t2 - transactional table; +# + +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; +CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; +CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; +CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; +CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; + + +# +# INSERT +# + +# prepare + + insert into t2 values (1); + reset master; + +# execute + + --error ER_DUP_ENTRY + insert into t2 values (bug27417(1)); + +# check + + source include/show_binlog_events.inc; /* the output must denote there is the query */; + select count(*) from t1 /* must be 1 */; + +# +# INSERT SELECT +# + +# prepare + delete from t1; + delete from t2; + insert into t2 values (2); + reset master; + +# execute + + --error ER_DUP_ENTRY + insert into t2 select bug27417(1) union select bug27417(2); + +# check + + source include/show_binlog_events.inc; /* the output must denote there is the query */; + select count(*) from t1 /* must be 2 */; + +# +# UPDATE inc multi-update +# + +# prepare + delete from t1; + insert into t3 values (1,1),(2,3),(3,4); + reset master; + +# execute + --error ER_DUP_ENTRY + update t3 set b=b+bug27417(1); + +# check + source include/show_binlog_events.inc; /* the output must denote there is the query */; + select count(*) from t1 /* must be 2 */; + +## multi_update::send_eof() branch + +# prepare + delete from t3; + delete from t4; + insert into t3 values (1,1); + insert into t4 values (1,1),(2,2); + + reset master; + +# execute + --error ER_DUP_ENTRY + UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */; + +# check + source include/show_binlog_events.inc; /* the output must denote there is the query */; + select count(*) from t1 /* must be 4 */; + +## send_error() branch of multi_update + +# prepare + delete from t1; + delete from t3; + delete from t4; + insert into t3 values (1,1),(2,2); + insert into t4 values (1,1),(2,2); + + reset master; + +# execute + --error ER_DUP_ENTRY + UPDATE t3,t4 SET t3.a=t4.a + bug27417(1); + +# check + select count(*) from t1 /* must be 1 */; + +# cleanup + drop table t4; + + +# +# DELETE incl multi-delete +# + +# prepare + delete from t1; + delete from t2; + delete from t3; + insert into t2 values (1); + insert into t3 values (1,1); + create trigger trg_del before delete on t2 for each row + insert into t3 values (bug27417(1), 2); + reset master; + +# execute + --error ER_DUP_ENTRY + delete from t2; +# check + source include/show_binlog_events.inc; /* the output must denote there is the query */; + select count(*) from t1 /* must be 1 */; + +# cleanup + drop trigger trg_del; + +# prepare + delete from t1; + delete from t2; + delete from t5; + create trigger trg_del_t2 after delete on t2 for each row + insert into t1 values (1); + insert into t2 values (2),(3); + insert into t5 values (1),(2); + reset master; + +# execute + --error ER_DUP_ENTRY + delete t2.* from t2,t5 where t2.a=t5.a + 1; + +# check + source include/show_binlog_events.inc; /* the output must denote there is the query */; + select count(*) from t1 /* must be 1 */; + + +# +# LOAD DATA +# + +# prepare + delete from t1; + create table t4 (a int default 0, b int primary key) engine=innodb; + insert into t4 values (0, 17); + reset master; + +# execute + --error ER_DUP_ENTRY + load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2); +# check + select * from t4; + select count(*) from t1 /* must be 2 */; + source include/show_binlog_events.inc; /* the output must denote there is the query */; + +# +# bug#23333 cleanup +# + + +drop trigger trg_del_t2; +drop table t1,t2,t3,t4,t5; +drop function bug27417; + + +--echo end of tests + diff --git a/mysql-test/suite/binlog/include/mix_innodb_myisam_side_effects.test b/mysql-test/suite/binlog/include/mix_innodb_myisam_side_effects.test new file mode 100644 index 00000000..7eca19bc --- /dev/null +++ b/mysql-test/suite/binlog/include/mix_innodb_myisam_side_effects.test @@ -0,0 +1,295 @@ +# the file to be sourced from binlog.binlog_mix_innodb_myisam + +# +# Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack +# bug #28960 non-trans temp table changes with insert .. select +# not binlogged after rollback +# +# testing appearence of insert into temp_table in binlog. +# There are two branches of execution that require different setup. + +# checking binlog content filled with row-based events due to +# a used stored function modifies non-transactional table + +## send_eof() branch + +# prepare + +create temporary table tt (a int unique); +create table ti (a int) engine=innodb; +reset master; + +# action + +begin; +insert into ti values (1); +insert into ti values (2) ; +# This is SAFE because --binlog-direct-non-transactional-updates=FALSE +insert into tt select * from ti; +rollback; + +# check + +select count(*) from tt /* 2 */; +source include/show_binlog_events.inc; +select count(*) from ti /* zero */; +insert into ti select * from tt; +select * from ti /* that is what slave would miss - bug#28960 */; + + +## send_error() branch +delete from ti; +delete from tt where a=1; +reset master; + +# action + +begin; +insert into ti values (1); +insert into ti values (2) /* to make the dup error in the following */; +--error ER_DUP_ENTRY +insert into tt select * from ti /* one affected and error */; +rollback; + +# check + +source include/show_binlog_events.inc; # nothing in binlog with row bilog format +select count(*) from ti /* zero */; +insert into ti select * from tt; +select * from tt /* that is what otherwise slave missed - the bug */; + +drop table ti; + + +# +# Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack +# +# Testing asserts: if there is a side effect of modifying non-transactional +# table thd->no_trans_update.stmt must be TRUE; +# the assert is active with debug build +# + +--disable_warnings +drop function if exists bug27417; +drop table if exists t1,t2; +--enable_warnings +# side effect table +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; +# target tables +CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); + +delimiter |; +create function bug27417(n int) +RETURNS int(11) +begin + insert into t1 values (null); + return n; +end| +delimiter ;| + +reset master; + +# execute + +insert into t2 values (bug27417(1)); +insert into t2 select bug27417(2); +reset master; + +--error ER_DUP_ENTRY +insert into t2 values (bug27417(2)); +source include/show_binlog_events.inc; #only (!) with fixes for #23333 will show there is the query +select count(*) from t1 /* must be 3 */; + +reset master; +select count(*) from t2; +delete from t2 where a=bug27417(3); +select count(*) from t2 /* nothing got deleted */; +source include/show_binlog_events.inc; # the query must be in regardless of #23333 +select count(*) from t1 /* must be 5 */; + +--enable_info +delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */; +--disable_info +select count(*) from t1 /* must be 7 */; + +# function bug27417 remains for the following testing of bug#23333 +drop table t1,t2; + +# +# Bug#23333 using the patch (and the test) for bug#27471 +# throughout the bug tests +# t1 - non-trans side effects gatherer; +# t2 - transactional table; +# +CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; +CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; +CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; +CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; +CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; + + +# +# INSERT +# + +# prepare + + insert into t2 values (1); + reset master; + +# execute + + --error ER_DUP_ENTRY + insert into t2 values (bug27417(1)); + +# check + + source include/show_binlog_events.inc; # must be event of the query + select count(*) from t1 /* must be 1 */; + +# +# INSERT SELECT +# + +# prepare + delete from t1; + delete from t2; + insert into t2 values (2); + reset master; + +# execute + + --error ER_DUP_ENTRY + insert into t2 select bug27417(1) union select bug27417(2); + +# check + + source include/show_binlog_events.inc; # must be events of the query + select count(*) from t1 /* must be 2 */; + +# +# UPDATE inc multi-update +# + +# prepare + delete from t1; + insert into t3 values (1,1),(2,3),(3,4); + reset master; + +# execute + --error ER_DUP_ENTRY + update t3 set b=b+bug27417(1); + +# check + source include/show_binlog_events.inc; # must be events of the query + select count(*) from t1 /* must be 2 */; + +## multi_update::send_eof() branch + +# prepare + delete from t3; + delete from t4; + insert into t3 values (1,1); + insert into t4 values (1,1),(2,2); + + reset master; + +# execute + --error ER_DUP_ENTRY + UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */; + +# check + source include/show_binlog_events.inc; # the offset must denote there is the query + select count(*) from t1 /* must be 4 */; + +## send_error() branch of multi_update + +# prepare + delete from t1; + delete from t3; + delete from t4; + insert into t3 values (1,1),(2,2); + insert into t4 values (1,1),(2,2); + + reset master; + +# execute + --error ER_DUP_ENTRY + UPDATE t3,t4 SET t3.a = t4.a + bug27417(1) where t3.a = 1; + +# check + select count(*) from t1 /* must be 1 */; + +# cleanup + drop table t4; + + +# +# DELETE incl multi-delete +# + +# prepare + delete from t1; + delete from t2; + delete from t3; + insert into t2 values (1); + insert into t3 values (1,1); + create trigger trg_del before delete on t2 for each row + insert into t3 values (bug27417(1), 2); + reset master; + +# execute + --error ER_DUP_ENTRY + delete from t2; +# check + source include/show_binlog_events.inc; # the offset must denote there is the query + select count(*) from t1 /* must be 1 */; + +# cleanup + drop trigger trg_del; + +# prepare + delete from t1; + delete from t2; + delete from t5; + create trigger trg_del_t2 after delete on t2 for each row + insert into t1 values (1); + insert into t2 values (2),(3); + insert into t5 values (1),(2); + reset master; + +# execute + --error ER_DUP_ENTRY + delete t2.* from t2,t5 where t2.a=t5.a + 1; + +# check + source include/show_binlog_events.inc; # must be events of the query + select count(*) from t1 /* must be 1 */; + + +# +# LOAD DATA +# + +# prepare + delete from t1; + create table t4 (a int default 0, b int primary key) engine=innodb; + insert into t4 values (0, 17); + reset master; + +# execute + --error ER_DUP_ENTRY + load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2); +# check + select * from t4; + select count(*) from t1 /* must be 2 */; + source include/show_binlog_events.inc; # must be events of the query + +# +# bug#23333 cleanup +# + + +drop trigger trg_del_t2; +drop table t1,t2,t3,t4,t5; +drop function bug27417; diff --git a/mysql-test/suite/binlog/include/mysqlbinlog_gtid_strict_mode.inc b/mysql-test/suite/binlog/include/mysqlbinlog_gtid_strict_mode.inc new file mode 100644 index 00000000..5150e906 --- /dev/null +++ b/mysql-test/suite/binlog/include/mysqlbinlog_gtid_strict_mode.inc @@ -0,0 +1,421 @@ +# +# This file runs test cases for using --gtid-strict-mode with mariadb-binlog to +# ensure warnings are properly displayed +# +# param $is_strict_mode boolean (0 for false, 1 for true) to enable or +# disable strict mode for GTID processing +# + +--let MYSQLD_DATADIR=`select @@datadir` +--let OUT_FILE=$MYSQLTEST_VARDIR/tmp/binlog.out + +if ($is_strict_mode == 0) +{ + --let BINLOG_STRICT_MODE_PARAM=--skip-gtid-strict-mode +} +if ($is_strict_mode == 1) +{ + --let BINLOG_STRICT_MODE_PARAM=--gtid-strict-mode +} +if ($is_verbose == 1) +{ + --let BINLOG_STRICT_MODE_PARAM=$BINLOG_STRICT_MODE_PARAM -vvv +} + +--let $log_error_ = $MYSQLTEST_VARDIR/tmp/out.err +--let SEARCH_FILE=$log_error_ + +--echo # +--echo # Test Case 1: +--echo # Sequential sequence numbers results in no errors or warnings +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t1 (a int); +INSERT INTO t1 values (1); +INSERT INTO t1 values (2); +INSERT INTO t1 values (3); +FLUSH LOGS; +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; +RESET MASTER; + +--echo # +--echo # Test Case 2: +--echo # A skipped sequence number results in no errors or warnings if all +--echo # numbers are monotonic (i.e. gaps in sequence number are allowed +--echo # provided they never decrease) +RESET MASTER; +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t1 (a int); +INSERT INTO t1 values (1); +INSERT INTO t1 values (2); +SET @@session.gtid_seq_no= 5; +INSERT INTO t1 values (3); +FLUSH LOGS; +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; +RESET MASTER; + +--echo # +--echo # Test Case 3: +--echo # A sequence number lower than the last processed value results in a +--echo # warning or error +CREATE TABLE t1 (a int); +RESET MASTER; +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +SET @@session.gtid_seq_no= 2; +INSERT INTO t1 values (2); +SET @@session.gtid_seq_no= 1; +INSERT INTO t1 values (1); +FLUSH LOGS; +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX: Found out of order GTID +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; +RESET MASTER; + +--echo # +--echo # Test Case 4: +--echo # Skipping a GTID and later receiving it results in a warning or error +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t1 (a int); +SET @@session.gtid_seq_no= 3; +INSERT INTO t1 values (2); +SET @@session.gtid_seq_no= 2; +INSERT INTO t1 values (3); +SET @@session.gtid_seq_no= 4; +INSERT INTO t1 values (4); +INSERT INTO t1 values (5); +FLUSH LOGS; +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX: Found out of order GTID +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; +RESET MASTER; + +--echo # +--echo # Test Case 5: +--echo # Repeat sequence numbers produce a warning +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t1 (a int); +SET @@session.gtid_seq_no= 2; +INSERT INTO t1 values (2); +SET @@session.gtid_seq_no= 2; +INSERT INTO t1 values (2); +SET @@session.gtid_seq_no= 3; +INSERT INTO t1 values (3); +INSERT INTO t1 values (4); +FLUSH LOGS; +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX: Found out of order GTID +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; +RESET MASTER; + +--echo # +--echo # Test Case 6: +--echo # Warnings from different domains are all displayed +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t1 (a int); +SET @@session.gtid_seq_no= 3; +INSERT INTO t1 values (2); +SET @@session.gtid_seq_no= 2; +INSERT INTO t1 values (3); +SET @@session.gtid_seq_no= 4; +INSERT INTO t1 values (4); +SET @@session.gtid_domain_id= 1; +SET @@session.server_id= 2; +CREATE TABLE t2 (a int); +SET @@session.gtid_seq_no= 3; +INSERT INTO t2 values (2); +SET @@session.gtid_seq_no= 2; +INSERT INTO t2 values (3); +SET @@session.gtid_seq_no= 4; +INSERT INTO t2 values (4); +FLUSH LOGS; +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX: Found out of order GTID +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; +DROP TABLE t2; +RESET MASTER; + +--echo # +--echo # Test Case 7: +--echo # A decreasing seq_no before a start-position is ignored +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +SET @@session.gtid_seq_no= 1; +CREATE TABLE t1 (a int); +SET @@session.gtid_seq_no= 3; +INSERT INTO t1 values (1); +SET @@session.gtid_seq_no= 2; +INSERT INTO t1 values (2); +--let $start_binlog_pos= query_get_value(SHOW MASTER STATUS,Position, 1) +SET @@session.gtid_seq_no= 4; +INSERT INTO t1 values (4); +INSERT INTO t1 values (3); +INSERT INTO t1 values (5); +FLUSH LOGS; + +--echo # GTID-based start-position +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-4 $BINLOG_STRICT_MODE_PARAM > log_error_ > OUT_FILE +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-4 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX +--source include/search_pattern_in_file.inc + +--echo # Position-based start-position +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 --start-position=start_binlog_pos $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 --start-position=$start_binlog_pos $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX +--source include/search_pattern_in_file.inc + +--remove_file $log_error_ +DROP TABLE t1; +RESET MASTER; + +--echo # +--echo # Test Case 8: +--echo # A decreasing seq_no inside of a --start/--stop position window is +--echo # displayed +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t1 (a int); +--let $start_binlog_pos= query_get_value(SHOW MASTER STATUS,Position, 1) +INSERT INTO t1 values (1); +INSERT INTO t1 values (2); +SET @@session.gtid_seq_no= 5; +INSERT INTO t1 values (4); +SET @@session.gtid_seq_no= 4; +INSERT INTO t1 values (3); +SET @@session.gtid_seq_no= 6; +INSERT INTO t1 values (5); +--let $stop_binlog_pos= query_get_value(SHOW MASTER STATUS,Position, 1) +FLUSH LOGS; + +--echo # GTID-based window +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-1 --stop-position=0-1-6 $BINLOG_STRICT_MODE_PARAM > log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-1 --stop-position=0-1-6 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX: Found out of order GTID +--source include/search_pattern_in_file.inc + +--echo # Position-based window +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 --start-position=start_binlog_pos --stop-position=stop_binlog_pos $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 --start-position=$start_binlog_pos --stop-position=$stop_binlog_pos $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX: Found out of order GTID +--source include/search_pattern_in_file.inc + +--remove_file $log_error_ +DROP TABLE t1; +RESET MASTER; + +--echo # +--echo # Test Case 9: +--echo # Error if --stop-position is not greater than or equal to +--echo # --start-position +--echo # +--echo # Note: Error is only displayed in strict mode, -vvv has no effect here +FLUSH LOGS; +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-2 --stop-position=0-1-1 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-2 --stop-position=0-1-1 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=ERROR: Queried GTID range is invalid in strict mode +--source include/search_pattern_in_file.inc + +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-2,1-2-1 --stop-position=0-1-1,1-2-2 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-2,1-2-1 --stop-position=0-1-1,1-2-2 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=ERROR: Queried GTID range is invalid in strict mode +--source include/search_pattern_in_file.inc +RESET MASTER; + +--echo # +--echo # Test Case 10: +--echo # Strict mode warnings should be independent of --offset option +--echo # specification +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +SET @@session.gtid_seq_no= 1; +CREATE TABLE t1 (a int); +SET @@session.gtid_seq_no= 3; +INSERT INTO t1 values (1); +SET @@session.gtid_seq_no= 2; +INSERT INTO t1 values (2); +SET @@session.gtid_seq_no= 4; +INSERT INTO t1 values (3); +INSERT INTO t1 values (4); +FLUSH LOGS; +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-1 --offset=8 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-1 --offset=8 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX: Found out of order GTID +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; +RESET MASTER; + +--echo # +--echo # Test Case 11: +--echo # Strict mode warnings should be independent of --start-timestamp +--echo # option specification +set @a=UNIX_TIMESTAMP("1970-01-21 15:32:22"); +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +SET @@session.gtid_seq_no= 1; +SET timestamp=@a; +CREATE TABLE t1 (a int); +SET @@session.gtid_seq_no= 3; +SET timestamp=@a+1; +INSERT INTO t1 values (1); +SET @@session.gtid_seq_no= 2; +SET timestamp=@a+2; +INSERT INTO t1 values (2); +SET @@session.gtid_seq_no= 4; +SET timestamp=@a+3; +INSERT INTO t1 values (3); +INSERT INTO t1 values (4); +FLUSH LOGS; +--echo # MYSQL_BINLOG MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-1 --start-datetime="1970-01-21 15:32:24" $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--error $is_strict_mode +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 --start-position=0-1-1 --start-datetime="1970-01-21 15:32:24" $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=$DEFAULT_ERROR_PREFIX: Found out of order GTID +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; +RESET MASTER; + +--echo # +--echo # Test Case 12: +--echo # Specifying multiple binary logs with a log-position start should +--echo # skip GTID state verification +RESET MASTER; +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t1 (a int); +INSERT INTO t1 values (1); +--let $b2_start_pos= query_get_value(SHOW MASTER STATUS,Position, 1) +SET @@session.gtid_domain_id= 1; +SET @@session.server_id= 2; +CREATE TABLE t2 (a int); +INSERT INTO t2 values (1); +FLUSH LOGS; +INSERT INTO t2 values (2); +FLUSH LOGS; +--let BINLOG_FILE1= query_get_value(SHOW BINARY LOGS, Log_name, 1) +# Skip file 2 as input +--let BINLOG_FILE2= query_get_value(SHOW BINARY LOGS, Log_name, 2) +--echo # MYSQL_BINLOG MYSQLD_DATADIR/BINLOG_FILE1 MYSQLD_DATADIR/BINLOG_FILE2 --start-position=b2_start_pos $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG_FILE1 $MYSQLD_DATADIR/$BINLOG_FILE2 --start-position=$b2_start_pos $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +DROP TABLE t1; +DROP TABLE t2; + + +--echo # +--echo # Test Case 13: +--echo # If multiple binary logs should be specified but a middle log is +--echo # missing, we should detect that and warn when using -vvv + +RESET MASTER; +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t1 (a int); +FLUSH LOGS; +INSERT INTO t1 values (1); +--let $b2_start_pos= query_get_value(SHOW MASTER STATUS,Position, 1) +SET @@session.gtid_domain_id= 1; +SET @@session.server_id= 2; +CREATE TABLE t2 (a int); +FLUSH LOGS; +SET @@session.gtid_domain_id= 2; +SET @@session.server_id= 3; +CREATE TABLE t3 (a int); +FLUSH LOGS; + +--echo # +--echo # GLLE from each log for state reference +--let $binlog_file= query_get_value(SHOW BINARY LOGS, Log_name, 1) +--source include/show_gtid_list.inc +--let $binlog_file= +--let $binlog_file= query_get_value(SHOW BINARY LOGS, Log_name, 2) +--source include/show_gtid_list.inc +--let $binlog_file= +--let $binlog_file= query_get_value(SHOW BINARY LOGS, Log_name, 3) +--source include/show_gtid_list.inc +--let $binlog_file= + +--let BINLOG_FILE1= query_get_value(SHOW BINARY LOGS, Log_name, 1) +# Skip file 2 as input +--let BINLOG_FILE3= query_get_value(SHOW BINARY LOGS, Log_name, 3) +--echo # MYSQL_BINLOG MYSQLD_DATADIR/BINLOG_FILE1 MYSQLD_DATADIR/BINLOG_FILE3 $BINLOG_STRICT_MODE_PARAM 2> log_error_ > OUT_FILE +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG_FILE1 $MYSQLD_DATADIR/$BINLOG_FILE3 $BINLOG_STRICT_MODE_PARAM 2> $log_error_ > $OUT_FILE +--echo # We should have two warnings about missing data from domains 0 and 1 if +--echo # -vvv is specified +--let SEARCH_FILE=$log_error_ +--let SEARCH_PATTERN=WARNING: Binary logs are missing data for domain 0[^\n]+the last seen event was +--source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=WARNING: Binary logs are missing data for domain 1[^\n]+neither the starting GTID position list nor any processed events +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +RESET MASTER; + +--echo # +--echo # Test Case 14: +--echo # If a --stop-position GTID occurs before the first specified binlog's +--echo # GLLE, error +RESET MASTER; +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t1 (a int); +INSERT INTO t1 values (1); +INSERT INTO t1 values (2); +FLUSH LOGS; +INSERT INTO t1 values (3); +FLUSH LOGS; + +--echo # +--echo # GLLE from each log for state reference +--let $binlog_file= query_get_value(SHOW BINARY LOGS, Log_name, 1) +--source include/show_gtid_list.inc +--let $binlog_file= +--let $binlog_file= query_get_value(SHOW BINARY LOGS, Log_name, 2) +--source include/show_gtid_list.inc +--let $binlog_file= +--echo # MYSQL_BINLOG MYSQLD_DATADIR/BINLOG_FILE2 $BINLOG_STRICT_MODE_PARAM --stop-position=0-1-2 2> log_error_ > OUT_FILE +--error 1 +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG_FILE2 $BINLOG_STRICT_MODE_PARAM --stop-position=0-1-2 2> $log_error_ > $OUT_FILE +--let SEARCH_PATTERN=ERROR: --stop-position GTID +--source include/search_pattern_in_file.inc +--remove_file $log_error_ +DROP TABLE t1; + +--remove_file $OUT_FILE diff --git a/mysql-test/suite/binlog/include/mysqlbinlog_gtid_window_test_cases.inc b/mysql-test/suite/binlog/include/mysqlbinlog_gtid_window_test_cases.inc new file mode 100644 index 00000000..2830f3f6 --- /dev/null +++ b/mysql-test/suite/binlog/include/mysqlbinlog_gtid_window_test_cases.inc @@ -0,0 +1,606 @@ +# +# This file runs test cases for providing GTIDs to --start-position and +# --stop-position arguments in mariadb-binlog +# +# param $is_remote boolean (0 for false, 1 for true) to perform a local file +# or remote host analysis +# + +--let MYSQLD_DATADIR=`select @@datadir` +--let data_inconsistent_err= "table data is inconsistent after replaying binlog using GTID start/stop positions"; +--let $tmp_out_ = $MYSQLTEST_VARDIR/tmp/null.log + +## Initialize test data +# +set @a=UNIX_TIMESTAMP("1970-01-21 15:32:22"); +SET timestamp=@a; +RESET MASTER; +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +--let $empty_binlog_pos= query_get_value(SHOW MASTER STATUS,Position, 1) +CREATE TABLE t1 (a int); +SET timestamp=@a+1; +INSERT INTO t1 values (1), (2); +--let test2_t1_mid_checksum= `CHECKSUM TABLE t1` + +SET @@session.gtid_domain_id= 1; +SET @@session.server_id= 2; +SET timestamp=@a+2; +CREATE TABLE t2 (a int); +SET timestamp=@a+3; +INSERT INTO t2 values (1); +--let t2_mid_checksum= `CHECKSUM TABLE t2` + +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +SET timestamp=@a+4; +INSERT INTO t1 values (3), (4); +--let t1_final_checksum_pos= query_get_value(SHOW MASTER STATUS,Position, 1) +--let t1_final_checksum= `CHECKSUM TABLE t1` + +SET @@session.gtid_domain_id= 1; +SET @@session.server_id= 2; +INSERT INTO t2 values (2); +--let test4_t2_good_checksum= `CHECKSUM TABLE t2` + +SET @@session.server_id= 3; +INSERT INTO t2 values (3); +--let test3_t2_good_checksum= `CHECKSUM TABLE t2` + +SET @@session.server_id= 2; +INSERT INTO t2 values (4); +--let t2_final_checksum= `CHECKSUM TABLE t2` + +FLUSH LOGS; + +# Multiple binlog file case, used by test 18 +SET @@session.gtid_domain_id= 0; +SET @@session.server_id= 1; +CREATE TABLE t3 (a int); +INSERT INTO t3 VALUES (1); +--let t3_final_checksum= `CHECKSUM TABLE t3` + +FLUSH LOGS; + +--echo # Gtid list event of the 2nd binlog file whose content is +--echo # matched against --start-position in the following tests: +--let $binlog_file= query_get_value(SHOW BINARY LOGS, Log_name, 2) +--source include/show_gtid_list.inc +--let $binlog_file= + +--let BINLOG_FILE= query_get_value(SHOW BINARY LOGS, Log_name, 1) +--let BINLOG_FILE2= query_get_value(SHOW BINARY LOGS, Log_name, 2) + +if ($is_remote == 0) +{ + --let BINLOG_FILE_PARAM=$MYSQLD_DATADIR/$BINLOG_FILE.orig + --let BINLOG_FILE_PARAM2=$MYSQLD_DATADIR/$BINLOG_FILE2.orig +} +if ($is_remote == 1) +{ + --let BINLOG_FILE_PARAM= --read-from-remote-server $BINLOG_FILE + --let BINLOG_FILE_PARAM2= --read-from-remote-server $BINLOG_FILE2 +} + +--copy_file $MYSQLD_DATADIR/$BINLOG_FILE $MYSQLD_DATADIR/$BINLOG_FILE.orig +--copy_file $MYSQLD_DATADIR/$BINLOG_FILE2 $MYSQLD_DATADIR/$BINLOG_FILE2.orig + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; + + +--echo # +--echo # Test Case 1: +--echo # The end of the binlog file resets the server and domain id of the +--echo # session + +# As gtid_domain_id and server_id should not change after reading binlog in GTID +# mode, change variables to otherwise-unused values to ensure they remain +--let $reset_gtid_domain_id = `select @@session.gtid_domain_id` +--let $reset_server_id = `select @@session.server_id` +SET @@session.gtid_domain_id= 10; +SET @@session.server_id= 20; + +# Replay the binlog +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=0-1-2 | $MYSQL +--let $test_gtid_domain_id = `select @@session.gtid_domain_id` + +# Ensure variables haven't changed +--let $assert_text = session gtid_domain_id should not change when reading binlog in GTID mode +--let $assert_cond = @@session.gtid_domain_id = 10 +--source include/assert.inc +--let $assert_text = session server_id should not change when reading binlog in GTID mode +--let $assert_cond = @@session.server_id = 20 +--source include/assert.inc + +# Reset back to previous state +--eval SET @@session.gtid_domain_id= $reset_gtid_domain_id +--eval SET @@session.server_id= $reset_server_id +DROP TABLE t1; + + +--echo # +--echo # Test Case 2: +--echo # Single GTID range specified +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=0-1-2 | $MYSQL + +if ($test2_t1_mid_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +DROP TABLE t1; + + +--echo # +--echo # Test Case 3: +--echo # Single GTID range with different server_ids +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=1-2-0 --stop-position=1-3-4 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=1-2-0 --stop-position=1-3-4 | $MYSQL + +if ($test3_t2_good_checksum != `CHECKSUM TABLE t2`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't1'`) +{ + die "t1 should not exist as binlog replay should exclude domain 0 from stop position"; +} +DROP TABLE t2; + + +--echo # +--echo # Test Case 4: +--echo # Multiple GTID ranges specified +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-0,1-2-0 --stop-position=0-1-3,1-2-3 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-0,1-2-0 --stop-position=0-1-3,1-2-3 | $MYSQL + +# Reuse checksum spot from test 4 +if ($t1_final_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if ($test4_t2_good_checksum != `CHECKSUM TABLE t2`) +{ + die $data_inconsistent_err; +} +DROP TABLE t1; +DROP TABLE t2; + + +--echo # +--echo # Test Case 5: +--echo # Multiple GTID ranges specified where the domain ids are listed in +--echo # different orders between start/stop position +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --stop-position=0-1-3,1-2-3 --start-position=1-2-0,0-1-0 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --stop-position=0-1-3,1-2-3 --start-position=1-2-0,0-1-0 | $MYSQL + +# Reuse checksum spot from test 4 +if ($t1_final_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if ($test4_t2_good_checksum != `CHECKSUM TABLE t2`) +{ + die $data_inconsistent_err; +} +DROP TABLE t1; +DROP TABLE t2; + + +--echo # +--echo # Test Case 6: +--echo # Only start position specified +CREATE TABLE t1 (a int); +INSERT INTO t1 values (3), (4); +--let test6_t1_mid_checksum= `CHECKSUM TABLE t1` +DROP TABLE t1; +CREATE TABLE t1 (a int); +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-2 | $MYSQL +if ($test6_t1_mid_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if ($t2_final_checksum != `CHECKSUM TABLE t2`) +{ + die $data_inconsistent_err; +} +DROP TABLE t1; +DROP TABLE t2; + + +--echo # +--echo # Test Case 7: +--echo # Only stop position specified +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --stop-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --stop-position=0-1-2 | $MYSQL + +# Reuse checksum spot from test 2 +if ($test2_t1_mid_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +DROP TABLE t1; + + +--echo # +--echo # Test Case 8: +--echo # Seq_no=0 in --start-position includes all events for a domain +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-0,1-2-0 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-0,1-2-0 | $MYSQL +if ($t1_final_checksum != `CHECKSUM TABLE t1`) +{ + die "t1 data should be complete as binlog replay should include domain 0 entirely in results"; +} +if ($t2_final_checksum != `CHECKSUM TABLE t2`) +{ + die "t2 data should be complete as binlog replay should include domain 1 entirely in results"; +} +DROP TABLE t1; +DROP TABLE t2; + +--echo # +--echo # Test Case 9: +--echo # Seq_no=0 in --stop-position excludes all events for a domain +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --stop-position=0-1-0,1-2-0 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --stop-position=0-1-0,1-2-0 | $MYSQL +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't1'`) +{ + die "t1 should not exist as binlog replay should exclude domain 0 from results"; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should exclude domain 1 from results"; +} + +--echo # +--echo # Test Case 10: +--echo # Output stops for all domain ids when all --stop-position GTID values +--echo # have been hit. +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --stop-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --stop-position=0-1-2 | $MYSQL +if ($test2_t1_mid_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should exclude domain 1 from results"; +} +DROP TABLE t1; + +--echo # +--echo # Test Case 11: +--echo # All GTID events from other domains are printed until the +--echo # --stop-position values are hit +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --stop-position=1-3-4 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --stop-position=1-3-4 | $MYSQL +if ($test3_t2_good_checksum != `CHECKSUM TABLE t2`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't1'`) +{ + die "t1 should not exist as binlog replay should exclude domain 0 from stop position"; +} +DROP TABLE t2; + +--echo # +--echo # Test Case 12: +--echo # Scalar and GTID values can be used together for stop or start +--echo # position +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=empty_binlog_pos --stop-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=$empty_binlog_pos --stop-position=0-1-2 | $MYSQL +if ($test2_t1_mid_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +DROP TABLE t1; +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=t1_final_checksum_pos | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=$t1_final_checksum_pos | $MYSQL +if ($t1_final_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if ($t2_mid_checksum != `CHECKSUM TABLE t2`) +{ + die $data_inconsistent_err; +} +DROP TABLE t1; +DROP TABLE t2; + +--echo # +--echo # Test Case 13: +--echo # If the start position is delayed within the binlog, events occurring +--echo # before that position are ignored +CREATE TABLE t1 (a int); +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-1 --stop-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-1 --stop-position=0-1-2 | $MYSQL +if ($test2_t1_mid_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should exclude domain 1 from results"; +} +DROP TABLE t1; + +--echo # +--echo # Test Case 14: +--echo # If start position is repeated, the last specification overrides all +--echo # previous ones +CREATE TABLE t1 (a int); +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-0 --start-position=0-1-1 --stop-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-0 --start-position=0-1-1 --stop-position=0-1-2 | $MYSQL +if ($test2_t1_mid_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should exclude domain 1 from results"; +} +DROP TABLE t1; + +--echo # +--echo # Test Case 15: +--echo # If stop position is repeated, the last specification overrides all +--echo # previous ones +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=0-1-1 --stop-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=0-1-1 --stop-position=0-1-2 | $MYSQL +if ($test2_t1_mid_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should exclude domain 1 from results"; +} +DROP TABLE t1; + +--echo # +--echo # Test Case 16: +--echo # Start position with --offset=<n> skips n events after the first +--echo # GTID is found + +# t1 needs to be specified because its creation should be skipped from +# --offset specification +CREATE TABLE t1 (a int); + +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=0-1-2 --offset=5 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=0-1-2 --offset=5 | $MYSQL +if ($test2_t1_mid_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should exclude events after GTID 0-1-2"; +} +DROP TABLE t1; + +--echo # +--echo # Test Case 17: +--echo # Start position with --start-datetime=<T> where T occurs after the +--echo # specified GTID results in no events before T +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=1-2-2 --start-datetime="1970-01-21 15:32:24" | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-0 --stop-position=1-2-2 --start-datetime="1970-01-21 15:32:24" | $MYSQL +if ($t2_mid_checksum != `CHECKSUM TABLE t2`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't1'`) +{ + die "t1 should not exist as binlog replay should exclude events before the given start-datetime"; +} +DROP TABLE t2; + +--echo # +--echo # Test Case 18: +--echo # If --stop-position is specified, domains which are not present +--echo # in its list should be excluded from output +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --stop-position=1-3-4 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --stop-position=1-3-4 | $MYSQL + +if ($test3_t2_good_checksum != `CHECKSUM TABLE t2`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't1'`) +{ + die "t1 should not exist as binlog replay should exclude domain 0 from stop position"; +} +DROP TABLE t2; + +--echo # +--echo # Test Case 19: +--echo # If the start and stop GTIDs in any domain are equal, the domain +--echo # should not have any output +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=0-1-2 --stop-position=0-1-2 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=0-1-2 --stop-position=0-1-2 | $MYSQL + +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't1'`) +{ + die "t1 should not exist as binlog replay should exclude domain 0 from stop position"; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should exclude domain 1 from stop position"; +} + +--echo # +--echo # Test Case 20: +--echo # If --start-position and --stop-position have different domain ids, +--echo # only events from GTIDs in the --stop-position list are output +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-position=1-2-2 --stop-position=0-1-3 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-position=1-2-2 --stop-position=0-1-3 | $MYSQL +if ($t1_final_checksum != `CHECKSUM TABLE t1`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should exclude domain 1 from stop position"; +} +DROP TABLE t1; + +--echo # +--echo # Test Case 21: +--echo # Successive binary logs (e.g. logs with previous logs that have been +--echo # purged) will write events when the --start-position matches their +--echo # Gtid_list_log_event state +if ($is_remote == 1) +{ + --echo # + --echo # Reset server state + RESET MASTER; + --exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG_FILE.orig | $MYSQL + FLUSH LOGS; + --exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG_FILE2.orig | $MYSQL + FLUSH LOGS; + --eval PURGE BINARY LOGS TO "$BINLOG_FILE2" + DROP TABLE t1; + DROP TABLE t2; + DROP TABLE t3; +} +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM2 --start-position=0-1-3,1-2-5 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM2 --start-position=0-1-3,1-2-5 | $MYSQL +if ($t3_final_checksum != `CHECKSUM TABLE t3`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't1'`) +{ + die "t1 should not exist as binlog replay should not have any events from unspecified binlog file"; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should not have any events from unspecified binlog file"; +} +DROP TABLE t3; + +--echo # +--echo # Test Case 22: +--echo # Successive binary logs can be called with --stop-position and +--echo # without --start-position +if ($is_remote == 1) +{ + --echo # + --echo # Reset server state + RESET MASTER; + --exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG_FILE.orig | $MYSQL + FLUSH LOGS; + --exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG_FILE2.orig | $MYSQL + FLUSH LOGS; + --eval PURGE BINARY LOGS TO "$BINLOG_FILE2" + DROP TABLE t1; + DROP TABLE t2; + DROP TABLE t3; +} +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM2 --stop-position=0-1-4 | MYSQL +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM2 --stop-position=0-1-4 | $MYSQL +if (`SELECT COUNT(*) FROM test.t3`) +{ + die $data_inconsistent_err; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't1'`) +{ + die "t1 should not exist as binlog replay should not have any events from unspecified binlog file"; +} +if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 't2'`) +{ + die "t2 should not exist as binlog replay should not have any events from unspecified binlog file"; +} +DROP TABLE t3; + + +if ($is_remote == 1) +{ + --echo # + --echo # Remote-only setup for error cases + RESET MASTER; + --exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG_FILE.orig | $MYSQL + FLUSH LOGS; + --exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG_FILE2.orig | $MYSQL + FLUSH LOGS; + --eval PURGE BINARY LOGS TO "$BINLOG_FILE2" +} + +--echo # At the following error cases analysis +--echo # note incompatible --start-position with the value of +--echo # Gtid list event of the 2nd binlog file printed above. + +--let err_out_= $MYSQLTEST_VARDIR/tmp/err.out +--let SEARCH_FILE=$err_out_ + +--echo # +--echo # Error Case 1: +--echo # A GTID --start-position that does not mention all domains that make +--echo # up the binary log state should error +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM2 --start-position=0-1-3 > tmp_out_ 2> err_out_ +--error 1 +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM2 --start-position=0-1-3 > $tmp_out_ 2> $err_out_ +if ($is_remote == 1) +{ + --let SEARCH_PATTERN=ERROR: Got error reading packet from server +} +if ($is_remote == 0) +{ + --let SEARCH_PATTERN=ERROR: Starting GTID position list does not specify an initial value +} +--source include/search_pattern_in_file.inc +--remove_file $err_out_ + +--echo # +--echo # Error Case 2: +--echo # A GTID --start-position with any sequence numbers which occur before +--echo # the binary log state should result in error +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM2 --start-position=0-1-2,1-2-5 > tmp_out_ 2> err_out_ +--error 1 +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM2 --start-position=0-1-2,1-2-5 > $tmp_out_ 2> $err_out_ +if ($is_remote == 1) +{ + --let SEARCH_PATTERN=ERROR: Got error reading packet from server +} +if ($is_remote == 0) +{ + --let SEARCH_PATTERN=ERROR: Binary logs are missing data for domain 0 +} +--source include/search_pattern_in_file.inc +--remove_file $err_out_ + +--echo # +--echo # Error Case 3: +--echo # A GTID --start-position with any sequence numbers that are not +--echo # eventually processed results in error +--echo # MYSQL_BINLOG BINLOG_FILE_PARAM2 --start-position=0-1-8,1-2-6 > tmp_out_ 2> err_out_ +--error 1 +--exec $MYSQL_BINLOG $BINLOG_FILE_PARAM2 --start-position=0-1-8,1-2-6 > $tmp_out_ 2> $err_out_ +if ($is_remote == 1) +{ + --let SEARCH_PATTERN=ERROR: Got error reading packet from server +} +if ($is_remote == 0) +{ + --let SEARCH_PATTERN=ERROR: Binary logs never reached expected GTID state +} +--source include/search_pattern_in_file.inc +--remove_file $err_out_ + +if ($is_remote == 1) +{ + --echo # + --echo # Remote-only cleanup from error cases + DROP TABLE t1; + DROP TABLE t2; + DROP TABLE t3; + RESET MASTER; +} + +--remove_file $MYSQLD_DATADIR/$BINLOG_FILE.orig +--remove_file $MYSQLD_DATADIR/$BINLOG_FILE2.orig +--remove_file $tmp_out_ diff --git a/mysql-test/suite/binlog/include/mysqlbinlog_row_engine.inc b/mysql-test/suite/binlog/include/mysqlbinlog_row_engine.inc new file mode 100644 index 00000000..e0987b97 --- /dev/null +++ b/mysql-test/suite/binlog/include/mysqlbinlog_row_engine.inc @@ -0,0 +1,1948 @@ +# mysqlbinlog_row.test +# +# Show that mysqlbinlog displays human readable comments to +# row-based log events. +# +# Procedure: +# Create a table that represents all-known types in 5.1. +# Write rows that contain the mins, maxes, and NULL for each type. +# Write a random or "problematic" value (i.e. one that might require +# escaping if it's represented as a string-y type) for each type. +# Update each of these rows. +# Delete each of these rows. +# Inspect the binlog. +# +# Bug#31455 - mysqlbinlog don't print user readable info about RBR events +# + +--source include/have_log_bin.inc +set sql_mode=""; + +SET NAMES 'utf8'; +#SHOW VARIABLES LIKE 'character_set%'; + + +--echo # +--echo # Preparatory cleanup. +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1, t2, t3; +--enable_warnings + +--echo # +--echo # We need a fixed timestamp to avoid varying results. +--echo # +SET timestamp=1000000000; + +--echo # +--echo # =================================================== +--echo # Test #1 - Insert/update/delete with all data types. +--echo # =================================================== +--echo # +--echo # Delete all existing binary logs. +--echo # +RESET MASTER; + +--echo # +--echo # Create a test table with all data types. +--echo # +eval CREATE TABLE t1 ( + c01 BIT, + c02 BIT(64), + c03 TINYINT, + c04 TINYINT UNSIGNED, + c05 TINYINT ZEROFILL, + c06 BOOL, + c07 SMALLINT, + c08 SMALLINT UNSIGNED, + c09 SMALLINT ZEROFILL, + c10 MEDIUMINT, + c11 MEDIUMINT UNSIGNED, + c12 MEDIUMINT ZEROFILL, + c13 INT, + c14 INT UNSIGNED, + c15 INT ZEROFILL, + c16 BIGINT, + c17 BIGINT UNSIGNED, + c18 BIGINT ZEROFILL, + c19 FLOAT, + c20 FLOAT UNSIGNED, + c21 FLOAT ZEROFILL, + c22 DOUBLE, + c23 DOUBLE UNSIGNED, + c24 DOUBLE ZEROFILL, + c25 DECIMAL, + c26 DECIMAL UNSIGNED, + c27 DECIMAL ZEROFILL, + # + c28 DATE, + c29 DATETIME, + c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + c31 TIME, + c32 YEAR, + # + c33 CHAR, + c34 CHAR(0), + c35 CHAR(1), + c36 CHAR(255), + c37 NATIONAL CHAR, + c38 NATIONAL CHAR(0), + c39 NATIONAL CHAR(1), + c40 NATIONAL CHAR(255), + c41 CHAR CHARACTER SET UCS2, + c42 CHAR(0) CHARACTER SET UCS2, + c43 CHAR(1) CHARACTER SET UCS2, + c44 CHAR(255) CHARACTER SET UCS2, + # + c45 VARCHAR(0), + c46 VARCHAR(1), + c47 VARCHAR(255), + c48 VARCHAR(261), + c49 NATIONAL VARCHAR(0), + c50 NATIONAL VARCHAR(1), + c51 NATIONAL VARCHAR(255), + c52 NATIONAL VARCHAR(261), + c53 VARCHAR(0) CHARACTER SET UCS2, + c54 VARCHAR(1) CHARACTER SET UCS2, + c55 VARCHAR(255) CHARACTER SET UCS2, + c56 VARCHAR(261) CHARACTER SET UCS2, + # + c57 BINARY, + c58 BINARY(0), + c59 BINARY(1), + c60 BINARY(255), + # + c61 VARBINARY(0), + c62 VARBINARY(1), + c63 VARBINARY(255), + c64 VARBINARY(261), + # + c65 TINYBLOB, + c66 TINYTEXT, + c67 TINYTEXT CHARACTER SET UCS2, + c68 BLOB, + c69 TEXT, + c70 TEXT CHARACTER SET UCS2, + c71 MEDIUMBLOB, + c72 MEDIUMTEXT, + c73 MEDIUMTEXT CHARACTER SET UCS2, + c74 LONGBLOB, + c75 LONGTEXT, + c76 LONGTEXT CHARACTER SET UCS2, + # + c77 ENUM('a','b','c'), + c78 SET('a','b','c'), + # + crn INT -- row number + ) ENGINE=$engine_type DEFAULT CHARSET latin1; + +--echo # +--echo # Insert minimum values. +--echo # +INSERT INTO t1 VALUES ( + b'0', -- c01 + b'0000000000000000000000000000000000000000000000000000000000000000', -- c02 + -128, -- c03 + 0, -- c04 + 000, -- c05 + false, -- c06 + -32768, -- c07 + 0, -- c08 + 00000, -- c09 + -8388608, -- c10 + 0, -- c11 + 00000000, -- c12 + -2147483648, -- c13 + 0, -- c14 + 0000000000, -- c15 + -9223372036854775808, -- c16 + 0, -- c17 + 00000000000000000000, -- c18 + -3.402823466E+38, -- c19 + 1.175494351E-38, -- c20 + 000000000000, -- c21 + -1.7976931348623E+308, -- c22 three digits cut for ps-protocol + 2.2250738585072E-308, -- c23 three digits cut for ps-protocol + 0000000000000000000000, -- c24 + -9999999999, -- c25 + 0, -- c26 + 0000000000, -- c27 + # + '1000-01-01', -- c28 + '1000-01-01 00:00:00', -- c29 + '1970-01-02 00:00:01', -- c30 one day later due to timezone issues + '-838:59:59', -- c31 + '1901', -- c32 + # + '', -- c33 + '', -- c34 + '', -- c35 + '', -- c36 + '', -- c37 + '', -- c38 + '', -- c39 + '', -- c40 + '', -- c41 + '', -- c42 + '', -- c43 + '', -- c44 + # + '', -- c45 + '', -- c46 + '', -- c47 + '', -- c48 + '', -- c49 + '', -- c50 + '', -- c51 + '', -- c52 + '', -- c53 + '', -- c54 + '', -- c55 + '', -- c56 + # + '', -- c57 + '', -- c58 + '', -- c59 + '', -- c60 + # + '', -- c61 + '', -- c62 + '', -- c63 + '', -- c64 + # + '', -- c65 + '', -- c66 + '', -- c67 + '', -- c68 + '', -- c69 + '', -- c70 + '', -- c71 + '', -- c72 + '', -- c73 + '', -- c74 + '', -- c75 + '', -- c76 + # + 'a', -- c77 + '', -- c78 + # + 1 -- crn -- row number + ); + +--echo # +--echo # Insert maximum values. +--echo # +INSERT INTO t1 VALUES ( + b'1', -- c01 + b'1111111111111111111111111111111111111111111111111111111111111111', -- c02 + 127, -- c03 + 255, -- c04 + 255, -- c05 + true, -- c06 + 32767, -- c07 + 65535, -- c08 + 65535, -- c09 + 8388607, -- c10 + 16777215, -- c11 + 16777215, -- c12 + 2147483647, -- c13 + 4294967295, -- c14 + 4294967295, -- c15 + 9223372036854775807, -- c16 + 18446744073709551615, -- c17 + 18446744073709551615, -- c18 + 3.402823466E+38, -- c19 + 3.402823466E+38, -- c20 + 3.402823466E+38, -- c21 + 1.7976931348623E+308, -- c22 three digits cut for ps-protocol + 1.7976931348623E+308, -- c23 three digits cut for ps-protocol + 1.7976931348623E+308, -- c24 three digits cut for ps-protocol + 9999999999, -- c25 + 9999999999, -- c26 + 9999999999, -- c27 + # + '9999-12-31', -- c28 + '9999-12-31 23:59:59', -- c29 + '2038-01-08 03:14:07', -- c30 one day earlier due to timezone issues + '838:59:59', -- c31 + '2155', -- c32 + # + x'ff', -- c33 + '', -- c34 + x'ff', -- c35 + REPEAT(x'ff',255), -- c36 + _utf8 x'efbfbf', -- c37 + '', -- c38 + _utf8 x'efbfbf', -- c39 + REPEAT(_utf8 x'efbfbf',255), -- c40 + _ucs2 x'ffff', -- c41 + '', -- c42 + _ucs2 x'ffff', -- c43 + REPEAT(_ucs2 x'ffff',255), -- c44 + # + '', -- c45 + x'ff', -- c46 + REPEAT(x'ff',255), -- c47 + REPEAT(x'ff',261), -- c48 + '', -- c49 + _utf8 x'efbfbf', -- c50 + REPEAT(_utf8 x'efbfbf',255), -- c51 + REPEAT(_utf8 x'efbfbf',261), -- c52 + '', -- c53 + _ucs2 x'ffff', -- c54 + REPEAT(_ucs2 x'ffff',255), -- c55 + REPEAT(_ucs2 x'ffff',261), -- c56 + # + x'ff', -- c57 + '', -- c58 + x'ff', -- c59 + REPEAT(x'ff',255), -- c60 + # + '', -- c61 + x'ff', -- c62 + REPEAT(x'ff',255), -- c63 + REPEAT(x'ff',261), -- c64 + # + 'tinyblob', -- c65 not using maximum value here + 'tinytext', -- c66 not using maximum value here + 'tinytext-ucs2', -- c67 not using maximum value here + 'blob', -- c68 not using maximum value here + 'text', -- c69 not using maximum value here + 'text-ucs2', -- c70 not using maximum value here + 'mediumblob', -- c71 not using maximum value here + 'mediumtext', -- c72 not using maximum value here + 'mediumtext-ucs2', -- c73 not using maximum value here + 'longblob', -- c74 not using maximum value here + 'longtext', -- c75 not using maximum value here + 'longtext-ucs2', -- c76 not using maximum value here + # + 'c', -- c77 + 'a,b,c', -- c78 + # + 2 -- crn -- row number + ); + +--echo # +--echo # Insert a row with NULL values and one with arbitrary values. +--echo # +INSERT INTO t1 VALUES ( + NULL, -- c01 + NULL, -- c02 + NULL, -- c03 + NULL, -- c04 + NULL, -- c05 + NULL, -- c06 + NULL, -- c07 + NULL, -- c08 + NULL, -- c09 + NULL, -- c10 + NULL, -- c11 + NULL, -- c12 + NULL, -- c13 + NULL, -- c14 + NULL, -- c15 + NULL, -- c16 + NULL, -- c17 + NULL, -- c18 + NULL, -- c19 + NULL, -- c20 + NULL, -- c21 + NULL, -- c22 + NULL, -- c23 + NULL, -- c24 + NULL, -- c25 + NULL, -- c26 + NULL, -- c27 + # + NULL, -- c28 + NULL, -- c29 + NULL, -- c30 + NULL, -- c31 + NULL, -- c32 + # + NULL, -- c33 + NULL, -- c34 + NULL, -- c35 + NULL, -- c36 + NULL, -- c37 + NULL, -- c38 + NULL, -- c39 + NULL, -- c40 + NULL, -- c41 + NULL, -- c42 + NULL, -- c43 + NULL, -- c44 + # + NULL, -- c45 + NULL, -- c46 + NULL, -- c47 + NULL, -- c48 + NULL, -- c49 + NULL, -- c50 + NULL, -- c51 + NULL, -- c52 + NULL, -- c53 + NULL, -- c54 + NULL, -- c55 + NULL, -- c56 + # + NULL, -- c57 + NULL, -- c58 + NULL, -- c59 + NULL, -- c60 + # + NULL, -- c61 + NULL, -- c62 + NULL, -- c63 + NULL, -- c64 + # + NULL, -- c65 + NULL, -- c66 + NULL, -- c67 + NULL, -- c68 + NULL, -- c69 + NULL, -- c70 + NULL, -- c71 + NULL, -- c72 + NULL, -- c73 + NULL, -- c74 + NULL, -- c75 + NULL, -- c76 + # + NULL, -- c77 + NULL, -- c78 + # + 3 -- crn -- row number + ), ( + b'1', -- c01 + b'1111111111111111111111111111111111111111111111111111111111111111', -- c02 + 127, -- c03 + 0, -- c04 + 001, -- c05 + true, -- c06 + 32767, -- c07 + 0, -- c08 + 00001, -- c09 + 8388607, -- c10 + 0, -- c11 + 00000001, -- c12 + 2147483647, -- c13 + 0, -- c14 + 0000000001, -- c15 + 9223372036854775807, -- c16 + 0, -- c17 + 00000000000000000001, -- c18 + -1.175494351E-38, -- c19 + 1.175494351E-38, -- c20 + 000000000000001, -- c21 + -2.2250738585072E-308, -- c22 + 2.2250738585072E-308, -- c23 + 00000000000000000000001, -- c24 + -9999999999, -- c25 + 9999999999, -- c26 + 0000000001, -- c27 + # + '2008-08-04', -- c28 + '2008-08-04 16:18:06', -- c29 + '2008-08-04 16:18:24', -- c30 + '16:18:47', -- c31 + '2008', -- c32 + # + 'a', -- c33 + '', -- c34 + 'e', -- c35 + REPEAT('i',255), -- c36 + _utf8 x'c3a4', -- c37 + '', -- c38 + _utf8 x'c3b6', -- c39 + REPEAT(_utf8 x'c3bc',255), -- c40 + _ucs2 x'00e4', -- c41 + '', -- c42 + _ucs2 x'00f6', -- c43 + REPEAT(_ucs2 x'00fc',255), -- c44 + # + '', -- c45 + 'a', -- c46 + REPEAT('e',255), -- c47 + REPEAT('i',261), -- c48 + '', -- c49 + _utf8 x'c3a4', -- c50 + REPEAT(_utf8 x'c3b6',255), -- c51 + REPEAT(_utf8 x'c3bc',261), -- c52 + '', -- c53 + _ucs2 x'00e4', -- c54 + REPEAT(_ucs2 x'00f6',255), -- c55 + REPEAT(_ucs2 x'00fc',261), -- c56 + # + '0', -- c57 + '', -- c58 + '1', -- c59 + REPEAT('1',255), -- c60 + # + '', -- c61 + 'b', -- c62 + REPEAT('c',255), -- c63 + REPEAT('\'',261), -- c64 + # + 'tinyblob', -- c65 + 'tinytext', -- c66 + 'tinytext-ucs2', -- c67 + 'blob', -- c68 + 'text', -- c69 + 'text-ucs2', -- c70 + 'mediumblob', -- c71 + 'mediumtext', -- c72 + 'mediumtext-ucs2', -- c73 + 'longblob', -- c74 + 'longtext', -- c75 + 'longtext-ucs2', -- c76 + # + 'b', -- c77 + 'b,c', -- c78 + # + 4 -- crn -- row number + ); + +--echo # +--echo # Show what we have in the table. +--echo # Do not display bit type output. It's binary and confuses diff. +--echo # Also BINARY with nul-bytes should be avoided. +--echo # +--replace_column 1 # 2 # 57 # 58 # 59 # 60 # +query_vertical SELECT * FROM t1; + +--echo # +--echo # NOTE: For matching FLOAT and DOUBLE values in WHERE conditions, +--echo # don't use exact match, but < or > and tweak the numbers a bit. +--echo # +--echo # Show how much rows are affected by each statement. +--echo # +--enable_info + +--echo # +--echo # Update min values to max values. +--echo # +UPDATE t1 SET + c01 = b'1', + c02 = b'1111111111111111111111111111111111111111111111111111111111111111', + c03 = 127, + c04 = 255, + c05 = 255, + c06 = true, + c07 = 32767, + c08 = 65535, + c09 = 65535, + c10 = 8388607, + c11 = 16777215, + c12 = 16777215, + c13 = 2147483647, + c14 = 4294967295, + c15 = 4294967295, + c16 = 9223372036854775807, + c17 = 18446744073709551615, + c18 = 18446744073709551615, + c19 = 3.402823466E+38, + c20 = 3.402823466E+38, + c21 = 3.402823466E+38, + c22 = 1.7976931348623E+308, + c23 = 1.7976931348623E+308, + c24 = 1.7976931348623E+308, + c25 = 9999999999, + c26 = 9999999999, + c27 = 9999999999, + # + c28 = '9999-12-31', + c29 = '9999-12-31 23:59:59', + c30 = '2038-01-08 03:14:07', + c31 = '838:59:59', + c32 = '2155', + # + c33 = x'ff', + c34 = '', + c35 = x'ff', + c36 = REPEAT(x'ff',255), + c37 = _utf8 x'efbfbf', + c38 = '', + c39 = _utf8 x'efbfbf', + c40 = REPEAT(_utf8 x'efbfbf',255), + c41 = _ucs2 x'ffff', + c42 = '', + c43 = _ucs2 x'ffff', + c44 = REPEAT(_ucs2 x'ffff',255), + # + c45 = '', + c46 = x'ff', + c47 = REPEAT(x'ff',255), + c48 = REPEAT(x'ff',261), + c49 = '', + c50 = _utf8 x'efbfbf', + c51 = REPEAT(_utf8 x'efbfbf',255), + c52 = REPEAT(_utf8 x'efbfbf',261), + c53 = '', + c54 = _ucs2 x'ffff', + c55 = REPEAT(_ucs2 x'ffff',255), + c56 = REPEAT(_ucs2 x'ffff',261), + # + c57 = x'ff', + c58 = '', + c59 = x'ff', + c60 = REPEAT(x'ff',255), + # + c61 = '', + c62 = x'ff', + c63 = REPEAT(x'ff',255), + c64 = REPEAT(x'ff',261), + # + c65 = 'tinyblob', + c66 = 'tinytext', + c67 = 'tinytext-ucs2', + c68 = 'blob', + c69 = 'text', + c70 = 'text-ucs2', + c71 = 'mediumblob', + c72 = 'mediumtext', + c73 = 'mediumtext-ucs2', + c74 = 'longblob', + c75 = 'longtext', + c76 = 'longtext-ucs2', + # + c77 = 'c', + c78 = 'a,b,c', + # + crn = crn + # + WHERE + # + c01 = b'0' AND + c02 = b'0000000000000000000000000000000000000000000000000000000000000000' AND + c03 = -128 AND + c04 = 0 AND + c05 = 000 AND + c06 = false AND + c07 = -32768 AND + c08 = 0 AND + c09 = 00000 AND + c10 = -8388608 AND + c11 = 0 AND + c12 = 00000000 AND + c13 = -2147483648 AND + c14 = 0 AND + c15 = 0000000000 AND + c16 = -9223372036854775808 AND + c17 = 0 AND + c18 = 00000000000000000000 AND + c19 < -3.402823465E+38 AND + c20 < 1.175494352E-38 AND + c21 = 000000000000 AND + c22 < -1.7976931348622E+308 AND + c23 < 2.2250738585073E-308 AND + c24 = 0000000000000000000000 AND + c25 = -9999999999 AND + c26 = 0 AND + c27 = 0000000000 AND + # + c28 = '1000-01-01' AND + c29 = '1000-01-01 00:00:00' AND + c30 = '1970-01-02 00:00:01' AND + c31 = '-838:59:59' AND + c32 = '1901' AND + # + c33 = '' AND + c34 = '' AND + c35 = '' AND + c36 = '' AND + c37 = '' AND + c38 = '' AND + c39 = '' AND + c40 = '' AND + c41 = '' AND + c42 = '' AND + c43 = '' AND + c44 = '' AND + # + c45 = '' AND + c46 = '' AND + c47 = '' AND + c48 = '' AND + c49 = '' AND + c50 = '' AND + c51 = '' AND + c52 = '' AND + c53 = '' AND + c54 = '' AND + c55 = '' AND + c56 = '' AND + # + # this does not reproduce the inserted value: c57 = '' AND + c58 = '' AND + # this does not reproduce the inserted value: c59 = '' AND + # this does not reproduce the inserted value: c60 = '' AND + # + c61 = '' AND + c62 = '' AND + c63 = '' AND + c64 = '' AND + # + c65 = '' AND + c66 = '' AND + c67 = '' AND + c68 = '' AND + c69 = '' AND + c70 = '' AND + c71 = '' AND + c72 = '' AND + c73 = '' AND + c74 = '' AND + c75 = '' AND + c76 = '' AND + # + c77 = 'a' AND + c78 = '' AND + # + crn = 1; + +--echo # +--echo # Update max values to min values. +--echo # +UPDATE t1 SET + c01 = b'0', + c02 = b'0000000000000000000000000000000000000000000000000000000000000000', + c03 = -128, + c04 = 0, + c05 = 000, + c06 = false, + c07 = -32768, + c08 = 0, + c09 = 00000, + c10 = -8388608, + c11 = 0, + c12 = 00000000, + c13 = -2147483648, + c14 = 0, + c15 = 0000000000, + c16 = -9223372036854775808, + c17 = 0, + c18 = 00000000000000000000, + c19 = -3.402823466E+38, + c20 = 1.175494351E-38, + c21 = 000000000000, + c22 = -1.7976931348623E+308, + c23 = 2.2250738585072E-308, + c24 = 0000000000000000000000, + c25 = -9999999999, + c26 = 0, + c27 = 0000000000, + # + c28 = '1000-01-01', + c29 = '1000-01-01 00:00:00', + c30 = '1970-01-02 00:00:01', + c31 = '-838:59:59', + c32 = '1901', + # + c33 = '', + c34 = '', + c35 = '', + c36 = '', + c37 = '', + c38 = '', + c39 = '', + c40 = '', + c41 = '', + c42 = '', + c43 = '', + c44 = '', + # + c45 = '', + c46 = '', + c47 = '', + c48 = '', + c49 = '', + c50 = '', + c51 = '', + c52 = '', + c53 = '', + c54 = '', + c55 = '', + c56 = '', + # + c57 = '', + c58 = '', + c59 = '', + c60 = '', + # + c61 = '', + c62 = '', + c63 = '', + c64 = '', + # + c65 = '', + c66 = '', + c67 = '', + c68 = '', + c69 = '', + c70 = '', + c71 = '', + c72 = '', + c73 = '', + c74 = '', + c75 = '', + c76 = '', + # + c77 = 'a', + c78 = '', + # + crn = crn + # + WHERE + # + c01 = b'1' AND + # the below does not reproduce the inserted value: + #c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND + c03 = 127 AND + c04 = 255 AND + c05 = 255 AND + c06 = true AND + c07 = 32767 AND + c08 = 65535 AND + c09 = 65535 AND + c10 = 8388607 AND + c11 = 16777215 AND + c12 = 16777215 AND + c13 = 2147483647 AND + c14 = 4294967295 AND + c15 = 4294967295 AND + c16 = 9223372036854775807 AND + c17 = 18446744073709551615 AND + c18 = 18446744073709551615 AND + c19 > 3.402823465E+38 AND + c20 > 3.402823465E+38 AND + c21 > 3.402823465E+38 AND + c22 > 1.7976931348622E+308 AND + c23 > 1.7976931348622E+308 AND + c24 > 1.7976931348622E+308 AND + c25 = 9999999999 AND + c26 = 9999999999 AND + c27 = 9999999999 AND + # + c28 = '9999-12-31' AND + c29 = '9999-12-31 23:59:59' AND + c30 = '2038-01-08 03:14:07' AND + c31 = '838:59:59' AND + c32 = '2155' AND + # + c33 = x'ff' AND + c34 = '' AND + c35 = x'ff' AND + c36 = REPEAT(x'ff',255) AND + c37 = _utf8 x'efbfbf' AND + c38 = '' AND + c39 = _utf8 x'efbfbf' AND + c40 = REPEAT(_utf8 x'efbfbf',255) AND + c41 = _ucs2 x'ffff' AND + c42 = '' AND + c43 = _ucs2 x'ffff' AND + c44 = REPEAT(_ucs2 x'ffff',255) AND + # + c45 = '' AND + c46 = x'ff' AND + c47 = REPEAT(x'ff',255) AND + c48 = REPEAT(x'ff',261) AND + c49 = '' AND + c50 = _utf8 x'efbfbf' AND + c51 = REPEAT(_utf8 x'efbfbf',255) AND + c52 = REPEAT(_utf8 x'efbfbf',261) AND + c53 = '' AND + c54 = _ucs2 x'ffff' AND + c55 = REPEAT(_ucs2 x'ffff',255) AND + c56 = REPEAT(_ucs2 x'ffff',261) AND + # + c57 = x'ff' AND + c58 = '' AND + c59 = x'ff' AND + c60 = REPEAT(x'ff',255) AND + # + c61 = '' AND + c62 = x'ff' AND + c63 = REPEAT(x'ff',255) AND + c64 = REPEAT(x'ff',261) AND + # + c65 = 'tinyblob' AND + c66 = 'tinytext' AND + c67 = 'tinytext-ucs2' AND + c68 = 'blob' AND + c69 = 'text' AND + c70 = 'text-ucs2' AND + c71 = 'mediumblob' AND + c72 = 'mediumtext' AND + c73 = 'mediumtext-ucs2' AND + c74 = 'longblob' AND + c75 = 'longtext' AND + c76 = 'longtext-ucs2' AND + # + c77 = 'c' AND + c78 = 'a,b,c' AND + # + crn = 2; + +--echo # +--echo # Update NULL values to arbitrary values. +--echo # +UPDATE t1 SET + c01 = b'1', + c02 = b'1111111111111111111111111111111111111111111111111111111111111111', + c03 = 127, + c04 = 0, + c05 = 001, + c06 = true, + c07 = 32767, + c08 = 0, + c09 = 00001, + c10 = 8388607, + c11 = 0, + c12 = 00000001, + c13 = 2147483647, + c14 = 0, + c15 = 0000000001, + c16 = 9223372036854775807, + c17 = 0, + c18 = 00000000000000000001, + c19 = -1.175494351E-38, + c20 = 1.175494351E-38, + c21 = 000000000000001, + c22 = -2.2250738585072E-308, + c23 = 2.2250738585072E-308, + c24 = 00000000000000000000001, + c25 = -9999999999, + c26 = 9999999999, + c27 = 0000000001, + # + c28 = '2008-08-04', + c29 = '2008-08-04 16:18:06', + c30 = '2008-08-04 16:18:24', + c31 = '16:18:47', + c32 = '2008', + # + c33 = 'a', + c34 = '', + c35 = 'e', + c36 = REPEAT('i',255), + c37 = _utf8 x'c3a4', + c38 = '', + c39 = _utf8 x'c3b6', + c40 = REPEAT(_utf8 x'c3bc',255), + c41 = _ucs2 x'00e4', + c42 = '', + c43 = _ucs2 x'00f6', + c44 = REPEAT(_ucs2 x'00fc',255), + # + c45 = '', + c46 = 'a', + c47 = REPEAT('e',255), + c48 = REPEAT('i',261), + c49 = '', + c50 = _utf8 x'c3a4', + c51 = REPEAT(_utf8 x'c3b6',255), + c52 = REPEAT(_utf8 x'c3bc',261), + c53 = '', + c54 = _ucs2 x'00e4', + c55 = REPEAT(_ucs2 x'00f6',255), + c56 = REPEAT(_ucs2 x'00fc',261), + # + c57 = '0', + c58 = '', + c59 = '1', + c60 = REPEAT('1',255), + # + c61 = '', + c62 = 'b', + c63 = REPEAT('c',255), + c64 = REPEAT('\'',261), + # + c65 = 'tinyblob', + c66 = 'tinytext', + c67 = 'tinytext-ucs2', + c68 = 'blob', + c69 = 'text', + c70 = 'text-ucs2', + c71 = 'mediumblob', + c72 = 'mediumtext', + c73 = 'mediumtext-ucs2', + c74 = 'longblob', + c75 = 'longtext', + c76 = 'longtext-ucs2', + # + c77 = 'b', + c78 = 'b,c', + # + crn = crn + # + WHERE + # + c01 IS NULL AND + c02 IS NULL AND + c03 IS NULL AND + c04 IS NULL AND + c05 IS NULL AND + c06 IS NULL AND + c07 IS NULL AND + c08 IS NULL AND + c09 IS NULL AND + c10 IS NULL AND + c11 IS NULL AND + c12 IS NULL AND + c13 IS NULL AND + c14 IS NULL AND + c15 IS NULL AND + c16 IS NULL AND + c17 IS NULL AND + c18 IS NULL AND + c19 IS NULL AND + c20 IS NULL AND + c21 IS NULL AND + c22 IS NULL AND + c23 IS NULL AND + c24 IS NULL AND + c25 IS NULL AND + c26 IS NULL AND + c27 IS NULL AND + # + c28 IS NULL AND + c29 IS NULL AND + # this got a timestamp instead of NULL: c30 IS NULL AND + c31 IS NULL AND + c32 IS NULL AND + # + c33 IS NULL AND + c34 IS NULL AND + c35 IS NULL AND + c36 IS NULL AND + c37 IS NULL AND + c38 IS NULL AND + c39 IS NULL AND + c40 IS NULL AND + c41 IS NULL AND + c42 IS NULL AND + c43 IS NULL AND + c44 IS NULL AND + # + c45 IS NULL AND + c46 IS NULL AND + c47 IS NULL AND + c48 IS NULL AND + c49 IS NULL AND + c50 IS NULL AND + c51 IS NULL AND + c52 IS NULL AND + c53 IS NULL AND + c54 IS NULL AND + c55 IS NULL AND + c56 IS NULL AND + # + c57 IS NULL AND + c58 IS NULL AND + c59 IS NULL AND + c60 IS NULL AND + # + c61 IS NULL AND + c62 IS NULL AND + c63 IS NULL AND + c64 IS NULL AND + # + c65 IS NULL AND + c66 IS NULL AND + c67 IS NULL AND + c68 IS NULL AND + c69 IS NULL AND + c70 IS NULL AND + c71 IS NULL AND + c72 IS NULL AND + c73 IS NULL AND + c74 IS NULL AND + c75 IS NULL AND + c76 IS NULL AND + # + c77 IS NULL AND + c78 IS NULL AND + # + crn = 3; + +--echo # +--echo # Update arbitrary values to NULL values. +--echo # +UPDATE t1 SET + c01 = NULL, + c02 = NULL, + c03 = NULL, + c04 = NULL, + c05 = NULL, + c06 = NULL, + c07 = NULL, + c08 = NULL, + c09 = NULL, + c10 = NULL, + c11 = NULL, + c12 = NULL, + c13 = NULL, + c14 = NULL, + c15 = NULL, + c16 = NULL, + c17 = NULL, + c18 = NULL, + c19 = NULL, + c20 = NULL, + c21 = NULL, + c22 = NULL, + c23 = NULL, + c24 = NULL, + c25 = NULL, + c26 = NULL, + c27 = NULL, + # + c28 = NULL, + c29 = NULL, + c30 = NULL, + c31 = NULL, + c32 = NULL, + # + c33 = NULL, + c34 = NULL, + c35 = NULL, + c36 = NULL, + c37 = NULL, + c38 = NULL, + c39 = NULL, + c40 = NULL, + c41 = NULL, + c42 = NULL, + c43 = NULL, + c44 = NULL, + # + c45 = NULL, + c46 = NULL, + c47 = NULL, + c48 = NULL, + c49 = NULL, + c50 = NULL, + c51 = NULL, + c52 = NULL, + c53 = NULL, + c54 = NULL, + c55 = NULL, + c56 = NULL, + # + c57 = NULL, + c58 = NULL, + c59 = NULL, + c60 = NULL, + # + c61 = NULL, + c62 = NULL, + c63 = NULL, + c64 = NULL, + # + c65 = NULL, + c66 = NULL, + c67 = NULL, + c68 = NULL, + c69 = NULL, + c70 = NULL, + c71 = NULL, + c72 = NULL, + c73 = NULL, + c74 = NULL, + c75 = NULL, + c76 = NULL, + # + c77 = NULL, + c78 = NULL, + # + crn = crn + # + WHERE + # + c01 = b'1' AND + # the below does not reproduce the inserted value: + #c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND + c03 = 127 AND + c04 = 0 AND + c05 = 001 AND + c06 = true AND + c07 = 32767 AND + c08 = 0 AND + c09 = 00001 AND + c10 = 8388607 AND + c11 = 0 AND + c12 = 00000001 AND + c13 = 2147483647 AND + c14 = 0 AND + c15 = 0000000001 AND + c16 = 9223372036854775807 AND + c17 = 0 AND + c18 = 00000000000000000001 AND + c19 > -1.175494352E-38 AND + c20 < 1.175494352E-38 AND + c21 = 000000000000001 AND + c22 > -2.2250738585073E-308 AND + c23 < 2.2250738585073E-308 AND + c24 = 00000000000000000000001 AND + c25 = -9999999999 AND + c26 = 9999999999 AND + c27 = 0000000001 AND + # + c28 = '2008-08-04' AND + c29 = '2008-08-04 16:18:06' AND + c30 = '2008-08-04 16:18:24' AND + c31 = '16:18:47' AND + c32 = '2008' AND + # + c33 = 'a' AND + c34 = '' AND + c35 = 'e' AND + c36 = REPEAT('i',255) AND + c37 = _utf8 x'c3a4' AND + c38 = '' AND + c39 = _utf8 x'c3b6' AND + c40 = REPEAT(_utf8 x'c3bc',255) AND + c41 = _ucs2 x'00e4' AND + c42 = '' AND + c43 = _ucs2 x'00f6' AND + c44 = REPEAT(_ucs2 x'00fc',255) AND + # + c45 = '' AND + c46 = 'a' AND + c47 = REPEAT('e',255) AND + c48 = REPEAT('i',261) AND + c49 = '' AND + c50 = _utf8 x'c3a4' AND + c51 = REPEAT(_utf8 x'c3b6',255) AND + c52 = REPEAT(_utf8 x'c3bc',261) AND + c53 = '' AND + c54 = _ucs2 x'00e4' AND + c55 = REPEAT(_ucs2 x'00f6',255) AND + c56 = REPEAT(_ucs2 x'00fc',261) AND + # + c57 = '0' AND + c58 = '' AND + c59 = '1' AND + c60 = REPEAT('1',255) AND + # + c61 = '' AND + c62 = 'b' AND + c63 = REPEAT('c',255) AND + c64 = REPEAT('\'',261) AND + # + c65 = 'tinyblob' AND + c66 = 'tinytext' AND + c67 = 'tinytext-ucs2' AND + c68 = 'blob' AND + c69 = 'text' AND + c70 = 'text-ucs2' AND + c71 = 'mediumblob' AND + c72 = 'mediumtext' AND + c73 = 'mediumtext-ucs2' AND + c74 = 'longblob' AND + c75 = 'longtext' AND + c76 = 'longtext-ucs2' AND + # + c77 = 'b' AND + c78 = 'b,c' AND + # + crn = 4; + +--echo # +--echo # Show what we have in the table. +--echo # Do not display bit type output. It's binary and confuses diff. +--echo # Also BINARY with nul-bytes should be avoided. +--echo # +--replace_column 1 # 2 # 57 # 58 # 59 # 60 # +query_vertical SELECT * FROM t1; + +--echo # +--echo # Delete the row that has max values now. +--echo # +DELETE FROM t1 WHERE + # + c01 = b'1' AND + # the below does not reproduce the inserted value: + #c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND + c03 = 127 AND + c04 = 255 AND + c05 = 255 AND + c06 = true AND + c07 = 32767 AND + c08 = 65535 AND + c09 = 65535 AND + c10 = 8388607 AND + c11 = 16777215 AND + c12 = 16777215 AND + c13 = 2147483647 AND + c14 = 4294967295 AND + c15 = 4294967295 AND + c16 = 9223372036854775807 AND + c17 = 18446744073709551615 AND + c18 = 18446744073709551615 AND + c19 > 3.402823465E+38 AND + c20 > 3.402823465E+38 AND + c21 > 3.402823465E+38 AND + c22 > 1.7976931348622E+308 AND + c23 > 1.7976931348622E+308 AND + c24 > 1.7976931348622E+308 AND + c25 = 9999999999 AND + c26 = 9999999999 AND + c27 = 9999999999 AND + # + c28 = '9999-12-31' AND + c29 = '9999-12-31 23:59:59' AND + c30 = '2038-01-08 03:14:07' AND + c31 = '838:59:59' AND + c32 = '2155' AND + # + c33 = x'ff' AND + c34 = '' AND + c35 = x'ff' AND + c36 = REPEAT(x'ff',255) AND + c37 = _utf8 x'efbfbf' AND + c38 = '' AND + c39 = _utf8 x'efbfbf' AND + c40 = REPEAT(_utf8 x'efbfbf',255) AND + c41 = _ucs2 x'ffff' AND + c42 = '' AND + c43 = _ucs2 x'ffff' AND + c44 = REPEAT(_ucs2 x'ffff',255) AND + # + c45 = '' AND + c46 = x'ff' AND + c47 = REPEAT(x'ff',255) AND + c48 = REPEAT(x'ff',261) AND + c49 = '' AND + c50 = _utf8 x'efbfbf' AND + c51 = REPEAT(_utf8 x'efbfbf',255) AND + c52 = REPEAT(_utf8 x'efbfbf',261) AND + c53 = '' AND + c54 = _ucs2 x'ffff' AND + c55 = REPEAT(_ucs2 x'ffff',255) AND + c56 = REPEAT(_ucs2 x'ffff',261) AND + # + c57 = x'ff' AND + c58 = '' AND + c59 = x'ff' AND + c60 = REPEAT(x'ff',255) AND + # + c61 = '' AND + c62 = x'ff' AND + c63 = REPEAT(x'ff',255) AND + c64 = REPEAT(x'ff',261) AND + # + c65 = 'tinyblob' AND + c66 = 'tinytext' AND + c67 = 'tinytext-ucs2' AND + c68 = 'blob' AND + c69 = 'text' AND + c70 = 'text-ucs2' AND + c71 = 'mediumblob' AND + c72 = 'mediumtext' AND + c73 = 'mediumtext-ucs2' AND + c74 = 'longblob' AND + c75 = 'longtext' AND + c76 = 'longtext-ucs2' AND + # + c77 = 'c' AND + c78 = 'a,b,c' AND + # + crn = 1; + +--echo # +--echo # Delete the row that has min values now. +--echo # +DELETE FROM t1 WHERE + # + c01 = b'0' AND + c02 = b'0000000000000000000000000000000000000000000000000000000000000000' AND + c03 = -128 AND + c04 = 0 AND + c05 = 000 AND + c06 = false AND + c07 = -32768 AND + c08 = 0 AND + c09 = 00000 AND + c10 = -8388608 AND + c11 = 0 AND + c12 = 00000000 AND + c13 = -2147483648 AND + c14 = 0 AND + c15 = 0000000000 AND + c16 = -9223372036854775808 AND + c17 = 0 AND + c18 = 00000000000000000000 AND + c19 < -3.402823465E+38 AND + c20 < 1.175494352E-38 AND + c21 = 000000000000 AND + c22 < -1.7976931348622E+308 AND + c23 < 2.2250738585073E-308 AND + c24 = 0000000000000000000000 AND + c25 = -9999999999 AND + c26 = 0 AND + c27 = 0000000000 AND + # + c28 = '1000-01-01' AND + c29 = '1000-01-01 00:00:00' AND + c30 = '1970-01-02 00:00:01' AND + c31 = '-838:59:59' AND + c32 = '1901' AND + # + c33 = '' AND + c34 = '' AND + c35 = '' AND + c36 = '' AND + c37 = '' AND + c38 = '' AND + c39 = '' AND + c40 = '' AND + c41 = '' AND + c42 = '' AND + c43 = '' AND + c44 = '' AND + # + c45 = '' AND + c46 = '' AND + c47 = '' AND + c48 = '' AND + c49 = '' AND + c50 = '' AND + c51 = '' AND + c52 = '' AND + c53 = '' AND + c54 = '' AND + c55 = '' AND + c56 = '' AND + # + # this does not reproduce the inserted value: c57 = '' AND + c58 = '' AND + # this does not reproduce the inserted value: c59 = '' AND + # this does not reproduce the inserted value: c60 = '' AND + # + c61 = '' AND + c62 = '' AND + c63 = '' AND + c64 = '' AND + # + c65 = '' AND + c66 = '' AND + c67 = '' AND + c68 = '' AND + c69 = '' AND + c70 = '' AND + c71 = '' AND + c72 = '' AND + c73 = '' AND + c74 = '' AND + c75 = '' AND + c76 = '' AND + # + c77 = 'a' AND + c78 = '' AND + # + crn = 2; + +--echo # +--echo # Delete the row that has arbitrary values now. +--echo # +DELETE FROM t1 WHERE + # + c01 = b'1' AND + # the below does not reproduce the inserted value: + #c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND + c03 = 127 AND + c04 = 0 AND + c05 = 001 AND + c06 = true AND + c07 = 32767 AND + c08 = 0 AND + c09 = 00001 AND + c10 = 8388607 AND + c11 = 0 AND + c12 = 00000001 AND + c13 = 2147483647 AND + c14 = 0 AND + c15 = 0000000001 AND + c16 = 9223372036854775807 AND + c17 = 0 AND + c18 = 00000000000000000001 AND + c19 > -1.175494352E-38 AND + c20 < 1.175494352E-38 AND + c21 = 000000000000001 AND + c22 > -2.2250738585073E-308 AND + c23 < 2.2250738585073E-308 AND + c24 = 00000000000000000000001 AND + c25 = -9999999999 AND + c26 = 9999999999 AND + c27 = 0000000001 AND + # + c28 = '2008-08-04' AND + c29 = '2008-08-04 16:18:06' AND + c30 = '2008-08-04 16:18:24' AND + c31 = '16:18:47' AND + c32 = '2008' AND + # + c33 = 'a' AND + c34 = '' AND + c35 = 'e' AND + c36 = REPEAT('i',255) AND + c37 = _utf8 x'c3a4' AND + c38 = '' AND + c39 = _utf8 x'c3b6' AND + c40 = REPEAT(_utf8 x'c3bc',255) AND + c41 = _ucs2 x'00e4' AND + c42 = '' AND + c43 = _ucs2 x'00f6' AND + c44 = REPEAT(_ucs2 x'00fc',255) AND + # + c45 = '' AND + c46 = 'a' AND + c47 = REPEAT('e',255) AND + c48 = REPEAT('i',261) AND + c49 = '' AND + c50 = _utf8 x'c3a4' AND + c51 = REPEAT(_utf8 x'c3b6',255) AND + c52 = REPEAT(_utf8 x'c3bc',261) AND + c53 = '' AND + c54 = _ucs2 x'00e4' AND + c55 = REPEAT(_ucs2 x'00f6',255) AND + c56 = REPEAT(_ucs2 x'00fc',261) AND + # + c57 = '0' AND + c58 = '' AND + c59 = '1' AND + c60 = REPEAT('1',255) AND + # + c61 = '' AND + c62 = 'b' AND + c63 = REPEAT('c',255) AND + c64 = REPEAT('\'',261) AND + # + c65 = 'tinyblob' AND + c66 = 'tinytext' AND + c67 = 'tinytext-ucs2' AND + c68 = 'blob' AND + c69 = 'text' AND + c70 = 'text-ucs2' AND + c71 = 'mediumblob' AND + c72 = 'mediumtext' AND + c73 = 'mediumtext-ucs2' AND + c74 = 'longblob' AND + c75 = 'longtext' AND + c76 = 'longtext-ucs2' AND + # + c77 = 'b' AND + c78 = 'b,c' AND + # + crn = 3; + +--echo # +--echo # Delete the row that has NULL values now. +--echo # +DELETE FROM t1 WHERE + # + c01 IS NULL AND + c02 IS NULL AND + c03 IS NULL AND + c04 IS NULL AND + c05 IS NULL AND + c06 IS NULL AND + c07 IS NULL AND + c08 IS NULL AND + c09 IS NULL AND + c10 IS NULL AND + c11 IS NULL AND + c12 IS NULL AND + c13 IS NULL AND + c14 IS NULL AND + c15 IS NULL AND + c16 IS NULL AND + c17 IS NULL AND + c18 IS NULL AND + c19 IS NULL AND + c20 IS NULL AND + c21 IS NULL AND + c22 IS NULL AND + c23 IS NULL AND + c24 IS NULL AND + c25 IS NULL AND + c26 IS NULL AND + c27 IS NULL AND + # + c28 IS NULL AND + c29 IS NULL AND + # this got a timestamp instead of NULL: c30 IS NULL AND + c31 IS NULL AND + c32 IS NULL AND + # + c33 IS NULL AND + c34 IS NULL AND + c35 IS NULL AND + c36 IS NULL AND + c37 IS NULL AND + c38 IS NULL AND + c39 IS NULL AND + c40 IS NULL AND + c41 IS NULL AND + c42 IS NULL AND + c43 IS NULL AND + c44 IS NULL AND + # + c45 IS NULL AND + c46 IS NULL AND + c47 IS NULL AND + c48 IS NULL AND + c49 IS NULL AND + c50 IS NULL AND + c51 IS NULL AND + c52 IS NULL AND + c53 IS NULL AND + c54 IS NULL AND + c55 IS NULL AND + c56 IS NULL AND + # + c57 IS NULL AND + c58 IS NULL AND + c59 IS NULL AND + c60 IS NULL AND + # + c61 IS NULL AND + c62 IS NULL AND + c63 IS NULL AND + c64 IS NULL AND + # + c65 IS NULL AND + c66 IS NULL AND + c67 IS NULL AND + c68 IS NULL AND + c69 IS NULL AND + c70 IS NULL AND + c71 IS NULL AND + c72 IS NULL AND + c73 IS NULL AND + c74 IS NULL AND + c75 IS NULL AND + c76 IS NULL AND + # + c77 IS NULL AND + c78 IS NULL AND + # + crn = 4; + +--echo # +--echo # Show what we have in the table. Should be empty now. +--echo # +query_vertical SELECT * FROM t1; + +--echo # +--echo # Hide how much rows are affected by each statement. +--echo # +--disable_info + +--echo # +--echo # Flush all log buffers to the log file. +--echo # +FLUSH LOGS; + +--echo # +--echo # Call mysqlbinlog to display the log file contents. +--echo # +let $MYSQLD_DATADIR= `select @@datadir`; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /(@[0-9]*=[0-9-]*[.][0-9]{1,3})[0-9e+-]*[^ ]*[ ]*(.*(FLOAT|DOUBLE).*[*].)/\1... \2/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /xid=\d*/xid=<xid>/ +--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001 + +--echo # +--echo # Cleanup. +--echo # +DROP TABLE t1; + +--echo # +--echo # ========================================= +--echo # Test #2 - Multi-row insert/update/delete. +--echo # ========================================= +--echo # +--echo # Delete all existing binary logs. +--echo # +RESET MASTER; + +--echo # +--echo # Create a test table with selected data types. +--echo # +eval CREATE TABLE t1 ( + c28 DATE, + c47 VARCHAR(24), + crn INT -- row number + ) ENGINE=$engine_type DEFAULT CHARSET latin1; + +--echo # +--echo # Show how much rows are affected by each statement. +--echo # +--enable_info + +--echo # +--echo # Multi-row insert. +--echo # +INSERT INTO t1 VALUES + ('2008-08-01','VARCHAR-01',1), + ('2008-08-02','VARCHAR-02',2), + ('2008-08-03','VARCHAR-03',3), + ('2008-08-04','VARCHAR-04',4), + ('2008-08-05','VARCHAR-05',5), + ('2008-08-06','VARCHAR-06',6), + ('2008-08-07','VARCHAR-07',7), + ('2008-08-08','VARCHAR-08',8), + ('2008-08-09','VARCHAR-09',9); + +--echo # +--echo # Multi-row update. +--echo # +UPDATE t1 SET c28 = ADDDATE(c28,10) WHERE crn < 8; + +--echo # +--echo # Show what we have in the table. +--echo # +SELECT * FROM t1; + +--echo # +--echo # Multi-row delete. +--echo # +DELETE FROM t1 WHERE crn < 8; + +--echo # +--echo # Show what we have in the table. +--echo # +SELECT * FROM t1; + +--echo # +--echo # Hide how much rows are affected by each statement. +--echo # +--disable_info + +--echo # +--echo # Flush all log buffers to the log file. +--echo # +FLUSH LOGS; + +--echo # +--echo # Call mysqlbinlog to display the log file contents. +--echo # +let $MYSQLD_DATADIR= `select @@datadir`; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /(@[0-9]*=[0-9-]*[.][0-9]{1,3})[0-9e+-]*[^ ]*[ ]*(.*(FLOAT|DOUBLE).*[*].)/\1... \2/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /xid=\d*/xid=<xid>/ +--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001 + +--echo # +--echo # Cleanup. +--echo # +DROP TABLE t1; + +--echo # +--echo # ==================================== +--echo # Test #3 - Multi-table update/delete. +--echo # ==================================== +--echo # +--echo # Delete all existing binary logs. +--echo # +RESET MASTER; + +--echo # +--echo # Create test tables with selected data types. +--echo # +eval CREATE TABLE t1 ( + c_1_1 DATE, + c_1_2 VARCHAR(255), + c_1_n INT -- row number + ) ENGINE=$engine_type DEFAULT CHARSET latin1; +# +eval CREATE TABLE t2 ( + c_2_1 DATE, + c_2_2 VARCHAR(255), + c_2_n INT -- row number + ) ENGINE=$engine_type DEFAULT CHARSET latin1; +# +eval CREATE TABLE t3 ( + c_3_1 DATE, + c_3_2 VARCHAR(255), + c_3_n INT -- row number + ) ENGINE=$engine_type DEFAULT CHARSET latin1; + +--echo # +--echo # Show how much rows are affected by each statement. +--echo # +--enable_info + +--echo # +--echo # Insert data. +--echo # +INSERT INTO t1 VALUES + ('2008-01-01','VARCHAR-01-01',11), + ('2008-01-02','VARCHAR-01-02',2), + ('2008-01-03','VARCHAR-01-03',3), + ('2008-01-04','VARCHAR-01-04',4), + ('2008-01-05','VARCHAR-01-05',5), + ('2008-01-06','VARCHAR-01-06',6), + ('2008-01-07','VARCHAR-01-07',7), + ('2008-01-08','VARCHAR-01-08',18), + ('2008-01-09','VARCHAR-01-09',19); +# +INSERT INTO t2 VALUES + ('2008-02-01','VARCHAR-02-01',21), + ('2008-02-02','VARCHAR-02-02',2), + ('2008-02-03','VARCHAR-02-03',3), + ('2008-02-04','VARCHAR-02-04',4), + ('2008-02-05','VARCHAR-02-05',5), + ('2008-02-06','VARCHAR-02-06',6), + ('2008-02-07','VARCHAR-02-07',7), + ('2008-02-08','VARCHAR-02-08',28), + ('2008-02-09','VARCHAR-02-09',29); +# +INSERT INTO t3 VALUES + ('2008-03-01','VARCHAR-03-01',31), + ('2008-03-02','VARCHAR-03-02',2), + ('2008-03-03','VARCHAR-03-03',3), + ('2008-03-04','VARCHAR-03-04',4), + ('2008-03-05','VARCHAR-03-05',5), + ('2008-03-06','VARCHAR-03-06',6), + ('2008-03-07','VARCHAR-03-07',7), + ('2008-03-08','VARCHAR-03-08',38), + ('2008-03-09','VARCHAR-03-09',39); + +--echo # +--echo # Multi-table update. +--echo # +UPDATE t1,t2,t3 SET + c_1_1 = ADDDATE(c_1_1,INTERVAL 10 YEAR), + c_2_1 = ADDDATE(c_2_1,INTERVAL 20 YEAR), + c_3_1 = ADDDATE(c_3_1,INTERVAL 30 YEAR) + WHERE c_1_n = c_2_n AND c_2_n = c_3_n; + +--echo # +--echo # Show what we have in the tables. +--echo # +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; + +--echo # +--echo # Multi-table delete. +--echo # +DELETE FROM t1,t2,t3 USING t1 INNER JOIN t2 INNER JOIN t3 + WHERE c_1_n = c_2_n AND c_2_n = c_3_n; + +--echo # +--echo # Show what we have in the tables. +--echo # +SELECT * FROM t1; +SELECT * FROM t2; +SELECT * FROM t3; + +--echo # +--echo # Hide how much rows are affected by each statement. +--echo # +--disable_info + +--echo # +--echo # Flush all log buffers to the log file. +--echo # +FLUSH LOGS; + +--echo # +--echo # Call mysqlbinlog to display the log file contents. +--echo # +let $MYSQLD_DATADIR= `select @@datadir`; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /(@[0-9]*=[0-9-]*[.][0-9]{1,3})[0-9e+-]*[^ ]*[ ]*(.*(FLOAT|DOUBLE).*[*].)/\1... \2/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /xid=\d*/xid=<xid>/ +--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001 + +--echo # +--echo # Cleanup. +--echo # +DROP TABLE t1, t2, t3; + +--echo # +--echo # =========================== +--echo # Test #4 - LOAD DATA INFILE. +--echo # =========================== +--echo # +--echo # Delete all existing binary logs. +--echo # +RESET MASTER; + +--echo # +--echo # Create a test table with selected data types. +--echo # +eval CREATE TABLE t1 ( + c1 INT DEFAULT 100, + c2 INT, + c3 VARCHAR(60) + ) ENGINE=$engine_type DEFAULT CHARSET latin1; + +--echo # +--echo # Show how much rows are affected by each statement. +--echo # +--enable_info + +--echo # +--echo # Load data. +--echo # +LOAD DATA INFILE '../../std_data/loaddata5.dat' + INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (c1,c2) + SET c3 = 'Wow'; + +--echo # +--echo # Show what we have in the table. +--echo # +SELECT * FROM t1; + +--echo # +--echo # Hide how much rows are affected by each statement. +--echo # +--disable_info + +--echo # +--echo # Flush all log buffers to the log file. +--echo # +FLUSH LOGS; + +--echo # +--echo # Call mysqlbinlog to display the log file contents. +--echo # +let $MYSQLD_DATADIR= `select @@datadir`; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /(@[0-9]*=[0-9-]*[.][0-9]{1,3})[0-9e+-]*[^ ]*[ ]*(.*(FLOAT|DOUBLE).*[*].)/\1... \2/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /xid=\d*/xid=<xid>/ +--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001 + +--echo # +--echo # Cleanup. +--echo # +DROP TABLE t1; + + +--echo # +--echo # Beginning of 10.2 test +--echo # +--echo # MDEV-25460: Assertion `!is_set() || (m_status == DA_OK_BULK && +--echo # is_bulk_op())' failed in Diagnostics_area::set_ok_status in my_ok +--echo # from mysql_sql_stmt_prepare +--echo # + +CREATE TEMPORARY TABLE a (c INT) ENGINE=InnoDB; +CREATE TABLE b (c INT) ENGINE=InnoDB; + +--error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR +PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT * FROM b'; + +DROP TABLE b; +DROP TEMPORARY TABLE a; + +CREATE TEMPORARY TABLE t (c INT); +--error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR +PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT 1'; +DROP TEMPORARY TABLE t; + +--echo # +--echo # End of 10.2 test +--echo # diff --git a/mysql-test/suite/binlog/include/print_optional_metadata.inc b/mysql-test/suite/binlog/include/print_optional_metadata.inc new file mode 100644 index 00000000..739903ab --- /dev/null +++ b/mysql-test/suite/binlog/include/print_optional_metadata.inc @@ -0,0 +1,34 @@ +# Auxaliary file for printing optional metadata in table_map_log_event +# Usage : +# --let $binlog_file= +# [--let $stop_position] +# [--let $print_primary_key] +# --source extra/binlog_tests/print_optional_metadata.inc + +--let $output_file= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.output + +--let $_stop_position_opt= +if ($stop_position) +{ + --let $_stop_position_opt=--stop-position=$stop_position +} + +--exec $MYSQL_BINLOG -F --print-table-metadata $_stop_position_opt $binlog_file > $output_file + + +--let SEARCH_PATTERN= # (?:Columns\(| {8}).* +--let SEARCH_FILE= $output_file +--let SEARCH_OUTPUT=matches +--let SEARCH_TYPE="_gm_" +--source include/search_pattern_in_file.inc + +if ($print_primary_key) +{ + --let SEARCH_PATTERN= # Primary Key + --source include/search_pattern_in_file.inc +} +--remove_file $output_file +--let $stop_position= +--let $_stop_position_opt= + + |