mirror of
https://github.com/MariaDB/server.git
synced 2025-02-10 15:35:34 +01:00
![Brandon Nesterenko](/assets/img/avatar_default.png)
The special logic used by the memory storage engine to keep slaves in sync with the master on a restart can break replication. In particular, after a restart, the master writes DELETE statements in the binlog for each MEMORY-based table so the slave can empty its data. If the DELETE is not executable, e.g. due to invalid triggers, the slave will error and fail, whereas the master will never see the problem. Instead of DELETE statements, use TRUNCATE to keep slaves in-sync with the master, thereby bypassing triggers. Reviewed By: =========== Kristian Nielsen <knielsen@knielsen-hq.org> Andrei Elkin <andrei.elkin@mariadb.com>
409 lines
21 KiB
Text
409 lines
21 KiB
Text
include/master-slave.inc
|
|
[connection master]
|
|
connection master;
|
|
create table t1 (a int primary key) engine=innodb;
|
|
create table t2 (a int primary key) engine=myisam;
|
|
begin;
|
|
insert into t1 values (1);
|
|
SET sql_mode = 'ANSI_QUOTES';
|
|
savepoint `a``; create database couldbebadthingshere; savepoint ``dummy`;
|
|
insert into t1 values (2);
|
|
insert into t2 values (1);
|
|
SET sql_mode = '';
|
|
rollback to savepoint `a``; create database couldbebadthingshere; savepoint ``dummy`;
|
|
Warnings:
|
|
Warning 1196 Some non-transactional changed tables couldn't be rolled back
|
|
insert into t1 values (3);
|
|
commit;
|
|
show binlog events from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid_list 1 # []
|
|
master-bin.000001 # Binlog_checkpoint 1 # master-bin.000001
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `test`; create table t1 (a int primary key) engine=innodb
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `test`; create table t2 (a int primary key) engine=myisam
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `test`; insert into t2 values (1)
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values (1)
|
|
master-bin.000001 # Query 1 # SAVEPOINT "a`; create database couldbebadthingshere; savepoint `dummy"
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values (2)
|
|
master-bin.000001 # Query 1 # ROLLBACK TO `a``; create database couldbebadthingshere; savepoint ``dummy`
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values (3)
|
|
master-bin.000001 # Xid 1 # COMMIT /* XID */
|
|
connection slave;
|
|
connection slave;
|
|
connection master;
|
|
BEGIN;
|
|
insert into t1 values(10);
|
|
set sql_mode = 'ANSI_QUOTES';
|
|
set sql_quote_show_create = 1;
|
|
savepoint a;
|
|
insert into t1 values(11);
|
|
savepoint "a""a";
|
|
insert into t1 values(12);
|
|
set sql_quote_show_create = 0;
|
|
savepoint b;
|
|
insert into t1 values(13);
|
|
savepoint "b""b";
|
|
insert into t1 values(14);
|
|
set sql_mode = '';
|
|
set sql_quote_show_create = 1;
|
|
savepoint c;
|
|
insert into t1 values(15);
|
|
savepoint `c``c`;
|
|
insert into t1 values(16);
|
|
set sql_quote_show_create = 0;
|
|
savepoint d;
|
|
insert into t1 values(17);
|
|
savepoint `d``d`;
|
|
insert into t1 values(18);
|
|
COMMIT;
|
|
set sql_quote_show_create = 1;
|
|
show binlog events from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values(10)
|
|
master-bin.000001 # Query 1 # SAVEPOINT "a"
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values(11)
|
|
master-bin.000001 # Query 1 # SAVEPOINT "a""a"
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values(12)
|
|
master-bin.000001 # Query 1 # SAVEPOINT b
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values(13)
|
|
master-bin.000001 # Query 1 # SAVEPOINT "b""b"
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values(14)
|
|
master-bin.000001 # Query 1 # SAVEPOINT `c`
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values(15)
|
|
master-bin.000001 # Query 1 # SAVEPOINT `c``c`
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values(16)
|
|
master-bin.000001 # Query 1 # SAVEPOINT d
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values(17)
|
|
master-bin.000001 # Query 1 # SAVEPOINT `d``d`
|
|
master-bin.000001 # Query 1 # use `test`; insert into t1 values(18)
|
|
master-bin.000001 # Xid 1 # COMMIT /* XID */
|
|
*** Test correct USE statement in SHOW BINLOG EVENTS ***
|
|
connection master;
|
|
set sql_mode = 'ANSI_QUOTES';
|
|
CREATE DATABASE "db1`; select 'oops!'";
|
|
use "db1`; select 'oops!'";
|
|
CREATE TABLE t1 (a INT PRIMARY KEY) engine=MyISAM;
|
|
INSERT INTO t1 VALUES (1);
|
|
set sql_mode = '';
|
|
INSERT INTO t1 VALUES (2);
|
|
set sql_mode = 'ANSI_QUOTES';
|
|
show binlog events from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # CREATE DATABASE "db1`; select 'oops!'"
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # use "db1`; select 'oops!'"; CREATE TABLE t1 (a INT PRIMARY KEY) engine=MyISAM
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use "db1`; select 'oops!'"; INSERT INTO t1 VALUES (1)
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use "db1`; select 'oops!'"; INSERT INTO t1 VALUES (2)
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
set sql_mode = '';
|
|
set sql_quote_show_create = 0;
|
|
show binlog events from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # CREATE DATABASE "db1`; select 'oops!'"
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; CREATE TABLE t1 (a INT PRIMARY KEY) engine=MyISAM
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; INSERT INTO t1 VALUES (1)
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; INSERT INTO t1 VALUES (2)
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
set sql_quote_show_create = 1;
|
|
show binlog events from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # CREATE DATABASE "db1`; select 'oops!'"
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; CREATE TABLE t1 (a INT PRIMARY KEY) engine=MyISAM
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; INSERT INTO t1 VALUES (1)
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; INSERT INTO t1 VALUES (2)
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
DROP TABLE t1;
|
|
use test;
|
|
***Test LOAD DATA INFILE with various identifiers that need correct quoting ***
|
|
use `db1``; select 'oops!'`;
|
|
set timestamp=1000000000;
|
|
CREATE TABLE `t``1` (`a``1` VARCHAR(4) PRIMARY KEY, `b``2` VARCHAR(3),
|
|
`c``3` VARCHAR(7));
|
|
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/f''le.txt' INTO TABLE `t``1`
|
|
FIELDS TERMINATED BY ',' ESCAPED BY '\\' ENCLOSED BY ''''
|
|
LINES TERMINATED BY '\n'
|
|
(`a``1`, @`b```) SET `b``2` = @`b```, `c``3` = concat('|', "b""a'z", "!");
|
|
SELECT * FROM `t``1`;
|
|
a`1 b`2 c`3
|
|
fo\o bar |b"a'z!
|
|
truncate `t``1`;
|
|
use test;
|
|
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/f''le.txt'
|
|
INTO TABLE `db1``; select 'oops!'`.`t``1`
|
|
FIELDS TERMINATED BY ',' ESCAPED BY '\\' ENCLOSED BY ''''
|
|
LINES TERMINATED BY '\n'
|
|
(`a``1`, `b``2`) SET `c``3` = concat('|', "b""a'z", "!");
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1`;
|
|
a`1 b`2 c`3
|
|
fo\o bar |b"a'z!
|
|
show binlog events from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; CREATE TABLE `t``1` (`a``1` VARCHAR(4) PRIMARY KEY, `b``2` VARCHAR(3),
|
|
`c``3` VARCHAR(7))
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Begin_load_query 1 # ;file_id=#;block_len=14
|
|
master-bin.000001 # Execute_load_query 1 # use `db1``; select 'oops!'`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/f\'le.txt' INTO TABLE `t``1` FIELDS TERMINATED BY ',' ENCLOSED BY '\'' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a``1`, @`b```) SET `b``2`= @`b```, `c``3`= concat('|', "b""a'z", "!") ;file_id=#
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; truncate `t``1`
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Begin_load_query 1 # ;file_id=#;block_len=14
|
|
master-bin.000001 # Execute_load_query 1 # use `test`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/f\'le.txt' INTO TABLE `db1``; select 'oops!'`.`t``1` FIELDS TERMINATED BY ',' ENCLOSED BY '\'' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a``1`, `b``2`) SET `c``3`= concat('|', "b""a'z", "!") ;file_id=#
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
|
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
|
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
|
DELIMITER /*!*/;
|
|
ROLLBACK/*!*/;
|
|
use `db1``; select 'oops!'`/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
SET @@session.pseudo_thread_id=999999999/*!*/;
|
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=0/*!*/;
|
|
SET @@session.sql_mode=0/*!*/;
|
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
|
/*!\C latin1 *//*!*/;
|
|
SET @@session.character_set_client=latin1,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
|
|
SET @@session.lc_time_names=0/*!*/;
|
|
SET @@session.collation_database=DEFAULT/*!*/;
|
|
CREATE TABLE `t``1` (`a``1` VARCHAR(4) PRIMARY KEY, `b``2` VARCHAR(3),
|
|
`c``3` VARCHAR(7))
|
|
/*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
LOAD DATA LOCAL INFILE '<name>' INTO TABLE `t``1` FIELDS TERMINATED BY ',' ENCLOSED BY '\'' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a``1`, @`b```) SET `b``2`= @`b```, `c``3`= concat('|', "b""a'z", "!")
|
|
/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
truncate `t``1`
|
|
/*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
use `test`/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
LOAD DATA LOCAL INFILE '<name>' INTO TABLE `db1``; select 'oops!'`.`t``1` FIELDS TERMINATED BY ',' ENCLOSED BY '\'' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a``1`, `b``2`) SET `c``3`= concat('|', "b""a'z", "!")
|
|
/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
DELIMITER ;
|
|
# End of log file
|
|
ROLLBACK /* added by mysqlbinlog */;
|
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1`;
|
|
a`1 b`2 c`3
|
|
fo\o bar |b"a'z!
|
|
connection master;
|
|
DROP TABLE `db1``; select 'oops!'`.`t``1`;
|
|
connection master;
|
|
drop table t1,t2;
|
|
*** Test truncation of long SET expression in LOAD DATA ***
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1000));
|
|
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/file.txt' INTO TABLE t1
|
|
FIELDS TERMINATED BY ','
|
|
(a, @b) SET b = CONCAT(@b, '| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|', @b);
|
|
SELECT * FROM t1 ORDER BY a;
|
|
a b
|
|
1 X| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|X
|
|
2 A| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|A
|
|
show binlog events from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Begin_load_query 1 # ;file_id=#;block_len=8
|
|
master-bin.000001 # Execute_load_query 1 # use `test`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/file.txt' INTO TABLE `t1` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @`b`) SET `b`= CONCAT(@b, '| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|', @b) ;file_id=#
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM t1 ORDER BY a;
|
|
a b
|
|
1 X| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|X
|
|
2 A| 123456789A123456789B123456789C123456789D123456789E123456789F123456789G123456789H123456789I123456789J123456789K123456789L123456789M123456789N123456789O123456789P123456789Q123456789R123456789123456789T123456789U123456789V123456789W123456789X123456789Y123456789Z123456789|A
|
|
connection master;
|
|
DROP TABLE t1;
|
|
*** Test user variables whose names require correct quoting ***
|
|
use `db1``; select 'oops!'`;
|
|
CREATE TABLE t1 (a1 BIGINT PRIMARY KEY, a2 BIGINT, a3 BIGINT, a4 BIGINT UNSIGNED, b DOUBLE, c DECIMAL(65,10), d VARCHAR(100));
|
|
INSERT INTO t1 VALUES (-9223372036854775808,42,9223372036854775807,18446744073709551615,-1234560123456789e110, -1234501234567890123456789012345678901234567890123456789.0123456789, REPEAT("x", 100));
|
|
SELECT @`a``1`:=a1, @`a``2`:=a2, @`a``3`:=a3, @`a``4`:=a4, @`b```:=b, @```c`:=c, @```d```:=d FROM t1;
|
|
@`a``1`:=a1 @`a``2`:=a2 @`a``3`:=a3 @`a``4`:=a4 @`b```:=b @```c`:=c @```d```:=d
|
|
-9223372036854775808 42 9223372036854775807 18446744073709551615 -1.234560123456789e125 -1234501234567890123456789012345678901234567890123456789.0123456789 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
|
INSERT INTO t1 VALUES (@`a``1`+1, @`a``2`*100, @`a``3`-1, @`a``4`-1, @`b```/2, @```c`, substr(@```d```, 2, 98));
|
|
show binlog events from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000001 # Gtid 1 # GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; CREATE TABLE t1 (a1 BIGINT PRIMARY KEY, a2 BIGINT, a3 BIGINT, a4 BIGINT UNSIGNED, b DOUBLE, c DECIMAL(65,10), d VARCHAR(100))
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; INSERT INTO t1 VALUES (-9223372036854775808,42,9223372036854775807,18446744073709551615,-1234560123456789e110, -1234501234567890123456789012345678901234567890123456789.0123456789, REPEAT("x", 100))
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
master-bin.000001 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000001 # User var 1 # @`a``1`=-9223372036854775808
|
|
master-bin.000001 # User var 1 # @`a``2`=42
|
|
master-bin.000001 # User var 1 # @`a``3`=9223372036854775807
|
|
master-bin.000001 # User var 1 # @`a``4`=18446744073709551615
|
|
master-bin.000001 # User var 1 # @`b```=-1.234560123456789e125
|
|
master-bin.000001 # User var 1 # @```c`=-1234501234567890123456789012345678901234567890123456789.0123456789
|
|
master-bin.000001 # User var 1 # @```d```=_latin1 X'78787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878' COLLATE latin1_swedish_ci
|
|
master-bin.000001 # Query 1 # use `db1``; select 'oops!'`; INSERT INTO t1 VALUES (@`a``1`+1, @`a``2`*100, @`a``3`-1, @`a``4`-1, @`b```/2, @```c`, substr(@```d```, 2, 98))
|
|
master-bin.000001 # Query 1 # COMMIT
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
|
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
|
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
|
DELIMITER /*!*/;
|
|
ROLLBACK/*!*/;
|
|
use `db1``; select 'oops!'`/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
SET @@session.pseudo_thread_id=999999999/*!*/;
|
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=0/*!*/;
|
|
SET @@session.sql_mode=0/*!*/;
|
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
|
/*!\C latin1 *//*!*/;
|
|
SET @@session.character_set_client=latin1,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
|
|
SET @@session.lc_time_names=0/*!*/;
|
|
SET @@session.collation_database=DEFAULT/*!*/;
|
|
CREATE TABLE t1 (a1 BIGINT PRIMARY KEY, a2 BIGINT, a3 BIGINT, a4 BIGINT UNSIGNED, b DOUBLE, c DECIMAL(65,10), d VARCHAR(100))
|
|
/*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
INSERT INTO t1 VALUES (-9223372036854775808,42,9223372036854775807,18446744073709551615,-1234560123456789e110, -1234501234567890123456789012345678901234567890123456789.0123456789, REPEAT("x", 100))
|
|
/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
SET @`a``1`:=-9223372036854775808/*!*/;
|
|
SET @`a``2`:=42/*!*/;
|
|
SET @`a``3`:=9223372036854775807/*!*/;
|
|
SET @`a``4`:=18446744073709551615/*!*/;
|
|
SET @`b```:=-1.2345601234568e+125/*!*/;
|
|
SET @```c`:=-1234501234567890123456789012345678901234567890123456789.0123456789/*!*/;
|
|
SET @```d```:=_latin1 X'78787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878' COLLATE `latin1_swedish_ci`/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
INSERT INTO t1 VALUES (@`a``1`+1, @`a``2`*100, @`a``3`-1, @`a``4`-1, @`b```/2, @```c`, substr(@```d```, 2, 98))
|
|
/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
DELIMITER ;
|
|
# End of log file
|
|
ROLLBACK /* added by mysqlbinlog */;
|
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
|
connection slave;
|
|
connection slave;
|
|
SELECT * FROM `db1``; select 'oops!'`.t1 ORDER BY a1;
|
|
a1 a2 a3 a4 b c d
|
|
-9223372036854775808 42 9223372036854775807 18446744073709551615 -1.234560123456789e125 -1234501234567890123456789012345678901234567890123456789.0123456789 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
|
-9223372036854775807 4200 9223372036854775806 18446744073709551614 -6.172800617283945e124 -1234501234567890123456789012345678901234567890123456789.0123456789 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
|
connection master;
|
|
DROP TABLE t1;
|
|
*** Test correct quoting in foreign key error message ***
|
|
use `db1``; select 'oops!'`;
|
|
CREATE TABLE `t``1` ( `a``` INT PRIMARY KEY) ENGINE=innodb;
|
|
CREATE TABLE `t``2` ( `b``` INT PRIMARY KEY, `c``` INT NOT NULL,
|
|
FOREIGN KEY fk (`c```) REFERENCES `t``1`(`a```)) ENGINE=innodb;
|
|
TRUNCATE `t``1`;
|
|
ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`db1``; select 'oops!'`.`t``2`, CONSTRAINT `fk` FOREIGN KEY (`c```) REFERENCES `db1``; select 'oops!'`.`t``1` (`a```))
|
|
DROP TABLE `t``2`;
|
|
DROP TABLE `t``1`;
|
|
*** Test correct quoting of DELETE FROM statement binlogged for HEAP table that is emptied due to server restart
|
|
connection slave;
|
|
include/stop_slave.inc
|
|
connection master;
|
|
CREATE TABLE `db1``; select 'oops!'`.`t``1` (`a``` INT PRIMARY KEY) ENGINE=heap;
|
|
INSERT INTO `db1``; select 'oops!'`.`t``1` VALUES (1), (2), (5);
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1` ORDER BY 1;
|
|
a`
|
|
1
|
|
2
|
|
5
|
|
connection default;
|
|
connection server_1;
|
|
connection master;
|
|
set timestamp=1000000000;
|
|
# The table should be empty on the master.
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1`;
|
|
a`
|
|
# The DELETE statement should be correctly quoted
|
|
show binlog events in 'master-bin.000002' from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000002 # Gtid 1 # GTID #-#-#
|
|
master-bin.000002 # Query 1 # TRUNCATE TABLE `db1``; select 'oops!'`.`t``1`
|
|
connection slave;
|
|
include/start_slave.inc
|
|
connection master;
|
|
connection slave;
|
|
connection slave;
|
|
# The table should be empty on the slave also.
|
|
SELECT * FROM `db1``; select 'oops!'`.`t``1`;
|
|
a`
|
|
connection master;
|
|
DROP TABLE `db1``; select 'oops!'`.`t``1`;
|
|
connection slave;
|
|
connection master;
|
|
use test;
|
|
DROP DATABASE `db1``; select 'oops!'`;
|
|
*** Test correct quoting of mysqlbinlog --rewrite-db option ***
|
|
CREATE TABLE t1 (a INT PRIMARY KEY);
|
|
INSERT INTO t1 VALUES(1);
|
|
show binlog events in 'master-bin.000002' from <binlog_start>;
|
|
Log_name Pos Event_type Server_id End_log_pos Info
|
|
master-bin.000002 # Gtid 1 # BEGIN GTID #-#-#
|
|
master-bin.000002 # Query 1 # use `test`; INSERT INTO t1 VALUES(1)
|
|
master-bin.000002 # Query 1 # COMMIT
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
|
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
|
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
|
DELIMITER /*!*/;
|
|
ROLLBACK/*!*/;
|
|
START TRANSACTION
|
|
/*!*/;
|
|
use `ts``et`/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
SET @@session.pseudo_thread_id=999999999/*!*/;
|
|
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=0/*!*/;
|
|
SET @@session.sql_mode=1411383296/*!*/;
|
|
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
|
|
/*!\C latin1 *//*!*/;
|
|
SET @@session.character_set_client=latin1,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
|
|
SET @@session.lc_time_names=0/*!*/;
|
|
SET @@session.collation_database=DEFAULT/*!*/;
|
|
INSERT INTO t1 VALUES(1)
|
|
/*!*/;
|
|
SET TIMESTAMP=1000000000/*!*/;
|
|
COMMIT
|
|
/*!*/;
|
|
DELIMITER ;
|
|
# End of log file
|
|
ROLLBACK /* added by mysqlbinlog */;
|
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
|
DROP TABLE t1;
|
|
include/rpl_end.inc
|