1

I have an SQL dump and I want to import it. First, I tried this in terminal: 

mysql -u root -ppass
create database db_name character set utf8 collate utf8_general_ci;
mysql -u root -ppass db_name < dump.sql

And I got the error: 

ERROR 1253 (42000) at line 23: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'

So, I guess that means the dump I'm trying to import is decoded in latin1.

Then, I opened the SQL dump and looked at it. I saw this line: 

CREATE DATABASE IF NOT EXISTS `db_name` DEFAULT CHARACTER SET latin1 COLLATE utf8_unicode_ci;

So, I did this: 

mysql -u root -ppass
create database db_name character set latin1 collate utf8_unicode_ci;

And I get this error: 

ERROR 1253 (42000): COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'

I guess the database dump file is broken. It's character set or encoding is not right I guess. Or maybe I'm doing something wrong.

What must I do to import this database? Thank you very much.

citizen_of_noobville
  • 327
  • 7
  • 21
  • http://stackoverflow.com/questions/21911733/error-1115-42000-unknown-character-set-utf8mb4-in-mysql – Nabin Dec 21 '14 at 15:11

1 Answers1

0

Your commands are right, but you must change your MySQL settings to avoid the error:

  • Edit this file:

    sudo nano -c /etc/mysql/my.cnf

  • Add these character settings:

    character-set-server=utf8 collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' init_connect='SET collation_connection = utf8_unicode_ci' skip-character-set-client-handshake

  • Finally, restart MySQL daemon.

  • Why do I get an error if my commands are right? I've never seen an error like this before. Is this because of bad database design or something? – citizen_of_noobville Oct 23 '14 at 10:16
  • @citizen_of_noobville As I said, the commands are right, the problem is in your MySQL settings. Did you edit that file I showed you ? –  Oct 23 '14 at 10:18
  • I did. I inserted those and restarted my mysql daemon using `sudo service mysql restart`. However, it still doesn't work. – citizen_of_noobville Oct 23 '14 at 11:04