summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--include/my_base.h10
-rw-r--r--mysql-test/t/ndb_trigger.test107
-rw-r--r--sql/ha_ndbcluster.cc23
-rw-r--r--sql/ha_ndbcluster.h2
-rw-r--r--sql/mysql_priv.h2
-rw-r--r--sql/sql_delete.cc26
-rw-r--r--sql/sql_insert.cc45
-rw-r--r--sql/sql_trigger.h5
-rw-r--r--sql/sql_update.cc29
9 files changed, 242 insertions, 7 deletions
diff --git a/include/my_base.h b/include/my_base.h
index 14e4e3afb44..fccba863f5a 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -171,7 +171,15 @@ enum ha_extra_function {
Off by default.
*/
HA_EXTRA_WRITE_CAN_REPLACE,
- HA_EXTRA_WRITE_CANNOT_REPLACE
+ HA_EXTRA_WRITE_CANNOT_REPLACE,
+ /*
+ Inform handler that delete_row()/update_row() cannot batch deletes/updates
+ and should perform them immediately. This may be needed when table has
+ AFTER DELETE/UPDATE triggers which access to subject table.
+ These flags are reset by the handler::extra(HA_EXTRA_RESET) call.
+ */
+ HA_EXTRA_DELETE_CANNOT_BATCH,
+ HA_EXTRA_UPDATE_CANNOT_BATCH
};
/* The following is parameter to ha_panic() */
diff --git a/mysql-test/t/ndb_trigger.test b/mysql-test/t/ndb_trigger.test
index 7a4e58033a9..586fdc0ac97 100644
--- a/mysql-test/t/ndb_trigger.test
+++ b/mysql-test/t/ndb_trigger.test
@@ -110,5 +110,112 @@ drop trigger t4_au;
drop trigger t4_ad;
drop table t1, t2, t3, t4, t5;
+# 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);
+
+CREATE TABLE t4 LIKE t1;
+
+DELIMITER //;
+CREATE TRIGGER t4_update AFTER UPDATE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.id = NEW.id; END //
+DELIMITER ;//
+
+CREATE TABLE t5 LIKE t2;
+
+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;
+
+INSERT INTO t4 SELECT * FROM t1;
+INSERT INTO t5 SELECT * FROM t2;
+UPDATE t1,t4 SET t1.xy = 3, t4.xy = 3 WHERE t1.id = 1 AND t4.id = 1;
+SELECT xy FROM t1 where id = 1;
+SELECT xy FROM t2 where id = 1;
+SELECT xy FROM t4 where id = 1;
+SELECT xy FROM t5 where id = 1;
+
+UPDATE t1,t4 SET t1.xy = 4, t4.xy = 4 WHERE t1.id IN (SELECT id FROM t3 WHERE id = 1) AND t4.id IN (SELECT id FROM t3 WHERE id = 1);
+SELECT xy FROM t1 where id = 1;
+SELECT xy FROM t2 where id = 1;
+SELECT xy FROM t4 where id = 1;
+SELECT xy FROM t5 where id = 1;
+
+INSERT INTO t1 VALUES (1,0) ON DUPLICATE KEY UPDATE xy = 5;
+SELECT xy FROM t1 where id = 1;
+SELECT xy FROM t2 where id = 1;
+
+DROP TRIGGER t1_update;
+DROP TRIGGER t4_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 ;//
+
+DELIMITER //;
+CREATE TRIGGER t4_delete AFTER DELETE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.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;
+
+INSERT INTO t1 VALUES (5,0);
+UPDATE t2 SET xy = 1 WHERE id = 6;
+TRUNCATE t4;
+INSERT INTO t4 SELECT * FROM t1;
+TRUNCATE t5;
+INSERT INTO t5 SELECT * FROM t2;
+SELECT * FROM t1 order by id;
+SELECT * FROM t2 order by id;
+SELECT * FROM t4 order by id;
+SELECT * FROM t5 order by id;
+DELETE FROM t1,t4 USING t1,t3,t4 WHERE t1.id IN (SELECT id FROM t3 WHERE id = 5) AND t4.id IN (SELECT id FROM t3 WHERE id = 5);
+SELECT * FROM t1 order by id;
+SELECT * FROM t2 order by id;
+SELECT * FROM t4 order by id;
+SELECT * FROM t5 order by id;
+
+INSERT INTO t1 VALUES (5, 0);
+REPLACE INTO t2 VALUES (6,1);
+SELECT * FROM t1 order by id;
+SELECT * FROM t2 order by id;
+REPLACE INTO t1 VALUES (5, 1);
+SELECT * FROM t1 order by id;
+SELECT * FROM t2 order by id;
+
+DROP TRIGGER t1_delete;
+DROP TRIGGER t4_delete;
+DROP TABLE t1, t2, t3, t4, t5;
--echo End of 5.1 tests
diff --git a/sql/ha_ndbcluster.cc b/sql/ha_ndbcluster.cc
index c363cddb8ba..cad41d9de99 100644
--- a/sql/ha_ndbcluster.cc
+++ b/sql/ha_ndbcluster.cc
@@ -2992,8 +2992,13 @@ int ha_ndbcluster::update_row(const byte *old_data, byte *new_data)
no_fields++;
op->setValue(no_fields, part_func_value);
}
- // 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));
}
@@ -3047,7 +3052,7 @@ int ha_ndbcluster::delete_row(const byte *record)
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);
}
@@ -3886,7 +3891,13 @@ int ha_ndbcluster::extra(enum ha_extra_function operation)
DBUG_PRINT("info", ("Turning OFF use of write instead of insert"));
m_use_write= FALSE;
break;
- default:
+ 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;
}
@@ -3897,6 +3908,8 @@ int ha_ndbcluster::extra(enum ha_extra_function operation)
int ha_ndbcluster::reset()
{
DBUG_ENTER("ha_ndbcluster::reset");
+ m_delete_cannot_batch= FALSE;
+ m_update_cannot_batch= FALSE;
cond_clear();
/*
Regular partition pruning will set the bitmap appropriately.
@@ -5826,6 +5839,8 @@ ha_ndbcluster::ha_ndbcluster(handlerton *hton, TABLE_SHARE *table_arg):
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),
diff --git a/sql/ha_ndbcluster.h b/sql/ha_ndbcluster.h
index 63665fde0f8..6dc32bdee1d 100644
--- a/sql/ha_ndbcluster.h
+++ b/sql/ha_ndbcluster.h
@@ -960,6 +960,8 @@ private:
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;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index e8398e5ebb2..b3a7afd449a 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -974,6 +974,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *table,List<Item> &fields,
bool ignore);
int check_that_all_fields_are_given_values(THD *thd, TABLE *entry,
TABLE_LIST *table_list);
+void prepare_triggers_for_insert_stmt(THD *thd, TABLE *table,
+ enum_duplicates duplic);
bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds);
bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
SQL_LIST *order, ha_rows rows, ulonglong options,
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index ea8c0e2d83e..422b885d7bd 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -216,7 +216,20 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
init_ftfuncs(thd, select_lex, 1);
thd->proc_info="updating";
- will_batch= !table->file->start_bulk_delete();
+ if (table->triggers &&
+ table->triggers->has_triggers(TRG_EVENT_DELETE,
+ TRG_ACTION_AFTER))
+ {
+ /*
+ The table has AFTER DELETE triggers that might access to subject table
+ and therefore might need delete to be done immediately. So we turn-off
+ the batching.
+ */
+ (void) table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH);
+ will_batch= FALSE;
+ }
+ else
+ will_batch= !table->file->start_bulk_delete();
table->mark_columns_needed_for_delete();
@@ -552,6 +565,17 @@ multi_delete::initialize_tables(JOIN *join)
transactional_tables= 1;
else
normal_tables= 1;
+ if (tbl->triggers &&
+ tbl->triggers->has_triggers(TRG_EVENT_DELETE,
+ TRG_ACTION_AFTER))
+ {
+ /*
+ The table has AFTER DELETE triggers that might access to subject
+ table and therefore might need delete to be done immediately.
+ So we turn-off the batching.
+ */
+ (void) tbl->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH);
+ }
tbl->prepare_for_position();
tbl->mark_columns_needed_for_delete();
}
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index aaffa09b978..af239c1f62b 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -341,6 +341,51 @@ static int check_update_fields(THD *thd, TABLE_LIST *insert_table_list,
}
+ Prepare triggers for INSERT-like statement.
+
+ SYNOPSIS
+ prepare_triggers_for_insert_stmt()
+ thd The current thread
+ table Table to which insert will happen
+ duplic Type of duplicate handling for insert which will happen
+
+ NOTE
+ Prepare triggers for INSERT-like statement by marking fields
+ used by triggers and inform handlers that batching of UPDATE/DELETE
+ cannot be done if there are BEFORE UPDATE/DELETE triggers.
+*/
+
+void prepare_triggers_for_insert_stmt(THD *thd, TABLE *table,
+ enum_duplicates duplic)
+{
+ if (table->triggers)
+ {
+ if (table->triggers->has_triggers(TRG_EVENT_DELETE,
+ TRG_ACTION_AFTER))
+ {
+ /*
+ The table has AFTER DELETE triggers that might access to
+ subject table and therefore might need delete to be done
+ immediately. So we turn-off the batching.
+ */
+ (void) table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH);
+ }
+ if (table->triggers->has_triggers(TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER))
+ {
+ /*
+ The table has AFTER UPDATE triggers that might access to subject
+ table and therefore might need update to be done immediately.
+ So we turn-off the batching.
+ */
+ (void) table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH);
+ }
+ mark_fields_used_by_triggers_for_insert_stmt(thd, table, duplic);
+ }
+}
+
+
+/*
bool mysql_insert(THD *thd,TABLE_LIST *table_list,
List<Item> &fields,
List<List_item> &values_list,
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index 75dda6be1cf..2be42cbccac 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -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] ||
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index baccb3358f7..7c25f21c14d 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -447,7 +447,20 @@ int mysql_update(THD *thd,
(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
MODE_STRICT_ALL_TABLES)));
- will_batch= !table->file->start_bulk_update();
+ if (table->triggers &&
+ table->triggers->has_triggers(TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER))
+ {
+ /*
+ The table has AFTER UPDATE triggers that might access to subject
+ table and therefore might need update to be done immediately.
+ So we turn-off the batching.
+ */
+ (void) table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH);
+ will_batch= FALSE;
+ }
+ else
+ will_batch= !table->file->start_bulk_update();
/*
We can use compare_record() to optimize away updates if
@@ -1116,6 +1129,20 @@ int multi_update::prepare(List<Item> &not_used_values,
table->no_keyread=1;
table->used_keys.clear_all();
table->pos_in_table_list= tl;
+ 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 has AFTER UPDATE triggers that might access to subject
+ table and therefore might need update to be done immediately.
+ So we turn-off the batching.
+ */
+ (void) table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH);
+ }
+ }
}
}
View Lorry Controller Status