Re: Cannot load from mysql.proc whenever TRIGGER is created

[Pestlett .]

It's okay, I found a (sadly very convoluted) way around it. Here it is for those who may be interested.

I changed the listen table so it has three columns a position column (UNIQUE), an ip column and an online column. When someone begins listening for notification they firstly check if their IP address is in the table, if so it will set the online column to 1, and if it's not then it will COUNT(*) how many rows are in the listen table and then insert their position as the return of the COUNT(*) query - 1, with their IP address in the ip column and online set to 1. As the UDF sends the notification over UDP it doesn't matter that much if the listener is online or not, but it's still nice to have.

If the listen table's pos column goes out of the 0-n sequence due to a rouge DELETE FROM listen WHERE.../or UPDATE listen SET pos =..., it would have to be cleared and then let the clients repopulate it again, which is the major downfall of this approach.

The TRIGGER below is called to replace my old way of looping through each row of a table, which currently seems impossible to do with MySQL. I would still really like to see a way of doing it like that as I can't use a prepared statement in the TRIGGER.


DELIMITER $$

CREATE TRIGGER notify AFTER update ON users
FOR EACH ROW
BEGIN

SET @i := 0;

SELECT count(*) INTO @x FROM listen;

IF @x > 0 THEN

WHILE @i < @x DO

SELECT COUNT(ip) INTO @online FROM listen WHERE pos = @i AND online = 1;

IF @online > 0 THEN

SELECT ip INTO @ipaddr FROM listen WHERE pos = @i;

SELECT Notify(@ipaddr, 'This is useless data') INTO @yz;

END IF;

SET @i := @i + 1;

END WHILE;

END IF;

END; $$

DELIMITER ;

I realised half-way through that it would have been easier to use the sys_exec() UDF (http://www.mysqludf.org/lib_mysqludf_sys/index.php) with a Ruby Script and had the clients who listen register on a flat file, but I wanted to see if I could do it with as much MySQL as possible and am partially chuffed with myself even if in the end it looks quite ugly.