Kevin Cowley R&D Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk
> -----Original Message----- > From: gerald_clark [mailto:[EMAIL PROTECTED] > Sent: 29 September 2004 17:29 > To: Kevin Cowley > Cc: [EMAIL PROTECTED] > Subject: Re: Indexing problem with UTF8 in 4.1.4? > > > > Kevin Cowley wrote: > > >Running 4.1.4 with a database that has a default encoding of UTF8 > > > >If we execute the following we get an error. > >CREATE TABLE idxbe_resident ( > >urn INT UNSIGNED NOT NULL, > >keyAddress_Part1 CHAR(5) BINARY NOT NULL, > >dataPerson_Name CHAR(60), > >dataAddress_Part1 CHAR(140), > >dataAddress_Part2 CHAR(128), > >INDEX (keyAddress_Part1, > > dataPerson_Name, > > dataAddress_Part1, > > dataAddress_Part2, urn)); > > > >1071 (Specified key was too long; max key length is 1000 bytes) > >If we change the dataAddress_Part1 field to be 139 characters the error > goes > >away. > > > Don'y know. If I knew why I wouldn't be asking. Now by our reconing the key of the fields is 343 bytes, encoding in UTF8 makes that key 343 bytes Not 1000 since under utf8 each character is encode in 8 bits. If it is converting to utf16 internally then the key would be 328*2+5+4 which is not >1000 bytes. > > > >If we change it to be greater than 255 characters we get a different > error > > 1170 (BLOB/TEXT column 'dataAddress_Part1' used in key > >specification without a key length) > > > VARCHAR() maximum length is 255. Greater than 255 is promoted to text. > We're not using VARCHAR unless MySQL is automatically translating CHAR to VARCHAR. Even so, not making the assumption key_length=field length for a field NOT declared as TEXT is somewhat poor. So my request stands. Can I have an explanation or are these both bugs? ************************************************************************************** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000 Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ************************************************************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
