[ZBX-5691] when adding new graph to template get lot's of SQL error Created: 2012 Oct 11  Updated: 2017 May 30  Resolved: 2012 Oct 19

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: 2.0.3
Fix Version/s: 2.0.4rc1, 2.1.0

Type: Incident report Priority: Minor
Reporter: Alexander Romanov Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: graphs, oracle
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Debian 6.0.6, Oracle 11g, Apache2

Attachments: JPEG File axis.jpg    
Issue Links:
Duplicate
is duplicated by ZBX-6687 Error when trying to create a new graph. Closed

 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

 Comments   
Comment by Alexander Vladishev [ 2012 Oct 15 ]

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;
Comment by Alexander Romanov [ 2012 Oct 15 ]

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)
Comment by Alexander Vladishev [ 2012 Oct 15 ]

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

Comment by Alexander Romanov [ 2012 Oct 15 ]

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

Comment by Alexander Vladishev [ 2012 Oct 15 ]

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.

Comment by Alexander Romanov [ 2012 Oct 15 ] 
TEMPLATEID (null)	
FLAGS	'0'
Comment by Alexander Vladishev [ 2012 Oct 16 ]

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'
Comment by Alexander Romanov [ 2012 Oct 18 ]

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 ?

Comment by Alexander Vladishev [ 2012 Oct 18 ]

Thanks, it will be added in future releases.

Comment by Alexander Romanov [ 2012 Oct 19 ]

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

Comment by Alexey Fukalov [ 2012 Oct 19 ]

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

Comment by Alexander Romanov [ 2012 Oct 19 ]

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

Comment by Alexander Vladishev [ 2012 Oct 19 ]

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

Comment by Alexey Fukalov [ 2012 Oct 19 ]

Fixed in 2.0.4rc1 r30993, pre-2.1.0 r30994.

Comment by Fabiano Souza [ 2014 Feb 28 ]

When you create a graph changes these two items:

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

Generated at Mon Apr 07 09:04:30 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.