Bug #95513
closed
SQL error: 'Incorrect integer value: '' for column 'grade' at row 1'
0%
Description
Having this definition:
'grade' => [
'exclude' => true,
'label' => 'LLL:EXT:myext/Resources/Private/Language/locallang_db.xlf:tx_myext_domain_model_location.grade',
'config' => [
'type' => 'select',
'renderType' => 'selectSingle',
'items' => [
['', 0],
],
'foreign_table' => 'tx_myext_domain_model_grade',
'foreign_table_where' => 'ORDER BY tx_myext_domain_model_grade.name',
],
],
One can properly choose a "grade" in the selector and persist it. But as soon as the default empty option is selected (0), persisting the record fails with:
SQL error: 'Incorrect integer value: '' for column 'grade' at row 1' (tx_myext_domain_model_location:1)
Somehow the 0 is detected as "empty" and transformed into an empty string when sent to the database.
Updated by Xavier Perseguers about 3 years ago
- Priority changed from Should have to Must have
- Is Regression set to Yes
Updated by Xavier Perseguers about 3 years ago
- Related to Bug #91731: Cannot empty a date field in TCEforms if MySQL is in strict mode added
Updated by Xavier Perseguers about 3 years ago
- Priority changed from Must have to Should have
- Is Regression changed from Yes to No
That's possibly still the very same problem we have since ages regarding strict mode of the underlying DBMS. This is in TYPO3 v11 with a new project and I didn't have this very problem in v10. Yet, in that other v10 project I did this in my DB connection:
'initCommands' => 'SET sql_mode=\'\';',
After testing, this fixes the problem in v11, so nothing new actually, unfortunately a very old one.
Updated by Xavier Perseguers about 3 years ago
- Related to Bug #78921: Copying records fails with SQL error without any message added
Updated by Xavier Perseguers about 3 years ago
To me, this would possibly not solve all "strict" problems, but it looks like the Core wrongly checks for "no value" with `empty()` instead of actually checking for either non-existant key in the fieldArray (TCA) or for actual empty string (=== '') and this should be easily solvable.
Updated by Xavier Perseguers about 3 years ago
- Category set to DataHandler aka TCEmain
Updated by Martin Kutschker about 3 years ago
I'd appreciate if the Core would run flawless with strict mode turned on.
Sidenote: using empty() in code should raise a red flag :)
Updated by Jan Delius about 3 years ago
Xavier Perseguers wrote:
Having this definition:
[...]
One can properly choose a "grade" in the selector and persist it. But as soon as the default empty option is selected (0), persisting the record fails with:
[...]
Somehow the 0 is detected as "empty" and transformed into an empty string when sent to the database.
We use
'eval' => 'int'
for this problem.
In addition, we always use the following with strict mode so that the field is also saved correctly if it is not displayed to the editor:
'default' => 0
Updated by Benni Mack about 3 years ago
Yes, we had this too. "default => 0" is a must.
Updated by Dieter Porth almost 2 years ago
I have the same problem. I actully tried to add an allowed object via Extbase to my model to prevent this bug. But it did not work, too. 'default'=> 0, won't work for me. 'eval' => 'int', in a TCA for rendertype 'selectSingle' won't work and does not make much sense.
The only help was the addition to [DB][default] in 'localConfiguration.php' with ` 'initCommands' => 'SET sql_mode=\'\';', `.
Updated by Kathara M.G. over 1 year ago
I'm receiving the same error for a field of type passthrough even after adding the 'default' => 0 (running on v11.5.24). Happens when creating an object without setting that property (ObjectStorage in model where default value is null)
A solution without turning off strict mode would be preferable. Setting sql_mode = '' worked for my problem too though.
Updated by Bernhard Eckl 10 months ago
ยท Edited
Same here. Default value is set in TCA and in Domain Model.
Updated by Garvin Hicking 5 months ago
- Status changed from New to Closed
As for the Extbase Model implications - if you have a nullable objectstorage (or attribute) there, you'd need that property to have a NULLable database field as well. A "Default" calcuation is performed by the FormEngine/DataHandler combination but differently implemented in the Extbase persistence layer (IIRC).
As for the general TCA, I do think that using "default => 0" would be the requirement. Currently this is also noted in a "Note" on https://docs.typo3.org/m/typo3/reference-tca/12.4/en-us/ColumnsConfig/Type/Select/Properties/Items.html - would that be sufficient as a solution to this issue?
Check out the "l10n_parent" fields of every core TCA, this is actually something similiar and has a "0" value items dropdown too, and comes with a "default => 0" declaration.
I hope closing this issue is acceptable. If you believe this is in mistake, please feel free to reopen or create a follow-up. Thanks!
Updated by Christian Weiske 4 months ago
- Related to Bug #104510: Incorrect integer value: '' when creating workspace overlay with inline MM field added