#102 mysql connections gone after long pause

open-fixed
nobody
None
7
2005-08-24
2004-12-30
No

I run the jukebox 24/7 but I want to pause it at night.
Pause works good for a few hours, but after 8-9 hours
the mysql connections disapears and you get an error
message:

DBD::mysql::st execute failed: MySQL server has gone
away at ./playit.pl line 176, <PLAYER> line 53.
DBD::mysql::st execute failed: MySQL server has gone
away at ./playit.pl line 175, <PLAYER> line 54.

And it shuts down.

This doesn't always happen, most of the time playit.pl
tries to play the next song, fails, plays the next
song, fails.. This is an endless loop and only stops
when you kill playit.pl

What I found out:
ripd.pl stays connected because it queries the database
every 10 seconds;
The other connections are lost after 28800 seconds (8
== hours).

Steps to reproduce:
1. Start jukebox
2. Play some songs
3. Press Stop/Pause
4. Wait 8 or more hours
5. Press Play.

Discussion

  • Roel Adriaans

    Roel Adriaans - 2004-12-31

    Logged In: YES
    user_id=470115

    I found out that this is called the 'morning bug' and that
    it exists because mysql has a timeout of 8 hours. Perl tries
    to reconnect but because the scripts use pre-defined sql
    queries it fails. This is because the predefined queries
    aren't send to the MySQL server after the reconnect.

    A simple solution is to change the timeout on the mysql
    site, but a more beautifull solution is to add
    mysql_auto_reconnect to every dbh handle that could have
    problems with the timeout.
    I've modified some files on my jukebox and I'm still testing
    if it solves the problem.

     
  • Paradxum

    Paradxum - 2005-03-12
    • priority: 5 --> 7
     
  • Paradxum

    Paradxum - 2005-03-12

    Logged In: YES
    user_id=596175

    I can verify that this bug exists and can be a major
    problem. I'm glad someone finally tracked it down.

    Looking at the playit.pl script it should not be too hard to
    modify so that that db connection is re-established and
    prepared statements re-evaluated.

    I believe playit.pl is the only script which should suffer
    from this, as ripd.pl polls no matter what, and those are
    the only two persistant scripts.)

     
  • Roel Adriaans

    Roel Adriaans - 2005-03-12

    Logged In: YES
    user_id=470115

    It's nice to hear that i'm not the only one with problems.

    afaik playit.pl isn't the only one with problems, but I'll
    have to dig in my patches to find out.

     
  • William Skellenger

    Logged In: YES
    user_id=631721

    I have not seen this issue... And I usually do exactly this:

    1. Jukebox is always running 24/7
    2. Log in to jukebox and select "random songs if something
    missing"
    3. Listen to random music for an hour or two
    4. Clear the playlist queue
    5. Pause current song
    6. Disable playing random songs

    This leaves one song on pause for a long time. I just
    clicked play and had no issues after 24 hours of it sitting
    on play. Maybe this is a MySQL configuration issue or
    settings issue?

     
  • Paradxum

    Paradxum - 2005-08-23

    Logged In: YES
    user_id=596175

    I have fixed this problem.... methinks.... I haven't tested
    it yet (as in I havent' waited the obligatory 8 hours) ...
    but the code looks oh so nice. After I'm sure it's fixed
    I'll upload the patch.... (it's in the rpm... which is
    almost done)

     
  • Paradxum

    Paradxum - 2005-08-24
    • status: open --> open-fixed
     
  • Paradxum

    Paradxum - 2005-08-24

    Logged In: YES
    user_id=596175

    This is fixed.... the patch is attached.... I will upload it
    into CVS within a few days.
    basically the patch fixes three issues...
    1.) before requesting the next song, check if we have a db
    connection ... if it error's out, re-establish the
    connection and re-prepare the statements that are used.
    2.) Redirect STD* to /dev/null .... This is daemon... we
    shouldn't have hanging connections.
    3.) Run a setsid to tell the os we are a daemon and going it
    alone.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks