Re: [Postfixadmin-devel] Unable to display mailboxes.

[<me...@td...>]

On Tue, 28 Apr 2009, David Goodwin wrote:

>> I did a little more investigation and found that if I set $CONF['used_quotas']
>> to no then the mailbox data shows up. If I reset it to yes then the mailbox
>> data goes away.
>
> Ah. That's interesting.
>
>>
>> While I am talking about quotas, assuming a reasonable dovecot config, is it
>> reasonable to expect that dovecot v1.2+ quotas will work with pfa?
>> http://wiki.dovecot.org/Quota/Dict indicates that the 1.2+ quotas are
>> different. If I use something like below to create the quota table, will
>> pfa know how to use the data that gets put in there?
>>
>
> As much as PFA can use the data in there; I suspect so.
>
>> CREATE TABLE quota (
>>   username varchar(100) not null,
>>   bytes bigint not null default 0,
>>   messages integer not null default 0,
>>   primary key (username)
>> );
>>
>> When I created the quota table I attached, I used what is in the DOVECOT.txt
>> file included with pfa. My intent is to use pfa with dove 1.2 so if I can do
>> something to get quotas working, that would be a good thing.
>>
>
> I've never used dovecot; or quotas... so I don't really know anything

I never used dovecot quotas either!! I am still learning how they work. :)

> about it. I'm guessing that table is used to keep a track of the
> current quota usage - as opposed to the limit (which is all PFA cares
> about).

According to the notes in config.inc.php, pfa is supposed to "Show used quotas
from Dovecot dictionary backend in virtual mailbox listing." This is what
is not working as far as I can tell. It appears to me that this was added
by this patch:
https://sourceforge.net/tracker/index.php?func=detail&aid=2060309&group_id=191583&atid=937966

Anyway upon further investigation it appears that the Dovecot V1.2 quota
setup is completely different from previous versions. I followed the
instructions at http://wiki.dovecot.org/Quota/Dict. According to this
document there was a problem with quota calculations in previous versions
under certain conditions. The new way to track quotas fixes this.

So, I configured dovecot as per the instructions for dovecot V1.2 on the 
dovecot wiki. Dovecot and quotas appear to work except that I cannot display
the used quotas. If I enable $CONF['used_quotas'] and try to display the
mailboxes I get the following errors:

DEBUG INFORMATION:
Invalid query: Unknown column 'quota.current' in 'field list'

This error appears to come from list-virtual.php on about line 171.

I think the problem is with the line:

$query = "SELECT $table_mailbox.*, $table_vacation.active AS v_active, $table_quota.current FROM $table_mailbox LEFT JOIN $table_vacation ON $table_mailbox.username=$table_vacation.email LEFT JOIN $table_quota ON $table_mailbox.username=$table_quota.username WHERE $table_mailbox.domain='$fDomain' AND $table_quota.path='quota/storage' ORDER BY $table_mailbox.username LIMIT $page_size OFFSET $fDisplay";

but I am not sure since I am not a programmer.

Below are the relevant config files.

############### Begin MySQL Quota table ##########################################

-- Generation Time: Apr 29, 2009 at 03:13 PM

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `postfix1`
--

-- --------------------------------------------------------

--
-- Table structure for table `quota`
--

CREATE TABLE IF NOT EXISTS `quota` (
   `username` varchar(100) NOT NULL,
   `bytes` bigint(20) NOT NULL default '0',
   `messages` int(11) NOT NULL default '0',
   PRIMARY KEY  (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `quota`
--

INSERT INTO `quota` (`username`, `bytes`, `messages`) VALUES
('te...@ex...', 1980, 2);

############### End MySQL Quota table ##########################################

################### Begin dovecot-dict-quota.conf ##########################

# v1.2+ only:
connect = host=localhost dbname=postfix user=postfix password=my_pass
map {
   pattern = priv/quota/storage
   table = quota
   username_field = username
   value_field = bytes
}
map {
   pattern = priv/quota/messages
   table = quota
   username_field = username
   value_field = messages
}

################### End dovecot-dict-quota.conf ##########################

When I send a message to a valid mailbox, the quota table gets populated with
the correct info.

Can someone look at this and give me an idea what I need to do to get this
working?

Should I file a bug in the tracker for this?

Regards,

-- 
Tom			me...@td...		Spamtrap address	 		me...@td...