MySQL Database Setup

The simplest way to setup a new database for CopyStorm in MySQL is:

CREATE SCHEMA copystorm

However, it is important that your database server be configured to support the full set of UTF-8 characters supported by Salesforce. Default MySQL configuration typically uses the latin1 character set, which omits UTF-8 characters like a smiley face with sunglasses (U+1F60E). If a non-included character appears in Salesforce, MySQL will refuse to accept it and report an error like:

SQL state [HY000]; error code [1366]; Incorrect string value: ‘\xF0\x93\x83\xA2\xF0\x93…’

Make Sure MySQL Supports the Full UTF-8 Character Set

You can tell if MySQL is already configured correctly by running the following command:

SHOW VARIABLES LIKE ‘character_set_server’

If the current value of “character_set_server” is “utf8mb4” then your server is setup correctly. If not, here are the steps to correct the problem:

Open the mysqld.cnf file for your MySQL. This contains the default parameters for your server.
Look for the “character_set_server” setting. If you find it then change it to utf8mb4 and save the changes. If not, then add lines like the following to the “[mysqld]” section:
- character_set_server = utf8mb4
- character-set-server = utf8mb4
- Which line depends on the exact MySQL version and OS distribution you are using.
Restart MySQL.
Run SHOW VARIABLES to verify the change.

Some Characters Look Wrong in SQL Workbench – Why?

MySQL Workbench often uses a default character set of utf8. If a string containing 4-byte utf8 characters is viewed in Workbench then the 4 byte characters appear as question marks. To fix this behavior, set the character set for the session:

SET CHARACTER SET utf8mb4;

If your workbench session still does not correctly display the characters then it may be a workbench problem. We have seen cases where Heidi (the MariaDB Workbench equivalent) and SQuirrel display the data correctly and Workbench does not.

Other Good Advice

Use a 64KB Page Size

Instance page size is set (using the variable innodb_page_size) when you first create a MySQL instance. This can cause problems when Salesforce contains very wide tables whose records will not fit into a single page (by default 16KB), and cannot be changed without deleting and rebuilding the database.

Capstorm always recommends using a 64KB page size when setting up a Capstorm MySQL database.

Allow For a Large Log File

MySQL’s maximum log file size needs to be around 4 times the size of the large object in MySQL. If this is not the case you may see a “Row Size Too Wide” error from MySQL. To change your log file size search for the “innodb_log_file_size” in the MySQL documentation.

Use Dynamic Tables to Avoid Problems With Many Large TEXT Columns

The default InnoDB format, Antelope, stores the first 768 bytes of a TEXT column with the row data — the rest is stored elsewhere. Since Antelope is the default InnoDB format almost every MySQL instance will be using it.

If at runtime a row has a lot of TEXT columns and many of them have more than 768 bytes, you may see a “Row Size Too Wide” error message from MySQL. An easy way to avoid this issue is to use the InnoDB Barracuda format and change tables to use ROW_FORMAT=Dynamic.

To change a database to use the Barracuda format:

SET GLOBAL innodb_file_format = barracuda;
SET GLOBAL innodb_file_format_max = barracuda;

This will cause future tables to use the Barracuda format. To force a table to use a Dynamic row format:

ALTER TABLE tableName ROW_FORMAT=Dynamic;

In addition, you can force all tables created by CopyStorm to be Dynamic Barracuda tables by adding the following lines to the config file “TableRuleRegistry.xml”:

<TableRule name="default" >

</TableRule>