Proposal for New Ghost Feature Integration

[BarShauli555]

Hi Team,

How are you?

I'm Bar from the Wix DBA team. A year ago, I added a new ghost feature to our private GitHub repository, and we would like to contribute it to this entire project.

Our main goal is to safely delete data and rebuild the table using ghost. We can implement this in two ways:

1. When changing the DDL of this table
2. When we want to delete old data using the ALTER command: ENGINE=InnoDB

I have added a new PR for this feature, and the code is currently running in our production environment and testing.

The PR includes:
A new WHERE clause statement, defaulting to 1=1. Here’s an example of filter operations:

Comparison operators: “<, >, =, !=”

1. data_created > year('2020')
2. id >= 100
3. status IN ('done') # Please use a maximum of 5 values for IN; otherwise, use a temp table.

Sub-select from a temp table:

1. id IN (SELECT id FROM temp_id) - Preferred for primary keys; otherwise, it’s a heavy process.
2. varchar_column IN (SELECT varchar_column FROM varchar_temp_table)

Adding the receiving condition to the SQL builder mechanism that filters in the data according to our additional statement in the WHERE clause.

Adding tests.

Please note: This deletion only occurs from the chunk size; if any DML is inserted during the ghost operation and is written to the binlog, it will stop the entire run.

Thank you for your attention. I look forward to your feedback!

Best regards,
Bar

Let me know what you are thinking :-)