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

ostraatenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.