Troubleshoot the error:
Schedule a restart of the Apache Tomcat service as soon as possible. This will clear up many causes for this type of error.
If the problem is still persisting after restarting the Apache Tomcat service, proceed with the troubleshooting steps below.
Clearly identify which table is having an error by using the Footprints.log file. Look for the error messages about a primary key violation.
How to troubleshoot FootPrints 12.1 using the footprints.log?
Check the difference between the problem table last primary key field record and the corresponding record in the fpscdb001_system.id_generator table. It is easier to use the attached script to find all such problems vs. looking at one individually.
Steps to review all primary key fields in Footprints 12
1. Stop the application server Apache Tomcat service
2. Backup the Footprints database
3. Open a new query window and run the attached SQL query, "10 List Id Generator_Primary Keys.sql" after replacing the schema fpscdb001 if needed.
This script creates a new view fpscdb001_system.v_IdGeneratorPrimaryKey that shows the next id values along with the corresponding table name and primary key column name
4. Open a new query window and run the attached SQL query, "30 fix all id generator.sql" after replacing the schema fpscdb001 if needed.
This script creates a SQL Server stored procedure and runs it immediately after creation in "Test Only" mode. Note the result data before proceeding.
5. Run the last line only to make the changes to the fpscdb001_system.id_generator table; EXEC fpscdb001_system.id_generator$fixAll @runMode = 'Update';
6. Start the Apache Tomcat service
----------------------------------------------------------------------------------------------------------------------------------------
Scripts for Postgres
10 List Id Generator_Primary Keys
DROP VIEW IF EXISTS fpscdb001_system.v_TablePrimaryKey;
CREATE VIEW fpscdb001_system.v_TablePrimaryKey
AS
SELECT t.TABLE_SCHEMA
,t.TABLE_NAME
-- ,KCU.TABLE_NAME AS Table_Name
-- ,KCU.CONSTRAINT_NAME AS Constraint_Name
,CASE
WHEN t.TABLE_NAME IN ('data_mart_load_lock')
THEN 'data_mart_load_lock_id'
WHEN t.TABLE_NAME IN ('time_zone_region')
THEN 'time_zone_region_id'
ELSE KCU.COLUMN_NAME
END AS PK_COLUMN_NAME
,CASE
WHEN t.TABLE_NAME IN (
'data_mart_load_history'
,'data_mart_load_lock'
)
THEN t.TABLE_NAME
ELSE t.TABLE_SCHEMA ||'.'|| t.TABLE_NAME
END AS db_table_name_for_id_generator
,t.TABLE_SCHEMA ||'.'|| t.TABLE_NAME AS FullTableName
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN (
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND KCU.TABLE_NAME = TC.TABLE_NAME
) ON tc.TABLE_SCHEMA = t.table_schema
AND tc.TABLE_NAME = t.table_name
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND ORDINAL_POSITION = 1
WHERE t.TABLE_TYPE = 'BASE TABLE';
DROP VIEW IF EXISTS fpscdb001_system.v_IdGeneratorPrimaryKey;
CREATE VIEW fpscdb001_system.v_IdGeneratorPrimaryKey
AS
SELECT i.*
,v.FullTableName
,v.PK_COLUMN_NAME
FROM fpscdb001_system.id_generator i
LEFT JOIN fpscdb001_system.v_TablePrimaryKey v ON v.db_table_name_for_id_generator = i.db_table_name;
SELECT *
FROM fpscdb001_system.v_IdGeneratorPrimaryKey
Order by db_table_name;
30 fix all id generator
DROP FUNCTION IF EXISTS fpscdb001_system.id_generator$fixAll();
CREATE OR REPLACE FUNCTION fpscdb001_system.id_generator$fixAll (RunMode VARCHAR(50) = 'Need Parameter Here')
RETURNS TEXT AS $$
BEGIN
--
SET NOCOUNT =ON;
DECLARE db_table_name VARCHAR(255)
;last_id_val BIGINT
;FullTableName VARCHAR(255)
;pk_column_name VARCHAR(255);
DECLARE max_id BIGINT
;current_id BIGINT
;increment_needed BIGINT
;NewStartId BIGINT;
DECLARE cmd1 TEXT;
DECLARE cmd2 TEXT;
DECLARE ParmDefinition TEXT;
BEGIN
CREATE TEMP TABLE tableStatusSummary (
db_table_name VARCHAR(255)
,last_id_val BIGINT
,max_id_seen BIGINT
,table_status VARCHAR(20)
);
DECLARE badCt BIGINT;
DECLARE goodCt BIGINT;
DECLARE TableCursor CURSOR
FOR
SELECT db_table_name
,last_id_val
,FullTableName
,pk_column_name
FROM fpscdb001_system.v_IdGeneratorPrimaryKey
WHERE FullTableName IS NOT NULL;
--
BEGIN
CREATE TEMP TABLE summary (
id identity NOT NULL primary key
,msgLine VARCHAR(150)
);
-- repeat inputs
INSERT INTO summary (msgLine)
VALUES ('-- Program to Inspect and Reset all Id Lengths --');
INSERT INTO summary (msgLine)
VALUES ('-- Inputs --');
IF RunMode NOT IN (
'Test Only'
,'Update'
)
THEN
BEGIN
INSERT INTO summary (msgLine)
VALUES ('Invalid run mode "' + RunMode + '". Please choose ''Test Only'' or ''Update'' ');
END;
ELSE
BEGIN
INSERT INTO summary (msgLine)
VALUES ('Run mode "' + RunMode + '". ');
END;
END IF;
DECLARE sessionCt INT;
BEGIN
SELECT sessionCt = Count(*)
FROM fpscdb001_system.sys_session;
END;
INSERT INTO summary (msgLine)
VALUES ('Session Count is:' + str(sessionCt));
IF sessionCt = 0
THEN
BEGIN
INSERT INTO summary (msgLine)
VALUES ('Looks Like Tomcat is down.');
END;
ELSE
BEGIN
INSERT INTO summary (msgLine)
VALUES ('Looks Like Tomcat is up.');
INSERT INTO summary (msgLine)
VALUES ('Please bring Tomcat Down!');
END;
END IF;
INSERT INTO summary (msgLine)
VALUES ('-- Running Program --');
-- load variables with lengths
--
SET badCt = 0;
SET goodCt = 0;
OPEN TableCursor;
FETCH
FROM TableCursor
INTO db_table_name
,last_id_val
,FullTableName
,pk_column_name;
EXIT WHEN NOT FOUND;
BEGIN
-- check the maxid of the table vs the lastid of the idgenerator
cmd1 = replace(replace(N'select max_idOUT = max(a.$1) from $2 a ', '$2', @FullTableName), '$1', @pk_column_name);
ParmDefinition = N'max_idOUT bigint OUTPUT';
EXECUTE sp_executesql cmd1
,ParmDefinition
,max_idOUT = max_id OUTPUT;
--
current_id =last_id_val;
IF current_id < max_id
THEN
BEGIN
badct := badCt + 1;
INSERT INTO tableStatusSummary (
db_table_name
,last_id_val
,max_id_seen
,table_status
)
VALUES (
db_table_name
,last_id_val
,max_id
,'*** Needs Reset ***'
);
SELECT increment_needed = max_id - current_id;
IF RunMode = 'Update'
THEN
BEGIN
EXECUTE fpscdb001_system.p_id_generator_getNext db_table_name
,nextId = current_id OUTPUT
,increment = increment_needed;
END;
END IF;
END;
ELSE
BEGIN
INSERT INTO tableStatusSummary (
db_table_name
,last_id_val
,max_id_seen
,table_status
)
VALUES (
db_table_name
,last_id_val
,max_id
,'ok'
);
goodCt = goodCt + 1;
END;
END IF;
--
FETCH NEXT
FROM TableCursor
INTO db_table_name
,last_id_val
,FullTableName
,pk_column_name;
INSERT INTO summary (msgLine)
VALUES ('Bad Tables Detected Count =' + ltrim(STR(badCt)));
INSERT INTO summary (msgLine)
VALUES ('Good Tables Detected Count =' + ltrim(STR(goodCt)));
IF badCt = 0
THEN
BEGIN
INSERT INTO summary (msgLine)
VALUES ('No Updates Needed');
END;
ELSE
BEGIN
IF RunMode = 'Update'
THEN
BEGIN
INSERT INTO summary (msgLine)
VALUES ('Updates Performed, Count =' + ltrim(STR(goodCt)));
INSERT INTO summary (msgLine)
VALUES ('*** Please Restart Tomcat ****');
END;
ELSE
BEGIN
INSERT INTO summary (msgLine)
VALUES ('*** No Updates Performed! ****');
INSERT INTO summary (msgLine)
VALUES ('*** Please Rerun in Update Mode! ***');
END;
END IF;
END;
END IF;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
INSERT INTO summary (msgLine)
VALUES ('--- end ---');
SELECT msgLine AS "----------------------------------------------- Messages -------------------------------------------------------------"
FROM summary
ORDER BY id;
--
SELECT *
FROM tableStatusSummary
ORDER BY table_status
,db_table_name;
END;
END;
RETURN 'Replacing of the string complete. Please check the INFO messages for further details';
END; $$
LANGUAGE plpgsql;
/*
EXECUTE fpscdb001_system.id_generator$fixAll @runMode = 'Test Only';
EXEC fpscdb001_system.id_generator$fixAll @runMode = 'Update';
*/