Re: [Phpgroupware-developers] HEAD: mysql / max key length for utf8

[Sigurd Nes]

I will revert the changes - and alter the column length of app_name to 25

Regards

Sigurd

Dave Hall wrote:
> On Sat, 2006-04-22 at 20:52 +0200, Sigurd Nes wrote:
>> I have commited a fix to cvs (HEAD)
>>
>> Modified files:
>>      inc            : class.schema_proc.inc.php
>>                       class.schema_proc_mysql.inc.php
>>      setup          : tables_current.inc.php
>>
>> Log message:
>>      handling of collate for creating columns in tables (custom character 
>> set)
>>
>> If you think this is insane - let me know - and I will revert it.
>>
> 
> I think this is a little crazy.  I didn't eat before replying to your
> message the other day.  
> 
> as mysql thinks unicode string could be 3 bytes per character the
> maximum key length is 1000/3 or 333 characters, currently we are sitting
> on 360.
> 
> There is a far simpler solution :)
> 
> The app_name now is 100 characters in the lang table.  In
> phpgw_applications the appname is limited to 25 characters.  We just
> need to shorten the length of the app_name column for phpgw_lang.
> 
> I really don't like the latin1 hack or having wasteful column lengths :)
> 
> Sorry if I have missed something.  If not let me know if you need any
> help making the changes.
> 
> Cheers
> 
> Dave
> 
> 
>> Regards
>>
>> Sigurd
>>
>>
>> Sigurd Nes wrote:
>>> Make sense (and it works fine)
>>>
>>> the sql statement for creating the table would be (given the database is
>>> set to utf as default):
>>>
>>> CREATE TABLE `phpgw_lang` (
>>> `message_id` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE
>>> latin1_general_ci NOT NULL ,
>>> `app_name` VARCHAR( 100 ) CHARACTER SET latin1 COLLATE latin1_general_ci
>>> DEFAULT 'common' NOT NULL ,
>>> `lang` VARCHAR( 5 ) CHARACTER SET latin1 COLLATE latin1_general_ci NOT
>>> NULL ,
>>> `content` TEXT
>>> ) TYPE = MYISAM ;
>>>
>>> We might want to introduce a COLLATE-attribute in the field-array in the
>>> table-definition in tables_current.inc.php to be handled by
>>> class.schema_proc_[db_type].inc.php
>>>
>>> Regards
>>>
>>> Sigurd
>>>
>>> Alan Langford wrote:
>>>> Since the message_id and app_name are part of the language independence,
>>>> why not just restrict them to the latin1 character set so they continue
>>>> to use one byte per character? A Chinese (for example) message_id seems
>>>> a little too flexible to me... leave the UTF character sets for fields
>>>> likely to have human readable (rather than developer readable, grin) text.
>>>>
>>>> On 2006 04 22 11:01, Sigurd Nes wrote:
>>>>> That would restrict the installation to only one language.
>>>>> (this is only tested on windows/MySQL 5.0.18 - I think pre 4.1 - one
>>>>> cannot specify utf8 (utf8_swedish_ci) as collation)
>>>>>
>>>>> Regards
>>>>>
>>>>> Sigurd
>>>>>
>>>>> Dave Hall wrote:
>>>>>> We should change the pk to be message_id, app_name imo
>>>>>>
>>>>>> Cheers
>>>>>>
>>>>>> Dave
>>>>>>
>>>>>> On Fri, 2006-04-21 at 18:14 +0200, Sigurd Nes wrote:
>>>>>>> Problem:
>>>>>>> The PRIMARY KEY ( message_id, app_name, lang ) of table phpgw_lang
>>>>>>> is to long (max key length is 1000 bytes) when creating database as
>>>>>>> utf8 with Mysql.
>>>>>>>
>>>>>>> regards
>>>>>>>
>>>>>>> Sigurd
>>>>>>>
>>>>>>>
>>
>> _______________________________________________
>> Phpgroupware-developers mailing list
>> address@hidden
>> http://lists.gnu.org/mailman/listinfo/phpgroupware-developers