Troubleshooting DBLE GUI Tool Compatibility and Using the General Log

Problem Background

When using some GUI tools to connect to DBLE, certain SQL statements that are not compatible with DBLE cause the GUI tool to behave abnormally and become unusable.

Typical troubleshooting steps:

Step 1: Identify which SQL statements the GUI tool sends to DBLE, usually by capturing packets with tcpdump, Wireshark, etc.

Step 2: Execute the captured SQLs one by one in a MySQL client to locate the problematic SQL.

Case Study 1

Login to phpMyAdmin (docker version 7.4.20) and the first screen does not show the database list.

DBLE version: 3.21.02.x

##docker方式搭建phpMyAdmin
## 拉取phpmyadmin镜像
$ docker pull phpmyadmin/phpmyadmin
##初始化phpmyadmin容器 且关联dble服务
$ docker run -d --name myadmin_aliyun -e PMA_HOST=xxx.mysql.rds.aliyuncs.com -e PMA_PORT=3xx6 -p 8081:80 phpmyadmin/phpmyadmin
##详解：
-d：以后台模式运行
--name myadmin：容器命名为 myadmin, 容器管理时用(启动/停止/重启/查看日志等)
-e PMA_HOST=xx.xxx.xx.xx：Dble服务器域名或IP地址
-e PMA_PORT=8066：Dble的8066端口
-p 8080:80：端口映射, 本地端口:容器端口, 访问: http://ip:8080
phpmyadmin/phpmyadmin：要初始化的镜像名

After accessing http://ip:8080 with DBLE’s 8066 user credentials, the interface still does not display the database list.

Investigation Step 1: Download tcpdump and Wireshark, capture the TCP traffic during the phpMyAdmin login, convert the capture to MySQL protocol in Wireshark, and manually extract each SQL from the Request Query view.

Investigation Step 2: Execute the collected SQLs one by one in a MySQL client; the problematic SQL is identified as DBLE returning an empty result set when phpMyAdmin queries the database list.

Problematic SQLs:

SELECT `SCHEMA_NAME` FROM `INFORMATION_SCHEMA`.`SCHEMATA`, (SELECT DB_first_level FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t ORDER BY DB_first_level ASC LIMIT 0, 100) t2 WHERE TRUE AND 1 = LOCATE(CONCAT(DB_first_level, '_'), CONCAT(SCHEMA_NAME, '_')) ORDER BY SCHEMA_NAME ASC; – query all databases

2021-08-06T15:33:28.350 9 Query SELECT COUNT(*) FROM ( SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE ) t; – query database count

Non‑developers may find this process difficult because it requires knowledge of TCP protocols and packet‑capture commands.

General Log

Enabling the general log records every SQL statement that reaches DBLE, eliminating the need for packet‑capture tools.

See the general log documentation: https://actiontech.github.io/dble-docs-cn/2.Function/2.27_general_log.html

Related Parameters

enableGeneralLog: 1 to enable, 0 to disable.

generalLogFile: Path where the general log file is stored.

generalLogFileSize: Size threshold (default 16 MB) that triggers log rotation.

generalLogQueueSize: Internal queue size, default 4096.

These parameters are configured in bootstrap.cnf and require a DBLE restart to take effect.

Related Commands

show @@general_log; – query the status and file path of the general log.

enable @@general_log; – enable the general log.

disable @@general_log; – disable the general log.

reload @@general_log_file='/tmp/dble-general/general/general.log'; – change the log file path.

These commands are executed on the DBLE management node and take effect immediately without a restart; the settings persist across restarts.

Version

The general log feature was introduced in DBLE 3.21.02; earlier versions do not support it.

Performance

Enabling the general log incurs a 3–5 % performance overhead; it is recommended to enable it temporarily for debugging and disable it afterward.

Output Type

DBLE’s general log only supports the File output mode, similar to MySQL.

Log Format

The log format matches MySQL’s general log format.

/FAKE_PATH/mysqld, Version: FAKE_VERSION. started with:
Tcp port: 3320  Unix socket: FAKE_SOCK
Time                 Id Command    Argument
2021-08-05T16:24:53.558     1 Query   select * from no_sharding_t1
2021-08-05T16:25:00.210     1 Query   desc tb_grandson1
2021-08-05T16:26:32.774     1 Query   desc sharding_2_t1
2021-08-05T16:26:37.990     1 Query   select * from sharding_2_t1
2021-08-05T16:26:54.862     1 Query   insert into sharding_2_t1 values(1,1,1,1,1)

Implementation Mechanism

DBLE’s general log uses an asynchronous disk‑write mechanism based on Log4j.

Implementation details:

Synchronous processing Wrap each SQL into a Log containing Time, Id, Command, Argument. Place the Log into the generalLog queue (blocks if the queue is full). Subsequent SQL processing continues.

Asynchronous processing Listener consumes the queue. If the Log size ≤ remaining Buffer space (default 4096), it is added to the Buffer; when the Buffer fills, logs are flushed to disk. If the Log size > remaining Buffer space, the Buffer and the current Log are written to disk in order. Disruptor (a ring buffer) is used; batch writes may occur. Log rotation occurs on daily date change or when the log file exceeds generalLogFileSize .