1include/master-slave.inc
2[connection master]
3connection master;
4CREATE TABLE t4 (
5id INT(5) unsigned NOT NULL auto_increment,
6name varchar(15) NOT NULL default '',
7number varchar(35) NOT NULL default 'default',
8PRIMARY KEY  (id),
9UNIQUE KEY unique_rec (name,number)
10) ENGINE=InnoDB;
11LOAD DATA
12INFILE '../../std_data/loaddata_pair.dat'
13REPLACE INTO TABLE t4
14(name,number);
15SELECT * FROM t4;
16id	name	number
171	XXX	12345
182	XXY	12345
19connection slave;
20SELECT * FROM t4;
21id	name	number
221	XXX	12345
232	XXY	12345
24connection master;
25LOAD DATA
26INFILE '../../std_data/loaddata_pair.dat'
27REPLACE INTO TABLE t4
28(name,number);
29SELECT * FROM t4;
30id	name	number
314	XXX	12345
325	XXY	12345
33connection slave;
34SELECT * FROM t4;
35id	name	number
364	XXX	12345
375	XXY	12345
38connection master;
39connection slave;
40connection master;
41FLUSH LOGS;
42connection slave;
43FLUSH LOGS;
44connection master;
45DROP DATABASE IF EXISTS mysqltest1;
46CREATE DATABASE mysqltest1;
47CREATE TEMPORARY TABLE mysqltest1.tmp (f1 BIGINT);
48CREATE TABLE mysqltest1.t1 (f1 BIGINT) ENGINE="InnoDB";
49SET AUTOCOMMIT = 0;
50connection slave;
51connection slave;
52ALTER TABLE mysqltest1.t1 ENGINE = MyISAM;
53SHOW CREATE TABLE mysqltest1.t1;
54Table	Create Table
55t1	CREATE TABLE `t1` (
56  `f1` bigint(20) DEFAULT NULL
57) ENGINE=MyISAM DEFAULT CHARSET=latin1
58connection master;
59INSERT INTO mysqltest1.t1 SET f1= 1;
60DROP TEMPORARY TABLE mysqltest1.tmp;
61ROLLBACK;
62SHOW CREATE TABLE mysqltest1.tmp;
63ERROR 42S02: Table 'mysqltest1.tmp' doesn't exist
64######### Must return no rows here #########
65SELECT COUNT(*) FROM mysqltest1.t1;
66COUNT(*)
670
68INSERT INTO mysqltest1.t1 SET f1= 2;
69CREATE TEMPORARY TABLE mysqltest1.tmp2(a INT);
70ROLLBACK;
71SHOW CREATE TABLE mysqltest1.tmp2;
72Table	Create Table
73tmp2	CREATE TEMPORARY TABLE `tmp2` (
74  `a` int(11) DEFAULT NULL
75) ENGINE=InnoDB DEFAULT CHARSET=latin1
76######### Must return no rows here #########
77SELECT COUNT(*) FROM mysqltest1.t1;
78COUNT(*)
790
80connection slave;
81connection slave;
82SHOW CREATE TABLE mysqltest1.tmp;
83ERROR 42S02: Table 'mysqltest1.tmp' doesn't exist
84SHOW CREATE TABLE mysqltest1.tmp2;
85ERROR 42S02: Table 'mysqltest1.tmp2' doesn't exist
86######### t1 has two rows here: the transaction not rolled back since t1 uses MyISAM  #########
87SELECT COUNT(*) FROM mysqltest1.t1;
88COUNT(*)
892
90FLUSH LOGS;
91connection master;
92FLUSH LOGS;
93DROP TEMPORARY TABLE IF EXISTS mysqltest1.tmp2;
94DROP DATABASE mysqltest1;
95End of 5.1 tests
96#
97# Bug#39675 rename tables on innodb tables with pending
98# transactions causes slave data issue.
99#
100DROP TABLE IF EXISTS t1;
101DROP TABLE IF EXISTS t2;
102DROP TABLE IF EXISTS t3;
103CREATE TABLE t1 (
104id INT PRIMARY KEY auto_increment,
105b INT DEFAULT NULL
106) ENGINE=InnoDB;
107CREATE TABLE t2 (
108id INT PRIMARY KEY auto_increment,
109b INT DEFAULT NULL
110) ENGINE=InnoDB;
111INSERT INTO t1 (b) VALUES (1),(2),(3);
112BEGIN;
113INSERT INTO t1(b) VALUES (4);
114connection master1;
115RENAME TABLE t1 TO t3, t2 TO t1;;
116connection master;
117COMMIT;
118connection master1;
119connection master;
120SELECT * FROM t1;
121id	b
122SELECT * FROM t3;
123id	b
1241	1
1252	2
1263	3
1274	4
128connection slave;
129connection slave;
130SELECT * FROM t1;
131id	b
132SELECT * FROM t3;
133id	b
1341	1
1352	2
1363	3
1374	4
138connection master;
139DROP TABLE t1;
140DROP TABLE t3;
141End of 6.0 tests
142include/rpl_end.inc
143