[ZBX-4145] psql 9.1 installation issues Created: 2011 Sep 14  Updated: 2017 May 30  Resolved: 2011 Oct 11

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: None
Affects Version/s: 1.8.7, 1.9.6 (beta)
Fix Version/s: 2.0.0

Type: Incident report Priority: Blocker
Reporter: Michael Veselovski Assignee: dimir
Resolution: Fixed Votes: 0
Labels: installation, postgresql
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Centos 6.0 x64, PostgreSQL9.1


 Description   

hello

i've tried to install zabbix on the server with psql as DB engine. I've tried 2 versions of zabbix latest stable 1.8.7 and 1.9.6 beta. both installation failed on DB setup step
[root@zabbix data]# cat images_pgsql.sql | psql -U zabbix zabbix
invalid command \
invalid command \
invalid command \
invalid command \
invalid command \'
invalid command \'
invalid command \
invalid command \
invalid command \
invalid command \'
invalid command \
invalid command \
invalid command \
invalid command \
invalid command \
invalid command \
invalid command \
invalid command \
invalid command \
invalid command \'
ERROR: syntax error at or near "INSERT"
LINE 2: INSERT INTO images VALUES (2,1,'Hub (small)','\\211PNG\\015\...

I've tried to check images_pgsql.sql file for possible errors but was not able to find any.



 Comments   
Comment by Viktors Kohanovskis (Inactive) [ 2011 Sep 16 ]

Hey Michael! Obviously problem is within Postgres 9.1 syntax difference respectively to 8.x versions (as example) according to images_pgsql.sql data file that we will review on that. Additional information about will be within this issue. Thank you

Comment by Michael Veselovski [ 2011 Sep 18 ]

Hello Viktors

thanks for update.

Comment by Theodor Ciobanu [ 2011 Sep 23 ]

Does this affect new installations only (aka migrating an existing db from an older version to 9.1 with pg_dump&psql works)? Or is Zabbix currently incompatible with this version of postgres in general?

Comment by dimir [ 2011 Sep 23 ]

Good question. Still in the process of investigation about this (incompatibility of Zabbix with PostgreSQL 9.1 in general). The problem is how we handle single-quote mark in the string in PostgreSQL case. We used escaping (\') while this is not standard process of doing that in PostgreSQL. Starting from version 9.1 they have decided to enable option

standard_conforming_strings

by default in PostgreSQL configuration. So in order to support that we would need to check all the places on how we handle such situations (both frontend and server side) in case of PostgreSQL.

As for new installations here is the fix in development branch:

svn://svn.zabbix.com/branches/dev/ZBX-4145

It would be great if you could try that and say if that fixes the new installation process.

Comment by dimir [ 2011 Sep 26 ]

We have the escape warnings turned off in PostgreSQL:

$ grep escape_string_warning src/libs/zbxdb/db.c -B3 -A1
if (80100 <= ZBX_PG_SVERSION)

{ /* disable "nonstandard use of \' in a string literal" warning */ DBexecute("set escape_string_warning to off"); }

Starting from 9.1 (including) this won't be enough. That is if "standard_conforming_strings" is ON (which is default starting from 9.1) we will get an error instead of warning. We could set it here to OFF which would be the easiest but not the prettiest solution.

Comment by dimir [ 2011 Sep 27 ]

I couldn't find any problems running Zabbix on PostgreSQL 9.1 besides installation issue which was fixed above.

Comment by dimir [ 2011 Sep 27 ]

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-4145 .

Comment by dimir [ 2011 Oct 11 ]

Fixed in pre1.8.9 r22284, pre1.9.7 r22288.

Comment by richlv [ 2011 Oct 11 ]

just a note
guidelines call for no extra spaces, but pgsql images now have "...', 'hex'"

do we wish to change that ?

Comment by dimir [ 2011 Oct 11 ]

Nicely noted! I have fixed it directly in pre1.8.9 and pre1.9.7.

Generated at Wed Apr 24 14:45:03 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.