1. Magento Help Center
  2. Best Practices
  3. General

Articles in this section

See more

Magento upgrade: compact to dynamic tables 2.2.x, 2.3.x to 2.4.x

  • Created
  • Updated

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

  1. 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.

  2. 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/['][,]//");
  3. Get a count of tables to be altered and their names by running the following command in the CLI/Terminal:

    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'"|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
  4. 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
  5. Download the file's contents to a text file on your local environment.
  6. Log in to MySQL. For starter use 3306 as there is only one MySQL instance.
  7. Copy and paste 100 or so rows at a time from that file into MySQL to alter the table formats from COMPACT to DYNAMIC.
  8. When complete check the list of compact tables you created in step 3 to ensure that there are no tables left to be converted.
  9. If there are any, repeat step 3 to 8 till none remain.
  10. Once complete, repeat on node 2/3 for Pro accounts. There should be no compact tables on any node when you've completed this.
  11. 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

Was this article helpful?
2 out of 2 found this helpful
Return to top

Related articles