MySQL disconnection problem

[jrcmilanez]

Hello friends, how are you?

After looking at the phpmyadmin advisor tool, I found a lot of aborted connections.

Googling about it I found this article talking about this error:

http://sureshkuna.blogspot.in/2010/12/a ... db-ms.html

After configured mysql logs to track this error, I'm getting the log below. Look likes otrs is not closing the mysql connections correctly, and mysql close them after some minutes of inactivity.

Code: Select all

140130  0:00:53 [Warning] Aborted connection 714528 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:04:55 [Warning] Aborted connection 714726 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:10:58 [Warning] Aborted connection 714295 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:15:00 [Warning] Aborted connection 715315 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:19:02 [Warning] Aborted connection 714966 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:21:03 [Warning] Aborted connection 715715 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:29:07 [Warning] Aborted connection 715441 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:31:08 [Warning] Aborted connection 715875 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:39:12 [Warning] Aborted connection 716842 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:41:13 [Warning] Aborted connection 711584 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:49:17 [Warning] Aborted connection 716004 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:53:19 [Warning] Aborted connection 716734 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:55:20 [Warning] Aborted connection 716510 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  0:57:21 [Warning] Aborted connection 717870 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:05:25 [Warning] Aborted connection 718308 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:09:27 [Warning] Aborted connection 718547 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:11:28 [Warning] Aborted connection 717177 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:15:30 [Warning] Aborted connection 718863 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:23:34 [Warning] Aborted connection 717542 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:25:41 [Warning] Aborted connection 718197 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:35:47 [Warning] Aborted connection 719082 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:39:49 [Warning] Aborted connection 718974 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:41:50 [Warning] Aborted connection 720168 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:43:51 [Warning] Aborted connection 720256 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:45:52 [Warning] Aborted connection 719172 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:47:53 [Warning] Aborted connection 719756 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  1:57:58 [Warning] Aborted connection 720648 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:06:02 [Warning] Aborted connection 719662 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:14:06 [Warning] Aborted connection 721712 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:20:09 [Warning] Aborted connection 721523 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:26:12 [Warning] Aborted connection 722335 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:30:14 [Warning] Aborted connection 720836 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:32:15 [Warning] Aborted connection 722630 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:36:17 [Warning] Aborted connection 720558 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:40:19 [Warning] Aborted connection 722445 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:44:21 [Warning] Aborted connection 722726 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:46:22 [Warning] Aborted connection 721224 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:48:23 [Warning] Aborted connection 723117 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:50:24 [Warning] Aborted connection 723519 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  2:52:24 [Warning] Aborted connection 723622 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  8:24:51 [Warning] Aborted connection 739518 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  8:24:51 [Warning] Aborted connection 739539 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  8:25:23 [Warning] Aborted connection 739537 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  8:25:52 [Warning] Aborted connection 739567 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130  8:29:47 [Warning] Aborted connection 739520 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 10:25:38 [Warning] Aborted connection 739547 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 10:28:32 [Warning] Aborted connection 739541 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 10:29:24 [Warning] Aborted connection 743141 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 10:30:29 [Warning] Aborted connection 739631 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 10:34:31 [Warning] Aborted connection 740828 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 10:36:32 [Warning] Aborted connection 739532 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 10:43:57 [Warning] Aborted connection 739636 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 10:44:02 [Warning] Aborted connection 741130 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 10:53:46 [Warning] Aborted connection 739535 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 11:50:46 [Warning] Aborted connection 752644 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 11:57:11 [Warning] Aborted connection 751968 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 11:58:12 [Warning] Aborted connection 739553 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 12:04:23 [Warning] Aborted connection 751972 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 12:06:16 [Warning] Aborted connection 750504 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 12:14:28 [Warning] Aborted connection 751975 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 12:20:30 [Warning] Aborted connection 757754 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 12:21:20 [Warning] Aborted connection 740993 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 12:28:58 [Warning] Aborted connection 751200 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 13:11:52 [Warning] Aborted connection 741008 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 13:21:02 [Warning] Aborted connection 759590 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 13:47:54 [Warning] Aborted connection 763477 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 16:03:20 [Warning] Aborted connection 752699 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 16:46:40 [Warning] Aborted connection 769227 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 16:55:28 [Warning] Aborted connection 768340 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 17:20:42 [Warning] Aborted connection 760579 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 17:22:45 [Warning] Aborted connection 766686 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 17:24:46 [Warning] Aborted connection 759927 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 17:33:00 [Warning] Aborted connection 760175 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 17:33:15 [Warning] Aborted connection 757846 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 17:52:09 [Warning] Aborted connection 764256 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 17:54:14 [Warning] Aborted connection 782696 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 17:54:21 [Warning] Aborted connection 785936 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 17:55:11 [Warning] Aborted connection 741239 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 18:00:17 [Warning] Aborted connection 739538 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 18:00:17 [Warning] Aborted connection 778581 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 18:00:18 [Warning] Aborted connection 757230 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 18:00:19 [Warning] Aborted connection 782089 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 18:03:14 [Warning] Aborted connection 785274 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 18:05:15 [Warning] Aborted connection 759300 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 18:06:11 [Warning] Aborted connection 752693 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 18:07:16 [Warning] Aborted connection 786076 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)
140130 18:07:50 [Warning] Aborted connection 785451 to db: 'otrs' user: 'otrs' host: 'localhost' (Got timeout reading communication packets)

[crythias]

OTRS version and OS would be helpful information.

A related search

[jrcmilanez]

Thanks for reply, friend!

OS is Red Hat Enterprise Linux Server (2.6.32-431.3.1.el6.x86_64)
MySQL is Ver 14.14 Distrib 5.1.71, for redhat-linux-gnu (x86_64) using readline 5.1.
OTRS is 3.2.12.

If you can see at mysql log time, the problem should be with the cron scripts. They are programmed for 10 minutes.

Looking at the cron scripts, I saw they create a database connection and above the script exit (exit(0)), the connection is not closed.

For PHP isn't necessary (http://br2.php.net/mysql_close). But OTRS is perl and I'm not an perl programmer to say if the connection is closed automatically.

Thanks!

[jrcmilanez]

Doing a simple test with this code, this driver close the mysql connection automatically after script execution:

Code: Select all

use DBI;
$dbh = DBI->connect('DBI:mysql:databasename', 'username', 'password'
	           ) || die "Could not connect to database: $DBI::errstr";

Maybe another error?

[crythias]

These are the default crons for 3.2.12:

Code: Select all

20 0 * * 0  $HOME/bin/otrs.DeleteCache.pl --expired >> /dev/null
30 0 * * 0  $HOME/bin/otrs.LoaderCache.pl -o delete >> /dev/null
*/10 * * * *    $HOME/bin/otrs.GenericAgent.pl -c db >> /dev/null
*/20 * * * *    $HOME/bin/otrs.GenericAgent.pl >> /dev/null
45 */2 * * *    $HOME/bin/otrs.PendingJobs.pl >> /dev/null
10 0 * * *  $HOME/bin/otrs.cleanup >> /dev/null
*/10 * * * *    $HOME/bin/otrs.PostMasterMailbox.pl >> /dev/null
01 01 * * * $HOME/bin/otrs.RebuildTicketIndex.pl >> /dev/null
55 */2 * * *    $HOME/bin/otrs.DeleteSessionIDs.pl --expired >> /dev/null
35 * * * *  $HOME/bin/otrs.UnlockTickets.pl --timeout >> /dev/null

While anything herein might trigger the issue (which may be essentially cosmetic), they all call the APIs which handle the open/close of the database.

[jrcmilanez]

Last sunday mysql worked fine and without any problem. Yes, was perfect.

Maybe the problem is the mysql timeouts. Mine are:

connect_timeout = 60
interactive_timeout = 1000
wait_timeout = 1000

Any ideas?

[crythias]

I don't have anything related to your request. You might attempt to run the support assessment.
You might attempt to increase the values according to information you find on the Internet.
You might consider your ticket load and the size of your database.
You might consider running a health check against your database.

Much of what you're encountering is related to your individual implementation, so it's a bit difficult to provide random guesses as to what might work for you.