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

Inconsistent behavior of DECODE in table definition causes replication abort and other errors

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Incomplete
    • 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL)
    • N/A
    • Parser
    • None

    Description

      DECODE function requires a different number of parameters in the standard mode and in ORACLE mode.

      This is clearly intentional and is not a subject of this report, although It causes expected inconveniences, e.g.

      10.9 5b37d583

      MariaDB [test]> CREATE TABLE t (crypt VARCHAR(128), pass VARCHAR(128), val VARCHAR(128) GENERATED ALWAYS AS (DECODE(crypt,pass)));
      Query OK, 0 rows affected (0.018 sec)
       
      MariaDB [test]> SET sql_mode=ORACLE;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> ALTER TABLE t MODIFY IF EXISTS x INT;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
      

      The actual problem is that the error above doesn't happen consistently. If the table (definition?) was open before, the error does not occur:

      MariaDB [test]> CREATE TABLE t (crypt VARCHAR(128), pass VARCHAR(128), val VARCHAR(128) GENERATED ALWAYS AS (DECODE(crypt,pass)));
      Query OK, 0 rows affected (0.086 sec)
       
      MariaDB [test]> SHOW CREATE TABLE t;
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                                                                           |
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t     | CREATE TABLE `t` (
        `crypt` varchar(128) DEFAULT NULL,
        `pass` varchar(128) DEFAULT NULL,
        `val` varchar(128) GENERATED ALWAYS AS (decode(`crypt`,`pass`)) VIRTUAL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> SET sql_mode=ORACLE;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> ALTER TABLE t MODIFY IF EXISTS x INT;
      Query OK, 0 rows affected, 1 warning (0.000 sec)
      Records: 0  Duplicates: 0  Warnings: 1
       
      MariaDB [test]> show warnings;
      +-------+------+---------------------------+
      | Level | Code | Message                   |
      +-------+------+---------------------------+
      | Note  | 1054 | Unknown column 'x' in 't' |
      +-------+------+---------------------------+
      1 row in set (0.000 sec)
      

      One of many effects it causes is that if it is executed in the replication context, CREATE and ALTER are written in the binary log, and ALTER is written with sql_mode=ORACLE, so the slave fails to execute it because it doesn't have SHOW CREATE in between.

      --source include/master-slave.inc
       
      CREATE TABLE t (crypt VARCHAR(128), pass VARCHAR(128), val VARCHAR(128) GENERATED ALWAYS AS (DECODE(crypt,pass)));
      SHOW CREATE TABLE t;
      SET sql_mode=ORACLE;
      ALTER TABLE t MODIFY IF EXISTS x INT;
       
      --sync_slave_with_master
       
      --connection master
      DROP TABLE t;
      --source include/rpl_end.inc
      

      10.4 b54e4bf0

      023-08-08  2:05:06 13 [ERROR] mysqld: Incorrect information in file: './test/t.frm'
      2023-08-08  2:05:06 13 [ERROR] Slave SQL: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1' on query. Default database: 'test'. Query: 'ALTER TABLE t MODIFY IF EXISTS x INT', Gtid 0-1-2, Internal MariaDB error code: 1064
      2023-08-08  2:05:06 13 [Warning] Slave: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1 Error_code: 1064
      2023-08-08  2:05:06 13 [Warning] Slave: Incorrect information in file: './test/t.frm' Error_code: 1033
      2023-08-08  2:05:06 13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 551
      

      Similar problem happens upon binlog replay and possibly in other scenarios.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              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.