Feature #68704

Set MySQL sql_mode

Added by Markus Blaschke about 4 years ago. Updated about 4 years ago.

Status:
Closed
Priority:
Should have
Category:
Install Tool
Target version:
Start date:
2015-08-03
Due date:
% Done:

0%

PHP Version:
Tags:
Complexity:
Sprint Focus:

Description

There should be an $TYPO3_CONF['DB']['sqlMode'] setting for dynamically set the sql mode.

The MySQL/MariaDB/PerconaDB SQL mode can be set per connection/session with:

SET sql_mode='';

or

SET sql_mode='VALUE_FROM_TYPO3_CONF';

Admins/Developers don't need to modify the global sql mode for running TYPO3 :)


Related issues

Related to TYPO3 Core - Bug #80035: Strict MySQL sql_mode breaks workspaces Closed 2017-02-27

History

#2 Updated by Wouter Wolters about 4 years ago

  • Status changed from New to Needs Feedback

This can be done with [SYS][setDBinit] in the Install Tool already. Please try this first.

#3 Updated by Daniel Goerz about 4 years ago

  • Tracker changed from Bug to Feature
  • Target version set to 7.5

Recategorized on demand of Markus.

#4 Updated by Markus Blaschke about 4 years ago

I know that it can be done with setDbInit but most devs don't know this MySQL feature.

Maybe TYPO3 should set this value for it's own database connection because most devs are removing this setting from the global MySQL/MariaDB configuration which is not a good idea.
Also TYPO3 could set tighter default values for the next Version (eg. 8) to prevent common sql errors.

With the sql mode you can prevent setting too big values for fields or setting wrong values into ENUM/SET fields. Also wrong GROUP BY usage can be prevented which is an error.

#5 Updated by Morton Jonuschat about 4 years ago

  • Category set to Install Tool
  • Status changed from Needs Feedback to Closed
  • Assignee set to Morton Jonuschat

After discussing this in #typo3-cms-coredev we decided to close this in favor of a more generic solution. At the moment setting SQLmodes, especially the STRICT_* modes, will result in lots of errors in the core as running MySQL strict mode is not supported.
There is currently a check for the install tool under review that will warn the user about incompatible modes. These can either be fixed on the server (globally) or by using .

In the long run MySQL strict mode will be supported in the core, then the main reason to remove the SQLmode setting from the DB configuration should be null and void. For the few remaining edge cases DBinit should be good enough. There have already been some discussions about modifying the SQLmode in the past and the outcome has always been that it undesirable for TYPO3 to modify these setting on it's own.

Also available in: Atom PDF