Cannot migrate site no matter what

I have never had this issue before when moving sites, but this one is proving to be super difficult for some reason. No matter if I use plugins, try manually importing database & files, etc. it won't accept it.

This is an example of an error I get when trying to manually import the database:

SQL query:

--
-- Database: tactical_seocon
--
-- --------------------------------------------------------
--
-- Table structure for table con_commentmeta
--
CREATE TABLE IF NOT EXISTS con_commentmeta (
meta_id bigint( 20 ) unsigned NOT NULL AUTO_INCREMENT ,
comment_id bigint( 20 ) unsigned NOT NULL DEFAULT '0',
meta_key varchar( 255 ) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL ,
meta_value longtext COLLATE utf8mb4_unicode_520_ci,
PRIMARY KEY ( meta_id ) ,
KEY comment_id ( comment_id ) ,
KEY meta_key ( meta_key ( 191 ) )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_520_ci AUTO_INCREMENT =1;

MySQL said: Documentation
#1273 - Unknown collation: 'utf8mb4_unicode_520_ci'

That is the error I get when trying to import the database via PHPMyadmin.

I get a similar error if trying to use the Duplicator plugin's installer.php file, and this is an excerpt from the error log:

DATABASE RESULTS
--------------------------------------
**ERROR** database error write 'Unknown collation: 'utf8mb4_unicode_520_ci'' - [sql=
CREATE TABLE con_commentmeta (
meta_id bigint(20) unsigned NOT NULL ...]
**ERROR** database error write 'Unknown collation: 'utf8mb4_unicode_520_ci'' - [sql=
CREATE TABLE con_comments (
comment_ID bigint(20) unsigned NOT NULL ...]
**ERROR** database error write 'Unknown collation: 'utf8mb4_unicode_520_ci'' - [sql=
CREATE TABLE con_links (
link_id bigint(20) unsigned NOT NULL AUTO_I...]
**ERROR** database error write 'Unknown collation: 'utf8mb4_unicode_520_ci'' - [sql=
CREATE TABLE con_options (
option_id bigint(20) unsigned NOT NULL AU...]
**ERROR** database error write 'Unknown collation: 'utf8mb4_unicode_520_ci'' - [sql=
CREATE TABLE con_postmeta (
meta_id bigint(20) unsigned NOT NULL AUT...]
**ERROR** database error write 'Unknown collation: 'utf8mb4_unicode_520_ci'' - [sql=
CREATE TABLE con_posts (

Could it be the host I'm trying to move it from? Or perhaps the hardening of the site due to Defender? I don't know what is wrong.

Thanks!

  • Rupok

    Hi Meredith,

    Could it be the host I'm trying to move it from? Or perhaps the hardening of the site due to Defender? I don't know what is wrong.

    Most probably it's happening because of different MySQL version on your two servers. Defender doesn't change Database collation, so it's totally out of the scenario. In your source database, your database collation is "utf8mb4_unicode_520_ci" but your target server database isn't detecting this collation.

    #1273 - Unknown collation: 'utf8mb4_unicode_520_ci'

    Error 1273 means that your database is using a collation, which the MySQL server does not understand. This can happen, when you use a database, which has been created with a newer version of MySQL, with an older MySQL server version. This problem can be solved by converting the wrong collations from utf8mb4_unicode_520_ci to utf8_general_ci. There is a script on stackoverflow, which does exactly that: http://stackoverflow.com/a/29939906/2808424

    So can you please try changing your database collation first and then try migrating? I believe, it will work. If it still doesn't work, can you please ask your host regarding why the database is not detecting "utf8mb4_unicode_520_ci" collation?

    Please let us know if you still have any confusion or if you are stuck at any point. We will be glad to help.

    Have a nice day. Cheers!
    Rupok

  • Rupok

    Hi Meredith,

    As you said you were trying to import manually, so I assume you have a backup of your database already. We will need this if anything goes wrong. So please save this.

    At first create a file with any name with a ".php" extension. Open that file, paste the code from that StackOverflow reply. Please update database name, db user, db password etc. according to your old database server credentials and save the file. Suppose, you named it "dbupdate.php". Now, login to your old server (from which you are trying to move away) with any FTP application like FileZilla and go to the root folder of your domain. Upload this "dbupdate.php" there. Now in your browser, go to http://yourdomain.com/dbupdate.php and if everything goes alright, it will give you an alert saying that collation change was successful. Now take a backup again from your old database server and try to upload in your new database server through PHPMyAdmin. It should work.

    Please let us know if it doesn't work or if you have any confusion or if you are stuck at any point. We will be glad to assist further.

    Have a nice day. Cheers!
    Rupok

  • Meredith

    Okay so I tried that, putting the dbname, username & dbpassword into the fields for lines 13-18. I went to the file and it presented me with a form in my browser, so I filled that in with the same thing. Then when I pressed submit it took me to a 404 page. So, I tried putting the values in the file everywhere it said dbname, dbusername, dbpassword etc. Then when I tried to go to it in my browser I got this error:

    Parse error: syntax error, unexpected '*', expecting variable (T_VARIABLE) or '$' in /home/tacticalseoconsu/public_html/dbupdate.php on line 28

    But the password has an asterix in it. Is that bad?

    Thanks again so much for your help!

  • Meredith

    Okay so I was able to get the script to run and it said successful. However after I migrated I still ended up with several errors in the error log (still the same thing) so several tables were dropped, resulting in the navigation missing amongst other things. I wonder why the error is still present but it was able to move over most of it?

    ERROR** database error write 'Unknown collation: 'utf8mb4_unicode_520_ci''

  • Rupok

    Hi Meredith,

    Well, I can see that this thread is marked as resolved. But as you said several tables were dropped, I don't think it was a successful migration. I can try to migrate if you want. Can you send me message with your Old and New PHPMyAdmin access credentials through our secure contact form here: https://premium.wpmudev.org/contact/ so I can try doing the migration and collation conversion for you? Please keep a backup of both database on your computer before you send these credentials so we can always revert back to current state if anything goes wrong.

    Subject: "Attn: Rupok"
    - Old Server Database Name
    - Old Server Database Username
    - Old Server Database Password
    - Old Server PHPMyAdmin Login URL

    - New Server Database Name
    - New Server Database Username
    - New Server Database Password
    - New Server PHPMyAdmin Login URL

    - Link back to this thread for reference
    - Any other relevant URLs

    Select "I have a different question" for your topic. This and the subject line ensure that it gets assigned to me.

    I'm looking forward to hear from you and completely resolve this issue as soon as possible.

    Have a nice day. Cheers!
    Rupok

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.