If you have any experience with hosting MySQL systems you have probably come across the following error. Here are some tips on how to fix it.
Error 28 is a common error message from your MySQL storage engine. This message indicates that the drive is full, meaning that one or more of the partitions on your server is out of space, and MySQL cannot write to the disk. You need to clear disk space for MySQL to operate. Note that, if you have a dedicated server, you may receive this error even if your control panel still shows free space. (For example, cPanel does not include the database disk space used. It just calculates the disk space of /home/cPanel_username (root directory) for the account.)
Often, the problem can be solved by cleaning up your /tmp directory, which can be overfilled with unnecessary files, like older backups from a scheduled backup script.
First, stop MySQL immediately.
On cpanel the /tmp directory is an image created on the / drive so removing files from it wonâ€™t helpâ€¦
Some places worth checking on cpanel is the domlogs if they do not need stats or /home/cpbackuptmp as it is a temporary directory for backups so long as backups are not currently running. Also to check their actual backups and if they are on the same drive that they aren’t keeping too much retention vs available size on their drive, i.e., if they have a 250gb drive and /home & /var/lib/mysql are 80gb keeping a daily & weekly will fill up the drive every time backups run.
# /etc/init.d/mysql start
If your /tmp directory is not causing the error, you may also need to look into your /var/log directory and remove or compress the logs.
Additionally, periodically optimizing your tables in MySQLcheck improves database performance. Optimizing is a similar process to defragmentation.
mysqlcheck –all-databases –analyze –optimize -u root -p
You can also repair all tables in your database.
mysqlcheck --all-databases --auto-repair -u root -p
Analyzing, optimizing, and repairing tables can also easily be scheduled as a cron job.
In PHPMyAdmin, table optimization is done by cleaning up table overhead. Overhead is similar to hard drive defragmentationâ€”database tables that are frequently accessed or updated store extra file size, called â€œOverhead,â€ that can take up a lot of space over time and requires maintenance. To clear this space, login to PHPMyAdmin, select the applicable database, and you should see a list of all the tables in the database. In the far right column, note the heading â€œOverhead.â€ Check all the tables that have an â€œOverheadâ€ value listed. At the bottom of the window, youâ€™ll find a drop-down menu. Choose â€œOptimize Tableâ€ from the menu. Overhead will be cleared, and if you click on the database name again, you’ll see all €œOverheadâ€ values should be cleared.
Hope this helps anyone out there looking to solve issues with their hosted Mysql setup.
Updated: January 2019