DBLE Troubleshooting: Solutions for GUI Tool Incompatibility and SQL Execution Issues

Author: Wen YunhanThe following content addresses DBLE troubleshooting, including various methods and techniques to identify and resolve issues.

Member of the DBLE development team at ActionSoft, primarily responsible for DBLE requirement development, troubleshooting, and community issue solving.

Source: Original Submission

*Produced by ActionSoft Open Source Community. Unauthorized use of original content is prohibited; please contact the editor for republishing and cite the source.

Issue Background

When using certain GUI tools to connect to DBLE, incompatibilities with some SQL statements in DBLE can cause the GUI tools to malfunction.

Usual troubleshooting steps:

• Step 1: Identify which SQL statements were issued to DBLE by the GUI tool; generally, use packet capture tools like tcpdump, Wireshark to obtain SQLs
• Step 2: Execute SQLs one by one in Mysql Client to locate the problematic SQL

Troubleshooting Case 1

Login to the first interface of phpMyAdmin, where the database list is not displayed

GUI Tool: phpMyAdmin 7.4.20 (using docker in this case)

DBLE Version: 3.21.02.x

Code Language: javascriptCopy

##Build phpMyAdmin using docker## Pull phpmyadmin image$ docker pull phpmyadmin/phpmyadmin##Initialize phpmyadmin container and associate with dble service$ docker run -d --name myadmin_aliyun -e PMA_HOST=xxx.mysql.rds.aliyuncs.com -e PMA_PORT=3xx6 -p 8081:80 phpmyadmin/phpmyadmin##Explanation:-d: Run in background mode--name myadmin: Name the container as myadmin, use for container management (start/stop/restart/check logs, etc.)-e PMA_HOST=xx.xxx.xx.xx: Domain or IP address of Dble server-e PMA_PORT=8066: Port 8066 of Dble-p 8080:80: Port mapping, local port:container port, access: http://ip:8080phpmyadmin/phpmyadmin: Name of the image to initialize

Visit http://ip:8080, log in using the 8066 user password of DBLE; after logging in, you will find the database list is not displayed as shown below:

Why is the database list not displayed?

Troubleshooting Step 1:

First, download tcpdump (for packet capture) and Wireshark (to view packets); then execute the tcpdump command to capture the TCP protocol transmission during the operation (after logging into phpmyadmin), generating a cap file. Place this file into Wireshark, convert it into Mysql protocol for filtering (as shown below) for easy reading, troubleshooting, and extracting all SQLs issued; (actually, each Request Query needs to be opened one by one and the SQL manually extracted)

Troubleshooting Step 2:

Execute the collected SQLs one by one in Mysql Client to locate the problematic SQL (Reason: when phpMyAdmin issues a query database list, DBLE returns an empty result set)

Specific problematic SQL:

1. 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
2. 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 count of databases

In step one, additional tools may be needed to assist in troubleshooting, potentially requiring some knowledge reserves (such as: TCP protocol, packet capture commands); thus, troubleshooting by non-developers can be quite frustrating.

general log

Enable the general log to record all SQL statements reaching DBLE. This way, you no longer need packet capture tools to obtain all SQLs (proceed directly to step two)

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

Relevant Parameters

• enableGeneralLog: toggle for the general log, 1-enable, 0-disable
• generalLogFile: storage path of the general log file
• generalLogFileSize: size to trigger file rotation, when exceeding 16MB, rotate general.log into yyy-MM/general-MM-dd-%d.log format file
• generalLogQueueSize: size used by the internal mechanism queue, defaults to 4096

The above parameters are configured in bootstrap.cnf, changes in this configuration require a restart to take effect.

Related Commands

1. show @@general_log; – Query general log toggle status and file path
2. enable @@general_log; – Enable general log
3. disable @@general_log; – Disable general log
4. reload @@general_log_file=’/tmp/dble-general/general/general.log’; – Modify the file path of the general log

The above commands are executable only on the management side and take effect immediately without needing to restart DBLE. Furthermore, the current settings will be retained during the next DBLE restart without loss.

Version

• Beginning with DBLE version 3.21.02, the general log was introduced; (previous versions do not support this)

Performance

• Enabling the general log results in a DBLE performance overhead of 3% to 5%; it is suggested to temporarily enable it for troubleshooting certain errors and disable it after debugging.

Output Type

• Compared to Mysql, the output type of DBLE’s general log is File mode only

Log Format

• Once enabled, the record format matches that of Mysql’s general log format

Code Language: javascriptCopy

/FAKE_PATH/mysqld, Version: FAKE_VERSION. started with:Tcp port: 3320  Unix socket: FAKE_SOCKTime                 Id Command    Argument2021-08-05T16:24:53.558     1 Query   select * from no_sharding_t12021-08-05T16:25:00.210     1 Query   desc tb_grandson12021-08-05T16:26:32.774     1 Query   desc sharding_2_t12021-08-05T16:26:37.990     1 Query   select * from sharding_2_t12021-08-05T16:26:54.862     1 Query   insert into sharding_2_t1 values(1,1,1,1,1)

Implementation Mechanism

Refer to log4j for the specific implementation of the asynchronous log-to-disk mechanism in DBLE’s general log

Specific Implementation

• Synchronous Processing
  • Package SQL into a Log; Log contains information such as Time, Id, Command, Argument
  • Place the Log in the generalLog queue; (if the queue is full, it will block here until there’s a free position)
  • Subsequent process handling of SQL
• Asynchronous Processing
  • Monitor and handle the queue
  • When the byte size of the current Log is less than or equal to the remaining space in the Buffer cache (default 4096), insert it into the Buffer cache; when the Buffer cache is full, trigger the log-to-disk process of Logs in the Buffer cache
  • When the byte size of the current Log exceeds the remaining space in the Buffer cache, logs of the Buffer cache and the current Log are sequentially logged to disk
  • Using Disruptor (the generalLog queue is effectively a circular queue), calculate if the current entry is a batch process, if so, logs in the Buffer cache and the current Log are sequentially logged to disk
  • Rotation conditions: change of date (daily) || file size of general.log > generalLogFileSize