MySQL迁移到MogDB典型问题汇编

本文将持续对MySQL迁移到MogDB数据库的典型问题进行汇编收录。

• 复合分区KEY分区问题(可替换为HASH分区)
• LIST分区键含表达式问题(暂不支持表达式)
• 表字段枚举类型空串问题(可用domain改写)
• 无符号整型使用问题(使用bigint替换)
• SELECT DISTINCT与ORDER BY问题(设置dolphin参数)
• zerofill属性问题

Q1.复合分区KEY分区问题

使用MTK 2.9.2迁移MySQL分区表到MogDB时，遇到如下报错：

MySQL-2008: MySQL Not Support SubPart Type KEY.

MySQL分区表结构如下：

CREATE TABLE t1 (
    a varchar(30) NOT NULL,
    b varchar(11) NOT NULL,
    c datetime NOT NULL,
    d varchar(100) DEFAULT NULL,
    UNIQUE KEY t_uq_idx (a,d,c,b),
    INDEX t_b_idx (b)
)
PARTITION BY RANGE (to_days(c))
SUBPARTITION BY KEY (b)
SUBPARTITIONS 9
(
    PARTITION p20230624 VALUES LESS THAN (739060),
    PARTITION p20230625 VALUES LESS THAN (739061),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

分析报错信息以及测试定位发现MySQL建表语句复合分区语法SUBPARTITION BY KEY暂不支持迁移，而一级分区PARTITION BY KEY可以迁移。

下面的t2表直接使用KEY分区，MTK可以正常迁移。

CREATE TABLE t2 (
    s1 char(32) PRIMARY KEY
) 
PARTITION BY KEY(s1)
PARTITIONS 10;

但最上面的t1表，KEY分区如果与其他分区(例如range分区)组合进行复合分区时就会出现上面的报错。

MTK支持复合分区使用HASH分区，源端KEY分区可以转换为HASH分区，不过HASH分区限制分区键必须有一列或多列包含整数值。

Q2.LIST分区键含表达式问题

MySQL的LIST分区键如果使用了表达式，例如下面的BY LIST (MONTH(sync_date))：

CREATE TABLE `user_t` ( `user_id` VARCHAR(100) , `sync_date` DATE NOT NULL ) PARTITION BY LIST (MONTH(sync_date)) ( PARTITION `p1` VALUES IN (1), PARTITION `p2` VALUES IN (2), PARTITION `p3` VALUES IN (3), PARTITION `p4` VALUES IN (4), PARTITION `p5` VALUES IN (5), PARTITION `p6` VALUES IN (6), PARTITION `p7` VALUES IN (7), PARTITION `p8` VALUES IN (8), PARTITION `p9` VALUES IN (9), PARTITION `p10` VALUES IN (10), PARTITION `p11` VALUES IN (11), PARTITION `p12` VALUES IN (12) );

使用MTK 2.9.2进行迁移时会提示如下错误：

create table code: 42601 msg:syntax error at or near "("

MTK读取源SQL时把PARTITION BY LIST (MONTH(sync_date))改写为PARTITION BY LIST COLUMNS (MONTH(sync_date))，如果使用本地环境进行模拟时需要去掉COLUMNS关键字。

同时PARTITION BY LIST (MONTH(sync_date))分区键由于使用了表达式，目标端创建时提示42601的错误代码，暂不支持List分区键含表达式。

PG的List分区键支持表达式，不过MySQL的MONTH函数需要进行改写，示例如下：

CREATE TABLE user_t( userid varchar, sync_date date ) PARTITION BY LIST(extract(mon from sync_date)); ...

Q3.表字段枚举类型空串问题

从MySQL迁移如下表结构到MogDB

CREATE TABLE `t1` (
`b` ENUM('','ANY','X509','SPECIFIED') NOT NULL DEFAULT ''
);

提示如下错误信息：

CREATE TYPE t1_b_type AS ENUM ('','ANY','X509','SPECIFIED') code: 42602 msg:invalid enum label "" Detail: Labels must contain 1 to 63 characters.

从日志信息可以看出，MySQL的枚举类型迁移到MogDB时，语法转化为：

CREATE TYPE ... AS ENUM ...

不过在MogDB里，枚举类型的值长度不能为0，不允许为空串，下面可以模拟复现：

MogDB=# CREATE TYPE type1 AS ENUM ('','a','b','c');
ERROR:  invalid enum label ""
DETAIL:  Labels must contain 1 to 63 characters.

虽然枚举类型不允许使用空串，但我们可以使用domain域对象进行改写：

CREATE DOMAIN type1 AS varchar
CHECK (value = ANY('{"", "ANY", "X509","SPECIFIED"}') )
;

先创建type1类型，然后在MogDB使用type1类型替代枚举类型即可创建成功

CREATE TABLE test (
a int NOT NULL DEFAULT 0,
b type1
);

Q4.无符号整型使用问题

MySQL的无符号整型在MogDB 3.1及以上版本得到了原生支持，参考如下：

新增UNSIGNED INT/TINYINT/SMALLINT/BIGINT类型，与普通整型相比，其最高位是数字位而非符号位；

https://docs.mogdb.io/zh/mogdb/v5.0/dolphin-numeric-types

注意：必须在创建数据库时，选择dbcompatibility为B，然后通过gsql可以正常查询显示UNSIGNED INT/TINYINT/SMALLINT/BIGINT类型。

Mogeaver 23.1.1以下版本查询无符号整型时会出现如下错误：

Error coverting string to composite type

Mogeaver升级到23.1.1及以上版本解决了该错误，可以正常显示。

不过使用openGauss-jdbc驱动3.0版本查询无符号整型时结果为NULL，jdbc驱动还未适配无符号整型。

目前可以使用如下语句，进行动态拼接，修改为bigint类型。

SELECT 'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name || ' TYPE bigint;' FROM information_schema.columns WHERE data_type LIKE 'uint%';

Q5.SELECT DISTINCT与ORDER BY问题

下面的两个SQL语句迁移到MogDB数据库后提示如下错误：

ERROR：for SELECT DISTINCT，ORDER BY expressions must appear in select list

场景语句一

select distinct id 
from t 
order by create_at;

场景语句二

select distinct t1.num inum
from tab1 t1
join tab2 t2
on t2.code = t1.code
group by t1.num,t2.type
order by num;  

order by后面的字段或者表达式必须出现在select distinct列表中，这是SQL标准行为。

不过在MogDB 5.0版本里，可以使用MySQL兼容性插件dolphin可以通过设置dolphin.sql_mode参数来兼容这种非标准行为。

方法是在MySQL兼容性数据库，通过修改dolphin.sql_mode参数，去掉sql_mode_full_group即可，操作示例如下：

ALTER DATABASE mydb SET dolphin.sql_mode to sql_mode_strict,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length ;

dolphin.sql_mode参数默认值是带sql_mode_full_group选项的，上面的语句去掉了sql_mode_full_group选项。

修改dolphin.sql_mode参数之后，使用新的session进行测试，上面的两个语句可以正常执行。

Q6.zerofill属性问题

MySQL数据库中，创建表时，可以指定zerofill属性，它通常用于在数字字段中填充前导零，可以确保了数字具有相同的位数，方便排序和比较。

例如下面的tab表

CREATE TABLE tab (
id INT(4) ZEROFILL AUTO_INCREMENT PRIMARY KEY,
...
);

使用int(4) zerofill属性，当插入数字100时，它将自动填充为0100。

但zerofill属性迁移到目标端openGauss数据库后，目标端该字段数据为0，其他字段值正常。

zerofill属性在openGauss5.0增加语法上的支持，实际并没有填充零。

参考链接如下：

https://docs.opengauss.org/zh/docs/5.0.0/docs/ExtensionReference/dolphin-%E6%95%B0%E5%80%BC%E7%B1%BB%E5%9E%8B.html