Issues publishing or applying scripts to automations in ConnectALL after database migration

Products

• ConnectAll On-Prem
• ConnectALL

Issue/Introduction

After migrating from one database platform to PostgreSQL, customer is unable to publish new scripts or apply scripts to automations.

Errors like the following may be seen in the UI logs

ERROR YYYY-MM-dd hh:mm:ss,zzz http-nio-nnnn-exec-n com.go2group.connectall.scripting.dao.ScriptsDAO - Unable to associate the script due to the error 
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "business_script_associations_pkey"
  Detail: Key (id)=(nn) already exists.

 

Cause

Most tables in a database have an ID column that can be referenced in other tables to link the records together.  Normally the ID columns have an "auto-assign" feature where those numbers get generated in sequential order.  The first record inserted into a table gets 1, the second gets 2 and so on.

When we migrate data into a table, we have to maintain the original ID values and so the migrator is overriding that incremental feature and inserting the original values from the old database.

PostgreSQL keeps its sequence values separate from the table and so there is a specific command that should be run after doing this that "trues up" the existing values in the table with what postgres needs to assign next and that was not being run.

Early versions of the database migration utility did not advance the sequence so this may need to be done manually if a migration was performed with an early version.

Resolution

Run the following SQL commands:

SELECT setval('adapter_field_type_mapping_seq', COALESCE((SELECT MAX(id) FROM adapter_field_type_mapping), 0) + 1, false);
SELECT setval('api_key_id_seq', COALESCE((SELECT MAX(id) FROM api_keys), 0) + 1, false);
SELECT setval('business_script_associations_seq', COALESCE((SELECT MAX(id) FROM business_script_associations), 0) + 1, false);
SELECT setval('business_scripts_seq', COALESCE((SELECT MAX(id) FROM business_scripts), 0) + 1, false);
SELECT setval('business_script_versions_seq', COALESCE((SELECT MAX(id) FROM business_script_versions), 0) + 1, false);
SELECT setval('ca_upgrade_history_seq', COALESCE((SELECT MAX(upgrade_history) FROM ca_upgrade_history), 0) + 1, false);
SELECT setval('fav_conn_advanced_properties_seq', COALESCE((SELECT MAX(adv_property_id) FROM fav_conn_advanced_properties), 0) + 1, false);
SELECT setval('field_types_seq', COALESCE((SELECT MAX(type_id) FROM field_types), 0) + 1, false);
SELECT setval('team_associations_seq', COALESCE((SELECT MAX(id) FROM team_associations), 0) + 1, false);
SELECT setval('teams_seq', COALESCE((SELECT MAX(id) FROM teams), 0) + 1, false);