#206 Please avoid using MySQL 8.0 feature for many shared web hosting

I checked latest commit ( 4884a97223).

After that I cannot access my timeline due to a DB error.

This commit requires a MySQL 8.0 feature JSON_TABLE (MySQL 8.0.4 or later) in classes/Memcached_DataObject.php.

My GNU social server is hosted by a shared web hosting service (MySQL 5.7, PHP 7.3).

So I will try fixing this by moving case 'mysql': below case 'mariadb':.

But the next WITH and VALUES clauses also require MySQL 8.0 (MariaDB 10.3.3 or later).

• WITH: https://dev.mysql.com/doc/refman/8.0/en/with.html
• VALUES: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html

I am not familiar with SQL.

Could you rewrite this code without the WITH and VALUES clauses, @XRevan86?

Many shared web hosting services use MySQL 5.6 or 5.7 (MariaDB 10.1 or 10.2) for WordPress requirements.

Would we avoid using a MySQL 8.0 feature?

This commit requires a MySQL 8.0 feature JSON_TABLE

Just for clarity, it is planned to be added in MariaDB 10.6. I'll change the version condition when that is a certainty.

But next WITH and VALUES clauses also require MySQL 8.0

WITH is available since MariaDB 10.2, VALUES – since MariaDB 10.3.
In Oracle MySQL they are indeed only present starting with MySQL 8.0.

So I will try fixing this by moving case 'mysql': to below case 'mariadb':.

That will not work even with Oracle MySQL 8.0, their implementation of VALUES picks a different subset of the standard (requires the optional ROW keyword, while MariaDB and PostgreSQL don't accept it at all).

for WordPress requirements

Are you saying that shared hostings won't move a muscle until WordPress forces them to by moving the bar higher?
Because MySQL 5.7 is increasingly becoming more and more of an antique by the year.

Would we avoid using a MySQL 8.0 feature?

I need WITH here at least to improve performance of the heavily used method multiGet.

I need WITH here at least to improve performance of the heavily used method multiGet.

OK. Performance is important. And Thanks for telling me to differ logic for MariaDB and MySQL query.

So I created patch for MySQL 5.7 (diff1.patch). Would you check it? My GNU social server is well done by this patch in latest commit.

Are you saying that shared hostings won't move a muscle until WordPress forces them to by moving the bar higher? Because MySQL 5.7 is increasingly becoming more and more of an antique by the year.

Here is Oracle MySQL support policy (p. 21) (http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf).

EOL of MySQL 5.7 = Oct 2023, 5.6=Feb 2021.

Many shared hosting is based on WordPress requirements. And WordPress requirements is based on vendor EOL.

In Japan, there is no shared hosting for supporting MySQL 8.0. Do you know shared hosting for supporting MySQL 8.0?

MySQL 5.7 will be used until Oct 2023 for EOL (for 3 years remainig). I think drop out of MySQL 5.7 is too early now.

If it is important for using latest DB feature, would we prepare for non latest DB logic?

For example, prepare $config['db']['version'] item in config.php. Then if $config['db']['version'] is specified and version is not latest, run for compatible SQL by if statement.

This gives choice to user to use GNU social on running old (cheap) shared web hosting.

It seems uploading feature is not work in NotABug.org.... So here is my patch.

diff --git a/classes/Memcached_DataObject.php b/classes/Memcached_DataObject.php
index 0d22eb8628..adce5f6a88 100644
--- a/classes/Memcached_DataObject.php
+++ b/classes/Memcached_DataObject.php
@@ -163,17 +163,27 @@ class Memcached_DataObject extends Safe_DataObject
                 break;
             case 'mysql':
             default:
+                // Delivers an empty set
+                if (count($vals_escaped) == 0) {
+                    $vals_escaped[] = 'NULL';
+                }
+                $subquery = "";
+                foreach ($vals_escaped as $i => $val) {
+                    if ($i == 0) {
+                        $subquery = "SELECT ${val} AS ${keyCol},  ${i} AS ${keyCol}_pos" ;
+                    } else {
+                        $subquery .= " UNION ALL SELECT ${val}, ${i}";
+                    }
+                }
                 $obj->_join = "\n" . sprintf(
                     <<<END
-                    RIGHT JOIN JSON_TABLE(
-                      JSON_ARRAY(%s), '$[*]' COLUMNS (
-                        {$keyCol} {$vals_cast_type} PATH '$',
-                        {$keyCol}_pos FOR ORDINALITY
-                      )
+                    RIGHT JOIN (
+                      %s
                     ) AS {$join_tablename} USING ({$keyCol})
                     END,
-                    implode(',', $vals_escaped)
+                    $subquery
                 );
+                break;
         }
 
         // Filters both NULLs requested and non-matching NULLs

EOL of MySQL 5.7 = Oct 2023, 5.6=Feb 2021.

EOL for CentOS 7 = Jun 2024, CentOS 6 = Nov 2020.
So they have very long support periods. Doesn't mean anyone should continue using them with a clear conscience.

Many shared hosting is based on WordPress requirements. And WordPress requirements is based on vendor EOL.

WordPress supports the latest MariaDB and Oracle MySQL fine, it's not against WordPress requirements to use something not ancient.

WordPress also works with PHP 5.6.20+ and MySQL 5.0+

And they also write this. And latest GNU social won't work on PHP 5.6 and MySQL 5.0, that's just how it is.

In Japan, there is no shared hosting for supporting MySQL 8.0. Do you know shared hosting for supporting MySQL 8.0?

Admittedly, I am no expert on shared hosting providers.
And I should point out that Oracle MySQL 8.0 is not officially supported by GNU social.
The currently supported DBMS are: PostgreSQL 11+ and MariaDB 10.3+.

I looked around the shared hosting provider of your choice, GMO DigiRock, and couldn't even find evidence of Oracle MySQL 5.7 there. They don't disclose their offerings much.
I do see that they have PHP 7.4 though, so I don't know why you picked PHP 7.3.
GNU social requires PHP 7.3+, it's not the maximum, it's the minimum. Same with WordPress, really.

Either way, I'm sure there are plenty of shared hostings that do provide something newer than Oracle MySQL 5.7.
There is this page: Web Hosting Providers Offering MariaDB for one thing.

Fixed up your patch:

--- a/classes/Memcached_DataObject.php
+++ b/classes/Memcached_DataObject.php
@@ -100,9 +100,18 @@ class Memcached_DataObject extends Safe_DataObject
         $db_type = common_config('db', 'type');
         if ($db_type === 'mysql') {
             $tmp_obj = new $cls();
-            $tmp_obj->query('SELECT 0 /*M! + 1 */ AS is_mariadb;');
-            if ($tmp_obj->fetch() && $tmp_obj->is_mariadb) {
-                $db_type = 'mariadb';
+            $tmp_obj->query(
+                <<<'END'
+                SELECT (0 /*M! + 1 */) <> 0 AS is_mariadb,
+                    (0 /*!80004 + 1 */ /*M!100303 + 1 */) = 0 AS is_old_mysql;
+                END
+            );
+            if ($tmp_obj->fetch()) {
+                if ($tmp_obj->is_old_mysql) {
+                    $db_type = 'old_mysql';
+                } elseif ($tmp_obj->is_mariadb) {
+                    $db_type = 'mariadb';
+                }
             }
         }
 
@@ -116,11 +125,17 @@ class Memcached_DataObject extends Safe_DataObject
             } else {
                 $val_escaped = "'{$obj->escape($val)}'";
             }
-            if ($db_type !== 'mariadb') {
-                $vals_escaped[] = $val_escaped;
-            } else {
-                // A completely different approach for MariaDB (see below)
+            if ($db_type === 'mariadb') {
                 $vals_escaped[] = "({$val_escaped},{$i})";
+            } elseif ($db_type === 'old_mysql') {
+                if ($i == 0) {
+                    $vals_escaped[] = "SELECT {$val_escaped} AS {$keyCol}, "
+                                    . "{$i} AS {$keyCol}_pos";
+                } else {
+                    $vals_escaped[] = "SELECT {$val_escaped},{$i}";
+                }
+            } else {
+                $vals_escaped[] = $val_escaped;
             }
         }
 
@@ -164,6 +179,19 @@ class Memcached_DataObject extends Safe_DataObject
                     implode(',', $vals_escaped)
                 );
                 break;
+            case 'old_mysql':
+                // Delivers an empty set
+                if (count($vals_escaped) == 0) {
+                    $vals_escaped[] = "SELECT NULL AS {$keyCol}, "
+                                    . "0 AS {$keyCol}_pos LIMIT 0";
+                }
+                $obj->_join = "\n" . sprintf(
+                    "{$join_keyword} (%s) AS {$join_tablename} USING ({$keyCol})",
+                    implode(' UNION ALL ', $vals_escaped)
+                );
+                break;
             case 'mysql':
             default:
                 $obj->_join = "\n" . sprintf(

But the problem with that approach is that it's pretty lengthy and it is even more verbose than what was there before, because… well, SELECT 1 AS id, 0 AS id_pos UNION ALL SELECT 2,1 UNION ALL SELECT 3,2 UNION ALL SELECT 4,3 UNION ALL SELECT 5,4 for VALUES (1,0),(2,1),(3,2),(4,3),(5,4) or JSON_ARRAY(1,2,3,4,5).

WordPress supports the latest MariaDB and Oracle MySQL fine, it's not against WordPress requirements to use something not ancient.

You are right. But WordPress support non latest DB, so shared hosting providers may use non latest DB for their convinience.

My shared hosting provider GMO DigiRock (CORESERVER) show them spec in here (https://www.coreserver.jp/spec/). They support MySQL 5.7 and PostgreSQL 9.6.

I selected PHP 7.3 for more stability than PHP 7.4.

Either way, I'm sure there are plenty of shared hostings that do provide something newer than Oracle MySQL 5.7. There is this page: Web Hosting Providers Offering MariaDB for one thing.

Thanks for information. I saw MariaDB global supporting provider in first time.

MariaDB is forked from MySQL 5.5. And non latest version, MySQL and MariaDB is high compatible.

GNU social v1.x supports MariaDB 5+, so I wish keep compatibility for non latest DB...

I understand lengthy and less performance is not good for developer, but I think it is also important for supporting non latest DB for user.

I am satisfied with your fixed up code for supporting MySQL 5.7 anyway. Thanks!

MySQL 5.7 does not support JSON_TABLE but supports JSON_ARRAY.

I will try rewriting more better SQL with JSON_ARRAY. Please wait some days.

My shared hosting provider GMO DigiRock (CORESERVER) show them spec in here (https://www.coreserver.jp/spec/).

Didn't see that page…
"sqlight" is an interesting option to have :-). So is "python 2/3" with no specifics.

They support MySQL 5.7 and PostgreSQL 9.6.

Oof, well, at least not PostgreSQL 9.2 as their other resources claim.

I selected PHP 7.3 for more stability than PHP 7.4.

Really doesn't matter unless there are some issues specific to the shared hosting.

MariaDB is forked from MySQL 5.5. And non latest version, MySQL and MariaDB is high compatible.

Compared to Oracle MySQL 5.7 new enough MariaDB is highly compatible, yes. Not in the other direction and not with Oracle MySQL 8.0 however.

But WordPress support non latest DB, so shared hosting providers may use non latest DB for their convinience.

It's like they don't want their customers to have the best coverage possible, seems self-defeating.

MySQL 5.7 does not support JSON_TABLE but supports JSON_ARRAY.
I will try rewriting more better SQL with JSON_ARRAY. Please wait some days.

JSON_ARRAY is used with JSON_TABLE, you could try using json_contains and json_search, but without WITH or SET STATEMENT it is problematic not to send the key values twice.
And won't be able to return exactly the tuples in order and amount requested, duplicates or not. But I added that just because it's easy with how it's done now, that can be removed, not that important.

Hi. I tried using JSON_ARRAY, but I gave up.

In my study, this answer (Convert JSON array in MySQL to rows - Stack Overflow) is the nearest, but this answer needs multiple line SELECT. This is the same as UNION ALL. So I gave up.

Instead, I checked your fixed code on my server. I found a bug. Last 3 lines, $join_keyword variable is used, but this variable is not assigned. I replaced this to RIGHT JOIN, then code works well.

+                $obj->_join = "\n" . sprintf(
+                    "{$join_keyword} (%s) AS {$join_tablename} USING ({$keyCol})",
+                    implode(' UNION ALL ', $vals_escaped)

After fix this bug, please commit it and close this issue.

Anyway, If it works on MySQL 5.7, there is no problem. Thanks!

Pushed a test commit, please, try.

@Xrevan86. Thanks for commit. I checked your commit (4290ec312). MySQL 5.7 works well anyway.

But after this commit, my home time line needs some minutes for finishing page rendering. It is too slowly.

Previous commit + your last patch (with fixed bug in this issue) is OK (I checked).

But your commit (4290ec3120) is a quite different and too many volume from this patch.

Also if there is no problem, difference is OK.

Your commit use some JSON functions. Is JSON functions heavy?

I was satisfied with your last patch. Why did you change commit from last patch?

If there is no problem, it is all OK how implementation.

Your commit uses some JSON functions. Are JSON functions heavy?

@senooken, yes and no. Because json_search can only work with strings, I tried casting every integer, which is probably the bit that's failing miserably.

After your comment, it seems your commit (4290ec3120) was dropped out from commit history.

What did you think about this commit?

I think your last patch is OK ever last commit (adc689cb15). I want to include this patch.

Is there a problem? To make sure I show this patch again in following.

This patch can be imported to run patch -p 1 <[patch-file-name] command in root directory (I think you know...).

diff --git a/classes/Memcached_DataObject.php b/classes/Memcached_DataObject.php
index 0d22eb8628..d71afed57c 100644
--- a/classes/Memcached_DataObject.php
+++ b/classes/Memcached_DataObject.php
@@ -98,9 +98,18 @@ class Memcached_DataObject extends Safe_DataObject
         $db_type = common_config('db', 'type');
         if ($db_type === 'mysql') {
             $tmp_obj = new $cls();
-            $tmp_obj->query('SELECT 0 /*M! + 1 */ AS is_mariadb;');
-            if ($tmp_obj->fetch() && $tmp_obj->is_mariadb) {
-                $db_type = 'mariadb';
+            $tmp_obj->query(
+                <<<'END'
+                SELECT 0 /*M! + 1 */ AS is_mariadb,
+                    (0 /*!80001 + 1 */ /*M!100303 + 1 */) = 0 AS is_old_mysql;
+                END
+            );
+            if ($tmp_obj->fetch()) {
+                if ($tmp_obj->is_old_mysql) {
+                    $db_type = 'old_mysql';
+                } elseif ($tmp_obj->is_mariadb) {
+                    $db_type = 'mariadb';
+                }
             }
         }
 
@@ -114,11 +123,17 @@ class Memcached_DataObject extends Safe_DataObject
             } else {
                 $val_escaped = "'{$obj->escape($val)}'";
             }
-            if ($db_type !== 'mariadb') {
-                $vals_escaped[] = $val_escaped;
+            if ($db_type === 'mariadb') {
+                 $vals_escaped[] = "({$val_escaped},{$i})";
+            } elseif ($db_type === 'old_mysql') {
+                if ($i == 0) {
+                    $vals_escaped[] = "SELECT {$val_escaped} AS {$keyCol}, "
+                                    . "{$i} AS {$keyCol}_pos";
+                } else {
+                    $vals_escaped[] = "SELECT {$val_escaped},{$i}";
+                }
             } else {
-                // A completely different approach for MariaDB (see below)
-                $vals_escaped[] = "({$val_escaped},{$i})";
+                $vals_escaped[] = $val_escaped;
             }
         }
 
@@ -161,6 +176,17 @@ class Memcached_DataObject extends Safe_DataObject
                     implode(',', $vals_escaped)
                 );
                 break;
+            case 'old_mysql':
+                // Delivers an empty set
+                if (count($vals_escaped) == 0) {
+                    $vals_escaped[] = "SELECT NULL AS {$keyCol}, "
+                                    . "0 AS {$keyCol}_pos LIMIT 0";
+                }
+                $obj->_join = "\n" . sprintf(
+                    "RIGHT JOJIN (%s) AS {$join_tablename} USING ({$keyCol})",
+                    implode(' UNION ALL ', $vals_escaped)
+                );
+                break;
             case 'mysql':
             default:
                 $obj->_join = "\n" . sprintf(

Hi. After last comment, WordPress's requirement was upgraded (Requirements | WordPress.org) MySQL v5.7 or MariaDB v10.3.

And my web shared hosting (CORESERVER) upgraded DB to MariaDB v10.6 on 2022-06-06 (【重要】【日程変更】コアサーバー 最新・高速サーバーへの移転メンテナンスのお知らせ | メンテナンス・障害 | Value Domain(バリュードメイン))!

I checked v2.0.0beta0 on upgraded my web shared hosting, it is OK. I think supporting MySQL v5.7 is better (last patch ignored by @XRevan86 is still OK now). But not required now (for me).

So I close this issue.