To address the issue where the bin/magento setup:upgrade command gets stuck on the "Schema creation/updates:" line and executes ALTER TABLE queries on all Magento tables, there are several steps you can take to mitigate or work around this problem. Here are some detailed suggestions:
Workarounds and Solutions:
Disable Schema Updates Temporarily:
If you are certain that your database schema is already up-to-date, you can bypass schema updates temporarily by commenting out or removing the db_schema.xml files in your custom or third-party modules. This is not a permanent solution but can help you proceed with other parts of the setup:upgrade process without being stuck on schema updates.
<!-- Comment out or temporarily remove the content of db_schema.xml files in custom modules -->
Modify the Setup Scripts:
Modify the setup scripts to skip the ALTER TABLE operations. This is a more advanced and risky approach, so proceed with caution and ensure you have proper backups before making changes.
Adjust MySQL Configuration:
Sometimes, MySQL settings can cause performance issues with large ALTER TABLE operations. Consider adjusting the following MySQL settings in your my.cnf or my.ini file:
innodb_buffer_pool_size=2G
innodb_log_file_size=512M
innodb_write_io_threads=8
innodb_read_io_threads=8
innodb_flush_log_at_trx_commit=2
These settings can help improve the performance of ALTER TABLE operations.
Upgrade to a Newer Magento Version:
If feasible, consider upgrading to a newer version of Magento. Magento 2.3.1 is quite old, and many issues related to database schema updates have been fixed in later versions. Upgrading Magento can help avoid such issues altogether.
Patch Magento Core Files:
If the issue is critical and none of the above solutions work, consider applying a temporary patch to the Magento core files to bypass schema updates. This approach requires in-depth knowledge of Magento's internals and should only be used as a last resort. Remember to document and test any patches thoroughly.
Investigate Specific Module Issues:
If the problem is caused by a specific module, disable that module temporarily to see if the issue persists. You can disable a module using the following command:
bin/magento module:disable Vendor_ModuleName
After identifying the problematic module, check its db_schema.xml and InstallSchema.php or UpgradeSchema.php files for potential issues.
Debugging Tools:
Use MySQL's SHOW PROCESSLIST to see which specific queries are running and taking time. This can give you insights into which tables or operations are causing the delays.
SHOW PROCESSLIST;
Example of Commenting out db_schema.xml:
<!-- app/code/Vendor/Module/etc/db_schema.xml -->
<!--
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="your_table" resource="default" engine="innodb" comment="Your Table">
<column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true" comment="Entity ID"/>
<!-- Other columns -->
</table>
</schema>
-->
Additional Considerations:
- Backup Your Database: Always ensure you have a full backup of your database before making any changes.
- Test on Staging Environment: Apply changes on a staging environment first to verify that they work as expected before deploying to production.
following these steps, you can mitigate the issue of unnecessary ALTER TABLE operations during the setup:upgrade process in Magento 2.3.1 with MySQL 8.
