MySQL server has gone away

Discussion in 'General' started by savago, Jan 24, 2011.

  1. savago

    savago New Member

    Some errors:

    Debian lenny 2.6.26-2-686 with ISPconfig 3 CP
    ps aux| grep amavis
    root 16470 0.0 0.0 3120 716 pts/0 S+ 10:39 0:00 grep amavis
    amavis 23212 0.0 2.2 56520 46844 ? Ss Jan20 0:06 amavisd (master)
    amavis 23938 0.0 2.3 59180 49428 ? S Jan20 0:03 amavisd (ch8-avail)
    amavis 23939 0.0 2.3 58060 48264 ? S Jan20 0:03 amavisd (ch8-avail)
    server1:/var/log# ps aux| grep mysqld
    root 16472 0.0 0.0 3120 716 pts/0 S+ 10:39 0:00 grep mysqld
    root 22955 0.0 0.0 3892 1284 ? S Jan20 0:00 /bin/sh /usr/bin/mysqld_safe
    mysql 22992 0.0 0.9 131304 20548 ? Sl Jan20 3:09 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
    root 22993 0.0 0.0 3008 604 ? S Jan20 0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld




    amavis[23939]: (23939-07) NOTICE: reconnecting in response to: err=2006, HY000, DBD::mysql::st execute failed: MySQL server has gone away at (eval 86) line 166, <GEN114> line 5.

    ...

    amavis[2399]: (02399-02) NOTICE: reconnecting in response to: err=2006, HY000, DBD::mysql::st execute failed: MySQL server has gone away at (eval 86) line 166, <GEN48> line 3.
     
    Last edited: Jan 24, 2011
  2. till

    till Super Moderator Staff Member ISPConfig Developer

  3. savago

    savago New Member

    Not working by me.
    amavis[2633]: (02633-04) NOTICE: reconnecting in response to: err=2006, HY000, DBD::mysql::st execute failed: MySQL server has gone away at (eval 86) line 166, <GEN68> line 5.

    This is only testing server with only 1 domain 1/ftp/mail user acount.There is not any website with running mysql db.

    Intel(R) Atom(TM) CPU 330 @ 1.60GH / 2 GB RAM
     
    Last edited: Jan 25, 2011
  4. falko

    falko Super Moderator ISPConfig Developer

    What's the output of
    Code:
    netstat -tap
    ?
     
  5. savago

    savago New Member

    Code:
    netstat -tap
    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
    tcp        0      0 *:imaps                 *:*                     LISTEN      2860/dovecot    
    tcp        0      0 *:pop3s                 *:*                     LISTEN      2860/dovecot    
    tcp        0      0 localhost:10024         *:*                     LISTEN      2191/amavisd (maste
    tcp        0      0 localhost:10025         *:*                     LISTEN      2821/master     
    tcp        0      0 *:mysql                 *:*                     LISTEN      23483/mysqld    
    tcp        0      0 *:submission            *:*                     LISTEN      2821/master     
    tcp        0      0 *:46380                 *:*                     LISTEN      1913/rpc.statd  
    tcp        0      0 *:pop3                  *:*                     LISTEN      2860/dovecot    
    tcp        0      0 localhost:spamd         *:*                     LISTEN      4426/spamd.pid  
    tcp        0      0 *:imap2                 *:*                     LISTEN      2860/dovecot    
    tcp        0      0 *:sunrpc                *:*                     LISTEN      1897/portmap    
    tcp        0      0 *:ssmtp                 *:*                     LISTEN      2821/master     
    tcp        0      0 *:ftp                   *:*                     LISTEN      2835/pure-ftpd (SER     
    tcp        0      0 localhost:domain        *:*                     LISTEN      2149/named      
    tcp        0      0 *:ssh                   *:*                     LISTEN      2164/sshd       
    tcp        0      0 *:smtp                  *:*                     LISTEN      2821/master     
    tcp        0      0 localhost:953           *:*                     LISTEN      2149/named      
    tcp6       0      0 [::]:http-alt           [::]:*                  LISTEN      2924/apache2    
    tcp6       0      0 [::]:www                [::]:*                  LISTEN      2924/apache2    
    tcp6       0      0 [::]:tproxy             [::]:*                  LISTEN      2924/apache2    
    tcp6       0      0 [::]:ftp                [::]:*                  LISTEN      2835/pure-ftpd (SER
    tcp6       0      0 [::]:domain             [::]:*                  LISTEN      2149/named      
    tcp6       0      0 [::]:ssh                [::]:*                  LISTEN      2164/sshd       
    tcp6       0      0 localhost:953           [::]:*                  LISTEN      2149/named      
    tcp6       0      0 [::]:https              [::]:*                  LISTEN      2924/apache2    
    
    
     
  6. falko

    falko Super Moderator ISPConfig Developer

    Hm, MySQL is up and running. Can you post your amavisd configuration from /etc/amavis/conf.d/50-user?
     
  7. savago

    savago New Member

    cat 50-user
    use strict;

    #
    # Place your configuration directives here. They will override those in
    # earlier files.
    #
    # See /usr/share/doc/amavisd-new/ for documentation and examples of
    # the directives you can use in this file
    #

    @bypass_virus_checks_maps = (
    \%bypass_virus_checks, \@bypass_virus_checks_acl, \$bypass_virus_checks_re);

    @bypass_spam_checks_maps = (
    \%bypass_spam_checks, \@bypass_spam_checks_acl, \$bypass_spam_checks_re);

    #
    # Database connection settings
    #

    @lookup_sql_dsn =
    ( ['DBI:mysql:database=dbispconfig;host=127.0.0.1;port=3306', 'ispconfig', 'bafc7429bfff9adff285674e8a5abfe0'] );

    # @storage_sql_dsn = @lookup_sql_dsn; # none, same, or separate database
    #$sql_select_policy = 'SELECT "Y" as local FROM mail_domain WHERE CONCAT("@",domain) IN (%k)';
    # $banned_files_quarantine_method = 'sql';
    # $spam_quarantine_method = 'sql';

    #
    # SQL Select statements
    #

    $sql_select_policy =
    'SELECT *,spamfilter_users.id'.
    ' FROM spamfilter_users LEFT JOIN spamfilter_policy ON spamfilter_users.policy_id=spamfilter_policy.id'.
    ' WHERE spamfilter_users.email IN (%k) ORDER BY spamfilter_users.priority DESC';


    $sql_select_white_black_list = 'SELECT wb FROM spamfilter_wblist'.
    ' WHERE (spamfilter_wblist.rid=?) AND (spamfilter_wblist.email IN (%k))' .
    ' ORDER BY spamfilter_wblist.priority DESC';

    #
    # Quarantine settings
    #

    $final_virus_destiny = D_BOUNCE;
    $final_spam_destiny = D_DISCARD;
    $final_banned_destiny = D_BOUNCE;
    $final_bad_header_destiny = D_PASS;

    # Default settings, we st this very high to not filter aut emails accidently
    $sa_spam_subject_tag = '***SPAM*** ';
    $sa_tag_level_deflt = 20.0; # add spam info headers if at, or above that level
    $sa_tag2_level_deflt = 60.0; # add 'spam detected' headers at that level
    $sa_kill_level_deflt = 60.0; # triggers spam evasive actions
    $sa_dsn_cutoff_level = 100; # spam level beyond which a DSN is not sent

    #
    # Disable spam and virus notifications for the admin user.
    # Can be overridden by the policies in mysql
    #

    $virus_admin = undef;
    $spam_admin = undef;


    #
    # Enable Logging
    #

    $DO_SYSLOG = 1;
    $LOGFILE = "/var/log/amavis.log"; # (defaults to empty, no log)

    # Set the log_level to 5 for debugging
    $log_level = 0; # (defaults to 0)


    #------------ Do not modify anything below this line -------------
    1; # insure a defined return
    server1:/etc/amavis/conf.d#
     
  8. falko

    falko Super Moderator ISPConfig Developer

    You have "por t=3306", but it must be "port=3306" (without the space).
     
  9. savago

    savago New Member


    Sorry,this is error when paste:
    Code:
    @lookup_sql_dsn =
       ( ['DBI:mysql:database=dbispconfig;host=127.0.0.1;port=3306', 'ispconfig', 'bafc7429bfff9adff285674e8a5abfe0'] );
    
     
  10. falko

    falko Super Moderator ISPConfig Developer

    Are the MySQL details in that line ok (DB name, username, password)?
     
  11. savago

    savago New Member

    New one:
    Code:
    Feb  2 18:04:10 server1 amavis[19715]: (19715-03) (!)WARN: Using cpio instead of pax can be a security risk; please add:  $pax='pax';  to amavisd.conf and check that the pax(1) utility is available on the system!
    
    
    This is happen not regularly,you can see here:
    Code:
    Jan 30 23:41:20 server1 amavis[2632]: (02632-19) NOTICE: reconnecting in response to: err=2006, HY000, DBD::mysql::st execute failed: MySQL server has gone away at (eval 86) line 166, <GEN241> line 5.
    Jan 30 23:41:22 server1 amavis[2632]: (02632-19) Passed CLEAN, [94.156.xxx.yy] [94.156.1xx.yy] <smokeping@ns2.somesite.com> -> <nikolay@somesite.eu>, Message-ID: <20110130214120.7FCBB3A017A@server1.somesite.eu>, mail_id: rm8jz3t6xsCK, Hits: 0, size: 1354, queued_as: 374EB3A030B, 1681 ms
    Jan 31 03:00:07 server1 amavis[2633]: (02633-20) NOTICE: reconnecting in response to: err=2006, HY000, DBD::mysql::st execute failed: MySQL server has gone away at (eval 86) line 166, <GEN250> line 5.
    Jan 31 03:00:10 server1 amavis[2633]: (02633-20) Passed CLEAN, [94.156.xxx.yy] [94.156.xxx.yy] <root@ns2.somesite.com> -> <nikolay@somesite.eu>, Message-ID: <E1Pji7h-0002jX-Hs@ns2.somesite.com>, mail_id: gyuJ81R101hZ, Hits: 0.212, size: 5983, queued_as: 0D9DF3A030E, 2278 ms
    
    


    And normal :
    Code:
    Feb  2 16:02:06 server1 postfix/smtpd[20094]: setting up TLS connection from mh1.hostmu.com[78.46.230.211]
    Feb  2 16:02:06 server1 postfix/smtpd[20094]: Anonymous TLS connection established from mh1.hostmu.com[78.46.230.211]: TLSv1 with cipher ADH-AES256-SHA (256/256 bits)
    Feb  2 16:02:06 server1 postfix/smtpd[20094]: F27A63A030D: client=mh1.hostmu.com[78.46.230.211]
    Feb  2 16:02:07 server1 postfix/cleanup[20098]: F27A63A030D: message-id=<20110202140223.ce5ef21ca504@www.howtoforge.com>
    Feb  2 16:02:07 server1 postfix/qmgr[2829]: F27A63A030D: from=<www-data@http4.howtoforge.com>, size=2227, nrcpt=1 (queue active)
    Feb  2 16:02:07 server1 postfix/smtpd[20094]: disconnect from mh1.hostmu.com[78.46.230.211]
    Feb  2 16:02:08 server1 postfix/smtpd[20102]: connect from localhost[127.0.0.1]
    Feb  2 16:02:08 server1 postfix/smtpd[20102]: B94463A0310: client=localhost[127.0.0.1]
    Feb  2 16:02:08 server1 postfix/cleanup[20098]: B94463A0310: message-id=<20110202140223.ce5ef21ca504@www.howtoforge.com>
    Feb  2 16:02:08 server1 postfix/smtpd[20102]: disconnect from localhost[127.0.0.1]
    Feb  2 16:02:08 server1 postfix/qmgr[2829]: B94463A0310: from=<www-data@http4.howtoforge.com>, size=2698, nrcpt=1 (queue active)
    Feb  2 16:02:08 server1 amavis[3866]: (03866-20) Passed CLEAN, [78.46.230.211] [188.40.103.101] <www-data@http4.howtoforge.com> -> <nikolay@somesite.eu>, Message-ID: <20110202140223.ce5ef21ca504@www.howtoforge.com>, mail_id: vU9doEvwqBT5, Hits: 0, size: 2225, queued_as: B94463A0310, 1724 ms
    Feb  2 16:02:08 server1 postfix/smtp[20099]: F27A63A030D: to=<nikolay@somesite.eu>, relay=127.0.0.1[127.0.0.1]:10024, delay=1.8, delays=0.08/0.03/0.01/1.7, dsn=2.0.0, status=sent (250 2.0.0 Ok, id=03866-20, from MTA([127.0.0.1]:10025): 250 2.0.0 Ok: queued as B94463A0310)
    Feb  2 16:02:08 server1 postfix/qmgr[2829]: F27A63A030D: removed
    Feb  2 16:02:08 server1 dovecot: auth-worker(default): mysql: Connected to localhost (dbispconfig)
    Feb  2 16:02:08 server1 deliver(nikolay@svilengrad.eu): msgid=<20110202140223.ce5ef21ca504@www.howtoforge.com>: saved mail to INBOX
    Feb  2 16:02:08 server1 postfix/pipe[20104]: B94463A0310: to=<nikolay@somesite.eu>, relay=dovecot, delay=0.08, delays=0.01/0.02/0/0.05, dsn=2.0.0, status=sent (delivered via dovecot service)
    Feb  2 16:02:08 server1 postfix/qmgr[2829]: B94463A0310: removed
    Feb  2 16:05:01 server1 dovecot: pop3-login: Disconnected: rip=127.0.0.1, lip=127.0.0.1, secured
    Feb  2 16:05:01 server1 dovecot: imap-login: Disconnected: rip=127.0.0.1, lip=127.0.0.1, secured
    Feb  2 16:05:01 server1 postfix/smtpd[20148]: connect from localhost[127.0.0.1]
    Feb  2 16:05:01 server1 postfix/smtpd[20148]: lost connection after CONNECT from localhost[127.0.0.1]
    Feb  2 16:05:01 server1 postfix/smtpd[20148]: disconnect from localhost[127.0.0.1]
    Feb  2 16:05:27 server1 postfix/anvil[20096]: statistics: max connection rate 1/60s for (smtp:78.46.230.211) at Feb  2 16:02:06
    Feb  2 16:05:27 server1 postfix/anvil[20096]: statistics: max connection count 1 for (smtp:78.46.230.211) at Feb  2 16:02:06
    Feb  2 16:05:27 server1 postfix/anvil[20096]: statistics: max cache size 1 at Feb  2 16:02:06
    
    
     
    Last edited: Feb 2, 2011
  12. falko

    falko Super Moderator ISPConfig Developer

    Please open /etc/amavis/conf.d/50-user and add
    Code:
    $pax='pax';
    in the middle.
     
  13. Ovidiu

    Ovidiu Active Member

    $pax='pax';

    solved the same problem for me but what exactly does that directive do?
     
  14. falko

    falko Super Moderator ISPConfig Developer

  15. Ovidiu

    Ovidiu Active Member

    still getting this occasionally, kinda a few times a day:

    Mysql is running, username and password are ok since it is only occasionally not working. Oh and I haven't hit the limits of mysql I have munin running here: http://zice.ro/munin/serverkompetenz.net/h1870666.serverkompetenz.net/index.html

    any other hints?
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    Do you have any "real" problems like non delivered emails? As far as I see, this message indicates just that amavisd is doing a reconnect to mysql which is a normal action, it has the level "NOTICE" so this indicates not a problem.
     
  17. Ovidiu

    Ovidiu Active Member

    nope, no real problems, at least I haven't noticed any yet.

    was just making sure everything runs smoothly. mysql timeouts are ok, never had any problems with websites accessing mysql.

    I guess I can leave it like this then. will amavisd report "real" problems into the same log file though?
     
  18. till

    till Super Moderator Staff Member ISPConfig Developer

    All reports should be in that log as far as I know, but the level should be Different from NOTICE.
     
  19. Ovidiu

    Ovidiu Active Member

    great, I checked that file and "grepped" for amavis and those were the only lines popping up. I'll rest my case then and leave it as is.

    thanks Till
    Ovidiu
     

Share This Page