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;
**** Resetting master and slave ****
include/stop_slave.inc
RESET SLAVE;
RESET MASTER;
include/start_slave.inc
CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 (a INT, b INT) ENGINE=Merge;
CREATE TABLE t3 (a INT, b INT) CHARSET=utf8;
CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT, b INT)
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 (a INT, b INT) ENGINE=Merge
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8
**** On Master ****
SHOW CREATE TABLE t1;
Table	t1
Create Table	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW CREATE TABLE t2;
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
SHOW CREATE TABLE t3;
Table	t3
Create Table	CREATE TABLE `t3` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
**** On Slave ****
SHOW CREATE TABLE t1;
Table	t1
Create Table	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
SHOW CREATE TABLE t2;
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
SHOW CREATE TABLE t3;
Table	t3
Create Table	CREATE TABLE `t3` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
CREATE TABLE t5 (b INT, c INT) SELECT * FROM t3;
CREATE TEMPORARY TABLE tt3 (a INT, b INT);
INSERT INTO tt3 VALUES (1,2), (2,4), (3,6), (4,2), (5,10), (6,12);
CREATE TABLE t6 (b INT, c INT) SELECT * FROM tt3;
**** On Master ****
SHOW CREATE TABLE t5;
Table	t5
Create Table	CREATE TABLE `t5` (
  `c` int(11) DEFAULT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT * FROM t5 ORDER BY a,b,c;
c	a	b
SHOW CREATE TABLE t6;
Table	t6
Create Table	CREATE TABLE `t6` (
  `c` int(11) DEFAULT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT * FROM t6 ORDER BY a,b,c;
c	a	b
NULL	1	2
NULL	2	4
NULL	3	6
NULL	4	2
NULL	5	10
NULL	6	12
**** On Slave ****
SHOW CREATE TABLE t5;
Table	t5
Create Table	CREATE TABLE `t5` (
  `c` int(11) DEFAULT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
SELECT * FROM t5 ORDER BY a,b,c;
c	a	b
SHOW CREATE TABLE t6;
Table	t6
Create Table	CREATE TABLE `t6` (
  `c` int(11) DEFAULT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
SELECT * FROM t6 ORDER BY a,b,c;
c	a	b
NULL	1	2
NULL	2	4
NULL	3	6
NULL	4	2
NULL	5	10
NULL	6	12
**** Resetting master and slave ****
include/stop_slave.inc
RESET SLAVE;
RESET MASTER;
include/start_slave.inc
CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3;
ERROR 23000: Duplicate entry '2' for key 'b'
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
CREATE TABLE t7 (a INT, b INT UNIQUE);
INSERT INTO t7 SELECT a,b FROM tt3;
ERROR 23000: Duplicate entry '2' for key 'b'
SELECT * FROM t7 ORDER BY a,b;
a	b
1	2
2	4
3	6
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE)
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t7)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Query	#	#	ROLLBACK
SELECT * FROM t7 ORDER BY a,b;
a	b
1	2
2	4
3	6
**** Resetting master and slave ****
include/stop_slave.inc
RESET SLAVE;
RESET MASTER;
include/start_slave.inc
CREATE TEMPORARY TABLE tt4 (a INT, b INT);
INSERT INTO tt4 VALUES (4,8), (5,10), (6,12);
BEGIN;
INSERT INTO t7 SELECT a,b FROM tt4;
ROLLBACK;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t7)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Query	#	#	COMMIT
SELECT * FROM t7 ORDER BY a,b;
a	b
1	2
2	4
3	6
4	8
5	10
6	12
SELECT * FROM t7 ORDER BY a,b;
a	b
1	2
2	4
3	6
4	8
5	10
6	12
**** Resetting master and slave ****
include/stop_slave.inc
RESET SLAVE;
RESET MASTER;
include/start_slave.inc
CREATE TABLE t8 LIKE t4;
CREATE TABLE t9 LIKE tt4;
CREATE TEMPORARY TABLE tt5 LIKE t4;
CREATE TEMPORARY TABLE tt6 LIKE tt4;
CREATE TEMPORARY TABLE tt7 SELECT 1;
**** On Master ****
SHOW CREATE TABLE t8;
Table	t8
Create Table	CREATE TABLE `t8` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8
SHOW CREATE TABLE t9;
Table	t9
Create Table	CREATE TABLE `t9` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t8 LIKE t4
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE `t9` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
)
**** On Slave ****
SHOW CREATE TABLE t8;
Table	t8
Create Table	CREATE TABLE `t8` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8
SHOW CREATE TABLE t9;
Table	t9
Create Table	CREATE TABLE `t9` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9;
STOP SLAVE;
SET GLOBAL storage_engine=@storage_engine;
START SLAVE;
================ BUG#22864 ================
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;
SET AUTOCOMMIT=0;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2 ENGINE=INNODB SELECT * FROM t1;
ROLLBACK;
CREATE TABLE t3 ENGINE=INNODB SELECT * FROM t1;
INSERT INTO t3 VALUES (4),(5),(6);
ROLLBACK;
CREATE TABLE t4 ENGINE=INNODB SELECT * FROM t1;
INSERT INTO t1 VALUES (4),(5),(6);
ROLLBACK;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
SHOW TABLES;
Tables_in_test
t1
t2
t3
t4
SELECT * FROM t1 ORDER BY a;
a
1
2
3
4
5
6
SELECT * FROM t2 ORDER BY a;
a
1
2
3
SELECT * FROM t3 ORDER BY a;
a
1
2
3
SELECT * FROM t4 ORDER BY a;
a
1
2
3
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT)
master-bin.000001	#	Query	#	#	BEGIN
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
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB
master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE `t3` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB
master-bin.000001	#	Table_map	#	#	table_id: # (test.t3)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE `t4` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB
master-bin.000001	#	Table_map	#	#	table_id: # (test.t4)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	BEGIN
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
SHOW TABLES;
Tables_in_test
t1
t2
t3
t4
SELECT * FROM t1 ORDER BY a;
a
1
2
3
4
5
6
SELECT * FROM t2 ORDER BY a;
a
1
2
3
SELECT * FROM t3 ORDER BY a;
a
1
2
3
SELECT * FROM t4 ORDER BY a;
a
1
2
3
DROP TABLE IF EXISTS t1,t2,t3,t4;
SET AUTOCOMMIT=1;
STOP SLAVE;
RESET SLAVE;
RESET MASTER;
START SLAVE;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2 (a INT) ENGINE=INNODB;
BEGIN;
INSERT INTO t2 SELECT a*a FROM t1;
CREATE TEMPORARY TABLE tt1
SELECT a+1 AS a
FROM t1
WHERE a MOD 2 = 1;
INSERT INTO t2 SELECT a+2 FROM tt1;
COMMIT;
SELECT * FROM t2 ORDER BY a;
a
1
4
4
6
9
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT)
master-bin.000001	#	Query	#	#	BEGIN
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
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 (a INT) ENGINE=INNODB
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
SELECT * FROM t2 ORDER BY a;
a
1
4
4
6
9
TRUNCATE TABLE t2;
**** Resetting master and slave ****
include/stop_slave.inc
RESET SLAVE;
RESET MASTER;
include/start_slave.inc
BEGIN;
INSERT INTO t2 SELECT a*a FROM t1;
CREATE TEMPORARY TABLE tt2
SELECT a+1 AS a
FROM t1
WHERE a MOD 2 = 1;
INSERT INTO t2 SELECT a+2 FROM tt2;
ROLLBACK;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
SELECT * FROM t2 ORDER BY a;
a
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Table_map	#	#	table_id: # (test.t2)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Query	#	#	ROLLBACK
SELECT * FROM t2 ORDER BY a;
a
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(1);
CREATE TABLE t2 (a INT UNIQUE) ENGINE=INNODB SELECT * FROM t1;
ERROR 23000: Duplicate entry '1' for key 'a'
INSERT INTO t1 VALUES (2);
*** the proof of the fix:
select must show that the last insert performed on the slave ***
SELECT * FROM t1;
a
1
1
2
DROP TABLE t1;
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;
DROP DATABASE IF EXISTS mysqltest1;
CREATE DATABASE mysqltest1;
CREATE TABLE mysqltest1.without_select (f1 BIGINT);
CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	DROP DATABASE IF EXISTS mysqltest1
master-bin.000001	#	Query	#	#	CREATE DATABASE mysqltest1
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE mysqltest1.without_select (f1 BIGINT)
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE `mysqltest1`.`with_select` (
  `f1` int(1) NOT NULL DEFAULT '0'
)
master-bin.000001	#	Table_map	#	#	table_id: # (mysqltest1.with_select)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Query	#	#	COMMIT
DROP DATABASE mysqltest1;
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 TEMPORARY TABLE t7(c1 INT);
CREATE TABLE t5(c1 INT);
CREATE TABLE t4(c1 INT);
CREATE VIEW bug48506_t1 AS SELECT 1;
CREATE VIEW bug48506_t2 AS SELECT * FROM t4;
CREATE VIEW bug48506_t3 AS SELECT t5.c1 AS A, t4.c1 AS B FROM t5, t4;
CREATE TABLE bug48506_t4(c1 INT);
DROP VIEW bug48506_t1, bug48506_t2, bug48506_t3;
DROP TABLE bug48506_t4;
CREATE TABLE IF NOT EXISTS bug48506_t1 LIKE t7;
CREATE TABLE IF NOT EXISTS bug48506_t2 LIKE t7;
CREATE TABLE IF NOT EXISTS bug48506_t3 LIKE t7;
CREATE TABLE IF NOT EXISTS bug48506_t4 LIKE t7;
SHOW TABLES LIKE 'bug48506%';
Tables_in_test (bug48506%)
bug48506_t4
DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3;
DROP TEMPORARY TABLES t7;
DROP TABLES t4, t5;
DROP TABLES IF EXISTS bug48506_t4;
CREATE TABLE t1 SELECT 1;
CREATE TABLE IF NOT EXISTS t1 SELECT 1;
Warnings:
Note	1050	Table 't1' already exists
Comparing tables master:test.t1 and slave:test.t1
DROP TABLE t1;
end of the tests