Moving data from MyISAM to InnoDB - records count error

Question

I've been trying to move some data from a MyISAM database table to a different InnoDB database.

The method I used to do that was to Select into outfile from the MyISAM table and then LOAD DATA INTO the InnoDB.

The tables don't match perfectly, but the InnoDB table looks like this

 Field            | Type         | Null | Key | Default | Extra          |
------------------+--------------+------+-----+---------+----------------+
 id               | int(11)      | NO   | PRI | NULL    | auto_increment |
 name             | varchar(500) | YES  | MUL | NULL    |                |
 sample_url       | varchar(500) | YES  |     | NULL    |                |
 summary          | text         | YES  |     | NULL    |                |
 path             | varchar(500) | YES  |     | NULL    |                |
 article_title    | varchar(500) | YES  |     | NULL    |                |
 mne_url          | varchar(500) | YES  |     | NULL    |                |
 website          | varchar(500) | YES  |     | NULL    |                |
 clear_name       | varchar(500) | YES  | MUL | NULL    |                |

so I selected from the MyISAM table with

 
SELECT \N, name, \N, \N, \N, \N, \N, mne, \N, clean FROM table INTO OUTFILE 'swapTable.txt'

I then used the LOAD DATA INFILE command to load the new data into the table.

When I view the database data in PHPmyAdmin, it shows 700k rows. However, a SELECT COUNT(*) returns 0. SELECT * also returns 0 rows.

InnoDB doesn't have a repair table command, but I tried OPTIMIZE, but it didn't make any changes.

Is there maybe a problem with how I'm trying to move the data? or is there a way to look into the database? THE .frm file is only 17k, which makes me think that the data didn't make it into the db, but I don't know why PHPmyAdmin would show 700k rows.

Answer 1

Why don't you run

ALTER TABLE mytable ENGINE=innoDB

Then take a dump of structure and content and run that in the other db. After that you can make changes to the table, if needed.