1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
CREATE TABLE t4 (
id INT(5) unsigned NOT NULL auto_increment,
name varchar(15) NOT NULL default '',
number varchar(35) NOT NULL default 'default',
PRIMARY KEY (id),
UNIQUE KEY unique_rec (name,number)
) ENGINE=InnoDB;
LOAD DATA
INFILE '../std_data_ln/loaddata_pair.dat'
REPLACE INTO TABLE t4
(name,number);
SELECT * FROM t4;
id name number
1 XXX 12345
2 XXY 12345
SELECT * FROM t4;
id name number
1 XXX 12345
2 XXY 12345
LOAD DATA
INFILE '../std_data_ln/loaddata_pair.dat'
REPLACE INTO TABLE t4
(name,number);
SELECT * FROM t4;
id name number
3 XXX 12345
4 XXY 12345
SELECT * FROM t4;
id name number
3 XXX 12345
4 XXY 12345
FLUSH LOGS;
FLUSH LOGS;
DROP DATABASE IF EXISTS mysqltest1;
CREATE DATABASE mysqltest1;
CREATE TEMPORARY TABLE mysqltest1.tmp (f1 BIGINT);
CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE="InnoDB";
SET AUTOCOMMIT = 0;
-------- switch to slave --------
SHOW CREATE TABLE mysqltest1.t1;
Table Create Table
t1 CREATE TABLE `t1` (
`f1` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
-------- switch to master --------
INSERT INTO mysqltest1.t1 SET f1= 1;
DROP TEMPORARY TABLE mysqltest1.tmp;
ROLLBACK;
SHOW CREATE TABLE mysqltest1.tmp;
ERROR 42S02: Table 'mysqltest1.tmp' doesn't exist
SELECT COUNT(*) FROM mysqltest1.t1;
COUNT(*)
0
INSERT INTO mysqltest1.t1 SET f1= 2;
CREATE TEMPORARY TABLE mysqltest1.tmp2(a INT);
ROLLBACK;
SHOW CREATE TABLE mysqltest1.tmp2;
Table Create Table
tmp2 CREATE TEMPORARY TABLE `tmp2` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT COUNT(*) FROM mysqltest1.t1;
COUNT(*)
0
-------- switch to slave --------
SHOW CREATE TABLE mysqltest1.tmp;
ERROR 42S02: Table 'mysqltest1.tmp' doesn't exist
SHOW CREATE TABLE mysqltest1.tmp2;
ERROR 42S02: Table 'mysqltest1.tmp2' doesn't exist
SELECT COUNT(*) FROM mysqltest1.t1;
COUNT(*)
2
FLUSH LOGS;
SHOW BINLOG EVENTS IN 'slave-bin.000002' LIMIT 1,8;
Log_name Pos Event_type Server_id End_log_pos Info
x x x x x DROP DATABASE IF EXISTS mysqltest1
x x x x x CREATE DATABASE mysqltest1
x x x x x use `test`; CREATE TEMPORARY TABLE mysqltest1.tmp (f1 BIGINT)
x x x x x use `test`; CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE="InnoDB"
x x x x x use `test`; INSERT INTO mysqltest1.t1 SET f1= 1
x x x x x use `test`; DROP TEMPORARY TABLE mysqltest1.tmp
x x x x x use `test`; INSERT INTO mysqltest1.t1 SET f1= 2
x x x x x use `test`; CREATE TEMPORARY TABLE mysqltest1.tmp2(a INT)
-------- switch to master --------
FLUSH LOGS;
SHOW BINLOG EVENTS IN 'master-bin.000002' LIMIT 1,12;
Log_name Pos Event_type Server_id End_log_pos Info
x x x x x DROP DATABASE IF EXISTS mysqltest1
x x x x x CREATE DATABASE mysqltest1
x x x x x use `test`; CREATE TEMPORARY TABLE mysqltest1.tmp (f1 BIGINT)
x x x x x use `test`; CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE="InnoDB"
x x x x x use `test`; BEGIN
x x x x x use `test`; INSERT INTO mysqltest1.t1 SET f1= 1
x x x x x use `test`; DROP TEMPORARY TABLE mysqltest1.tmp
x x x x x use `test`; ROLLBACK
x x x x x use `test`; BEGIN
x x x x x use `test`; INSERT INTO mysqltest1.t1 SET f1= 2
x x x x x use `test`; CREATE TEMPORARY TABLE mysqltest1.tmp2(a INT)
x x x x x use `test`; ROLLBACK
DROP DATABASE mysqltest1;
End of 5.1 tests
|
