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!

Failed Upgrade MySQL 5.6 --> 5.7 -- Now What?

Discussion in 'Database Discussion' started by Trane Francks, Apr 17, 2019.

  1. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    98
    Likes Received:
    10
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    Greetings, folks.

    This has been a very stressful morning. Since the recent upgrade to WHM 78, I'd been getting nagged that our MySQL 5.6 was out of date and that the preferred version would be 5.7. This morning, after a successful backup of all the user accounts, I took the plunge.

    Things did not go well.

    The server was running MySQL Governor atop CLOUDLINUX 7.6. The CL command line switch to 5.7 seemed to take correctly and the subsequent install started throwing various errors. Upon completion of the install, mysql wouldn't start. Rolling back also resulted in a no-start. An attempt to upgrade to MariaDB also failed.

    Finally, I uninstalled MySQL Governor, deleted MySQL PID and then ran a cPanel rpm package check to fix things. MySQL was simply not there at all now. cPanel installed the missing 5.6 rpms, but MySQL still wouldn't start. I finally solved that problem by deleting the InnoDB log files and now the server is sitting on 5.6 with my original my.cnf file. CL MySQL Governor is currently not installed.

    With that long preamble out of the way, I need to ask: Are there any incompatibilities whatsoever in my.cnf between MySQL 5.6 and 5.7? What about MySQL 5.6 to MariaDB 10.3, which appears to still be the recommended upgrade path? While I recognize that it's probably time to upgrade, I really don't want a repeat of this morning's fiasco.

    my.cnf:

    [mysqld]
    performance-schema = 0
    skip-external-locking
    query_cache_size = 32M
    socket = "/var/lib/mysql/mysql.sock"
    thread_cache_size = 8
    max_allowed_packet = 268435456
    key_buffer = 256M
    sort_buffer_size = 1M
    character_set_server = utf8
    read_rnd_buffer_size = 4M
    collation_server = utf8_general_ci
    server-id = 1
    thread_concurrency = 4
    max_connections = 200
    local-infile = 0
    read_buffer_size = 1M
    port = 3306
    skip-federated
    myisam_sort_buffer_size = 64M
    table_open_cache = 2M
    default-storage-engine = InnoDB
    innodb_file_per_table = 1
    open_files_limit = 50000
    innodb_fast_shutdown=0

    [client]
    port = 3306
    socket = "/var/lib/mysql/mysql.sock"

    [myisamchk]
    write_buffer = 2M
    read_buffer = 2M
    key_buffer = 128M
    sort_buffer_size = 128M

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [isamchk]
    write_buffer = 2M
    read_buffer = 2M
    key_buffer = 128M
    sort_buffer_size = 128M

    [mysqlhotcopy]
    interactive-timeout

    [mysql]
    no-auto-rehash

    Thanks for any help. If I was nervous about upgrading prior to this morning, I'm definitely not looking forward to doing so now.

    trane
     
  2. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,534
    Likes Received:
    2,182
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @Trane Francks,

    Can you open a support ticket so we can take a closer look at your system? You can post the ticket number here and I'll link this thread to it.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    98
    Likes Received:
    10
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    Hi, Michael.

    After a day of client work, I went after this upgrade again this morning. This time, without CloudLinux's MySQL Governor installed, I tried again, but decided to go with MariaDB. Today also failed, but I was able to note which option caused the failure between MariaDB 10.0 to 10.1, at which point the upgrade process failed and the server's databases were now offline.

    The upgrade complained of unknown option 'skip-federated', which is listed in my.cnf above. Despite the upgrade process claiming that it would repair my.cnf to ensure all options were correct, IT DID NOT. Hence, the failure of MySQL/MariaDB to operate. So, I'm answering my own original question. Are there any incompatibilities? Yes. If you've got skip-federated in your my.cnf, remove it PRIOR to upgrading from MySQL 5.6 to 5.7 or from MariaDB 10.0 to 10.1.

    The server is happily purring along with MariaDB 10.3 now. This thread can be marked as resolved.
     
    cPanelMichael likes this.
  4. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,534
    Likes Received:
    2,182
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @Trane Francks,

    Thank you for the additional information, and I'm glad to see you were able to solve the issue.

    I attempted to reproduce this issue by adding the "skip-federated" line to the /etc/my.cnf file prior to upgrading from MySQL version 5.7 to MariaDB version 10.3. However, the existence of this line did not prevent the MySQL upgrade from succeeding, and MariaDB starts well after the upgrade. Do you happen to remember the exact entry that you removed from /etc/my.cnf when you encountered the reported issue?

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    98
    Likes Received:
    10
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    Michael,

    The my.cnf from my functioning MySQL 5.6 was listed in the original post. MySQL 5.6 to MariaDB 10.0 succeeded with this original file. MariaDB 10.0 to 10.1 failed with this my.cnf in place. The ONLY change required to make 10.1 through 10.3 succeed was to remove skip-federated from the [mysqld] section of my.cnf.
     
    cPanelMichael likes this.
  6. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,534
    Likes Received:
    2,182
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @Trane Francks,

    I encourage you to submit a defect report using the following link if you'd like us to take a closer look and open an internal case to prevent this from happening in the future:

    cPanel Customer Portal

    Let me know the ticket number once it's opened and I'll link this thread to it.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice