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.