信创改造-mysql迁移数据到人大金仓问题纪要(二)

技术教程gslnedu2024-12-30 2:11:1414A⁺A^-

1.date_format(）函数需要改成人大金仓支持的函数 to_date('2023-09-10', 'yyyy-mm-dd')

2. mybatis配置文件中存在list时，报错Unable to make public int java.util.Arrays$ArrayList.size()

配置如下：

<if test="educationDegree != null and educationDegree.size() > 0 ">
    and (education_degree in
         <foreach collection="educationDegree" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>)
</if>

报错如下：

### Error querying database. Cause: java.lang.reflect.InaccessibleObjectException: Unable to make public int java.util.Arrays$ArrayList.size() accessible: module java.base does not "opens java.util" to unnamed module @302c971f ### Cause: java.lang.reflect.InaccessibleObjectException: Unable to make public int java.util.Arrays$ArrayList.size() accessible: module java.base does not "opens java.util" to unnamed module @302c971f

解决方案：

<if test="educationDegree != null and !educationDegree.isEmpty">
    and (education_degree in
         <foreach collection="educationDegree" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>)
</if>

原因是ArrayList.size() 是java内部函数，mybatis如果要调用的话，需要加如下参数：

你可以通过修改JVM启动参数，强制Java模块系统开放所需的包。具体来说，可以在启动JVM时添加以下参数：

--add-opens java.base/java.util=ALL-UNNAMED

错误: 函数 CONCAT(bigint, bigint) 不是唯一的

配置如下：

<if test="typeList != null and !typeList.isEmpty ">
  and CONCAT(dbao.disability_category,dbao.disability_level) in
    <foreach collection="typeList" item="item" open="(" close=")" separator=",">
         #{item}
    </foreach>
</if>

这个错误提示表明Kingbase数据库无法确定使用哪个CONCAT函数来连接两个bigint类型的列。为了解决这个问题，你需要显式地将bigint类型的列转换为字符串类型，然后再进行连接。

解决方案：

你可以使用CAST函数将bigint类型的列转换为VARCHAR类型，然后再进行连接。以下是修改后的SQL语句：

<if test="typeList != null and !typeList.isEmpty ">
  and CONCAT(CAST(dbao.disability_category AS VARCHAR), CAST(dbao.disability_level AS VARCHAR)) in
    <foreach collection="typeList" item="item" open="(" close=")" separator=",">
         #{item}
    </foreach>
</if>

解决迁移数据后，主键冲突问题

现象：迁移数据后，由于未执行过nextval，所以首次执行只会获取最大的主键值并不会加一，从而导致冲突。

解决方案：

DO $
DECLARE
seq_name text;
schema_name text;
table_name text;
column_name text;
max_value bigint;
BEGIN
FOR seq_name, schema_name, table_name, column_name IN
SELECT c.relname AS sequence_name, n.nspname AS schema_name, t.relname AS table_name, a.attname AS column_name
FROM pg_class c
JOIN pg_sequence s ON c.oid = s.seqrelid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_depend d ON d.objid = c.oid AND d.deptype = 'a'
JOIN pg_class t ON d.refobjid = t.oid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE c.relkind = 'S'
AND n.nspname NOT IN ('SYS_HM', 'other_restricted_schema') -- 添加其他受限模式
LOOP
BEGIN
-- 获取表的主键最大值
EXECUTE format('SELECT MAX(%I) FROM %I.%I', column_name, schema_name, table_name)
INTO max_value;
-- 如果表中没有数据，max_value 将为 NULL
IF max_value IS NULL THEN
max_value := 0;
END IF;
-- 设置序列的当前值为最大值加一
EXECUTE format('SELECT setval(''%I.%I'', %L)', schema_name, seq_name, max_value + 1);
RAISE NOTICE 'Sequence % in schema % set to %', seq_name, schema_name, max_value + 1;
EXCEPTION
WHEN undefined_table THEN
RAISE NOTICE 'Sequence % does not exist in schema %', seq_name, schema_name;
-- 创建缺失的序列
EXECUTE format('CREATE SEQUENCE %I.%I', schema_name, seq_name);
RAISE NOTICE 'Sequence % in schema % created', seq_name, schema_name;
END;
END LOOP;
END $;

在 Kingbase 中，::date 这种类型转换语法可能不被支持，导致的报错

报错现象：

Preparing: SELECT * FROM activity_user WHERE activity_start_time::date = CURRENT_DATE - 1 AND status = 1;
09:00:00.084 [quartzScheduler_Worker-1] ERROR c.a.d.f.s.StatFilter - [mergeSql,150] - merge sql error, dbType kingbase, druid-1.2.14, sql : SELECT *
FROM activity_user
WHERE activity_start_time::date = CURRENT_DATE - 1 AND status = 1;

问题分析：