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:
None 
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 8:22] Paul Maunders
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.
[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".