Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jan 9 '10 at 7:57

This question came from our site for professional and enthusiast programmers.

    
This sounds like an issue with phpmyadmin, not the script. (serverfault?) –  Kevin Peno Jan 8 '10 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 '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. img14.imageshack.us/img14/6642/77392131.png After that I stopped everything to save my processor. –  Omie Jan 8 '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. –  md5sum Jan 8 '10 at 21:27

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.