MySQL Bugs: #92078: Connector/ODBC bug when retrieving TEXT records using parameterized queries

Bug #92078	Connector/ODBC bug when retrieving TEXT records using parameterized queries
Submitted:	20 Aug 2018 9:53	Modified:	28 Apr 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

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

____________________________________________________________________________

FYI: this error does not occur when using non-parameterized queries or with 'NO_SSPS=1' set in connection string

Thank you for the bug report and test case.

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.

Posted by developer:
 
The bug is fixed in the version 8.0.16, but the fix is improved in 8.0.17.

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.

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.

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

Ooops, can you please Disguise the password in above example?