Dovecot SQL query woes

    Hi there,


    I've just started comparing my dovecot setup against the iMSCP test box. Yes, my production is still on ispcp and highly customized, so I'm really a bit slow in migration. But anyhow...


    The SQL query is using prefetch, which is nice and what I want. Currently I still have the old way of domain, subdomain and aliasdomain queries. Checking through this, I've came across some problems...


    The current query:

    Code
    1. user_query = SELECT CONCAT('/var/mail/virtual/', SUBSTRING(`mail_addr`, LOCATE('@', `mail_addr`) +1 ), '/', \ SUBSTRING(`mail_addr`, 1, LOCATE('@', `mail_addr`) -1) ) AS 'home', '1000' AS 'uid', \ '8' AS 'gid', CONCAT('*:bytes=', `quota`, 'B') AS 'quota_rule' FROM `mail_users` \ WHERE `mail_addr` = '%u' AND `status` = 'ok'


    The minor problems:
    - Compared to the github master I'm missing po_active = 'yes'. But beyond the SQL template I've not found this statement anywhere. It's not in my database (migrated from ispcp) either!
    - When running imscp --reconfigure the user and password where overwritten, but it seems that the dovecot.data has not the migrated data from ispcp. There is dovecot_user which doesn't exist. I have ispcp_dovecot!


    The trouble:
    - the query fetches any email account! It fetches mail accounts and also forwards. So it could deliver to /var/mail/hostmaster! Ok, postfix would rewrite the forward before touching dovecot, but still seems strange.
    - There is no iterate query for mail expunge or quota checking using doveadm.


    I've added this:

    Code
    1. user_query = SELECT CONCAT('/var/mail/virtual/', SUBSTRING(`mail_addr`, LOCATE('@', `mail_addr`) +1 ), '/', \SUBSTRING(`mail_addr`, 1, LOCATE('@', `mail_addr`) -1) ) AS 'home', '1000' AS 'uid', \'8' AS 'gid', CONCAT('*:bytes=', `quota`, 'B') AS 'quota_rule' FROM `mail_users` \WHERE `mail_addr` = '%u' AND `status` = 'ok' AND `mail_type` LIKE '%%_mail%%'


    and this:

    Code
    1. iterate_query = SELECT `mail_addr` AS 'user' FROM `mail_users` WHERE `mail_type` LIKE '%%_mail%%'


    Now you can again run 'doveadm quota get -A'. Perhaps nuxwin could incorporate the changes into the template?


    Hello ;


    Minor problems

    • The po_active field should be there ( depending to which i-MSCP version you migrated ). I lack of information to answer correctly: eg, to which i-MSCP version you migrated exactly?
    • This is the expected behavior. No update path is provided for Dovecot since it was not implemented in ispCP ( 1.0.7 ). You must just delete the old user. If the dovecot_user is not created, it's another problem. Again, I lack of information there.


    The troubles

    • I'll have a look. We can have some mails which are both normal and forwarded. Thus, I must process some tests before answering.
    • The iterate query is not really needed for i-MSCP. Most of i-MSCP users will not use the doveadm command. Also, the iterate_query parameter is not available in dovecot versions prior 2.x.

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

    SSH key for online support

    Hi Nuxwin,

    • the test install I am refering to was updated from ispcp 1.0.7 to imscp 1.1.0(?). I have noted down the tasks done and will reproduce the situation as the production system has not been migrated yet. On the test box I'm doing regular git updates (it's now at 1.1.19) so I thought the po_active should have come in the line of imscp upgrades. So I assume a fail in a imscp migration 1.1.x to 1.1.y. When was po_active introduced?
    • I surely understand that it cannot reuse the ispcp dovecot user ;( . But when it changed to dovecot_user, MySQL wouldn't accept it. I've now rechecked things and it was the iterate task which took a default select to table users (which in turn doesn't exist :)). So this is fine now that I've fixed the iterate task. DONE!

    For the trouble:

    • Yes that's why I have the LIKE %_mail%. To handle also "normal_mail,forward_mail". Something one of my resellers makes regular use of :). The case now seems to work with normal and forwards, alias and subdomains. Feel free to recheck though!
    • The iterate query will sometime become relevant for iMSCP once Courier support get's pulled. I might be a dovecot nerd...Sorry!!! :evil:

    Courier code has not seen much activity the last years. Dovecot now can use dbox instead of maildir to compress mailboxes and lower disk IO. Expunge autocleans Trash and Junk folders. Both these features make heavy use of doveadm. Then Full text search (short fts) is a very good plugin for high performant searches in roundcube using Solr. And I have more and more users relying heavily on the use of the webmail. Don't get me into the groupware (carddav/caldav) and cloud storage (ownCloud)... dovecot is future!


    I've read about the courier support we have to keep and the not clear migration path. All true for years! Perhaps we can do the cut at 2.0... I'm waiting patiently 8o Do we still have support for Lenny? RH 4? Apache 1.x? 8)


    Adding in the iterate query doesn't hurt much. Just some more bytes. And checking is as simple as doing a 'doveadm quota get -A'.



    My big migration task is next weekend...

    Hello ;


    1. The po_active field has been added in version 1.1.15 ( see https://github.com/i-MSCP/imsc…ba8ac3472656f961f5bbbbf9d ). At one moment, the database update script was broken... Thus, you should check your database schema ;) What is the DATABASE_REVISION value in you config table exactly?
    2. I'm using Dovecot and I've never encountered any problem with the SQL user ( which is created automatically by the i-MSCP Dovecot installer ).


    For the trouble


    1. Again, I'll check the behavior, I'm not god and I don't say that what you're saying is false ;)
    2. Courier is here and will stay here as long as packages are available ;)


    BTW:

    • The default choice for the POP3/IMAP servers since i-MSCP version 1.1.21 is Dovecot.
    • Debian Lenny has a Long Term Support ( LTS ). This explain why we are still supporting it. You cannot imagine the number of administrators which are using Lenny with i-MSCP ;)
    • Nothing prevent you to use Dovecot if you prefer it ;) I don't claim that Courier is better but I respect choice of those which want use it ;)
    • The migration path for Courier to Dovecot ( and vice-versa ) is easy as running: perl imscp-autoinstall -dar po ... Of course, this prevent us to use the dbox backend for now ;)
    • I'll add the iterate query in time. I've already added it in past but removed it due to incompatibility with Dovecot < 2.x ;)

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

    SSH key for online support

    Thanks Nuxwin!


    Revision now after git up to 1.1.21 is 197. The po_active is now there, so at least here the migration did succeed. Makes me confident that you know what you are doing :)! After fixing the permissions on my custom tables (sql quota and expires) for dovecot_user everything is fine and smooth.


    I will do the migration with 1.2 now that it also has encrypted passwords again. I will report back with my experiences then... I guess ispcp 1.0.7 to imscp 1.2 should work directly!?


    And yes, I've read the news about Dovecot becoming the default. Prayers have been heard, this far at least...


    Final words for this year: I think those still on Courier just have no other choice (time, works for me, etc) but those still on Lenny have no excuse. Lenny (5.0) is to my finding not supported anymore. Yes, we also do have systems still running squeeze which indeed is the first LTS from Debian. But Lenny?


    Whatever, let's not fight about the details. Let's make some users (e.g. wifes) happy!

    Sorry, I wanted talk about Squeeze, not Lenny ( for which support has been dropped since a while already ) ;)


    Dovecot as provided in Debian Squeeze repository is the 1.1.x serie.


    Happy new year ;)

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

    SSH key for online support