The biggest thing most people forget about TRUNCATE TABLE is that TRUNCATE TABLE is DDL and not DML. In InnoDB, the metadata within ibdata1 contains a numbered list of InnoDB tables. Using TRUNCATE TABLE causes the internal metadata id of the InnoDB table to shift. This happens because TRUNCATE TABLE effectively does the following:
Example: To truncate an InnoDB Table called mydb.mytb
USE mydb
CREATE TABLE newtb LIKE mytb;
ALTER TABLE mytb RENAME oldtb;
ALTER TABLE newtb RENAME mytb;
DROP TABLE oldtb;
The new mytb would thus have a different internal metadata id.
When you copied the .ibd file to some other place, the .ibd contains within it the original internal metadata id. Simply putting the .ibd file back does not cause a reconciliation of the internal metadata id with that of the one in ibdata1.
What you should have done is this:
Copy the .ibd file of the InnoDB table. Then, run this
ALTER TABLE tablename DISCARD TABLESPACE;
To bring it back later on, copy the .ibd file back into datadir and then run
ALTER TABLE tablename IMPORT TABLESPACE;
This would have preserved the internal metadata id.
Make sure .frm is always present.
I once helped a client restore 30 InnoDB tables he hosed in the same manner. I had to use another DB server and play some games with adding and dropping InnoDB tables to hunt down the correct internal metadata id.
The client found this article : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file . We used it and it helped a great deal. I hope it helps you.
"no space left on the device" could also mean that the partition is out of inodes. Use df -i
to check inode usage.
Edit: Each file, directory, and symbolic link requires one inode. So the idea is to remove some files from the / partition. It doesn't matter how large the files are. You can of course just pick some files on the partition and move them to another partition, if you can find some that are convenient to move.
It's unusual to run out of inodes, so it's possible you have some directory full of small or zero-length files somewhere that you're not aware of. Unfortunately, I don't know of an easy way to find this sort of thing. Running find / -xdev
would print the name of every file and directory on the partition. You might be able to spot some suspicious directory full of empty files.
If you can add additional disk partitions to the system, you could pick some directory on the / partition and make it into a separate partition. Copy all of the files in that directory to the new partition, delete them from the / partition, then mount the new partition on top of the old (and now empty) directory.
The nuclear option would be to back up the / partition, wipe it, and rebuild it with more inodes. That's an advanced topic and I won't try to explain it here.
Best Answer
Is possible to TRUNCATE TABLE ? If yes, please try that.
Is MySQL server binary log enabled ? If YEs, Purge binarylogs & then delete records.
Is MySQL Server enabled with general log or Audit Log ? If Yes, Purge general log
Check error log size & path if needed purge it.
Note: Delete statement will not release space & if your server is INNODB_FILE_PER_TABLE=OFF then TRUNCATE or DROP statement will also not reclaim the space