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
