revision-id: bba46cb393edc36fc5e5a783402cce8bc7b2a15b (mariadb-10.3.17-33-gbba46cb393e) parent(s): 130d9490c844d82a7a8613c343a1969c0bec4c62 author: Sujatha committer: Sujatha timestamp: 2019-08-16 12:58:33 +0530 message: MDEV-19632: Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode Analysis and Fix: ================= When SQL_MODE=ORACLE the 'BLOB' type gets translated to 'LONGBLOB'. Because of this change replicated CREATE ... SELECT statement fails on slave in row based replication. The reason being, the source field type is 'BLOB' and destination field type is 'LONGBLOB'. The BLOB type translation issue was fixed as part of MDEV-20263. At present MDEV-19632 adds a replication specific test case to prove replication works fine. --- .../suite/rpl/r/rpl_row_blob_oracle_mode.result | 10 ++++++ .../suite/rpl/t/rpl_row_blob_oracle_mode.test | 36 ++++++++++++++++++++++ 2 files changed, 46 insertions(+) diff --git a/mysql-test/suite/rpl/r/rpl_row_blob_oracle_mode.result b/mysql-test/suite/rpl/r/rpl_row_blob_oracle_mode.result new file mode 100644 index 00000000000..1c697825002 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_blob_oracle_mode.result @@ -0,0 +1,10 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (0); +SET SQL_MODE= 'ORACLE'; +CREATE TABLE t2 SELECT * FROM t1; +connection slave; +connection master; +DROP TABLE t1, t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_blob_oracle_mode.test b/mysql-test/suite/rpl/t/rpl_row_blob_oracle_mode.test new file mode 100644 index 00000000000..f002b99f639 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_blob_oracle_mode.test @@ -0,0 +1,36 @@ +# ==== Purpose ==== +# +# Test verifies that fields of type 'BLOB' are replicated properly in row based +# replication, when SQL_MODE= ORACLE. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Create a table which contains a field of type 'BLOB'. +# 1 - Insert some data into the table. +# 2 - Set SQL_MODE to 'ORACLE'. +# 3 - Execute a CREATE ... SELECT statement to create a new table. +# 4 - Verify that slave is in sync with master. No type conversion errors +# should be reported. +# +# ==== References ==== +# +# MDEV-20263: sql_mode=ORACLE: BLOB(65535) should not translate to LONGBLOB +# MDEV-19632: Replication aborts with ER_SLAVE_CONVERSION_FAILED upon +# CREATE ... SELECT in ORACLE mode +# + +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (0); + +SET SQL_MODE= 'ORACLE'; +CREATE TABLE t2 SELECT * FROM t1; +--sync_slave_with_master + +# Cleanup +--connection master +DROP TABLE t1, t2; +--source include/rpl_end.inc