文档编写：从代码层面详解mysql-test测试框架的原理及使用方法

[willhan123]

[vhengeee]

南昌航空大学-郑雅斌-认领成功

[7yi65]

广东技术师范大学-林钡镕-认领成功

[Could-Yun]

中国石油大学（华东）-云靖媛-认领成功

[7yi65]

广东技术师范大学-林钡镕-认领成功

MySQL-Test 框架原理及使用方法

1. 核心原理：它是如何工作的？

想象成一个自动化的机器人测试员，它的工作流程基于一个简单的“刺激-响应”模型：

1. 给予刺激 (Input)：机器人执行一个SQL语句（如 SELECT * FROM users;）。
2. 观察响应 (Output)：机器人记录下数据库返回的所有结果。
3. 比对结果 (Verification)：机器人将“实际结果”与预先定义好的“预期结果”文件进行比对。
4. 给出判决 (Result)：如果两者完全一致，测试通过；只要有任何不同，测试失败。

整个流程的参与组件与数据流如下图所示：

flowchart TD
    A[Test Case File<br>.test] --> B{mysqltest<br>指令解析器}
    C[Expected Result<br>.result] --> B

    B --> D[MySQL Server]
    D --> E[Actual Result<br>.reject]

    E --> F{Diff Comparator}
    C --> F

    F -- Match --> G[✅ Pass]
    F -- Differ --> H[❌ Fail<br>Generate .diff]

Loading

2. 核心组件详解 (文件层面)

一个测试用例主要由以下文件组成，它们协同工作：

文件类型	文件名示例	作用	内容示例
测试用例	my_test_case.test	包含要执行的SQL或命令	SELECT 1+1;
预期结果	my_test_case.result	定义应该返回的结果	1+1\n2
拒绝结果	my_test_case.reject	实际返回的结果（运行时生成）	1+1\n2
差异对比	my_test_case.diff	预期与实际的差异（失败时生成）	(空，因为测试通过)

3. 核心命令详解 (代码层面)

测试用例文件 (.test) 中除了写纯SQL，还可以使用 mysqltest 解释器的特殊命令来控制流程：

命令类别	命令	说明	代码示例
基本命令	source	引入另一个脚本文件	source include/have_ssl.inc;
连接控制	connect	创建新连接	connect (con1, localhost, root,,);
	disconnect	断开连接	disconnect con1;
	connection	切换当前连接	connection default;
流程控制	if	条件判断	if ($have_ssl) { ... }
	let	定义变量	let $datadir = SELECT @@datadir``;
	exec	执行操作系统命令	exec cp $datadir/file.txt /tmp/;
结果控制	echo	输出信息到结果	echo This is a log message;
	exit	停止执行	exit;
--enable_<feature>	enable_query_log	控制是否记录SQL语句	enable_query_log;
	enable_result_log	控制是否记录SQL结果	enable_result_log;

4. 使用方法：运行测试

进入编译好的 mysql-test 目录，使用 mtr 脚本。

# 进入测试目录
cd /TXSQL/mysql-test

# 1. 运行一个最简单的测试
./mtr simple

# 2. 运行一个测试套件（多个相关测试）
./mtr main

# 3. 运行单个特定测试（例如测试基本选择的测试）
./mtr main.select

# 4. 带参数运行：并行测试、重试、记录日志等
./mtr --force --parallel=4 --retry=1 main

5. 编写测试

1. 创建测试文件：在 mysql-test/suite/main/ 下创建 my_cool_test.test。
2. 写入内容：

   --source include/have_innodb.inc
   
   --echo #
   --echo # 测试目的：验证我的新功能XXX
   --echo #
   
   CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(255)) ENGINE=InnoDB;
   INSERT INTO test_table VALUES (1, 'OpenTenBase'), (2, 'TXSQL');
   
   --disable_query_log
   --let $expected_count = 2
   --enable_query_log
   
   SELECT COUNT(*) FROM test_table;
   --echo # 上面语句的预期结果应该是 $expected_count
   
   DROP TABLE test_table;

3. 生成预期结果：首次运行，让它自动生成 .result 文件。

   ./mtr --record main.my_cool_test

4. 正式运行测试：

   ./mtr main.my_cool_test

[vhengeee]

MySQL-Test（MTR）测试框架(For the convenience of foreign developers to read, I have also written an English version of the document)

目录

• 1. 框架架构与核心组件
• 2. 核心代码实现原理
• 3. 测试脚本语言详解
• 4. 配置文件系统
• 5. 测试执行流程
• 6. 结果校验机制
• 7. 高级特性与扩展
• 8. 实战案例分析
• 9. 故障排查与调优
• 10. 最佳实践指南

---

1. 框架架构与核心组件

1.1 整体架构

- 角色分工：
  - 驱动：mysql-test-run.pl（简称 mtr）负责选择用例、启动/重启 mysqld（必要时多实例/Router）、组织执行与汇总报告。
  - 引擎：mysqltest 解释“测试脚本语言”，与 Server 建立会话、执行 SQL 与脚本命令、产生并规范化输出。
- 基本配对：每个用例由 t/ 下的 .test 与 r/ 下的 .result 同名配对；执行后把实际输出同 .result 对比，不一致则失败，并将实际输出落盘为 .reject。

MySQL-Test框架采用双层架构设计：

┌─────────────────────────────────────────────────────────┐
│                    MTR驱动层                            │
│  mysql-test-run.pl (mtr)                               │
│  ┌─────────────┐ ┌─────────────┐ ┌─────────────┐        │
│  │ 用例解析器   │ │ 环境管理器   │ │ 结果收集器   │        │
│  └─────────────┘ └─────────────┘ └─────────────┘        │
└─────────────────────────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────┐
│                   执行引擎层                             │
│  mysqltest                                             │
│  ┌─────────────┐ ┌─────────────┐ ┌─────────────┐        │
│  │ 脚本解析器   │ │ SQL执行器   │ │ 输出处理器   │        │
│  └─────────────┘ └─────────────┘ └─────────────┘        │
└─────────────────────────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────┐
│                   MySQL服务层                           │
│  mysqld instances                                      │
└─────────────────────────────────────────────────────────┘

1.2 目录与文件

• mysql-test/ 目录包含：
  • t/：测试输入 .test；r/：期望输出 .result；
  • var/log/：运行期日志与 .reject；
  • include/：共享片段（如 have_innodb.inc）；
  • suite/：按套件组织（如 suite/main/t, suite/main/r）。
• per-test 辅助文件：
  • test_name-master.opt：主实例 mysqld 启动参数。
  • test_name-slave.opt：从实例 mysqld 启动参数。
  • test_name-client.opt：mysqltest 客户端参数。
  • test_name.cnf：多实例/客户端/[ENV] 环境变量集中配置。

1.3 核心组件职责

1.3.1 MTR驱动层 (mysql-test-run.pl)

主要职责：

• 测试用例发现与解析
• MySQL实例生命周期管理
• 测试环境初始化与清理
• 并行执行调度
• 结果汇总与报告生成

核心代码逻辑：

# 伪代码示例
sub run_test_case {
    my ($test_case) = @_;
    
    # 1. 解析测试配置
    my $config = parse_test_config($test_case);
    
    # 2. 启动MySQL实例
    my $mysqld = start_mysqld($config);
    
    # 3. 执行测试
    my $result = execute_mysqltest($test_case, $mysqld);
    
    # 4. 收集结果
    return collect_results($result);
}

1.3.2 执行引擎层 (mysqltest)

主要职责：

• 测试脚本语言解析
• 数据库连接管理
• SQL语句执行
• 输出格式化与规范化
• 错误处理与状态管理

核心执行流程：

// 伪代码示例
int main_execute_test() {
    // 1. 初始化连接
    MYSQL *mysql = mysql_init(NULL);
    
    // 2. 解析测试脚本
    while (read_command(&command)) {
        switch (command.type) {
            case Q_QUERY:
                execute_query(mysql, command.query);
                break;
            case Q_ECHO:
                output_echo(command.text);
                break;
            case Q_ERROR:
                expect_error(command.error_code);
                break;
        }
    }
    
    // 3. 清理资源
    mysql_close(mysql);
    return exit_code;
}

---

2. 核心代码实现原理

2.1 测试用例发现机制

目录结构约定：

mysql-test/
├── t/                    # 测试脚本目录
│   ├── basic.test
│   ├── innodb.test
│   └── replication.test
├── r/                    # 期望结果目录
│   ├── basic.result
│   ├── innodb.result
│   └── replication.result
├── include/              # 公共包含文件
│   ├── have_innodb.inc
│   └── default_my.cnf
├── suite/                # 测试套件
│   ├── main/
│   ├── binlog/
│   └── rpl/
└── var/                  # 运行时目录
    ├── log/
    └── tmp/

用例发现算法：

sub collect_test_cases {
    my @test_cases = ();
    
    # 扫描测试目录
    find(sub {
        if (/\.test$/) {
            my $test_name = $File::Find::name;
            $test_name =~ s/\.test$//;
            
            # 检查对应的result文件
            my $result_file = "$test_name.result";
            if (-f $result_file) {
                push @test_cases, {
                    name => $test_name,
                    test_file => $File::Find::name,
                    result_file => $result_file
                };
            }
        }
    }, $test_dir);
    
    return @test_cases;
}

2.2 MySQL实例管理

实例启动流程：

sub start_mysqld {
    my ($config) = @_;
    
    # 1. 准备数据目录
    prepare_datadir($config->{datadir});
    
    # 2. 生成配置文件
    my $my_cnf = generate_my_cnf($config);
    
    # 3. 初始化数据库
    if ($config->{initialize}) {
        system("$mysqld --initialize-insecure --datadir=$config->{datadir}");
    }
    
    # 4. 启动服务
    my $pid = fork();
    if ($pid == 0) {
        exec("$mysqld --defaults-file=$my_cnf");
    }
    
    # 5. 等待服务就绪
    wait_for_server_start($config->{port});
    
    return $pid;
}

2.3 测试脚本解析器

命令解析状态机：

typedef enum {
    PARSE_COMMAND,
    PARSE_QUERY,
    PARSE_DELIMITER,
    PARSE_COMMENT
} parser_state_t;

int parse_test_line(char *line, command_t *cmd) {
    static parser_state_t state = PARSE_COMMAND;
    
    switch (state) {
        case PARSE_COMMAND:
            if (strncmp(line, "--", 2) == 0) {
                return parse_command(line + 2, cmd);
            } else {
                state = PARSE_QUERY;
                return parse_query_start(line, cmd);
            }
            break;
            
        case PARSE_QUERY:
            return parse_query_continue(line, cmd);
            
        default:
            return -1;
    }
}

---

3. 测试脚本语言详解

3.1 测试脚本编写规范

三段式结构：setup / act / assert（通过 .result 对比体现），全程用 --echo 打标签。
输出可重复：避免时间戳/随机数/临时路径；必要时用脚本语言的替换/规范化指令处理。
原子独立：每个 .test 自己创建/清理对象，不依赖执行顺序。
小而专注：一个用例聚焦一个功能或一组紧密相关断言。
可诊断：结果中包含必要上下文（列名、关键字段、阶段标签）。
参数显式化：与行为相关的参数通过 .opt/.cnf 管理，便于审计与复现。

3.2 基本语法结构

命令分类：

命令类型	语法	功能描述
控制命令	--command	控制测试执行流程
SQL语句	SELECT ...;	执行SQL查询
注释	# comment	代码注释
输出控制	--echo text	输出文本到结果

3.3 核心命令详解

3.3.1 连接管理命令

-- 建立新连接
--connect (connection_name, host, user, password, database, port, socket)
--connect (conn1, localhost, root, , test, $MASTER_MYPORT, )

-- 切换连接
--connection connection_name
--connection conn1

-- 断开连接
--disconnect connection_name
--disconnect conn1

实现原理：

static MYSQL* connections[MAX_CONNECTIONS];
static int current_connection = 0;

int handle_connect_command(char *params) {
    char name[64], host[64], user[64], password[64];
    int port;
    
    // 解析连接参数
    parse_connect_params(params, name, host, user, password, &port);
    
    // 建立连接
    MYSQL *mysql = mysql_init(NULL);
    if (!mysql_real_connect(mysql, host, user, password, NULL, port, NULL, 0)) {
        return handle_connection_error(mysql);
    }
    
    // 保存连接
    int conn_id = find_free_connection_slot();
    connections[conn_id] = mysql;
    register_connection_name(name, conn_id);
    
    return 0;
}

3.3.2 错误处理命令

-- 期望特定错误
--error ER_DUP_ENTRY
INSERT INTO t VALUES (1, 'duplicate');

-- 期望多个可能的错误
--error ER_DUP_ENTRY, ER_DUP_KEY
INSERT INTO t VALUES (1, 'duplicate');

-- 期望成功（默认行为）
--error 0
SELECT * FROM t;

错误匹配机制：

static int expected_errors[MAX_EXPECTED_ERRORS];
static int num_expected_errors = 0;

int execute_query_with_error_check(MYSQL *mysql, const char *query) {
    int result = mysql_real_query(mysql, query, strlen(query));
    int actual_error = mysql_errno(mysql);
    
    if (num_expected_errors > 0) {
        // 检查是否匹配期望的错误
        for (int i = 0; i < num_expected_errors; i++) {
            if (expected_errors[i] == actual_error) {
                reset_expected_errors();
                return 0; // 匹配期望错误
            }
        }
        
        // 错误不匹配
        fprintf(stderr, "Expected error %d, got %d\n", 
                expected_errors[0], actual_error);
        return 1;
    }
    
    // 没有期望错误，检查是否成功
    if (result != 0) {
        fprintf(stderr, "Unexpected error: %s\n", mysql_error(mysql));
        return 1;
    }
    
    return 0;
}

3.3.3 输出控制命令

-- 输出文本
--echo ### Test Case: Basic SELECT

-- 禁用查询输出
--disable_query_log
SET @var = 1;
--enable_query_log

-- 禁用结果输出
--disable_result_log
SELECT * FROM large_table;
--enable_result_log

-- 输出变量值
--echo Variable value: $MYSQL_VERSION

3.4 高级脚本特性

3.4.1 条件执行

-- 检查功能支持
--source include/have_innodb.inc
--source include/have_ssl.inc

-- 平台相关跳过
--source include/not_windows.inc

-- 版本检查
--source include/mysql_version_ge_8_0.inc

条件检查实现：

# have_innodb.inc 内容示例
--disable_query_log
--disable_result_log
--disable_warnings

let $have_innodb= `SELECT COUNT(*) FROM INFORMATION_SCHEMA.ENGINES 
                   WHERE ENGINE='InnoDB' AND SUPPORT IN ('YES', 'DEFAULT')`;

if (!$have_innodb) {
  --skip Test requires InnoDB engine
}

--enable_warnings
--enable_result_log
--enable_query_log

3.4.2 循环与变量

-- 变量定义
let $counter = 10;
let $table_name = test_table;

-- 循环执行
while ($counter > 0) {
  eval INSERT INTO $table_name VALUES ($counter, 'data_$counter');
  dec $counter;
}

-- 条件判断
if ($mysql_version >= 80000) {
  SELECT 'MySQL 8.0 or later';
}
if ($mysql_version < 80000) {
  SELECT 'MySQL version before 8.0';
}

---

4. 配置文件系统

4.1 配置文件层次结构

配置优先级（从高到低）：
1. test_name.cnf          # 测试专用配置
2. test_name-master.opt   # 主实例启动参数
3. test_name-slave.opt    # 从实例启动参数
4. test_name-client.opt   # 客户端参数
5. suite.cnf              # 套件级配置
6. default_my.cnf         # 默认配置

4.2 配置文件格式详解

4.2.1 .cnf 配置文件

# test_replication.cnf
!include include/default_my.cnf

[mysqld.1]
# 主服务器配置
server-id=1
log-bin=master-bin
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON

[mysqld.2]
# 从服务器配置
server-id=2
relay-log=slave-relay
read-only=ON
gtid-mode=ON
enforce-gtid-consistency=ON

[mysqltest]
# 客户端配置
ps-protocol
view-protocol

[ENV]
# 环境变量
MASTER_PORT=@mysqld.1.port
SLAVE_PORT=@mysqld.2.port
MASTER_SOCKET=@mysqld.1.socket
SLAVE_SOCKET=@mysqld.2.socket

4.2.2 .opt 参数文件

# test_innodb-master.opt
--innodb-buffer-pool-size=128M
--innodb-log-file-size=64M
--innodb-flush-log-at-trx-commit=1
--innodb-file-per-table=ON
--sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO

4.3 动态配置注入

端口分配机制：

sub allocate_ports {
    my ($num_instances) = @_;
    my @ports = ();
    
    for my $i (1..$num_instances) {
        my $port = find_free_port();
        push @ports, $port;
        
        # 注册端口变量
        $ENV{"MYSQLD_${i}_PORT"} = $port;
    }
    
    return @ports;
}

sub substitute_variables {
    my ($config_content) = @_;
    
    # 替换端口变量
    $config_content =~ s/\@mysqld\.(\d+)\.port/$ENV{"MYSQLD_${1}_PORT"}/g;
    
    # 替换socket变量
    $config_content =~ s/\@mysqld\.(\d+)\.socket/$ENV{"MYSQLD_${1}_SOCKET"}/g;
    
    return $config_content;
}

---

5. 测试执行流程

5.1 完整执行时序图

MTR Driver          MySQL Instance       mysqltest           Test Files
    │                      │                  │                    │
    ├─ 1. Parse test case ─┤                  │                    │
    │                      │                  │                    │
    ├─ 2. Start mysqld ────┤                  │                    │
    │                      ├─ 2.1 Initialize │                    │
    │                      ├─ 2.2 Start      │                    │
    │                      ├─ 2.3 Ready      │                    │
    │                      │                  │                    │
    ├─ 3. Execute test ────┼──────────────────┤                    │
    │                      │                  ├─ 3.1 Read .test ──┤
    │                      │                  ├─ 3.2 Parse script │
    │                      ├─ 3.3 Execute SQL┤                    │
    │                      ├─ 3.4 Return res ┤                    │
    │                      │                  ├─ 3.5 Format output│
    │                      │                  │                    │
    ├─ 4. Compare results ┼──────────────────┤                    │
    │                      │                  ├─ 4.1 Read .result ┤
    │                      │                  ├─ 4.2 Compare      │
    │                      │                  ├─ 4.3 Generate diff│
    │                      │                  │                    │
    ├─ 5. Cleanup ─────────┤                  │                    │
    │                      ├─ 5.1 Stop       │                    │
    │                      ├─ 5.2 Cleanup    │                    │

5.2 核心执行算法

sub execute_test_suite {
    my (@test_cases) = @_;
    my @results = ();
    
    for my $test_case (@test_cases) {
        my $result = execute_single_test($test_case);
        push @results, $result;
        
        # 失败处理
        if ($result->{status} eq 'FAIL' && !$opt_force) {
            last;
        }
    }
    
    return generate_summary_report(@results);
}

sub execute_single_test {
    my ($test_case) = @_;
    
    eval {
        # 1. 环境准备
        my $env = prepare_test_environment($test_case);
        
        # 2. 启动MySQL
        my $mysqld = start_mysqld_for_test($test_case, $env);
        
        # 3. 执行测试
        my $output = run_mysqltest($test_case, $mysqld);
        
        # 4. 结果比较
        my $comparison = compare_with_expected($output, $test_case);
        
        # 5. 清理环境
        cleanup_test_environment($env, $mysqld);
        
        return {
            name => $test_case->{name},
            status => $comparison->{status},
            duration => $comparison->{duration},
            diff => $comparison->{diff}
        };
    };
    
    if ($@) {
        return {
            name => $test_case->{name},
            status => 'ERROR',
            error => $@
        };
    }
}

5.3 并行执行机制

sub execute_parallel {
    my ($test_cases, $parallel_count) = @_;
    
    my $pm = Parallel::ForkManager->new($parallel_count);
    my @results = ();
    
    $pm->run_on_finish(sub {
        my ($pid, $exit_code, $ident, $exit_signal, $core_dump, $result) = @_;
        push @results, $result;
    });
    
    for my $test_case (@$test_cases) {
        $pm->start and next;
        
        # 子进程执行测试
        my $result = execute_single_test($test_case);
        $pm->finish(0, $result);
    }
    
    $pm->wait_all_children;
    return @results;
}

---

6. 结果校验机制

6.1 输出规范化

规范化规则：

// 输出规范化函数
char* normalize_output(char* raw_output) {
    char* normalized = malloc(strlen(raw_output) * 2);
    char* dest = normalized;
    char* src = raw_output;
    
    while (*src) {
        switch (*src) {
            case '\t':
                // 制表符转换为固定空格
                *dest++ = ' ';
                break;
                
            case '\r':
                // 移除回车符
                break;
                
            default:
                *dest++ = *src;
        }
        src++;
    }
    
    *dest = '\0';
    
    // 移除行尾空白
    trim_trailing_whitespace(normalized);
    
    return normalized;
}

6.2 差异检测算法

typedef struct {
    int line_number;
    char* expected;
    char* actual;
    diff_type_t type; // MISSING, EXTRA, DIFFERENT
} diff_entry_t;

diff_result_t* compare_outputs(char* expected, char* actual) {
    diff_result_t* result = malloc(sizeof(diff_result_t));
    result->differences = NULL;
    result->diff_count = 0;
    
    char** expected_lines = split_lines(expected);
    char** actual_lines = split_lines(actual);
    
    int exp_count = count_lines(expected_lines);
    int act_count = count_lines(actual_lines);
    
    // 使用LCS算法进行差异检测
    int** lcs_matrix = compute_lcs_matrix(expected_lines, actual_lines, 
                                         exp_count, act_count);
    
    // 回溯生成差异列表
    generate_diff_list(result, expected_lines, actual_lines, 
                      lcs_matrix, exp_count, act_count);
    
    free_matrix(lcs_matrix);
    free_lines(expected_lines);
    free_lines(actual_lines);
    
    return result;
}

6.3 非确定性输出处理

常见非确定性模式：

-- 时间戳替换
--replace_regex /\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}/TIMESTAMP/

-- 线程ID替换
--replace_regex /thread_id=[0-9]+/thread_id=ID/

-- 文件路径替换
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR

-- 端口号替换
--replace_result $MASTER_MYPORT MASTER_PORT

替换引擎实现：

char* apply_replacements(char* input, replacement_rule_t* rules, int rule_count) {
    char* result = strdup(input);
    
    for (int i = 0; i < rule_count; i++) {
        replacement_rule_t* rule = &rules[i];
        
        switch (rule->type) {
            case REPLACE_REGEX:
                result = regex_replace(result, rule->pattern, rule->replacement);
                break;
                
            case REPLACE_STRING:
                result = string_replace(result, rule->pattern, rule->replacement);
                break;
                
            case REPLACE_COLUMN:
                result = column_replace(result, rule->column_index, rule->replacement);
                break;
        }
    }
    
    return result;
}

---

7. 高级特性与扩展

7.1 多实例测试

主从复制测试示例：

# rpl_basic.test
--source include/master-slave.inc

# 在主服务器上创建表
--connection master
CREATE TABLE t1 (id INT PRIMARY KEY, data VARCHAR(100));
INSERT INTO t1 VALUES (1, 'test data');

# 同步到从服务器
--sync_slave_with_master

# 在从服务器上验证数据
--connection slave
SELECT * FROM t1;

# 清理
--connection master
DROP TABLE t1;
--sync_slave_with_master

--source include/rpl_end.inc

多实例配置：

# rpl_basic.cnf
[mysqld.1]
server-id=1
log-bin=master-bin
binlog-format=ROW

[mysqld.2]
server-id=2
relay-log=slave-relay
read-only=ON

[ENV]
MASTER_MYPORT=@mysqld.1.port
SLAVE_MYPORT=@mysqld.2.port

7.2 性能测试集成

# performance_test.test
--echo ### Performance Test: Large INSERT

# 创建测试表
CREATE TABLE perf_test (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(1000),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 记录开始时间
let $start_time = `SELECT UNIX_TIMESTAMP()`;

# 执行大量插入
--disable_query_log
let $i = 10000;
while ($i > 0) {
    eval INSERT INTO perf_test (data) VALUES (REPEAT('x', 100));
    dec $i;
}
--enable_query_log

# 记录结束时间
let $end_time = `SELECT UNIX_TIMESTAMP()`;
let $duration = `SELECT $end_time - $start_time`;

--echo ### Duration: $duration seconds

# 验证结果
SELECT COUNT(*) FROM perf_test;

DROP TABLE perf_test;

7.3 插件测试框架

# plugin_test.test
--source include/have_plugin_auth.inc

# 安装插件
INSTALL PLUGIN test_plugin SONAME 'test_plugin.so';

# 测试插件功能
SELECT plugin_name, plugin_status 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE plugin_name = 'test_plugin';

# 使用插件功能
SELECT test_plugin_function('test_input');

# 卸载插件
UNINSTALL PLUGIN test_plugin;

---

8. 实战案例分析

8.1 事务隔离级别测试

# isolation_levels.test
--echo ### Transaction Isolation Level Test

# 创建测试表
CREATE TABLE isolation_test (
    id INT PRIMARY KEY,
    value INT
);

INSERT INTO isolation_test VALUES (1, 100);

# 测试READ COMMITTED
--echo ### Testing READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

--connect (conn1, localhost, root, , test, $MASTER_MYPORT, )
--connect (conn2, localhost, root, , test, $MASTER_MYPORT, )

# 连接1开始事务
--connection conn1
BEGIN;
UPDATE isolation_test SET value = 200 WHERE id = 1;

# 连接2读取数据（应该看到旧值）
--connection conn2
BEGIN;
SELECT value FROM isolation_test WHERE id = 1;

# 连接1提交
--connection conn1
COMMIT;

# 连接2再次读取（应该看到新值）
--connection conn2
SELECT value FROM isolation_test WHERE id = 1;
COMMIT;

# 清理
--disconnect conn1
--disconnect conn2
--connection default
DROP TABLE isolation_test;

8.2 死锁检测测试

# deadlock_detection.test
--echo ### Deadlock Detection Test

CREATE TABLE deadlock_test1 (id INT PRIMARY KEY, data VARCHAR(100));
CREATE TABLE deadlock_test2 (id INT PRIMARY KEY, data VARCHAR(100));

INSERT INTO deadlock_test1 VALUES (1, 'data1');
INSERT INTO deadlock_test2 VALUES (1, 'data2');

--connect (conn1, localhost, root, , test, $MASTER_MYPORT, )
--connect (conn2, localhost, root, , test, $MASTER_MYPORT, )

# 连接1：锁定表1，然后尝试锁定表2
--connection conn1
BEGIN;
UPDATE deadlock_test1 SET data = 'updated1' WHERE id = 1;

# 连接2：锁定表2，然后尝试锁定表1
--connection conn2
BEGIN;
UPDATE deadlock_test2 SET data = 'updated2' WHERE id = 1;

# 连接1尝试锁定表2（会等待）
--connection conn1
--send UPDATE deadlock_test2 SET data = 'conflict1' WHERE id = 1

# 连接2尝试锁定表1（会触发死锁）
--connection conn2
--error ER_LOCK_DEADLOCK
UPDATE deadlock_test1 SET data = 'conflict2' WHERE id = 1;
ROLLBACK;

# 连接1的操作应该成功
--connection conn1
--reap
COMMIT;

# 清理
--disconnect conn1
--disconnect conn2
--connection default
DROP TABLE deadlock_test1, deadlock_test2;

8.3 存储引擎特性测试

# innodb_features.test
--source include/have_innodb.inc

--echo ### InnoDB Foreign Key Test

CREATE TABLE parent (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    data VARCHAR(100),
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=InnoDB;

# 插入父记录
INSERT INTO parent VALUES (1, 'Parent 1');
INSERT INTO parent VALUES (2, 'Parent 2');

# 插入子记录
INSERT INTO child VALUES (1, 1, 'Child 1');
INSERT INTO child VALUES (2, 1, 'Child 2');
INSERT INTO child VALUES (3, 2, 'Child 3');

# 测试外键约束
--error ER_NO_REFERENCED_ROW_2
INSERT INTO child VALUES (4, 999, 'Invalid Child');

# 测试级联删除
DELETE FROM parent WHERE id = 1;
SELECT * FROM child ORDER BY id;

# 清理
DROP TABLE child, parent;

---

9. 故障排查与调优

9.1 常见问题诊断

9.1.1 测试失败分析流程

# 1. 查看测试结果摘要
./mtr --report-features test_name

# 2. 检查reject文件
cat var/log/test_name.reject

# 3. 比较期望与实际输出
diff -u r/test_name.result var/log/test_name.reject

# 4. 查看MySQL错误日志
cat var/log/mysqld.1.err

# 5. 重新运行单个测试（详细模式）
./mtr --verbose test_name

9.1.2 调试技巧

# 在测试脚本中添加调试信息
--echo ### DEBUG: Current connection info
SELECT CONNECTION_ID(), USER(), DATABASE();

# 显示当前状态
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

# 输出变量值
--echo ### DEBUG: Variable values
--echo MASTER_PORT: $MASTER_MYPORT
--echo SLAVE_PORT: $SLAVE_MYPORT

# 暂停执行（手动调试）
--echo ### DEBUG: Pausing for manual inspection
# --sleep 60

9.2 性能优化

9.2.1 并行执行优化

# 优化并行度配置
sub calculate_optimal_parallel {
    my $cpu_cores = get_cpu_count();
    my $memory_gb = get_memory_size_gb();
    
    # 基于资源计算最优并行度
    my $parallel = min(
        $cpu_cores,
        int($memory_gb / 2),  # 每个实例约需2GB内存
        16  # 最大并行数限制
    );
    
    return max($parallel, 1);
}

# 智能测试分组
sub group_tests_by_resource_usage {
    my (@tests) = @_;
    
    my @light_tests = grep { $_->{estimated_memory} < 512 } @tests;
    my @heavy_tests = grep { $_->{estimated_memory} >= 512 } @tests;
    
    return {
        light => \@light_tests,
        heavy => \@heavy_tests
    };
}

9.2.2 资源管理优化

sub optimize_test_environment {
    my ($test_case) = @_;
    
    # 根据测试类型调整配置
    my $config = {
        'innodb_buffer_pool_size' => '128M',
        'innodb_log_file_size' => '64M',
        'max_connections' => '100'
    };
    
    if ($test_case->{type} eq 'performance') {
        $config->{'innodb_buffer_pool_size'} = '512M';
        $config->{'innodb_log_file_size'} = '256M';
    } elsif ($test_case->{type} eq 'replication') {
        $config->{'max_connections'} = '200';
        $config->{'slave_parallel_workers'} = '4';
    }
    
    return $config;
}

9.3 CI/CD集成

# .github/workflows/mysql-test.yml
name: MySQL Test Suite

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        mysql-version: ['8.0', '8.4']
        test-suite: ['main', 'binlog', 'rpl', 'innodb']
    
    steps:
    - uses: actions/checkout@v2
    
    - name: Setup MySQL
      run: |
        sudo apt-get update
        sudo apt-get install mysql-server-${{ matrix.mysql-version }}
    
    - name: Run Test Suite
      run: |
        cd mysql-test
        ./mtr --suite=${{ matrix.test-suite }} \
              --parallel=4 \
              --retry=2 \
              --force \
              --report-features
    
    - name: Upload Test Results
      if: failure()
      uses: actions/upload-artifact@v2
      with:
        name: test-results-${{ matrix.mysql-version }}-${{ matrix.test-suite }}
        path: mysql-test/var/log/

---

10. 最佳实践指南

10.1 测试设计原则

10.1.1 FIRST原则

• Fast（快速）: 测试应该快速执行
• Independent（独立）: 测试之间不应相互依赖
• Repeatable（可重复）: 测试结果应该一致
• Self-Validating（自验证）: 测试应该有明确的通过/失败结果
• Timely（及时）: 测试应该及时编写

10.1.2 测试结构模式

# 标准测试模板
--echo ########################################
--echo # Test Case: [功能描述]
--echo # Purpose: [测试目的]
--echo # Expected: [期望结果]
--echo ########################################

--echo ### Setup Phase
# 环境准备代码

--echo ### Test Phase
# 实际测试代码

--echo ### Verification Phase
# 结果验证代码

--echo ### Cleanup Phase
# 环境清理代码

10.2 代码质量保证

10.2.1 测试代码审查清单

## 测试代码审查清单

### 功能性
- [ ] 测试覆盖了所有关键路径
- [ ] 边界条件得到充分测试
- [ ] 错误情况得到适当处理
- [ ] 测试断言清晰明确

### 可维护性
- [ ] 测试名称具有描述性
- [ ] 代码结构清晰，易于理解
- [ ] 使用了适当的注释
- [ ] 避免了重复代码

### 可靠性
- [ ] 测试结果确定性
- [ ] 正确处理了非确定性输出
- [ ] 适当的超时设置
- [ ] 完整的资源清理

### 性能
- [ ] 测试执行时间合理
- [ ] 避免了不必要的等待
- [ ] 合理的资源使用

10.2.2 自动化质量检查

#!/bin/bash
# check_test_quality.sh

echo "检查测试质量..."

# 检查测试文件命名规范
find t/ -name "*.test" | while read test_file; do
    if [[ ! "$test_file" =~ ^[a-z0-9_]+\.test$ ]]; then
        echo "警告: 测试文件命名不规范: $test_file"
    fi
done

# 检查是否有对应的result文件
find t/ -name "*.test" | while read test_file; do
    result_file="${test_file/\/t\//\/r\/}"
    result_file="${result_file/.test/.result}"
    if [[ ! -f "$result_file" ]]; then
        echo "错误: 缺少result文件: $result_file"
    fi
done

# 检查测试文件大小
find t/ -name "*.test" -size +100k | while read large_test; do
    echo "警告: 测试文件过大: $large_test"
done

echo "质量检查完成"

10.3 团队协作规范

10.3.1 测试命名约定

命名规范：
- 功能测试: feature_basic.test, feature_advanced.test
- 回归测试: bug_12345.test (关联bug编号)
- 性能测试: perf_feature_name.test
- 压力测试: stress_feature_name.test
- 兼容性测试: compat_version_feature.test

10.3.2 文档化要求

# 测试文件头部注释模板
#
# Test: feature_name
# Author: [作者]
# Date: [创建日期]
# Purpose: [测试目的]
# Dependencies: [依赖条件]
# Related: [相关测试/bug]
# Notes: [特殊说明]
#

--source include/have_innodb.inc

--echo #
--echo # Bug#12345: [Bug描述]
--echo # Test case for [具体测试场景]
--echo #

10.4 持续改进

10.4.1 测试度量指标

# 生成测试覆盖率报告
SELECT 
    suite_name,
    COUNT(*) as total_tests,
    SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) as passed_tests,
    SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) as failed_tests,
    AVG(execution_time) as avg_execution_time
FROM test_results 
GROUP BY suite_name;

10.4.2 性能趋势分析

# performance_trend.py
import matplotlib.pyplot as plt
import pandas as pd

def analyze_performance_trend(test_results):
    """分析测试性能趋势"""
    df = pd.DataFrame(test_results)
    
    # 按日期分组计算平均执行时间
    daily_perf = df.groupby('date')['execution_time'].mean()
    
    # 绘制趋势图
    plt.figure(figsize=(12, 6))
    plt.plot(daily_perf.index, daily_perf.values)
    plt.title('Test Suite Performance Trend')
    plt.xlabel('Date')
    plt.ylabel('Average Execution Time (seconds)')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig('performance_trend.png')
    
    return daily_perf

---

[vhengeee]

MySQL-Test (MTR) Testing Framework（另有中文文档）

Table of Contents

• 1. Architecture and Core Components
• 2. Core Code Implementation Principles
• 3. Test Script Language Explained
• 4. Configuration File System
• 5. Test Execution Flow
• 6. Result Verification Mechanism
• 7. Advanced Features and Extensions
• 8. Real-World Case Studies
• 9. Troubleshooting and Tuning
• 10. Best Practices Guide

---

1. Architecture and Core Components

1.1 Overall Architecture

- Role separation:
  - Driver: mysql-test-run.pl (aka mtr) is responsible for selecting test cases, starting/restarting mysqld (multi-instance/Router if needed), orchestrating execution, and aggregating reports.
  - Engine: mysqltest interprets the "test scripting language", establishes sessions with the Server, executes SQL and script commands, and produces and normalizes output.
- Basic pairing: Each test case is paired by name between .test under t/ and .result under r/; after execution, the actual output is compared against .result. If they differ, the test fails, and the actual output is saved as .reject.

The MySQL-Test framework adopts a two-layer architecture:

┌─────────────────────────────────────────────────────────┐
│                    MTR Driver Layer                     │
│  mysql-test-run.pl (mtr)                               │
│  ┌─────────────┐ ┌─────────────┐ ┌─────────────┐        │
│  │ Test Case   │ │ Environment │ │ Result      │        │
│  │ Parser      │ │ Manager     │ │ Collector   │        │
│  └─────────────┘ └─────────────┘ └─────────────┘        │
└─────────────────────────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────┐
│                   Execution Engine Layer                │
│  mysqltest                                             │
│  ┌─────────────┐ ┌─────────────┐ ┌─────────────┐        │
│  │ Script      │ │ SQL         │ │ Output      │        │
│  │ Parser      │ │ Executor    │ │ Processor   │        │
│  └─────────────┘ └─────────────┘ └─────────────┘        │
└─────────────────────────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────┐
│                   MySQL Service Layer                   │
│  mysqld instances                                      │
└─────────────────────────────────────────────────────────┘

1.2 Directories and Files

• The mysql-test/ directory contains:
  • t/: test inputs .test; r/: expected outputs .result;
  • var/log/: runtime logs and .reject files;
  • include/: shared snippets (e.g., have_innodb.inc);
  • suite/: organized by suites (e.g., suite/main/t, suite/main/r).
• Per-test auxiliary files:
  • test_name-master.opt: startup options for the master mysqld instance.
  • test_name-slave.opt: startup options for the slave mysqld instance.
  • test_name-client.opt: mysqltest client options.
  • test_name.cnf: centralized config for multi-instance/client/[ENV] environment variables.

1.3 Core Component Responsibilities

1.3.1 MTR Driver Layer (mysql-test-run.pl)

Main responsibilities:

• Test case discovery and parsing
• MySQL instance lifecycle management
• Test environment initialization and cleanup
• Parallel execution scheduling
• Result aggregation and report generation

Core code logic:

# Pseudocode example
sub run_test_case {
    my ($test_case) = @_;
    
    # 1. Parse test configuration
    my $config = parse_test_config($test_case);
    
    # 2. Start MySQL instance
    my $mysqld = start_mysqld($config);
    
    # 3. Execute the test
    my $result = execute_mysqltest($test_case, $mysqld);
    
    # 4. Collect results
    return collect_results($result);
}

1.3.2 Execution Engine Layer (mysqltest)

Main responsibilities:

• Test scripting language parsing
• Database connection management
• SQL statement execution
• Output formatting and normalization
• Error handling and state management

Core execution flow:

// Pseudocode example
int main_execute_test() {
    // 1. Initialize connection
    MYSQL *mysql = mysql_init(NULL);
    
    // 2. Parse test script
    while (read_command(&command)) {
        switch (command.type) {
            case Q_QUERY:
                execute_query(mysql, command.query);
                break;
            case Q_ECHO:
                output_echo(command.text);
                break;
            case Q_ERROR:
                expect_error(command.error_code);
                break;
        }
    }
    
    // 3. Cleanup resources
    mysql_close(mysql);
    return exit_code;
}

---

2. Core Code Implementation Principles

2.1 Test Case Discovery Mechanism

Directory structure conventions:

mysql-test/
├── t/                    # Test script directory
│   ├── basic.test
│   ├── innodb.test
│   └── replication.test
├── r/                    # Expected result directory
│   ├── basic.result
│   ├── innodb.result
│   └── replication.result
├── include/              # Common include files
│   ├── have_innodb.inc
│   └── default_my.cnf
├── suite/                # Test suites
│   ├── main/
│   ├── binlog/
│   └── rpl/
└── var/                  # Runtime directory
    ├── log/
    └── tmp/

Test case discovery algorithm:

sub collect_test_cases {
    my @test_cases = ();
    
    # Scan the test directory
    find(sub {
        if (/\.test$/) {
            my $test_name = $File::Find::name;
            $test_name =~ s/\.test$//;
            
            # Check for corresponding result file
            my $result_file = "$test_name.result";
            if (-f $result_file) {
                push @test_cases, {
                    name => $test_name,
                    test_file => $File::Find::name,
                    result_file => $result_file
                };
            }
        }
    }, $test_dir);
    
    return @test_cases;
}

2.2 MySQL Instance Management

Instance startup flow:

sub start_mysqld {
    my ($config) = @_;
    
    # 1. Prepare data directory
    prepare_datadir($config->{datadir});
    
    # 2. Generate configuration file
    my $my_cnf = generate_my_cnf($config);
    
    # 3. Initialize database
    if ($config->{initialize}) {
        system("$mysqld --initialize-insecure --datadir=$config->{datadir}");
    }
    
    # 4. Start service
    my $pid = fork();
    if ($pid == 0) {
        exec("$mysqld --defaults-file=$my_cnf");
    }
    
    # 5. Wait for the server to be ready
    wait_for_server_start($config->{port});
    
    return $pid;
}

2.3 Test Script Parser

Command parsing state machine:

typedef enum {
    PARSE_COMMAND,
    PARSE_QUERY,
    PARSE_DELIMITER,
    PARSE_COMMENT
} parser_state_t;

int parse_test_line(char *line, command_t *cmd) {
    static parser_state_t state = PARSE_COMMAND;
    
    switch (state) {
        case PARSE_COMMAND:
            if (strncmp(line, "--", 2) == 0) {
                return parse_command(line + 2, cmd);
            } else {
                state = PARSE_QUERY;
                return parse_query_start(line, cmd);
            }
            break;
            
        case PARSE_QUERY:
            return parse_query_continue(line, cmd);
            
        default:
            return -1;
    }
}

---

3. Test Script Language Explained

3.1 Test Script Writing Guidelines

Three-phase structure: setup / act / assert (reflected by comparison with .result), use --echo to label phases throughout.
Reproducible output: avoid timestamps/random numbers/temp paths; when necessary, use replacement/normalization directives in the scripting language.
Atomic independence: each .test creates/cleans up its own objects, without relying on execution order.
Small and focused: one test case focuses on a single feature or a tightly related set of assertions.
Diagnosable: results include necessary context (column names, key fields, phase labels).
Explicit parameters: parameters related to behavior are managed via .opt/.cnf for auditability and reproducibility.

3.2 Basic Syntax Structure

Command categories:

Command Type	Syntax	Description
Control	--command	Control test execution flow
SQL	SELECT ...;	Execute SQL query
Comment	# comment	Code comments
Output	--echo text	Output text to result

3.3 Core Commands in Detail

3.3.1 Connection Management Commands

-- Create a new connection
--connect (connection_name, host, user, password, database, port, socket)
--connect (conn1, localhost, root, , test, $MASTER_MYPORT, )

-- Switch connection
--connection connection_name
--connection conn1

-- Disconnect
--disconnect connection_name
--disconnect conn1

Implementation principle:

static MYSQL* connections[MAX_CONNECTIONS];
static int current_connection = 0;

int handle_connect_command(char *params) {
    char name[64], host[64], user[64], password[64];
    int port;
    
    // Parse connection parameters
    parse_connect_params(params, name, host, user, password, &port);
    
    // Establish connection
    MYSQL *mysql = mysql_init(NULL);
    if (!mysql_real_connect(mysql, host, user, password, NULL, port, NULL, 0)) {
        return handle_connection_error(mysql);
    }
    
    // Save connection
    int conn_id = find_free_connection_slot();
    connections[conn_id] = mysql;
    register_connection_name(name, conn_id);
    
    return 0;
}

3.3.2 Error Handling Commands

-- Expect a specific error
--error ER_DUP_ENTRY
INSERT INTO t VALUES (1, 'duplicate');

-- Expect one of multiple possible errors
--error ER_DUP_ENTRY, ER_DUP_KEY
INSERT INTO t VALUES (1, 'duplicate');

-- Expect success (default behavior)
--error 0
SELECT * FROM t;

Error matching mechanism:

static int expected_errors[MAX_EXPECTED_ERRORS];
static int num_expected_errors = 0;

int execute_query_with_error_check(MYSQL *mysql, const char *query) {
    int result = mysql_real_query(mysql, query, strlen(query));
    int actual_error = mysql_errno(mysql);
    
    if (num_expected_errors > 0) {
        // Check if it matches an expected error
        for (int i = 0; i < num_expected_errors; i++) {
            if (expected_errors[i] == actual_error) {
                reset_expected_errors();
                return 0; // Expected error matched
            }
        }
        
        // Error did not match
        fprintf(stderr, "Expected error %d, got %d\n", 
                expected_errors[0], actual_error);
        return 1;
    }
    
    // No expected error, check for success
    if (result != 0) {
        fprintf(stderr, "Unexpected error: %s\n", mysql_error(mysql));
        return 1;
    }
    
    return 0;
}

3.3.3 Output Control Commands

-- Output text
--echo ### Test Case: Basic SELECT

-- Disable query output
--disable_query_log
SET @var = 1;
--enable_query_log

-- Disable result output
--disable_result_log
SELECT * FROM large_table;
--enable_result_log

-- Output variable value
--echo Variable value: $MYSQL_VERSION

3.4 Advanced Scripting Features

3.4.1 Conditional Execution

-- Check for feature support
--source include/have_innodb.inc
--source include/have_ssl.inc

-- Platform-specific skip
--source include/not_windows.inc

-- Version check
--source include/mysql_version_ge_8_0.inc

Conditional check implementation:

# Example contents of have_innodb.inc
--disable_query_log
--disable_result_log
--disable_warnings

let $have_innodb= `SELECT COUNT(*) FROM INFORMATION_SCHEMA.ENGINES 
                   WHERE ENGINE='InnoDB' AND SUPPORT IN ('YES', 'DEFAULT')`;

if (!$have_innodb) {
  --skip Test requires InnoDB engine
}

--enable_warnings
--enable_result_log
--enable_query_log

3.4.2 Loops and Variables

-- Variable definition
let $counter = 10;
let $table_name = test_table;

-- Loop execution
while ($counter > 0) {
  eval INSERT INTO $table_name VALUES ($counter, 'data_$counter');
  dec $counter;
}

-- Conditional checks
if ($mysql_version >= 80000) {
  SELECT 'MySQL 8.0 or later';
}
if ($mysql_version < 80000) {
  SELECT 'MySQL version before 8.0';
}

---

4. Configuration File System

4.1 Configuration File Hierarchy

Configuration priority (from high to low):
1. test_name.cnf          # Test-specific configuration
2. test_name-master.opt   # Master instance startup parameters
3. test_name-slave.opt    # Slave instance startup parameters
4. test_name-client.opt   # Client parameters
5. suite.cnf              # Suite-level configuration
6. default_my.cnf         # Default configuration

4.2 Configuration File Formats

4.2.1 .cnf Configuration Files

# test_replication.cnf
!include include/default_my.cnf

[mysqld.1]
# Master server configuration
server-id=1
log-bin=master-bin
binlog-format=ROW
gtid-mode=ON
enforce-gtid-consistency=ON

[mysqld.2]
# Slave server configuration
server-id=2
relay-log=slave-relay
read-only=ON
gtid-mode=ON
enforce-gtid-consistency=ON

[mysqltest]
# Client configuration
ps-protocol
view-protocol

[ENV]
# Environment variables
MASTER_PORT=@mysqld.1.port
SLAVE_PORT=@mysqld.2.port
MASTER_SOCKET=@mysqld.1.socket
SLAVE_SOCKET=@mysqld.2.socket

4.2.2 .opt Parameter Files

# test_innodb-master.opt
--innodb-buffer-pool-size=128M
--innodb-log-file-size=64M
--innodb-flush-log-at-trx-commit=1
--innodb-file-per-table=ON
--sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO

4.3 Dynamic Configuration Injection

Port allocation mechanism:

sub allocate_ports {
    my ($num_instances) = @_;
    my @ports = ();
    
    for my $i (1..$num_instances) {
        my $port = find_free_port();
        push @ports, $port;
        
        # Register port variables
        $ENV{"MYSQLD_${i}_PORT"} = $port;
    }
    
    return @ports;
}

sub substitute_variables {
    my ($config_content) = @_;
    
    # Replace port variables
    $config_content =~ s/\@mysqld\.(\d+)\.port/$ENV{"MYSQLD_${1}_PORT"}/g;
    
    # Replace socket variables
    $config_content =~ s/\@mysqld\.(\d+)\.socket/$ENV{"MYSQLD_${1}_SOCKET"}/g;
    
    return $config_content;
}

---

5. Test Execution Flow

5.1 Full Execution Sequence Diagram

MTR Driver          MySQL Instance       mysqltest           Test Files
    │                      │                  │                    │
    ├─ 1. Parse test case ─┤                  │                    │
    │                      │                  │                    │
    ├─ 2. Start mysqld ────┤                  │                    │
    │                      ├─ 2.1 Initialize │                    │
    │                      ├─ 2.2 Start      │                    │
    │                      ├─ 2.3 Ready      │                    │
    │                      │                  │                    │
    ├─ 3. Execute test ────┼──────────────────┤                    │
    │                      │                  ├─ 3.1 Read .test ──┤
    │                      │                  ├─ 3.2 Parse script │
    │                      ├─ 3.3 Execute SQL┤                    │
    │                      ├─ 3.4 Return res ┤                    │
    │                      │                  ├─ 3.5 Format output│
    │                      │                  │                    │
    ├─ 4. Compare results ┼──────────────────┤                    │
    │                      │                  ├─ 4.1 Read .result ┤
    │                      │                  ├─ 4.2 Compare      │
    │                      │                  ├─ 4.3 Generate diff│
    │                      │                  │                    │
    ├─ 5. Cleanup ─────────┤                  │                    │
    │                      ├─ 5.1 Stop       │                    │
    │                      ├─ 5.2 Cleanup    │                    │

5.2 Core Execution Algorithms

sub execute_test_suite {
    my (@test_cases) = @_;
    my @results = ();
    
    for my $test_case (@test_cases) {
        my $result = execute_single_test($test_case);
        push @results, $result;
        
        # Failure handling
        if ($result->{status} eq 'FAIL' && !$opt_force) {
            last;
        }
    }
    
    return generate_summary_report(@results);
}

sub execute_single_test {
    my ($test_case) = @_;
    
    eval {
        # 1. Prepare environment
        my $env = prepare_test_environment($test_case);
        
        # 2. Start MySQL
        my $mysqld = start_mysqld_for_test($test_case, $env);
        
        # 3. Execute the test
        my $output = run_mysqltest($test_case, $mysqld);
        
        # 4. Compare results
        my $comparison = compare_with_expected($output, $test_case);
        
        # 5. Cleanup
        cleanup_test_environment($env, $mysqld);
        
        return {
            name => $test_case->{name},
            status => $comparison->{status},
            duration => $comparison->{duration},
            diff => $comparison->{diff}
        };
    };
    
    if ($@) {
        return {
            name => $test_case->{name},
            status => 'ERROR',
            error => $@
        };
    }
}

5.3 Parallel Execution Mechanism

sub execute_parallel {
    my ($test_cases, $parallel_count) = @_;
    
    my $pm = Parallel::ForkManager->new($parallel_count);
    my @results = ();
    
    $pm->run_on_finish(sub {
        my ($pid, $exit_code, $ident, $exit_signal, $core_dump, $result) = @_;
        push @results, $result;
    });
    
    for my $test_case (@$test_cases) {
        $pm->start and next;
        
        # Child process executes the test
        my $result = execute_single_test($test_case);
        $pm->finish(0, $result);
    }
    
    $pm->wait_all_children;
    return @results;
}

---

6. Result Verification Mechanism

6.1 Output Normalization

Normalization rules:

// Output normalization function
char* normalize_output(char* raw_output) {
    char* normalized = malloc(strlen(raw_output) * 2);
    char* dest = normalized;
    char* src = raw_output;
    
    while (*src) {
        switch (*src) {
            case '\t':
                // Convert tab to a fixed space
                *dest++ = ' ';
                break;
                
            case '\r':
                // Remove carriage return
                break;
                
            default:
                *dest++ = *src;
        }
        src++;
    }
    
    *dest = '\0';
    
    // Remove trailing whitespace
    trim_trailing_whitespace(normalized);
    
    return normalized;
}

6.2 Diff Detection Algorithm

typedef struct {
    int line_number;
    char* expected;
    char* actual;
    diff_type_t type; // MISSING, EXTRA, DIFFERENT
} diff_entry_t;

diff_result_t* compare_outputs(char* expected, char* actual) {
    diff_result_t* result = malloc(sizeof(diff_result_t));
    result->differences = NULL;
    result->diff_count = 0;
    
    char** expected_lines = split_lines(expected);
    char** actual_lines = split_lines(actual);
    
    int exp_count = count_lines(expected_lines);
    int act_count = count_lines(actual_lines);
    
    // Use the LCS algorithm for diff detection
    int** lcs_matrix = compute_lcs_matrix(expected_lines, actual_lines, 
                                         exp_count, act_count);
    
    // Backtrack to generate the diff list
    generate_diff_list(result, expected_lines, actual_lines, 
                      lcs_matrix, exp_count, act_count);
    
    free_matrix(lcs_matrix);
    free_lines(expected_lines);
    free_lines(actual_lines);
    
    return result;
}

6.3 Handling Non-Deterministic Output

Common non-deterministic patterns:

-- Timestamp replacement
--replace_regex /\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}/TIMESTAMP/

-- Thread ID replacement
--replace_regex /thread_id=[0-9]+/thread_id=ID/

-- File path replacement
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR

-- Port number replacement
--replace_result $MASTER_MYPORT MASTER_PORT

Replacement engine implementation:

char* apply_replacements(char* input, replacement_rule_t* rules, int rule_count) {
    char* result = strdup(input);
    
    for (int i = 0; i < rule_count; i++) {
        replacement_rule_t* rule = &rules[i];
        
        switch (rule->type) {
            case REPLACE_REGEX:
                result = regex_replace(result, rule->pattern, rule->replacement);
                break;
                
            case REPLACE_STRING:
                result = string_replace(result, rule->pattern, rule->replacement);
                break;
                
            case REPLACE_COLUMN:
                result = column_replace(result, rule->column_index, rule->replacement);
                break;
        }
    }
    
    return result;
}

---

7. Advanced Features and Extensions

7.1 Multi-Instance Testing

Master-slave replication test example:

# rpl_basic.test
--source include/master-slave.inc

# Create table on the master
--connection master
CREATE TABLE t1 (id INT PRIMARY KEY, data VARCHAR(100));
INSERT INTO t1 VALUES (1, 'test data');

# Synchronize to the slave
--sync_slave_with_master

# Validate data on the slave
--connection slave
SELECT * FROM t1;

# Cleanup
--connection master
DROP TABLE t1;
--sync_slave_with_master

--source include/rpl_end.inc

Multi-instance configuration:

# rpl_basic.cnf
[mysqld.1]
server-id=1
log-bin=master-bin
binlog-format=ROW

[mysqld.2]
server-id=2
relay-log=slave-relay
read-only=ON

[ENV]
MASTER_MYPORT=@mysqld.1.port
SLAVE_MYPORT=@mysqld.2.port

7.2 Performance Test Integration

# performance_test.test
--echo ### Performance Test: Large INSERT

# Create test table
CREATE TABLE perf_test (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(1000),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# Record start time
let $start_time = `SELECT UNIX_TIMESTAMP()`;

# Perform bulk inserts
--disable_query_log
let $i = 10000;
while ($i > 0) {
    eval INSERT INTO perf_test (data) VALUES (REPEAT('x', 100));
    dec $i;
}
--enable_query_log

# Record end time
let $end_time = `SELECT UNIX_TIMESTAMP()`;
let $duration = `SELECT $end_time - $start_time`;

--echo ### Duration: $duration seconds

# Verify results
SELECT COUNT(*) FROM perf_test;

DROP TABLE perf_test;

7.3 Plugin Test Framework

# plugin_test.test
--source include/have_plugin_auth.inc

# Install plugin
INSTALL PLUGIN test_plugin SONAME 'test_plugin.so';

# Test plugin functionality
SELECT plugin_name, plugin_status 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE plugin_name = 'test_plugin';

# Use plugin functionality
SELECT test_plugin_function('test_input');

# Uninstall plugin
UNINSTALL PLUGIN test_plugin;

---

8. Real-World Case Studies

8.1 Transaction Isolation Level Test

# isolation_levels.test
--echo ### Transaction Isolation Level Test

# Create test table
CREATE TABLE isolation_test (
    id INT PRIMARY KEY,
    value INT
);

INSERT INTO isolation_test VALUES (1, 100);

# Test READ COMMITTED
--echo ### Testing READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

--connect (conn1, localhost, root, , test, $MASTER_MYPORT, )
--connect (conn2, localhost, root, , test, $MASTER_MYPORT, )

# Connection 1 starts a transaction
--connection conn1
BEGIN;
UPDATE isolation_test SET value = 200 WHERE id = 1;

# Connection 2 reads data (should see the old value)
--connection conn2
BEGIN;
SELECT value FROM isolation_test WHERE id = 1;

# Connection 1 commits
--connection conn1
COMMIT;

# Connection 2 reads again (should see the new value)
--connection conn2
SELECT value FROM isolation_test WHERE id = 1;
COMMIT;

# Cleanup
--disconnect conn1
--disconnect conn2
--connection default
DROP TABLE isolation_test;

8.2 Deadlock Detection Test

# deadlock_detection.test
--echo ### Deadlock Detection Test

CREATE TABLE deadlock_test1 (id INT PRIMARY KEY, data VARCHAR(100));
CREATE TABLE deadlock_test2 (id INT PRIMARY KEY, data VARCHAR(100));

INSERT INTO deadlock_test1 VALUES (1, 'data1');
INSERT INTO deadlock_test2 VALUES (1, 'data2');

--connect (conn1, localhost, root, , test, $MASTER_MYPORT, )
--connect (conn2, localhost, root, , test, $MASTER_MYPORT, )

# Connection 1: lock table 1, then attempt to lock table 2
--connection conn1
BEGIN;
UPDATE deadlock_test1 SET data = 'updated1' WHERE id = 1;

# Connection 2: lock table 2, then attempt to lock table 1
--connection conn2
BEGIN;
UPDATE deadlock_test2 SET data = 'updated2' WHERE id = 1;

# Connection 1 attempts to lock table 2 (will wait)
--connection conn1
--send UPDATE deadlock_test2 SET data = 'conflict1' WHERE id = 1

# Connection 2 attempts to lock table 1 (will trigger deadlock)
--connection conn2
--error ER_LOCK_DEADLOCK
UPDATE deadlock_test1 SET data = 'conflict2' WHERE id = 1;
ROLLBACK;

# Connection 1 should succeed
--connection conn1
--reap
COMMIT;

# Cleanup
--disconnect conn1
--disconnect conn2
--connection default
DROP TABLE deadlock_test1, deadlock_test2;

8.3 Storage Engine Feature Tests

# innodb_features.test
--source include/have_innodb.inc

--echo ### InnoDB Foreign Key Test

CREATE TABLE parent (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    data VARCHAR(100),
    FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=InnoDB;

# Insert parent records
INSERT INTO parent VALUES (1, 'Parent 1');
INSERT INTO parent VALUES (2, 'Parent 2');

# Insert child records
INSERT INTO child VALUES (1, 1, 'Child 1');
INSERT INTO child VALUES (2, 1, 'Child 2');
INSERT INTO child VALUES (3, 2, 'Child 3');

# Test foreign key constraint
--error ER_NO_REFERENCED_ROW_2
INSERT INTO child VALUES (4, 999, 'Invalid Child');

# Test cascade delete
DELETE FROM parent WHERE id = 1;
SELECT * FROM child ORDER BY id;

# Cleanup
DROP TABLE child, parent;

---

9. Troubleshooting and Tuning

9.1 Common Issue Diagnosis

9.1.1 Test Failure Analysis Flow

# 1. View test result summary
./mtr --report-features test_name

# 2. Check the reject file
cat var/log/test_name.reject

# 3. Compare expected vs actual output
diff -u r/test_name.result var/log/test_name.reject

# 4. View the MySQL error log
cat var/log/mysqld.1.err

# 5. Re-run a single test (verbose mode)
./mtr --verbose test_name

9.1.2 Debugging Tips

# Add debugging information in the test script
--echo ### DEBUG: Current connection info
SELECT CONNECTION_ID(), USER(), DATABASE();

# Show current state
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

# Output variable values
--echo ### DEBUG: Variable values
--echo MASTER_PORT: $MASTER_MYPORT
--echo SLAVE_PORT: $SLAVE_MYPORT

# Pause execution (manual debugging)
--echo ### DEBUG: Pausing for manual inspection
# --sleep 60

9.2 Performance Optimization

9.2.1 Parallel Execution Tuning

# Optimize parallelism settings
sub calculate_optimal_parallel {
    my $cpu_cores = get_cpu_count();
    my $memory_gb = get_memory_size_gb();
    
    # Compute optimal parallelism based on resources
    my $parallel = min(
        $cpu_cores,
        int($memory_gb / 2),  # About 2GB per instance
        16  # Maximum parallelism limit
    );
    
    return max($parallel, 1);
}

# Intelligent test grouping
sub group_tests_by_resource_usage {
    my (@tests) = @_;
    
    my @light_tests = grep { $_->{estimated_memory} < 512 } @tests;
    my @heavy_tests = grep { $_->{estimated_memory} >= 512 } @tests;
    
    return {
        light => \@light_tests,
        heavy => \@heavy_tests
    };
}

9.2.2 Resource Management Optimization

sub optimize_test_environment {
    my ($test_case) = @_;
    
    # Adjust configuration based on test type
    my $config = {
        'innodb_buffer_pool_size' => '128M',
        'innodb_log_file_size' => '64M',
        'max_connections' => '100'
    };
    
    if ($test_case->{type} eq 'performance') {
        $config->{'innodb_buffer_pool_size'} = '512M';
        $config->{'innodb_log_file_size'} = '256M';
    } elsif ($test_case->{type} eq 'replication') {
        $config->{'max_connections'} = '200';
        $config->{'slave_parallel_workers'} = '4';
    }
    
    return $config;
}

9.3 CI/CD Integration

# .github/workflows/mysql-test.yml
name: MySQL Test Suite

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        mysql-version: ['8.0', '8.4']
        test-suite: ['main', 'binlog', 'rpl', 'innodb']
    
    steps:
    - uses: actions/checkout@v2
    
    - name: Setup MySQL
      run: |
        sudo apt-get update
        sudo apt-get install mysql-server-${{ matrix.mysql-version }}
    
    - name: Run Test Suite
      run: |
        cd mysql-test
        ./mtr --suite=${{ matrix.test-suite }} \
              --parallel=4 \
              --retry=2 \
              --force \
              --report-features
    
    - name: Upload Test Results
      if: failure()
      uses: actions/upload-artifact@v2
      with:
        name: test-results-${{ matrix.mysql-version }}-${{ matrix.test-suite }}
        path: mysql-test/var/log/

---

10. Best Practices Guide

10.1 Test Design Principles

10.1.1 FIRST Principles

• Fast: Tests should run quickly
• Independent: Tests should not depend on each other
• Repeatable: Test results should be consistent
• Self-Validating: Tests should have clear pass/fail outcomes
• Timely: Tests should be written in a timely manner

10.1.2 Test Structure Pattern

# Standard test template
--echo ########################################
--echo # Test Case: [Feature Description]
--echo # Purpose: [Test Purpose]
--echo # Expected: [Expected Result]
--echo ########################################

--echo ### Setup Phase
# Environment preparation code

--echo ### Test Phase
# Actual test code

--echo ### Verification Phase
# Result verification code

--echo ### Cleanup Phase
# Environment cleanup code

10.2 Code Quality Assurance

10.2.1 Test Code Review Checklist

## Test Code Review Checklist

### Functionality
- [ ] Tests cover all critical paths
- [ ] Boundary conditions are adequately tested
- [ ] Error cases are properly handled
- [ ] Test assertions are clear and explicit

### Maintainability
- [ ] Test names are descriptive
- [ ] Code structure is clear and easy to understand
- [ ] Appropriate comments are used
- [ ] Duplicate code is avoided

### Reliability
- [ ] Deterministic test results
- [ ] Non-deterministic output is handled correctly
- [ ] Appropriate timeouts are set
- [ ] Complete resource cleanup

### Performance
- [ ] Reasonable test execution time
- [ ] Unnecessary waits are avoided
- [ ] Reasonable resource usage

10.2.2 Automated Quality Checks

#!/bin/bash
# check_test_quality.sh

echo "Checking test quality..."

# Check test file naming conventions
find t/ -name "*.test" | while read test_file; do
    if [[ ! "$test_file" =~ ^[a-z0-9_]+\.test$ ]]; then
        echo "Warning: Non-standard test file name: $test_file"
    fi
done

# Check for corresponding result files
find t/ -name "*.test" | while read test_file; do
    result_file="${test_file/\/t\//\/r\/}"
    result_file="${result_file/.test/.result}"
    if [[ ! -f "$result_file" ]]; then
        echo "Error: Missing result file: $result_file"
    fi
done

# Check test file size
find t/ -name "*.test" -size +100k | while read large_test; do
    echo "Warning: Test file too large: $large_test"
done

echo "Quality check complete"

10.3 Team Collaboration Guidelines

10.3.1 Test Naming Conventions

Naming conventions:
- Feature tests: feature_basic.test, feature_advanced.test
- Regression tests: bug_12345.test (associated bug ID)
- Performance tests: perf_feature_name.test
- Stress tests: stress_feature_name.test
- Compatibility tests: compat_version_feature.test

10.3.2 Documentation Requirements

# Test file header comment template
#
# Test: feature_name
# Author: [Author]
# Date: [Creation Date]
# Purpose: [Test Purpose]
# Dependencies: [Dependencies]
# Related: [Related tests/bugs]
# Notes: [Special notes]
#

--source include/have_innodb.inc

--echo #
--echo # Bug#12345: [Bug Description]
--echo # Test case for [Specific test scenario]
--echo #

10.4 Continuous Improvement

10.4.1 Test Metrics

# Generate test coverage report
SELECT 
    suite_name,
    COUNT(*) as total_tests,
    SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) as passed_tests,
    SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) as failed_tests,
    AVG(execution_time) as avg_execution_time
FROM test_results 
GROUP BY suite_name;

10.4.2 Performance Trend Analysis

# performance_trend.py
import matplotlib.pyplot as plt
import pandas as pd

def analyze_performance_trend(test_results):
    """Analyze test performance trends"""
    df = pd.DataFrame(test_results)
    
    # Group by date and calculate average execution time
    daily_perf = df.groupby('date')['execution_time'].mean()
    
    # Plot the trend chart
    plt.figure(figsize=(12, 6))
    plt.plot(daily_perf.index, daily_perf.values)
    plt.title('Test Suite Performance Trend')
    plt.xlabel('Date')
    plt.ylabel('Average Execution Time (seconds)')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig('performance_trend.png')
    
    return daily_perf

---