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:
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.2.2 OS:Microsoft Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[25 Nov 2012 15:45] Sue J
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.
[3 Dec 2012 17:04] Lawrenty Novitsky
Could you please try NO_SSPS connection string option(Prepare statements on client) to check if the problem persists?
[3 Dec 2012 19:20] Sue J
Yes, that seems to resolve the problem.

Does it affect performance in any way?
[4 Dec 2012 4:48] Bogdan Degtyariov
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.
[11 Dec 2012 2:30] Bogdan Degtyariov
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
           !vc = "abcd 1234"
           !yesno = 1
    End With
End Sub
[11 Dec 2012 2:49] Bogdan Degtyariov
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
           !vc = "abcd 1234"
           !yesno = 1
    End With
End Sub
[11 Dec 2012 2:59] Bogdan Degtyariov
Manual typing the value 1 in the bit field causes error.
Verified with ODBC driver version 5.2.2.
[11 Dec 2012 6:39] Bogdan Degtyariov
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.
[13 Dec 2012 14:42] Bogdan Degtyariov
Patch and test case

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

[13 Dec 2012 23:38] Lawrenty Novitsky
The patch has been pushed as rev#1104 and is supposed to be released in 5.2.3
[20 Dec 2012 22:25] John Russell
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'