MySQL Bugs: #19566: Cannot update or delete using SQLSetPos

Bug #19566	Cannot update or delete using SQLSetPos
Submitted:	5 May 2006 17:10	Modified:	17 May 2007 20:19
Reporter:	Pascal Viandier	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.15	OS:	Any (Debian Linux X86)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	SQLSetPos

Description:
Under some circumstances explained below, update/delete a row using SQLSetPos() does not work because the SQL statement sent to MySQL is wrong.
Detailed:
The problem occurs for tables that have a char(x) column used in more than one index.
Using SQLFetchScroll() to retrieve rows works, but using SQLSetPos() to update or delete a row from the result set produces a wrong SQL statement: the column used by the indexes appears two times in the update or delete statement's WHERE clause with two different values so the statement does not match any row.

Configuration:
MyODBC 3.51.12 on Linux Debian X86
MySQL 5.1.7-beta on SUN Sparc Solaris 8

The SQL statements sent to MySQL by the MyODBC driver were grabbed using tcpdump on the MySQL port and decoded using Ethereal.

I have not found any workaround to this. I cannot modify the existing production database to remove the problematic indices schema because other programs use the same database.

How to repeat:
Table structure:
CREATE TABLE `tstidx` (
  `chp1` char(3) default NULL,
  `chp2` smallint(6) default NULL,
  `chp3` char(10) default NULL,
  `chp4` int(11) default NULL,
  `chp5` date default NULL,
  UNIQUE KEY `tstidx1` (`chp1`),
  UNIQUE KEY `tstidx3` (`chp3`,`chp1`),
  KEY `tstidx2` (`chp2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Use SQLFetchScroll() to fetch rows,
Modify the value of chp5 (change the date) of the current row of the result set
Then SQLSetPos(hstmt, i, SQL_UPDATE, SQL_LOCK_NO_CHANGE) on the current row. It produces the following SQL statement:
UPDATE `tstidx`
SET `chp1`='KKK',
    `chp2`=106,
    `chp3`='Rec KKK   ',
    `chp4`=111,
    `chp5`='20060511'
WHERE `chp1`='KKK' AND  <--- Rigth value (current row)
      `chp2`=106 AND
      `chp3`='Rec KKK' AND
      `chp1`='AAA'  <--- Wrong value (value of chp1 in the first row)
 LIMIT 1;

As one can see, chp1 appears two times in the where clause.
The first occurence shows the current value
The second shows the value  of the column for the first row of the result set. 
This does not work but does not produce any error since it is a syntactically valid SQL statement.

Hi Pascal. Thanks for your prblem report. Can you please provide me with datadump and sample application (and/or) ODBC trace log?
One question, why don't you have primary key defined, ie. why doesn't this line "UNIQUE KEY `tstidx1` (`chp1`)," look like "PRIMARY KEY (`chp1`),"?

Please find attached the MyODBC trace showing the problem.
Interesting enough, I tried the same test using two Sparc Solaris boxes and the test produce the error "copy error" in myodbc.log.

I think I found the problem and I made a patch in cursor.c in the function check_if_pk_exist() to ensure the same column name does not occur more than once in the index descriptor. It works. If you are interested I can send it.
However I have not fully solved the "TODO: fix this loop..." but at least the update and delete work with SQLSetPos().

To answer your last question, the database was migrated from Informix, and the original has no primary key on any table. That's why there is no primary key in the test table since I tried to have an example as close as possible as the production database (689 tables!).

Pascal, feel free to do so.

Hi Tonci,

Please find the patch in attachment. It includes a new function already_added()
and a replacement for check_if_pk_exists() in "diff -u" format suitable for "patch -P0".

Pascal, I will have to ask you to provide test case on this one. Your log shows proper index determination but wrong repositioning. At the same time, SQLFetchScroll can use 3 ways to reposition correctly ("SQLFetchScroll fetches the specified rowset of data from the result set and returns data for all bound columns. Rowsets can be specified at an absolute or relative position or by bookmark.").

Pascal, this seems to escalate into real trouble as I digg through it...
Test case would be nice.

Hi Tonci,

I don't have enough time right now to give you a testcase for this bug, however I attach the source of a small C program that reproduces the problem.
When running it, you will see the message "0 rows affected" because of the faulty WHERE clause.
Hope this will help.
Thanks

C source program to reproduce the bug

Attachment: mytest2.c (text/plain), 4.29 KiB.

One precision:
The header file mytest3.h included in the program is the one provided with the MyODBC sources in the test subdirectory.

Thanks

Pascal

Hi Pascal.
I was able to verify this bug with code provided on
  - MySQL server 5.0.22bk on Suse linux
  - WinXP SP2

ODBC log file

Attachment: 19566myodbclog.zip (application/zip, text), 7.81 KiB.

VS2005 sources

Attachment: Source19566.zip (application/zip, text), 5.52 KiB.

Hi,

Is there any chance to see this bug solved before I retire?

Pascal

This should be fixed in the upcoming 3.51.14 release with the changes to properly generate the WHERE clause for positioned updates.

Jim, alas same thing happens with 3.51.15GA on WinXP Pro SP2 with MySQL server 5.0.38BK:

 Connecting to 'myodbc1'...
 row 1: AAA,100,MySQL1
 row 2: BBB,200,MySQL2
 row 3: CCC,300,MySQL3
 rows affected:0      <<< Due to malformed WHERE as described. See below.
-- test-end --

Modifications amde to test case:
  SQLCHAR     connOut[255], Val[255], Rez[8]; /* buffer for connection output */
  SQLSMALLINT szConnOut; /* num bytes returned in connOut */

and  
  //rc = SQLDriverConnect(hdbc,
  //                      NULL,
  //                      (SQLCHAR *) the_dsn, SQL_NTS,
  //                      NULL, 0, NULL,
  //                      SQL_DRIVER_COMPLETE);
  //TEST 2
  rc = SQLDriverConnect(hdbc, NULL, (SQLCHAR *)"DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;UID=root;PWD=;Database=test;OPTION=512",
	   _countof("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;UID=root;PWD=;Database=test;OPTION=512"), connOut, 255, &szConnOut, SQL_DRIVER_NOPROMPT);

070515 12:38:42	      5 Connect     root@localhost on test
		      5 Query       SET SQL_AUTO_IS_NULL=0
070515 12:38:43	      5 Query       drop table tstidx
		      5 Query       create table tstidx(chp1 char(3) default null, chp2 int default null, chp3 char(10) default null)
		      5 Query       create unique index tstidx1 on tstidx (chp1)
		      5 Query       create unique index tstidx2 on tstidx (chp3, chp1)
		      5 Query       create index tstidx3 on tstidx (chp2)
		      5 Query       insert into tstidx values('AAA', 100,'MySQL1')
		      5 Query       insert into tstidx values('BBB', 200,'MySQL2')
		      5 Query       insert into tstidx values('CCC', 300,'MySQL3')
		      5 Query       select * from tstidx
070515 12:38:44	      5 Query       select * from tstidx where chp1='BBB'
		      5 Query       show keys from `tstidx`
		      5 Query       SELECT `chp1` FROM `tstidx`
		      5 Query       UPDATE `tstidx` SET `chp1`='BBB',`chp2`=1000,`chp3`='MySQL2    ' WHERE `chp1`='BBB' AND `chp2`=200 AND `chp3`='MySQL2' AND `chp1`='AAA' LIMIT 1
		      5 Query       select * from tstidx
070515 12:38:50	      5 Query       DROP DATABASE IF EXISTS client_odbc_test
		      5 Quit

Logs

Attachment: 19566-logs.zip (application/x-zip-compressed, text), 6.29 KiB.

Here is the test case (currently flagged TODO) added to the repo for this. This is related to Bug #28255. (But with the fix for that bug, the test case changes from generating an incorrect query to refusing to do the update, which it should be able to do.)

DECLARE_TEST(t_bug19566)
{
  SQLLEN nlen;

  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug19566");
  ok_sql(hstmt, "CREATE TABLE t_bug19566 (a INT, b INT, PRIMARY KEY (a,b), UNIQUE (b))");
  ok_sql(hstmt, "INSERT INTO t_bug19566 VALUES (1,3),(1,4)");

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));

  ok_stmt(hstmt, SQLSetCursorName(hstmt, (SQLCHAR *)"bug", SQL_NTS));

  ok_sql(hstmt, "SELECT a FROM t_bug19566 WHERE b > 3");

  ok_stmt(hstmt, SQLFetch(hstmt));
  is_num(my_fetch_int(hstmt, 1), 1);

  ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE));
  ok_stmt(hstmt, SQLSetPos(hstmt, 1, SQL_DELETE, SQL_LOCK_NO_CHANGE));

  ok_stmt(hstmt, SQLRowCount(hstmt, &nlen));
  is_num(nlen, 1);

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_UNBIND));
  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));

  ok_sql(hstmt, "SELECT * FROM t_bug19566");

  ok_stmt(hstmt, SQLFetch(hstmt));
  is_num(my_fetch_int(hstmt, 1), 1);
  is_num(my_fetch_int(hstmt, 2), 3);

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));

  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug19566");

  return OK;
}

I have an initial patch for this (that chooses the first unique/primary key for which we have all of the component elements), but developing a good set of tests is going to require the fix for Bug #28255 to be pushed first.

This is a duplicate of Bug #10563. The fix will be handled there.