| Bug #67451 | In mysql cluster 7.2 trigger bug occured. this is seriou reliability problem. | ||
|---|---|---|---|
| Submitted: | 2 Nov 2012 2:01 | Modified: | 11 Dec 2014 6:17 |
| Reporter: | ws lee | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S1 (Critical) |
| Version: | mysql5.5.27-ndb7.2.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[6 Nov 2012 11:33]
MySQL Verification Team
Thank you for the report. Verified as described.
[10 Apr 2013 21:21]
Eddy Pulido
To avoid having to calculate the number of unique logins that have transpired in a day, we have a summary table which has the trigger below. Every time a unique login occurs, a new value is calculated into the corresponding row of the summary table. We've made sure each SQL Node has this trigger since MySQL Cluster does not replicate those files to all SQL Nodes.
However, after I've tried shutting down SQL Nodes randomly and bringing them back online, the summary table no longer gets updated when a unique login occurs.
Here's the trigger code.
CREATE TRIGGER summary_trigger
AFTER INSERT
ON login
FOR EACH ROW
BEGIN
IF (SELECT COUNT(1) FROM
(SELECT 1 FROM login
WHERE
MONTH(login_day) = MONTH(NOW())
AND
login.bussiness_id = new.bussiness_id
AND
login.app_id = new.app_id
AND
login.company_id = new.company_id
LIMIT 2) t) = 1
THEN
/* this User(new.bussiness_id) not in login table this month, update count */
UPDATE login_summary SET unique_sessions = unique_sessions + 1
WHERE
login_summary.login_month = MONTH(NOW())
AND
login_summary.app_id = new.app_id
AND
login_summary.company_id = new.company_id;
IF ROW_COUNT() = 0 THEN
/* update resulted in 0 rows being modified so do insert */
INSERT INTO login_summary VALUES (MONTH(NOW()), new.company_id, new.app_id, 1);
END IF;
END IF;
END
I'm using the following version of MySQL, and I'm running on SLES 11.x:
mysql Ver 14.14 Distrib 5.5.29-ndb-7.2.10, for Linux (x86_64) using readline 5.1
This behavior is consistent and we can reproduce regularly. The problem also surfaces when the SQL Nodes run out of connections, say, if we don't loadbalance the JDBC URL.
my 2 cents, fwiw.
[11 Dec 2014 6:16]
MySQL Verification Team
Marking this as duplicate of Bug #74751.
Description: in mysql cluster 7.2 trigger bug occured. during update any record, timestamp field of that record was modified in '0000-00-00 00:00:00' value. this is seriou reliability problem. mysql 7.2 version is very very unstable. i unbeliveable this 7.2 version is production release. How to repeat: 1. create tab le test1 CREATE TABLE `test1` ( `id` int(11) NOT NULL, `created_t` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `modified_t` timestamp NULL DEFAULT NULL, `id2` smallint(6) NOT NULL, `name` varchar(256) NOT NULL, `value` varchar(128) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx1` (`id2`,`name`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8; 2. create trigger trg1 delimiter // drop trigger trg1 // CREATE TRIGGER trg1 BEFORE UPDATE ON test1 FOR EACH ROW BEGIN IF (old.created_t <> new.created_t) or (old.modified_t <> new.modified_t) THEN insert into virtualtable_for_errhandler values(0); ELSE set new.modified_t=now(); END IF; End // delimiter ; 3. insert data to test1 insert into test1(id,created_t,id2,name,value) values(1,now(),1,'test1','1'); 4. select test1 mysql> select * from test1; +----+---------------------+------------+-----+-------+-------+ | id | created_t | modified_t | id2 | name | value | +----+---------------------+------------+-----+-------+-------+ | 1 | 2012-11-02 10:58:50 | NULL | 1 | test1 | 1 | +----+---------------------+------------+-----+-------+-------+ 1 row in set (0.00 sec) 4. update dat of id=1 mysql> UPDATE test1 SET value = '2' WHERE name = 'test1' AND id2 = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 5. re select test1 mysql> select * from test1; +----+---------------------+---------------------+-----+-------+-------+ | id | created_t | modified_t | id2 | name | value | +----+---------------------+---------------------+-----+-------+-------+ | 1 | 0000-00-00 00:00:00 | 2012-11-02 10:59:15 | 1 | test1 | 2 | +----+---------------------+---------------------+-----+-------+-------+ 1 row in set (0.00 sec) 6. show created_t 0000-00-00 00:00:00 unbeliveable value. shocking. i suggest mysql cluster 7.2 version must degrade in development relase.