{{toc numerate=1}}
The SQL mode settings of MariaDB and MySQL are no longer compatible.
===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
===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
====Lax or strict====
* strict mode:
* development
* testing
* 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.
====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.
=====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
===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 via ##SHOW VARIABLES## or ##information_schema##.
* ##@@version## is not reliable because Percona leaves no clue, although I suspect the '-30.3-' is a clue in ##8.0.36-28-log##.
* https://stackoverflow.com/questions/37317869/determine-if-mysql-or-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.
===Patches===
1. ((commit:662abe0dc40e8b59443ede5ef90b19b71f35c52f adds db_vendor to primary config))
1. ((commit:09fcd6ddef057862ddbdabe4b91591f6fa978094 renames sql_mode_strict to sql_mode))
2. ((commit:f31faa7a6f777a0678eec696b7c72e77887bfdb8 installer auto-detects db_vendor))
===ToDo===
* --Provide the ability to turn the session SQL mode off-- DONE
* 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.
====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
* 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
====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 the ##db_vendor## in the installer and show this option only with active ##debug=3##, and the user can only set it in the primary config?
====Suggestions====
* rename ##sql_mode_strict## to ##sql_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
===References===
* ((/Dev/Guidelines/SQL/SQLmodes))