Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am using GetAncestry function found at this post, The problem is when I try to use it with select query Mysql hang, I am not sure why it is happens my Mysql version is "5.5.16" any help appriciable

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM pctable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;
share|improve this question
Please post your data into the question or in SQLFiddle – RolandoMySQLDBA Apr 12 at 16:20
BTW another question: Are your tables MyISAM or InnoDB ??? – RolandoMySQLDBA Apr 12 at 16:55
my tables type InnoDB – Ayaz Apr 12 at 18:09
I have just replaced column name and table name, Your other functions working well, but I stuck with GetAncestry function. – Ayaz Apr 12 at 18:18

2 Answers

up vote 0 down vote accepted

Here is my working theory: Because you have the table data in InnoDB it may subjected to MVCC and Transaction Isolation in the midst of the InnoDB Infrastructure.

Please notice in my original post that I used MyISAM tables, which are not subject to ACID compliance.

Make a copy of the table, convert it to MyISAM, and run GetAncestry against it. You should find better performance.

Give it a Try !!!

share|improve this answer
Why would MVCC get in the way of a recursive query? There are only selects in the procedure which should never be blocked by any other transaction. – a_horse_with_no_name Apr 12 at 22:15
up vote 0 down vote

The problem here is your use of SELECT ... INTO.

As I illustrated in my answer to this question, there are two ways to place the value returned from a query into a variable: SELECT ... INTO (as you're doing) or by using a scalar subquery as a scalar operand -- that is, assigning the value returned from a scalar subquery to the variable, using SET.

The two techniques do not have the same result when no records are found.

Specifically, SELECT ... INTO will never return the NULL you are looking for in a no-record-found situation, because when no records are found, it not only doesn't return anything... it's as if the statement had not been executed and the value of your variable remains what it was before you ran the query.

So, you're in an infinite loop. As soon as you stop matching rows, your variable's value stops changing and persists at its previous value.

Your workaround is to use the scalar subquery directly inside the IFNULL(), because this does return NULL (as you appear to be expecting) when no records are matched:

SET ch = IFNULL((SELECT parent_id FROM pctable WHERE id = ch),-1);

From the documentation for SELECT ... INTO:

If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.