-
Notifications
You must be signed in to change notification settings - Fork 2.2k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Bug Report: Do not use timestamps for schema_migrations and vreplication_log #17925
Comments
Hi! Putting some bullet numbers here so we can discuss each point separately.
|
Yes this is an external feature (that exists in or Vitess fork) that allows us to replicate tables to other instances of Vitess using binglogs.
Correct to be more complete we could update these tables, however our Cross-Vitess Replication doesn't support multi-sharded keyspaces at the moment, so these tables were not updated.
Correct, due to the mysqlbinlog bug, the line for
For our use, DATETIME is analogous with TIMESTAMP as we set everything to UTC, but I understand if someone is using timezones in a different way it would be an issue. The upside is DATETIME goes beyond 2038 :) Our goal is upstream all of our fixes at HubSpot, but if this minor change causes more choas that good, I'm okay with keeping this forked. Maybe a compromise is have some sort of dual-mode initializer that defaults to timestamp but can also use "datetime" if a flag is supplied... |
Overview of the Issue
At HubSpot we have a feature that allows us to sync data across different deployments of Vitess and is implemented by using MySQL binlogs.
When this was used in conjunction with Vitess migrations we were hitting the default timestamp bug whenever we run a new vitess migration, even though nothing we had created had default timestamps. We looked through the binlogs and sure enough, the vreplication tables have default timestamps...
After much debugging, we found that this happens whenever a CREATE TABLE/ALTER TABLE is run with a TIMESTAMP field (no matter how this was configured). The solution here is to create the column as a DATETIME field, which is virtually the same as TIMESTAMP for this use case.
Now when a vitess migration is started/stopped/completed, the CREATE TABLE/ALTER table statements are still run, but they're using datetime instead of timestamp, so the explicit_defaults_for_timestamp line does not make it into the binlogs.
Reproduction Steps
N/A
Binary Version
Operating System and Environment details
MYSQL_VERSION=8.0.33 `uname -sr` Linux 6.1.109-hs83.el9.x86_64
Log Fragments
The text was updated successfully, but these errors were encountered: