He went to make a simple function, following the four steps described above.
It seems OK. The function runs without errors, but it always returns zero.
delimiter // drop function if exists update_ratio // create function update_ratio() RETURNS INT begin declare sleep_wait int default 5; declare start int default 1; declare finish int default 1; set start = (select counter from mytable); do sleep(sleep_wait); set finish = (select counter from mytable); return (finish-start)/sleep_wait; end $$ delimiter ;
Mystery! Running the statements manually gives always a sensible result. Using triggers to monitor the table shows that indeed it is updated many times per second, but the function returns always zero.
More puzzling is the fact that if we convert the function to a procedure, it gives the wanted result.
The solution to the mystery is found in the MySQL online manual
A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log.
In other words, it means that all tables referenced in a stored functions are locked when the function starts. Therefore the external procedures that were updating the table will have to wait until the function's end before updating. When the function reads from the table, it gets always the same record counter, because no updates were happening in the meantime. That's why the second read is the same as the first one, and the result is zero.
What should you do then?
One option is to convert the function into a procedure:
That gets the job done. If you want to get the result into a variable, you can do it with two statements.
delimiter // drop procedure if exists show_update_ratio // create procedure show_update_ratio() begin declare sleep_wait int default 5; declare start int default 1; declare finish int default 1; select counter into start from mytable; do sleep(sleep_wait); select counter into finish from mytable; SET @UPS := (finish-start)/sleep_wait; end //
If you don't change the last SET into a SELECT and just display the value.
call show_update_ratio(); select @UPS;
Another option is using several SQL commands from your application. Also in this case, make sure that you are NOT wrapping this code inside a transaction, or you will get the same result in both queries
# WRONG! set autocommit=0; BEGIN; select counter into @start from mytable; set @start = start; do sleep(5); select counter into @finish from mytable; select (@finish - @start) / 5 as UPS;If you go for this solution (or even the stored procedure), make sure that you are either using autocommit, or commit after each query if you must use a transaction.