Hi everyone, we are using an elt pipeline to move data from MySql (tap-mysql) to BigQuery (target-bigquery, variant: adswerve), and we're encountering some strange behavior while using "LOG BASED" as replication method. Using the command:

meltano elt --job_id=<job_name> tap-mysql target-csv

everything works correctly, the "payload" field on the database (postgreSQL) follows the format:

{
  "singer_state": {
    "currently_syncing": null,
    "bookmarks": {
      "database_name-table_name": {
        "version": 1615393814139,
        "log_file": "mysql-bin.000375",
        "log_pos": 17165039
      }
    }
  }
}

Meanwhile when using the command:

meltano elt --job_id=<job_name> tap-mysql target-bigquery

it appears the pipeline works only on the initial import, and fails to catch updates on the database table. The "payload" field on the database (postgreSQL) follows the format:

{
  "singer_state": {
    "currently_syncing": null,
    "bookmarks": {
      "database_name-table_name": {
        "last_pk_fetched": {
          "entity_id": 40314
        },
        "log_file": "mysql-bin.000375,
        "log_pos": 17556051,
        "max_pk_values": {
          "entity_id": 40314
        },
        "version": 1615394206653
      }
    }
  }
}

Apparently if we manually remove the additional keys "last_pk_fetched" and "max_pk_values", the target-bigquery works as expected. Any idea why these values ​​show up in the database? Thank you in advance 🙂

Hey, @rocco_melillo. Do you mind logging an issue for us to make sure this gets resolved? In the past, I’ve observed the

lask_pk_fetched

and

max_pk_values

being logged as basically an extra bookmark indicating status of an in-progress batch, but in most cases, the tap would send a final STATE message without those values, essentially attempting to clear them. When replication_method is LOG_BASED, those

pk

values should be information only and should not have a role in replication. However, if replication key happens to also be set to the pk within the metadata, that might be something we’d want to look at - if the replication_key is perhaps conflicting with replication_method.

Hi @aaronsteers, this code doesn't seem to work:

# clear max pk value and last pk fetched upon successful sync
singer.clear_bookmark(state, catalog_entry.tap_stream_id, 'max_pk_values')
singer.clear_bookmark(state, catalog_entry.tap_stream_id, 'last_pk_fetched')

those values are not cleared. attached the log file of the first run.

@rocco_melillo - Thanks for posting this log. It is probably worth opening an issue against the tap-mysql repo here. If that bookmark is not properly being reset, then it could cause an attempt to “resume” the initial full sync, rather than to proceed with log-based replication from the last point. And also would be helpful for debugging would be at least the initial part of the log for the second execution. (If not already contained in the log you posted, that is.)

Hi @aaronsteers, I would like to inform you that I solved it by creating an ad-hoc script that deletes those values from the database after running the first elt. Then everything works fine but I'm not satisfied😅. It seems to fail only with target-bigquery 🤔 anyway.. I'm not using pipelinewise variant of tap-mysql. I'm using this: https://github.com/singer-io/tap-mysql I am attaching the log file of the second run thank you