Solved

Debugging MySQL procedure

Posted on 2014-01-02
3
582 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

772 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