[MDEV-20242] DROP TEMPORARY TABLE creates a transaction in binary log on read only server Created: 2019-08-02  Updated: 2019-08-04  Resolved: 2019-08-04

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Replication
Affects Version/s: 10.2.26, 10.1.41, 10.3.17, 10.4.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: IMP Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-17863 DROP TEMPORARY TABLE creates a transa... Closed

 Description   

Even if an instance is running with read_only=1 a query DROP TEMPORARY TABLE IF EXISTS from non-privileges user is logged into binary logs, causing problems on slaves during failover.

> SELECT @@read_only;
+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
 
> DROP TEMPORARY TABLE IF EXISTS i_do_no_exist;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1051
Message: Unknown table 'test.i_do_no_exist'
1 row in set (0.00 sec)
 
> SHOW BINLOG EVENTS;
+------------------+------+-------------------+-----------+-------------+-------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type        | Server_id | End_log_pos | Info                                                                                      |
+------------------+------+-------------------+-----------+-------------+-------------------------------------------------------------------------------------------+
                                                               ...
| mysql-bin.000001 | 1173 | Gtid              |       101 |        1211 | GTID 0-101-5                                                                              |
| mysql-bin.000001 | 1211 | Query             |       101 |        1353 | DROP TEMPORARY TABLE IF EXISTS `test`.`i_do_no_exist` /* generated by server */           |
+------------------+------+-------------------+-----------+-------------+-------------------------------------------------------------------------------------------+
15 rows in set (0.01 sec)

The bug is also reported in Oracle's MySQL: https://bugs.mysql.com/bug.php?id=85258
as well as in Percona Server: https://jira.percona.com/browse/PS-1785.

The fixes from Percona Server could likely be (easily) applied to the MariaDB server:


Generated at Thu Feb 08 08:57:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.