Description:
I am seeing (in some cases, sporadic) issues using bound parameters with the above ODBC connector.
Here is the query:
INSERT INTO PlayListNames ( Description, IncludeInLists, VoiceTrack, ScheduleDate, ScheduleTime, AutoSchedule, AutoSequence, AutoSeqPrimaryPlayListId, AutoSeqBackupPlayListId, ScheduleDays, ResetListOnSchedule, AutoSeqPrimaryLoop, AutoSeqBackupLoop, CloudUpload, WriteMetadataOnCloudUpload ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
There are 15 parameters, all bound correctly. Sporadically (say 1 in 8), this query will fail with the following error diagnostic:
[MySQL][ODBC 8.4(w) Driver][mysqld-8.0.37-0ubuntu0.22.04.3]Using unsupported buffer type: %d (parameter: %d)
However a subsequent re-execution of the same query (without touching the bindings) will normally then succeed.
A second instance (which I think may be the same problem) but which always triggers this problem is if I run the following query:
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)=?));
But with the same set of bound parameters ie. I've bound 15 but only 1 is needed. I think this is a legitimate thing to do, certainly I've seen no problems in the past with various ODBC connectors as in I would expect the remaining params to be ignored. Regardless, this will always trigger the above failure yet succeed if retried.
How to repeat:
Can be consistently repeated using a query which binds more parameters than are in the SQL.
Suggested fix:
I think this issue (in the same as #115531) may have been triggered by WL #15967 but there may also be something not quite right with the 8.3 connector as well.
In all cases, the error appears to be triggered by this procedure failing:
bind_failed =
mysql_stmt_bind_named_param(ssps, param_bind.data(),
p_number, query_attr_names.data());
p_number is set just prior to this call as follows:
unsigned int p_number =
dbc->mysql->server_capabilities & CLIENT_QUERY_ATTRIBUTES
? query_attr_names.size() : param_count;
With an 8.0x server, I would expect it to be set to query_attr_names.size(), which appears to be the case.
With the INSERT query, the first time the query is executed, the (I think) pertinent variables are:
query_attr_names.size() 18
param_count 15
p_number 18
However on the 2nd call they are:
query_attr_names.size() 16
param_count 15
p_number 16
So there is a discrepancy going on there. Of interest, there is also a slight discrepancy going on with the 8.3 connector:
First run:
p_number 16
stmt->query_attr_names.size() 16
stmt->param_count 15
Second run:
p_number 15
stmt->query_attr_names.size() 15
stmt->param_count 15
I've not seen a problem with 8.3 however it does trigger a runtime error (which can be ignored) with the debug builds of the connector on the first run. This I think is down to a memcpy attempting to copy 0 bytes to a NULL pointer - possibly that 16th (uninitialized?) parameter.
With the SELECT query, the difference between the two calls is more pronounced:
First run:
query_attr_names.size() 32
param_count 1
p_number 32
Second run:
query_attr_names.size() 16
param_count 1
p_number 16
With the 8.3 connector, this consistently uses what appear (to me anyway), sensible values:
p_number 1
stmt->query_attr_names.size() 1
stmt->param_count 1
To me it looks like the parameter count being passed into the SQL client library is over that actually supplied which means accessing data beyond the end of the supplied array. As a result, you then get inconsistent behavior depending on what sits in memory after that. Odds are just being one over tends to be ok but by a factor of two it's almost certain to trigger a problem.
Description: I am seeing (in some cases, sporadic) issues using bound parameters with the above ODBC connector. Here is the query: INSERT INTO PlayListNames ( Description, IncludeInLists, VoiceTrack, ScheduleDate, ScheduleTime, AutoSchedule, AutoSequence, AutoSeqPrimaryPlayListId, AutoSeqBackupPlayListId, ScheduleDays, ResetListOnSchedule, AutoSeqPrimaryLoop, AutoSeqBackupLoop, CloudUpload, WriteMetadataOnCloudUpload ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ); There are 15 parameters, all bound correctly. Sporadically (say 1 in 8), this query will fail with the following error diagnostic: [MySQL][ODBC 8.4(w) Driver][mysqld-8.0.37-0ubuntu0.22.04.3]Using unsupported buffer type: %d (parameter: %d) However a subsequent re-execution of the same query (without touching the bindings) will normally then succeed. A second instance (which I think may be the same problem) but which always triggers this problem is if I run the following query: 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)=?)); But with the same set of bound parameters ie. I've bound 15 but only 1 is needed. I think this is a legitimate thing to do, certainly I've seen no problems in the past with various ODBC connectors as in I would expect the remaining params to be ignored. Regardless, this will always trigger the above failure yet succeed if retried. How to repeat: Can be consistently repeated using a query which binds more parameters than are in the SQL. Suggested fix: I think this issue (in the same as #115531) may have been triggered by WL #15967 but there may also be something not quite right with the 8.3 connector as well. In all cases, the error appears to be triggered by this procedure failing: bind_failed = mysql_stmt_bind_named_param(ssps, param_bind.data(), p_number, query_attr_names.data()); p_number is set just prior to this call as follows: unsigned int p_number = dbc->mysql->server_capabilities & CLIENT_QUERY_ATTRIBUTES ? query_attr_names.size() : param_count; With an 8.0x server, I would expect it to be set to query_attr_names.size(), which appears to be the case. With the INSERT query, the first time the query is executed, the (I think) pertinent variables are: query_attr_names.size() 18 param_count 15 p_number 18 However on the 2nd call they are: query_attr_names.size() 16 param_count 15 p_number 16 So there is a discrepancy going on there. Of interest, there is also a slight discrepancy going on with the 8.3 connector: First run: p_number 16 stmt->query_attr_names.size() 16 stmt->param_count 15 Second run: p_number 15 stmt->query_attr_names.size() 15 stmt->param_count 15 I've not seen a problem with 8.3 however it does trigger a runtime error (which can be ignored) with the debug builds of the connector on the first run. This I think is down to a memcpy attempting to copy 0 bytes to a NULL pointer - possibly that 16th (uninitialized?) parameter. With the SELECT query, the difference between the two calls is more pronounced: First run: query_attr_names.size() 32 param_count 1 p_number 32 Second run: query_attr_names.size() 16 param_count 1 p_number 16 With the 8.3 connector, this consistently uses what appear (to me anyway), sensible values: p_number 1 stmt->query_attr_names.size() 1 stmt->param_count 1 To me it looks like the parameter count being passed into the SQL client library is over that actually supplied which means accessing data beyond the end of the supplied array. As a result, you then get inconsistent behavior depending on what sits in memory after that. Odds are just being one over tends to be ok but by a factor of two it's almost certain to trigger a problem.