The SQL mode settings of MariaDB and MySQL are no longer compatible.

1. Why it requirers SQL modes?

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
  • 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

• 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/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

1. adds db_vendor to primary config
2. renames sql_mode_strict to sql_mode
3. installer auto-detects db_vendor

5. 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.

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
• 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

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 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?

5.3. 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

6. References

• /Dev/Guidelines/SQL/SQLmodes