[ZBX-16619] SQL statement execution when call item.create method via API Created: 2019 Sep 10  Updated: 2019 Sep 24

Status: Need info
Project: ZABBIX BUGS AND ISSUES
Component/s: API (A)
Affects Version/s: 4.0.12
Fix Version/s: None

Type: Incident report Priority: Trivial
Reporter: Dmitry Assignee: Zabbix Development Team
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

OS: Centos 7.x
RDBMS: PostgreSQL 9.6
PHP: 7.1.24


 Description   

When I'm trying to create item for the host via API, I'm get  the following error:

{{{}}
  "jsonrpc": "2.0",
  "error": {
    "code": -32500,
    "message": "Application error.",
    "data": "SQL statement execution has failed \"INSERT INTO items (name,key_,hostid,type,value_type,interfaceid,delay,master_itemid,flags,query_fields,headers,itemid) VALUES ('{$JMX.1} Heap Memory','jmx.HeapMemoryUsage.used[\"\{$JMX.1.PORT}\"]','10930','0','0','1348','60s',NULL,'0','','','74461')\"."
  },
  "id": 1
}

I try to do it via bash script with POST curl request. I use the following curl cmd options:

curl -s -k -X POST -H "Content-Type:application/json" --data "$ZabbixAPIRequest" "$ZBX_API_URL"

where:

$ZabbixAPIRequest - variable, which contain the following JSON

{{{}}
    "jsonrpc": "2.0",
    "method": "item.create",
    "params": {
        "name": "{$JMX.1} Heap Memory",
        "key": "jmx.HeapMemoryUsage.used["\{$JMX.1.PORT}"]",
        "hostid": "10930",
        "type": "0",
        "value_type": "0",
        "interfaceid": "1348",
        "applications": [
            "8293"
        ],
        "delay": "60s"
    },
    "auth": "xxxx252yyyy68fxxxa3bzzz0xxxxx",
    "id": 1
}

$ZBX_API_URL - https://zabbix.....com/api_jsonrpc.php - URL to zabbix API on our server

I've successfully created host, application for newly created host, user's macroses , first, before attempt of create item.

 

 Comments   
Comment by Dmitry [ 2019 Sep 11 ]

Update.

Using json for request, such as post before cause the following SQL error:

2019-09-11 05:46:28.445 UTC >ОШИБКА:  нулевое значение в столбце "params" нарушает ограничение NOT NULL
< 2019-09-11 05:46:28.445 UTC >ПОДРОБНОСТИ:  Ошибочная строка содержит (74461, 0, , , 10930, {$JMX.1} Heap Memory, jmx.HeapMemoryUsage.used["\{$JMX.1.PORT}"], 60s, 90d, 365d, 0, 0, , , , 0, , , , , 0, , null, null, null, , 0, , , , , 0, 0, 1348, , null, 0, 30d, 0, 0, 0, , 0, , null, 3s, , , , 200, 1, 0, , , 0, 0, 0, , , , 0, 0, 0).

 

In other words, API when it generates SQL request, substitutes on "null" instead of "empty string" for undefined clearly params.
But apparently table scheme assumes, that some fields can't have "null" value.

 

Comment by Alexander Vladishev [ 2019 Sep 24 ]

PostgreSQL DB schema contain default value for "params" field. Perhaps your database schema is different. Please check.

CREATE TABLE items (
        itemid                   bigint                                    NOT NULL,
        type                     integer         DEFAULT '0'               NOT NULL,
        snmp_community           varchar(64)     DEFAULT ''                NOT NULL,
        snmp_oid                 varchar(512)    DEFAULT ''                NOT NULL,
        hostid                   bigint                                    NOT NULL,
        name                     varchar(255)    DEFAULT ''                NOT NULL,
        key_                     varchar(255)    DEFAULT ''                NOT NULL,
        ...
        params                   text            DEFAULT ''                NOT NULL,    <-- Default value for this field is empty string
        ...
        allow_traps              integer         DEFAULT '0'               NOT NULL,
        PRIMARY KEY (itemid)
);
Generated at Mon Jun 01 19:42:17 EEST 2026 using Jira 10.3.18#10030018-sha1:5642e4ad348b6c2a83ebdba689d04763a2393cab.