MySQL Bugs: #36470: ODBC update boolean wrong in MySQL ODBC 5.1, correct in MySQL ODBC 3.51

Bug #36470	ODBC update boolean wrong in MySQL ODBC 5.1, correct in MySQL ODBC 3.51
Submitted:	2 May 2008 12:12	Modified:	21 Oct 2008 19:07
Reporter:	James Courtier-Dutton	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When a view is defined on a table and one does a SELECT QUERY in MS ACCESS against the view, BOOLEAN types are correct.
When one then does an UPDATE via the view, and the update contains something like the following as viewed from the debug log on the mysql server:
UPDATE `RIDS` SET `DocSubject`='External'  WHERE `id` = 1 AND `Problem general` = 1;

Here `Problem general` is a Boolean.
id is the primary key and is a unsigned int.

The problem is this. The Select query for id = 1 returns `Problem general` = 0;
MS Access correctly displays it as no-tick in an MS ACCESS form.
But, when the update is done, the query sent to MySQL looks for `Problem general` = 1;  So the update fails.

This is a bug with MySQL ODBC 5.1.
Simply using MySQL ODBC 3.51 instead allows it all to work correctly.

How to repeat:
When a view is defined on a table and one does a SELECT QUERY in MS ACCESS against the view, BOOLEAN types are correct.
When one then does an UPDATE via the view, and the update contains something like the following as viewed from the debug log on the mysql server:
UPDATE `RIDS` SET `DocSubject`='External'  WHERE `id` = 1 AND `Problem general` = 1;

Here `Problem general` is a Boolean.
id is the primary key and is a unsigned int.

The problem is this. The Select query for id = 1 returns `Problem general` = 0;
MS Access correctly displays it as no-tick in an MS ACCESS form.
But, when the update is done, the query sent to MySQL looks for `Problem general` = 1;  So the update fails.

This is a bug with MySQL ODBC 5.1.
Simply using MySQL ODBC 3.51 instead allows it all to work correctly.

Hi James and thanks for your report.
Can you please attach DDL/DML script with data used for test and report exact MySQL server/MS Access version as well as steps for reproducing the problem in Access (I presume there's form and linked view involved).

I will try to do this. The actual data is private stuff, so I will have to try and create a new database that reproduces the problem. I could then attach the ms database .mdb file as well as the mysqldump of the mysql database.

mysql  Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i486) using readline 5.2
msaccess ver: Microsoft Office Access 2003 (11.8166.8203) SP3

MS Access file with two links, table3.51 is using ODBC Driver 3.51, table51 is using Driver 5.1

Attachment: test1-2000.mdb (application/msaccess, text), 160.00 KiB.

mysqldump of database test1

Attachment: mysqldump-test1.sql (application/octet-stream, text), 1.80 KiB.

In MS Access, the ODBC 3.51 table has -1, -1, -1, 0, 0 in the status column.
the ODBC 5.1 table has -1, -1, -1, -1, -1.

So, obviously, the ODBC 5.1 driver is not correctly handling BOOLEANS or BIT(1) data types.

The table using ODBC 3.51 can be updated from access just by editing the table view.
The table using ODBC 5.1 cannot be updated from access just by editing the table view.

As you can see, I managed to narrow down the problem a bit.
No MS Access forms are needed to reproduce the problem.
Just simple views of the tables in MS Access.

James, thank you for your efforts. Checking.

Looking at the source code for the ODBC driver. It looks like the problem might be in driver/results.c:

--- mysql-connector-odbc-3.51.25r1093/driver/results.c	2008-04-11 10:58:44.000000000 +0100
+++ mysql-connector-odbc-5.1.4r1107/driver/results.c	2008-04-15 02:03:36.000000000 +0100

     case SQL_C_BIT:
       if (rgbValue)
-        *((char *)rgbValue)= (value[0] == 1 ? 1 : (atoi(value) == 0) ? 0 : 1);
+      {
+        if (value[0] == 1 || !atoi(value))
+          *((char *)rgbValue)= 1;
+        else
+          *((char *)rgbValue)= 0;
+      }
       *pcbValue= 1;
       break;

The above is changing the meaning of the code in the different versions.
I would suggest maybe seeing if we can establish if value is a pointer to an int, or a pointer to a string before we do the tests.

In the above:
value[0]  atoi(value)  *pcbValue (3.51)  (5.1)  (should-be)
0         0                       0        1       0
1         0                       1        1       1
60        0                       1        1       0
61        1                       1        1       1

As you can see, neither are 100% correct, but 5.1 is probably the worst!

*pcbValue should be *rgbValue in the above table of outputs.

-        if (value[0] == 1 || !atoi(value))
+        if (value[0] == 1 || atoi(value))

That should fix the problem.

Patch to fix the problem

Attachment: bool-fix.diff (text/x-diff), 545 bytes.

James, verified as described using script provided by you. MySQL server version is 5.0.60pb.

mysql> SELECT IF(status=true, 'true', 'false') FROM table1;
+----------------------------------+
| IF(status=true, 'true', 'false') |
+----------------------------------+
| true                             |
| true                             |
| true                             |
| false                            |
| false                            |
+----------------------------------+
5 rows in set (0.00 sec)

Also, column is correctly recognized:
SQLColumns:
		In:	StatementHandle = 0x00851FD0, CatalogName = "test1", NameLength1 = 5, SchemaName = "test1", NameLength2 = 5, TableName = "table1", 	NameLength3 = 6, ColumnName = SQL_NULL_HANDLE, NameLength4 = 0
		Return:	SQL_SUCCESS=0

Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION", "IS_NULLABLE"
"te", <Null>, "t", "id", 4, "integer unsigned", 10, 4, 0, 10, 1, "", <Null>, 4, <Null>, <Null>, 1, "YES"
"te", <Null>, "t", "title", 12, "varchar", 50, 50, <Null>, <Null>, 1, "", <Null>, 12, <Null>, 50, 2, "YES"
"te", <Null>, "t", "status", -7, "bit", 1, 1, 0, 10, 1, "", <Null>, -7, <Null>, <Null>, 3, "YES"
3 rows fetched from 18 columns.

Still, Access shows TRUE for all 5 rows in your table.

Is there any chance someone could provide me with a binary for the ODBC driver 5.1 with my attached patch included. I don't have a windows compiler to test it.
I also think I have found another bug, but it would be easier to narrow down the new bug problem if I had this one fixed first.

James, no, not that I see for now. The bug is still waiting to be assigned to someone who can then check on your patch.

Bug#37118 was marked as duplicate of this one.

Bug #37699 was marked as duplicate of this one.

Bug #38991 was marked as duplicate of this one.

Another user affected in Bug#39871.

Rising the severity due to numerous users being hurt by this problem.

Raised to P2

This is a duplicate of bug#39644. We will support bit(1) and integer fields, but not arbitrary values masked with 1 as requested by reporter.

What "arbitrary values masked with 1" are you refering to?

The behaviour should be like so:

value[0]  atoi(value)  *pcbValue (3.51)  (5.1)  (should-be)
0         0                       0        1       0
1         0                       1        1       1
60        0                       1        0       0
61        0                       1        0       1

As 60 and 61 aren't ASCII digits, atoi() will return 0. If you need this behaviour you can use a bitmask in your SQL queries. atoi() will *not* return 1 for ASCII value 61.

Ah! I see, I made a mistake with the submission.
I meant '0' = 0x30 an '1' = 0x31.
So that the code can handle strings using the ascii digit 0 and 1 as well as normal ints of 0 and 1.
I think that is what the atoi() in the original code was trying to do.

My patch "bool-fix.diff" should do the fix that I had intended.

James,

We will likely apply a fix very similar to that. You can follow the progress at bug#39644.