From: Date: February 27 2007 11:00am Subject: bk commit into 5.0 tree (mskold:1.2301) BUG#26242 List-Archive: http://lists.mysql.com/commits/20633 X-Bug: 26242 Message-Id: <20070227100047.BA718244BCD@linux.site> Below is the list of changes that have just been committed into a local 5.0 repository of marty. When marty does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-02-27 11:00:36+01:00, mskold@stripped +8 -0 Bug #26242 UPDATE with subquery and triggers failing with cluster tables: Use HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when batching of delete/update is not possible include/my_base.h@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +3 -1 Bug #26242 UPDATE with subquery and triggers failing with cluster tables: Use HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when batching of delete/update is not possible mysql-test/r/ndb_trigger.result@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +56 -0 Bug #26242 UPDATE with subquery and triggers failing with cluster tables: Use HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when batching of delete/update is not possible mysql-test/t/ndb_trigger.test@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +53 -0 Bug #26242 UPDATE with subquery and triggers failing with cluster tables: Use HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when batching of delete/update is not possible sql/ha_ndbcluster.cc@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +18 -3 Bug #26242 UPDATE with subquery and triggers failing with cluster tables: Use HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when batching of delete/update is not possible sql/ha_ndbcluster.h@stripped, 2007-02-27 11:00:14+01:00, mskold@stripped +2 -0 Bug #26242 UPDATE with subquery and triggers failing with cluster tables: Use HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when batching of delete/update is not possible sql/sql_delete.cc@stripped, 2007-02-27 11:00:15+01:00, mskold@stripped +11 -0 Bug #26242 UPDATE with subquery and triggers failing with cluster tables: Use HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when batching of delete/update is not possible sql/sql_trigger.h@stripped, 2007-02-27 11:00:15+01:00, mskold@stripped +5 -0 Bug #26242 UPDATE with subquery and triggers failing with cluster tables: Use HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when batching of delete/update is not possible sql/sql_update.cc@stripped, 2007-02-27 11:00:15+01:00, mskold@stripped +11 -0 Bug #26242 UPDATE with subquery and triggers failing with cluster tables: Use HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH to inform handler when batching of delete/update is not possible # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: mskold # Host: linux.site # Root: /windows/Linux_space/MySQL/mysql-5.0 --- 1.81/include/my_base.h 2007-02-27 11:00:47 +01:00 +++ 1.82/include/my_base.h 2007-02-27 11:00:47 +01:00 @@ -161,7 +161,9 @@ enum ha_extra_function { Off by default. */ HA_EXTRA_WRITE_CAN_REPLACE, - HA_EXTRA_WRITE_CANNOT_REPLACE + HA_EXTRA_WRITE_CANNOT_REPLACE, + HA_EXTRA_DELETE_CANNOT_BATCH, + HA_EXTRA_UPDATE_CANNOT_BATCH }; /* The following is parameter to ha_panic() */ --- 1.181/sql/sql_delete.cc 2007-02-27 11:00:47 +01:00 +++ 1.182/sql/sql_delete.cc 2007-02-27 11:00:47 +01:00 @@ -209,7 +209,18 @@ bool mysql_delete(THD *thd, TABLE_LIST * thd->proc_info="updating"; if (table->triggers) + { table->triggers->mark_fields_used(thd, TRG_EVENT_DELETE); + if (table->triggers->has_triggers(TRG_EVENT_DELETE, + TRG_ACTION_AFTER)) + { + /* + The table table has AFTER DELETE triggers that need delete + to be done immediately, no batching is possible + */ + (void) table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH); + } + } while (!(error=info.read_record(&info)) && !thd->killed && !thd->net.report_error) --- 1.201/sql/sql_update.cc 2007-02-27 11:00:47 +01:00 +++ 1.202/sql/sql_update.cc 2007-02-27 11:00:47 +01:00 @@ -435,7 +435,18 @@ int mysql_update(THD *thd, MODE_STRICT_ALL_TABLES))); if (table->triggers) + { table->triggers->mark_fields_used(thd, TRG_EVENT_UPDATE); + if (table->triggers->has_triggers(TRG_EVENT_UPDATE, + TRG_ACTION_AFTER)) + { + /* + The table table has AFTER UPDATE triggers that need update + to be done immediately, no batching is possible + */ + (void) table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH); + } + } /* We can use compare_record() to optimize away updates if --- 1.1/mysql-test/r/ndb_trigger.result 2007-02-27 11:00:47 +01:00 +++ 1.2/mysql-test/r/ndb_trigger.result 2007-02-27 11:00:47 +01:00 @@ -116,4 +116,60 @@ op a b d 1 1.050000000000000000000000000000 d 2 2.050000000000000000000000000000 drop tables t1, t2, t3; +CREATE TABLE t1 ( +id INT NOT NULL PRIMARY KEY, +xy INT +) ENGINE=ndbcluster; +INSERT INTO t1 VALUES (1, 0); +CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id = NEW.id; END // +CREATE TABLE t2 ( +id INT NOT NULL PRIMARY KEY, +xy INT +) ENGINE=ndbcluster; +INSERT INTO t2 VALUES (2, 0); +CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY) ENGINE=ndbcluster; +INSERT INTO t3 VALUES (1); +UPDATE t1 SET xy = 3 WHERE id = 1; +SELECT xy FROM t1 where id = 1; +xy +3 +SELECT xy FROM t2 where id = 1; +xy +3 +UPDATE t1 SET xy = 4 WHERE id IN (SELECT id FROM t3 WHERE id = 1); +SELECT xy FROM t1 where id = 1; +xy +4 +SELECT xy FROM t2 where id = 1; +xy +4 +DROP TRIGGER t1_update; +CREATE TRIGGER t1_delete AFTER DELETE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id > 4; END // +INSERT INTO t1 VALUES (5, 0),(6,0); +INSERT INTO t2 VALUES (5, 1),(6,1); +INSERT INTO t3 VALUES (5); +SELECT * FROM t1 order by id; +id xy +1 4 +5 0 +6 0 +SELECT * FROM t2 order by id; +id xy +1 4 +2 0 +5 1 +6 1 +DELETE FROM t1 WHERE id IN (SELECT id FROM t3 WHERE id = 5); +SELECT * FROM t1 order by id; +id xy +1 4 +6 0 +SELECT * FROM t2 order by id; +id xy +1 4 +2 0 +5 1 +6 0 +DROP TRIGGER t1_delete; +DROP TABLE t1, t2, t3; End of 5.0 tests --- 1.1/mysql-test/t/ndb_trigger.test 2007-02-27 11:00:47 +01:00 +++ 1.2/mysql-test/t/ndb_trigger.test 2007-02-27 11:00:47 +01:00 @@ -89,4 +89,57 @@ select * from t2 order by op, a, b; drop tables t1, t2, t3; +# Test for bug#26242 +# Verify that AFTER UPDATE/DELETE triggers are executed +# after the change has actually taken place + +CREATE TABLE t1 ( + id INT NOT NULL PRIMARY KEY, + xy INT +) ENGINE=ndbcluster; + +INSERT INTO t1 VALUES (1, 0); + +DELIMITER //; +CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id = NEW.id; END // +DELIMITER ;// + +CREATE TABLE t2 ( + id INT NOT NULL PRIMARY KEY, + xy INT +) ENGINE=ndbcluster; + +INSERT INTO t2 VALUES (2, 0); + +CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY) ENGINE=ndbcluster; + +INSERT INTO t3 VALUES (1); + +UPDATE t1 SET xy = 3 WHERE id = 1; +SELECT xy FROM t1 where id = 1; +SELECT xy FROM t2 where id = 1; + +UPDATE t1 SET xy = 4 WHERE id IN (SELECT id FROM t3 WHERE id = 1); +SELECT xy FROM t1 where id = 1; +SELECT xy FROM t2 where id = 1; + +DROP TRIGGER t1_update; + +DELIMITER //; +CREATE TRIGGER t1_delete AFTER DELETE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id > 4; END // +DELIMITER ;// + +INSERT INTO t1 VALUES (5, 0),(6,0); +INSERT INTO t2 VALUES (5, 1),(6,1); +INSERT INTO t3 VALUES (5); +SELECT * FROM t1 order by id; +SELECT * FROM t2 order by id; +DELETE FROM t1 WHERE id IN (SELECT id FROM t3 WHERE id = 5); +SELECT * FROM t1 order by id; +SELECT * FROM t2 order by id; + + +DROP TRIGGER t1_delete; +DROP TABLE t1, t2, t3; + --echo End of 5.0 tests --- 1.22/sql/sql_trigger.h 2007-02-27 11:00:47 +01:00 +++ 1.23/sql/sql_trigger.h 2007-02-27 11:00:47 +01:00 @@ -110,6 +110,11 @@ public: const char *old_table, const char *new_db, const char *new_table); + bool has_triggers(trg_event_type event_type, + trg_action_time_type action_time) + { + return (bodies[event_type][action_time]); + } bool has_delete_triggers() { return (bodies[TRG_EVENT_DELETE][TRG_ACTION_BEFORE] || --- 1.296/sql/ha_ndbcluster.cc 2007-02-27 11:00:47 +01:00 +++ 1.297/sql/ha_ndbcluster.cc 2007-02-27 11:00:47 +01:00 @@ -2524,8 +2524,11 @@ int ha_ndbcluster::update_row(const byte ERR_RETURN(op->getNdbError()); } - // Execute update operation - if (!cursor && execute_no_commit(this,trans,false) != 0) { + // Execute update operation if we are not doing a scan for update + // and there exist UPDATE AFTER triggers + + if ((!cursor || m_update_cannot_batch) && + execute_no_commit(this,trans,false) != 0) { no_uncommitted_rows_execute_failure(); DBUG_RETURN(ndb_err(trans)); } @@ -2566,7 +2569,7 @@ int ha_ndbcluster::delete_row(const byte no_uncommitted_rows_update(-1); - if (!m_primary_key_update) + if (!(m_primary_key_update || m_delete_cannot_batch)) // If deleting from cursor, NoCommit will be handled in next_result DBUG_RETURN(0); } @@ -3277,6 +3280,8 @@ int ha_ndbcluster::extra(enum ha_extra_f DBUG_PRINT("info", ("HA_EXTRA_RESET")); DBUG_PRINT("info", ("Clearing condition stack")); cond_clear(); + m_delete_cannot_batch= FALSE; + m_update_cannot_batch= FALSE; break; case HA_EXTRA_CACHE: /* Cash record in HA_rrnd() */ DBUG_PRINT("info", ("HA_EXTRA_CACHE")); @@ -3393,6 +3398,14 @@ int ha_ndbcluster::extra(enum ha_extra_f DBUG_PRINT("info", ("Turning OFF use of write instead of insert")); m_use_write= FALSE; break; + case HA_EXTRA_DELETE_CANNOT_BATCH: + DBUG_PRINT("info", ("HA_EXTRA_DELETE_CANNOT_BATCH")); + m_delete_cannot_batch= TRUE; + break; + case HA_EXTRA_UPDATE_CANNOT_BATCH: + DBUG_PRINT("info", ("HA_EXTRA_UPDATE_CANNOT_BATCH")); + m_update_cannot_batch= TRUE; + break; } DBUG_RETURN(0); @@ -4744,6 +4757,8 @@ ha_ndbcluster::ha_ndbcluster(TABLE *tabl m_bulk_insert_rows((ha_rows) 1024), m_rows_changed((ha_rows) 0), m_bulk_insert_not_flushed(FALSE), + m_delete_cannot_batch(FALSE), + m_update_cannot_batch(FALSE), m_ops_pending(0), m_skip_auto_increment(TRUE), m_blobs_pending(0), --- 1.109/sql/ha_ndbcluster.h 2007-02-27 11:00:47 +01:00 +++ 1.110/sql/ha_ndbcluster.h 2007-02-27 11:00:47 +01:00 @@ -774,6 +774,8 @@ bool uses_blob_value(bool all_fields); ha_rows m_bulk_insert_rows; ha_rows m_rows_changed; bool m_bulk_insert_not_flushed; + bool m_delete_cannot_batch; + bool m_update_cannot_batch; ha_rows m_ops_pending; bool m_skip_auto_increment; bool m_blobs_pending;