Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-12078

Creating or updating an action through the API fails with SQL error

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Open
    • Priority: Trivial
    • Resolution: Unresolved
    • Affects Version/s: 3.2.4
    • Fix Version/s: None
    • Component/s: API (A)
    • Labels:
    • Environment:
      CentOS 7.3.1611 on Amazon EC2 (t2.micro)

      Description

      When trying to create or update an action through the API, an error is returned (see below). Upon trying to re-enter the same query, the database returns a more detailed error:

      ERROR:  insert or update on table "opmessage" violates foreign key constraint "c_opmessage_1"
      DETAIL:  Key (operationid)=(14) is not present in table "operations".
      

      I suspect that the API-call results in zabbix trying to insert first into the 'opmessage'-table, and later into the 'operations'-table instead of the other way around.

      API call for action.create:

      {
          "params":{
              "operations":[
                  {
                      "operationtype":1,
                      "opcommand":{
                          "command":"echo 'hello world'",
                          "type":0,
                          "execute_on":0
                      },
                      "opcommand_hst":[
                          {
                              "hostid":0
                          }
                      ]
                  },
                  {
                      "operationtype":0,
                      "opmessage":{
                          "default_msg":1,
                          "mediatypeid":4,
                          "message":"Trigger: {TRIGGER.NAME}\nTrigger status: {TRIGGER.STATUS}\nTrigger severity: {TRIGGER.SEVERITY}\nTrigger URL: {TRIGGER.URL}\n\nItem values:\n\n1. {ITEM.NAME1} ({HOST.NAME1}:{ITEM.KEY1}): {ITEM.VALUE1}\n2. {ITEM.NAME2} ({HOST.NAME2}:{ITEM.KEY2}): {ITEM.VALUE2}\n3. {ITEM.NAME3} ({HOST.NAME3}:{ITEM.KEY3}): {ITEM.VALUE3}\n\nOriginal event ID: {EVENT.ID}\n",
                          "subject":"{TRIGGER.STATUS}: {TRIGGER.NAME}"
                      },
                      "opmessage_usr":[
                          {
                              "userid":1
                          }
                      ]
                  }
              ],
              "def_shortdata":"{TRIGGER.STATUS}: {HOST.NAME1} - {TRIGGER.NAME}",
              "name":"somename",
              "esc_period":3600,
              "def_longdata":"Trigger: {TRIGGER.NAME}\nTrigger status: {TRIGGER.STATUS}\nTrigger severity: {TRIGGER.SEVERITY}\nTrigger URL: {TRIGGER.URL}\n\nThis is what caused the alarm:\n\n{ITEM.NAME1} ({HOST.NAME1}:{ITEM.KEY1}): {ITEM.VALUE1}\n{ITEM.NAME2} ({HOST.NAME2}:{ITEM.KEY2}): {ITEM.VALUE2}\n\nOriginal event ID: {EVENT.ID}\n",
              "filter":{
                  "conditions":[
                      {
                          "conditiontype":16,
                          "operator":7,
                          "value":""
                      },
                      {
                          "conditiontype":3,
                          "operator":2,
                          "value":"some_other_name"
                      }
                  ],
                  "evaltype":1
              },
              "eventsource":0
          },
          "jsonrpc":"2.0",
          "id":0,
          "auth":"secret_auth_token",
          "method":"action.create"
      }
      

      Zabbix return message:

      {'body': '{"jsonrpc":"2.0","error":{"code":-32500,"message":"Application error.","data":"SQL statement execution has failed \\"INSERT INTO opmessage (default_msg,mediatypeid,message,subject,operationid) VALUES (\'1\',\'4\',\'Trigger: {TRIGGER.NAME}\\nTrigger status: {TRIGGER.STATUS}\\nTrigger severity: {TRIGGER.SEVERITY}\\nTrigger URL: {TRIGGER.URL}\\n\\nItem values:\\n\\n1. {ITEM.NAME1} ({HOST.NAME1}:{ITEM.KEY1}): {ITEM.VALUE1}\\n2. {ITEM.NAME2} ({HOST.NAME2}:{ITEM.KEY2}): {ITEM.VALUE2}\\n3. {ITEM.NAME3} ({HOST.NAME3}:{ITEM.KEY3}): {ITEM.VALUE3}\\n\\nOriginal event ID: {EVENT.ID}\\n\',\'{TRIGGER.STATUS}: {TRIGGER.NAME}\',\'14\')\\"."},"id":0}', 'status': 200}
      

      Upon inspection, the 'operations'-table only has operations up to an ID of 10.

      Manually creating the same action through the webinterface works fine, but then calling action.update through the API gives the same kind of error:

      {
          "params":{
              "operations":[
                  {
                      "operationtype":1,
                      "opcommand":{
                          "command":"echo 'hello world'",
                          "type":0,
                          "execute_on":0
                      },
                      "opcommand_hst":[
                          {
                              "hostid":0
                          }
                      ]
                  },
                  {
                      "operationtype":0,
                      "opmessage":{
                          "default_msg":1,
                          "mediatypeid":4,
                          "message":"Trigger: {TRIGGER.NAME}\nTrigger status: {TRIGGER.STATUS}\nTrigger severity: {TRIGGER.SEVERITY}\nTrigger URL: {TRIGGER.URL}\n\nItem values:\n\n1. {ITEM.NAME1} ({HOST.NAME1}:{ITEM.KEY1}): {ITEM.VALUE1}\n2. {ITEM.NAME2} ({HOST.NAME2}:{ITEM.KEY2}): {ITEM.VALUE2}\n3. {ITEM.NAME3} ({HOST.NAME3}:{ITEM.KEY3}): {ITEM.VALUE3}\n\nOriginal event ID: {EVENT.ID}\n",
                          "subject":"{TRIGGER.STATUS}: {TRIGGER.NAME}"
                      },
                      "opmessage_usr":[
                          {
                              "userid":1
                          }
                      ]
                  }
              ],
              "def_shortdata":"{TRIGGER.STATUS}: {HOST.NAME1} - {TRIGGER.NAME}",
              "name":"somename",
              "esc_period":3600,
              "def_longdata":"Trigger: {TRIGGER.NAME}\nTrigger status: {TRIGGER.STATUS}\nTrigger severity: {TRIGGER.SEVERITY}\nTrigger URL: {TRIGGER.URL}\n\nThis is what caused the alarm:\n\n{ITEM.NAME1} ({HOST.NAME1}:{ITEM.KEY1}): {ITEM.VALUE1}\n{ITEM.NAME2} ({HOST.NAME2}:{ITEM.KEY2}): {ITEM.VALUE2}\n\nOriginal event ID: {EVENT.ID}\n",
              "filter":{
                  "conditions":[
                      {
                          "conditiontype":16,
                          "operator":7,
                          "value":""
                      },
                      {
                          "conditiontype":3,
                          "operator":2,
                          "value":"some_other_name"
                      }
                  ],
                  "evaltype":1
              },
              "actionid":"8"
          },
          "jsonrpc":"2.0",
          "id":0,
          "auth":"secret_auth_token",
          "method":"action.update"
      }
      

      Which gives the result:

      {'body': '{"jsonrpc":"2.0","error":{"code":-32500,"message":"Application error.","data":"SQL statement execution has failed \\"INSERT INTO opmessage (default_msg,mediatypeid,message,subject,operationid) VALUES (\'1\',\'4\',\'Trigger: {TRIGGER.NAME}\\nTrigger status: {TRIGGER.STATUS}\\nTrigger severity: {TRIGGER.SEVERITY}\\nTrigger URL: {TRIGGER.URL}\\n\\nItem values:\\n\\n1. {ITEM.NAME1} ({HOST.NAME1}:{ITEM.KEY1}): {ITEM.VALUE1}\\n2. {ITEM.NAME2} ({HOST.NAME2}:{ITEM.KEY2}): {ITEM.VALUE2}\\n3. {ITEM.NAME3} ({HOST.NAME3}:{ITEM.KEY3}): {ITEM.VALUE3}\\n\\nOriginal event ID: {EVENT.ID}\\n\',\'{TRIGGER.STATUS}: {TRIGGER.NAME}\',\'17\')\\"."},"id":0}', 'status': 200}
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              Neighbour Herbert Buurman
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: