[MDEV-17588] replicate-do filters cause errors when creating filtered-out tables on master with syntax unsupported on slave Created: 2018-10-31  Updated: 2020-08-25  Resolved: 2019-07-10

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 5.5, 10.0, 10.1, 10.2.14, 10.2.17, 10.2.18, 10.3.10, 10.2, 10.3, 10.4
Fix Version/s: 10.2.26, 10.1.41, 10.3.17, 10.4.7

Type: Bug Priority: Critical
Reporter: Juan Assignee: Sachin Setiya (Inactive)
Resolution: Fixed Votes: 0
Labels: replication, upstream

Issue Links:
Blocks
is blocked by MDEV-19653 Add class Sql_cmd_create_table Closed
Problem/Incident
causes MDEV-20137 rpl.mdev_17588 fails in buildbot with... Closed
Relates
relates to MDEV-20352 Unstable rpl.mdev_17588 Closed

 Description   

When the spider engine is installed on the master but not on the slave, and replication is filtered with replicate-do-table, DDL on tables excluded by the filter will cause a replication error, so:

Install spider engine on master

 
source /usr/share/mysql/install_spider.sql
 
show master status;

configure slave

 
change master to <the master>;
 
replicate-do-table=test.r1;
 
start slave;
 
show slave status\G

slave status

 
             Slave_IO_Running: Yes
 
            Slave_SQL_Running: Yes
 
 
 
           Replicate_Do_Table: test.r1
 
 
 
               Last_IO_Errno: 0
 
                Last_IO_Error:
 
               Last_SQL_Errno: 0
 
               Last_SQL_Error:
 
 
 
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

create a spider table on the master that should not be replicated on the slave

 
CREATE TABLE `not_r1` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  PRIMARY KEY (`id`)
 
) ENGINE=SPIDER COMMENT='table "not_r1", database "test", port "3306", host "10.0.0.181", user "spider", password "spider"'

slave status

 
             Slave_IO_Running: Yes
 
            Slave_SQL_Running: No
 
 
 
           Replicate_Do_Table: test.r1
 
 
 
                Last_IO_Errno: 0
 
                Last_IO_Error:
 
               Last_SQL_Errno: 1286
 
               Last_SQL_Error: Error 'Unknown storage engine 'spider'' on query. Default database: 'test'. Query: 'create table not_r1(
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  PRIMARY KEY (`id`)
 
) ENGINE=spider
 
COMMENT 'table "not_r1", database "test", port "3306", host "10.0.0.181", user "spider", password "spider"''
 
 
 
      Slave_SQL_Running_State:



 Comments   
Comment by Juan [ 2018-10-31 ]

Tested on 10.2.17 master w 10.2.14 slave per customer set-up as well as with 10.2.18 master & slave and 10.3.10 master & slave.

Comment by Elena Stepanova [ 2018-11-01 ]

Reproducible on all of MariaDB 5.5-10.4 and on MySQL 5.7 (I didn't try other MySQL versions).

Strangely, this use case doesn't seem to be covered even by the extensive section about replication rules in MySQL manual, so I'm not sure if it's the intended behavior, passing it to Elkin for an expert opinion. If it works as designed, please re-target it as Documentation and re-assign to writers.

Test case to demonstrate the problem on MariaDB, run with

--mysqld=--replicate-do-table=db.t2 --mysqld=--sql-mode='NO_ENGINE_SUBSTITUTION'


--source include/master-slave.inc
 
--source include/have_binlog_format_row.inc
 
 
 
set sql_log_bin= 0;
 
install soname 'ha_tokudb';
 
set sql_log_bin= 1;
 
 
 
create table t1 (a int) engine=TokuDB;
 
--sync_slave_with_master
 
show create table t1;

Actual result

 
Replicate_Do_Table	test.t2
 
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	1286
 
Last_Error	Error 'Unknown storage engine 'TokuDB'' on query. Default database: 'test'. Query: 'create table t1 (a int) engine=TokuDB'

Comment by Sachin Setiya (Inactive) [ 2019-01-17 ]

Hi elenst
Thanks for test case.

This trial patch fixes the issue

diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
 
index 0b85b597baf..7a23f5ff4bd 100644
 
--- a/sql/sql_yacc.yy
 
+++ b/sql/sql_yacc.yy
 
@@ -5790,10 +5790,12 @@ storage_engines:
 
               $$= plugin_hton(plugin);
 
             else
 
             {
 
-              if (thd->variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION)
 
+              if (thd->variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION &&
 
+                   !thd->slave_thread)
 
                 my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str));
 
               $$= 0;
 
-              push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
 
+              if (!thd->slave_thread)
 
+                push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
 
                                   ER_UNKNOWN_STORAGE_ENGINE,
 
                                   ER_THD(thd, ER_UNKNOWN_STORAGE_ENGINE),
 
                                   $1.str);

Comment by Sachin Setiya (Inactive) [ 2019-04-23 ]

http://lists.askmonty.org/pipermail/commits/2019-April/013670.html

Comment by Andrei Elkin [ 2019-04-25 ]

Review comments are sent out.

Comment by Andrei Elkin [ 2019-05-17 ]

A poc patch was reviewed to approve the direction. The final one should be checked with
Sergei Voitovich.

Comment by Sachin Setiya (Inactive) [ 2019-05-27 ]

http://lists.askmonty.org/pipermail/commits/2019-May/013807.html (Still in prototype phase )

Comment by Sergey Vojtovich [ 2019-05-29 ]

Please discuss further directions with Andrei.

Generated at Thu Feb 08 08:37:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.