MySQL will not preserve symbol information on import
I am trying to import a large SQL file; created by mysqldump on one server and then converted with iconv; using the following commands:
$ mysqldump -uxxx -p xxx > data.sql
$ iconv -f UTF-8 -t ISO-8859-1//TRANSLIT data.sql data.iconv.sql
I noticed that "\ x91" in the database turned into "\ xc3 \ x82 \ xc2 \ x91" in the dump because mysqldump is trying to convert everything to utf-8. I converted it back to "\ x91" with iconv. If I don't convert them, they will be named "\ xc2 \ x3f" on the new server; not just "\ x3f" as it is now.
So, as before, there are some characters that are unique to the cp1252 encoding, such as "\ x91" and "\ x92". Both of these characters become "\ x3f" in the new database; this is the command i use to import:
$ /opt/mysql5/bin/mysql -uxxx -p -Dxxx < data.iconv.sql
Does anyone know how this might happen and what to do to prevent it? Knowing this, I must transfer the data exactly the way it was.
Additional Information:
I used this source for iconv things. As you can see, on this page, the other person had problems with cp1252; perhaps the reason.
Server version:
old-host: Version 10.10. Distribution 5.0.18 for pc-linux-gnu (i486) new-host: Ver. 10.11. 5.0.51 distribution, for pc-linux-gnu (i686)
source to share
On my system, mysqld stores data in latin1 with Swedish collation by default. Likewise, the mysql command line client provides data in latin1 by default. mysqldump, on the other hand, defaults to utf-8.
This causes problems when exporting data via mysqldump and then importing using the mysql command line client that are not common between the two character sets are mutated.
The solution is to force mysqldump to decorate the data with additional commands that will set the mysql clients' character set correctly:
mysqldump --set-charset ...
This will add " set name = utf-8
" to the dumped data by default . This can now be imported by the mysql client.
An additional option " --default-character-set=xxx
" can be used with mysqldump to convert the dump to something other than utf-8.
Using a parameter -set-charset
should prevent you from using the icon at all.
source to share
What version of mysqldump are you using?
The latest version 5 released table dump packages with commands that set the character set, for example:
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE ...
...
...
SET character_set_client = @saved_cs_client;
Could their presence / absence affect your import?
source to share
Try: -
iconv -f IBM-1252 -t ISO-8859-1
Not sure why my previous answer was flagged. utf-8 is not ASCII! All 7-bit utf characters are identical to the 7-bit ASCI character set, but the first bit b'1000000 has a special meaning in UTF-8 and indicates that the character is a two or four byte unicode character.
source to share