| Bug #72592 | MySQL Cluster after update trigger: trigger starts to return null for NEW.column | ||
|---|---|---|---|
| Submitted: | 9 May 2014 13:31 | Modified: | 7 Jan 2015 14:28 |
| Reporter: | Jarrod van den Heuvel | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | mysql-5.6.15 ndb-7.3.4 | OS: | Windows (win server 2008/win 7) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 7.3.4, custer, new, trigger | ||
[9 Jun 2014 11:24]
Jarrod van den Heuvel
Updated serverity
[7 Jan 2015 14:31]
MySQL Verification Team
Thank you for the report and test case. Could you please check with latest version 7.3.7? // 7.3.7 // CREATE TABLE testingtable....engine = ndbcluster [ushastry@ushastry]/export/umesh/mysql-cluster-7.3.7: bin/mysql -u root -p test -S /tmp/mysql_ushastry.sock < Bug72592.test Enter password: templogtransactionid output 4 testingtable-second:0-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 3 testingtable-first:0-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 2 testingtable-first:1-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 1 testingtable-first:1-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 templogtransactionid output 6 testingtable-second:0-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 5 testingtable-first:0-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 4 testingtable-second:0-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 3 testingtable-first:0-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 2 testingtable-first:1-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 1 testingtable-first:1-CURRENT-2015-01-07 15:11:31-2015-01-07 15:11:31 // CREATE TABLE testingtable....engine = innodb [ushastry@ushastry]/export/umesh/mysql-cluster-7.3.7: bin/mysql -u root -p test -S /tmp/mysql_ushastry.sock < Bug72592.test Enter password: templogtransactionid output 4 testingtable-second:0-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 3 testingtable-first:0-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 2 testingtable-first:1-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 1 testingtable-first:1-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 templogtransactionid output 6 testingtable-second:0-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 5 testingtable-first:0-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 4 testingtable-second:0-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 3 testingtable-first:0-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 2 testingtable-first:1-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 1 testingtable-first:1-CURRENT-2015-01-07 15:12:01-2015-01-07 15:12:01 // 7.3.5 // CREATE TABLE testingtable....engine = ndbcluster [ushastry@ushastry]/export/umesh/mysql-cluster-7.3.5: bin/mysql -u root -p test -S /tmp/mysql_ushastry.sock < k.test Enter password: templogtransactionid output 4 testingtable-second:0-CURRENT-2015-01-07 15:24:48-2015-01-07 15:24:48 3 testingtable-first:0-CURRENT-2015-01-07 15:24:48-2015-01-07 15:24:48 2 testingtable-first:1-CURRENT-2015-01-07 15:24:48-2015-01-07 15:24:48 1 testingtable-first:1-CURRENT-2015-01-07 15:24:48-2015-01-07 15:24:48 templogtransactionid output 5 testingtable-first:0-CURRENT-0000-00-00 00:00:00-0000-00-00 00:00:00 4 testingtable-second:0-CURRENT-2015-01-07 15:24:48-2015-01-07 15:24:48 3 testingtable-first:0-CURRENT-2015-01-07 15:24:48-2015-01-07 15:24:48 2 testingtable-first:1-CURRENT-2015-01-07 15:24:48-2015-01-07 15:24:48 1 testingtable-first:1-CURRENT-2015-01-07 15:24:48-2015-01-07 15:24:48 // CREATE TABLE testingtable....engine = innodb [ushastry@ushastry]/export/umesh/mysql-cluster-7.3.5: bin/mysql -u root -p test -S /tmp/mysql_ushastry.sock < k.test Enter password: templogtransactionid output 4 testingtable-second:0-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01 3 testingtable-first:0-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01 2 testingtable-first:1-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01 1 testingtable-first:1-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01 templogtransactionid output 6 testingtable-second:0-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01 5 testingtable-first:0-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01 4 testingtable-second:0-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01 3 testingtable-first:0-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01 2 testingtable-first:1-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01 1 testingtable-first:1-CURRENT-2015-01-07 15:26:01-2015-01-07 15:26:01
Description: Basic run down: Primary table 'testingtable' has a trigger 'testingtable_afterupdate' after update. This trigger is into insert into a temporary table 'temphistorysqlstestingtable' (have it as temporary and normal (innodb, nbd) no difference) the transaction id of the row that was updated if there were any changes. The 'templog' table is just for output purposes to see the results What happens: After creating the primary table, inserting a row and performing multiple updates on that row, templog shows correct response each time 'testingtable-first:1..' with the correct datetime and transaction id of the row updated Then create the temp table and perform 1 update, and templog shows correct again with 'testingtable-first:0..' and extra row 'testingtable-second:0..' showing that it has inserted into the temp table because it now exists. Perform a second update and all the values that are to be referenced are not that of the row updated. templog now shows that the updateddate is 0000-00-00 00:00:00 and the transaction id of 0, with the other columns being null. Now this doesn't happen if the first thing after creating the primary table is creating the temporary table. If the temp table exists before the first updates everything behaves accordingly. But if you drop the temp, perform updates, then recreate the temp, perform the 2 updates the second one is incorrect again. I have tested the case with the primary table 'testingtable' as innodb table and NEW/OLD return the correct values of the row updated. If executing the how to repeat steps, if the primary table is ndbcluster templog has 5 rows in it VS innodb 6 rows (correct) as there are 2 updates where the temp table doesn't exist and 2 where it does. How to repeat: DROP TRIGGER IF EXISTS testingtable_afterupdate; DROP TEMPORARY TABLE IF EXISTS temphistorysqlstestingtable; DROP TABLE IF EXISTS testingtable; DROP TABLE IF EXISTS templog; CREATE TABLE templog ( templogtransactionid bigint NOT NULL AUTO_INCREMENT, output LONGTEXT, PRIMARY KEY (templogtransactionid) ); CREATE TABLE testingtable ( testingtabletransactionid bigint NOT NULL AUTO_INCREMENT, updateddate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, historydate datetime, updateduser varchar(50), currenthistoryflag varchar(10) NOT NULL DEFAULT 'CURRENT', testcolumn VARCHAR(50), PRIMARY KEY (testingtabletransactionid) ) engine = -- innodb ndbcluster ; DELIMITER /// CREATE TRIGGER testingtable_afterupdate AFTER UPDATE ON testingtable FOR EACH ROW BEGIN DECLARE no_such_table TINYINT(1) DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE '42S02' SET no_such_table := 1; DO (SELECT NULL FROM temphistorysqlstestingtable LIMIT 0); INSERT INTO templog(output) values (concat('testingtable', '-first:', no_such_table, '-', NEW.currenthistoryflag, '-', NEW.updateddate, '-', OLD.updateddate)); IF no_such_table = 0 AND NEW.currenthistoryflag <> 'HISTORY' AND NEW.updateddate <> '0000-00-00 00:00:00' AND (IFNULL(NEW.testingtabletransactionid, '') <> IFNULL(OLD.testingtabletransactionid,'') OR IFNULL(NEW.updateddate, '') <> IFNULL(OLD.updateddate,'') OR IFNULL(NEW.historydate, '') <> IFNULL(OLD.historydate,'') OR IFNULL(NEW.updateduser, '') <> IFNULL(OLD.updateduser,'') OR IFNULL(NEW.currenthistoryflag, '') <> IFNULL(OLD.currenthistoryflag,'') OR IFNULL(NEW.testcolumn, '') <> IFNULL(OLD.testcolumn,'')) THEN INSERT INTO templog(output) values (concat('testingtable', '-second:', no_such_table, '-', NEW.currenthistoryflag, '-', NEW.updateddate, '-', OLD.updateddate)); INSERT INTO temphistorysqlstestingtable (transactionids) VALUES ( NEW.testingtabletransactionid ); END IF; END; /// DELIMITER ; INSERT INTO testingtable (testcolumn) VALUES ('test'); UPDATE testingtable SET testcolumn = 'test2' WHERE testcolumn='test'; UPDATE testingtable SET testcolumn = 'test' WHERE testcolumn='test2'; CREATE TEMPORARY TABLE temphistorysqlstestingtable (transactionids BIGINT); UPDATE testingtable SET testcolumn = 'test2' WHERE testcolumn='test'; SELECT * FROM templog order by templogtransactionid desc limit 50; UPDATE testingtable SET testcolumn = 'test' WHERE testcolumn='test2'; SELECT * FROM templog order by templogtransactionid desc limit 50;