--where clause incorrectly applied to child tables in pt-online-schema-change (causes “unknown column” error)

[rukbat]

Hello,

when using pt-online-schema-change with the --where option on a table that has foreign-key child tables, the tool crashes during the row-estimate phase for each child.

Steps to reproduce:

Create two tables with a foreign-key relationship, e.g.:

CREATE TABLE parent (
    id INT PRIMARY KEY,
    status VARCHAR(10)
);

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

Populate both tables with sample data.

Run:

pt-online-schema-change
–execute
–force
–alter-foreign-keys-method auto
–alter “ENGINE=InnoDB”
–where “status = ‘active’”
D=dbname,t=parent

Observe the failure with an error similar to:

# NibbleIterator:6302 10847 EXPLAIN SELECT * FROM `dbname`.`child` WHERE status = ‘active’

DBD::mysql::db selectrow_hashref failed: fetch() without execute() [for Statement “EXPLAIN SELECT * FROM `dbname`.`child` WHERE status = ‘active’”] at /opt/percona-toolkit-3.7.0-2/bin/pt-online-schema-change line 6303.

Because the WHERE expression references columns that exist only in the parent table, MySQL returns `ERROR 1054 (42S22): Unknown column ‘’ in ‘where clause’`. This aborts the migration even though the parent table itself would process fine.

It looks like a bug, but I’m unable to open a ticket because Jira appears to be experiencing an issue and won’t let me create a new ticket (ref: https://status.atlassian.com/).

[Vinodh_Krishnaswamy]

Hi @rukbat ,

Welcome to the community!

@rukbat

Because the WHERE expression references columns that exist only in the parent table, MySQL returns ERROR 1054 (42S22): Unknown column ‘’ in ‘where clause’. This aborts the migration even though the parent table itself would process fine.

While your test proves the same (and I could repeat the same), why do you want to use “–where” option in this case? As per the documentation, it is not advised to use as it may cause data loss.

Percona Toolkit Documentation

Warning! If used without options --no-drop-new-table and --no-swap-tables may lead to data loss, therefore this operation only allowed if option --force also specified.

I tested with those options and could repeat the same issue:

$ pt-online-schema-change  --dry-run --alter-foreign-keys-method auto --alter "ENGINE=InnoDB" --where "status='active'" --no-drop-new-table  --no-swap-tables  D=vinodh,t=parent,h=127.0.0.1,P=8041,u=msandbox,p=msandbox
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
DBD::mysql::db selectrow_hashref failed: Unknown column 'status' in 'where clause' [for Statement "EXPLAIN SELECT * FROM `vinodh`.`child` WHERE status='active'"] at /usr/bin/pt-online-schema-change line 6303.

At the same time, I have tested without –where and –force options, the tool works as expected:

$ pt-online-schema-change  --dry-run --alter-foreign-keys-method auto --alter "ENGINE=InnoDB"    D=vinodh,t=parent,h=127.0.0.1,P=8041,u=msandbox,p=msandbox --force
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `vinodh`.`child` (approx. 10051 rows)
Will automatically choose the method to update foreign keys.
Starting a dry run.  `vinodh`.`parent` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table vinodh._parent_new OK.
Altering new table...
Altered `vinodh`.`_parent_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not determining the method to update foreign keys because this is a dry run.
Not updating foreign key constraints because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2025-09-24T17:20:46 Dropping new table...
2025-09-24T17:20:46 Dropped new table OK.
Dry run complete.  `vinodh`.`parent` was not altered.

However, I have created a bug for the same as it is not the expected behaviour - Jira. Thanks for reporting to us.

Regards,

Vinodh Guruji