1#!/usr/bin/env perl 2# -*-mode:cperl; indent-tabs-mode: nil-*- 3 4## Web-based report on Bucardo activity 5## 6## Copyright 2007-2009 Greg Sabino Mullane <greg@turnstep.com> 7 8use strict; 9use warnings; 10use Data::Dumper; 11use IO::Handle; 12use DBI; 13use CGI; 14 15BEGIN { 16 my $fingerofblame = 'your_email@example.com'; 17 use CGI::Carp qw(fatalsToBrowser set_message); 18 set_message("Something went wrong?! Inconceivable! Email $fingerofblame to get 'er fixed."); 19 use Time::HiRes qw(gettimeofday tv_interval); 20 use vars qw($scriptstart); 21 $scriptstart = [gettimeofday()]; 22}; 23 24use vars qw($q @q %q %dbh $dbh $SQL $sth $info $x $cols @cols $t %info); 25 26$q = new CGI; @q = $q->param; undef %q; for (@q) { $q{$_} = $q->param($_); } 27for (qw(host showhost db sync syncinfo)) { delete $q{$_}; @{$q{$_}} = $q->param($_); } 28my $PORT = $ENV{SERVER_PORT} != 80 ? ":$ENV{SERVER_PORT}" : ''; 29my $PROTO = $ENV{HTTPS} ? 'https' : 'http'; 30my $HERE = "$PROTO://$ENV{SERVER_NAME}$PORT$ENV{SCRIPT_NAME}"; 31my $DONEHEADER = 0; 32my $old_q = "freezer.master_q"; 33my @otherargs = qw(started ended); 34my @showargs = qw(showsql showexplain showanalyze daysback); 35 36*STDOUT->autoflush(1); 37print "Content-type: text/html\n\n"; 38 39my $MAXDAYSBACK = 7; 40 41## Flags to document 42 43## Basic stuff: 44## host=<hostname> 45## host=<hostname>;sync=<syncname> 46## host=<hostname>;db=<targetdbname> 47## Most of the above can be combined to appear on one screen, e.g. 48## host=<hostname>;db=db1;db=db2;db=db3 49## host=<hostname>;sync=sync1 50 51## More control: 52## host=all - show current status of all known hosts (see <DATA>) 53## showhost=<hostname> - force a host to be shown even if other args are given 54 55## Detailed information 56## host=<hostname>;syncinfo=<syncname> Detailed information about a specific sync 57## host=<hostname>;syncinfo=all Detailed information about all sync on a host 58 59## Set with form boxes: 60## started - go back in time a certain amount (e.g. 2h20m) or to a time (14:34) or a date (20071212 12:30) 61## ended - same as started, but sets upper limit 62## limit - maximum number of rows to return 63## sort - which column to sort on 64 65## Debugging: 66## nonagios - do not produce the hidden nagios output 67## shownagios - show the nagios output on the screen 68## showsql - show SQL on the screen 69## showexplain - show explain plan on the screen 70## showanalyze - show explain analyze output on the screen 71## hidetime - do not show the "Total time" at the bottom of the screen 72 73## Read in the connection information 74my (@dbs,%db,$tempdb); 75while (<DATA>) { 76 next if /^#/ or ! /^([A-Z]+)\s*:\s*(.+)\s*$/; 77 my ($name,$value) = ($1,$2); 78 if ('DATABASE' eq $name) { 79 $tempdb = lc $value; 80 push @dbs, $tempdb; 81 } 82 $db{$tempdb}{$name} = $value; 83} 84 85## Common modifiers 86my $WHERECLAUSE = ''; 87my (%where, @adjust, %adjust); 88my %int = (s=>'second',m=>'minute','h'=>'hour',d=>'day',n=>'month',y=>'year'); 89my $validtime = join '|' => values %int, map { "${_}s" } values %int; 90$validtime = qr{$validtime}i; 91if (exists $q{started}) { 92 ## May be negative offset 93 if ($q{started} =~ /\-?\d+\s*[smhd]/i) { 94 ## May be multiples 95 my $time = ''; 96 while ($q{started} =~ /(\d+)\s*([a-z]+)/gi) { 97 my ($offset,$int) = ($1, length $2>1 ? $2 : $2==1 ? $int{lc $2} : $int{lc $2}."s"); 98 $int = "minutes" if $int eq "min"; 99 $int =~ /^$validtime$/ or &Error("Unknown time period: $int"); 100 $time .= "$offset $int "; 101 } 102 chop $time; 103 $where{started} = "started >= now() - '$time'::interval"; 104 push @adjust, [Started => "-$time"]; 105 $adjust{started} = $time; 106 } 107 ## May be a simple time HH:MI[:SS] 108 elsif ($q{started} =~ /^\-?\s*(\d\d:[0123456]\d(?::?[0123456]\d)?)/) { 109 my $dbh = connect_database($q{host}->[0]); 110 my $yymmdd = $dbh->selectall_arrayref("select to_char(now(),'YYYYMMDD')")->[0][0]; 111 my $time = "$yymmdd $1"; 112 $where{started} = "started >= '$time'"; 113 push @adjust, [Started => $time]; 114 $adjust{started} = $time; 115 } 116 ## May be a simple date of YYYYMMDD 117 elsif ($q{started} =~ /^\s*(\d\d\d\d\d\d\d\d)\s*$/) { 118 my $time = "$1 00:00"; 119 $where{started} = "started >= '$time'"; 120 push @adjust, [Started => $time]; 121 $adjust{started} = $time; 122 } 123 ## May be a date of YYYYMMDD HH:MI[:SS] 124 elsif ($q{started} =~ /^\s*(\d\d\d\d\d\d\d\d)\s+(\d\d?:[0123456]\d(?::?[0123456]\d)?)/) { 125 my $time = "$1 $2"; 126 $where{started} = "started >= '$time'"; 127 push @adjust, [Started => $time]; 128 $adjust{started} = $time; 129 } 130} 131if (exists $where{started}) { 132 $WHERECLAUSE = "WHERE $where{started}"; 133} 134 135if (exists $q{ended}) { 136 if ($q{ended} =~ /\-?\d+\s*[smhd]/i) { 137 my $time = ''; 138 while ($q{ended} =~ /(\d+)\s*([a-z]+)/gi) { 139 my ($offset,$int) = ($1, length $2>1 ? $2 : $2==1 ? $int{lc $2} : $int{lc $2}."s"); 140 $int = "minutes" if $int eq "min"; 141 $int =~ /^$validtime$/ or &Error("Unknown time period: $int"); 142 $time .= "$offset $int "; 143 } 144 chop $time; 145 $where{ended} = "started <= now() - '$time'::interval"; 146 push @adjust, [Ended => "$time"]; 147 $adjust{ended} = $time; 148 } 149 ## May be a simple time HH:MI[:SS] 150 elsif ($q{ended} =~ /^\-?\s*(\d\d?:[0123456]\d(?::?[0123456]\d)?)/) { 151 my $dbh = connect_database($q{host}->[0]); 152 my $yymmdd = $dbh->selectall_arrayref("select to_char(now(),'YYYYMMDD')")->[0][0]; 153 my $time = "$yymmdd $1"; 154 $where{ended} = "started <= '$time'"; 155 push @adjust, [Ended => $time]; 156 $adjust{ended} = $time; 157 } 158 ## May be a simple date of YYYYMMDD 159 elsif ($q{ended} =~ /^\s*(\d\d\d\d\d\d\d\d)\s*$/) { 160 my $time = "$1 00:00"; 161 $where{ended} = "started >= '$time'"; 162 push @adjust, [Ended => $time]; 163 $adjust{ended} = $time; 164 } 165 ## May be a date of YYYYMMDD HH:MI[:SS] 166 elsif ($q{ended} =~ /^\s*(\d\d\d\d\d\d\d\d)\s+(\d\d?:[0123456]\d(?::?[0123456]\d)?)/) { 167 my $time = "$1 $2"; 168 $where{ended} = "started >= '$time'"; 169 push @adjust, [Ended => $time]; 170 $adjust{ended} = $time; 171 } 172} 173if (exists $where{ended}) { 174 $WHERECLAUSE .= $WHERECLAUSE ? " AND $where{ended}" : " WHERE $where{ended}"; 175} 176$WHERECLAUSE and $WHERECLAUSE .= "\n"; 177 178my $DEFLIMIT = 300; 179my $LIMIT = $DEFLIMIT; 180if (exists $q{limit} and $q{limit} =~ /^\d+$/) { 181 $LIMIT = $q{limit}; 182 $adjust{limit} = $q{limit}; 183 ## Keep this last 184 push @adjust, ['Maximum rows to pull' => $q{limit}]; 185} 186 187my $SQLSTART = 188qq{ sync,targetdb, 189 COALESCE(to_char(started, 'DDMon HH24:MI:SS'::text), '???'::text) AS started2, 190 COALESCE(to_char(ended, 'HH24:MI:SS'::text), '???'::text) AS ended2, 191 COALESCE(to_char(aborted, 'HH24:MI:SS'::text), ''::text) AS aborted2, 192 CASE WHEN aborted IS NOT NULL THEN to_char(aborted - started, 'MI:SS'::text) ELSE ''::text END AS atime, 193 CASE WHEN inserts IS NOT NULL THEN to_char(ended - started, 'MI:SS'::text) ELSE ''::text END AS runtime, 194 inserts, updates, deletes, COALESCE(whydie,'') AS whydie, pid, ppid, 195 started, ended, aborted, ended-started AS endinterval, aborted-started AS abortinterval, 196 extract(epoch FROM ended) AS endedsecs, 197 extract(epoch FROM started) AS startedsecs, 198 extract(epoch FROM aborted) AS abortedsecs, 199 extract(epoch FROM aborted-started) AS atimesecs, 200 extract(epoch FROM ended-started) AS runtimesecs, 201 CASE 202 WHEN started IS NULL THEN '? ' 203 WHEN now()-ended <= '1 minute'::interval THEN ceil(extract(epoch FROM now()-ended))::text || 's' 204 WHEN now()-ended <= '100 minutes'::interval THEN ceil(extract(epoch FROM now()-ended)/60)::text || ' m' 205 WHEN now()-ended > '24 hours'::interval THEN ceil(extract(epoch FROM now()-ended)/60/60/24)::text || ' Days' 206 ELSE ceil(extract(epoch FROM now()-ended)/60/60)::text || ' h' 207 END AS minutes, 208 floor(CASE 209 WHEN ENDED IS NOT NULL THEN extract(epoch FROM now()-ended) 210 WHEN ABORTED IS NOT NULL THEN extract(epoch FROM now()-aborted) 211 WHEN STARTED IS NOT NULL THEN extract(epoch FROM now()-started) 212 ELSE extract(epoch FROM now()-cdate) 213 END) AS age 214}; 215 216my $found=0; 217 218## View one or more databases 219if (@{$q{db}}) { 220 if (! @{$q{host}}) { 221 ## Must have a host, unless there is only one 222 my $count = keys %db; 223 1==$count or &Error("Must specify a host"); 224 } 225 for my $host (@{$q{host}}) { 226 for my $database (@{$q{db}}) { 227 &showdatabase($host,$database); $found++; 228 } 229 } 230} 231 232## View one or more syncs 233if (@{$q{sync}}) { 234 if (! @{$q{host}}) { 235 ## Must have a host, unless there is only one 236 my $count = keys %db; 237 1==$count or &Error("Must specify a host"); 238 } 239 for my $host (@{$q{host}}) { 240 for my $sync (@{$q{sync}}) { 241 &showsync($host,$sync); $found++; 242 } 243 } 244} 245 246## View meta-information about a sync 247if (@{$q{syncinfo}}) { 248 my @hostlist; 249 if (! @{$q{host}}) { 250 ## Must have a host, unless there is only one 251 my $count = keys %db; 252 1==$count or &Error("Must specify a host"); 253 push @hostlist, keys %db; 254 } 255 elsif (1==@{$q{host}} and $q{host}->[0] eq 'all') { 256 @hostlist = sort keys %db; 257 } 258 else { 259 @hostlist = @{$q{host}}; 260 } 261 for my $host (@hostlist) { 262 next if $db{$host}{SKIP}; 263 if (1==@{$q{syncinfo}} and $q{syncinfo}->[0] eq 'all') { 264 $dbh = connect_database($host); 265 $SQL = "SELECT name FROM bucardo.sync ORDER BY name WHERE status = 'active'"; 266 for my $sync (@{$dbh->selectall_arrayref($SQL)}) { 267 &showsyncinfo($host,$sync->[0]); $found++; 268 } 269 } 270 else { 271 for my $sync (@{$q{syncinfo}}) { 272 &showsyncinfo($host,$sync); $found++; 273 } 274 } 275 } 276} 277 278## Don't show these if part of another query 279if (exists $q{host} and !$found) { 280 ## Hope nobody has named their host "all" 281 if (1==@{$q{host}} and $q{host}->[0] eq 'all') { 282 for (@dbs) { 283 &showhost($_); $found++; 284 } 285 } 286 else { 287 for (@{$q{host}}) { 288 &showhost($_); $found++; 289 } 290 } 291} 292## But they can be forced to show: 293elsif (exists $q{showhost}) { 294 for (@{$q{showhost}}) { 295 &showhost($_); $found++; 296 } 297} 298 299if (!$found or exists $q{overview}) { 300 ## Default action: 301 &Header("Bucardo stats"); 302 print qq{<h2 class="s">Bucardo stats</h2>\n}; 303 print "<ul>"; 304 for (grep { ! $db{$_}{SKIP} } @dbs) { 305 print qq{<li><a href="$HERE?host=$_">$db{$_}{DATABASE} stats</a></li>\n}; 306 } 307} 308 309&Footer(); 310 311 312sub showhost { 313 314 my $host = shift; 315 316 exists $db{$host} or &Error("Unknown database: $host"); 317 my $d = $db{$host}; 318 return if $d->{SKIP}; 319 320 &Header("Bucardo stats for $d->{DATABASE}"); 321 322 my $maxdaysback = (exists $q{daysback} and $q{daysback} =~ /^\d$/) ? $q{daysback} : $MAXDAYSBACK; 323 324 ## Connect to the main database to check on the health 325 $info{dcount} = '?'; $info{tcount} = '?'; 326 unless ($q{norowcount}) { 327 $dbh = connect_database($host."_real"); 328 $SQL = "SELECT 1,count(*) FROM bucardo.bucardo_delta UNION ALL SELECT 2,count(*) FROM bucardo.bucardo_track ORDER BY 1"; 329 $info = $dbh->selectall_arrayref($SQL); 330 $info{dcount} = $info->[0][1]; 331 $info{tcount} = $info->[1][1]; 332 $dbh->disconnect(); 333 } 334 print qq{<h3 class="s">$d->{DATABASE} latest <a href="$HERE">Bucardo</a> sync results }; 335 print qq{</h3>\n}; 336 337 ## Gather all sync information 338 $dbh = connect_database($host); 339 $SQL = "SELECT *, extract(epoch FROM checktime) AS checksecs, ". 340 "extract(epoch FROM overdue) AS overduesecs, ". 341 "extract(epoch FROM expired) AS expiredsecs ". 342 "FROM bucardo.sync"; 343 $sth = $dbh->prepare($SQL); 344 $sth->execute(); 345 my $sync = $sth->fetchall_hashref('name'); 346 347 ## Gather all database group information 348 $SQL = "SELECT dbgroup,db,priority FROM bucardo.dbmap ORDER BY dbgroup, priority, db"; 349 my $dbg; 350 my $order = 1; 351 my $oldgroup = ''; 352 for my $row (@{$dbh->selectall_arrayref($SQL)}) { 353 if ($oldgroup ne $row->[0]) { 354 $order = 0; 355 } 356 $dbg->{$row->[0]}{$row->[1]} = {order=>$order++, pri=>$row->[2]}; 357 } 358 ## Put the groups into the sync structure 359 for my $s (values %$sync) { 360 $s->{running} = undef; 361 if (defined $s->{targetgroup}) { 362 my $x = $dbg->{$s->{targetgroup}}; 363 for my $t (keys %$x) { 364 for my $t2 (keys %{$x->{$t}}) { 365 $s->{dblist}{$t}{$t2} = $x->{$t}{$t2}; 366 } 367 } 368 } 369 else { 370 $s->{dblist}{$s->{targetdb}} = {order=>1, pri=>1}; 371 } 372 } 373 ## Grab any that are queued but not started for each sync/target combo 374 $SQL = "SELECT $SQLSTART FROM (SELECT * FROM bucardo.q ". 375 "NATURAL JOIN (SELECT sync, targetdb, max(ended) AS ended FROM bucardo.q ". 376 "WHERE started IS NULL GROUP BY 1,2) q2) AS q3"; 377 $sth = $dbh->prepare($SQL); 378 $sth->execute(); 379 for my $row (@{$sth->fetchall_arrayref({})}) { 380 $sync->{ $row->{sync} }{ dblist }{ $row->{targetdb} }{queued} = $row; 381 } 382 383 ## Grab any that are currently in progress 384 $SQL = "SELECT $SQLSTART FROM (SELECT * FROM bucardo.q ". 385 "NATURAL JOIN (SELECT sync, targetdb, max(ended) AS ended FROM bucardo.q ". 386 "WHERE started IS NOT NULL and ENDED IS NULL GROUP BY 1,2) q2) AS q3"; 387 $sth = $dbh->prepare($SQL); 388 $sth->execute(); 389 for my $row (@{$sth->fetchall_arrayref({})}) { 390 $sync->{ $row->{sync} }{ dblist }{ $row->{targetdb} }{current} = $row; 391 } 392 ## Grab the last successful 393 $SQL = "SELECT $SQLSTART FROM (SELECT * FROM bucardo.q ". 394 "NATURAL JOIN (SELECT sync, targetdb, max(ended) AS ended FROM bucardo.q ". 395 "WHERE ended IS NOT NULL AND aborted IS NULL GROUP BY 1,2) q2) AS q3"; 396 $sth = $dbh->prepare($SQL); 397 $sth->execute(); 398 for my $row (@{$sth->fetchall_arrayref({})}) { 399 $sync->{$row->{sync}}{dblist}{$row->{targetdb}}{success} = $row; 400 } 401 402 ## Grab the last aborted 403 $SQL = "SELECT $SQLSTART FROM (SELECT * FROM bucardo.q ". 404 "NATURAL JOIN (SELECT sync, targetdb, max(ended) AS ended FROM bucardo.q ". 405 "WHERE aborted IS NOT NULL GROUP BY 1,2) q2) AS q3"; 406 $sth = $dbh->prepare($SQL); 407 $sth->execute(); 408 for my $row (@{$sth->fetchall_arrayref({})}) { 409 $sync->{ $row->{sync} }{ dblist }{ $row->{targetdb} }{aborted} = $row; 410 } 411 412 413 ## While we don't have all syncs, keep going backwards 414 my $TSQL = "SELECT $SQLSTART FROM (SELECT * FROM freezer.child_q_DATE ". 415 "NATURAL JOIN (SELECT sync, targetdb, max(ended) AS ended FROM freezer.child_q_DATE ". 416 "WHERE CONDITION GROUP BY 1,2) AS q2) AS q3"; 417 418 my $done = 0; 419 my $daysback = 0; 420 WAYBACK: { 421 422 ## Do we have all sync information yet? 423 ## We want to find either 'success' or 'aborted' for each sync/target combo 424 $done = 1; 425 SYNC: for my $s (keys %$sync) { 426 next if $sync->{$s}{status} ne 'active'; 427 my $list = $sync->{$s}{dblist}; 428 for my $t (keys %$list) { 429 if (!exists $list->{$t}{success} and ! exists $list->{$t}{aborted}) { 430 $done = 0; 431 last SYNC; 432 } 433 } 434 } ## end check syncs 435 436 last WAYBACK if $done; 437 438 ## Grab aborted runs from this time period 439 $SQL = "SELECT TO_CHAR(now()- interval '$daysback days', 'YYYYMMDD')"; 440 my $date = $dbh->selectall_arrayref($SQL)->[0][0]; 441 442 ($SQL = $TSQL) =~ s/DATE/$date/g; 443 $SQL =~ s/CONDITION/aborted IS NOT NULL/; 444 $sth = $dbh->prepare($SQL); 445 eval { 446 $sth->execute(); 447 }; 448 if ($@) { 449 if ($@ =~ /relation .+ does not exist/) { 450 last WAYBACK; 451 } 452 die $@; 453 } 454 for my $row (@{$sth->fetchall_arrayref({})}) { 455 $sync->{ $row->{sync} }{ dblist }{ $row->{targetdb} }{aborted} = $row 456 if exists $sync->{$row->{sync}}{dblist}{$row->{targetdb}} 457 and ! exists $sync->{$row->{sync}}{dblist}{$row->{targetdb}}{aborted}; 458 } 459 460 ## Grab succesful runs from this time period 461 $SQL = "SELECT TO_CHAR(now()- interval '$daysback days', 'YYYYMMDD')"; 462 $date = $dbh->selectall_arrayref($SQL)->[0][0]; 463 ($SQL = $TSQL) =~ s/DATE/$date/g; 464 $SQL =~ s/CONDITION/ended IS NOT NULL AND aborted IS NULL/; 465 $sth = $dbh->prepare($SQL); 466 $sth->execute(); 467 for my $row (@{$sth->fetchall_arrayref({})}) { 468 $sync->{ $row->{sync} }{ dblist }{ $row->{targetdb} }{success} = $row 469 if exists $sync->{$row->{sync}}{dblist}{$row->{targetdb}} 470 and ! exists $sync->{$row->{sync}}{dblist}{$row->{targetdb}}{success}; 471 } 472 473 last if $daysback >= $maxdaysback; 474 $daysback++; 475 redo; 476 477 } ## end of WAYBACK 478 479 ## Quick count of problems for nagios 480 unless ($q{nonagios}) { 481 my %problem = (overdue => 0, expired => 0, death=>0); 482 my (@odetail,@edetail,@death); 483 for my $s (sort keys %$sync) { 484 next if $sync->{$s}{expiredsecs} == 0; 485 for my $t (sort { 486 $sync->{$s}{dblist}{$a}{order} <=> $sync->{$s}{dblist}{$b}{order} 487 } keys %{$sync->{$s}{dblist}}) { 488 my $x = $sync->{$s}{dblist}{$t}; 489 my $sc = $x->{success}; ## may be undef 490 if (! defined $sc or ! exists $sc->{minutes}) { 491 $x->{expired} = 2; 492 $problem{expired}++; 493 push @edetail, "Expired $s | $t | ?\n"; 494 next; 495 } 496 (my $shortmin = $sc->{minutes}) =~ s/\s//g; 497 ## We have an age 498 if ($sc->{age} > $sync->{$s}{expiredsecs}) { 499 $x->{expired} = 1; 500 $problem{expired}++; 501 push @edetail, "Expired $s | $t | $shortmin\n"; 502 } 503 elsif ($sc->{age} > $sync->{$s}{overduesecs}) { 504 $x->{overdue} = 1; 505 $problem{overdue}++; 506 push @odetail, "Overdue $s | $t | $shortmin\n"; 507 } 508 if (length $sc->{whydie}) { 509 $x->{death} = 1; 510 $problem{death}++; 511 (my $flatdie = $sc->{whydie}) =~ s/\n/ /g; 512 push @death, "Death $s | $t | $flatdie\n"; 513 } 514 } 515 } 516 print $q{shownagios} ? "<pre>\n" : "\n<!-- \n"; 517 print qq{\nBegin Nagios\nHost: $host\nExpired: $problem{expired}\nOverdue: $problem{overdue}\n}; 518 print qq{Death: $problem{death}\n}; 519 print qq{bucardo_delta rows: $info{dcount}\nbucardo_track rows: $info{tcount}\n}; 520 print @edetail; 521 print @odetail; 522 print @death; 523 print "End Nagios\n\n"; 524 print $q{shownagios} ? "</pre>\n" : "-->\n"; 525 } 526 527 my $time = $dbh->selectall_arrayref("select to_char(now(),'DDMon HH24:MI:SS')")->[0][0]; 528 print qq{<table class="tb1" border="1"><caption><span class="c">Current time: $time</span> (days back: $daysback)</caption><tr class="t0">}; 529 530 $cols = q{ 531 Started 532 Ended 533 Aborted 534 Atime 535 Runtime 536 Inserts 537 Updates 538 Deletes 539 Whydie 540 Last Good 541 }; 542 543 @cols = map { s/^\s+//; $_ } grep /\w/ => split /\n/ => $cols; 544 545 unshift @cols, $d->{SINGLE} ? ('Sync type', 'Sync name', '?') : ('Sync type', 'Sync name', 'Databases'); 546 547 my $otherarg = ''; 548 for (@showargs) { 549 if (exists $q{$_} and length $q{$_}) { 550 $otherarg .= qq{;$_=$q{$_}}; 551 } 552 } 553 554 555 our $OCOL = 2; 556 if (exists $q{sort} and $q{sort} =~ /^(\-?\d+)$/) { 557 $OCOL = $1; 558 } 559 560 for ($x=1; $cols[$x-1]; $x++) { 561 if ($d->{SINGLE} and $x==3) { 562 next; 563 } 564 if ($x == $OCOL) { 565 print qq{<th class="t0"><a href="$HERE?host=$host$otherarg;sort=-$x">$cols[$x-1]</a> ^</th>\n}; 566 } 567 elsif ($x == abs($OCOL)) { 568 print qq{<th class="t0"><a href="$HERE?host=$host$otherarg;sort=$x">$cols[$x-1]</a> v</th>\n}; 569 } 570 else { 571 print qq{<th class="t0"><a href="$HERE?host=$host$otherarg;sort=$x">$cols[$x-1]</a></th>\n}; 572 } 573 } 574 print qq{</tr>}; 575 576 my $z=1; 577 our %row; 578 undef %row; 579 $order=1; 580 for my $s (sort keys %$sync) { 581 for my $t (sort { 582 $sync->{$s}{dblist}{$a}{order} <=> $sync->{$s}{dblist}{$b}{order} 583 } keys %{$sync->{$s}{dblist}}) { 584 my $x = $sync->{$s}{dblist}{$t}; 585 my $class = 'xxx'; 586 $class = 'overdue' if $x->{overdue}; 587 $class = 'expired' if $x->{expired}; 588 $class = 'error' if exists $x->{error}; 589 $class = 'inactive' if $sync->{$s}{status} ne 'active'; 590 $order++; 591 $row{$order}{syncinfo} = $sync->{$s}; 592 $row{$order}{sync} = $s; 593 $row{$order}{target} = $t; 594 $row{$order}{html} = qq{<tr class="$class">\n}; 595 $row{$order}{isactive} = $sync->{$s}{status} eq 'active' ? 1 : 0; 596 my $inactive = $sync->{$s}{status} eq 'inactive' ? ' (inactive)' : ''; 597 if (! $d->{SINGLE}) { 598 $row{$order}{html} .= qq{ 599<th>$sync->{$s}{synctype}</th> 600<th align="center"><a href="$HERE?host=$host;sync=$s">$s</a>$inactive</th> 601<th><a href="$HERE?host=$host;db=$t">$t</a></th> 602}; 603 } 604 else { 605 $row{$order}{html} .= qq{ 606<th>$sync->{$s}{synctype}</th> 607<th><a href="$HERE?host=$host;sync=$s">$s</a>$inactive</th> 608}; 609 } 610 611 ## May be undef: pid, whydie, deletes, updates, inserts, ppid 612 my $safe = {}; 613 my $info = $x->{success} || $x->{aborted} || 614 { 615 started2 => '???', 616 ended2 => '???', 617 aborted2 => '???', 618 atime => '???', 619 runtime => '???', 620 inserts => '', 621 updates => '', 622 deletes => '', 623 minutes => '', 624 }; 625 $row{$order}{tinfo} = $info; 626 for my $var (keys %$info) { 627 $safe->{$var} = defined $info->{$var} ? $info->{$var} : '?'; 628 } 629 my $whydie = exists $info->{death} ? "PID: $safe->{pid}<br />PPID: $safe->{ppid}<br />$x->{whydie}" : ''; 630 631 ## Interval rounding errors makes 0:00 time common. Boost to 1 as needed 632 if (defined $safe->{endinterval} and $safe->{endinterval} =~ /00:00:00./o and $safe->{endinterval} !~ /000000$/o) { 633 $safe->{runtime} = '00:01'; 634 } 635 if (defined $safe->{abortinterval} and $safe->{abortinterval} =~ /00:00:00./o and $safe->{abortinterval} !~ /000000$/o) { 636 $safe->{atime} = '00:01'; 637 } 638 639 $row{$order}{html} .= qq{ 640<th class="ts">$safe->{started2}</th> 641<th>$safe->{ended2}</th> 642<th>$safe->{aborted2}</th> 643<th>$safe->{atime}</th> 644<th>$safe->{runtime}</th> 645<th align="right">$safe->{inserts}</th> 646<th align="right">$safe->{updates}</th> 647<th align="right">$safe->{deletes}</th> 648<th align="left"><pre>$whydie</pre></th> 649<th align="right" class="ts"><div class="overdue" id="o$z">Sync: $s<br />Overdue time: $sync->{$s}{overdue}<br />Expire time: $sync->{$s}{expired}</div><span 650 onmouseover="showdue('o$z')" onmouseout="hidegoat('o$z')">$safe->{minutes}</span></th> 651</tr>\n}; 652 $z++; 653 } 654 } 655 656 ## Sort and print 657 my $class = "t2"; 658 for my $r (sort megasort 659 keys %row) { 660 $class = $class eq "t1" ? "t2" : "t1"; 661 $row{$r}{html} =~ s/class="xxx"/class="$class"/; 662 print $row{$r}{html}; 663 } 664 665 666 sub megasort { 667 ## sync type, sync name, target database 668 if (1 == $OCOL) { 669 return ( 670 $row{$a}{syncinfo}{synctype} cmp $row{$b}{syncinfo}{synctype} 671 or $row{$a}{sync} cmp $row{$b}{sync} 672 or $row{$a}{target} cmp $row{$b}{target} 673 ); 674 } 675 if (-1 == $OCOL) { 676 return ( 677 $row{$b}{syncinfo}{synctype} cmp $row{$a}{syncinfo}{synctype} 678 or $row{$a}{sync} cmp $row{$b}{sync} 679 or $row{$a}{target} cmp $row{$b}{target} 680 ); 681 } 682 683 ## sync name, target database 684 if (2 == $OCOL) { 685 return ($row{$b}{isactive} <=> $row{$a}{isactive} 686 or $row{$a}{sync} cmp $row{$b}{sync} 687 or $row{$a}{target} cmp $row{$b}{target}) 688 } 689 if (-2 == $OCOL) { 690 return ($row{$b}{isactive} <=> $row{$a}{isactive} 691 or $row{$b}{sync} cmp $row{$a}{sync} 692 or $row{$b}{target} cmp $row{$a}{target}) 693 } 694 695 ## target database, sync name 696 if (3 == $OCOL) { 697 return ($row{$a}{target} cmp $row{$b}{target} 698 or $row{$a}{sync} cmp $row{$b}{sync}); 699 } 700 if (-3 == $OCOL) { 701 return ($row{$b}{target} cmp $row{$a}{target} 702 or $row{$b}{sync} cmp $row{$a}{sync}); 703 } 704 705 ## start time, sync name, target database 706 if (4 == $OCOL) { 707 return -1 if exists $row{$a}{tinfo}{startedsecs} and ! exists $row{$b}{tinfo}{startedsecs}; 708 return +1 if !exists $row{$a}{tinfo}{startedsecs} and exists $row{$b}{tinfo}{startedsecs}; 709 return ($row{$a}{tinfo}{startedsecs} <=> $row{$b}{tinfo}{startedsecs} 710 or $row{$a}{sync} cmp $row{$b}{sync} 711 or $row{$a}{target} cmp $row{$b}{target} 712 ); 713 } 714 if (-4 == $OCOL) { 715 return +1 if exists $row{$a}{tinfo}{startedsecs} and ! exists $row{$b}{tinfo}{startedsecs}; 716 return -1 if !exists $row{$a}{tinfo}{startedsecs} and exists $row{$b}{tinfo}{startedsecs}; 717 return ($row{$b}{tinfo}{startedsecs} <=> $row{$a}{tinfo}{startedsecs} 718 or $row{$a}{sync} cmp $row{$b}{sync} 719 or $row{$a}{target} cmp $row{$b}{target} 720 ); 721 } 722 723 724 ## end time, sync name, target database 725 if (5 == $OCOL) { 726 return -1 if exists $row{$a}{tinfo}{endedsecs} and ! exists $row{$b}{tinfo}{endedsecs}; 727 return +1 if !exists $row{$a}{tinfo}{endedsecs} and exists $row{$b}{tinfo}{endedsecs}; 728 return ($row{$a}{tinfo}{endedsecs} <=> $row{$b}{tinfo}{endedsecs} 729 or $row{$a}{sync} cmp $row{$b}{sync} 730 or $row{$a}{target} cmp $row{$b}{target} 731 ); 732 } 733 if (-5 == $OCOL) { 734 return +1 if exists $row{$a}{tinfo}{endedsecs} and ! exists $row{$b}{tinfo}{endedsecs}; 735 return -1 if !exists $row{$a}{tinfo}{endedsecs} and exists $row{$b}{tinfo}{endedsecs}; 736 return ($row{$b}{tinfo}{endedsecs} <=> $row{$a}{tinfo}{endedsecs} 737 or $row{$a}{sync} cmp $row{$b}{sync} 738 or $row{$a}{target} cmp $row{$b}{target} 739 ); 740 } 741 742 ## aborted time, sync name, target database 743 if (6 == $OCOL) { 744 return -1 if exists $row{$a}{tinfo}{abortedsecs} and ! exists $row{$b}{tinfo}{abortedsecs}; 745 return +1 if !exists $row{$a}{tinfo}{abortedsecs} and exists $row{$b}{tinfo}{abortedsecs}; 746 return ($row{$a}{tinfo}{abortedsecs} <=> $row{$b}{tinfo}{abortedsecs} 747 or $row{$a}{sync} cmp $row{$b}{sync} 748 or $row{$a}{target} cmp $row{$b}{target} 749 ); 750 } 751 if (-6 == $OCOL) { 752 return +1 if exists $row{$a}{tinfo}{abortedsecs} and ! exists $row{$b}{tinfo}{abortedsecs}; 753 return -1 if !exists $row{$a}{tinfo}{abortedsecs} and exists $row{$b}{tinfo}{abortedsecs}; 754 return ($row{$b}{tinfo}{abortedsecs} <=> $row{$a}{tinfo}{abortedsecs} 755 or $row{$a}{sync} cmp $row{$b}{sync} 756 or $row{$a}{target} cmp $row{$b}{target} 757 ); 758 } 759 760 ## abort time, sync name, target database 761 if (7 == $OCOL) { 762 return -1 if exists $row{$a}{tinfo}{atimesecs} and ! exists $row{$b}{tinfo}{atimesecs}; 763 return +1 if !exists $row{$a}{tinfo}{atimesecs} and exists $row{$b}{tinfo}{atimesecs}; 764 return ($row{$a}{tinfo}{atimesecs} <=> $row{$b}{tinfo}{atimesecs} 765 or $row{$a}{sync} cmp $row{$b}{sync} 766 or $row{$a}{target} cmp $row{$b}{target} 767 ); 768 } 769 if (-7 == $OCOL) { 770 return +1 if exists $row{$a}{tinfo}{atimesecs} and ! exists $row{$b}{tinfo}{atimesecs}; 771 return -1 if !exists $row{$a}{tinfo}{atimesecs} and exists $row{$b}{tinfo}{atimesecs}; 772 return ($row{$b}{tinfo}{atimesecs} <=> $row{$a}{tinfo}{atimesecs} 773 or $row{$a}{sync} cmp $row{$b}{sync} 774 or $row{$a}{target} cmp $row{$b}{target} 775 ); 776 } 777 778 ## run time, sync name, target database 779 if (8 == $OCOL) { 780 return -1 if exists $row{$a}{tinfo}{runtimesecs} and ! exists $row{$b}{tinfo}{runtimesecs}; 781 return +1 if !exists $row{$a}{tinfo}{runtimesecs} and exists $row{$b}{tinfo}{runtimesecs}; 782 return ($row{$a}{tinfo}{runtimesecs} <=> $row{$b}{tinfo}{runtimesecs} 783 or $row{$a}{sync} cmp $row{$b}{sync} 784 or $row{$a}{target} cmp $row{$b}{target} 785 ); 786 } 787 if (-8 == $OCOL) { 788 return +1 if exists $row{$a}{tinfo}{runtimesecs} and ! exists $row{$b}{tinfo}{runtimesecs}; 789 return -1 if !exists $row{$a}{tinfo}{runtimesecs} and exists $row{$b}{tinfo}{runtimesecs}; 790 return ($row{$b}{tinfo}{runtimesecs} <=> $row{$a}{tinfo}{runtimesecs} 791 or $row{$a}{sync} cmp $row{$b}{sync} 792 or $row{$a}{target} cmp $row{$b}{target} 793 ); 794 } 795 796 ## inserts, sync name, target database 797 if (9 == $OCOL) { 798 return -1 if exists $row{$a}{tinfo}{inserts} and ! exists $row{$b}{tinfo}{inserts}; 799 return +1 if !exists $row{$a}{tinfo}{inserts} and exists $row{$b}{tinfo}{inserts}; 800 return ($row{$a}{tinfo}{inserts} <=> $row{$b}{tinfo}{inserts} 801 or $row{$a}{sync} cmp $row{$b}{sync} 802 or $row{$a}{target} cmp $row{$b}{target} 803 ); 804 } 805 if (-9 == $OCOL) { 806 return +1 if exists $row{$a}{tinfo}{inserts} and ! exists $row{$b}{tinfo}{inserts}; 807 return -1 if !exists $row{$a}{tinfo}{inserts} and exists $row{$b}{tinfo}{inserts}; 808 return ($row{$b}{tinfo}{inserts} <=> $row{$a}{tinfo}{inserts} 809 or $row{$a}{sync} cmp $row{$b}{sync} 810 or $row{$a}{target} cmp $row{$b}{target} 811 ); 812 } 813 814 ## updates, sync name, target database 815 if (10 == $OCOL) { 816 return -1 if exists $row{$a}{tinfo}{updates} and ! exists $row{$b}{tinfo}{updates}; 817 return +1 if !exists $row{$a}{tinfo}{updates} and exists $row{$b}{tinfo}{updates}; 818 return ($row{$a}{tinfo}{updates} <=> $row{$b}{tinfo}{updates} 819 or $row{$a}{sync} cmp $row{$b}{sync} 820 or $row{$a}{target} cmp $row{$b}{target} 821 ); 822 } 823 if (-10 == $OCOL) { 824 return +1 if exists $row{$a}{tinfo}{updates} and ! exists $row{$b}{tinfo}{updates}; 825 return -1 if !exists $row{$a}{tinfo}{updates} and exists $row{$b}{tinfo}{updates}; 826 return ($row{$b}{tinfo}{updates} <=> $row{$a}{tinfo}{updates} 827 or $row{$a}{sync} cmp $row{$b}{sync} 828 or $row{$a}{target} cmp $row{$b}{target} 829 ); 830 } 831 832 833 ## deletes, sync name, target database 834 if (11 == $OCOL) { 835 return -1 if exists $row{$a}{tinfo}{deletes} and ! exists $row{$b}{tinfo}{deletes}; 836 return +1 if !exists $row{$a}{tinfo}{deletes} and exists $row{$b}{tinfo}{deletes}; 837 return ($row{$a}{tinfo}{deletes} <=> $row{$b}{tinfo}{deletes} 838 or $row{$a}{sync} cmp $row{$b}{sync} 839 or $row{$a}{target} cmp $row{$b}{target} 840 ); 841 } 842 if (-11 == $OCOL) { 843 return +1 if exists $row{$a}{tinfo}{deletes} and ! exists $row{$b}{tinfo}{deletes}; 844 return -1 if !exists $row{$a}{tinfo}{deletes} and exists $row{$b}{tinfo}{deletes}; 845 return ($row{$b}{tinfo}{deletes} <=> $row{$a}{tinfo}{deletes} 846 or $row{$a}{sync} cmp $row{$b}{sync} 847 or $row{$a}{target} cmp $row{$b}{target} 848 ); 849 } 850 851 852 ## whydie, sync name, target database 853 if (12 == $OCOL) { 854 return -1 if exists $row{$a}{tinfo}{whydie} and ! exists $row{$b}{tinfo}{whydie}; 855 return +1 if !exists $row{$a}{tinfo}{whydie} and exists $row{$b}{tinfo}{whydie}; 856 return ($row{$a}{tinfo}{whydie} cmp $row{$b}{tinfo}{whydie} 857 or $row{$a}{sync} cmp $row{$b}{sync} 858 or $row{$a}{target} cmp $row{$b}{target} 859 ); 860 } 861 if (-12 == $OCOL) { 862 return +1 if exists $row{$a}{tinfo}{whydie} and ! exists $row{$b}{tinfo}{whydie}; 863 return -1 if !exists $row{$a}{tinfo}{whydie} and exists $row{$b}{tinfo}{whydie}; 864 return ($row{$b}{tinfo}{whydie} cmp $row{$a}{tinfo}{whydie} 865 or $row{$a}{sync} cmp $row{$b}{sync} 866 or $row{$a}{target} cmp $row{$b}{target} 867 ); 868 } 869 870 871 ## last good, sync name, target database 872 ## XXX bubble bad to top? 873 if (13 == $OCOL) { 874 return -1 if exists $row{$a}{tinfo}{endedsecs} and ! exists $row{$b}{tinfo}{endedsecs}; 875 return +1 if !exists $row{$a}{tinfo}{endedsecs} and exists $row{$b}{tinfo}{endedsecs}; 876 return ($row{$b}{tinfo}{endedsecs} <=> $row{$a}{tinfo}{endedsecs} 877 or $row{$a}{sync} cmp $row{$b}{sync} 878 or $row{$a}{target} cmp $row{$b}{target} 879 ); 880 } 881 if (-13 == $OCOL) { 882 return +1 if exists $row{$a}{tinfo}{endedsecs} and ! exists $row{$b}{tinfo}{endedsecs}; 883 return -1 if !exists $row{$a}{tinfo}{endedsecs} and exists $row{$b}{tinfo}{endedsecs}; 884 return ($row{$a}{tinfo}{endedsecs} <=> $row{$b}{tinfo}{endedsecs} 885 or $row{$a}{sync} cmp $row{$b}{sync} 886 or $row{$a}{target} cmp $row{$b}{target} 887 ); 888 } 889 890 ## Default: sync name, target database 891 return ($row{$a}{sync} cmp $row{$b}{sync} 892 or $row{$a}{target} cmp $row{$b}{target}) 893 894 } 895 896 print "</table>\n"; 897 898 Footer_Summary(); 899 900 return $daysback; 901 902} ## end of showhost 903 904 905sub D { 906 my $info = shift; 907 print "<hr /><pre>\n"; 908 my $dump = Dumper $info; 909 $dump =~ s/&/&/go; 910 $dump =~ s/</</go; 911 $dump =~ s/>/>/go; 912 print $dump; 913 print "</pre><hr />\n"; 914} ## end of D 915 916 917sub runsql { 918 my $arg = shift; 919 my $SQL = $arg->{sql}; 920 my $dbh = $arg->{dbh}; 921 $sth = $dbh->prepare($SQL); 922 my $querystart = [gettimeofday()]; 923 $sth->execute(); 924 my $querytime = tv_interval($querystart); 925 my $fetchstart = [gettimeofday()]; 926 $info = $sth->fetchall_arrayref({}); 927 my $fetchtime = tv_interval($fetchstart); 928 if ($q{showsql}) { 929 print qq{<div class="showsql"><h3>SQL:</h3><pre>$SQL</pre>}; 930 print qq{<span class="showtime">Execute time: $querytime<br />Fetch time: $fetchtime</span></div>\n}; 931 } 932 for (1..2) { 933 if (1==$_) { 934 next if ! $q{showexplain}; 935 $sth = $dbh->prepare("EXPLAIN $SQL"); 936 } 937 else { 938 next if ! $q{showanalyze}; 939 $sth = $dbh->prepare("EXPLAIN ANALYZE $SQL"); 940 } 941 $sth->execute(); 942 my $plan = join "\n" => map { $_->[0] } @{$sth->fetchall_arrayref()}; 943 $plan =~ s/^/ /; ## Allow first keyword to show up 944 $plan =~ s/ / /g; ## Shrink whitespace 945 $plan =~ s/ width=\d+\)/\)/g; ## Remove dump stat 946 $plan =~ s#cost=(\d+\.\d+\.\.\d+\.\d+)#C=$1#g; ## Shrink cost 947 $plan =~ s/rows=/R=/g; ## Shrink rows 948 $plan =~ s#actual time=(\S+)#AT=<span class="actualtime">$1</span>#g; 949 $plan =~ s#loops=#L=#g; 950 $plan =~ s#Scan (on )?(\w+)#Scan $1<span class="relname">$2</span>#g; 951 $plan =~ s#^(\s*)->(\s+[A-Z][a-zA-Z]+)+#$1<span class="parrow">-></span><span class="pword">$2</span>#gm; 952 $plan =~ s#^(\s*)(\s+[A-Z][a-zA-Z]+)+#$1<span class="pword2">$2</span>#gm; 953 $plan =~ s#^(\s*Total runtime: )(\d+\.\d+ ms)#<span class="runtime1">$1</span><span class="runtime2">$2</span>#m; 954 printf qq{<div class="showsql"><h3>Explain %s:</h3><pre>$plan</pre></div>}, 955 1==$_ ? "plan" : "analyze"; 956 } 957 exit if $q{showanalyze}; ## XXXX GREG 958 959 print qq{<form method="get" action="$HERE">\n}; 960 for (sort keys %{$arg->{hidden}}) { 961 print qq{<input type="hidden" name="$_" value="$arg->{hidden}{$_}" />}; 962 } 963 964 if (exists $q{sort}) { 965 print qq{<input type="hidden" name="sort" value="$q{sort}" />}; 966 } 967 for (@showargs) { 968 next if $_ eq 'daysback'; 969 if (exists $q{$_} and length $q{$_}) { 970 print qq{<input type="hidden" name="$_" value="$q{$_}" />}; 971 } 972 } 973 974 if ($arg->{type} eq 'host') { 975 printf qq{<span class="maxrows">Earliest date: <strong>$arg->{earliest}</strong> Maximum days back: <input type="text" name="daysback" size="%d" value="$arg->{daysback}"/></span>}, length($arg->{daysback}) + 3; 976 } 977 else { 978 print qq{<span class="maxrows">Maximum rows: <input type="text" name="limit" size="4" value="$LIMIT"/></span>}; 979 printf qq{<span class="timeshift">Start time: <input type="text" name="started" size="*%d"%s/></span>}, 980 $adjust{started} ? 2+length($adjust{started}) : 4, 981 $adjust{started} ? qq{ value="$adjust{started}" } : ""; 982 printf qq{<span class="timeshift">End time: <input type="text" name="ended" size="*%d"%s/></span>}, 983 $adjust{ended} ? 2+length($adjust{ended}) : 4, 984 $adjust{ended} ? qq{ value="$adjust{ended}" } : ""; 985 } 986 print qq{ <input type="submit" value="Change" />}; 987 print qq{</form>}; 988 989 if (@adjust) { 990 print qq{<p><span class="adjust1">Adjustments:</span>}; 991 for (@adjust) { 992 print qq{<span class="adjust2">$_->[0] </span><span class="adjust3">$_->[1] </span> }; 993 } 994 print "</p>\n"; 995 } 996 997 my $time = $dbh->selectall_arrayref("select to_char(now(),'DDMon HH24:MI:SS')")->[0][0]; 998 print qq{<table class="tb1" border="1"><caption><span class="c">Current time: $time</span></caption><tr class="t0">}; 999 return $info; 1000 1001} ## end of runsql 1002 1003sub showdatabase { 1004 1005 my ($host,$name) = @_; 1006 1007 exists $db{$host} or &Error("No such host: $host"); 1008 my $d = $db{$host}; 1009 1010 &Header("$d->{DATABASE} Bucardo stats for target database $name"); 1011 1012 print qq{<h3 class="s"><a href="$HERE?host=$host">$d->{DATABASE}</a> <a href="$HERE">Bucardo</a> stats for target database "$name"</h3>\n}; 1013 1014 ## Default sort 1015 my $OCOL = 2; 1016 my $ODIR = $where{started} ? "ASC" : "DESC"; 1017 if (exists $q{sort} and $q{sort} =~ /^(\-?)(\d+)$/) { 1018 $OCOL = $2; 1019 $ODIR = (length $1 ? "DESC" : "ASC"); 1020 } 1021 my $OCOL2 = $OCOL; 1022 $OCOL2 = "started" if 2 == $OCOL; 1023 $OCOL2 = "ended" if 3 == $OCOL; 1024 $OCOL2 = "aborted" if 4 == $OCOL; 1025 1026 $SQL = 1027qq{SELECT 1028 sync, 1029$SQLSTART 1030FROM (SELECT * FROM bucardo.q WHERE targetdb=\$1 UNION ALL SELECT * FROM bucardo.$old_q WHERE targetdb=\$1) q 1031${WHERECLAUSE}ORDER BY $OCOL2 $ODIR, 1 ASC, started DESC 1032LIMIT $LIMIT}; 1033 1034 ## XXX Same as the sync - do a pre-scan to get the magic number of days 1035 $dbh = connect_database($host); 1036 $SQL =~ s/\$1/$dbh->quote($name)/ge; 1037 $info = runsql({dbh => $dbh, sql => $SQL, hidden => {host=>$host,db=>$name}}); 1038 1039 $cols = q{ 1040 Sync name 1041 Started 1042 Ended 1043 Aborted 1044 Atime 1045 Runtime 1046 Inserts 1047 Updates 1048 Deletes 1049 Whydie 1050 }; 1051 1052 @cols = map { s/^\s+//; $_ } grep /\w/ => split /\n/ => $cols; 1053 1054 my $otherarg = ''; 1055 if ($LIMIT != $DEFLIMIT) { 1056 $otherarg .= qq{;limit=$LIMIT}; 1057 } 1058 for (@otherargs, @showargs) { 1059 if (exists $q{$_} and length $q{$_}) { 1060 $otherarg .= qq{;$_=$q{$_}}; 1061 } 1062 } 1063 for ($x=1; $cols[$x-1]; $x++) { 1064 if ($x != $OCOL) { 1065 print qq{<th class="t0"><a href="$HERE?host=$host;db=$name$otherarg;sort=$x">$cols[$x-1]</a></th>\n}; 1066 } 1067 elsif ($ODIR eq "ASC") { 1068 print qq{<th class="t0"><a href="$HERE?host=$host;db=$name$otherarg;sort=-$x">$cols[$x-1]</a> ^</th>\n}; 1069 } 1070 else { 1071 print qq{<th class="t0"><a href="$HERE?host=$host;db=$name$otherarg;sort=$x">$cols[$x-1]</a> v</th>\n}; 1072 } 1073 } 1074 print qq{</tr>}; 1075 1076 $t = "t2"; 1077 for (@$info) { 1078 $t = $t eq "t1" ? "t2" : "t1"; 1079 my $whydie = length $_->{whydie} ? "PID: $_->{pid}<br />PPID: $_->{ppid}<br />$_->{whydie}" : ''; 1080 print qq{ 1081<tr class="$t"> 1082<th><a href="$HERE?host=$host;sync=$_->{sync}">$_->{sync}</a></th> 1083<th class="ts">$_->{started2}</th> 1084<th>$_->{ended2}</th> 1085<th>$_->{aborted2}</th> 1086<th>$_->{atime}</th> 1087<th>$_->{runtime}</th> 1088<th align="right">$_->{inserts}</th> 1089<th align="right">$_->{updates}</th> 1090<th align="right">$_->{deletes}</th> 1091<th align="left"><pre>$whydie</pre></th> 1092</tr> 1093 }; 1094 } 1095 print "</table>\n"; 1096 1097} ## end of showdatabase 1098 1099 1100sub showsync { 1101 1102 my ($host,$name) = @_; 1103 1104 exists $db{$host} or &Error("No such host: $host"); 1105 my $d = $db{$host}; 1106 1107 &Header("$d->{DATABASE} Bucardo stats for sync $name"); 1108 1109 ## Default order by 1110 my $OCOL = 2; 1111 my $ODIR = $where{started} ? "ASC" : "DESC"; 1112 if (exists $q{sort} and $q{sort} =~ /^(\-?)(\d+)$/) { 1113 $OCOL = $2; 1114 $ODIR = (length $1 ? "DESC" : "ASC"); 1115 } 1116 my $OCOL2 = $OCOL; 1117 $OCOL2 = "started" if 2 == $OCOL; 1118 $OCOL2 = "ended" if 3 == $OCOL; 1119 $OCOL2 = "aborted" if 4 == $OCOL; 1120 1121 $dbh = connect_database($host); 1122 1123 ## Quick check that this is a valid sync 1124 $SQL = "SELECT * FROM bucardo.sync WHERE name = ?"; 1125 $sth = $dbh->prepare($SQL); 1126 my $count = $sth->execute($name); 1127 if ($count eq '0E0') { 1128 &Error("That sync does not exist"); 1129 } 1130 my $syncinfo = $sth->fetchall_arrayref({})->[0]; 1131 1132 printf qq{<h3 class="s"><a href="%s">%s</a> <a href="%s">Bucardo</a> sync <a href="%s">"%s"</a>\n}, 1133 "$HERE?host=$host", $d->{DATABASE}, $HERE, "$HERE?host=$host;syncinfo=$name", $name; 1134 my $space = ' ' x 10; 1135 my $mouseover = qq{onmouseover="showgoat('info',+50)"}; 1136 my $mouseout = qq{onmouseout="hidegoat('info')"}; 1137 print qq{$space<a class="headerhide" href="" $mouseover $mouseout>$space$space quickinfo $space$space</a></h3>\n}; 1138 my $INFO = ''; 1139 for (sort keys %$syncinfo) { 1140 next if ! defined $syncinfo->{$_} or ! length $syncinfo->{$_}; 1141 if ($_ eq 'conflict_code') { 1142 $syncinfo->{conflict_code} = '(NOT SHOWN)'; 1143 } 1144 $INFO .= qq{$_: <b>$syncinfo->{$_}</b><br />}; 1145 } 1146 print qq{<div class="hiddengoat" id="info">$INFO</div>}; 1147 1148 my $daysback = $q{daysback} || $d->{DAYSBACKSYNC} || 7; 1149 $daysback =~ /^\d+$/ or &Error("Invalid number of days"); 1150 $SQL = "SELECT TO_CHAR(now()-'$daysback days'::interval, 'DD FMMonth YYYY')"; 1151 my $earliest = $dbh->selectall_arrayref($SQL)->[0][0]; 1152 my $oldwhere = " WHERE sync=\$1 AND cdate >= '$earliest'"; 1153 1154 $SQL = $d->{SINGLE} ? 1155qq{SELECT 1156 synctype, 1157$SQLSTART 1158FROM (SELECT * FROM bucardo.q WHERE sync=\$1 UNION ALL SELECT * FROM bucardo.$old_q $oldwhere) q 1159${WHERECLAUSE}ORDER BY $OCOL2 $ODIR, 1 ASC 1160LIMIT $LIMIT} : 1161qq{SELECT 1162 targetdb, 1163$SQLSTART 1164FROM (SELECT * FROM bucardo.q WHERE sync=\$1 UNION ALL SELECT * FROM bucardo.$old_q $oldwhere) q 1165${WHERECLAUSE}ORDER BY $OCOL2 $ODIR, 1 ASC 1166LIMIT $LIMIT}; 1167 1168 $SQL =~ s/\$1/$dbh->quote($name)/ge; 1169 $info = runsql({dbh => $dbh, sql => $SQL, hidden => {host=>$host,sync=>$name}}); 1170 1171 $cols = q{ 1172 Started 1173 Ended 1174 Aborted 1175 Atime 1176 Runtime 1177 Inserts 1178 Updates 1179 Deletes 1180 Whydie 1181 }; 1182 1183 @cols = map { s/^\s+//; $_ } grep /\w/ => split /\n/ => $cols; 1184 1185 unshift @cols, $d->{SINGLE} ? ('Sync type') : ('Database'); 1186 1187 my $otherarg = ''; 1188 if ($LIMIT != $DEFLIMIT) { 1189 $otherarg .= qq{;limit=$LIMIT}; 1190 } 1191 for (@otherargs, @showargs) { 1192 if (exists $q{$_} and length $q{$_}) { 1193 $otherarg .= qq{;$_=$q{$_}}; 1194 } 1195 } 1196 for ($x=1; $cols[$x-1]; $x++) { 1197 if (!@$info) { 1198 print qq{<th class="t0">$cols[$x-1]</th>\n}; 1199 } 1200 else { 1201 my $c = 't0'; 1202 if ($x != $OCOL) { 1203 print qq{<th class="$c"><a href="$HERE?host=$host;sync=$name$otherarg;sort=$x">$cols[$x-1]</a></th>\n}; 1204 } 1205 elsif ($ODIR eq "ASC") { 1206 print qq{<th class="$c"><a href="$HERE?host=$host;sync=$name$otherarg;sort=-$x">$cols[$x-1]</a> ^</th>\n}; 1207 } 1208 else { 1209 print qq{<th class="$c"><a href="$HERE?host=$host;sync=$name$otherarg;sort=$x">$cols[$x-1]</a> v</th>\n}; 1210 } 1211 } 1212 } 1213 print qq{</tr>}; 1214 1215 $t = "t2"; 1216 for (@$info) { 1217 $t = $t eq "t1" ? "t2" : "t1"; 1218 print qq{<tr class="$t">}; 1219 if ($d->{SINGLE}) { 1220 print qq{<th>$_->{synctype}</th>\n}; 1221 } 1222 else { 1223 print qq{<th><a href="$HERE?host=$host;db=$_->{targetdb}">$_->{targetdb}</a></th>\n}; 1224 } 1225my $whydie = length $_->{whydie} ? "PID: $_->{pid}<br />PPID: $_->{ppid}<br />$_->{whydie}" : ''; 1226print qq{ 1227<th class="ts">$_->{started2}</th> 1228<th>$_->{ended2}</th> 1229<th>$_->{aborted2}</th> 1230<th>$_->{atime}</th> 1231<th>$_->{runtime}</th> 1232<th align="right">$_->{inserts}</th> 1233<th align="right">$_->{updates}</th> 1234<th align="right">$_->{deletes}</th> 1235<th align="left"><pre>$whydie</pre></th> 1236</tr> 1237 }; 1238 } 1239 print "</table>\n"; 1240 1241} ## end of showsync 1242 1243 1244sub showsyncinfo { 1245 1246 my ($host,$name) = @_; 1247 1248 exists $db{$host} or &Error("No such host: $host"); 1249 my $d = $db{$host}; 1250 1251 &Header("$d->{DATABASE} Bucardo information on sync $name"); 1252 1253 printf qq{<h3 class="s"><a href="%s">%s</a> <a href="%s">Bucardo</a> sync %s (<a href="%s">view stats</a>)</h3>\n}, 1254 "$HERE?host=$host", $d->{DATABASE}, $HERE, $name, "$HERE?host=$host;sync=$name"; 1255 1256 $dbh = connect_database($host); 1257 if (! exists $info{$host}{syncinfo}) { 1258 $SQL = "SELECT * FROM bucardo.sync"; 1259 $sth = $dbh->prepare($SQL); 1260 $sth->execute(); 1261 $info{$host}{syncinfo} = $sth->fetchall_hashref('name'); 1262 } 1263 if (! exists $info{$host}{syncinfo}{$name}) { 1264 &Error("Sync not found: $name"); 1265 } 1266 $info = $info{$host}{syncinfo}{$name}; 1267 1268 ## Grab all herds if not loaded 1269 if (! exists $info{$host}{herds} ) { 1270 $SQL = qq{ 1271 SELECT * 1272 FROM bucardo.herdmap h, bucardo.goat g 1273 WHERE g.id = h.goat 1274 ORDER BY priority DESC, tablename ASC 1275 1276 }; 1277 $sth = $dbh->prepare_cached($SQL); 1278 $sth->execute(); 1279 $info{$host}{herds} = $sth->fetchall_arrayref({}); 1280 } 1281 ## Get the goats for this herd: 1282 my @goats = grep { $_->{herd} eq $info->{source} } @{$info{$host}{herds}}; 1283 1284 my $goatinfo = qq{Goats in herd <em>$info->{source}</em>:}; 1285 for (@goats) { 1286 $goatinfo .= sprintf qq{<br />$_->{tablename}%s%s}, 1287 $_->{ghost} ? " GHOST!" : '', 1288 $_->{pkey} ? " (pkey: <em>$_->{pkey}</em>)" : ''; 1289 } 1290 1291 my $target = qq{Target database:</th><td class="syncinfo">$info->{targetdb}</th>}; 1292 if ($info->{targetgroup}) { 1293 my $t = $info->{targetgroup}; 1294 if (! exists $info{$host}{dbs}{$t}) { 1295 $SQL = "SELECT dm.db FROM bucardo.dbmap dm JOIN bucardo.db db ON db.name = dm.db WHERE dm.dbgroup = ? AND db.status = 'active' ORDER BY dm.priority DESC, dm.db ASC"; 1296 $sth = $dbh->prepare_cached($SQL); 1297 $sth->execute($t); 1298 $info{$host}{dbs}{$t} = $sth->fetchall_arrayref({}); 1299 } 1300 my $dbinfo = "Databases in group <em>$t</em>:"; 1301 for (@{$info{$host}{dbs}{$t}}) { 1302 $dbinfo .= "<br />$_->{db}"; 1303 } 1304 $target = qq{Target database group:</th><td class="syncinfo2" }; 1305 $target .= qq{onmouseover="showgoat('db$t',-100)" onmouseout="hidegoat('db$t')">}; 1306 $target .= qq{<div class="hiddengoat" id="db$t">$dbinfo</div>$t</th>}; 1307 } 1308 1309 print qq{<table class="syncinfo" border="1">\n}; 1310 $x = $info->{name}; 1311 1312 for (qw(ping kidsalive stayalive)) { 1313 $info->{"YN$_"} = $info->{$_} ? "Yes" : "No"; 1314 } 1315 1316 my $fullcopy = ''; 1317 if ($info->{synctype} eq 'fullcopy') { 1318 $fullcopy = qq{<tr><th class="syncinfo">Delete method:</th><td class="syncinfo">$info->{deletemethod}</th></tr>}; 1319 } 1320 my $delta = ''; 1321 if ($info->{synctype} ne 'fullcopy') { 1322 $delta = qq{<tr><th class="syncinfo">Ping:</th><td class="syncinfo">$info->{YNping}</th></tr>}; 1323 } 1324 1325 print qq{ 1326<tr><th class="syncinfo">Sync name:</th><td class="syncinfo">$info->{name}</th></tr> 1327<tr><th class="syncinfo">Status:</th><td class="syncinfo">$info->{status}</th></tr> 1328<tr><th class="syncinfo">Sync type:</th><td class="syncinfo">$info->{synctype}</th></tr> 1329<tr><th class="syncinfo">Source:</th><td class="syncinfo2" onmouseover="showgoat('o$x',-100)" onmouseout="hidegoat('o$x')"> 1330<div class="goatinfo" id="o$x">$goatinfo</div>$info->{source}</th></tr> 1331<tr><th class="syncinfo">$target</tr> 1332$delta 1333<tr><th class="syncinfo">Check time:</th><td class="syncinfo">$info->{checktime}</th></tr> 1334<tr><th class="syncinfo">Overdue limit:</th><td class="syncinfo">$info->{overdue}</th></tr> 1335<tr><th class="syncinfo">Expired limit:</th><td class="syncinfo">$info->{expired}</th></tr> 1336$fullcopy 1337<tr><th class="syncinfo">Controller stays alive:</th><td class="syncinfo">$info->{YNstayalive}</th></tr> 1338<tr><th class="syncinfo">Kids stay alive:</th><td class="syncinfo">$info->{YNkidsalive}</th></tr> 1339<tr><th class="syncinfo">Priority:</th><td class="syncinfo">$info->{priority}</th></tr> 1340 }; 1341 1342 1343 print "</table>\n"; 1344 1345} ## end of showsyncinfo 1346 1347 1348sub Header { 1349 1350 return if $DONEHEADER++; 1351 my $title = shift || "Bucardo Stats"; 1352 print qq{<html> 1353<head> 1354<title>$title</title> 1355<script type="text/javascript"> 1356<!-- 1357var X = 0; 1358var Y = 0; 1359window.captureEvents(Event.MOUSEMOVE) 1360window.onmousemove=Move; 1361function Move(e) { X = e.pageX; Y = e.pageY; } 1362function showdue(o) { 1363var obj = document.getElementById(o); 1364obj.style.top=Y-100; 1365obj.style.visibility = 'visible'; 1366return false; 1367} 1368function hidegoat(g) { 1369 var obj = document.getElementById(g); 1370 obj.style.visibility = 'hidden'; 1371 return false; 1372} 1373function showgoat(g,offset) { 1374 var obj = document.getElementById(g); 1375 obj.style.top=Y+offset; 1376 obj.style.visibility = 'visible'; 1377 return false; 1378} 1379// --> 1380</script> 1381 1382<style type="text/css"> 1383body { margin-left: 1em; 1384font-family: arial, sans-serif; 1385} 1386h1.s, h2.s, h3.s { 1387 background-color: #3399ff; 1388 border: solid 1px #999999; 1389 padding: 0.2em; 1390 padding-left: 0.5em; 1391 -moz-border-radius: 20px; 1392} 1393a.headerhide { 1394 color: #3399ff; 1395} 1396span.hideheader { 1397 color: #3399ff; 1398 font-size: smaller; 1399} 1400h3.error { 1401 background-color: #ff3333; 1402 border: solid 1px #999999; 1403 padding: 0.5em; 1404 padding-left: 0.5em; 1405 -moz-border-radius: 20px; 1406} 1407p.error { 1408 padding-left: 0.5em; 1409 font-family: monospace; 1410 font-weight: bolder; 1411} 1412span.adjust0 { margin-bottom: 10px; } 1413span.adjust1 { background-color: #bbeeee; font-weight: bolder; } 1414span.adjust2 { background-color: #aaffaa; margin-left: 1em;} 1415span.adjust3 { background-color: #dede88;} 1416span.maxrows { padding-left: 1em; } 1417span.timeshift { padding-left: 1.5em; } 1418span.error { } 1419span.c { 1420 background-color: #66ccee; 1421 -moz-border-radius: 10px; 1422 font-weight: bolder; 1423 padding-left: 10px; 1424 padding-right: 10px; 1425 padding-top: 2px; 1426} 1427table.tb1 { empty-cells: show; font-size: 14px; } 1428th { padding-left: 10px; padding-right: 10px; } 1429th.ts { white-space: nowrap; } 1430th.t0 { padding: 5px; } 1431th.t0l { padding: 5px; text-align: left; } 1432tr.t0 { background-color: #ccffcc; } 1433tr.t1 { background-color: #ffdddd; } 1434tr.t2 { background-color: #ddddff; } 1435tr.overdue { background-color: red; color: white; } 1436tr.overdue a:visited { color: cyan; } 1437tr.overdue a:active { color: black; } 1438tr.overdue a { color: yellow; } 1439tr.inactive { background-color: grey; color: white; } 1440tr.inactive a:visited { color: cyan; } 1441tr.inactive a:active { color: black; } 1442tr.inactive a { color: yellow; } 1443tr.expired { background-color: black; color: white; } 1444tr.expired a:visited { color: cyan; } 1445tr.expired a:active { color: black; } 1446tr.expired a { color: yellow; } 1447tr.error { background-color: purple; color: white; } 1448tr.error a:visited { color: cyan; } 1449tr.error a:active { color: black; } 1450tr.error a { color: yellow; } 1451div.overdue { 1452 visibility: hidden; 1453 z-index: 1; 1454 text-align: center; 1455 padding: 1em; 1456 background-color: #ff00ff; 1457 color: white; 1458 position: absolute; 1459 right: 40%; 1460 right: 40%; 1461 top: 40%; 1462} 1463div.goatinfo { 1464 visibility: hidden; 1465 z-index: 1; 1466 text-align: left; 1467 padding: 1em; 1468 background-color: #cc00cc; 1469 color: white; 1470 position: absolute; 1471 right: 40%; 1472 top: 20%; 1473} 1474div.hiddengoat { 1475 visibility: hidden; 1476 z-index: 1; 1477 text-align: left; 1478 padding: 1em; 1479 background-color: #33FFFF; 1480 color: black; 1481 position: absolute; 1482 right: 40%; 1483 top: 20%; 1484} 1485div.showsql { 1486 font-family: monospace; 1487 color: blue; 1488 background-color: #ccccff; 1489 -moz-border-radius: 10px; 1490 padding-bottom: 1em; 1491} 1492span.showtime { font-weight: bolder; } 1493span.relname { color: #cc0000; } 1494span.parrow { color: #000000; font-weight: bold; } 1495span.pword { font-weight: bold; } 1496span.pword2 { font-weight: bold; color: #330033; } 1497span.actualtime { color: #000000; font-weight: bold; } 1498span.runtime1 { font-weight: bold; } 1499span.runtime2 { background-color: white; color: black; font-size: 110%; font-weight: 800; } 1500table.syncinfo { background-color: #ccffff; border: 1px solid black; empty-cells: show; font-size: 16px; margin-left: 10px;} 1501th.syncinfo { color: black; text-align: left; } 1502td.syncinfo { color: blue; font-weight: 800; padding-left: .5em; padding-right: .5em; } 1503td.syncinfo2 { color: #cc0000; font-weight: 800; padding-left: .5em; padding-right: .5em; } 1504</style> 1505</head> 1506 1507<body> 1508}; 1509 1510} ## end of Header 1511 1512sub Footer_Summary { 1513 1514 my $scripttime = tv_interval($scriptstart); 1515 unless ($q{hidetime}) { 1516 printf "<p><small>Total time: %.2f", $scripttime; 1517 if (exists $info{dcount}) { 1518 print " Rows in bucardo_delta: $info{dcount} Rows in bucardo_track: $info{tcount}"; 1519 } 1520 print "</small></p>"; 1521 } 1522} 1523 1524sub Footer { 1525 print "</body></html>\n"; 1526 exit; 1527} ## end of Footer 1528 1529sub connect_database { 1530 1531 my $name = shift; 1532 1533 if (!exists $db{$name}) { 1534 &Error("No such database: $name"); 1535 } 1536 if (exists $dbh{$name}) { 1537 return $dbh{$name}; 1538 } 1539 my $d = $db{$name}; 1540 $dbh = DBI->connect_cached($d->{DSN},$d->{DBUSER},$d->{DBPASS}, {AutoCommit=>0,RaiseError=>1,PrintError=>0}); 1541 $dbh{$name} = $dbh; 1542 ## Be explicit: this is okay for this particular script 1543 $dbh->{AutoCommit} = 1; 1544 $dbh->do("SET statement_timeout = 0"); 1545 $dbh->do("SET constraint_exclusion = 'on'"); 1546 $dbh->do("SET random_page_cost = 1.2"); 1547 return $dbh; 1548 1549} ## end of connect_database 1550 1551sub Error { 1552 my $msg = shift; 1553 my $line = (caller)[2]; 1554 &Header("Error"); 1555 print qq{<h3 class="error">Bucardo stats error</h3>\n}; 1556 print qq{<p class="error"><span class="error">$msg</span></p>\n}; 1557 &Footer(); 1558} 1559 1560__DATA__ 1561 1562## List each database you want to monitor here 1563## Format is NAME: VALUE 1564## DATABASE: Name of the database, will appear in the headers 1565## DSN: Connection information string. 1566## DBUSER: Who to connect as 1567## DBPASS: Password to connect with 1568## SINGLE: Optional, set to target database if that is the only one 1569## SKIP: Used for row counts, do not list anywhere 1570 1571DATABASE: SampleDB1 1572DSN: dbi:Pg:database=bucardo;port=5432;host=sample1.example.com 1573DBUSER: bucardo_readonly 1574DBPASS: foobar 1575SINGLE: otherdb 1576DAYSBACK: 2 1577DAYSBACKSYNC: 3 1578 1579DATABASE: OtherDB 1580DSN: dbi:Pg:database=bucardo;port=5432;host=sample2.example.com 1581DBUSER: bucardo_readonly 1582DBPASS: foobar 1583DAYSBACK: 5 1584DAYSBACKSYNC: 30 1585DAYSBACKDB: 30 1586