MySQL Bugs: #16024: query returns bad value if result is varchar and contains certain characters.

Bug #16024	query returns bad value if result is varchar and contains certain characters.
Submitted:	27 Dec 2005 23:26	Modified:	31 Mar 2014 10:38
Reporter:	Vilmos Kozma	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.16 & Connector/J - 3.1.12	OS:	Windows (Windows XP)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
An sql select returns bad value if the result is varchar and the result value contains certain characters. In the example below (How to repeat), I insert a string into a varchar type field of a table. The string contains every possible character which can be represented in one byte. When i read it back some certain characters change. 

This issue makes the asBinary function unusable since it returns with varchar (so it has the same problem) and represents binary data (so possibly contains those certain bytes).

As an alternative we can use the asText function and MySql's own format.

In java, using the asText function with huge amount of spatial data would significantly decrease performance.

Using the original format would be faster, but I didn't find any description of it.
Are there any?

How to repeat:
<javacode>
		...
		Connection con = getConnection();
		byte[] ba = new byte[256];
		for (int i = 0; i < 256; i++) { 
			//filling the array with values ranged from -128 to 127
			ba[i] = (byte)(i-128);
		}
		String insertValue = new String(ba);
		DBMan.update(con, "drop table if exists foo");
		DBMan.update(con, "create table foo(" +
							"id int, " +
							"textField varchar(256))");
		DBMan.update(con, "insert into foo" +
				"(id, textField) values(1, ?)", insertValue);
		String foo = DBMan.executeQueryForString(con, 
							"select textfield from foo " +
											"where id=1");
		byte[] result = foo.getBytes();
		...
</javacode>

At this point the result array has wrong value at positions where value shoud be:
-127, -125, -120, -112, -104. Instead, they are always 63.

Thank you for a problem report. Please, inform about the exact version of MySQL server used.

The exact version is 5.0.16. I installed it from mysql-5.0.16-win32.zip.

Please, send the 'SHOW CREATE TABLE foo' results (I need DEFAULT CHARSET =  ... clause), and the results of  

show variables like 'collation%' 

and 

show wariables like 'character%' 

from your Java client.

SHOW CREATE TABLE foo

CREATE TABLE `foo` (
  `id` int(11) default NULL,
  `textField` varchar(256) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
---------------
show variables like 'collation%'

collation_connection	utf8_general_ci
collation_database	utf8_general_ci
collation_server	utf8_general_ci
---------------
show variables like 'character%'

Variable_name	Value
character_set_client	utf8
character_set_connection	utf8
character_set_database	utf8
character_set_results	
character_set_server	utf8
character_set_system	utf8
character_sets_dir	C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\

I got the same problem. I save the character "Ž" (code 0x8e cp1252) and I get back "?" from the column BUSINESSSTREET.

privatecontact  CREATE TABLE `privatecontact` (
...                                                                                                             `BUSINESSSTREET` varchar(83) default NULL,                                                                                                        ...                                                                                                                                      
) ENGINE=InnoDB DEFAULT CHARSET=latin1

collation_connection	latin1_swedish_ci
collation_database	latin1_swedish_ci
collation_server	latin1_swedish_ci

Connection with Connector/j 3.1.12 gives the following output in the MySQL log file:
060310 15:10:49	      3 Connect     AXPDBA@localhost on AXPDB
		      3 Query       SET NAMES latin1
		      3 Query       SET character_set_results = NULL
		      3 Query       SHOW VARIABLES
		      3 Query       SHOW COLLATION
		      3 Query       SET autocommit=1
060310 15:10:50	      3 Query       SET autocommit=1

I don't have this problem if I save this character not using the connector/j but a simple MySQL client.

The connector/j seems to have problem to convert some characters from/to java unicode to/from MySQL server character set (latin1)

Please try a latest nightly snapshot from http://downloads.mysql.com/snapshots.php#connector-j as we now map latin1 to cp1252 when connected to MySQL-4.1.0 or newer, which should solve your problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.