Invalid date format : 0000 vs. NULL (etc) Topic is solved

Be informed that this forum is not an official support forum for Joomla! 4.0. Any issues regarding Joomla! 4.0 must be reported at https://issues.joomla.org/.

Joomla 4.0 is still in Beta stage. This forum should be used for sharing information about Joomla! 4.0.

Moderator: ooffick

Forum rules
Post Reply
mikerotec
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 183
Joined: Fri Aug 08, 2014 10:08 pm

Invalid date format : 0000 vs. NULL (etc)

Post by mikerotec » Wed Jun 03, 2020 5:04 pm

I've been looking at having the whole Joomla database collated to (modern) utf8mb4_unicode_ci but right off the bat I get the warning message back that #_action_logs table has "invalid default date" of 0000-00-00 00:00:00 as default for 'log_date' field.

So I'm wondering what's the official story now? Will joomla be null date defaults going forward everywhere? Seems that would cause a lot of woe in the backwards compatibility department...

Or will Joomla installations/upgrades check to make sure database is configured with SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; or NO STRICT MODE, or some such???

I've been googling all morning and while this seems to be an ongoing concern going back years, I haven't yet found an official advice on it yet. Can anyone direct me to the info? Thanks in advance!

PS: currently working with

Database Type mysql
Database Version 5.7.30
Database Collation utf8mb4_unicode_ci
Database Connection Collation utf8mb4_unicode_ci
PHP Version 7.3.18
Joomla! Version Joomla! 3.9.19 Stable [ Amani ] 2-June-2020 15:00 GMT

 
User avatar
anibal_sanchez
Joomla! Explorer
Joomla! Explorer
Posts: 437
Joined: Sat Mar 17, 2012 3:01 pm
Location: Oviedo, Asturias, Spain
Contact:

Re: Invalid date format : 0000 vs. NULL (etc)

Post by anibal_sanchez » Wed Jun 03, 2020 5:26 pm

Hi,

This is what is defined in Potential backward compatibility issues in Joomla 4
: https://docs.joomla.org/Potential_backw ... n_Joomla_4

Strict mode has been enabled. The following flags are now active by default in Joomla 4 and you may have to update your database queries accordingly. This will help us with future mysql version upgrades and also aligns more closely with postgres to enable easier compatibility with queries in both languages.

Code: Select all

'STRICT_TRANS_TABLES',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_AUTO_CREATE_USER',
'NO_ENGINE_SUBSTITUTION',
Best Regards
@anibal_sanchez - http://blog.anibalhsanchez.com

JED Team Leader

mikerotec
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 183
Joined: Fri Aug 08, 2014 10:08 pm

Re: Invalid date format : 0000 vs. NULL (etc)

Post by mikerotec » Wed Jun 03, 2020 5:36 pm

Thanks!! so ( just to be clear) can I assume then that

1) the Joomla 3 -> 4 updater will convert all of Joomla's own tables to use NULL date defaults?

and

2) any older extensions that still use the 0000 style date defaults will break?

and (by extension)

3) the upcoming "Joomla 3->4 upgrade tester" will check all tables for invalid date defaults?

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 26929
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Invalid date format : 0000 vs. NULL (etc)

Post by Per Yngve Berg » Wed Jun 03, 2020 9:00 pm

The 0000 style dates are no longer accepted from mysql version 5.7

User avatar
brian
Joomla! Master
Joomla! Master
Posts: 11928
Joined: Fri Aug 12, 2005 7:19 am
Location: Leeds, UK
Contact:

Re: Invalid date format : 0000 vs. NULL (etc)

Post by brian » Wed Jun 03, 2020 9:26 pm

> 1) the Joomla 3 -> 4 updater will convert all of Joomla's own tables to use NULL date defaults?

yes
"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/

User avatar
anibal_sanchez
Joomla! Explorer
Joomla! Explorer
Posts: 437
Joined: Sat Mar 17, 2012 3:01 pm
Location: Oviedo, Asturias, Spain
Contact:

Re: Invalid date format : 0000 vs. NULL (etc)

Post by anibal_sanchez » Thu Jun 04, 2020 1:24 pm

So, I guess that:

2) any older extensions that still use the 0000 style date defaults will break?

Yes. The old extension will NOT work. The extension upgrade for Joomla 4 must convert the tables to NULL dates.

3) the upcoming "Joomla 3->4 upgrade tester" will check all tables for invalid date defaults?

No, the Update Checker checks the hosting support for Joomla 4 and the availability of extensions for Joomla 4. It will not check individually the tables, only the availability of extensions versions for Joomla 4. Indirectly, the result will be the same.
@anibal_sanchez - http://blog.anibalhsanchez.com

JED Team Leader

mikerotec
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 183
Joined: Fri Aug 08, 2014 10:08 pm

Re: Invalid date format : 0000 vs. NULL (etc)

Post by mikerotec » Thu Jun 04, 2020 3:24 pm

Awesome! thanks very much for the clarification :)

 

Post Reply

Return to “Joomla! 4 Related”