[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1491
  • Last Modified:

Importing a mysqldump file when there are many character set conversion problems

Hi,
I'm trying to create a database using an export SQL file that I created with mysqldump. The source database is using character set latin1 and collation latin1_swedish_ci. In fact both database are created equally with regards to character  and collation variables are just similar. See code snippet.

Initially I thought it would be something simple like:
mysqldump --skip-opt -u**** -p**** -h myhost database 1> dump.sql
And then import:
mysql -u*** -p**** -v database < dump.sql

Of course I was wrong?

I noticed after some experimenting that character conversions where going on e.g. glossário_de_tradução was converted into glossÒ¡rio_de_traduÒ§Ò£o in the dump.sql file. Fixing that was of course first order of business. By changing the mysqldump command line I was able to produce a correct dump.sql:
mysqldump --default-character-set=latin1 --allow-keywords --quote-names --skip-opt -u**** -p**** -h myhost database 1> dump.sql

Now I have been working on importing that file for hours and hours with very little results to show for it. No matter what character sets and collation I use, character set conversions are going on which results in stuff in my database that looks like: glossário_de_tradução.

So I tried to create the database with statements like

create database database character set utf8 collate utf8_general_ci;
create database database character set latin1 collate latin1_swedish_ci;

With similar bad results. Changing the dump file didn't work as well.

CREATE TABLE `pages` (
...
)
ENGINE=innoDB CHARSET=utf8 COLLATE=utf8_general_ci
;

or

CREATE TABLE `pages` (
...
)
ENGINE=innoDB CHARSET=latin1 COLLATE=latin1_swedish_ci
;

I expected a database migration to be hard work but I never expected anything like this. Maybe my own fault for being such a cheap skate and wanting to use a free database instead of a commercial product. But anyway, no use in crying of the sea of spilled MySQL milk, migrating to another database is not an option anymore, my question now is how do you actually export and import a MySQL database?

Thanks and Regards,
Onno
mysql> show variables like 'collation%'
    -> ;
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci | 
| collation_database   | latin1_swedish_ci | 
| collation_server     | latin1_swedish_ci | 
+----------------------+-------------------+
3 rows in set (0.00 sec)    
 
mysql> show variables like 'character%'
    -> ;
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           | 
| character_set_connection | latin1                           | 
| character_set_database   | latin1                           | 
| character_set_filesystem | binary                           | 
| character_set_results    | latin1                           | 
| character_set_server     | latin1                           | 
| character_set_system     | utf8                             | 
| character_sets_dir       | /opt/lampp/share/mysql/charsets/ | 
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

Open in new window

0
ostraaten
Asked:
ostraaten
1 Solution
 
ostraatenAuthor Commented:
I solved the damn problem myself with the following statements:
mysqldump --default-character-set=latin1 --allow-keywords --quote-names --skip-opt -u*** -p*** -h*** db 1> dump.sql

Create dabase:
create database db character set utf8 collate utf8_general_ci;
Import:
mysql --default-character-set=utf8  -u#{user} -p#{pass} -v #{db} < dump.sql

I don't understand why this works but I don't mind.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now