Cannot load from mysql.proc whenever TRIGGER is created

[pestlett pestlett]

Hi,

I've made a Notify UDF to tie in with a Notify/Listen hack I've put together and now I'm trying to setup a trigger that will SELECT some IPs from a table called listen and then execute the Notify Function to each IP in the table. The Notify function works fine on its own when called from the CLI, so I'm doubtful it's that part of the trigger that causes this error whenever I try and UPDATE the users table (SELECT, DELETE &c all work fine) I get this error:

mysql> UPDATE users SET gid = 3 WHERE id = 1;
ERROR 1548 (HY000): Cannot load from mysql.proc. The table is probably corrupted
mysql> CHECK TABLE users;
+---------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| central.users | check | status | OK |
+---------------+-------+----------+----------+
1 row in set (0.00 sec)

Here is the trigger I've got so far:

DELIMITER $$

CREATE TRIGGER central.notify AFTER update ON central.users
FOR EACH ROW
BEGIN

DECLARE _i INT;
DECLARE _x INT;
DECLARE _ip VARCHAR(15);
DECLARE _yz INT;

SET _i = 0;

SET _x = (SELECT count(*) FROM listen);

IF _x > 0 THEN

WHILE _i < _x DO

SET _ip = (SELECT ip FROM central.listen LIMIT 1);

SET _yz = (SELECT Notify(_ip, 'This is a test'));

SET _i := _i + 1;

END WHILE;

END IF;

END $$

DELIMITER ;

_yz was just something I threw in to catch the value of SELECT Notify(), which is an integer.

This line
SET @_ip = (SELECT ip FROM central.listen LIMIT 1);

was originally
SET @_ip = (SELECT ip FROM central.listen LIMIT _i, 1);

but it didn't work initially, so I removed it to just test the trigger and then got the error this post is about.


Thanks for any help you may be able to offer.