Uploaded image for project: 'ZABBIX FEATURE REQUESTS'
  1. ZABBIX FEATURE REQUESTS
  2. ZBXNEXT-2254

error after sucessfull zabbix_server upgrade - wrong sql command on postgres

XMLWordPrintable

      I upgraded zabbix installation from 2.0.9 to 2.2.1. I executed new zabbix_server binary for the first time and database tables were upgraded successfully. Then I tried to restart zabbix server and in zabbix log appeared:
      — snipped output —
      26486:20140212:135012.565 In DBconnect() flag:0
      26486:20140212:135012.575 query [txnlev:0] [select oid from pg_type where typname='bytea']
      26486:20140212:135012.577 PostgreSQL Server version: 90204
      26486:20140212:135012.577 query [txnlev:0] [set escape_string_warning to off]
      26486:20140212:135012.577 query [txnlev:0] [show standard_conforming_strings]
      26486:20140212:135012.577 query [txnlev:0] [set bytea_output=escape]
      26486:20140212:135012.578 End of DBconnect():0
      26486:20140212:135012.578 query [txnlev:0] [select 1 from information_schema.tables where table_name='dbversion' and table_schema='public']
      26486:20140212:135012.586 DBcheck_version() "dbversion" does not exist
      26486:20140212:135012.586 query [txnlev:0] [select 1 from information_schema.columns where table_name='config' and column_name='server_check_interval']
      26486:20140212:135012.598 query [txnlev:1] [begin;]
      26486:20140212:135012.598 query [txnlev:1] [create table dbversion (
      mandatory integer default '0' not null,
      optional integer default '0' not null
      )]
      26486:20140212:135012.599 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: relation "dbversion" already exists
      [create table dbversion (
      mandatory integer default '0' not null,
      optional integer default '0' not null
      )]
      — snipped output —

      After successfull upgrade zabbix_server run this sql command:
      26486:20140212:135012.578 query [txnlev:0] [select 1 from information_schema.tables where table_name='dbversion' and table_schema='public']

      Unfortunately, this sql command will always return zero, because all zabbix tables belongs to schema named zabbixp in my case. There is no default public schema because of security reasons.
      Setting "DBSchema" parameter in zabbix_server.conf doesn't work for Postgresql db, query is still performed with ....table_schema='public'

      First solution:
      Is possible activate the parametr DBSchema for postgresql too?

      Second solution:
      Run sql commands with no schema specification,because is possible to set parametr schema search order for names that are not schema-qualified. For individual DB users can be set "ALTER ROLE 'role_name' SET search_path TO 'schema_name';" or globally in postgresql.conf is parametr search_path. This setting is usually responsibility of DB admin.

      select 1 from information_schema.tables where table_name='dbversion';
      It will work in all cases, because of usage default schema.

      Thank you
      Tom

            kristsk Krists Krigers (Inactive)
            tommycz Tomas Pipo
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: