phpMyAdmin optimize does not work CL 8 MySQL 8
During the ongoing process of trying to migrate my shared hosting service customers from old CloudLinux 6.10 / MySQL 5.7 servers to new CloudLinux 8.9.0 / MySQL 8 servers, I discovered that this very much needed tool does not work.
I reached out to the new DC where the new servers are, and after hours of trying to troubleshoot the issue, I finally asked if they would submit a ticket to cPanel about it. They did so back on February 21st, and here's the short version:
--------------------
cPanel says to DC:
We were able to replicate this behavior on your server and have submitted a case as CPANEL-43867 to our developer. You can follow our changelog below to see when an update is published... Until the bug is patched (case CPANEL-43867), we recommend using the alternative optimize table approach:
1. Select the database (database_name)
2. Choose Structure next to the database (database_options)
3. At the very bottom, under the Information section, observe the Overhead
4. Click Optimize Table,
5. Observe the data is reclaimed as intended
--------------------
I say to DC:
That method is extremely clunky, users can't be expected to do it, and it's not going to work out well when I'm dealing with a lot of sites with large databases / overhead, and rely on phpMyAdmin optimize to clean up a ton of them during migration, and will need to continually need to optimize as the DB's bloat fast.
--------------------
DC says to me:
We agree the work-around is clunky, and wish we could help more but being that it's to do with phpMyAdmin and changing anything within phpMyAdmin would be overwritten with upcp, we can only recommend using the command line for all tables at once:
mysqlcheck --all-databases --optimize
Or:
mysql -e "USE DATABASE [database]; OPTIMIZE TABLE table1,table2,table3;"
--------------------
None of the above are good nor practical solutions, especially in midst of migrations.
Is there a place where us mere mortals can find updates regarding case CPANEL-43867 and an ETA as to when this will be fixed?
Please? Thank you.
The few posters in https://support.cpanel.net/hc/en-us/community/posts/19160749901079-Overhead-not-being-cleared-in-cPanel from 2 years ago are vindicated.
-
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 -
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 -
There isn't a public article for this issue, so feel free to poke me if you ever want an update!
0 -
Hi cPRex - poke poke :) Any news on a fix for this?
0 -
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 -
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 -
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 -
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 -
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 -
And strangely enough, this time the post appears.
0 -
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.
Comments
11 comments