the update routine should work in both ways (upgrade and downgrade of mysql version) - downgrade is experimental.

How does Joomla know you have upgraded mysql and to make the change?

there is a new table, which simply saves the nullDate value

@richard67 @wilsonge @mbabker @andrepereiradasilva @aschkenasy Guys, can we have your input on this please?

I still dont understand how we are notifying users on existing sites when there server has been updated to 5.7 or how we are notifying users who installed originally on 5.7 and have moved hosts to a server running 5.6

Honestly I don't think we can support people downgrading. We have the same issue with moving from a utf8mb4 compliant server to one that doesn't support utf8mb4 - it just fails. Admittedly downgrading these dates should be more straightforward than the utf8mb4 downgrade. But it's an interesting question if we want to set a precedent....

OK so thats downgrading - what about upgrading

@roland-d Sorry I can not give you much input, have no idea about the changes on MySQL's null dates. But as far as I could see Michael already mentioned the issues on implementation.

@brianteeman Regarding upgrading of MySQL the current implementation of the utf8mb4 conversion would (respective the check in the schema manager) would list the conversion to be done as an open database problem after moving data from non-utf8mb4 to utf8mb4 database or upgrading MySQL with the same effect. This seems to be missing for this null date thing.

But if after such migration/upgrade nobody looks at the database schema manager display, he/she/it never will notice that. So it needs an alert similar to post install messages or the 2.5 end of support plugin's notice, which is shown to any backend user as soon as he/she/it logs in to backend.

And to make it perfect, the extensions like e.g. weblinks, which use a schema update to do the utf8mb4 conversion (and could do the same fot this null date thing) could provide in their XML the name (ir in case something was forgotten, a list of names) of which schema updates to be used, and the core could run them again if a migration/ugrade case has been detected and the conversion is started with the db fix button (or maybe a new separate button).

The migration/upgrade case is - if nothing went wrong - to be done only once, and this for core and all extensions. For utf8mb4 conversion and this null date thing it needs separate handling because of different conditions regarding MySQL version numbers to be checked, but beside this the handling should be the same, i.e. let the db fixer report it to be done and the fix button perform it then, plus the migration case detection on login to backend with the message telling to go to the db fixer display (including the link to it).

The downgrade case cannot be supported for utf8mb4 conversion because MySQL implicitely enlarges columns of type xxxTEXT to the next larger yyyTEXT tyoe, e.g. tinytext to mediumtext (or so) when doing the conversion, but the columns stay this big then (making them smaller by a procedure would possibly cause data loss). For the null date thing i am not sure if this is an issue, i.e. if the new null date was a valid date before or not. If not, then downgrade would be possible, but if it was valid before, it cannot be done vice versa.

So far my thoughts, sorry if I cannot really help more. I never was MySQL expert, only Oracle SQL expert, and the stuff for the utf8mb4 conversion I had to find out myself by investigation, but now I am in a new job and not have this much time anymore, otherwise I would implement the utf8mb4 conversion for extensions on migration/upgrade case and the alerting of the backend user, and for sure could also help more with the null date thing. But I would have to investigate for that, and I do not see me having time and energy in the next few weeks or even months - new job is hard after 15 months sabatical ;-)

downgrading should be possible with current code, but the testing was pretty complex even before with multiple virtual machines, so it's experimental.

@richard67 I appreciate your input and wish you the best in the new job.

As for notifying users their server has been updated to MySQL 5.7 we can't do that. Well we can but that means a check on every page load and that is not what we want. The only two ways I see we can do this is:
1. User installs a new Joomla version and we perform the fix if needed
2. User goes to the Database page and is told the database needs fixing and can fix it by clicking the Fix button

@wmchris Both ways are implemented now or not yet?

@roland-d it doesn't hook to the fix database, it's hooked to the upgrade and the install routines. I dont know if the script.php is called from fix databases, if not, then only the new table for the checks will be created

@wilsonge Do you know if the script.php is called when I FTP the files to the site and then hit the Fix button?

As for notifying users their server has been updated to MySQL 5.7 we can't do that. Well we can but that means a check on every page load and that is not what we want. The only two ways I see we can do this is:
1. User installs a new Joomla version and we perform the fix if needed
2. User goes to the Database page and is told the database needs fixing and can fix it by clicking the Fix button

Without that its just a whole heap of problems.

You mean without the notification?

The 2 steps I outlined that is how the UTF8MB4 stuff works as far as I know.

I personally think it would be the best idea to force the patch after a login - because using the old style with a mysql server compiled on strict mode could cause a database interlock if the patch is not applied.

Regarding the 2 items mentioned above: That's correct for utf8mb4 stuff. The null date thing in this pr seems to support only the 1st method.

@roland-d - it should now be triggered when pressing the fix button, testing takes some time
btw: i updated the testing instructions.

@wmchris You trigger the conversion now on a database fix, but you do not have added a method to show it as an open database problem in the db fixer display. So the backend user will not see any notice or know anything about it, as far as I understand your PR as it is now.

@roland-d See also my comment here related to Chris' previous comment.

@richard67 already talked to @roland-d and we didn't find a decent way of how to inform the administrator. i personally would suggest the database on login and display the error message, but this doesn't comply with the joomla guidelines and would - of course - add another sql query to every login which is 99% of the time useless.

The db fixer will - of course - tell you, that your DB is outdated and suggest a fix. But this could only happen if you upload the new joomla version using FTP.

We've prepared two virtual machines (vmware, compatible with vmware player) prepared to test this issue. On the first machine is a default debian,APM(apache, php and mysql) installation including the supplied modified joomla version in the folder /var/www/html/joomla-cms. This machine also includes a mini fake joomla update service in the folder /var/www/html/updater. joomla is not installed, yet.

The second machine is based on debian and a modified version of the mysql 5.7 deb packages, which are known to trigger this error. Both machines are compatible to each other.

Login credentials for everything:
Username: root
Passwort: test

Download:
http://icampus.thm.de/wpws_2016/Virtual%20Machines.rar

Mirror:
http://ul.to/fhq4y9sb

@richard67 Are the issues you addressed now taken care of?

Now we just need to look for people to test this I guess so we can comment further if needed.

Am on business trip this week, cannot really check

@roland-d I could meanwhile have a look, but I do not see that with this pull request there is the null date conversion being reported as an open database problem. So no, the issues I saw are not handled. But maybe I am wrong, am not used in working with iPhone on github.

@richard67 did you have time to check this?
@alikon / @andrepereiradasilva can you please review?

@jeckodevelopment No.

I have tested this item 🔴 unsuccessfully on 4c468b7

Tried new installation on MySQL 5.7 and got an error message Table 'dbname.#__menu' doesn't exist SQL=INSERT INTO '#__menu' during installation (step 4). I downloaded the J! from @wmchris his staging repo.

I've added the used puphpet images here for convenience.

mysql5.7 - apache.zip
mysql5.6 - apache.zip

I see this is milestoned for 3.7.3, but it had a failed test 4 months ago and the code hasn't been updated since the last comments were given last August. What's the status here?

@mbabker The status is that the issue is still present but I don't think that the TS is going to continue working on this.

just retested staging on my mysql5.7 server and i had no issue when using joomla itself. the problem only occurred for me when I try and edit a record with phpmyadmin or similar

I have the same experience and actually wonder why it doesn't error out when using Joomla but perhaps because we don't run strict mode just yet.

From reading the documentation about NO_ZERO_DATE, I would expect an error only on insert/update (and in strict mode). I doubt reading data with 0000-00-00 00:00:00 would produce errors.
So we just need to make sure we have the proper nulldate when saving, which is simple.

The tricky part is all those places where we have select queries with a conditional using a nulldate. I'm not sure how MySQL does behave here since the data actually may contain the "old" 0000-00-00 or the new date.
But maybe we should consider using NULL instead nulldate, that would solve a few issues here as well 😄

For the reasons I posted before (e.g. no info in database fixer view about nulldate conversion) and which have not been solved yet, and for the reasons @Bakual stated above, I am against this PR.

@brianteeman The reason why in phpmyadmin problems show up and in Joomla not may really be the strict mode, like @roland-d assumed, too.

Just some compilation of the current scenario of MySQL sql strict modes:

Joomla Behaviour

• Joomla 3.x removes the default sql strict mode in all MySQL versions, ie, DOESN'T use sql strict modes in ANY MySQL version.

• Joomla 4.x (database framework 2.0), as it is, removes the default strict mode in all MySQL versions and overrides with a custom sql strict mode (ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER and NO_ENGINE_SUBSTITUTION).

  This is actually the MySQL 5.7.8+ default sql strict mode without, because of this issue, the dates strict modes (NO_ZERO_IN_DATE and NO_ZERO_DATE).

From what i can gather from the oracle documentation this is the current scenario of MySQL 5.7.x versions...

Default MySQL 5.7.x sql modes

• From MySQL 5.7.0 to 5.7.4: NO_ENGINE_SUBSTITUTION

• MySQL 5.7.5 and 5.7.6: NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES (incorporating NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO)

• MySQL 5.7.7: NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES (incorporating NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO) and NO_AUTO_CREATE_USER

• MySQL 5.7.8+: NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ZERO_IN_DATE, NO_ZERO_DATE and ERROR_FOR_DIVISION_BY_ZERO

Future (MySQL 5.8.x?): STRICT_TRANS_TABLES will incorporate again ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_IN_DATE and NO_ZERO_DATE (like from 5.7.4 to 5.7.7) and more?

References

• https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting
• https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes

may i have some feedback on this joomla-framework/database#94

IMHO enable strict mode in 4.x and go for the NULL date in all scenarios on 4.x (as 3.x, as explained above, should have no issues with that)

@alikon Feedback given there .. but not sure if it is very helpful.

IMHO '1000-01-01' should not be misused as pseudo-NULL date, instead of '0000-00-00' or NULL.

I agree with @Bakual : We should just use NULL instead, which is meant for that.

But as I understand , this issue has been solved by Oracle for 5.7.8 in strictSQL mode, and might re-appear only in 5.8.x ?

we still need to manage situation where :

• datetime field cannot be NULL (es: created_time)
• datetime field cannot be NULL and must have a default (es: now())

not only

• datetime field NULLable (es: checked_out_time)

Sure, but for those not nullable fields we should use better defaults or starting values than "0000-00-00" or "1000-00-00".

For the nulldate problem, this might be a solution:

As of MySQL 5.7.7, REPAIR TABLE upgrades a table if it contains old temporal columns in pre-5.6.4 format and the avoid_temporal_upgrade
system variable is disabled. If avoid_temporal_upgrade is enabled, REPAIR TABLE ignores the old temporal columns present in the table and
does not upgrade them.

To check for tables that contain such temporal columns and need a rebuild, disable avoid_temporal_upgrade before executing CHECK TABLE ...
FOR UPGRADE.

To upgrade tables that contain such temporal columns, disable avoid_temporal_upgrade before executing REPAIR TABLE or mysql_upgrade.

I am closing this for several reasons

1. The creator is no longer available as stated by @roland-d
2. Joomla itself has no problem because it doesnt use strict mode

   joomla-cms/libraries/joomla/database/driver/mysqli.php

   Line 177 in 05fd1d9

   mysqli_query($this->connection, "SET @@SESSION.sql_mode = '';");

3. The conversion and associated potential problems is therefore not required

@brianteeman Fine to close this but this is however an issue nonetheless. That is because we use an invalid default value for datetime fields in MySQL 5.7 and up. In addition, it is impossible to modify the database outside of Joomla with PhpMyAdmin for example. Of course we are going to strict mode in Joomla 4, not sure how that will impact things. That is all I want to say about this :)