Adobe Commerce 2.3.5 upgrade: compact to dynamic tables

This article provides a guide on the prerequisites to upgrade from MariaDB 10.0 to 10.2. Adobe Commerce version 2.3.5 and later requires MariaDB version 10.2.

The upgrade of MariaDB itself will be performed by the Adobe Commerce Support team. However, prior to them starting the upgrade process, you will need to take action to convert all tables in your database from COMPACT format to DYNAMIC. You will also need to convert the storage engine type from MyISAM to InnoDB.

Converting from COMPACT to DYNAMIC tables can take several hours with a large database. All the database ALTER commands below should be carried out in maintenance mode during a low traffic period on your site. You should not attempt to run these commands when your site is live and not in maintenance mode, due to the risk of data corruption to your database.

For steps on how to enable maintenance mode, please refer to Installation Guide > Enable or disable maintenance mode in our developer documentation.

Affected product and versions

Adobe Commerce on cloud infrastructure version 2.3.4 or earlier with MariaDB version to 10.0 or earlier.

Issue

Upgrading your MariaDB version to 10.2 or later is rejected by Adobe Commerce support, due to COMPACT tables needing to be converted to DYNAMIC and/or storage engine type being MyISAM.

Solution

SSH into node 1 on your environment. You do not need to perform these changes on every MySQL node, only node 1 is sufficient. The changes you make there will replicate across to the other core nodes in your cluster.

   SELECT table_name, row_format FROM information_schema.tables WHERE table_schema=DATABASE() and row_format = 'Compact';

Run this command to see the table sizes - bigger sized tables will take longer to convert. You should plan accordingly when taking your site in and out of maintenance mode which batches of tables to convert in which order, so as to plan the timings of the maintenance windows needed:
```
   SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;
```
Run this command to covert the tables that need to be converted into Dynamic. You need to do this one by one for every table on the database that needs to be converted:
```
   ALTER TABLE [ table name here ] ROW_FORMAT=DYNAMIC;
```
After all the Compact to Dynamic table version changes have been completed, then the following command should be run in the CLI/Terminal to check which tables need to be converted from MyISAM storage engine to InnoDB:
```
    SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'MyISAM';
```
You should then run the following command to convert any tables identified as MyISAM to InnoDB;
```
    ALTER TABLE [ table name here ] ENGINE=InnoDB;
```

The day before the upgrade of MariaDB to 10.2 is due to happen, you should check again the following commands, as some tables may be converted back due to code deployments since you made the original changes:

   SELECT table_name, row_format FROM information_schema.tables WHERE table_schema=DATABASE() and row_format = 'Compact';

     SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'MyISAM';

If any tables have converted back, you will need to repeat the steps above on the reverted tables, or the support team will not be able to proceed with the upgrade ticket.

Adobe Commerce 2.3.5 upgrade: compact to dynamic tables

Affected product and versions

Issue

Solution

Related Readings in our support knowledge base