List:Bugs« Previous MessageNext Message »
From:Linus Upson Date:July 4 2004 1:47am
Subject:RE: unique key and utf8
View as plain text  
Many of the mysql test cases fail when I configure 4.1.3 with
'--with-charset=utf8'. The symptoms are similar to the index truncation
problem I reported with 4.1.1.

Here is how mysql was configured:

C compiler:    gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-34)
C++ compiler:  g++ (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-34)
Environment:
System: Linux seek 2.4.21-15.0.2.ELsmp #1 SMP Wed Jun 16 22:52:07 EDT 2004
i686 i686 i386 GNU/Linux
Architecture: i686

./configure --prefix=/home/linus/mysql/utf8 --with-tcp-port=8506
--with-unix-socket-path=/home/linus/mysql/utf8/tmp/mysql.sock
--with-charset=utf8

The full test results from the following command are attached.

$ ./mysql-test-run --force > test-utf8.txt

I also tried to configure mysql using the same configure command but without
the '--with-charset=utf8' option. More of the tests ran, but it eventually
failed with:

...
ctype_mb                       [ pass ]
ctype_recoding                 [ pass ]
ctype_sjis                     [ skipped ]
ERROR: /home/linus/mysql/utf8/mysql-test/var/run/master.pid was not created
in 30 seconds;  Aborting

I've attached the complete test output as test-default-charset.txt.

Thanks,
Linus


-----Original Message-----
From: Sergei Golubchik [mailto:serg@stripped] 
Sent: Wednesday, June 30, 2004 2:09 PM
To: Linus Upson
Cc: bugs@stripped; bar@stripped
Subject: Re: unique key and utf8


Hi!

On Jun 30, Linus Upson wrote:
> The following was tested with MySQL 4.1.1 running on RedHat ES 3. This 
> message was sent in UTF-8.
> 
> The unique key index prefix length is misleading when you use the utf8 
> character set. If you specify a prefix of N characters the uniqueness 
> constraint appears to be enforced for the first 3*N bytes of column, 
> not the first N characters.

Oops.
I agree, it's confusing.
We'll fix it.
 
> I discovered this problem because mysql silently shortened the unique 
> index prefix on one of my utf8 varchar columns. When I execute the 
> following statement:
> 
> CREATE TABLE `t` (
>   `c` varchar(150) NOT NULL default '',
>   UNIQUE KEY `c` (`c`(150))
> ) TYPE=MyISAM DEFAULT CHARSET=utf8
> 
> there are no errors or warnings, but the result is:
> 
> mysql> show create table t \G
> *************************** 1. row ***************************
>        Table: t
> Create Table: CREATE TABLE `t` (
>   `c` varchar(150) NOT NULL default '',
>   UNIQUE KEY `c` (`c`(64))
> ) TYPE=MyISAM DEFAULT CHARSET=utf8
> 1 row in set (0.00 sec)
> 
> The 150 character prefix should still fit within the 500 byte index 
> limit of 4.1.1 since 3 * 150 = 450, but for some reason the prefix has 
> been reduced to 64.

I wasn't able to repeat in in MySQL 4.1.3 - index was created as expected.
MySQL *never* silently truncates a UNIQUE index (if it does, it's a bug).
 
Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer /_/
/_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com

Installing Test Databases
Removing Stale Files
Installing Master Databases
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=..
--datadir=mysql-test/var/master-data --skip-innodb --skip-ndbcluster --skip-bdb     
Installing Slave Databases
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=..
--datadir=mysql-test/var/slave-data --skip-innodb --skip-ndbcluster --skip-bdb     
Manager disabled, skipping manager start.
Loading Standard Test Databases
Starting Tests

 TEST                           RESULT
------------------------------------------
alias                          [ fail ]

Errors are (from /home/linus/mysql/utf8/mysql-test/var/log/mysqltest-time) :
/home/linus/mysql/utf8/bin/mysqltest: At line 65: query 'SELECT
ELT(FIELD(kundentyp,'PP','PPA','PG','PGA','FK','FKA','FP','FPA','K','KA','V','VA',''),
'Privat (Private Nutzung)','Privat (Private Nutzung) Sitz im Ausland','Privat
(geschaeftliche Nutzung)','Privat (geschaeftliche Nutzung) Sitz im Ausland','Firma
(Kapitalgesellschaft)','Firma (Kapitalgesellschaft) Sitz im Ausland','Firma
(Personengesellschaft)','Firma (Personengesellschaft) Sitz im Ausland','oeff. rechtl.
Koerperschaft','oeff. rechtl. Koerperschaft Sitz im Ausland','Eingetragener
Verein','Eingetragener Verein Sitz im Ausland','Typ unbekannt') AS Kundentyp ,kategorie
FROM t1 WHERE hdl_nr < 2000000 AND kategorie IN ('Prepaid','Mobilfunk') AND st_klasse
= 'Workflow' GROUP BY kundentyp ORDER BY kategorie' failed: 1271: Illegal mix of
collations for operation 'field'
(the last lines may be the most important ones)

Ending Tests
Shutting-down MySQL daemon

Master shutdown finished
Slave shutdown finished
Resuming Tests

alter_table                    [ pass ]
analyse                        [ fail ]

Errors are (from /home/linus/mysql/utf8/mysql-test/var/log/mysqltest-time) :
/home/linus/mysql/utf8/bin/mysqltest: At line 41: Result length mismatch
(the last lines may be the most important ones)
Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/analyse.result	2004-07-04 03:33:44.000000000 +0300
--- r/analyse.reject	2004-07-04 04:22:57.000000000 +0300
***************
*** 19,31 ****
  test.t1.bool	N	Y	1	1	0	0	1.0000	NULL	ENUM('N','Y') NOT NULL
 
test.t1.d	2002-03-03	2002-03-05	10	10	0	0	10.0000	NULL	ENUM('2002-03-03','2002-03-04','2002-03-05')
NOT NULL
  create table t2 select * from t1 procedure analyse();
  select * from t2;
 
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
! test.t1.i	1	7	1	1	0	0	4.0000	2.2361	ENUM('1','3','5','7') NOT NULL
! test.t1.j	2	8	1	1	0	0	5.0000	2.2361	ENUM('2','4','6','8') NOT NULL
  test.t1.empty_string			0	0	4	0	0.0000	NULL	CHAR(0) NOT NULL
  test.t1.bool	N	Y	1	1	0	0	1.0000	NULL	ENUM('N','Y') NOT NULL
!
test.t1.d	2002-03-03	2002-03-05	10	10	0	0	10.0000	NULL	ENUM('2002-03-03','2002-03-04','2002-03-05')
NOT NULL
  drop table t1,t2;
  EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE();
  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
--- 19,35 ----
  test.t1.bool	N	Y	1	1	0	0	1.0000	NULL	ENUM('N','Y') NOT NULL
 
test.t1.d	2002-03-03	2002-03-05	10	10	0	0	10.0000	NULL	ENUM('2002-03-03','2002-03-04','2002-03-05')
NOT NULL
  create table t2 select * from t1 procedure analyse();
+ Warnings:
+ Warning	1265	Data truncated for column 'Optimal_fieldtype' at row 4
+ Warning	1265	Data truncated for column 'Optimal_fieldtype' at row 4
+ Warning	1265	Data truncated for column 'Optimal_fieldtype' at row 4
  select * from t2;
 
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
! test.t1.i	1	7	1	1	0	0	4.0000	2.2361	ENUM('1','3','5','7')
! test.t1.j	2	8	1	1	0	0	5.0000	2.2361	ENUM('2','4','6','8')
  test.t1.empty_string			0	0	4	0	0.0000	NULL	CHAR(0) NOT NULL
  test.t1.bool	N	Y	1	1	0	0	1.0000	NULL	ENUM('N','Y') NOT NULL
! test.t1.d	2002-03-03	2002-03-05	10	10	0	0	10.0000	NULL	ENUM('2002-03-03','200
  drop table t1,t2;
  EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE();
  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
***************
*** 36,51 ****
  show create table t2;
  Table	Create Table
  t2	CREATE TABLE `t2` (
!   `Field_name` char(255) NOT NULL default '',
!   `Min_value` char(255) default NULL,
!   `Max_value` char(255) default NULL,
    `Min_length` bigint(11) NOT NULL default '0',
    `Max_length` bigint(11) NOT NULL default '0',
    `Empties_or_zeros` bigint(11) NOT NULL default '0',
    `Nulls` bigint(11) NOT NULL default '0',
!   `Avg_value_or_avg_length` char(255) NOT NULL default '',
!   `Std` char(255) default NULL,
!   `Optimal_fieldtype` char(64) NOT NULL default ''
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  select * from t1 where 0=1 procedure analyse();
 
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
--- 40,55 ----
  show create table t2;
  Table	Create Table
  t2	CREATE TABLE `t2` (
!   `Field_name` char(85) character set utf8 NOT NULL default '',
!   `Min_value` char(85) character set utf8 default NULL,
!   `Max_value` char(85) character set utf8 default NULL,
    `Min_length` bigint(11) NOT NULL default '0',
    `Max_length` bigint(11) NOT NULL default '0',
    `Empties_or_zeros` bigint(11) NOT NULL default '0',
    `Nulls` bigint(11) NOT NULL default '0',
!   `Avg_value_or_avg_length` char(85) character set utf8 NOT NULL default '',
!   `Std` char(85) character set utf8 default NULL,
!   `Optimal_fieldtype` char(22) character set utf8 NOT NULL default ''
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  select * from t1 where 0=1 procedure analyse();
 
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
***************
*** 55,70 ****
  show create table t2;
  Table	Create Table
  t2	CREATE TABLE `t2` (
!   `Field_name` char(255) NOT NULL default '',
!   `Min_value` char(255) default NULL,
!   `Max_value` char(255) default NULL,
    `Min_length` bigint(11) NOT NULL default '0',
    `Max_length` bigint(11) NOT NULL default '0',
    `Empties_or_zeros` bigint(11) NOT NULL default '0',
    `Nulls` bigint(11) NOT NULL default '0',
!   `Avg_value_or_avg_length` char(255) NOT NULL default '',
!   `Std` char(255) default NULL,
!   `Optimal_fieldtype` char(64) NOT NULL default ''
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  select * from t2;
 
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
--- 59,74 ----
  show create table t2;
  Table	Create Table
  t2	CREATE TABLE `t2` (
!   `Field_name` char(85) character set utf8 NOT NULL default '',
!   `Min_value` char(85) character set utf8 default NULL,
!   `Max_value` char(85) character set utf8 default NULL,
    `Min_length` bigint(11) NOT NULL default '0',
    `Max_length` bigint(11) NOT NULL default '0',
    `Empties_or_zeros` bigint(11) NOT NULL default '0',
    `Nulls` bigint(11) NOT NULL default '0',
!   `Avg_value_or_avg_length` char(85) character set utf8 NOT NULL default '',
!   `Std` char(85) character set utf8 default NULL,
!   `Optimal_fieldtype` char(22) character set utf8 NOT NULL default ''
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  select * from t2;
 
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
***************
*** 78,93 ****
  show create table t2;
  Table	Create Table
  t2	CREATE TABLE `t2` (
!   `Field_name` char(255) NOT NULL default '',
!   `Min_value` char(255) default NULL,
!   `Max_value` char(255) default NULL,
    `Min_length` bigint(11) NOT NULL default '0',
    `Max_length` bigint(11) NOT NULL default '0',
    `Empties_or_zeros` bigint(11) NOT NULL default '0',
    `Nulls` bigint(11) NOT NULL default '0',
!   `Avg_value_or_avg_length` char(255) NOT NULL default '',
!   `Std` char(255) default NULL,
!   `Optimal_fieldtype` char(64) NOT NULL default ''
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  select * from t2;
 
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
--- 82,97 ----
  show create table t2;
  Table	Create Table
  t2	CREATE TABLE `t2` (
!   `Field_name` char(85) character set utf8 NOT NULL default '',
!   `Min_value` char(85) character set utf8 default NULL,
!   `Max_value` char(85) character set utf8 default NULL,
    `Min_length` bigint(11) NOT NULL default '0',
    `Max_length` bigint(11) NOT NULL default '0',
    `Empties_or_zeros` bigint(11) NOT NULL default '0',
    `Nulls` bigint(11) NOT NULL default '0',
!   `Avg_value_or_avg_length` char(85) character set utf8 NOT NULL default '',
!   `Std` char(85) character set utf8 default NULL,
!   `Optimal_fieldtype` char(22) character set utf8 NOT NULL default ''
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  select * from t2;
 
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
-------------------------------------------------------
Please follow the instructions outlined at
http://www.mysql.com/doc/en/Reporting_mysqltest_bugs.html
to find the reason to this problem and how to report this.

Ending Tests
Shutting-down MySQL daemon

Master shutdown finished
Slave shutdown finished
Resuming Tests

ansi                           [ pass ]
archive                        [ skipped ]
auto_increment                 [ pass ]
backup                         [ pass ]
bdb-alter-table-1              [ skipped ]
bdb-alter-table-2              [ skipped ]
bdb-crash                      [ skipped ]
bdb-deadlock                   [ skipped ]
bdb                            [ skipped ]
bdb_cache                      [ skipped ]
bench_count_distinct           [ pass ]
bigint                         [ pass ]
binary                         [ fail ]

Errors are (from /home/linus/mysql/utf8/mysql-test/var/log/mysqltest-time) :
/home/linus/mysql/utf8/bin/mysqltest: At line 11: query 'insert into t1 values ("Installing Test Databases
Removing Stale Files
Installing Master Databases
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=..
--datadir=mysql-test/var/master-data --skip-innodb --skip-ndbcluster --skip-bdb     
Installing Slave Databases
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=..
--datadir=mysql-test/var/slave-data --skip-innodb --skip-ndbcluster --skip-bdb     
Manager disabled, skipping manager start.
Loading Standard Test Databases
Starting Tests

 TEST                           RESULT
------------------------------------------
alias                          [ pass ]
alter_table                    [ pass ]
analyse                        [ pass ]
ansi                           [ pass ]
archive                        [ skipped ]
auto_increment                 [ pass ]
backup                         [ pass ]
bdb-alter-table-1              [ skipped ]
bdb-alter-table-2              [ skipped ]
bdb-crash                      [ skipped ]
bdb-deadlock                   [ skipped ]
bdb                            [ skipped ]
bdb_cache                      [ skipped ]
bench_count_distinct           [ pass ]
bigint                         [ pass ]
binary                         [ pass ]
bool                           [ pass ]
bulk_replace                   [ pass ]
case                           [ pass ]
cast                           [ pass ]
check                          [ pass ]
comments                       [ pass ]
compare                        [ pass ]
connect                        [ pass ]
constraints                    [ pass ]
count_distinct                 [ pass ]
count_distinct2                [ pass ]
count_distinct3                [ pass ]
create                         [ pass ]
ctype_big5                     [ skipped ]
ctype_collate                  [ pass ]
ctype_cp1251                   [ pass ]
ctype_create                   [ pass ]
ctype_latin1                   [ pass ]
ctype_latin1_de                [ pass ]
ctype_many                     [ skipped ]
ctype_mb                       [ pass ]
ctype_recoding                 [ pass ]
ctype_sjis                     [ skipped ]
ERROR: /home/linus/mysql/base/mysql-test/var/run/master.pid was not created in 30
seconds;  Aborting

Thread
unique key and utf8Linus Upson30 Jun
  • Re: unique key and utf8Sergei Golubchik30 Jun
    • RE: unique key and utf8Linus Upson4 Jul
      • Re: unique key and utf8Sinisa Milivojevic12 Jul