View Single Post
  #4  
Old 21st July 2006, 21:25
wwinfrey wwinfrey is offline
Junior Member
 
Join Date: Jul 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK, I took a look at this post, and from there, looked at the Postfix mysql table man page at http://www.postfix.org/mysql_table.5.html.

After reading about the differences in how these .cf files are formed, I made the following changes to the .cf tables.

mysql-virtual_domains.cf:

Code:
user = mail_admin
password = password
dbname = mail
query = SELECT virtual FROM domains WHERE domain = '%s'
hosts = 127.0.0.1
mysql-virtual_email2email.cf:

Code:
user = mail_admin
password = password
dbname = mail
query = SELECT email FROM users WHERE email = '%s'
hosts = 127.0.0.1
mysql-virtual_forwardings.cf:

Code:
user = mail_admin
password = password
dbname = mail
query = SELECT destination FROM forwardings WHERE source = '%s'
hosts = 127.0.0.1
mysql-virtual_mailbox_limit_maps.cf:

Code:
user = mail_admin
password = password
dbname = mail
query = SELECT quota FROM users WHERE email = '%s'
hosts = 127.0.0.1
mysql-virtual_mailboxes.cf:

Code:
user=mail_admin
password = password
dbname = mail
query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = '%s'
hosts = 127.0.0.1
mysql-virtual_transports.cf:

Code:
user = mail_admin
password = password
dbname = mail
query = SELECT transport FROM transport WHERE domain = '%s'
hosts = 127.0.0.1
After putting in these changes, I restarted postfix. Now, when I try the same telnet test (to send mail to user@virtual.test), I get very similar results to earlier:

/var/log/mysql/mysql.log:

Code:
060721 13:13:21     122 Connect     mail_admin@localhost on mail
                    122 Query       set autocommit=1
                    122 Query       SELECT "Y" as local FROM domains WHERE CONCAT("@",domain) IN ('user@virtual.test','@virtual.test','@.virtual.test','@.test','@.')
                    119 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = 'localhost'
                    118 Query       SELECT transport FROM transport WHERE domain = 'test@localhost'
                    118 Query       SELECT transport FROM transport WHERE domain = 'localhost'
                    119 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = 'virtual.test'
                    118 Query       SELECT transport FROM transport WHERE domain = 'user@virtual.test'
                    118 Query       SELECT transport FROM transport WHERE domain = 'virtual.test'
                    118 Query       SELECT transport FROM transport WHERE domain = '.test'
                    120 Query       SELECT destination FROM forwardings WHERE source = 'user@virtual.test'
                    123 Connect     mail_admin@localhost on mail
                    123 Query       SELECT email FROM users WHERE email = 'user@virtual.test'
                    119 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = 'virtual.test'
                    118 Query       SELECT transport FROM transport WHERE domain = 'user@virtual.test'
                    118 Query       SELECT transport FROM transport WHERE domain = 'virtual.test'
                    118 Query       SELECT transport FROM transport WHERE domain = '.test'
                    120 Query       SELECT destination FROM forwardings WHERE source = 'test@localhost'
                    123 Query       SELECT email FROM users WHERE email = 'test@localhost'
                    120 Query       SELECT destination FROM forwardings WHERE source = 'test'
                    123 Query       SELECT email FROM users WHERE email = 'test'
                    120 Query       SELECT destination FROM forwardings WHERE source = '@localhost'
                    123 Query       SELECT email FROM users WHERE email = '@localhost'
                    119 Query       SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email = 'localhost'
                    118 Query       SELECT transport FROM transport WHERE domain = 'test@localhost'
                    118 Query       SELECT transport FROM transport WHERE domain = 'localhost'
and from /var/log/mail.log:

Code:
Jul 21 13:13:21 twinkie postfix/cleanup[6757]: 133CA3BB466F: message-id=<20060721191239.133CA3BB466F@twinkie.domain.com>
Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 133CA3BB466F: from=<test@localhost>, size=388, nrcpt=1 (queue active)
Jul 21 13:13:21 twinkie amavis[4054]: (04054-02) NOTICE: reconnecting in response to: sql execute: sts=2006, DBD::mysql::st execute failed: MySQL server has gone away at (eval 41) line 1
27, <GEN29> line 5.
Jul 21 13:13:21 twinkie postfix/smtpd[6761]: connect from localhost[127.0.0.1]
Jul 21 13:13:21 twinkie postfix/smtpd[6761]: 5C13B3BB4670: client=localhost[127.0.0.1]
Jul 21 13:13:21 twinkie postfix/cleanup[6757]: 5C13B3BB4670: message-id=<20060721191239.133CA3BB466F@twinkie.domain.com>
Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 5C13B3BB4670: from=<test@localhost>, size=792, nrcpt=1 (queue active)
Jul 21 13:13:21 twinkie postfix/smtpd[6761]: disconnect from localhost[127.0.0.1]
Jul 21 13:13:21 twinkie amavis[4054]: (04054-02) Passed, <test@localhost> -> <user@virtual.test>, quarantine lSSGqyu4ahkW, Message-ID: <20060721191239.133CA3BB466F@twinkie.domain.co
m>, Hits: -
Jul 21 13:13:21 twinkie postfix/smtp[6758]: 133CA3BB466F: to=<user@virtual.test>, relay=127.0.0.1[127.0.0.1], delay=55, status=sent (250 2.6.0 Ok, id=04054-02, from MTA([127.0.0.1]:10025
): 250 Ok: queued as 5C13B3BB4670)
Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 133CA3BB466F: removed
Jul 21 13:13:21 twinkie postfix/smtp[6763]: 5C13B3BB4670: to=<user@virtual.test>, relay=none, delay=0, status=bounced (Host or domain name not found. Name service error for name=virtual.
test type=A: Host not found)
Jul 21 13:13:21 twinkie postfix/cleanup[6757]: 8B2E53BB46EE: message-id=<20060721191321.8B2E53BB46EE@twinkie.domain.com>
Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 8B2E53BB46EE: from=<>, size=2651, nrcpt=1 (queue active)
Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 5C13B3BB4670: removed
Jul 21 13:13:21 twinkie postfix/local[6765]: 8B2E53BB46EE: to=<test@localhost>, relay=local, delay=0, status=bounced (unknown user: "test")
Jul 21 13:13:21 twinkie postfix/qmgr[6717]: 8B2E53BB46EE: removed
Jul 21 13:13:22 twinkie postfix/smtpd[6750]: disconnect from localhost[127.0.0.1]
It appears the problem is in the query executed on the mail.users table is incorrect. It's ending with "WHERE email = 'virtual.test'" instead of "WHERE email = 'user@virtual.test'". Any idea why the %s substitution is failing ? It seems to be the correct query everywhere else except for virtual_mailboxes.
Reply With Quote