Please keep in mind that TRUNCATE TABLE
is a DDL command that tells the InnoDB storage engine to pretend there are no records.
According to the MySQL Documentation on TRUNCATE TABLE :
When fast truncation is used, it resets any AUTO_INCREMENT counter to
zero. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to
zero by TRUNCATE TABLE, regardless of whether there is a foreign key
constraint.)
However, doing TRUNCATE TABLE
does nothing for space already allocated.
To quickly drop the space down to zero rows, you can do the following:
#
# Algorithm 1
#
CREATE TABLE watchdog_new LIKE watchdog;
ALTER TABLE watchdog RENAME watchdog_zap;
ALTER TABLE watchdog_new RENAME watchdog;
DROP TABLE watchdog_zap;
Since TRUNCATE TABLE
tells mysql that the table is considered empty, you can do this:
#
# Algorithm 2
#
TRUNCATE TABLE watchdog;
ALTER TABLE watchdog ENGINE=InnoDB;
These two steps behave the same way as Algorithm 1
.
These steps also can be done with
#
# Algorithm 3
#
TRUNCATE TABLE watchdog;
OPTIMIZE TABLE watchdog;
Internally, OPTIMIZE TABLE would perform Algorithm 1
and then perform ANALYZE TABLE. For InnoDB, the ANALYZE TABLE
phase of OPTIMIZE TABLE
is bypassed. In fact, it is unnecessary because index statistics are recomputed on each query executed against an InnoDB table.
InnoDB tables cannot be copied the same way that MyISAM tables can.
Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.
I wrote two posts in DBA StackExchange about this tablespace id concept
Here is excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.
You could apply the suggestions from the Chris Calendar link, or you could go back to the old installation of mysql, startup up mysql, and then mysqldump the ddms
database. Then, import that mysqldump into your new mysql instance. Trust me, this would be far easier.
Best Answer
You can go to the OS and drop the file because the data dictionary in the system tablespace (ibdata1) does not know of the
.ibd
file's existence any more.I wrote a post 2 months ago about deleting temporary
.ibd
files : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave. It applies in your case as well.Give it a Try !!!