[MDEV-9266] Creating index on temporaray table breaks replication - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.0.36, 10.1.35, 10.2.17, 10.3.9
Component/s: Replication
Labels:
- upstream-not-affected
Environment:
CentOS Linux release 7.1.1503 (Core)

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.

Attachments

Issue Links

is duplicated by

MDEV-14118 CREATE INDEX on temporary table breaks replication slaves

Closed

MDEV-26948 MariaDB 10.2.10 does not write create temporary table into binary log in mixed mode

Closed

relates to

MDEV-16728 RENAME temporary table may not find the target table on slave in RBR

Open

Activity

Ascending order - Click to sort in descending order

P.R. Water created issue - 2015-12-11 14:24

Elena Stepanova made changes - 2015-12-26 20:50

Field	Original Value	New Value
Status	Open [ 1 ]	Confirmed [ 10101 ]

Elena Stepanova added a comment - 2015-12-26 20:51

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.

Elena Stepanova added a comment - 2015-12-26 20:51 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 .

Elena Stepanova made changes - 2015-12-26 20:51

Fix Version/s		10.0 [ 16000 ]
Fix Version/s		10.1 [ 16100 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s	10.0.22-galera [ 19801 ]
Assignee		Kristian Nielsen [ knielsen ]

Marc added a comment - 2016-04-13 14:22 - edited

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

Marc added a comment - 2016-04-13 14:22 - edited 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

Valerii Kravchuk added a comment - 2017-10-06 09:03

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

Valerii Kravchuk added a comment - 2017-10-06 09:03 This is still happens in 10.1.28. Upstream MySQL 5.6.x (checked on 5.6.29) is NOT affected.

Valerii Kravchuk made changes - 2017-10-06 09:03

Labels

upstream-not-affected

Elena Stepanova made changes - 2017-10-30 16:53

Link

This issue is duplicated by ~~MDEV-14118~~ [ ~~MDEV-14118~~ ]

Elena Stepanova made changes - 2017-10-30 16:54

Fix Version/s		10.2 [ 14601 ]
Affects Version/s		10.2 [ 14601 ]
Assignee	Kristian Nielsen [ knielsen ]	Andrei Elkin [ elkin ]

Elena Stepanova added a comment - 2017-10-30 16:54

10.2 and 10.3 are also affected.

Elena Stepanova added a comment - 2017-10-30 16:54 10.2 and 10.3 are also affected.

Sergei Golubchik made changes - 2017-11-09 18:23

Sprint

10.2.11 [ 203 ]

Valerii Kravchuk made changes - 2017-12-15 11:21

Priority

Major [ 3 ]

Critical [ 2 ]

Andrei Elkin made changes - 2017-12-20 14:51

Sprint

10.2.11 [ 203 ]

10.2.11, 10.2.12 [ 203, 216 ]

Sergei Golubchik made changes - 2018-01-17 16:35

Sprint

10.2.11, 10.2.12 [ 203, 216 ]

10.2.11, 10.2.12, 10.0.34 [ 203, 216, 224 ]

Andrei Elkin made changes - 2018-03-07 18:54

Sprint

10.2.11, 10.2.12, 10.0.34 [ 203, 216, 224 ]

10.2.11, 10.2.12, 10.0.34, 10.2.14 [ 203, 216, 224, 229 ]

Andrei Elkin made changes - 2018-03-14 17:40

Sprint

10.2.11, 10.2.12, 10.0.34, 10.2.14 [ 203, 216, 224, 229 ]

10.2.11, 10.2.12, 10.0.34, 10.2.14, 10.1.32 [ 203, 216, 224, 229, 235 ]

Andrei Elkin made changes - 2018-05-02 12:38

Assignee

Andrei Elkin [ elkin ]

Sachin Setiya [ sachin.setiya.007 ]

Sachin Setiya (Inactive) added a comment - 2018-05-02 16:03 - edited

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

Sachin Setiya (Inactive) added a comment - 2018-05-02 16:03 - edited 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

Sachin Setiya (Inactive) made changes - 2018-05-03 05:56

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sachin Setiya (Inactive) added a comment - 2018-05-03 05:56 - edited

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

Sachin Setiya (Inactive) added a comment - 2018-05-03 05:56 - edited http://lists.askmonty.org/pipermail/commits/2018-May/012475.html http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-mdev-9266

Sachin Setiya (Inactive) made changes - 2018-05-03 05:56

Assignee	Sachin Setiya [ sachin.setiya.007 ]	Andrei Elkin [ elkin ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sachin Setiya (Inactive) added a comment - 2018-05-04 12:34

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

Sachin Setiya (Inactive) added a comment - 2018-05-04 12:34 latest patch http://lists.askmonty.org/pipermail/commits/2018-May/012478.html

Andrei Elkin added a comment - 2018-05-29 17:14

The patch needs to address DROP INDEX as well.

Andrei Elkin added a comment - 2018-05-29 17:14 The patch needs to address DROP INDEX as well.

Andrei Elkin made changes - 2018-05-29 17:14

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Andrei Elkin made changes - 2018-06-07 09:17

Assignee

Andrei Elkin [ elkin ]

Sachin Setiya [ sachin.setiya.007 ]

Julien Fritsch made changes - 2018-06-15 17:25

Assignee

Sachin Setiya [ sachin.setiya.007 ]

Sergei Golubchik [ serg ]

Julien Fritsch made changes - 2018-06-18 08:23

Assignee

Sergei Golubchik [ serg ]

Andrei Elkin [ elkin ]

Andrei Elkin made changes - 2018-06-19 17:26

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Julien Fritsch made changes - 2018-06-21 10:20

Fix Version/s

10.3 [ 22126 ]

Andrei Elkin made changes - 2018-07-11 11:33

Link

This issue relates to MDEV-16728 [ MDEV-16728 ]

Andrei Elkin added a comment - 2018-07-11 11:33

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

Andrei Elkin added a comment - 2018-07-11 11:33 The rename case was attempted tackling within the current issue but eventually due to a number of complications I filled a dedicated ticket.

Andrei Elkin made changes - 2018-07-11 14:06

Assignee

Andrei Elkin [ elkin ]

Sachin Setiya [ sachin.setiya.007 ]

Andrei Elkin added a comment - 2018-07-11 14:09

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.

Andrei Elkin added a comment - 2018-07-11 14:09 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.

Sachin Setiya (Inactive) made changes - 2018-07-18 11:58

Fix Version/s		10.0.36 [ 22916 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sachin Setiya (Inactive) made changes - 2018-07-18 14:31

Fix Version/s		10.1.35 [ 23116 ]
Fix Version/s		10.2.17 [ 23111 ]
Fix Version/s		10.3.9 [ 23114 ]

Alice Sherepa made changes - 2021-11-02 10:28

Link

This issue is duplicated by ~~MDEV-26948~~ [ ~~MDEV-26948~~ ]

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 73123 ]

MariaDB v4 [ 149903 ]

Jira Automation (IT) made changes - 2024-07-04 09:03

Zendesk Related Tickets

181796 187321 158732 182166 180786 200409

People

Assignee:: Sachin Setiya (Inactive)

Reporter:: P.R. Water

Votes:: 7 Vote for this issue

Watchers:: 14 Start watching this issue

Dates

Created:: 2015-12-11 14:24

Updated:: 2024-07-08 01:13

Resolved:: 2018-07-18 11:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration