Migrate MySQL to MariaDB

I have been getting the error "Falsche Anfrage / Wrong Query SQL-Query = SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'finished server.php." since i updated one of my servers from Ubuntu 18.04 to 20.04. After research i found the best solution is to use MariaDB but i was wondering if there is any suggestions/precautions on migrating the server's database to MariaDB?

My mysql version is : Ver 8.0.33-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
ISPConfig : Mulit-server setup
Ubuntu 20.04

Any help, links or tips will be greatly appreciated.

Thanks

 

I get

Code:

Query OK, 0 rows affected, 5 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| Warning | 1681 | Updating 'character_set_database' is deprecated. It will be made read-only in a future release. 

 

Anything to do with ? "postfix_server_plugin: plugin is enabled but this server is not configured as a mail server."

 

Hmm.... Didn't seem to work

Code:

Thu 06 Jul 2023 05:50:02 PM CEST PHP Warning:  Undefined array key 1 in /usr/local/ispconfig/server/lib/classes/cron.d/100-monitor_mem_usage.inc.php on line 79
Thu 06 Jul 2023 05:51:01 PM CEST Falsche Anfrage / Wrong Query SQL-Query = SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'finished server.php.
Thu 06 Jul 2023 05:52:01 PM CEST Falsche Anfrage / Wrong Query SQL-Query = SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'finished server.php.
Thu 06 Jul 2023 05:53:01 PM CEST Falsche Anfrage / Wrong Query SQL-Query = SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'finished server.php.
Thu 06 Jul 2023 05:54:01 PM CEST Falsche Anfrage / Wrong Query SQL-Query = SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'finished server.php.
Thu 06 Jul 2023 05:55:01 PM CEST Falsche Anfrage / Wrong Query SQL-Query = SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'finished server.php.
Thu 06 Jul 2023 05:55:01 PM CEST Falsche Anfrage / Wrong Query SQL-Query = SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'PHP Warning:  Undefined >
Thu 06 Jul 2023 05:55:01 PM CEST PHP Warning:  Undefined array key "db_flags" in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php on line 671
Thu 06 Jul 2023 05:55:01 PM CEST PHP Warning:  Undefined array key 1 in /usr/local/ispconfig/server/lib/classes/cron.d/100-monitor_mem_usage.inc.php on line 79
Thu 06 Jul 2023 05:56:01 PM CEST Falsche Anfrage / Wrong Query SQL-Query = SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'finished server.php.

 

Solved them all by doinf the above and adjusting the .php files with an extra check.

/usr/local/ispconfig/server/lib/classes/cron.d/100-monitor_clamav_log.inc.php file : around line 111 should be

Code:

if ($tmp[(sizeof($tmp) - 1)] == '') {
                        /* the log ends with an empty line remove this */
                        array_pop($tmp);
                }
                if (strpos($tmp[(sizeof($tmp) - 1)], '-------------') !== false) {
                        /* the log ends with "-----..." remove this */
                        array_pop($tmp);
                }
                for ($i = sizeof($tmp) - 1; $i > 0; $i--) {
                        if (strpos($tmp[$i], '---------') === false) {
                                /* no delimiter found, so add this to the last-log */
                                $lastLog[] = $tmp[$i];
                        } else {
                                /* delimiter found, so there is no more line left! */
                                break;
                        }
                }

/usr/local/ispconfig/server/lib/classes/db_mysql.inc.php file : line 671 should be

Code:

$clientdb_flags = isset($conf['db_flags']) && ($conf['db_flags'] !== NULL) ? $conf['db_flags'] : NULL;

/usr/local/ispconfig/server/lib/classes/cron.d/100-monitor_mem_usage.inc.php : line 79 should be

Code:

$tmp = isset($part[1]) ? explode(' ', trim($part[1])) : false;

 

Maybe someone can add these changes to the official repo/git?

 

The change from thread #5 was reverted after i did and ispconfig (force) update because the SQL SET error persisted. After the update, i redid thread #5 change. After removing the php warnings that one also went away strangely enough.

 

Might have spoke too soon. Still getting the last error of "
Falsche Anfrage / Wrong Query SQL-Query = SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'finished server.php."

 

Definitely should solve it for now.