Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL)
Description
If "DROP TEMPORARY TABLE..." gets executed on server with GTID enabled and read_only mode enabled, then 'DROP /* TEMPORARY */ TABLE IF EXISTS `sometablename`' gets inserted in server binary log. This creates errant extra transaction on slave and breaks replication.
As temporary tables are never replicated with RBR format so might be good to disable binlogging of temporary tables.
mysql [localhost] {msandbox} (test) > SHOW CREATE PROCEDURE testproc\G
|
*************************** 1. row ***************************
|
Procedure: testproc
|
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
|
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `testproc`()
|
BEGIN
|
DROP TEMPORARY TABLE IF EXISTS `t1_tmp`;
|
DROP TEMPORARY TABLE IF EXISTS `t2_tmp`;
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS `t1_tmp` (
|
`t1` varchar(400) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS `t2_tmp` (
|
`t2` varchar(16) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
END
|
character_set_client: latin1
|
collation_connection: latin1_swedish_ci
|
Database Collation: latin1_swedish_ci
|
1 row in set (0.00 sec)
|
|
|
mysql [localhost] {msandbox} ((none)) > USE test;
|
Database changed
|
mysql [localhost] {msandbox} (test) > SET GLOBAL read_only=1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
mysql [localhost] {msandbox} (test) > CALL testproc();
|
Query OK, 0 rows affected (0.02 sec)
|
|
|
|
|
mysql [localhost] {msandbox} (test) > SHOW BINLOG EVENTS;
|
|
|
+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
|
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
| mysql-bin.000001 | 1167 | Query | 1 | 1306 | use `test`; DROP TEMPORARY TABLE IF EXISTS `t1_tmp` /* generated by server */ |
|
| mysql-bin.000001 | 1306 | Query | 1 | 1445 | use `test`; DROP TEMPORARY TABLE IF EXISTS `t2_tmp` /* generated by server */ |
|
| mysql-bin.000001 | 1445 | Query | 1 | 1648 | use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1_tmp` (
|
`t1` varchar(400) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
|
| mysql-bin.000001 | 1648 | Query | 1 | 1843 | use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t2_tmp` (
|
`t2` varchar(16) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
+------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Attachments
Issue Links
- causes
-
-
- Stalled
-
- duplicates
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
serg, when I agreed on the above proposals from you, I still was not certain of a solution to the support case which I initially believed was caused by connection close. If fact the customer case must be rooted by a new MDEV-20091 that I reported. The latter complains about MDEV-5589 implementation of not logging DROP TEMPORARY TABLE for tables where the CREATE TABLE was not logged feature.
Notice that the MDEV-5589 idea is very close to marking per a table which I thought along
our discussion. So there's is knowledge in every temp table def about its logged status that we can possibly exploit.
Yet I have to prioritize now MDEV-20091 fixing 'cos actually represents the customer case and apparently does not require lots of design consideration.
serg, to earlier comment of yours, incl
to handle tables created before but dropped during read-only, I would suggest not to drop them That is, when a user drops such a temporary table, the server doesn't, and only marks the table as "dropped". The server will pretend a table doesn't exist anymore, but will still keep it around. It'll be dropped later, on disconnect.., and
if a THD is in the RBR-forever mode, log all drops of temporary tables (using DROP TEMPORARY TABLE IF EXISTS syntax)
My question has been (was in removed/edited comments) how/if logging should be implemented for a case like
--connection user
|
set @@binlog_format=STATEMENT; |
CREATE TEMPORARY table tt; # CREATE Logged |
--connection admin
|
set @@global.read_only=1; |
--connection user
|
--disconnect
|
|
We are not going to log such otherwise must-do-log DROP into binlog until (if ever) 
--connection admin
|
set @@global.read_only=0; |
But at this point the connection user has gone. So practically we would have to create a list of deferred-to-log-DROP temp tables which the connection admin would attend as a pre-hook to its main read-write opening action.
It's worth to mention DROP /regular/ TABLE IF EXISTS as a second object to handle if we are going to pursue
no binlogging at read-only principle.
Footnotes:
A similar question when CREATE is issued at having ROW format is answered by MDEV-5589/ MDEV-20091
Work in bb-10.3-monty, f49f37eb2dff02dba19f8b3a4e5b55d65e13c131
Will add the MDEV's this fixed in the final commit before pushing
Coding, documentation and testing
Temporary tables will not be logged in read-only mode. Temporary tables created while the server is in read-only mode will not be logged even after the server has disabled the read-only mode.
okay, an addition:
and, sure, in that approach that we both seem to agree on there will be no temp-table related writes into binlog in read_only mode. At all. So it'll naturally solve the latter case.