| 1 |
include/master-slave.inc |
| 2 |
Warnings: |
| 3 |
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. |
| 4 |
Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
| 5 |
[connection master] |
| 6 |
CREATE TABLE t1 (a int); |
| 7 |
CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ); |
| 8 |
CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ); |
| 9 |
CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES (); |
| 10 |
CREATE TEMPORARY TABLE t1_tmp (i1 int); |
| 11 |
ALTER TABLE t1_tmp ADD COLUMN b INT; |
| 12 |
DELETE FROM t2; |
| 13 |
CREATE TEMPORARY TABLE t2_tmp (a int); |
| 14 |
ALTER TABLE t1_tmp ADD COLUMN c INT; |
| 15 |
### assertion: assert that slave has no temp table opened. |
| 16 |
include/sync_slave_sql_with_master.inc |
| 17 |
SHOW STATUS LIKE 'Replica_open_temp_tables'; |
| 18 |
Variable_name Value |
| 19 |
Replica_open_temp_tables 0 |
| 20 |
DROP TABLE t1_tmp, t2; |
| 21 |
INSERT INTO t1 VALUES (1); |
| 22 |
DROP TEMPORARY TABLE t2_tmp; |
| 23 |
INSERT INTO t1 VALUES (2); |
| 24 |
include/sync_slave_sql_with_master.inc |
| 25 |
### assertion: assert that slave has no temporary tables opened |
| 26 |
SHOW STATUS LIKE 'Replica_open_temp_tables'; |
| 27 |
Variable_name Value |
| 28 |
Replica_open_temp_tables 0 |
| 29 |
DROP TABLE t3, t1; |
| 30 |
include/sync_slave_sql_with_master.inc |
| 31 |
include/show_binlog_events.inc |
| 32 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 33 |
slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a int) |
| 34 |
slave-bin.000001 # Query # # use `test`; CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ) |
| 35 |
slave-bin.000001 # Query # # use `test`; CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) ) |
| 36 |
slave-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES () |
| 37 |
slave-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */ |
| 38 |
slave-bin.000001 # Query # # BEGIN |
| 39 |
slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1) |
| 40 |
slave-bin.000001 # Xid # # COMMIT /* XID */ |
| 41 |
slave-bin.000001 # Query # # BEGIN |
| 42 |
slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (2) |
| 43 |
slave-bin.000001 # Xid # # COMMIT /* XID */ |
| 44 |
slave-bin.000001 # Query # # use `test`; DROP TABLE `t3`,`t1` /* generated by server */ |
| 45 |
|
| 46 |
# Bug#55478 Row events wrongly apply on the temporary table of the same name |
| 47 |
# ========================================================================== |
| 48 |
# The statement should be binlogged |
| 49 |
CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB; |
| 50 |
|
| 51 |
# Case 1: CREATE TABLE t1 ... SELECT |
| 52 |
# ---------------------------------- |
| 53 |
|
| 54 |
# The statement generates row events on t1. And the rows events should |
| 55 |
# be inserted into the base table on slave. |
| 56 |
CREATE TABLE t1 ENGINE=MyISAM SELECT rand(); |
| 57 |
include/show_binlog_events.inc |
| 58 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 59 |
master-bin.000001 # Query # # use `test`; CREATE TABLE `t1` ( |
| 60 |
`rand()` double NOT NULL DEFAULT '0' |
| 61 |
) ENGINE=MyISAM |
| 62 |
master-bin.000001 # Query # # BEGIN |
| 63 |
master-bin.000001 # Table_map # # table_id: # (test.t1) |
| 64 |
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 65 |
master-bin.000001 # Query # # COMMIT |
| 66 |
|
| 67 |
# Case 2: DROP TEMPORARY TABLE in a transacation |
| 68 |
# ---------------------------------------------- |
| 69 |
|
| 70 |
BEGIN; |
| 71 |
DROP TEMPORARY TABLE t1; |
| 72 |
# The rows event will binlogged before 'DROP TEMPORARY TABLE t1', |
| 73 |
# as t1 is non-transactional table |
| 74 |
INSERT INTO t1 VALUES(Rand()); |
| 75 |
COMMIT; |
| 76 |
include/show_binlog_events.inc |
| 77 |
Log_name Pos Event_type Server_id End_log_pos Info |
| 78 |
master-bin.000001 # Query # # BEGIN |
| 79 |
master-bin.000001 # Table_map # # table_id: # (test.t1) |
| 80 |
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F |
| 81 |
master-bin.000001 # Query # # COMMIT |
| 82 |
include/sync_slave_sql_with_master.inc |
| 83 |
# Compare the base table. |
| 84 |
include/diff_tables.inc [master:t1, slave:t1] |
| 85 |
|
| 86 |
DROP TABLE t1; |
| 87 |
include/sync_slave_sql_with_master.inc |
| 88 |
include/rpl_end.inc |