TYPO3: SQL error: 'Incorrect integer value: '' for column 'sys_language_uid' at row 1'

Question

I have newly setup TYPO3, but when I try to add/save content, it gives me this error:

SQL error: 'Incorrect integer value: '' for column 'sys_language_uid' at row 1

Olivarez

Answer 1

The behavior is related to database management systems using strict mode, like MySQL since version 5.7. Disabling strict mode (like provided in the accepted answer) is just a work around.

The real solution would be to explicitly cast values to integer by modifying TCA (table configuration array) for the according field definitions.

• for fields of type input that would be setting/extending 'eval' => 'int', see example tt_content.starttime
• or in general for all field types it would be to define the default value using 'default' => 0, see example tt_content.sys_language_uid

Fletcher

See also the note in the updated docs docs.typo3.org/typo3cms/TCAReference/ColumnsConfig/Type/… – Husking

Answer 2

set this in Localconfiguration.php file

[SYS][setDBinit] = SET SESSION sql_mode=''

Bendicta

Anyway, if you want to use Extensions that do not set default field values yet and thus do not support strict mode, you might want to diable Strict mode like that in /etc/mysql/my.cnf : [mysqld] # deactivating strict mode as some Typo3 extensions do not support it yet (needs default-value for fields in TCA) # default: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION – Thomsen

In the long, it is not a good idea to disable the strict. For newer DB server versions this will be the default. So, you might get problems when you update or migrate your system to different DB server. So, while this can be a quick fix until you really fixed the problem, you really should not do this long term. You are just postponing problems to later. I would suggest to add a note to this answer to clarify this. – Secrete

Thats not a solution for developers of public extensions. You could do this in your private projects, but you should fix your extension instead. – Turnaround

Answer 3

For me, after trying different approaches, the solution was:

mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';" 

Then, you can verify that the mode is set by running the following:

 mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

And you should see:

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

Moralez

It is not a good long term solution to make the DB "less strict". – Secrete

Answer 4

In latest TYPO3 Versions you have to add this in LocalConfiguration.php

'setDBinit' => 'SET SESSION sql_mode = \'NO_ENGINE_SUBSTITUTION\';',

Regardant

It is not a good long term solution to make the DB "less strict" – Secrete

Answer 5

In Typo3 9.5/10.4

It you are not able to change the programming of the used extentions - or change the running mode of mysql server - it might be useful to change the mode of the typo3 connection. You can simply add to youre LocalConfiguration.php.

[DB][Connections][Default][initCommands]='SET SESSION sql_mode = \'\';',

Pedestrian