|
From: Tanstaafl <tan...@li...> - 2012-01-16 14:59:19
|
Thanks very much for taking the time to explain this in such detail Christian! I understand much better now why things are as they are... Now I just have to decide if I want to move out of my comfort zone in mysql and switch to postgreSQL... On 2012-01-14 12:08 PM, Christian Boltz <pos...@cb...> wrote: > Hello, > > I'm a bit undecided which mail in this thread I should answer - I'll > just take this one ;-) > > And somebody should write a summary in the postfixadmin wiki for this > (more or less) FAQ. I'll happily give you write permissions if you > a) login to sourceforge and then visit the postfixadmin wiki (this will > create a wiki user for you) > b) tell me your sourceforge username > > Am Freitag, 13. Januar 2012 schrieb Tanstaafl: >> On 2012-01-13 7:47 AM, David Goodwin<da...@co...> wrote: >>> On 13 Jan 2012, at 12:37, Tanstaafl wrote: >>>> Can I simply edit the dump file prior to restoring it? Ie, >>>> change the Engine in each of the table definitions from MyISAM >>>> to InnoDB?> >>> Yes. We don't use any full text indexes so there is no dependency >>> on MyISAM. >> Hmmm... I didn't know of that limitation with InnoDB, I just read >> somewhere that InooDB was preferred... >> >> So... maybe I should stick with MyISAM? > > At the moment, postfixadmin uses MyISAM for most tables (except vacation > and vacation_notification because they have a foreign key [1]). There is > no specific reason for that, except the historic one - "nobody changed > it". > > If you want, you can easily switch a table from MyISAM to InnoDB with > "ALTER TABLE foo ENGINE=InnoDB" - however I don't see a reason to do > that at the moment ;-) > > Using transactions [2] would be a reason to switch to InnoDB - however > we would first need someone who implements transaction usage. It's > probably not as easy as it looks - for example, creating a mailbox also > creates an alias for it, so we can't just do a "begin transaction" at > the beginning of every *Handler. And the more interesting question is > about rollback: creating a mailbox will send a welcome mail and (if > configured) create subfolders using IMAP. Now imagine creating the > subfolders fails - would you do a rollback or not? The welcome mail was > sent at that point, and there's no way to undo that... > >>>> As for changing the character set from latin1 to utf8... can I >>>> do >>>> it the same way as described above (simply edit the dump file >>>> before restoring)? > > utf8 is a different beast in MySQL ;-) > > utf8 can take up to 3 byte per character, which means a VARCHAR(255) to > store a mail address [3] would need 3*255 bytes on disk. But that's not > the main problem - even if you have lots of mail addresses, you will > most probably be more concerned about the disk space for your mail > storage than the disk space for the database ;-) > > The main problem is that an index in MySQL is limited to 1024 bytes (not > characters). In other words: It's impossible to have an index over two > VARCHAR(255) columns in utf8 - and that's something we need (see > vacation_notification table - fortunately this table stores only mail > addresses, so latin1 is enough). > > If you are interested in more technical details, > http://mysqldump.azundris.com/archives/60-Handling-character-sets.html > might be an interesting reading. Or even better: read everything on > http://mysqldump.azundris.com/ - the author worked as MySQL consultant > for some years. > > That all said: The postfixadmin database uses utf8 where it is needed > (name, description etc.) and uses latin1 in all fields that are not > allowed to contain non-ascii characters (mail address, domain [4] etc.) > > I see no reason to change all fields to utf8, and in some cases utf8 > would even make some serious trouble (the two-column index in > vacation_notification). > > And BTW: MySQL automatically converts the charset, so you can give it > utf8 as input and it will store it as latin1 if the field is defined as > such. > >>> No. Any utf-8 characters already in there will be stored in a >>> weird >>> encoding. If you have no existing UTF8 characters in your data >>> then it would work. >> >> Ok, thanks... is there a way to test for existing utf8 characters? I'm >> assuming I don't, since this database has apparently always been in >> latin1 format... > > Only ASCII characters (0..127) are the same in all charsets. If you are > using other characters (like the german umlauts äöü) which are in the > 128..255 area, you'll get invalid utf8. > > In other words: never change the charset in a mysqldump file. > If you really want to change a column to uft8, do it directly in MySQL > ("ALTER TABLE foo CHANGE bar bar VARCHAR(255) CHARACTER SET utf8 NOT > NULL"). > >> Last... what is *recommended* for postfixadmin? > > MyISAM for most tables, as created by setup.php/upgrade.php. > > > Regards, > > Christian Boltz > > [1] this foreign key made some database changes quite funny[tm], and > from that I learned that it might be better to avoid foreign keys > enforced by the database ;-) > > [2] we already use transactions in some rare cases for postgresql > > [3] making the fields shorter is not an option - we even got a bugreport > saying that RFC allows up to 320 (IIRC) characters in a mail address > and that we should make our fields longer... > While that is technically a valid request, it would make our tables > incompatible with older MySQL versions which are limited to 255 > characters in VARCHAR (not sure about postgresql length limits). > And seriously, do you really want to have a mail address that is > longer than 255 characters? If yes, I'd like to see your business > card ;-) > > [4] I'm aware of unicode domains - but those are encoded as > "xn--something.tld" by clients (browsers etc.) > |
