[#29239] - [4.0] com_content B/C break
- Expected Behaviour
- 27 May 2020
- Medium
- Build: 4.0
- # 29239
Steps to reproduce the issue
select an article with the following code:
$db = Factory::getDbo();
$nullDate = $db->quote($db->getNullDate());
$nowDate = $db->quote(substr_replace(Factory::getDate()->toSql(), '00', -2));
$query = $db->getQuery(true)
->select($db->quoteName(array('id', 'catid', 'title', 'alias', 'introtext', 'fulltext', 'state', 'publish_up', 'publish_down')))
->from($db->quoteName('#__content'))
->where($db->quoteName('state') . ' = 1');
->where('(publish_up = ' . $nullDate . ' OR publish_up <= ' . $nowDate . ')')
->where('(publish_down = ' . $nullDate . ' OR publish_down >= ' . $nowDate . ')');
$db->setQuery($query);
$result = $db->loadObjectList();
Expected result
A list of articles that fit the query
Actual result
no articles fit the query
System information (as much as possible)
There is a B/C breaking issue here. In Joomla 3.x the nulldate stored on datetime fields in the #__content table was 0000-00-00 00:00:00
in Joomla4 the nulldate is not stored but these fields are set as null or none
This breaks components / plugins that rely on a correct stored nulldate
Additional comments
The same possibly applies to other tables that have a datetime field
Joomla 3 #__content
Joomla 4 #__content
joomla-cms-bot
- 
The reasons for the change are well documented and the correct decision
0000-00-00 00:00:00 is NOT a current null date and deprecated by MySQL since version 5.6 or so.
This should be closed as expected and intended behaviour
See e.g. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date for details.
| Status | New | ⇒ | Expected Behaviour |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2020-05-27 10:23:22 |
| Closed_By | ⇒ | richard67 |
Closing as expected behavior. Please read the MySQL documentation about deprecation of the old "null dates" '0000-00-00 00:00:00'.
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/29239.
@richard67 That explains it. need to figure out how to support this without breaking the extension for 3.x. Trying to handle this in one extension as I do not want to support multiple versions of one extension :(
@brianteeman I'm following this guide, but didn't find any mention of it. I'm for sure not the oly one who is going to run into this. https://docs.joomla.org/Potential_backward_compatibility_issues_in_Joomla_4
@SharkyKZ will do
Thanks for following up, will close this one :)
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/29239.
Adding IS NULL condition to where statements should work in both J3 and J4.
->where('(publish_up = ' . $nullDate . ' OR publish_up <= ' . $nowDate . ' OR publish_up IS NULL)')
@SharkyKZ Cool, will try that. Will also try what will happen if 0000-00-00 00:00 is stored in the table (due to updating from 3.9 to 4.0) what will happen then and then also what will happen with different mysql versions that support or do not support this / have the config set to allow / disallow): this because the articlemodel in 4.0 is using IS_NULL and thus relying on mysql to do the conversion.
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/29239.
The minimum required MySQL version for Joomla 4.0 is MySQL 5.6, as far as I know, and this already handles well real null values for datetime columns. Conversion of the datetimes will be done for core tables like e.g. content or category by the update SQL scripts of J4.
@SharkyKZ now I now why I didn't use the ArticlesModel for this... it doesn't support filtering out based on publish_up / publish_down, so going directly into the database is the fastest way (otherwise I have to iterate through each result to see if it matches). Adding OR IS NULL into the query works :) #thanks
@richard67 that should then be really water-tied as using IS NULL (like it is now in the 4.0 articlesmodel will NOT return articles with value 0000-00-00 00:00:00 (just tested that: no conversion is done mysql side). Still think it should at least be mentioned on the 'Potential backward compatibility issues in Joomla 4' list, just to capture these experiences and avoid others stumble into the same. Remember: running into and solving these issues add to the costs for customers who are looking to upgrade, where the functionality of the website after the upgrade doesn't add any 'income'. Important to keep the costs as low as possible!
@HLeithner I will, I maintain several huge sites (80K+ articles) where articles are dynamically created / imported from ftp, xml, api, etc sources. Customer is going to use the investment needed to update to J4 to migrate away to another CMS :( but I can use this (when time permits as it is unpaid work for me) to see what issues might arise in the 'real world'
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/29239.
@Ruud68 If you say there is no conversion it is definitely wrong, see e.g. here for the content table https://github.com/joomla/joomla-cms/blob/4.0-dev/administrator/components/com_admin/sql/updates/mysql/4.0.0-2019-09-14.sql or here for the menu table https://github.com/joomla/joomla-cms/blob/4.0-dev/administrator/components/com_admin/sql/updates/mysql/4.0.0-2019-03-30.sql . If there was no conversion for you: Have you maybe updated from an older alpha version of 4.0? Updating between 4.0 version before the upcoming beta is not supported. If you update a 3.10 to 4.0. the conversion should be done when updatingin the right way, using the Joomla Update Component. Any old way of updating like e.g. just copying the files and then running the database fix is not supported anymore since Joomla 3.5
@richard67 sorry, that is not what I meant, I did a FRESH install so there was no updating. I was just stating that the updating routine should be 'water-tied' because it otherwise would lead to unexpected behavior ( = IS NULL not returning the 0000 etc records) :)
Which updating routine do you mean then if not updating? Am confused.
@richard67 when updating an existing 3.9 site (which is what users will be going to do), this is not what I did, I did a fresh install. Sorry for the confusion :)
As I've shown with my link there are update sql scripts changing the old pseudo null dates on both kinds of databases.
Furthermore, there is no (official) update path from 3.9 to 4, only from 3.10 to 4 ;-)
Furthermore, there is no (official) update path from 3.9 to 4, only from 3.10 to 4 ;-)
I just noticed that :), will be testing this when it is possible. doing these kinds of updates on content tables with 80k+ articles will for sure freak the server out :)))
Test with 80k+ articles is really useful. Please report back here if ok or if problems. Thanks in advance.
last upgrade for 3.9.x I did for this customer stopped during the upgrade. Had to do the sql upgrades manually step by step. And that was on a 4 cpu / 12GB memory vps... one of the (many) reasons why this customer will not be upgrading :S #cannotwinthemall
Time to update your code.