Bug #95513
open
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 over 2 years ago
- Priority changed from Should have to Must have
- Is Regression set to Yes
Updated by Xavier Perseguers over 2 years ago
- Related to Bug #91731: Cannot empty a date field in TCEforms if MySQL is in strict mode added
Updated by Xavier Perseguers over 2 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 over 2 years ago
- Related to Bug #78921: Copying records fails with SQL error without any message added
Updated by Xavier Perseguers over 2 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 over 2 years ago
- Category set to DataHandler aka TCEmain
Updated by Martin Kutschker over 2 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 over 2 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 over 2 years ago
Yes, we had this too. "default => 0" is a must.
Updated by Dieter Porth about 1 year 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. about 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 3 months ago
ยท Edited
Same here. Default value is set in TCA and in Domain Model.