MySQL: Repository Error Row Size too Large

This will require direct access to the database server for setup and builder privileges for implementation.

Problem:

When running the repository script to create the tables in the empty MySQL repository database, an error occurs. The below error occurs when running the repository script:

Error Code: 1118. Row size too large. The maximum row size for the used table type, not counting 
BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns 
to TEXT or BLOBs.

This always follows this part of the script:

CREATE TABLE fv_chart2 (...

Issue:

The problem lies in the fact that Unicode is not supported on MySQL. When creating the iDashboards database inside MySQL and accepting the default character encoding (aka collation), which was utf8 - default collation, the UTF-8 encoding can use up to 3 bytes per character, and therefore MySQL must accommodate that size. A VARCHAR(500) column actually requires 1502 bytes. When the repository creation script gets to the point of creating the fv_chart2, which contains a lot of VARCHAR columns, that UTF-8 3x multiplier caused the row size to exceed MySQL's maximum of 65535 bytes and the error occurs.

Resolution:

The best way to get around this issue would be to recreate the iDashboards schema and choose latin1 - default collation. Every database has a database character set and a database collation. The CREATE DATABASE and ALTER DATABASE statements have optional clauses for specifying the database character set and collation:

The keyword SCHEMA can be used instead of DATABASE. All database options are stored in a text file named db.opt that can be found in the database directory. The CHARACTER SET and COLLATE clauses make it possible to create databases with different character sets and collations on the same MySQL server.

Example:  CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL chooses the database character set and database collation in the following manner:

• If both CHARACTER SET X and COLLATE Y are specified, character set X and collation Y are used.
• If CHARACTER SET X is specified without COLLATE, character set X and its default collation are used. To see the default collation for each character set, use the SHOW COLLATION statement.
• If COLLATE Y is specified without CHARACTER SET, the character set associated with Y and collation Y are used.

Otherwise, the server character set and server collation are used.

For More Information:

• iDashboards Admin Manual 13. System Configuration
• iDashboards Data Hub Manual 4. Data Hub Installation
• Oracle's MySQL Documentation

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.