Jira (PDB-3840) Add additional cleanup of the facts table during migrations

[Erik Hansen (JIRA)]

Erik Hansen created an issue

PuppetDB / PDB-3840 Add additional cleanup of the facts table during migrations Issue Type: Bug Assignee: Unassigned Created: 2018/02/07 5:04 PM Priority: Normal Reporter: Erik Hansen I think we need to add this: DELETE from facts WHERE fact_path_id NOT IN (SELECT id FROM fact_paths); We already do something similar here: https://github.com/puppetlabs/puppetdb/blob/5.1.4/src/puppetlabs/puppetdb/scf/migrate.clj#L1318 Add Comment

This message was sent by Atlassian JIRA (v7.5.1#75006-sha1:7df2574)

[Erik Hansen (JIRA)]

Erik Hansen updated an issue

PuppetDB / PDB-3840 Add additional cleanup of the facts table during migrations

Change By: Erik Hansen I think we need to add this these : {code:java}DELETE from facts WHERE fact_path_id NOT IN (SELECT id FROM fact_paths); DELETE FROM facts WHERE fact_value_id NOT IN (SELECT id FROM fact_values); {code}

We already do something similar here:

[ https://github.com/puppetlabs/puppetdb/blob/5.1.4/src/puppetlabs/puppetdb/scf/migrate.clj#L1318 ]

Add Comment

[Rob Browning (JIRA)]

Rob Browning assigned an issue to Rob Browning

PuppetDB / PDB-3840 Add additional cleanup of the facts table during migrations

Change By: Rob Browning Assignee: Rob Browning

Add Comment

This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93)

[Rob Browning (JIRA)]

Rob Browning updated an issue

PuppetDB / PDB-3840 Add additional cleanup of the facts table during migrations

Change By: Rob Browning Team: PuppetDB

Add Comment

[Rob Browning (JIRA)]

Rob Browning updated an issue

PuppetDB / PDB-3840 Add additional cleanup of the facts table during migrations

Change By: Rob Browning Release Notes Summary: PuppetDB should no longer crash when performing an older database migration in some (likely rare) situations.  The crash should produce an error in the log that mentions "java.sql.BatchUpdateException: Batch entry 3 ALTER TABLE facts ADD CONSTRAINT fact_path_id_fk". Release Notes: Bug Fix

Add Comment

[Rob Browning (JIRA)]

Rob Browning commented on PDB-3840

Re: Add additional cleanup of the facts table during migrations For future reference, when this issue causes a crash, it should produce an error in the log that looks like this: ERROR [p.p.s.migrate] Caught SQLException during migration

java.sql.BatchUpdateException: Batch entry 3 ALTER TABLE facts ADD CONSTRAINT fact_path_id_fk

FOREIGN KEY (fact_path_id) REFERENCES fact_paths(id) was aborted. Call getNextException to see the cause. at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:133) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2004) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:360) at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:1019) at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:125) at com.zaxxer.hikari.pool.HikariProxyStatement.executeBatch(HikariProxyStatement.java) at clojure.java.jdbc$execute_batch.invokeStatic(jdbc.clj:439) at clojure.java.jdbc$execute_batch.invoke(jdbc.clj:432) at clojure.java.jdbc$db_do_commands$fn__21859.invoke(jdbc.clj:748) at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:662) at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:598) at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:611) at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:598) at clojure.java.jdbc$db_do_commands.invokeStatic(jdbc.clj:747) at clojure.java.jdbc$db_do_commands.invoke(jdbc.clj:732) at puppetlabs.puppetdb.jdbc$do_commands.invokeStatic(jdbc.clj:38) at puppetlabs.puppetdb.jdbc$do_commands.doInvoke(jdbc.clj:33) at clojure.lang.RestFn.invoke(RestFn.java:512) at puppetlabs.puppetdb.scf.migrate$rededuplicate_facts.invokeStatic(migrate.clj:1433) at puppetlabs.puppetdb.scf.migrate$rededuplicate_facts.invoke(migrate.clj:1315) at puppetlabs.puppetdb.scf.migrate$migrate_BANG_$fn__36253$fn__36255$fn__36257.invoke(migrate.clj:1616)

Add Comment

[Rob Browning (JIRA)]

Rob Browning updated an issue

PuppetDB / PDB-3840

Add additional cleanup of the facts table during migrations

Change By: Rob Browning Fix Version/s: PDB 6.3.3 Fix Version/s: PDB 6.0.4 Fix Version/s: PDB 5.2.9

Add Comment