[issue2551282] MySQL utf8mb4 issues

New submission from John Rouillard:

Gabor Nagy reported:

 OperationalError: (1253, "COLLATION 'utf8mb3_bin' is not valid for CHARACTER SET 'utf8mb4'")

when using mysql. This appears to be a version issue with mysql and is a hint
that changes made in 2.2.0 and 2.0.0 to support other UTF8 charsets in mysql
is incomplete.

See email chain: https://sourceforge.net/p/roundup/mailman/roundup-
users/thread/20230616213123.150b935f%40Dell/

TL;DR is to add a modification of this patch to use configurable
values:

diff --git a/back_mysql.py.bak b/back_mysql.py
index b0ec1c6..7b128a4 100644
--- a/back_mysql.py.bak
+++ b/back_mysql.py
@@ -92,9 +92,9 @@ def db_create(config):
     kwargs = connection_dict(config)
     conn = MySQLdb.connect(**kwargs)
     cursor = conn.cursor()
-    command = "CREATE DATABASE %s"%config.RDBMS_NAME
+    command = "CREATE DATABASE %s COLLATE utf8mb4_unicode_ci"%config.RDBMS_NAME
     if sys.version_info[0] > 2:
-        command += ' CHARACTER SET utf8'
+        command += ' CHARACTER SET utf8mb4'
     logging.info(command)
     cursor.execute(command)
     conn.commit()
@@ -625,7 +625,7 @@ class Database(rdbms_common.Database):
                 raise
 
 class MysqlClass:
-    case_sensitive_equal = 'COLLATE utf8_bin ='
+    case_sensitive_equal = 'COLLATE utf8mb4_bin ='
 
     # TODO: AFAIK its version dependent for MySQL
     supports_subselects = False

This will be deferred to 2.4.0 because 2.3.0 is already in beta and changing the
DB level at this point would delay 2.3.0. I will have more time to look at this in
August and hopefully more people will be able to test it before the 2.4.0 release.

Check the email thread for other issues with that patch and the possible requirement
to have a charset binary collation setting as it may not be possible to derive a suitable
collation from the charset.

----------
components: Database
keywords: patch
messages: 7792
nosy: ngaba, rouilj
severity: normal
status: new
title: MySQL utf8mb4 issues
type: crash
versions: devel

_________________________________________________
Roundup tracker <[email protected]>
<https://issues.roundup-tracker.org/issue2551282>
_________________________________________________