Guest User

Untitled

a guest
Jun 19th, 2011
274
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/usr/bin/perl
  2.  
  3. # check for recording anomalies -
  4. # based somewhat on greg froese's "myth.rebuilddatabase.pl"
  5. # -- Lincoln Dale <ltd@interlink.com.au>, September 2006
  6. # 2007-03-11: Added pretty print of unknown files vs. orphaned thumbnails.
  7. # (Robert Kulagowski) 2008-02-15: Added dryrun and rerecord options (David
  8. # George)
  9.  
  10. # The intent of this script is to be able to find orphaned rows in the
  11. # 'recorded' table (entries which don't have matching media files) and
  12. # orphaned media files (potentially taking up gigabytes of otherwise usable
  13. # disk space) which have no matching row in the 'recorded' db table.
  14. #
  15. # By default, running the script will simply return a list of problems it
  16. # finds. Running with --dodbdelete will remove db recorded rows for which
  17. # there is no matching media file. Running with --dodelete will delete
  18. # media files for which there is no matching db record.
  19. #
  20. # This script may be useful to fix up some orphaned db entries (causes
  21. # mythweb to run very slowly) as well as reclaim some disk space from some
  22. # orphaned media files. (in an ideal world, neither of these would ever
  23. # happen, but I've seen both happen in reality). This script makes it easy
  24. # to keep track of whether it has or hasn't happened, even if you have
  25. # thousands of recordings and terabytes of stored media.
  26. #
  27. # no warranties expressed or implied. if you run this and it deletes all
  28. # your recordings and sets mythtv to fill up all your disk space with The
  29. # Home Shopping Network, its entirely your fault.
  30. #
  31. # The dryrun option will allow you to see the db entries/files that will be
  32. # deleted without actually executing them.
  33. # The rerecord option is useful if you lose a hard drive in your storage
  34. # group to tell the scheduler to re-record the lost programs (if they happen
  35. # to be shown again).
  36.  
  37. my $progname = "myth.find_orphans.pl";
  38. my $revision = "0.21";
  39.  
  40. use DBI;
  41. use Sys::Hostname;
  42. use Getopt::Long;
  43.  
  44. #
  45. # options
  46. #
  47.  
  48. my $opt_host = hostname;
  49. my $opt_dbhost = $opt_host;
  50. my $opt_database = "mythconverg";
  51. my $opt_user = "";
  52. my $opt_pass = "";
  53. my $opt_ext = "{nuv,mpg,mpeg,avi}";
  54. my $opt_dir = "";
  55. my $opt_dodelete = 0;
  56. my $opt_dodbdelete = 0;
  57. my $debug = 0;
  58. my $opt_help = 0;
  59. my $opt_dryrun = 0;
  60. my $opt_rerecord = 0;
  61.  
  62. GetOptions(
  63. 'host=s' => \$opt_host,
  64. 'dbhost=s' => \$opt_dbhost,
  65. 'database=s' => \$opt_database,
  66. 'user=s' => \$opt_user,
  67. 'pass=s' => \$opt_pass,
  68. 'dir=s' => \$opt_dir,
  69. 'dodelete' => \$opt_dodelete,
  70. 'dodbdelete' => \$opt_dodbdelete,
  71. 'dryrun' => \$opt_dryrun,
  72. 'rerecord' => \$opt_rerecord,
  73. 'debug+' => \$debug,
  74. 'help' => \$opt_help,
  75. 'h' => \$opt_help,
  76. 'v' => \$opt_help);
  77.  
  78. if ($opt_help) {
  79. print<<EOF
  80. $progname (rev $revision)
  81. (checks MythTV recording directories for orphaned files)
  82.  
  83. options:
  84. --host=(host) MythTV backend host ($opt_host)
  85. --dbhost=(host) host where MySQL database for backend is ($opt_dbhost)
  86. --database=(dbname) MythTV database ($opt_database)
  87. --user=(user) MySQL MythTV database user ($opt_user)
  88. --pass=(pass) MySQL MythTV database password ($opt_pass)
  89. --dir=directories manually specify recording directories (otherwise setting is from database)
  90. --debug increase debug level
  91. --dodbdelete remove recorded db entries with no matching file (default: don't)
  92. --dodelete delete files with no record (default: don't)
  93. --dryrun display delete actions without doing them
  94. --rerecord set db entries to re-record missing files (requires --dodbdelete)
  95.  
  96. EOF
  97. ;
  98. exit(0);
  99. }
  100.  
  101. #
  102. # go go go!
  103. #
  104.  
  105. my $valid_recordings = 0;
  106. my $missing_recordings = 0;
  107. my $errors = 0;
  108. my $unknown_files = 0;
  109. my $known_files = 0;
  110. my $unknown_size = 0;
  111. my $known_size = 0;
  112. my $unknown_thumbnail = 0;
  113.  
  114. if (!($dbh = DBI->connect("dbi:mysql:database=$opt_database:host=$opt_dbhost","$opt_user","$opt_pass"))) {
  115. die "Cannot connect to database $opt_database on host $opt_dbhost: $!\n";
  116. }
  117.  
  118. if ($opt_dir eq "") {
  119. &dir_lookup("SELECT dirname FROM storagegroup WHERE hostname=(?) AND groupname != 'DB Backups'");
  120. &dir_lookup("SELECT data FROM settings WHERE value='RecordFilePrefix' AND hostname=(?)");
  121.  
  122. printf STDERR "Recording directories ($opt_host): $opt_dir\n" if $debug;
  123. }
  124.  
  125. if ($opt_dir eq "") {
  126. printf "ERROR: no directory found or specified\n";
  127. exit 1;
  128. }
  129.  
  130. foreach $d (split(/,/,$opt_dir)) {
  131. $d =~ s/\/$//g; # strip trailing /
  132. $dirs{$d}++;
  133. }
  134.  
  135.  
  136. #
  137. # look in recorded table, make sure we can find every file ..
  138. #
  139.  
  140. my $q = "SELECT title, subtitle, description, starttime, endtime, chanid, basename FROM recorded WHERE hostname=(?) ORDER BY starttime";
  141. $sth = $dbh->prepare($q);
  142. $sth->execute($opt_host) || die "Could not execute ($q): $!\n";
  143.  
  144. while (my @row=$sth->fetchrow_array) {
  145. ($title, $subtitle, $description ,$starttime, $endtime, $channel, $basename) = @row;
  146.  
  147. # see if we can find it...
  148. $loc = find_file($basename);
  149. if ($loc eq "") {
  150. printf "Missing media: %s (title:%s, start:%s)\n",$basename,$title,$starttime;
  151. $missing_recordings++;
  152.  
  153. if ($opt_dodbdelete) {
  154. $title =~ s/"/\\"/g;
  155. $subtitle =~ s/"/\\"/g;
  156. $description =~ s/"/\\"/g;
  157. my $sql = sprintf "DELETE FROM oldrecorded WHERE title LIKE \"%s\" AND subtitle LIKE \"%s\" AND description LIKE \"%s\" LIMIT 1", $title, $subtitle, $description;
  158. printf "unmarking program as recorded: %s\n",$sql;
  159. $dbh->do($sql) || die "Could not execute $sql: $!\n";
  160. my $sql = sprintf "DELETE FROM recorded WHERE basename LIKE \"%s\" LIMIT 1",$basename;
  161. printf "performing database delete: %s\n",$sql;
  162. if (!$opt_dryrun) {
  163. $dbh->do($sql) || die "Could not execute $sql: $!\n";
  164. }
  165.  
  166. if ($opt_rerecord) {
  167. my $sql = sprintf "UPDATE oldrecorded SET duplicate = 0 where title = \"%s\" and starttime = \"%s\" and chanid = \"%s\"",
  168. $title, $starttime, $channel;
  169. printf "updating oldrecorded: %s\n", $sql;
  170. if (!$opt_dryrun) {
  171. $dbh->do($sql) || die "Could not execute $sql: $!\n";
  172. }
  173. }
  174. }
  175. } else {
  176. $valid_recordings++;
  177. $seen_basename{$basename}++;
  178. $seen_basename{$basename.".png"}++; # thumbnail
  179. }
  180. }
  181.  
  182. #
  183. # look in recording directories, see if there are extra files not in database
  184. #
  185.  
  186. foreach my $this_dir (keys %dirs) {
  187. opendir(DIR, $this_dir) || die "cannot open directory $this_dir: $!\n";
  188. foreach $this_file (readdir(DIR)) {
  189. if (-f "$this_dir/$this_file") {
  190.  
  191. next if ($this_file eq "nfslockfile.lock");
  192.  
  193. my $this_filesize = -s "$this_dir/$this_file";
  194. if ($seen_basename{$this_file} == 0) {
  195. $sorted_filesizes{$this_filesize} .= sprintf "unknown file [%s]: %s/%s\n",pretty_filesize($this_filesize),$this_dir,$this_file;
  196. $unknown_size += $this_filesize;
  197. if (substr($this_file,-4) eq ".png") {
  198. $unknown_thumbnail++;
  199. }
  200. else {
  201. $unknown_files++;
  202. }
  203.  
  204. if ($opt_dodelete) {
  205. printf STDERR "deleting [%s]: %s/%s\n",pretty_filesize($this_filesize),$this_dir,$this_file;
  206.  
  207. if (!$opt_dryrun) {
  208. unlink "$this_dir/$this_file";
  209.  
  210. if (-f "$this_dir/$this_file") {
  211. $errors++;
  212. printf "ERROR: could not delete $this_dir/$this_file\n";
  213. }
  214. }
  215. }
  216. } else {
  217. $known_files++;
  218. $known_size += $this_filesize;
  219. printf "KNOWN file [%s]: %s/%s\n",pretty_filesize($this_filesize),$this_dir,$this_file if $debug;
  220. }
  221. } else {
  222. printf "NOT A FILE: %s/%s\n",$this_dir,$this_file if $debug;
  223. }
  224. }
  225. closedir DIR;
  226. }
  227.  
  228.  
  229. #
  230. # finished, report results
  231. #
  232.  
  233. foreach my $key (sort { $a <=> $b } keys %sorted_filesizes) {
  234. printf $sorted_filesizes{$key};
  235. }
  236.  
  237. printf "Summary:\n";
  238. printf " Host: %s, Directories: %s\n", $opt_host, join(" ",keys %dirs);
  239. printf " %d ERRORS ENCOUNTERED (see above for details)\n",$errors if ($errors > 0);
  240. printf " %d valid recording%s, %d missing recording%s %s\n",
  241. $valid_recordings, ($valid_recordings != 1 ? "s" : ""),
  242. $missing_recordings, ($missing_recordings != 1 ? "s" : ""),
  243. ($missing_recordings > 0 ? ($opt_dodbdelete ? "were fixed" : "not fixed, check above is valid and use --dodbdelete to fix") : "");
  244. printf " %d known media files using %s\n %d orphaned thumbnails with no corresponding recording\n %d unknown files using %s %s\n",
  245. $known_files, pretty_filesize($known_size),
  246. $unknown_thumbnail,$unknown_files, pretty_filesize($unknown_size),
  247. ($unknown_files > 0 ? ($opt_dodelete ? "were fixed" : "not fixed, check above and use --dodelete to clean up if the above output is accurate") : "");
  248.  
  249. exit(0);
  250.  
  251. ###########################################################################
  252. # filesize bling
  253.  
  254. sub pretty_filesize
  255. {
  256. local($fsize) = @_;
  257. return sprintf "%0.1fGB",($fsize / 1000000000) if ($fsize >= 1000000000);
  258. return sprintf "%0.1fMB",($fsize / 1000000) if ($fsize >= 1000000);
  259. return sprintf "%0.1fKB",($fsize / 1000) if ($fsize >= 1000);
  260. return sprintf "%0.0fB",$fsize;
  261. }
  262.  
  263. ###########################################################################
  264. # find a file in directories without globbing
  265.  
  266. sub find_file
  267. {
  268. local($fname) = @_;
  269.  
  270. foreach my $d (keys %dirs) {
  271. my $f = $d."/".$fname;
  272. if (-e $f) {
  273. return $f;
  274. }
  275. }
  276. return;
  277. }
  278.  
  279. ###########################################################################
  280.  
  281. sub dir_lookup
  282. {
  283. my $query = shift;
  284.  
  285. $sth = $dbh->prepare($query);
  286. $sth->execute($opt_host) || die "Could not execute ($dir_query)";
  287. while (my @row = $sth->fetchrow_array) {
  288. $opt_dir .= "," if ($opt_dir ne "");
  289. $opt_dir .= $row[0];
  290. }
  291. }
  292.  
  293. ###########################################################################
RAW Paste Data