pt-online-schema-change loses data when changing a generated column to a regular column
General
Escalation
General
Escalation
Description
Environment
Platform: MacOS 14.7
MySQL - v8.0.39 Homebrew
pt-online-schema-change - 3.6.0 Homebrew
Activity
Show:
Aaditya Dubey November 6, 2024 at 3:51 PM
Hi
Thank you for the report.
It is verified as described.
Details
Details
Assignee
Unassigned
UnassignedReporter
Jackie Xu
Jackie XuPriority
Components
Affects versions
Labels
Needs QA
Yes
Smart Checklist
Open Smart Checklist
Smart Checklist

Open Smart Checklist
Created October 10, 2024 at 8:10 PM
Updated November 6, 2024 at 3:52 PM
We recently suffered an issue with
pt-online-schema-change
causing an entire column to be lost when we changed it from aSTORED
generated column to a regular column.The process of turning a generated column with the
STORED
attribute back to a regular column is supported under the MySQL specs forALTER TABLE
and works as follow according to its docsbut the
pt-online-schema-change
will cause this column to be lost, likely due to generated columns being ignored as stated in its documentation:Expected behavior
Existing data of the column is preserved, exactly as described by the MySQL specs
Actual behavior
Existing data is lost and turned into
NULL
(or whatever default value is set)Steps to reproduce with a simple example (cli version 3.6.0, MySQL v8.0.39)
1. Create the follow test database and table in and insert some test values
Verify its properties
Query the table to verify the column data
Change the column to a non-generated (regular) column for
squared
usingpt-online-schema-change
Describe table to check the schema itself has been changed successfully
Query from the table again, and notice the data is lost and is now all
NULL
Repeat the test with a regular MySQL
ALTER TABLE
DDL operation and see that the data is not lostPlease help address this issue as it poses a problem to schema rollbacks not being possible without losing the data, here’s what happened in our production db:
We turned a regular column to a generated columns → No issues
We needed to revert back the schema change due to some other reason → Data is lost
Our DB is now in a corrupted state