[SOLVED] MySQL, UTF-8 and accented characters

Tinkster · 01-28-2010, 06:48 PM

Hi,

How do I stop a MySQL database that is defined as
utf-8 from assuming that 'Sergio' and 'Sérgio' are
the same thing?

It's refusing to insert 'Sérgio' claiming that:

Code:

ERROR 1062 (23000) at line 4433: Duplicate entry 'Sérgio' for key 1

Cheers,
Tink

bigrigdriver · 01-29-2010, 08:10 PM

I think the answer can be found in Chapter 9 of the MySql Reference Manual. At the moment on creating the database, you need to specify the character set to be used (which contains both the accented and unaccented leters), not just specifying utf-8. There are several character sets listed, such as German, Swedish, English, etc.

You may be able to redefine your database to treat accented letters as different from unaccented, or you may be able to redefine your entries/queries to treat them as different.

I hope that this helps solve the problem, or at least gets the cognitive juices flowing which leads to the solution.

Tinkster · 01-30-2010, 03:27 AM

Quote:

Originally Posted by bigrigdriver

I think the answer can be found in Chapter 9 of the MySql Reference Manual. At the moment on creating the database, you need to specify the character set to be used (which contains both the accented and unaccented leters), not just specifying utf-8. There are several character sets listed, such as German, Swedish, English, etc.

I guess I'll have a read of chapter 9, even though the concept of
having to specify TWO character sets (e.g. UTF-8 and Swedish)
appears rather moronic considering that UTF was conceived to get
rid of such idiosyncratic distinctions. But then, this is MySQL,
and I've never held it in very high esteem.

Quote:

Originally Posted by bigrigdriver

You may be able to redefine your database to treat accented letters as different from unaccented, or you may be able to redefine your entries/queries to treat them as different.

I hope that this helps solve the problem, or at least gets the cognitive juices flowing which leads to the solution.

Heh. Kind of. Thanks for the hint - where ever it may lead.

Cheers,
Tink

Tinkster · 02-01-2010, 04:43 PM

'lo patient readers,

A brief update ... after 4 days of ongoing pain I still haven't managed to
import my data ... whether I use "INSERT" statements, "LOAD DATA", or the
command line tool mysqlimport, I can't get accented characters into the database.

Short of setting my system locale to cp1250 which is MySQLs (odd) default
and reinstalling everything I've tried pretty much every option and combination
available, using latin1 (otherwise known as iso-8856-1), utf8 and ASCII with
en_NZ and en_US from the command-line (and in the database server and client
config files), and the best thing I can achieve is either names with accents
to be truncated in the database (skipping ~50000 people because of 'duplicate key
violations'), or giving a "funny" character that looks something like y with a
circumflex for an accented e and a y with a grave for an i with a grave - which
would help if one was willing to memorize all the alternatives and use them in
searches .... hahaha. And that's with collation being set to either utf8_generic_ci
or utf8_bin.

People in the #mysql channel on freenode didn't have any ideas, and quite frankly
I'm pretty fed up with the product and its community.

Funnily enough PostgreSQL on the same machine imported the same file w/o a
hick-up, albeit somewhat slower than MySQLs pathetic workings.

Anyway ... should anyone have any other ideas I'd be grateful to hear of them.

Cheers,
Tink

bathory · 02-01-2010, 05:08 PM

Have you tried to transform the .sql file from utf-8 to iso8859-1, or whatever encoding you have to use:

Code:

cat file.sql | iconv -f utf-8 -t iso8859-1 > file-iso8859-1.sql

Tinkster · 02-01-2010, 06:59 PM

Most certainly did ... and variations thereof. As I said - this has been
going for over 4 days now ... :/

carbonfiber · 02-02-2010, 03:54 AM

Quickie #1: set the collation to utf8_bin (default is utf8_general_ci for a utf8 character set)
Quickie #2: set the character set to 'binary'

Look up the difference between utf8_general_ci and utf8_bin to see why.

Tinkster · 02-02-2010, 04:12 AM

Quote:

Originally Posted by carbonfiber

Quickie #1: set the collation to utf8_bin (default is utf8_general_ci for a utf8 character set)
Quickie #2: set the character set to 'binary'

Look up the difference between utf8_general_ci and utf8_bin to see why.

[edit]
Ooops .. that just changed.
Tried #2 at this stage (which was #1 a few minutes ago)
[/edit]

Interesting suggestion - tried it. Same result. Instead of

Code:

ì

I get

Code:

ý

Cheers,
Tink

carbonfiber · 02-02-2010, 04:19 AM

Code:

mysql> create table tinkster (name varchar(12) character set utf8 collate utf8_bin unique);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into tinkster values ('Sergio'),('Sérgio'),('ì'),('ý');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from tinkster;
+---------+
| name    |
+---------+
| Sergio  |
| Sérgio  |
| ì       |
| ý       |
+---------+
4 rows in set (0.00 sec)

Tinkster · 02-02-2010, 04:25 AM

Quote:

Originally Posted by carbonfiber

Code:

mysql> create table tinkster (name varchar(12) character set utf8 collate utf8_bin unique);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into tinkster values ('Sergio'),('Sérgio'),('ì'),('ý');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from tinkster;
+---------+
| name    |
+---------+
| Sergio  |
| Sérgio  |
| ì       |
| ý       |
+---------+
4 rows in set (0.00 sec)

Yah, fine, good and well ... I can reproduce that. Now stick
those in a file, and import it.

[edit]
And I'm not being facetious - it's that I have ~ 1.8 Mio
rows that I'd like to import into the database, and no, I
won't be copy & pasting them into an interactive session.
[/edit]

Cheers,
Tink

Tinkster · 02-02-2010, 04:26 AM

Quote:

Originally Posted by carbonfiber

Quickie #1: set the collation to utf8_bin (default is utf8_general_ci for a utf8 character set)
Quickie #2: set the character set to 'binary'

Look up the difference between utf8_general_ci and utf8_bin to see why.

And #1 leads to the same result as #2.

Thanks, anyway.

Cheers,
Tink

Tinkster · 02-02-2010, 04:30 AM

Oh, and another update. MySQL 5.1.37 on WinXP has the same problem
that 5.0.84 on Slackware has. Importing accented characters from a
file appears (to me at least) impossible with MySQL, and it makes no
difference whether that's a delimited text file, or whether I wrap
the data into properly escaped insert statements.

Cheers,
Tink

carbonfiber · 02-02-2010, 04:39 AM

Code:

$ cat ~/DB 
Sergio
Sérgio
ì
ý

-------- -------- -------- --------

mysql> delete from tinkster;
Query OK, 4 rows affected (0.00 sec)

mysql> load data local infile '~/DB' into table tinkster;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tinkster;
+---------+
| name    |
+---------+
| Sergio  |
| Sérgio  |
| ì       |
| ý       |
+---------+
4 rows in set (0.00 sec)

mysql version: 5.1.42.

Tinkster · 02-02-2010, 04:44 AM

Quote:

Originally Posted by carbonfiber

Code:

$ cat ~/DB 
Sergio
Sérgio
ì
ý

-------- -------- -------- --------

mysql> delete from tinkster;
Query OK, 4 rows affected (0.00 sec)

mysql> load data local infile '~/DB' into table tinkster;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tinkster;
+---------+
| name    |
+---------+
| Sergio  |
| Sérgio  |
| ì       |
| ý       |
+---------+
4 rows in set (0.00 sec)

mysql version: 5.1.42.

Now that looks promising ... can you please post your
'locale' output and your my.cnf?

And what does 'file' say your DB file is?

Cheers,
Tink

carbonfiber · 02-02-2010, 04:50 AM

Sure.

Code:

$ locale
LANG=en_US.utf8
LC_CTYPE="en_US.utf8"
LC_NUMERIC="en_US.utf8"
LC_TIME="en_US.utf8"
LC_COLLATE=C
LC_MONETARY="en_US.utf8"
LC_MESSAGES="en_US.utf8"
LC_PAPER="en_US.utf8"
LC_NAME="en_US.utf8"
LC_ADDRESS="en_US.utf8"
LC_TELEPHONE="en_US.utf8"
LC_MEASUREMENT="en_US.utf8"
LC_IDENTIFICATION="en_US.utf8"
LC_ALL=

my.cnf is the 'default' as shipped by Arch Linux: http://repos.archlinux.org/wsvn/pack...ra-i686/my.cnf (I don't usually run MySQL on this machine, I installed it just to see if I could help you fix your problem - that's why I'm using the default configuration file)

Code:

$ file DB 
DB: UTF-8 Unicode text