includes/db/DatabaseMysql.php

   1 <?php
   2 /**
   3  * This is the MySQL database abstraction layer.
   4  *
   5  * @file
   6  * @ingroup Database
   7  */
   8
   9 /**
  10  * Database abstraction object for mySQL
  11  * Inherit all methods and properties of Database::Database()
  12  *
  13  * @ingroup Database
  14  * @see Database
  15  */
  16 class DatabaseMysql extends DatabaseBase {
  17         function getType() {
  18                 return 'mysql';
  19         }
  20
  21         /*private*/ function doQuery( $sql ) {
  22                 if( $this->bufferResults() ) {
  23                         $ret = mysql_query( $sql, $this->mConn );
  24                 } else {
  25                         $ret = mysql_unbuffered_query( $sql, $this->mConn );
  26                 }
  27                 return $ret;
  28         }
  29
  30         function open( $server, $user, $password, $dbName ) {
  31                 global $wgAllDBsAreLocalhost;
  32                 wfProfileIn( __METHOD__ );
  33
  34                 # Load mysql.so if we don't have it
  35                 wfDl( 'mysql' );
  36
  37                 # Fail now
  38                 # Otherwise we get a suppressed fatal error, which is very hard to track down
  39                 if ( !function_exists( 'mysql_connect' ) ) {
  40                         throw new DBConnectionError( $this, "MySQL functions missing, have you compiled PHP with the --with-mysql option?\n" );
  41                 }
  42
  43                 # Debugging hack -- fake cluster
  44                 if ( $wgAllDBsAreLocalhost ) {
  45                         $realServer = 'localhost';
  46                 } else {
  47                         $realServer = $server;
  48                 }
  49                 $this->close();
  50                 $this->mServer = $server;
  51                 $this->mUser = $user;
  52                 $this->mPassword = $password;
  53                 $this->mDBname = $dbName;
  54
  55                 wfProfileIn("dbconnect-$server");
  56
  57                 # The kernel's default SYN retransmission period is far too slow for us,
  58                 # so we use a short timeout plus a manual retry. Retrying means that a small
  59                 # but finite rate of SYN packet loss won't cause user-visible errors.
  60                 $this->mConn = false;
  61                 if ( ini_get( 'mysql.connect_timeout' ) <= 3 ) {
  62                         $numAttempts = 2;
  63                 } else {
  64                         $numAttempts = 1;
  65                 }
  66                 $this->installErrorHandler();
  67                 for ( $i = 0; $i < $numAttempts && !$this->mConn; $i++ ) {
  68                         if ( $i > 1 ) {
  69                                 usleep( 1000 );
  70                         }
  71                         if ( $this->mFlags & DBO_PERSISTENT ) {
  72                                 $this->mConn = mysql_pconnect( $realServer, $user, $password );
  73                         } else {
  74                                 # Create a new connection...
  75                                 $this->mConn = mysql_connect( $realServer, $user, $password, true );
  76                         }
  77                         #if ( $this->mConn === false ) {
  78                                 #$iplus = $i + 1;
  79                                 #wfLogDBError("Connect loop error $iplus of $max ($server): " . mysql_errno() . " - " . mysql_error()."\n");
  80                         #}
  81                 }
  82                 $phpError = $this->restoreErrorHandler();
  83                 # Always log connection errors
  84                 if ( !$this->mConn ) {
  85                         $error = $this->lastError();
  86                         if ( !$error ) {
  87                                 $error = $phpError;
  88                         }
  89                         wfLogDBError( "Error connecting to {$this->mServer}: $error\n" );
  90                         wfDebug( "DB connection error\n" );
  91                         wfDebug( "Server: $server, User: $user, Password: " .
  92                                 substr( $password, 0, 3 ) . "..., error: " . mysql_error() . "\n" );
  93                         $success = false;
  94                 }
  95
  96                 wfProfileOut("dbconnect-$server");
  97
  98                 if ( $dbName != '' && $this->mConn !== false ) {
  99                         $success = @/**/mysql_select_db( $dbName, $this->mConn );
 100                         if ( !$success ) {
 101                                 $error = "Error selecting database $dbName on server {$this->mServer} " .
 102                                         "from client host " . wfHostname() . "\n";
 103                                 wfLogDBError(" Error selecting database $dbName on server {$this->mServer} \n");
 104                                 wfDebug( $error );
 105                         }
 106                 } else {
 107                         # Delay USE query
 108                         $success = (bool)$this->mConn;
 109                 }
 110
 111                 if ( $success ) {
 112                         $version = $this->getServerVersion();
 113                         if ( version_compare( $version, '4.1' ) >= 0 ) {
 114                                 // Tell the server we're communicating with it in UTF-8.
 115                                 // This may engage various charset conversions.
 116                                 global $wgDBmysql5;
 117                                 if( $wgDBmysql5 ) {
 118                                         $this->query( 'SET NAMES utf8', __METHOD__ );
 119                                 } else {
 120                                         $this->query( 'SET NAMES binary', __METHOD__ );
 121                                 }
 122                                 // Set SQL mode, default is turning them all off, can be overridden or skipped with null
 123                                 global $wgSQLMode;
 124                                 if ( is_string( $wgSQLMode ) ) {
 125                                         $mode = $this->addQuotes( $wgSQLMode );
 126                                         $this->query( "SET sql_mode = $mode", __METHOD__ );
 127                                 }
 128                         }
 129
 130                         // Turn off strict mode if it is on
 131                 } else {
 132                         $this->reportConnectionError( $phpError );
 133                 }
 134
 135                 $this->mOpened = $success;
 136                 wfProfileOut( __METHOD__ );
 137                 return $success;
 138         }
 139
 140         function close() {
 141                 $this->mOpened = false;
 142                 if ( $this->mConn ) {
 143                         if ( $this->trxLevel() ) {
 144                                 $this->commit();
 145                         }
 146                         return mysql_close( $this->mConn );
 147                 } else {
 148                         return true;
 149                 }
 150         }
 151
 152         function freeResult( $res ) {
 153                 if ( $res instanceof ResultWrapper ) {
 154                         $res = $res->result;
 155                 }
 156                 if ( !@/**/mysql_free_result( $res ) ) {
 157                         throw new DBUnexpectedError( $this, "Unable to free MySQL result" );
 158                 }
 159         }
 160
 161         function fetchObject( $res ) {
 162                 if ( $res instanceof ResultWrapper ) {
 163                         $res = $res->result;
 164                 }
 165                 @/**/$row = mysql_fetch_object( $res );
 166                 if( $this->lastErrno() ) {
 167                         throw new DBUnexpectedError( $this, 'Error in fetchObject(): ' . htmlspecialchars( $this->lastError() ) );
 168                 }
 169                 return $row;
 170         }
 171
 172         function fetchRow( $res ) {
 173                 if ( $res instanceof ResultWrapper ) {
 174                         $res = $res->result;
 175                 }
 176                 @/**/$row = mysql_fetch_array( $res );
 177                 if ( $this->lastErrno() ) {
 178                         throw new DBUnexpectedError( $this, 'Error in fetchRow(): ' . htmlspecialchars( $this->lastError() ) );
 179                 }
 180                 return $row;
 181         }
 182
 183         function numRows( $res ) {
 184                 if ( $res instanceof ResultWrapper ) {
 185                         $res = $res->result;
 186                 }
 187                 @/**/$n = mysql_num_rows( $res );
 188                 if( $this->lastErrno() ) {
 189                         throw new DBUnexpectedError( $this, 'Error in numRows(): ' . htmlspecialchars( $this->lastError() ) );
 190                 }
 191                 return $n;
 192         }
 193
 194         function numFields( $res ) {
 195                 if ( $res instanceof ResultWrapper ) {
 196                         $res = $res->result;
 197                 }
 198                 return mysql_num_fields( $res );
 199         }
 200
 201         function fieldName( $res, $n ) {
 202                 if ( $res instanceof ResultWrapper ) {
 203                         $res = $res->result;
 204                 }
 205                 return mysql_field_name( $res, $n );
 206         }
 207
 208         function insertId() { return mysql_insert_id( $this->mConn ); }
 209
 210         function dataSeek( $res, $row ) {
 211                 if ( $res instanceof ResultWrapper ) {
 212                         $res = $res->result;
 213                 }
 214                 return mysql_data_seek( $res, $row );
 215         }
 216
 217         function lastErrno() {
 218                 if ( $this->mConn ) {
 219                         return mysql_errno( $this->mConn );
 220                 } else {
 221                         return mysql_errno();
 222                 }
 223         }
 224
 225         function lastError() {
 226                 if ( $this->mConn ) {
 227                         # Even if it's non-zero, it can still be invalid
 228                         wfSuppressWarnings();
 229                         $error = mysql_error( $this->mConn );
 230                         if ( !$error ) {
 231                                 $error = mysql_error();
 232                         }
 233                         wfRestoreWarnings();
 234                 } else {
 235                         $error = mysql_error();
 236                 }
 237                 if( $error ) {
 238                         $error .= ' (' . $this->mServer . ')';
 239                 }
 240                 return $error;
 241         }
 242
 243         function affectedRows() { return mysql_affected_rows( $this->mConn ); }
 244
 245         /**
 246          * Estimate rows in dataset
 247          * Returns estimated count, based on EXPLAIN output
 248          * Takes same arguments as Database::select()
 249          */
 250         public function estimateRowCount( $table, $vars='*', $conds='', $fname = 'DatabaseMysql::estimateRowCount', $options = array() ) {
 251                 $options['EXPLAIN'] = true;
 252                 $res = $this->select( $table, $vars, $conds, $fname, $options );
 253                 if ( $res === false ) {
 254                         return false;
 255                 }
 256                 if ( !$this->numRows( $res ) ) {
 257                         return 0;
 258                 }
 259
 260                 $rows = 1;
 261                 foreach ( $res as $plan ) {
 262                         $rows *= $plan->rows > 0 ? $plan->rows : 1; // avoid resetting to zero
 263                 }
 264                 return $rows;
 265         }
 266
 267         function fieldInfo( $table, $field ) {
 268                 $table = $this->tableName( $table );
 269                 $res = $this->query( "SELECT * FROM $table LIMIT 1", __METHOD__, true );
 270                 if ( !$res ) {
 271                         return false;
 272                 }
 273                 $n = mysql_num_fields( $res->result );
 274                 for( $i = 0; $i < $n; $i++ ) {
 275                         $meta = mysql_fetch_field( $res->result, $i );
 276                         if( $field == $meta->name ) {
 277                                 return new MySQLField($meta);
 278                         }
 279                 }
 280                 return false;
 281         }
 282
 283         /**
 284          * Get information about an index into an object
 285          * Returns false if the index does not exist
 286          */
 287         function indexInfo( $table, $index, $fname = 'DatabaseMysql::indexInfo' ) {
 288                 # SHOW INDEX works in MySQL 3.23.58, but SHOW INDEXES does not.
 289                 # SHOW INDEX should work for 3.x and up:
 290                 # http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html
 291                 $table = $this->tableName( $table );
 292                 $index = $this->indexName( $index );
 293                 $sql = 'SHOW INDEX FROM ' . $table;
 294                 $res = $this->query( $sql, $fname );
 295
 296                 if ( !$res ) {
 297                         return null;
 298                 }
 299
 300                 $result = array();
 301
 302                 foreach ( $res as $row ) {
 303                         if ( $row->Key_name == $index ) {
 304                                 $result[] = $row;
 305                         }
 306                 }
 307
 308                 return empty( $result ) ? false : $result;
 309         }
 310
 311         function selectDB( $db ) {
 312                 $this->mDBname = $db;
 313                 return mysql_select_db( $db, $this->mConn );
 314         }
 315
 316         function strencode( $s ) {
 317                 $sQuoted = mysql_real_escape_string( $s, $this->mConn );
 318
 319                 if($sQuoted === false) {
 320                         $this->ping();
 321                         $sQuoted = mysql_real_escape_string( $s, $this->mConn );
 322                 }
 323                 return $sQuoted;
 324         }
 325
 326         function ping() {
 327                 $ping = mysql_ping( $this->mConn );
 328                 if ( $ping ) {
 329                         return true;
 330                 }
 331
 332                 mysql_close( $this->mConn );
 333                 $this->mOpened = false;
 334                 $this->mConn = false;
 335                 $this->open( $this->mServer, $this->mUser, $this->mPassword, $this->mDBname );
 336                 return true;
 337         }
 338
 339         /**
 340          * Returns slave lag.
 341          * At the moment, this will only work if the DB user has the PROCESS privilege
 342          * @result int
 343          */
 344         function getLag() {
 345                 if ( !is_null( $this->mFakeSlaveLag ) ) {
 346                         wfDebug( "getLag: fake slave lagged {$this->mFakeSlaveLag} seconds\n" );
 347                         return $this->mFakeSlaveLag;
 348                 }
 349                 $res = $this->query( 'SHOW PROCESSLIST', __METHOD__ );
 350                 if( !$res ) {
 351                         return false;
 352                 }
 353                 # Find slave SQL thread
 354                 foreach( $res as $row ) {
 355                         /* This should work for most situations - when default db
 356                          * for thread is not specified, it had no events executed,
 357                          * and therefore it doesn't know yet how lagged it is.
 358                          *
 359                          * Relay log I/O thread does not select databases.
 360                          */
 361                         if ( $row->User == 'system user' &&
 362                                 $row->State != 'Waiting for master to send event' &&
 363                                 $row->State != 'Connecting to master' &&
 364                                 $row->State != 'Queueing master event to the relay log' &&
 365                                 $row->State != 'Waiting for master update' &&
 366                                 $row->State != 'Requesting binlog dump' &&
 367                                 $row->State != 'Waiting to reconnect after a failed master event read' &&
 368                                 $row->State != 'Reconnecting after a failed master event read' &&
 369                                 $row->State != 'Registering slave on master'
 370                                 ) {
 371                                 # This is it, return the time (except -ve)
 372                                 if ( $row->Time > 0x7fffffff ) {
 373                                         return false;
 374                                 } else {
 375                                         return $row->Time;
 376                                 }
 377                         }
 378                 }
 379                 return false;
 380         }
 381
 382         function getServerVersion() {
 383                 return mysql_get_server_info( $this->mConn );
 384         }
 385
 386         function useIndexClause( $index ) {
 387                 return "FORCE INDEX (" . $this->indexName( $index ) . ")";
 388         }
 389
 390         function lowPriorityOption() {
 391                 return 'LOW_PRIORITY';
 392         }
 393
 394         public static function getSoftwareLink() {
 395                 return '[http://www.mysql.com/ MySQL]';
 396         }
 397
 398         function standardSelectDistinct() {
 399                 return false;
 400         }
 401
 402         public function setTimeout( $timeout ) {
 403                 $this->query( "SET net_read_timeout=$timeout" );
 404                 $this->query( "SET net_write_timeout=$timeout" );
 405         }
 406
 407         public function lock( $lockName, $method, $timeout = 5 ) {
 408                 $lockName = $this->addQuotes( $lockName );
 409                 $result = $this->query( "SELECT GET_LOCK($lockName, $timeout) AS lockstatus", $method );
 410                 $row = $this->fetchObject( $result );
 411
 412                 if( $row->lockstatus == 1 ) {
 413                         return true;
 414                 } else {
 415                         wfDebug( __METHOD__." failed to acquire lock\n" );
 416                         return false;
 417                 }
 418         }
 419
 420         /**
 421          * FROM MYSQL DOCS: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_release-lock
 422          */
 423         public function unlock( $lockName, $method ) {
 424                 $lockName = $this->addQuotes( $lockName );
 425                 $result = $this->query( "SELECT RELEASE_LOCK($lockName) as lockstatus", $method );
 426                 $row = $this->fetchObject( $result );
 427                 return $row->lockstatus;
 428         }
 429
 430         public function lockTables( $read, $write, $method, $lowPriority = true ) {
 431                 $items = array();
 432
 433                 foreach( $write as $table ) {
 434                         $tbl = $this->tableName( $table ) .
 435                                         ( $lowPriority ? ' LOW_PRIORITY' : '' ) .
 436                                         ' WRITE';
 437                         $items[] = $tbl;
 438                 }
 439                 foreach( $read as $table ) {
 440                         $items[] = $this->tableName( $table ) . ' READ';
 441                 }
 442                 $sql = "LOCK TABLES " . implode( ',', $items );
 443                 $this->query( $sql, $method );
 444         }
 445
 446         public function unlockTables( $method ) {
 447                 $this->query( "UNLOCK TABLES", $method );
 448         }
 449
 450         /**
 451          * Get search engine class. All subclasses of this
 452          * need to implement this if they wish to use searching.
 453          *
 454          * @return String
 455          */
 456         public function getSearchEngine() {
 457                 return 'SearchMySQL';
 458         }
 459
 460         public function setBigSelects( $value = true ) {
 461                 if ( $value === 'default' ) {
 462                         if ( $this->mDefaultBigSelects === null ) {
 463                                 # Function hasn't been called before so it must already be set to the default
 464                                 return;
 465                         } else {
 466                                 $value = $this->mDefaultBigSelects;
 467                         }
 468                 } elseif ( $this->mDefaultBigSelects === null ) {
 469                         $this->mDefaultBigSelects = (bool)$this->selectField( false, '@@sql_big_selects' );
 470                 }
 471                 $encValue = $value ? '1' : '0';
 472                 $this->query( "SET sql_big_selects=$encValue", __METHOD__ );
 473         }
 474
 475         public function unixTimestamp( $field ) {
 476                 return "UNIX_TIMESTAMP($field)";
 477         }
 478
 479         /**
 480          * Determines if the last failure was due to a deadlock
 481          */
 482         function wasDeadlock() {
 483                 return $this->lastErrno() == 1213;
 484         }
 485
 486         /**
 487          * Determines if the last query error was something that should be dealt
 488          * with by pinging the connection and reissuing the query
 489          */
 490         function wasErrorReissuable() {
 491                 return $this->lastErrno() == 2013 || $this->lastErrno() == 2006;
 492         }
 493
 494         /**
 495          * Determines if the last failure was due to the database being read-only.
 496          */
 497         function wasReadOnlyError() {
 498                 return $this->lastErrno() == 1223 ||
 499                         ( $this->lastErrno() == 1290 && strpos( $this->lastError(), '--read-only' ) !== false );
 500         }
 501
 502         function duplicateTableStructure( $oldName, $newName, $temporary = false, $fname = 'DatabaseMysql::duplicateTableStructure' ) {
 503                 $tmp = $temporary ? 'TEMPORARY ' : '';
 504                 if ( strcmp( $this->getServerVersion(), '4.1' ) < 0 ) {
 505                         # Hack for MySQL versions < 4.1, which don't support
 506                         # "CREATE TABLE ... LIKE". Note that
 507                         # "CREATE TEMPORARY TABLE ... SELECT * FROM ... LIMIT 0"
 508                         # would not create the indexes we need....
 509                         #
 510                         # Note that we don't bother changing around the prefixes here be-
 511                         # cause we know we're using MySQL anyway.
 512
 513                         $res = $this->query( "SHOW CREATE TABLE $oldName" );
 514                         $row = $this->fetchRow( $res );
 515                         $oldQuery = $row[1];
 516                         $query = preg_replace( '/CREATE TABLE `(.*?)`/',
 517                                 "CREATE $tmp TABLE `$newName`", $oldQuery );
 518                         if ($oldQuery === $query) {
 519                                 # Couldn't do replacement
 520                                 throw new MWException( "could not create temporary table $newName" );
 521                         }
 522                 } else {
 523                         $query = "CREATE $tmp TABLE $newName (LIKE $oldName)";
 524                 }
 525                 $this->query( $query, $fname );
 526         }
 527
 528 }
 529
 530 /**
 531  * Legacy support: Database == DatabaseMysql
 532  */
 533 class Database extends DatabaseMysql {}
 534
 535 /**
 536  * Utility class.
 537  * @ingroup Database
 538  */
 539 class MySQLField implements Field {
 540         private $name, $tablename, $default, $max_length, $nullable,
 541                 $is_pk, $is_unique, $is_multiple, $is_key, $type;
 542
 543         function __construct ( $info ) {
 544                 $this->name = $info->name;
 545                 $this->tablename = $info->table;
 546                 $this->default = $info->def;
 547                 $this->max_length = $info->max_length;
 548                 $this->nullable = !$info->not_null;
 549                 $this->is_pk = $info->primary_key;
 550                 $this->is_unique = $info->unique_key;
 551                 $this->is_multiple = $info->multiple_key;
 552                 $this->is_key = ( $this->is_pk || $this->is_unique || $this->is_multiple );
 553                 $this->type = $info->type;
 554         }
 555
 556         function name() {
 557                 return $this->name;
 558         }
 559
 560         function tableName() {
 561                 return $this->tableName;
 562         }
 563
 564         function type() {
 565                 return $this->type;
 566         }
 567
 568         function isNullable() {
 569                 return $this->nullable;
 570         }
 571
 572         function defaultValue() {
 573                 return $this->default;
 574         }
 575
 576         function isKey() {
 577                 return $this->is_key;
 578         }
 579
 580         function isMultipleKey() {
 581                 return $this->is_multiple;
 582         }
 583 }
 584
 585 class MySQLMasterPos {
 586         var $file, $pos;
 587
 588         function __construct( $file, $pos ) {
 589                 $this->file = $file;
 590                 $this->pos = $pos;
 591         }
 592
 593         function __toString() {
 594                 return "{$this->file}/{$this->pos}";
 595         }
 596 }