Mariadb issue since upgrading from Mysql to mariadb

InfusedHosting · Thursday at 9:35 AM

Hi all,

Looking for some advice really.. our server for some reason had switched over from Mysql to Mariadb a few months back after doing server updates. (Not sure if the option had been changed inside custombuild options as this was not planned).

Since this upgrade, when browsing any accounts on the server and clicking databases, it shows Internal error - failed to list databases.
Also the phpmyadmin does not load from directadmin either. If you manually browse to hostname/phpmyadmin, you can login to any database with the given user/pass combo.

All of the databases are functional and working fine, it just appears to be with directadmin accessing the database list and allowing SSO into phpmyadmin.

When performing "mysql_upgrade" on SSH, it errors on phase 4/7

Phase 3/7: Fixing views
mysql.user OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1273 (HY000) at line 7: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 10: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 14: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 28: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 36: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 44: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 47: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 56: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 59: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 62: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 75: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 82: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 96: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 120: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 125: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 138: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 140: Unknown collation: 'utf8_general_ci'
ERROR 29 (HY000) at line 171: File '/var/log/mysql-slow-queries.log' not found (Errcode: 13 "Permission denied")
ERROR 1273 (HY000) at line 172: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 178: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 179: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 180: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 181: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 182: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 183: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 184: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 185: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 189: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 190: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 191: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 192: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 193: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 194: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 195: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 196: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 197: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 198: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 205: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 206: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 210: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 213: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 216: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 219: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 224: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 272: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 276: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 279: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 296: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 299: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 308: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 311: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 326: Unknown collation: 'utf8_bin'
ERROR 1115 (42000) at line 334: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 335: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 337: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 338: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 378: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 384: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 387: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 389: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 392: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 394: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 397: Unknown collation: 'utf8_bin'
ERROR 1408 (HY000) at line 402: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
ERROR 1273 (HY000) at line 405: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 406: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 407: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 408: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 412: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 413: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 415: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 416: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 419: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 420: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 421: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 422: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 427: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 430: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 431: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 434: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 469: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 470: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 471: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 472: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 473: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 475: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 476: Unknown collation: 'utf8_bin'
ERROR 1115 (42000) at line 507: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 572: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 574: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 632: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 633: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 634: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 635: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 636: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 641: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 642: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 643: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 644: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 645: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 646: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 647: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 648: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 649: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 650: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 651: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 652: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 655: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 656: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 657: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 659: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 660: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 661: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 662: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 700: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 701: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 702: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 704: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 705: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 706: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 708: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 709: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 710: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 763: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 770: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 771: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 772: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 808: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 809: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 810: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
FATAL ERROR: Upgrade failed

Any help to resolve this issue would be greatly appreciated before we do a full server reinstall.

mariadb status

● mariadb.service - MariaDB database server
Loaded: loaded (/etc/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Thu 2025-01-02 16:20:06 GMT; 16min ago
Process: 1734839 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 1734654 ExecStartPre=/bin/sh -c [ -x /usr/bin/galera_recovery ] || exit 0; VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && sys>
Process: 1734651 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 1734747 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 43 (limit: 74851)
Memory: 2.1G
CGroup: /system.slice/mariadb.service
└─1734747 /usr/sbin/mysqld

Jan 02 16:36:21 server.****************.co.uk mysqld[1734747]: 2025-01-02 16:36:21 1258 [ERROR] Incorrect definition of table mysql.table_st>
Jan 02 16:36:21 server.****************..co.uk mysqld[1734747]: 2025-01-02 16:36:21 1258 [ERROR] Incorrect definition of table mysql.table_st>
Jan 02 16:36:21 server.****************..co.uk mysqld[1734747]: dummy governor function called: (dummy_lve_thr_exit)
Jan 02 16:36:21 server.****************..co.uk mysqld[1734747]: dummy governor function called: (dummy_send_info_end)
Jan 02 16:36:21 server.****************..co.uk mysqld[1734747]: dummy governor function called: (dummy_send_info_begin)
Jan 02 16:36:21 server.****************..co.uk mysqld[1734747]: dummy governor function called: (dummy_put_in_lve)
Jan 02 16:36:21 server****************..co.uk mysqld[1734747]: 2025-01-02 16:36:21 1258 [ERROR] Incorrect definition of table mysql.table_st>
Jan 02 16:36:21 server****************..co.uk mysqld[1734747]: 2025-01-02 16:36:21 1258 [ERROR] Incorrect definition of table mysql.table_st>
Jan 02 16:36:21 server****************..co.uk mysqld[1734747]: dummy governor function called: (dummy_lve_thr_exit)
Jan 02 16:36:21 server.****************.co.uk mysqld[1734747]: dummy governor function called: (dummy_send_info_end)

Regards

Alec

exlhost · Thursday at 10:37 AM

Did you try rebuilding phpmyadmin?

Code:

da config-set one_click_pma_login 1
systemctl restart directadmin
da build phpmyadmin

InfusedHosting · Thursday at 10:43 AM

exlhost said:
Did you try rebuilding phpmyadmin?

Code:

da config-set one_click_pma_login 1 systemctl restart directadmin da build phpmyadmin

Yeah tried rebuilding phpmyadmin, doesn't help the issue - it just displays an orange box saying failed to open.
You can however access phpmyadmin but browsing to the hostname/phpmyadmin and this works including logging into any databases

Hostmavi · Thursday at 2:56 PM

Hi;
i am not well in database.

do you have any setting like this your my.cnf

default-character-set = utf8
character-set-server=utf8
collation-server=utf8_general_ci

check this link

CHANGE HISTORY

10.6 Community

Present starting in MariaDB Community Server 10.6.0.

Collation name changed in MariaDB Community Server 10.6.1 from utf8_general_ci to utf8mb3_general_ci

You should also be aware that the utf8mb3 character set is deprecated

pls check the link
utf8mb4

check your following setting in ssh
use commands
mysql

SHOW SESSION VARIABLES LIKE 'collation%';

collation_connection
collation_database
collation_server

Hope you find solituion for it

InfusedHosting · 2025-01-03T05:33:26-0700

Hostmavi said:
Hi;
i am not well in database.

do you have any setting like this your my.cnf

default-character-set = utf8
character-set-server=utf8
collation-server=utf8_general_ci

check this link

CHANGE HISTORY

10.6 Community

Present starting in MariaDB Community Server 10.6.0.

Collation name changed in MariaDB Community Server 10.6.1 from utf8_general_ci to utf8mb3_general_ci

You should also be aware that the utf8mb3 character set is deprecated

pls check the link
utf8mb4

check your following setting in ssh
use commands
mysql

SHOW SESSION VARIABLES LIKE 'collation%';

collation_connection
collation_database
collation_server

Hope you find solituion for it

Hey

Thanks for the reply.

I've checked my my.cnf file, no reference to character sets or defaults for those.

Running the mysql command pulls back the following

MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+--------------------+
3 rows in set (0.002 sec)

MariaDB [(none)]>

Hostmavi · 2025-01-03T08:51:33-0700

Hi;
your Output from SHOW SESSION VARIABLES LIKE 'collation%'; all is Okey

last 2 Thing

you can check mysql database collation

get yor da_admin Password

cat /usr/local/directadmin/conf/my.cnf

Login phpMyAdmin user as da_admin
check the database mysql tables Collatins
mybe ther is wrong collation like utf8_general_ci, utf8_bin

i add the screenshot mysql database from one of test server

and i never used this pls read this link too
Old-mode

This is all i can do for you
Best Rregards

InfusedHosting · 2025-01-04T16:31:05-0700

Hi all

Issue has now been resolved. after further investigation we found that there was a client and database version mismatch.
Client was running mariadb 10.4 and the database was running 10.6. After removing the 10.4 client and installing 10.6, i was then able to execute mysql_upgrade and fix all the table privileges. DA now loads all databases and phpmyadmin works.

Thanks to everyone that offered assistance.

Mariadb issue since upgrading from Mysql to mariadb

InfusedHosting

Verified User

exlhost

Verified User

InfusedHosting

Verified User

Hostmavi

Verified User

InfusedHosting

Verified User

Hostmavi

Verified User

InfusedHosting

Verified User