Bug #92078 Connector/ODBC bug when retrieving TEXT records using parameterized queries
Submitted: 20 Aug 2018 9:53 Modified: 28 Apr 2021 16:57
Reporter: Ruud van den Hout Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.3.11 and 8.0.x OS:Any
Assigned to: CPU Architecture:Any

[20 Aug 2018 9:53] Ruud van den Hout
Description:
Connector/ODBC bug when retrieving TEXT records using parameterized queries

I think I might have found a bug in the MySQL Connector/ODBC for versions greater then 5.3.10 (eg: latest 5.3.11 en 8.x builds) when retrieving TEXT records using parameterized queries

As you can see when you run the example below the first call to show() returns no recordset, but once the TEXT field is empty(or null) the second call to show() the recordset is returned successfully

btw, i think this is a similar report:
https://forums.mysql.com/read.php?37,668110,668110#msg-668110

How to repeat:
I'll try to demonstrate this with an example:

____________________________________________________________________________

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(255) NOT NULL,
  `Content` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', 'test 1', 'test 1');
INSERT INTO `test` VALUES ('2', 'test 2', null);

odbcbug.vbs
____________________________________________________________________________

dim adoConn, objCommand, param, rs, strError

set adoConn = CreateObject("ADODB.Connection")
adoConn.Open "Driver={MySQL ODBC 5.3 Unicode Driver}; Server=localhost; Option=2; Database=odbcbug; Uid={YOUR USERNAME};Pwd={YOUR PASSWORD};" 
if adoConn.errors.count <> 0 Then 
	For Each errLoop In adoConn.Errors  
	 strError = "Error #" & errLoop.Number & VbCrLf & _  
		"   " & errLoop.Description & VbCrLf & _  
		"   (Source: " & errLoop.Source & ")" & VbCrLf & _  
		"   (SQL State: " & errLoop.SQLState & ")" & VbCrLf & _  
		"   (NativeError: " & errLoop.NativeError & ")" & VbCrLf   
	 wscript.Echo( strError )  
	Next  
Else
	Show(1)
	Show(2)
end If
Err.Clear
On Error Goto 0

function Show(id)
	dim sql
	sql = "SELECT title, content FROM `test` WHERE ID=?"
	Set objCommand = CreateObject("ADODB.Command")
	objCommand.ActiveConnection = adoConn
	objCommand.CommandText = sql
	objCommand.CommandType = 1
	Set param = objCommand.CreateParameter("@ID",3, 1)
	param.value = id
	objCommand.Parameters.Append param
	Set rs = objCommand.Execute()
	If Not rs.EOF Then
		wscript.Echo("Title:" & rs("title") & VbCrLf & "Content:" & rs("content") )
	End if
	Set rs = nothing
	Set param = nothing
	Set objCommand = nothing
End function

____________________________________________________________________________
[20 Aug 2018 10:14] Ruud van den Hout
FYI: this error does not occur when using non-parameterized queries or with 'NO_SSPS=1' set in connection string
[14 Oct 2018 0:27] MySQL Verification Team
Thank you for the bug report and test case.
[16 Oct 2018 18:20] K Scrupps
We are experiencing same issue with ODBC 8.0.12: presence of a mediumtext field in a select query causes no results to be returned when using a parameter in the query.  Setting NO_SSPS=1 (client-side preparation) or using a parameter-less query returns results.
[28 Feb 2019 10:44] Bogdan Degtyariov
Posted by developer:
 
The bug is fixed in the version 8.0.16, but the fix is improved in 8.0.17.
[2 Apr 2019 22:44] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 5.3.13 / 8.0.16 releases, and here's the changelog entry that also stems from several other bug reports:

An exception was emitted when fetching contents of a BLOB/TEXT records
after executing a statement as a server-side prepared statement with a
bound parameter. 

The workaround is not using parameters or specifying NO_SSPS=1 in the 
connection string; this allows the driver to fetch the data.

Thank you for the bug report.
[28 Apr 2021 16:37] Ruud van den Hout
This issue sort of reintroduced itself with 5.3.14

Not op TEXT or MEDIUMTEXT, but on the PRIMARY KEY "ID".... you can use basically the same sample code! 

This is really crap! Cost me a few hours the realize what was going on, specifying NO_SSPS=1 in the connection string fixed this.
[28 Apr 2021 16:47] Ruud van den Hout
dim adoConn, objCommand, param, rs, strError

set adoConn = CreateObject("ADODB.Connection")
adoConn.Open "Driver={MySQL ODBC 5.3 Unicode Driver}; Server=localhost; Option=2; Database=test; Uid=root;Pwd=0104*dier;" 
if adoConn.errors.count <> 0 Then 
	For Each errLoop In adoConn.Errors  
	 strError = "Error #" & errLoop.Number & VbCrLf & _  
		"   " & errLoop.Description & VbCrLf & _  
		"   (Source: " & errLoop.Source & ")" & VbCrLf & _  
		"   (SQL State: " & errLoop.SQLState & ")" & VbCrLf & _  
		"   (NativeError: " & errLoop.NativeError & ")" & VbCrLf   
	 wscript.Echo( strError )  
	Next  
Else
	Show(1)
	Show(2)
end If
Err.Clear
On Error Goto 0

function Show(id)
	dim sql
	sql = "SELECT ID, title, content FROM `test` WHERE ID=?"
	Set objCommand = CreateObject("ADODB.Command")
	objCommand.ActiveConnection = adoConn
	objCommand.CommandText = sql
	objCommand.CommandType = 1
	Set param = objCommand.CreateParameter("@ID",3, 1)
	param.value = id
	objCommand.Parameters.Append param
	Set rs = objCommand.Execute()
	If Not rs.EOF Then
		wscript.Echo( "ID:" & Cint(rs("ID")) & VbCrLf & "Title:" & rs("title") & VbCrLf & "Content:" & rs("content") )
	End if
	Set rs = nothing
	Set param = nothing
	Set objCommand = nothing
End function

ID always returns: 0
[28 Apr 2021 16:52] Ruud van den Hout
Ooops, can you please Disguise the password in above example?