Bug #17679 Bigint toINT functionality seems broken
Submitted: 23 Feb 2006 21:54 Modified: 10 Nov 16:08
Reporter: Ronald van Raaij
Status: Closed
Category:Connector/ODBC Severity:S3 (Non-critical)
Version:3.51.12 OS:Microsoft Windows (Windows XP)
Assigned to: Target Version:5.1
Triage: D3 (Medium)

[23 Feb 2006 21:54] Ronald van Raaij 
Description:
Connecting with MSACCESS 2003 to MySQL 5.0.16 to a newly created database and tables, the
dreaded #deleted# bug showed up again.
Changing the datatype of the primary key from Bigint to INT solved the issue.

All the other fixes/workarounds suggested here:

http://dev.mysql.com/doc/refman/5.1/en/deleted-problem.html

have been tried and had no effect.

How to repeat:
Create new database, empty table like this one:
reate table BICCategories
(
    IDBic                     BIGINT unsigned       not null  AUTO_INCREMENT PRIMARY
KEY,
    Code                      CHAR(255)             null      ,
    Description               CHAR(255)             null      ,
    Remark                    TEXT                  null      ,
    SISOCode                  CHAR(50)              null      ,
    OldID                     BIGINT unsigned       null      ,
    TS                        TIMESTAMP             not null        default NOW(),
    TS2                       TIMESTAMP             not null       default '2006-02-23
20:25:20'
)
TYPE= InnoDB;

Create ODBC DSN
Check 
x Don't Optimize Column Width
x Return Matching Rows
x Allow Big Results
x Change BIGINT Columns To INT

Create table link in Access 2003 

add record

#deleted# shows
[24 Feb 2006 14:02] Valeriy Kravchuk 
Thank you for a problem report. What exact version of Connector/ODBC do you use?
[24 Feb 2006 18:40] Ronald van Raaij 
Sorry forgotten that, I use the 3.51.12 ODBC connector
[25 Feb 2006 16:36] Ronald van Raaij 
Newsflash: Just upgraded the server to 5.0.18 same difference;-) ie problem persists. I
also created a new database and a new table that had no effect.
[27 Feb 2006 22:40] Jorge del Conde 
Thanks for your bug report.  I was able to reproduce this using 3.51.12 and Access 2003
[14 Nov 2006 4:55] Jess Balint 
fix connector/odbc version
[9 Apr 2008 16:39] Brent Wegmann 
Is there any update to this... what can we do to get around it?
[11 Sep 2008 5:12] Jess Balint 
COLUMN_SIZE was still the wrong value for Access, even when FLAG_NO_BIGINT was set

Also, BIGINT should work fine with Access now since Bug#24535 is fixed.
[24 Sep 2008 12:00] Bogdan Degtyariov 
Verified with the latest revision (801). MS Access is not able to read BIGINT values
properly from the table with just two columns (BIGINT and VARCHAR).
#DELETE appears instead of real values.
[6 Aug 18:20] Jess Balint 
Will be released in 5.1.6
[18 Sep 16:28] Tony Bedford 
An entry was added to the 5.1.6 changelog:

Microsoft Access was not able to read BIGINT values properly from a table with just two
columns of type BIGINT and VARCHAR. #DELETE appeared instead of the correct values.
[4 Nov 20:19] Lawrin Novitsky 
Pathch has been backported to 3.51 and pushed as rev#704. Will be in 3.51.28 release
[10 Nov 12:29] Lawrin Novitsky 
(ported to 3.51)
[10 Nov 16:08] Tony Bedford 
Changelog entry updated to include version 3.51.28.