Issue Task Closed

#94100SQL Mode in TYPO3

Marc Hirdes 05/10/21, 11:03 updated 06/12/24, 08:33

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.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

History 3

  1. Garvin Hicking commented and changed status New Needs Feedback assigned to Garvin Hicking 06/11/24, 16:36

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

  2. Garvin Hicking commented 06/11/24, 16:45

    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',
        ];
  3. Garvin Hicking commented and changed status Needs Feedback Closed 06/12/24, 08:33