MySQL/MariaDB mail-connection problem

Discussion in 'Installation/Configuration' started by muelli75, Jan 12, 2020.

  1. muelli75

    muelli75 New Member

    hello out there!

    We run a debian-server (buster) and ispconfig 3.1.15p2 since month without any problems.
    Since a few days, the mailservice (dovecot-core:amd64/buster 1:2.3.4.1-5+deb10u1) didnt accept conncections for some time.

    After minutes without any action, the service runs well again.
    I think, its on mariadb, because the logs show

    Code:
    SASL LOGIN authentication failed: Connection lost to authentication server
    Here a some lines of the logs while the service is down:

    /var/log/syslog:
    Code:
    date 23:11:35 monarch postfix/smtpd[30150]: connect from unknown[123.31.43.16]
    date 23:11:36 monarch dovecot: master: Warning: Killed with signal 15 (by pid=30165 uid=0 code=kill)
    date 23:11:36 monarch dovecot: imap([email protected])<30094><TrRNR7ybXalbjQBX>: Server shutting down. in=123 out=10916 deleted=0 expunged=0 trashed=0 hdr_count=0 hdr_bytes=0 body_count=0 body_bytes=0
    date 23:11:36 monarch dovecot: imap([email protected])<29851><NwhVQ7ybtdlYdIae>: Server shutting down. in=182 out=2285 deleted=0 expunged=0 trashed=0 hdr_count=0 hdr_bytes=0 body_count=0 body_bytes=0
    date 23:11:36 monarch dovecot: log(29700): Warning: Killed with signal 15 (by pid=1 uid=0 code=kill)
    date 23:11:36 monarch postfix/smtpd[27427]: warning: unknown[123.31.43.15]: SASL LOGIN authentication failed: UGFzc3dvcmQ6
    date 23:11:36 monarch postfix/smtpd[30150]: warning: unknown[123.31.43.16]: SASL LOGIN authentication failed: Connection lost to authentication server
    date 23:11:36 monarch postfix/smtpd[30147]: connect from unknown[123.31.43.15]
    date 23:11:37 monarch postfix/smtpd[30150]: NOQUEUE: filter: RCPT from static.232.255.47.78.clients.your-server.de[78.47.255.232]: <[email protected]>: Sender address triggers FILTER amavis:[127.0.0.1]:10024; from=<[email protected]> to=<[email protected]> proto=SMTP helo=<example.com>
    date 23:11:37 monarch postfix/smtpd[30150]: warning: 232.255.47.78.zen.spamhaus.org: RBL lookup error: Host or domain name not found. Name service error for name=232.255.47.78.zen.spamhaus.org type=A: Host not found, try again
    date 23:11:39 monarch postfix/proxymap[30081]: warning: mysql:/etc/postfix/mysql-virtual_domains.cf: query failed: Connection was killed
    date 23:11:39 monarch postfix/trivial-rewrite[27348]: warning: virtual_mailbox_domains: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf: table lookup problem
    date 23:11:39 monarch postfix/trivial-rewrite[27348]: warning: virtual_mailbox_domains lookup failure
    date 23:11:39 monarch postfix/smtpd[27427]: warning: connect to mysql server 127.0.0.1: Can't connect to MySQL server on '127.0.0.1' (115)
    date 23:11:39 monarch postfix/smtpd[27427]: warning: mysql:/etc/postfix/mysql-virtual_client.cf lookup error for "unknown"
    date 23:11:39 monarch postfix/smtpd[27427]: NOQUEUE: reject: RCPT from unknown[183.166.98.84]: 451 4.3.5 <unknown[183.166.98.84]>: Client host rejected: Server configuration error; from=<[email protected]> to=<[email protected]> proto=SMTP helo=<vps26525.com>
    date 23:11:39 monarch postfix/smtpd[30147]: warning: unknown[123.31.43.15]: SASL LOGIN authentication failed: UGFzc3dvcmQ6
    date 23:11:53 monarch postfix/smtpd[30150]: warning: hostname host86.at-sib.ru does not resolve to address 80.66.81.86: Name or service not known
    date 23:11:53 monarch postfix/smtpd[30150]: connect from unknown[80.66.81.86]
    date 23:12:01 monarch dovecot: auth-worker(30176): Warning: sqlpool(mysql): Query failed, retrying: MySQL server has gone away (idled for 24 secs)
    date 23:12:01 monarch dovecot: imap-login: Login: user=<[email protected]>, method=PLAIN, rip=88.116.134.158, lip=136.243.9.172, mpid=31330, session=<Nz5lTLybttlYdIae>
    date 23:12:05 monarch postfix/smtpd[30150]: warning: unknown[80.66.81.86]: SASL LOGIN authentication failed: UGFzc3dvcmQ6
    
    /var/log/mail-log
    Code:
    date 16:38:21 monarch amavis[20338]: (20338-16) (!!)collect_results from [23748] (/usr/bin/ripole): exit 102 File '/var/lib/amavis/tmp/amavis-20200109T161614-20338-owarXq2Y/parts/p002' is not OLE2 format\n
    Jan 10 09:15:01 monarch amavis[22783]: (22783-02) (!!)TROUBLE in process_request: sql exec: err=1927, 70100, DBD::mysql::st execute failed: Connection was killed at (eval 109) line 173, <GEN37> line 3521. at (eval 111) line 333, <GEN37> line 3521.
    Jan 10 09:52:22 monarch amavis[28205]: (28205-08) (!!)collect_results from [30693] (/usr/bin/ripole): exit 102 File '/var/lib/amavis/tmp/amavis-20200110T094003-28205-EmDhyGiK/parts/p003' is not OLE2 format\n
    Here is the log of /var/log/mysql/error.log bevor and after we restart the mariadb-server manually
    Code:
    date 23:14:05 1070 [Warning] InnoDB: Cannot add field `besucherbilder` in table `c15_XXXXXX`.`messedetails` because after adding it, the row size is 8509 which is greater than maximum allowed size (8126) for a record on index leaf page.
    date 23:15:02 1109 [Warning] InnoDB: Cannot add field `metadata` in table `c11_joomla`.`jos_content` because after adding it, the row size is 8789 which is greater than maximum allowed size (8126) for a record on index leaf page.
    date 23:15:02 1109 [Warning] InnoDB: Cannot add field `post_source` in table `c21_wpdb`.`wp_ff_posts` because after adding it, the row size is 8355 which is greater than maximum allowed size (8126) for a record on index leaf page.
    date 23:15:02 1109 [Warning] InnoDB: Cannot add field `profile_10` in table `c21_wpdb`.`wp_newsletter` because after adding it, the row size is 8803 which is greater than maximum allowed size (8126) for a record on index leaf page.
    date 23:15:30 0 [Note] /usr/sbin/mysqld (initiated by: unknown): Normal shutdown
    date 23:15:30 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
    date 23:15:30 0 [Note] InnoDB: Instance 0, restricted to 2048 pages due to innodb_buf_pool_dump_pct=25
    date 23:15:30 0 [Note] InnoDB: Buffer pool(s) dump completed at 200109 23:15:30
    date 23:15:31 0 [Note] InnoDB: Shutdown completed; log sequence number 74329899348; transaction id 607435078
    date 23:15:31 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
    date 23:15:31 0 [Note] /usr/sbin/mysqld: Shutdown complete
    
    date 23:15:31 0 [Note] CONNECT: Version 1.06.0010 June 01, 2019
    date 23:15:31 0 [Note] InnoDB: Using Linux native AIO
    date 23:15:31 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    date 23:15:31 0 [Note] InnoDB: Uses event mutexes
    date 23:15:31 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
    date 23:15:31 0 [Note] InnoDB: Number of pools: 1
    date 23:15:31 0 [Note] InnoDB: Using SSE2 crc32 instructions
    date 23:15:31 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
    date 23:15:31 0 [Note] InnoDB: Completed initialization of buffer pool
    date 23:15:31 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    date 23:15:32 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
    date 23:15:32 0 [Note] InnoDB: Creating shared tablespace for temporary tables
    date 23:15:32 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    date 23:15:32 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
    date 23:15:32 0 [Note] InnoDB: Waiting for purge to start
    date 23:15:32 0 [Note] InnoDB: 10.3.18 started; log sequence number 74329899348; transaction id 607435080
    date 23:15:32 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
    date 23:15:32 0 [Note] Plugin 'FEEDBACK' is disabled.
    date 23:15:32 0 [Note] InnoDB: Buffer pool(s) load completed at 200109 23:15:32
    date 23:15:32 0 [Note] Server socket created on IP: '::'.
    date 23:15:32 0 [Note] Reading of all Master_info entries succeeded
    date 23:15:32 0 [Note] Added new Master_info '' to hash table
    date 23:15:32 0 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '10.3.18-MariaDB-0+deb10u1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Debian 10
    and, at last the netstat-output while the dovecot didnt accept connections:
    Code:
    [email protected] ~ # netstat |grep mysql
    tcp        0      0 localhost.localdo:48380 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48300 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48428 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48298 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48302 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48426 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48378 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48296 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:47986 localhost.localdo:mysql ESTABLISHED
    tcp        0      0 localhost.localdo:48000 localhost.localdo:mysql ESTABLISHED
    tcp        0      0 localhost.localdo:48294 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48308 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48418 localhost.localdo:mysql ESTABLISHED
    tcp        0      0 localhost.localdo:48422 localhost.localdo:mysql TIME_WAIT 
    tcp        0      0 localhost.localdo:48424 localhost.localdo:mysql TIME_WAIT 
    tcp6       0      0 localhost.localdo:mysql localhost.localdo:47986 ESTABLISHED
    tcp6       0      0 localhost.localdo:mysql localhost.localdo:48418 ESTABLISHED
    tcp6       0      0 localhost.localdo:mysql localhost.localdo:48000 ESTABLISHED
    unix  3      [ ]         STREAM     CONNECTED     92305016 /var/run/mysqld/mysqld.sock
    Cant find tips relating to the mariadb-error
    Code:
    InnoDB: Cannot add field `core_metakey` in table `c17XXXdb01`.`nsync_ucm_content` because after adding it, the row size is 8811 which is greater than maximum allowed size (8126) for a record on index leaf page.
    Is anybody out there who can show me the right path to solve this problem?

    Thank you in advance!

    Martin
     
  2. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    Have you checked all services are running continuously? Maybe you get out of memory errors? See what gets written in /var/log/syslog at the time you have those problems. If nothing useful there, check all logs that get written at the time when problem occurs. Use ls -lht /var/log to see which log files are the latest. Some log files are in subdirectories of /var/log/.
     
  3. Jesse Norell

    Jesse Norell Well-Known Member

    A google search with your specific table name removed finds useful info:
    Code:
    nnoDB: Cannot add field `core_metakey` in table .`nsync_ucm_content` because after adding it, the row size is 8811 which is greater than maximum allowed size (8126) for a record on index leaf page.
    Including this one which looks promising: https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/
     
  4. muelli75

    muelli75 New Member

    First, thank you for your replys!

    @Jesse Norell: your most promising link looks very interesting! I will investigate ...

    @Taleman: no out of memory, we run 32GB of physically ram, 3 to 3.5 GB are used

    But! checked which log was last written, and so ..... fail2ban was written as last. OK, our IP is banned for somehow.
    Code:
    2020-01-13 19:35:48,175 fail2ban.actions        [11733]: NOTICE  [dovecot] Ban 8X.X5.2X6.XX
    
    Have now to check on which client runs a bad login ...

    Thanks again!

    Martin
     

Share This Page