Thread: [Postfixadmin-devel] Question re: changing db engine and character set...
Brought to you by:
christian_boltz,
gingerdog
From: Tanstaafl <tan...@li...> - 2012-01-13 12:36:58
|
Hello all, I have a question regarding converting the database engine used to InnoDB (currently MyISAM) and the character set to utk8 (currently latin1)... 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? Also - why does the engine keep getting reset to MyISAM? I have changed the tables to InnoDB before, but updates always change it back... 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)? Lastly, how to make sure that from now on, all databases default to InnoDB and utf8? Thanks... |
From: David G. <da...@co...> - 2012-01-13 12:47:33
|
On 13 Jan 2012, at 12:37, Tanstaafl wrote: > Hello all, > > I have a question regarding converting the database engine used to > InnoDB (currently MyISAM) and the character set to utk8 (currently > latin1)... > > 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. You probably can't just change the encoding type though (i.e. edit the dump file and push it back in). > > Also - why does the engine keep getting reset to MyISAM? I have changed > the tables to InnoDB before, but updates always change it back… > > 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)? > 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. > Lastly, how to make sure that from now on, all databases default to > InnoDB and utf8? > Unknown. If you created your database from being empty then I could see it possibly being set to MyISAM. The normal upgrade.php routine should not change an existing table format - unless (for instance) we added functionality in which required a specific table format. David. |
From: Tanstaafl <tan...@li...> - 2012-01-13 13:05:51
|
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? >> 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)? > 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... Last... what is *recommended* for postfixadmin? Thanks as always! Charles |
From: Tanstaafl <tan...@li...> - 2012-01-13 13:11:27
|
On 2012-01-13 8:05 AM, Tanstaafl <tan...@li...> wrote: > 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... I found something online that said to run the following command, but I don't know how to interpret the result: # file pfa_234.sql pfa_234.sql: ASCII English text, with very long lines ? |
From: OVC_SonamPenjor <s_p...@ru...> - 2012-04-04 05:49:49
|
Hi, Any comments and suggestions on the codes that I have attached in a month of March. Thanks Sonam Penjor |
From: Sonam P. <s_p...@ru...> - 2012-07-26 10:16:48
|
Hi All, I have submitted my works for uploading the users from csv file last month. However, I haven't received any comments yet. I would appreciate for the comments to improve my work further. Thanks Sonam Penjor |
From: Christian B. <pos...@cb...> - 2012-07-31 22:11:04
|
Hello, Am Donnerstag, 26. Juli 2012 schrieb Sonam Penjor: > I have submitted my works for uploading the users from csv file last > month. However, I haven't received any comments yet. > I would appreciate for the comments to improve my work further. I've seen your mails. First and most important: Thank you! Unfortunately I was constantly busy over the last weeks (and still am) which means I didn't have time to check and test your code :-( (If someone else wants to check the bulk mailbox creation code - just do it[tm] and report back. PostfixAdmin isn't a one-man-show ;-) Regards, Christian Boltz -- > Wenn allerdings etwas nicht funktioniert, bin ich über jede > Fehlermeldung froh, auch wenn sie tausendfach im Log steht ;-) Kann ich nicht beurteilen. Bei mir funktioniert immer alles. Pfeif...flöt... :-) [> Christian Boltz und Ratti in fontlinge-devel] |
From: David G. <da...@co...> - 2012-01-13 13:14:28
|
On 13 Jan 2012, at 13:05, Tanstaafl wrote: > 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… > InnoDB is good for tables which have frequent updates and reads. When you update a MyISAM table the entire table is locked - so on a high traffic site reads are delayed. I'd be surprised if there were many Postfixadmin installations which have enough write and read traffic for it to matter which they use. InnoDB is the way forward - MySQL 6(?) will (perhaps does already?) effectively default to using it. I think it recovers better from crashes and supports transactions etc. Postfixadmin doesn't use transactions. > So... maybe I should stick with MyISAM? > >>> 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)? > >> 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… > Not that I know of. > Last... what is *recommended* for postfixadmin? > If it's working, don't "fix" it….. David. |
From: Tanstaafl <tan...@li...> - 2012-01-13 13:26:33
|
On 2012-01-13 8:14 AM, David Goodwin <da...@co...> wrote: > On 13 Jan 2012, at 13:05, Tanstaafl wrote: >> Hmmm... I didn't know of that limitation with InnoDB, I just read >> somewhere that InooDB was preferred… > InnoDB is good for tables which have frequent updates and reads. When > you update a MyISAM table the entire table is locked - so on a > high traffic site reads are delayed. > > I'd be surprised if there were many Postfixadmin installations which > have enough write and read traffic for it to matter which they use. > > InnoDB is the way forward - MySQL 6(?) will (perhaps does already?) > effectively default to using it. I think it recovers better from crashes > and supports transactions etc. Postfixadmin doesn't use transactions. Ok, sounds like switching to InnoDB would be the way to go then. Out of curiosity, is that what you use? Or do you use postgresql? >> Last... what is *recommended* for postfixadmin? > If it's working, don't "fix" it….. Normally I'd agree, but my reason for considering doing this switch is to 'future-proof' my postfixadmin system, and everything I've read says that utf8 is the future... gentoo has switched to utf8 for their entire systems. On the wiki page I linked in my last email, it says: "Be careful when switching to UTF-8. Once you have converted your data, any program/webapp that uses the database will have to check that the data they are sending to the database is valid UTF-8. If it isn't then MySQL will silently truncate the data after the invalid part, which can cause all sorts of problems. If your program/webapp doesn't specifically say that it supports unicode then you may want to stick with latin1 instead." So, does postfixadmin validate input for utf8 if it is used? Or is there any other reason it might be a problem? Thanks again David... |
From: David G. <da...@co...> - 2012-01-13 13:53:29
|
On 13 Jan 2012, at 13:26, Tanstaafl wrote: > On 2012-01-13 8:14 AM, David Goodwin <da...@co...> wrote: >> On 13 Jan 2012, at 13:05, Tanstaafl wrote: >>> Hmmm... I didn't know of that limitation with InnoDB, I just read >>> somewhere that InooDB was preferred… > >> InnoDB is good for tables which have frequent updates and reads. When >> you update a MyISAM table the entire table is locked - so on a >> high traffic site reads are delayed. >> >> I'd be surprised if there were many Postfixadmin installations which >> have enough write and read traffic for it to matter which they use. >> >> InnoDB is the way forward - MySQL 6(?) will (perhaps does already?) >> effectively default to using it. I think it recovers better from crashes >> and supports transactions etc. Postfixadmin doesn't use transactions. > > Ok, sounds like switching to InnoDB would be the way to go then. > > Out of curiosity, is that what you use? Or do you use postgresql? For Postfix I use PostgreSQL … For nearly all our development work we use MySQL, and within that generally always InnoDB. > >>> Last... what is *recommended* for postfixadmin? > >> If it's working, don't "fix" it….. > > Normally I'd agree, but my reason for considering doing this switch is > to 'future-proof' my postfixadmin system, and everything I've read says > that utf8 is the future... gentoo has switched to utf8 for their entire > systems. > Ubuntu switched to UTF8 by default ages ago (when it was released I think). > On the wiki page I linked in my last email, it says: > > "Be careful when switching to UTF-8. Once you have converted your data, > any program/webapp that uses the database will have to check that the > data they are sending to the database is valid UTF-8. If it isn't then > MySQL will silently truncate the data after the invalid part, which can > cause all sorts of problems. If your program/webapp doesn't specifically > say that it supports unicode then you may want to stick with latin1 > instead." > > So, does postfixadmin validate input for utf8 if it is used? Or is there > any other reason it might be a problem? I have memory of there being some UTF8 patches submitted/merged into PFA over a year ago. Some parts can't support UTF8 (e.g. domain names) while others can (e.g. vacation message, someone's name etc). I think these have all been dealt with, and I thought a new installation of PFA would default to InnoDB and UTF-8 charset encoded tables. I'm too lazy to go into the code and check though. David. |
From: Tanstaafl <tan...@li...> - 2012-01-13 16:57:33
|
On 2012-01-13 8:53 AM, David Goodwin <da...@co...> wrote: >> Out of curiosity, is that what you use? Or do you use postgresql? > For Postfix I use PostgreSQL … > > For nearly all our development work we use MySQL, and within that generally always InnoDB. Ok, well, since you use both, would you mind if I picked your brain on a few things? My apologies for hoe long this is, and if you don't have time to answer, that is ok... Ever since Oracle acquired mysql, I've been toying with the idea of switching to postgresql. I installed it and messed around a bit, but am thoroughly confused on some fairly simple things, which gave me reason to pause - if I'm so confused about the simple things, maybe I shouldn't switch... But, I'm thinking/hoping it is more my thought processes than it is a matter of postgresql being *that* much more complicated. I am about as far from an 'expert' at anything sql as anyone could be, but I have found managing my mysql to be very painless and easy, and I don't really have the time to become a postgres guru just to run my postfixadmin database. So, if I can get some help with doing the following things the postgres way (these are about the only things I ever do in mysql, up to now that is), then maybe I can give switching another look... Following is my documented procedure for installing and/or updating my postfixadmin in mySQL - what I need help with is how to do the equivalent in postgresql. I appreciate any tips you can provide... ************************************************* Updating PostfixAdmin after emerging the new version and installing it with webapp-config: 1. dump the current/production DB to an sql file: From the root commandline (NOT the mysql command line): # mysqldump -u root -p -v pfa_currentver > /home/user/pfa_currentver_preupdate.sql pgsql equivalent: ? 2. create the admin user (if already exists, skip this step): mysql> CREATE USER 'pfa_admin'@'localhost' IDENTIFIED BY 'password'; pgsql equivalent (maybe?): pgsql> CREATE USER pfa_admin WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT PASSWORD 'password'; 3. create the new DB: # mysql -u root -p password mysql> CREATE DATABASE pfa_newver; pgsql equivalent (maybe?): pgsql> CREATE DATABASE pfa_newver OWNER pfa_admin; ? 4. grant required access to the pfa_newver DB to the pfa_admin user: mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `pfa_newver`.* TO 'pfa_admin'@'localhost'; pgsql equivalent? 5. create the pfa_vacation user (if already exists, skip the CREATE command) and grant it minimal rights necessary to interact with the vacation databases: mysql> CREATE USER 'pfa_vacation'@'localhost' IDENTIFIED BY 'password'; pgsql equivalent (maybe?): pgsql> CREATE USER pfa_vacation WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT PASSWORD 'password'; mysql> GRANT SELECT, UPDATE ON `pfa_newver`.`vacation` TO 'pfa_vacation'@'localhost'; pgsql equivalent? mysql> GRANT SELECT ON `pfa_newver`.`alias` TO 'pfa_vacation'@'localhost'; pgsql equivalent? mysql> GRANT SELECT ON `pfa_newver`.`alias_domain` TO 'pfa_vacation'@'localhost' pgsql equivalent? mysql> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON `pfa_newver`.`vacation_notification` TO 'pfa_vacation'@'localhost'; pgsql equivalent? 6. create a read-only user (if already exists, skip the CREATE command) and grant only select privileges for querying the database: mysql> CREATE USER 'pfa'@'localhost' IDENTIFIED BY 'password'; pgsql equivalent (maybe?): CREATE USER pfa WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT PASSWORD 'password'; mysql> GRANT SELECT, EXECUTE ON `pfa_newver`.* TO 'pfa'@'localhost'; pgsql equivalent (maybe?): 7. import the dump from the production DB into the new DB: # mysql -u root -p password pfa_newver < /home/user/pfa_currentver_preupdate.sql pgsql equivalent? 8. stop courier-imap then postfix 9. modify the following config files to reference the new db_name: /etc/postfix/maps/mysql/all /etc/courier/authlib/authmysqlrc 10. restart courier-imap and then postfix done... |
From: Tanstaafl <tan...@li...> - 2012-01-13 17:13:54
|
On 2012-01-13 11:57 AM, Tanstaafl <tan...@li...> wrote: > On 2012-01-13 8:53 AM, David Goodwin<da...@co...> wrote: >>> Out of curiosity, is that what you use? Or do you use postgresql? >> For Postfix I use PostgreSQL … >> >> For nearly all our development work we use MySQL, and within that >> generally always InnoDB. > Ok, well, since you use both, would you mind if I picked your brain > on a few things? My apologies for hoe long this is, and if you don't > have time to answer, that is ok... I should have added... I am in no hurry at all to get this switched over, so if you are willing to take a few minutes here and there over the next few weeks or months to answer these question, that would be absolutely fine with me... Thanks again for all your hard work on the premiere tool for managing a linux based mail system! Charles |
From: David G. <da...@co...> - 2012-01-13 20:42:25
|
> But, I'm thinking/hoping it is more my thought processes than it is a > matter of postgresql being *that* much more complicated. > > I am about as far from an 'expert' at anything sql as anyone could be, > but I have found managing my mysql to be very painless and easy, and I > don't really have the time to become a postgres guru just to run my > postfixadmin database. > > So, if I can get some help with doing the following things the postgres > way (these are about the only things I ever do in mysql, up to now that > is), then maybe I can give switching another look... > > Following is my documented procedure for installing and/or updating my > postfixadmin in mySQL - what I need help with is how to do the > equivalent in postgresql. I appreciate any tips you can provide... > > ************************************************* > > Updating PostfixAdmin after emerging the new version and installing it > with webapp-config: > > 1. dump the current/production DB to an sql file: > > From the root commandline (NOT the mysql command line): > # mysqldump -u root -p -v pfa_currentver > > /home/user/pfa_currentver_preupdate.sql > > pgsql equivalent: > As the system user postgres : pg_dump -D postfix > postgres.sql (from memory). > > 2. create the admin user (if already exists, skip this step): > > mysql> CREATE USER 'pfa_admin'@'localhost' IDENTIFIED BY 'password'; > > pgsql equivalent (maybe?): > pgsql> CREATE USER pfa_admin WITH NOSUPERUSER NOCREATEDB NOCREATEROLE > INHERIT PASSWORD 'password'; > Postgres has a different permissions structure. Create user postgres with password 'fish' (I think) > 3. create the new DB: > > # mysql -u root -p password > > mysql> CREATE DATABASE pfa_newver; > > pgsql equivalent (maybe?): > pgsql> CREATE DATABASE pfa_newver OWNER pfa_admin; ? > Yep. > 4. grant required access to the pfa_newver DB to the pfa_admin user: > > mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, > INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE > ROUTINE, ALTER ROUTINE ON `pfa_newver`.* TO 'pfa_admin'@'localhost'; > > pgsql equivalent? > No need. Ownership fixes that. > > > > 6. create a read-only user (if already exists, skip the CREATE command) > and grant only select privileges for querying the database: > > mysql> CREATE USER 'pfa'@'localhost' IDENTIFIED BY 'password'; > > pgsql equivalent (maybe?): > CREATE USER pfa WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT > PASSWORD 'password'; > Not sure. Will have to research / read docs. > > 7. import the dump from the production DB into the new DB: > > # mysql -u root -p password pfa_newver < > /home/user/pfa_currentver_preupdate.sql > > pgsql equivalent? > psql postfix < postfix.dump You might need to specify user etc when connecting. > Somewhere on my website (codepoets.co.UK) there should be a tutorial covering Installation with pgsql. David. |
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] |
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.) > |
From: David G. <da...@co...> - 2012-01-16 15:14:13
|
>> >> 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. Indeed. But that's fine. As effectively you are passing it a load of bytes in, and it's just returning them as is. >>>> 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. Yes - but when querying the database for that data it'll probably end up coming out right - as it'll be returned to you the same as what you entered it as (99% of the time) - as long as no one has done e.g. addslashes on the data and the unicode character doesn't contain the Byte 0x5a or whatever it is. ('). >> 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"). Wouldn't this still require a dump of the database and reinsertion in order to get the stuff encoded correctly)? I don't think MySQL can tell what is, and isn't a UTF8 character (can it)? I think if you just change the table charset it'll still contain the invalid encoding as is. I assume that changing the charset would just make a difference for future queries (e.g. it'll realise that £ or ö is actually one character and not 2 if you're doing a strlen operation within MySQL). >> >>> Last... what is *recommended* for postfixadmin? >> >> MyISAM for most tables, as created by setup.php/upgrade.php. >> InnoDB is better :-) <round name="one">fight</round> David. |
From: Christian B. <pos...@cb...> - 2012-01-16 17:10:07
|
Hello, Am Montag, 16. Januar 2012 schrieb David Goodwin: > >> 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. > > Indeed. But that's fine. As effectively you are passing it a load of > bytes in, and it's just returning them as is. No, it's returning them in the charset of your connection - which you can influence with SET NAMES ;-) (And you really want to read http://mysqldump.azundris.com/archives/60-Handling-character-sets.html which I already mentioned yesterday.) > >> 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"). > > Wouldn't this still require a dump of the database and reinsertion in > order to get the stuff encoded correctly)? > > I don't think MySQL can tell what is, and isn't a UTF8 character (can > it)? > > I think if you just change the table charset it'll still contain the > invalid encoding as is. MySQL _does_ a charset conversion when you ALTER a column from one charset from another (if the latin1 column contained a latin1-"ö", it will contain a utf8-"ö" after changing the column to utf8). This of course only works if the data in the original encoding is correct - you can't expect valid output data if you provide broken input... ;-) (Actually, it's a bit tricky to _avoid_ the automatic charset conversion, for example if you accidently stored utf8-encoded stuff in a latin1 column. IIRC the only way is to change the column to a "binary" varchar and then to utf8. Or of course the hard way of replacing the correct[tm] file in /var/lib/mysql/ - but you shouldn't do this at home ;-)) > I assume that changing the charset would just make a difference for > future queries (e.g. it'll realise that £ or ö is actually one > character and not 2 if you're doing a strlen operation within MySQL). The client will not even notice a difference if the column charset is changed. (Except if it tries to store a character that is not available in latin1, of course.) > >>> Last... what is *recommended* for postfixadmin? > >> > >> MyISAM for most tables, as created by setup.php/upgrade.php. > > InnoDB is better :-) > > <round name="one">fight</round> <round name="two" type="shoutmatch">Even if I assume your statement is true: we don't use the features that makes it better, so there is no difference for us ;-) And MyISAM supports fulltext search, which InnoDB doesn't...</round> <additional_punch>If you implement transaction support everywhere (with the things from my last mail in mind), I'll happily switch all tables to InnoDB ;-) </additional_punch> Regards, Christian Boltz -- Früher mußte man den Müll heimlich im Wald verbuddeln; Heute gibt es EBAY :-) [Axel Lindlau in suse-linux] |
From: Sonam P. <s_p...@ru...> - 2012-01-22 14:11:43
|
Hello, I have been trying to upload my bulk mail source code for the review for last one week but without any success. I would appreciate if you can provide me some guidelines. Thank you Sonam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. |
From: Tanstaafl <tan...@li...> - 2012-01-23 13:16:20
|
On 2012-01-22 9:11 AM, Sonam Penjor <s_p...@ru...> wrote: > I have been trying to upload my bulk mail source code for the review for > last one week but without any success. > > I would appreciate if you can provide me some guidelines. Please do not hijack threads... Post a *new* message (do NOT reply to an existing one and just change the subject), and use a *meaningful* subject... |
From: Sonam P. <s_p...@ru...> - 2012-01-25 03:53:59
|
> Please do not hijack threads... > > Post a *new* message (do NOT reply to an existing one and just change > the subject), and use a *meaningful* subject... My sincere apology for creating confusion by posting my message under different threads. I will take extra care in future to avoid such misunderstanding..... > ------------------------------------------------------------------------------ > Try before you buy = See our experts in action! > The most comprehensive online learning library for Microsoft developers > is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, > Metro Style Apps, more. Free future releases when you subscribe now! > http://p.sf.net/sfu/learndevnow-dev2 > _______________________________________________ > Postfixadmin-devel mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postfixadmin-devel > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. |
From: Tanstaafl <tan...@li...> - 2012-01-25 12:39:19
|
On 2012-01-24 10:53 PM, Sonam Penjor <s_p...@ru...> wrote: > My sincere apology for creating confusion by posting my message under > different threads. > > I will take extra care in future to avoid such misunderstanding..... No problem, and sorry if my response was a bit harsh... |
From: Sonam P. <s_p...@ru...> - 2012-01-17 04:14:22
|
Hello, This is my last and final part of my bulk email uploading. I was just thinking whether it is really important to change character set encoding as I am using data to be uploaded from CSV file. If so then, should I update column wise or table wise. I was also wondering how to upload the my source code for the review. I would appreciate for your comments, advice and recommendations on my last and final part of the activities. Thank You Sonam Penjor -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. |
From: Christian B. <pos...@cb...> - 2012-01-31 23:22:21
|
Hello, sorry for not responding earlier - I was on vacation and then had some "fun" with crashing harddisks on a server... Am Dienstag, 17. Januar 2012 schrieb Sonam Penjor: > This is my last and final part of my bulk email uploading. I was just > thinking whether it is really important to change character set > encoding as I am using data to be uploaded from CSV file. If so then, > should I update column wise or table wise. I'd ignore the charset for now and wait what happens ;-) If you really want to implement charset handling, then you'll probably need a selectbox on the upload page: the uploaded file is encoded as [ choose your encoding v ] But as I said: I'd try without it first ;-) > I was also wondering how to upload the my source code for the review. That depends: - if you modified existing files, please provide your changes as a patch (easiest way: "svn diff > bulk-creation.diff"). - if you created new files, well, then we obviously need the complete files ;-) Depending on the number of changed and added files, you can put everything (patch + new files) in a tar.gz or zip - or not. For the upload itsself, you can - append everything to a mail to this mailinglist - or - - choose the more formal way by creating a new item in the patches tracker on sf.net/projects/postfixadmin and upload your file(s) there. For the records: You can only upload files to tracker items you created yourself, which is a somewhat annoying limitation on sourceforge. Regards, Christian Boltz -- Such mal im Archiv dieser Liste nach 'reiserfs', oder genauer, nach 'rasierfs' und 'reisswolffs'. Reiserfs reagiert auf Fehler (diverser Art) wie ne Diva... [David Haller in suse-linux] |
From: Sonam P. <s_p...@ru...> - 2012-02-08 12:10:37
Attachments:
postfixadmin_bulkemail.zip
|
Hi, I am attaching the complete files that I have been working on the bulk email creation for the review. Should you have any inquiries on the attached files, I would be happy to explain. I will appreciate for the comments, advices and recommendations. Thank You Sonam Penjor -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. |
From: Christian B. <pos...@cb...> - 2012-02-08 22:15:28
|
Hello, Am Mittwoch, 8. Februar 2012 schrieb Sonam Penjor: > I am attaching the complete files that I have been working on the bulk > email creation for the review. Thanks a lot! > Should you have any inquiries on the > attached files, I would be happy to explain. > > I will appreciate for the comments, advices and recommendations. You will regret this offer ;-)) Some questions first: - Do you have some example CSV files I could use for testing? (That would be easier than creating them myself ;-) Bonus points if you also have some CSV files with invalid data (duplicate usernames, invalid mail addresses, whatever) - what's the biggest (in number of lines) CSV file you tested? - some documentation/description how the CSV file should look like (allowed columns etc.) would be useful. I'd even place it next to the form in the template file. - did I get it right that your only modification to en.lang is the addition of 14 texts after this comment: /* Newly added for the uploading Bulk email ID */ (just to avoid I overlooked something, it seems en.lang got some changes since you copied it) I'm attaching an updated en.lang - if you have to do more changes, add more texts etc. please use the attached en.lang as base. Now to some notes on your code: - please don't play around with error_reporting - there's a good reason I have a very strict setting for it on my development machine ;-) I know notices can be annoying sometimes, but often they help to find bugs like typos in a variable name which would be hard to find otherwise - your class needs to be named model/UploadCsvFile.php - otherwise the autoloader won't find it - better error handling would be nice. For example, if I upload an invalid csv file, I get (only) an error message "Invalid Delimiter". It would be better to display the error message using flash_error() and to display the form again - the "Invalid Delimiter" error message seems to be misleading - I uploaded a file with the correct delimiter but wrong column headers (first line: "a,b,c,d") and it complained about "Invalid Delimiter". From reading the code, I'd say you should print out the error message you get from scanCsvfile() instead of just checking for failure. - I have attached a patch for create-bulkmailbox.php and model/UploadCsvFile.php with some small changes and inline notes - that's easier than including them in the mail. I also attached the modified files, but reading the patches is probably easier. - in the class, please explicitely mark the functions as private or public So much for the first review ;-) The above is what I noticed when reading the code. I didn't test much (except with an invalid CSV file ;-) yet, but the above and the inline comments will already give you several ideas what you can improve ;-) And finally: I hope you aren't shocked too much about all the things I found in your code ;-)) Regards, Christian Boltz -- As usual, I'm voicing my opinion on this topic, not announcing "truth" ;) [Cristian Rodríguez in opensuse-factory] |