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

          Activity

            likely, slave thread should always set IGNORE_BAD_TABLE_OPTIONS in the sql_mode?

            serg Sergei Golubchik added a comment - likely, slave thread should always set IGNORE_BAD_TABLE_OPTIONS in the sql_mode ?

            In fact, the slave thread already effectively has IGNORE_BAD_TABLE_OPTIONS due to this code in report_unknown_option():

            commit 4568a72ce45207a538d89449ffcff4a84cb3ea33
            Author: Sergei Golubchik <serg@mariadb.org>
            Date:   Sun Jan 10 01:31:38 2021 +0100
             
                don't do a warning for bad table options in replication slave thread
             
              if (val->parsed || suppress_warning || thd->slave_thread) {
                DBUG_RETURN(FALSE);
            

            However, there is another place now where the warning is given (which is then converted into an error due to STRICT_(TRANS|ALL)_TABLES), in mysql_prepare_create_table_finalize(), possibly introduced with crash-safe alter table?

              /* Give warnings for not supported table options */
              if (create_info->used_fields & HA_CREATE_USED_TRANSACTIONAL &&
                  !file->has_transactional_option())
                push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, ER_UNKNOWN_OPTION,
                                    ER_THD(thd, ER_UNKNOWN_OPTION), "transactional");
            

            So I guess is to just make the code consistent and skip the warning (/error) in both places for the replication threads.

            Patch: https://lists.mariadb.org/hyperkitty/list/commits@lists.mariadb.org/thread/S63DUWY64WZZC6ADUKVNXWTMBPD4WJNG/

            knielsen Kristian Nielsen added a comment - In fact, the slave thread already effectively has IGNORE_BAD_TABLE_OPTIONS due to this code in report_unknown_option(): commit 4568a72ce45207a538d89449ffcff4a84cb3ea33 Author: Sergei Golubchik <serg@mariadb.org> Date: Sun Jan 10 01:31:38 2021 +0100   don't do a warning for bad table options in replication slave thread   if (val->parsed || suppress_warning || thd->slave_thread) { DBUG_RETURN(FALSE); However, there is another place now where the warning is given (which is then converted into an error due to STRICT_(TRANS|ALL)_TABLES), in mysql_prepare_create_table_finalize(), possibly introduced with crash-safe alter table? /* Give warnings for not supported table options */ if (create_info->used_fields & HA_CREATE_USED_TRANSACTIONAL && !file->has_transactional_option()) push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, ER_UNKNOWN_OPTION, ER_THD(thd, ER_UNKNOWN_OPTION), "transactional"); So I guess is to just make the code consistent and skip the warning (/error) in both places for the replication threads. Patch: https://lists.mariadb.org/hyperkitty/list/commits@lists.mariadb.org/thread/S63DUWY64WZZC6ADUKVNXWTMBPD4WJNG/

            I propose to fix this in 11.4. I think the problem is too benign to warrant changing the code in old GA release. Also, a work-around exists by setting sql_mode on the slave without STRICT_ALL_TABLES and STRICT_TRANS_TABLES.

            knielsen Kristian Nielsen added a comment - I propose to fix this in 11.4. I think the problem is too benign to warrant changing the code in old GA release. Also, a work-around exists by setting sql_mode on the slave without STRICT_ALL_TABLES and STRICT_TRANS_TABLES.

            Pushed to 11.4

            knielsen Kristian Nielsen added a comment - Pushed to 11.4

            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.