Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Priscilla Piedra there's no real maximum for fact character size, but 500 characters would be totally safe. It tends to be really large facts that are very dynamic that cause issues like the one you saw. I imagine if that ssl_certificates fact is extremely huge, and an element were inserted at the beginning of the array, you might run into the issue you did, but it's hard for me to know without actually seeing the fact. If that fact is something your company has created (rather than created by a module for instance) one change you can make to reduce the strain on PuppetDB would be to reshape it so that the top-level structure is a hash (keyed on certname for instance) rather than an array. If it doesn't cause further issues though you may as well leave it. I don't expect the changed max_stack_depth setting to cause performance drawbacks, but I would also think that now that you've successfully executed the delete, you should be able to revert it back to 2mb without trouble. The only guidelines I can give you are the ones in the postgres docs I linked, which recommend making the change that you made, so in principle it should be fine to leave, but let us know if you hit more issues. Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Priscilla Piedra commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt, Just wondering if there is a stablish max for facts, (eg. max of 500 characters) or objects in case of a structure fact, or can I store information with not limit? Will set max_stack_depth setting to 6MB or higher would cost any performace issues on db? is there a guideline for it? Thanks 2016-04-18 11:10 GMT-06:00 Wyatt Alt (JIRA) < – Ing.Priscilla Piedra Hidalgo Software Developer Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Priscilla Piedra good to hear it! I've still got no idea how you might have ended up in this situation to begin with though, so let us know if the issue comes back. Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Priscilla Piedra commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt Alt I have applied this config on DB and I haven't seen the error... seems like it worked! Thanks for your help Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Priscilla Piedra the postgres docs I linked suggest a megabyte less than the output of ulimit -s On my machine ulimit -s outputs 8192, which is in kilobytes, so personally I would try 7mb. Your case may be different though – depends on what ulimit reports. Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Priscilla Piedra commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt Alt Do you know what is a good value to set max_stack_depth? My current value is 2MB Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Priscilla Piedra Thanks – the postgres logs you gave me show that the reason the deletion is failing is not a foreign key constraint violation, but this: 2016-04-12 15:26:28 GMT puppetdb puppetdb_rw ERROR: stack depth limit exceeded 2016-04-12 15:26:28 GMT puppetdb puppetdb_rw HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate. can you check ulimit -s and change postgres's max_stack_depth parameter to be a megabyte or so less (this will require a postgres restart)? My ulimit is reporting kilobytes, so if it's the same for you make sure to make the appropriate conversion. My guess is that once you've ended up in the situation you're in, it can only get worse. There's a good chance that if raising max_stack_depth allows the delete to succeed you can then lower it again if needed. The configuration parameter is documented here: http://www.postgresql.org/docs/9.5/static/runtime-config-resource.html If the delete still fails, I'd like to see the same postgres logs with the new failure. In that event we'll probably need to clear those facts out manually via a truncate or targeted delete. It's still an open question how you exceeded the stack depth originally – nothing you've reported so far indicates anything else out of the ordinary.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Priscilla Piedra commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt Alt ! Thanks for your help! To answer your questions: 1) have you been on 2.3.8 for a long time or have you recently upgraded: We have like two months using 2.3.8 version, before we had 2.2.2 2) did this start occurring suddenly or has it been going on for a while This problem appeared suddenly... like two weeks ago. 3) if suddenly, do any of the fact names I listed above correspond in an obvious way to recent changes? No, the facts are the same, there is no a recent change on them. 4) Do you use a single PDB + postgres/single master or is your PDB/postgres setup more complicated than that? We use a single postgres master, but for each PM we have puppetdb installed (we have 10 pms) 5) (unlikely) does anyone ever perform manual deletions on this database? Manual deletions no, but it is configured on each PDB report-ttl = 14d I sent to your email the output of the log. Thanks again!! Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Priscilla Piedra I've spent some time this morning looking into this – no leads yet at all, but here are a few notes in random order (partly for you, partly for the team): You have 242169 occurrences of this problem, across 13,940 nodes which based on your email is probably more or less your whole fleet There are four unique fact values that are causing the problem. They are all strings, with no particular pattern. 99.5% of the occurrences are caused by the empty string. There are a relatively small number (24) of actual facts represented. They are [1] "installed_software" "installed_hotfixes_security""nics" "hba_properties" "volumes" [6] "distinguished_name" "installed_hotfixes_nonsecurity" "ssl_certificates" "sqlserver_tcp_port_1" "hp_diskdrives" [11] "hp_arraycontrollers""hp_storagevolumes" "interfaces" "sqlserver_tcp_port_4" "sqlserver_tcp_port_5" [16] "apt_package_updates""serialnumber" "bigfix_admingroup" "sqlserver_tcp_port" "sqlserver_tcp_port_2" [21] "sqlserver_tcp_port_3" "sqlserver_tcp_port_6" "hp_proliant_sp_version" " S=CA"
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Priscilla Piedra commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt Alt !! Thanks for helping me with this! I sent you the output of this query to your email wy...@puppetlabs.com (is a big output) Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hey Priscilla Piedra, I need to warn you that a fix for 2.3.8 is pretty unlikely at this point, be we can help you debug this in any case. To get the ball rolling can you give the output of the following executed against the puppetdb database? select factset_id, fact_value_id, value_hash, value_integer, value_float, value_string, value_boolean, value_json, name, path from facts f inner join fact_values fv on f.fact_value_id=fv.id inner join fact_paths fp on f.fact_path_id=fp.id where f.fact_value_id in (131215,132501,132501,175,129002) order by fact_value_id; Wyatt Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Priscilla Piedra commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi, It is suposed that this bug was fixed in version 2.3.4 but I'm having this issue on PDB 2.3.8 java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (131215,132501,132501,175,129002) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (131215,132501,132501,175,129002)AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id, f.fact_value_id) NOT in ((5790,111587,131215),(5790,118579,132501),(5790,110159,132501),(5790,120871,175),(5790,111511,129002))) 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__5845.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$eval9406$delete_pending_value_id_orphans_BANG__9407$fn9408$fn_9409.invoke(storage.clj:813) ~[na:na] at clojure.lang.AFn.applyToHelper(AFn.java:165) ~[puppetdb.jar:na] at clojure.lang.AFn.applyTo(AFn.java:144) ~[puppetdb.jar:na] at clojure.core$apply.invoke(core.clj:624) ~[puppetdb.jar:na] at clojure.core$map$fn__4260.invoke(core.clj:2578) ~[puppetdb.jar:na] at clojure.core$map$fn__4245.invoke(core.clj:2559) ~[puppetdb.jar:na] at clojure.lang.LazySeq.sval(LazySeq.java:40) ~[puppetdb.jar:na] at clojure.lang.LazySeq.seq(LazySeq.java:49) ~[puppetdb.jar:na] at clojure.lang.RT.seq(RT.java:484) ~[puppetdb.jar:na] at clojure.core$seq.invoke(core.clj:133) ~[puppetdb.jar:na] at clojure.core$dorun.invoke(core.clj:2855) ~[puppetdb.jar:na] at com.puppetlabs.puppetdb.scf.storage$eval9406$delete_pending_value_id_orphans_BANG__9407$fn_9408.invoke(storage.clj:801) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval9406$delete_pending_value_id_orphans_BANG___9407.invoke(storage.clj:787) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval9585$update_facts_BANG__9586$fn9590$fn_9592.invoke(storage.clj:1013) ~[na:na] at clojure.java.jdbc.internal$transaction_STAR_.invoke(internal.clj:223) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval9585$update_facts_BANG__9586$fn_9590.invoke(storage.clj:976) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval9585$update_facts_BANG___9586.invoke(storage.clj:969) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval9789$replace_facts_BANG__9790$fn9794$fn_9796.invoke(storage.clj:1235) ~[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$eval9789$replace_facts_BANG__9790$fn_9794.invoke(storage.clj:1232) ~[na:na] at com.puppetlabs.puppetdb.scf.storage$eval9789$replace_facts_BANG___9790.invoke(storage.clj:1225) ~[na:na] at
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Kurt Wall updated an issue PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Change By: Kurt Wall QA Status: Reviewed Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Duncan McNaught commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue I put the new package in 3 hours ago - the errors are not occurring anymore for us. Thanks for the quick fix. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Simon Oxwell commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Yep - looking good. Thanks! (now to figure out how to clear the DLO) Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Simon Oxwell commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt, I've put the new packages in, and it seems to be going well so far! Will update again in a few hours. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Simon Oxwell Duncan McNaught we released a fix today in PDB 2.3.4. Give it a shot when you can and let us know how it goes! Wyatt Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt updated an issue PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Change By: Wyatt Alt Fix Version/s: PDB2.3.x Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Rob Browning updated an issue PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Change By: Rob Browning Sprint: PuppetDB2015-05- 06 20 Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Rob Browning updated an issue PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Change By: Rob Browning Sprint: PuppetDB2015-05- 20 06 Scrum Team: PuppetDB Story Points: 4 Scope Change Category: Found Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Simon Oxwell commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt, Ah, great. That's awesome. Glad to be of help. Look forward to trying out the fix. Simon Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Rob Browning assigned an issue to Rob Browning PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Change By: Rob Browning Assignee: RobBrowning Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Simon Oxwell Wow, nice going! Thanks for tracking it down so specifically. We have the issue reproduced and are testing a fix currently. Duncan McNaught Thanks for that data. If your scenario matches the one Simon is describing I don't think we need anything else. We'll update this thread once we release a bugfix – hopefully sometime next week. Thanks again for the help, guys! Wyatt Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Simon Oxwell commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt - sorry I haven't been able to get this over the last few days, had other operational priorities. I've now got a new server playing up, and it's not being blocked by a structured fact: puppetdb=# DELETE FROM fact_values fv puppetdb-#WHERE fv.id in (2347921,5398,5421,1525955,2374767,2301540,2373170,5416,2301540,5407,2374770,2374768,2374770,2374767,2374769,5398,2374771) puppetdb-# AND NOT EXISTS (SELECT 1 FROM facts f puppetdb(#WHERE f.fact_value_id in (2347921,5398,5421,1525955,2374767,2301540,2373170,5416,2301540,5407,2374770,2374768,2374770,2374767,2374769,5398,2374771) puppetdb(# AND f.fact_value_id = fv.id puppetdb(# AND (f.factset_id, f.fact_path_id) NOT in ((93,148),(93,79),(93,4731),(93,4723),(93,131),(93,68),(93,48),(93,4720),(93,27),(93,4836),(93,127),(93,86),(93,40),(93,103),(93,142),(93,92),(93,105))); ERROR: update or delete on table fact_values violates foreign key constraint fact_value_id_fk on table facts DETAIL: Key (id)=(5407) is still referenced from table facts.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Additionally, I don't know how you are using these facts but if you aren't specifically querying them using the fact-contents endpoint you could also consider storing them as strings. Depending on your use case this could well be the quickest solution. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Duncan McNaught commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue FYI - I'm seeing this a lot too after upgrade to 2.3.3. Thanks for adding the bug Simon Oxwell Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Duncan McNaught commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue I can enable slow statement logging tomorrow if that will help too. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Duncan McNaught Are you also seeing this in relation to large structured facts? If you'd be willing to take the three bulleted steps I described in that last message and report back that would be informative. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Duncan McNaught updated an issue PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Here is the dashboard screenshot for our two puppetdb nodes. Also showing cpu not working much on the database server master. Change By: Duncan McNaught Attachment: ScreenShot2015-04-30at4.58.05PM.png Attachment: ScreenShot2015-04-30at4.54.03PM.png Attachment: ScreenShot2015-04-30at4.53.45PM.png Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Rob Browning assigned an issue to Unassigned PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Change By: Rob Browning Assignee: RobBrowning Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title
Wyatt Alt commented on PDB-1448
Re: 'replace facts' failing due to foreign key constraint issue
Simon Oxwell
Thanks, those results are informative. We haven't reproduced your exact error on a running PDB yet (although we can make it happen under manufactured circumstances), but I think I have a sense of where things may be going wrong. This may be more detail than your interested in, but if you think of a structured fact as a tree, then conceptually the way we store it is as a set of key-value pairs where the values are the leaves and the keys are strings that describes a path from the root of the tree to the leaf. We do this to allow these paths and values to be shared among multiple facts, to reduce storage space. In the case of arrays, the index is incorporated into the path, so if your structured fact is
foo: [a,b, {c: d}]
then you can think of the data we store as looking like this (#~ is an arbitrary delimiter):
{foo#~1: a
foo#~2: b
foo#~3#~c: d}
The downside of handling arrays this way is that an insertion or deletion at the front of an array causes an index shift for all subsequent elements as well as their children. In your case, I can see that one of those facts has at least 586 top-level array elements, each with a collection key and a profiles key that also contains some number of array elements. My guess (supported by your facter output) is that values are being inserted or deleted from that structured fact and causing the recomputation of thousands of paths with each puppet run. This will cause the replace facts command to take longer than it normally does, and consequently will widen the window for other database transactions to conflict with the update and cause a rollback, which
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Simon Oxwell commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt, I've now got 5 servers affected. I've collected the blocking fact values ids from the postgres logs, and they all seem to be related to our structured collections: puppetdb=# select factset_id,fact_value_id,value_hash,name,path from facts f inner join fact_values fv on f.fact_value_id=fv.id inner join fact_paths fp on f.fact_path_id=fp.id where f.fact_value_id in (807,810,1788,2709,2731,5697,1524348,1546444,1547903,1549732,1552056) order by fact_value_id; factset_id | fact_value_id |value_hash|name| path +---+--++-- 4 | 807 | eedbf60502cdb6b60552f2044fe16fcf6483e077 | funnelback_collections_profiles_structured | funnelback_collections_profiles_structured#~59#~collection 4 | 807 | eedbf60502cdb6b60552f2044fe16fcf6483e077 | funnelback_collections_structured | funnelback_collections_structured#~59 4 | 810 | cd339c27e31eff5c2c390c4c4e688d8ff1f60763 | funnelback_collections_profiles_structured | funnelback_collections_profiles_structured#~586#~collection 4 | 810 | cd339c27e31eff5c2c390c4c4e688d8ff1f60763 | funnelback_collections_structured | funnelback_collections_structured#~586
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt updated an issue PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Change By: Wyatt Alt Sprint: PuppetDB2015-05-06 Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt assigned an issue to Rob Browning PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Change By: Wyatt Alt Assignee: WyattAlt RobBrowning Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Simon Oxwell Thanks. I've got no real leads yet but we've pulled this into our current sprint for investigation. Our best vague guess at the moment is that one or both of those facts are being updated in a way that we are not handling correctly. I've got a few questions; interested if you can shed some light. are the problematic facts on the other three hosts also structured like these two, or are there any simple name/value pairs affected? when you run the delete command you mentioned in your report, does the problem come back immediately or after some time? does the problem always reappear on the same hosts? same facts? are you seeing more nodes affected as time goes on? are there any failed commands in /var/lib/puppetdb/mq/discarded that reflect the same constraint violation? are you running with the default puppet run interval on all your nodes? (30 minutes) for a host that's affected, could you give the output of facter -j funnelback_collections_profiles_structured facter -j funnelback_collections_structured (substituting in the relevant fact
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt assigned an issue to Wyatt Alt PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Change By: Wyatt Alt Assignee: WyattAlt Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups Puppet Bugs group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Simon Oxwell Thanks for reporting this. When you say same error, does it always have the same id? Would you mind giving the output of these queries? select * from facts f inner join fact_values fv on f.fact_value_id=fv.id inner join fact_paths fp on f.fact_path_id=fp.id where f.fact_value_id=1524348; select count(*) from fact_values where id not in (select distinct fact_value_id from facts); select fact_path_id, fp.name, count(*) cnt from facts f join fact_paths fp on fact_path_id=fp.id join fact_values fv on f.fact_value_id=fv.id where fv.id not in (select distinct fact_value_id from facts) group by fact_path_id, fp.name order by cnt desc; Also, does your setup include multiple PuppetDB nodes or just a single one? Add Comment
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Simon Oxwell commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Hi Wyatt, Currently I have four servers that aren't updating, so I've got four different 'replace facts' errors repeating in the logs, so there's four distinct fact_set_id's from the NOT EXISTS IN subquery that match those four servers. The fact _value_id's listed are consistent for each server. The query outputs: puppetdb=# select * from facts f inner join fact_values fv on f.fact_value_id=fv.id inner join fact_paths fp on f.fact_path_id=fp.id where f.fact_value_id=1524348; factset_id | fact_path_id | fact_value_id | id| value_type_id |value_hash| value_integer | value_float | value_string | value_boolean | value_json | id | depth |name| path +--+---+-+---+--+---+-+--+---++-+---++--- 36 | 139 | 1524348 | 1524348 | 0 | 5c11c98386f3c4e9fb8a59f6c8540d4db4cbf625 | | | cang-test| | | 139 | 2 | funnelback_collections_profiles_structured | funnelback_collections_profiles_structured#~4#~collection 36 | 46 | 1524348 | 1524348 | 0 | 5c11c98386f3c4e9fb8a59f6c8540d4db4cbf625 | | | cang-test| | | 46 | 1 | funnelback_collections_structured | funnelback_collections_structured#~4 (2 rows)
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Wyatt Alt commented on PDB-1448 Re: 'replace facts' failing due to foreign key constraint issue Simon Oxwell Thanks, that's informative. For the host associated with the two facts above, could you give the results of the following? curl -X GET http://localhost:8080/v4/nodes/your hostname/facts/funnelback_collections_profiles_structured curl -X GET http://localhost:8080/v4/nodes/your hostname/facts/funnelback_collections_profiles_profiles_structured You can identify the node with select certname from factsets where id=36; Wyatt Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d)
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title
Simon Oxwell commented on PDB-1448
Re: 'replace facts' failing due to foreign key constraint issue
Hi Wyatt,
Here's the results of the two queries (I took the liberty of changing the second one from funnelback_collections_profiles_profiles_structured to funnelback_collections_structured to match the query from the previous update, otherwise it returned nothing)
I've anonymized some of the names too (to hide our server and client names), but consistently across the output of both commands.
curl -X GET http://localhost:8080/v4/nodes/server-name.search/facts/funnelback_collections_profiles_structured
[ {
value : [ {
collection : client1-intranet,
profiles : [ _default, _default_preview ]
}, {
collection : client2-youtube,
profiles : [ _default, _default_preview ]
}, {
Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue
Title: Message Title Simon Oxwell created an issue PuppetDB / PDB-1448 'replace facts' failing due to foreign key constraint issue Issue Type: Bug Affects Versions: PDB 2.3.3 Assignee: Unassigned Created: 2015/04/22 9:53 PM Environment: CentOS 7, Postgres 9.2.7 Priority: Normal Reporter: Simon Oxwell 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 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
