Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20242

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

    XMLWordPrintable

Details

    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:

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              IMP IMP
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.