Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

phpMyAdmin Issues After MySQL Upgrade

Discussion in 'Database Discussions' started by XxUnkn0wnxX, Oct 25, 2016.

Tags:
  1. XxUnkn0wnxX

    XxUnkn0wnxX Member

    Joined:
    Feb 3, 2014
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    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
     
    #1 XxUnkn0wnxX, Oct 25, 2016
    Last edited by a moderator: Oct 26, 2016
  2. SysSachin

    SysSachin Well-Known Member

    Joined:
    Aug 23, 2015
    Messages:
    568
    Likes Received:
    40
    Trophy Points:
    28
    Location:
    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.
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    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.
     
  4. raffo

    raffo Member

    Joined:
    Nov 28, 2008
    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    51
    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)
     
    eva2000 likes this.
  5. raffo

    raffo Member

    Joined:
    Nov 28, 2008
    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    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....
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    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.
     
  7. raffo

    raffo Member

    Joined:
    Nov 28, 2008
    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    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.
     
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    38,658
    Likes Received:
    1,425
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    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.
     
Loading...

Share This Page