Bug #88331

MySQL strict mode: let integer time fields be filled with NULL

Added by Hagen Gebauer 2 months ago. Updated 2 months ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
Extbase
Target version:
-
Start date:
2019-05-10
Due date:
% Done:

0%

TYPO3 Version:
9
PHP Version:
7.3
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

I have an events extension with the following fields

date_start date NULL DEFAULT NULL,
date_end date NULL DEFAULT NULL,
time_start int(11) NULL DEFAULT NULL,
time_end int(11) NULL DEFAULT NULL,

The only required field is date_start so that it is possible to add events without defining a specific start time. The end time is rarely used anyway.

I want to be able to send both time fields with an empty value instead of '0' but that returns an SQL error on submitting the backend form when MySQL is in strict mode (and it clears the whole form, to boot). For the date_end field this works and will be stored as NULL. For now I go with the default value '0' and assume that '00:00' is an empty time field and no event starts at midnight – but that doesn’t feel right.

If this isn’t a TYPO3 bug and/or there is another way to achieve what I want, I apologise. But when adding a row via mysql> with empty values for the time fields they will be stored as NULL – just as I want it.

The TCA for the fields is

'date_start' => [
    'exclude' => true,
    'l10n_mode' => 'exclude',
    'label' => '' . $locLang . 'events.date_start',
    'config' => [
        'dbType' => 'date',
        'type' => 'input',
        'renderType' => 'inputDateTime',
        'size' => 7,
        'eval' => 'date, required',
        'default' => NULL
    ],
],
'date_end' => [
    'exclude' => true,
    'l10n_mode' => 'exclude',
    'label' => '' . $locLang . 'events.date_end',
    'config' => [
        'dbType' => 'date',
        'type' => 'input',
        'renderType' => 'inputDateTime',
        'size' => 7,
        'eval' => 'date',
        'default' => NULL
    ],
],
'time_start' => [
    'exclude' => true,
    'l10n_mode' => 'exclude',
    'label' => '' . $locLang . 'events.time_start',
    'config' => [
        'type' => 'input',
        'renderType' => 'inputDateTime',
        'size' => 4,
        'eval' => 'time',
        'default' => 0,
    ]
],
'time_end' => [
    'exclude' => true,
    'l10n_mode' => 'exclude',
    'label' => '' . $locLang . 'events.time_end',
    'config' => [
        'type' => 'input',
        'renderType' => 'inputDateTime',
        'size' => 4,
        'eval' => 'time',
        'default' => 0,
    ]
],

History

#1 Updated by Hagen Gebauer 2 months ago

  • Category set to Extbase
  • Tags deleted (extbase)

#2 Updated by Josef Glatz 2 months ago

I had the exact same scenario today after upgrading the MySql server.

As it was an update from an older instance I disabled the strict mode. But it feels not correct.
Another approach I found in ext:eventnews is to use an extra field is_fullday.

I‘m interested how this can be integrated in a great way.

#3 Updated by Hagen Gebauer 2 months ago

Josef Glatz wrote:

Another approach I found in ext:eventnews is to use an extra field is_fullday.

Thank you! Yes, I had the idea as well to implement an additional boolean value. But I consider this unnecessary information since MySQL is able to set time fields to NULL. In my case it’s not necessarily about all day events, it might be an event with the time not yet fixed. I don’t have a real calendar view where this information matters, I just want the controller to be able to decide whether to display the time or not.

Also available in: Atom PDF