| Bug #20243 | before update trigger not executing with cluster, but works with myisam | ||
|---|---|---|---|
| Submitted: | 3 Jun 2006 8:22 | Modified: | 8 Dec 2006 15:55 |
| Reporter: | Paul Maunders | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S1 (Critical) |
| Version: | 5.0.21 | OS: | Linux (Linux CentOS release 4.3 (Final)) |
| Assigned to: | CPU Architecture: | Any | |
[3 Jun 2006 12:46]
Valeriy Kravchuk
Changed category to a more appropriate one.
[30 Jun 2006 14:24]
Valeriy Kravchuk
With both 5.1-BK and 5.0.23-BK I've got:
openxs@suse:~/dbs/5.0/mysql-test> cat r/ndb_20243.log
DROP TABLE IF EXISTS testtrigger;
DROP TABLE IF EXISTS testtriggerndb;
CREATE TABLE `testtrigger` (
`id` int(11) NOT NULL auto_increment,
`email` varchar(70) NOT NULL,
`email_reverse` varchar(70) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
CREATE TRIGGER ttuser_email_reverse_in
BEFORE INSERT ON testtrigger
FOR EACH ROW
SET NEW.email_reverse = REVERSE(NEW.email);
CREATE TRIGGER ttuser_email_reverse_up
BEFORE UPDATE ON testtrigger
FOR EACH ROW
SET NEW.email_reverse = REVERSE(NEW.email);
INSERT INTO `testtrigger` VALUES (1, 'bob@dontspamme.com',
'moc.emmapstnod@bob');
CREATE TABLE `testtriggerndb` (
`id` int(11) NOT NULL auto_increment,
`email` varchar(70) NOT NULL,
`email_reverse` varchar(70) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
CREATE TRIGGER ttndbuser_email_reverse_in
BEFORE INSERT ON testtriggerndb
FOR EACH ROW
SET NEW.email_reverse = REVERSE(NEW.email);
CREATE TRIGGER ttndbuser_email_reverse_up
BEFORE UPDATE ON testtriggerndb
FOR EACH ROW
SET NEW.email_reverse = REVERSE(NEW.email);
INSERT INTO `testtriggerndb` VALUES (1, 'bob@dontspamme.com',
'moc.emmapstnod@bob');
SELECT * FROM `testtrigger`;
id email email_reverse
1 bob@dontspamme.com moc.emmapstnod@bob
SELECT * FROM `testtriggerndb`;
id email email_reverse
1 bob@dontspamme.com moc.emmapstnod@bob
DROP TABLE IF EXISTS testtrigger;
DROP TABLE IF EXISTS testtriggerndb;
from the following test for MySQL's test suite:
openxs@suse:~/dbs/5.0/mysql-test> cat t/ndb_20243.test
-- source include/have_ndb.inc
-- source include/not_embedded.inc
--disable_warnings
DROP TABLE IF EXISTS testtrigger;
DROP TABLE IF EXISTS testtriggerndb;
--enable_warnings
#
# Basic test of BEFORE INSERT trigger in NDB
#
#
# Create a normal table with primary key
#
CREATE TABLE `testtrigger` (
`id` int(11) NOT NULL auto_increment,
`email` varchar(70) NOT NULL,
`email_reverse` varchar(70) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
CREATE TRIGGER ttuser_email_reverse_in
BEFORE INSERT ON testtrigger
FOR EACH ROW
SET NEW.email_reverse = REVERSE(NEW.email);
CREATE TRIGGER ttuser_email_reverse_up
BEFORE UPDATE ON testtrigger
FOR EACH ROW
SET NEW.email_reverse = REVERSE(NEW.email);
INSERT INTO `testtrigger` VALUES (1, 'bob@dontspamme.com',
'moc.emmapstnod@bob');
CREATE TABLE `testtriggerndb` (
`id` int(11) NOT NULL auto_increment,
`email` varchar(70) NOT NULL,
`email_reverse` varchar(70) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
CREATE TRIGGER ttndbuser_email_reverse_in
BEFORE INSERT ON testtriggerndb
FOR EACH ROW
SET NEW.email_reverse = REVERSE(NEW.email);
CREATE TRIGGER ttndbuser_email_reverse_up
BEFORE UPDATE ON testtriggerndb
FOR EACH ROW
SET NEW.email_reverse = REVERSE(NEW.email);
INSERT INTO `testtriggerndb` VALUES (1, 'bob@dontspamme.com',
'moc.emmapstnod@bob');
SELECT * FROM `testtrigger`;
SELECT * FROM `testtriggerndb`;
--disable_warnings
DROP TABLE IF EXISTS testtrigger;
DROP TABLE IF EXISTS testtriggerndb;
--enable_warnings
So, the results are exactly the same.
[19 Sep 2006 16:19]
Paul Maunders
Have you tested the UPDATE trigger? I can see from your example that you are doing a test with INSERT, but that worked fine for me also. The problem I was having is when you try to update the email field.... the trigger does not work.
[20 Sep 2006 12:21]
Paul Maunders
Also, I can recreate this bug with version 5.1.11
[8 Nov 2006 15:55]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.27 and/or 5.1.12, and inform about the results.
[9 Dec 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: The following bug has been repeated by several people on the mysql cluster mailing list. I have a table which contains an email address field, and another field to store the REVERSE of that e-mail address. I have then written two triggers to update the reverse_email field with the result of REVERSE(email) on any INSERTS or UPDATES to the table. When I use a MyISAM table, both triggers work fine, but when using NDBCluster, only the BEFORE INSERT trigger works. How to repeat: First, the MyISAM table: CREATE TABLE `testtrigger` ( `id` int(11) NOT NULL auto_increment, `email` varchar(70) NOT NULL, `email_reverse` varchar(70) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Dumping data for table `testtrigger` -- INSERT INTO `testtrigger` VALUES (1, 'bob@dontspamme.com', 'moc.emmapstnod@bob'); And then the NDBCluster table: CREATE TABLE `testtriggerndb` ( `id` int(11) NOT NULL auto_increment, `email` varchar(70) NOT NULL, `email_reverse` varchar(70) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Dumping data for table `testtriggerndb` -- INSERT INTO `testtriggerndb` VALUES (1, 'bob@dontspamme.com', 'moc.emmapstnod@bob'); And here are my triggers... CREATE TRIGGER ttuser_email_reverse_in BEFORE INSERT ON testtrigger FOR EACH ROW SET NEW.email_reverse = REVERSE(NEW.email); CREATE TRIGGER ttuser_email_reverse_up BEFORE UPDATE ON testtrigger FOR EACH ROW SET NEW.email_reverse = REVERSE(NEW.email); CREATE TRIGGER ttndbuser_email_reverse_in BEFORE INSERT ON testtriggerndb FOR EACH ROW SET NEW.email_reverse = REVERSE(NEW.email); CREATE TRIGGER ttndbuser_email_reverse_up BEFORE UPDATE ON testtriggerndb FOR EACH ROW SET NEW.email_reverse = REVERSE(NEW.email); Suggested fix: I couldn't think of a workaround for this.