Skip to main content
cPanel Technical Support has been heavily impacted by hurricane Beryl and our ability to respond to tickets has been hindered as a result. We appreciate your understanding and patience as we address these delays.

phpMyAdmin optimize does not work CL 8 MySQL 8

Comments

11 comments

  • cPRex Jurassic Moderator

    Hey hey!  I checked the case on my end and I don't see any updates to it just yet.  I did let the database team know that you inquired, but I have zero timeframe of when this will be fixed at this point.

    0
  • Metro2

    Thank you for checking on it. Is there a way for me to check/get updates on the case on my own, or is that for cPanel staff eyes only?

    0
  • cPRex Jurassic Moderator

    There isn't a public article for this issue, so feel free to poke me if you ever want an update!

    0
  • Metro2

    Hi cPRex - poke poke :) Any news on a fix for this?

    0
  • cPRex Jurassic Moderator

    Well, I have details, but there isn't going to be a fix from us.  It seems that PHPMyAdmin itself hasn't changed the way it interacts with MySQL for the 8.0 upgrade, which leads to this problem.  They have a case open on their end here:

    https://github.com/phpmyadmin/phpmyadmin/issues/16378

    and while there are some workarounds posted there isn't an official fix.

    0
  • Metro2

    I've posted at least two replies since the last response from cPRex , but neither of they both never appeared. Moderated somehow maybe? A bit frustrating, as they were detailed and raised couple of possibly important questions. Strange too, as I've made other posts in other threads with no problem.

    Edit: apparently my sleep deprivation is helping me create a new form of grammar. "neither of they both never appeared" probably should have been either "neither of them have appeared" or "they both never appeared".

    0
  • cPRex Jurassic Moderator

    Yeah?  I don't see anything from you that has triggered any moderation tools, so I don't have a good explanation for that on my end - weird!

    0
  • Metro2

    OK, I'll try to remember a short version of what I had posted and I'll try again in a few minutes. Definitely weird.

    0
  • Metro2

    One part of my previous two replies was along the lines of this:

    cPRex - I know you don't work for MySQL or phpMyAdmin, so maybe you can't give an official response, but I don't think anyone would hold it against you if you gave your opinions / speculation regarding this:

    In https://github.com/phpmyadmin/phpmyadmin/issues/16378#issuecomment-1795145303 we see mention of "set this"

    information_schema_stats_expiry=0

    But does that mean adding that line to /etc/mysql.cnf ?

    If so, what are the repercussions of doing so?

    I'm always hesitant to make changes to things that are a bit over my head and I'm unsure of what they're going to do / what the outcome will be.

    And the part went something like this:

    From my primitive understanding of what they're talking about, it seems that this comes down to MySQL 8's caching function, so after some reading, it occurred to me to try this basic workaround and it seems like it actually works:

    1. I logged in to a user's cPanel and clicked phpMyAdmin

    2. I clicked on one of their WordPress databases and scrolled down and...

    3. Check all > With selected > Optimize table

    The phpMyAdmin page then refreshes, but shows no change in size / bytes, but then

    4. Again clicked the name of the WP database , again scrolled down, and...

    5. Check all > With selected > Analyze table

    After that the phpMyAdmin page refreshes and when I click on the DB name again, it shows all overhead has been cleared / cleaned up, the overall size of the DB in "MiB" is reduced.

    This seems to indicate that the Optimize operation actually occurred, but that maybe MySQL 8's newer caching method was just not displaying the Optimized version of the DB tables, and that somehow Analyze gives that cache a "nudge".

    And so while my workaround is tedious and inconvenient, as it can only be done by manually performing those steps on one database at a time via phpMyAdmin (and may not help with things like plugins that purportedly "auto optimize" a DB), I *think* that *maybe* it reveals something. I think it maybe hints that the Optimize function still does actually work, but maybe is "hidden" by the MySQL 8 caching which is different than that of MySQL 5.7.

    I'd love to get your thoughts and opinions on any or all of the above. Thanks!

    0
  • Metro2

    And strangely enough, this time the post appears.

    0
  • cPRex Jurassic Moderator

    Yes, I think it's fine to try adding that to the MySQL configuration on the system to see if that takes care of it - you won't hurt anything by trying!

    0

Please sign in to leave a comment.