Could not find stored procedure 'fnGetAutoID2' / Database error obtaining sequence number

David Registro shared this problem 4 years ago
Resolved

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

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

Comments (1)

photo
1

This error occurs when the Yellowfin DB is missing a required Stored Procedure/Function that is used to create it's auto incrementing ID's for the DB tables.


In most cases this, occurs when a DB has been moved from one server to another, and the backup did not include the stored proc/function, or the DB user permissions have changed.


In order to resolve the issue, there are 2 things you need to do;


  1. Create the stored proc/function if missing
  2. Ensure the Yellowfin DB users (specified in the web.xml) has full access to execute the stored proc/function.



Before continuing with this, please ensure YF has been stopped.


For MySQL , you can run the queries below:


  1. 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 ;//


  1. GRANT SELECT ON MySQL.Proc to <your YF user>


For Microsoft Sql Server you can run this query;


  1. CREATE PROCEDURE [dbo].[fnGetAutoID2] ( @sTableName VARCHAR(40), @increment int )
  2. AS BEGIN TRAN DECLARE @seqNum INT IF @sTableName IS NULL RETURN NULL IF @increment IS NULL RETURN NULL UPDATE KeyGenerator WHERE TableName = UPPER(@sTableName) IF @@error <> 0 RETURN NULL COMMIT TRAN RETURN @seqNum
  3. SET @seqNum=LastSequenceNumber=LastSequenceNumber+@increment

In Oracle and Postgres, this is stored in the SEQUENCE section:

/d465039a73d9fe0fd577788750806636

If missing you can run the following query to generate it:

CREATE SEQUENCE nexteventsequence INCREMENT BY 1 START WITH <yourLastValueInDB> MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
If you still have issues please send across any errors thrown by running the above queries, and also attach the Yellowfin log files located under \yellowfin\appserver\logs.

Comments have been locked on this page!