Issue Error: Connection to the database server failed: SQLSTATE[HY000]: General error: 1273 Unknown collation: 'UTF8_GENERAL_CI'

[spiralon]

Server operating system version

CentOS Linux 7 (Core)

Plesk version and microupdate number

Plesk Obsidian Web Pro Edition 18.0.52 Update #3

1.) When trying to Export Dump from the Plesk Database dashboard I get following error:
Unable to export a dump of wp_c2:

• mysqldump: Warning: Charset id '33' csname 'utf8' trying to replace existing csname 'utf8mb3'
• mysqldump: Warning: Charset id '83' csname 'utf8' trying to replace existing csname 'utf8mb3'
• mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'wp_c2'': Cannot load from mysql.proc. The table is probably corrupted (1728)
• 2023-06-28 07:27:46,970 [ERROR] 'mysqldump' finished with non-zero exit code: 2

2.) If I try to create a new database from the same dashboard, I get following error below:

Error: Connection to the database server failed: SQLSTATE[HY000]: General error: 1273 Unknown collation: 'UTF8_GENERAL_CI'
Mariadb Version: 10.2.44-MariaDB
Inside my.cnf:
default-character-set=utf8mb4
collation-server=utf8mb4_unicode_ci


# plesk repair mysql

Checking MySQL database servers

Unable connect to localhost:3306: SQLSTATE[HY000]: General error:
1273 Unknown collation: 'UTF8_GENERAL_CI' ......................... [ERROR]

Repair databases on available servers ............................... [OK]

Repair database users on available servers .......................... [OK]

Error messages: 1; Warnings: 0; Errors resolved: 0


exit status 1


After upgrading plesk this problem started.

Could someone point what could be the solution for this ?

 

[Peter Debik]

I am pretty sure this is not an issue with Plesk, but with MariaDB. I think the issue could be caused by the newer MariaDB version. It dropped UTF8_GENERAL_CI in its normal mode. As ar as I know, but hav never done it myself, you need to enable an "old mode" set as described in OLD_MODE to continue using that charset. Also, it would be good to see your complete my.cnf file, because sometimes users have the right configuration in the wrong section, e.g. client instead of mysql. Beyond, it could also help to upgrade MariaDB to version 10.6.

 

[spiralon]

I am pretty sure this is not an issue with Plesk, but with MariaDB. I think the issue could be caused by the newer MariaDB version. It dropped UTF8_GENERAL_CI in its normal mode. As ar as I know, but hav never done it myself, you need to enable an "old mode" set as described in OLD_MODE to continue using that charset. Also, it would be good to see your complete my.cnf file, because sometimes users have the right configuration in the wrong section, e.g. client instead of mysql. Beyond, it could also help to upgrade MariaDB to version 10.6.

Thanks for your reply
I tried the old mode however on the documentation it says "UTF8_IS_UTF8MB3" option is for mariadb 10.6 not 10.2.

================================
/etc/my.cnf.d/plesk-utf8mb4.cnf
================================
[mysqldump]
default-character-set=utf8mb4

================================
/etc/my.cnf
================================

[mysql]
#default-character-set = utf8
default-character-set = utf8mb4
[mysqld]
bind-address = ::
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8mb4
#default-character-set=utf8mb4
#character-set-server=utf8
collation-server=utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'

table_open_cache = 4096
## Cache
table_definition_cache = 4096
thread_cache_size = 8
query_cache_size = 0
query_cache_type = 0

## Per-thread Buffers
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 4M

## Temp Tables
max_heap_table_size = 64M
tmp_table_size = 64M

## Networking
interactive_timeout = 3600
max_connections = 400
max_connect_errors = 1000000
max_allowed_packet = 256M
skip_name_resolve
skip_secure_auth
wait_timeout = 28800
## MyISAM

key_buffer_size = 128M
#myisam_recover = FORCE,BACKUP
myisam_sort_buffer_size = 128M

## InnoDB
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8
#innodb_log_file_size = 256M
innodb_thread_concurrency = 0
#innodb_flush_log_at_trx_commit = 2
innodb_read_io_threads = 6
innodb_write_io_threads = 2


# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in Understanding and administering systemd


[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

 

[Peter Debik]

Have you tried with omitting all the default charset and charset directives in my.cnf? Maybe these are causing the trouble. I've been using 5.5, 10.3, 10.5 for many years and never saw a similar issue, but I also never had a specific charset set in my.cnf, so I am guessing that this could have something to do with it.

 

[mahmut ozdemir]

Have you tried with omitting all the default charset and charset directives in my.cnf? Maybe these are causing the trouble. I've been using 5.5, 10.3, 10.5 for many years and never saw a similar issue, but I also never had a specific charset set in my.cnf, so I am guessing that this could have something to do with it.

Hello

I have the same problem, when I upgraded Plesk to 18.56, SQL crashed.


ERROR 1273 (HY000) at line 234: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 245: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 251: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 275: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 280: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 293: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 297: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 310: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 312: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 345: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 351: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 352: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 353: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 354: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 355: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 356: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 357: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 358: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 359: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 360: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 361: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 362: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 366: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 367: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 368: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 369: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 370: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 371: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 372: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 373: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 374: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 375: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 376: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 377: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 378: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 379: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 380: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 381: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 389: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 390: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 394: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 397: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 400: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 403: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 408: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 455: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 459: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 462: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 479: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 482: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 491: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 494: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 509: Unknown collation: 'utf8_bin'
ERROR 1115 (42000) at line 515: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 516: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 518: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 519: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 558: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 564: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 567: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 569: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 572: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 574: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 577: Unknown collation: 'utf8_bin'
ERROR 1408 (HY000) at line 582: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
ERROR 1273 (HY000) at line 585: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 586: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 587: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 588: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 589: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 590: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 594: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 595: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 597: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 598: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 601: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 602: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 603: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 604: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 609: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 610: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 613: Unknown collation: 'utf8_general_ci'
ERROR 1728 (HY000) at line 626: Cannot load from mysql.proc. The table is probably corrupted
ERROR 1728 (HY000) at line 628: Cannot load from mysql.proc. The table is probably corrupted
ERROR 1728 (HY000) at line 636: Cannot load from mysql.proc. The table is probably corrupted
ERROR 1728 (HY000) at line 638: Cannot load from mysql.proc. The table is probably corrupted
ERROR 1273 (HY000) at line 668: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 669: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 670: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 671: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 672: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 674: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 675: Unknown collation: 'utf8_bin'
ERROR 1115 (42000) at line 702: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 703: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 704: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
FATAL ERROR: Upgrade failed
[root@server ~]#

 

[Peter Debik]

@mahmut ozdemir Actually, it is not the same. In the above thread, MariaDB was running, but in your case it seems that the upgrade itself throws errors. It may have a similar cause (using charset definitions in my.cnf that are no longer available for the new MariaDB version), but it would still be best to describe the issue in your own, new thread, so that it can be clearly distinguished from the issue described in this thread here.