PDA

View Full Version : Problem replicating a MySQL on SQLite



RazZziel
15th December 2010, 20:35
Hi, I'm replicating a remote MySQL database onto a local SQLite database with QT4, SELECTing the remote data and INSERTing it into the local database.

On Windows it worked flawlessly, but on Linux I'm facing encoding problems, with stuff like Euro symbols (€) or Spanish accents (áéÃ*óú). The original MySQL database has the schema set to UTF8:


CREATE SCHEMA IF NOT EXISTS `4access` DEFAULT CHARACTER SET UTF8

Accessing directly using the mysql client, everything is fine:


mysql> select * from currency;
+-------+---------+---------+-----------+---------+---------------------+
| cu_id | cu_name | cu_code | cu_symbol | cu_rate | version |
+-------+---------+---------+-----------+---------+---------------------+
| 1 | Euro | EU | € | 1 | 2010-12-15 20:28:04 |
| 2 | Dollar | US | $ | 1.5 | 2010-12-15 20:28:04 |
| 3 | Pound | UK | £ | 0.8 | 2010-12-15 20:28:04 |
+-------+---------+---------+-----------+---------+---------------------+

But after replicating the database with my application, I open the sqlite databse and some characters are screwed:


sqlite> select * from currency;
1|Euro|EU|€|1.0|2010-12-15T20:28:04
2|Dollar|US|$|1.5|2010-12-15T20:28:04
3|Pound|UK|£|0.8|2010-12-15T20:28:04

Does anyone know where the problem can be?

ChrisW67
15th December 2010, 23:53
You don't tell us exactly how you do the transfer. It seems likely that the character set on your connection (different from the database char set) to the Mysql database is Latin-1 rather than UTF-8. From the mysql man page:


· --default-character-set=charset_name

Use charset_name as the default character set for the client and
connection.

A common issue that can occur when the operating system uses utf8
or another multi-byte character set is that output from the mysql
client is formatted incorrectly, due to the fact that the MySQL
client uses the latin1 character set by default. You can usually
fix such issues by using this option to force the client to use the
system character set instead.

See Section 9.5, “Character Set Configuration”, for more
information.

RazZziel
5th October 2011, 18:00
Sorry, didn't see your reply. I fixed the problem issuing a SET NAMES UTF8 right after I open my MySQL connection