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

[Dave Hall]

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
-- 
Dave Hall (aka skwashd)
API Coordinator
phpGroupWare
+-------------------------------------+-------------------------------+
| e address@hidden          | w phpgroupware.org            |
| j address@hidden                 | aim skwashd                   |
| icq 278064022                       | msn address@hidden       |
| sip address@hidden       | y! skwashd                    |
+-------------------------------------+-------------------------------+