[4.0] Replace the zero datetime value '0000-00-00 00:00:00' by the real NULL value by csthomas · Pull Request #21901 · joomla/joomla-cms

csthomas · 2018-08-28T22:46:44Z

Pull Request for Issue # .

Summary of Changes

This PR changes only datetimes in the module table.
No more zero dates like "0000-00-00 00:00:00" in the module table.

Method checkIn() is only added (almost copy/paste) to set column check_out_time to NULL.
At method store() the default value has been changed to $updateNulls = true to be able to save NULL values.

TODO

~~missing changes for the install files (Mysql, PostgreSQL)~~
~~missing changes in the update files for Postgresql version~~

Changes for other tables should be made in the next PRs

Testing Instructions

First way:

On existed 4.0-dev installation upload changes and then click on the database "Fix" button.
The UPDATE ... query won't be applied by the FIX button. You can do it manually (in phpMyAdmin), but it is not required, the system will work anyway!
- on Postgresql database Fix button does not work because PR Repair the update of database schema changes on postgreSQL #19707 has not yet been merged in 4.0.

Second way:

Upload changes, then remove file configuration.php and install joomla 4.0 again (also you can test sample data).

In both cases:

After changes will be applied you can create and save modules with real NULL dates.
Test publish_up, publish_down and check_out_time columns. Everything work as before.

Expected result

You can create, edit and save modules.
Modules are loaded normally on backend and frontend.

How to revert changes in your test db

SET sql_mode = '';
ALTER TABLE `#__modules` MODIFY `publish_up` DATETIME NOT NULL DEFAULT '0000-00-00 0000:00:00';
ALTER TABLE `#__modules` MODIFY `publish_down` DATETIME NOT NULL DEFAULT '0000-00-00 0000:00:00';
ALTER TABLE `#__modules` MODIFY `checked_out_time` DATETIME NOT NULL DEFAULT '0000-00-00 0000:00:00';

laoneo · 2018-08-30T05:48:42Z

I like the change. I was always wondering why we bloat the rows with such 0 dates instead of null.

alikon · 2018-08-30T18:54:12Z

administrator/components/com_admin/sql/updates/mysql/4.0.0-2018-08-29.sql

+
+-- Use 0 instead of '0000-00-00 00:00:00' to not get 'Invalid default value for ...'
+UPDATE `#__modules` SET
+	`publish_up` = CASE WHEN `publish_up` IN (0, '1000-01-01 00:00:00') THEN NULL ELSE `publish_up` END,


shouldn't be IN (0, '1000-01-01 00:00:00', '0000-00-00 00:00:00')

0 == '0000-00-00 00:00:00' in mysql. This is almost the same.

If we want to be strict then it should be IN ('0000-00-00 00:00:00', '1000-01-01 00:00:00') but then in phpmyadmin on mysql 5.7 I got error Invalid default value for ...

yes cause phpmyadmin have a standard/default sql_mode for mysql > 5.7

but for mysql server < 5.7 is always true that 0 == '0000-00-00 00:00:00' ?
not sure depending on sql_mode settings

alikon · 2018-08-30T18:55:51Z

we should do this now in 4.0 or never more

csthomas · 2018-08-30T23:25:30Z

Ready for core review and test:)

csthomas · 2018-08-31T00:08:10Z

By adding patch from curl https://patch-diff.githubusercontent.com/raw/joomla/joomla-cms/pull/19707.diff | git apply --reject you can test the update file for postgresql

laoneo · 2018-08-31T06:49:39Z

libraries/src/Table/Module.php

-		// Set publish_up, publish_down and checked_out_time to null date if not set
-		if (!$this->publish_up)
+		// Pre-processing by observers
+		$event = AbstractEvent::create(


Can we do the events in a new pr, so we can focus here on the null issue?

csthomas · 2018-08-31T09:26:32Z

In the "Files changed" tab it is probably not visible. I copy&paste checkIn() method from Table.php and pasted it into Module.php. Then I changed only the comment and two lines to change the sql zero date with NULL and empty string with null.

I will change the order of methods in the file, and the diff view should be more readable.

csthomas · 2018-08-31T09:41:43Z

The diff between Table.php and Module.php for checkIn() method.

I did not want to add changes in Table.php because it breaks other tables.

laoneo · 2018-08-31T09:44:33Z

Would be good if we can do it without copy paste. Can you not expand the query with an "or is null" in Table.php?

csthomas · 2018-08-31T10:03:27Z

Can you not expand the query with an "or is null" in Table.php?

Read carefully, this is an update query (SET ....):

- ->set($this->_db->quoteName($checkedOutTimeField) . ' = ' . $this->_db->quote($this->_db->getNullDate()));
+ ->set($this->_db->quoteName($checkedOutTimeField) . ' = NULL');

csthomas · 2018-08-31T11:52:56Z

I changed the code and now should be more readable, without overloading checkIn() method.

csthomas · 2018-09-04T12:58:36Z

@alikon @tonypartridge @ggppdk @Bakual I am asking you for a test, at least for a part, I know people are very busy.

If this go then I can work on the rest of tables.

ggppdk · 2018-09-04T15:54:49Z

I have tested this item ✅ successfully on b3e640c

@csthomas thanks for making such PRs to improve / fix Database API and Database handling

I have tested with MySQL
frontend and backend modules,

also looked to see if there are more places to be updated with usage of isNullDatetime() by looking at files that have #__modules as
but did not find any more places

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/21901.}

alikon · 2018-09-10T18:57:14Z

on my to do list

laoneo · 2018-09-26T11:05:25Z

Can we get here some tests? Would be nice to get it in.

alikon · 2018-11-14T20:02:53Z

I have tested this item ✅ successfully on a692e02

sorry for the delay

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/21901.}

csthomas · 2018-11-14T20:05:43Z

There is some conflicts so I have to resolve it. @wilsonge do you have time to review the code?

csthomas · 2018-11-14T21:44:36Z

I rebased PR. There were conflicts in sql files. Now there is need one more test.

laoneo · 2018-12-03T09:50:48Z

Would be good to get some more tests here and the conflicts fixed, so we can get it merged.

csthomas · 2018-12-03T10:11:21Z

I did what I could. Your turn.

ggppdk · 2019-03-19T17:20:44Z

#24151

So what is the status with this PR ?
Furthermore after this gets merged , more similar PRs will be needed

wilsonge · 2019-03-19T19:46:15Z

Let's go for this. I think we still have other compatibility issues. But this seems like a positive starting point.

ghost · 2019-03-20T05:58:01Z

@ggppdk can #24151 be closed or needs more Pull Requests?

wilsonge · 2019-03-20T09:40:07Z

@franz-wohlkoenig needs more. this rolls it out for modules. this will need rolling out everywhere that has a publish up/down column (com_content etc etc)

hardik-codes · 2019-03-20T10:41:53Z

Members may I help with further PRs ?
@wilsonge @franz-wohlkoenig

wilsonge · 2019-03-20T12:22:50Z

Fine by me

hardik-codes · 2019-03-20T12:29:58Z

Fine by me

Where in are the further changes to be made?

sakiss · 2019-06-14T11:32:27Z

Tested that and works.
Fixes the error messages related with empty published_up, published_down and checked_out_time in the module saving as well.

joomla-cms-bot added the PR-4.0-dev label Aug 28, 2018

This was referenced Aug 28, 2018

Add support for database expression joomla-framework/database#136

Closed

[4.0] Install: Incorrect datetime value #16788

Closed

alikon reviewed Aug 30, 2018

View reviewed changes

csthomas force-pushed the com_module_null_datetime branch from 40f8046 to 4049165 Compare August 30, 2018 23:01

csthomas changed the title ~~[4.0][POC] Replace the zero datetime value '0000-00-00 00:00:00' by the real NULL value~~ [4.0] Replace the zero datetime value '0000-00-00 00:00:00' by the real NULL value Aug 30, 2018

laoneo reviewed Aug 31, 2018

View reviewed changes

laoneo approved these changes Aug 31, 2018

View reviewed changes

laoneo added the Documentation Required label Sep 26, 2018

csthomas mentioned this pull request Nov 14, 2018

[4.0] Alpha 5 installer works fine, no errors, but site is "Empty"! #22974

Closed

Tomasz Narloch added 5 commits November 14, 2018 22:17

Replace null datetime '0000-00-00 00:00:00' to real database NULL

8b89ac4

Be more flexible if the update query won't be executed

438663f

Add chnages to sql files

c3d829f

Replace tick by doublequote in the postgresql update file

fcf7170

Postgresql - fix default NULL

8adfb4d

csthomas force-pushed the com_module_null_datetime branch from a692e02 to b9a01f7 Compare November 14, 2018 21:39

wilsonge approved these changes Nov 23, 2018

View reviewed changes

Merge branch '4.0-dev' into com_module_null_datetime

238c533

Merge branch '4.0-dev' into com_module_null_datetime

bb726fd

wilsonge approved these changes Dec 12, 2018

View reviewed changes

Merge branch '4.0-dev' into com_module_null_datetime

ae3f275

Merge branch '4.0-dev' into com_module_null_datetime

4ad0402

wilsonge merged commit 447983b into joomla:4.0-dev Mar 19, 2019

wilsonge added this to the Joomla 4.0 milestone Mar 19, 2019

Quy mentioned this pull request Mar 29, 2019

[4.0] Correcting com_fields created and modified dates #24234

Closed

wilsonge mentioned this pull request Apr 9, 2019

[4.0] Don't store a null date but instead store the value null #24535

Closed

alikon mentioned this pull request Apr 22, 2019

[4.0] Contact - nullable datetime fields #24675

Merged

twister65 mentioned this pull request Apr 26, 2019

[4.0] database importer exporter (rebase) #24733

Merged

alikon mentioned this pull request Jul 9, 2019

Question: Joomla 3/4 and MySQL 8 #25452

Closed

wilsonge mentioned this pull request Jul 17, 2019

[4.0] [installation] [mysql] - null date 1000-01-01 00:00:00 #25461

Closed

Quy mentioned this pull request Aug 1, 2019

[4.0] Setting datetime fields to real null value for com_content #25760

Closed

richard67 mentioned this pull request Aug 10, 2019

[4.0] Real null dates for mod_latestactions and mod_privacy_dashboard on new installation #25805

Merged

richard67 mentioned this pull request Oct 6, 2019

[4.0] [ModuleHelper] Check nullable datetime columns for real NULL values #26493

Merged

ChristineWk mentioned this pull request Oct 14, 2019

Some Notices after Joomla 4.00-alpha12-dev-Development-Update Package #26591

Closed

Uh oh!

Conversation

csthomas commented Aug 28, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary of Changes

TODO

Testing Instructions

Expected result

How to revert changes in your test db

Uh oh!

laoneo commented Aug 30, 2018

Uh oh!

alikon Aug 30, 2018

Choose a reason for hiding this comment

Uh oh!

csthomas Aug 30, 2018

Choose a reason for hiding this comment

Uh oh!

alikon Aug 30, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

alikon commented Aug 30, 2018

Uh oh!

csthomas commented Aug 30, 2018

Uh oh!

csthomas commented Aug 31, 2018

Uh oh!

laoneo Aug 31, 2018

Choose a reason for hiding this comment

Uh oh!

csthomas commented Aug 31, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

csthomas commented Aug 31, 2018

Uh oh!

laoneo commented Aug 31, 2018

Uh oh!

csthomas commented Aug 31, 2018

Uh oh!

csthomas commented Aug 31, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

csthomas commented Sep 4, 2018

Uh oh!

ggppdk commented Sep 4, 2018

Uh oh!

alikon commented Sep 10, 2018

Uh oh!

laoneo commented Sep 26, 2018

Uh oh!

alikon commented Nov 14, 2018

Uh oh!

csthomas commented Nov 14, 2018

Uh oh!

csthomas commented Nov 14, 2018

Uh oh!

laoneo commented Dec 3, 2018

Uh oh!

csthomas commented Dec 3, 2018

Uh oh!

ggppdk commented Mar 19, 2019

Uh oh!

wilsonge commented Mar 19, 2019

Uh oh!

ghost commented Mar 20, 2019

Uh oh!

wilsonge commented Mar 20, 2019

Uh oh!

hardik-codes commented Mar 20, 2019

Uh oh!

wilsonge commented Mar 20, 2019

Uh oh!

hardik-codes commented Mar 20, 2019

Uh oh!

sakiss commented Jun 14, 2019

Uh oh!

Reviewers

Assignees

Labels

csthomas commented Aug 28, 2018 •

edited

Loading

alikon Aug 30, 2018 •

edited

Loading

csthomas commented Aug 31, 2018 •

edited

Loading

csthomas commented Aug 31, 2018 •

edited

Loading