SQL issue with Unicode emojis (CCMS 9.1.1)

Using Concrete / Help
1

Hi,

on one of my sites, we found that Unicode emojis cause an error… on another of my sites, it works absolutely fine.

UPDATE: it was this particular flag emoji: 🏳️‍🌈
Its unicode is a bit complex: https://unicode.org/L2/L2016/16183-rainbow-flag.pdf

This is the error we get when trying pressing OK after an edit, on the issue site:

296174509_1047661629475742_1503127992659123117_n
296174509_1047661629475742_1503127992659123117_n1756×1122 178 KB

I’ve checked the first obvious thing with the database for both sites; they both have DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci on all relevant tables, so that should be fine.

If someone recognises this issue, I’d be most grateful for an indication what might be wrong. Adding the environment from both for your viewing.

Here’s the environment from the issue site:

# Concrete Version
Core Version - 9.1.1
Version Installed - 9.1.1
Database Version - 20220516191423

# Database Information
Version: 10.5.15-MariaDB-0+deb11u1
SQL Mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# Concrete Packages
ContreteCMS Discourse Connect (0.93), Login Return (1.0), Polyforeningen (0.1.93.17)

# Concrete Overrides
None

# Concrete Cache Settings
Block Cache - On
Overrides Cache - On
Full Page Caching - Off
Full Page Cache Lifetime - Every 6 hours (default setting).

# Server Software
Apache/2.4.54 (Debian)

# Server API
apache2handler

# PHP Version
7.4.30

# PHP Extensions
apache2handler, calendar, Core, ctype, curl, date, dom, exif, FFI, fileinfo, filter, ftp, gd, gettext, hash, iconv, intl, json, libxml, mbstring, mysqli, mysqlnd, openssl, pcre, PDO, pdo_mysql, Phar, posix, readline, Reflection, session, shmop, SimpleXML, sockets, sodium, SPL, standard, sysvmsg, sysvsem, sysvshm, tokenizer, xml, xmlreader, xmlwriter, xsl, Zend OPcache, zip, zlib

# PHP Settings
max_execution_time - 30
log_errors_max_len - 1024
max_file_uploads - 20
max_input_nesting_level - 64
max_input_time - 60
max_input_vars - 1000
memory_limit - 128M
post_max_size - 8M
upload_max_filesize - 2M
mbstring.regex_retry_limit - 1000000
mbstring.regex_stack_limit - 100000
mysqli.max_links - Unlimited
mysqli.max_persistent - Unlimited
pcre.backtrack_limit - 1000000
pcre.recursion_limit - 100000
session.cache_limiter - <i>no value</i>
session.gc_maxlifetime - 7200
unserialize_max_depth - 4096
opcache.max_accelerated_files - 10000
opcache.max_file_size - 0
opcache.max_wasted_percentage - 5

Here’s the environment from the well working site:

# Concrete Version
Core Version - 9.0.1
Version Installed - 9.0.1
Database Version - 20211104161958

# Database Information
Version: 10.1.48-MariaDB-0+deb9u2
SQL Mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# Concrete Packages
Debug Kit (0.9.5), Login/Logout Link (1.0), RL (0.9.13), RL autonav extras (0.1), Simple Gallery (1.0.9)

# Concrete Overrides
None

# Concrete Cache Settings
Block Cache - On
Overrides Cache - Off
Full Page Caching - Off
Full Page Cache Lifetime - Every 6 hours (default setting).

# Server Software
Apache/2.4.25 (Debian)

# Server API
fpm-fcgi

# PHP Version
7.4.30

# PHP Extensions
calendar, cgi-fcgi, Core, ctype, date, dom, exif, FFI, fileinfo, filter, ftp, gd, gettext, hash, iconv, json, libxml, mbstring, mysqli, mysqlnd, openssl, pcre, PDO, pdo_mysql, Phar, posix, readline, Reflection, session, shmop, SimpleXML, sockets, sodium, SPL, standard, sysvmsg, sysvsem, sysvshm, tokenizer, xml, xmlreader, xmlwriter, xsl, Zend OPcache, zip, zlib

# PHP Settings
max_execution_time - 30
log_errors_max_len - 1024
max_file_uploads - 20
max_input_nesting_level - 64
max_input_time - 60
max_input_vars - 1000
memory_limit - 192M
post_max_size - 8M
upload_max_filesize - 2M
mbstring.regex_retry_limit - 1000000
mbstring.regex_stack_limit - 100000
mysqli.max_links - Unlimited
mysqli.max_persistent - Unlimited
pcre.backtrack_limit - 1000000
pcre.recursion_limit - 100000
session.cache_limiter - <i>no value</i>
session.gc_maxlifetime - 7200
unserialize_max_depth - 4096
opcache.max_accelerated_files - 10000
opcache.max_file_size - 0
opcache.max_wasted_percentage - 5
2

So, it appears that the obvious wasn’t obvious enough. The flag emoji includes 1F3F3, which is 4 bytes in utf-8, so requires utf8mb4 rather than utf8…

So the question is, then, how the hell did the “working” site get away with it?

3

I don’t know how you’ve set up the problematic Concrete initially.

If you’ve been upgrading the site from pre 8.5.0 and the server doesn’t support utf8mb4 (or database user don’t have enough privilege to alter the database/table settings), Concrete will let you upgrade with utf8.

4

@katz515, the one that “works” with utf8 (not utf8mb4) is originally pre-v9. I think I initially installed v8.5.5, using this procedure: Basic Setup & Installation

But, it’s true that it’s on a fairly old machine, running Debian 9 [stretch], so that might impact what was supported at the time I made the initial installation.

I’m currently guessing that the “STRICT_TRANS_TABLES” SQL mode was instrumental in generating the failure on one site but not the other…

1 Like