Re: [Postfixadmin-devel] SF.net SVN: postfixadmin:[506] trunk/upgrade.php

[Christian B. <pos...@cb...>]

Hello,

Am Montag, 26. Januar 2009 schrieb David Goodwin:
> > > @@ -156,8 +156,8 @@
> > > -                '{UTF-8}'           => '/*!40100 CHARACTER SET
> > > utf8   COLLATE utf8_unicode_ci */', -                '{LATIN1}'  
> > >        => '/*!40100 CHARACTER SET latin1 COLLATE
> > > latin1_swedish_ci */', +                '{UTF-8}'           =>
> > > '/*!40100 CHARACTER SET utf8 */', +                '{LATIN1}'    
> > >      => '/*!40100 CHARACTER SET latin1 */',
> >
> > Do you really think COLLATE can cause problems?
>
> Well... people who had their default collation/character set, set to
> uft8 had problems.

If it works this way - OK. But I still think the problem is that there 
are _different_ settings - maybe we forgot to set COLLATE somewhere 
before or something like that.

> > IMHO it causes more problems if we do not explicitely set it
> > because we then get a "random" (as in "MySQL default on this
> > system") collation. I doubt that this is positive ;-)
>
> Is it a problem if it's set to e.g utf-8? I get the impression that
> the distros are moving towards this as a default.

MySQL and utf-8 is an "interesting" story, and things aren't that easy 
as they might seem.

Best example: utf-8 varchar fields use 3 times the disk space a latin1 
field uses. Not nice, but not really problematic in our use case. (If 
you don't have disk space for the database, you don't even need to 
think about hosting mailboxes ;-)

The real problem is when you want to have an index over multiple varchar 
columns. A utf-8 varchar(255) is counted as 3*255 = 765, and the total 
index length is limited to 1024. This means you can't have an index 
across two utf-8 varchar(255) columns.
(We have an unique index across two varchar(255) columns in 
vacation_notification - for obvious reasons those columns are in 
latin1.)

This is the main reason why I try to stick to latin1 fields whereever 
possible.

> > > @@ -306,7 +306,7 @@
> > > -    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
> > > TYPE=InnoDB ... +    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
> > > TYPE=InnoDB ...
> >
> > Sidenote: CHARSET=utf8 (and COLLATE) should be replaced with
> > {UTF-8}
>
> I don't think I fully understand the difference between the two! It's
> so much easier with PostgreSQL!

CHARSET ist just another word for CHARACTER SET. No difference involved.

> > > @@ -772,7 +772,6 @@
> > > -        ALTER TABLE `$table_vacation_notification` DROP FOREIGN
> > > KEY `vacation_notification_pkey`
> >
> > This ALTER TABLE was there for a reason (probably because some
> > version of the vacation_notification table had such a primary key).
> >
> > I'd like to reintroduce it with the ignore_errors flag set, so that
> > the key is deleted if it exists. Any objections?
>
> Not really. I had one user who was seeing problems when it was trying
> to remove the key (and it didn't already exist). I presumed it was
> not necessary, and could be removed.

That is the fun part with the ignore_errors flag - if something fails 
for whatever reason, it just silently ignores it and continues with the 
next query ;-)

You could even do a query like "ASDFAWE ASDF QWE SDFQ WEDSS FA;" - as 
long as you pass the ignore_errors flag, nothing bad will happen (from 
the user's POV) ;-)

> I think I'd rather upgrade.php stay as it is - as I have some small
> amount of belief that it now works for everyone. At least no one else
> has reported bugs with it!

I hope this means there are no bugs left ;-)

I promise one thing: If we ever get any bugs regarding the 
vacation_notification constraint again, I will find a way to drop the 
constraint (even if it means to parse "show create table" output) - and 
never create a constraint again.


Regards,

Christian Boltz
-- 
         .:  Schneewittchen & die Pfälzer Waldconnection  :.
        Ein polit-kabarettistisches Märchenstück mit viel Musik
  gesungen & gespielt von Mitgliedern der Landjugend RheinhessenPfalz
18.1.2009 Berlin - 6.2.2009 Neustadt - Infos: www.LJ-RheinhessenPfalz.de