> For the complete documentation index, see [llms.txt](https://learn.capstorm.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://learn.capstorm.com/best-practices/mysql-database-setup.md). # 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”: ```xml ```