MySQL v8 Packed MYISAM tables - INFORMATION_SCHEMA not updated

fepeacock

• September 02, 2020 07:27

Hello, I am a new user of cPanel/WHM and our dedicated server has v88.0.14 with MySQL v8. We need MyISAM tables for read-speed, compression and do not have a need for row-locking. I do not know whether the issue we are facing is related to cPanel or is due to MySQL v8 exclusively. I have already queried on the MySQL forum but have not had a response yet. The issue is that after packing the MYISAM table successfully, the INFORMATION_SCHEMA does not reflect the new average record size and total data size. It still has the original values of the pre-packed files. It has updated the row format type from "Fixed" to "Compressed" but not the two variable sizes I mentioned earlier. This has an impact on phpMyAdmin and mysql_client. The overstating of the data size has huge consequences on our estimation of the available disk space. Are their any users who are aware of this issue and can suggest a solution? Many thanks, Frank

• 

  fepeacock

  • September 03, 2020 08:51

  Hello, I have found the solution: MySQL v8 caches dynamic table metadata. It is necessary to set the system variable "information_schema_stats_expiry = 0" to allow updates to this data or else to wait for the expiry time in seconds to elapse. Cheers, Frank

  0
• 

  cPanelLauren

  • September 04, 2020 20:01

  I'm really happy to see you were able to find the solution to this and thank you for updating the thread with what resolved the issue.

  0

Please sign in to leave a comment.