Bug #93895 Prepared statements (server) return no results, client statements work
Submitted: 11 Jan 2019 17:17 Modified: 13 May 2019 12:29
Reporter: Graham Cotgreave Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:8.0.13 OS:Windows (MYODBC5ADLL)
Assigned to: Bogdan Degtyariov CPU Architecture:x86
Tags: connect, ODBC

[11 Jan 2019 17:17] Graham Cotgreave
Description:
Please see the thread https://forums.mysql.com/read.php?37,671084,671084#msg-671084

which describes how the same bound prepared statement fails when executed on the server side but works on the client side pre prepare.

Solutions suggested by https://forums.mysql.com/profile.php?37,296688
failed to fix and bug report is being submitted as suggested.

How to repeat:
/ ConsoleApplication1.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include "Windows.h"
#include "sqltypes.h"
#include "sql.h"

#include "sqlext.h"

#define MAX_TEAMNAME 40
#define MAX_USERNAME 40
#define MAX_SWITCHID 40
#define MAX_USERRREC 2092

typedef struct {
    SQLSMALLINT     Value_Type ;
    SQLSMALLINT     Parameter_Type ;
    size_t          Offset ;            /* Offset into the C structure at which
                                           the attribute is located. */
    size_t          Size ;              /* Size of the attribute in the C
                                           structure. */
} ODBC_BINDPARAM_T ;

typedef struct _ODBC_USER_T {
    char            Name [MAX_USERNAME+1] ;     SQLLEN  cbName ;
    char            Team [MAX_TEAMNAME+1] ;     SQLLEN  cbTeam ;
    char            SwitchID [MAX_SWITCHID+1] ; SQLLEN  cbSwitchID ;
    char            Data[MAX_USERRREC] ;        SQLLEN  cbData ;

    SQLLEN  count ;     /* For DELETE stored procedure. */
} ODBC_USER_T ;

SQLHANDLE ODBC_hEnv = SQL_NULL_HANDLE ;   /* Environment handle. */
SQLHANDLE   hDbc = SQL_NULL_HANDLE ;

char select_str[] = "select * from \"USER\" where  (\"NAME\" = ?) order by \"NAME\"" ;
ODBC_BINDPARAM_T bindparam ;
ODBC_USER_T user ;
char    temp[256] ;

extern bool ODBC_Init (void) ;

int main()
{
    int             rc ;
    int             col ;
    SQLHANDLE       hCursor ;

    if (! ODBC_Init ()) {
        return (-4) ;
    }

   /*
    * Define a forward-only cursor.
    */
    if (SQLAllocHandle (SQL_HANDLE_STMT, hDbc, &hCursor) !=
        SQL_SUCCESS) {
        return (-5) ;
    }
    if (SQLSetStmtAttr (hCursor, SQL_ATTR_CURSOR_TYPE,
        (SQLPOINTER)SQL_CURSOR_FORWARD_ONLY, 0) != SQL_SUCCESS) {
        return (-6) ;
    }

  /*
   * Set up row-wise binding to read results.
   * We only fetch one row at a time from the result set.
   */
    SQLSetStmtAttr (hCursor, SQL_ATTR_ROW_BIND_TYPE,
        (SQLPOINTER) sizeof (ODBC_USER_T), 0) ;
    SQLSetStmtAttr (hCursor, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)1, 0) ;

   /*
    * Bind columns to retrieve results via SQLFetch.
    */
    col = 1 ;
    SQLBindCol (hCursor, col++, SQL_C_CHAR, user.Name,
                sizeof (user.Name), &user.cbName) ;
    SQLBindCol (hCursor, col++, SQL_C_CHAR, user.Team,
                sizeof (user.Team), &user.cbTeam) ;
    SQLBindCol (hCursor, col++, SQL_C_CHAR, user.SwitchID,
                sizeof (user.SwitchID), &user.cbSwitchID) ;
    SQLBindCol (hCursor, col++, SQL_C_BINARY, user.Data,
                sizeof (user.Data), &user.cbData) ;

    sprintf (user.Name, "MANAGER") ;
    SQLBindParameter (hCursor, 1, SQL_PARAM_INPUT,
                      SQL_C_CHAR, SQL_CHAR, 0, 0,
                      user.Name, sizeof(user.Name),
                      NULL) ;
    SQLPrepare (hCursor, (SQLCHAR *)select_str, SQL_NTS) ;

    if (SQLExecute (hCursor) != SQL_SUCCESS) {
        SQLError (ODBC_hEnv, hDbc, hCursor, NULL, NULL,
                  (SQLCHAR *) temp, sizeof (temp), NULL) ;
        printf ("SQLExecute failed:\n%s\n", temp) ;
        return (-1) ;
    }
    rc = SQLFetch (hCursor) ;
    if (rc == SQL_SUCCESS) {
            //Hurah
       printf ("This works with 'client prepare'\n") ;
    }
    else if (rc == SQL_NO_DATA) {
        printf ("This is the Error case when the 'prepare is run'\n") ;
        return (-2) ;
    }

    return 0;
}

bool ODBC_Init (void)
{

    SQLHANDLE        hStmt ;

    if (ODBC_hEnv == SQL_NULL_HANDLE) {
/*
 * Initalise the library.
 */
        if (SQLAllocEnv (&ODBC_hEnv) != SQL_SUCCESS) {
            ///ARRGGHHH
            return (false) ;
        }
    }

    if (SQLAllocConnect (ODBC_hEnv, &hDbc) == SQL_SUCCESS) {
        if (SQLConnect (hDbc,
                        (SQLCHAR *) "MySQL ODBC 8.0", SQL_NTS,
                        (SQLCHAR *) "a_user", SQL_NTS,
                        (SQLCHAR *) "a_password", SQL_NTS) != SQL_SUCCESS) {
            SQLError (ODBC_hEnv, hDbc, SQL_NULL_HSTMT, NULL, NULL,
                (SQLCHAR *)temp, sizeof (temp), NULL) ;
            printf ("SQLConnect failed:\n%s\n", temp) ;
            return (false) ;
        }
    }
    if  (SQLAllocHandle (SQL_HANDLE_STMT, hDbc, &hStmt) != SQL_SUCCESS) {
        SQLError (ODBC_hEnv, hDbc, hStmt, NULL, NULL,
            (SQLCHAR *)temp, sizeof (temp), NULL) ;
        printf ("SQLAllocHandle failed:\n%s\n", temp) ;
        return (false) ;
    }
    if (SQLExecDirect (hStmt, (SQLCHAR *)"use \"mydbase\" ;", SQL_NTS) != SQL_SUCCESS) {
         SQLError (ODBC_hEnv, hDbc, SQL_NULL_HSTMT, NULL, NULL,
             (SQLCHAR *)temp, sizeof (temp), NULL) ;
         printf ("SQLExecDirect failed:\n%s\n", temp) ;
         return (false) ;
     }
     return (true) ;
}   /* ODBC_Init */

////// Table Definition

CREATE TABLE "USER" (
  "NAME" varchar(40) COLLATE utf8_bin NOT NULL,
  "TEAM" varchar(40) COLLATE utf8_bin DEFAULT NULL,
  "SWITCHID" varchar(60) COLLATE utf8_bin DEFAULT NULL,
  "DATA" blob,
  PRIMARY KEY ("NAME")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

///////
Results from Wireshark

//////Protocol seen from bound parameters sent to MySQL engine

MySQL Protocol prepare request
    Packet Length: 57
    Packet Number: 0
    Request Command Prepare Statement
        Command: Prepare Statement (22)
        Statement: select * from `USER` where  (`NAME` = ?) order by `NAME`

then the parameter
MySQL Protocol
    Packet Length: 22
    Packet Number: 0
    Request Command Execute Statement
        Command: Execute Statement (23)
        Statement ID: 1
        Flags: Defaults (0)
        Iterations (unused): 1
        New parameter bound flag: First call or rebound (1)
        Parameter
            Type: FIELD_TYPE_STRING (254)
            Unsigned: 0
            Value: MANAGER

Then the result which appears correct

MySQL Protocol
    Packet Length: 1
    Packet Number: 1
    Number of fields: 4

MySQL Protocol
    Packet Length: 53
    Packet Number: 2
    Catalog: def
    Database: cradle_prj_eeee
    Table: USER
    Original table: USER
    Name: NAME
    Original name: NAME
    Charset number: utf8 COLLATE utf8_bin (83)
    Length: 120
    Type: FIELD_TYPE_VAR_STRING (253)
    Flags: 0x5083
    Decimals: 0

MySQL Protocol
    Packet Length: 53
    Packet Number: 3
    Catalog: def
    Database: cradle_prj_eeee
    Table: USER
    Original table: USER
    Name: TEAM
    Original name: TEAM
    Charset number: utf8 COLLATE utf8_bin (83)
    Length: 120
    Type: FIELD_TYPE_VAR_STRING (253)
    Flags: 0x0080
    Decimals: 0

MySQL Protocol
    Packet Length: 61
    Packet Number: 4
    Catalog: def
    Database: cradle_prj_eeee
    Table: USER
    Original table: USER
    Name: SWITCHID
    Original name: SWITCHID
    Charset number: utf8 COLLATE utf8_bin (83)
    Length: 180
    Type: FIELD_TYPE_VAR_STRING (253)
    Flags: 0x0080
    Decimals: 0

MySQL Protocol
    Packet Length: 53
    Packet Number: 5
    Catalog: def
    Database: cradle_prj_eeee
    Table: USER
    Original table: USER
    Name: DATA
    Original name: DATA
    Charset number: binary COLLATE binary (63)
    Length: 65535
    Type: FIELD_TYPE_BLOB (252)
    Flags: 0x0090
    Decimals: 0

MySQL Protocol
    Packet Length: 2115
    Packet Number: 6
    Affected Rows: 0
    Last INSERT ID: 7
    Server Status: 0x414d
        .... .... .... ...1 = In transaction: Set
        .... .... .... ..0. = AUTO_COMMIT: Not set
        .... .... .... .1.. = More results: Set
        .... .... .... 1... = Multi query - more resultsets: Set
        .... .... ...0 .... = Bad index used: Not set
        .... .... ..0. .... = No index used: Not set
        .... .... .1.. .... = Cursor exists: Set
        .... .... 0... .... = Last row sent: Not set
        .... ...1 .... .... = database dropped: Set
        .... ..0. .... .... = No backslash escapes: Not set
        .... .0.. .... .... = Session state changed: Not set
        .... 0... .... .... = Query was slow: Not set
        ...0 .... .... .... = PS Out Params: Not set
    Warnings: 16718
    Message: ER\aPROJECT\001 \357\277\275,\bMANAGER                                 S\357\277\275\357\277\275\357\277\275\357\277\275\a\357\277\275)z2\357\277\275(\357\277\275M4\357\277\275
    Payload: d55dde388dc54f1060cb5d52b6284f0dac1a8ff49440259c...

MySQL Protocol
    Packet Length: 7
    Packet Number: 7
    EOF marker: 254
    Warnings: 0
    Server Status: 0x0002
        .... .... .... ...0 = In transaction: Not set
        .... .... .... ..1. = AUTO_COMMIT: Set
        .... .... .... .0.. = More results: Not set
        .... .... .... 0... = Multi query - more resultsets: Not set
        .... .... ...0 .... = Bad index used: Not set
        .... .... ..0. .... = No index used: Not set
        .... .... .0.. .... = Cursor exists: Not set
        .... .... 0... .... = Last row sent: Not set
        .... ...0 .... .... = database dropped: Not set
        .... ..0. .... .... = No backslash escapes: Not set
        .... .0.. .... .... = Session state changed: Not set
        .... 0... .... .... = Query was slow: Not set
        ...0 .... .... .... = PS Out Params: Not set
    Payload: 0000

Nothing arrives back at the client.

Now using the driver setting to prepare on client

Command seen going to MySQL

Request Command Query
    Command: Query (3)
    Statement: select * from `USER` where  (`NAME` = 'MANAGER') order by `NAME`

And the response

MySQL Protocol
    Packet Length: 1
    Packet Number: 1
    Number of fields: 4

MySQL Protocol
    Packet Length: 53
    Packet Number: 2
    Catalog: def
    Database: cradle_prj_eeee
    Table: USER
    Original table: USER
    Name: NAME
    Original name: NAME
    Charset number: utf8 COLLATE utf8_bin (83)
    Length: 120
    Type: FIELD_TYPE_VAR_STRING (253)
    Flags: 0x5083
    Decimals: 0

MySQL Protocol
    Packet Length: 53
    Packet Number: 3
    Catalog: def
    Database: cradle_prj_eeee
    Table: USER
    Original table: USER
    Name: TEAM
    Original name: TEAM
    Charset number: utf8 COLLATE utf8_bin (83)
    Length: 120
    Type: FIELD_TYPE_VAR_STRING (253)
    Flags: 0x0080
    Decimals: 0

MySQL Protocol
    Packet Length: 61
    Packet Number: 4
    Catalog: def
    Database: cradle_prj_eeee
    Table: USER
    Original table: USER
    Name: SWITCHID
    Original name: SWITCHID
    Charset number: utf8 COLLATE utf8_bin (83)
    Length: 180
    Type: FIELD_TYPE_VAR_STRING (253)
    Flags: 0x0080
    Decimals: 0

MySQL Protocol
    Packet Length: 53
    Packet Number: 5
    Catalog: def
    Database: cradle_prj_eeee
    Table: USER
    Original table: USER
    Name: DATA
    Original name: DATA
    Charset number: binary COLLATE binary (63)
    Length: 65535
    Type: FIELD_TYPE_BLOB (252)
    Flags: 0x0090
    Decimals: 0

MySQL Protocol
    Packet Length: 2113
    Packet Number: 6
    Catalog: MANAGER
    Database: PROJECT
    Table: 
    Original table [truncated]: MANAGER                                 S\357\277\275\357\277\275\357\277\275\357\277\275\a\357\277\275)z2\357\277\275(\357\277\275M4\357\277\275\357\277\275]\357\277\2758\357\277\275\357\277\275O\020`\357\277\

[Malformed Packet: MySQL]
    [Expert Info (Error/Malformed): Malformed Packet (Exception occurred)]

MySQL Protocol
    Packet Length: 7
    Packet Number: 7
    EOF marker: 254
    Warnings: 0
    Server Status: 0x0002
        .... .... .... ...0 = In transaction: Not set
        .... .... .... ..1. = AUTO_COMMIT: Set
        .... .... .... .0.. = More results: Not set
        .... .... .... 0... = Multi query - more resultsets: Not set
        .... .... ...0 .... = Bad index used: Not set
        .... .... ..0. .... = No index used: Not set
        .... .... .0.. .... = Cursor exists: Not set
        .... .... 0... .... = Last row sent: Not set
        .... ...0 .... .... = database dropped: Not set
        .... ..0. .... .... = No backslash escapes: Not set
        .... .0.. .... .... = Session state changed: Not set
        .... 0... .... .... = Query was slow: Not set
        ...0 .... .... .... = PS Out Params: Not set
    Payload: 0000

Resultant value seen at client.
[17 Jan 2019 13:21] Bogdan Degtyariov
Hi Graham,

Thank you for a very detailed report with the C++ test case and the protocol trace. We really appreciated how well it is prepared with plenty of information.

Before going further I need to clarify one detail:
your server is running with 'ANSI_QUOTES' SQL Mode. What other SQL Modes are used? I cannot see the test program setting any SQL Modes for the connection, so it must be configured on the server.
Thanks.
[17 Jan 2019 14:37] Graham Cotgreave
The MySQL installation is out of 'the box'.

Running    select @@sql_mode ;
Gives      ANSI_QUOTES

No other settings have been altered, as far as I know.
[24 Jan 2019 7:13] Bogdan Degtyariov
Hi Graham,

Unfortunately, repeating the problem you reported was not a trivial task.

I was only able to get SQL_NO_DATA status if the table had no rows matching the criteria (NAME='MANAGER').
After inserting this row I was able to fetch it later (fragment from the general query log):

2019-01-24T06:47:59.920602Z        10 Prepare   INSERT INTO `test`.`user` (`NAME`, `TEAM`, `SWITCHID`, `DATA`) VALUES ('MANAGER', 'Developers', 'Something', ?)
2019-01-24T06:47:59.921102Z        10 Long Data
2019-01-24T06:47:59.921102Z        10 Execute   INSERT INTO `test`.`user` (`NAME`, `TEAM`, `SWITCHID`, `DATA`) VALUES ('MANAGER', 'Developers', 'Something', 'Lorem ipsum dolor sit amet')

Your program succeeded with both client and server cursors.
Here is what I had in the server general query log for the server-side prepared statement (you can see two stages Prepare and Execute):

2019-01-24T06:52:04.391146Z        11 Prepare   select * from "USER" where  ("NAME" = ?) order by "NAME"

2019-01-24T06:52:04.391146Z        11 Execute   select * from "USER" where  ("NAME" = 'MANAGER') order by "NAME"

Here is the entry corresponding to the client-side prepared statement (No Prepare/Execute commands, but Query instead):

2019-01-24T06:54:49.952170Z        12 Query     select * from "USER" where  ("NAME" = 'MANAGER') order by "NAME"

Both times the test worked correctly (rc == SQL_SUCCESS).
Maybe the row I inserted wasn't the right one to cause the error.

So, I will ask a few extra pieces of information from you:

 1. The data row (INSERT statement from mysqldump command will do) that is supposed to match the criteria for the SELECT.

 2. The fragment of the server general query log showing the actual Prepare and Execute queries with the data in a similar way I showed above.

 3. The DSN contents for your data source as the output from the myodbc-installer command.
    For example, my DSN named as "test_8_ansi" has the following details:

C:\Program Files (x86)\MySQL\Connector ODBC 8.0>myodbc-installer.exe -s -l -c2 -n "test_8_ansi"
Name:                test_8_ansi
Driver:              C:\Program Files (x86)\MySQL\Connector ODBC 8.0\myodbc8a.dll
Server:              localhost
Uid:                 ********
Pwd:                 ********
Database:            test
Port:                3306
Options:

IMPORTANT: please make sure you asterisk ***** or completely remove the password in the output (line with Pwd: ) before sending it to us.

Thanks.
[24 Jan 2019 13:16] Graham Cotgreave
The Export SQL for the table data
---------------------------------
Insert into "myprj"."USER" (NAME,TEAM,SWITCHID) values ('MANAGER','PROJECT',' ');

The Gen Log Entries for Client prepared then server prepared statements
------------------------------------------------------------------------
2019-01-24T12:55:29.255182Z        33 Query     select * from "USER" where  ("NAME" = 'MANAGER') order by "NAME"

2019-01-24T13:00:02.269418Z        34 Prepare   select * from "USER" where  ("NAME" = ?) order by "NAME"
2019-01-24T13:00:02.679843Z        34 Execute   select * from "USER" where  ("NAME" = 'MANAGER') order by "NAME"

The Driver info
---------------
C:\Program Files\MySQL\Connector ODBC 8.0>myodbc-installer.exe -s -l -c1 -n "MySQL ODBC 8.0 Test"
Name:                MySQL ODBC 8.0 Test
Driver:              C:\Program Files\MySQL\Connector ODBC 8.0\myodbc8w.dll
Description:         Test MySQL connection
Server:              10.0.3.100
Character set:       UTF8
Port:                3306
Options:

Will attach file with the data dump including the BLOB
[30 Jan 2019 7:53] Bogdan Degtyariov
Thank you Graham.
I was able to repeat the problem with the non-NULL data in the BLOB column.
If data length is 0 or 1 the not-found status is not returned.
[30 Jan 2019 8:02] Bogdan Degtyariov
Looks like there is an issue when ODBC driver is trying to fetch the contents of BLOB after executing statement as a server-side prepared statement with parameter.
[26 Feb 2019 5:06] Bogdan Degtyariov
Posted by developer:
 
The issue is fixed in the version 8.0.16
[8 Mar 2019 22:34] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.16 release, and here's the changelog entry:

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

Thank you for the bug report.
[10 May 2019 14:04] Graham Cotgreave
Having tested the latest driver (8.0.16) in our environment, it appears to have fixed the problem with the initial (supplied) case, a single read on a table with text and a blob column.

However, if we have a number of rows, reading the first matching result suffers a similar problem. Switching the client side prepare back 'on' resolves the issue.

Our initial example used a table with MANAGER as a user name and a blob holding info about the user. Now with ADAM, BOB, CHARLIE and MANAGER defined, trying to select ADAM fails, whereas BOB, CHARLIE and MANAGER work.

This appears to be closely related to this bug report. Should this be a reinvestigation of the current issue, or raised as a new bug?
[13 May 2019 10:04] Bogdan Degtyariov
Hi Graham,

Thank you for the feedback.
Unfortunately, the description of this new problem does not provide enough information to diagnose it. Do I understand it correct that the version 8.0.16 worked for you if it was just one row in the result, but same issue happened if multiple rows were returned?

In any case it calls for a new bug report.
Thanks.
[13 May 2019 12:29] Graham Cotgreave
Hello Bogdan,

Yes you are correct, with only one row in the table all appeared fine. With multiple rows in the table, it appears that the 'first' row suffers a similar issue to the original problem. 
With the primary key set to ADAM, BOB, CHARLIE and MANAGER with a BLOB column on each row, we could not successfully read the ADAM entry, but could read BOB, CHARLIE and MANAGER. 
Really odd behaviour. 

I will attempt to get more information on the failure and see what is going on over the network, and raise a new report as you suggest. 

Regards
Graham