MySQL Bugs: #108990: Update integer value via integer key raises collation error

Bug #108990	Update integer value via integer key raises collation error
Submitted:	3 Nov 2022 14:59	Modified:	22 Nov 2022 12:20
Reporter:	Drazen Herendic	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	8.0.29	OS:	Debian (11)
Assigned to:		CPU Architecture:	x86
Tags:	COllation bug

Description:
I wanted to update integer value via integer PK.
update table1 set validity = 121 where id = 5
I got error: Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

Collation variables:
collation_connection utf8mb4_0900_ai_ci
collation_database utf8_unicode_ci
collation_server utf8_unicode_ci

Character set variables:
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb3
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb3
character_set_system utf8mb3
character_sets_dir /usr/share/mysql-8.0/charsets/

Table DDL:

CREATE TABLE `table1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`description` varchar(100) DEFAULT '',
`description_name` varchar(100) DEFAULT NULL,
`validity` int NOT NULL,
`active_from` datetime NOT NULL,
`active_to` datetime NOT NULL,
`status` int NOT NULL,
`type` int DEFAULT NULL,
`group` int DEFAULT '1',
PRIMARY KEY (`id`),
KEY `idx_table1_date_from` (`active_from`,`active_to`,`status`),
KEY `idx_table1_type` (`type`),
KEY `idx_table1_group` (`group`)
) ENGINE=InnoDB AUTO_INCREMENT=12211 DEFAULT CHARSET=utf8mb3;

Table has more integer fields at the end, I have truncated that part. The only char colums are shown above.

Information schema say that table has collation utf8_general_ci.
Information schema say that all three char columns have character set utf8mb3 and collation utf8_general_ci.

No matter what collation I tried to force in the update statement, error was always the same.

For test, I created another table using create table statement of affected table. Then I filled it with all the data from the original. Update works on this cloned table.

I tried also this: I altered collation of the table to be the same as database and server, utf8_unicode_ci.
alter table table1 convert to character set utf8mb3 COLLATE utf8_general_ci;
It didn't help, error message is the same so I changed collation back to as it was.

I also tried more combinations to alter the table, to align it with database collation. Even switching it to utf8mb4 and changing collation didn't help.

I think I restarted mysql once, just to try it. As expected, it didn't help.
I'm stuck on this error and I don't know what else I can do to figure out what to do at all to move from standstill.

On top of this, there are a couple more tables that have this issue.

How to repeat:
I don't know if there is a way to repeat but to connect to this exact server to witness this behavior.
As I wrote in description, I cloned affected table and clone has no issue.

Suggested fix:
I'm glad to try any suggestion you have to identify the root cause of this behavior.

Hi Mr. Herendic,

Thank you for your bug report.

However, we need a fully reproducible test case. The only thing that is missing is one INSERT command for several rows, which would also cover the id of 5.

We are waiting on your feedback.

Hello.

When I try to insert record with PK 5, I get regular response:
Error Code: 1062. Duplicate entry '5' for key 'ad_priceplans.PRIMARY'

INSERT INTO table1 (id, name, description, description_name, validity, active_from, active_to, status, type, group) 
values (5, 'test name', 'test name', 'test description name', 35, '2022-10-01', '2023-10-01', 1, 3, 4);

When I try to insert a new record with unused PK value, I get the same collation error as initially mentioned:
Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

INSERT INTO table1 (id, name, description, description_name, validity, active_from, active_to, status, type, group) 
values (12200, 'test name', 'test name', 'test description name', 35, '2022-10-01', '2023-10-01', 1, 3, 4);

Among other things, I tried the select below to get more additional info about columns. I have tried some time ago to look into the source code to figure out something about the issue, but I gave up, it's not so simple. :)
Anyway, select:
select id, collation(validity), coercibility(validity), collation(id), coercibility(id), collation(name), coercibility(name) from table1 where id = 5;
Result:
5	binary	5	binary	5	utf8_general_ci	2

I would like to mention, this server is the test server and I would rather find out what is going on sooner, than to witness this behavior on production server without knowing anything about what is going on.
Remark: production server has version 8.0.25

Hi Mr. Herendic,

We were not able to reproduce the behaviour that you report, with the data that you provided.

We need fully repeatable test case, with all data, which also means with all rows in the table that are required to see that behaviour.

We will attempt to repeat the behaviour in 8.0.31.

We also need a full query and all the settings.

Can't repeat.

Thank you for your quick response.

As I mentioned before, after I created local copy of problematic table inside the same schema (same DDL, same data), new table had no issues, compared to the original.
So, very probably you won't observe this behavior in your lab.
Regarding the data to send to you, I'm not allowed to send it as-is, since it's business data not to be easily shared (maybe it would require NDA).

I spoke to my boss and we concluded that our only option currently is to create new table and align everything else to the new one.

But before I do that, my hope is that you can give me some hint what else I can check on the server to get more diagnostic information about possible causes of the behavior.
Any hints, tips and tricks are welcome.

Thank you, Mr. Herendic.

Hello.

I have some relevant news.
It turned out that one trigger was making problems on several tables that use it.
I just checked my previous texts, I haven't mention any triggers before.

The only statement in the trigger (after insert, update, delete)
    UPDATE `cache_table` SET `timestamp` = UNIX_TIMESTAMP() WHERE `name` = tbl_name;

Now I force the collation to align with the table/field collation of this update:
    UPDATE `cache_table` SET `timestamp` = UNIX_TIMESTAMP() WHERE `name` = tbl_name COLLATE utf8_general_ci;

If this was obvious to me then, I wouldn't have opened this ticket at first place.
Good thing is there is no bug here. :)
I think you can close this ticket.

Thank you.

Hi Mr. Herendic,

You are truly welcome.