~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

[connection master]

==== Initialize ====

[on master]

CREATE TABLE t1 (a CHAR(48));

CREATE TEMPORARY TABLE t1_tmp1(a INT);

INSERT INTO t1 VALUES (UUID());

[on slave]

==== Verify results on slave ====

SHOW STATUS LIKE "Slave_open_temp_tables";

Variable_name Value

Slave_open_temp_tables 1

[on master]

[on master1]

[on slave]

==== Verify results on slave ====

SHOW STATUS LIKE "Slave_open_temp_tables";

Variable_name Value

Slave_open_temp_tables 0

==== Clean up ====

[on master]

include/rpl_connect.inc [creating master]

DROP TABLE t1;

[on slave]

include/rpl_reset.inc

CREATE TABLE t1 (a int);

CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );

CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );

CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();

CREATE TEMPORARY TABLE t1_tmp (i1 int);

ALTER TABLE t1_tmp ADD COLUMN b INT;

DELETE FROM t2;

CREATE TEMPORARY TABLE t2_tmp (a int);

ALTER TABLE t1_tmp ADD COLUMN c INT;

### assertion: assert that there is one open temp table on slave

SHOW STATUS LIKE 'Slave_open_temp_tables';

Variable_name Value

Slave_open_temp_tables 1

DROP TABLE t1_tmp, t2;

INSERT INTO t1 VALUES (1);

DROP TEMPORARY TABLE t2_tmp;

INSERT INTO t1 VALUES (2);

### assertion: assert that slave has no temporary tables opened

SHOW STATUS LIKE 'Slave_open_temp_tables';

Variable_name Value

Slave_open_temp_tables 0

DROP TABLE t3, t1;

include/show_binlog_events.inc

Log_name Pos Event_type Server_id End_log_pos Info

slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a int)

slave-bin.000001 # Query # # use `test`; CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) )

slave-bin.000001 # Query # # use `test`; CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) )

slave-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ()

slave-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t1_tmp (i1 int)

slave-bin.000001 # Query # # use `test`; ALTER TABLE t1_tmp ADD COLUMN b INT

slave-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1_tmp` /* generated by server */

slave-bin.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */

slave-bin.000001 # Query # # BEGIN

slave-bin.000001 # Table_map # # table_id: # (test.t1)

slave-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F

slave-bin.000001 # Xid # # COMMIT /* XID */

slave-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t2_tmp` /* generated by server */

slave-bin.000001 # Query # # BEGIN

slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (2)

slave-bin.000001 # Xid # # COMMIT /* XID */

slave-bin.000001 # Query # # use `test`; DROP TABLE `t3`,`t1` /* generated by server */

# Bug#55478 Row events wrongly apply on the temporary table of the same name

# ==========================================================================

# The statement should be binlogged

CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB;

# Case 1: CREATE TABLE t1 ... SELECT

# ----------------------------------

# The statement generates row events on t1. And the rows events should

# be inserted into the base table on slave.

CREATE TABLE t1 ENGINE=MyISAM SELECT rand();

include/show_binlog_events.inc

Log_name Pos Event_type Server_id End_log_pos Info

master-bin.000001 # Query # # BEGIN

master-bin.000001 # Query # # use `test`; CREATE TABLE `t1` (

`rand()` double NOT NULL DEFAULT '0'

) ENGINE=MyISAM

master-bin.000001 # Table_map # # table_id: # (test.t1)

master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F

master-bin.000001 # Query # # COMMIT

# Case 2: DROP TEMPORARY TABLE in a transacation

# ----------------------------------------------

BEGIN;

DROP TEMPORARY TABLE t1;

# The rows event will binlogged before 'DROP TEMPORARY TABLE t1',

# as t1 is non-transactional table

INSERT INTO t1 VALUES(Rand());

100

COMMIT;

101

include/show_binlog_events.inc

102

Log_name Pos Event_type Server_id End_log_pos Info

103

master-bin.000001 # Query # # BEGIN

104

master-bin.000001 # Table_map # # table_id: # (test.t1)

105

master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F

106

master-bin.000001 # Query # # COMMIT

107

master-bin.000001 # Query # # BEGIN

108

master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1` /* generated by server */

109

master-bin.000001 # Query # # COMMIT

110

# Compare the base table.

111

include/diff_tables.inc [master:t1, slave:t1]

112

113

DROP TABLE t1;

114

include/rpl_end.inc

Older »