SQL Modes Revisited

1. Why it requirers SQL modes?

• the user might have no control over the server SQL mode settings
• the parameters for the server SQL mode may vary

2. WackoWiki settings

• 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'
];	

• db_vendor
• sql_mode

• server
• session
• wiki engine

2.1. Lax or strict

• strict mode:
  • development
  • testing
  • new deployments
• non-strict mode:
  • backward-compatible
  • mainly for legacy scripts

2.2. Installer

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^[link1]
• https://mariadb.com/kb/en/sql-mode/#no_auto_create_user

4. Patches

1. adds db_vendor to primary config^[link2]
2. renames sql_mode_strict to sql_mode^[link3]
3. installer auto-detects db_vendor^[link4]

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!

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^[link5]