Updates to MySQL tables are not reflecting in PostgeSQL tables with pg_chameleon

65 Views Asked by At

I have successfully set up my replication from my MySQL database to my PostgreSQL database using pg_chameleon. However, whenever I insert or update one of my tables on the MySQL database, I get the following error:

replay_replica ERROR: There was an error during the replay of data. ergoscales.kalibrasie_kaarte 
SQLSTATE: 42703 - ERROR MESSAGE: column "null" of relation "kalibrasie_kaarte" does not exist. The affected tables are no longer replicated.

I don't understand the error because there is no null column, and I have ensure the table structures are the same in both databases. I've been using a smaller table as my main tester, and you can see from the create statements, that the table structures are correct:

MySQL:

CREATE TABLE `kalibrasie_kaarte` (
  `id` int NOT NULL AUTO_INCREMENT,
  `Card_Number` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb3;

PostgreSQL:

CREATE TABLE IF NOT EXISTS ergoscales.kalibrasie_kaarte
(
    id bigint NOT NULL DEFAULT 'nextval('ergoscales.kalibrasie_kaarte_id_seq'::regclass)',
    "Card_Number" integer,
    CONSTRAINT kalibrasie_kaarte_pkey PRIMARY KEY (id)
)

This is my pg_chameleon config file, I am unsure if the issue is lying in here somewhere. Is it perhaps the type_override?

#global settings
pid_dir: '~/.pg_chameleon/pid/'
log_dir: '~/.pg_chameleon/logs/'
log_dest: file
log_level: info
log_days_keep: 10
rollbar_key: ''
rollbar_env: ''

# type_override allows the user to override the default type conversion into a different one.
type_override:
  "tinyint(1)":
    override_to: boolean
    override_tables:
      - "*"

#postgres  destination connection
pg_conn:
  host: "localhost"
  port: "5432"
  user: "user"
  password: "password"
  database: "ergoscales"
  charset: "utf8"

sources:
  mysql:
    db_conn:
      host: "localhost"
      port: "3306"
      user: "user"
      password: "password"
      charset: 'utf8'
      connect_timeout: 10
    schema_mappings:
      ergoscales: ergoscales
    limit_tables:
    skip_tables:
    grant_select_to:
    lock_timeout: "120s"
    my_server_id: 100
    replica_batch_size: 10000
    replay_max_rows: 10000
    batch_retention: '1 day'
    copy_max_memory: "300M"
    copy_mode: 'file'
    out_dir: /tmp
    sleep_loop: 1
    on_error_replay: continue
    on_error_read: continue
    auto_maintenance: "disabled"
    gtid_enable: No
    type: mysql
    skip_events:
      insert:
      delete:
      update:

I also tried re-enabling the replication for the specific table manually. Works once, but back to square 1 after updating MySQL table again.

0

There are 0 best solutions below