search

actiontech / dtle

Distributed Data Transfer Service for MySQL
https://actiontech.github.io/dtle-docs-cn
Mozilla Public License 2.0
549 stars 132 forks source link

mysql-test 移植 #766

Open ghost opened 3 years ago

ghost commented 3 years ago

1 获取 mysql-test

https://dev.mysql.com/downloads/mysql/

下载下述两个压缩包(或其他版本)

tar --xz -xf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
tar --xz -xf mysql-test-8.0.26-linux-glibc2.12-x86_64.tar.xz

两个压缩包将解压到同一目录 mysql-8.0.26-linux-glibc2.12-x86_64 中

ghost commented 3 years ago

2 mysql-test 架构

https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_MYSQL_TEST_RUN.html

cd .../mysql-test/ 目录结构

执行测试

./mysql-test-run.pl rpl_binlog_transaction_dependency_history_size # single test
./mysql-test-run.pl --suite=rpl # a suite
ghost commented 3 years ago

3 .test文件内容

# 注释

# 普通sql语句.
INSERT INTO t1 VALUES (null); # 1 2

# mysqltest指令, 使用双横杠开头.
--source include/master-slave.inc
--connection slave

# mysqltest指令, 也可以不使用双横杠开头. 可能是老式用法.
# 根据规范, mysqltest指令不会和普通sql语句冲突.
connection master;

常用mysqltest指令

``` # 类似bash的source和C/C++的#include --source path/to/file # 建立一个客户端到mysqld的连接 --connect # 断开一个连接 --disconnect # 切换连接. 某些测试需要使用多个连接 --connection connection_name # 定义变量 --let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1) # eval一个sql语句, 把变量替换成实际值, 并执行 --eval CHANGE REPLICATION SOURCE TO SOURCE_LOG_POS=$query_pos, SOURCE_AUTO_POSITION=0 # 流程控制 if (expr) { command list } while (expr) { command list } ```
ghost commented 3 years ago

4 mysql-test 移植

和Writeset MTS有关的test, 一部分在 WL#9556 中添加. 另一部分是原有的mts_logical_clock test (回放端).

4.1 rpl_binlog_transaction_dependency_tracking.test

test目标: 测试writeset dependency_tracking是否生成了正确的依赖关系.

test内容

1: 用变量`$logical_timestamps`保存期待的结果 ```sql # 直接保存 --let $logical_timestamps=0 1;1 2;1 3;3 4;4 5;4 6;4 7;4 8 # 或利用mysql表, 方便写循环 INSERT INTO `tests` (`id`, `description`, `results`) VALUES (1, 'Writeset', '0 1;1 2;1 3;1 4;3 5;3 6;4 7;7 8;6 9;9 10'),... (12, 'Commit_order+history', '0 1;1 2;1 3;3 4;4 5;4 6;6 7;7 8;7 9;9 10'); --let $logical_timestamps=`SELECT results FROM tests WHERE id=$idx` ``` 2: 执行各类sql 3: 调用 `assert_logical_timestamps.inc` 进行比较 ```sql --let $source_file= $server_1_datadir/$binlog_file --let $logical_timestamps=`SELECT results FROM tests WHERE id=$idx` --source include/assert_logical_timestamps.inc ``` 3.1: `assert_logical_timestamps.inc` 调用mysqlbinlog程序解析产生的binlog, 调用 `assert_grep.inc`比较mysqlbinlog的输出. ```sql --exec $MYSQL_BINLOG --force-if-open $_alt_offset $_alt_datadir/$binlog_file > $assert_file ... --source include/assert_grep.inc ```

移植方案

  1. 执行类似的系列TX, 构造binlog (不使用MySQL的writeset_depedency_tracking)
  2. 用dtle回放
  3. dtle将对TX的last_commit进行重新计算, 并打印特定日志(debug)
  4. 验证日志中的结果
ghost commented 3 years ago

4.2 rpl_binlog_transaction_dependency_history_size.test

test目标: 测试可调参数 binlog_transaction_dependency_history_size 行为是否正常.

内容:

1: 在 rpl_binlog_transaction_dependency_history_size-master.opt 中设置一个较小的history_size

--binlog_transaction_dependency_tracking=COMMIT_ORDER
--transaction_write_set_extraction=XXHASH64
--binlog_transaction_dependency_history_size=3

2: 和上述测试一样, 调用 assert_logical_timestamps.inc进行比较.

移植方案: dtle中对应的参数为DependencyHistorySize, 我们还是通过日志输出来验证正确性.

4.3 rpl_binlog_transaction_dependency_tracking_with_filters.test

1: Add transactions with different dependency tracking and empty transaction on master.

验证这些场景下master mysqld生成binlog中的TX last_commit.

2: Test timestamps order with filtered and non-filtered transactions on Slave.

2.1: (我们不用COMMIT_ORDER, 略)

2.2: WRITESET with SLAVE

2.3: (我们不用WRITESET_SESSION, 略)

注: include/sync_slave_sql_with_master.inc:

Must be called on the master. Will change connection to the slave.

4.4 rpl_binlog_transaction_dependency_tracking_with_indexes.test

Verify that logical timestamps are generated correctly for all types of indexes when binlog_transaction_dependency_tracking=WRITESET.

P1: 如果一个TX涉及无PK表, 它必须

如果这个TX同时还涉及有PK表, 需要更新history

P2: 一个TX只涉及有PK表, 需要根据writeset来计算依赖

P3: NOT NULL UNIQUE index 等同于PK

P4: 如果同时有PK和其他keys, 其他keys产生的uniqueness也会产生依赖

P5: 如果同时有PK和可空列, 同一列中的空值不会冲突(我的理解: 特指UK中的NULL不会互相依赖)

对于一张有3个列(a,b,c)的表, 验证不同key的组合下, 同一系列DML TX的依赖关系. A. column a上有/无PK B. b和c上有/无 NotNull constraints C. b和c上有/无 UK constraints D. b和c上都没有index / b上有index / c上有index (Skip the no-index case if uniqueness constraints are required).

key组合数量: 2x2x(2x3-1) = 20

ghost commented 3 years ago

place holder

ghost commented 3 years ago

place holder

ghost commented 3 years ago

place holder

ghost commented 3 years ago

忽略的test

binlog_reset_connection_not_reset_writeset.test # 和 tracking=writeset_session 有关. 我们不是session.
binlog_transaction_dependency_tracking_on_start.test # 只和 mysql 配置有关
binlog_transaction_dependency_tracking_sensitive.test # 被后续commit删除
rpl_binlog_transaction_dependency_tracking_with_fk.test # foreign key在实践中基本不用
rpl_binlog_transaction_dependency_tracking_with_invisible_columns.test # invisible_columns 是 8.0.23引入的新功能
ghost commented 3 years ago

Writeset MTS 术语