Error importing database in phpmyadmin

0
votes

I've been working on my site locally and i'm now trying to migrate it to a live. I export the local sql database and try to import it to the new database but it returns the error:

SQL query:

Table structure of table wpmedokonta_commentmeta

CREATE TABLE wpmedokonta_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_ci DEFAULT NULL , meta_value LONGTEXT COLLATE utf8mb4_unicode_ci, PRIMARY KEY ( meta_id ) , KEY comment_id ( comment_id ) , KEY meta_key ( meta_key ( 191 ) ) ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

MySQL said: Documentation

1273 - Unknown collation: 'utf8mb4_unicode_ci'

Anyone know why this happens? Not sure if it helps but i'm not using comments anywhere in my site...

6
mysqldatabasewordpressphpmyadminmigration

6 Answers

3
votes

You could also change utf8mb4_unicode_ci to utf8_general_ci and utf8mb4 to utf8 in your sql file.

1
votes

Probably this is because you have different MYSQL version. check if the version of local server is same as live server.

0
votes

Replace utf8mb4_unicode_ci with with utf8_unicode_ci. It should work.

Also if you see InnoDB in your database, replace InnoDB with MyISAM.

0
votes

I had the same issue, and I only had to maximize the compatibility when exporting on the local servidor.

For that, on the Export Method you need to chose "Custom - display all posible options" as the folow image:

Export Method

Then you need to check the box "Database system or older MySQL server to maximize output compatibility with" and chose MYSQL40

Compatibility with MYSQL40

Now your export file is done, just download it.

And last but not least, remember to put the same SQL compatibility (MYSQL40) on the importing step as folow:

Import File Compatibility

0
votes

Make sure that in phpMyAdmin you first change the Server connection collation from utf8mb4_unicode_ci to utf8_general_ci as mentioned by @Kristy Kavada above.

If it still doesnt work make sure you have added a ; in use databasename; as I had missed my semicolon and was getting the error.

0
votes

Step one create a database with unique name and then open the database then import the database you have exported