Trying to create translation of necessary_cookie_options entries throws SQL "incorrect integer value" error

[bfzgaier]

System information:
TYPO3 11.5
PHP 7.4.30
MariaDB 10.5 (MySQL 5.5.5-10.5.15-MariaDB-0+deb11u1)

If you try to create a translation for a necessary_cookie_options item, the following SQL error is thrown:
2: SQL error: 'Incorrect integer value: '' for column db.tx_mindshapecookieconsent_domain_model_cookieoption.cookie_category at row 1' (tx_mindshapecookieconsent_domain_model_cookieoption:NEW636d1917988cd937434709)

The problem should be the new defaults for MariaDB. It enforces field types more strictly, and therefore '' is not cast to integer.
Adding a default => 0 value to the TCA configuration fixed the issue.

mindshape_cookie_consent/Configuration/TCA/tx_mindshapecookieconsent_domain_model_cookieoption.php

Lines 68 to 75 in 8aba632

	'cookie_category' => [
	'exclude' => true,
	'label' => 'LLL:EXT:mindshape_cookie_consent/Resources/Private/Language/locallang.xlf:tca.cookie_category.label',
	'config' => [
	'type' => 'select',
	'renderType' => 'selectSingle',
	'foreign_table' => \Mindshape\MindshapeCookieConsent\Domain\Model\CookieCategory::TABLE,
	],

[featdd]

Hi @bfzgaier,

I could'nt reproduce this, will have to investigate further when I'm finding some time.
But as you describe the problem this also can be a core issue with the new mariadb features!?

Greetings
Daniel

[bfzgaier]

Yes, it also could be a core issue. Depending on how the core handles default values.
It looks like empty string is the current default for at least select fields, but the column is defined as integer.

mindshape_cookie_consent/ext_tables.sql

Line 64 in 59ca441

`cookie_category` int(11) unsigned DEFAULT '0' NOT NULL,

In older Mariadb/MySQL Versions this was cast to integer or ignored (I am not really sure)

Here are the current default sql modes for MySQL 5.7 and MariaDB 10.2
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
https://mariadb.com/kb/en/sql-mode/

The flag STRICT_TRANS_TABLES should be responsible.

Strict mode. Statements with invalid or missing data are aborted and rolled back, except that for non-transactional storage engines and statements affecting multiple rows where the invalid or missing data is not the first row, MariaDB will convert the invalid value to the closest valid value, or, if a value is missing, insert the column default value.

https://mariadb.com/kb/en/sql-mode/#strict_trans_tables

[featdd]

Hi @bfzgaier,

thanks for your research, I could reproduce this now and added the default value for this field.

See: 42986eb

Greetings
Daniel