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

Dear John,

1) Thanks for the detailed information. First of all, MySQL's partial
(3-bit) utf8 implementation is a shame. I am not happy... 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. :)

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

Hmm...

Regards,
Gábor

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


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