SQL Exception Inserting Transactions into ARRFSYSAUDITLOG
Article ID: 108577
Updated On:
Products
CA Rapid App Security
CA Advanced Authentication
CA API Gateway
Issue/Introduction
We are getting some SQL Exceptions in arcotriskfort.log and because of this Transactions are not logged in table ARRFSYSAUDITLOG.
Tue Jul 10 06:23:09.012 2018 INFO: pid 2436 tid 5380: 8: 3:608: CallerId[], OrgName[ORGNAME], RelatedInstId[1], RelatedTxnId[865]
Tue Jul 10 06:23:09.012 2018 INFO: pid 2436 tid 5380: 2: 3:608: UDS Log : Successfully retrieved the user [[email protected]] for organization [ORGNAME]
Tue Jul 10 06:23:09.075 2018 WARNING: pid 2436 tid 5380: 8: 3:608: SQLException in ARRFDbOperations::insertSysAuditLog : [[DB: Sql Error]]
Tue Jul 10 06:23:10.091 2018 WARNING: pid 2436 tid 5380: 8: 3:608: SQLException in ARRFDbOperations::insertSysAuditLog : [[DB: Sql Error]]
Tue Jul 10 06:23:09.012 2018 INFO: pid 2436 tid 5380: 8: 3:608: CallerId[], OrgName[ORGNAME], RelatedInstId[1], RelatedTxnId[865]
Tue Jul 10 06:23:09.012 2018 INFO: pid 2436 tid 5380: 2: 3:608: UDS Log : Successfully retrieved the user [[email protected]] for organization [ORGNAME]
Tue Jul 10 06:23:09.075 2018 WARNING: pid 2436 tid 5380: 8: 3:608: SQLException in ARRFDbOperations::insertSysAuditLog : [[DB: Sql Error]]
Tue Jul 10 06:23:10.091 2018 WARNING: pid 2436 tid 5380: 8: 3:608: SQLException in ARRFDbOperations::insertSysAuditLog : [[DB: Sql Error]]
Cause
In Previous releases before 9.0, we used to run the SQL scripts manually and it was working fine for all.
In 9.0 release, we came up with simplified installer which executes the SQL scripts, bootstraps and made the server UP and running. We eliminated the manual intervention in this release. But here in this, we found that one of the SQL scripts ( create sequence ) failed to execute from Installer which resulted in this error. However we missed capturing this error in the installer log too as we execute the complete SQL script file at once.
In 9.0 release, we came up with simplified installer which executes the SQL scripts, bootstraps and made the server UP and running. We eliminated the manual intervention in this release. But here in this, we found that one of the SQL scripts ( create sequence ) failed to execute from Installer which resulted in this error. However we missed capturing this error in the installer log too as we execute the complete SQL script file at once.
Environment
AA 9.x
MS SQL Server
MS SQL Server
Resolution
Follow the steps outlined to resolve the issue --
1. Please Stop the CA AA services ( Risk + Case Management + Strong Auth)
2. Stop the application Application server
3. Please make sure you have backup of DB and then run the SQL
DROP PROCEDURE GETNEXTSEQID;
GO
CREATE PROCEDURE GETNEXTSEQID
@pServer_ID ARADMIN_TXID_SERVER_ID_TYPE,
@pInstance_ID ARADMIN_TXID_INSTANCE_ID_TYPE,
@pIP_Address ARADMIN_TXID_IP_ADDRESS_TYPE,
@pBlock_Size INT,
@pReturn_Seq_ID ARADMIN_TXID_USED_SEQ_NUM_TYPE OUTPUT
AS
DECLARE @lnewseq INT;
DECLARE @errorvar INT SET @errorvar = -1 ;
SET NOCOUNT ON;
SELECT @errorvar = USEDSEQNUM FROM ARADMINTXID
WHERE SERVERID = @pServer_ID AND INSTANCEID = @pInstance_ID;
IF @errorvar = -1
BEGIN
SET @pReturn_Seq_ID = 0
INSERT INTO ARADMINTXID
(SERVERID, INSTANCEID, IPADDRESS, USEDSEQNUM)
VALUES (@pServer_ID, @pInstance_ID, @pIP_Address, @pBlock_Size);
RETURN
END
ELSE
BEGIN
SET @pReturn_Seq_ID = @errorvar;
SET @lnewseq = @pReturn_Seq_ID + @pBlock_Size;
UPDATE ARADMINTXID SET USEDSEQNUM = @lnewseq, IPADDRESS = @pIP_Address
WHERE SERVERID = @pServer_ID AND INSTANCEID = @pInstance_ID;
END
RETURN
4. Recompile the Procedure as SQL server may have it cached, a Restart of SQL server can also be done if possible.
5. Start the CA AA services ( Risk + Case Management + Strong Auth)
6. Start the Application server
1. Please Stop the CA AA services ( Risk + Case Management + Strong Auth)
2. Stop the application Application server
3. Please make sure you have backup of DB and then run the SQL
DROP PROCEDURE GETNEXTSEQID;
GO
CREATE PROCEDURE GETNEXTSEQID
@pServer_ID ARADMIN_TXID_SERVER_ID_TYPE,
@pInstance_ID ARADMIN_TXID_INSTANCE_ID_TYPE,
@pIP_Address ARADMIN_TXID_IP_ADDRESS_TYPE,
@pBlock_Size INT,
@pReturn_Seq_ID ARADMIN_TXID_USED_SEQ_NUM_TYPE OUTPUT
AS
DECLARE @lnewseq INT;
DECLARE @errorvar INT SET @errorvar = -1 ;
SET NOCOUNT ON;
SELECT @errorvar = USEDSEQNUM FROM ARADMINTXID
WHERE SERVERID = @pServer_ID AND INSTANCEID = @pInstance_ID;
IF @errorvar = -1
BEGIN
SET @pReturn_Seq_ID = 0
INSERT INTO ARADMINTXID
(SERVERID, INSTANCEID, IPADDRESS, USEDSEQNUM)
VALUES (@pServer_ID, @pInstance_ID, @pIP_Address, @pBlock_Size);
RETURN
END
ELSE
BEGIN
SET @pReturn_Seq_ID = @errorvar;
SET @lnewseq = @pReturn_Seq_ID + @pBlock_Size;
UPDATE ARADMINTXID SET USEDSEQNUM = @lnewseq, IPADDRESS = @pIP_Address
WHERE SERVERID = @pServer_ID AND INSTANCEID = @pInstance_ID;
END
RETURN
4. Recompile the Procedure as SQL server may have it cached, a Restart of SQL server can also be done if possible.
5. Start the CA AA services ( Risk + Case Management + Strong Auth)
6. Start the Application server
Feedback
Powered by
