This article provides a solution for the issue when you cannot upgrade Magento from 2.2x and 2.3.x to 2.4.x due to COMPACT
MySQL tables not having been converted to DYNAMIC
tables in your database.
If you try to upgrade Magento from 2.2x and 2.3.x to 2.4.x without converting row formats from COMPACT to DYNAMIC, you will probably not be allowed to upgrade, and if you manage to upgrade there may be data loss on some tables.
When using the MySQL queries below you may need to replace all single and double quotes due to there being characters that look like them but are not them that are used in some browser display fonts.
Converting from COMPACT
to DYNAMIC
tables can take a long time with a large database. This process should be done in maintenance mode during a low traffic period. For steps refer to DevDocs Installation Guide > Enable or disable maintenance mode.
Affected product and versions
- Magento Commerce Cloud v2.3
- Magento Commerce Cloud v2.2
Issue
COMPACT
tables must be converted to DYNAMIC
tables in your database before you can upgrade from Magento Commerce Cloud v2.2 and v2.3 to v2.4.
Solution
- SSH into the environment. For steps, refer to DevDocs Magento Commerce Cloud > SSH into your environment.
For pro accounts prod/staging you will need to do this on all 3 nodes, over port 3304. For starter accounts/integration/dev branches you will not need to specify a port or do it multiple times as there is a single database container. - Authenticate to MySQL by running the following command in the CLI/Terminal:
export DB_NAME=$(grep [\']db[\'] -A 20 app/etc/env.php | grep dbname | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//"); export MYSQL_HOST=$(grep [\']db[\'] -A 20 app/etc/env.php | grep host | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//"); export DB_USER=$(grep [\']db[\'] -A 20 app/etc/env.php | grep username | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//"); export MYSQL_PWD=$(grep [\']db[\'] -A 20 app/etc/env.php | grep password | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/[']$//" | sed "s/['][,]//");
- Get a count of tables to be altered and their names by running the following command in the CLI/Terminal:
For Pro
mysql -P 3304 -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT table_name,row_format FROM information_schema.tables WHERE table_schema='$DB_NAME' and row_format='compact'"|wc -l mysql -P 3304 -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT table_name,row_format FROM information_schema.tables WHERE table_schema='$DB_NAME' and row_format='compact'"|less
- Build the
ALTER
table list file, and make sure it's complete:
For starter use 3306 as there is only one MySQL instance.
For Pro
mysql -P 3304 -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT table_name,row_format FROM information_schema.tables WHERE table_schema='$DB_NAME' and row_format='compact'"|grep -v table_name|awk '{print "alter table "$1" ROW_FORMAT=DYNAMIC; "}' > ~/var/alter.txt fw0ef0jqfdlwdj@i-f5w6ef4w6e5f4we6f4:~$ wc -l ~/var/alter.txt 612 /app/fw0ef0jqfdlwdj/var/alter.txt fw0ef0jqfdlwdj@i-f5w6ef4w6e5f4we6f4:~$ mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT table_name,row_format FROM information_schema.tables WHERE table_schema='$DB_NAME' and row_format='compact'"|grep -v table_name|wc -l 612
- Download the file's contents to a text file on your local environment.
- Log in to MySQL. For starter use 3306 as there is only one MySQL instance.
- Copy and paste 100 or so rows at a time from that file into MySQL to alter the table formats from
COMPACT
toDYNAMIC
. - When complete check the list of compact tables you created in step 3 to ensure that there are no tables left to be converted.
- If there are any, repeat step 3 to 8 till none remain.
- Once complete, repeat on node 2/3 for Pro accounts. There should be no compact tables on any node when you've completed this.
- Double check you have no
MyISAM
tables. For steps refer to Database best practices for Magento Commerce Cloud > Convert all MyISAM tables to InnoDb.
Related Reading
- Magento 2.4.0 known issue: missing "Refund" label in Klarna
- Magento 2.4.0 known issue: two buttons missing on Create New Order page in Admin
- Different addresses not allowed when unselecting 'My billing and shipping address are the same' using Vertex Address Cleansing
- Magento Commerce 2.4.0 known issue: when Braintree is enabled, Venmo partial invoice issue
- Magento 2.4.0 known issue: Error message selecting local payment method displayed for some countries during checkout
- Magento 2.4.0 known issue: Amazon Pay enabled, payment methods missing when Return to standard checkout used
- Magento 2.4.0 known issue: 2.4.0 installation fails with outdated stores cache
- Magento 2.4.0 known issue: 404 error when removing rewards points on multi-shipping checkout
- Magento 2.4.0 known issue: orders display error
- Magento 2.4.0 known issue: B2B Admin cannot add a configurable product to a quote
- Magento 2.4.0 known issue: Braintree payment methods do not show up in Multiple Addresses checkout
- Shipping labels creation known issue in Magento 2.4.0
- Magento 2.4.0 known issue - refresh on Customer's Activities does not work
- Magento 2.4.0 known issue - Export Tax Rates does not work
- Magento 2.4.0 known issue: “Add selections to my cart” button does not work
- Magento 2.4.0 known issue: raw message data display on storefront