mirror of
https://github.com/MariaDB/server.git
synced 2025-02-15 09:55:33 +01:00
867b53cf4e
The slave replication should accept not supported table options (eg. "transactional" for MyISAM), as such options can end up being set from the master in binlogged CREATE TABLE. This was already handled in report_unknown_option(), which skips the error in slave threads. But in mysql_prepare_create_table_finalize() there was still a warning given, and this warning gets converted into an error when STRICT_(ALL|TRANS)_TABLES. So skip this warning for replication also. Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
304 lines
7.8 KiB
Text
304 lines
7.8 KiB
Text
# Testing table creations for row-based replication.
|
|
|
|
--source include/have_binlog_format_row.inc
|
|
--source include/have_innodb.inc
|
|
--source include/master-slave.inc
|
|
connection slave;
|
|
--source include/have_innodb.inc
|
|
connection master;
|
|
|
|
# Set the default storage engine to different values on master and
|
|
# slave. We need to stop the slave for the server variable to take
|
|
# effect, since the variable is only read on start-up.
|
|
sync_slave_with_master;
|
|
--disable_query_log
|
|
set @@default_storage_engine = @@global.default_storage_engine;
|
|
STOP SLAVE;
|
|
--source include/wait_for_slave_to_stop.inc
|
|
SET GLOBAL default_storage_engine=memory;
|
|
START SLAVE;
|
|
--source include/wait_for_slave_to_start.inc
|
|
--enable_query_log
|
|
|
|
--source include/rpl_reset.inc
|
|
|
|
connection master;
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE OR REPLACE 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;
|
|
--source include/show_binlog_events.inc
|
|
--query_vertical SHOW CREATE TABLE t1
|
|
--query_vertical SHOW CREATE TABLE t2
|
|
--query_vertical SHOW CREATE TABLE t3
|
|
sync_slave_with_master;
|
|
--query_vertical SHOW CREATE TABLE t1
|
|
--query_vertical SHOW CREATE TABLE t2
|
|
--query_vertical SHOW CREATE TABLE t3
|
|
|
|
connection master;
|
|
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;
|
|
--query_vertical SHOW CREATE TABLE t5
|
|
SELECT * FROM t5 ORDER BY a,b,c;
|
|
--query_vertical SHOW CREATE TABLE t6
|
|
SELECT * FROM t6 ORDER BY a,b,c;
|
|
sync_slave_with_master;
|
|
--query_vertical SHOW CREATE TABLE t5
|
|
SELECT * FROM t5 ORDER BY a,b,c;
|
|
--query_vertical SHOW CREATE TABLE t6
|
|
SELECT * FROM t6 ORDER BY a,b,c;
|
|
|
|
--source include/rpl_reset.inc
|
|
|
|
connection master;
|
|
# Test for erroneous constructions
|
|
--error ER_DUP_ENTRY
|
|
CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3;
|
|
# Shouldn't be written to the binary log
|
|
--source include/show_binlog_events.inc
|
|
|
|
# Test that INSERT-SELECT works the same way as for SBR.
|
|
CREATE TABLE t7 (a INT, b INT UNIQUE);
|
|
--error ER_DUP_ENTRY
|
|
INSERT INTO t7 SELECT a,b FROM tt3;
|
|
SELECT * FROM t7 ORDER BY a,b;
|
|
# Should be written to the binary log
|
|
--source include/show_binlog_events.inc
|
|
sync_slave_with_master;
|
|
SELECT * FROM t7 ORDER BY a,b;
|
|
|
|
--source include/rpl_reset.inc
|
|
|
|
connection master;
|
|
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;
|
|
--source include/show_binlog_events.inc
|
|
SELECT * FROM t7 ORDER BY a,b;
|
|
sync_slave_with_master;
|
|
SELECT * FROM t7 ORDER BY a,b;
|
|
|
|
--source include/rpl_reset.inc
|
|
|
|
connection master;
|
|
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;
|
|
--query_vertical SHOW CREATE TABLE t8
|
|
--query_vertical SHOW CREATE TABLE t9
|
|
--source include/show_binlog_events.inc
|
|
sync_slave_with_master;
|
|
--query_vertical SHOW CREATE TABLE t8
|
|
--query_vertical SHOW CREATE TABLE t9
|
|
|
|
connection master;
|
|
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9;
|
|
sync_slave_with_master;
|
|
# Here we reset the value of the default storage engine
|
|
STOP SLAVE;
|
|
--source include/wait_for_slave_to_stop.inc
|
|
SET GLOBAL default_storage_engine=@@default_storage_engine;
|
|
START SLAVE;
|
|
--source include/wait_for_slave_to_start.inc
|
|
--enable_ps_protocol
|
|
|
|
# BUG#22864 (Rollback following CREATE ... SELECT discards 'CREATE
|
|
# table' from log):
|
|
--echo ================ BUG#22864 ================
|
|
|
|
--source include/rpl_reset.inc
|
|
|
|
connection master;
|
|
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;
|
|
|
|
SHOW TABLES;
|
|
SELECT * FROM t1 ORDER BY a;
|
|
SELECT * FROM t2 ORDER BY a;
|
|
SELECT * FROM t3 ORDER BY a;
|
|
SELECT * FROM t4 ORDER BY a;
|
|
--source include/show_binlog_events.inc
|
|
sync_slave_with_master;
|
|
SHOW TABLES;
|
|
SELECT * FROM t1 ORDER BY a;
|
|
SELECT * FROM t2 ORDER BY a;
|
|
SELECT * FROM t3 ORDER BY a;
|
|
SELECT * FROM t4 ORDER BY a;
|
|
|
|
connection master;
|
|
DROP TABLE IF EXISTS t1,t2,t3,t4;
|
|
SET AUTOCOMMIT=1;
|
|
sync_slave_with_master;
|
|
|
|
# Some tests with temporary tables
|
|
--source include/rpl_reset.inc
|
|
|
|
connection master;
|
|
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;
|
|
--source include/show_binlog_events.inc
|
|
sync_slave_with_master;
|
|
SELECT * FROM t2 ORDER BY a;
|
|
|
|
connection master;
|
|
TRUNCATE TABLE t2;
|
|
sync_slave_with_master;
|
|
|
|
--source include/rpl_reset.inc
|
|
|
|
connection master;
|
|
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;
|
|
|
|
SELECT * FROM t2 ORDER BY a;
|
|
source include/show_binlog_events.inc;
|
|
sync_slave_with_master;
|
|
SELECT * FROM t2 ORDER BY a;
|
|
|
|
connection master;
|
|
DROP TABLE t1,t2;
|
|
sync_slave_with_master;
|
|
|
|
#
|
|
# bug#35762 Failing CREATE-SELECT produces bad binlog in row mode
|
|
#
|
|
|
|
connection master;
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
|
INSERT INTO t1 VALUES (1),(1);
|
|
--error ER_DUP_ENTRY
|
|
CREATE TABLE t2 (a INT UNIQUE) ENGINE=INNODB SELECT * FROM t1;
|
|
INSERT INTO t1 VALUES (2);
|
|
|
|
sync_slave_with_master;
|
|
# connection slave;
|
|
|
|
--echo *** the proof of the fix:
|
|
--echo select must show that the last insert performed on the slave ***
|
|
SELECT * FROM t1;
|
|
|
|
connection master;
|
|
DROP TABLE t1;
|
|
sync_slave_with_master;
|
|
|
|
#
|
|
# BUG#34707: Row based replication: slave creates table within wrong database
|
|
#
|
|
|
|
--source include/rpl_reset.inc
|
|
|
|
connection master;
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mysqltest1;
|
|
--enable_warnings
|
|
CREATE DATABASE mysqltest1;
|
|
|
|
CREATE TABLE mysqltest1.without_select (f1 BIGINT);
|
|
CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1;
|
|
source include/show_binlog_events.inc;
|
|
sync_slave_with_master;
|
|
|
|
connection master;
|
|
DROP DATABASE mysqltest1;
|
|
sync_slave_with_master;
|
|
|
|
#
|
|
# BUG#48506: crash in CREATE TABLE <existing_view> IF NOT EXISTS LIKE
|
|
# <tmp_tbl> with RBL
|
|
#
|
|
|
|
--source include/rpl_reset.inc
|
|
|
|
connection master;
|
|
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);
|
|
--disable_warnings
|
|
sync_slave_with_master;
|
|
DROP VIEW bug48506_t1, bug48506_t2, bug48506_t3;
|
|
DROP TABLE bug48506_t4;
|
|
|
|
connection master;
|
|
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;
|
|
--enable_warnings
|
|
sync_slave_with_master;
|
|
|
|
SHOW TABLES LIKE 'bug48506%';
|
|
|
|
connection master;
|
|
DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3;
|
|
DROP TEMPORARY TABLES t7;
|
|
DROP TABLES t4, t5;
|
|
DROP TABLES IF EXISTS bug48506_t4;
|
|
|
|
|
|
--echo *** MDEV-31794: Preserved unsupported table flags break replication
|
|
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=Aria TRANSACTIONAL=1;
|
|
# After ALTER to MyISAM, TRANSACTIONAL=1 is a left-over option normally
|
|
# invalid for MyISAM.
|
|
ALTER TABLE t1 ENGINE=MyISAM;
|
|
# Since row-based binlogging is used, the temporary table t1 is not binlogged,
|
|
# so this CREATE TABLE LIKE is replicated as a plain CREATE TABLE which
|
|
# specifies invalid TRANSACTIONAL=1 for a MyISAM table.
|
|
# Test that the slave will still allow the create table.
|
|
CREATE TABLE t2 LIKE t1;
|
|
--sync_slave_with_master
|
|
SHOW CREATE TABLE t2;
|
|
|
|
--connection master
|
|
DROP TEMPORARY TABLE t1;
|
|
DROP TABLE t2;
|
|
|
|
|
|
--source include/rpl_end.inc
|
|
|
|
--echo end of the tests
|