Hi there. Mail is stuck in the queue and I've got these in mail.log which might be the reason why. Permissions for /etc/postfix/mysql-virtual* is 640. I already have clamav and amavis disabled. Code: Jul 1 18:01:13 mail postfix/smtpd[11188]: connect from localhost[127.0.0.1] Jul 1 18:01:13 mail postfix/smtpd[11188]: CA052D8D7AF: client=localhost[127.0.0.1] Jul 1 18:01:13 mail postfix/cleanup[11183]: warning: proxy:mysql:/etc/postfix/mysql-virtual_outgoing_bcc.cf lookup error for "[email protected]" Jul 1 18:01:13 mail postfix/cleanup[11183]: warning: CA052D8D7AF: sender_bcc_maps map lookup problem -- message not accepted, try again later Jul 1 18:01:13 mail postfix/cleanup[11183]: CA2B6D8D7AF: message-id=<[email protected]> Jul 1 18:01:13 mail postfix/smtpd[11188]: disconnect from localhost[127.0.0.1] ehlo=1 mail=1 rcpt=1 data=0/1 rset=1 quit=1 commands=5/6 Jul 1 18:01:13 mail postfix/qmgr[6469]: CA2B6D8D7AF: from=<[email protected]>, size=1159, nrcpt=1 (queue active) Jul 1 18:01:13 mail postfix/smtpd[11188]: connect from localhost[127.0.0.1] Jul 1 18:01:13 mail postfix/smtpd[11188]: CC630D8E086: client=localhost[127.0.0.1] Jul 1 18:01:13 mail postfix/cleanup[11183]: warning: proxy:mysql:/etc/postfix/mysql-virtual_outgoing_bcc.cf lookup error for "[email protected]" Jul 1 18:01:13 mail postfix/cleanup[11183]: warning: CC630D8E086: sender_bcc_maps map lookup problem -- message not accepted, try again later Jul 1 18:01:13 mail postfix/cleanup[11183]: CC90DD8E086: message-id=<[email protected]> Jul 1 18:01:13 mail postfix/smtpd[11188]: disconnect from localhost[127.0.0.1] ehlo=1 mail=1 rcpt=1 data=0/1 rset=1 quit=1 commands=5/6 Jul 1 18:01:13 mail postfix/qmgr[6469]: CC90DD8E086: from=<[email protected]>, size=1159, nrcpt=1 (queue active) Jul 1 18:01:13 mail postfix/smtpd[11188]: connect from localhost[127.0.0.1] Jul 1 18:01:13 mail postfix/smtpd[11188]: CE6D8D8E089: client=localhost[127.0.0.1] Jul 1 18:01:13 mail postfix/cleanup[11183]: warning: proxy:mysql:/etc/postfix/mysql-virtual_outgoing_bcc.cf lookup error for "[email protected]" Jul 1 18:01:13 mail postfix/cleanup[11183]: warning: CE6D8D8E089: sender_bcc_maps map lookup problem -- message not accepted, try again later Jul 1 18:01:13 mail postfix/cleanup[11183]: CE95BD8E089: message-id=<[email protected]> Jul 1 18:01:13 mail postfix/smtpd[11188]: disconnect from localhost[127.0.0.1] ehlo=1 mail=1 rcpt=1 data=0/1 rset=1 quit=1 commands=5/6 Jul 1 18:01:13 mail postfix/qmgr[6469]: CE95BD8E089: from=<[email protected]>, size=1159, nrcpt=1 (queue active)
That is the correct permissions (owner root, group postfix). What does /etc/postfix/mysql-virtual_outgoing_bcc.cf contain (sans the actual password)? You can query against that map manually with Code: postmap -q [email protected] mysql:/etc/postfix/mysql-virtual_outgoing_bcc.cf It should return the list of outgoing bcc addrs for existing email addresses with 'Send outgoing BCC to' set, and should return empty for any non-existent email addresses and for any without that field set. If that's the only mysql map being complained about, I'd guess you have a table structure or permissions problem. You could try to run update.php from ISPConfig installation sources and let it reconfigure database permissions and also reconfigure services. If that doesn't fix it, and postmap doesn't tell you a more useful error (I don't know if it will or not), try to query manually using the info in mysql-virtual_outgoing_bcc.cf Code: # mysql -u ispconfig -p dbispconfig Enter password: (paste password from the .cf file) MariaDB [dbispconfig]> select sender_cc from mail_user where email = "[email protected]"; It will probably fail, but produce an error somewhere which indicates the problem. Additional thought: you might try running a repair of all your database tables and see if things change.
Thanks Jesse. This is the mysql-virtual-outgoing_bcc.cf file Code: user = ispconfig password = 05accfbb35597ca681f417f97c6c92bc dbname = dbispconfig table = mail_user select_field = sender_cc where_field = email additional_conditions = and postfix = 'y' and disabledeliver = 'n' and disablesmtp = 'n' and sender_cc != '' hosts = 127.0.0.1 Code: postmap -q [email protected] mysql:/etc/postfix/mysql-virtual_outgoing_bcc.cf postmap: warning: mysql:/etc/postfix/mysql-virtual_outgoing_bcc.cf: query failed: Unknown column 'sender_cc' in 'field list' postmap: fatal: table mysql:/etc/postfix/mysql-virtual_outgoing_bcc.cf: query error: Operation not supported I am having trouble logging into the database using username ispconfig. I tried earlier to reset the password but that resulted in the ISPConfig portal showing a blank page so I reset the password back to the clear text in the config.inc.php file. I have run the update.php script several times even this last time forcing a reconfiguration of permissions but I still have the same problem. I tried this earlier via phpmyadmin but no errors were returned, everything returned "OK". I'm trying not to tweak the database much without guidance because I'm a noob in that area. I do appreciate the step-by-step procedure included here. I forgot to add that i run this script but it hang.
I run the same command against alias-maps.cf Code: root@mail-20:/etc/postfix# postmap -q [email protected] mysql:/etc/postfix/mysql-virtual-alias-maps.cf postmap: warning: connect to mysql server 127.0.0.1: Access denied for user 'ispconfig'@'localhost' (using password: YES) postmap: fatal: table mysql:/etc/postfix/mysql-virtual-alias-maps.cf: query error: Success
That looks fine. You're missing at least the sender_cc column of mail_user table. You probably want to verify the entire table structure, which you can do manually via phpmyadmin or using mysqldump to dump your table definition, and compare your tables to https://git.ispconfig.org/ispconfig/ispconfig3/-/blob/stable-3.1/install/sql/ispconfig3.sql; or you might just try applying all the recent incremental updates from https://git.ispconfig.org/ispconfig/ispconfig3/-/tree/stable-3.1/install/sql/incremental and see if that catches the table structure up. Make a backup of the database before you start. That means the password in mysql-virtual-alias-maps.cf is wrong; apparently the one in mysql-virtual_outgoing_bcc.cf is correct. Reconfiguring services with update.php should have rewritten all of those files correctly, unless it didn't complete correctly or they've been modified since then.
That's the other bit that confused me. I already have sender_cc in the table mail_user. I was wondering whether it is required in another table. [attached] You're right. There was a wrong password in that file. Corrected it and run it again. Code: root@mail:/etc/postfix# postmap -q [email protected] mysql:/etc/postfix/mysql-virtual-alias-maps.cf postmap: warning: mysql:/etc/postfix/mysql-virtual-alias-maps.cf: query failed: Table 'dbispconfig.alias' doesn't exist postmap: fatal: table mysql:/etc/postfix/mysql-virtual-alias-maps.cf: query error: Operation not supported I am looking for instructions on how to run the incremental script.
Curious. Two thoughts on that: maybe it "looks like" sender_cc but isn't really, eg. a hidden/control character in the .cf file ? or is it a different server, eg. if you have a multi-server setup, and phpmyadmin is showing the control panel node as good, but the mail server node is where the problem is (I think this is not the case, because your .cf file has hosts = 127.0.0.1)? Hmm.... that sounds like a bad 'mysql-virtual-alias-maps.cf' file, but in checking to paste exactly what it should look like, I see that that file is not from ISPConfig, it is simply named in a similar fashion. What does it contain? My guess is you can just remove that from virtual_alias_maps and call it good, but might want to investigate if it's doing something (local config/setup).
The .sql files are just sql commands, you can paste them into a mysql connection or run them via phpmyadmin. As you are showing the sender_cc column in your screenshot, I suspect this may not be your problem as much, and you're likely to just see a bunch of "column already exists" type errors.
Yes I do have a multi-server setup, db being the master. In the .cf file should the hosts = IP address of the master instead of 127.0.0.1? Here's the /etc/postfix/mysql-virtual-alias.cf Code: user = ispconfig hosts = 127.0.0.1 dbname = dbispconfig password = 04accfbb57w54fa681f417f99c6c95bc query = SELECT destination FROM alias WHERE source = '%s' additional_conditions = and postfix = 'y' and server_id = 2 Okay, duly noted. Running this query in dbispconfig returns similar results Besides these two, the queries against the other columns seem fine. I now understand what you meant.
I run the query again but added the quotation marks and got some output Code: [...] | maildir_format | | maildir_format | | maildir_format | | maildir_format | +----------------+ 1090 rows in set (0.00 sec) Code: [...] | sender_cc | | sender_cc | | sender_cc | | sender_cc | | sender_cc | | sender_cc | +-----------+ 1090 rows in set (0.00 sec)
I checked the mysql database in the mail server and realized the columns bringing up those errors are missing. I think they were not picked up from the db (master) during the upgrade Code: mysql> describe mail_user; +--------------------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+------------------+------+-----+---------------------+----------------+ | mailuser_id | int(11) unsigned | NO | PRI | NULL | auto_increment | | sys_userid | int(11) unsigned | NO | | 0 | | | sys_groupid | int(11) unsigned | NO | | 0 | | | sys_perm_user | varchar(5) | NO | | | | | sys_perm_group | varchar(5) | NO | | | | | sys_perm_other | varchar(5) | NO | | | | | server_id | int(11) unsigned | NO | MUL | 0 | | | email | varchar(255) | NO | MUL | | | | password | varchar(255) | NO | | | | | name | varchar(255) | NO | | | | | uid | int(11) unsigned | NO | | 5000 | | | gid | int(11) unsigned | NO | | 5000 | | | maildir | varchar(255) | NO | | | | | quota | bigint(20) | NO | | -1 | | | cc | varchar(255) | NO | | | | | homedir | varchar(255) | NO | | | | | autoresponder | enum('n','y') | NO | | n | | | autoresponder_start_date | datetime | NO | | 0000-00-00 00:00:00 | | | autoresponder_end_date | datetime | NO | | 0000-00-00 00:00:00 | | | autoresponder_subject | varchar(255) | NO | | Out of office reply | | | autoresponder_text | mediumtext | YES | | NULL | | | move_junk | enum('n','y') | NO | | n | | | custom_mailfilter | mediumtext | YES | | NULL | | | postfix | enum('n','y') | NO | | y | | | access | enum('n','y') | NO | | y | | | disableimap | enum('n','y') | NO | | n | | | disablepop3 | enum('n','y') | NO | | n | | | disabledeliver | enum('n','y') | NO | | n | | | disablesmtp | enum('n','y') | NO | | n | | | login | varchar(255) | NO | | | | | disablesieve | enum('n','y') | NO | | n | | | disablesieve-filter | enum('y','n') | NO | | n | | | disablelda | enum('n','y') | NO | | n | | | disablelmtp | enum('n','y') | NO | | n | | | disabledoveadm | enum('n','y') | NO | | n | | | last_quota_notification | date | YES | | NULL | | | backup_interval | varchar(255) | NO | | none | | | backup_copies | int(11) | NO | | 1 | | +--------------------------+------------------+------+-----+---------------------+----------------+ 38 rows in set (0.00 sec)
The database structure is nothing that gets transferred from master to slave, the structure gets only updated by the ISPConfig updater during update installation. In a multiserver setup, you must update ISPConfig on each node and all nodes must always run the same ISPConfig version. If you missed updating the slave nodes, then update them now to ensure they are consistent with the master.
Understood. I have run the update.php script several times from /tmp/ispconfig3_install/install/ after extracting from ISPConfig-3.1.15p3.tar.gz same version that was run in the master but still get the same errors
Comparing your table to the install/sql/ispconfig3.sql you are also missing the 'greylisting' column. All 3 missing columns are in the install/sql/incremental/upd_0081.sql file, and it makes me wonder if something went wrong with that update on your mail server; if so, you should have had this same problem for the last 4 years (when that file was added to an ISPConfig release). In any case, run the sql commands in there (eg. paste into mysql session on your mail server), and maybe hit all of upd_0082.sql - upd_0088.sql while you're at it. Or it might be quicker to just "update server set dbversion = 80" and run update.php again, to let the ISPConfig installer apply all the updates in sequence. (Note again, do this on your mail server, not the ispconfig master server.)
This is the first time we are running an upgrade of both OS and application from Ubuntu 14.04 to Ubuntu 18.04 and ISPConfig 3.0.5.4p9 to 3.1.15p3. We never run any patches/upgrades in between then. With your instructions I've managed to clear the lookup errors. I run the update.php script after running the incremental sql file from .80 to 88. I still see something off with mysql authentication. Code: root@mail:/var/log/mysql# tail -f error.log 2020-07-02T21:52:41.714801+03:00 1754 [Note] Aborted connection 1754 to db: 'dbispconfig' user: 'ispconfig' host: 'localhost' (Got an error reading communication packets) 2020-07-02T21:56:03.163059+03:00 1779 [Note] Aborted connection 1779 to db: 'dbispconfig' user: 'ispconfig' host: 'localhost' (Got an error reading communication packets) 2020-07-02T21:56:30.293907+03:00 1857 [Note] Access denied for user 'ispconfig'@'localhost' (using password: YES) mailq Code: BE83E5C90EC 32787 Thu Jul 2 17:43:29 MAILER-DAEMON (mail transport unavailable) [email protected] 48BA05C067B 30456 Thu Jul 2 21:02:12 [email protected] [email protected] 3D2405EFC7A 4096 Thu Jul 2 19:40:23 [email protected] (mail transport unavailable) [email protected] master.cf Code: # ========================================================================== smtp inet n - n - - smtpd #smtp inet n - y - 1 postscreen #smtpd pass - - y - - smtpd #dnsblog unix - - y - 0 dnsblog #tlsproxy unix - - y - 0 tlsproxy submission inet n - y - - smtpd -o syslog_name=postfix/submission -o smtpd_tls_security_level=encrypt -o smtpd_sasl_auth_enable=yes -o smtpd_client_restrictions=permit_sasl_authenticated,reject # -o smtpd_tls_auth_only=yes # -o smtpd_reject_unlisted_recipient=no # -o smtpd_client_restrictions=$mua_client_restrictions # -o smtpd_helo_restrictions=$mua_helo_restrictions # -o smtpd_sender_restrictions=$mua_sender_restrictions # -o smtpd_recipient_restrictions= # -o smtpd_relay_restrictions=permit_sasl_authenticated,reject # -o milter_macro_daemon_name=ORIGINATING smtps inet n - y - - smtpd -o syslog_name=postfix/smtps -o smtpd_tls_wrappermode=yes -o smtpd_sasl_auth_enable=yes -o smtpd_client_restrictions=permit_sasl_authenticated,reject [...] amavis unix - - - - 2 smtp -o smtp_data_done_timeout=1200 -o smtp_send_xforward_command=yes -o smtp_bind_address= 127.0.0.1:10025 inet n - n - - smtpd -o content_filter= -o local_recipient_maps= -o relay_recipient_maps= -o smtpd_restriction_classes= -o smtpd_client_restrictions= -o smtpd_helo_restrictions= -o smtpd_sender_restrictions= -o smtpd_recipient_restrictions=permit_mynetworks,reject -o mynetworks=127.0.0.0/8 -o strict_rfc821_envelopes=yes -o receive_override_options=no_unknown_recipient_checks,no_header_body_checks -o smtp_send_xforward_command=yes -o disable_dns_lookups=yes 127.0.0.1:10027 inet n - n - - smtpd -o content_filter= -o local_recipient_maps= -o relay_recipient_maps= -o smtpd_restriction_classes= -o smtpd_client_restrictions= -o smtpd_helo_restrictions= -o smtpd_sender_restrictions= -o smtpd_recipient_restrictions=permit_mynetworks,reject -o mynetworks=127.0.0.0/8 -o strict_rfc821_envelopes=yes -o receive_override_options=no_unknown_recipient_checks,no_header_body_checks -o smtp_send_xforward_command=yes -o milter_default_action=accept -o milter_macro_daemon_name=ORIGINATING -o disable_dns_lookups=yes /CODE] postconf -n [CODE]root@mail:/var/log/mysql# postconf -n alias_database = hash:/etc/aliases, hash:/var/lib/mailman/data/aliases alias_maps = hash:/etc/aliases, hash:/var/lib/mailman/data/aliases append_dot_mydomain = no biff = no body_checks = regexp:/etc/postfix/body_checks broken_sasl_auth_clients = yes compatibility_level = 2 content_filter = amavis:[127.0.0.1]:10024 dovecot_destination_recipient_limit = 1 greylisting = check_policy_service inet:127.0.0.1:10023 header_checks = regexp:/etc/postfix/header_checks html_directory = /usr/share/doc/postfix/html inet_interfaces = all inet_protocols = all mailbox_size_limit = 0 maildrop_destination_concurrency_limit = 1 maildrop_destination_recipient_limit = 1 message_size_limit = 900000000 mime_header_checks = regexp:/etc/postfix/mime_header_checks mydestination = mail.srv.domain.tld, localhost, localhost.localdomain myhostname = mail.srv.domain.tld mynetworks = 127.0.0.0/8 x.x.x.237/32 x.x.x.66/32 x.x.96.0/19 x.x.252.0/22 x.x.x.0/24 x.x.128.0/18 x.x.237.30/32 x.x.78.66/32 x.x.0.0/16 myorigin = /etc/mailname nested_header_checks = regexp:/etc/postfix/nested_header_checks owner_request_special = no proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $sender_bcc_maps $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $smtpd_sender_login_maps readme_directory = /usr/share/doc/postfix receive_override_options = no_address_mappings recipient_delimiter = + relay_domains = mysql:/etc/postfix/mysql-virtual_relaydomains.cf relay_recipient_maps = mysql:/etc/postfix/mysql-virtual_relayrecipientmaps.cf relayhost = sender_bcc_maps = proxy:mysql:/etc/postfix/mysql-virtual_outgoing_bcc.cf smtp_connect_timeout = 120s smtp_sasl_auth_enable = no smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd smtp_sasl_security_options = smtp_tls_exclude_ciphers = RC4, aNULL smtp_tls_note_starttls_offer = yes smtp_tls_protocols = !SSLv2,!SSLv3 smtp_tls_security_level = may smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu) smtpd_client_message_rate_limit = 100 smtpd_client_restrictions = check_client_access mysql:/etc/postfix/mysql-virtual_client.cf smtpd_error_sleep_time = 1s smtpd_hard_error_limit = 20 smtpd_helo_required = yes smtpd_helo_restrictions = permit_sasl_authenticated, permit_mynetworks, check_helo_access regexp:/etc/postfix/helo_access, reject_invalid_hostname, reject_non_fqdn_hostname, reject_invalid_helo_hostname, reject_unknown_helo_hostname, check_helo_access regexp:/etc/postfix/blacklist_helo smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination, check_recipient_access mysql:/etc/postfix/mysql-virtual_recipient.cf, check_recipient_access mysql:/etc/postfix/mysql-virtual_policy_greylist.cf smtpd_relay_restrictions = permit_mynetworks permit_sasl_authenticated defer_unauth_destination smtpd_restriction_classes = greylisting smtpd_sasl_auth_enable = yes smtpd_sasl_authenticated_header = yes smtpd_sasl_local_domain = smtpd_sasl_path = private/auth smtpd_sasl_security_options = noanonymous smtpd_sasl_type = dovecot smtpd_sender_login_maps = proxy:mysql:/etc/postfix/mysql-virtual_sender_login_maps.cf smtpd_sender_restrictions = check_sender_access regexp:/etc/postfix/tag_as_originating.re , permit_mynetworks, permit_sasl_authenticated, check_sender_access mysql:/etc/postfix/mysql-virtual_sender.cf, check_sender_access regexp:/etc/postfix/tag_as_foreign.re smtpd_soft_error_limit = 10 smtpd_tls_cert_file = /etc/postfix/smtpd.cert smtpd_tls_exclude_ciphers = RC4, aNULL smtpd_tls_key_file = /etc/postfix/smtpd.key smtpd_tls_loglevel = 1 smtpd_tls_mandatory_protocols = !SSLv2, !SSLv3 smtpd_tls_protocols = !SSLv2,!SSLv3 smtpd_tls_received_header = yes smtpd_tls_security_level = may smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache smtpd_use_tls = yes transport_maps = hash:/var/lib/mailman/data/transport-mailman, proxy:mysql:/etc/postfix/mysql-virtual_transports.cf virtual_alias_domains = virtual_alias_maps = hash:/var/lib/mailman/data/virtual-mailman, proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, proxy:mysql:/etc/postfix/mysql-virtual_email2email.cf virtual_gid_maps = mysql:/etc/postfix/mysql-virtual_gids.cf virtual_mailbox_base = /var/vmail virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf virtual_maps = hash:/var/lib/mailman/data/virtual-mailman virtual_transport = dovecot virtual_uid_maps = mysql:/etc/postfix/mysql-virtual_uids.cf
When you run update.php, say yes to letting it reconfigure database permissions and reconfigure services. Restart all services (or reboot the server if easier). If that was done and this still shows up, something didn't go right in the permissions/config update.php setup. You might run 'postfix check' and make sure it doesn't complain about things, then see if you have output from any of these: Code: postmap -q [email protected] mysql:/etc/postfix/mysql-virtual_transports.cf postmap -q domain.tld mysql:/etc/postfix/mysql-virtual_transports.cf postmap -q .domain.tld mysql:/etc/postfix/mysql-virtual_transports.cf postmap -q serviceaccount@ mysql:/etc/postfix/mysql-virtual_transports.cf The default transport should be smtp, which you have defined. Also, what is the line immediately preceding 'amavis' in master.cf? (if it happens to be a commented line ending in a backslash, add a new empty line before amavis)
Nevermind that, I'm mixing up a bug in the tcp wrapper config parser with postfix. But you might post the entire master.cf for good measure, there should be quite a bit of stuff in '[...]'
No, not really. I've updated my own mail systems from early 3.0 betas to the current 3.1. releases step by step (for about 10 years now) without any major issue, even converted them from courier to dovecot while being live.
