Postfix issue upon update of MariaDB from ^5 to ^10

Discussion in 'General' started by Alex Mamatuik, Mar 15, 2023.

  1. Alex Mamatuik

    Alex Mamatuik Member

    At least, new versions of Drupal (d9, d10) require
    • MariaDB 10.3.7+,
    but when OS centos 7 is used, the default MariaDB - 5.5.68.

    To upgrade for a newer one, the former databases' server must be wiped out:
    yum remove mariadb-server mariadb mariadb-libs
    Upon removal, all the dependencies are also being erased, amongst of:
    • dovecot-mysql
    • perl-DBD-MySQL
    • postfix
    • postgrey
    • pure-ftpd

    And a re-enable of them doesn't allow to operate Roundcube properly.

    Just a fresh installation:
    yum install postfix
    systemctl enable postfix.service
    systemctl restart postfix.service
    yum  install dovecot dovecot-mysql dovecot-pigeonhole
    yum install pure-ftpd
    systemctl enable pure-ftpd.service
    systemctl start pure-ftpd.service
     yum install perl-DBD-mysql postgrey
    brings SMTP (server authentication) Error 250, when trying to send an e-mail through the Roundcube.

    Having added previously configured backup-snippets to (/etc/postfix)
    amavis unix - - - - 4 smtp
    -o smtp_data_done_timeout=1200
    -o smtp_send_xforward_command=yes
    -o smtp_bind_address= 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 smtpd_end_of_data_restrictions=
    -o mynetworks=
    -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
    -o address_verify_virtual_transport=$virtual_transport
    -o address_verify_transport_maps=$transport_maps 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 smtpd_end_of_data_restrictions=
    -o mynetworks=
    -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
    -o address_verify_virtual_transport=$virtual_transport
    -o address_verify_transport_maps=$transport_maps
    -o milter_default_action=accept
    -o milter_macro_daemon_name=ORIGINATING
    virtual_alias_domains = proxy:mysql:/etc/postfix/
    virtual_alias_maps = hash:/var/lib/mailman/data/virtual-mailman, proxy:mysql:/etc/postfix/, proxy:mysql:/etc/postfix/, proxy:mysql:/etc/postfix/
    virtual_mailbox_domains = proxy:mysql:/etc/postfix/
    virtual_mailbox_maps = proxy:mysql:/etc/postfix/
    virtual_mailbox_base = /var/vmail
    virtual_uid_maps = proxy:mysql:/etc/postfix/
    virtual_gid_maps = proxy:mysql:/etc/postfix/
    sender_bcc_maps = proxy:mysql:/etc/postfix/
    smtpd_sasl_auth_enable = yes
    broken_sasl_auth_clients = yes
    smtpd_sasl_authenticated_header = yes
    smtpd_restriction_classes = greylisting
    greylisting = check_policy_service inet:
    smtpd_recipient_restrictions = permit_mynetworks, reject_unknown_recipient_domain, reject_unlisted_recipient, check_recipient_access proxy:mysql:/etc/postfix/, permit_sasl_authenticated, reject_non_fqdn_recipient, reject_unauth_destination, check_recipient_access proxy:mysql:/etc/postfix/, check_recipient_access mysql:/etc/postfix/, check_policy_service unix:private/quota-status
    smtpd_use_tls = yes
    smtpd_tls_security_level = may
    smtpd_tls_cert_file = /etc/postfix/smtpd.cert
    smtpd_tls_key_file = /etc/postfix/smtpd.key
    transport_maps = hash:/var/lib/mailman/data/transport-mailman, proxy:mysql:/etc/postfix/
    relay_domains = proxy:mysql:/etc/postfix/
    relay_recipient_maps = proxy:mysql:/etc/postfix/
    smtpd_sender_login_maps = proxy:mysql:/etc/postfix/
    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 $virtual_uid_maps $virtual_gid_maps $smtpd_client_restrictions $smtpd_sender_restrictions $smtpd_recipient_restrictions $smtp_sasl_password_maps $sender_dependent_relayhost_maps
    smtpd_helo_required = yes
    smtpd_helo_restrictions = permit_mynetworks, check_helo_access regexp:/etc/postfix/helo_access, permit_sasl_authenticated, reject_invalid_helo_hostname, reject_non_fqdn_helo_hostname, check_helo_access regexp:/etc/postfix/blacklist_helo, ,reject_unknown_helo_hostname, permit
    smtpd_sender_restrictions = check_sender_access proxy:mysql:/etc/postfix/, check_sender_access regexp:/etc/postfix/, permit_mynetworks, permit_sasl_authenticated, reject_non_fqdn_sender, reject_unlisted_sender, check_sender_access regexp:/etc/postfix/
    smtpd_reject_unlisted_sender = no
    smtpd_client_restrictions = check_client_access proxy:mysql:/etc/postfix/, permit_inet_interfaces, permit_mynetworks, permit_sasl_authenticated, reject_rbl_client, reject_unauth_pipelining , permit
    smtpd_etrn_restrictions = permit_mynetworks, reject
    smtpd_data_restrictions = permit_mynetworks, reject_unauth_pipelining, reject_multi_recipient_bounce, permit
    smtpd_client_message_rate_limit = 100
    maildrop_destination_concurrency_limit = 1
    maildrop_destination_recipient_limit = 1
    virtual_transport = maildrop
    header_checks = regexp:/etc/postfix/header_checks
    mime_header_checks = regexp:/etc/postfix/mime_header_checks
    nested_header_checks = regexp:/etc/postfix/nested_header_checks
    body_checks = regexp:/etc/postfix/body_checks
    owner_request_special = no
    smtp_tls_security_level = may
    smtpd_tls_mandatory_protocols = !SSLv2, !SSLv3
    smtpd_tls_protocols = !SSLv2,!SSLv3
    smtp_tls_protocols = !SSLv2,!SSLv3
    smtpd_tls_exclude_ciphers = RC4, aNULL
    smtp_tls_exclude_ciphers = RC4, aNULL
    smtpd_tls_mandatory_ciphers = medium
    tls_preempt_cipherlist = yes
    address_verify_negative_refresh_time = 60s
    enable_original_recipient = yes
    sender_dependent_relayhost_maps = proxy:mysql:/etc/postfix/
    smtp_sasl_password_maps = proxy:mysql:/etc/postfix/, texthash:/etc/postfix/sasl_passwd
    smtp_sender_dependent_authentication = yes
    smtp_sasl_auth_enable = yes
    smtp_sasl_security_options = noanonymous, noplaintext
    smtp_sasl_tls_security_options = noanonymous
    smtpd_forbidden_commands = CONNECT,GET,POST,USER,PASS
    smtpd_relay_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
    address_verify_sender_ttl = 15686s
    myhostname = <>
    mynetworks = [::1]/128
    dovecot_destination_recipient_limit = 1
    smtpd_sasl_type = dovecot
    smtpd_sasl_path = private/auth
    content_filter = amavis:[]:10024
    receive_override_options = no_address_mappings
    mailbox_size_limit = 0
    message_size_limit = 0
    authorized_flush_users =
    authorized_mailq_users = nagios, icinga
    we eliminate SMTP 250 error, but... neither 'delivery report', nor received mails is showed in the INBOX folder.

    And only upon ispconfig upgrade:,
    when services are reconfigured
    Reconfigure Services? (yes,no,selected) [yes]:
    Configuring Postfix
    Configuring Dovecot
    Configuring Pureftpd
    Configuring Database
    the RoundCube resumes its normal operations.

    Hope, the pundits and other dexterous (in the finest sense of this word) members will help me to understand of what was the mistake.

    Moreover, if we look at the previous tutorials, we can see, that undermentioned strings are necessarily uncommented:
    1) /etc/postfix/
    submission inet n - n - - 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 milter_macro_daemon_name=ORIGINATING

    2) /etc/postfix/
    myhostname =
    mydomain =
    myorigin = $myhostname
    inet_interfaces = all
    inet_interfaces = localhost
    inet_protocols = all
    mydestination = $myhostname, localhost.$mydomain, localhost
    home_mailbox = Maildir/
    while adding the new lines to the end of file:
    smtpd_tls_key_file = /etc/postfix/ssl/mailserver.key
    smtpd_tls_cert_file = /etc/postfix/ssl/mailserver.crt
    smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
    smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
    Current newest versions of the ISPConfig installer have automated scripts, serving these purposes.
  2. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

    So far that I know, ISPConfig only supports MariaDB version that is shipped with the server OS. Other than that, a lot tried and mainly failed.
  3. Alex Mamatuik

    Alex Mamatuik Member

    Necessary to say, that i was executing MariaDB update incorrectly:
    yum remove mariadb-server mariadb mariadb-libs
    yum clean all
    yum install MariaDB-server galera-4 MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common 
    It leads to an undesirable removal of all related services (postfix, postgrey, dovecot, pure-ftpd).

    A good practice to execute the update smoothly: yum upgrade maria*
    --> Running transaction check
    ---> Package MariaDB-client.x86_64 0:10.3.38-1.el7.centos will be obsoleting
    --> Processing Dependency: MariaDB-common for package: MariaDB-client-10.3.38-1.el7.centos.x86_64
    ---> Package MariaDB-compat.x86_64 0:10.3.38-1.el7.centos will be obsoleting
    ---> Package MariaDB-devel.x86_64 0:10.3.38-1.el7.centos will be obsoleting
    --> Processing Dependency: MariaDB-shared >= 10.2.42 for package: MariaDB-devel-10.3.38-1.el7.centos.x86_64
    ---> Package MariaDB-server.x86_64 0:10.3.38-1.el7.centos will be obsoleting
    --> Processing Dependency: galera for package: MariaDB-server-10.3.38-1.el7.centos.x86_64
    ---> Package mariadb.x86_64 1:5.5.68-1.el7 will be obsoleted
    ---> Package mariadb-devel.x86_64 1:5.5.68-1.el7 will be obsoleted
    ---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be obsoleted
    ---> Package mariadb-server.x86_64 1:5.5.68-1.el7 will be obsoleted
    --> Running transaction check
    ---> Package MariaDB-common.x86_64 0:10.3.38-1.el7.centos will be installed
    ---> Package MariaDB-shared.x86_64 0:10.3.38-1.el7.centos will be installed
    ---> Package galera.x86_64 0:25.3.37-1.el7.centos will be installed

    Dependencies Resolved

    Package Arch Version Repository Size
    MariaDB-client x86_64 10.3.38-1.el7.centos mariadb 11 M
    replacing mariadb.x86_64 1:5.5.68-1.el7
    MariaDB-compat x86_64 10.3.38-1.el7.centos mariadb 2.2 M
    replacing mariadb-libs.x86_64 1:5.5.68-1.el7
    MariaDB-devel x86_64 10.3.38-1.el7.centos mariadb 7.2 M
    replacing mariadb-devel.x86_64 1:5.5.68-1.el7
    MariaDB-server x86_64 10.3.38-1.el7.centos mariadb 25 M
    replacing mariadb-server.x86_64 1:5.5.68-1.el7
    Installing for dependencies:
    MariaDB-common x86_64 10.3.38-1.el7.centos mariadb 82 k
    MariaDB-shared x86_64 10.3.38-1.el7.centos mariadb 113 k
    galera x86_64 25.3.37-1.el7.centos mariadb 8.1 M

    To examine all tables in all MariaDB databases for incompatibilities with the currently installed version of the MariaDB server: the mysql_upgrade command is performed.
    mysql_upgrade - u <user_name> -p

    However, i still do not understand, which configuration snippet for postfix (or for something else) was lost in the very first coarse update approach.

    Anyway, updating MariaDB from 5.5.68 to 10.3.38 doesn't affect to workability of websites, ISP Config control panel and so on.

    Tips to check mysql version:
    mysql -V
    mysql --version
    mysql - u <user_name> -p
        select @@version;
        SHOW VARIABLES LIKE 'version';
    + through phpMyAdmin "Database server" section.

    Full list of commands to execute update from BASH:
    • mysqldump -u root -p --all-databases > /tmp/all-database.sql
    • (just in case) cp -a /etc/my.cnf /etc/my.cnf-$(date +%F).bak
    • yum update
    • tee /etc/yum.repos.d/MariaDB.repo<<EOF
      name = MariaDB
      baseurl =
      gpgkey =
      gpgcheck = 1
    • yum upgrade maria*
    • systemctl restart mariadb
    • nano /etc/my.cnf
      comment-out some obstructive variables like #innodb_additional_mem_pool_size = 16106127
    • systemctl restart mariadb
    • systemctl status mariadb
    • systemctl enable mariadb
    • mysql --version
    • mysql_upgrade -u root -p
    ahrasis likes this.
  4. Alex Mamatuik

    Alex Mamatuik Member

    Some working configuration of /etc/my.cnf for MySQL MariaDB10 would be:
    # This group is read both by the client and the server
    # use it for options that affect everything
    # Disabling symbolic-links is recommended to prevent assorted security risks
    # Settings user and group are ignored when systemd is used.
    # If you need to run mysqld under a different user or group,
    # customize your systemd unit file for mariadb according to the
    # instructions in
    open_files_limit = 65535
    #Configure tmp_table_size and max_heap_table_size
    #Both directives should have the same size and will help you prevent disk writes. The tmp_table_siz$
    max_heap_table_size = 16M
    tmp_table_size = 16M
    #max_connections = 100
    max_connections = 150
    #thread_cache_size = 50
    #thread_cache_size = 26
    thread_cache_size = 50
    thread_stack = 192K
    #Set the query cache type. Possible options are as follows:
    #0 : Don't cache results in or retrieve results from the query cache.
    #1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
    #2 : Cache results only for queries that begin with SELECT SQL_CACHE
    # query_cache_size = 16M
    query_cache_type = 1
    query_cache_size = 16M
    query_cache_limit = 4M
    #query_cache_min_res_unit = 2k
    query_cache_min_res_unit = 4k
    # CALCULATIONS: 1048576 = 1M
    key_buffer_size = 26M
    read_buffer_size = 64k
    read_rnd_buffer_size = 64k
    sort_buffer_size = 1M
    join_buffer_size = 2M
    #interactive_timeout = 60
    #wait_timeout = 60
    #connect_timeout = 60
    #interactive_timeout = 300
    interactive_timeout = 50
    wait_timeout = 1800
    connect_timeout = 20
    #Enabling MySQL Slow query Logs
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql-slow.log
    long_query_time = 1
    #skip-character-set-client-handshake !!! STOPS SEND-MAIL PROCESS
    #skip-name-resolve=1  !!! STOPS SEND-MAIL PROCESS
    # Skip reverse DNS lookup of clients
    #skip-name-resolve {with opened doesn't allow sending}
    #innodb_buffer_pool_size = 378435456
    innodb_buffer_pool_size = 128M
    #innodb_additional_mem_pool_size = 16106127 !!!doesn't allow to restart mariadb
    myisam_sort_buffer_size = 64M
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #key_buffer = 36M
    key_buffer = 36M
    #InnoDB Multi-byte UTF-8 support
    #default-character-set = utf8mb4
    open_files_limit = 65535
    # include all files from the config directory
    # include *.cnf from the config directory
    !includedir /etc/my.cnf.d
    ahrasis likes this.

Share This Page