Add "algorithm" support to db_schema.xml

[qsolutions-pl]

Description

A lot of 3rd party extensions add extra columns to core tables and it is prolonging the deployment process and causing a huge downtime for production sites.

I recommend to add a new attribute to XML node in the db_schema.xml to support MySQL ALGORITHM=INPLACE feature that does not produce this downtime. Thanks @toonvd for the idea on #magechat.

Example code:

<table name="sales_order" resource="sales" comment="Sales Flat Order">
    <column xsi:type="smallint" name="my_field" unsigned="false" nullable="true" identity="false"
        algorithm="INPLACE" comment="My Custom Field"/>
</table>

will produce following SQL:

ALTER TABLE sales_order
ADD COLUMN my_field smallint DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

Expected behavior

Change the SQL query used to modify table structure:

ALTER TABLE sales_order
ADD COLUMN my_field smallint DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

Benefits

• no downtime when new columns are added to huge database tables

Additional information

No response

Release note

No response

[m2-assistant[bot]]

Hi @qsolutions-pl. Thank you for your report. To speed up processing of this issue, make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, Add a comment to the issue:

• @magento give me 2.4-develop instance - upcoming 2.4.x release
• For more details, review the Magento Contributor Assistant documentation.
• Add a comment to assign the issue: @magento I am working on this

• To learn more about issue processing workflow, refer to the Code Contributions.

  Join Magento Community Engineering Slack and ask your questions in #github channel. :warning: According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting. :clock10: You can find the schedule on the Magento Community Calendar page. :telephone_receiver: The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, join the Community Contributions Triage session to discuss the appropriate ticket.