MySQL Bugs: #15454: watch

Bug #15454	watch
Submitted:	3 Dec 2005 4:38	Modified:	29 Mar 2011 18:35
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
could we make a watch, like select function do in sockets?
i have a system that always check for table updates, like when some one insert or delete or update a table i select (sum()) a column that is always changed and check with old one

could we make a watch in server? when somethings happen servers contact client and send "hey you watch changed!" it would help me with less bandwidth use

:) thanks and sorry poor english, brazilian guy ;)

How to repeat:
create a watch in server side

Suggested fix:
??

Hi Roberto,
if the goal is to decrease client<->server bandwidth usage, it could be done with a stored procedure or function (using MySQL 5.0):
if the table you want to watch is named T, create a stored function WATCH_TABLE_T, with return type INT. Say you want it to return 0 when the table has changed and 1 when there is an error. In that function, put a loop which tests if the value changed, if it didn't change, then call SLEEP(2) and iterate the loop, otherwise break out of the loop and return 0. If some error (if table was dropped for example), return 1.
Then in your client you just need to SELECT WATCH_TABLE_T();
The SELECT will not return until the table is changed or an error occurs. So this is a solution which uses no bandwidth: all "wait for the table to change" work will be done on server, client is just waiting for the notification.
You can read http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html to help.

here is my procedure:

CREATE PROCEDURE spd_monitor_output_spool (IN C_ID BIGINT)
BEGIN
  WHILE (SELECT COUNT(*) FROM output_spool WHERE client_id=C_ID AND status!='end')<=0 DO
    DO SLEEP(0);
  END WHILE;
END;

my clients will call this:

CALL spd_monitor_output_spool(1);
CALL spd_monitor_output_spool(2);
CALL spd_monitor_output_spool(3); ....

but if i have many clients (10) my server crash with 125 threads!!!! what's wrong? i had more than 10 CALL process with SHOW PROCESSLIST

here is my procedure:

CREATE PROCEDURE spd_monitor_output_spool (IN C_ID BIGINT)
BEGIN
  WHILE (SELECT COUNT(*) FROM output_spool WHERE client_id=C_ID AND status!='end')<=0 DO
    DO SLEEP(0);
  END WHILE;
END;

my clients will call this:

CALL spd_monitor_output_spool(1);
CALL spd_monitor_output_spool(2);
CALL spd_monitor_output_spool(3); ....

but if i have many clients (10) my server crash with 125 threads!!!! what's wrong? i had more than 10 CALL process with SHOW PROCESSLIST

If the was a crash of serverf, please, send the appropriate part of the error log. Server version will be also useful to know.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

solved with others ideas (memcached, file locks, sql cache)
i will close it, it´s something more RPC, not a database problem