Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have two production servers running FreeBSD 7.3 (Prod A and Prod B). Locally I'm on OS X Mountain Lion (Local). I have an EC2 staging server running Ubuntu 12.04 (Staging).

I have a mysql database containing UTF8 characters. Each databse and table is set for UTF8. I can see unicode characters rendering in the table and when output to the site application as expected.

Doing a mysqldump on Prod A or Prod B of a database hosted on Prod A works fine. Viewing the .sql file with vim, I can see the unicode characters rendering correctly.

Here's the full mysqldump command I've been using:

mysqldump -uUSER -pPASS --default-character-set=utf8 --set-charset -r output.sql database_name

If I copy this file between Prod A and Prod B, the file is fine.

If I copy this file to either Staging or Local, all unicode characters break.

I have tried a number of methods to copy...

  • zip the .sql on Prod A and scp it to Local or Staging (breaks)
  • Run mysqldump from Staging or Local, directly connecting to mysql database (breaks)
  • Run mysqldump from Staging or Local, via an SSH tunnel to mysql (breaks)
  • Copy the plaintext .sql file via scp from Prod to Staging or Local (breaks)

It seems that any time the file leaves the special Prod environment, unicode breaks.

What could be causing this?

PROD:

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       | /usr/local/share/mysql/charsets/ | 
+--------------------------+----------------------------------+

STAGING:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Edited to note:

If I use a local Mac client SequelPro, I am able to export the tables to Local.

Here are some differences as reported by file:

PROD:

$ file s.sql
s.sql: UTF-8 Unicode English text, with very long lines
$ file -bi s.sql
text/plain charset=utf-8

Local:

->file s.sql
s.sql: ASCII English text, with very long lines
->file -bi s.sql
regular file
share|improve this question
    
Please post output of show variables like '%character%'; in mysql console on both Prod and Staging –  Alex Aug 22 '12 at 22:01
    
Added output above –  Ian Aug 22 '12 at 22:03
    
Try to put character-set-server=utf8 in your my.cnf and then restart the server on Local, that may help. –  Alex Aug 22 '12 at 22:10
    
The file doesn't reach mysql on Local or Staging before I can see it become corrupted. Just viewing the file in vim is enough to see the issue. If I were to try to import it, SequelPro would error and mysql would hang. I will try your suggestion though! –  Ian Aug 22 '12 at 22:13
    
Hmmm I just realized you have different settings on Prod and Staging so my suggestion can be irrelevant. It looks like the DB contains multi-byte data in single-byte storage so you should try to perform the conversion on the fly while dumping. –  Alex Aug 22 '12 at 22:22
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.