[PDB-1363] Puppetdb broken because of forced check for tables in public schema Created: 2015/03/31  Updated: 2015/05/06  Resolved: 2015/04/01

Status: Closed
Project: PuppetDB
Component/s: None
Affects Version/s: PDB 2.3.1
Fix Version/s: PDB 2.3.2

Type: Bug Priority: Normal
Reporter: Chuck Schweizer Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Template:
Story Points: 0
Sprint: PuppetDB 2015-04-08
QA Contact: Kurt Wall

 Description   

The following commit breaks puppetdb in my environment because we do not use the PUBLIC schema.

https://github.com/puppetlabs/puppetdb/commit/53210f67eb8b2223cfb719ae484313b8cec55042#diff-11e61c2a3b3f71dacf7007d803e71b3d


QA Risk Assessment

Probability Low
Severity Low - failed installation
Risk Level Low
Test Level Unit


 Comments   
Comment by Rob Browning [ 2015/03/31 ]

Does it fail with an SQLException by any chance? And thanks for the report. Oh, nevermind, it shouldn't.

Comment by Chuck Schweizer [ 2015/03/31 ]

Sorry should have been a little more specific. The change makes it so that only the public schema is searched for existing puppetdb tables. The end result in my environment is that puppetdb always tries to create the tables since they don't exist in the public schema and then fails to create them because they already exist.

Comment by Chuck Schweizer [ 2015/03/31 ]

Postgres Error

2015-03-31 22:50:37 UTC|puppetdb|39489|CREATE TABLE|10.0.0.50|puppet ERROR:  relation "certnames" already exists
2015-03-31 22:50:37 UTC|puppetdb|39489|CREATE TABLE|10.0.0.50|puppet STATEMENT:  CREATE TABLE certnames (name TEXT PRIMARY KEY)

PuppetDB Error

2015-03-31 22:39:57,606 INFO  [c.p.p.s.migrate] Applying database migration version 1
2015-03-31 22:39:57,622 ERROR [c.p.p.s.migrate] Caught SQLException during migration
java.sql.BatchUpdateException: Batch entry 0 CREATE TABLE certnames (name TEXT PRIMARY KEY) was aborted.  Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2746) ~[puppetdb.jar:na]

PuppetDB debug

2015-03-31 23:09:24,987 DEBUG [main] [c.j.b.PreparedStatementHandle] SELECT table_name FROM information_schema.tables WHERE LOWER(table_schema) = 'public'
2015-03-31 23:09:25,025 DEBUG [main] [c.j.b.PreparedStatementHandle] SELECT table_name FROM information_schema.tables WHERE LOWER(table_schema) = 'public'
2015-03-31 23:09:25,030 INFO  [main] [c.p.p.s.migrate] Applying database migration version 1

Postgres tables exist

                               List of relations
 Schema |         Name          | Type  |   Owner    |    Size    | Description 
--------+-----------------------+-------+------------+------------+-------------
 puppet | catalog_resources     | table | puppetdb   | 8192 bytes | 
 puppet | catalogs              | table | puppetdb   | 8192 bytes | 
 puppet | certnames             | table | puppetdb   | 3024 kB    | 
 puppet | edges                 | table | puppetdb   | 8192 bytes | 
 puppet | environments          | table | puppetdb   | 16 kB      | 
 puppet | fact_paths            | table | puppetdb   | 168 kB     | 
 puppet | fact_values           | table | puppetdb   | 216 MB     | 
 puppet | facts                 | table | puppetdb   | 591 MB     | 
 puppet | factsets              | table | puppetdb   | 6176 kB    | 
 puppet | latest_reports        | table | puppetdb   | 8192 bytes | 
 puppet | report_statuses       | table | puppetdb   | 8192 bytes | 
 puppet | reports               | table | puppetdb   | 8192 bytes | 
 puppet | resource_events       | table | puppetdb   | 8192 bytes | 
 puppet | resource_params       | table | puppetdb   | 8192 bytes | 
 puppet | resource_params_cache | table | puppetdb   | 8192 bytes | 
 puppet | schema_migrations     | table | puppetdb   | 40 kB      | 
 puppet | value_types           | table | puppetdb   | 40 kB      | 
(17 rows)

Comment by Chuck Schweizer [ 2015/04/01 ]

In order to get puppetdb 2.3.1 working in my environment this is what I changed. Not sure if this is the best way to fix the issue to work with a non public schema, but it did work for me.

--- storage_utils.clj	2015-04-01 11:34:19.812850426 +0000
+++ ./temp/com/puppetlabs/puppetdb/scf/storage_utils.clj	2015-03-31 23:30:01.913622050 +0000
@@ -73,7 +73,7 @@
   current connection.  This is most useful for debugging / testing  purposes
   to allow introspection on the database.  (Some of our unit tests rely on this.)"
   []
-  (let [query   "SELECT table_name FROM information_schema.tables WHERE LOWER(table_schema) = 'public'"
+  (let [query   "SELECT table_name FROM information_schema.tables"
         results (sql/transaction (jdbc/query-to-vec query))]
     (map :table_name results)))
 
@@ -82,7 +82,7 @@
   current connection.  This is most useful for debugging / testing  purposes
   to allow introspection on the database.  (Some of our unit tests rely on this.)"
   []
-  (let [query   "SELECT sequence_name FROM information_schema.sequences WHERE LOWER(sequence_schema) = 'public'"
+  (let [query   "SELECT sequence_name FROM information_schema.sequences"
         results (sql/transaction (jdbc/query-to-vec query))]
     (map :sequence_name results)))

Comment by Ryan Senior [ 2015/04/01 ]

Chuck Schweizer That makes sense, then you just provide the default schema in your JDBC connection string?

Comment by Chuck Schweizer [ 2015/04/01 ]

In our case we just update the search string for the puppetdb user

ALTER USER puppetdb SET search_path to 'puppet'

Comment by Rob Browning [ 2015/04/01 ]

Thanks for the update. At this point, we're preparing a 2.3.2 release that should fix the current problem, and we've created a ticket (PDB-1384) to consider adding an arrangement like yours to the test suite.

Comment by Wyatt Alt [ 2015/04/01 ]

Chuck Schweizer we have addressed this issue in today's 2.3.2 release.

Generated at Tue Jun 25 08:31:59 PDT 2019 using JIRA 7.7.1#77002-sha1:e75ca93d5574d9409c0630b81c894d9065296414.