Re: [Dbmail-dev] SQL errors in dbmail-mailbox.c (current git)
On Dienstag 09 Juni 2009 Aaron Stone wrote: One concern I remember was about the running configuration being different from the values in the database; basically, did you manually alter the database config values without bouncing the daemon? A dbmail-config utility that managed this table for you would be a great thing, IMHO, to centralize sanity checking and bouncing of daemons, etc. It'd be nice if you could still override those entries via the config file, e.g. when you want to do a quick test. A warning could be spilled that the db-config is overridden by the file-config variable. But maybe that creates confusion? A config util would certainly be helpful, especially as users could create a config dump to send with a request to the list. There should be a special -s (safe mode) dump that excludes hostnames and passwords so everybody can directly dump that to the list. mfg zmi -- // Michael Monnerie, Ing.BSc- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: curl -s http://zmi.at/zmi.asc | gpg --import // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4 signature.asc Description: This is a digitally signed message part. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] SQL errors in dbmail-mailbox.c (current git)
On Montag 08 Juni 2009 Jonathan Feally wrote: I'm not sure if a per user script to move messages is the best approach. Probably just need to add it to dbmail-util to move them from the old structure to the new one. We don't even need to change the phymessage_id, just insert the mimeparts, then the partslist rows. Once those are there, the physmessage_id rows can be deleted from the messageblks. But how do you delete messageblks without their entire message? There are lots of constraints all over the place. You'd need to redefine those. OK, as there will be no more old inserts, it shouldn't really hurt to loose those. If per-user is not good - what would be better? mfg zmi -- // Michael Monnerie, Ing.BSc- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: curl -s http://zmi.at/zmi.asc | gpg --import // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4 signature.asc Description: This is a digitally signed message part. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] SQL errors in dbmail-mailbox.c (current git)
Michael Monnerie wrote: But how do you delete messageblks without their entire message? There are lots of constraints all over the place. You'd need to redefine those. OK, as there will be no more old inserts, it shouldn't really hurt to loose those. If per-user is not good - what would be better? mfg zmi I just did a test delete of an old message mysql select * from dbmail_physmessage where id=1000; +--+-+-+-+ | id | messagesize | rfcsize | internal_date | +--+-+-+-+ | 1000 |2164 |2226 | 2003-07-12 09:13:57 | +--+-+-+-+ 1 row in set (0.02 sec) mysql delete from dbmail_messageblks where physmessage_id=1000; Query OK, 2 rows affected (0.00 sec) mysql select * from dbmail_physmessage where id=1000; +--+-+-+-+ | id | messagesize | rfcsize | internal_date | +--+-+-+-+ | 1000 |2164 |2226 | 2003-07-12 09:13:57 | +--+-+-+-+ 1 row in set (0.00 sec) mysql select * from dbmail_partlists where physmessage_id=1000; Empty set (0.00 sec) The constraints are set such that the dbmail_phymessage row would need to be deleted to cascade a delete in the dbmail_messageblks. So simply inserting the unique parts in the mimeparts, then insert the part_id's into partlists, update header cache, then delete the messageblks. All sounds simple enough. Essentially a retrieve piped to insert loop. I think that it should just do all messages. Perhaps an option to do x number of messages per run. Free disk space could be a concern on some database servers. In the case of a MySQL INNODB setup, inserting all the messages would take up more space in the mimeparts table while the messageblks would no be shrinking on the disk until it was optimized or dropped. Database or server load could also cause issues with just leaving the convert process running to chomp on 10,000+ messages. -Jon -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] SQL errors in dbmail-mailbox.c (current git)
Aaron Stone wrote: We dropped the dbmail_config table very early on, between DBMail 1.1 and 1.2. There were good reasons to have a configuration file, and once things started going in there, having two places to configure the daemons seemed like a bad idea. One concern I remember was about the running configuration being different from the values in the database; basically, did you manually alter the database config values without bouncing the daemon? Hate to break up the party, but: I'm deeply suspicious of the config table idea. It sucked back then, and I havent heard _any_ compelling reasons that would support the added complexity. Other than the lack of any perceivable added benefit (for me at least), my main objections are with regard to change management and security. For example, I like being able to stick /etc/dbmail/ into a git repository. And I *really* don't want my ldap bind_dn parameters in a database. This is a no-go area for 2.4.0 in my book. Once we start working on the hydra idea (supporting separate/different database backend servers per user) post-2.4 it might make some sense for very large deployments to alleviate management complexity. But not for the 2.4 release. -- Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlandshttp://www.nfg.nl ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] SQL errors in dbmail-mailbox.c (current git)
Jonathan Feally wrote: The constraints are set such that the dbmail_phymessage row would need to be deleted to cascade a delete in the dbmail_messageblks. So simply inserting the unique parts in the mimeparts, then insert the part_id's into partlists, update header cache, then delete the messageblks. All sounds simple enough. Essentially a retrieve piped to insert loop. Jon, there's no need to update the header cache. -- Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlandshttp://www.nfg.nl ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] SQL errors in dbmail-mailbox.c (current git)
Michael Monnerie wrote: On Montag 08 Juni 2009 Jonathan Feally wrote: I'm not sure if a per user script to move messages is the best approach. Probably just need to add it to dbmail-util to move them from the old structure to the new one. We don't even need to change the phymessage_id, just insert the mimeparts, then the partslist rows. Once those are there, the physmessage_id rows can be deleted from the messageblks. But how do you delete messageblks without their entire message? There are lots of constraints all over the place. You'd need to redefine those. OK, as there will be no more old inserts, it shouldn't really hurt to loose those. It's perfectly safe to delete from the messageblks table if the data has been inserted into mimeparts. No schema change is needed for storage migration to work. Like Jon said: it's a matter of pulling a message from the store, reinsert it into mimeparts while avoiding the header caching calls, and finally nuke the messageblks rows. If per-user is not good - what would be better? Such a migration run will be *very* long running (think several days). It should not block any services, or hurt overall performance too much. So personally I'd go for a low prio maintenance thread that kicks in on low system loads. -- Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlandshttp://www.nfg.nl ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] SQL errors in dbmail-mailbox.c (current git)
Paul J Stevens wrote: Jon, there's no need to update the header cache. Ok, I wasn't sure. I didn't think it needed to be done. Throttling the movement would be difficult. While the processing could be done on another box separate of the production daemons, the database load and disk bottleneck is the real key. On MySQL, an INSERT LOW_PRIORITY could be used. This would effectively let the database do the throttling by making the migration process wait for the insert to be completed when the table goes idle. And when dropping the rows from the messageblks, a DELETE LOW_PRIORITY can also be used to keep from deleting the old rows until the table is idle. Does Postgres have a similar option for insert and/or delete? I doubt that we need to worry about SQLite for an option as it doesn't have a daemon running to order query execution. -Jon -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
Re: [Dbmail-dev] dbmail_config table and hydra
Paul J Stevens wrote: Other than the lack of any perceivable added benefit (for me at least), my main objections are with regard to change management and security. For example, I like being able to stick /etc/dbmail/ into a git repository. And I *really* don't want my ldap bind_dn parameters in a database. For ldap, I think that the ldap bind_dn and bind_pw, and possibly ldap_host could be items in the flat file only. I don't think that the rest of the ldap information would be a huge security risk. For hydra, you can't just do it per message as it would break sorting with messages spread across multiple databases. The only real solution I can see is to have a mailbox assigned to a backend database. This will keep all of the messages for that mailbox on the same server. Header caching, and sorting would remain on that server. I'm not sure about UID overlap between mailboxes though. If I had a UID 1 in multiple mailboxes, would clients go nuts? I think they would be ok, but I'm not sure if that breaks a RFC. Front end database tables: dbmail_acl dbmail_aliases dbmail_config (storage of most of the configurable items and information about the backend database server locations, also defines rules for which backend to use) dbmail_filters dbmail_keywords dbmail_mailboxes (with new column of which backend storage database contains the message - probably just and int that has a server defined in the dbmail_config table) dbmail_pbsp dbmail_replycache dbmail_sievescripts dbmail_subscription dbmail_usermap dbmail_users Back end single-instance storage databases: (sort views) dbmail_ccfield dbmail_datefield dbmail_fromfield dbmail_subjectfield dbmail_tofield (tables) dbmail_envelope dbmail_header dbmail_headername dbmail_headervalue dbmail_messages dbmail_mimeparts dbmail_partlists dbmail_physmessage dbmail_referencesfield -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ___ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
