ZABBIX BUGS AND ISSUES

when adding new graph to template get lot's of SQL error

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 2.0.3
  • Fix Version/s: 2.0.4rc1, 2.1.0
  • Component/s: Frontend (F)
  • Labels:
  • Environment:
    Debian 6.0.6, Oracle 11g, Apache2
  • Zabbix ID:
    RTF

Description

I'm trying to add graph to template and get this errors in frontend:
==
Details
Graph added

ociexecute(): ORA-01722: invalid number [include/db.inc.php:517]
SQL error [ORA-01722: invalid number] in [INSERT INTO graphs (name,width,height,ymin_type,ymax_type,yaxismin,yaxismax,ymin_itemid,ymax_itemid,show_work_period,show_triggers,graphtype,show_legend,show_3d,percent_left,percent_right,graphid) VALUES ('States','900','200','0','1','0.00','100.00',NULL,NULL,'1','1','0','1','0','0','0','527')]
ociexecute(): ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found [include/db.inc.php:517]
SQL error [ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found] in [INSERT INTO graphs_items (gitemid,graphid,itemid,sortorder,type,calc_fnc,drawtype,yaxisside,color) VALUES ('1733','527','23570','0','0','2','3','0','FF0000')]
ociexecute(): ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found [include/db.inc.php:517]
SQL error [ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found] in [INSERT INTO graphs_items (gitemid,graphid,itemid,sortorder,type,calc_fnc,drawtype,yaxisside,color) VALUES ('1734','527','23588','1','0','2','1','0','009900')]
ociexecute(): ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found [include/db.inc.php:517]
SQL error [ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found] in [INSERT INTO graphs_items (gitemid,graphid,itemid,sortorder,type,calc_fnc,drawtype,yaxisside,color) VALUES ('1735','527','23589','2','0','2','0','0','FFAA00')]
ociexecute(): ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found [include/db.inc.php:517]
SQL error [ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found] in [INSERT INTO graphs_items (gitemid,graphid,itemid,sortorder,type,calc_fnc,drawtype,yaxisside,color) VALUES ('1736','527','23590','3','0','2','0','0','FF0000')]
ociexecute(): ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found [include/db.inc.php:517]
SQL error [ORA-02291: integrity constraint (MONITORING.C_GRAPHS_ITEMS_1) violated - parent key not found] in [INSERT INTO graphs_items (gitemid,graphid,itemid,sortorder,type,calc_fnc,drawtype,yaxisside,color) VALUES ('1737','527','23591','4','0','2','0','0','0000FF')]
==
and of course graph wasn't add

Issue Links

Activity

Hide
Alexander Vladishev added a comment -

All this SQL statements are executed without errors on our test environment. Probably a problem in database structure.
Please, attach an output of the SQL command:

describe graphs;
describe graphs_items;
Show
Alexander Vladishev added a comment - All this SQL statements are executed without errors on our test environment. Probably a problem in database structure. Please, attach an output of the SQL command:
describe graphs;
describe graphs_items;
Hide
Alexander Romanov added a comment -

describe graphs;

SQL> describe graphs;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRAPHID				   NOT NULL NUMBER(20)
 NAME						    NVARCHAR2(128)
 WIDTH					   NOT NULL NUMBER(10)
 HEIGHT 				   NOT NULL NUMBER(10)
 YAXISMIN				   NOT NULL NUMBER(20,4)
 YAXISMAX				   NOT NULL NUMBER(20,4)
 TEMPLATEID					    NUMBER(20)
 SHOW_WORK_PERIOD			   NOT NULL NUMBER(10)
 SHOW_TRIGGERS				   NOT NULL NUMBER(10)
 GRAPHTYPE				   NOT NULL NUMBER(10)
 SHOW_LEGEND				   NOT NULL NUMBER(10)
 SHOW_3D				   NOT NULL NUMBER(10)
 PERCENT_LEFT				   NOT NULL NUMBER(20,4)
 PERCENT_RIGHT				   NOT NULL NUMBER(20,4)
 YMIN_TYPE				   NOT NULL NUMBER(10)
 YMAX_TYPE				   NOT NULL NUMBER(10)
 YMIN_ITEMID					    NUMBER(20)
 YMAX_ITEMID					    NUMBER(20)
 FLAGS					   NOT NULL NUMBER(10)

describe graphs_items;

SQL> describe graphs_items;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GITEMID				   NOT NULL NUMBER(20)
 GRAPHID				   NOT NULL NUMBER(20)
 ITEMID 				   NOT NULL NUMBER(20)
 DRAWTYPE				   NOT NULL NUMBER(10)
 SORTORDER				   NOT NULL NUMBER(10)
 COLOR						    NVARCHAR2(6)
 YAXISSIDE				   NOT NULL NUMBER(10)
 CALC_FNC				   NOT NULL NUMBER(10)
 TYPE					   NOT NULL NUMBER(10)
Show
Alexander Romanov added a comment - describe graphs;
SQL> describe graphs;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRAPHID				   NOT NULL NUMBER(20)
 NAME						    NVARCHAR2(128)
 WIDTH					   NOT NULL NUMBER(10)
 HEIGHT 				   NOT NULL NUMBER(10)
 YAXISMIN				   NOT NULL NUMBER(20,4)
 YAXISMAX				   NOT NULL NUMBER(20,4)
 TEMPLATEID					    NUMBER(20)
 SHOW_WORK_PERIOD			   NOT NULL NUMBER(10)
 SHOW_TRIGGERS				   NOT NULL NUMBER(10)
 GRAPHTYPE				   NOT NULL NUMBER(10)
 SHOW_LEGEND				   NOT NULL NUMBER(10)
 SHOW_3D				   NOT NULL NUMBER(10)
 PERCENT_LEFT				   NOT NULL NUMBER(20,4)
 PERCENT_RIGHT				   NOT NULL NUMBER(20,4)
 YMIN_TYPE				   NOT NULL NUMBER(10)
 YMAX_TYPE				   NOT NULL NUMBER(10)
 YMIN_ITEMID					    NUMBER(20)
 YMAX_ITEMID					    NUMBER(20)
 FLAGS					   NOT NULL NUMBER(10)
describe graphs_items;
SQL> describe graphs_items;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GITEMID				   NOT NULL NUMBER(20)
 GRAPHID				   NOT NULL NUMBER(20)
 ITEMID 				   NOT NULL NUMBER(20)
 DRAWTYPE				   NOT NULL NUMBER(10)
 SORTORDER				   NOT NULL NUMBER(10)
 COLOR						    NVARCHAR2(6)
 YAXISSIDE				   NOT NULL NUMBER(10)
 CALC_FNC				   NOT NULL NUMBER(10)
 TYPE					   NOT NULL NUMBER(10)
Hide
Alexander Vladishev added a comment -

What default values have the 'graphs.templateid' and 'graphs.flags' fields?

Show
Alexander Vladishev added a comment - What default values have the 'graphs.templateid' and 'graphs.flags' fields?
Hide
Alexander Romanov added a comment -

sorry, i doesn't know, where can i get this information

Show
Alexander Romanov added a comment - sorry, i doesn't know, where can i get this information
Hide
Alexander Vladishev added a comment -

You can use the statement:

select column_name,data_default from dba_tab_columns where table_name = 'GRAPHS' and owner = '<owner>' and column_name in ('FLAGS', 'TEMPLATEID');

Please change '<owner>' to correct value.

Show
Alexander Vladishev added a comment - You can use the statement:
select column_name,data_default from dba_tab_columns where table_name = 'GRAPHS' and owner = '<owner>' and column_name in ('FLAGS', 'TEMPLATEID');
Please change '<owner>' to correct value.
Hide
Alexander Romanov added a comment -
TEMPLATEID (null)	
FLAGS	'0'
Show
Alexander Romanov added a comment -
TEMPLATEID (null)	
FLAGS	'0'
Hide
Alexander Vladishev added a comment - - edited

Probably with tables everything is all ok.

Please check NLS_NUMERIC_CHARACTERS variable. Its should contains '.' (dot).

select value from nls_database_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
Show
Alexander Vladishev added a comment - - edited Probably with tables everything is all ok. Please check NLS_NUMERIC_CHARACTERS variable. Its should contains '.' (dot).
select value from nls_database_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
Hide
Alexander Romanov added a comment -

Result is ',' (comma). I use oracle instance not only for monitoring.

So, as i suppose, i have to options:
1. new instance only for monitoring, and change this parameter
2. using

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '
somewhere in zabbix fronted.
can you include this in future releases ?

Show
Alexander Romanov added a comment - Result is ',' (comma). I use oracle instance not only for monitoring. So, as i suppose, i have to options: 1. new instance only for monitoring, and change this parameter 2. using
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '
somewhere in zabbix fronted. can you include this in future releases ?
Hide
Alexander Vladishev added a comment -

Thanks, it will be added in future releases.

Show
Alexander Vladishev added a comment - Thanks, it will be added in future releases.
Hide
Alexander Romanov added a comment -

That's good.
Can you help me with current frontend version - where can i insert that code now?

Show
Alexander Romanov added a comment - That's good. Can you help me with current frontend version - where can i insert that code now?
Hide
Alexey Fukalov added a comment -

Dev branch: svn://svn.zabbix.com/branches/dev/ZBX-5691

Show
Alexey Fukalov added a comment - Dev branch: svn://svn.zabbix.com/branches/dev/ZBX-5691
Hide
Alexander Romanov added a comment - - edited

Thanks, this ticket solves not only described problem, but also some others, for example simple graphs of Template Linux item - CPU IO WAIT, as i suppose - because of '.' or ',' in NLS

Show
Alexander Romanov added a comment - - edited Thanks, this ticket solves not only described problem, but also some others, for example simple graphs of Template Linux item - CPU IO WAIT, as i suppose - because of '.' or ',' in NLS
Hide
Alexander Vladishev added a comment -

Successfully tested! Please review my changes in r30991:30992.

Show
Alexander Vladishev added a comment - Successfully tested! Please review my changes in r30991:30992.
Hide
Alexey Fukalov added a comment -

Fixed in 2.0.4rc1 r30993, pre-2.1.0 r30994.

Show
Alexey Fukalov added a comment - Fixed in 2.0.4rc1 r30993, pre-2.1.0 r30994.
Hide
Fabiano Souza added a comment -

When you create a graph changes these two items:

Y axis MIN value = Fized = 0
Y axis MAX value = Fixed = 100

Show
Fabiano Souza added a comment - When you create a graph changes these two items: Y axis MIN value = Fized = 0 Y axis MAX value = Fixed = 100

People

Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: