problem in converting big table from type MyISAM to INNODB

[fsqueeen]

i'm trying to convert a big table (approx 200,000 rows) from type MYISAM to INNODB but i found problem after converting the table type.

I tried 2 ways to convert it,

TblA - myISAM (original table with 200,420 rows)
TblB - (to be INNODB table)

1. Copy structure of TblA to TblB, alter TblB to INNODB type, then insert records from TblA into TblB - the error is, TblB (203,373 rows) becomes more rows than TblA after these operations!

2. Copy structure + data of TblA to TblB, then alter table type of TblB to INNODB type - the error is, TblB (1xx,xxx rows) becomes less rows than TblA now!

I don't know what's wrong with these operations, after all, i cant get a INNODB type table that has same rows number with the original table... pls help...

thanks in advance...

 

[TonyGroves]

How are you counting the rows?

 

[fsqueeen]

from phpmyadmin, juz by looking at the total rows for this table.

 

[TonyGroves]

If you do "SELECT COUNT(*) FROM tblname" for both tables, do you still get different figures?

 

[bstjean]

If you just want to convert from MyISAM to MyInnoDB, then it<s fairly simple.

1) MAKE A BACKUP OF YOUR DATABASE (OR TABLE)
2) ALTER TABLE tableToConvert TYPE=InnoDB;

Voilà!

 

[fsqueeen]

Thanks TonyGroves, by using this query to count table rows, i able to get the same row number finally!

but, may i know why the total rows shown is different everytime i browse through this table? And, what is meant by this sentence -> InnoDB free: 46080 kB ?

thanks...

 

[TonyGroves]

I've no idea why it shows different figures. It sounds like a bug in PHPMyAdmin, but I've never used that program, so there could be a reason for it. I don't know what the "InnoDB free" figure means; it could be an indication of the total file space used.