Toad for MySQL - Community for development, administration, tuning, tips and tricks

chrisbothajr_671

Posts: 4
Registered: 9/23/11

Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Sep 23, 2011 3:09 AM

Hi,

I just finished installing Quest's latest version of `Toad for MySQL`, version 6.0.0.1714.

I'm running Windows 7 Professional, with MySQL 5.1.51 Community Edition.

When you try to debug a stored procedure, I'm getting the error that says:

"Table 'questsoftware.debugbreakpoints' doesn't exist"

I assume Toad is looking for a bunch of tables under the `questsoftware` database, in order to be able to perform a debug on a stored procedure, which I have non of.

There are no tables whatsoever under the `questsoftware` database, which I reckon was suppose to be created during the installation, correct?

So how do I go about fixing this problem to be able to debug using Toad for MySQL??

If someone can please tell me how to fix this issue, as well as why this is happening.

Thank you.
Chris

Mauritz

Posts: 314
Registered: 8/23/07

Re: Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Sep 23, 2011 9:34 AM

in response to: chrisbothajr_671

Reply

That is very strange. All the stuff needed on the server side should be created automatically when first you try to use this feature.

chrisbothajr_671

Posts: 4
Registered: 9/23/11

Re: Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Sep 23, 2011 10:16 AM

in response to: Mauritz

Reply

Well, that is true isn't it. But it didn't. Another bug on Windows 7 maybe?

So how do I go about getting those system tables in order be able to use TOAD for debugging stored procedures?

Mauritz

Posts: 314
Registered: 8/23/07

Re: Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Sep 24, 2011 1:11 PM

in response to: chrisbothajr_671

Reply

I have figured out what is going wrong (Did you by any chance get an error about missing _ToadLS too).

This will be fixed in the 3.0.1 release but here is the script that creates the needed debugging objects in the database:

CREATE DATABASE questsoftware;
CREATE TABLE questsoftware.DEBUGBREAKPOINTS (
connectionid int NOT NULL,
type varchar(10) NOT NULL,
db varchar(64) NOT NULL,
name varchar(64) NOT NULL,
line int NOT NULL,
PRIMARY KEY (connectionid,type,db,name,line)
) ENGINE=InnoDB;
CREATE TABLE questsoftware.DEBUGLOG (
id int NOT NULL AUTO_INCREMENT,
connectionid int NOT NULL,
type varchar(10) NOT NULL,
db varchar(64) NULL,
name varchar(64) NULL,
value varchar(2000) NULL,
PRIMARY KEY (id),
INDEX (connectionid)
) ENGINE=InnoDB;
CREATE PROCEDURE questsoftware.DEBUGDELETE(IN n VARCHAR(64))
READS SQL DATA
BEGIN
INSERT INTO DEBUGLOG (connectionid, type, name) VALUES (connection_id(), 'Delete', n);
END;
CREATE PROCEDURE questsoftware.DEBUGENABLE()
READS SQL DATA
BEGIN
DECLARE cid INT;
    SELECT connection_id() INTO cid;
    DO GET_LOCK(CONCAT('toadbreak_',cid),10);
DELETE FROM DEBUGLOG WHERE connectionid = cid;
END;
CREATE PROCEDURE questsoftware.DEBUGENTER(t VARCHAR(10), d VARCHAR(64), n VARCHAR(64), i VARCHAR(64))
READS SQL DATA
BEGIN
    DECLARE cid INT;
DECLARE dlm DATETIME;
DECLARE olm DATETIME;
DECLARE u INT;
SELECT connection_id() INTO cid;
IF EXISTS (SELECT 'X' FROM information_schema.routines
                WHERE routine_type = t AND LOWER(routine_schema) = 'questdebug'
                            AND LOWER(routine_name) = CONCAT(i,'impl'))
    THEN
        SELECT last_altered FROM information_schema.routines
         WHERE routine_type = t AND LOWER(routine_schema) = 'questdebug'
         AND LOWER(routine_name) = CONCAT(i,'impl') INTO dlm;
        SELECT last_altered FROM information_schema.routines
         WHERE routine_type = t AND LOWER(routine_schema) = d
         AND LOWER(routine_name) = n INTO olm;
        IF dlm < olm THEN
      SELECT 1 INTO u;
    ELSE
      SELECT 0 INTO u;
        END IF;
    ELSE
    SELECT 1 INTO u;
    END IF;
IF t = 'FUNCTION' THEN
    UPDATE DEBUGSTEPINFO SET step = 'I' WHERE connectionid = cid;
END IF;
INSERT INTO DEBUGLOG (connectionid, type, db, name, value) VALUES (cid, t, d, n, u);
IF IS_USED_LOCK(CONCAT('toadbreak_', cid)) = cid THEN
    DO GET_LOCK(CONCAT('toadbreak2_', cid), 1000000);
ELSE
    DO GET_LOCK(CONCAT('toadbreak_', cid), 1000000);
END IF;
DELETE FROM DEBUGLOG WHERE connectionid = cid;
END;
CREATE PROCEDURE questsoftware.DEBUGLEAVE()
READS SQL DATA
BEGIN
    DECLARE cid INT;

SELECT connection_id() INTO cid;
INSERT INTO DEBUGLOG (connectionid, type) VALUES (cid, 'Leave');

IF IS_USED_LOCK(CONCAT('toadbreak_', cid)) = cid THEN
    DO GET_LOCK(CONCAT('toadbreak2_', cid), 1000000);
ELSE
    DO GET_LOCK(CONCAT('toadbreak_', cid), 1000000);
END IF;

DELETE FROM DEBUGLOG WHERE connectionid = cid;
END;
CREATE PROCEDURE questsoftware.DEBUGSET(IN n VARCHAR(64), IN t ENUM('P','L'), IN v VARCHAR(20000))
READS SQL DATA
BEGIN
INSERT INTO DEBUGLOG (connectionid, type, name, value) VALUES (connection_id(), CONCAT('Value',t), n, v);
END;
CREATE TABLE questsoftware.DEBUGSTEPINFO (
connectionid int NOT NULL,
step enum('C','S','O','I') NOT NULL,
    stackdepth INT NULL,
callstackdepth INT NULL,
PRIMARY KEY (connectionid)
) ENGINE=InnoDB;
CREATE TABLE questsoftware.DEBUGTARGET (
type varchar(10) NOT NULL,
db varchar(64) NOT NULL,
name varchar(64) NOT NULL,
line int NOT NULL,
PRIMARY KEY (type,db,name,line)
) ENGINE=InnoDB;
CREATE PROCEDURE questsoftware.DEBUGTRACE(t VARCHAR(10), d VARCHAR(64), n VARCHAR(64), IN l INT)
READS SQL DATA
BEGIN
DECLARE cid INT;
    DECLARE st ENUM('C','S','O','I');
    DECLARE stsd INT;
DECLARE sd INT;
    SELECT connectionid, step, stackdepth, callstackdepth FROM DEBUGSTEPINFO
    WHERE connectionid = connection_id() INTO cid, st, stsd, sd;
    IF (st = 'I') OR (st = 'O' AND sd < stsd) OR (st = 'S' AND sd <= stsd) OR
       EXISTS (SELECT 'X' FROM DEBUGBREAKPOINTS a
                  WHERE connectionid = cid AND type = t
                                AND db = d AND name = n AND line = l) THEN
    INSERT INTO DEBUGLOG (connectionid, type, name) VALUES (cid, 'Trace', l);
    IF IS_USED_LOCK(CONCAT('toadbreak_',cid)) = cid THEN
      DO GET_LOCK(CONCAT('toadbreak2_', cid), 1000000);
    ELSE
      DO GET_LOCK(CONCAT('toadbreak_', cid), 1000000);
    END IF;
    DELETE FROM DEBUGLOG WHERE connectionid = cid;
    END IF;
END;
CREATE PROCEDURE questsoftware.DEBUGWAIT(IN cid INT)
READS SQL DATA
BEGIN
IF IS_USED_LOCK(CONCAT('toadbreak_',cid)) = connection_id() THEN
    WHILE IS_USED_LOCK(CONCAT('toadbreak2_',cid)) IS NOT NULL DO
      DO SLEEP(0.1);
    END WHILE;
ELSE
    WHILE IS_USED_LOCK(CONCAT('toadbreak_',cid)) IS NOT NULL DO
      DO SLEEP(0.1);
    END WHILE;
END IF;
END;
CREATE DATABASE questdebug;

chrisbothajr_671

Posts: 4
Registered: 9/23/11

Re: Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Sep 26, 2011 1:58 PM

in response to: Mauritz

Reply

Hi,

I have not received anything about an `_ToadLS`error.

I have tried to execute the script you provided to create the necessary debugger tables, but to no avail. The strange thing is, that when I execute that script, I get the errors that state that the tables already exist. (????)

But when you launch the debugger to try and debug a stored procedure, you get the error that i.e. table `questsoftware.DEBUGBREAKPOINTS` doesn't exist??

This is beyond confusing; When you try and create the so called `missing` tables, you get the error that the tables already exist, and when you try and debug a stored procedure, you get the error that says that the same tables are missing?? What the heck is going on??

You also don't see any tables listed under the tables list. I can see the indices and stored procedures of the `questsoftware` database, but NO tables?

Mauritz

Posts: 314
Registered: 8/23/07

Re: Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Sep 26, 2011 2:10 PM

in response to: chrisbothajr_671

Reply

To be honest it sounds like there is something fishy going on with your server.

Could you try just dropping the questdebug & questsoftware database and rerun the script I attached earlier.

Mauritz

Posts: 314
Registered: 8/23/07

Re: Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Sep 26, 2011 2:10 PM

in response to: Mauritz

Reply

Also after you run the script to recreate the objects needed restart Toad just to be on the safe side before you try to actually debug.

McScreech

Posts: 13
Registered: 9/30/11

Re: Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Sep 30, 2011 11:00 AM

in response to: Mauritz

Reply

I was unable to run the script suggested by Mauritz without a few mods.

Before the first CREATE PROCEDURE, I inserted a 'delimiter xxx' command, then after the 'END;' of each procedure insert 'xxx', before subsequent sql blocks change the delimiter back to ';' then switch between the ';' and 'xxx' delimiters as required. (i.e. before CREATE TABLE questsoftware.DEBUGSTEPINFO, CREATE PROCEDURE questsoftware.DEBUGTRACE and CREATE DATABASE questdebug)

I did it this way to avoid moving code snippets around to group all procedure definitions together in case the sequence provided was relevant. If sequence is not important you could just group all plain sql statements (only CREATE TABLE and DATABASE commands) at the beginning and only have to change the delimiter twice.

I was able to create the tables and procedures and am now beginning to learn how to use the debugger.

McS

Beauty! Thanx Henrik.

When I came back to check for new replies I saw the post for the patch upgrade. I did a fresh install of ToadMySQL 6.0.1.1723 and the debug scripts were automatically created when I started by first debug session.

BTW, my first attempt mentioned above _appeared_to_ work also, but it's very encouraging to see such a quick response from the developers.

McS

Message was edited by: McScreech

alexx

Posts: 1
Registered: 12/21/11

Re: Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Dec 21, 2011 1:29 PM

in response to: McScreech

Reply

I ran the script in the thread below. I realize it's an old thread, but I couldn't find anything else anywhere via Google. I can't debug, as Toad is generating erroneous sql:

drop procedure if exists questdebug.612fbb7136c22b92265d965dd9d8bd9d95556388impl;
CREATE PROCEDURE questdebug.612fbb7136c22b92265d965dd9d8bd9d95556388impl(IN depositAmount decimal)
BEGIN
CALL questsoftware.DEBUGSET('depositAmount','L',depositAmount);
CALL questsoftware.DEBUGSET('depositAmount','P',depositAmount);
CALL questsoftware.DEBUGTRACE('PROCEDURE','bank','update_member_account_balance',1);IN depositAmount decimal(19.2), IN memberId BIGINT)
BEGIN
declare rowId bigint(20);
declare newBalance, oldBalance decimal(19,2);
select current_balance from bankAccount where bankmember_id = memberId into oldBalance;
select rowId from bankAccount where bankmember_id = memberId into rowId;
set newBalance = oldBalance - depositAmount;
update bankAccount set bankAccount.current_balance=newBalance where bankAccount.id = rowId;
end;
END

McScreech

Posts: 13
Registered: 9/30/11

Re: Unable To Debug Stored Procedures Because Of Missing Debugger Tables
Posted: Dec 22, 2011 6:38 AM

in response to: alexx

Reply

@alexx: I have not yet received a working response to this issue. Debugging SPs _still_ does not work for me (I have actually given up trying to use this tool). Sorry, I can't help. McS

Toad World

Downloads

Welcome, Guest