Bug #9684 ResultSet.getString() returns a byte array reference for TEXT BINARY
Submitted: 6 Apr 2005 16:34 Modified: 18 Aug 2005 12:10
Reporter: Hontvari Jozsef Levente Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version:3.1.7 OS:
Assigned to: Mark Matthews CPU Architecture:Any

[6 Apr 2005 16:34] Hontvari Jozsef Levente
ResultSet.getString(n) returns something like "[B@b29c9d" instead of the actual text for TEXT columns which were defined with binary collation.  

The problem doesn't happen if the collation is not a binary collation.

How to repeat:
create a column e.g. 
`sourceText` text binary
in a table where the default character set is utf 8
`sourceText` text character set utf8 collate utf8_bin

insert a string "gamma" into the column

return the string with a query

Suggested fix:
getString() should convert the raw byte content of TEXT columns to characters.
[6 Apr 2005 18:26] Mark Matthews
The BINARY collation/charset is not a character set, so there is no way to convert it directly to a string without loss. If you want to compare case-sensitively, then you really need to use a _cs variant of a character set for the TEXT column.

Have you tried the latest nightly build? It does make this work in the cases where it can be made to work.
[6 Apr 2005 19:34] Hontvari Jozsef Levente
I tried now the 3.1 nightly build (20050406), but it doesn't work either. On the other hand the the older production version 3.0.16 DOES work.

Did you notice that the test case I wrote uses UTF-8 charset? MySql doc is very far from being unambiquous regarding the meaning of 'xy BINARY' types, but at least it states clearly that TEXT columns always have a charset (in contrast to BLOB for example:  "TEXT columns are treated as non-binary strings (character strings). ... TEXT columns have a character set"). The test case works both with TEXT BINARY and TEXT CHARACTER SET utf8 COLLATE utf8_bin. (This two is equivalent according to the mysql doc, if the default table charset is utf-8, and they indeed behave similarly).
[6 Apr 2005 19:40] Hontvari Jozsef Levente
Regarding the _cs collation (you wrote charset, but I assume you mean collation), I cannot see _cs collation for utf-8 (in the result list of the "show collation" command). There are many _ci collation, and a single utf8_bin collation, which - I think - must be the utf-8 case sensitive collation. Please correct me if I am wrong.
[25 Apr 2005 9:29] Jesper Matthiesen
How is this not a bug? It works as one would think (getString returns a string) for a varchar column, but not for a text column (getString returns a byte array) .. This can only be a bug..
Furthermore, the collation of a column should NOT affect the data type returned, only the data itself.
[26 Apr 2005 17:03] Sergei Golubchik
Still, looks like a bug.
[26 Apr 2005 17:12] Mark Matthews
I can't repeat this with 3.1.8 with the following testcase when connected to MySQL-4.1.11.

public void testBug9684() throws Exception {
		String tableName = "testBug9684";
		try {
			createTable(tableName, "(sourceText text character set utf8 collate utf8_bin)");
			this.stmt.executeUpdate("INSERT INTO " + tableName + " VALUES ('abc')");
			this.rs = this.stmt.executeQuery("SELECT sourceText FROM " + tableName);
			assertEquals("java.lang.String", this.rs.getString(1).getClass().getName());
		} finally {
			if (this.rs != null) {
				this.rs = null;

As you see, it actually tests if the value returned is of type java.lang.String. What does your query look like exactly?
[26 Apr 2005 21:47] Hontvari Jozsef Levente
getString did return a String instance but the _content_ of that String was a dump of a byte array, e.g. "[B@b29c9d". 

In order to test this bug, the assertion in your test case must be this: 
assertEquals("abc", this.rs.getString(1));
[26 Apr 2005 22:02] Mark Matthews
The driver returns 'abc' in this case. 

Can you please tell us exactly what version of the server you are using, and please write your entire query into the bug report. We can't repeat your issue here with the combination of the latest version of MySQL and the JDBC driver.
[26 Apr 2005 22:35] Hontvari Jozsef Levente
MySql server version is 4.1.7, see below. I can reproduce the issue with mysql connector 3.1.8, while 3.0.16 works well. The query is "SELECT * FROM x".

mysql> status
mysql  Ver 14.7 Distrib 4.1.7, for Win95/Win98 (i32)

Connection id:          8
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         4.1.7-nt-log
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    latin1
[26 Apr 2005 22:49] Mark Matthews
MySQL-4.1.7 is quite old. Is there any way you could test with a more recent version, like 4.1.11?
[27 Apr 2005 9:03] Jesper Matthiesen
I get the same using 3.1.7/3.1.8 on mysql 4.1.9..
Looks like the driver calls toString() on the byte array (which evaluates to a byte array reference) and then returns that... [Bxxxxxx is indeed a reference of type byte array...
[27 Apr 2005 15:35] Jesper Matthiesen
We have tried again..

with a simple "select comment from xxx", where "comment" is a text column with charset utf8 and collation utf8_bin, getString() returns e.g. "[B@12a34f"

if the collation of the column is changed to utf8_general_ci, getString() returns the correct data....

the data in the database show up fine using the mysql command line client (on linux)

we are using mysql 4.1.11 on linux and mysql-connector 3.1.8 on windows xp..
[2 May 2005 14:46] Hontvari Jozsef Levente
I can also reproduce the problem on 4.1.11-nt-log after upgrading to that MySQL version. The old connector still works well, the new still doesn't.
[10 May 2005 10:57] Jim Janson
I just upgraded the driver to 3.1.8 and the db to 4.1.11 on Windows XP and I'm seeing this problem.  In my case, the column is a BLOB.  If I drop back to 3.0.14 of the driver, the problem goes away.

If you need some additional info to help reproduce this, please let me know.
[10 May 2005 12:40] Mark Matthews
BLOBs don't have character sets, so they can't be converted to strings, so what you report (i.e. a BLOB column where .getString() doesn't work) isn't a bug.
[10 May 2005 12:44] Jim Janson
Ok.  You don't have to call it a bug but it is certainly a change in behavior from previous versions of the driver.
[18 Aug 2005 9:41] [ name withheld ]

I had almost the same problem. I have a BLOB field, in which I store strings of variable size. I don't use character sets, because they only a limited set is allowed. (Basically, [a-z],[A-Z],[0-9],['/','?']

When retrieving it, using result.getString(4), I got something like a reference instead of a String! Strangely enough, in some other machines where the service was deploy, it would work.

After some search around, I found it was due to different connectors usage: mysql-connector 3.0.15 would work properly, 3.1.8 would not. I will check now with the latest-greatest production version (3.1.10).
[18 Aug 2005 9:59] [ name withheld ]
Works by changing BLOB to a TEXT column.

behaviour in connector 3.1.x => is different.
[18 Aug 2005 12:10] Mark Matthews
This is a documented change: