Bug #10563 Update using multicolumn primary key with duplicate indexes fails
Submitted: 11 May 2005 17:23 Modified: 3 Jul 2007 19:05
Reporter: Ian Klassen Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.11 OS:Windows (Windows XP SP1)
Assigned to: Jim Winstead CPU Architecture:Any

[11 May 2005 17:23] Ian Klassen
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);
}
[11 May 2005 17:23] Ian Klassen
Visual Studio 6.0 Project

Attachment: myodbctest - keys.zip (application/x-zip-compressed, text), 39.69 KiB.

[12 May 2005 0:35] MySQL Verification Team
Thank you for the bug report and test case.
[17 May 2007 20:20] Jim Winstead
Bug #19566 was marked as a duplicate of this bug.
[12 Jun 2007 0:18] Jim Winstead
Select the correct key to use when handling SQLSetPos

Attachment: bug10563.patch (text/plain), 6.68 KiB.

[15 Jun 2007 19:33] Jim Winstead
The fix for this bug has been committed to the repository, and will be included in 3.51.17.

Thanks for the bug report.
[3 Jul 2007 19:05] Paul DuBois
Noted in 3.51.17 changelog.

If there was more than one unique key on a table, the correct fields
were not used in handling SQLSetPos().