I tried to upgrade mysql but the upgrade failed. My upgrade steps and the error log is below. Initially my site was totally down and throwing 500 errors, and I ran this command which brough it back up:
mysql_upgrade -u root -h localhost -p --verbose --force
The web site seems to be fine, and I do not see any errors being recorded in the mysql log, however, the MySQL/MariaDB Upgrade (Step 5 of 5) there is this notice: " The upgrade process failed. You should correct any errors listed in the output window and retry the upgrade process" and I do not see PHPMYADMIN as an option anymore in cPanel.
I did make backups of the site databases, and the mysql before I upgraded.
MY UPGRADE STEPS:
1) Turn website off
2) Backup DB's:
mysqldump --all-databases > all_databases_20220721.sql
3) turn off tailwatched mysql
4) systemctl stop mysqld
5) cp -rav /var/lib/mysql /backup2/mysql_20220719.bak
6) systemctl start mysqld
7) Run MariaDB upgrade - Stuck here
8) turn on tailwatched mysql
9) Test
10) Turn website on
I see multiple errors like this in my mysql log:
2022-07-22 3:25:26 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2022-07-22 3:25:26 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2022-07-22 3:25:26 0 [ERROR] InnoDB: Cannot open datafile for read-only: './celiac_com_advertising/phpads_account_preference_assoc.ibd' OS error: 71
2022-07-22 3:25:26 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2022-07-22 3:25:26 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2022-07-22 3:25:26 0 [ERROR] InnoDB: Could not find a valid tablespace file for celiac_com_advertising/phpads_account_preference_assoc. Please refer to InnoDB Data Dictionary Troubleshooting for how to resolv$
2022-07-22 3:25:26 0 [Warning] InnoDB: Ignoring tablespace for celiac_com_advertising/phpads_account_preference_assoc because it could not be opened.
Here is the log created during the upgrade process:
Starting process with log file at /var/cpanel/logs/mysql_upgrade.20220722-031433/unattended_upgrade.log
Beginning “MariaDB 10.6” upgrade...
Obtained version information from system.
Ensuring the “MariaDB106” repository is available and working.
distro does not use package modules; ignoring request to disable mysql
distro does not use package modules; ignoring request to disable mariadb
checkyum version 22.3 (excludes: bind-chroot)
Ensuring that the package “MariaDB-client” with version matching “10.6” is available.
Ensuring that the package “MariaDB-common” with version matching “10.6” is available.
Ensuring that the package “MariaDB-compat” with version matching “10.6” is available.
Ensuring that the package “MariaDB-devel” with version matching “10.6” is available.
Ensuring that the package “MariaDB-server” with version matching “10.6” is available.
Ensuring that the package “MariaDB-shared” with version matching “10.6” is available.
Ensuring that the package “coreutils” is available.
The package “coreutils” version “8.22-24.el7_9.2” is already installed.
Ensuring that the package “grep” is available.
The package “grep” version “2.20-3.el7” is already installed.
Ensuring that the package “perl-DBI” is available.
The package “perl-DBI” version “1.627-4.el7” is already installed.
Ensuring that the package “shadow-utils” is available.
The package “shadow-utils” version “4.6-5.el7” is already installed.
The “MariaDB106” repository is available and working.
Installing pre-known MariaDB dependencies.
Installed pre-known MariaDB dependencies.
Ensuring MariaDB packages for version “10.6”.
Verifying that the system is in a state where MariaDB packages can be installed.
Verifying that the MariaDB packages can be installed by doing a test install.
checkyum version 22.3 (excludes: bind-chroot kernel)
Loaded plugins: fastestmirror, universal-hooks
Loading mirror speeds from cached hostfile
* EA4: 63.247.64.58
* EA4-experimental: 63.247.64.58
* cpanel-addons-production-feed: 63.247.64.58
* cpanel-plugins: 63.247.64.58
* base: centos-distro.cavecreek.net
* elrepo: elrepo.org
* epel: mirror.arizona.edu
* extras: mirror.chpc.utah.edu
* updates: centos-distro.cavecreek.net
Package MariaDB-server-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-client-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-devel-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-shared-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-common-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-compat-10.6.8-1.el7.centos.x86_64 already installed and latest version
Nothing to do
Preinstall check passed.
Running: /usr/local/cpanel/scripts/check_cpanel_pkgs --targets=MySQL41,MySQL50,MySQL51,MySQL55,MySQL56,MySQL57,MySQL80,MariaDB100,MariaDB101,MariaDB102,MariaDB103,MariaDB105,MariaDB106 --fix --no-broken --no-digest
Installing MariaDB from official repository.
distro does not use package modules; ignoring request to disable mysql
distro does not use package modules; ignoring request to disable mariadb
Hooks system enabled.
Checking for and running RPM::Versions 'pre' hooks for any Packages about to be installed
All required 'pre' hooks have been run
Looking for providers that will cause dependency problems: mysql-server
Uninstalling the following via “rpm” prior to “yum” to avoid dependency problems: MariaDB-server
Removing any of the following incompatible packages: mariadb-client mariadb-devel mariadb-embedded mariadb-embedded-devel mariadb-libs mariadb-libs-compat mariadb-release mariadb-server mariadb-test mysql-community-client mysql-community-common mysql-community-devel mysql-community-embedded mysql-community-embedded-devel mysql-community-libs mysql-community-libs-compat mysql-community-release mysql-community-server mysql-community-test mysql-client mysql-devel mysql-embedded mysql-embedded-devel mysql-libs mysql-libs-compat mysql-release mysql-server mysql-test mysql55-mysql-bench mysql55-mysql-devel mysql55-mysql-libs mysql55-mysql-server mysql55-mysql-test mysql57-community-release mysql80-community-release rh-mysql56-mysql-bench rh-mysql56-mysql-common rh-mysql56-mysql-config rh-mysql56-mysql-devel rh-mysql56-mysql-errmsg rh-mysql56-mysql-server rh-mysql56-mysql-test rh-mysql57-mysql-common rh-mysql57-mysql-config rh-mysql57-mysql-devel rh-mysql57-mysql-errmsg rh-mysql57-mysql-server rh-mysql57-mysql-test mytop
Loaded plugins: fastestmirror, universal-hooks
Loading mirror speeds from cached hostfile
* EA4: 63.247.64.58
* EA4-experimental: 63.247.64.58
* cpanel-addons-production-feed: 63.247.64.58
* cpanel-plugins: 63.247.64.58
* base: centos-distro.cavecreek.net
* elrepo: elrepo.org
* epel: mirror.arizona.edu
* extras: mirror.chpc.utah.edu
* updates: centos-distro.cavecreek.net
Package MariaDB-client-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-devel-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-shared-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-common-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-compat-10.6.8-1.el7.centos.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-server.x86_64 0:10.6.8-1.el7.centos will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
MariaDB-server x86_64 10.6.8-1.el7.centos MariaDB106 25 M
Transaction Summary
================================================================================
Install 1 Package
Total download size: 25 M
Installed size: 122 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : MariaDB-server-10.6.8-1.el7.centos.x86_64 1/1
/etc/yum/universal-hooks/pkgs/MariaDB-server/posttrans/100-build_mysql_conf: running in the background.
Verifying : MariaDB-server-10.6.8-1.el7.centos.x86_64 1/1
Installed:
MariaDB-server.x86_64 0:10.6.8-1.el7.centos
Complete!
MariaDB installed from official repository.
Building configuration.
This step may produce some errors or warnings in the log. The errors are usually harmless and are a result of table changes between versions of MySQL or MariaDB.
Running: /usr/local/cpanel/bin/build_mysql_conf
Done building configuration.
Hooks system enabled.
Checking for and running RPM::Versions 'post' hooks for any Packages about to be installed
All required 'post' hooks have been run
Running: /usr/local/cpanel/scripts/check_cpanel_pkgs --targets=MySQL41,MySQL50,MySQL51,MySQL55,MySQL56,MySQL57,MySQL80,MariaDB100,MariaDB101,MariaDB102,MariaDB103,MariaDB105,MariaDB106 --fix --no-broken --no-digest
The system could not start MariaDB 10.6
Startup Log
Jul 22 01:32:26 scott.celiac.com systemd[1]: Starting MariaDB 10.6.8 database server...
Jul 22 01:32:27 scott.celiac.com mariadbd[12683]: 2022-07-22 1:32:27 0 [Note] /usr/sbin/mariadbd (server 10.6.8-MariaDB) starting as process 12683 ...
Jul 22 01:32:27 scott.celiac.com systemd[1]: Started MariaDB 10.6.8 database server.
Jul 22 01:35:12 scott.celiac.com systemd[1]: Stopping MariaDB 10.6.8 database server...
Jul 22 01:35:13 scott.celiac.com systemd[1]: Stopped MariaDB 10.6.8 database server.
Jul 22 02:45:30 scott.celiac.com systemd[1]: Starting MariaDB 10.6.8 database server...
Jul 22 02:45:30 scott.celiac.com mariadbd[25210]: 2022-07-22 2:45:30 0 [Note] /usr/sbin/mariadbd (server 10.6.8-MariaDB) starting as process 25210 ...
Jul 22 02:45:31 scott.celiac.com systemd[1]: Started MariaDB 10.6.8 database server.
Jul 22 03:14:55 scott.celiac.com systemd[1]: Stopping MariaDB 10.6.8 database server...
Jul 22 03:14:56 scott.celiac.com systemd[1]: Stopped MariaDB 10.6.8 database server.
Restarting mysql service.
(XID uthx9v) The “mysql” service is disabled.
MariaDB did not start up after upgrade; there may be a problem in /etc/my.cnf. at /usr/local/cpanel/Whostmgr/Mysql/Upgrade.pm line 859.
Obtained version information from system.
Proceeding with MySQL/MariaDB upgrade despite the following:
Critical: The system failed to query the MySQL server to check for the presence of pre-4.1-style MySQL passwords. ((XID 4dey6e) The system failed to connect to the “MySQL” database “mysql” because of an error: CR_CONNECTION_ERROR (Can't connect to local server through socket '/var/lib/mysql/mysql.sock' (2))) This issue could also hinder mysql_upgrade's ability to run, which could potentially leave MySQL in an unusable state if you proceed.
Normal: The selected MariaDB version (10.6) is the same as the currently installed MariaDB version (10.6). No upgrade will be performed at this time, though the normal upgrade steps will still be executed. This is only useful if a previous upgrade failed while partially completed.
Normal: MariaDB 10.6 introduced a new reserved word: OFFSET. This can no longer be used as an identifier without being quoted.
Normal: From MariaDB 10.6, tables that are of the `COMPRESSED` row format are read-only by default. This is the first step towards removing write support and deprecating the feature. The `innodb_read_only_compressed` variable
must be set to `OFF` in order to make the tables writable.
mysql_upgrade -u root -h localhost -p --verbose --force
The web site seems to be fine, and I do not see any errors being recorded in the mysql log, however, the MySQL/MariaDB Upgrade (Step 5 of 5) there is this notice: " The upgrade process failed. You should correct any errors listed in the output window and retry the upgrade process" and I do not see PHPMYADMIN as an option anymore in cPanel.
I did make backups of the site databases, and the mysql before I upgraded.
MY UPGRADE STEPS:
1) Turn website off
2) Backup DB's:
mysqldump --all-databases > all_databases_20220721.sql
3) turn off tailwatched mysql
4) systemctl stop mysqld
5) cp -rav /var/lib/mysql /backup2/mysql_20220719.bak
6) systemctl start mysqld
7) Run MariaDB upgrade - Stuck here
8) turn on tailwatched mysql
9) Test
10) Turn website on
I see multiple errors like this in my mysql log:
2022-07-22 3:25:26 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2022-07-22 3:25:26 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2022-07-22 3:25:26 0 [ERROR] InnoDB: Cannot open datafile for read-only: './celiac_com_advertising/phpads_account_preference_assoc.ibd' OS error: 71
2022-07-22 3:25:26 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2022-07-22 3:25:26 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2022-07-22 3:25:26 0 [ERROR] InnoDB: Could not find a valid tablespace file for celiac_com_advertising/phpads_account_preference_assoc. Please refer to InnoDB Data Dictionary Troubleshooting for how to resolv$
2022-07-22 3:25:26 0 [Warning] InnoDB: Ignoring tablespace for celiac_com_advertising/phpads_account_preference_assoc because it could not be opened.
Here is the log created during the upgrade process:
Starting process with log file at /var/cpanel/logs/mysql_upgrade.20220722-031433/unattended_upgrade.log
Beginning “MariaDB 10.6” upgrade...
Obtained version information from system.
Ensuring the “MariaDB106” repository is available and working.
distro does not use package modules; ignoring request to disable mysql
distro does not use package modules; ignoring request to disable mariadb
checkyum version 22.3 (excludes: bind-chroot)
Ensuring that the package “MariaDB-client” with version matching “10.6” is available.
Ensuring that the package “MariaDB-common” with version matching “10.6” is available.
Ensuring that the package “MariaDB-compat” with version matching “10.6” is available.
Ensuring that the package “MariaDB-devel” with version matching “10.6” is available.
Ensuring that the package “MariaDB-server” with version matching “10.6” is available.
Ensuring that the package “MariaDB-shared” with version matching “10.6” is available.
Ensuring that the package “coreutils” is available.
The package “coreutils” version “8.22-24.el7_9.2” is already installed.
Ensuring that the package “grep” is available.
The package “grep” version “2.20-3.el7” is already installed.
Ensuring that the package “perl-DBI” is available.
The package “perl-DBI” version “1.627-4.el7” is already installed.
Ensuring that the package “shadow-utils” is available.
The package “shadow-utils” version “4.6-5.el7” is already installed.
The “MariaDB106” repository is available and working.
Installing pre-known MariaDB dependencies.
Installed pre-known MariaDB dependencies.
Ensuring MariaDB packages for version “10.6”.
Verifying that the system is in a state where MariaDB packages can be installed.
Verifying that the MariaDB packages can be installed by doing a test install.
checkyum version 22.3 (excludes: bind-chroot kernel)
Loaded plugins: fastestmirror, universal-hooks
Loading mirror speeds from cached hostfile
* EA4: 63.247.64.58
* EA4-experimental: 63.247.64.58
* cpanel-addons-production-feed: 63.247.64.58
* cpanel-plugins: 63.247.64.58
* base: centos-distro.cavecreek.net
* elrepo: elrepo.org
* epel: mirror.arizona.edu
* extras: mirror.chpc.utah.edu
* updates: centos-distro.cavecreek.net
Package MariaDB-server-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-client-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-devel-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-shared-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-common-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-compat-10.6.8-1.el7.centos.x86_64 already installed and latest version
Nothing to do
Preinstall check passed.
Running: /usr/local/cpanel/scripts/check_cpanel_pkgs --targets=MySQL41,MySQL50,MySQL51,MySQL55,MySQL56,MySQL57,MySQL80,MariaDB100,MariaDB101,MariaDB102,MariaDB103,MariaDB105,MariaDB106 --fix --no-broken --no-digest
Installing MariaDB from official repository.
distro does not use package modules; ignoring request to disable mysql
distro does not use package modules; ignoring request to disable mariadb
Hooks system enabled.
Checking for and running RPM::Versions 'pre' hooks for any Packages about to be installed
All required 'pre' hooks have been run
Looking for providers that will cause dependency problems: mysql-server
Uninstalling the following via “rpm” prior to “yum” to avoid dependency problems: MariaDB-server
Removing any of the following incompatible packages: mariadb-client mariadb-devel mariadb-embedded mariadb-embedded-devel mariadb-libs mariadb-libs-compat mariadb-release mariadb-server mariadb-test mysql-community-client mysql-community-common mysql-community-devel mysql-community-embedded mysql-community-embedded-devel mysql-community-libs mysql-community-libs-compat mysql-community-release mysql-community-server mysql-community-test mysql-client mysql-devel mysql-embedded mysql-embedded-devel mysql-libs mysql-libs-compat mysql-release mysql-server mysql-test mysql55-mysql-bench mysql55-mysql-devel mysql55-mysql-libs mysql55-mysql-server mysql55-mysql-test mysql57-community-release mysql80-community-release rh-mysql56-mysql-bench rh-mysql56-mysql-common rh-mysql56-mysql-config rh-mysql56-mysql-devel rh-mysql56-mysql-errmsg rh-mysql56-mysql-server rh-mysql56-mysql-test rh-mysql57-mysql-common rh-mysql57-mysql-config rh-mysql57-mysql-devel rh-mysql57-mysql-errmsg rh-mysql57-mysql-server rh-mysql57-mysql-test mytop
Loaded plugins: fastestmirror, universal-hooks
Loading mirror speeds from cached hostfile
* EA4: 63.247.64.58
* EA4-experimental: 63.247.64.58
* cpanel-addons-production-feed: 63.247.64.58
* cpanel-plugins: 63.247.64.58
* base: centos-distro.cavecreek.net
* elrepo: elrepo.org
* epel: mirror.arizona.edu
* extras: mirror.chpc.utah.edu
* updates: centos-distro.cavecreek.net
Package MariaDB-client-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-devel-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-shared-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-common-10.6.8-1.el7.centos.x86_64 already installed and latest version
Package MariaDB-compat-10.6.8-1.el7.centos.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-server.x86_64 0:10.6.8-1.el7.centos will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
MariaDB-server x86_64 10.6.8-1.el7.centos MariaDB106 25 M
Transaction Summary
================================================================================
Install 1 Package
Total download size: 25 M
Installed size: 122 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : MariaDB-server-10.6.8-1.el7.centos.x86_64 1/1
/etc/yum/universal-hooks/pkgs/MariaDB-server/posttrans/100-build_mysql_conf: running in the background.
Verifying : MariaDB-server-10.6.8-1.el7.centos.x86_64 1/1
Installed:
MariaDB-server.x86_64 0:10.6.8-1.el7.centos
Complete!
MariaDB installed from official repository.
Building configuration.
This step may produce some errors or warnings in the log. The errors are usually harmless and are a result of table changes between versions of MySQL or MariaDB.
Running: /usr/local/cpanel/bin/build_mysql_conf
Done building configuration.
Hooks system enabled.
Checking for and running RPM::Versions 'post' hooks for any Packages about to be installed
All required 'post' hooks have been run
Running: /usr/local/cpanel/scripts/check_cpanel_pkgs --targets=MySQL41,MySQL50,MySQL51,MySQL55,MySQL56,MySQL57,MySQL80,MariaDB100,MariaDB101,MariaDB102,MariaDB103,MariaDB105,MariaDB106 --fix --no-broken --no-digest
The system could not start MariaDB 10.6
Startup Log
Jul 22 01:32:26 scott.celiac.com systemd[1]: Starting MariaDB 10.6.8 database server...
Jul 22 01:32:27 scott.celiac.com mariadbd[12683]: 2022-07-22 1:32:27 0 [Note] /usr/sbin/mariadbd (server 10.6.8-MariaDB) starting as process 12683 ...
Jul 22 01:32:27 scott.celiac.com systemd[1]: Started MariaDB 10.6.8 database server.
Jul 22 01:35:12 scott.celiac.com systemd[1]: Stopping MariaDB 10.6.8 database server...
Jul 22 01:35:13 scott.celiac.com systemd[1]: Stopped MariaDB 10.6.8 database server.
Jul 22 02:45:30 scott.celiac.com systemd[1]: Starting MariaDB 10.6.8 database server...
Jul 22 02:45:30 scott.celiac.com mariadbd[25210]: 2022-07-22 2:45:30 0 [Note] /usr/sbin/mariadbd (server 10.6.8-MariaDB) starting as process 25210 ...
Jul 22 02:45:31 scott.celiac.com systemd[1]: Started MariaDB 10.6.8 database server.
Jul 22 03:14:55 scott.celiac.com systemd[1]: Stopping MariaDB 10.6.8 database server...
Jul 22 03:14:56 scott.celiac.com systemd[1]: Stopped MariaDB 10.6.8 database server.
Restarting mysql service.
(XID uthx9v) The “mysql” service is disabled.
MariaDB did not start up after upgrade; there may be a problem in /etc/my.cnf. at /usr/local/cpanel/Whostmgr/Mysql/Upgrade.pm line 859.
Obtained version information from system.
Proceeding with MySQL/MariaDB upgrade despite the following:
Critical: The system failed to query the MySQL server to check for the presence of pre-4.1-style MySQL passwords. ((XID 4dey6e) The system failed to connect to the “MySQL” database “mysql” because of an error: CR_CONNECTION_ERROR (Can't connect to local server through socket '/var/lib/mysql/mysql.sock' (2))) This issue could also hinder mysql_upgrade's ability to run, which could potentially leave MySQL in an unusable state if you proceed.
Normal: The selected MariaDB version (10.6) is the same as the currently installed MariaDB version (10.6). No upgrade will be performed at this time, though the normal upgrade steps will still be executed. This is only useful if a previous upgrade failed while partially completed.
Normal: MariaDB 10.6 introduced a new reserved word: OFFSET. This can no longer be used as an identifier without being quoted.
Normal: From MariaDB 10.6, tables that are of the `COMPRESSED` row format are read-only by default. This is the first step towards removing write support and deprecating the feature. The `innodb_read_only_compressed` variable
must be set to `OFF` in order to make the tables writable.
