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.
- Use SSH to log in to the Console as root user.
- 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)
- 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
- Use SSH to log in to the Console as root user.
- Open an SSH session to the appliance experiencing the reference data index issue.
- Stop hostcontext on the appliance by using the command:
systemctl stop hostcontext
- To update the replication database, type the following command:
sed -i /reference_data_element_data/d /opt/qradar/conf/templates/replication.sql
- 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;"
- Restart hostcontext on the appliance by using the command:
systemctl start hostcontext
- Log in to the QRadar Console as an administrator.
- Click Admin tab.
- 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":"BU059","label":"IBM Software w\/o TPS"},"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)"}]
Was this topic helpful?
Document Information
Modified date:
02 November 2020
UID
ibm16332315