Project

General

Profile

Actions

Bug #95513

closed

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

Added by Xavier Perseguers over 2 years ago. Updated 4 days ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
DataHandler aka TCEmain
Target version:
-
Start date:
2021-10-07
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
11
PHP Version:
8.0
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

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.


Related issues 2 (1 open1 closed)

Related to TYPO3 Core - Bug #91731: Cannot empty a date field in TCEforms if MySQL is in strict modeClosed2020-06-30

Actions
Related to TYPO3 Core - Bug #78921: Copying records fails with SQL error without any messageNew2016-12-08

Actions
Actions #1

Updated by Xavier Perseguers over 2 years ago

  • Priority changed from Should have to Must have
  • Is Regression set to Yes

Possibly vaguely related to #95428 but here it's about storing 0 in the DB whereas #95428 is about supporting a default value (for new records) which should not be mixed-up with the "empty" selection.

Actions #2

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
Actions #3

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.

Actions #4

Updated by Xavier Perseguers over 2 years ago

  • Related to Bug #78921: Copying records fails with SQL error without any message added
Actions #5

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.

Actions #6

Updated by Xavier Perseguers over 2 years ago

  • Category set to DataHandler aka TCEmain
Actions #7

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 :)

Actions #8

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
Actions #9

Updated by Benni Mack over 2 years ago

Yes, we had this too. "default => 0" is a must.

Actions #10

Updated by Dieter Porth over 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=\'\';', `.

Actions #11

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.

Actions #12

Updated by Bernhard Eckl 5 months ago ยท Edited

Same here. Default value is set in TCA and in Domain Model.

Actions #13

Updated by Garvin Hicking 4 days 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!

Actions

Also available in: Atom PDF