Jira (PDB-1448) 'replace facts' failing due to foreign key constraint issue

[Simon Oxwell (JIRA)]

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". 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 (5407) order by fact_value_id; factset_id | fact_value_id | value_hash | name | path ------------+---------------+------------------------------------------+------------------+------------------ 93 | 5407 | 10a70dee52f2a1e7d64cc4c13c2dd1dd1489a1bd | macaddress_vnet2 | macaddress_vnet2 This appears to have occurred when the server was rebooted. It's a vm host, and its reordered the virtual interfaces: The mac address that was on vnet2 is now on vnet1, and vnet2 has a new value, was previously vnet0. (and vnet0 now has vnet1's old value) I tried this on a standalone puppetdb server talking it to with curl: curl -X POST \ -H "Accept: application/json" \ -H "Content-Type: application/json" \ -d '{"command":"replace facts","version":3,"payload":{"name":"example_server","environment":"DEV","values":{"key1":"value1","key2":"value2","key3":"value3"}}}' \ http://localhost:8080/v3/commands   curl -X POST \ -H "Accept: application/json" \ -H "Content-Type: application/json" \ -d '{"commad":"replace facts","version":3,"payload":{"name":"example_server","environment":"DEV","values":{"key1":"value2","key2":"value3","key3":"value1"}}}' \ http://localhost:8080/v3/commands And that appeared to break it: 2015-05-01 17:43:00,027 ERROR [c.p.p.command] [74417443-1c92-433c-b7c8-c8113b0f7441] [replace facts] Retrying after attempt 4, due to: java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (31,32,33) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (31,32,33) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((1,25),(1,26),(1,27))) was aborted. Call getNextException to see the cause. java.sql.BatchUpdateException: Batch entry 0 DELETE FROM fact_values fv WHERE fv.id in (31,32,33) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in (31,32,33) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in ((1,25),(1,26),(1,27))) was aborted. Call getNextException to see the cause. at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746) ~[puppetdb.jar:na] SQL log: ERROR: update or delete on table "fact_values" violates foreign key constraint "fact_value_id_fk" on table "facts" DETAIL: Key (id)=(31) is still referenced from table "facts". STATEMENT: DELETE FROM fact_values fv WHERE fv.id in ($1,$2,$3) AND NOT EXISTS (SELECT 1 FROM facts f WHERE f.fact_value_id in ($4,$5,$6) AND f.fact_value_id = fv.id AND (f.factset_id, f.fact_path_id) NOT in (($7,$8),($9,$10),($11,$12))) all my facts: 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; 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 ------------+--------------+---------------+----+---------------+------------------------------------------+---------------+-------------+--------------+---------------+------------+----+-------+------+------ 1 | 25 | 31 | 31 | 0 | daf626c4ebd6bdd697e043111454304e5fb1459e | | | value1 | | | 25 | 0 | key1 | key1 1 | 26 | 32 | 32 | 0 | a4b6834d78bfee9a445806ff3b3664662a10ebe5 | | | value3 | | | 26 | 0 | key3 | key3 1 | 27 | 33 | 33 | 0 | 849988af22dbd04d3e353caf77f9d81241ca9ee2 | | | value2 | | | 27 | 0 | key2 | key2 (3 rows) I'm not sure why it started me from fact_value_id 31 - I have previously shutdown puppetdb, dropped the database and deleted the KahaDB for the MQ to try and clean it up. Anyway, hope this may help. I'll go back and get the other data you asked for next week. 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.