Bug #18763 ByteArray objects cannot be converted to strings
Submitted: 3 Apr 2006 22:40 Modified: 27 Dec 2007 16:14
Reporter: Dale Marinello Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.21-BK, 4.1.9-nt OS:Linux (Linux, Windows XP)
Assigned to: Assigned Account CPU Architecture:Any

[3 Apr 2006 22:40] Dale Marinello
When using the jdbc:mysql url and com.mysql.jdbc.Driver driver class in ColdFusion odbc setup, the following query and output produces the above error.

How to repeat:
GameDate is a DateTime Field in the SchGames Table

<cfquery name="months" datasource="#request.dns#">
select distinct date_format(GameDate,'%b %Y') as yrmon from Schgames


Suggested fix:
Define odbc on the server and use socket odbc in ColdFusion.
[30 May 2006 19:13] Mark Matthews
The server is returning the incorrect metadata in this case. Debugging it with the JDBC driver, it's telling the client that date_format should be a BINARY _type_.

A workaround might be to use CAST to get the server to return it as a string:

select distinct CAST(date_format(GameDate,'%b %Y') AS CHAR)  as yrmon from Schgames
[31 May 2006 20:43] Valeriy Kravchuk
Verified on 4.1.21-BK build on Linux:

mysql> select distinct date_format(now(),'%b %Y') as yrmon from t2;
| yrmon    |
| May 2006 |
1 row in set (0.00 sec)

mysql> create table tt2 as select distinct date_format(now(),'%b %Y') as yrmon from t2;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc tt2;
| Field | Type      | Null | Key | Default | Extra |
| yrmon | binary(8) | YES  |     | NULL    |       |
1 row in set (0.00 sec)

So, it is a server bug - wrong data type of column in results.
[27 Dec 2007 16:14] Alexey Kopytov
Fixed by the changeset for bug #22646. Not reproducible on fresh 4.1 and 5.0 trees.