MySQL Bugs: #67702: Problem with BIT columns in MS Access

Bug #67702	Problem with BIT columns in MS Access
Submitted:	25 Nov 2012 15:45	Modified:	20 Dec 2012 22:25
Reporter:	Sue J	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.2.2	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
We have a Microsoft Access front-end application linked to a MySQL server (5.5.15).

When running with ODBC connector 5.2.2 (32 bit), inserts fail with ODBC--insert on a linked table 'Members' failed.

The table contains fields which are defined as bit(1) with a default of b'0'

If we change remove the bit fields from the Insert statement, the insert is carried out without a problem.

The Insert statement (with its bit fields) works as intended with older ODBC connectors (5.1.8 and 5.1.10) so it would appear that there is a problem with 5.2.2

The problem is reproduceable with Windows 7 professional (64 bit) running Access 2003 and Windows 8 Professional (64 bit) running Access 2010 (32 bit)

How to repeat:
Create a table with bit(1) fields

Create an MSAccess application and link to the MySQL table
Insert a row into the table using DAO - the insert fails.

Insert a row (manually) using the linked table - insert works (assume this is because it is being set from the field's default value)

Overtype the field (which is being displayed as 'No') with a 0
message Data too long for column 'Directory_Excluded' at row 1 is output.

Could you please try NO_SSPS connection string option(Prepare statements on client) to check if the problem persists?

Yes, that seems to resolve the problem.

Does it affect performance in any way?

The performance can decrease if your front end client (MS Access) prepares INSERT query once and then only supplies the data for the prepared query.

So far I have not observed such behavior with MS Access installed in my system.
Therefore, the performance for that particular client should not change.

In any case you can disregard such issues as with the manual data entering/editing even a much bigger performance impact can be unnoticed.

Another workaround is to use ADO instead of DAO.
The following test case works well for me:

' create table bug67702(id int primary key auto_increment, 
' vc varchar(32), yesno bit(1));

Sub bug67702()
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    conn.Open "DSN=test52w", "******", "******", 0
    rs.Open "SELECT * FROM bug67702", conn, adOpenDynamic, adLockOptimistic
    With rs
         .AddNew
           !vc = "abcd 1234"
           !yesno = 1
        .Update
    End With
End Sub

The DAO test case worked without problems as well:

Sub bug67702()
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase("", False, False, "ODBC;DSN=test52w")
    
    Set rs = db.OpenRecordset("SELECT * FROM bug67702")
    
    With rs
         .AddNew
           !vc = "abcd 1234"
           !yesno = 1
        .Update
    End With
End Sub

Manual typing the value 1 in the bit field causes error.
Verified with ODBC driver version 5.2.2.

The executed statement on the server looks as follows:

UPDATE `bug67702` SET `yesno`='1'  WHERE `id` = '5' AND `vc` = 'bbb' AND `yesno` = '0'

This is not right because `yesno` column is assigned the value 31, which is the code of symbol '1'. It is out of the allowed range, so the insert fails.

Patch and test case

Attachment: bug67702.diff (application/octet-stream, text), 3.97 KiB.

The patch has been pushed as rev#1104 and is supposed to be released in 5.2.3

Added to changelog for 5.2.3: 

Insert operations could fail if the inserted values contained any bit
fields, mapped to table columns declared as bit(1): Data too long for
column 'column_name'