告别老旧mysql_fdw，升级正当时

MySQL_fdw简介

mysql_fdw是PG的扩展，提供了 MySQL 服务器和 PostgreSQL数据库之间的接口。它将 PostgreSQL语句 ( SELECT/ INSERT/ DELETE/ UPDATE) 转换为 MySQL 数据库可以理解的查询。

简单总结下原理：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
Create Table: 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 in set (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字节。

mysql_convert_to_pg函数中同样新增了TEXT类型的处理。

那么执行结果的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

我们看下2.8.0版本中的变化：

mysqlBeginForeignScan函数中调用mysql_stmt_attr_set(festate->stmt,STMT_ATTR_UPDATE_MAX_LENGTH,&festate->has_var_size_col);

bind_stmt_params_and_exec函数中调用mysql_stmt_store_result获得field->max_length，遍历festate->retrieved_attrs，调用mysql_bind_result处理结果字段长度为field->max_length

修复方案解读完毕，我们看下实际执行过程做下验证。

可以看到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 limit 1;
                                                            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
(12 rows)

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
(6 rows)

test=>

总结

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以下版本的朋友，快升级吧。