2

I'm trying to setup a simple replication from MySQL to Postgres. Identical schemas. After following the steps in the Demo Tutorial with a slight change (using MySQL and Postgres drivers) I am still unable to get the replication working.

A few changes were needed based on complaints after running bin/sym

  1. SET GLOBAL show_compatibility_56 = ON needed to be set in the MySQL DB
  2. For Postgres I needed to use protocolVersion=3 instead of 2 which was set in the example.

The weird thing is that SymmetricDS is able to create the sym_* tables, but complains about not being able to read them. I have verified that the tables do not exist before bin/sym is run, but do exist after. Here is an excerpt from the log

// Successful creation of table
[store-001] - PostgreSqlSymmetricDialect - DDL applied: CREATE TABLE "sym_notification"(
    "notification_id" VARCHAR(128) NOT NULL,
    ...
    PRIMARY KEY ("notification_id")
)

...

// Unable to read from created table
[store-001] - PostgreSqlDdlReader - Failed to read table: sym_notification
[store-001] - PostgreSqlDdlReader - Failed to read table: sym_notification
[store-001] - AbstractDatabaseWriter - Did not find the sym_notification table in the target database
[store-001] - PostgreSqlDdlReader - Failed to read table: sym_monitor
[store-001] - PostgreSqlDdlReader - Failed to read table: sym_monitor
[store-001] - AbstractDatabaseWriter - Did not find the sym_monitor table in the target database

The same error apply for all the sym_* tables.

The databases are running in Docker, but since SymmetricDS is not complaining about being unable to connect, and is able to create the tables, I assume it is not related to Docker.

The database in the Postgres DB is created by the same user as specified in engines/store-001.properties. Could this still have something to do with roles and access privileges?

hanshenrik
  • 199
  • 1
  • 11
  • Any error messages in the Postgres server log? – Nick Barnes Nov 30 '16 at 13:10
  • Yes, before each STATEMENT it logs, there is an error message: `ERROR: column am.amcanorder does not exist at character 427` @NickBarnes – hanshenrik Nov 30 '16 at 13:27
  • Looks like SymmetricDS doesn't support your version of Postgres. The `pg_am.amcanorder` column was [there in 9.5](https://www.postgresql.org/docs/9.5/static/catalog-pg-am.html), but was [removed in 9.6](https://www.postgresql.org/docs/9.6/static/catalog-pg-am.html). – Nick Barnes Nov 30 '16 at 13:33
  • Wait, is this error coming from the SymmetricDS session? That column name doesn't seem to be mentioned in their codebase. It might be caused by something unrelated. – Nick Barnes Nov 30 '16 at 13:44
  • Awesome, thanks! Replication works using Postgres 9.5.5 instead of 9.6.1 (latest tag on Docker). But the Postgres server log says `ERROR: relation "gp_id" does not exist at character 20 STATEMENT: select gpname from gp_id` for all 9.x versions, so I guess this could potentially cause trouble in the future. – hanshenrik Nov 30 '16 at 14:06
  • Yes, the error is triggered when I run `bin/sym`, which initiates replication from the MySQL source to the Postgres target. – hanshenrik Nov 30 '16 at 14:10
  • The `gp_id` error doesn't look like a problem. Judging by the [source](https://github.com/JumpMind/symmetric-ds/blob/0c5cc1c24b42a64405f4b79c3cb6b594a35467f2/symmetric-jdbc/src/main/java/org/jumpmind/db/platform/greenplum/GreenplumPlatform.java#L31), it's just testing to see if you're running Greenplum (a Postgres fork). – Nick Barnes Nov 30 '16 at 19:36

3 Answers3

2

If you upgrade to the latest JDBC driver from Postgres it will work.

Replace the existing Postgres driver from the lib directory from the latest from here: https://jdbc.postgresql.org/download.html

chenson42
  • 1,108
  • 6
  • 13
0

Try to connect to the postgres database with the same username/password used by symmetric-ds from some DB navigator, for example Jetbrain's Datagrip and then try inserting, updating, selecting something from sym_* tables. Assign access rights to the user if necessary.

Boris Pavlović
  • 63,078
  • 28
  • 122
  • 148
0

When using Postgres 9.6.1 (current latest release) the following error is logged on the server when running bin/sym

ERROR: column am.amcanorder does not exist at character 427

The problem was resolved by using Postgres 9.5.5 instead thanks to Nick Barnes pointing this out in a comment.

Community
  • 1
  • 1
hanshenrik
  • 199
  • 1
  • 11