|
From: Christian B. <pos...@cb...> - 2012-01-14 17:08:20
|
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.) -- Ich verlas mich. Die Dokumentation ist devel und nicht unstable, daher kann wohl nur ein kyrillischer Zeichensatz oder gar ein inhaltlicher Fehler vorkommen. Obwohl... Man könnte sie unter Windows 95 lesen, damit sie abstürzt. Das wäre aber OT. [Ferdinand Ihringer in suse-linux] |
