Joomla-cms: [4.0] Upgrade MySQL requirements for Joomla 4 to MySQL 5.7

[kernusr]

Is your feature request related to a problem? Please describe.

Now Joomla 4 has a minimum requirement mysql 5.6.
And:
1 - MySQL 5.6 end of life in February 2021 (less than 12 months)
2 - in the database there are fields is written as JSON encoded data. To simplify the life of extension developers, I would like to be able to work with JSON in MySQL.

Describe the solution you'd like

I suggest updating the minimum requirements to mysql 5.7.8

[richard67]

JSON database field type could be a problem because as far as I know MariaDB won't support that.

@wilsonge What's your opinion?

[kernusr]

@richard67 JSON data type, got support in MariaDB 10.2.7 https://mariadb.com/kb/en/json-data-type
But I'm not just talking about the JSON field type
With MySQL 5.7.8 and later I can execute a query, for example:

SELECT `attribs` - >> '$ .article_page_title' FROM` #__ content`

Because the data in the attribs field is JSON encoded

[richard67]

@kernusr Would that work in MariaDB, too? If so, beginning with which version? Maybe we should also update the MariaDB minimum version requirement?

[richard67]

For explanation: We use the same database drivers for MySQL and MariaDB, but we have of course different minimum version requirements. The support for MariaDB is more or less inofficial, so the minimum version for MariaDB is not listed in Joomla's documentation about required database versions.

[richard67]

Current minimum MariaDB version is 10.1 for J4.

[kernusr]

Current minimum MariaDB version is 10.1 for J4.

How can you talk about the minimum version of MariaDB if her support is not stated in the documentation.
As far as I understand, the user uses MariaDB at his own risk. There is no version check for the MySQL forks in jumla. Therefore, the user must solve all compatibility problems independently.
Otherwise, it is necessary to prescribe compatibility conditions not only with MariaDB but also with other MySQL forks (for example: Percona)

The talk about the "official" support of Mary was here https://github.com/joomla/joomla-cms/issues/14141 and it is closed

[richard67]

We check the minimum version for MariaDB at several places, e.g. when running installation we define it here https://github.com/joomla/joomla-cms/blob/4.0-dev/installation/src/Helper/DatabaseHelper.php#L30 and use it here https://github.com/joomla/joomla-cms/blob/4.0-dev/installation/src/Helper/DatabaseHelper.php#L150.

[kernusr]

Please look fo this https://endoflife.date/mariadb
MariaDB 10.1 has end of life in 17 Oct 2020

[kernusr]

We check the minimum version for MariaDB at several places, e.g. when running installation we define it here https://github.com/joomla/joomla-cms/blob/4.0-dev/installation/src/Helper/DatabaseHelper.php#L30 and use it here https://github.com/joomla/joomla-cms/blob/4.0-dev/installation/src/Helper/DatabaseHelper.php#L150.

I was inattentive and missed this moment

[richard67]

If MySQL supports the JSON stuff since version 5.7.8, we only have to find out which MariaDB version starts to support that, too, and raise the requirement for both to the right value. @kernusr Can you investigate that MariaDB version number?

[richard67]

@kernusr And why 5.7.8 and not just 5.7 as minimum for MySQL?

[kernusr]

As far as I know, MariaDB has received json support since the JSON data type, got support in MariaDB 10.2.7
But I will additionally check this on the test server and report the result

[richard67]

If you can investigate all that I can make the necessary pull request here for Joomla 4.

[kernusr]

@kernusr And why 5.7.8 and not just 5.7 as minimum for MySQL?

100% work with JSON is confirmed only from version 5.7.8
I can not guarantee that in MySQL 5.7.0 this will work correctly

If we hope that users install all software updates on time, then we can limit ourselves to checking version of MySQL 5.7

[brianteeman]

Hosts tend to be quite conservative when it comes to the version of mysql in use.

If I understand this request correctly there is nothing stopping you making it a requirement for using your code.

As there is nothing in core that would be using this I would be reluctant to change the minimum version for the entirety of Joomla - my 2c

[brianteeman]

I would also look at our usage stats before making any change

[richard67]

@brianteeman Agree on that. I think it has to be decided by release lead or PLT anyway. Just in case if it needs to make the PR, I will be available. Regarding stats: Do we already have useful stats for J4?

[kernusr]

I agree with @brianteeman that hosts are conservative
However, the release date of the RC and Stable version of the joomla are not defined
I am sure that the release of J!4 will take place after the end of support for MySQL 5.6 and MariaDB 10.1 and the hosts will have no choice how to update the MySQL version on their servers

[brianteeman]

Assume people won't change hosts when they upgrade from 3 :)

[ReLater]

A question to the extension developers: Why don't you establish a DB check in your extension's script.php? Gives you the freedom to restrict the requirements to whatever you want and it's simple.

I mean instead of kicking the next group of potential users because their DB version doesn't match. If the Joomla core doesn't need a version higher than xx, Joomla shouldn't establish restrictions from my point of view.

[ReLater]

and the hosts will have no choice how to update the MySQL version on their servers

They have and always had the choice to use older versions, too. That's the "living reality".

[HLeithner]

We choose MySQL 5.6 as requirement because we need a migration path between Joomla 3.10 and 4.0, Joomla 3.x could have problems with MySQL 5.7 because of the datetime column.

[richard67]

@HLeithner Thanks for the explanation ... I knew there was a reason for it, I just did not remember now which one 😄

[b2z]

Does 5.7 enforce strict standard? Why there could be a problem with datetime field?

[wilsonge]

Kinda correct. See the section https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes . It's configurable around the NO_ZERO_DATE flag but we can't really start to guarantee behavior. In MySQL 8 that then become enabled by default which is the hard break. Changing all our null dates is obviously a b/c break for anything querying content tables etc which is why we made the change in J4 but we obviously need a j3 and j4 version that support the same MySQL version