{"id":1651,"date":"2025-10-09T17:24:45","date_gmt":"2025-10-09T15:24:45","guid":{"rendered":"https:\/\/www.mcgill.org.za\/stuff\/?p=1651"},"modified":"2025-10-09T22:15:24","modified_gmt":"2025-10-09T20:15:24","slug":"mysql_upgrade-fails-with-unknown-collation-utf8_general_ci","status":"publish","type":"post","link":"https:\/\/www.mcgill.org.za\/stuff\/archives\/1651","title":{"rendered":"mysql_upgrade fails: Unknown collation: ‘utf8_general_ci’"},"content":{"rendered":"\n

There I was, innocently trying to upgrade mysql from some version to some other version, and I ran into this beautiful bug:<\/p>\n\n\n\n

Phase 4\/7: Running 'mysql_fix_privilege_tables'\nERROR 1273 (HY000) at line 7: Unknown collation: 'utf8_general_ci'<\/code><\/pre>\n\n\n\n
Turns out this is caused mostly by the stupid.<\/p>\n\n\n\n
Dependencies<\/h2>\n\n\n\n
First, it wanted a bunch of new RPMs:<\/p>\n\n\n\n
yum -y install pv socat libpcre2-8<\/code><\/pre>\n\n\n\n
Then I installed the new system RPMs (very carefully):<\/p>\n\n\n\n
systemctl stop mariadb\nrpm -Uvh MariaDB*.rpm\nsystemctl start mariadb\nmysql_upgrade<\/code><\/pre>\n\n\n\n
mysql_upgrade fails<\/h2>\n\n\n\n
And it failed spectacularly:<\/p>\n\n\n\n
root@bigdata10:~ # mysql_upgrade\nPhase 1\/7: Checking and upgrading mysql database\nProcessing databases\nmysql\nmysql.column_stats OK\nmysql.columns_priv OK\nmysql.db OK\nmysql.event OK\nmysql.func OK\nmysql.gtid_slave_pos OK\nmysql.help_category OK\nmysql.help_keyword OK\nmysql.help_relation OK\nmysql.help_topic OK\nmysql.host OK\nmysql.index_stats OK\nmysql.innodb_index_stats OK\nmysql.innodb_table_stats OK\nmysql.ndb_apply_status OK\nmysql.plugin OK\nmysql.proc OK\nmysql.procs_priv OK\nmysql.proxies_priv OK\nmysql.roles_mapping OK\nmysql.servers OK\nmysql.slave_master_info OK\nmysql.slave_relay_log_info OK\nmysql.slave_worker_info OK\nmysql.table_stats OK\nmysql.tables_priv OK\nmysql.time_zone OK\nmysql.time_zone_leap_second OK\nmysql.time_zone_name OK\nmysql.time_zone_transition OK\nmysql.time_zone_transition_type OK\nmysql.transaction_registry OK\nmysql.user OK\nPhase 2\/7: Installing used storage engines\u2026 Skipped\nPhase 3\/7: Fixing views\nbigdata.v_lost OK\nbigdata.view_all_users\nNote : Creation context of view bigdata<\/code>.view_all_users<\/code> is invalid\nstatus : OK\nPhase 4\/7: Running 'mysql_fix_privilege_tables'\nERROR 1273 (HY000) at line 7: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 9: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 10: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 12: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 13: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 14: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 15: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 16: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 17: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 18: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 19: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 20: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 21: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 22: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 23: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 24: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 25: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 26: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 27: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 28: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 31: Unknown character set: 'utf8'\nERROR 1243 (HY000) at line 32: Unknown prepared statement handler (stmt) given to EXECUTE\nERROR 1243 (HY000) at line 33: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE\nERROR 1115 (42000) at line 35: Unknown character set: 'utf8'\nERROR 1243 (HY000) at line 36: Unknown prepared statement handler (stmt) given to EXECUTE\nERROR 1243 (HY000) at line 37: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE\nERROR 1115 (42000) at line 38: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 76: Unknown character set: 'utf8'\nERROR 1243 (HY000) at line 77: Unknown prepared statement handler (stmt) given to EXECUTE\nERROR 1243 (HY000) at line 78: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE\nERROR 1115 (42000) at line 80: Unknown character set: 'utf8'\nERROR 1243 (HY000) at line 81: Unknown prepared statement handler (stmt) given to EXECUTE\nERROR 1243 (HY000) at line 82: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE\nERROR 1115 (42000) at line 84: Unknown character set: 'utf8'\nERROR 1243 (HY000) at line 85: Unknown prepared statement handler (stmt) given to EXECUTE\nERROR 1243 (HY000) at line 86: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE\nERROR 1115 (42000) at line 139: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 141: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 142: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 143: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 161: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 164: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 168: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 172: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 187: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 195: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 203: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 206: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 215: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 218: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 221: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 234: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 245: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 251: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 275: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 280: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 293: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 297: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 310: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 312: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 345: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 351: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 352: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 353: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 354: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 355: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 356: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 357: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 358: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 359: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 360: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 361: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 362: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 366: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 367: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 368: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 369: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 370: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 371: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 372: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 373: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 374: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 375: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 376: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 377: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 378: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 379: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 380: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 381: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 389: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 390: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 394: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 397: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 400: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 403: Unknown collation: 'utf8_general_ci'\nERROR 1115 (42000) at line 408: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 455: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 459: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 462: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 479: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 482: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 491: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 494: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 509: Unknown collation: 'utf8_bin'\nERROR 1115 (42000) at line 515: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 516: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 518: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 519: Unknown character set: 'utf8'\nERROR 1115 (42000) at line 558: Unknown character set: 'utf8'\nERROR 1273 (HY000) at line 564: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 567: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 569: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 572: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 574: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 577: Unknown collation: 'utf8_bin'\nERROR 1408 (HY000) at line 582: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.\nERROR 1273 (HY000) at line 585: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 586: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 587: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 588: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 589: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 590: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 594: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 595: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 597: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 598: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 601: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 602: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 603: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 604: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 609: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 610: Unknown collation: 'utf8_general_ci'\nERROR 1273 (HY000) at line 613: Unknown collation: 'utf8_general_ci'\nERROR 1728 (HY000) at line 626: Cannot load from mysql.proc. The table is probably corrupted\nERROR 1728 (HY000) at line 628: Cannot load from mysql.proc. The table is probably corrupted\nERROR 1728 (HY000) at line 636: Cannot load from mysql.proc. The table is probably corrupted\nERROR 1728 (HY000) at line 638: Cannot load from mysql.proc. The table is probably corrupted\nERROR 1273 (HY000) at line 668: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 669: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 670: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 671: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 672: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 674: Unknown collation: 'utf8_bin'\nERROR 1273 (HY000) at line 675: Unknown collation: 'utf8_bin'\nERROR 1115 (42000) at line 700: Unknown character set: 'utf8'\nERROR 1243 (HY000) at line 701: Unknown prepared statement handler (stmt) given to EXECUTE\nERROR 1243 (HY000) at line 702: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE\nFATAL ERROR: Upgrade failed<\/code><\/pre>\n\n\n\n
Old version still running<\/h2>\n\n\n\n
After much seeking for this unusual failure, I noticed that I was still running the old version of the mysql server. Oops. The cause was that the mariadb-server<\/code> just declined to rpm-upgrade<\/code> and so running mysql_upgrade was trying to set up new server semantics against the old server, which complained about not having all sorts of collations, etc.<\/p>\n\n\n\n
Here’s how it didn’t install:<\/p>\n\n\n\n
rpm -Uvh MariaDB-server-*rpm\nPreparing\u2026 ################################# [100%]\n******************************************************************\nA MySQL or MariaDB server package (MariaDB-server-..) is installed.\n\nUpgrading directly from MySQL 10.3 to MariaDB 10.11 may not\nbe safe in all cases.  A manual dump and restore using mysqldump is\nrecommended.  It is important to review the MariaDB manual's Upgrading\nsection for version-specific incompatibilities.\n\nA manual upgrade is required.\n\n- Ensure that you have a complete, working backup of your data and my.cnf\n  files\n- Shut down the MySQL server cleanly\n- Remove the existing MySQL packages.  Usually this command will\n  list the packages you should remove:\n  rpm -qa | grep -i '^mysql-'\n\n  You may choose to use 'rpm --nodeps -ev <package-name>' to remove\n  the package which contains the mysqlclient shared library.  The\n  library will be reinstalled by the MariaDB-shared package.\n- Install the new MariaDB packages supplied by MariaDB Foundation\n- Ensure that the MariaDB server is started\n- Run the 'mysql_upgrade' program\n\nThis is a brief description of the upgrade process.  Important details\ncan be found in the MariaDB manual, in the Upgrading section.\n******************************************************************\nerror: %pre(MariaDB-server-..) scriptlet failed, exit status 1\nerror: MariaDB-server-..: install failed\nerror: MariaDB-server-..: erase skipped<\/code><\/pre>\n\n\n\n
The fix is to first remove<\/strong> the installed mariadb-server RPM, and then install the fresh version:<\/p>\n\n\n\n
systemctl stop mariadb\nrpm -e mariadb-server          # erase installed version\nrpm -hiv MariaDB-server-*rpm   # install that server\nsystemctl start mariadb        # start it up\nmysql_upgrade                  # update the tables<\/code><\/pre>\n\n\n\n
And then it ran happily.<\/p>\n\n\n\n
(Actually, it ran happily after<\/em> I turned off references to SSL configuration files that don’t actually exist on the server:)<\/p>\n\n\n\n
The log said:<\/p>\n\n\n\n
[ERROR] Failed to setup SSL<\/code><\/pre>\n\n\n\n
To which I said:<\/p>\n\n\n\n
perl -p -i -e 's\/^ssl\/#ssl\/' \/etc\/my.cnf<\/code><\/pre>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
There I was, innocently trying to upgrade mysql from some version to some other version, and I ran into this beautiful bug: Turns out this is caused mostly by the stupid. Dependencies First, it wanted a bunch of new RPMs: … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46,2],"tags":[274,20,336,190,11,337,338],"class_list":["post-1651","post","type-post","status-publish","format-standard","hentry","category-software","category-stuff","tag-mariadb","tag-mysql","tag-rpm","tag-stuff","tag-stupidity","tag-utf8","tag-utf8_general_ci"],"_links":{"self":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/1651","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/comments?post=1651"}],"version-history":[{"count":4,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/1651\/revisions"}],"predecessor-version":[{"id":1655,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/posts\/1651\/revisions\/1655"}],"wp:attachment":[{"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/media?parent=1651"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/categories?post=1651"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mcgill.org.za\/stuff\/wp-json\/wp\/v2\/tags?post=1651"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}