How to get mailbox limits from database in 11.5 - was working in previous version

[Christopher McBride]

Hi,

I have a script which checks the mailboxes of all clients to notify them when they're reaching capacity.

This does so by checking the psa database for their set limit and matching that to the maildirsize file within the user's Maildir directory.

After upgrading, though, my SQL query no longer works - it was:

SELECT clients.email as owner_email, domains.id, domains.name, Limits.value AS domain_quota, mail.mail_name, mail.mbox_quota from Limits, clients, domains, mail WHERE mail.postbox='true' AND limit_name = 'mbox_quota' AND domains.limits_id = Limits.id AND mail.dom_id = domains.id AND clients.id = domains.cl_id ORDER BY domains.name ASC

However, on closer inspection, all values in the domains table for limit_id are NULL.


Has this changed in 11.5? Is there an alternative way to quickly get all limits for domains?


Thanks

 

[IgorG]

It seems it is limitsId from SubscriptionProperties:

mysql> select * from Limits where limit_name = 'mbox_quota';
+----+------------+-----------+
| id | limit_name | value |
+----+------------+-----------+
| 1 | mbox_quota | 104857600 |
| 2 | mbox_quota | 104857600 |
| 3 | mbox_quota | 104857600 |
| 4 | mbox_quota | 104857600 |
| 5 | mbox_quota | -1 |
| 6 | mbox_quota | 104857600 |
| 7 | mbox_quota | -1 |
| 8 | mbox_quota | 104857600 |
| 9 | mbox_quota | -1 |
| 10 | mbox_quota | 104857600 |
| 13 | mbox_quota | 104857600 |
| 14 | mbox_quota | 104857600 |
| 15 | mbox_quota | 104857600 |
| 16 | mbox_quota | 104857600 |
+----+------------+-----------+
14 rows in set (0.00 sec)

mysql> select * from SubscriptionProperties where name='limitsId';
+-----------------+----------+-------+
| subscription_id | name | value |
+-----------------+----------+-------+
| 1 | limitsId | 1 |
| 5 | limitsId | 5 |
| 7 | limitsId | 7 |
| 9 | limitsId | 9 |
| 13 | limitsId | 13 |
| 14 | limitsId | 14 |
| 15 | limitsId | 15 |
| 16 | limitsId | 16 |
+-----------------+----------+-------+
8 rows in set (0.00 sec)

 

[Christopher McBride]

Thanks Igor.

The query has then been refactored to:

SELECT domains.id, domains.name, clients.email as owner_email, mail.mail_name, Limits.value AS domain_quota, mail.mbox_quota from Limits, clients, domains, mail, Subscriptions, SubscriptionProperties WHERE mail.postbox='true' AND limit_name = 'mbox_quota' AND mail.dom_id = domains.id AND clients.id = domains.cl_id AND Subscriptions.id=SubscriptionProperties.subscription_id AND Subscriptions.object_type='domain' AND object_id=domains.id AND SubscriptionProperties.name = 'limitsID' AND SubscriptionProperties.value = Limits.id ORDER BY domains.name ASC

 

[Christopher McBride]

Out of curiosity, is this feature of checking mailboxes approaching capacity, going to come natively to Plesk soon?

 

[HMoellendorf87]

Hi Christopher McBride,

this feature is under review by Parallels as you can see here: http://plesk.uservoice.com/forums/1...mer-email-notification-when-mailbox-quota-exc