Sven, yes, the error message is misleading. And we must get longer CHARs and VARCHARs to MySQL. It is not good to silently change internal index definitions!
I guess that 4-byte UTF8 characters are not needed. You can code 16 million characters with 3 bytes. That should be enough for all languages on Earth. Thank you, Heikki ----- Alkuperäinen viesti ----- Lähettäjä: "Sven Woltmann" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Lähetetty: Tuesday, February 10, 2004 3:53 PM Aihe: RE: Foreign Key Reference to a VARCHAR 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]
