ispCP - Board - Support
Roundcube quota error - Printable Version

+- ispCP - Board - Support (http://www.isp-control.net/forum)
+-- Forum: ispCP Omega Support Area (/forum-30.html)
+--- Forum: Usage (/forum-34.html)
+--- Thread: Roundcube quota error (/thread-5867.html)

Pages: 1 2 3

RE: Roundcube quota error - BeNe - 03-07-2009 11:10 AM

Quote:No I don't have it implemented. I rather have the mails stuck in my queue as bounced to the sender... So I am able to get in touch with the customer and after he deleted some messages or bought more quota I can flush the queue and he receive his mails...
Make sense and he get´s every mail!

I fixed something now and the most works now Smile Means the Quota with 1GB and the mail delivering! BUT Filtering (pysieved) doesn´t work anymore.
I can see my old filters and create a new one, but without any effect!

Here is my config:
Code:
# 1.1.9: /etc/dovecot/dovecot.conf
# OS: Linux 2.6.9-023stab048.4-smp i686 Debian 5.0 reiserfs
base_dir: /var/run/dovecot/
protocols: imap pop3 imaps pop3s
disable_plaintext_auth: no
login_dir: /var/run/dovecot//login
login_executable(default): /usr/lib/dovecot/imap-login
login_executable(imap): /usr/lib/dovecot/imap-login
login_executable(pop3): /usr/lib/dovecot/pop3-login
mail_access_groups: mail
mail_location: maildir:/var/mail/virtual/%d/%n
mail_executable(default): /usr/lib/dovecot/imap
mail_executable(imap): /usr/lib/dovecot/imap
mail_executable(pop3): /usr/lib/dovecot/pop3
mail_plugins(default): quota imap_quota
mail_plugins(imap): quota imap_quota
mail_plugins(pop3): quota
mail_plugin_dir(default): /usr/lib/dovecot/modules/imap
mail_plugin_dir(imap): /usr/lib/dovecot/modules/imap
mail_plugin_dir(pop3): /usr/lib/dovecot/modules/pop3
pop3_uidl_format(default): %08Xu%08Xv
pop3_uidl_format(imap): %08Xu%08Xv
pop3_uidl_format(pop3): %u-%v
namespace:
  type: private
  prefix: INBOX.
  inbox: yes
  list: yes
  subscriptions: yes
auth pysieved:
  mechanisms: plain login
  verbose: yes
  passdb:
    driver: checkpassword
    args: /var/www/ispcp/engine/ispcp-dovecot-mngr
  userdb:
    driver: prefetch
  userdb:
    driver: sql
    args: /etc/dovecot/dovecot-sql-domain.conf
  userdb:
    driver: sql
    args: /etc/dovecot/dovecot-sql-aliasdomain.conf
  userdb:
    driver: sql
    args: /etc/dovecot/dovecot-sql-subdomain.conf
  socket:
    type: listen
    client:
      path: /var/run/pysieved/auth
      mode: 432
      user: vmail
      group: mail
    master:
      path: /var/run/pysieved/auth-master
      mode: 432
      user: vmail
      group: mail
auth default:
  mechanisms: plain login
  verbose: yes
  passdb:
    driver: checkpassword
    args: /var/www/ispcp/engine/ispcp-dovecot-mngr
  userdb:
    driver: prefetch
  userdb:
    driver: sql
    args: /etc/dovecot/dovecot-sql-domain.conf
  userdb:
    driver: sql
    args: /etc/dovecot/dovecot-sql-aliasdomain.conf
  userdb:
    driver: sql
    args: /etc/dovecot/dovecot-sql-subdomain.conf
  socket:
    type: listen
    client:
      path: /var/spool/postfix/private/auth
      mode: 432
      user: postfix
      group: postfix
    master:
      path: /var/run/dovecot/auth-master
      mode: 432
      user: vmail
      group: mail
plugin:
  quota: maildir:User quota
  quota_rule: *:storage=1G
And here is the SQL String i´m currently using:
(dovecot-sql-domain.conf)
Code:
user_query = SELECT concat('/var/mail/virtual/', domain.domain_name, '/',mail_acc) as home, '1000' as uid, '8' as gid, concat('maildir:storage=', floor(quota/1024)) AS quota FROM mail_users inner join domain on mail_users.domain_id = domain.domain_id WHERE mail_acc='%n' and (mail_type='normal_mail' OR mail_type='normal_mail,normal_forward');
As you can see, this is the string from the HowTo and not the String that you posted. It is more or less a mix of both and work.
This is the on you posted some post before with the 1GB Quota:
Code:
user_query = SELECT concat('/var/mail/virtual/', domain.domain_name, '/',mail_acc) as home, '1000' as uid, '8' as gid, concat(''*:bytes='', quota) AS quota_rule FROM mail_users inner join domain on mail_users.domain_id = domain.domain_id WHERE mail_acc='%n' and (mail_type='normal_mail' OR mail_type='normal_mail,normal_forward');
But with this i get Errors in the mail.log and pysieved doesn´t work so i get no e-Mails:
Code:
auth-worker(default): sql(bene@mydomain.tld): User query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':bytes='', quota) AS quota_rule FROM mail_users inner join domain on mail_users.' at line 1
Mar  7 00:28:41 vsXXXX dovecot: auth-worker(default): sql(bene@mydomain.tld): Unknown user
But why ?


Quote:Hmm I might need some sleep ;-)
Dito! Wink

Greez BeNe

RE: Roundcube quota error - rbtux - 03-07-2009 11:19 AM

sieve plugin is not loaded or not even compiled...
about the sql
Code:
''*:bytes=''

should be
Code:
"*:bytes="


RE: Roundcube quota error - BeNe - 03-07-2009 10:11 PM

Quote:sieve plugin is not loaded or not even compiled...
Yeah looks so Rolleyes
Code:
# dovecot --build-options
Build options: ioloop=epoll notify=inotify ipv6 openssl
Mail storages: maildir mbox dbox cydir raw
SQL drivers: mysql postgresql sqlite
Passdb: checkpassword ldap pam passwd passwd-file shadow sql
Userdb: nss ldap passwd passwd-file prefetch sql static

Thanks about the SQL correction.

Greez BeNe

RE: Roundcube quota error - rbtux - 03-07-2009 10:29 PM

sieve is built against dovecot not with dovecot...

download it from: http://www.dovecot.org/releases/sieve/dovecot-sieve-1.1.6.tar.gz
you compile with:
Code:
./configure --with-dovecot=<see below>
make
make install

--with-dovecot has to point to the directory containing the dovecot-config file (when installing the according -dev package ubuntu and I guess thus debian puts this into /usr/lib/dovecot)

What did you actually install? Where have you got this 1.1.9 from? Because debian normally builds the sieve plugin...

RE: Roundcube quota error - BeNe - 03-07-2009 11:06 PM

I will test it as you said.
Quote:What did you actually install? Where have you got this 1.1.9 from? Because debian normally builds the sieve plugin...
I used the Repositroy from kirya -> http://packages.kirya.net/debian/pool/main/d/dovecot/
I sent an e-Mail to him about sieve and his precompiled-package.
Here is the answer:
Quote:...
I use sieve with "my" backported packages without any issue (everything
is in dovecot-common)
...
So this should be Ok. Maybe i must reinstall sieve as you said because i use now 1.1 and not more 1.0.

Greez BeNe

RE: Roundcube quota error - TheCry - 03-12-2009 03:19 AM

Hi together..
Today i've installed dovecot with the tutorial from the WIKI.
To see the quota in roundcube you have to add one entry in the section "plugins" to the dovecot.conf.
Code:
plugin {
quota = maildir:User quota:ignore=Trash
}
The ":ignore=Trash" is optional.

Can you edit the WIKI, too?

Cu
Sascha

RE: Roundcube quota error - BeNe - 03-12-2009 03:54 AM

Yes, there is some more todo also if you want to use the quota from the ispcp table. I will try to add the modification to the wiki this week.

Greez BeNe

RE: Roundcube quota error - TheCry - 03-12-2009 04:12 AM

BTW... Is it possible to change this Quota from the ISCP Panel?
I did'nt find it anywhere.

RE: Roundcube quota error - BeNe - 03-12-2009 05:06 PM

No Sad
I do it directly in the MySQL DB without a gui.

Greez BeNe

RE: Roundcube quota error - FISA4 - 03-13-2009 08:31 AM

i tried to make a enhancement for this "little" problem but as a result of my non existing knowledge of "writing SQL-Statements in ispCP style" i had to write the SQL as i know it. Wink

at the moment the "enhancement" can only read the quota but i will change it that it will write the new quota.

It works only with dovecot >= 1.1.0 not with the 1.0.RC5

At the moment i write the HowTo. I will add it as soon as possible.

[EDIT PART 1 - Show Mailbox Quota in GUI]

open /var/www/ispcp/gui/reseller/domain_edit.php:

after:
Code:
require '../include/ispcp-lib.php';

add:
Code:
include '../include/ispcp-mailquota.php

around line 69 after:
Code:
'TR_DISK' => tr('Disk limit [MB] <br /><i>(0 unlimited)</i>'),

add:
Code:
'TR_MAIL_QUOTA' => tr('Mailbox Quota [MB]'),

Change Line 229 form:
Code:
GLOBAL $username;

to
Code:
GLOBAL $username, $mailquota;

around Line 282 after:
Code:
'VL_DOM_DISK' => $disk,

add:
Code:
'VL_MAIL_QUOTA' => $mailquota,


now we have to edit /var/www/ispcp/gui/themes/omega_original/reseller/domain_edit.tpl

after:
Code:
            <tr>
                        <td width="25">&nbsp;</td>
                        <td class="content2" width="193">{TR_MAIL_ACCOUNT}</td>
                        <td class="content"><input type="text" name="dom_mail_acCount" value="{VL_DOM_MAIL_ACCOUNT}" style="width:100px" class="textinput">
                        </td>
                      </tr>

add:
Code:
<tr>
                        <td width="25">&nbsp;</td>
                        <td class="content2" width="193">{TR_MAIL_QUOTA}</td>
                        <td class="content"><input type="text" name="mailboxquota" value="{VL_MAIL_QUOTA}" style="width:100px" class="textinput"></td>
                        </td>
                      </tr>

last create a new file: /var/www/ispcp/gui/includes/ispcp-mailquota.php
insert :
Code:
<?php
/**
* ispCP ω (OMEGA) a Virtual Hosting Control System
*
* @copyright     2001-2006 by moleSoftware GmbH
* @copyright     2006-2008 by ispCP | http://isp-control.net
* @version     SVN: $ID$
* @link         http://isp-control.net
* @author         ispCP Team
*
* @license
*   This program is free software; you can redistribute it and/or modify it under
*   the terms of the MPL General Public License as published by the Free Software
*   Foundation; either version 1.1 of the License, or (at your option) any later
*   version.
*   You should have received a copy of the MPL Mozilla Public License along with
*   this program; if not, write to the Open Source Initiative (OSI)
*   http://opensource.org | osi@opensource.org
*/


/** Connect to Database */

$dbUser="root";     // Database Username
$dbPass="";        // Database Password
$dbHost="localhost";    // Database Server
$dbName="ispcp";    // Database

$connect = @mysql_connect($dbHost, $dbUser, $dbPass) or die("Could not connect to Database-Server!");
$selectDB = @mysql_select_db($dbName, $connect) or die("Could not select <b>$dbName</b>!");


/** Read Mailbox Quota */

if(isset($_GET['edit_id']))
{
$id=$_GET['edit_id'];

$sql="SELECT `quota` FROM `mail_users` WHERE `domain_id` = '".$id."' LIMIT 1;";
$handle = mysql_query($sql);
$result=mysql_fetch_object($handle);
$mailquota = $result->quota/1024/1024;
mysql_close();
}

?>
Maybe someone could change it that we can remove the "connect to database" part.

[/EDIT]

Sorry for my poor english, i am a native german. Wink