How to Fix ALTER COLUMN Errors

CopyStorm does its best to keep column definitions in the target database exactly the same as the corresponding definitions in Salesforce. Normally this happens automatically as CopyStorm performs required ALTER TABLE/COLUMN statements on the target database. This article describes what to do when an ALTER fails. An ALTER may fail for several reasons:

  • A column data type in Salesforce changes. Few databases support this conversion automatically.
    • For example, changing a String to a Number.
  • Changes to the length of a column in Salesforce force a fundamental data type change in the database. Some databases support this, some do not.
    • For example, changing a VARCHAR to a CLOB.
  • A new Salesforce column or a length change to an existing column makes the database table “too wide” for the database.
    • This issue typically only arises in MySQL.

Solutions for this class of problems are discussed in the following sections.

Cross-Database Solutions

A solution that almost always works for any database type is to DROP the problem table and let CopyStorm rebuild it from scratch. When a table is dropped in the database, CopyStorm will detect the change and automatically rebuild it.

There are two common ways to DROP a table. The simplest approach is to do one of:

  • Use your favorite SQL tool to run a “DROP TABLE tableName” command.
  • On the Database Reset section of the CopyStorm Configuration tab temporarily check the “Force Rebuild” option and run CopyStorm with only the problem table selected. CopyStorm will drop and rebuild the table when it runs.

The following approach is more complex but avoids re-downloading data from Salesforce:

  • Rename the table causing the ALTER issue.
  • Open CopyStorm and use it to recreate the problem table WITHOUT downloading any data. The procedure is:
    • Go to the Configuration tab.
    • Select just the problem table in the Core Configuration section.
    • Enter a date far into the future for the “Modified Since” value (e.g. 1/1/2150). This parameter can be found in the Maintenance section.
    • Start the copy. CopyStorm will create the table but not transfer data.
    • Close CopyStorm without saving changes.
  • Copy the contents of the renamed table into the table just created by CopyStorm:
    • INSERT INTO tableName (fieldName1, fieldName2, …) SELECT fieldName1, fieldName2, … FROM RenamedTable
  • Drop the renamed table.

Oracle Specific Solutions

Oracle does not support directly changing a column type from VARCHAR2 to CLOB. Unfortunately, this operation is needed when a column in Salesforce increases length to an amount not supported by an Oracle VARCHAR2 column.

To fix this problem, you can rebuild the problem column by hand.

For example, suppose that the column Account.myField__c has increased in length from 2000 to 8000. To hold values in Oracle the corresponding column in Oracle needs to change from VARCHAR2 to CLOB. The following SQL statements will make this happen:

  • ALTER TABLE Account ADD scratch CLOB NULL
  • UPDATE Account SET scratch=myField__c
  • ALTER TABLE Account DROP COLUMN myField__c
  • ALTER TABLE Account RENAME COLUMN scratch TO myField__c

MySQL Specific Solutions

MySQL has a unique design property among databases that can cause issues when backing up Salesforce: A table cannot be declared wider than the database page size.

When a new column is added in Salesforce or a column’s length is increased, MySQL sometimes reports a “Row Size Too Large” error when increasing a column size or adding a column would push a table beyond the maximum database page size.

Any solution that backs up all record data involves changing the data type of some VARCHAR columns to TEXT. This works because TEXT columns take less space in a table row than VARCHAR types because TEXT column data is not stored within a table row.

The easiest solution is to drop the table and let CopyStorm rebuild it.

  • Starting with CopyStorm version 7.37.10, CopyStorm will automatically store “enough” VARCHAR columns as TEXT to make the table row size small enough.
  • In earlier CopyStorm versions, the parameter “Max Varchar” on the Configuration tab can be adjusted until table creation works.
    • “Max Varchar” determines the largest string that will be stored as a VARCHAR, larger strings will be stored as TEXT.
    • This parameter can be found in the Database Schema configuration section.

An alternate solution is to change some of the longer columns in your table from VARCHAR to TEXT:

  • ALTER TABLE myTable__c MODIFY myColumn__c LONGTEXT CHARACTER SET utf8