MySQL Bugs: #24535: Notorious #DELETED problem when linking tables in Access and BIGINT PK

Bug #24535	Notorious #DELETED problem when linking tables in Access and BIGINT PK
Submitted:	23 Nov 2006 10:12	Modified:	28 Mar 2008 18:18
Reporter:	Tonci Grgin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Windows (Win XP Pro (SP2))
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	ODBC, ODBC5-RC

Description:
When linking external data to Access2003, all fields have #DELETED value if the first field is BIGINT and PK (works with INT). Importing the *same* table gives correct result. Same goes for File and User DSNs.

Environment:
  - MySQL server 5.0.27BK on Win XP Pro SP2 localhost
  - connector/ODBC 5.00.09
  - MS Access 2003 (11.6566.8036) SP2

How to repeat:
 - Create File DSN
 - Open Access, create new database
 - Choose "Get external data" / "Link tables"
 - Choose table with BIGINT field
 - Open table, all fields are with value #DELETED
--
 - Choose "Get external data" / "Import"
 - Choose the same table with BIGINT field
 - Open table, all fields have correct values

Example:
CREATE TABLE `bug_scenario` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `column_a` varchar(45) default NULL,
  `column_b` varchar(45) default NULL,
  `column_c` varchar(45) default NULL,
  `column_d` varchar(45) default NULL,
  `column_e` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; - rem: engine makes no difference
INSERT INTO `bug_scenario` VALUES (1,"AAA","BBB","CCC","DDD","EEE");

Suggested fix:
-

Hi Tonci,

thanks for writing a bug report.

Susanne

The bug #26343 is a duplicate of this bug here.

Bug #28640 is a duplicate of this bug here.

This is a duplicate of bug #31308

Verified as described for MyODBC v5.1 by using MS Access 2007.

Added FLAG_DFLT_BIGINT_BIND_STR to map SQL_BIGINT to SQL_C_CHAR when calling SQLBindParameter() with SQL_C_DEFAULT. This is beha

Attachment: bug24535.diff (application/octet-stream, text), 2.00 KiB.

code looks ok, but i don't have access to check if it really works, but i trust you that it does :)
first that comes to mind that it requires checkbox in gui to be added. although automatic detection of ms access should be enough, at least for beta.

Committed as rev 1074 and will be released in 5.1.3.

A note has been added to the 5.1.3 changelog: 

Using a linked table in Access 2003 where the table has a BIGINT column as the first column in the table, and is configured as the primary key, shows #DELETED for all rows of the table.