Task #94100: SQL Mode in TYPO3 - TYPO3 Core - TYPO3 Forge

Actions

Copy link

Task #94100

closed

SQL Mode in TYPO3

Added by Marc Hirdes over 3 years ago. Updated 5 months ago.

Status:

Closed

Priority:

Should have

Assignee:

Garvin Hicking

Category:

Documentation

Target version:

Start date:

2021-05-10

Due date:

% Done:

Estimated time:

TYPO3 Version:

PHP Version:

Tags:

Complexity:

Sprint Focus:

Description

Which SQL modes in TYPO3 are necessary and why? Which should be set as system requirements?

Here I get no information for this topic https://docs.typo3.org/m/typo3/guide-installation/master/en-us/In-depth/SystemRequirements/Index.html We made bad experience with STRICT_TRANS_TABLES. Therefore we would set the SQL mode to this value:

sql_mode = "ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREAT‌E_USER,NO_ENGINE_SUB‌STITUTION"

A recommondation from TYPO3 in the system requirements documentation would be great.

Actions

Copy link

Updated by Garvin Hicking 5 months ago · Edited

Status changed from New to Needs Feedback
Assignee set to Garvin Hicking

Just to document this here; we will work on this.

A work-in-progress draft statement that we could add to the docs is the following, but first needs a proper check by the TYPO3 Core Team, most likably Stefan ;) (hi!)

TYPO3 strives to be compatible to the DEFAULT `SQL_MODE` settings of the supported database engine versions. If you change any setting to a NON-default SQL mode, be prepared to audit all involved code to be compatible to your server mode choice. Notably the settings `ANSI` and `ANSI_QUOTES` may cause issues. `NO_AUTO_VALUE_ON_ZERO`, `NO_ENGINE_SUBSTITUTION`, `NO_AUTO_CREATE_USER`, `ERROR_FOR_DIVISION_BY_ZERO`, `ONLY_FULL_GROUP_BY`, `NO_ZERO_DATE`, `NO_ZERO_IN_DATE`, `STRICT_ALL_TABLES` and `STRICT_TRANS_TABLES` should work with TYPO3 versions 12 and upwards. Custom or third-party extensions need to be evaluated individually! Listed as incompatible is: `NO_BACKSLASH_ESCAPES`.

Actions

Copy link

Updated by Garvin Hicking 5 months ago · Edited

Testing-Framework uses this:

SET SESSION sql_mode = '
STRICT_ALL_TABLES, 
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_VALUE_ON_ZERO,
NO_ENGINE_SUBSTITUTION,
NO_ZERO_DATE,
NO_ZERO_IN_DATE,
ONLY_FULL_GROUP_BY'

Also typo3/sysext/install/Classes/SystemEnvironment/DatabaseCheck/Platform/MySql.php has this:

    /**
     * List of MySQL modes that are incompatible with TYPO3 CMS
     *
     * @var array
     */
    protected $incompatibleSqlModes = [
        'NO_BACKSLASH_ESCAPES',
    ];

Actions

Copy link