MySQL Bugs: #29402: field type charset 63 problem

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:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.16,3.51..25	OS:	Windows (XP / 2003)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
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

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)

mysql>

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
Flags:      NOT_NULL BINARY

+---------------+
| 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)?

-avi

Category has been changed.

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?

Test case

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

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.

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.

patch and test case

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

patch version 2

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

Hi,

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")
	db.connectionString=strConnectionString
	db.open	
	set rc=db.execute("SELECT CONCAT(1,'A') AS TEST")
	
	'Display result
	wscript.echo rc("TEST")	
	
	'do some cleaning
	rc.close
	set rc=nothing
	db.close	
	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

I have not verified, but this has also been reported as affecting ODBC 5.1.

patch v3

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

change the help text for the option to "Always handle binary function results as character data"

The patch has been committed to the source repository.
New connection option will appear in the next release of MyODBC 3.51.26.

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.

New option added to option table in Connector/ODBC configuration section of main docs.

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?

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"

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.

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?

Exactly!

Thanks for your interest in MySQL.

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
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\arkivio_sss]
"Driver"="C:\\WINDOWS\\system32\\ArkMyOdbc3.dll"
"Description"=""
"Database"="arkivio_sss"
"Server"="----"
"User"="root"
"Password"=""
"Port"=""
"Option"="0"
"Stmt

OR HERE =>
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ArkMySQL]
@=""
"Driver"="C:\\WINDOWS\\system32\\ArkMyOdbc3.dll"
"Setup"="C:\\WINDOWS\\system32\\ArkMyOdbc3.dll"
"UsageCount"=dword:0000000c
"APILevel"="2"
"ConnectFunctions"="YYN"
"DriverODBCVer"="03.51"
"FileUsage"="0"
"FileExtns"="*.txt"
"SQLLevel"="1"
"CPTimeout"="60"
"DSNHelpFile"="c:\\WINNT\\help\\my3dsn.hlp"

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.

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.

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.