错误结果？警惕mysql_fdw数据黑洞
记录一起mysql_fdw bug，同样的sql通过mysql_fdw远程查询，和本地查询mysql数据库结果是不同的。
MySQL_fdw简介
mysql_fdw是PG的扩展，提供了 MySQL 服务器和 PostgreSQL数据库之间的接口。它将 PostgreSQL语句 ( SELECT/ INSERT/ DELETE/ UPDATE) 转换为 MySQL 数据库可以理解的查询。
添加图片注释，不超过 140 字（可选）
简单总结下原理：MySQL_fdw将PG的SQL语句deparse为mysql的SQL语句，调用封装的mysql C-Api连接MySQL数据库执行SQL，获取结果后convert为PG对应的数据格式，返回给PG数据库客户端。
问题
最近朋友反馈一个PG实例通过mysql_fdw查询的数据不正确，同样的sql在mysql中和PG的外部表查询结果是不同的。
这套环境比较老，MySQL 5.7.44 、PostgreSQL 12.4、mysql_fdw_2_7_0
现象如下： MySQL中查询data_tbl表，id为自增列，表有3条数据，执行sql：select id,random_string from data_tbl order by id desc limit 1查到的数据为id为3的这条数据，这是符合预期的。
但是在PG中使用mysql_fdw建立的外部表查到的居然是id为1的数据，很明显结果是不正确的。
MySQL查询结果：
readonly@localhost [test]>show create table data_tbl\G *************************** 1.row *************************** Table: data_tbl CreateTable: CREATE TABLE`data_tbl` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `random_string` longtext NOT NULL, `created_at`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) readonly@localhost [test]>select count(1) from data_tbl\G *************************** 1.row *************************** count(1): 3 1 row in set (0.00 sec) readonly@localhost [test]>select max(id) from data_tbl\G *************************** 1.row *************************** max(id): 3 1 row inset (0.00 sec) readonly@localhost [test]>select id,random_string from data_tbl order by id desc limit 1\G *************************** 1.row *************************** id: 3 random_string: ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/ --random_string字段值比较长，这里截取了一部分，可以看到查询的id为3
PG外表查询结果：
test=> \d+ data_tbl Foreign table "public.data_tbl" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description ---------------+-----------------------------+-----------+----------+---------+-------------+----------+--------------+------------- id | bigint | | | | | plain | | random_string | text | | | | | extended | | created_at | timestamp without time zone | | | | | plain | | Server: mysql_test FDW options: (dbname 'test', table_name 'data_tbl') test=> select count(1) from data_tbl; -[ RECORD 1 ] count | 3 test=> select id,random_string from data_tbl order by id desc limit 1; -[ RECORD 1 ]-+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- id | 1 random_string | ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/ --random_string字段值比较长，这里截取了一部分，可以看到查询的id为1
分析
只有3条数据，id最大值为3，但这个sql依照id字段倒序输出1行在PG外表中的结果却为1，难道是PG中倒序排序出了问题？
经过排查发现当只查询id字段时，输出的结果id为3是正确的，说明这个异常和random_string字段有关系。
test=> select id from data_tbl order by id desc limit 1; id ---- 3 (1 row) test=>
random_string是text类型的，根据我们对mysql_fdw原理的了解，我预感问题应该出在从MySQL查询到结果mysql_fdw转换为PG数据格式返回结果这个阶段。
好在mysql_fdw的代码量很小，一边排查代码，一边查看新版本的Release Notes和commit history，发现在mysql_fdw_2_8_0版本中“Fix text column data truncation when reading very long data”这个Fix似乎和我们遇到的场景有些相关性。
我们来看看修复的具体内容：
mysql_bind_result函数中新增了对TEXT的结果长度处理，如果field->max_length不为0，则字段长度为结果的max_length，也就是按照实际结果长度处理。之前的版本允许最大长度为MAXDATALEN即65536字节。
添加图片注释，不超过 140 字（可选）
mysql_convert_to_pg函数中同样新增了TEXT类型的处理。
添加图片注释，不超过 140 字（可选）
那么执行结果的field->max_length是怎么获取的呢？
这就涉及到MySQL的C-Api函数了，翻阅了源码和文档，当调用mysql_stmt_attr_set(MYSQL_STMT, STMT_ATTR_UPDATE_MAX_LENGTH, &flag)做了配置后，mysql_stmt_store_result(MYSQL_STMT *stmt)就可以更新metadata记录结果的field->max_length
添加图片注释，不超过 140 字（可选）
我们看下2.8.0版本中的变化：
mysqlBeginForeignScan函数中调用mysql_stmt_attr_set(festate->stmt,STMT_ATTR_UPDATE_MAX_LENGTH,&festate->has_var_size_col);
添加图片注释，不超过 140 字（可选）
bind_stmt_params_and_exec函数中调用mysql_stmt_store_result获得field->max_length，遍历festate->retrieved_attrs，调用mysql_bind_result处理结果字段长度为field->max_length
添加图片注释，不超过 140 字（可选）
修复方案解读完毕，我们看下实际执行过程做下验证。
可以看到id为2的这条记录random_string的长度为65537，所以在低版本mysql_fdw从这行开始的结果都会被丢弃。
mysql> select id,length(random_string) from data_tbl; +----+-----------------------+ | id | length(random_string) | +----+-----------------------+ | 1 | 65536 | | 2 | 65537 | | 3 | 65536 | +----+-----------------------+ 3 rows in set (0.01 sec) mysql>
那么同时也说明了虽然order by id desc limit 1但是order by和limit算子都没有push down到mysql，是直接查询所有数据到pg再做order by和limit，由于id=2开始被截断了，所以这个sql执行结果返回的结果为id=1的数据。
Release Notes也说明了2_8_0支持了order by和limit/offset的 push down。
2_7_0版本： 执行计划显示order by和limit没有push down到remote server，查询id为2对应的random_string字段值返回空。
test=> explain (analyze,verbose) select id,random_string from data_tbl order by id desc limit1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1030.00..1030.00 rows=1 width=40) (actual time=10.939..10.941 rows=1 loops=1) Output: id, random_string -> Sort (cost=1030.00..1032.50 rows=1000 width=40) (actual time=10.935..10.936 rows=1 loops=1) Output: id, random_string Sort Key: data_tbl.id DESC Sort Method: quicksort Memory: 89kB -> Foreign Scan on public.data_tbl (cost=25.00..1025.00 rows=1000 width=40) (actual time=10.861..10.866 rows=1 loops=1) Output: id, random_string Remote server startup cost: 25 Remote query: SELECT `id`, `random_string` FROM `test`.`data_tbl` Planning Time: 1.636 ms Execution Time: 13.692 ms (12rows) test=> select id,random_string from data_tbl where id=2; id | random_string ----+--------------- (0 rows) test=>
2_8_0版本： order by和limit都已push down至remote server，可以查到id为2对应的random_string字段值，且获取的field->max_length为65537
test=> explain (analyze,verbose) select id,random_string from data_tbl order by id desc limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.data_tbl (cost=1.00..2.00 rows=1 width=40) (actual time=7.654..7.665 rows=1 loops=1) Output: id, random_string Remote server startup cost: 25 Remote query: SELECT `id`, `random_string` FROM `test`.`data_tbl` ORDER BY `id` IS NOT NULL, `id` DESC LIMIT 1 Planning Time: 19.757 ms Execution Time: 10.775 ms (6rows) test=>
添加图片注释，不超过 140 字（可选）
总结
mysql_fdw_2_8_0版本之前对于text类型，如果数据长度超过MAXDATALEN即65536字节，就会将返回的结果从这行开始截断并丢弃，因此返回的数据是不完整的。
mysql_fdw_2_8_0版本修复了text类型长度超过65536查询结果被截断问题：https://github.com/EnterpriseDB/mysql_fdw/commit/4b1b14bebfafb760d63f569a712f7f7589643f75
同时也做了些优化，可以将order by和limit/offset push down至remote server中。
使用mysql_fdw_2_8_0以下版本的朋友，快升级吧。
往期链接：
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70033867/viewspace-3108348/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
pgstattuple源码探究
下一篇：
data corruption分析与处理
请登录后发表评论
登录
全部评论
