I was getting the following error when trying to list mailboxes in
dbmal-imapd 2.0rc2 with mysql-server-3.23.58-1.73 on redhat 7.3
Feb 8 20:39:22 sls-cd7p8 dbmail/imap4d[17628]: COMMAND: [2 lsub "" "*"]
Feb 8 20:39:22 sls-cd7p8 dbmail/imap4d[17628]: dbmysql.c,db_query:
query [SELECT mbx.name, mbx.mailbox_idnr, mbx.owner_idnr FROM mailboxes
mbx LEFT JOIN acl ON acl.mailbox_id = mbx.mailbox_idnr JOIN subscription
sub ON sub.user_id = '9' AND sub.mailbox_id = mbx.mailbox_idnr WHERE
mbx.owner_idnr = '9' OR (acl.user_id = '9' AND acl.lookup_flag = '1')
GROUP BY mbx.name, mbx.mailbox_idnr, mbx.owner_idnr] failed
Feb 8 20:39:22 sls-cd7p8 dbmail/imap4d[17628]: dbmysql.c,db_query:
mysql_real_query failed: You have an error in your SQL syntax near 'ON
sub.user_id = '9' AND sub.mailbox_id = mbx.mailbox_idnr WHERE
mbx.owner_idnr ' at line 1
Feb 8 20:39:22 sls-cd7p8 dbmail/imap4d[17628]:
db.c,db_list_mailboxes_by_regex: error during mailbox query
Feb 8 20:39:22 sls-cd7p8 dbmail/imap4d[17628]:
db.c,db_findmailbox_by_regex: error listing mailboxes
Here is a simple patch that fixes the problem, by specifying the second
join as a LEFT join as well.
--- db.c.old Sun Feb 8 21:11:05 2004
+++ db.c Sun Feb 8 21:11:56 2004
@@ -2145,7 +2145,7 @@
"FROM mailboxes mbx "
"LEFT JOIN acl "
"ON acl.mailbox_id = mbx.mailbox_idnr "
- "JOIN subscription sub ON sub.user_id = '%llu' "
+ "LEFT JOIN subscription sub ON sub.user_id = '%llu' "
"AND sub.mailbox_id = mbx.mailbox_idnr "
"WHERE mbx.owner_idnr = '%llu' "
"OR (acl.user_id = '%llu' AND acl.lookup_flag =
'1') "
Cheers,
Blake
