Error 'Database error obtaining sequence number'

David Registro shared this problem 2 years ago
Resolved

You may see one of the errors below on a new install of YF, or just after restoring a Yellowfin DB:

Error occurred getting sequence Nbr: Could not find stored procedure 'fnGetAutoID2'.


Error: java.lang.Exception: Database error obtaining sequence number


These errors are usually something we with MySQL.

The issue itself stems from a function that is used to create auto-id's in the Yellowfin DB tables

The function is sometimes not included with DB dumps, or it is there, but the DB user does not have privileges to run it. In the instance of a new install, the DB user used for the install did not have the privileges to create the funtion.

Best Answer
photo

To fix the above issues, you need to ;


  1. Ensure the Yellowfin DB user account for access to the MySQL db has the necessary privileges required to run stored procedure. Run the command below against your Yellowfin DB: GRANT SELECT ON MySQL.Proc to <your YF user>


    Then restart YF.

  2. If the actual function is missing, running the following query will create it for you. Once again run this against your Yellowfin DB:
    DELIMITER //
    CREATE FUNCTION fnGetAutoID2 (sTableName varchar(40), increment int)
    RETURNS int
    BEGIN
    DECLARE seqNum INT;
    IF sTableName IS NULL THEN RETURN NULL; END IF;
    IF increment IS NULL THEN RETURN NULL; END IF;
    UPDATE KeyGenerator
    SET LastSequenceNumber = LastSequenceNumber + increment
    WHERE TableName = sTableName;
    SELECT LastSequenceNumber INTO seqNum
    FROM KeyGenerator
    WHERE TableName = sTableName LIMIT 1;
    RETURN seqNum;
    END//
    DELIMITER ;// 


    Then restart YF.


    Regards,

    David


Comments (1)

photo
1

To fix the above issues, you need to ;


  1. Ensure the Yellowfin DB user account for access to the MySQL db has the necessary privileges required to run stored procedure. Run the command below against your Yellowfin DB: GRANT SELECT ON MySQL.Proc to <your YF user>


    Then restart YF.

  2. If the actual function is missing, running the following query will create it for you. Once again run this against your Yellowfin DB:
    DELIMITER //
    CREATE FUNCTION fnGetAutoID2 (sTableName varchar(40), increment int)
    RETURNS int
    BEGIN
    DECLARE seqNum INT;
    IF sTableName IS NULL THEN RETURN NULL; END IF;
    IF increment IS NULL THEN RETURN NULL; END IF;
    UPDATE KeyGenerator
    SET LastSequenceNumber = LastSequenceNumber + increment
    WHERE TableName = sTableName;
    SELECT LastSequenceNumber INTO seqNum
    FROM KeyGenerator
    WHERE TableName = sTableName LIMIT 1;
    RETURN seqNum;
    END//
    DELIMITER ;// 


    Then restart YF.


    Regards,

    David