Search code examples
javadatabaseh2symmetricds

SymmetricDS length for type varchar cannot exceed N

so I'm trying to use symmetricDS for replicating java h2 database to postgres. I'm using the zip file simple configuration. Here is what happen. I followed the getting started guide, download the symmetricds, and try the demo, then I tried with my own configuration with some table in the trigger. But:

  • If I replicate the table without varchar field in h2 it works perfectly fine.
  • If I have a table that has varchar field in it, it crash during creating the table.

JdbcSqlTemplate - ERROR: length for type varchar cannot exceed 10485760 Position: 161. Failed to execute: CREATE TABLE "asset"( "db_id" BIGINT NOT NULL DEFAULT nextval('"asset_db_id_seq"'), "id" BIGINT NOT NULL, "account_id" BIGINT NOT NULL, "name" VARCHAR(2147483647) NOT NULL, "description" VARCHAR(2147483647), "quantity" BIGINT NOT NULL, "decimals" SMALLINT NOT NULL, "initial_quantity" BIGINT NOT NULL, "height" INTEGER NOT NULL, "latest" BOOLEAN DEFAULT 'TRUE' NOT NULL, PRIMARY KEY ("db_id") )

indeed a clear error saying the varchar should not exceed 255, but that's how the source database is, is there anyway to force any varchar to TEXT type? or are there any other way around this? or is this a bug in symmetricds has yet to be solved?

Thanks.

Solution

  • I managed to go way around this by creating the table on target database manually. Here is what I did before running bin/sym.

    • generate query for table I want to create using dbexport by bin/dbexport --engine corp-000 --compatible=postgres --no-data table_a table_b > samples/create_asset_and_trade.sql
    • modify the flaw in generated query file samples/create_asset_and_trade.sql. in my case it's the length of the varchar.
    • after fixing that, run the generated query to fill in the target database using dbimport. bin/dbimport --engine store-001 samples/create_asset_and_trade.sql.
    • running bin/sym should be okay now, it'll detect that the table is already created, and skip the table creation step.

    This is not the ideal way, but it should work for now.