There I was, innocently trying to upgrade mysql from some version to some other version, and I ran into this beautiful bug:
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1273 (HY000) at line 7: Unknown collation: 'utf8_general_ci'Turns out this is caused mostly by the stupid.
Dependencies
First, it wanted a bunch of new RPMs:
yum -y install pv socat libpcre2-8Then I installed the new system RPMs (very carefully):
systemctl stop mariadb
rpm -Uvh MariaDB*.rpm
systemctl start mariadb
mysql_upgrademysql_upgrade fails
And it failed spectacularly:
root@bigdata10:~ # mysql_upgrade
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_apply_status OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.transaction_registry OK
mysql.user OK
Phase 2/7: Installing used storage engines… Skipped
Phase 3/7: Fixing views
bigdata.v_lost OK
bigdata.view_all_users
Note : Creation context of view bigdata.view_all_users is invalid
status : 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 9: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 10: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 12: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 13: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 14: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 15: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 16: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 17: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 18: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 19: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 20: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 21: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 22: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 23: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 24: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 25: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 26: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 27: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 28: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 31: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 32: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 33: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 35: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 36: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 37: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 38: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 76: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 77: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 78: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 80: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 81: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 82: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 84: Unknown character set: 'utf8'
ERROR 1243 (HY000) at line 85: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 86: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1115 (42000) at line 139: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 141: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 142: Unknown character set: 'utf8'
ERROR 1115 (42000) at line 143: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 161: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 164: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 168: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 172: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 187: Unknown collation: 'utf8_bin'
ERROR 1273 (HY000) at line 195: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 203: Unknown collation: 'utf8_general_ci'
ERROR 1115 (42000) at line 206: Unknown character set: 'utf8'
ERROR 1273 (HY000) at line 215: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 218: Unknown collation: 'utf8_general_ci'
ERROR 1273 (HY000) at line 221: Unknown collation: 'utf8_general_ci'
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 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
FATAL ERROR: Upgrade failedOld version still running
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 just declined to rpm-upgrade 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.
Here’s how it didn’t install:
rpm -Uvh MariaDB-server-*rpm
Preparing… ################################# [100%]
******************************************************************
A MySQL or MariaDB server package (MariaDB-server-..) is installed.
Upgrading directly from MySQL 10.3 to MariaDB 10.11 may not
be safe in all cases. A manual dump and restore using mysqldump is
recommended. It is important to review the MariaDB manual's Upgrading
section for version-specific incompatibilities.
A manual upgrade is required.
- Ensure that you have a complete, working backup of your data and my.cnf
files
- Shut down the MySQL server cleanly
- Remove the existing MySQL packages. Usually this command will
list the packages you should remove:
rpm -qa | grep -i '^mysql-'
You may choose to use 'rpm --nodeps -ev <package-name>' to remove
the package which contains the mysqlclient shared library. The
library will be reinstalled by the MariaDB-shared package.
- Install the new MariaDB packages supplied by MariaDB Foundation
- Ensure that the MariaDB server is started
- Run the 'mysql_upgrade' program
This is a brief description of the upgrade process. Important details
can be found in the MariaDB manual, in the Upgrading section.
******************************************************************
error: %pre(MariaDB-server-..) scriptlet failed, exit status 1
error: MariaDB-server-..: install failed
error: MariaDB-server-..: erase skippedThe fix is to first remove the installed mariadb-server RPM, and then install the fresh version:
systemctl stop mariadb
rpm -e mariadb-server # erase installed version
rpm -hiv MariaDB-server-*rpm # install that server
systemctl start mariadb # start it up
mysql_upgrade # update the tablesAnd then it ran happily.
(Actually, it ran happily after I turned off references to SSL configuration files that don’t actually exist on the server:)
The log said:
[ERROR] Failed to setup SSLTo which I said:
perl -p -i -e 's/^ssl/#ssl/' /etc/my.cnf