There are a number of quirks to MySQL when it comes to adding columns or altering columns in an existing table. The technique it employs, unlike other databases supported by Capstorm, can be quite time-consuming.
To perform an ALTER TABLE abc ADD COLUMN command, MySQL:
- Creates an empty shadow of the original table WITH the new column.
- Copies the entire existing table to the shadow table.
- This can take a long time for huge tables.
- Replaces the existing table with the shadow table.
This MySQL behavior can make it faster to rebuild a CopyStorm database when upgrading protocols rather than upgrading a MySQL database in place.
CopyStorm version numbers contain three parts:
- Major Version
- Protocol Version
- Minor Version
For example, 7.34.1 is major version 7, Salesforce protocol 34, and minor version 1. A value of 7.31.1 would indicate the same feature set but using Salesforce protocol 31.
When Salesforce updates protocols new columns and tables are almost always added (this happens 3 times a year, CopyStorm releases a new protocol update after each Summer protocol update). The first time you run CopyStorm with an updated protocol on your MySQL database new tables will be added (not a problem) and new columns may be added to existing tables (a problem for large tables).
There is no hard and fast “best practice” but there are a few options that have various tradeoffs.
If you are keeping deleted records forever or preserving deleted columns then your only option is to let MySQL upgrade the tables in place. Other approaches would cause you to lose data.
If you do not keep deleted records and your organization can live with a table being offline for a small amount of time, the easiest option is to run CopyStorm once with the “Force Rebuild” option checked so tables can be fully rebuilt. This option will make CopyStorm drop and rebuild each table when backing up Salesforce, and can be found on the “Advanced” tab. This option should always be turned off when performing incremental backups — dropping and rebuilding each table each time a backup runs defeats the purpose of being incremental.
If you do not keep deleted records but your organization wants the MySQL database down for the minimum amount of time, then:
- Use the new version of CopyStorm to create a new database while continuing to run the older version of CopyStorm on the older database.
- When the new version of CopyStorm is done backing up Salesforce to the new database, use it to replace the older database. Use the new version of CopyStorm going forward.