WL#5344: Testing binlogging of operations on temporary tables
Affects: Server-5.5
—
Status: Complete
CONTEXT ======= In this test case, we check how changes to temporary tables are written to the binary log. RELATED BUGS ============ BUG#53259.
Changing Temporary Tables
=========================
Temporary tables that are changed, i.e.,
(1) created by a CREATE TEMPORARY, CREATE TEMPORARY SELECT or CREATE LIKE;
(2) dropped by a DROP;
(3) dropped by a DROP TEMPORARY;
(4) have a row inserted, deleted or updated;
are subjected to the following rules regarding what is written to the
binary log.
RULES
=====
If there is an on-going transaction, changes to temporary tables are written to
the binary log according to the type of the engine in use, until something is
written to the transactional cache. After that, any change is kept in the
context of the on-going transaction. To easy the presentation, in what follows,
we assume that something was already written to the transactional cache.
(1) If the format is ROW:
(1.1) DROP TEMPORARY TABLE is written to the binary log with an IF
EXISTS appended to the statement if a temporary table is dropped.
(1.2) DROP TEMPORARY TABLE returns an error, i.e. table does not
exist, if we try to drop a regular table.
(1.3) Any DROP that affects a temporary table has the temporary table
logged as part of a DROP TEMPORARY TABLE IF EXISTS.
(1.4) Other tables, if there is any, are written to the binary log
as part of the DROP TABLE.
(1.5) DROP is written to the binary log as a single statement because
it commits any on-going transaction.
(1.6) Other changes to temporary tables are not written to the binary log.
(2) If the format is STATEMENT, changes to temporary tables are written to
the binary log as follows:
(2.1) INSERTS, UPDATES and DELETES are kept in the context of any on-going
transaction and wrapped by a COMMIT/ROLLBACK.
(2.2) CREATE TEMPORARY, CREATE TEMPORARY SELECT, CREATE LIKE and DROP
TEMPORARY TABLE are kept in the context of any on-going transaction if
there is one.
(2.3) Otherwise, such commands are written as single statements.
(2.4) There is no special procedure for DROP TEMPORARY TABLE [IF EXISTS].
(2.5) DROP TEMPORARY TABLE returns an error, i.e. table does not exist,
if we try to drop a regular table.
(2.6) DROP is written to the binary log as a single statement because
it commits any on-going transaction.
(3) If the format is MIXED and the execution switched from statements to
rows due to any reason, the system behaves as in the ROW format.
(4) If the format is MIXED and the execution did not switch from statements to
rows, the system behaves as in the STATEMENT format.
REMARKS:
========
. In (1.1), a statement written to the binary log with an appended IF EXISTS,
because a temporary table "could" be created and replicated when a different
format was in use.
. In (1.3), any DROP that affects a temporary table has the drop re-written as
DROP TEMPORARY IF EXISTS and DROP because the IF EXISTS needed to be appended to
the DROP TEMPORARY.
. DROP TEMPORARY TABLE, when the table is regular, raises an error as the table
does not exist.
. DROP TEMPORARY TABLE, when the table is temporary, works fine.
EXPECTED RESULTS:
=================
So we verify if DROP TEMPORARY works correctly by dropping different types of
tables and checking if the statement is written to the binary log as expected:
DROP TEMPORARY TABLE:
---------------------
(STATEMENT)
Temp T-Table Temp N-Table Xe
Temp T-Table k k k with error
Temp N-Table k k k with error
Xe k with error k with error -
T-table k with error k with error -
N-table k with error k with error -
(ROW)
Temp T-Table Temp N-Table Xe
Temp T-Table k with IF k with IF k with IF and
Temp T-Table
Temp N-Table k with IF k with IF k with IF and
Temp N-Table
Xe k with IF and k with IF and -
Temp T-Table Temp N-Table
T-Table k with IF and k with IF and -
Temp T-Table Temp N-Table
DROP TEMPORARY TABLE IF EXISTS:
-------------------------------
(STATEMENT)
Temp T-table Temp N-Table Xe
Temp T-Table k k k
Temp N-Table k k k
Xe k k k
(ROW)
Temp T-Table Temp N-Table Xe
Temp T-Table k with IF k with IF k with IF and
Temp T-Table
Temp N-Table k with IF k with IF k with IF and
Temp N-Table
Xe k with IF and k with IF and -
Temp T-Table Temp N-Table
T-Table k with IF and k with IF and -
Temp T-Table Temp N-Table
DROP TABLE:
-----------
(STATEMENT)
Temp T-Table Temp N-Table Xe
Temp T-Table y y y with error
Temp N-Table y y y with error
Xe y with error y with error -
T-Table y y y with error
N-Table y y y with error
(ROW)
Temp T-Table Temp N-Table Xe
Temp T-Table k with IF k with IF . k with IF and
Temp T-Table
. y with error
and Xe
Temp N-Table k with IF k with IF . k with IF and
Temp N-Table
. y with error
and Xe
Xe . k with IF and . k with IF and -
Temp T-Table Temp N-Table
. y with error . y with error
and Xe and Xe
T-Table . k with IF and . k with IF and y with error
Temp T-Table Temp N-Table
. y and T-Table . y and T-Table
N-Table . k with IF and . k with IF and y with error
Temp T-Table Temp N-Table
. y and N-Table . y and N-Table
DROP TABLE IF EXISTS:
---------------------
(STATEMENT)
Temp T-Table Temp N-Table Xe
Temp T-Table y y y
Temp N-Table y y y
Xe y y -
T-Table y y y
N-Table y y y
(ROW)
Temp T-Table Temp N-Table Xe
Temp T-Table k with IF k with IF . k with IF and
Temp T-Table
. y and Xe
Temp N-Table k with IF k with IF . k with IF and
Temp N-Table
. y and Xe
Xe . k with IF and . k with IF and y
Temp T-Table Temp N-Table
. y and Xe . y and Xe
T-Table . k with IF and . k with IF and y
Temp T-Table Temp N-Table
. y and T-Table . y and T-Table
N-Table . k with IF and . k with IF and y
Temp T-Table Temp N-Table
. y and N-Table . y and N-Table
LEGEND:
-------
. k - DROP TEMPORARY [IF EXISTS]
. y - DROP [IF EXISTS]
TEST CASES:
===========
We should add the following files:
. an engine to processes commands involving temporary tables in order to ease
the creation of different permutations.
mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.inc
. definition of the permutations:
mysql-test/extra/rpl_tests/rpl_drop_create_temp_table.test
. test cases that call the rpl_drop_create_temp_table.test for the different
logging formats:
mysql-test/suite/rpl/t/rpl_mixed_drop_create_temp_table.test
mysql-test/suite/rpl/t/rpl_row_drop_create_temp_table.test
mysql-test/suite/rpl/t/rpl_stm_drop_create_temp_table.test
. their result files:
mysql-test/suite/rpl/r/rpl_mixed_drop_create_temp_table.result
mysql-test/suite/rpl/r/rpl_row_drop_create_temp_table.result
mysql-test/suite/rpl/r/rpl_stm_drop_create_temp_table.result
We should remove the following tests that were temporarily introduced while this
worklog was not ready:
mysql-test/suite/rpl/t/rpl_temp_temporary.test
mysql-test/suite/rpl/t/rpl_temp_temporary-master.opt
mysql-test/suite/rpl/r/rpl_temp_temporary.result
The engine allows the following commands:
B - Begin
C - Commit
R - Rollback
T - Inserts a row into a T-table
N-Temp - Inserts a row into a temporary N-table.
T-SELECT-N-Temp - Selects from a temporary N-table and inserts
into a T-table.
N-SELECT-N-Temp - Selects from a temporary N-table and inserts
into a N-table.
T-SELECT-T-Temp - Selects from a temporary T-table and inserts
into a T-table.
N-SELECT-T-Temp - Selects from a temporary T-table and inserts
into a N-table.
Create-N-Temp - Creates a temporary N-table if a temporary N-table
was dropped before
Create-T-Temp - Creates a temporary T-table if a temporary T-table
was dropped before
Drop-Temp-T-Temp - Drops a temporary T-table if there is any
Drop-Temp-N-Temp - Drops a temporary N-table if there is any
Drop-Temp-TN-Temp - Drops both a temporary T-table and N-table if there
is any
Drop-Temp-TT-Temp - Drops two temporary T-tables if there is any
Drop-Temp-NN-Temp - Drops two temporary N-tables if there is any
Drop-Temp-Xe-Temp - Tries to drop a temporary table that does not exist
Drop-Temp-NXe-Temp - Drops a temporary N-table if there is any and
a temporary table that does not exist
Drop-Temp-TXe-Temp - Drops a temporary T-table if there is any and
a temporary table that does not exist
Drop-Temp-If-Xe-Temp - Tries to drop a temporary table that does not exist
Drop-Temp-If-TXe-Temp - Drops a temporary T-table if there is any and
a temporary table that does not exist
Drop-T - Drops a T-table if there is any
Drop-N - Drops a N-table if there is any
Drop-Xe - Tries to drop a table that does not exist
Drop-TXe - Drops a T-table if there is any and a table that does
not exist
Drop-NXe - Drops a N-table if there is any and a table that does
not exist
Drop-TN - Drops both a T-table and N-table if there is any
Drop-TT - Drops two T-tables if there is any
Drop-NN - Drops two N-tables if there is any
Drop-N-TN-Temp - Drops a N-table and both a temporary T-table and
N-table if there is any
Drop-If-Xe - Tries to drop a table that does not exist
Drop-If-TXe - Drops a T-table if there is any and a table that does
not exist
Drop-If-NXe - Drops a N-table if there is any and a table that does
not exist
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.