Bug #93787 Auto-inc primary key field changes to 0 on update with ODBC > 5.3.4
Submitted: 2 Jan 2019 18:07 Modified: 14 Aug 2020 20:32
Reporter: Brian Lowrance Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.3.9, 5.3.11, 8.0.13 OS:Windows (Server 2012 R2)
Assigned to: CPU Architecture:Other (x64)

[2 Jan 2019 18:07] Brian Lowrance
Description:
I have an ASP classic website that uses the MySQL ODBC Connector.  An UPDATE query when using the 5.3.4 ODBC Connector works fine.  If I upgrade the ODBC Connector to 5.3.9, 5.3.11 or 8.0.13 when I try to update the fields of a specific record that uses a Primary Key as an auto-incrementing number the query causes the Primary Key to change from the current value to 0.

With MySQL general logging enabled on the server, I can see the following differences between 5.3.4 and newer ODBC drivers:
ODBC 5.3.4 generates:
UPDATE `workorder` SET `School`='Central Office',`Note`='Test' WHERE `Ticketnum`='56799' LIMIT 1

ODBC 5.3.9 and up generates the following:
UPDATE `workorder` SET `Ticketnum`=DEFAULT,`School`='Central Office',`Note`='Test' WHERE `Ticketnum`='56799' LIMIT 1
Specifically note the following addition:  SET `Ticketnum`=DEFAULT,

How to repeat:
Perform the test under both 5.3.4 ODBC connector and any newer version.  5.3.4 ODBC connector works as expected, newer versions do not.
(MySql Server Version is 5.7.23)

CREATE TABLE `workorder` (
  `Ticketnum` int(11) NOT NULL AUTO_INCREMENT,
  `School` varchar(50) DEFAULT NULL,
  `Note` varchar(200) DEFAULT NULL
  PRIMARY KEY (`Ticketnum`),
) ENGINE=InnoDB AUTO_INCREMENT=56798 DEFAULT CHARSET=utf8;

Insert a record into the database at 56799 (for this example)
INSERT INTO `techdata`.`workorder` (`Ticketnum`, `School`, `Note`) VALUES ('56799', 'Central Office', `Original Note`);

Build an ASP classic web page running on IIS with the appropriate MySQL Connector/ODBC installed for testing (if using the same server, restart IIS between ODBC version install/uninstalls):
  'Connect to the database in ASP:
    CONST adOpenDynamic = 2
    CONST adLockOptimistic = 3
    ConnectionString="PROVIDER=MSDASQL;DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=servername;DATABASE=techdata;UID=username;PWD=secretpassword;"
    Set Connect = Server.CreateObject("ADODB.Connection")
    Connect.Open ConnectionString

  'Build query and fill the recordset in ASP:
    Set Techdata = Server.CreateObject("ADODB.Recordset")
    Query = "SELECT * FROM WorkOrder Where Ticketnum=56799"
    Techdata.Open Query, Connect, adOpenDynamic, adLockOptimistic

  'Modify the returned record in ASP:
    Techdata("Note") = "Test"
    Techdata.update

  'Close the connection
    Techdata.close
    Connect.close

Expected:  
  Record 56799 is updated with the new note field data.

Result in ODBC driver versions newer than 5.3.4:
  Record 56799's Ticketnum field is changed to 0.  Other record updates after this attempt fail since 0 is now erroneously in use ("Error Code: 1062. Duplicate entry '0' for key 'PRIMARY').
[2 Jan 2019 18:12] Brian Lowrance
Modifying "Version" field to indicate this affects releases after 5.3.4 (5.3.9, 5.3.11, 8.0.13).
[14 Jul 2020 20:32] MySQL Verification Team
Please check version 8.0.21.
[15 Aug 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[17 Mar 2021 10:45] Simon Goodwin
I have just experienced the same problem.

When I update any tables, all of which have primary keys with Auto Increment enabled, the primary key is written back to the database as 0.

The ODBC Connector is chosen by my web host and I have no control over it.

Can anyone advise on what I should do to get around this issue as I am totally stuck, being unable to complete a migration project on behalf of a client.

My knowledge of MySQL is very primitive, being just enough to let me get by when making database-driven websites.