Bug #50173 presence of NULL character (ASCII 00) in a Varchar/text result in trancation
Submitted: 8 Jan 2010 9:18 Modified: 2 Feb 2010 11:13
Reporter: uma rengasamy Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.1.40sp1-enterprise-gpl-advanced-log OS:Linux (REDHAT 5.2)
Assigned to: CPU Architecture:Any

[8 Jan 2010 9:18] uma rengasamy
Description:
We have one more issue with MYSQL described below, if you have any fix for it or any work around then please help.

Issue:
We have a problem in the migrated database layer - most probably it has to do with the ODBC driver of MySQL. It treats any string as a Char-string terminated by \0. The application may have put a "low-value" to any alphanumeric field or a 0 in any COMP field that comes in the middle of a data structure and was writing to a file without any issues. However, when the same stream of characters are being written to the MySQL db layer to CHAR / VARCHAR / Text columns of a table, the string is getting truncated at the first occurrence of a \0 byte (i.e., "low-value" or binary 0), so rest of the data, if present, is not being written at all.

Example : 
CHAR data string to be inserted "ABC\0XYZ" and after insertion the value is "ABC" and rest got truncated because of the presence of NULL character at the middle of the string.
Request your help urgently.

In summary we need to know, whether it is possible in MySQL to store data with embedded ASCII 0 characters in them into TEXT or VARCHAR columns. 

We are migrating data from COBOL files to RDBMS. In COBOL we can have such data due to 2 primary reasons 

1. COBOL data items declared as COMP (binary) fields that have a value

2. COBOL data/group items have the COBOL LOW-VALUE (x'00')

looks like the problem is with the ODBC driver.. 

If I try adding it through MYSQL query browser, and find that following queries working.

But same thing you want to do it through application using the ODBC driver.. two possible things happening based on the type of query.. if the ‘value is hard coded rather than putting it in a host variable its doing fine, but as soon as the query is having the host variable which has NULL char, its getting truncated that that position 

Attaching the screenshots. Not sure if that will help to pin point the problem. 

Regards,

Abhishek

How to repeat:
We tried with a COBOL application running in Microfocus Linux and Widows environment, but my guess is it is reproducable in any environment.
write a cobol application accessing a table from MYSQL the 1st query using the host variable will result in "AB" at the employee column rathan than "AB\0123" but the 2nd query will result in entire string being added inspite of having a NULL char in the string, need to fix the persig problem fr 1st query using host variable:

MYSQL table:

CREATE TABLE  `test1`.`test` (
  `no` char(6) NOT NULL,
  `emp` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

COBOL program:

      $set sql
       program-id. DemoAdapter.
       environment division.
       input-output section.
       data division.
       working-storage section.
       exec sql
        begin declare section
       end-exec
       exec sql declare
         test  table
        (
         no                    char(6)    not null default,
         emp                   char(10)    not null default
        )
        end-exec.
       01  testing.
           03 number1                sql type is
                                      char(6).
           03 employee1              sql type is char(10).
       exec sql
          end declare section
       end-exec
       linkage section.
       procedure division.
       exec sql
         connect to :ODBCDSNName
       end-exec
           move "AB" to employee1(1:2)
           move low-values to employee1(3:1)
           move "123" to employee1(4:3)

           exec sql
           insert into
           test
           ( no, emp) values ( 1111, :employee1)
           end-exec.

           exec sql
           insert into
           test
           ( no, emp) values ( 1113, x'373300350035')
           end-exec.
           exec sql
              commit
           end-exec.
           stop run.

Suggested fix:
Not sure how to fix, please help.
[8 Jan 2010 10:06] uma rengasamy
you might need to add SQLCA copybook to run the program in wirking storage section if you try to replicate it using COBOL.
       exec sql
          include "sqlca.cpy"
       end-exec.
[8 Jan 2010 11:50] Bogdan Degtyariov
Hello Uma,

Which ODBC Driver Manager you are currently using? Is it UnixODBC, iODBC, DataDirect or something else?

We do not have the Cobol interpreter to test your program.
Can you please enable the ODBC trace on your machine and attach it to the bug report?

In case of UnixODBC as the most popular driver manager in Unix/Linux systems you would need to add the following section in odbcinst.ini file:

[ODBC]
TraceFile = /tmp/sql.log
Trace = Yes

Thanks.
[8 Jan 2010 13:38] uma rengasamy
UnixODBC trace

Attachment: ODBCTrace.log (application/octet-stream, text), 13.25 KiB.

[8 Jan 2010 13:38] uma rengasamy
Mysql server log

Attachment: localhost.log (application/octet-stream, text), 16.83 KiB.

[8 Jan 2010 13:43] uma rengasamy
We are using UNIXODBC manager -> unixODBC-2.2.11-7.1
attaching the ODBCtrace and MYSQL server log.. both proves that the 1st query using host variable for the emp column is getting truncated at the 1st occurance of NULL char..
Hope that helps.
[11 Jan 2010 3:59] Bogdan Degtyariov
Uma,

Thank you for attaching the logs. I will check them and let you know my findings.
Thanks.
[11 Jan 2010 6:58] Bogdan Degtyariov
Uma,

I have noticed that your application is trying to set the SQL_ATTR_PARAMSET_SIZE statement attribute. After that the driver returns the "not supported" error. Nevertheless, the application is binding buffers and expecting the query to be executed multiple times.

The arrays of parameters are implemented in the latest revisions of MyODBC driver:

https://launchpad.net/~myodbc-developers/myodbc/paramarrays

You can wait for the next official release of Connector/ODBC 5.1.7 or build the driver from sources that are accessible from the above launchpad address.
[11 Jan 2010 7:04] Tonci Grgin
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Explanation: As Bogdan said, you should try with latest sources or wait for new driver release. In case it does not fix your problem, please reopen the report.
[11 Jan 2010 8:09] uma rengasamy
any expected date of ODBC driver 5.1.7 release? This is critical for us.. our delivery has huge impact on it.. expected date will help us plan for delivery properly. Please suggest the date.
[11 Jan 2010 9:08] Tonci Grgin
Uma, no there has been no date set yet but you can build driver from source repository and test.
[11 Jan 2010 9:13] Tonci Grgin
Or check if patch is present in snapshots (although I don't see it, it's probably just in branch Bogdan mentioned):
http://downloads.mysql.com/snapshots/mysql-connector-odbc-5.1/mysql-connector-odbc-5.1.7r8...
[11 Jan 2010 14:57] uma rengasamy
The delivered source is not working.. we will wait for the release or if you have some intermediate patch we can use that will be great..

Just to add the SELECT query also has the same issue..please consider this for SELECT clause. any all other possible clauses where truncation happens due to presence of NULL.
[12 Jan 2010 3:07] Bogdan Degtyariov
Uma,

I will make a new 32-bit Linux build for you today.
Thanks for your patience.
[12 Jan 2010 11:13] Bogdan Degtyariov
Uma,

Please find the linux snapshot build here:

ftp://ftp.mysql.com/pub/mysql/download/mysql-connector-odbc-5.1.7r863_20100112_1019-linux-...

Please generate another ODBC trace if this build does not work for you.
Thanks.
[12 Jan 2010 12:30] uma rengasamy
unfortunately the FTP site is blocked in our network, could you attach the version here and we will try that..
appreciate your quick response.
[12 Jan 2010 12:52] Bogdan Degtyariov
Unfortunately, files bigger than 500K are not accepted by our bugs tracking system. I will check possibilities with HTTP.
[12 Jan 2010 12:55] uma rengasamy
send me a ZIP version to email IDs: 
abhishek.chatterjee@wipro.com
uma.r66@wipro.com 

that will be great.
[13 Jan 2010 7:48] uma rengasamy
We followed the below methods to compile the source , since we havent received the complete pack . 

1.Overwritten exsisting lib files with ftp://ftp.mysql.com/pub/mysql/download/mysql-connector-odbc-5.1.7r863_20100112_1019-linux-... 

2.Overwritten http://downloads.mysql.com/snapshots/mysql-connector-odbc-5.1/mysql-connector-odbc-5.1.7r8... with 
 ftp://ftp.mysql.com/pub/mysql/download/mysql-connector-odbc-5.1.7r863_20100112_1019-linux-... and build the source . 

Still the error persists 
Kindly guide us if the above method is incorrect sor Kindly send us the complete pack  . Attached the ODBC trace . 

Thanks and Regards
Uma.R
[13 Jan 2010 7:52] uma rengasamy
Trace log form new source

Attachment: odbctrace.log (application/octet-stream, text), 30.61 KiB.

[13 Jan 2010 8:19] Bogdan Degtyariov
Uma,

As I see in the ODBC trace, the driver still displays the not supported error:

DIAG [01S02] [MySQL][ODBC 5.1 Driver][mysqld-5.1.40sp1-enterprise-gpl-advanced-log]Param arrays not supported

This means that you are still using the old driver. Probably the odbc.ini configuration file has the reference to the old version of the driver as libmyodbc5-5.1.6.so instead of libmyodbc5-5.1.7.so.
Can you attach both odbcinst.ini and odbc.ini files to the bug report?
Thanks.
[13 Jan 2010 10:07] uma rengasamy
Attached the zip file which contains 
1.so files which we used 
2.odbcinst
3.odbc
4.odbctrace 

Is there any other way to check the ODBC Driver version ???

Regards
Uma.R
[13 Jan 2010 10:17] uma rengasamy
odbc-Details

Attachment: Mysql.zip (application/x-zip-compressed, text), 3.06 KiB.

[2 Feb 2010 11:13] Bogdan Degtyariov
Marking the report as not a bug. The problem was in Cobol interpretation of string data.