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

Bug #1668602 reported by Arturas Moskvinas on 2017-02-28
10
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.6
Fix Released
Medium
Laurynas Biveinis
5.7
Fix Released
Medium
Laurynas Biveinis

Bug Description

If "DROP TEMPORARY TABLE..." gets executed on server with GTID enabled and read_only mode enabled, then 'DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `sometablename`' gets inserted in server binary log. This creates errant transaction, that other slaves in cluster might not have and can break replication if server with errant transaction gets promoted to master and this transaction is already deleted from binary logs.

Percona version tested: 5.6.35-80.0, Debian jessie
How to reproduce:
1. On any read-only server with non-privileged user execute "drop temporary table if exists `sometablename`", no need to actually create temporary table before dropping.
2. Check `show master status`, you'll find new gtid generated on slave in read-only mode

description: updated
description: updated
Sveta Smirnova (svetasmirnova) wrote :

Thank you for the report.

Verified as described.

Sveta Smirnova (svetasmirnova) wrote :
Changed in percona-server:
status: New → Confirmed
tags: added: upstream

To be clear, the bug applies for row/mixed replication. DROP TEMPORARY TABLE still must be logged for statement mode.

Arturas Moskvinas (arturas-w) wrote :

Can you give more context regarding statement mode? It is rather odd that you can "insert" events into replication log on read-only servers which should not be possible without extra privileges or it applies only to writable nodes?

A fix in progress for this relies on the code changes in fix for bug 1539504, and it pointed out that that fix is incomplete: bug 1671013, fixing it first as a prerequisite.

Artūras, my comment applies in general case. I will check whether read_only does the right thing there too

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.