How Tencent’s TMySQLParse Automates MySQL SQL Auditing and Cuts DBA Workload

1. Background

Tencent Game's DB change process required DBA manual review of SQL scripts submitted through the Game Cloud Storage (GCS) platform, leading to frequent syntax‑error failures (3.3% failure rate) and costly delays.

Analysis of a year’s change orders revealed the need for an automated SQL audit tool, resulting in the creation of TMySQLParse.

2. Implementation Principles

TMySQLParse is built to be fully compatible with MySQL input and syntax.

The tool modifies the MySQL client source, reusing its parsing modules while removing server‑side dependencies.

2.1 Input Module

The input module adapts the MySQL client initialization flow, culminating in the core function read_and_execute(), which is replaced by read_and_sqlparse() to extract and parse SQL statements.

Key client functions involved include MY_INIT(), Isatty(), load_defaults(), get_options(), batch_readline_init(), mysql_server_init(), init_alloc_root(), sql_connect(), read_and_execute(), and mysql_end(). The processing loop reads each line via batch_readline() or my_cgets() depending on isatty().

2.2 Syntax Analysis Module

MySQL’s grammar is defined with yacc; the tool leverages the existing parse_sql() function, which builds a syntax tree stored in a LEX structure.

By reusing MySQL’s yacc rules, TMySQLParse achieves:

Full MySQL syntax compatibility

No need to construct a separate LEX structure, while still exposing syntax‑feature information

3. Usage Guide

TMySQLParse is an independent component based on MySQL 5.5.24 that checks SQL syntax correctness and extracts features such as statement type, database, table, and index information.

Key capabilities:

Accepts terminal or file input (supports delimiters and comments)

Supports multiple MySQL versions’ reserved words

Identifies high‑risk statements (e.g., DROP, DELETE without WHERE, ALTER without safety checks)

3.1 Parameters

Common command‑line options include:

./tmysqlparse -f result.xml test -v "5.1" < input.sql

- -f specifies output XML file - -v sets MySQL version for reserved‑word compatibility - -V/--version shows tool version - --help displays help - Providing a database name (e.g., test) runs analysis against that schema

3.2 Input/Output

Two input modes are supported: interactive terminal and file redirection ( ./tmysqlparse < xxx.sql).

Output is XML containing sections such as <result>, <syntax_failed>, <failed_info>, <risk_warnings>, and <warning_info>, each detailing errors, line numbers, and warning types (e.g., STMT_DROP_DB, STMT_DELETE without WHERE).

3.3 Practical Example

When feeding a series of statements (including a malformed delete * from t1 and an alter table with an extra add), TMySQLParse reports two syntax errors and flags missing indexes as high‑risk warnings.

4. Real‑World Integration

TMySQLParse has been integrated into Tencent Game’s GCS platform. After deployment, the platform automatically highlights syntax errors and high‑risk statements, matching MySQL’s native error messages and significantly reducing DBA manual review effort.

The integration screenshot shows detected syntax errors and high‑risk alerts directly within the GCS UI.