upgrade from mysql 5.7 to 8.0 failed because of an old database with utf8mb3 characters

[LAZer]

hi, I Upgraded mysql 5.7 to 8.0 and now after installation cpanel have removed version 5.7 and it only has 8.0
even when I want to install 5.7 again manually yum wont find files...

anyway there is an old database with utf8mb3 characters in it . which innodb is giving errors about it and won't start.
how can I bypass character checking of innodb so that mysql server 8 starts and I go and remove the database from innodb by commands?

 

[LAZer]

when I delete the directory of that database from /var/lib/mysql
innodb gives this errors and anyway it won't start . I put back the files.

[InnoDB] Operating system error number 2 in a file operation.
1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './p_orum/user_setting.ibd' OS error: 71

I have backup of the hosts and /var/lib/mysql directory from this morning . but the issue is that cpanel removed yum libraries of mysql 5.7
I could atleast install 5.7 again . drop this database and upgrade to 8 again.

***************************************************
even removing that database directory and letting innodb recovery mode = 1 in /my/cnf didn't work either.

/usr/sbin/mysqld (mysqld 8.0.33) starting as process 12024
1 [System] [MY-011012] [Server] Starting upgrade of data directory.
1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
1 [ERROR] [MY-012936] [InnoDB] Database upgrade cannot be accomplished with innodb_force_recovery > 0
1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
0 [ERROR] [MY-010119] [Server] Aborting
0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.33) MySQL Community Server - GPL.

********************************
sudo mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

 

[LAZer]

2 [ERROR] [MY-013140] [Server] Comment for field 'p_orum.page.delete' contains an invalid utf8mb3 character string: '\xCD\xD0\xDD'.
2 [ERROR] [MY-013140] [Server] Comment for field 'p_orum.bank_accountdata.delete' contains an invalid utf8mb3 character string: '\xCD\xD0\xDD'.
2 [ERROR] [MY-013140] [Server] Comment for field 'p_orum.bank_account.delete' contains an invalid utf8mb3 character string: '\xCD\xD0\xDD'.
2 [ERROR] [MY-013140] [Server] Comment for field 'p_orum.admindata.delete' contains an invalid utf8mb3 character string: '\xCD\xD0\xDD'.
2 [ERROR] [MY-013140] [Server] Comment for field 'p_orum.admin_setting.delete' contains an invalid utf8mb3 character string: '\xCD\xD0\xDD'.
2 [ERROR] [MY-013140] [Server] Comment for field 'p_orum.admin.delete' contains an invalid utf8mb3 character string: '\xCD\xD0\xDD'.
0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
0 [ERROR] [MY-010119] [Server] Aborting

 

[ffeingol]

A cPanel support ticket might be your fastest resolution.

 

[LAZer]

I fixed the issue , I removed mysql 8 with yum .
then installed mysql 5.7 manually with :

sudo wget [URL]https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm[/URL]
sudo rpm -ivh mysql57-community-release-el7-11.noarch.rpm
sudo yum install mysql-community-server


now mysql service is up . I'm removing those suspended users and its databases. and will do the upgrade to 8 again.

 

[cPRex]

I'm glad you found a good solution. In general, manually removing data from /var/lib/mysql and manually downgrading the database server aren't things that cPanel supports, but it's good to hear it worked well in this instance.