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

Hi Gabor:

In message <20230616213123.150b935f@Dell>,
Nagy Gabor writes:
>Thanks for detailed response.
You're welcome.

>[...] 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.)

I'm glad you are finding it useful. Please tell your friends 8-).

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

Sorry, I don't have an answer for you.

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

Hmm, the COLLATE looks wierd. Maybe that's how the utf8_general_ci
collation on the db gets displayed at the table level? (IIRC you said
utf8 is really utf8mb3 under the hood.)

>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

I may need to add a new collate setting or something.

>     if sys.version_info[0] > 2:
>-        command += ' CHARACTER SET utf8'
>+        command += ' CHARACTER SET utf8mb4'

This should be command += ' CHARACTER SET %s' %
config.RDBMS_MYSQL_CHARSET.  Using %s here I will claim is ok as the
source of the value is the administrator. Usually I use placeholders
tht are substituted at runtime to prevent SQL injection.

>     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 ='

Hmm, yeah..... I wonder if there is a standard for these. For a given
charset does:

   CHARSET_unicode_ci  (or CHARSET_generic_ci)
   CHARSET_bin

always exist?

>Discussion:
>1) The change to case_sensitive_equal = 'COLLATE utf8mb4_bin ='
>eliminates the Error in the subject of this email on utf8mb4 tables.

Good.

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

Yeah, that makes sense. The mb3 and mb4 are different lengths and it
kind of makes sense they are incompatible. I assume you can convert
your mb3 tables to mb4 wuing SQL.

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

Great.

>(The "COLLATE utf8_general_ci" change of Roundup v2.2.0 is not enough to
>achieve this on my system.)

Understood. Looking back, I don't see why it would have worked.


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

Well, for newly created tables yes. For people upgrading they will
start reporting the erros you ran into with mb4 queries running
against mb3 tables.

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

If the CHARSET_* forms I mention above work, then I should be able to
handle the conversion. Apparently the unicode_ci forms perform worse
than the general_ci, but are more correct. There are also newer
varients unicode__unicode_520_ci. I hate to do it but I think I need
all three settings:

   mysql_charset
   mysql_collation_case
   mysql_collation_ignore_case

or some such.

At least one report I saw was that japanese collation was broken using
the unicode variants.

These changes won't make it into 2.3.0 I'm afraid. Unlike your other
change, this is playing with the data storage layer. So it needs some
more testing and planning. I guess I have to learn more mysql to
create tables and see if I can do some sort of correction/migration
via roundup-admin.

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

My pleasure. It was simple enough and just "looked right" (tm) (R). I
was able to walk though it by hand in the debugger and verify a few
cases. So I put it in untested. Not sure if it will come back to bite
me.

Have a great rest of your week.

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