[MDEV-14118] CREATE INDEX on temporary table breaks replication slaves Created: 2017-10-24  Updated: 2020-01-16  Resolved: 2017-10-30

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.2.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Neil Skrypuch Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

CentOS 7.4


Issue Links:
Duplicate
duplicates MDEV-9266 Creating index on temporaray table br... Closed

 Description   

When attempting to create an index on a temporary table, replication on any slaves breaks with a "table does not exist" error. This is easy to reproduce. With a master and slave set up with GTID (not sure if this matters - did not test non-GTID replication) and async replication, run the following statements:

CREATE TEMPORARY TABLE `t3` (`c1` INT, `c2` INT, PRIMARY KEY(`c1`)) ENGINE=InnoDB;
CREATE INDEX `idx1` ON `t3` (`c2`);
DROP TEMPORARY TABLE `t3`;

You will find that replication on the slave has broken with the following error:

Error 'Table 'test.t3' doesn't exist' on query. Default database: 'test'. Query: 'CREATE INDEX `idx1` ON `t3` (`c2`)'

Curiously enough, this only seems to be an issue with CREATE INDEX. This can be observed with the following statements, which do not break replication:

CREATE TEMPORARY TABLE `t3` (`c1` INT, `c2` INT, PRIMARY KEY(`c1`)) ENGINE=InnoDB;
ALTER TABLE `t3` ADD INDEX `idx1` (`c2`);
DROP TEMPORARY TABLE `t3`;

This is especially curious as the manual suggests that CREATE INDEX is merely syntactic sugar for ALTER TABLE.

Ideally, CREATE INDEX would not break replication slaves when used on temporary tables.



 Comments   
Comment by Elena Stepanova [ 2017-10-30 ]

Thanks for the report. We already have one for it, MDEV-9266. I'll add 10.2 to the list of affected versions there.

Comment by Gerardo Malazdrewicz [ 2020-01-15 ]

This does not seem to be the same issue, and reporter may be right about GTID

I found this issue, on 10.3.18 (Debian 10), with this chain of servers:

Master (mysql 5.5) --> Slave1 (MariaDB ) ---gtid replication --> Slave2 (MariaDB).

This issue appeared in Slave2 but NOT in Slave1.

Comment by Gerardo Malazdrewicz [ 2020-01-16 ]

It also happened with "Using_Gtid: No"

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