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

Hi Gabor:

In message <20230528101406.540290ed@Dell>, Nagy Gabor writes:
>I am using python3 (3.10.9), and str() is not necessary, I added for
>sanity check of the argument. (Btw, newdata is the same as in
>detectors API).

Understood.

>I did some digging, and I found that the problem is caused by a mariadb
>update:
>
>If I register the same auditor to an old class, say, the good old
>'status' class, the detector runs as expected. I got this COLLATION
>error when I register to this auditor to a "new" class, which was
>created some days ago (by editing schema.py).
>
>I am not a mysql expert, but I see some differences between the two
>mysql tables of these classes. After mysqldump, I see the following:
>
>For the '_status' table, I see
>CREATE TABLE `_status` (
>...
>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
>
>For the new '_teamtagok' table, I see
>CREATE TABLE `_teamtag` (
>...
>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>
>I do not know what is reason for this, I suspect utf8mb4 is a newer
>version of mysql's utf8 charset, and utf8mb3 is an older version, and
>after my mariadb upgrade, the setting "mysql_charset = utf8"  implied
>that new tables were created using utf8mb4. (Great...)

Interesting.  From the error you report, it sounds like the collation
used by the Roundup connection is using utf8mb3 when talking to a
utf8mb4 table. I wonder if after the MariaDB upgrade new tables are
created with utf8mb4. However specifying utf8 in Roundup causes the
Roundup connection to use utf8mb3 and set the collation to mb3?

I am just guessing here, but the error seems to indicate this as a
possibility.

I hesitate to suggest that you try changing the mysql_charset in
config.ini to utf8mb4. It may fail in the opposite way. Also I don't
know if that will cause any state changes on MariaDB that aren't
reversed if you change the mysql_charset back to utf8.

I still don't understand why this happens only for

   conflict = cl.filter(None, filterspec={}, retired=True, exact_match_spec={'name':str(newdata['name'])})

and not for:

  conflict = cl.filter(None, filterspec={'name':str(newdata['name'])}, retired=True, exact_match_spec={})

maybe Ralf has some idea? My guess is that exact match uses '=' rather
than 'like' and collation matters somehow with '='? That doesn't
make sense to me intuitively (then again mysql doesn't make sense to
me intuitively so...).

>So I think this problem only appears when you upgraded mysql. (Which
>is good news, this is the "normal" use case, I guess.)

Well Roundup should work even across database (including mysql)
upgrades. However the changes they have made in encoding from latin1
-> utf8 (utf8mb4) is not something that Roundup is designed to handle
transparently.

You might want to upgrade to utf8mb4 on all your tables. This

 https://adamhooper.medium.com/in-mysql-never-use-utf8-use-utf8mb4-11761243e434

discusses what utf8 actually means and why it's a problem. It matches
my understanding of why we started using utf8mb4 as the default in
newer Roundup releases. Sadly neither I nor any of the core developers
runs mysql/mariadb AFAIK. I run sqlite for my test trackers and ran
Postgresql in production.

This link (from the article above):

 https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4

discusses how to change from utf8 to utf8mb4 (utf8 multibyte 4
character) using mysql (which should work with mariadb). The Roundup
upgrading document describes how do do this using the 1.6.0 and 2.0.0
versions of Roundup, but only works if you start with a 1.6.x (python2
only) install.

The errata at:

 https://wiki.roundup-tracker.org/ReleaseErrata#Mysql_database_conversion_python_2

describes a method (untested) on performing a MySQL/MariaDB conversion
of character set type from latin1 to utf8. I expect it would work if
utf8 is replaced with utf8mb4.

>I do not know if there is any problem on the client (I mean roundup)
>side. But I noticed that in roundup 2.1.0's configuration.py, the
>default mysql_charset was changed to 'utf8mb4' from 'utf8'...

https://issues.roundup-tracker.org/issue2551115 triggered the utf8 ->
utf8mb4 change.

I hope this helps. Let us know how you fix this and if you need more
help. Having a report of a successful utf8 -> utf8mb4 conversion would
be useful. I can add it to the Errata page.

Make sure to take a backup (and test a restore) of your db before making
any changes.

Have a great week.

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