MyISAM database transfer
not sure if this is the right section if so can it please be moved. Anyway here is my situation.
A client had a previous website up with a blog that was wordpress i have re-created the entire site in another wordpress database and forgot about the posts from the previous one. The website has since been redirected to the new site i still have FTP access to the old site i just cant get into the wordpress to do an export i can only do it via the phpMyAdmin panel that i know of and this is where the problem comes in the old database is of type MyISAM and the new one is of type InnoDB and will not allow me to simply take the wp_posts and put it in the new one.
Is there a way i can convert it so i can use it in the new database?
by the way if you are unclear of my problem still let me know and i will try and explain it better for you.
One easy thing to do: dump the old one, getting the dump as MyISAM.
On the new site, create a TEMPORARY database and import the MyISAM tables to *THAT* database. [If the dump file from the old DB specifies the database name, just use an editor to change it in the dump file.]
Now just construct a query that will copy the data from the temp database table(s) into the InnoDB tables in the main database.
Might be tedious if there are lots of tables and lots of fields, but probably lees work than any other way.
When done, you can just drop the temp database.
Should have mentioned that I don't use phpmyadmin...I just do it all from command line. So I don't know what restrictions phpmyadmin imposes on you.
I dont have access to all the files till monday but i did try this but it just kept give me a error at like x saying wrong syntax saying something about TYPE = MYISAM i cant remember the exact line i will check when back at work. But i tried editing the file and just replacing the MYISAM with innodb but still same error same place.
Thanks for you reply i will try this process again
Ahhh...it is possible that the MySQL installation you are trying to import into doesn't support MyISAM. That's a possible but seldom used option when installing MySQL.
SO that would explain the inability to import MyISAM, at all.
Doesn't explain why changing from
ENGINE=INNODB didn't work, unless you have data integrity problems the MyISAM ignored (as it almost always does) where INNODB choked on them.
For that, I'd need the complete error message to hope to diagnose the problem.
Perhaps a better approach would be to create a temp database on the *OLD* system and create the tables in that temp DB as INNODB. Then use SQL to copy the data from the MyISAM tables to the temp DB's INNODB tables.
If that works, then dump the temp DB tables and import them.