Solved

Debugging MySQL procedure

Posted on 2014-01-02
3
586 Views
Last Modified: 2014-01-02
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)
0
Comment
Question by:shaunwingin
  • 2
3 Comments
 

Author Comment

by:shaunwingin
ID: 39751313
I am using MySQL workbench and I noticed a state: "waiting for cable metadata lock"

Can you please assist to resolve the error.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39751315
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
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 39751327
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
Because your company can’t afford for you to make SEO mistakes, you’ll want to ensure you’re taking the right steps each and every time you post a new piece of content. This list of optimization do’s and don’ts can help you become an SEO wizard.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
This video teaches users how to migrate an existing Wordpress website to a new domain.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question