1

I'm on Windows 7 ultimate 32bit + xampp 1.7.2 [MySQL v5.1.37]

This is my stored procedure :

delimiter //
CREATE PROCEDURE updatePoints(IN parentid INT(5),IN userid INT(5))
DECLARE chpoints INT(5);
BEGIN
 SELECT points INTO chpoints FROM quiz_challenges WHERE id = parentid;
 UPDATE quiz_users SET points = points + chpoints WHERE forumid=userid;
END;
//                                   
delimiter ;

At first it was showing error 1064 while creating stored procedure. I added delimiters part and when I tried running the query from phpmyadmin, Firefox went into not responding state. After that I started Internet Explorer and tried opening my pages which use the same database, it worked fine. However, I tried opening phpmyadmin and IE went into not responding state as well. I restarted both servers. Later restarted PC. Tried again but its same behavior.

So whats wrong with this tiny little code ? Am I missing something which might be causing infinite loop ?

Thanks

4
  • This sounds like an issue with phpmyadmin, not the script. (serverfault?)
    – Kevin Peno
    Jan 8, 2010 at 19:50
  • Also, one thing I notice is that your DECLARE must appear inside the BEGIN ... END; See :dev.mysql.com/doc/refman/5.0/en/declare.html
    – Kevin Peno
    Jan 8, 2010 at 19:51
  • I moved DECLARE inside BEGIN block and it has surely made some difference. Now firefox doesnt go into not responding state, I can use it but it says waiting for localhost and my processor hits 80c mark. Nothing happens after that. Screenshot after ~1.30 mins of waiting. img14.imageshack.us/img14/6642/77392131.png After that I stopped everything to save my processor.
    – Omie
    Jan 8, 2010 at 20:14
  • I'm gonna say definitely phpMyAdmin after looking at the screen shot... try running the query in something like SqlYog or a similar program. mySql isn't freezing, Apache is.
    – md5sum
    Jan 8, 2010 at 21:27

2 Answers 2

1

In my experience phpmyadmin does not like the use of delimiter // inside the SQL query window. Instead, there is a "delimiter" text box in the phpmyadmin SQL query window, the contents of which are set to ";" by default. Enter "//" in this text box instead of ";", and try your query with the delimiter // and delimiter ; lines omitted.

0

OBSERVATION #1

The order is incorrect. The DECLARE chpoint INT(5) should be after BEGIN, not before

delimiter //
CREATE PROCEDURE updatePoints(IN parentid INT(5),IN userid INT(5))
BEGIN
 DECLARE chpoints INT(5);
 SELECT points INTO chpoints FROM quiz_challenges WHERE id = parentid;
 UPDATE quiz_users SET points = points + chpoints WHERE forumid=userid;
END;
//                                   
delimiter ;

OBSERVATION #2

You should make sure of the following for quiz_users

  • forum_id is indexed
  • points is not indexed (That's right, I said not indexed because the value would be incremented and cause the BTREE for that column's index to shuffle that key's place in the index).

OBSERVATION #3

If the PHP script is iterating parentid and userid values, BIG OUCH !!!

This would indicate that a browser session is doing round trips to feed each call to ther stored procedure. You should think about passing all the parentid and userid combinations in a table and letting the stored procedure handle it on the server side with doing round trips from the browser session.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .