IBM Support

IJ25798: Deploys changes can fail due to a reference data element index issue between appliances

Troubleshooting


Problem

As described in APAR IJ25798, deploy changes can fail to complete when an inconsistency exists between the reference_data_element_data1 index on the QRadar Console and managed hosts in the deployment. This technical note provides further details to the workaround administrators can implement to resolve index errors related to a deploy changes.

Symptom

Similar messages might be visible in /var/log/qradar.error when the issue occurs:
 
[hostcontext.hostcontext] [Thread-68701] ComponentOutput: [ERROR] [NOT:0000003000]
[127.0.0.1/- -] [-/- -]ErrorStreamreplication: 
psql:/store/replication/tx0000000000000302764.sql:220939:
ERROR:  index row size 2928 exceeds maximum 2712 for index
"reference_data_element_data1"
[hostcontext.hostcontext] [Thread-68701] ComponentOutput:[ERROR] [NOT:0000003000]
[127.0.0.1/- -] [-/- -]ErrorStreamreplication: HINT:  Values larger than 1/3 of a buffer page 
cannot be indexed.
[hostcontext.hostcontext] [Thread-68701] ComponentOutput:[ERROR] [NOT:0000003000]
[127.0.0.1/- -] [-/- -]ErrorStreamreplication: Consider a function index of an MD5 hash of the
value, or use full text indexing.
[hostcontext.hostcontext] [Thread-68701] ComponentOutput:[ERROR] [NOT:0000003000]
[127.0.0.1/- -] [-/- -]ErrorStreamreplication: CONTEXT:  SQL statement "INSERT INTO 
public.reference_data_element SELECT * FROM rep.public_reference_data_element"
[hostcontext.hostcontext] [Thread-68701] ComponentOutput:[ERROR] [NOT:0000003000]
[127.0.0.1/- -] [-/- -]ErrorStreamreplication: 
PL/pgSQL function replicate_restore_dump(text,text) line 24 at EXECUTE {hostname}-
primary replication[197954]: Could not apply /store/replication/tx0000000000000302764.sql.

Diagnosing The Problem

Administrators can use the following procedure to identify each appliance that fails to deploy due to a reference data index issue.
  1. Use SSH to log in to the Console as root user.
  2. To identify the appliances that fail to deploy due to an index issue, type:
     /opt/qradar/support/all_servers.sh "psql -U qradar -c '\d+ reference_data_element' | grep 'reference_data_element_data1'"
    
    For example, the output displays all appliances experiencing the deploy changes issue described in APAR IJ25798.
    # /opt/qradar/support/all_servers.sh "psql -U qradar -c '\d+ reference_data_element' | grep 'reference_data_element_data1'"
    
    192.168.0.84 -> 740APPhost.example.com
    Appliance Type: 4000    Product Version: 2020.3.0.20200716115107
     15:27:56 up 23 min,  0 users,  load average: 8.18, 8.31, 6.46
    ------------------------------------------------------------------------
        "reference_data_element_data1" btree (rdk_id, data)
  3. Record each appliance IP address with the issue.

Resolving The Problem

Before you begin
  • This procedure is intended for QRadar SIEM appliances and requires root access. QRadar on Cloud administrators must contact support for a possible workaround.
  • The workaround for this issue requires that services be stopped and a full deploy completed from the Console. Administrators ought to consider scheduling a maintenance window before performing the workaround described in this technical note.
Procedure
  1. Use SSH to log in to the Console as root user.
  2. Open an SSH session to the appliance experiencing the reference data index issue.
  3. Stop hostcontext on the appliance by using the command:
    systemctl stop hostcontext
  4.  To update the replication database, type the following command:
    sed -i /reference_data_element_data/d /opt/qradar/conf/templates/replication.sql
    Tip: You can copy the code snipped in this step to ensure you do not mistype the command.
  5. To drop the reference_data_element_data1 index, type:
    psql -U qradar -c "BEGIN; SET TRANSACTION READ WRITE; DROP INDEX IF EXISTS reference_data_element_data1; COMMIT;"
  6. Restart hostcontext on the appliance by using the command:
    systemctl start hostcontext
  7. Log in to the QRadar Console as an administrator.
  8. Click Admin tab.
  9. Click Advanced > Deploy Full Configuration.

    Results
    Wait for the deploy to complete. If you continue to experience issues with deploy changes, contact QRadar support for assistance.

Document Location

Worldwide

[{"Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU008","label":"Security"},"Product":{"code":"SSBQAC","label":"IBM Security QRadar SIEM"},"ARM Category":[{"code":"a8m0z000000cwtNAAQ","label":"Deployment"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Document Information

Modified date:
02 November 2020

UID

ibm16332315