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”:
Last updated
Was this helpful?