java.sql.BatchUpdateException: Batch entry 4 ALTER TABLE factsets ADD CONSTRAINT factsets_certname_fk FOREIGN KEY (certname) REFERENCES certnames(name) ON UPDATE CASCADE ON DELETE CASCADE was aborted. Call getNextException to see the cause.

JIRA | Rob Browning | 2 years ago
  1. 0

    With current master, migration 29 fails when run against a stable database as depicted below. And while this is quite rough, it'll should demonstrate/test the problem: https://github.com/rbrw/puppetdb/tree/tmp/test-stable-db-migration You should be able to run it (after editing pg-setup-pdb to list your certs at the bottom) like this: {code:sh} PDB_ACCTEST_DB_PORT=12112 PATH=/usr/lib/postgresql/9.4/bin:"$PATH" \ acceptance/local/test-stable-migration {code} {quote} ERROR: insert or update on table "factsets" violates foreign key constraint "factsets_certname_fk" DETAIL: Key (certname)=(host-3) is not present in table "certnames". STATEMENT: ALTER TABLE factsets ADD CONSTRAINT factsets_certname_fk FOREIGN KEY (certname) REFERENCES certnames(name) ON UPDATE CASCADE ON DELETE CASCADE 2015-03-12 17:55:15,477 ERROR [main] [p.p.s.migrate] Caught SQLException during migration java.sql.BatchUpdateException: Batch entry 4 ALTER TABLE factsets ADD CONSTRAINT factsets_certname_fk FOREIGN KEY (certname) REFERENCES certnames(name) ON UPDATE CASCADE ON DELETE CASCADE was aborted. Call getNextException to see the cause. at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1887) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:469) ~[bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE] at clojure.java.jdbc$do_commands$fn__7845.invoke(jdbc.clj:188) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223) [na:na] at clojure.java.jdbc$do_commands.doInvoke(jdbc.clj:187) ~[na:na] at clojure.lang.RestFn.invoke(RestFn.java:512) [clojure-1.6.0.jar:na] at puppetlabs.puppetdb.scf.migrate$migrate_to_report_id_and_noop_column_and_drop_latest_reports.invoke(migrate.clj:1004) ~[na:na] {quote}

    JIRA | 2 years ago | Rob Browning
    java.sql.BatchUpdateException: Batch entry 4 ALTER TABLE factsets ADD CONSTRAINT factsets_certname_fk FOREIGN KEY (certname) REFERENCES certnames(name) ON UPDATE CASCADE ON DELETE CASCADE was aborted. Call getNextException to see the cause.
  2. 0

    With current master, migration 29 fails when run against a stable database as depicted below. And while this is quite rough, it'll should demonstrate/test the problem: https://github.com/rbrw/puppetdb/tree/tmp/test-stable-db-migration You should be able to run it (after editing pg-setup-pdb to list your certs at the bottom) like this: {code:sh} PDB_ACCTEST_DB_PORT=12112 PATH=/usr/lib/postgresql/9.4/bin:"$PATH" \ acceptance/local/test-stable-migration {code} {quote} ERROR: insert or update on table "factsets" violates foreign key constraint "factsets_certname_fk" DETAIL: Key (certname)=(host-3) is not present in table "certnames". STATEMENT: ALTER TABLE factsets ADD CONSTRAINT factsets_certname_fk FOREIGN KEY (certname) REFERENCES certnames(name) ON UPDATE CASCADE ON DELETE CASCADE 2015-03-12 17:55:15,477 ERROR [main] [p.p.s.migrate] Caught SQLException during migration java.sql.BatchUpdateException: Batch entry 4 ALTER TABLE factsets ADD CONSTRAINT factsets_certname_fk FOREIGN KEY (certname) REFERENCES certnames(name) ON UPDATE CASCADE ON DELETE CASCADE was aborted. Call getNextException to see the cause. at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1887) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405) ~[postgresql-9.2-1003-jdbc4.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893) ~[postgresql-9.2-1003-jdbc4.jar:na] at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:469) ~[bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE] at clojure.java.jdbc$do_commands$fn__7845.invoke(jdbc.clj:188) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223) [na:na] at clojure.java.jdbc$do_commands.doInvoke(jdbc.clj:187) ~[na:na] at clojure.lang.RestFn.invoke(RestFn.java:512) [clojure-1.6.0.jar:na] at puppetlabs.puppetdb.scf.migrate$migrate_to_report_id_and_noop_column_and_drop_latest_reports.invoke(migrate.clj:1004) ~[na:na] {quote}

    JIRA | 2 years ago | Rob Browning
    java.sql.BatchUpdateException: Batch entry 4 ALTER TABLE factsets ADD CONSTRAINT factsets_certname_fk FOREIGN KEY (certname) REFERENCES certnames(name) ON UPDATE CASCADE ON DELETE CASCADE was aborted. Call getNextException to see the cause.
  3. 0

    Hi, We're seeing an issue with our puppetdb since it was upgraded to 2.3.1, with some nodes not updating. We get errors like the following the the puppetdb.log {noformat} 015-04-23 14:45:10,223 ERROR [c.p.p.command] [b7ba3fa5-c61b-4461-a2b5-4317d00a92c8] [replace facts] Retrying after attempt 4, due to: java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((36,820),(36,44),(36,279),(36,67),(36,924),(36,452),(36,812),(36,105),(36,616),(36,139),(36,1019),(36,487),(36,231),(36,65),(36,15),(36,48),(36,88),(36,655),(36,118),(36,87),(36,230),(36,647),(36,493),(36,991),(36,857),(36,81),(36,2),(36,1),(36,79),(36,249),(36,911),(36,349),(36,63),(36,548),(36,141),(36,324),(36,1006),(36,40),(36,27),(36,612),(36,364),(36,530),(36,832),(36,339),(36,25),(36,526),(36,92),(36,797),(36,85),(36,642),(36,131),(36,86),(36,383),(36,58),(36,103),(36,13),(36,871),(36,68),(36,136),(36,536),(36,127),(36,624),(36,7),(36,128),(36,543),(36,519),(36,214),(36,4),(36,46),(36,482),(36,897),(36,49),(36,809),(36,704),(36,811))) was aborted. Call getNextException to see the cause. java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((36,820),(36,44),(36,279),(36,67),(36,924),(36,452),(36,812),(36,105),(36,616),(36,139),(36,1019),(36,487),(36,231),(36,65),(36,15),(36,48),(36,88),(36,655),(36,118),(36,87),(36,230),(36,647),(36,493),(36,991),(36,857),(36,81),(36,2),(36,1),(36,79),(36,249),(36,911),(36,349),(36,63),(36,548),(36,141),(36,324),(36,1006),(36,40),(36,27),(36,612),(36,364),(36,530),(36,832),(36,339),(36,25),(36,526),(36,92),(36,797),(36,85),(36,642),(36,131),(36,86),(36,383),(36,58),(36,103),(36,13),(36,871),(36,68),(36,136),(36,536),(36,127),(36,624),(36,7),(36,128),(36,543),(36,519),(36,214),(36,4),(36,46),(36,482),(36,897),(36,49),(36,809),(36,704),(36,811))) was aborted. Call getNextException to see the cause. at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746) ~[puppetdb.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1887) ~[puppetdb.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405) ~[puppetdb.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893) ~[puppetdb.jar:na] at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:469) ~[puppetdb.jar:na] at clojure.java.jdbc.internal$do_prepared_STAR_$fn__7259.invoke(internal.clj:356) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223) ~[na:na] at clojure.java.jdbc.internal$do_prepared_STAR_.doInvoke(internal.clj:356) ~[na:na] at clojure.lang.RestFn.applyTo(RestFn.java:139) ~[puppetdb.jar:na] at clojure.core$apply.invoke(core.clj:626) ~[puppetdb.jar:na] at clojure.java.jdbc$do_prepared.doInvoke(jdbc.clj:206) ~[na:na] at clojure.lang.RestFn.invoke(RestFn.java:423) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.scf.storage$eval10818$delete_pending_value_id_orphans_BANG___10819$fn__10820.invoke(storage.clj:797) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10818$delete_pending_value_id_orphans_BANG___10819.invoke(storage.clj:777) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10995$update_facts_BANG___10996$fn__11000$fn__11002.invoke(storage.clj:993) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10995$update_facts_BANG___10996$fn__11000.invoke(storage.clj:956) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10995$update_facts_BANG___10996.invoke(storage.clj:949) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval11197$replace_facts_BANG___11198$fn__11202$fn__11204.invoke(storage.clj:1212) ~[na:na] at com.puppetlabs.puppetdb.scf.storage.proxy$java.lang.Object$Callable$7da976d4.call(Unknown Source) ~[na:na] at com.yammer.metrics.core.Timer.time(Timer.java:91) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.scf.storage$eval11197$replace_facts_BANG___11198$fn__11202.invoke(storage.clj:1209) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval11197$replace_facts_BANG___11198.invoke(storage.clj:1202) ~[na:na] at com.puppetlabs.puppetdb.command$eval13392$fn__13395$fn__13408.invoke(command.clj:381) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn$fn__7670$fn__7671$fn__7672.invoke(jdbc.clj:290) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:204) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn$fn__7670$fn__7671.invoke(jdbc.clj:290) ~[na:na] at clojure.java.jdbc.internal$with_connection_STAR_.invoke(internal.clj:186) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn$fn__7670.invoke(jdbc.clj:287) ~[na:na] at com.puppetlabs.jdbc$eval7648$retry_sql_STAR___7649$fn__7650$fn__7651.invoke(jdbc.clj:259) ~[na:na] at com.puppetlabs.jdbc$eval7648$retry_sql_STAR___7649$fn__7650.invoke(jdbc.clj:258) ~[na:na] at com.puppetlabs.jdbc$eval7648$retry_sql_STAR___7649.invoke(jdbc.clj:250) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn.invoke(jdbc.clj:286) ~[na:na] at com.puppetlabs.puppetdb.command$eval13392$fn__13395.invoke(command.clj:379) ~[na:na] at clojure.lang.MultiFn.invoke(MultiFn.java:231) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.command$produce_message_handler$fn__13564.invoke(command.clj:647) ~[na:na] at com.puppetlabs.puppetdb.command$wrap_with_discard$fn__13513$fn__13517.invoke(command.clj:554) ~[na:na] at com.puppetlabs.puppetdb.command.proxy$java.lang.Object$Callable$7da976d4.call(Unknown Source) ~[na:na] at com.yammer.metrics.core.Timer.time(Timer.java:91) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.command$wrap_with_discard$fn__13513.invoke(command.clj:553) ~[na:na] at com.puppetlabs.puppetdb.command$wrap_with_exception_handling$fn__13498$fn__13499.invoke(command.clj:507) ~[na:na] at com.puppetlabs.puppetdb.command.proxy$java.lang.Object$Callable$7da976d4.call(Unknown Source) ~[na:na] at com.yammer.metrics.core.Timer.time(Timer.java:91) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.command$wrap_with_exception_handling$fn__13498.invoke(command.clj:506) ~[na:na] at com.puppetlabs.puppetdb.command$wrap_with_command_parser$fn__13508.invoke(command.clj:529) [na:na] at com.puppetlabs.puppetdb.command$wrap_with_meter$fn__13488.invoke(command.clj:467) [na:na] at com.puppetlabs.puppetdb.command$wrap_with_thread_name$fn__13522.invoke(command.clj:569) [na:na] at com.puppetlabs.mq$create_message_listener$reify__11783.onMessage(mq.clj:270) [na:na] at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:560) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:498) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:467) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:263) [puppetdb.jar:na] at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1058) [puppetdb.jar:na] at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1050) [puppetdb.jar:na] at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:947) [puppetdb.jar:na] at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75] {noformat} and the corresponding error in the postgres.log: {noformat} RROR: update or delete on table "fact_values" violates foreign key constraint "fact_value_id_fk" on table "facts" DETAIL: Key (id)=(1524348) is still referenced from table "facts". STATEMENT: DELETE FROM fact_values fv WHERE fv.id in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in ($76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100,$101,$102,$103,$104,$105,$106,$107,$108,$109,$110,$111,$112,$113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128,$129,$130,$131,$132,$133,$134,$135,$136,$137,$138,$139,$140,$141,$142,$143,$144,$145,$146,$147,$148,$149,$150) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in (($151,$152),($153,$154),($155,$156),($157,$158),($159,$160),($161,$162),($163,$164),($165,$166),($167,$168),($169,$170),($171,$172),($173,$174),($175,$176),($177,$178),($179,$180),($181,$182),($183,$184),($185,$186),($187,$188),($189,$190),($191,$192),($193,$194),($195,$196),($197,$198),($199,$200),($201,$202),($203,$204),($205,$206),($207,$208),($209,$210),($211,$212),($213,$214),($215,$216),($217,$218),($219,$220),($221,$222),($223,$224),($225,$226),($227,$228),($229,$230),($231,$232),($233,$234),($235,$236),($237,$238),($239,$240),($241,$242),($243,$244),($245,$246),($247,$248),($249,$250),($251,$252),($253,$254),($255,$256),($257,$258),($259,$260),($261,$262),($263,$264),($265,$266),($267,$268),($269,$270),($271,$272),($273,$274),($275,$276),($277,$278),($279,$280),($281,$282),($283,$284),($285,$286),($287,$288),($289,$290),($291,$292),($293,$294),($295,$296),($297,$298),($299,$300))) {noformat} I've been able to get things moving along by performing {noformat} delete from facts where fact_value_id in ( <list of numbers from failing fact_values delete>) {noformat} and I've updated to 2.3.3, but new 'factsets' keep developing the same error. We're on CentOS 7 and postgres 9.2.7. We have about 110 puppet agents.

    JIRA | 2 years ago | Simon Oxwell
    java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((36,820),(36,44),(36,279),(36,67),(36,924),(36,452),(36,812),(36,105),(36,616),(36,139),(36,1019),(36,487),(36,231),(36,65),(36,15),(36,48),(36,88),(36,655),(36,118),(36,87),(36,230),(36,647),(36,493),(36,991),(36,857),(36,81),(36,2),(36,1),(36,79),(36,249),(36,911),(36,349),(36,63),(36,548),(36,141),(36,324),(36,1006),(36,40),(36,27),(36,612),(36,364),(36,530),(36,832),(36,339),(36,25),(36,526),(36,92),(36,797),(36,85),(36,642),(36,131),(36,86),(36,383),(36,58),(36,103),(36,13),(36,871),(36,68),(36,136),(36,536),(36,127),(36,624),(36,7),(36,128),(36,543),(36,519),(36,214),(36,4),(36,46),(36,482),(36,897),(36,49),(36,809),(36,704),(36,811))) was aborted. Call getNextException to see the cause.
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Hi, We're seeing an issue with our puppetdb since it was upgraded to 2.3.1, with some nodes not updating. We get errors like the following the the puppetdb.log {noformat} 015-04-23 14:45:10,223 ERROR [c.p.p.command] [b7ba3fa5-c61b-4461-a2b5-4317d00a92c8] [replace facts] Retrying after attempt 4, due to: java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((36,820),(36,44),(36,279),(36,67),(36,924),(36,452),(36,812),(36,105),(36,616),(36,139),(36,1019),(36,487),(36,231),(36,65),(36,15),(36,48),(36,88),(36,655),(36,118),(36,87),(36,230),(36,647),(36,493),(36,991),(36,857),(36,81),(36,2),(36,1),(36,79),(36,249),(36,911),(36,349),(36,63),(36,548),(36,141),(36,324),(36,1006),(36,40),(36,27),(36,612),(36,364),(36,530),(36,832),(36,339),(36,25),(36,526),(36,92),(36,797),(36,85),(36,642),(36,131),(36,86),(36,383),(36,58),(36,103),(36,13),(36,871),(36,68),(36,136),(36,536),(36,127),(36,624),(36,7),(36,128),(36,543),(36,519),(36,214),(36,4),(36,46),(36,482),(36,897),(36,49),(36,809),(36,704),(36,811))) was aborted. Call getNextException to see the cause. java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((36,820),(36,44),(36,279),(36,67),(36,924),(36,452),(36,812),(36,105),(36,616),(36,139),(36,1019),(36,487),(36,231),(36,65),(36,15),(36,48),(36,88),(36,655),(36,118),(36,87),(36,230),(36,647),(36,493),(36,991),(36,857),(36,81),(36,2),(36,1),(36,79),(36,249),(36,911),(36,349),(36,63),(36,548),(36,141),(36,324),(36,1006),(36,40),(36,27),(36,612),(36,364),(36,530),(36,832),(36,339),(36,25),(36,526),(36,92),(36,797),(36,85),(36,642),(36,131),(36,86),(36,383),(36,58),(36,103),(36,13),(36,871),(36,68),(36,136),(36,536),(36,127),(36,624),(36,7),(36,128),(36,543),(36,519),(36,214),(36,4),(36,46),(36,482),(36,897),(36,49),(36,809),(36,704),(36,811))) was aborted. Call getNextException to see the cause. at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746) ~[puppetdb.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1887) ~[puppetdb.jar:na] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405) ~[puppetdb.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893) ~[puppetdb.jar:na] at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:469) ~[puppetdb.jar:na] at clojure.java.jdbc.internal$do_prepared_STAR_$fn__7259.invoke(internal.clj:356) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223) ~[na:na] at clojure.java.jdbc.internal$do_prepared_STAR_.doInvoke(internal.clj:356) ~[na:na] at clojure.lang.RestFn.applyTo(RestFn.java:139) ~[puppetdb.jar:na] at clojure.core$apply.invoke(core.clj:626) ~[puppetdb.jar:na] at clojure.java.jdbc$do_prepared.doInvoke(jdbc.clj:206) ~[na:na] at clojure.lang.RestFn.invoke(RestFn.java:423) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.scf.storage$eval10818$delete_pending_value_id_orphans_BANG___10819$fn__10820.invoke(storage.clj:797) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10818$delete_pending_value_id_orphans_BANG___10819.invoke(storage.clj:777) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10995$update_facts_BANG___10996$fn__11000$fn__11002.invoke(storage.clj:993) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10995$update_facts_BANG___10996$fn__11000.invoke(storage.clj:956) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval10995$update_facts_BANG___10996.invoke(storage.clj:949) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval11197$replace_facts_BANG___11198$fn__11202$fn__11204.invoke(storage.clj:1212) ~[na:na] at com.puppetlabs.puppetdb.scf.storage.proxy$java.lang.Object$Callable$7da976d4.call(Unknown Source) ~[na:na] at com.yammer.metrics.core.Timer.time(Timer.java:91) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.scf.storage$eval11197$replace_facts_BANG___11198$fn__11202.invoke(storage.clj:1209) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval11197$replace_facts_BANG___11198.invoke(storage.clj:1202) ~[na:na] at com.puppetlabs.puppetdb.command$eval13392$fn__13395$fn__13408.invoke(command.clj:381) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn$fn__7670$fn__7671$fn__7672.invoke(jdbc.clj:290) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:204) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn$fn__7670$fn__7671.invoke(jdbc.clj:290) ~[na:na] at clojure.java.jdbc.internal$with_connection_STAR_.invoke(internal.clj:186) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn$fn__7670.invoke(jdbc.clj:287) ~[na:na] at com.puppetlabs.jdbc$eval7648$retry_sql_STAR___7649$fn__7650$fn__7651.invoke(jdbc.clj:259) ~[na:na] at com.puppetlabs.jdbc$eval7648$retry_sql_STAR___7649$fn__7650.invoke(jdbc.clj:258) ~[na:na] at com.puppetlabs.jdbc$eval7648$retry_sql_STAR___7649.invoke(jdbc.clj:250) ~[na:na] at com.puppetlabs.jdbc$with_transacted_connection_fn.invoke(jdbc.clj:286) ~[na:na] at com.puppetlabs.puppetdb.command$eval13392$fn__13395.invoke(command.clj:379) ~[na:na] at clojure.lang.MultiFn.invoke(MultiFn.java:231) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.command$produce_message_handler$fn__13564.invoke(command.clj:647) ~[na:na] at com.puppetlabs.puppetdb.command$wrap_with_discard$fn__13513$fn__13517.invoke(command.clj:554) ~[na:na] at com.puppetlabs.puppetdb.command.proxy$java.lang.Object$Callable$7da976d4.call(Unknown Source) ~[na:na] at com.yammer.metrics.core.Timer.time(Timer.java:91) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.command$wrap_with_discard$fn__13513.invoke(command.clj:553) ~[na:na] at com.puppetlabs.puppetdb.command$wrap_with_exception_handling$fn__13498$fn__13499.invoke(command.clj:507) ~[na:na] at com.puppetlabs.puppetdb.command.proxy$java.lang.Object$Callable$7da976d4.call(Unknown Source) ~[na:na] at com.yammer.metrics.core.Timer.time(Timer.java:91) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.command$wrap_with_exception_handling$fn__13498.invoke(command.clj:506) ~[na:na] at com.puppetlabs.puppetdb.command$wrap_with_command_parser$fn__13508.invoke(command.clj:529) [na:na] at com.puppetlabs.puppetdb.command$wrap_with_meter$fn__13488.invoke(command.clj:467) [na:na] at com.puppetlabs.puppetdb.command$wrap_with_thread_name$fn__13522.invoke(command.clj:569) [na:na] at com.puppetlabs.mq$create_message_listener$reify__11783.onMessage(mq.clj:270) [na:na] at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:560) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:498) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:467) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325) [puppetdb.jar:na] at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:263) [puppetdb.jar:na] at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1058) [puppetdb.jar:na] at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1050) [puppetdb.jar:na] at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:947) [puppetdb.jar:na] at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75] {noformat} and the corresponding error in the postgres.log: {noformat} RROR: update or delete on table "fact_values" violates foreign key constraint "fact_value_id_fk" on table "facts" DETAIL: Key (id)=(1524348) is still referenced from table "facts". STATEMENT: DELETE FROM fact_values fv WHERE fv.id in ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in ($76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100,$101,$102,$103,$104,$105,$106,$107,$108,$109,$110,$111,$112,$113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128,$129,$130,$131,$132,$133,$134,$135,$136,$137,$138,$139,$140,$141,$142,$143,$144,$145,$146,$147,$148,$149,$150) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in (($151,$152),($153,$154),($155,$156),($157,$158),($159,$160),($161,$162),($163,$164),($165,$166),($167,$168),($169,$170),($171,$172),($173,$174),($175,$176),($177,$178),($179,$180),($181,$182),($183,$184),($185,$186),($187,$188),($189,$190),($191,$192),($193,$194),($195,$196),($197,$198),($199,$200),($201,$202),($203,$204),($205,$206),($207,$208),($209,$210),($211,$212),($213,$214),($215,$216),($217,$218),($219,$220),($221,$222),($223,$224),($225,$226),($227,$228),($229,$230),($231,$232),($233,$234),($235,$236),($237,$238),($239,$240),($241,$242),($243,$244),($245,$246),($247,$248),($249,$250),($251,$252),($253,$254),($255,$256),($257,$258),($259,$260),($261,$262),($263,$264),($265,$266),($267,$268),($269,$270),($271,$272),($273,$274),($275,$276),($277,$278),($279,$280),($281,$282),($283,$284),($285,$286),($287,$288),($289,$290),($291,$292),($293,$294),($295,$296),($297,$298),($299,$300))) {noformat} I've been able to get things moving along by performing {noformat} delete from facts where fact_value_id in ( <list of numbers from failing fact_values delete>) {noformat} and I've updated to 2.3.3, but new 'factsets' keep developing the same error. We're on CentOS 7 and postgres 9.2.7. We have about 110 puppet agents.

    JIRA | 2 years ago | Simon Oxwell
    java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (90,180,2414,2667,2590,1264,2436,1561878,2568,1524348,90,2516,2436,2468,1561273,1561275,33,1210,1236,2678,90,33,2320,2478,2494,2452,90,2678,1551663,33,26,2660,1561269,896,33,578,33,1561277,1551664,90,4641,180,2590,90,83,578,1551663,83,2452,2451,1561876,1561877,180,1561272,1561876,2474,90,1551664,180,2516,1561277,2462,1561271,33,2568,26,2494,2667,1524348,2414,180,33,33,90,4616) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((36,820),(36,44),(36,279),(36,67),(36,924),(36,452),(36,812),(36,105),(36,616),(36,139),(36,1019),(36,487),(36,231),(36,65),(36,15),(36,48),(36,88),(36,655),(36,118),(36,87),(36,230),(36,647),(36,493),(36,991),(36,857),(36,81),(36,2),(36,1),(36,79),(36,249),(36,911),(36,349),(36,63),(36,548),(36,141),(36,324),(36,1006),(36,40),(36,27),(36,612),(36,364),(36,530),(36,832),(36,339),(36,25),(36,526),(36,92),(36,797),(36,85),(36,642),(36,131),(36,86),(36,383),(36,58),(36,103),(36,13),(36,871),(36,68),(36,136),(36,536),(36,127),(36,624),(36,7),(36,128),(36,543),(36,519),(36,214),(36,4),(36,46),(36,482),(36,897),(36,49),(36,809),(36,704),(36,811))) was aborted. Call getNextException to see the cause.
  6. 0

    [HIVE-9720] Metastore does not properly migrate column stats when renaming a table across databases. - ASF JIRA

    apache.org | 1 year ago
    java.sql.BatchUpdateException: Batch entry 0 DELETE FROM WHERE ='1621' was aborted. Call getNextException to see the cause.

    10 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.BatchUpdateException

      Batch entry 4 ALTER TABLE factsets ADD CONSTRAINT factsets_certname_fk FOREIGN KEY (certname) REFERENCES certnames(name) ON UPDATE CASCADE ON DELETE CASCADE was aborted. Call getNextException to see the cause.

      at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError()
    2. PostgreSQL JDBC Driver
      AbstractJdbc2Statement.executeBatch
      1. org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746)[postgresql-9.2-1003-jdbc4.jar:na]
      2. org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1887)[postgresql-9.2-1003-jdbc4.jar:na]
      3. org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)[postgresql-9.2-1003-jdbc4.jar:na]
      4. org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2893)[postgresql-9.2-1003-jdbc4.jar:na]
      4 frames
    3. BoneCP :: Core Library
      StatementHandle.executeBatch
      1. com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:469)[bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE]
      1 frame
    4. Clojure
      jdbc$do_commands$fn__7845.invoke
      1. clojure.java.jdbc$do_commands$fn__7845.invoke(jdbc.clj:188)[na:na]
      1 frame
    5. notificationagent
      internal$transaction_STAR_.invoke
      1. clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223)[na:na]
      1 frame
    6. Clojure
      RestFn.invoke
      1. clojure.java.jdbc$do_commands.doInvoke(jdbc.clj:187)[na:na]
      2. clojure.lang.RestFn.invoke(RestFn.java:512)[clojure-1.6.0.jar:na]
      2 frames
    7. puppetlabs.puppetdb.scf
      migrate$migrate_to_report_id_and_noop_column_and_drop_latest_reports.invoke
      1. puppetlabs.puppetdb.scf.migrate$migrate_to_report_id_and_noop_column_and_drop_latest_reports.invoke(migrate.clj:1004)[na:na]
      1 frame