Bug #55344 Invalid error message encoding used for server messages
Submitted: 18 Jul 2010 12:40 Modified: 21 Dec 2012 7:22
Reporter: Serge Rieder Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.13 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[18 Jul 2010 12:40] Serge Rieder
Description:
Whenever server error message contains any non-latin characters in it (e.g. non-English table name, column name, etc) then it's incorrectly formatted on client-side.

Server language is "english".
Connection charset is "UTF-8".
This problem present in all latest Connector/J releases (at least starting from 5.1.5 version)
It is reproductible on any MySQL 5.xx server (including latest 5.1.48)

How to repeat:
Execute any sql statements which causes error and which contains non-latin entity name. For example:
---
SELECT * FROM таблица
---
Table "таблица" is actually doesn't exists, so I expect to see an error message.
But I see incorrectly converted message like this:
---
Table 'xxx.таблица' doesn't exist
---
I assume that string "таблица" is a result of convertion of UTF-8 byte array into Cp1252 string.

Suggested fix:
I've check source code and I see that field errorMessageEncoding (ConnectionImpl class) is used to convert server-side message to string on client side.
Value of this field is a constant "Cp1252" or it's taken from server language to charset mapping. For english it's always "latin1". 
Actually, as I see, server-side errors are sent in connection charset and doesn't correlate with server variable "language".
So, the solution is to use connection charset for error messages formatting.
[20 Jul 2010 8:54] Tonci Grgin
Hi Serge and thanks for your report.

I'd be most interested in seeing the output of SHOW VARIABLES LIKE "%char%"; Next, I'd like to see a full test case attached, along with connection string and such. My bet is that some sort of automatic conversion is happening since you use CP1251 table name. What happens if you replace "таблица" with "tablica"?
[20 Jul 2010 10:08] Serge Rieder
Test case for error messages encoding

Attachment: MySQLErrorsTest.java (text/x-java), 1.29 KiB.

[20 Jul 2010 10:11] Serge Rieder
I've attached test case. It shows next result on my local host:
---
SHOW VARIABLES
character_set_client=utf8
character_set_connection=utf8
character_set_database=utf8
character_set_filesystem=binary
character_set_results=
character_set_server=utf8
character_set_system=utf8
character_sets_dir=D:\Programs\MySQL\MySQL Server 5.0\share\charsets\
SELECT * from tablica
Table 'sa.tablica' doesn't exist
SELECT * from таблица
Table 'sa.?‚?°?±?»???†?°' doesn't exist
---

Note 1: I do not use CP1251 charset anywhere, only utf-8.
Note 2: Results do not depends on server or database charset, e.g. when character_set_database=latin1 then results are the same.
[19 Dec 2012 17:13] Haixing Hu
Yes, I hava found the same problem.
It last for almost two years without a fix.
Anybody notice that?
[19 Dec 2012 17:27] Haixing Hu
I'm sorry. I found that this bug seems to be fix at v5.1.22

Here is the dependency in my pom.xml:

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.22</version>
    </dependency>
[21 Dec 2012 7:18] Alexander Soklakov
Hi Haixing,

Thanks for refresh of this bug report.

To be precise it was fixed in 5.1.21 (Bug#64205, Connected through Connector/J 5.1 to MySQL 5.5, the error message is garbled). That patch fixed encoding of messages sent back from server. Just don't forget to hint server about encoding of request. Look at the difference:

1. You don't tell about your encoding:

	Properties props = new Properties();
	Connection c = getConnectionWithProps(props);
	Statement s = c.createStatement();
	s.executeQuery("SELECT * FROM `таблица`");

server log:

	19 Query	/* mysql-connector-java-5.1.23-SNAPSHOT ( Revision: alexander.soklakov@oracle.com-20121121065345-c25kui96rwyjlnfm ) */SELECT @@session.auto_increment_increment
	19 Query	SHOW COLLATION
	19 Query	SET NAMES latin1
	19 Query	SET character_set_results = NULL
	19 Query	SET autocommit=1
	19 Query	SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
	19 Query	SELECT * FROM `???????`

exception:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'test.???????' doesn't exist
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1050)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4113)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4045)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2496)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2657)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2730)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2680)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)
	...

2. You tell about your encoding:
	Properties props = new Properties();
	props.setProperty("characterEncoding", "UTF-8");

	Connection c = getConnectionWithProps(props);
	Statement s = c.createStatement();
	s.executeQuery("SELECT * FROM `таблица`");

server log:

	21 Query	/* mysql-connector-java-5.1.23-SNAPSHOT ( Revision: alexander.soklakov@oracle.com-20121121065345-c25kui96rwyjlnfm ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
	21 Query	/* mysql-connector-java-5.1.23-SNAPSHOT ( Revision: alexander.soklakov@oracle.com-20121121065345-c25kui96rwyjlnfm ) */SELECT @@session.auto_increment_increment
	21 Query	SHOW COLLATION
	21 Query	SET NAMES utf8
	21 Query	SET character_set_results = NULL
	21 Query	SET autocommit=1
	21 Query	SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
	21 Query	SELECT * FROM `таблица`

exception:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'test.таблица' doesn't exist
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1050)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4113)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4045)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2496)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2657)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2730)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2680)
	at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)
	...
[21 Dec 2012 7:22] Alexander Soklakov
This is a duplicate of Bug#64205