Bugzilla (bugzilla.redhat.com) will be under maintenance for infrastructure upgrades and will not be available on July 31st between 12:30 AM - 05:30 AM UTC. We appreciate your understanding and patience. You can follow status.redhat.com for details.
Bug 1417019 - SQL Syntax running manager container in cloudforms appliance
Summary: SQL Syntax running manager container in cloudforms appliance
Keywords:
Status: VERIFIED
Alias: None
Product: Middleware Manager
Classification: JBoss
Component: Inventory
Version: unspecified
Hardware: Unspecified
OS: Linux
high
high
Target Milestone: ---
: ---
Assignee: Joel Takvorian
QA Contact: Matt Mahoney
jstickle
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2017-01-27 01:35 UTC by mario.mendoza
Modified: 2018-01-30 17:56 UTC (History)
4 users (show)

Fixed In Version:
Doc Type: Enhancement
Doc Text:
If you use a pre-existing Postgres cluster, you need to make sure that standard_conforming_strings are enabled (default since postgres 9.1) If in doubt you can issue an ALTER TABLE statement to enable them. Suppose the database for Middleware Manager is called 'mwmanager' Then run (as db-admin) ALTER DATABASE mwmanager SET standard_conforming_strings TO ON
Clone Of:
Environment:
Last Closed:


Attachments (Terms of Use)
Procedure and Logs (203.96 KB, text/plain)
2017-01-27 01:35 UTC, mario.mendoza
no flags Details
Log without errors after recommended fix applied (129.02 KB, text/plain)
2017-01-31 21:28 UTC, mario.mendoza
no flags Details
hawkular-services Log (15.54 KB, application/zip)
2017-02-15 15:25 UTC, Matt Mahoney
no flags Details
evm.log (712.25 KB, application/zip)
2017-02-15 15:26 UTC, Matt Mahoney
no flags Details
CFME Screen-shot (63.07 KB, image/png)
2017-02-15 15:26 UTC, Matt Mahoney
no flags Details

Description mario.mendoza 2017-01-27 01:35:00 UTC
Created attachment 1244909 [details]
Procedure and Logs

Description of problem:

A) Using the procedure described in attached file there is an error  which prevents to start the Inventory service.

19:49:54,824 WARN  [org.hawkular.inventory.cdi] (ServerService Thread Pool -- 77) HAWKINV003501: Inventory backend failed to initialize in an attempt 1 of 15 with message: org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "'\';"
  Position: 94.

B) Looking at the postgresql logs there is an SQL sintax error: 

ERROR:  unterminated quoted string at or near "'\';" at character 94
STATEMENT:  COPY "V_BULK_TEMP_EDGE1hZNSlbb" ("within") FROM stdin CSV DELIMITER '	' QUOTE e'\x01' ESCAPE '\';
Version-Release number of selected component (if applicable):


How reproducible:
 
 Ever

Steps to Reproduce:

 See attached file

Actual results:

 Database syntax error.

Expected results:


Additional info:

Comment 1 Heiko W. Rupp 2017-01-27 10:36:13 UTC
I see you pointing Hawkular at this jdbc url:

-e HAWKULAR_INVENTORY_JDBC_URL=jdbc:postgresql://172.17.0.1:5432/mwmanager 

while the postgres setup commands deal with a PSQL instance on 192....

Are you sure that is the same? Can you retry the installation with the 192.. address in the jdbc url?

Comment 2 mario.mendoza 2017-01-28 13:17:22 UTC
Yes this is the same. 

The service connects to database without problems.
The database log records the connection.
The problem is a sql sentence syntax error.

The container can only connect with Docker network. This is the reason.

Comment 3 mario.mendoza 2017-01-29 11:06:36 UTC
I can share my VM in case you require it.

Comment 4 Lukas Krejci 2017-01-31 14:18:43 UTC
There are 2 possible fixes:

1) Modify the setup commands
2) Create new version of Inventory that does the necessary setup on startup (but would require privs to alter the database in addition to create/alter/delete tables in its database).

The attached setup requires the following adjustment:
In "[2] SET DATABASE & MWMANAGER CONTAINERS", modify the step 7 to read:

[root@localhost ~]# su postgres
bash-4.2$ psql
could not change directory to "/root": Permission denied
psql (9.5.4)
Type "help" for help.

postgres=# CREATE USER mwmanager WITH PASSWORD 'mwmanager';
CREATE ROLE
postgres=# CREATE DATABASE mwmanager OWNER mwmanager;
CREATE DATABASE
postgres=# ALTER DATABASE mwmanager SET standard_conforming_strings TO ON;
ALTER DATABASE
postgres=# \q
bash-4.2$ exit


I.e. add the "ALTER DATABASE" command to modify the configuration of the database to be different from the appliance default. Note that this is specific to the "mwmanager" database and does NOT influence the "vmdb_production" database used by CloudForms.

The default value of standard_conforming_strings configuration property is "ON" in Postgres (since 9.1), but the CloudForms appliance modifies it to "OFF" (for reasons it is not sure about. The comment in /var/opt/rh/rh-postgresql95/lib/pgsql/data/postgresql.conf reads "MIQ Value not sure why this is enabled").

Comment 5 Lukas Krejci 2017-01-31 19:19:36 UTC
Matt, could you please test this with the setup instructions modified as per my comment? Thanks.

Comment 6 Lukas Krejci 2017-01-31 19:20:49 UTC
Whoops, I thought Matt was assigned.. :) Mike, could you please assign this to someone to test with the modified setup instructions?

Comment 7 mario.mendoza 2017-01-31 21:22:42 UTC
Adding the "ALTER DATABASE" it works as expected and no ERRORS are found.
I'll attach a new log file

Comment 8 mario.mendoza 2017-01-31 21:28:23 UTC
Created attachment 1246454 [details]
Log without errors after recommended fix applied

Comment 10 Matt Mahoney 2017-02-15 15:25:40 UTC
Created attachment 1250631 [details]
hawkular-services Log

Comment 11 Matt Mahoney 2017-02-15 15:26:14 UTC
Created attachment 1250632 [details]
evm.log

Comment 12 Matt Mahoney 2017-02-15 15:26:48 UTC
Created attachment 1250633 [details]
CFME Screen-shot


Note You need to log in before you can comment on or make changes to this bug.