#102 mysql connections gone after long pause
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.
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.
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.)
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.
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?
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)
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.
Major patch... Fixes Issue