Skip to content

Holo-shipper工具在同步带有自增序列Serial类型字段的表会失败 #39

@Staroon

Description

@Staroon

在使用 Holo-shipper 工具进行实例间数据库表结构同步的时候,发现有部分表同步失败:
具体报错日志:

[embedded-holo-client-worker] WARN com.alibaba.hologres.client.impl.ConnectionHolder - execute sql fail, try again[1/3], sleepMs = 1000 ms
com.alibaba.hologres.org.postgresql.util.PSQLException: ERROR: invalid name syntax
        at com.alibaba.hologres.org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2565)
        at com.alibaba.hologres.org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2297)
        at com.alibaba.hologres.org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
        at com.alibaba.hologres.org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
        at com.alibaba.hologres.org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
        at com.alibaba.hologres.org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
        at com.alibaba.hologres.org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
        at com.alibaba.hologres.org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
        at com.alibaba.hologres.org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:258)
        at com.alibaba.hologres.shipper.holo.HoloTable.lambda$setTableDDL$9(HoloTable.java:241)
        at com.alibaba.hologres.client.impl.handler.SqlActionHandler.lambda$handle$0(SqlActionHandler.java:29)
        at com.alibaba.hologres.client.impl.ConnectionHolder.doRetryExecute(ConnectionHolder.java:269)
        at com.alibaba.hologres.client.impl.ConnectionHolder.retryExecute(ConnectionHolder.java:243)
        at com.alibaba.hologres.client.impl.handler.SqlActionHandler.handle(SqlActionHandler.java:29)
        at com.alibaba.hologres.client.impl.handler.SqlActionHandler.handle(SqlActionHandler.java:15)
        at com.alibaba.hologres.client.impl.Worker.handle(Worker.java:94)
        at com.alibaba.hologres.client.impl.Worker.run(Worker.java:123)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

排查发现这部分表都带有自增序列类型的字段,使用hg_dump_script获取到的建表语句无法直接在新实例上执行:

CREATE TABLE xxx.xxxxx (
    id integer NOT NULL default nextval('xxx.xxxxx'::regclass),
    ...
);

需要手动将建表语句中的自增序列字段更改为serial方可建表成功:

CREATE TABLE xxx.xxxxx (
    id serial NOT NULL,
    ...
);

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions