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: | Windows (XP / 2003) |
Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
[27 Jun 2007 21:53]
avi weiss
[27 Jun 2007 22:52]
MySQL Verification Team
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>
[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 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
[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
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
[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
Exactly! 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 [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"
[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.