Solved. This seemes to have been an issue of "matched rows" vs. "changed
rows". The use of stored procedures was not part of the scope of the
problem. The different behaviors were the result of how different
clients connect to the server.
This thread helped me understand the problem:
http://lists.mysql.com/perl/72.
It seems that calling ROW_COUNT() when connected via DBD::mysql returns
"matched rows" and not "changed rows"
I was not able to resolve this by adding "mysql_found_rows=0" to the DSN
passed to DBD::mysql. It seemed to not have any effect.
I was able to overcome this in my situation by changing this:
UPDATE categories c,temporary_cat_status t
SET c.categories_status=0
WHERE c.categories_id=t.cid
AND t.enabledsubcats=0
to this:
UPDATE categories c,temporary_cat_status t
SET c.categories_status=0
WHERE c.categories_id=t.cid
AND t.enabledsubcats=0
AND c.categories_status=1
This ensured that the matched rows and changed rows were always the same.
Thanks.
On 02/15/2010 04:47 PM, Erik wrote:
I have a stored procedure which produces different results when called
from Perl DBI compared to when it is called from the other MySQL
clients, including the MySQL command line.
The procedure does not take any arguments and does not retrn any data.
It simply processes data in a few tables, iterating over a WHILE loop
until some condition is met. Basically it looks like this:
BEGIN
DECLARE rowcnt INT DEFAULT 1;
[Some queries to get things set up]
WHILE ( rowcnt > 0 ) DO
TRUNCATE TABLE scratch;
INSERT INTO scratch
SELECT .... ;
UPDATE datatable1,scratch
SET datatable1.x = 1
WHERE [conditons] ;
SET rowcnt=ROW_COUNT();
[call to procedure to log value of rowcnt]
END WHILE;
END
During the second iteration the UPDATE statment is where the behavior
changes depening on the database client. As far as I can tell the tables
contain identical data at this point when using DBI or another client. I
have compared the contents of the tables at various stages and it
appears the ROW_COUNT function is not returning the correct value.
I have compared the results of SHOW SESSION VARIABLES to ensure that the
connections are set up the same. But I don't see how a stored procedure
which is run completely on the server can function differently when it
is called by different clients.
I would greatly appreciate any suggestions you may have to assist me in
troubleshooting.
I have tested with:
Client side: perl(5.10.0-24ubuntu4), libdbi-perl( 1.609-1),
libdbd-mysql-perl (4.011-1ubuntu1)
Server: mysql(5.0.22-4~bpo.1)
and
Client side: perl(5.8.8-7etch6), libdbi-perl(1.53-1etch1),
libdbd-mysql-perl(3.0008-1)
Server: mysql(5.0.22-4~bpo.1)
and
Client side: perl(5.10.0-19lenny2), libdbi-perl(1.605-1),
libdbd-mysql-perl(4.007-1+lenny1)
Server: mysql(5.0.51a-24+lenny2+spu1)
