[MDEV-9266] Creating index on temporaray table breaks replication Created: 2015-12-11  Updated: 2021-11-02  Resolved: 2018-07-18

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.0, 10.1, 10.2
Fix Version/s: 10.0.36, 10.1.35, 10.2.17, 10.3.9

Type: Bug Priority: Critical
Reporter: P.R. Water Assignee: Sachin Setiya (Inactive)
Resolution: Fixed Votes: 7
Labels: upstream-not-affected
Environment:

CentOS Linux release 7.1.1503 (Core)

Issue Links:
Duplicate
is duplicated by MDEV-14118 CREATE INDEX on temporary table break... Closed
is duplicated by MDEV-26948 MariaDB 10.2.10 does not write create... Closed
Relates
relates to MDEV-16728 RENAME temporary table may not find t... Open
Sprint: 10.2.11, 10.2.12, 10.0.34, 10.2.14, 10.1.32

 Description   

Creating an index on temporary table breaks (row based) replication.

On the master execute the following SQL statements:

create temporary table TMP_FOO( bar int not null) engine=memory;
 
create index tmp_idx01 on TMP_FOO(bar);

On the slave we get the following error:

Error 'Table 'mmm_data.TMP_FOO' doesn't exist' on query. Default database: 'mmm_data'. Query: 'create index tmp_idx01 on TMP_FOO(bar)'

Configuration of master:

log-bin                         = mysql-bin
 
server-id                       = 4
 
log_bin_trust_function_creators = 1
 
binlog_format                   = row
 
slave_compressed_protocol       = 1
 
#sync_binlog                    = 1
 
expire_logs_days                = 35
 
binlog-ignore-db                = mmm_temp

Configuration of slave:

server-id                       = 5
 
log_bin_trust_function_creators = 1
 
log-bin                         = myslave2-bin
 
relay-log                       = myslave2-relay-bin
 
binlog_format                   = row
 
log-slave-updates               = 1
 
sync_binlog                     = 1
 
expire_logs_days                = 35
 
slave_compressed_protocol       = 1

Work a round: On the slave set slave-skip-errors in my.cnf:

slave-skip-errors = 1146

Note: The master and slave server run the same OS and MariaDB versions.

 Comments   
Comment by Elena Stepanova [ 2015-12-26 ]

Thanks for the report.
Somehow in 10.x CREATE INDEX for a temporary table ends up in the binary log, while it should not.

Another workaround is to use ALTER TABLE .. ADD INDEX instead of CREATE INDEX.

Comment by Marc [ 2016-04-13 ]

We have also hit this issue with MariaDB 10.1.13, on Red Hat 6 64 bits.

Since the create index on the temporary table is generated by a third party application, we used the variable replication_wild_ignore_table as a workaround.

Any date known for the resolution of this issue?

Regards,

Marc

Comment by Valerii Kravchuk [ 2017-10-06 ]

This is still happens in 10.1.28. Upstream MySQL 5.6.x (checked on 5.6.29) is NOT affected.

Comment by Elena Stepanova [ 2017-10-30 ]

10.2 and 10.3 are also affected.

Comment by Sachin Setiya (Inactive) [ 2018-05-02 ]

A mtr test case

-- source include/have_binlog_format_row.inc
 
-- source include/master-slave.inc
 
 
 
--connection master
 
create temporary table t1(a int);
 
create index index_t1 on t1(a);
 
insert into t1 values(1);
 
 
 
--sync_slave_with_master
 
--error ER_NO_SUCH_TABLE
 
select * from t1;
 
 
 
--connection master
 
drop table t1;
 
 
 
--source include/rpl_end.inc

Comment by Sachin Setiya (Inactive) [ 2018-05-03 ]

http://lists.askmonty.org/pipermail/commits/2018-May/012475.html
http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-mdev-9266

Comment by Sachin Setiya (Inactive) [ 2018-05-04 ]

latest patch http://lists.askmonty.org/pipermail/commits/2018-May/012478.html

Comment by Andrei Elkin [ 2018-05-29 ]

The patch needs to address DROP INDEX as well.

Comment by Andrei Elkin [ 2018-07-11 ]

The rename case was attempted tackling within the current
issue but eventually due to a number of complications I filled a dedicated ticket.

Comment by Andrei Elkin [ 2018-07-11 ]

The ticket returned back to sachin.setiya.007 the author of initial patch after RENAME subtask which was not required by the report is made in its own ticket due to complications potentially involving changes in the parser.

The latest patch is good to push.

Generated at Thu Feb 08 07:33:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.