The simplest way to setup a new database for CopyStorm in MySQL is:
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…’
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.
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.
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.
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.
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” />