1include/master-slave.inc 2[connection master] 3connection slave; 4set sql_log_bin=0; 5create database y; 6set sql_log_bin=1; 7connection master; 8drop database if exists mysqltest1; 9drop database if exists x; 10create database mysqltest1; 11set sql_log_bin=0; 12create database x; 13set sql_log_bin=1; 14use mysqltest1; 15create table t1 (a int); 16insert into t1 values(9); 17use x; 18create table t1 (a int); 19insert into t1 values(9); 20select * from mysqltest1.t1; 21a 229 23select * from x.t1; 24a 259 26connection slave; 27show databases like 'mysqltest1'; 28Database (mysqltest1) 29mysqltest1 30select * from test.t1; 31a 329 33select * from y.t1; 34a 359 36connection master; 37use mysqltest1; 38drop table t1; 39drop database mysqltest1; 40connection slave; 41connection slave; 42drop database if exists rewrite; 43create database rewrite; 44connection master; 45use test; 46create table t1 (a date, b date, c date not null, d date); 47load data infile '../../std_data/loaddata1.dat' ignore into table t1 fields terminated by ','; 48Warnings: 49Warning 1265 Data truncated for column 'a' at row 1 50Warning 1265 Data truncated for column 'c' at row 1 51Warning 1265 Data truncated for column 'd' at row 1 52Warning 1265 Data truncated for column 'a' at row 2 53Warning 1265 Data truncated for column 'b' at row 2 54Warning 1265 Data truncated for column 'd' at row 2 55load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES; 56connection slave; 57connection slave; 58select * from rewrite.t1; 59a b c d 600000-00-00 NULL 0000-00-00 0000-00-00 610000-00-00 0000-00-00 0000-00-00 0000-00-00 622003-03-03 2003-03-03 2003-03-03 NULL 632003-03-03 2003-03-03 2003-03-03 NULL 64connection master; 65truncate table t1; 66load data infile '../../std_data/loaddata1.dat' ignore into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d); 67Warnings: 68Warning 1265 Data truncated for column 'c' at row 1 69Warning 1265 Data truncated for column 'd' at row 1 70Warning 1265 Data truncated for column 'b' at row 2 71Warning 1265 Data truncated for column 'd' at row 2 72connection slave; 73connection slave; 74select * from rewrite.t1; 75a b c d 76NULL NULL 0000-00-00 0000-00-00 77NULL 0000-00-00 0000-00-00 0000-00-00 78NULL 2003-03-03 2003-03-03 NULL 79connection master; 80drop table t1; 81create table t1 (a text, b text); 82load data infile '../../std_data/loaddata2.dat' into table t1 fields terminated by ',' enclosed by ''''; 83Warnings: 84Warning 1261 Row 3 doesn't contain data for all columns 85connection slave; 86connection slave; 87select concat('|',a,'|'), concat('|',b,'|') from rewrite.t1; 88concat('|',a,'|') concat('|',b,'|') 89|Field A| |Field B| 90|Field 1| |Field 2' 91Field 3,'Field 4| 92|Field 5' ,'Field 6| NULL 93|Field 6| | 'Field 7'| 94connection master; 95drop table t1; 96create table t1 (a int, b char(10)); 97load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines; 98Warnings: 99Note 1265 Data truncated for column 'a' at row 1 100Note 1265 Data truncated for column 'a' at row 2 101Warning 1366 Incorrect integer value: 'error ' for column `test`.`t1`.`a` at row 3 102Warning 1262 Row 3 was truncated; it contained more data than there were input columns 103Note 1265 Data truncated for column 'a' at row 4 104Warning 1366 Incorrect integer value: 'wrong end ' for column `test`.`t1`.`a` at row 5 105Warning 1262 Row 5 was truncated; it contained more data than there were input columns 106connection slave; 107connection slave; 108select * from rewrite.t1; 109a b 1101 row 1 1112 row 2 1120 1234567890 1133 row 3 1140 1234567890 115connection master; 116truncate table t1; 117load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; 118Warnings: 119Note 1265 Data truncated for column 'a' at row 1 120Note 1265 Data truncated for column 'a' at row 2 121Note 1265 Data truncated for column 'a' at row 3 122Warning 1366 Incorrect integer value: ' 123' for column `test`.`t1`.`a` at row 4 124Warning 1261 Row 4 doesn't contain data for all columns 125connection slave; 126connection slave; 127select * from rewrite.t1; 128a b 1291 row 1 1302 row 2 1313 row 3 1320 133set sql_log_bin= 0; 134drop database rewrite; 135set sql_log_bin= 1; 136connection master; 137set sql_log_bin= 0; 138drop table t1; 139set sql_log_bin= 1; 140 141**** 142**** Bug #46861 Auto-closing of temporary tables broken by replicate-rewrite-db 143**** 144 145**** 146**** Preparing the environment 147**** 148connection master; 149connect con_temp_03,127.0.0.1,root,,test,$MASTER_MYPORT,; 150connect con_temp_02,127.0.0.1,root,,test,$MASTER_MYPORT,; 151connect con_temp_01,127.0.0.1,root,,test,$MASTER_MYPORT,; 152connection master; 153SET sql_log_bin= 0; 154CREATE DATABASE database_master_temp_01; 155CREATE DATABASE database_master_temp_02; 156CREATE DATABASE database_master_temp_03; 157SET sql_log_bin= 1; 158connection slave; 159SET sql_log_bin= 0; 160CREATE DATABASE database_slave_temp_01; 161CREATE DATABASE database_slave_temp_02; 162CREATE DATABASE database_slave_temp_03; 163SET sql_log_bin= 1; 164 165**** 166**** Creating temporary tables on different databases with different connections 167**** 168**** con_temp_01 --> creates 169**** t_01_01_temp on database_master_temp_01 170**** 171**** con_temp_02 --> creates 172**** t_01_01_temp on database_master_temp_01 173**** t_02_01_temp, t_02_02_temp on database_master_temp_02 174**** 175**** con_temp_02 --> creates 176**** t_01_01_temp on database_master_temp_01 177**** t_02_01_temp, t_02_02_temp on database_master_temp_02 178**** t_03_01_temp, t_03_02_temp, t_03_03_temp on database_master_temp_03 179**** 180connection con_temp_01; 181USE database_master_temp_01; 182CREATE TEMPORARY TABLE t_01_01_temp(a int); 183INSERT INTO t_01_01_temp VALUES(1); 184connection con_temp_02; 185USE database_master_temp_01; 186CREATE TEMPORARY TABLE t_01_01_temp(a int); 187INSERT INTO t_01_01_temp VALUES(1); 188USE database_master_temp_02; 189CREATE TEMPORARY TABLE t_02_01_temp(a int); 190INSERT INTO t_02_01_temp VALUES(1); 191CREATE TEMPORARY TABLE t_02_02_temp(a int); 192INSERT INTO t_02_02_temp VALUES(1); 193connection con_temp_03; 194USE database_master_temp_01; 195CREATE TEMPORARY TABLE t_01_01_temp(a int); 196INSERT INTO t_01_01_temp VALUES(1); 197USE database_master_temp_02; 198CREATE TEMPORARY TABLE t_02_01_temp(a int); 199INSERT INTO t_02_01_temp VALUES(1); 200CREATE TEMPORARY TABLE t_02_02_temp(a int); 201INSERT INTO t_02_02_temp VALUES(1); 202USE database_master_temp_03; 203CREATE TEMPORARY TABLE t_03_01_temp(a int); 204INSERT INTO t_03_01_temp VALUES(1); 205CREATE TEMPORARY TABLE t_03_02_temp(a int); 206INSERT INTO t_03_02_temp VALUES(1); 207CREATE TEMPORARY TABLE t_03_03_temp(a int); 208INSERT INTO t_03_03_temp VALUES(1); 209 210**** Dropping the connections 211**** We want to SHOW BINLOG EVENTS, to know what was logged. But there is no 212**** guarantee that logging of the terminated con1 has been done yet.a To be 213**** sure that logging has been done, we use a user lock. 214 215connection master; 216connection slave; 217connection slave; 218show status like 'Slave_open_temp_tables'; 219Variable_name Value 220Slave_open_temp_tables 10 221connection master; 222connection con_temp_01; 223select get_lock("con_01",10); 224get_lock("con_01",10) 2251 226connection master; 227disconnect con_temp_01; 228select get_lock("con_01",10); 229get_lock("con_01",10) 2301 231connection con_temp_02; 232select get_lock("con_02",10); 233get_lock("con_02",10) 2341 235connection master; 236disconnect con_temp_02; 237select get_lock("con_02",10); 238get_lock("con_02",10) 2391 240connection con_temp_03; 241select get_lock("con_03",10); 242get_lock("con_03",10) 2431 244connection master; 245disconnect con_temp_03; 246select get_lock("con_03",10); 247get_lock("con_03",10) 2481 249 250**** Checking the binary log and temporary tables 251 252connection master; 253connection slave; 254connection slave; 255show status like 'Slave_open_temp_tables'; 256Variable_name Value 257Slave_open_temp_tables 0 258connection master; 259include/show_binlog_events.inc 260Log_name Pos Event_type Server_id End_log_pos Info 261master-bin.000001 # Gtid # # GTID #-#-# 262master-bin.000001 # Query # # use `database_master_temp_01`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_01_01_temp` 263master-bin.000001 # Gtid # # GTID #-#-# 264master-bin.000001 # Query # # use `database_master_temp_02`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_02_02_temp`,`t_02_01_temp` 265master-bin.000001 # Gtid # # GTID #-#-# 266master-bin.000001 # Query # # use `database_master_temp_01`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_01_01_temp` 267master-bin.000001 # Gtid # # GTID #-#-# 268master-bin.000001 # Query # # use `database_master_temp_03`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_03_03_temp`,`t_03_02_temp`,`t_03_01_temp` 269master-bin.000001 # Gtid # # GTID #-#-# 270master-bin.000001 # Query # # use `database_master_temp_02`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_02_02_temp`,`t_02_01_temp` 271master-bin.000001 # Gtid # # GTID #-#-# 272master-bin.000001 # Query # # use `database_master_temp_01`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_01_01_temp` 273**** 274**** Cleaning up the test case 275**** 276connection master; 277SET sql_log_bin= 0; 278DROP DATABASE database_master_temp_01; 279DROP DATABASE database_master_temp_02; 280DROP DATABASE database_master_temp_03; 281DROP DATABASE x; 282SET sql_log_bin= 1; 283connection slave; 284SET sql_log_bin= 0; 285DROP DATABASE database_slave_temp_01; 286DROP DATABASE database_slave_temp_02; 287DROP DATABASE database_slave_temp_03; 288DROP DATABASE y; 289SET sql_log_bin= 1; 290connection master; 291connection slave; 292include/rpl_end.inc 293