Type InnoDB Tables Moved

[jackal]

We are in the process of moving some accounts from a server to a new server, we have tried this in whm and also in shell.
Both work but we have one problem that continues to exist.
Somehow in every db that has this type of table (InnoDB ) they get corrupt in the move. All other db table types transfer ok without error.


error : Can't open file: 'words.InnoDB'. (errno: 1)
In phpmyadmin all these type tables say "in use not running"
and the only way to fix is go into var/lib/mysql/ and delete the database. We then went back to old server and into phpmyadmin and downloaded the db and then uploaded via phpmyadmin to the new server and all workked ok , no problems with any tables, so it is something in the move that is doing it.

Any idea what may be causing this, we believe it to be something on the new server because we have transfered on of these sites before to our other server without any issues.

Hope someone can lead us in the right direstion to fix this issue.

 

[xphost]

i have similar problem

 

[xphost]

i found

i try to optimize mysql and just add in /etc/my.cnf with any other options

skip-innodb

so after remove skip-innodb and restart mysql all work fine

 

[perlchild]

Originally posted by xphost
i found

i try to optimize mysql and just add in /etc/my.cnf with any other options

skip-innodb

so after remove skip-innodb and restart mysql all work fine

Using skip-innodb disables innodb tables(so new tables get created as myisam)

Innodb tables are contained in special files and cannot be copied over in the usual manner. I recommend a mysqldump to transfer them from one server to the other.

 

[Allin1Joe]

If cPanel changes the way these files are backed up, then doing a restore on Inno can be done. The issue is they are creating a table, then trying to enable the constraints on that table which reference tables that might not exist yet. Then, data is being inserted that tries to reference data that doesn't exist yet. So, if you change your output to the following format, the restore will work perfectly:

table structure (indexes and primary keys can be placed here)
data

Then, after ALL tables have been created and populated, create the constraints.