Bug #88331
closedMySQL strict mode: let integer time fields be filled with NULL
0%
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,
]
],
Updated by Hagen Gebauer over 5 years ago
- Category set to Extbase
- Tags deleted (
extbase)
Updated by Josef Glatz over 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.
Updated by Hagen Gebauer over 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.
Updated by Alexander Schnitzler over 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.
Updated by Rémy DANIEL almost 4 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
Updated by B. Kausch almost 4 years ago
'eval' => 'time,null'
will fix the problem. Then it is clear, that the value muss be explicitly greenlighted for editing.
Updated by Hagen Gebauer about 2 years 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.
Updated by Riccardo De Contardi 8 months 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.