Are you trying to update directly from 3.6.4 to 4.0?

No, here was: 1.5 -> ... -> 3.4 -> 3.6 -> 3.9 -> 4.0

I tried several migration processes and in another issue it turned out that not all changes to the database were made. For example, I didn't have the com_search extension disabled (it has been replaced by com_finder)

Can you try updating to 3.10 before 4.0?

Yup, and still i have 0000-00-00 00:00:00 in created_time. (But it doesn't bother me now, because this server is configured differently.)

@bato3 Which method did you use to update? You know that since Joomla 3.5 we shall always use the Joomla Update Component to udpate. The old way, copying or unpacking the files from the update package and then running the database fix and extension discovery is not valid anymore. There is a big red box at the top of the descipriton for that old method which says that.

It seems to me that in some past during the long update hisotry either this method has been used, or some update has failed, so that database changes were missing, as you have pointed out in your previous comment with the reference to the other issue.

In addition, direct update from 3.9 to 4.0 is not supported. You should always go via 3.10. Just for information, it might not be the reason for this issue.

I believe that during the upgrade, a check should be made for the known columns for the wrong value and fixed.

One of the many update SQL script of Joomla 4 does exactly that. So if that has not happened in your case, this is another sign for something going wrong with your update.

Ok, I got It. 2019-09-29 make column changes. But it only checks modified time.

UPDATE `#__categories` SET `modified_time` = `created_time`, `modified_user_id` = `created_user_id` WHERE `modified_time` = '0000-00-00 00:00:00';

But these categories were imported from version 1.5 and do not have a creation date.

And I can see that Joomla 4.0 changes quite a few columns to datetime NOT NULL, but it doesn't check to see if there is any wrong data.

ALTER TABLE `#__ucm_content` MODIFY `core_created_time` datetime NOT NULL;
ALTER TABLE `#__ucm_content` MODIFY `core_modified_time` datetime NOT NULL;
ALTER TABLE `#__contact_details` MODIFY `created` datetime NOT NULL; 
ALTER TABLE `#__banners` MODIFY `created` datetime NOT NULL;
ALTER TABLE `#__newsfeeds` MODIFY `created` datetime NOT NULL;
ALTER TABLE `#__content` MODIFY `created` datetime NOT NULL;
-- ALTER TABLE `#__messages` MODIFY `date_time` datetime NOT NULL;
ALTER TABLE `#__redirect_links` MODIFY `created_date` datetime NOT NULL;
-- ALTER TABLE `#__action_logs` MODIFY `log_date` datetime NOT NULL;
ALTER TABLE `#__fields` MODIFY `created_time` datetime NOT NULL;
ALTER TABLE `#__fields_groups` MODIFY `created` datetime NOT NULL;
ALTER TABLE `#__privacy_requests` MODIFY `requested_at` datetime NOT NULL;
ALTER TABLE `#__privacy_consents` MODIFY `created` datetime NOT NULL;
ALTER TABLE `#__tags` MODIFY `created_time` datetime NOT NULL;
ALTER TABLE `#__user_notes` MODIFY `created_time` datetime NOT NULL;
ALTER TABLE `#__categories` MODIFY `created_time` datetime NOT NULL;
ALTER TABLE `#__users` MODIFY `registerDate` datetime NOT NULL;
-- ALTER TABLE `#__history` MODIFY `save_date` datetime NOT NULL;

We cannot assume that Joomla core is the only data provider for these columns. (In my case it was a migration from 1.5) So if you change data requirements, you also need to check data integrity.

If there's no creation date we can't force it. I guess we need to set it to the SQL null date and handle that case in our PHP?

Or we do it like once discussed and set it to the Joomla birthday, or to 1990-01-01 or anything else younger than 1970-01-01 but old enough to see it means "never before".

But shouldn't a migration of 1.5 data to a later Joomla version handle that?

I still think a valid creation date should have been set after having migrated data from 1.5 by the site admin. I think the Joomla core software can't decide for a website admin which creation date he or she wants to use in such a case, because it very likely will be either the date when the site went life, or when content was created for the very first time.

@wilsonge Question: Could it make sense to add such health checks to the pre-update checker, showing that there are e.g. categories not having a valid creation date? But if we add such a health check it can only check core tables, not extension-specific tables.

My understanding is this: if so far I have not needed a creation date, it is irrelevant to me.

Another thing is that I just noticed that I can change this date in the "publication" tab. I manually added the "required" attribute to the date field and it's better now because it shows:

Error
The form cannot be submitted as it's missing required data.
Please correct the marked fields and try again.

This message convinces me to look for an error in my actions, not as a write error to the database - where I think it's a bug with the system.

This is also a solution, although I personally would prefer it to be done automatically.

Or you can create a utility that reads the entire data structure, finds the datetime NOT NULL columns and gives the user a choice of what to do with it.

.

But shouldn't a migration of 1.5 data to a later Joomla version handle that?

From what I remember, Joomla itself does not support upgrades from 1.x to 2. You had to use extensions. I tested a few, chose the one that worked for me.

Conclusion

Last but not least, this problem affects only a limited percentage of users. In the production environment, I have such a configuration that I don't bother me.

So, we can give an option in the system configuration: "Validate Creation Dates", which would optionally add the attribute required="" to creation dates and be forced on servers with NO_ZERO_DATE configuration.

Adding release blocker to track this

@wilsonge Do you have ideas for a solution?

Best would be to have it in the pre-update checker in 3.10 so it is fixed before the update.

@bato3 would it be possible to get a backup of that site in the 3.10-alpha3 state so we have something to work on and build a patch against? If possible please send it to tobias.zulauf[AT]commuinity.joomla.org

If there's no creation date we can't force it. I guess we need to set it to the SQL null date and handle that case in our PHP?

@wilsonge We can and we should force a valid date and not an SQL null date (there is nothing like that, the '1970-01-01 ...' in PostgreSQL was always just an abuse of the oldest date), like we once discussed some 1 year ago when doing the null dates job in J4. I thought over it and understand the issue, and I've discussed possible solutions with @zero-24 . I'll make the necessary pull requests on weekend, if nothing unforeseen happens in the mean time.

@bato3 Stay tuned, we will fix it. No need for sending a backup.

OK. I mean I agree we should force a valid date. But the date itself will affect things like the category list view ordering. So we have to be extra careful. It's possible some sort of data integrity check in pre-update checker could be better (presumably saving the category would be enough to force a date to be created). Anyhow look forward to seeing your PR :)

Pull requests for this issue:

• Fix for update to J4: #31449 (Release Blocker).
• Postinstall message for 3.9 and 3.10 to show if there are old null dates for created dates for some core content in database: #31450 .
• Extend pre-update checker in 3.10 by showing the same as the postinstall message from #31450 will do: To be done.

As long as their are to be done or work in progress, I suggest to leave this issue open.

As PR #31449 for 4.0-dev has been merged, it remains to be done some PR(s) for 3.10-dev for a postinstall message and the pre-update check. I have branches for that ready but it may take a bit time since I'm currently a bit limited.

@wilsonge Shall we close this issue, or shall we leave it open until the other PR(s) and the documentation has been made?

@richard67 close this when we have PRs for all problems that came up with this issues, I removed the release blocker label meanwhile because the biggest problem is solved.

@richard67 close this when we have PRs for all problems that came up with this issues

@rdeutz Ok, will do so if remaining open things (PR for some post install message and same or other PR for showing some info in the pre-update checker, both pointing to some documentation to be done, too) are ready. I have it in preparation.

I removed the release blocker label meanwhile because the biggest problem is solved.

Agree. I just didn't want to decide that alone, that's why I haven't done that before.

@richard67 does this still need to be kept open?

does this still need to be kept open?

@brianteeman Depends on @wilsonge and @zero-24 if they want to have some post install message and pre-update check for it.

maybe create a new issue for that so it doesnt get lost and close this one.

I'll see what I can do in the next days (having a bit more time than usual this and next week due to personal holiday) and either close due to a PR, or make a new issue.

I think we can close this one since the main issue has been fixed with PR ##31449 and there haven't been any issues right now about the 1980 date used for fixing the missing created dates.