CODEWITHSUNDEEP
phpmysqlsqltriggers
Abhishek Goyal
Abhishek Goyal

Reputation: 877

mysql strange behaviour with triggers

I have a table discussions, which stores the data of posts on my website.

Now I have 2 triggers,one of which inserts the path column when a new row is added, and another one which updates the path when a row is updated, as follows: 

CREATE TRIGGER discussions_trigger BEFORE INSERT ON discussions FOR EACH ROW
BEGIN
DECLARE next_id INT;
SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='discussions');
IF(NEW.visibility<1)
THEN
SET NEW.path = CONCAT('discussions.php?id=',next_id);
ELSEIF(NEW.visibility>=1)
THEN
SET NEW.path = CONCAT('private.php?id=',next_id);
END IF;

CREATE TRIGGER discussions_update_trigger BEFORE UPDATE ON discussions FOR EACH ROW
BEGIN
DECLARE next_id INT;
SET next_id = OLD.id;
IF(NEW.visibility<1)
THEN
SET NEW.path = CONCAT('discussions.php?id=',next_id);
ELSEIF(NEW.visibility>=1)
THEN
SET NEW.path = CONCAT('private.php?id=',next_id);
END IF;

While this works like it should, every once in a while, when I try to insert or update the table discussions, I get the following error :

User 'abhishek'@'An unknown IP' is not defined.

However, the user abhishek is allowed only from localhost.

After trying everything, I discovered that dropping the triggers, and then re-creating them solves the problem. When I got the error again, I re-created the triggers and it worked. What's the problem here?

Upvotes: 0

Views: 63

Answers (1)

visakh
visakh

Reputation: 2553

Run show triggers\G and check whether the DEFINER is set to user@localhost.

If not, then modify the DEFINER of the TRIGGER to user@localhost. The syntax is as follows:

CREATE DEFINER='username'@'localhost' TRIGGER trigger_name trigger definition

For a detailed discussion, see the comments to the question.

Upvotes: 1

Related Questions