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

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
Fix Version/s: 10.5, 10.6
Component/s: Data Definition - Temporary, Replication
Labels:
None

Description

In RBR operations on a tmp table are not binlogged, expect of currently
the only case of

DROP TEMPORARY TABLE IF EXISTS `tmp_table`

which is motivated by a possibility of `some_name`
existing on slave having arrived at slave at times before RBR mode has
been set.
[Since the existence is merely potential IF EXISTS is specified into
the above logged query].

Not being logged in RBR
RENAME TABLE `tmp_table_old` to `tmp_table_new`
can make master and slave inconsistent similarly to the above DROP
of potentially existing `tmp_table_old` on slave.
After RENAME on master `tmp_table_old` will no longer exist, and
should the "new" renamed table be deleted the following

DROP TEMPORARY TABLE IF EXISTS `tmp_table_new` /* renamed table */

would be replicated not to find the target table on the slave and thus an orphan
"old" table will remain there uncleaned forever.

A way to fix this issue requires either add
'IF EXISTS' keyword to 'RENAME' sql command or
provide an proper execution context within the replicated query so that
slave execution would simulate 'IF EXISTS'.

A multi-table RENAME (of list-of-table-pairs) needs some attention to prevent
renaming of a base "namesake" table instead of an aimed temporary one.
Perhaps RENAME TEMPORARY TABLE syntax could be introduced and used
to replicate the temporary table(s) RENAME separately (practically to extract the temporary table parts from a RENAME query to construct two Query-log-events).

Attachments

Issue Links

is duplicated by

MDEV-16909 RBR aborts with ER_NO_SUCH_TABLE upon RENAME on temporary table

Stalled

relates to

MDEV-9266 Creating index on temporaray table breaks replication

Closed

MDEV-25587 RENAME on namesake of temporary table breaks binlog replay

Open

Activity

Elena Stepanova added a comment - 2019-05-31 17:24 - edited

See some test cases in MDEV-16909, those cause replication abort with ER_TABLE_EXISTS_ERROR (Table '...' already exists) and ER_NO_SUCH_TABLE (Table '...' doesn't exist).

Here is another one, a little tricker, but still the general area:

--source include/have_binlog_format_mixed.inc

--source include/master-slave.inc

CREATE TABLE t1 (a INT);

CREATE TEMPORARY TABLE t1 (b INT);

SET BINLOG_FORMAT= ROW;

ALTER TABLE t1 RENAME TO tmp;

ALTER TABLE t1 ALGORITHM=INPLACE, MODIFY COLUMN IF EXISTS x BIGINT AFTER xx;

--sync_slave_with_master

# Cleanup

--connection master

DROP TABLE t1;

--source include/rpl_end.inc

10.4 f98bb231
2019-05-31 20:21:18 13 [ERROR] Slave SQL: Error 'ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY' on query. Default database: 'test'. Query: 'ALTER TABLE t1 ALGORITHM=INPLACE, MODIFY COLUMN IF EXISTS x BIGINT AFTER xx', Gtid 0-1-3, Internal MariaDB error code: 1845
2019-05-31 20:21:18 13 [Warning] Slave: Unknown column 'x' in 't1' Error_code: 1054
2019-05-31 20:21:18 13 [Warning] Slave: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY Error_code: 1845
2019-05-31 20:21:18 13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 603

Here CREATE TEMPORARY TABLE is written into the binary log, but RENAME is not, so the slave thinks it's still working with a temporary table, hence the error.
Remember that the switch to row-based format is not necessarily manual, there can be a number of reasons which would make the server switch to RBR between CREATE and RENAME.

Elena Stepanova added a comment - 2019-05-31 17:24 - edited See some test cases in MDEV-16909 , those cause replication abort with ER_TABLE_EXISTS_ERROR (Table '...' already exists) and ER_NO_SUCH_TABLE (Table '...' doesn't exist) . Here is another one, a little tricker, but still the general area: --source include/have_binlog_format_mixed.inc --source include/master-slave.inc CREATE TABLE t1 (a INT ); CREATE TEMPORARY TABLE t1 (b INT ); SET BINLOG_FORMAT= ROW; ALTER TABLE t1 RENAME TO tmp; ALTER TABLE t1 ALGORITHM=INPLACE, MODIFY COLUMN IF EXISTS x BIGINT AFTER xx; --sync_slave_with_master # Cleanup --connection master DROP TABLE t1; --source include/rpl_end.inc 10.4 f98bb231 2019-05-31 20:21:18 13 [ERROR] Slave SQL: Error 'ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY' on query. Default database: 'test'. Query: 'ALTER TABLE t1 ALGORITHM=INPLACE, MODIFY COLUMN IF EXISTS x BIGINT AFTER xx', Gtid 0-1-3, Internal MariaDB error code: 1845 2019-05-31 20:21:18 13 [Warning] Slave: Unknown column 'x' in 't1' Error_code: 1054 2019-05-31 20:21:18 13 [Warning] Slave: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY Error_code: 1845 2019-05-31 20:21:18 13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 603 Here CREATE TEMPORARY TABLE is written into the binary log, but RENAME is not, so the slave thinks it's still working with a temporary table, hence the error. Remember that the switch to row-based format is not necessarily manual, there can be a number of reasons which would make the server switch to RBR between CREATE and RENAME.

People

Assignee:: Andrei Elkin

Reporter:: Andrei Elkin

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2018-07-11 11:31

Updated:: 2024-09-10 15:08

Git Integration

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

MariaDB Server