Error 'Database error obtaining sequence number'

David Registro shared this problem 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.

The same problem 0

Best Answer

David Registro ●

To fix the above issues, you need to ;

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.

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

Replies (1)

David Registro ● ●

To fix the above issues, you need to ;

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.

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

Reply URL