Description:
When attempting to update a record using a multi-column primary key on a table that also has indexes defined for the columns found in the primary key, the update fails.
How to repeat:
See the attached Visual Studio 6.0 project to reproduce this bug.
(Update CChildView::OnTest() with your own connection settings.)
Use this database to test this bug:
CREATE DATABASE test;
USE test;
CREATE TABLE key_test (
PRIMARY KEY (key_1, key_2, key_3),
key_2 int not null,
index(key_2),
key_1 int not null,
index(key_1),
key_3 int not null,
data smallint
);
Looking at the sql.log you will see the following after running the code:
UPDATE `key_test` SET `data`=3 WHERE `key_1`=2 AND `key_2`=2 AND `key_3`=2 AND `key_2`=1 AND `key_1`=1 LIMIT 1
Clearly no records will be found with this statement. The last two parameters are found when MyODBC retrieves the first record in the database when it fails to match the other indexes.
Suggested fix:
Simply reversing a nested table in the following function should correct this issue. I have noted the changes with original code in the comments.
Using this code changes the update statement to:
UPDATE `key_test` SET `data`=3 WHERE `key_1`=2 AND `key_2`=2 AND `key_3`=2 AND `key_2`=2 AND `key_1`=2 LIMIT 1
static SQLRETURN insert_pk_fields(STMT FAR *stmt, DYNAMIC_STRING *dynQuery)
{
MYSQL_RES *result= stmt->result;
MYSQL_FIELD *field;
SQLUSMALLINT ncol;
uint index;
MYCURSOR *cursor= &stmt->cursor;
SQLUINTEGER pk_count= 0;
/*
Look for primary key columns in the current result set,
if it exists, take that data else query new resultset
*/
// Comment: This fix is required to search all returned fields
// for indexes that may include duplicate field names.
// PHH modified from:
// for (ncol= 0; ncol < result->field_count; ncol++)
// {
// field= result->fields+ncol;
// for (index= 0; index < cursor->pk_count; index++)
// to:
for (index= 0; index < cursor->pk_count; index++)
{
for (ncol= 0; ncol < result->field_count; ncol++)
{
field= result->fields+ncol;
if (!myodbc_strcasecmp(cursor->pkcol[index].name,field->name))
{
// PK data exists...
dynstr_append_quoted_name(dynQuery,field->name);
dynstr_append_mem(dynQuery,"=",1);
if (insert_field(stmt,result,dynQuery,ncol))
return(SQL_ERROR);
cursor->pkcol[index].bind_done= TRUE;
pk_count++;
break;
}
}
}
if (pk_count != cursor->pk_count)
{
/*
Primary key column doesn't exists in the opened rs, so
get the data by executing a query
*/
DYNAMIC_STRING query;
MYSQL_RES *presult;
SQLUSMALLINT field_count= 0;
if (init_dynamic_string(&query, "SELECT ", 1024,1024))
return set_error(stmt,MYERR_S1001,NULL,4001);
for (index= 0; index < cursor->pk_count; index++)
{
if (!cursor->pkcol[index].bind_done)
{
dynstr_append_quoted_name(&query,stmt->cursor.pkcol[index].name);
dynstr_append_mem(&query,",",1);
}
}
query.length-= 1;
dynstr_append_mem(&query," FROM ",6);
if (!find_used_table(stmt))
{
dynstr_free(&query);
return(SQL_ERROR);
}
dynstr_append_quoted_name(&query,stmt->table_name);
MYLOG_QUERY(stmt, query.str);
pthread_mutex_lock(&stmt->dbc->lock);
if (mysql_query(&stmt->dbc->mysql,query.str) ||
!(presult= mysql_store_result(&stmt->dbc->mysql)))
{
set_error(stmt,MYERR_S1000,mysql_error(&stmt->dbc->mysql),
mysql_errno(&stmt->dbc->mysql));
pthread_mutex_unlock(&stmt->dbc->lock);
dynstr_free(&query);
return(SQL_ERROR);
}
pthread_mutex_unlock(&stmt->dbc->lock);
for (index= 0;index< (uint) stmt->current_row;index++)
presult->data_cursor= presult->data_cursor->next;
for (index= 0; index < cursor->pk_count; index++)
{
if (!cursor->pkcol[index].bind_done)
{
dynstr_append_quoted_name(dynQuery,cursor->pkcol[index].name);
dynstr_append_mem(dynQuery,"=",1);
/*
Might have multiple pk fields in the missing list ..
so avoid the wrong query by having internal field_count..
*/
if (insert_field(stmt,presult,dynQuery,field_count++))
{
mysql_free_result(presult);
dynstr_free(&query);
return(SQL_ERROR);
}
}
}
mysql_free_result(presult);
dynstr_free(&query);
}
return(SQL_SUCCESS);
}
Description: When attempting to update a record using a multi-column primary key on a table that also has indexes defined for the columns found in the primary key, the update fails. How to repeat: See the attached Visual Studio 6.0 project to reproduce this bug. (Update CChildView::OnTest() with your own connection settings.) Use this database to test this bug: CREATE DATABASE test; USE test; CREATE TABLE key_test ( PRIMARY KEY (key_1, key_2, key_3), key_2 int not null, index(key_2), key_1 int not null, index(key_1), key_3 int not null, data smallint ); Looking at the sql.log you will see the following after running the code: UPDATE `key_test` SET `data`=3 WHERE `key_1`=2 AND `key_2`=2 AND `key_3`=2 AND `key_2`=1 AND `key_1`=1 LIMIT 1 Clearly no records will be found with this statement. The last two parameters are found when MyODBC retrieves the first record in the database when it fails to match the other indexes. Suggested fix: Simply reversing a nested table in the following function should correct this issue. I have noted the changes with original code in the comments. Using this code changes the update statement to: UPDATE `key_test` SET `data`=3 WHERE `key_1`=2 AND `key_2`=2 AND `key_3`=2 AND `key_2`=2 AND `key_1`=2 LIMIT 1 static SQLRETURN insert_pk_fields(STMT FAR *stmt, DYNAMIC_STRING *dynQuery) { MYSQL_RES *result= stmt->result; MYSQL_FIELD *field; SQLUSMALLINT ncol; uint index; MYCURSOR *cursor= &stmt->cursor; SQLUINTEGER pk_count= 0; /* Look for primary key columns in the current result set, if it exists, take that data else query new resultset */ // Comment: This fix is required to search all returned fields // for indexes that may include duplicate field names. // PHH modified from: // for (ncol= 0; ncol < result->field_count; ncol++) // { // field= result->fields+ncol; // for (index= 0; index < cursor->pk_count; index++) // to: for (index= 0; index < cursor->pk_count; index++) { for (ncol= 0; ncol < result->field_count; ncol++) { field= result->fields+ncol; if (!myodbc_strcasecmp(cursor->pkcol[index].name,field->name)) { // PK data exists... dynstr_append_quoted_name(dynQuery,field->name); dynstr_append_mem(dynQuery,"=",1); if (insert_field(stmt,result,dynQuery,ncol)) return(SQL_ERROR); cursor->pkcol[index].bind_done= TRUE; pk_count++; break; } } } if (pk_count != cursor->pk_count) { /* Primary key column doesn't exists in the opened rs, so get the data by executing a query */ DYNAMIC_STRING query; MYSQL_RES *presult; SQLUSMALLINT field_count= 0; if (init_dynamic_string(&query, "SELECT ", 1024,1024)) return set_error(stmt,MYERR_S1001,NULL,4001); for (index= 0; index < cursor->pk_count; index++) { if (!cursor->pkcol[index].bind_done) { dynstr_append_quoted_name(&query,stmt->cursor.pkcol[index].name); dynstr_append_mem(&query,",",1); } } query.length-= 1; dynstr_append_mem(&query," FROM ",6); if (!find_used_table(stmt)) { dynstr_free(&query); return(SQL_ERROR); } dynstr_append_quoted_name(&query,stmt->table_name); MYLOG_QUERY(stmt, query.str); pthread_mutex_lock(&stmt->dbc->lock); if (mysql_query(&stmt->dbc->mysql,query.str) || !(presult= mysql_store_result(&stmt->dbc->mysql))) { set_error(stmt,MYERR_S1000,mysql_error(&stmt->dbc->mysql), mysql_errno(&stmt->dbc->mysql)); pthread_mutex_unlock(&stmt->dbc->lock); dynstr_free(&query); return(SQL_ERROR); } pthread_mutex_unlock(&stmt->dbc->lock); for (index= 0;index< (uint) stmt->current_row;index++) presult->data_cursor= presult->data_cursor->next; for (index= 0; index < cursor->pk_count; index++) { if (!cursor->pkcol[index].bind_done) { dynstr_append_quoted_name(dynQuery,cursor->pkcol[index].name); dynstr_append_mem(dynQuery,"=",1); /* Might have multiple pk fields in the missing list .. so avoid the wrong query by having internal field_count.. */ if (insert_field(stmt,presult,dynQuery,field_count++)) { mysql_free_result(presult); dynstr_free(&query); return(SQL_ERROR); } } } mysql_free_result(presult); dynstr_free(&query); } return(SQL_SUCCESS); }