Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24009

alter table ~collate utf8mb4_unicode_ci

    XMLWordPrintable

    Details

      Description

      We have confirmed that there is a problem with the collation process of utf8mb4_unicode_ci.
      In general, we have seen that MariaDB manages the values of empty space('') and char(0) differently.
      After that, as a result of performing the character set/collation change work, in utf8mb4_unicode_ci, the above acronyms were duplicated.
      It was confirmed that a duplicate entry'' for key'PRIMARY' error occurred.
      Please refer to the test results below.

       
      MariaDB [test]> status;
      --------------
      mysql  Ver 15.1 Distrib 10.3.24-MariaDB, for Linux (x86_64) using readline 5.1
       
      Connection id:		766474
      Current database:	test
      Current user:		root@localhost
      SSL:			Not in use
      Current pager:		stdout
      Using outfile:		''
      Using delimiter:	;
      Server:			MariaDB
      Server version:		10.3.24-MariaDB-log MariaDB Server
      Protocol version:	10
      Connection:		Localhost via UNIX socket
      Server characterset:	utf8mb4
      Db     characterset:	utf8mb4
      Client characterset:	utf8mb4
      Conn.  characterset:	utf8mb4
      ...
       
      MariaDB [test]> create table t1( c1 varchar(100) character set utf8 not null, nm varchar(100) , primary key(c1)) engine=innodb character set utf8;
      Query OK, 0 rows affected (0.176 sec)
       
      MariaDB [test]> insert into t1 values('','');
      Query OK, 1 row affected (0.074 sec)
       
      MariaDB [test]> insert into t1 values(char(0),'');
      Query OK, 1 row affected (0.088 sec)
       
      MariaDB [test]> select * from t1 where c1='';
      +----+------+
      | c1 | nm   |
      +----+------+
      |    |      |
      +----+------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select * from t1 where c1=char(0);
      +----+------+
      | c1 | nm   |
      +----+------+
      |    |      |
      +----+------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> alter table t1 character set utf8mb4;
      Query OK, 0 rows affected (0.065 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> 
      MariaDB [test]> alter table t1 modify c1 varchar(100) character set utf8mb4 collate utf8mb4_unicode_ci not null, modify nm varchar(100) character set utf8mb4 collate utf8mb4_unicode_ci ;
      ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY' <------------------------
       
      MariaDB [test]> alter table t1 modify c1 varchar(100) character set utf8mb4 collate utf8mb4_general_ci not null, modify nm varchar(100) character set utf8mb4 collate utf8mb4_general_ci ;
      Query OK, 2 rows affected (0.476 sec)              
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> O.M.G.
      
      

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            kyoung-yeon ssauravy
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.