Bug #47955 UPDATEs using indexes that modify no rows return incorrect found rows
Submitted: 9 Oct 2009 18:59 Modified: 11 Nov 2009 18:04
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:mysql-5.1-telco-6.3 OS:Any
Assigned to: Martin Skold CPU Architecture:Any
Tags: 6.3.21, 7.0.7
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[9 Oct 2009 18:59] Todd Farmer
Description:
When an UPDATE statement is issued against an NDB table where an index is used to identify rows and no data is changed, NDB returns zero found rows:

mysql> DROP TABLE IF EXISTS ndbtb;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE ndbtb (
    ->  col1 INT NOT NULL,
    ->  col2 INT NOT NULL,
    ->  col3 INT NOT NULL,
    ->  col4 INT NOT NULL,
    ->  col5 INT NOT NULL,
    ->  PRIMARY KEY(col1),
    ->  UNIQUE KEY(col2),
    ->  KEY(col3),
    ->  KEY(col4, col3)
    -> ) ENGINE = NDBCLUSTER;
Query OK, 0 rows affected (2.56 sec)

mysql>
mysql> INSERT INTO ndbtb VALUES (1, 1, 1, 1, 1);
Query OK, 1 row affected (0.08 sec)

mysql>
mysql> UPDATE ndbtb SET col5 = 1 WHERE col1 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col2 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col3 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col4 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> -- No indexes used:
mysql> UPDATE ndbtb SET col5 = 1 WHERE col1 + 0 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql>
mysql> -- InnoDB consistently returns correct values
mysql> ALTER TABLE ndbtb ENGINE = InnoDB;
Query OK, 1 row affected (2.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col1 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col2 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col3 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col4 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col1 + 0 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE ndbtb SET col5 = 1 WHERE col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

How to repeat:
DROP TABLE IF EXISTS ndbtb;
CREATE TABLE ndbtb (
 col1 INT NOT NULL, 
 col2 INT NOT NULL,
 col3 INT NOT NULL,
 col4 INT NOT NULL,
 col5 INT NOT NULL,
 PRIMARY KEY(col1),
 UNIQUE KEY(col2),
 KEY(col3),
 KEY(col4, col3)
) ENGINE = NDBCLUSTER;

INSERT INTO ndbtb VALUES (1, 1, 1, 1, 1);

UPDATE ndbtb SET col5 = 1 WHERE col1 = 1 and col5 = 1;
UPDATE ndbtb SET col5 = 1 WHERE col2 = 1 and col5 = 1;
UPDATE ndbtb SET col5 = 1 WHERE col3 = 1 and col5 = 1;
UPDATE ndbtb SET col5 = 1 WHERE col4 = 1 and col5 = 1;
-- No indexes used:
UPDATE ndbtb SET col5 = 1 WHERE col1 + 0 = 1 and col5 = 1;
UPDATE ndbtb SET col5 = 1 WHERE col5 = 1;

Suggested fix:
Return correct number of rows matched.
[9 Oct 2009 19:08] Todd Farmer
Workaround (almost always a bad idea) is to bypass index usage in UPDATE statements by encasing indexed columns in no-op functions (WHERE CONCAT(idx_col, '' ...)
[9 Oct 2009 19:17] Todd Farmer
Problem is not dependent on engine_condition_pushdown:

mysql> SET engine_condition_pushdown = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ndbtb SET col5 = 1 WHERE col1 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SET engine_condition_pushdown = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ndbtb SET col5 = 1 WHERE col1 = 1 and col5 = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
[9 Oct 2009 21:25] Bogdan Kecman
tested: 7.0.4, 7.0.5, 7.0.7, 7.0.8, ndbd and ndbmtd same problem.
tested: 6.3.21, ndbd - same problem

mysql> create table ndbtb (id VARCHAR(100) NOT NULL, STATE INTEGER NOT NULL,PRIMARY KEY(ID)) engine = NDBCLUSTER; 
Query OK, 0 rows affected (2.54 sec)

mysql> insert into ndbtb values ('broker1', 1), ('broker2', 0); 
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update ndbtb set state = 2 where id = 'broker1' and state = 1; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from ndbtb ;
+---------+-------+
| id      | STATE |
+---------+-------+
| broker1 |     2 | 
| broker2 |     0 | 
+---------+-------+
2 rows in set (0.00 sec)

mysql> update ndbtb set state = 2 where id = 'broker1' and state = 2; 
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from  ndbtb where id = 'broker1' and state = 2;
+---------+-------+
| id      | STATE |
+---------+-------+
| broker1 |     2 | 
+---------+-------+
1 row in set (0.00 sec)

mysql> update ndbtb set state = 2 where id = 'broker1' and state = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
[9 Oct 2009 23:48] Martin Skold
This patch seems to work:

=== modified file 'sql/sql_update.cc'
--- sql/sql_update.cc   2009-10-01 05:56:05 +0000
+++ sql/sql_update.cc   2009-10-09 23:43:01 +0000
@@ -859,11 +859,13 @@ int mysql_update(THD *thd,
       all those limitations.
 
       Moreover, since there is no read before update, found == updated,
-      as there is no optimization to remove the update if the new data
+      if there is no optimization to remove the update if the new data
       should equal the old.
     */
     table->file->info(HA_STATUS_WRITTEN_ROWS);
-    found= updated= table->file->stats.rows_updated;
+    updated= table->file->stats.rows_updated;
+    if (!can_compare_record)
+      found= updated;
   }
 
   /* If LAST_INSERT_ID(X) was used, report X */
[15 Oct 2009 8:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86903

3106 Martin Skold	2009-10-15
      Bug#47955 UPDATEs using indexes that modify no rows return incorrect found rows: don't use updated rows to set found number of rows, added test case
      modified:
        mysql-test/suite/ndb/r/ndb_update.result
        mysql-test/suite/ndb/t/ndb_update.test
        sql/sql_update.cc
[15 Oct 2009 12:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86965

3107 Martin Skold	2009-10-15
      Bug#47955 UPDATEs using indexes that modify no rows return incorrect found rows: when we could not compare records we have to check with handler and assume found rows == updated rows
      modified:
        sql/sql_update.cc
[15 Oct 2009 12:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86978

3124 Martin Skold	2009-10-15 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_alter_table_backup.result
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_insert.result
        mysql-test/suite/ndb/r/ndb_update.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_insert.test
        mysql-test/suite/ndb/t/ndb_update.test
        sql/ha_ndbcluster.cc
        sql/sql_update.cc
[15 Oct 2009 14:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86985

3080 Martin Skold	2009-10-15 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_alter_table_backup.result
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_insert.result
        mysql-test/suite/ndb/r/ndb_update.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_insert.test
        mysql-test/suite/ndb/t/ndb_update.test
        sql/ha_ndbcluster.cc
        sql/sql_update.cc
[15 Oct 2009 14:03] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:martin.skold@mysql.com-20091015140324-53ll4ja07nf7f72q) (version source revid:martin.skold@mysql.com-20091015140324-53ll4ja07nf7f72q) (merge vers: 5.1.39-ndb-7.1.0) (pib:13)
[15 Oct 2009 14:08] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:martin.skold@mysql.com-20091015140912-ick0kiktabyhzkjh) (version source revid:martin.skold@mysql.com-20091015125902-uc7afycq0fxb1nvl) (merge vers: 5.1.39-ndb-7.0.9) (pib:13)
[15 Oct 2009 14:12] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:martin.skold@mysql.com-20091015141245-p900c5li04ydixyj) (version source revid:martin.skold@mysql.com-20091015123753-y8zozhy0zhcetpg7) (merge vers: 5.1.39-ndb-6.3.28) (pib:13)
[27 Oct 2009 9:58] Eyal Sorek
Hi,
We have mysql-5.1.32 and we sporadically experience similar problem on InnoDB table also. In happens high load with high concurrency of many processes, most of the time it works OK, and in some weird and undefined cases (fraction of percent), it returns wrongly "matched: 0  Changed: 0". According to this bug scenario, we tried to remove the table PK and the symptom was not reproduced anymore.
Thanks,
Eyal Sorek
[11 Nov 2009 15:30] Martin Skold
InnoDB problem is most likely unrelated since there no batching of updates
is being done.
[11 Nov 2009 18:04] Jon Stephens
Documented bugfix in the NDB-6.3.28 and 7.0.9 changelogs as follows:
      
        When an UPDATE statement was issued against an NDB table where
        an index was used to identify rows but no data was actually
        changed, NDB returned zero found rows.
      
        For example, consider the table created and populated as shown
        here:
      
    CREATE TABLE t1 
    (
        c1 INT NOT NULL, 
        c2 INT NOT NULL,
        PRIMARY KEY(c1), 
        KEY(c2)
    ) 
    ENGINE = NDB;

    INSERT INTO t1 VALUES(1, 1);
      
        The following UPDATE statements, even though they did not change
        any rows, each still matched a row, but this was reported
        incorrectly in both cases, as shown here:
    
    mysql> UPDATE t1 SET c2 = 1 WHERE c1 = 1;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0  Changed: 0  Warnings: 0

    mysql> UPDATE t1 SET c1 = 1 WHERE c2 = 1;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0  Changed: 0  Warnings: 0
  
        Now in such cases, the number of rows matched is correct. (In
        the case of each of the example UPDATE statements just shown,
        this is displayed as Rows matched: 1, as it should be.)
      
        This issue could affect UPDATE statements involving any 
        indexed columns in NDB tables, regardless of the type of 
        index (including KEY, UNIQUE KEY, and PRIMARY KEY) or the 
        number of columns covered by the index.

Closed.