[ZBX-19562] Oracle - data.sql script broken - Templates import broken Created: 2021 Jun 15  Updated: 2024 Apr 10  Resolved: 2021 Aug 25

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Installation (I), Templates (T)
Affects Version/s: 5.0.12, 5.2.6, 5.4.1
Fix Version/s: 5.0.14rc1, 6.0 (plan)

Type: Problem report Priority: Critical
Reporter: Thomas Oftring Assignee: Aleksandrs Larionovs (Inactive)
Resolution: Fixed Votes: 1
Labels: Database, Oracle, backend
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Oracle Databas 19c on Oracle Linux 8
Zabbix Server on RedHat 8

Attachments: PNG File Screenshot 2021-10-18 at 01.05.49.png     PNG File Screenshot_error_template_hp_ilo_import.png     PNG File Screenshot_error_template_oracle_import.png     PNG File Screenshot_error_template_tel_asterisk_import.png     Text File data_script_error.log     File mysql_create_items_table.sql     File oracle_create_items_table.sql     File postgresql_create_items_table.sql     PNG File screenshot-1.png     File sqlite_create_items_table.sql     XML File template_db_oracle_odbc.xml     XML File template_server_hp_ilo_snmp.xml     XML File template_tel_asterisk_http.xml    
Issue Links:
Causes
causes ZBX-19717 DATA.SQL build scripts fails with ORA... Closed
Sub-task
depends on ZBX-19227 Oracle 19c errors during data.sql exe... Closed
part of ZBXNEXT-6196 Zabbix is limited on oracle to store ... Closed
Team: Team INT
Sprint: Sprint 77 (Jun 2021), Sprint 78 (Jul 2021), Sprint 79 (Aug 2021)
Story Points: 1

 Description   

Hello,

the database scripts for creating the Zabbix database on Oracle is broken.

The source of the problems is the table definition for the table items. Two columns are leading to the problem:

params nvarchar2(2048) DEFAULT ''
description nvarchar2(2048) DEFAULT ''

Both columns are defined with a maximung length of 2048 chars. This is not enough to put in some items that are contained in the following three templates, that are included in the data.sql script:

1. Template DB Oracle by ODBC (https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/oracle_odbc?at=refs%2Ftags%2F5.0.12)
2. Template Server HP iLO SNMP (https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/server/hp_ilo_snmp?at=refs%2Ftags%2F5.0.12)
3. Template Tel Asterisk by HTTP (https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/tel/asterisk_http?at=refs%2Ftags%2F5.0.12)

With importing the according templates on a Zabbix 5.0.12 in the web frontend on a Oracle database you get the same errors. See the attached screenshots.
 
The Template DB Oracle by ODBC and Template Server HP iLO SNMP only affects Zabbix Version 5.0.12.
The Template  Tel Asterisk by HTTP affects also the Zabbix versions 5.2.6 and 5.4.1

For the databases MySQL, PostgreSQL and SQlite the colums are defined as text. So there is no problem with the templates (compare the attached table create scripts for that databases and the oracle database).

This is realy bad, because there would be an increasing number of Zabbix Templates that would not be usable on Zabbix installations with Oracle databases.

For the first step, the according templates should be removed from the Oracle data.sql script, so that it is possible to create the database without errors.

The second step must be to find all column defintions that are shorter, than in the other supported databases and fix them. All columns must have the same
lenght (definition) to be sure that all templates are working on all supported datbases.
 

Steps to reproduce:

  1. Create empty database in Oracle
  2. Execute script schema.sql
  3. Execute script images.sql
  4. Execute script data.sql (generates errors)

Result:
See attached screenshots
See log file data_script_error.log

 Comments   
Comment by Alexander Bakaldin [ 2021 Jul 05 ]

Available in:

Documentation updated:

Comment by Alexander Bakaldin [ 2021 Aug 18 ]

Available in:

Generated at Sun Apr 06 09:05:32 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.