phpMyAdmin Issues After MySQL Upgrade

XxUnkn0wnxX

Member
Feb 3, 2014
22
0
1
cPanel Access Level
Website Owner
hi i recently upgraded to mysql 5.7 (Percona Server)

now myphpadmin is not functioning properly anymore

i cannot even run certain querys anymore like
SHOW DATABASES;

and when ever i use the USE command or click on a data base i get this error:

puu.sh/rVO9u.jpg

myphpadmin has become unusable how do i fix this?

puu.sh/rVOaf.jpg
 
Last edited by a moderator:

SysSachin

Well-Known Member
Aug 23, 2015
604
48
28
India
cPanel Access Level
Root Administrator
Twitter
Hi,
Is there any error message showing in the MySQL error log file ? Might be tables are not present in that databases.
Please check mysql error log so that you will get exact idea.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,909
2,228
463
New hi i recently upgraded to mysql 5.7 (Percona Server)
Hello,

Percona isn't supported with cPanel. I reccomend reverting the steps you took to manually install it and using one of the database servers supported with cPanel (MySQL, MariaDB).

Thank you.
 

raffo

Member
Nov 28, 2008
9
1
51
Hello,

Percona isn't supported with cPanel. I reccomend reverting the steps you took to manually install it and using one of the database servers supported with cPanel (MySQL, MariaDB).

Thank you.
Sorry, but this is a bug of cpanel, the latest release of MySQL is 5.7 (as you can see here)
The 5.7 version has renamed a column of the database "mysql" where store user informations, the "password" column has ben renamed to "authentication_string" so cPanel must fix the query where grant privileges to database, the fix can be like this:

update user set password=PASSWORD("YOURPASSWORDHERE") where user='root';

Should now be:

update user set authentication_string=password('YOURPASSWORDHERE') where user='root';

If you fix this, cpanel will be compatible with MySQL 5.7 and so also with MySQL Percona Server 5.7 :)

I opened a ticket for that, i hope you will do this fix, it's a simple action.

How to change MySQL 'root' password using MySQL v5.7?
MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.6 (2015-03-09, Milestone 16)
 
  • Like
Reactions: eva2000

raffo

Member
Nov 28, 2008
9
1
51
cPanel reply on my ticket that MySQL will be removed and replaced with mariadb.. So the MySQL 5.6 it's the last version that cpanel support.

Wonderful....
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,909
2,228
463
Hello @raffo,

It's correct the current plan is move to MariaDB at some point in the future, with no plans to support MySQL 5.7. Is there anything in-particular (e.g. specific options/performance concerns) that's preventing you from switching to MariaDB?

Thank you.
 

raffo

Member
Nov 28, 2008
9
1
51
Hi @cPanelMichael,

There are some features that i like in Percona MySQL, the first is the XtraDB Cluster that has guarantee HA service for years for our customers. There are also some other features like all UDF of percona for control, limit and monitor the users mysql, XtraBackup also it's a very useful function that permit to make snapshot of databases in less time without blocking the db, it also allow to sync a new node of the cluster without do any action on the server.

Well we have now fixed the problem of issue of MySQL 5.7 on cPanel without make any change in cPanel following these steps:

STEP 1 - Create new column after "user" column
By phpmyadmin:
Name: Password Type: char Size: 41 Encoding: utf8_bin

STEP 2 - Copy the password from authentication_string
We do that with a simple bash script while we have lot of users

Code:
#!/bin/bash

mysql -e "select user,authentication_string from mysql.user;" > mysql_users.txt

while read line; do

    user=$(echo $line | awk '{ print $1 }')
    pass=$(echo $line | awk '{ print $2 }')

    mysql -e "update mysql.user set Password='$pass' WHERE User='$user';"

done < mysql_users.txt
After this change cPanel will work fine and the websites will also connect to the DB.
Anyway this is a workaround we made not for production but to downgrade from mysql 5.7 to 5.6 and then to upgrade to MariaDB Galera Cluster.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,909
2,228
463
Hello,

Thank you for taking the time to show us the steps you used to get it working. I did want to mention that Xtradb is supported with MariaDB:

About XtraDB

Thank you.