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

Omie
  • 103
  • 1
  • 2
  • 7
  • This sounds like an issue with phpmyadmin, not the script. (serverfault?) – Kevin Peno Jan 08 '10 at 19:50
  • Also, one thing I notice is that your DECLARE must appear inside the `BEGIN ... END;` See :http://dev.mysql.com/doc/refman/5.0/en/declare.html – Kevin Peno Jan 08 '10 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. http://img14.imageshack.us/img14/6642/77392131.png After that I stopped everything to save my processor. –  Jan 08 '10 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. –  Jan 08 '10 at 21:27

2 Answers2

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.

RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80