Details
Major 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.