[4.0] Install: Incorrect datetime value

[810]

Steps to reproduce the issue

install from 4.0 branch

Expected result

Finish the installation

Actual result

Incorrect datetime value: '0000-00-00 00:00:00' for column 'lastResetTime' at row 1

System information (as much as possible)

Additional comments

[joomdonation]

I just tried to install (with sample data) and it worked OK for me.

[810]

did you tried nightly build or real branch. i have tried the real branch.

[joomdonation]

I tried real branch. I am using Windows, attached is the system information

Maybe someone else can test and confirm the error.

[ghost]

@810 can you give System information?

[810]

[ghost]

installed latest Staging without Error.

System information

4.0.0-dev macOS Sierra, 10.12.5 Firefox 54 (64-bit)

MAMP 4.1.1

• PHP 7.0.15
• MySQLi 5.6.35

[810]

I have tried PHP 5.6 and 7.1.4 Browsers: Internet explorer 11 & Chrome 59

[810]

on the first time, im also getting this notice: Notice: Undefined index: sample_file in C:\wamp\www\j4\installation\view\install\html.php on line 61

but now, i haven't got this error anymore.

[Bakual]

@franz-wohlkoenig This is about 4.0-dev branch, not staging.

@810 Did you get that error in the log? Or as an errorpage?

[brianteeman]

Pretty sure this is a mysql 5.7 iasue

[810]

@bakual i saw the notice behind the spinner, on top. But is showed also on the error.log

[roland-d]

I have mentioned this before, the NULL date has changed in MySQL 5.7. Where MySQL 5.6 accepts 0000-00-00 00:00:00 in 5.7 it must be 1001-01-01 00:00:00. See this PR as well: #11530

[mbabker]

This really needs to be addressed and at a priority, somehow. As we all work through upgrading systems and get on newer MySQL releases (or even the drop in replacements like MariaDB or Percona), we're going to hit MySQL 5.7 related issues more consistently (I myself just upgraded all of my dev tooling onto MySQL 5.7 and I'm now hitting a plethora of related issues with numerous projects that had been developed against MySQL 5.6 and its less strict structures).

[C-Lodder]

Also an issue for me. All started when I wasn't able to save a module, so tried to reinstall Joomla and getting the same error when install the database.

PHP 7.0.10 MySQL 5.7.14

[Bakual]

Is this only an issue when saving new nulldates or is it also an issue with existing records? Asking because adjusting the nulldate would actually be easy, we already have an API to fetch those and we just would have to conditionally adjust the date there. However if we need to update the existing records, then that will get very complicate and would need to be done for each table (also from 3rd parties).

[brianteeman]

from my limited experience its just when saving new nulldates

ie create new item or update item

[andrepereiradasilva]

in 4.0 the NO_ZERO_IN_DATE and NO_ZERO_DATE sql_mode could be forced in mysql databases but will it can be a big B/C break See also https://github.com/joomla/joomla-cms/pull/12494#issuecomment-255107581 for more info

[alikon]

from Mysql 5.7 manual

As of MySQL 5.7.4, the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes are deprecated. From MySQL 5.7.4 though 5.7.7, these modes do nothing when named explicitly. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). In other words, strict mode means the same thing in those versions as the pre-5.7.4 meaning of strict mode plus ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE.

[andrepereiradasilva]

humm didn't knew 4.0 was already using framework database package.

That i know of (didn't check the code) framework package does not force a strict sql_mode across mysql versions so it will lead to issues with different mysql versions.

[andrepereiradasilva]

BTW the ones forced before using the database framework package were this ones https://github.com/joomla/joomla-cms/pull/12494/files

[andrepereiradasilva]

@alikon

from Mysql 5.7 manual

As of MySQL 5.7.4, the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes are deprecated. From MySQL 5.7.4 though 5.7.7, these modes do nothing when named explicitly. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). In other words, strict mode means the same thing in those versions as the pre-5.7.4 meaning of strict mode plus ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE.

true, but in 5.7.8 oracle changed that again

In MySQL 5.7.4, the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes were changed so that they did nothing when named explicitly. Instead, their effects were included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). The intent was to reduce the number of SQL modes with an effect dependent on strict mode and make them part of strict mode itself.

However, the change to make strict mode more strict by including ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE caused some problems. For example, in MySQL 5.6 with strict mode but not NO_ZERO_DATE enabled, TIMESTAMP columns can be defined with DEFAULT '0000-00-00 00:00:00'. In MySQL 5.7.4 with the same mode settings, strict mode includes the effect of NO_ZERO_DATE and TIMESTAMP columns cannot be defined with DEFAULT '0000-00-00 00:00:00'. This causes replication of CREATE TABLE statements from 5.6 to 5.7.4 to fail if they contain such TIMESTAMP columns.

The long term plan is still to have the three affected modes be included in strict SQL mode and to remove them as explicit modes in a future MySQL release. But to restore compatibility in MySQL 5.7 with MySQL 5.6 strict mode and to provide additional time for affected applications to be modified, the following changes have been made: [...]

See https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html#mysqld-5-7-8-sql-mode

[andrepereiradasilva]

IMHO the bottom line is joomla should decide if

it should go to a strict approach (for uniformization across database engines and future compatibility) and do the needed changes now in 4.0 branch and assume a B/C break

or

use a less strict approach and deal with the issues that come with it (across database engines and possible future issues) along the 4.0 lifetime and have no (or less severe) B/C break.

[alikon]

...another additional consideration should be that MariaDB is becoming more divergent from mysql... and its adoption is growing

[roland-d]

The problem I also had using MySQL 5.7 is that I could no longer edit records via a GUI because the SQL query would error out due to incorrect values. After months of troubles, I have now downgraded to MySQL 5.6.

[mbabker]

I did a new install the other day and just did a mass search/replace to use the 1001 default date. Bad idea. At least with modules, the publish_down WHERE clause is causing major issues (and after copying that query into Sequel Pro and trying to change that clause, I couldn't get it to return results without just dropping it). So we aren't just looking at an install time issue here, we're going to have to systematically work through every piece of the system to make sure things are working.

As for the MariaDB thing. Last I knew there isn't an efficient way in PHP to distinguish MariaDB or Percona Server (or any other MySQL drop in replacement or fork) from native MySQL other than version_compare(), but that's a really flaky thing to rely on IMO. And since it all still uses the same C level libraries/binaries, we're still limited at what we can do at the PHP level to handle those differently.

[roland-d]

A mass replace won't work because there are checks all over the place that check against a nulldate value. I believe that is actually cleaned up to use the getNulldate() function but this function doesn't account for the pre-5.7 or post 5.7 date. This seems a trivial change to make for that function. It would be a start.

I am quite sure we run into exactly the same questions we had when building the UTF8MB4 patch, how do we deal with users moving sites between hosts with different MySQL versions. That is the first one that comes to mind. Is MySQL 5.7 going to be the minimum requirement for J4? If so, that would make life easier.

At least I am happy that this issue is finally getting addressed after all this time.

[brianteeman]

Is MySQL 5.7 going to be the minimum requirement for J4? If so, that would make life easier.

I don't think that's going to be a reality. Looking at WordPress usage stars (all I could find quickly) mysql 5.7 usage is a little over 4% https://wordpress.org/about/stats/

[mbabker]

Our's aren't much better. 6.6% of all time records in our stats database are MySQL 5.7.

[andrepereiradasilva]

when https://github.com/joomla-framework/database/pull/92 is merged into the cms code the issue should be fixed for now.

Note: you can try to do that PR changes manually by applying them in https://github.com/joomla/joomla-cms/tree/4.0-dev/libraries/vendor/joomla/database/src

anyway take in consideration the comment there https://github.com/joomla-framework/database/pull/92#issuecomment-310706294

but, for that, this is only a TEMPORARY fix. remember the issue is still there. When oracle, as they promissed, changes again the STRICT_TRANS_TABLES to include the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE the zero dates will have problems in the cms as joomla will be forcing the STRICT_TRANS_TABLES sql_mode.

So, before 4.0 is releasead, either joomla remove this sql_mode or corrects all zero dates across the cms code.

[alikon]

This seems a trivial change to make for that function. It would be a start.

@roland-d are quite trivial for start ? :smile:

[roland-d]

@alikon Are you asking me to submit a PR?

[alikon]

no asking for feedback about https://github.com/joomla-framework/database/pull/94

[wilsonge]

when https://github.com/joomla-framework/database/pull/92 is merged into the cms code the issue should be fixed for now.

Just updated the package

[mbabker]

So I ran into another fun quirk today.

Doing some work, I needed to add a new column to a table. This table has 2 datetime columns with the 0000 date as the default value. When I tried to add my column, MySQL wouldn't let me because those columns had an invalid structure, I had to change them to something valid before I could make my schema change.

So that could possibly raise issues for folks down the line.

[brianteeman]

yeah thats the same issue as i mentioned before. any changes to the db outside of joomla hit this

[andrepereiradasilva]

yeah thats the same issue as i mentioned before. any changes to the db outside of joomla hit this

yes, because that strict mode is in MySQL 5.7.5+ default mode (as commented in https://github.com/joomla/joomla-cms/pull/11530#issuecomment-310830883) so any apps using MySQL default mode will give that error because they are using using strict mode.

But that's not Joomla fault :)

[csthomas]

I would suggest to deprecate $db->getNullDate() which is used as

column = $db->quote($db->getNullDate())

and add a new method like $db->isNullDate('column')) which will be more flexible and can generate sql text like

(column IS NULL OR column = '0000-00-00 00:00:00' OR column = '1000-01-01 00:00:00')

[mbabker]

As if we weren't already having enough problems with MySQL 5.7, MySQL 8.0 hit general availability today. https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available/

[brianteeman]

on the plus side it makes utf8mb4 the default

[csthomas]

I added a PR in database framework, which may help with this issue.

[mbabker]

OK, I like that isNullDatetime() method. I think we need to keep getNullDate() working though, and the only way I can think to do that is to have some kind of version conditional check for the MySQL drivers.

public function getNullDate()
{
    static $checked = false;

    if (!$checked) {
        $this->nullDate = version_compare($this->getVersion(), '5.7', 'ge') ? '1000-01-01 00:00:00' : '0000-00-00 00:00:00';

        $checked = true;
    }

    return $this->nullDate;
}

[csthomas]

I think we need to keep getNullDate()

Yes, it may stay. IMO I would add a suggestion that isNullDatetime() is more advised.

the only way I can think to do that is to have some kind of version conditional check for the MySQL drivers.

This will be more complicated solution.

When the administrator changes MySQL version, Joomla will need to trigger sql query, which will change all rows to "new null date" '1000-00-00 00:00:00'.

Easier will be to not control the mysql version when comparing two dates, but only when inserting a new date.

There will be no problem with scanning with one or even several more dates

(publish_up IN ('1000-00-00 00:00:00', '0000-00-00 00:00:00') or publish IS NULL)

vs

(publish_up = '1000-00-00 00:00:00' or publish IS NULL)

The second query won't be noticeably faster.

Old articles have 0000-00-00 00:00:00 and a new can have 1000-00-00 00:00:00.

Another way can be to convert all zeros dates to 1000-00-00 00: 00: 00, starting with Joomla 4, regardless of the mysql version.

[brianteeman]

Another way can be to convert all zeros dates to 1000-00-00 00: 00: 00, starting with Joomla 4, regardless of the mysql version.

We really shouldn't touch a user's data

[alikon]

in https://github.com/joomla-framework/database/pull/120 my proposal for the getNullDate()

[mbabker]

That isNullDatetime() is good for helping with building a query that needs to deal with multiple potential null values. IMO getNullDate() should always report what is the current platform null date and should be version/config aware (as @alikon PR does). And definitely no on force changing user data.

[csthomas]

And definitely no on force changing user data.

I thing the same.

I see it as:

• getNullDate()

  should always report what is the current platform null date and should be version/config aware

• isNullDatetime() compares dates for the current platform but should not be version aware. Some rows may have old null dates and others new null dates.

[mbabker]

👍 👍

[wilsonge]

I like this solution a lot too!

[brianteeman]

@csthomas did you get anywhere with this. Was it resolved elsewhere?

[csthomas]

One more change is needed in the db framework, similar to https://github.com/joomla/joomla-cms/issues/16788#issuecomment-385579869 but I would like to return '1000-01-01 00:00:00' in all mysql/mariadb cases.

IMO I would like to change lines with:

protected $nullDate = '0000-00-00 00:00:00';

to

protected $nullDate = '1000-01-01 00:00:00';

(even for mysql 5.5).

This value will only be used for inserting / updating. @mbabker What do you think about it?