pt-online-schema-change loses data when changing a generated column to a regular column

Description

We recently suffered an issue with pt-online-schema-change causing an entire column to be lost when we changed it from a STORED 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 for ALTER TABLEand works as follow according to its docs

Stored but not virtual generated columns can be altered to nongenerated columns. The stored generated values become the values of the nongenerated column.

but the pt-online-schema-change will cause this column to be lost, likely due to generated columns being ignored as stated in its documentation:

The tools ignores MySQL 5.7+ GENERATED columns since the value for those columns is generated according to the expression used to compute column values.

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

  1. Verify its properties

  1. Query the table to verify the column data

  1. Change the column to a non-generated (regular) column for squared using pt-online-schema-change

  1. Describe table to check the schema itself has been changed successfully

  1. Query from the table again, and notice the data is lost and is now all NULL

  1. Repeat the test with a regular MySQL ALTER TABLE DDL operation and see that the data is not lost

Please 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:

  1. We turned a regular column to a generated columns → No issues

  2. We needed to revert back the schema change due to some other reason → Data is lost

  3. Our DB is now in a corrupted state

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

Assignee

Reporter

Priority

Affects versions

Needs QA

Yes

Smart Checklist

Created October 10, 2024 at 8:10 PM
Updated November 6, 2024 at 3:52 PM