Some emoji can't be used in items or action items for MySQL installations

[textbook]

Steps to recreate:

• Visit a retro in an installation using MySQL (e.g. https://postfacto-jrs-mysql.apps.pcfone.io/retros/test-retro)
• Attempt to create an item or action with a (four-btye) emoji in it (e.g. bananas 🍌)
• See sad outcomes:
  • item/action item not created
  • 500 on POST /api/retros/:slug/items or POST /api/retros/:slug/action_items
  • (if item) retro_reducer.js:78 Uncaught (in promise) TypeError: Cannot read property 'id' of undefined
  • Mysql2 error in the server logs:

    2021-04-26T13:50:38.11+0100 [APP/PROC/WEB/1] OUT D, [2021-04-26T12:50:38.114188 #17] DEBUG -- : [30b0d005-42e3-4575-8b41-bb5f14419ece]   Item Create (0.9ms)  INSERT INTO `items` (`retro_id`, `description`, `category`, `created_at`, `updated_at`) VALUES (1, 'bananas 🍌', 'meh', '2021-04-26 12:50:38', '2021-04-26 12:50:38')
    2021-04-26T13:50:38.11+0100 [APP/PROC/WEB/1] OUT D, [2021-04-26T12:50:38.115277 #17] DEBUG -- : [30b0d005-42e3-4575-8b41-bb5f14419ece]   TRANSACTION (0.6ms)  ROLLBACK
    2021-04-26T13:50:38.11+0100 [APP/PROC/WEB/1] OUT I, [2021-04-26T12:50:38.115733 #17]  INFO -- : [30b0d005-42e3-4575-8b41-bb5f14419ece] Completed 500 Internal Server Error in 11ms (ActiveRecord: 3.4ms | Allocations: 1246)
    2021-04-26T13:50:38.11+0100 [APP/PROC/WEB/1] OUT F, [2021-04-26T12:50:38.116710 #17] FATAL -- : [30b0d005-42e3-4575-8b41-bb5f14419ece]
    2021-04-26T13:50:38.11+0100 [APP/PROC/WEB/1] OUT [30b0d005-42e3-4575-8b41-bb5f14419ece] ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value: '\xF0\x9F\x8D\x8C' for column 'description' at row 1):
    

This seems like a common problem for Rails-on-MySQL: https://stackoverflow.com/q/22464011/3001761

[gid0]

This is also a problem in our company. We're trying to migrate away from PostgreSQL. I just deployed the latest version of Postfacto in TAS 2.11 and followed the deployment instructions. Created a postfacto-redis instance, and postfacto-db using the latest MySQL tile https://network.pivotal.io/products/pivotal-mysql/#/releases/978342 If I try to add the first emoji "😀" which is by default in the Postfacto UI and post my comment, I end up with a 500 error and the following appears in the app logs:

2021-12-24T12:24:41.086+01:00 [APP/PROC/WEB/1] [OUT] D, [2021-12-24T11:24:41.085976 #14] DEBUG -- : [e0495a04-1b10-4e05-b3f7-abd13967bde9] [1m[36mItem Create (0.5ms)[0m [1m[32mINSERT INTO `items` (`retro_id`, `description`, `category`, `created_at`, `updated_at`) VALUES (1, '😀', 'happy', '2021-12-24 11:24:41', '2021-12-24 11:24:41')[0m
2021-12-24T12:24:41.086+01:00 [APP/PROC/WEB/1] [OUT] D, [2021-12-24T11:24:41.086355 #14] DEBUG -- : [e0495a04-1b10-4e05-b3f7-abd13967bde9] [1m[36mTRANSACTION (0.2ms)[0m [1m[31mROLLBACK[0m
2021-12-24T12:24:41.086+01:00 [APP/PROC/WEB/1] [OUT] I, [2021-12-24T11:24:41.086572 #14] INFO -- : [e0495a04-1b10-4e05-b3f7-abd13967bde9] Completed 500 Internal Server Error in 5ms (ActiveRecord: 1.5ms | Allocations: 1285)
2021-12-24T12:24:41.087+01:00 [APP/PROC/WEB/1] [OUT] F, [2021-12-24T11:24:41.087116 #14] FATAL -- : [e0495a04-1b10-4e05-b3f7-abd13967bde9]
2021-12-24T12:24:41.087+01:00 [APP/PROC/WEB/1] [OUT] [e0495a04-1b10-4e05-b3f7-abd13967bde9] ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value: '\xF0\x9F\x98\x80' for column 'description' at row 1):
2021-12-24T12:24:41.087+01:00 [APP/PROC/WEB/1] [OUT] [e0495a04-1b10-4e05-b3f7-abd13967bde9]
2021-12-24T12:24:41.087+01:00 [APP/PROC/WEB/1] [OUT] [e0495a04-1b10-4e05-b3f7-abd13967bde9] app/controllers/items_controller.rb:45:in `create'

[gid0]

I made some progress on the service side... I created it with a custom config:

cf create-service p.mysql 100mb postfacto-db -c '{ "default-charset": "utf8mb4", "default-collation": "utf8mb4_unicode_ci" }'

see https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html and https://docs.pivotal.io/p-mysql/2-10/change-default.html#character

Now when installing the app and checking the DB, all the tables have been correctly set up with utf8mb4, including the columns like "description":

MySQL [service_instance_db]> SELECT * FROM information_schema.SCHEMATA WHERE schema_name = "service_instance_db";
+--------------+---------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME         | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+---------------------+----------------------------+------------------------+----------+
| def          | service_instance_db | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
+--------------+---------------------+----------------------------+------------------------+----------+
1 row in set (0.205 sec)

MySQL [service_instance_db]> SELECT TABLE_COLLATION,COLLATION_NAME,CHARACTER_SET_NAME FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "service_instance_db" AND T.table_name = "items";
+--------------------+--------------------+--------------------+
| TABLE_COLLATION    | COLLATION_NAME     | CHARACTER_SET_NAME |
+--------------------+--------------------+--------------------+
| utf8mb4_unicode_ci | utf8mb4_unicode_ci | utf8mb4            |
+--------------------+--------------------+--------------------+
1 row in set (0.325 sec)

MySQL [service_instance_db]> SELECT CHARACTER_SET_NAME,COLLATION_NAME FROM information_schema.`COLUMNS`  WHERE table_schema = "service_instance_db" AND table_name = "items" AND column_name = "description";
+--------------------+--------------------+
| CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------------+--------------------+
| utf8mb4            | utf8mb4_unicode_ci |
+--------------------+--------------------+
1 row in set (0.306 sec)

And I can manually insert en emoji by running the INSERT statement that the app failed to execute, like:

MySQL [service_instance_db]> INSERT INTO `items` (`retro_id`, `description`, `category`, `created_at`, `updated_at`) VALUES (1, '😀', 'happy', '2021-12-24 12:56:40', '2021-12-24 12:56:40');
MySQL [service_instance_db]> SELECT * FROM items;
+----+----------+-------------+----------+------------+---------------------+---------------------+------+-------------+------------+----------+
| id | retro_id | description | category | vote_count | created_at          | updated_at          | done | archived_at | archive_id | archived |
+----+----------+-------------+----------+------------+---------------------+---------------------+------+-------------+------------+----------+
|  1 |        1 | 😀           | happy    |          0 | 2021-12-24 12:56:40 | 2021-12-24 12:56:40 |    0 | NULL        |       NULL |        0 |
+----+----------+-------------+----------+------------+---------------------+---------------------+------+-------------+------------+----------+
1 row in set (0.332 sec)

Still, the character appears as "?" in the postfacto UI. And I can't insert an emoji from the app, it still gives a 500 with the same error as before in the logs. It seems that the app still tries to use "utf8" when connecting to the DB, even though "utf8mb4" is the default.

So I "patched" the app by adding the following to the assets/config/database.yml file:

production:
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci

I pushed the app again, and voilà, postfacto is working with emojis.

The DB setup is clean, now is there a better way to make the app use the mysql utf8mb4 encoding (like with env vars or a different service config) rather than patching the database.yml file?

[gid0]

TL;DR: Create MySQL service instance with:

cf create-service SERVICE PLAN SERVICE_INSTANCE -c '{ "default-charset": "utf8mb4", "default-collation": "utf8mb4_unicode_ci" }'

In the Postfacto sources, add this to the assets/config/database.yml file:

production:
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci

Push app... emojis should be working.

CC @textbook if you're still searching for a solution/workaround.