cgi-bin/ljdb.pl

   1 #!/usr/bin/perl
   2 #
   3
   4 use strict;
   5 use lib "$ENV{LJHOME}/cgi-bin";
   6 use DBI::Role;
   7 use DBI;
   8
   9 # need ljconfig to set up database connection
  10 use LJ::Config;
  11 LJ::Config->load;
  12
  13 $LJ::DBIRole = new DBI::Role {
  14     'timeout' => sub {
  15         my ($dsn, $user, $pass, $role) = @_;
  16         return $LJ::MASTER_DB_TIMEOUT || 0 if $role && $role eq "master";
  17         return $LJ::DB_TIMEOUT;
  18     },
  19     'sources' => \%LJ::DBINFO,
  20     'default_db' => "livejournal",
  21     'time_check' => 60,
  22     'time_report' => \&LJ::dbtime_callback,
  23 };
  24
  25 package LJ::DB;
  26
  27 use Carp qw(croak);
  28
  29 # <LJFUNC>
  30 # name: LJ::DB::time_range_to_ids
  31 # des:  Performs a binary search on a table's primary id key looking
  32 #       for time boundaries as specified.  Returns the boundary ids
  33 #       that were found, effectively simulating a key on 'time' for
  34 #       the specified table.
  35 # info: This function shouldn't normally be used, but there are
  36 #       rare instances where it's useful.
  37 # args: opts
  38 # des-opts: A hashref of keys. Keys are:
  39 #           'table' - table name to query;
  40 #           'roles' - arrayref of db roles to use, in order. Defaults to ['slow'];
  41 #           'idcol' - name of 'id' primary key column.
  42 #           'timecol' - name of unixtime column to use for constraint;
  43 #           'starttime' - starting unixtime time of rows to match;
  44 #           'endtime' - ending unixtime of rows to match.
  45 # returns: startid, endid; id boundaries which should be used by
  46 #          the caller.
  47 # </LJFUNC>
  48
  49 sub time_range_to_ids {
  50     my %args = @_;
  51
  52     my $table     = delete $args{table}     or croak("no table arg");
  53     my $idcol     = delete $args{idcol}     or croak("no idcol arg");
  54     my $timecol   = delete $args{timecol}   or croak("no timecol arg");
  55     my $starttime = delete $args{starttime} or croak("no starttime arg");
  56     my $endtime   = delete $args{endtime}   or croak("no endtime arg");
  57     my $roles     = delete $args{roles};
  58     unless (ref $roles eq 'ARRAY' && @$roles) {
  59         $roles = [ 'slow' ];
  60     }
  61     croak("bogus args: " . join(",", keys %args))
  62         if %args;
  63
  64     my $db = LJ::get_dbh(@$roles)
  65         or die "unable to acquire db handle, roles=", join(",", @$roles);
  66
  67     my ($db_min_id, $db_max_id) = $db->selectrow_array
  68         ("SELECT MIN($idcol), MAX($idcol) FROM $table");
  69     die $db->errstr if $db->err;
  70     die "error finding min/max ids: $db_max_id < $db_min_id"
  71         if $db_max_id < $db_min_id;
  72
  73     # final output
  74     my ($startid, $endid);
  75     my $ct_max = 100;
  76
  77     foreach my $curr_ref ([$starttime => \$startid], [$endtime => \$endid]) {
  78         my ($want_time, $dest_ref) = @$curr_ref;
  79
  80         my ($min_id, $max_id) = ($db_min_id, $db_max_id);
  81
  82         my $curr_time = 0;
  83         my $last_time = 0;
  84
  85         my $ct = 0;
  86         while ($ct++ < $ct_max) {
  87             die "unable to find row after $ct tries" if $ct > 100;
  88
  89             my $curr_id = $min_id + int(($max_id - $min_id) / 2)+0;
  90
  91             my $sql =
  92                 "SELECT $idcol, $timecol FROM $table " .
  93                 "WHERE $idcol>=$curr_id ORDER BY 1 LIMIT 1";
  94
  95             $last_time = $curr_time;
  96             ($curr_id, $curr_time) = $db->selectrow_array($sql);
  97             die $db->errstr if $db->err;
  98
  99             # stop condition, two trigger cases:
 100             #  * we've found exactly the time we want
 101             #  * we're still narrowing but not finding rows in between, stop here with
 102             #    the current time being just short of what we were trying to find
 103             if ($curr_time == $want_time || $curr_time == $last_time) {
 104
 105                 # if we never modified the max id, then we
 106                 # have searched to the end without finding
 107                 # what we were looking for
 108                 if ($max_id == $db_max_id && $curr_time <= $want_time) {
 109                     $$dest_ref = $max_id;
 110
 111                 # same for min id
 112                 } elsif ($min_id == $db_min_id && $curr_time >= $want_time) {
 113                     $$dest_ref = $min_id;
 114
 115                 } else {
 116                     $$dest_ref = $curr_id;
 117                 }
 118                 last;
 119             }
 120
 121             # need to traverse into the larger half
 122             if ($curr_time < $want_time) {
 123                 $min_id = $curr_id;
 124                 next;
 125             }
 126
 127             # need to traverse into the smaller half
 128             if ($curr_time > $want_time) {
 129                 $max_id = $curr_id;
 130                 next;
 131             }
 132         }
 133     }
 134
 135     return ($startid, $endid);
 136 }
 137
 138 sub _connection_options {
 139     my $options = {
 140         'AutoCommit' => 1,
 141         'RaiseError' => 0,
 142         'PrintError' => 0,
 143     };
 144
 145     if ( $LJ::IS_DEV_SERVER ) {
 146         $options->{'RaiseError'} = 1;
 147     }
 148     else {
 149         $options->{'PrintError'} = 1;
 150     }
 151
 152     return { 'connection_opts' => $options };
 153 }
 154
 155 sub dbh_by_role {
 156     return $LJ::DBIRole->get_dbh( _connection_options(), @_ );
 157 }
 158
 159 sub dbh_by_name {
 160     my $name = shift;
 161     my $dbh = dbh_by_role("master")
 162         or die "Couldn't contact master to find name of '$name'\n";
 163
 164     my $fdsn = $dbh->selectrow_array("SELECT fdsn FROM dbinfo WHERE name=?", undef, $name);
 165     die "No fdsn found for db name '$name'\n" unless $fdsn;
 166
 167     return $LJ::DBIRole->get_dbh_conn( _connection_options(), $fdsn );
 168
 169 }
 170
 171 sub dbh_by_fdsn {
 172     my $fdsn = shift;
 173     return $LJ::DBIRole->get_dbh_conn( _connection_options(), $fdsn );
 174 }
 175
 176 sub root_dbh_by_name {
 177     my $name = shift;
 178     my $dbh = dbh_by_role("master")
 179         or die "Couldn't contact master to find name of '$name'";
 180
 181     my $fdsn = $dbh->selectrow_array("SELECT rootfdsn FROM dbinfo WHERE name=?", undef, $name);
 182     die "No rootfdsn found for db name '$name'\n" unless $fdsn;
 183
 184     return $LJ::DBIRole->get_dbh_conn( _connection_options(), $fdsn );
 185 }
 186
 187 sub user_cluster_details {
 188     my $name = shift;
 189     my $dbh = dbh_by_role("master") or die;
 190
 191     my $role = $dbh->selectrow_array("SELECT role FROM dbweights w, dbinfo i WHERE i.name=? AND i.dbid=w.dbid",
 192                                      undef, $name);
 193     return () unless $role && $role =~ /^cluster(\d+)([ab])$/;
 194     return ($1, $2);
 195 }
 196
 197 package LJ;
 198
 199 use Carp qw(croak);
 200 use LJ::User qw//;
 201
 202 # when calling a supported function (currently: LJ::load_user() or LJ::load_userid*), LJ::SMS::load_mapping()
 203 # ignores in-process request cache, memcache, and selects directly
 204 # from the global master
 205 #
 206 # called as: require_master(sub { block })
 207 sub require_master {
 208     my $callback = shift;
 209     croak "invalid code ref passed to require_master"
 210         unless ref $callback eq 'CODE';
 211
 212     # run code in the block with local var set
 213     local $LJ::_PRAGMA_FORCE_MASTER = 1;
 214     return $callback->();
 215 }
 216
 217 sub no_cache {
 218     my $sb = shift;
 219     local $LJ::MemCache::GET_DISABLED = 1;
 220     return $sb->();
 221 }
 222
 223 sub cond_no_cache {
 224     my ($cond, $sb) = @_;
 225     return no_cache($sb) if $cond;
 226     return $sb->();
 227 }
 228
 229 sub no_ml_cache {
 230     my $sb = shift;
 231     local $LJ::NO_ML_CACHE = 1;
 232     return $sb->();
 233 }
 234
 235 # <LJFUNC>
 236 # name: LJ::get_dbh
 237 # class: db
 238 # des: Given one or more roles, returns a database handle.
 239 # info:
 240 # args:
 241 # des-:
 242 # returns:
 243 # </LJFUNC>
 244 sub get_dbh {
 245     my $opts = ref $_[0] eq "HASH" ? shift : {};
 246
 247     unless (exists $opts->{'max_repl_lag'}) {
 248         # for slave or cluster<n>slave roles, don't allow lag
 249         if ($_[0] =~ /slave$/) {
 250             $opts->{'max_repl_lag'} = $LJ::MAX_REPL_LAG || 100_000;
 251         }
 252     }
 253
 254     if ($LJ::DEBUG{'get_dbh'} && $_[0] ne "logs") {
 255         my $errmsg = "get_dbh(@_) at \n";
 256         my $i = 0;
 257         while (my ($p, $f, $l) = caller($i++)) {
 258             next if $i > 3;
 259             $errmsg .= "  $p, $f, $l\n";
 260         }
 261         warn $errmsg;
 262     }
 263
 264     my $nodb = sub {
 265         my $roles = shift;
 266         my $err = LJ::errobj("Database::Unavailable",
 267                              roles => $roles);
 268         return $err->cond_throw;
 269     };
 270
 271     foreach my $role (@_) {
 272         # let site admin turn off global master write access during
 273         # maintenance
 274         return $nodb->([@_]) if $LJ::DISABLE_MASTER && $role eq "master";
 275         my $db = LJ::get_dbirole_dbh($opts, $role);
 276         return $db if $db;
 277     }
 278     return $nodb->([@_]);
 279 }
 280
 281 sub get_db_reader {
 282     return LJ::get_dbh("master") if $LJ::_PRAGMA_FORCE_MASTER;
 283     return LJ::get_dbh("slave", "master");
 284 }
 285
 286 sub get_db_writer {
 287     return LJ::get_dbh("master");
 288 }
 289
 290 # Use the uniq DB if available, otherwise fall back to the main database
 291 # Force the use of the uniq DB by setting $LJ::_PRAGMA_FORCE_UNIQ
 292 sub get_uniq_db_reader {
 293     if ($LJ::_PRAGMA_FORCE_UNIQ) {
 294         return $LJ::_PRAGMA_FORCE_MASTER ?
 295             LJ::get_dbh("uniq_master") :
 296             LJ::get_dbh("uniq_slave", "uniq_master");
 297     }
 298     return LJ::get_dbh("uniq_master", "master") if $LJ::_PRAGMA_FORCE_MASTER;
 299     return LJ::get_dbh("uniq_slave", "uniq_master", "slave", "master");
 300 }
 301
 302 sub get_uniq_db_writer {
 303     return LJ::get_dbh("uniq_master") if $LJ::_PRAGMA_FORCE_UNIQ;
 304     return LJ::get_dbh("uniq_master", "master");
 305 }
 306
 307 # <LJFUNC>
 308 # name: LJ::get_cluster_reader
 309 # class: db
 310 # des: Returns a cluster slave for a user, or cluster master if no slaves exist.
 311 # args: uarg
 312 # des-uarg: Either a userid scalar or a user object.
 313 # returns: DB handle.  Or undef if all dbs are unavailable.
 314 # </LJFUNC>
 315 sub get_cluster_reader
 316 {
 317     return LJ::get_cluster_master(@_);
 318 }
 319
 320 # <LJFUNC>
 321 # name: LJ::get_cluster_def_reader
 322 # class: db
 323 # des: Returns a definitive cluster reader for a given user, used
 324 #      when the caller wants the master handle, but will only
 325 #      use it to read.
 326 # args: uarg
 327 # des-uarg: Either a clusterid scalar or a user object.
 328 # returns: DB handle.  Or undef if definitive reader is unavailable.
 329 # </LJFUNC>
 330 sub get_cluster_def_reader
 331 {
 332     return LJ::get_cluster_master(@_);
 333 }
 334
 335 # <LJFUNC>
 336 # name: LJ::get_cluster_master
 337 # class: db
 338 # des: Returns a cluster master for a given user, used when the caller
 339 #      might use it to do a write (insert/delete/update/etc...)
 340 # args: uarg
 341 # des-uarg: Either a clusterid scalar or a user object.
 342 # returns: DB handle.  Or undef if master is unavailable.
 343 # </LJFUNC>
 344 sub get_cluster_master
 345 {
 346     my @dbh_opts = scalar(@_) == 2 ? (shift @_) : ();
 347     my $arg = shift;
 348     my $id = LJ::isu($arg) ? $arg->{'clusterid'} : $arg;
 349     return undef unless ($id);
 350     return undef if $LJ::READONLY_CLUSTER{$id};
 351     return LJ::get_dbh(@dbh_opts, LJ::master_role($id));
 352 }
 353
 354 ## input: cluster id
 355 ## ouptut: hashiref like {active => 'a', dead => 'b' }
 356 sub _cluster_config {
 357     my $cid = shift;
 358
 359     my $block_id = 'cluster_config.rc';
 360     my $block = LJ::ExtBlock->load_by_id($block_id, {cache_valid => 15});
 361     return ($block && $block->data->{$cid}) ? $block->data->{$cid} : {};
 362 }
 363
 364
 365 # input: LJ::User object or cluster id
 366 # output: the DBI::Role role name of a cluster master (like 'cluster10a')
 367 sub master_role {
 368     my $arg = shift;
 369
 370     my $cid = LJ::isu($arg) ? $arg->{'clusterid'} : $arg;
 371     if ($LJ::IS_DEV_SERVER) {
 372         return "cluster${cid}";
 373     } else {
 374         my $ab = _cluster_config($cid)->{'active'} || 'a';
 375         return "cluster${cid}${ab}";
 376     }
 377 }
 378
 379 # input: LJ::User object or cluster id
 380 # output: role name of inactive server, or undef if inactive server is dead
 381 sub get_inactive_role {
 382     my $arg = shift;
 383
 384     my $cid = LJ::isu($arg) ? $arg->{'clusterid'} : $arg;
 385     if ($LJ::IS_DEV_SERVER) {
 386         return "cluster${cid}";
 387     } else {
 388         my $c = _cluster_config($cid);
 389         my $ab = ($c && $c->{'active'} && $c->{'active'} eq 'b') ? 'a' : 'b';
 390         if ($c && $c->{'dead'} && $c->{'dead'} eq $ab) {
 391             ## oops, inactive is dead
 392             return;
 393         } else {
 394             return "cluster${cid}${ab}";
 395         }
 396     }
 397 }
 398
 399 # <LJFUNC>
 400 # name: LJ::get_dbirole_dbh
 401 # class: db
 402 # des: Internal function for get_dbh(). Uses the DBIRole to fetch a dbh, with
 403 #      hooks into db stats-generation if that's turned on.
 404 # info:
 405 # args: opts, role
 406 # des-opts: A hashref of options.
 407 # des-role: The database role.
 408 # returns: A dbh.
 409 # </LJFUNC>
 410 sub get_dbirole_dbh {
 411     my $dbh = $LJ::DBIRole->get_dbh( LJ::DB::_connection_options(), @_ )
 412         or return undef;
 413
 414     if ( $LJ::DB_LOG_HOST && $LJ::HAVE_DBI_PROFILE ) {
 415         $LJ::DB_REPORT_HANDLES{ $dbh->{Name} } = $dbh;
 416
 417         # :TODO: Explain magic number
 418         $dbh->{Profile} ||= "2/DBI::Profile";
 419
 420         # And turn off useless (to us) on_destroy() reports, too.
 421         undef $DBI::Profile::ON_DESTROY_DUMP;
 422     }
 423
 424     return $dbh;
 425 }
 426
 427 # <LJFUNC>
 428 # name: LJ::get_lock
 429 # des: get a MySQL lock on a given key/dbrole combination.
 430 # returns: undef if called improperly, true on success, die() on failure
 431 # args: db, dbrole, lockname, wait_time?
 432 # des-dbrole: the role this lock should be gotten on, either 'global' or 'user'.
 433 # des-lockname: the name to be used for this lock.
 434 # des-wait_time: an optional timeout argument, defaults to 10 seconds.
 435 # </LJFUNC>
 436 sub get_lock
 437 {
 438     my ($db, $dbrole, $lockname, $wait_time) = @_;
 439     return undef unless $db && $lockname;
 440     return undef unless $dbrole eq 'global' || $dbrole eq 'user';
 441
 442     my $curr_sub = join(", ", ((caller 1)[0..3])); # caller of current sub
 443
 444     # die if somebody already has a lock
 445     use Carp qw/cluck confess/;
 446     confess "LOCK ERROR: can't get lock from\n$curr_sub\nbecause it's already taken from\n$LJ::LOCK_OUT{$dbrole}\n"
 447         if exists $LJ::LOCK_OUT{$dbrole};
 448
 449     # get a lock from mysql
 450     $wait_time ||= 10;
 451     # NOTE: we have to get the result of GET_LOCK, so do NOT use $db->do()
 452     my ($got) = $db->selectrow_array( 'SELECT GET_LOCK(?,?)', undef, $lockname, $wait_time );
 453     return undef unless $got;
 454
 455     # successfully got a lock
 456     $LJ::LOCK_OUT{$dbrole} = $curr_sub;
 457     return 1;
 458 }
 459
 460 # <LJFUNC>
 461 # name: LJ::may_lock
 462 # des: see if we <strong>could</strong> get a MySQL lock on
 463 #       a given key/dbrole combination, but don't actually get it.
 464 # returns: undef if called improperly, true on success, die() on failure
 465 # args: db, dbrole
 466 # des-dbrole: the role this lock should be gotten on, either 'global' or 'user'.
 467 # </LJFUNC>
 468 sub may_lock
 469 {
 470     my ($db, $dbrole) = @_;
 471     return undef unless $db && ($dbrole eq 'global' || $dbrole eq 'user');
 472
 473     # die if somebody already has a lock
 474     if ($LJ::LOCK_OUT{$dbrole}) {
 475         my $curr_sub = (caller 1)[3]; # caller of current sub
 476         die "LOCK ERROR: $curr_sub; can't get lock from $LJ::LOCK_OUT{$dbrole}\n";
 477     }
 478
 479     # see if a lock is already out
 480     return undef if exists $LJ::LOCK_OUT{$dbrole};
 481
 482     return 1;
 483 }
 484
 485 # <LJFUNC>
 486 # name: LJ::release_lock
 487 # des: release a MySQL lock on a given key/dbrole combination.
 488 # returns: undef if called improperly, true on success, die() on failure
 489 # args: db, dbrole, lockname
 490 # des-dbrole: role on which to get this lock, either 'global' or 'user'.
 491 # des-lockname: the name to be used for this lock
 492 # </LJFUNC>
 493 sub release_lock
 494 {
 495     my ($db, $dbrole, $lockname) = @_;
 496     return undef unless $db && $lockname;
 497     return undef unless $dbrole eq 'global' || $dbrole eq 'user';
 498
 499     # get a lock from mysql
 500     $db->do("SELECT RELEASE_LOCK(?)", undef, $lockname);
 501     delete $LJ::LOCK_OUT{$dbrole};
 502
 503     return 1;
 504 }
 505
 506 sub lock_taken {
 507     my ( $db, $dbrole, $lockname ) = @_;
 508
 509     return unless $db && $lockname;
 510     return unless $dbrole eq 'global' || $dbrole eq 'user';
 511
 512     my ($connid) = $db->selectrow_array( 'SELECT IS_USED_LOCK(?)',
 513         undef, $lockname );
 514
 515     return $connid ? 1 : 0;
 516 }
 517
 518 sub lock_free {
 519     my ( $db, $dbrole, $lockname ) = @_;
 520
 521     return unless $db && $lockname;
 522     return unless $dbrole eq 'global' || $dbrole eq 'user';
 523
 524     return ! lock_taken( $db, $dbrole, $lockname );
 525 }
 526
 527 # <LJFUNC>
 528 # name: LJ::disconnect_dbs
 529 # des: Clear cached DB handles
 530 # </LJFUNC>
 531 sub disconnect_dbs {
 532     # clear cached handles
 533     $LJ::DBIRole->disconnect_all( { except => [qw(logs)] });
 534 }
 535
 536 # <LJFUNC>
 537 # name: LJ::use_diff_db
 538 # class:
 539 # des: given two DB roles, returns true only if it is certain the two roles are
 540 #      served by different database servers.
 541 # info: This is useful for, say, the moveusercluster script: You would not want
 542 #       to select something from one DB, copy it into another, and then delete it from the
 543 #       source if they were both the same machine.
 544 # args:
 545 # des-:
 546 # returns:
 547 # </LJFUNC>
 548 sub use_diff_db {
 549     $LJ::DBIRole->use_diff_db(@_);
 550 }
 551
 552 # to be called as &nodb; (so this function sees caller's @_)
 553 sub nodb {
 554     shift @_ if
 555         ref $_[0] eq "LJ::DBSet" || ref $_[0] eq "DBI::db" ||
 556         ref $_[0] eq "Apache::DBI::db";
 557 }
 558
 559 sub dbtime_callback {
 560     my ($dsn, $dbtime, $time) = @_;
 561     my $diff = abs($dbtime - $time);
 562     if ($diff > 2) {
 563         $dsn =~ /host=([^:\;\|]*)/;
 564         my $db = $1;
 565         print STDERR "Clock skew of $diff seconds between web($LJ::SERVER_NAME) and db($db)\n";
 566     }
 567 }
 568
 569 sub foreach_cluster {
 570     my $coderef = shift;
 571     my $opts = shift || {};
 572
 573     foreach my $cluster_id (@LJ::CLUSTERS) {
 574         if ($opts->{active}) {
 575             my $dbh = LJ::get_cluster_master($cluster_id);
 576             $coderef->($cluster_id, $dbh);
 577         } else {
 578             my $dbr = LJ::DBUtil->get_inactive_db($cluster_id, $opts->{verbose});
 579             $coderef->($cluster_id, $dbr);
 580         }
 581     }
 582 }
 583
 584
 585 sub isdb { return ref $_[0] && (ref $_[0] eq "DBI::db" ||
 586                                 ref $_[0] eq "Apache::DBI::db"); }
 587
 588
 589 sub bindstr { return join(', ', map { '?' } @_); }
 590
 591 package LJ::Error::Database::Unavailable;
 592 sub fields { qw(roles) }  # arrayref of roles requested
 593
 594 sub as_string {
 595     my $self = shift;
 596     my $ct = @{$self->field('roles')};
 597     my $clist = join(", ", @{$self->field('roles')});
 598     return $ct == 1 ?
 599         "Database unavailable for role $clist" :
 600         "Database unavailable for roles $clist";
 601 }
 602
 603
 604 package LJ::Error::Database::Failure;
 605 sub fields { qw(db) }
 606
 607 sub user_caused { 0 }
 608
 609 sub as_string {
 610     my $self = shift;
 611     my $code = $self->err;
 612     my $txt  = $self->errstr;
 613     return "Database error code $code: $txt";
 614 }
 615
 616 sub err {
 617     my $self = shift;
 618     return $self->field('db')->err;
 619 }
 620
 621 sub errstr {
 622     my $self = shift;
 623     return $self->field('db')->errstr;
 624 }
 625
 626 1;