characters with accents in MySQL client?
Posted by: João Paulo Magalhães
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:

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 |         |
+----------+-------------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+


Options: ReplyQuote


Subject
Views
Written By
Posted
characters with accents in MySQL client?
24062
March 12, 2008 07:16PM


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.