OUT parameters don't work for some users

Question

I have an Excel worksheet that needs to make a number of calls out to a MySQL server. Here's the VBA code, simplified to illustrate this issue:

Public Function Connect() As ADODB.Connection
    Dim cn As New ADODB.Connection
    With cn
        .ConnectionString = "<Server>"
        .Properties("Initial Catalog").Value = "<DB>"
        .Properties("User ID").Value = "<User>"
        .Properties("Password").Value = "<Password>"
        .CommandTimeout = 1500
    End With
    Set Connect = cn
End Function

Public Sub TestSProc()
    Dim cxn As ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim p1 As ADODB.Parameter
    Dim p2 As ADODB.Parameter
    Set cxn = Connect()
    cxn.Open
    Set cmd.ActiveConnection = cxn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "OutParamTest"
    Set p1 = cmd.CreateParameter(, adBoolean, adParamOutput)
    Set p2 = cmd.CreateParameter(, adVarChar, adParamOutput, 255)
    cmd.Parameters.Append p1
    cmd.Parameters.Append p2
    cmd.Execute
    MsgBox "Result: " & p1.Value & " / " & p2.Value
End Sub

This executes the following stored procedure:

CREATE PROCEDURE `OutParamTest`(
    OUT `isAvailable` BIT,
    OUT `remarks` VARCHAR(255)
)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
  SET isAvailable = false;
  SET remarks = 'The quick brown fox jumps over the lazy dog.';
END

On my machine, and for almost all users, this returns:

Result: False / The quick brown fox jumps over the lazy dog.

But for one user, it returns:

Result: True /

The only difference I can find between my machine, and the user's is the MySQL ODBC 5.3 ANSI and Unicode Driver versions. I have version 5.03.02.00, and originally this user had an older version. I asked him to upgrade, and he now has version 5.03.09.00 (newer than mine), but the problem still exists.

Is there something I'm missing? Is there some kind of machine-level configuration variable that could cause OUT parameters to stop working? Or do OUT parameters only work in some select driver versions?

UPDATE: I modified the SP as follows:

CREATE PROCEDURE `OutParamTest`(
    OUT `num` INT,
    OUT `remarks` VARCHAR(255)
)
BEGIN
  SET num = 12345;
  SET remarks = 'The quick brown fox jumps over the lazy dog.';
END

And made the corresponding changes in VBA. Now on my machine it returns:

Result: 12345 / The quick brown fox jumps over the lazy dog.

And on this user's machine it returns:

Result: 232718680 /

I fail to see how this MCVE could ever return True for p1, but it's an interesting question regardless. — Mathieu Guindon
@Mat'sMug Agreed, that is odd. My working hypothesis on that is that somewhere along the line, it ends up trying to do a type conversion. — p.s.w.g
I'm not very familiar with MySQL, but I presume Set isAvailable = false is setting it to 0 (assuming false is defined as such somewhere). Does explicitly making it 0 change the outcome? Making the stored procedure return a constant / explicit 0 would rule out the possibility of the SQL being the problem (...although, assuming all users connect to the same server, that should already be ruled out) — Mathieu Guindon
@Mat'sMug That doesn't change the results. However, when I changed the parameter to an INT, I got much more interesting results. See my updated question. To my eye, it looks like it could be returning a pointer rather than the actual value. — p.s.w.g

p.s.w.g p.s.w.g · Accepted Answer · 2018-01-19T17:01:39

@wchiquito pointed out that this is likely related to MySQL Bug #83698, and even though I've been unable to replicate the precise behavior elsewhere (due to my company's peculiar network set up and security restrictions) I agree that this is most likely the culprit.

Since asking this question, I've re-written the stored procedure and associated spreadsheet to return data only via a Recordset. It's not nearly as 'clean', IMO, but at least it works on all users' machines. Still, if anyone can find a way to get OUT params to work (or if MySQL ever decides to fix this bug) please let me know.

OUT parameters don't work for some users

1 Answers