Bug #29402 field type charset 63 problem
Submitted: 27 Jun 2007 21:53 Modified: 8 Jul 2008 12:43
Reporter: avi weiss Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.16,3.51..25 OS:Microsoft Windows (XP / 2003)
Assigned to: Bogdan Degtyariov
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[27 Jun 2007 21:53] avi weiss
using 5.0.37 mysql server, 2.51.16 mysql ODBC connector on both a windows XP and windows 2003 box.

using ADO to retrieve a recordset from the following query: select concat('a', 1).   on win XP, field type is 200 (asci string). on win 2003, field type is204 (binary string).

dont believe there should be a discrepancy between these. for reference linux also returns type 204.

How to repeat:
DBOpenRS rs,"select concat(1,'a') as test"

rs.fields(0).name  => 'test'
rs.fields(0).type  => 200 / 204
rs(0) => 1a
[27 Jun 2007 22:52] Miguel Solorzano
Thank you for the bug report. Could you please do a test using the command
client tool started with option -T like below and verify if there is
discrepancy?. Thanks in advance.

c:\mysql-5.0.27-win32\bin>mysql -uroot -T -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.41-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select concat(1,'a') as test;
Field   1:  `test`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     2
Max_length: 2
Decimals:   31
Flags:      BINARY

| test |
| 1a   |
1 row in set (0.08 sec)

[28 Jun 2007 1:20] avi weiss
Hi Miguel;

so I ran the cmd client tool on both platforms as requested, and got this result on both platforms:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.41-community-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select concat(1,'a');
Field   1:  `concat(1,'a')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     2
Max_length: 2
Decimals:   31

| concat(1,'a') |
| 1a            |
1 row in set (0.14 sec)

so apparently, the DB is returning the same type on both platforms. Yet when I query the field type through ADO syntaxt, I get type '200' on win xp and type '204' on win2003. So does this make it a connector issue (ODBC connector 3.51.16)?

[28 Jun 2007 7:21] Sveta Smirnova
Category has been changed.
[17 Jul 2007 17:42] Tonci Grgin
Hello Avi.

First, we must clear things up regarding c/ODBC v2. It is abandoned and there's no need to take it in account.

Now, I tested with
 - MySQL server 5.0.44BK on WinXP Pro SP2 host
 - MyODBC 3.51.17
 - WinXP Pro SP2 fully patched
 - Win2k (sorry, no 2k3...), same server running on XP machine, same MyODBC 3.51.17, rarely updated
 - Test case attached

Both results are the same, adVarChar (200). My guess is that this is MS problem. Can you compare library versions with MDAC and inform us of result?
[17 Jul 2007 17:42] Tonci Grgin
Test case

Attachment: bug29402.vbs (application/octet-stream, text), 1.64 KiB.

[17 Jul 2007 18:55] Tonci Grgin
Hi Avi. I got my colleague to test 3.51.17 on Win2k3 32bit and the result is the same as on XP and w2k.
[22 Apr 2008 19:40] Bogdan Degtyariov
The bug can appear when using the string functions such as CONCAT(VARCHAR, INTEGER). In case of mixed parameters the returned charset is 63 (binary).

The solution proposed by developers is to analyze MYSQL_FIELD->org_table which would be empty for the functions results.
[30 Apr 2008 15:55] Bogdan Degtyariov
patch and test case

Attachment: patch29402.diff (application/octet-stream, text), 3.80 KiB.

[20 May 2008 18:14] Bogdan Degtyariov
patch version 2

Attachment: patch29402v2.diff (application/octet-stream, text), 8.67 KiB.

[25 Jun 2008 20:09] Fang MX

I have the same bug :

OS server windows XP,win2k3
OS client windows XP,win2k3

Mysql server version 5.0.19
MySQL ODBC Driver 3.51.12 and 3.51.25

I run a short test :

sub test(strConnectionString)
	dim db
	set db=createObject("ADODB.connection")
	set rc=db.execute("SELECT CONCAT(1,'A') AS TEST")
	'Display result
	wscript.echo rc("TEST")	
	'do some cleaning
	set rc=nothing
	set db=Nothing
end sub

First with ODBC Driver version 3.51.12. Result type 204 : binary

Second with ODBC Driver version 3.51.25. Result type 200 : 1A

same behaviour with win 2k3

Note:When i replace test query with this "SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w')" same behaviour

Odbc Driver 3.51.12 result type 200 ok : 22 22 10 10:23:00 PM 22:23:00 00 6
Odbc Driver 3.51.25 result type 204 ok : binary
[26 Jun 2008 19:44] Ben Krug
I have not verified, but this has also been reported as affecting ODBC 5.1.
[27 Jun 2008 1:12] Bogdan Degtyariov
patch v3

Attachment: patch29402v3.diff (application/octet-stream, text), 7.00 KiB.

[30 Jun 2008 17:36] Jim Winstead
change the help text for the option to "Always handle binary function results as character data"
[8 Jul 2008 4:49] Bogdan Degtyariov
The patch has been committed to the source repository.
New connection option will appear in the next release of MyODBC 3.51.26.
[8 Jul 2008 10:37] Tony Bedford
An entry has been added to the 3.51.26 Changelog:

There is a new connection option, FLAG_NO_BINARY_RESULT. When set this option disables charset 63 for columns with an empty org_table.

The new option also needs to be documented in main docs.
[8 Jul 2008 12:43] Tony Bedford
New option added to option table in Connector/ODBC configuration section of main docs.
[31 Jul 2008 16:34] Mary Miller-Clark
I just installed 3.51.26 to run with my application.  I am still having same problem, so I tried setting the new option to see if that helped and got same results after setting option.  Any comments?
[31 Jul 2008 18:40] Tonci Grgin
Mary, you are right. Now comes strange part; going through predefined DSN everything works! Please, util I figure out what's gone wrong, instead of
Const cConnectionString= "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=root;pwd=***;PORT=****;OPTION=3+268435456"
use predefined DSN with new option checked like this:
Const cConnectionString= "DSN=351-on-5068"
[31 Jul 2008 18:51] Tonci Grgin
As Jess kindly pointed out to me:
"<jbalint>	theres no calculator in the driver"

So, ;PORT=****;OPTION=268435459" works (3+268435456) and problem's gone.
[1 Aug 2008 16:37] Mary Miller-Clark
Ok, now I'm confused.  here is our current connection string:
'strConn = "driver={MySQL ODBC 3.51 Driver};uid=;pwd=;database=arkivio_sss"
'strConn = DRIVER={ArkMySQL};SERVER=localhost;DATABASE=arkivio_sss;USER=root;PASSWORD=;OPTION=35;"

what are you suggesting that I use?  wouldn't I just add 268435456 to 35? and set option to 268435491?
[1 Aug 2008 18:48] Tonci Grgin

Thanks for your interest in MySQL.
[1 Aug 2008 20:54] Mary Miller-Clark
One more question: For interim testing purposes, can I just set that option in the registry?  If so, where? would I do it Here=> 
Windows Registry Editor Version 5.00

Windows Registry Editor Version 5.00
[2 Aug 2008 7:39] Tonci Grgin
Mary, definitely "[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\arkivio_sss]". You just need to search for your DSN name in registry as this is per-DSN (connection) option.

Thanks for your interest in MySQL.
[6 Aug 2008 17:45] Mary Miller-Clark
I did a new build of my code using MySQL 5.0.66a with ODBC 3.51.26 and set the new option in the registry.  My problem was not solved.  I rebuilt my code with MySQL 5.0.66a and  ODBC 3.51.21 and my date-time problem was resolved.  So apparently there is still a problem in 3.51.26.
[7 Aug 2008 17:22] Mary Miller-Clark
An update, I tried a new build and set the option in the vbs file, and that worked.  On my previous build, I set the option in the registry, which did not solve the problem.  Just wanted to let you know that everything is working as it should now, with option set in vbs file where connection is made.