WL#9465: Remove the MAXDB sql_mode
Affects: Server-8.0 — Status: Complete — Priority: Medium
By a historical reason we support the MAXDB sql_mode: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_maxdb In the parser the MAXDB mode means that we silently substitute the TIMESTAMP column type in CREATE/ALTER TABLE with the DATETIME type -- this looks weird and requires some ad hoc processing. The current WL does the removal of the MAXDB mode in 8.0.
Requirements for either current WL and WL#8383: F1: When executing a SET sql_mode statement (either on global or session level), including any of the following options shall generate an error and have no effect on the sql_mode: DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, ORACLE, POSTGRESQL. F2: When executing a SET sql_mode statement (either on global or session level), including any of the following bit flags shall generate an error and have no effect on the sql_mode: 0x00100, 0x00200, 0x00400, 0x00800, 0x01000, 0x02000, 0x04000, 0x08000, 0x10000, 0x20000. NF3: If a stored function F was created at 5.7 in the "SET sql_mode=MAXDB" environment, then, after the on-line migration to 8.0, "SHOW CREATE FUNCTION F" should *not* output the MAXDB entry in its "sql_mode" column. NF4: The same for stored procedures. NF5: The same for triggers. NF6: The same for events. F7: When setting sql-mode to one of DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, ORACLE, POSTGRESQL, in either a configuration file or in command-line options, the server must print an error and not start.
Note: These specifications resemble ones for the WL#8383. The only divergence is a different processing of the TIMESTAMP column type after the MAXDB mode removal. Main changes: # SQL semantics: ---------- ## No more ad-hoc processing of the TIMESTAMP type Before this WL the MAXDB mode forced a silent substitution of TIMESTAMP with DATETIME in type declarations of table columns, SP parameters, variables and return values. After the MAXDB mode removal, TIMESTAMP is always TIMESTAMP. Good: mysqldump of MAXDB-enabled database uses binary table metadata, thus its output will contain DATETIME instead of TIMESTAMP, so restored table dump will be consistent with the original data. Bad: After the statement-based replication of CREATE TABLE from old MAXDB-enabled servers to a new one, old servers will have a DATETIME column, while a new one will have TIMESTAMP instead. ---------- ## `SET sql_mode=MAXDB` results in a error: "Variable 'sql_mode' can't be set to the value of 'MAXDB'". Good: This change doesn't break mysqldump & restore of *table* metadata, since it doesn't contain `SET sql_mode` statements. Bad: This change can break mysqldump & restore of *routines* (SP, SF, triggers, events), since mysqldump at old servers can wrap SHOW CREATE SP output with `SET sql_mode=MAXDB` statements there. Bad: If the MAXDB mode is "on" at old servers, a replication to a new one can cause run time errors. ---------- ## SHOW CREATE TABLE output There was some filtering in the MAXDB mode. After the removal, a DBA should use the `sql_mode=ANSI` workaround for a similar effect if necessary. Neutral change: Before this change, the MAXDB mode (like the ANSI mode etc.) suppressed some non-standard MySQL-specific clauses in the output. That helped to port MySQL schema to other databases. However, it seems that neither MAXDB mode, nor ANSI, never suppressed/converted all MySQL-only features in the output. Thus, the MAXDB mode never emulated a 100% MAXDB-compatible output, and its removal should not hurt there. OTOH, the ANSI mode is still supported, so it can be used as a replacement of the MAXDB mode in some cases. ---------- # DD/I_S tables: mysql.events, mysql.routines and mysql.triggers ---------- ## Metadata: no more references to MAXDB. Neutral change. ---------- ## Migration from .frm (5.7) to DD (8.0). Note: we don't care about migrations between 8.x and 8.x yet. After the current WL it simply resets the MAXDB bit flag while migrating routine metadata. Good: The `SET sql_mode=MAXDB` statement also forced a few "elementary" syntax-related flags: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE and NO_AUTO_CREATE_USER. Thus, while the current WL cleanups the MAXDB flag itself in the metadata of routines, the "elementary" flags like PIPES_AS_CONCAT survive. So, in most cases, a body of a former MAXDB-enabled routine still has a correct set of syntax-related flags, and it is still parseable/syntaxicaly correct after mysqldump & restore. Bad: If SP/SF/etc was created in the MAXDB mode, then SHOW CREATE SP/SF/etc statement produces a bit inconsistent output. For example: Original SP: CREATE FUNCTION f1(p TIMESTAMP) RETURNS TIMESTAMP BEGIN DECLARE v TIMESTAMP; RETURN NULL; END; The `SHOW CREATE FUNCTION f1` command reconstructs and outputs metadata of f1 as: CREATE FUNCTION f1(p TIMESTAMP) RETURNS datetime BEGIN DECLARE v TIMESTAMP; RETURN NULL; END; Note the return value type: it is substituted with DATETIME, while parameter and local variable types still have the TIMESTAMP type. While TIMESTAMP and DATETIME are equivalent in the MAXDB mode, they become different if we drop the MAXDB flag. This is not clear how to deal with this case: replace the DATETIME return type with TIMESTAMP, or try to convert textual TIMESTAMP references to DATETIME instead, or what. Since most likely nobody uses the MAXDB mode, we don't convert anything there.
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.