SQL Modes Revisited
The SQL mode settings of MariaDB and MySQL are no longer compatible.
1. Why it requirers SQL modes?
Mode | MariaDB | MySQL | Notes |
---|---|---|---|
Server | |||
Session Lax | |||
Session Strict |
Server SQL mode
- the user might have no control over the server SQL mode settings
- the parameters for the server SQL mode may vary
Session SQL mode
Shared hosting
Strict mode
InnoDB
2. WackoWiki settings
constants
-
SQL_MODE_LAX
-
SQL_MODE_STRICT
const SQL_MODE_LAX = [ 'mariadb' => 'NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER', 'mysql' => 'NO_ENGINE_SUBSTITUTION' ]; const SQL_MODE_STRICT = [ 'mariadb' => 'TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY', 'mysql' => 'TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY' ];
primary config
-
db_vendor
-
sql_mode
If a user transfers his database to another vendor, he must change the db_vendor
setting, for example from 'mariadb
' to 'mysql
' in his config file by hand or during an upgrade in the installer.
Default settings
- server
- session
- wiki engine
2.1. Lax or strict
- strict mode:
- development, testing and new deployments
- non-strict mode:
- backward-compatible
- mainly for legacy scripts
Currently the sql_mode
mode is set in the default config file and can only be changed by hand in the config file. Default is set to 0
(server).
This wiki runs in strict mode.
2.2. Installer
With WackoWiki 6.1.26 the installer provides an option in the database settings to select the database vendor, MariaDB or MySQL, before a database connection is established.
This enabels the engine to select specific SQL mode parameters for the selected database vendor.
Another possibility is to disable the SQL mode with debug
off and auto-set the db_vendor
.
The user no longer has to modify the SQL mode parameters in the constants.php file for MySQL. In an upgrade it will take the necessary values fom the primary config.
2.2.1. Naming / wording and translations
'DbVendor' => 'Database vendor', 'DbVendorDesc' => 'The database vendor you use.', 'DbSqlMode' => 'SQL mode', 'DbSqlModeDesc' => 'The SQL mode you want use.',
- database vendor (mariadb, mysql)
- The term vendor is difficult to translate, more like manufacturer or OEM and not like provider - which mistakenly can be interpeted as ISP or hosting company.
- better decription for SQL mode
3. MySQL derivates
Do we get a valid, reliable and distinct identifier for all the different MySQL derivates?-
version REGEXP 'MariaDB' -- > Mariadb version_comment REGEXP 'Percona' -- > Percona else MySQL
-
version_comment
can be accessed viaSHOW VARIABLES
orinformation_schema
. -
@@version
is not reliable because Percona leaves no clue, although I suspect the '-30.3-' is a clue in8.0.36-28-log
. - https://stackoverflow.com/ques[...]r-percona-or-mariadb
- https://mariadb.com/kb/en/sql-mode/#no_auto_create_user
In regard to the SQL mode Percona server can be treated as MySQL.
4. Patches
- adds db_vendor to primary config
- renames sql_mode_strict to sql_mode
- installer auto-detects db_vendor
5. ToDo
-
Provide the ability to turn the session SQL mode offDONE - update database settings screenshot
- update documentation
- users may ask themself what this is all about when they have to choose between [server, session lax, session strict]
- Please do tests on your stacks!
Somehow I'm not quite happy with the current solution.
5.1. Unsolved
- even if the
db_vendor
is set in the installer it still will throw a Fatal error if MariaDB and SQL mode Lax is selected however MySQL is used- e.g.
NO_AUTO_CREATE_USER
causes a Fatal error
- e.g.
- once a database connection is established it can check if the chosen
db_vendor
is correct, but it won't catch the Fatal error- maybe do the check and set SQL mode to Server to avoid the Fatal error if
debug
mode is off - DONE
- maybe do the check and set SQL mode to Server to avoid the Fatal error if
5.2. Questions
- What should be changed or improved?
- Should the session strict mode be set as new default?
- Or should the server SQL mode be set as new default?
- ls it better to hide the
sql_mode
option like thedb_vendor
in the installer and show this option only with activedebug=3
, and the user can only set it in the primary config?
5.3. Suggestions
- rename
sql_mode_strict
tosql_mode
-
0
- SQL_MODE_SERVER -
1
- SQL_MODE_SESSION_LAX -
2
- SQL_MODE_SESSION_STRICT
-
- add
sql_mode
as option to the database configuration in the installer- SQL Mode
- Server
- Session lax
- Session strict
- wrap the SQL mode options in an
<details>
tag and set it closed when the server SQL mode is on
- SQL Mode