| 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: | |
| 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 | ||
[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.
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.