vitess: Bug Report: PITR from timestamp or position not working

Overview of the Issue

Doing incremental(builtin method)/full(xtrabackup) method. Failing PITR using timestamp or position. K8S deployment with operator.

Reproduction Steps

  1. Deploy the following vschema:
{
    "tables": {
        "product": {},
        "customer": {},
        "corder": {}
    }
}
  1. Deploy Schema:
create table if not exists product(
  sku varbinary(128),
  description varbinary(128),
  price bigint,
  primary key(sku)
) ENGINE=InnoDB;
create table if not exists customer(
  customer_id bigint not null auto_increment,
  email varbinary(128),
  primary key(customer_id)
) ENGINE=InnoDB;
create table if not exists corder(
  order_id bigint not null auto_increment,
  customer_id bigint,
  sku varbinary(128),
  price bigint,
  primary key(order_id)
) ENGINE=InnoDB;
  1. Run full backup manually for the first time and then run cron with every 12h full backup and incrementals every hour. Add cron to insert random data into tables every 30min which will ensure incrementals wouldn’t fail (they fail if there is no change in db from the last backup)

  2. Try to PITR from timestamp vtctldclient RestoreFromBackup --restore-to-timestamp "timestamp" tablet-alias

  3. View the error

Binary Version

vitess version v18.0.1
latest tag for vitess operator

Operating System and Environment details

K8S environment

Log Fragments

Here is the log output when running PITR from timestamp:

https://gist.github.com/GenLN/7b9ba323aee7778390a9269379b16c2d

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 37 (23 by maintainers)

Most upvoted comments

Why don’t you try https://github.com/vitessio/vitess/pull/15440, see if that works?

Would you mind creating a new Issue re: the need to include a mysqlbinlog binary in a k8s deployment? This would be a documentation change.

Thank you for explaining. To be more precise:

The same bug is hitting if used official mysql image

This is an altogether different issue, it is about not having mysqlbinlog binary. The issue we’re having in this Issue is that the restore process places binlog files in a directory that is not shared between tablet and mysqld pods.

Looks good! OK, we need to decide what the actual solution should be. As far as I can see, it’s either https://github.com/vitessio/vitess/pull/15440, or as @mattlord suggested, add a command line flag for you to configure the path yourself.

@mattlord I think you’re referencing an unrelated setting. The path we’re using is generated by vitess, not by MySQL. Vitess reads a backup manifest, then copies over the backup files (binary logs in our case) under said generated path, then requests mysqld to run mysqlbinlog using those files.

Moreover, this is done by the built-in engine, not xtrabackup engine. I can add a configuration in builtin engine as an alternative to https://github.com/vitessio/vitess/pull/15440.

The xtrabackupengine uses the tmpdir value in the my.cnf file: https://github.com/vitessio/vitess/blob/main/go/vt/mysqlctl/xtrabackupengine.go

https://dev.mysql.com/doc/refman/8.0/en/temporary-files.html

That defaults to /tmp. You could change that to /vt/tmp here in your cluster definition: https://github.com/planetscale/vitess-operator/blob/main/docs/api.md#mysqldspec