CODEWITHSUNDEEP
postgresqldockersymmetricds
hanshenrik
hanshenrik

Reputation: 209

SymmetricDS Postgres target gives "Failed to read table" for all sym_* tables

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?

Upvotes: 2

Views: 778

Answers (3)

chenson42
chenson42

Reputation: 1108

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

Upvotes: 2

hanshenrik
hanshenrik

Reputation: 209

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.

Upvotes: 0

Boris Pavlović
Boris Pavlović

Reputation: 64632

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.

Upvotes: 0

Related Questions