Full support for Unicode in a Liferay PaaS database

Note: please note that Liferay has renamed its Liferay Experience Could offerings to Liferay SaaS (formerly LXC) and Liferay PaaS (formerly LXC-SM).

Issue

Emoji symbols can not be stored in database, for example as part of a new web content.

In the Liferay service's log you get this kind of errors:

[JDBCExceptionReporter:234] Incorrect string value: '\xF0\x9F\x8F\x80&lt;/...' for column 'content' at row 1

Environment

Liferay PaaS with the database in the utf8 charset.

This can be checked using the MySQL client:

SELECT schema_name AS 'database', default_character_set_name AS 'charset', default_collation_name AS 'collation' FROM information_schema.SCHEMATA WHERE schema_name = 'lportal';
+----------+---------+-----------------+
| database | charset | collation |
+----------+---------+-----------------+
| lportal | utf8 | utf8_general_ci |
+----------+---------+-----------------+
1 row in set (0.001 sec)

Resolution

MySQL's utf8 charset only supports the storage of UTF-8 encoded symbols taking one to three bytes. For a full support of unicode you should use the utf8mb4 charset instead.

For a new project (with data loss)

Modify your database/LCP.json and add the environment variable LCP_GCP_DATABASE_CHARSET": "utf8mb4" to each environment in where your database service is going to be deployed:

...
  "environments": {
    "infra": {
      "deploy": false
    },
    "prd": {
      "env": {
        "LCP_GCP_DATABASE_CHARSET": "utf8mb4"
      }
    },
    "uat": {
      "env": {
        "LCP_GCP_DATABASE_CHARSET": "utf8mb4"
      }
    },
    "dev": {
      "env": {
        "LCP_GCP_DATABASE_CHARSET": "utf8mb4"
      }
    }
  }
...

Create a new build.
Create a complete backup if you are working with the prd environment, just in case it is needed.
Delete the existing database service from the Liferay PaaS console. This operation is going to remove also your database.
Deploy the new build.

For existing projects (without data loss)
1. Download a backup.
2. Import the dump in an empty MySQL database.
3. Convert the database from utf8 to utf8mb4 . If you do not know a specific tool you can find a manual process in this article.
4. Get a dump of the converted database and upload a complete backup by following the Migrating from an on premises DXP installation guide.
5. Create a clean utf8mb4 database in Liferay Cloud by following the steps 1-5 of the For a new project process.
6. Restore the complete backup uploaded in the step 4.

Note: Restoring an old utf8 backup in a utf8mb4 database would lose the utf8mb4 configuration.