[Case 95813] Transferring accounts on MySQL 5.6 to a server using 5.5

[Kent Brockman]

I had to transfer in one account from one of my servers to another one. Both them are using cPanel 11.42.1 (build 12). They have the same configs in almost everything.

I did a transfer using the feature "Copy Multiple Accounts/Packages From Another Server" with the following settings:
Don't Compress Transfers: unticked
Use SSL: unticked
Use Incremental Backups speed-up: ticked
Allow Override: unticked

Authentication:
Login: using root with Password (default)
Use FTP to Transfer: unticked
(I unticked FTP to avoid errors. Because we use FTP in a non standard port to discourage brute force attacks.)

Also, I didnt transfered packages because both servers share the same ones.


I found then, that the copied account didnt fully ported the database: it created the database and the user with its privileges but havent imported the tables or any data. Also, it didnt copy the cpanel skin (Style) nor the user selected locale.

I have to go on and check if everything is ok or messed up, but I wanted to let you know so you can test it and verify what bug may be introduced in the last versions.

The last time I needed to transfer an account (and it worked just great) was in July 2013, so I can't help telling in what version could this bug being introduced. Just for the record, I always use the last stable version and I upgrade to it almost always the same day it is just released.

Hope you can find the bug.
Best regards!

 

[vanessa]

If the restore created the database and privs, but did not import the content, it's most likely that the import simply failed. If you have output from the process you may want to review that, or attempt to import one of the databases manually to see if you're able to trigger an error.

 

[Kent Brockman]

I ended importing the database from a dump of the original using phpmyadmin. I had no time to do more tests. The only thing I can tell is, the transfer failed to import the database. Be careful of that. Test it internally. I told you the settings I used, try to replicate it. Thanks.

 

[vanessa]

I tested this utility on a group of 5 accounts with the same settings posted, and unfortunately was not able to reproduce the problem.

 

[Kent Brockman]

Well, ok. Do you remember what is the usual path to find the log for these transfers? I didnt take note of it, but as you said, it may contain some important output.

 

[cPanelMichael]

Hello

The transfer logs are stored in this directory:

/var/cpanel/logs/

Thank you.

 

[Kent Brockman]

Michael, thank you for the tip.
I found a while to retrieve the log and I found the following error in it, and discovered the reason: the origin server has MySQL 5.6, and the destination server has MySQL 5.5.

(I replaced the real database name by MYDB_site)

Restoring MySQL databases....
Restoring database MYDB_site......
ERROR 1064 (42000) at line 18: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=8' at line 9 ERROR 1146 (42S02) at line 32: Table 'MYDB_site.wp_commentmeta' doesn't exist ERROR 1146 (42S02) at line 33: Table 'MYDB_site.wp_commentmeta' doesn't exist ERROR 1146 (42S02) at line 34: Table 'MYDB_site.wp_commentmeta' doesn't exist ERROR 1064 (42000) at line 42: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=3' at line 22 ERROR 1146 (42S02) at line 69: Table 'MYDB_site.wp_comments' doesn't exist ERROR 1146 (42S02) at line 70: Table 'MYDB_site.wp_comments' doesn't exist ERROR 1146 (42S02) at line 71: Table 'MYDB_site.wp_comments' doesn't exist ERROR 1064 (42000) at line 79: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB' at line 17 ERROR 1146 (42S02) at line 101: Table 'MYDB_site.wp_links' doesn't exist ERROR 1146 (42S02) at line 102: Table 'MYDB_site.wp_links' doesn't exist ERROR 1146 (42S02) at line 103: Table 'MYDB_site.wp_links' doesn't exist ERROR 1064 (42000) at line 111: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=261' at line 8 ERROR 1146 (42S02) at line 124: Table 'MYDB_site.wp_options' doesn't exist ERROR 1146 (42S02) at line 125: Table 'MYDB_site.wp_options' doesn't exist ERROR 1146 (42S02) at line 126: Table 'MYDB_site.wp_options' doesn't exist ERROR 1146 (42S02) at line 127: Table 'MYDB_site.wp_options' doesn't exist ERROR 1064 (42000) at line 135: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=226' at line 9 ERROR 1146 (42S02) at line 149: Table 'MYDB_site.wp_postmeta' doesn't exist ERROR 1146 (42S02) at line 150: Table 'MYDB_site.wp_postmeta' doesn't exist ERROR 1146 (42S02) at line 151: Table 'MYDB_site.wp_postmeta' doesn't exist ERROR 1146 (42S02) at line 152: Table 'MYDB_site.wp_postmeta' doesn't exist ERROR 1064 (42000) at line 160: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=875' at line 30 ERROR 1146 (42S02) at line 195: Table 'MYDB_site.wp_posts' doesn't exist ERROR 1146 (42S02) at line 196: Table 'MYDB_site.wp_posts' doesn't exist ERROR 1146 (42S02) at line 197: Table 'MYDB_site.wp_posts' doesn't exist ERROR 1146 (42S02) at line 198: Table 'MYDB_site.wp_posts' doesn't exist ERROR 1064 (42000) at line 206: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB' at line 7 ERROR 1146 (42S02) at line 218: Table 'MYDB_site.wp_term_relationships' doesn't exist ERROR 1146 (42S02) at line 219: Table 'MYDB_site.wp_term_relationships' doesn't exist ERROR 1146 (42S02) at line 220: Table 'MYDB_site.wp_term_relationships' doesn't exist ERROR 1146 (42S02) at line 221: Table 'MYDB_site.wp_term_relationships' doesn't exist ERROR 1064 (42000) at line 229: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=5' at line 11 ERROR 1146 (42S02) at line 245: Table 'MYDB_site.wp_term_taxonomy' doesn't exist ERROR 1146 (42S02) at line 246: Table 'MYDB_site.wp_term_taxonomy' doesn't exist ERROR 1146 (42S02) at line 247: Table 'MYDB_site.wp_term_taxonomy' doesn't exist ERROR 1146 (42S02) at line 248: Table 'MYDB_site.wp_term_taxonomy' doesn't exist ERROR 1064 (42000) at line 256: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=5' at line 9 ERROR 1146 (42S02) at line 270: Table 'MYDB_site.wp_terms' doesn't exist ERROR 1146 (42S02) at line 271: Table 'MYDB_site.wp_terms' doesn't exist ERROR 1146 (42S02) at line 272: Table 'MYDB_site.wp_terms' doesn't exist ERROR 1146 (42S02) at line 273: Table 'MYDB_site.wp_terms' doesn't exist ERROR 1064 (42000) at line 281: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=34' at line 9 ERROR 1146 (42S02) at line 295: Table 'MYDB_site.wp_usermeta' doesn't exist ERROR 1146 (42S02) at line 296: Table 'MYDB_site.wp_usermeta' doesn't exist ERROR 1146 (42S02) at line 297: Table 'MYDB_site.wp_usermeta' doesn't exist ERROR 1146 (42S02) at line 298: Table 'MYDB_site.wp_usermeta' doesn't exist ERROR 1064 (42000) at line 306: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=3' at line 15 ERROR 1146 (42S02) at line 326: Table 'MYDB_site.wp_users' doesn't exist ERROR 1146 (42S02) at line 327: Table 'MYDB_site.wp_users' doesn't exist ERROR 1146 (42S02) at line 328: Table 'MYDB_site.wp_users' doesn't exist ERROR 1146 (42S02) at line 329: Table 'MYDB_site.wp_users' doesn't exist Done
Done
Done

So, it is really impossible to migrate accounts from a server with 5.6 to 5.5? no workaround? no fix? maybe non-recommended?

Thanks.

 

[cPanelMichael]

Case 95813 addresses an issue where pkgacct downgrades MySQL dumps to 4.x when the source server is version 5.6 and the target server is version 5.5. You can monitor our change log for the inclusion of this case number.

Thank you.

 

[Kent Brockman]

ok thank you!
Hope you can figure out the error soon. I'll had to migrate several accounts to the oldest server and having to import every db manually is a PITA.