1

I am setting up a master master replication and trying to do some tests by setting up a before update trigger.

I am getting an error when I run the code below

CREATE TRIGGER update_blogs
BEFORE UPDATE ON blogs
FOR EACH ROW
BEGIN
IF (NEW.updated_replication < OLD.updated_replication) THEN
  SET NEW= OLD ;
END IF;
END$$

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

What I am trying to do is only allow the row to be updated if the new row has a greater updated_replication(timestamp) value.

I am using mysql.

Can any one please tell me where I am wrong. How can I debug such errors? Is this any kind of syntax error?

Error that I get now

user2742122
  • 603
  • 2
  • 8
  • 15

2 Answers2

3

Two problems:

First problem: you can't SET NEW = OLD. You can only assign individual columns, not the whole row. So you could make sure the new value does not decrease:

IF (NEW.updated_replication < OLD.updated_replication) THEN
  SET NEW.updated_replication = OLD.updated_replication;
END IF;

But that will set one column and let any other columns change according to the UPDATE that spawned this trigger. That might leave you with data that doesn't agree with itself.

If you want the whole row to revert to the old column values, you'd have to write a series of assignments in the SET statement, one for each column of the row.

If you instead want to abort the whole update, then you need to learn the SIGNAL feature.

IF (NEW.updated_replication < OLD.updated_replication) THEN
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'You can\'t travel backwards in time!';
END IF;

This doesn't roll back the transaction, just the single UPDATE that spawned the trigger. Any other changes made in the same transaction are still pending.

Second problem: you haven't set the DELIMITER when defining a trigger with a compound statement. See my answer here: Create function through MySQLdb

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the answer. I have gone through the SIGNAL docs but didnt understand what the '45000' denotes. Also will "SIGNAL " it affect other queries running in the same connection after the update? – user2742122 Jun 18 '14 at 18:51
  • 1
    The manual states: "To signal a generic SQLSTATE value, use '45000', which means 'unhandled user-defined exception.'" – Bill Karwin Jun 18 '14 at 20:43
  • 1
    As for affecting other queries, no, not any more than any other error (e.g. duplicate key violation) would. If you don't catch the signal with a signal handler, then the signal causes an error which makes the trigger fail, which makes the UPDATE that spawned it fail. If that UPDATE was itself invoked from a trigger, then that trigger fails and its spawned DML operation fails, all the way up to the client's request. But subsequent requests are not affected. – Bill Karwin Jun 18 '14 at 20:46
  • Thanks that worked. One more question, can I create the trigger for all tables in my database in a single stretch. I mean can I run a loop through the tables in the database and run the create trigger code? I tried it but I have no previous experience in such mysql coding. – user2742122 Jun 19 '14 at 12:23
  • There's no way to create a trigger for all tables. It would make little sense, because each table should have its own distinct set of columns, and referencing a non-existent column name in a given table would be an error. You can get a list of all tables from INFORMATION_SCHEMA.TABLES. You can even get a list of tables that include a given column name by joining to INFORMATION_SCHEMA.COLUMNS. But you might want to keep it simple -- just write an SQL script, copy & paste the trigger definition for each table. – Bill Karwin Jun 19 '14 at 15:16
  • Thanks, but the idea behind this is to create a field updated in all tables and check that field before all updated are made. ie when a master master replication is set up it will only allow the latest update to be executed. – user2742122 Jun 19 '14 at 15:23
  • Replication does not filter based on data values. But it does update only the rows that were updated on the master. And with master-master replication, it won't allow infinite loops. I think you may be trying to solve a problem that is already handled by default replication. – Bill Karwin Jun 19 '14 at 15:40
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/55921/discussion-between-user2742122-and-bill-karwin). – user2742122 Jun 19 '14 at 15:42
0

I don't think assignment of a database record with tableA = tableB is legal which is essentially what NEW=OLD is doing.

It would be better to do some other operation if the update is not desired:

CREATE TRIGGER update_blogs
BEFORE UPDATE ON blogs
FOR EACH ROW
BEGIN
IF (NEW.updated_replication >= OLD.updated_replication) THEN
  SET NEW.invalid = True;
END IF;
END;$$

Where invalid is a column added just for this purpose. A periodically run stored procedure could then deal with such records, perhaps by deleting them.

wallyk
  • 56,922
  • 16
  • 83
  • 148
  • Thanks for the answer. So ROLLBACK; will stop the current update and leave the record as it was right? – user2742122 Jun 18 '14 at 18:29
  • I have added an image in the post can you just check that and tell me why I am getting that error? I am just trying to learn some basics. I am using xampp in windows 7 The second set of code is also giving me the same error – user2742122 Jun 18 '14 at 18:36
  • @user2742122: yep, check out the docs for [`rollback`](http://dev.mysql.com/doc/refman/5.7/en/commit.html) – wallyk Jun 18 '14 at 18:38
  • You can't roll back inside a trigger: *ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.* – Bill Karwin Jun 18 '14 at 18:40
  • @BillKarwin: I am not surprised. Therefore, use the flagging of a bad record alternative. – wallyk Jun 18 '14 at 18:41
  • @all but SET NEW.invalid = True; is also giving the same error for me. Is it something else that I am missing? – user2742122 Jun 18 '14 at 18:45
  • @user2742122: You'll have to add that column to the table. – wallyk Jun 18 '14 at 18:55