From 7eec6de8d5bcfd7562e65083553693772a3a30d5 Mon Sep 17 00:00:00 2001
From: Eric Lammerts <eric@lammerts.org>
Date: Mon, 28 May 2012 14:29:59 -0400
Subject: [PATCH] add connect_to_db() to soepkiptng.lib; set
 mysql_auto_reconnect (thx Roel)

---
 flac2mp3                       |  4 +---
 flac2ogg                       |  4 +---
 q                              |  3 +--
 raw2flac                       |  4 +---
 raw2ogg                        |  4 +---
 soepkiptng.lib                 | 12 ++++++++++++
 soepkiptng_add_seealso         |  3 +--
 soepkiptng_add_shoutcast       |  3 +--
 soepkiptng_delete              |  3 +--
 soepkiptng_detect_hidden_track |  3 +--
 soepkiptng_httpd               |  6 ++----
 soepkiptng_show_lyrics         |  3 +--
 soepkiptng_status              |  3 +--
 soepkiptng_sync_playlist       |  3 +--
 soepkiptng_update              |  3 +--
 soepkiptng_update6.pl          |  3 +--
 soepkiptng_update7.pl          |  3 +--
 soepkiptng_update_gain         |  3 +--
 soepkiptng_write_info          |  4 +---
 soepkiptngd                    |  8 ++++----
 soepkiptngd_mobile             |  1 +
 21 files changed, 36 insertions(+), 47 deletions(-)

diff --git a/flac2mp3 b/flac2mp3
index cbc3bda..2221160 100755
--- a/flac2mp3
+++ b/flac2mp3
@@ -66,9 +66,7 @@ $cwd = cwd;
 
 $| = 1;
 
-$dbh = DBI->connect("DBI:$conf{'db_type'}:$conf{'db_name'}:$conf{'db_host'}",
-	$conf{'db_user'}, $conf{'db_pass'})
-	or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 foreach $flacfile (@ARGV) {
 	next if -d $flacfile;
diff --git a/flac2ogg b/flac2ogg
index 884a128..fafc2e2 100755
--- a/flac2ogg
+++ b/flac2ogg
@@ -66,9 +66,7 @@ $cwd = cwd;
 
 $| = 1;
 
-$dbh = DBI->connect("DBI:$conf{'db_type'}:$conf{'db_name'}:$conf{'db_host'}",
-	$conf{'db_user'}, $conf{'db_pass'})
-	or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 foreach $flacfile (@ARGV) {
 	next if -d $flacfile;
diff --git a/q b/q
index d2a816a..d2b996c 100755
--- a/q
+++ b/q
@@ -103,8 +103,7 @@ sub killit($) {
 
 $| = 1;
 
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 if($opt_n) {
 	open F, $conf{statusfile} or exit;
diff --git a/raw2flac b/raw2flac
index 4813ffb..5fb2d3a 100755
--- a/raw2flac
+++ b/raw2flac
@@ -67,9 +67,7 @@ $flacd_stdin = 'flac -sdc --endian=little --sign=signed --force-raw-format -';
 sub convert_dir($);
 sub convert_raw($);
 
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass})
-	or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 sub cleanup {
 	unlink $tmpfile or warn "$tmpfile: $!\n";
diff --git a/raw2ogg b/raw2ogg
index b3d77e8..ff5db94 100755
--- a/raw2ogg
+++ b/raw2ogg
@@ -68,9 +68,7 @@ sub latin1_to_utf($) {
 	return $s;
 }
 
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass})
-	or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 foreach $rawfile (@ARGV) {
 	next if -d $rawfile;
diff --git a/soepkiptng.lib b/soepkiptng.lib
index 548bfb4..76073f5 100644
--- a/soepkiptng.lib
+++ b/soepkiptng.lib
@@ -67,6 +67,14 @@ sub read_configfile($;@) {
 		join(" ", @extraconfigfiles, @globalconfigfiles);
 }
 
+sub connect_to_db($) {
+	my ($conf) = @_;
+	my $dbh = DBI->connect("DBI:$conf->{db_type}:$conf->{db_name}:$conf->{db_host}", $conf->{db_user}, $conf->{db_pass})
+		or die "can't connect to database";
+	$dbh->{mysql_auto_reconnect} = 1;
+	return $dbh;
+}
+
 %latin9_to_ascii = (
 	128 => '_', 129 => '_', 130 => '_', 131 => '_',
 	132 => '_', 133 => '_', 134 => '_', 135 => '_',
@@ -240,6 +248,7 @@ sub get_table_ids($$$) {
 sub add_song($$$@) {
 	my ($db, $tbl, $user, @ids) = @_;
 
+	$db->ping;
 	$db->do("LOCK TABLES $tbl WRITE");
 	del_song($db, $tbl, @ids);
 	my ($order) = $db->selectrow_array("SELECT MAX(song_order) FROM $tbl");
@@ -261,6 +270,7 @@ sub reorder_table($$$@) {
 sub move_song_to_top($$@) {
 	my ($db, $tbl, @ids) = @_;
 
+	$db->ping;
 	$db->do("LOCK TABLES $tbl WRITE");
 	reorder_table($db, $tbl, $#ids + 2, get_table_ids($db, $tbl, "ORDER BY song_order"));
 	reorder_table($db, $tbl, 1, @ids);
@@ -272,6 +282,7 @@ sub move_song_to_bottom($$@) {
 	my %ids;
 
 	foreach(@ids) { $ids{$_} = 1; };
+	$db->ping;
 	$db->do("LOCK TABLES $tbl WRITE");
 	my @q = get_table_ids($db, $tbl, "ORDER BY song_order");
 	my (@q1, @q2);
@@ -285,6 +296,7 @@ sub move_song_to_bottom($$@) {
 sub shuffle_table($$) {
 	my ($db, $tbl) = @_;
 
+	$db->ping;
 	$db->do("LOCK TABLES $tbl WRITE");
 	reorder_table($db, $tbl, 1, get_table_ids($db, $tbl, "ORDER BY rand()"));
 	$db->do("UNLOCK TABLES");
diff --git a/soepkiptng_add_seealso b/soepkiptng_add_seealso
index 78a55ae..01b022c 100755
--- a/soepkiptng_add_seealso
+++ b/soepkiptng_add_seealso
@@ -84,8 +84,7 @@ sub get_artist($$) {
 
 
 $| = 1;
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 # cleanup seealso table first
 foreach $id (qw/id1 id2/) {
diff --git a/soepkiptng_add_shoutcast b/soepkiptng_add_shoutcast
index 3e117fc..3aaac8d 100755
--- a/soepkiptng_add_shoutcast
+++ b/soepkiptng_add_shoutcast
@@ -56,8 +56,7 @@ sub add_shout($$) {
 
 use DBI;
 
-$dbh = DBI->connect("DBI:$conf{'db_type'}:$conf{'db_name'}:$conf{'db_host'}", $conf{'db_user'}, $conf{'db_pass'})
-	or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 my $shc = $dbh->selectcol_arrayref("SELECT filename FROM song WHERE filename LIKE \"http:%\"");
 foreach(@$shc) {
diff --git a/soepkiptng_delete b/soepkiptng_delete
index 43946f0..545a758 100755
--- a/soepkiptng_delete
+++ b/soepkiptng_delete
@@ -57,8 +57,7 @@ EOF
 	exit;
 }
 
-my $dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+my $dbh = connect_to_db(\%conf);
 
 my $sth = $dbh->prepare("SELECT filename FROM song WHERE present AND filename like ?");
 my $dir = "/%";
diff --git a/soepkiptng_detect_hidden_track b/soepkiptng_detect_hidden_track
index 1d9b110..62abdcc 100755
--- a/soepkiptng_detect_hidden_track
+++ b/soepkiptng_detect_hidden_track
@@ -51,8 +51,7 @@ EOF
 my %conf;
 read_configfile(\%conf, $opt_c);
 
-my $dbh = DBI->connect("DBI:$conf{'db_type'}:$conf{'db_name'}:$conf{'db_host'}", $conf{'db_user'}, $conf{'db_pass'})
-	or die "can't connect to database";
+my $dbh = connect_to_db(\%conf);
 
 FILE: foreach my $file (@ARGV) {
 	my $path = abs_path($file);
diff --git a/soepkiptng_httpd b/soepkiptng_httpd
index 3bc94b5..8cd504e 100755
--- a/soepkiptng_httpd
+++ b/soepkiptng_httpd
@@ -123,8 +123,7 @@ sub run_instance() {
 		}
 	};
 
-	my $dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	        $conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+	my $dbh = connect_to_db(\%conf);
 
 	my $i;
 	for($i = 0; $i < $lifetime; $i++) {
@@ -132,8 +131,7 @@ sub run_instance() {
 		reloadlib();
 		if(!$dbh->ping) {
 			$dbh->disconnect;
-			$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-			        $conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+			$dbh = connect_to_db(\%conf);
 		}
 
 		my $r = $conn->get_request or next;
diff --git a/soepkiptng_show_lyrics b/soepkiptng_show_lyrics
index dd52c06..2386399 100755
--- a/soepkiptng_show_lyrics
+++ b/soepkiptng_show_lyrics
@@ -39,8 +39,7 @@ getopts('avnc:g');
 
 read_configfile(\%conf, $opt_c);
 
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 $pid = 0;
 open STDOUT, ">/dev/null";
diff --git a/soepkiptng_status b/soepkiptng_status
index 88835e2..0649c85 100755
--- a/soepkiptng_status
+++ b/soepkiptng_status
@@ -38,8 +38,7 @@ read_configfile(\%conf, $opt_c);
 
 $| = 1;
 
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 $SIG{'HUP'} = sub {
 	exec $0;
diff --git a/soepkiptng_sync_playlist b/soepkiptng_sync_playlist
index d2ebd98..1334310 100755
--- a/soepkiptng_sync_playlist
+++ b/soepkiptng_sync_playlist
@@ -65,8 +65,7 @@ read_configfile(\%conf, $opt_c);
 
 $| = 1;
 
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 $ARGV[0] or die <<EOF;
 Usage: $0 <playlistname> [<dir>]
diff --git a/soepkiptng_update b/soepkiptng_update
index 148e5c1..e9467f3 100755
--- a/soepkiptng_update
+++ b/soepkiptng_update
@@ -109,8 +109,7 @@ if($opt_q) {
 	$SIG{__WARN__} = sub { };
 }
 
-my $dbh = DBI->connect("DBI:$conf{'db_type'}:$conf{'db_name'}:$conf{'db_host'}", $conf{'db_user'}, $conf{'db_pass'})
-	or die "can't connect to database";
+my $dbh = connect_to_db(\%conf);
 
 my (%track, %artist, %album, %tracknr);
 read_eric_files() unless $opt_e;
diff --git a/soepkiptng_update6.pl b/soepkiptng_update6.pl
index 52500f1..aec0ae1 100755
--- a/soepkiptng_update6.pl
+++ b/soepkiptng_update6.pl
@@ -64,8 +64,7 @@ sub uuid_ascii($)
 
 $| = 1;
 
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 $ids = $dbh->selectcol_arrayref("SELECT id FROM song WHERE present AND filename LIKE '/%' AND uuid IS NULL");
 
diff --git a/soepkiptng_update7.pl b/soepkiptng_update7.pl
index 4dc558a..047896e 100755
--- a/soepkiptng_update7.pl
+++ b/soepkiptng_update7.pl
@@ -38,8 +38,7 @@ getopts('c:');
 
 read_configfile(\%conf, $opt_c);
 
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 $files = $dbh->selectcol_arrayref("SELECT filename FROM song WHERE present AND filesize IS NULL AND filename LIKE '/%'");
 $num = 0;
diff --git a/soepkiptng_update_gain b/soepkiptng_update_gain
index 30c10e7..b12fa0a 100755
--- a/soepkiptng_update_gain
+++ b/soepkiptng_update_gain
@@ -99,8 +99,7 @@ read_configfile(\%conf, $opt_c);
 
 $| = 1;
 
-$dbh = DBI->connect("DBI:$conf{db_type}:$conf{db_name}:$conf{db_host}",
-	$conf{db_user}, $conf{db_pass}) or die "can't connect to database";
+$dbh = connect_to_db(\%conf);
 
 $sth = $dbh->prepare(
 	"SELECT filename FROM song WHERE present AND gain IS NULL AND filename LIKE '/%'" .
diff --git a/soepkiptng_write_info b/soepkiptng_write_info
index f9daf98..c1393d8 100755
--- a/soepkiptng_write_info
+++ b/soepkiptng_write_info
@@ -464,9 +464,7 @@ EOF
 
 $| = 1;
 
-my $dbh = DBI->connect("DBI:$conf{'db_type'}:$conf{'db_name'}:$conf{'db_host'}",
-	$conf{'db_user'}, $conf{'db_pass'})
-	or die "can't connect to database";
+my $dbh = connect_to_db(\%conf);
 
 foreach $f (@ARGV) {
 	if(-d $f) {
diff --git a/soepkiptngd b/soepkiptngd
index cd15a5f..b25a8b9 100755
--- a/soepkiptngd
+++ b/soepkiptngd
@@ -147,6 +147,7 @@ sub get_song_queued() {
 	my $s = undef;
 
 	# get queued song
+	$dbh->ping;
 	$dbh->do("LOCK TABLES queue WRITE, song READ, artist READ, album READ");
 	for(;;) {
 		my $sth = $dbh->prepare(
@@ -743,10 +744,9 @@ for(;;) {
 
 	# (re)open database connection if necessary
 	if(!$dbh || !$dbh->ping) {
-		$dbh = DBI->connect("DBI:mysql:$conf{db_name}:$conf{db_host}",
-			$conf{db_user}, $conf{db_pass}) or warn
-				"Can't connect to database $conf{db_name}" .
-				"\@$conf{db_host} as user $conf{db_user}\n";
+		warn "reconnecting to database\n";
+		$dbh = eval { connect_to_db(\%conf) };
+		warn $@ if $@;
 	}
 
 	if($dbh) {
diff --git a/soepkiptngd_mobile b/soepkiptngd_mobile
index 859e478..6043918 100755
--- a/soepkiptngd_mobile
+++ b/soepkiptngd_mobile
@@ -159,6 +159,7 @@ sub killsong() {
 
 	say("kill song $keypadvalue") if $debug;
 	if($keypadvalue && open_db()) {
+		$dbh->ping;
 		$dbh->do("LOCK TABLES queue WRITE, song READ");
 		my $res = $dbh->selectall_arrayref(
 			"SELECT song_id,song.track FROM queue " .
-- 
2.11.4.GIT