MySQL Bugs: #75275: Generated INSERT statement uses DEFAULT for INT field when last column is text

Bug #75275	Generated INSERT statement uses DEFAULT for INT field when last column is text
Submitted:	19 Dec 2014 15:51	Modified:	29 Jan 2015 10:03
Reporter:	Adam Waller	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.3.4	OS:	Windows (Access ADODB)
Assigned to:		CPU Architecture:	Any

Description:
When using ADODB code in Microsoft Access to connect to a MySQL database using the ODBC 5.3.4 driver, a value specified for an INT column is replaced with DEFAULT when the last column in the table is of a text data type.
See further details at http://stackoverflow.com/questions/27492235/indexed-mysql-field-not-saving-value-when-crea... 

How to repeat:
Create a MySQL table as follows:

CREATE TABLE `phplist_user_user_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `summary` varchar(255) DEFAULT NULL,
  `detail` text,
  `systeminfo` text,
  PRIMARY KEY (`id`)
)

In VBA, add a reference to the Microsoft ActiveX Data Objects 6.1 Library, and paste the following code into a regular module.

Public Sub MySQL_BugDemonstration()
   
    Dim oConn As New ADODB.Connection
    Dim strSQL As String
    Dim rst As ADODB.Recordset
    
    ' Establish connection to MySQL database
    oConn.Open "Driver=MySQL ODBC 5.3 Unicode Driver;" & _
            "SERVER=myserver;UID=root;PWD={mysecret};" & _
            "DATABASE=test;PORT=3306;DFLT_BIGINT_BIND_STR=1"
    
    ' Open recordset with phplist_user_user_history table
    Set rst = New ADODB.Recordset
    strSQL = "phplist_user_user_history limit 0,1"
    rst.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
    
    With rst
        .AddNew
            !userid = 123
            !ip = ""
            ![Date] = Now()
            !Summary = "My summary"
            !Detail = "My details"
            !systeminfo = "Automated syncronization process."
        .Update
        .Close
    End With
    
    ' Clear reference
    Set rst = Nothing
    Set oConn = Nothing
    
End Sub

Run the sample code. It will encounter an error trying to update the record. If you profile the MySQL server, you will see the following generated SQL statement (Note how 'DEFAULT' was used for the userid column):

INSERT INTO `test`.`phplist_user_user_history`(`id`,`userid`,`ip`,`date`,`summary`,`detail`,`systeminfo`) 
VALUES (DEFAULT,DEFAULT,'',_binary'2014-12-18 11:51:13','My summary','My details','Automated syncronization process.')

Now add a second INT(11) column as the last column in the table, and run the test code again. This time it correctly sets the userid field.

Suggested fix:
The SQL statement generated by the ODBC connector should reflect the values explicitly set through ADO, regardless of the data type of the last column in the table.

Hello Adam Waller,

Thank you for your feedback.
Verified this behavior on Visual Studio 2013 (VB.Net) and MySQL ODBC 5.3.4.

Thanks,
Chiranjeevi.

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.6.21-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql>
mysql> CREATE TABLE phplist_user_user_history
    -> (id int(11) NOT NULL AUTO_INCREMENT,
    -> userid int(11) NOT NULL ,
    -> ip varchar(255) DEFAULT NULL,
    -> date datetime DEFAULT NULL,
    -> summary varchar(255) DEFAULT NULL,
    -> detail text,
    -> systeminfo text,
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.29 sec)

mysql> select * from phplist_user_user_history;
Empty set (0.00 sec)

Error Message screenshot

Attachment: 75275_Screenshot.PNG (image/png, text), 95.74 KiB.