Description:
ErrCode 28 – No Space Left on Device
Issues: We are having a lot of issues in our enviroment, with Mysql 5.7 in a CentOS server. Most part of the problems are clearly related to available space on disk:
- There’s no way to access, edit, create or delete any procedures.
- If we run a select on mysql.proc table, na erro came up: “mysql.proc is crashed and must be repaired”.
- It’s not possible to create a new Function. We don’t have another functions in our enviroment to verify if they are showed without problems. When we try to create a new function, we have the following error: “ErrCode 28 – No Space Left on Device”.
- There’s no way to create a new Table. When we try it, we have the following error: “ErrCode 28 – No Space Left on Device”.
- There’s no way to create a new Trigger. When we try it, we have the following error: “ErrCode 28 – No Space Left on Device”. If we try to make any tiny change in any trigger, the same erro came up: “ErrCode 28 – No Space Left on Device”.
- There’s no way to create a Database. When we try it, we have the following error: “ErrCode 28 – No Space Left on Device”.
So, it’s very clear that’s no free space in our enviroment, to support most part of our database operations. But its not true. We made the following investigation steps to notice that.
Investigation steps: We check throught the following steps, the Available área on our File System. First of all, we verified in the Mysql variable DataDir, the Data Directory of our Data Base: /var/lib/mysql. We’ve confirmed this information in the following screen of Mysql Workbench. Na attention point in the same Picture, is the information Disk Space in Data Dir: “Unable to retrieve”. Since our problem started, this information alternates among 0 and “Unable to retrieve”:
https://drive.google.com/open?id=1s9ZW9_z7EZBtFMPixRFlSDVFUiiSj0xL
We run some CentOS commands, to evaluete the free space on /dev/root FileSystem. This FileSystem has been mounted on the root (/) directory, as we would see in the three following pictures:
Df -i
https://drive.google.com/open?id=19VUqo6flODatkzoJW442GVDs2NYigtNi
Df -h
https://drive.google.com/open?id=1b98GIBdEFZv-i0jbyc-ANoHppBVn8Upw
CPanel:
https://drive.google.com/open?id=18SXMUh6rQV7-4n5ZMBuKfrpeVq0we_2Y
So, we have a lot of free space on DataDir Filesystem, instead of the several issues and errors related to no space left on device.
In this context, i share with you a question: How is possible MySql complains about free space on device, with a lot of available space?
Anyone knows this MySQL bug? Has anyone had the same problem?
We found at Planet.mysql, na article that describes the same issues, that i described in this text, but it did’t show up any solution for the problem. This articule clearly associate the issues with no free space on disk:
https://planet.mysql.com/entry/?id=5989853
Aditional information: trying to solve the problem, we’ve executed sobe workarounds, but they did’nt help us. These workarounds are suggested for an specific problem correction: the crashed Mysql.proc table. They did’nt work, aparently, due to the free space issue, and not to a crash on Mysql.proc table, as we, first of all, noticed.
WorkAround 1: We have executed the command mysql_upgrade. The upgrade as runned correctly, but the problem was not solved.
WorkAround 2: We’ve tried to repair the mysql.proc table, with the myisamchk -r -q command, but the problem was not solved. We got this instruction in the following article: https://dev.mysql.com/doc/refman/8.0/en/myisam-repair.html
Best Regards,
Jorge Eurípedes
How to repeat:
Trying to create a table, procedure, function or database.
Suggested fix:
There's no information of how to fix it. I found an article that show the same problems, but don't offers a solution: https://planet.mysql.com/entry/?id=5989853
