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

Dear John,

Thank you for the quick reply.

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

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...) So I think this
problem only appears when you upgraded mysql. (Which is good news, this
is the "normal" use case, I guess.)

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

Regards,
Gábor

> In message <20230527093237.5d33f361@Dell>,
> Nagy Gabor writes:
> >Dear Roundup Users,
> >
> >I am using Roundup 2.1.0 and MariaDB 10.9.4.
> >In a detector (auditor), the following code leads to the error
> >mentioned in the subject of this email:
> >
> >conflict = cl.filter(None, filterspec={}, retired=True,
> >exact_match_spec={'name':str(newdata['name'])})
> >
> >The problem disappears when I interchange the dictionaries passed
> >to filterspec and exact_match_spec. I do not know what is the
> >problem here, I have an other auditor with exact_match_spec filter,
> >and it works as expected. I do not understand what is the problem
> >here.
> >
> >I have 
> >mysql_charset = utf8
> >set in config.ini.
> >
> >Or should I file a bug report for this?  
> 
> What version of Python are you running?
> 
> If python2, maybe this issue applies?
> 
>   https://issues.roundup-tracker.org/issue2551216
> 
> Does this happen with all values for 'name', or just particular
> ones. Also why do you need the str() call?
> 
> Ralf, any thoughts here on why swapping from exact_match_spec to
> filterspec doen't trigger an error?
> 
> --
> 				-- 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