Solved

Debugging MySQL procedure

Posted on 2014-01-02
3
590 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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 …
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

730 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