Situation :In cPanel database management we have come across the issue that the truncate didn’t free the innoDB space. The issue is that we have Configured the MySQL server with innodb per table. There was a table comments having approximate 600+ MB size all because of comment spamming. I went to the table field in phpMyAdmin and clicked on “Empty” and all went good in regards to truncation.
Table has no records or zero record. But the phpmyAdmin still said
“InnoDB free: 67778911 KB” and when I checked the size by logging to the server in backend, it also showed the size.
12K /var/lib/mysql/db_gallery/comments.frm
653M /var/lib/mysql/db_gallery/comments.ibd
What could be the issue.? Won’t truncate work by actually removing the data from the .ibd file ? What is happening ?
I googled and found this link at StackOverFlow http://dba.stackexchange.com/questions/10779/problem-with-innodb-per-table-file-sizes
Went ahead to Operations tab –> Table Maintenance –> “Optimize table” and it’s fixed. It showed “InnoDB free: 0 kB” and a backend listing showed the below size history too ð
12K /var/lib/mysql/db_gallery/comments.frm
100K /var/lib/mysql/db_gallery/comments.ibd