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

Hi Gabor:

I'm sorry this is becoming such a chore.

In message <20230530144304.3e55d8e3@Dell>, Nagy Gabor writes:
>1) Thanks for the detailed information. First of all, MySQL's partial
>(3-bit) utf8 implementation is a shame. I am not happy...

I agree.

>I have to convert my Roundup databases to utf8mb4 to support all utf8
>characters, and it is not so evident as it sounds (thanks for the
>links, btw):
>https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html
>I have to check that the "maximum permitted length" limitation does
>not break anything, so I have to understand Roundup's SQL database,
>which I did not wanted. :)

I took a quick scan of the code in rdbms_common.py and back_mysql.py
in the backends directory.

From that it looks like VARCHAR(255) is used for single character
(I.E. ANSI/ASCII character set) represented values. For example
encoded passwords, intervals. One Time Keys used to be VARCHAR but
were converted to TEXT. So the conversion shouldn't matter as the
fields will be the same size before/after.

The String type in Roundup is represented as TEXT in mysql. While
strings can be large, files and message bodies are stored outside of
the database. So in general, you shouldn't have BLOBs or
multi-megabyte TEXT fields.

>2) After converting my test database to utf8mb4, and setting
>mysql_charset = utf8mb4 in config.ini, the bug still persists, I got
>precisely the same error message. Where does this utf8mb3_bin is come
>from?... I even reinitalised the database with roundup-admin, with no success.
>
>After doing a roundup-admin initialise with mysql_charset = utf8mb4,
>mysql_dump showed lots of utf8mb3 databases, so its seems mysql_charset
>had no effect. Strange.

Indeed. I would expect that to have solved the problem.
You are testing with 2.1.0 right? The changelog for 2.2.0 has:

- issue2551216 - create new mysql databases using COLLATE
  utf8_general_ci to prevent crashes in test suite. (John Rouillard)

which was a no-op as far as I could tell with Python 3, but I may not
have had the correct data in the db to trigger the issue. The patch for
that change is:

diff -r eccb2f53566d -r 38e0fc1c7f11 roundup/backends/back_mysql.py
--- a/roundup/backends/back_mysql.py    Tue Jul 05 19:45:12 2022 -0400
+++ b/roundup/backends/back_mysql.py    Sun Jul 10 15:49:39 2022 -0400
@@ -92,7 +92,7 @@
     kwargs = connection_dict(config)
     conn = MySQLdb.connect(**kwargs)
     cursor = conn.cursor()
-    command = "CREATE DATABASE %s"%config.RDBMS_NAME
+    command = "CREATE DATABASE %s COLLATE utf8_general_ci"%config.RDBMS_NAME
     if sys.version_info[0] > 2:
         command += ' CHARACTER SET utf8'
     logging.info(command)

Maybe try making this change and re-test? Also I note that the
hardcoded 'CHARACTER SET utf8' you mention below is seen in this diff.

>First I thought there are some problems in my
>mariadb .cnf files, but I found some strange hardcoded lines in
>backends/back_mysql.py:
>
>In db_create():
>    if sys.version_info[0] > 2:
>        command += ' CHARACTER SET utf8'
>
>In class MysqlClass:
>    case_sensitive_equal = 'COLLATE utf8_bin ='

Try changing the character set to use utf8mb4 and see if that
helps. Running 'python3 -m pytest test/test_mysql.py' should turn up
any major issues I hope.

My best guess is that you are onto something with

    case_sensitive_equal = 'COLLATE utf8_bin ='

The file rdbms_common.py sets

    case_sensitive_equal = '='

and uses that property in queries in _filter_sql for exact match if I
understand the code correctly. This matches your observations about it
only happening with exact match.

Ralf wrote that code so he would know if I am misinterpreting it
incorrectly. There is a "utf8mb4_bin" collation. Maybe try changing
'utf8_bin' for 'utf8mb4_bin' see if it gets rid of the utf8mb3
failure.

Maybe:

   https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

will help? It seems to indicate that utf8mb4_unicode_ci or
utf8mb4_general_ci are possible replacements for utf8_general_ci.

  https://forums.mysql.com/read.php?103,187048,188748#msg-188748

and the stackoverflow answer seem to indicate that my utf8_general_ci
should be replaced by utf8_unicode_ci and I assume the same would be
true for utf8mb4.

We don't target/recommend a specific version of MySQL/MariaDB. However
MySQL 8.0 (with utf8mb4* collations IIUC) has been out since 2016, so
more than the 5 years of support given my most LTS Linux releases. If
the utf8mb4 variants work for you, we can consider requiring mysql >
8.0 for the 2.4.0 release next year.

The test suite could use some help from somebody who understands
unicode better than I do and can create a test that sorts differently
between utf8[mb4]_general_ci and utf8[mb4]_unicode_ci.


--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.