[MDEV-29345] update case insensitive (large) unique key with insensitive change of value - duplicate key Created: 2022-08-21  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.4.22, 10.6.9
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Critical
Reporter: Daniel Black Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 2
Labels: not-10.3

Issue Links:
Problem/Incident
is caused by MDEV-371 Unique indexes for blobs Closed
Relates
relates to MDEV-27653 long uniques don't work with unicode ... Closed

 Description    

create table x (id int auto_increment primary key, name varchar(2048) collate utf8_general_ci, unique key u(name));
 
insert into x values (1,'test');
 
update x set name = "Test" where id=1
 
 
 
Duplicate entry 'Test' for key 'u

A smaller varchar length ~200 will not generate an error.

Suspected MDEV-371 regression.

source: https://www.reddit.com/r/mariadb/comments/wu83pe/change_case_with_unique_constraint/

 Comments   
Comment by Marko Mäkelä [ 2022-08-22 ]

Here is a little simpler test case, not involving InnoDB at all:

create table t1 (name varchar(2048) collate utf8_general_ci, unique key u(name));
 
insert t1 set name='test';
 
update t1 set name='Test';
 
drop table t1;

I disabled MDEV-371 with the following:

diff --git a/sql/sql_table.cc b/sql/sql_table.cc
 
index 6e8a4795f21..f8f3eefc114 100644
 
--- a/sql/sql_table.cc
 
+++ b/sql/sql_table.cc
 
@@ -2441,6 +2441,8 @@ static inline void make_long_hash_field_name(LEX_CSTRING *buf, uint num)
 
 static Create_field * add_hash_field(THD * thd, List<Create_field> *create_list,
 
                                       KEY *key_info)
 
 {
 
+  my_error(ER_TOO_LONG_KEY, MYF(0), 1000);
 
+  return nullptr;
 
   List_iterator<Create_field> it(*create_list);
 
   Create_field *dup_field, *cf= new (thd->mem_root) Create_field();
 
   cf->flags|= UNSIGNED_FLAG | LONG_UNIQUE_HASH_FIELD;

With MDEV-371 disabled, the error ER_TOO_LONG_KEY would be output for anything longer than varchar(333). For shorter values, no duplicate key error will be flagged.

Generated at Thu Feb 08 10:07:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.