MySQL Bugs: #5669: MyODBC problems with primary key on multiple fields

Bug #5669	MyODBC problems with primary key on multiple fields
Submitted:	20 Sep 2004 15:46	Modified:	13 Sep 2007 1:18
Reporter:	Przemyslaw Popielarski	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (Windows XP SP1)
Assigned to:		CPU Architecture:	Any

Description:
I've got a table:

# Server version 4.0.21-log
CREATE TABLE tTransactions_test_premaxa (
  TRANID mediumint(8) unsigned NOT NULL auto_increment,
  FIRMID mediumint(8) unsigned NOT NULL default '0',
  TRANTYPEID smallint(5) unsigned NOT NULL default '0',
  UPDATETIME timestamp(14) NOT NULL,
  PRIMARY KEY  (TRANTYPEID,TRANID),
  KEY idxunique (FIRMID,TRANID,TRANTYPEID)
) TYPE=MyISAM;

# Dumping data for table 'tTransactions_test_premaxa'
INSERT INTO tTransactions_test_premaxa VALUES("1", "0", "2", "20040920170829");
INSERT INTO tTransactions_test_premaxa VALUES("1", "0", "3", "20040920171026");

And I'm connecting to it with MS Access XP SP2 through MyODBC (tests were done with versions 2.50, 3.51.06 and 3.51.9). Then I select table view and I can see my two records. Then I try to insert new record with FIRMID=0 and TRANTYPEID=1. I leave TRANID empty to make it autoincremented.

And what's happening?  After accepting changes the new records doesn't show up, but instead the one of the old records is duplicated.

I tried to investigate this bug and this is the log from c:\sql.log:
-- Query logging
--
--  Driver name: MySQL ODBC 3.51 Driver  Version: 03.51.09
-- Timestamp: 040920 17:21:19

SELECT Config, nValue FROM MSysConf;
SHOW TABLES FROM `mysql` like '%';
SHOW KEYS FROM `tTransactions_test_premaxa`;
SELECT `tTransactions_test_premaxa`.`TRANTYPEID`,`tTransactions_test_premaxa`.`TRANID` FROM `tTransactions_test_premaxa` ;
SELECT `TRANID`,`FIRMID`,`TRANTYPEID`,`UPDATETIME`  FROM `tTransactions_test_premaxa`  WHERE `TRANTYPEID` = 2 AND `TRANID` = 1 OR `TRANTYPEID` = 3 AND `TRANID` = 1 OR `TRANTYPEID` = 3 AND `TRANID` = 1 OR `TRANTYPEID` = 3 AND `TRANID` = 1 OR `TRANTYPEID` = 3 AND `TRANID` = 1 OR `TRANTYPEID` = 3 AND `TRANID` = 1 OR `TRANTYPEID` = 3 AND `TRANID` = 1 OR `TRANTYPEID` = 3 AND `TRANID` = 1 OR `TRANTYPEID` = 3 AND `TRANID` = 1 OR `TRANTYPEID` = 3 AND `TRANID` = 1;
INSERT INTO  `tTransactions_test_premaxa`  (`FIRMID`,`TRANTYPEID`) VALUES (0,1);
SELECT `TRANID`,`FIRMID`,`TRANTYPEID`,`UPDATETIME`  FROM `tTransactions_test_premaxa`  WHERE `TRANTYPEID` = 1 AND `TRANID` IS NULL;
SELECT `TRANID`,`FIRMID`,`TRANTYPEID`,`UPDATETIME`  FROM `tTransactions_test_premaxa`  WHERE `TRANTYPEID` = 1 AND `TRANID` IS NULL;
SELECT `tTransactions_test_premaxa`.`TRANTYPEID`,`tTransactions_test_premaxa`.`TRANID` FROM `tTransactions_test_premaxa` WHERE `FIRMID` = 0;
SELECT `TRANID`,`FIRMID`,`TRANTYPEID`,`UPDATETIME`  FROM `tTransactions_test_premaxa`  WHERE `TRANTYPEID` = 3 AND `TRANID` = 1;
SELECT `TRANID`,`FIRMID`,`TRANTYPEID`,`UPDATETIME`  FROM `tTransactions_test_premaxa`  WHERE `TRANTYPEID` = 3 AND `TRANID` = 1;

What can I see in this log is terrible mess (why?!), but maybe you find out why do I get wrong record after inserting a new one.

How to repeat:
as said before

Suggested fix:
as said before

Verified with 3.51.10 and Access 2003.

hello.. I got the same problem.. when you insert a new record it shows as #deleted#, you close the table/form and open it again and its ok..

I solved this by setting the autonumber field to "0" on the event "Before Update" in the access form.. this way the primary key field has a value before the transaction ends.. when its done the field takes the corresponding autoincrement value..

good luck

mike

The problem is that Microsoft Access 2003 is using `TRANID` IS NULL as part of the WHERE clause in fetching the data after it has been inserted. But not only is it using that (which is non-standard behavior and disabled by default in the driver), it is doing it twice, once before it commits the transaction, and again after it has committed. MySQL's support for selecting a row with an auto-increment value for this only works once. So even though the record is correctly returned the first time, no record will be returned the second time.

I don't see any sensible way around this in the driver. Reports are that Microsoft Access 2007 does not rely on the non-standard 'IS NULL' behavior for auto-incrementing columns.