CREATE TEMPORARY TABLE creates a transaction in binary log on read only server

Hi,

According to Documentation:

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_read_only

Even with read_only enabled, the server permits these operations:

    Updates performed by slave threads, if the server is a replication slave. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.

    Use of ANALYZE TABLE or OPTIMIZE TABLE statements. The purpose of read-only mode is to prevent changes to table structure or contents. Analysis and optimization do not qualify as such changes. This means, for example, that consistency checks on read-only replication slaves can be performed with mysqlcheck --all-databases --analyze.

    Operations on TEMPORARY tables.

So Operations on TEMPORARY tables enabled with read_only.

If it is not related to your report then please provide more information such as, which version you have been used? configuration options both for master and slave server, the error log portions and anything you think will be valuable.

This happens on latest percona server with:

enforce-gtid-consistency
gtid-mode = ON
read_only=ON

All operations executed on slave, so master server do not participate in this session. Problem, that after using temporary table on read_only slave I get new transaction in binary log. Such as:

DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `sometablename`

No other queries gets logged to binary log and this one is autogenerated by server, not executed by user.

Any updates here?

I specifically don't see the reason why the statement "DROP /*!40005 TEMPORARY */ TABLE IF EXISTS..." needs to be logged to the binary log when ROW binlog format is used. So it should potentially be possible to suppress the automatic drop statement when binlog_format is set to ROW. When binlog_format is set to ROW, then temporary table create is not logged to the binary logs.

When ROW binlog_format is used then CREATE statement does not increase the GTID sequence number as the CREATE is not logged to the binary log. However, on connection termination a DROP statement is written to the binary log which increases the GTID sequence number.

```
mysql> set session binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000004 | 433 | | | bedd0818-5f32-11e6-bfa0-0242ac110002:1-7061 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> create temporary table t1_temp_3 as select * from t1;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000004 | 433 | | | bedd0818-5f32-11e6-bfa0-0242ac110002:1-7061 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> Bye
root@4b9ad2a13f3d:/# mysql -proot
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.31-77.0-log Percona Server (GPL), Release 77.0, Revision 5c1061c

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000004 | 602 | | | bedd0818-5f32-11e6-bfa0-0242ac110002:1-7062 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

m...

Steps to reproduce in comment #5 can be reproduced even in upstream MySQL 5.6/5.7, hence marking this as "confirmed". Will need developer comments on this.

http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-usage.html
In MySQL 5.6, you can switch from statement-based to row-based binary logging mode even when temporary tables have been created. However, while using the row-based format, the MySQL server cannot determine the logging mode that was in effect when a given temporary table was created. For this reason, the server in such cases logs a DROP TEMPORARY TABLE IF EXISTS statement for each temporary table that still exists for a given client session when that session ends. While this means that it is possible that an unnecessary DROP TEMPORARY TABLE statement might be logged in some cases, the statement is harmless, and does not cause an error even if the table does not exist, due to the presence of the IF EXISTS option.

Reporters affected by this bug are basically asking if there is something that can be done to avoid errant GTIDs on the slaves.

Actually there is a workaround for errant gtid, you can setup ring topology this way all those errant gtid caused by temporary table creation will be executed on each member of the ring.

https://github.com/percona/percona-server/pull/1184, https://github.com/percona/percona-server/pull/1196

There is another bug related to temporary table handling in read-only mode: https://bugs.launchpad.net/percona-server/+bug/1668602 , not sure if you want to close it as duplicate or not, reproduction is a bit different

The fix for this bug is incomplete, see bug 1671013

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-964