new datafile

[Pompiuses]

I'm using mysql 4.0.16 on win xp.

Since the file mysql stores data in is getting very large (c:\mysql\data\ibdata1), I want to delete this file and start with a new one. How can I do this? I tried to delete this file and restart mysql, but that didn't work.

[Pompiuses]

Anyone??

[Kiwi]

mySql has a a function that will clean up a table with a lot of deleted records: optimise. I would suggest you try that before messing around with the files.

[mordred]

Apart from Kiwis suggestion, what speaks against deleting records by using the MySQL shell, phpMyAdmin or any other administration tool? If you want to get rid of the table, you can drop it and create a new one. For the sake of consistency on your system, don't mess around with the data directory unless you have a very good reason to.

[Pompiuses]

Originally posted by mordred
Apart from Kiwis suggestion, what speaks against deleting records by using the MySQL shell, phpMyAdmin or any other administration tool? If you want to get rid of the table, you can drop it and create a new one. For the sake of consistency on your system, don't mess around with the data directory unless you have a very good reason to.

When you are using InnoDB tables mysql don't delete them from the datafile, even if you delete the records in the database. Which mens that the datafile will grow big fast. Therefore I really need to scratch my datafile! Really no one who has done this before??

[raf]

Yes. you could just create a dumpfile with only the structure (no data) and then i delete ('drop') the table and then we execute the dumpfile to recreate the empty table.

What strange behaviour for the InnoDB table. I'm sure there must be an easier way to flush or truncate the table like i always do for myIsams.

<edit>Check

http://www.mysql.com/doc/en/TRUNCATE.html

</edit>

[Pompiuses]

hmmm.... can't find a solution to this problem. I've dropped all my databases, but the mysql datafile c:\mysql\data\ibdata1 is still 5 GB .

It seems there's no way to shrink this file. That really sucks when you are using InnoDB tables. Looks like the only way is a reinstall of mysql

[Kiwi]

From what I can gather, you cannot optimise InnoDB tables. (which I think is quite a major problem). The solution I've seen is to change the table to MyISAM, optimise it, and change it back:

ALTER TABLE t TYPE = MyISAM;
OPTIMIZE TABLE t;
ALTER TABLE t TYPE = InnoDB; 

I'm not sure that the optimise is the middle is essential, but probably wouldn't hurt. I have no idea what this does to proper FK constraints, especially cascades. I would test it out before relying on it.

The InnoDB people are planning to implement optimise, but haven't done so yet, as far as I can tell.