ClickHouse Connection Troubleshooting
Ensuring a functional connection to ClickHouse, especially with fully operational statistics, requires the proper functioning of the mysqlreplicator and stats_proxy services.This document outlines troubleshooting steps to address common issues encountered during ClickHouse connections.
In addition it is crucial that all of the ports required for a remote ClickHouse connection are open.
Ports: 9000, 8123
To begin troubleshooting, verify the status of the essential services (mysqlreplicator and stats_proxy)
To verify the service status you can use the following queries:
ps fax | grep -i ‘mysqlreplicator’
ps fax | grep -i ‘stats_proxy’
If either of the services is not running, start them by using the following queries:
/opt/pbxware/sh/pbxware start mysqlreplicator
/opt/pbxware/sh/pbxware start stats_proxy
While further troubleshooting, prioritize checking service logs for insights into encountered issues.
Service logs can be found on the following paths:
MySQLreplicator logs:
tail -n500 /opt/pbxware/pw/var/log/mysqlreplicator/mysqlreplicator.log
Stats_Proxy logs:
tail -n500 /opt/pbxware/pw/var/log/stats_proxy/stats_proxy.log
During remote ClickHouse connections, common issues may arise. Below you can find the most common issues which may be encountered together with the necessary steps on how to resolve them.
ClickHouse Upgrade
In PBXware Version 6 deployments, ClickHouse operates on Version 18.16.1 which is not compatible for Omni Channel statistics.
With the release of PBXware Version 7, enhancements and additional functionalities have been introduced.
PBXware Version 7 introduces advanced Omni statistics, leveraging features exclusive to ClickHouse Version 22.3.12.19. Therefore, upgrading ClickHouse to this specified version becomes imperative to ensure compatibility and optimal performance.
The upgrade to ClickHouse Version 22.3.12.19 is seamlessly integrated into the PBXware Version 7 upgrade process. As part of upgrading to PBXware 7, ClickHouse will be automatically updated to the required version.
Dedicated Clickhouse instance upgrades are provided at no additional charge.
Common ClickHouse Issues and Resolutions
Adding New User/Password to ClickHouse
• Issue: Passwords containing special characters (!&%"#$, etc.) may cause problems as some of the characters are escapes and may trigger a different action while the password is added.
• Resolution: Ensure passwords only contain letters and numbers, up to a maximum of 32 characters.
ClickHouse Tables Not Being Created
• Issue: When establishing a remote ClickHouse connection a common issue which may occur is that tables do not get created automatically.
Log output when this issue occurs will be similar to this:
FATAL REPLICATOR: Failed to get uid for ext_log err=Failed to get max uid: code: 60, message: Table default.ext_log doesn't exist.
• Resolution: Manually create tables using the following queries:
Access ClickHouse database:
/opt/pbxware/sh/clickhouse-client
Create erg_log table:
CREATE TABLE default.erg_log (
eventdate Date MATERIALIZED toDate(time),
uid UInt64,
date_time UInt32,
callid String,
channelid String,
queue_name String,
type String,
event String,
param1 String,
param2 String,
param3 String,
entpos Int32,
expos Int32,
htime Int32,
ttime Int32,
time DateTime,
tenant String
) ENGINE = MergeTree() PARTITION BY toYYYYMM(eventdate) ORDER BY (uid, date_time) SETTINGS index_granularity = 8192;Create queue_log table:
CREATE TABLE default.queue_log (
eventdate Date MATERIALIZED toDate(time),
uid UInt64,
date_time UInt32,
callid String,
channelid String,
queue_name String,
type String,
event String,
param1 String,
param2 String,
param3 String,
entpos Int32,
expos Int32,
htime Int32,
ttime Int32,
time DateTime,
campaign Int32,
rtime Int32,
strategy String,
parentid String,
did String
) ENGINE = MergeTree() PARTITION BY toYYYYMM(eventdate) ORDER BY (uid, date_time) SETTINGS index_granularity = 8192;Create ext_log table:
CREATE TABLE default.ext_log (
eventdate Date MATERIALIZED toDate(finished_at),
uid UInt64,
ext String,
tenant_code String,
linked_id String,
remote_number String,
remote_name String,
date_time UInt32,
hold_time UInt32,
duration UInt32,
type Int8,
conference UInt8,
answered UInt8,
finished_at DateTime
) ENGINE = MergeTree() PARTITION BY toYYYYMM(eventdate) ORDER BY (uid, date_time) SETTINGS index_granularity = 8192;Extension Statistics Not Being Populated
• Issue: After establishing a ClickHouse connection an issue may present itself where extension statistics are not available despite all of the services running properly. This issue is caused due to the ext_log within ClickHouse not being populated.
• Resolution: In order to resolve this issue it is required to add statsdb_enable to pbxware.config using the following query:
INSERT INTO pbxware.config SET name = 'statsdb_enable', value = 1;
Once done perform a PBXware reload:
/opt/pbxware/sh/pbxware reload
ClickHouse Populating Excessive Space
• Issue: Query logging within system.query_log is by default enabled in ClickHouse version 22.3.12.19 which causes an excessive amount of queries to be logged and increases the storage usage populated by ClickHouse itself.
•
• Resolution: It is required to disable query logging within system.query_log through configuration files (users.xml).
In order to make the necessary adjustments follow these steps:
Edit users.xml configuration file with query:
nano /opt/pbxware/pw/etc/clickhouse-server/users.xml
Within the configuration file under Default Settings section add the following line to the configuration:
<log_queries>0</log_queries>
After adjusting the configuration file it is required to restart ClickHouse.
/opt/pbxware/sh/pbxware stop clickhouse-server
/opt/pbxware/sh/pbxware start clickhouse-server
Adding Multiple IPs to "Allow IP Addresses"
• Issue: When configuring an instance to “Act As Server” adding multiple Ips to the “Allow IP Addresses” field my be prevented due to a character limitation for the specified field.
• Resolution: Increase maxlength value in HTML attribute value for "Allow IP Addresses" field.
Right click the “Allow IP Addresses” field in your browser and select “Inspect”
Within the HTML configuration increase the “value maxlength” which by default will be 50.
This knowledgebase document provides a comprehensive guide for addressing common ClickHouse issues and their resolutions. For further assistance, please contact our technical support team.