Debugging MySQL procedure

Say,

When I call the procedure - it hangs. Within the procedure are several "calls" to outside procedures, how can I trace which procedures are causing the problem?

Is there a way to have it return certain statistics as it runs? (feedback)
shaunwinginAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Hi.

MySQL unfortunately does not have a PRINT statement that I am aware of; therefore, one approach is to create a table specifically for logging activity within your procedures.  Subsequently, you could INSERT records into the log as the procedure processes.  For some code, especially on my web services, I will include a debug parameter that I can toggle to true when I want to get log information, avoiding large extraneous table if procedure runs often.

If you trap date/time, it may give you a sense of duration.

Another method is to use debugging tools, such as the Visual Studio debugger:
http://dev.mysql.com/doc/refman/5.5/en/connector-net-visual-studio-debugger.html

SHOW PROFILE also may be useful:
http://dev.mysql.com/doc/refman/5.1/en/show-profile.html

I hope that helps!
0
 
shaunwinginAuthor Commented:
I am using MySQL workbench and I noticed a state: "waiting for cable metadata lock"

Can you please assist to resolve the error.
0
 
Kevin CrossChief Technology OfficerCommented:
I am using MySQL workbench and I noticed a state: "waiting for cable metadata lock"
- Refer to manual page http://dev.mysql.com/doc/refman/5.0/en/innodb-monitors.html
- query INFORMATION_SCHEMA tables for locks
SELECT *
FROM INFORMATION_SCHEMA.INNODB_LOCKS l
WHERE EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS w
    WHERE w.BLOCKING_TRX_ID = l.LOCK_TRX_ID
)
;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.