ZABBIX BUGS AND ISSUES
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-5691

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

    Details

    • Type: Bug Bug
    • Status: 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

      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

            • Assignee:
              Alexey Fukalov
              Reporter:
              Alexander Romanov
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: