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 ; 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" >
<CreateTableRule database=”MySQL” option=”CHARACTER SET utf8mb4 ROW_FORMAT=Dynamic” />