Bug #17679 Bigint toINT functionality seems broken

• Submitted: 23 Feb 2006 20:54
• Modified: 10 Nov 2009 15:08
• Reporter: Ronald van Raaij
• Status: Closed
• Category: Connector / ODBC
• Severity: S3 (Non-critical)
• Version: 3.51.12
• OS: Windows (Windows XP)
• CPU Architecture: Any

[23 Feb 2006 20: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 13:02] Valeriy Kravchuk

Thank you for a problem report. What exact version of Connector/ODBC do you use?

[24 Feb 2006 17:40] Ronald van Raaij

Sorry forgotten that, I use the 3.51.12 ODBC connector

[25 Feb 2006 15: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 21: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 3:55] Jess Balint

fix connector/odbc version

[9 Apr 2008 14:39] Brent Wegmann

Is there any update to this... what can we do to get around it?

[11 Sep 2008 3: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 10: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 2009 16:20] Jess Balint

Will be released in 5.1.6

[18 Sep 2009 14: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 2009 19:19] Lawrenty Novitsky

Pathch has been backported to 3.51 and pushed as rev#704. Will be in 3.51.28 release

[10 Nov 2009 11:29] Lawrenty Novitsky

(ported to 3.51)

[10 Nov 2009 15:08] Tony Bedford

Changelog entry updated to include version 3.51.28.