[PDB-4509] Fresh install of puppetdb will not start ERROR: invalid byte sequence for encoding "UTF8" Created: 2019/09/18  Updated: 2019/10/22  Resolved: 2019/10/04

Status: Resolved
Project: PuppetDB
Component/s: PuppetDB
Affects Version/s: PDB 6.5.0
Fix Version/s: PDB 5.2.10, PDB 6.3.5, PDB 6.7.1

Type: Bug Priority: Normal
Reporter: Scot Kreienkamp Assignee: Austin Blatt
Resolution: Fixed Votes: 0
Labels: resolved-issue-added
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

RHEL7.7, PG9.6.15, puppet-agent-6.8.1, puppetdb-6.5.0


puppetdb=# select extname from pg_extension;




Attachments: PNG File psql -l output.PNG     Text File puppetdb.log    
Issue Links:
causes PDB-4555 PuppetDB connection failure causing s... Resolved
Team: PuppetDB
Method Found: Customer Feedback
Release Notes: Bug Fix
Release Notes Summary: PuppetDB migrations require the postgres database the setting `standard_conforming_strings` to be `true`. Before a Postgres exception would terminate startup, now we will verify that setting before checking if any migrations are necessary.
QA Risk Assessment: Needs Assessment


On first start of puppetdb after install it is failing with error: 

2019-09-18T13:31:10.415-04:00 INFO [p.p.s.migrate] Applying database migration version 43
2019-09-18T13:31:10.456-04:00 ERROR [p.p.s.migrate] Caught SQLException during migration
java.sql.BatchUpdateException: Batch entry 1 CREATE AGGREGATE sha1_agg (BYTEA)
sfunc = dual_sha1,
stype = bytea,
initcond = '\x00'
) was aborted: ERROR: invalid byte sequence for encoding "UTF8": 0x00 Call getNextException to see other errors in the batch.

Comment by Austin Blatt [ 2019/09/18 ]

Robert Roland Can you take a look at this error? It's in the same realm as your md5 to sha1 migration change, so I want to check if you know what would cause this error off the top of your head.

Comment by Scot Kreienkamp [ 2019/09/18 ]

Manual steps ran:
createuser -DRSP puppetdb
createdb -E UTF8 -O puppetdb puppetdb
psql puppetdb -c 'create extension pg_trgm'

As a troubleshooting step I configured puppetdb to connect using the postgres user. No change.

Comment by Austin Blatt [ 2019/09/18 ]

Scot Kreienkamp it seems like this is not the first time someone has hit it, since this Puppet Users thread has the same error that you encountered, but alas, no resolution. But we can start off where they were.

Can you attach the output of psql -c '\l+'?

Comment by Scot Kreienkamp [ 2019/09/19 ]

It would be difficult to read due to the formatting so I put in a screenshot of it.

Comment by Scot Kreienkamp [ 2019/09/19 ]

That thread was me when I was trying to set up PuppetDB the first time around on the server I'm now trying to replace... I switched from Oracle Linux to Redhat Linux and it magically worked. I never knew why, which is why I never posted a resolution. This time I started with RH thinking that I wouldn't see that problem again only to have it resurface. These are very vanilla installs of RHEL7 and PuppetDB, was then and is now. That's what has me puzzled.

Comment by Scot Kreienkamp [ 2019/09/23 ]

Figured it out... went back and compared all the database settings in the old server and the new server, and one settings change broke it:

standard_conforming_strings = off

That's in our standard postgres template because one of our primary internal applications requires it. Once I changed that to on it worked fine. Maybe a check could be added before the migrations that outputs an error message if that setting is off?

Comment by Austin Blatt [ 2019/09/23 ]

Scot Kreienkamp that's awesome, yes! We will definitely add a check for this since we've been stumped by it at least a couple of times.

Generated at Sun Sep 27 17:24:57 PDT 2020 using Jira 8.5.2#805002-sha1:a66f9354b9e12ac788984e5d84669c903a370049.