Bug #103036 Primary key field changes to 0 on update if the last field in table is TEXT
Submitted: 18 Mar 11:06 Modified: 26 Apr 12:14
Reporter: Simon Goodwin Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.3 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[18 Mar 11:06] Simon Goodwin
Description:
I am an end user without direct access to the server hosting my website so I don't have (or understand) more configuration details than those provided.

I encountered the following problem having recently created a new website driven by classic ASP.

* Any table with a last field of data type TEXT, MEDIUMTEXT or LONGTEXT would have its primary key set to 0 when any part of the row is updated.

I found I could prevent this from happening by using Workbench (6.3.10) to move the last field of one of these data types to further up the order of fields. Subsequent updates left the primary key field intact.

How to repeat:
I used this code to establish the connection:
=============================================

sConnectMySQL = "Driver={MySQL ODBC 5.3 UNICODE Driver}; Server=" & sDBserver & "; Database=" & sDBdatabase & "; Uid=" & sDBuser & "; Password=" & sDBpassword & "; Option=3"

Set adoCon = Server.CreateObject("ADODB.connection")
adoCon.Open(sConnectMySQL)

I used this code to update the record with PK = 1
(the problem arises regardless of the row selected)
=================================================
Set rs = Server.CreateObject("ADODB.Recordset")
recordID = 1
sSQL = "SELECT * FROM tblWebPage WHERE RecordID = " & recordID

rs.CursorType = 2
rs.LockType = 3

rs.Open sSQL, adoCon
if not rs.EOF then
'       rs("sPageName") is not the last field, it's a varchar(100)
	rs("sPageName") = rs("sPageName") & "A"

	rs.Update
end if

rs.Close
[26 Mar 12:14] MySQL Verification Team
Hello Simon Goodwin,

Thank you for the bug report.
To investigate further this issue at our end, may I kindly request you to provide complete test case with table structure? Thanks.

Regards,
Ashwini Patil
[27 Apr 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".