Virtual Domain throws SQL error, unclear where it comes from

[tneo]

I have configured my server to use Dovecot, Postfix and PostfixAdmin to set up a virtual domain configuration. When I access the Virtual Domains page on the Postfix Mail Server page, the following error is shown:

This map cannot be edited : Failed to query table : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from where = limit 1' at line 1

It is unclear what Webmin is trying to execute in this instance. I checked all the configuration files and the queries and nothing is wrong. What is Webmin trying to do on this page?

In case it matters. I followed this setup: https://www.linuxbabe.com/redhat/postfixadmin-create-virtual-mailboxes-centos-mail-server

[jcameron]

This can happen if Webmin cannot parse the MySQL settings in your Postfix configuration to extract the query and table.

Can you attach your Postfix config to this bug report, or at least the lines that reference the virtual table?

[tneo]

I think you just need these lines, right?

relay_domains = $mydestination lmdb:/etc/postfix/relay

virtual_alias_domains =

virtual_alias_maps = proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf, proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_maps.cf, proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_catchall_maps.cf

virtual_uid_maps = static:303

virtual_gid_maps = static:303

virtual_minimum_uid = 303

virtual_mailbox_base = /srv/maildirs

virtual_mailbox_domains = proxy:mysql:/etc/postfix/sql/mysql_virtual_domains_maps.cf

virtual_mailbox_limit = 0

virtual_mailbox_limit_inbox = no

virtual_mailbox_maps = proxy:mysql:/etc/postfix/sql/mysql_virtual_mailbox_maps.cf, proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_mailbox_maps.cf

For dovecot LMTP replace 'virtual' with 'lmtp:unix:private/dovecot-lmtp'

virtual_transport = virtual

Additional for quota support

virtual_mailbox_limit_maps = mysql:/etc/postfix/mysql_virtual_mailbox_limit_maps.cf

virtual_mailbox_limit_override = yes

Needs Maildir++ compatible IMAP servers, like Courier-IMAP

virtual_maildir_filter = yes

virtual_maildir_filter_maps = lmdb:/etc/postfix/vfilter

virtual_maildir_limit_message = Sorry, the user's maildir has overdrawn his diskspace quota, please try again later.

virtual_maildir_limit_message_maps = lmdb:/etc/postfix/vmsg

virtual_overquota_bounce = yes

virtual_trash_count = yes

virtual_trash_name = ".Trash"

[jcameron]

I'd be interested to see what's in /etc/postfix/sql/mysql_virtual_alias_maps.cf

[tneo]

user = postman
password = strong_password
hosts = localhost
dbname = postfixadmin
query = SELECT goto FROM alias WHERE address='%s' AND active = '1'
#expansion_limit = 100

[jcameron]

That looks ok. What about the contents of mysql_virtual_alias_domain_maps.cf and mysql_virtual_alias_domain_catchall_maps.cf

[tneo]

mysql_virtual_alias_domain_maps.cf

user = postman
password = strong_password
hosts = localhost
dbname = postfixadmin
query = SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' and alias.address = CONCAT('%u', '@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active='1'

mysql_virtual_alias_domain_catchall_maps.cf

user = postman
password = strong_password
hosts = localhost
dbname = postfixadmin
query = SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' and alias.address = CONCAT('@', alias_domain.target_domain) AND alias.active = 1 AND alias_domain.active='1'

[jcameron]

Ok, that's a problem - Webmin can't parse that SQL statement to figure out which tables it needs to update to add and remove aliases.

It only supports simple table structures like you've used in mysql_virtual_alias_maps.cf

[tneo]

Right. I'll won't use webmin for Virtual mail than. :)

[jcameron]

I mean you could, but it would require potentially a change to your table structure for storing mail aliases.

[tneo]

I understand. I'm not inclined to tinker with the table structure.