Getting error Violation of PRIMARY KEY constraint for a Footprints 12 table when adding a new record. How to fix the error "Violation of PRIMARY KEY constraint"? Violation of PRIMARY KEY constraint 'item_reln_pk'. Cannot insert duplicate key in object 'fpscdb001_system.item_reln'. |
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 121. Stop the application server Apache Tomcat service2. 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'; */ |