characters with accents in MySQL client?
Posted by: João Paulo Magalhães
Date: March 12, 2008 07:16PM
Date: March 12, 2008 07:16PM
Hi all
I am not managing to insert/view characters with accents from within the mysql client program (v5.0.51a3 debian - same version as the server program):
-When inserting, it ignores the string from the first accented character onwards (with warning 1366: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD).
-When displaying accented characters (which I can insert elsewhere with php) it displays bogus chars.
-Despite this, these characters display nicely in html through php.
I have set everything to utf8, as you can see below. Being new to sql, I have never had any contact with other versions, so I would like to hear from someone who has utf8 working properly. I suspect that the client is handing out the values in non-UTF8 encoding (because of the warning) - but then all the info says it's using utf8.
More details below.
all the best,
João
===========================================================
Consider the mysql session:
===========================================================
Issuing the same INSERT code from php:
The result in mysql:
But the latter 4 rows display nicely in HTML through php (here with copy/paste):
1 client
2 client-aaaaaaaaaa
3 client-
4 client-
5 client
6 client-aaaaaaaaaaátéúãlllooo
7 client-ááááááaaa
8 client-€
===========================================================
Here's info on the db/connection:
I am not managing to insert/view characters with accents from within the mysql client program (v5.0.51a3 debian - same version as the server program):
-When inserting, it ignores the string from the first accented character onwards (with warning 1366: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD).
-When displaying accented characters (which I can insert elsewhere with php) it displays bogus chars.
-Despite this, these characters display nicely in html through php.
I have set everything to utf8, as you can see below. Being new to sql, I have never had any contact with other versions, so I would like to hear from someone who has utf8 working properly. I suspect that the client is handing out the values in non-UTF8 encoding (because of the warning) - but then all the info says it's using utf8.
More details below.
all the best,
João
===========================================================
Consider the mysql session:
mysql> INSERT INTO tecidos (nome) values ('client'),('client-aaaaaaaaaaátéúãlllooo'),('client-ááááááaaa'),('client-€'); Query OK, 4 rows affected, 3 warnings (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> show warnings; +---------+------+----------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\xE1t\xE9\xFA\xE3l...' for column 'nome' at row 2 | | Warning | 1366 | Incorrect string value: '\xE1\xE1\xE1\xE1\xE1\xE1...' for column 'nome' at row 3 | | Warning | 1366 | Incorrect string value: '\xA4' for column 'nome' at row 4 | +---------+------+----------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select id,nome from tecidos; +----+------------------------------------------+ | id | nome | +----+------------------------------------------+ | 1 | client | | 2 | client-aaaaaaaaaa | | 3 | client- | | 4 | client- | +----+------------------------------------------+ 4 rows in set (0.00 sec)
===========================================================
Issuing the same INSERT code from php:
$db->query("INSERT INTO tecidos (nome) values ('client'),('client-aaaaaaaaaaátéúãlllooo'),('client-ááááááaaa'),('client-€')");
The result in mysql:
mysql> select id,nome from tecidos; +----+------------------------------------------+ | id | nome | +----+------------------------------------------+ | 1 | client | | 2 | client-aaaaaaaaaa | | 3 | client- | | 4 | client- | | 5 | client | | 6 | client-aaaaaaaaaaátéúãlllooo | | 7 | client-ááááááaaa | | 8 | client-â⬠| +----+------------------------------------------+ 8 rows in set (0.00 sec)
But the latter 4 rows display nicely in HTML through php (here with copy/paste):
1 client
2 client-aaaaaaaaaa
3 client-
4 client-
5 client
6 client-aaaaaaaaaaátéúãlllooo
7 client-ááááááaaa
8 client-€
===========================================================
Here's info on the db/connection:
mysql> status; -------------- mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Connection id: 571 Current database: ***** Current user: ***@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.51a-3 (Debian) Protocol version: 10 Connection: Localhost via UNIX socket Insert id: 1 Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 1 hour 46 min 8 sec Threads: 3 Questions: 3253 Slow queries: 0 Opens: 219 Flush tables: 1 Open tables: 64 Queries per second avg: 0.511 -------------- mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> show variables like 'coll%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) mysql> show full columns from tecidos; +----------+-------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +----------+-------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | id | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | nome | varchar(255) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | +----------+-------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.