MySQL Bugs: #68778: MySQL ODBC Driver 5.1 resulting junk Varchar values while using select query

Bug #68778	MySQL ODBC Driver 5.1 resulting junk Varchar values while using select query
Submitted:	26 Mar 2013 12:19	Modified:	3 Apr 2013 5:04
Reporter:	Ratheesh ratheesh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1	OS:	Windows (Windows 2008)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ODBC Driver resulting junk Varchar values

Description:
MySQL ODBC Driver 5.1 resulting junk Varchar values while using Select query

How to repeat:
I am using MySQL Server 5.5.20 and MySQL ODBC connector version 5.1. We have created database using collate latin1_genera_cs option. When using MySQL ODBC connector 5.1 I am able to insert values in to databases without issues. And when I try to use select query, the Varchar Field values are returned as junk chars. But we are able to get the select query values using 3.51 OBDC connector. Please do help me.

We always test our software products before releasing them and such simple case as SELECT from varchar column could not be overlooked.
I am sure your issue is the result of improper connection options settings.

In order to proceed we need to more specific information about the problem:

1. What is the exact three-number version of ODBC Driver 5.1 you are using?
2. What is the client application?
3. What connection options you are using? 
4. Provide your connection string if applicable
5. Can you make a short C/C++/VB test case?

1) What is the exact three-number version of ODBC Driver 5.1 you are using? 
	ODBC Version  :: 5.1.12 (5.01.12.00)

2) What is the client application?
	LibODBC 

3) What connection options you are using?  
	We are not using SSL option. We are using the default connection options.

4) Provide your connection string if applicable
	We are using SQLConnect() function using dsn,username and password to connect a driver and a data source.
	http://msdn.microsoft.com/en-us/library/windows/desktop/ms711810%28v=vs.85%29.aspx

5) Can you make a short C/C++/VB test case?

	Test Case Procedure is as follows.
	
	1) Create database testdb collate latin1_general_cs;
	
	2) CREATE TABLE test_details (ID bigint(20) NOT NULL,  NAME varchar(255) COLLATE latin1_general_cs NOT NULL,  OWNER_NAME varchar(255) COLLATE		latin1_general_cs NOT NULL,  OWNER_ID bigint(20) NOT NULL,  CREATED_TIME bigint(20) NOT NULL,  LOGOUT_TIME bigint(20) NOT NULL,  TESTER_NAME varchar(255) COLLATE latin1_general_cs NOT NULL,  NO_LOGINS bigint(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ;
	
	3) insert into test_details values(1,'tester','tester',1,1364294586,1364294693,'26_03_2013_1613',5);
	
	4) After that we used C++ program to retrieve the value. We got junk VARCHAR values in output. Sample out put as follows.

		TESTER_NAME ::::     Ç£Gá⌐&└Z♥╘
		NAME ::::     ÇT
		OWNER_NAME ::::   Ç☻
		CREATED_TIME ::: 1364294586
		tester name ::::     Ç£Gá⌐&└Z♥╘
		NAME ::::     ÇTJ
		OWNER_NAME ::::   Ç☻
		CREATED_TIME ::: 1364294586
		TESTER_NAME ::::     Ç£Gá⌐&└Z♥╘
		NAME ::::     ÇTJ
		OWNER_NAME ::::   Ç☻
		CREATED_TIME ::: 1364294506

Hi Ratheesh,

Thanks for your answers.
Unfortunately, these answers do not have all the specific details I requested.

For question #2 you said the client application is LibODBC.
As far as I know libodbc is the driver manager library from UnixODBC, which can also be used in Windows. In any case it is not the client. However, I can guess that it is a C++ application written by you.

In question #3 I wanted to ask not only about SSL, but about any DSN settings such as character set, cursor options, metadata, results, etc (the driver GUI dialog has about 30 different options). If you set incorrect character set for the connection your data might be corrupted.

What do you see when trying to select from the test table in mysql command line client? Does the varchar column look ok?

And the most important (#5), I wanted to see a simplified C++ test program that shows how the ODBC functions are called and how exactly it leads to the data corruption. The general description you gave is not enough to repeat the issue.

Sometimes a simple test case is not easy to make.
Therefore, I made a C++ test program for you to try (bug68778.cpp).
Please compile and run it in the OS the command prompt:

bug68778.exe DSN username password

For me it displayed the following output:

Connecting..................[DONE]
Creating database...........[DONE]
Creating table..............[DONE]
Inserting data..............[DONE]
Selecting data..............[DONE]
RESULTS:
-------------------- row 1 -------------------
ID : 1
NAME : tester
OWNER_NAME : tester
OWNER_ID : 1
CREATED_TIME : 1364294586
LOGOUT_TIME : 1364294693
TESTER_NAME : 26_03_2013_1613
NO_LOGINS : 5

C++ test case

Attachment: bug68778.cpp (text/plain), 4.06 KiB.

Thanks for your reply. We tried the program that you have sent. The program is working fine. We noticed that the issue is due to the LibODBC++ wrapper that we are using currently. Now we have upgraded our LibODBC++ version to latest, the junk chars issues are resolved in this version.