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

Hi John,

Thanks for detailed response. Today I had time for some testing.
It is important to note that I am not very familiar in MySQL, I just
report some observations. (One of the good things in Roundup that I do
not have to deal with direct SQL commands. I was very happy - and
surprised - when I found Roundup, a very flexible bug tracker: That is
exactly what I looked for, I did not believe such an open source
project exists. It took a while until I understood how Roundup works,
but now I find it very handy.)

First of all, I have no clue how I managed to create utf8mb4 encoded
tables in my Roundup's sql database. Now I cannot create utf8mb4 table
with Roundup 2.1.0 or 2.2.0 (no sql patches applied) even if I set
mysql_charset = utf8mb4 in config.ini. This is mystery how my Roundup
created such tables
(I did not upgrade or configure mariadb after my utf8mb4 tables were
created.)

Now I think mysql_charset = utf8mb4 has no real effect in Roundup
2.1.0. The only effect I see in the mysqldump of the database is:

/*!40101 SET NAMES utf8mb4 */,

but the databases still looks like this:

CREATE TABLE `_status` (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


Based on your previous email, the following patch (of back_mysql.py of
v2.1.0) solves the problems, plus the mysql_charset = utf8mb4 setting
in config.ini:

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

Discussion:
1) The change to case_sensitive_equal = 'COLLATE utf8mb4_bin ='
eliminates the Error in the subject of this email on utf8mb4 tables.
(Unfortunately, utf8mb4_bin does not seem to be backward compatible,
because if I do exact_match_spec search with this patch applied in an
utf8mb3 table, I get the same error with the numbers 3 and 4
interchanged. Great...)

2) The patches on db_create() has the effect that 'roundup-admin
initalise' finally creates utf8mb4 encoded tables. I also tested the
implicit table creation (when you add a new class to schema.py), those
tables are also created with utf8mb4.
(The "COLLATE utf8_general_ci" change of Roundup v2.2.0 is not enough to
achieve this on my system.)

3) MySQL 8.0 documentation says this in
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html :
"utf8: An alias for utf8mb3. In MySQL 8.0, this alias is deprecated;
use utf8mb4 instead. utf8 is expected in a future release to become an
alias for utf8mb4."
So if we wait long enough, this problem might disappear automatically.
:)

+1) Of course, these hardcoded "utf8mb4 commands" in the source code are
ugly (those are proof of concept codes), this should be somehow
configurable, or the code should figure out (from mysql_charset) these
codeparts.

Regards,
Gábor

P.S. I also thank you for your quick response to my bug report for
the sorted() method of the class MultilinkHTMLProperty.

> 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.


_______________________________________________
Roundup-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/roundup-users