1

On my CentOS server, I have got a dump file of a mysql table which contains special czech characters in it (e.g. á, é, č, ž). When I view the file using cat, the special characters are broken (e.g. ĂĄ instead of á).

I need to correct those characters in the dump and then import it back to the database.

I have tried a few different ways already, but non of them worked, including:

  • substitute the characters using sed with the following expression - cat c_schools | sed 's/ĂĄ/á/g' > c_schools - but I get an unterminated s command error,
  • open the file in VIM and fix it manually, but VIM won't let me type the check characters,
  • open the file in NANO and fix it manually, but NANO seems to be unable to work with the file at all (the text changes even as I move with the cursor).

As suggested in a comment, I opened the file in VIM again and try the following commands:

  • set encoding=utf-8 (this one had no effect at all),
  • set termencoding=iso8859-2 (the output is much better after this one but some characters are still incorrectly displayed, such as ? instead of č, ě or š),
  • :set fileencoding (outputs fileencoding=utf-8)

When I copy the file to my Windows machine over FTP and open it in my standard text editor (Notepad++), it is displayed correctly and the encoding used is UTF-8, without BOM.

  • Is it a problem in mysqldump or your terminal not showing the characters correctly? – user4035 Jun 22 '13 at 22:59
  • The problem is in fact that my Wordpress application misunderstands the data (see another question of mine). I was hoping that perhaps the data is corrupted in the database and that I could fix it manually and then import it back. – Dušan Rychnovský Jun 23 '13 at 8:40
  • Copy the dump to your own machine and check it's encoding, so we could exclude the wrong terminal character set on the server. – user4035 Jun 23 '13 at 9:50
  • I have already done that (as noted at the end of the question) and indeed it is displayed correctly. Does that prove for sure that the data in the database are correct and that the problem lies in the Wordpress application? – Dušan Rychnovský Jun 23 '13 at 10:07
  • 1
    > "Does that prove that the problem lies in the Wordpress application?" Yes – user4035 Jun 23 '13 at 10:09
2

That looks like Unicode (UTF-8) data displayed as ISO-8859-2. There are essentially two possibilities:

  1. Your terminal uses ISO-8859-2 as output character set. Then the data itself is probably encoded correctly but displayed incorrectly: What you see is the result that is expected when you display UTF-8 data as if it were ISO-8859-2. Try a terminal and/or an editor that is capable of working with UTF-8. (I'm not sure about nano, but vim is able to work with UTF-8 if you use set encoding=utf-8.)

  2. Your terminal uses UTF-8 as output character set. Then the data itself is encoded incorrectly: It's data that has been "converted" from ISO-8859-2 to UTF-8 even though it was already UTF-8 data. You can use iconv or recode to translate UTF-8 back to ISO-8859-2 (or rather, "doubly encoded UTF-8" back to proper UTF-8), e.g., by iconv -f UTF8 -t ISO_8859-2 yourinputfile.

What terminal program do you use and what's the output of echo $LC_CTYPE?

  • The output of echo $SHELL is /bin/bash. The output of echo $LC_CTYPE is an empty line. – Dušan Rychnovský Jun 23 '13 at 8:31
  • The fact that the file is displayed correctly in Notepad and that LC_CTYPE is unset seems to indicate that it's really a display problem (i.e., case 1 in my original answer), rather than an encoding problem (case 2). – Uwe Jun 23 '13 at 14:00
  • What's your terminal program? Do you use the console (without X11), or do you use any windowing system? In the second case, do you use xterm, rxvt, konsole, gnome terminal, or what other terminal emulator? It is possible to some extent to use UTF-8 on the Linux console, but the support is limited. Most current terminal emulators are able (or can be configured) to work with UTF-8. Search for "unicode" and either "linux console" or the name of your terminal emulator program. – Uwe Jun 23 '13 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.