PG_chameleon during start replica turns columns into UNKNOWN_COL{number}

Question

Has anyone encountered this? I have setup an ubuntu instance in my WSL. Installed necessary venv for python and all packages needed for the pg_chameleon. I have this configuration right here.

# 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: ''


# postgres  destination connection
pg_conn:
  host: "5.16.255.254"
  port: "5432"
  user: "gisreyn"
  password: "samplepassword"
  database: "dbagwd_devtest"
  charset: "utf8"

sources:
  mysql:
    db_conn:
      host: "5.16.78.1"
      port: "3306"
      user: "gisbwd"
      password: "samplepassword"
      charset: 'utf8'
      connect_timeout: 10
    schema_mappings:
      water_db: sch_chameleon
    limit_tables:
      - water_db.master
    grant_select_to:
      - gisreyn
    lock_timeout: "120s"
    my_server_id: 1
    replica_batch_size: 500
    replay_max_rows: 10000
    batch_retention: '1 day'
    copy_max_memory: "300M"
    copy_mode: 'file'
    out_dir: /tmp
    sleep_loop: 10
    on_error_replay: continue
    on_error_read: continue
    auto_maintenance: "disabled"
    gtid_enable: false
    type: mysql
    keep_existing_schema: Yes
    skip_tables: []

so problem is, during the initialization, when I run this chameleon init_replica --config default --source mysql --debug, everything works fine until I run the start_replica, then when a change is detected and time for the replication process to start i encounter this:

'UPDATE sch_chameleon.master SET "UNKNOWN_COL0"=''18089'',"UNKNOWN_COL1"=''08612A0805'',"UNKNOWN_COL2"=''MARIETTA SANTOS'',"UNKNOWN_COL3"=''MARCOS HIGHWAY'',"UNKNOWN_COL4"=''A'',"UNKNOWN_COL5"=''2'',"UNKNOWN_COL6"=''4'',"UNKNOWN_COL7"='''',"UNKNOWN_COL8"='''',"UNKNOWN_COL9"=''6210160106'',"UNKNOWN_COL10"=''Q'',"UNKNOWN_COL11"=''2'',"UNKNOWN_COL12"=''2022-08-31'',"UNKNOWN_COL13"=''0.00'',"UNKNOWN_COL14"=''1896.00'',"UNKNOWN_COL15"=NULL,"UNKNOWN_COL16"=''1'',"UNKNOWN_COL17"=''1895.00'',"UNKNOWN_COL18"=''0170170'',"UNKNOWN_COL19"=''Q'',"UNKNOWN_COL20"=''2'',"UNKNOWN_COL21"=''2011-11-20'',"UNKNOWN_COL22"=''0.00'',"UNKNOWN_COL23"=''0.00'',"UNKNOWN_COL24"=''1'',"UNKNOWN_COL25"=''0150024'',"UNKNOWN_COL26"=''Q'',"UNKNOWN_COL27"=''2'',"UNKNOWN_COL28"=''2011-06-13'',"UNKNOWN_COL29"=''0.00'',"UNKNOWN_COL30"=''0.00'',"UNKNOWN_COL31"=''1'',"UNKNOWN_COL32"=''RMA020320240308'',"UNKNOWN_COL33"=''2024-01-04'',"UNKNOWN_COL34"=''2024-02-03'',"UNKNOWN_COL35"=''254.00'',"UNKNOWN_COL36"=''267.00'',"UNKNOWN_COL37"=''13.00'',"UNKNOWN_COL38"=''2024-02-03'',"UNKNOWN_COL39"=''0.00'',"UNKNOWN_COL40"=''0.00'',"UNKNOWN_COL41"=''0.00'',"UNKNOWN_COL42"=''0.00'',"UNKNOWN_COL43"=''491.50'',"UNKNOWN_COL44"=''0.00'',"UNKNOWN_COL45"=''0.00'',"UNKNOWN_COL46"=''0.00'',"UNKNOWN_COL47"=''0.00'',"UNKNOWN_COL48"=''530.10'',"UNKNOWN_COL49"=''-491.00'',"UNKNOWN_COL50"=''2024-02-12'',"UNKNOWN_COL51"=''0.50'',"UNKNOWN_COL52"=''15.00'',"UNKNOWN_COL53"=''14.00'',"UNKNOWN_COL54"=''16.000'',"UNKNOWN_COL55"=''3'',"UNKNOWN_COL56"=''0'',"UNKNOWN_COL57"=''A1SS13'',"UNKNOWN_COL58"=''8'',"UNKNOWN_COL59"=''1800-01-01'',"UNKNOWN_COL60"=NULL,"UNKNOWN_COL61"=''0.00'',"UNKNOWN_COL62"=''-21.40'',"UNKNOWN_COL63"=''0.00'',"UNKNOWN_COL64"=''0.00'',"UNKNOWN_COL65"=''0.00'',"UNKNOWN_COL66"=''0.00'',"UNKNOWN_COL67"=''0.00'',"UNKNOWN_COL68"=''0.00'',"UNKNOWN_COL69"=''0.00'',"UNKNOWN_COL70"=''0.00'',"UNKNOWN_COL71"=''0.00'',"UNKNOWN_COL72"=''0.00'',"UNKNOWN_COL73"=''0.00'',"UNKNOWN_COL74"=''0.00'',"UNKNOWN_COL75"=''0.00'',"UNKNOWN_COL76"=''0.00'',"UNKNOWN_COL77"=''0.00'',"UNKNOWN_COL78"=''0.00'',"UNKNOWN_COL79"=''0.00'',"UNKNOWN_COL80"=''0.00'',"UNKNOWN_COL81"=''0864'',"UNKNOWN_COL82"=''0805'',"UNKNOWN_COL83"=''1'',"UNKNOWN_COL84"=''0'',"UNKNOWN_COL85"=''0'',"UNKNOWN_COL86"=NULL,"UNKNOWN_COL87"=''202402'',"UNKNOWN_COL88"='''',"UNKNOWN_COL89"=''1'',"UNKNOWN_COL90"=''0864'',"UNKNOWN_COL91"=''0805'',"UNKNOWN_COL92"=''0.00'',"UNKNOWN_COL93"=''0.00'',"UNKNOWN_COL94"=''0860805'',"UNKNOWN_COL95"=''0.00'',"UNKNOWN_COL96"=''0.00'',"UNKNOWN_COL97"=NULL,"UNKNOWN_COL98"=''2024-02-19'',"UNKNOWN_COL99"=''674.00'',"UNKNOWN_COL100"=''0.00'',"UNKNOWN_COL101"=''0'',"UNKNOWN_COL102"=''-0.90'',"UNKNOWN_COL103"=''1'',"UNKNOWN_COL104"=''0.00'',"UNKNOWN_COL105"=''0.0'',"UNKNOWN_COL106"=''0.0'',"UNKNOWN_COL107"=''09279860252'',"UNKNOWN_COL108"=''SANTOS, MARIETTA'',"UNKNOWN_COL109"=''2024-01-03 03:05:40'' WHERE reference_id=NULL;'

when I dont have a NULL value in the reference_id column in mysql. Please help. The initialization works fine and copies everything from the mysql to postgresql so I thought the pg_chameleon is able to map the columns from the mysql table.

I am expecting for the pg_chameleon to be able to replicate my mysql table to postgresql table

Answer 1

TL;DR> binlog_row_metadata must be set to FULL

Just encoutered the same error and after digging a while I figured out a missing mysql parameter to change that is not checked in the __check_mysql_config function of the mysql_lib module available in the pg_chameleon documentation:

binlog_row_metadata must also (as binlog_row_image) be set to FULL.

This is indicated in the python-mysql-replication used by pg_chameleon:

• in the MySQL server settings section of the README.
• and in the code (not sure this is the correct version used by pg_chameleon, but it's the same code than the one available in my pg_chameleon virtual env directory).

And indeed, after making this change in mysql, the replication was ok for me and the "unknown column" error was fixed.