Project

General

Profile

Actions

Bug #88331

closed

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

Added by Hagen Gebauer almost 5 years ago. Updated 20 days ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2019-05-10
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
9
PHP Version:
7.3
Tags:
persistence
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,
    ]
],

Related issues 1 (1 open0 closed)

Related to TYPO3 Core - Feature #91688: TCA: Make default value mandatory for some field types if SQL field defined as "NOT NULL" - avoid error in extensions due to strict and missing defaultNew2020-06-22

Actions
Actions #1

Updated by Hagen Gebauer almost 5 years ago

  • Category set to Extbase
  • Tags deleted (extbase)
Actions #2

Updated by Josef Glatz almost 5 years 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.

Actions #3

Updated by Hagen Gebauer almost 5 years 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.

Actions #4

Updated by Alexander Schnitzler about 4 years ago

  • Tags set to persistence
Actions #5

Updated by Alexander Schnitzler about 4 years ago

  • Category changed from Extbase to Database API (Doctrine DBAL)

Sorry but this is not an extbase ticket. This has to be fixed in the backend.

Actions #6

Updated by Rémy DANIEL over 3 years ago

  • Related to Feature #91688: TCA: Make default value mandatory for some field types if SQL field defined as "NOT NULL" - avoid error in extensions due to strict and missing default added
Actions #7

Updated by B. Kausch over 3 years ago

'eval' => 'time,null' will fix the problem. Then it is clear, that the value muss be explicitly greenlighted for editing.

Actions #8

Updated by Hagen Gebauer over 1 year ago

Sorry for never getting back on this … it got lost in my e-mails. Thank you, B. Kausch: Your solution works like a charm, certainly in T3v11. I haven’t tested it in older versions. My TCA for the time fields is now:

'time_start' => [
    'exclude' => true,
    'l10n_mode' => 'exclude',
    'label' => '' . $locLang . 'events.time_start',
    'config' => [
        'type' => 'input',
        'renderType' => 'inputDateTime',
        'size' => 4,
        'eval' => 'time,null',
        'default' => NULL,
    ]
],
'time_end' => [
    'exclude' => true,
    'l10n_mode' => 'exclude',
    'label' => '' . $locLang . 'events.time_end',
    'config' => [
        'type' => 'input',
        'renderType' => 'inputDateTime',
        'size' => 4,
        'eval' => 'time,null',
        'default' => NULL,
    ]
],

From my point of view, this ticket can be closed.

Actions #9

Updated by Riccardo De Contardi 20 days ago

  • Status changed from New to Closed

Sorry for the very late reply; I am closing this ticket

If you think that this is the wrong decision or there is still work to be done, please open a new ticket with a reference to this one.

Thank you.

Actions

Also available in: Atom PDF