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

Preserved unsupported table flags break replication

Details

    Description

      There is old and very questionable logic that when a table is converted from one engine to another, existing table flags are preserved, even if they are unsupported by the new engine. Like so:

      MariaDB [test]> create table t (a int) engine=Aria transactional=1;
      		 
      Query OK, 0 rows affected (0.034 sec)
      		 
       
      		 
      MariaDB [test]> alter table t engine=MyISAM;
      		 
      Query OK, 0 rows affected, 1 warning (0.067 sec)   
      Records: 0  Duplicates: 0  Warnings: 1
      		 
       
      		 
      MariaDB [test]> show warnings;
      		 
      +---------+------+------------------------------------------------------------------------------------+
      		 
      | Level   | Code | Message                                                                            |
      		 
      +---------+------+------------------------------------------------------------------------------------+
      		 
      | Warning | 1478 | Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1' |
      		 
      +---------+------+------------------------------------------------------------------------------------+
      		 
      1 row in set (0.000 sec)
      		 
       
      		 
      MariaDB [test]> show create table t;
      		 
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                                                   |
      		 
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | t     | CREATE TABLE `t` (
      		 
        `a` int(11) DEFAULT NULL
      		 
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 TRANSACTIONAL=1 |
      		 
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      1 row in set (0.000 sec)

      When the statement returned by SHOW CREATE is executed, it throws an error (in the strict mode which is default).

      Complaints have been raised more than once, also by users (e.g. MySQL#67727, MDEV-8733) and support (e.g. MDEV-21387), but they are always rejected on the grounds that the behavior is intentional. I think it's wrong, but for now we have to assume it stays this way, and side-effects have to be dealt with on case by case basis.

      Alternatively, somebody can try to raise the root cause issue again.

      One of such side-effects affects replication, for example in this simple scenario

      --source include/have_binlog_format_row.inc
      		 
      --source include/master-slave.inc
      		 
       
      		 
      create temporary table t1 (a int) engine=Aria transactional=1;
      		 
      alter table t1 engine=MyISAM;
      		 
      create table t like t1;
      		 
       
      		 
      --sync_slave_with_master
      		 
       
      		 
      # Cleanup
      		 
      --connection master
      		 
      drop table t;
      		 
      --source include/rpl_end.inc

      replication fails with

      10.4 30f3db3c

      		 
      2023-07-29  0:47:12 13 [ERROR] Slave SQL: Error 'Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'' on query. Default database: 'test'. Query: 'CREATE TABLE `t` (
      		 
        `a` int(11) DEFAULT NULL
      		 
      ) ENGINE=MyISAM TRANSACTIONAL=1', Gtid 0-1-1, Internal MariaDB error code: 1478

      The reason is that CREATE .. LIKE works on the master side, the strict mode allows it; but since it involves a temporary table, and RBR is in use, for the binary log CREATE .. LIKE is converted into a plain CREATE TABLE statement, with all flags preserved, and fails.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31822 ALTER TABLE ENGINE=x started failing instead of producing warning on unsupported TRANSACTIONAL=1

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8733 Table storage engine conversion does not remove TRANSACTIONAL flags

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21387 Unable to convert Aria Tables to MyISAM after specifying TRANSACTIONAL=0

          • Major - Major loss of function.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Kristian Nielsen made transition -
            Open In Progress
            		490d 22h 48m 1
            Kristian Nielsen made transition -
            In Progress Closed
            		4d 15h 1m 1

            People

              knielsen Kristian Nielsen
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.