Heikki,

yes, actually I use the UTF8 character set.  Thanks for your
clarification and the workaround!

A few comments:

- For e-mail-addresses it's ok to set the column's character set to
latin1.  And in case I need UTF8 support in a referenced VARCHAR, I'll
limit it to 85 characters.  But about those four-byte sequences:  Will
that be implemented _after_ MySQL gets longer VARCHAR columns?  If not,
a new version with four-byte sequences would not be able to handle my
tables anymore, right?

- Maybe you should change the error message in SHOW INNODB STATUS ;)

Thanks again,

Sven



> -----Original Message-----
> From: Heikki Tuuri [mailto:[EMAIL PROTECTED] 
> Sent: Dienstag, 10. Februar 2004 14:19
> To: [EMAIL PROTECTED]
> Subject: Re: Foreign Key Reference to a VARCHAR
> 
> Sven,
> 
> are you using the UTF8 charset? Then a single character may 
> use up to 3 bytes. Since MySQL cannot work with index columns 
> longer than 255 bytes, for columns CHAR(86) or longer, MySQL 
> must define a 'column prefix' index, if you define an index 
> on the column. That is, MySQL internally creates an index of 
> type INDEX (colname(85)) instead of an index on the full column.
> And FOREIGN KEYs do not work on 'column prefix' indexes :(.
> 
> A workaround: use latin1 or latin1_german1_ci. Then 1 
> character only takes 1 byte.
> 
> In the future, MySQL will get longer CHAR and VARCHAR 
> columns. That will alleviate this problem. The current limit 
> 255 bytes is rather short.
> 
> I may also improve the foreign key check algorithm so that it 
> can work also on column prefix indexes.
> 
> http://www.mysql.com/doc/en/Charset-Unicode.html :
> 
> "
> The UTF8 character set (transform Unicode representation) is 
> an alternative way to store Unicode data. It is implemented 
> according to RFC2279. The idea of the UTF8 character set is 
> that various Unicode characters fit into byte sequences of 
> different lengths.
> 
> Basic Latin letters, digits, and punctuation signs use one byte.
> 
> Most European and Middle East script letters fit into a 
> two-byte sequence:
> extended Latin letters (with tilde, macron, acute, grave and 
> other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, 
> Syriac, and others.
> 
> Korean, Chinese and Japanese ideographs use three-byte sequences.
> 
> Currently, MySQL UTF8 support does not include four-byte sequences.
> "
> 
> Heikki
> Innobase Oy
> http://www.innodb.com
> InnoDB - transactions, row level locking, and foreign keys 
> for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB 
> which also backs up MyISAM tables
> 
> Order MySQL support from http://www.mysql.com/support/index.html
> 
> 
> ............
> List:MySQL General Discussion< Previous MessageNext Message > 
> From:Sven WoltmannDate:February 10 2004 1:12am 
> Subject:Foreign Key Reference to a VARCHAR
> 
> Hi,
> 
> I hope this is not a well known problem since I just signed 
> up to this list.
> But I checked the February archive and couldn't find anything on this.
> 
> I was trying for a couple of hours now to create a foreign 
> key reference on a varchar field:
> 
> CREATE TABLE users
> (
>     login            VARCHAR(20)          NOT NULL,
>     password         VARCHAR(20)          NOT NULL,
>     email_address    VARCHAR(100)         NOT NULL,
> --
>     PRIMARY KEY(login),
>     INDEX(email_address)
> )
> TYPE = InnoDB;
> 
> create table email_alias
> (
>     alias            VARCHAR(100)         NOT NULL,
>     email_address    VARCHAR(100)         NOT NULL,
> --
>     PRIMARY KEY(alias),
>     INDEX(email_address),
>     FOREIGN KEY (email_address) references users(email_address)
> )
> TYPE = InnoDB;
> 
> Actually my tables were a lot bigger, but I stripped them 
> down to these short tables to resolve my problem, which is:
> 
> When creating the second table, I get the error message:
> ERROR 1005 (HY000): Can't create table 
> './test/email_alias.frm' (errno: 150)
> 
> I admit, the first time I didn't put an INDEX on 
> email_address.  I figured that out quite fast.  Then I 
> searched again in the Newsgroups and did a SHOW INNODB 
> STATUS.  I got the following message:
> 
> "Cannot find an index in the referenced table where the 
> referenced columns appear as the first columns [...]"
> 
> Well - this didn't help at all :(  So I changed my table 
> definitions a hundred times to find out what exactly the 
> problem was.  And after hours, I found out:
> 
> The VARCHAR must not be longer than 85 characters.  If you 
> replace the "100"
> in the example above by a "85", IT WORKS!!!
> 
> So... have I missed the fine print in the documentation or 
> did I actually find a bug?
> 
> Here's my configuration:
> 
> - 4.1.1-alpha-standard-log
> - Official MySQL-standard binary
> - i686
> - pc-linux (debian 3.0 woody)
> 
> Sven
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to