Bug using UNIQUE Definition

Hi,

I have started to learn more about mySql. I am using MySql (Version 4.012)
and ODBC driver (Version 3.51.6).

During my tests I got follwing error:

Table definition:

CREATE TABLE IF NOT EXISTS ModellVariable (
ID INTEGER AUTO_INCREMENT,
ModellId INTEGER DEFAULT 0 NOT NULL,
VariableId INTEGER DEFAULT 0 NOT NULL,
Fahrzeug INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY (ID),
INDEX INDEX_ModellVariable_1 (ModellId),
INDEX INDEX_ModellVariable_2 (VariableId),
FOREIGN KEY (ModellId) REFERENCES ModellIndex (ID),
FOREIGN KEY (VariableId) REFERENCES VariablenIndex (ID),
UNIQUE INDEX UNIQUE_ModellVariable (ModellId,VariableId,Fahrzeug), )
TYPE=INNODB;

SQL statements of a record update using MFC classes of VisualC++ via ODBC
driver:

select * from ModellVariable where ID=9;;
show keys from `ModellVariable`;
SELECT `ModellId`,`VariableId` FROM `ModellVariable`;
UPDATE `ModellVariable` SET `Fahrzeug`=3 WHERE `ID`=9 AND `ModellId`=3 AND
`VariableId`=1 AND `Fahrzeug`=2 AND `ModellId`=1 AND `VariableId`=1 LIMIT
1;

The Update statement is incorrect. I am expecting this following statement:

UPDATE `ModellVariable` SET `Fahrzeug`=3 WHERE `ID`=9;

I assume that the other parts of the original statements are added by the
ODBC driver.

--
MfG Ansgar Gamm

Adresse:

Ludwigstr. 20b
33330 Gütersloh

Telefon:

+49 177 79 44139


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Bug using UNIQUE Definition

Hi,

> I have started to learn more about mySql. I am using MySql (Version 4.012=
)
> and ODBC driver (Version 3.51.6).
>=20
> During my tests I got follwing error:
>=20
> Table definition:
>=20
> CREATE TABLE IF NOT EXISTS ModellVariable (
> ID INTEGER AUTO_INCREMENT,
> ModellId INTEGER DEFAULT 0 NOT NULL,
> VariableId INTEGER DEFAULT 0 NOT NULL,
> Fahrzeug INTEGER DEFAULT 0 NOT NULL,
> PRIMARY KEY (ID),
> INDEX INDEX_ModellVariable_1 (ModellId),
> INDEX INDEX_ModellVariable_2 (VariableId),
> FOREIGN KEY (ModellId) REFERENCES ModellIndex (ID),
> FOREIGN KEY (VariableId) REFERENCES VariablenIndex (ID),
> UNIQUE INDEX UNIQUE_ModellVariable (ModellId,VariableId,Fahrzeug), )
> TYPE=3DINNODB;
>=20
> SQL statements of a record update using MFC classes of VisualC++ via ODBC
> driver:
>=20
> select * from ModellVariable where ID=3D9;;
> show keys from `ModellVariable`;
> SELECT `ModellId`,`VariableId` FROM `ModellVariable`;
> UPDATE `ModellVariable` SET `Fahrzeug`=3D3 WHERE `ID`=3D9 AND `ModellId`=3D3 AN=
D
> `VariableId`=3D1 AND `Fahrzeug`=3D2 AND `ModellId`=3D1 AND `VariableId`=3D1 LIMIT
> 1;
>=20
> The Update statement is incorrect. I am expecting this following statemen=
t:
>=20
> UPDATE `ModellVariable` SET `Fahrzeug`=3D3 WHERE `ID`=3D9;
>=20
> I assume that the other parts of the original statements are added by the
> ODBC driver.

Thanks for your excellent bug report.

This turned out to be a bug in parsing the key columns by the driver while
generating cursor based updates/deletes query internally to append a
where clause.=20

This will be fixed in the next version of MyODBC.

Meanwhile the alternative is to use client-side cursor type while
opening the record set.

I have entered this bug to our bug tracking system. You can follow its
progress at: http://bugs.mysql.com/bug.php?id=3D260


Rgds,
Indrek

--=20
MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Indrek Siitan
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, The Support Guy
/_/ /_/\_, /___/\___\_\___/ Uuem=F5isa, Haapsalu, Estonia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org