| Bug #115531 | Bound parameter queries fail with MySQL 5.7 server | ||
|---|---|---|---|
| Submitted: | 7 Jul 2024 15:12 | Modified: | 19 Jul 2024 22:41 |
| Reporter: | Jon Bird | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 8.4, 9.0 | OS: | Windows (64-bit) |
| Assigned to: | CPU Architecture: | x86 | |
[8 Jul 2024 12:12]
MySQL Verification Team
Hello Jon, Thank you for the bug report. Please note that MySQL 5.7 is covered under Oracle Lifetime Sustaining Support. For more info, please see https://www.mysql.com/support/eol-notice.html Regards, Ashwini Patil
[8 Jul 2024 18:59]
Jon Bird
Hi Ashwini, Point taken regarding the support status for MySQL server 5.7 but in this instance it appears to an issue with the connector itself. The "info" page on the ODBC download page says "The latest MySQL Connector/ODBC version is recommended for use with MySQL Server version 5.7 and higher." which implies it should work with 5.7. From my perspective, this is more an observation than anything, I don't have any particular need for this particular use case. If there is no desire or appetite to maintain compatibility then an easier "fix" is simply to change the information on that page to reflect that 8.4 & later only work with MySQL server 8.0x. That would then at least hopefully stop people bumping into this issue. Slightly aside, I think there is another issue with this same change that went into 8.4 which is causing sporadic failures with 8.0x servers, I just haven't had the time yet to quantify it. Rgs, Jon.
[11 Jul 2024 5:22]
Bogdan Degtyariov
This bug is verified. The ODBC Driver has the issue as described in the bug report.
[11 Jul 2024 8:40]
Bogdan Degtyariov
Hi Jon, Thank you for reporting the problem in many details including pointing out to incorrect information about supported MySQL Server versions in our online pages. Even through the latest Connector/ODBC does not support MySQL Server 5.7 due to its End Of Life, we are trying to maintain a reasonable level of compatibility when possible (which is not always the case, but in this instance it is possible). We created a patch, which fixes this problem. However, in according to our policies we cannot promise about the particular ODBC driver versions where the patch might be published. The online documentation and information about GA version of Connector/ODBC will be reviewed and corrected to reflect the latest changes.
[18 Jul 2024 6:17]
Bogdan Degtyariov
Posted by developer: The patch for this issue has been pushed into the source tree and tested against MySQL 5.7.
[19 Jul 2024 22:41]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Connector/ODBC 9.1.0 release, and here's the proposed changelog entry from the documentation team: --- When connected to a MySQL 5.7 server, queries using bound parameters would not succeed and emitted a "No data supplied for parameters in prepared statement" error. --- In addition, the documentation was updated to better reflect official 8.0+ support. Thank you for the bug report.
Description: When connected to a MySQL 5.7x database, queries which use bound parameters fail with the diagnostic error coming back as: [mysqld-5.7.44-0ubuntu0.18.04.1+esm1]No data supplied for parameters in prepared statement Here's the ODBC log leading up to this: playoutdb 2dbc-2cdc ENTER SQLFreeStmt HSTMT 0x0000023752B16AA0 UWORD 2 <SQL_UNBIND> playoutdb 2dbc-2cdc EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 0x0000023752B16AA0 UWORD 2 <SQL_UNBIND> playoutdb 2dbc-2cdc ENTER SQLFreeStmt HSTMT 0x0000023752B16AA0 UWORD 3 <SQL_RESET_PARAMS> playoutdb 2dbc-2cdc EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 0x0000023752B16AA0 UWORD 3 <SQL_RESET_PARAMS> playoutdb 2dbc-2cdc ENTER SQLBindParameter HSTMT 0x0000023752B16AA0 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD -8 <SQL_C_WCHAR> SWORD -9 <SQL_WVARCHAR> SQLULEN 255 SWORD 0 PTR 0x00007FF709A51174 SQLLEN 510 SQLLEN * 0x00007FF709A4FDE0 playoutdb 2dbc-2cdc EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) HSTMT 0x0000023752B16AA0 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD -8 <SQL_C_WCHAR> SWORD -9 <SQL_WVARCHAR> SQLULEN 255 SWORD 0 PTR 0x00007FF709A51174 SQLLEN 510 SQLLEN * 0x00007FF709A4FDE0 (-3) playoutdb 2dbc-2cdc ENTER SQLExecDirectW HSTMT 0x0000023752B16AA0 WCHAR * 0x00007FF709A54170 [ -3] "SELECT PlayListNames.PlayListId, PlayListNames.Description, PlayListNames.IncludeInLists, PlayListNames.VoiceTrack, PlayListNames.ScheduleDate, PlayListNames.ScheduleTime, PlayListNames.AutoSchedule, PlayListNames.AutoSequence, PlayListNames.AutoSeqPrimaryPlayListId ,PlayListNames.AutoSeqBackupPlayListId, PlayListNames.ScheduleDays, PlayListNames.ResetListOnSchedule, PlayListNames.AutoSeqPrimaryLoop, PlayListNames.AutoSeqBackupLoop, PlayListNames.CloudUpload, PlayListNames.WriteMetadataOnCloudUpload FROM PlayListNames WHERE (((PlayListNames.Description)=?));\ 0" SDWORD -3 playoutdb 2dbc-2cdc EXIT SQLExecDirectW with return code -1 (SQL_ERROR) HSTMT 0x0000023752B16AA0 WCHAR * 0x00007FF709A54170 [ -3] "SELECT PlayListNames.PlayListId, PlayListNames.Description, PlayListNames.IncludeInLists, PlayListNames.VoiceTrack, PlayListNames.ScheduleDate, PlayListNames.ScheduleTime, PlayListNames.AutoSchedule, PlayListNames.AutoSequence, PlayListNames.AutoSeqPrimaryPlayListId ,PlayListNames.AutoSeqBackupPlayListId, PlayListNames.ScheduleDays, PlayListNames.ResetListOnSchedule, PlayListNames.AutoSeqPrimaryLoop, PlayListNames.AutoSeqBackupLoop, PlayListNames.CloudUpload, PlayListNames.WriteMetadataOnCloudUpload FROM PlayListNames WHERE (((PlayListNames.Description)=?));\ 0" SDWORD -3 DIAG [HY000] [MySQL][ODBC 8.4(w) Driver][mysqld-5.7.44-0ubuntu0.18.04.1+esm1]No data supplied for parameters in prepared statement (2031) How to repeat: Run any query with a bound parameter Suggested fix: Doing a bit of debug, I believe this issue was caused by the following fix in the 8.4 release: Setting query attributes for executed queries now supports prepared statements in SSPS mode. (WL #15967) This changed the logic around do_query & bind_query_attrs with the result that a call is now made into the latter which then immediately trips this logic: else if (!dbc->has_query_attrs) { return set_error(MYERR_01000, "The server does not support query attributes", 0); } As a result, the final error is then misleading, it is presumably overwriting the above later on.