Problem 

When a user tries to insert a UTF8 character, which requires 4-byte encoding (e.g. emojis like " 😊🍻"), the “Incorrect String Value” error message is displayed.

Diagnosis

Fisheye / Crucible application connected to MySQL database server with UTF8 encoding.

Cause

MySQL prior to version 5.5.3 does not support 4-byte UTF8 characters.

Solution

Procedure

Upgrade your MySQL database to version 5.7.7 or higher. Switch the character encoding to utf8mb4 and collation to utf8mb4_bin. Follow the steps below:

1. Upgrade Fisheye / Crucible.
   If you use a version older than 4.7.0 perform an upgrade first (see Fisheye upgrade guide). 
   
   
2. Shutdown Fisheye / Crucible.
   
If you have an existing Fisheye / Crucible installation, make sure you shutdown the application gracefully and generate a database backup before moving on to the next step.


   
   
3. Shutdown the MySQL database.
   
   

4. Open the MySQL configuration file with a text editor.

   1. In Linux environments the file is usually located at /etc/my.cnf
   2. In Windows environments the file is usually located at C:\ProgramData\MySQL\MySQL Server 5.7\my.ini.
      This can be confirmed by opening the Properties panel of the Windows service used by MySQL and verifying the path pointed to by the defaults-file attribute in the Path to executable field.
      For example:
      
      
   3. For additional clarifications regarding this matter, please review the MySQL 5.7 Reference Manual on Using Option Files and / or talk to your MySQL database administrator.

5. Add the following lines to the MySQL configuration file: 

   [mysqld]
   character-set-server = utf8mb4
   collation-server = utf8mb4_bin

6. Start your MySQL database and verify the changes by running the following SQL query:

   SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'character\_set\_%' OR variable_name LIKE 'collation%';

   Expected output:

   
   
   

7. Create a new database based on the new character set and collation, remembering to grant all privileges to a MySQL database user, as suggested in Migrating to MySQL:

   mysql> SET GLOBAL default_storage_engine = 'InnoDB';
   mysql> CREATE DATABASE crucible CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   mysql> GRANT ALL PRIVILEGES ON crucible.* TO 'fisheyeuser'@'localhost' IDENTIFIED BY 'password';
   mysql> FLUSH PRIVILEGES;

8. Update MySql JDBC driver to version 5.1.47+, but not 8.x version (see Migrating to MySQL).
   
   

9. Start Fisheye / Crucible.
   
   

10. Migrate a current database to the new database created in a previous step.

1. Open Administration > System Settings > Database page.
2. Click Edit and fill the form with your new database settings (new database name).

3. In the Parameters field, enter:

   useUnicode=true
   characterEncoding=UTF8MB4
   connectionCollation=utf8mb4_bin

4. Click Test Connection.

5. Click Save & Migrate. Double-check if source and target URLs are valid in the warning dialog presented and click "Confirm".

Notes

• We recommend migration of data to a new database. In case you decide to upgrade the database in-place, you have to not only alter the database but also all existing tables to set the utf8mb4 encoding.
• If you want to change the name of the new database to the old one after migration:
  1. Shutdown Fisheye / Crucible.
  2. Rename the database in MySQL.
  3. Edit the Fisheye / Crucible config.xml file directly.

  4. Start Fisheye / Crucible.

     Do not try to rename it using Administration > System Settings > Database dialog as Fisheye / Crucible will recognize a different URL as a new database and will attempt a migration.

     This will result in erasure of the target database (which is your source database as well in this case).

• If you see "The database is not using case-sensitive Unicode ..." message after clicking the "Test connection" button then either your Fisheye / Crucible is older than 4.7.0 (older versions do not recognise utf8mb4 setting) or you have invalid options in the "Parameters" field. 
  
  

• In case you entered 4-byte characters (like emojis) in various places in the application (review title, comments, project name etc) prior to the migration, then they have been already stored in a corrupted form in the database. Migration will not fix these characters automatically. Edit this data manually after the upgrade in order to fix it.
  
  

• If you see the message below after the migration and is not connected to a MySQL behind SSL:

  Warning

  Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45\+, 5.6.26\+ and 5.7.6\+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need to either explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

  The parameter useSSL=false must be appended to the database connection string in the config.xml file, like so:

  database string connection

  jdbc.url=jdbc:mysql://<DB-SERVER-ADDRESS>:3306/<DB_NAME>?useSSL=false