3 * This is the MySQL database abstraction layer.
5 * This program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License along
16 * with this program; if not, write to the Free Software Foundation, Inc.,
17 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18 * http://www.gnu.org/copyleft/gpl.html
25 * Database abstraction object for mySQL
26 * Inherit all methods and properties of Database::Database()
31 class DatabaseMysql
extends DatabaseBase
{
44 protected function doQuery( $sql ) {
45 if( $this->bufferResults() ) {
46 $ret = mysql_query( $sql, $this->mConn
);
48 $ret = mysql_unbuffered_query( $sql, $this->mConn
);
54 * @param $server string
56 * @param $password string
57 * @param $dbName string
59 * @throws DBConnectionError
61 function open( $server, $user, $password, $dbName ) {
62 global $wgAllDBsAreLocalhost;
63 wfProfileIn( __METHOD__
);
65 # Load mysql.so if we don't have it
69 # Otherwise we get a suppressed fatal error, which is very hard to track down
70 if ( !function_exists( 'mysql_connect' ) ) {
71 throw new DBConnectionError( $this, "MySQL functions missing, have you compiled PHP with the --with-mysql option?\n" );
74 # Debugging hack -- fake cluster
75 if ( $wgAllDBsAreLocalhost ) {
76 $realServer = 'localhost';
78 $realServer = $server;
81 $this->mServer
= $server;
83 $this->mPassword
= $password;
84 $this->mDBname
= $dbName;
86 wfProfileIn("dbconnect-$server");
88 # The kernel's default SYN retransmission period is far too slow for us,
89 # so we use a short timeout plus a manual retry. Retrying means that a small
90 # but finite rate of SYN packet loss won't cause user-visible errors.
92 if ( ini_get( 'mysql.connect_timeout' ) <= 3 ) {
97 $this->installErrorHandler();
98 for ( $i = 0; $i < $numAttempts && !$this->mConn
; $i++
) {
102 if ( $this->mFlags
& DBO_PERSISTENT
) {
103 $this->mConn
= mysql_pconnect( $realServer, $user, $password );
105 # Create a new connection...
106 $this->mConn
= mysql_connect( $realServer, $user, $password, true );
108 #if ( $this->mConn === false ) {
110 #wfLogDBError("Connect loop error $iplus of $max ($server): " . mysql_errno() . " - " . mysql_error()."\n");
113 $phpError = $this->restoreErrorHandler();
114 # Always log connection errors
115 if ( !$this->mConn
) {
116 $error = $this->lastError();
120 wfLogDBError( "Error connecting to {$this->mServer}: $error\n" );
121 wfDebug( "DB connection error\n" );
122 wfDebug( "Server: $server, User: $user, Password: " .
123 substr( $password, 0, 3 ) . "..., error: " . mysql_error() . "\n" );
126 wfProfileOut("dbconnect-$server");
128 if ( $dbName != '' && $this->mConn
!== false ) {
129 wfSuppressWarnings();
130 $success = mysql_select_db( $dbName, $this->mConn
);
133 $error = "Error selecting database $dbName on server {$this->mServer} " .
134 "from client host " . wfHostname() . "\n";
135 wfLogDBError(" Error selecting database $dbName on server {$this->mServer} \n");
140 $success = (bool)$this->mConn
;
144 // Tell the server we're communicating with it in UTF-8.
145 // This may engage various charset conversions.
148 $this->query( 'SET NAMES utf8', __METHOD__
);
150 $this->query( 'SET NAMES binary', __METHOD__
);
152 // Set SQL mode, default is turning them all off, can be overridden or skipped with null
154 if ( is_string( $wgSQLMode ) ) {
155 $mode = $this->addQuotes( $wgSQLMode );
156 $this->query( "SET sql_mode = $mode", __METHOD__
);
159 // Turn off strict mode if it is on
161 $this->reportConnectionError( $phpError );
164 $this->mOpened
= $success;
165 wfProfileOut( __METHOD__
);
172 protected function closeConnection() {
173 return mysql_close( $this->mConn
);
177 * @param $res ResultWrapper
178 * @throws DBUnexpectedError
180 function freeResult( $res ) {
181 if ( $res instanceof ResultWrapper
) {
184 wfSuppressWarnings();
185 $ok = mysql_free_result( $res );
188 throw new DBUnexpectedError( $this, "Unable to free MySQL result" );
193 * @param $res ResultWrapper
194 * @return object|stdClass
195 * @throws DBUnexpectedError
197 function fetchObject( $res ) {
198 if ( $res instanceof ResultWrapper
) {
201 wfSuppressWarnings();
202 $row = mysql_fetch_object( $res );
204 if( $this->lastErrno() ) {
205 throw new DBUnexpectedError( $this, 'Error in fetchObject(): ' . htmlspecialchars( $this->lastError() ) );
211 * @param $res ResultWrapper
213 * @throws DBUnexpectedError
215 function fetchRow( $res ) {
216 if ( $res instanceof ResultWrapper
) {
219 wfSuppressWarnings();
220 $row = mysql_fetch_array( $res );
222 if ( $this->lastErrno() ) {
223 throw new DBUnexpectedError( $this, 'Error in fetchRow(): ' . htmlspecialchars( $this->lastError() ) );
229 * @throws DBUnexpectedError
230 * @param $res ResultWrapper
233 function numRows( $res ) {
234 if ( $res instanceof ResultWrapper
) {
237 wfSuppressWarnings();
238 $n = mysql_num_rows( $res );
240 if( $this->lastErrno() ) {
241 throw new DBUnexpectedError( $this, 'Error in numRows(): ' . htmlspecialchars( $this->lastError() ) );
247 * @param $res ResultWrapper
250 function numFields( $res ) {
251 if ( $res instanceof ResultWrapper
) {
254 return mysql_num_fields( $res );
258 * @param $res ResultWrapper
262 function fieldName( $res, $n ) {
263 if ( $res instanceof ResultWrapper
) {
266 return mysql_field_name( $res, $n );
272 function insertId() {
273 return mysql_insert_id( $this->mConn
);
277 * @param $res ResultWrapper
281 function dataSeek( $res, $row ) {
282 if ( $res instanceof ResultWrapper
) {
285 return mysql_data_seek( $res, $row );
291 function lastErrno() {
292 if ( $this->mConn
) {
293 return mysql_errno( $this->mConn
);
295 return mysql_errno();
302 function lastError() {
303 if ( $this->mConn
) {
304 # Even if it's non-zero, it can still be invalid
305 wfSuppressWarnings();
306 $error = mysql_error( $this->mConn
);
308 $error = mysql_error();
312 $error = mysql_error();
315 $error .= ' (' . $this->mServer
. ')';
323 function affectedRows() {
324 return mysql_affected_rows( $this->mConn
);
328 * @param $table string
329 * @param $uniqueIndexes
331 * @param $fname string
332 * @return ResultWrapper
334 function replace( $table, $uniqueIndexes, $rows, $fname = 'DatabaseMysql::replace' ) {
335 return $this->nativeReplace( $table, $rows, $fname );
339 * Estimate rows in dataset
340 * Returns estimated count, based on EXPLAIN output
341 * Takes same arguments as Database::select()
343 * @param $table string|array
344 * @param $vars string|array
345 * @param $conds string|array
346 * @param $fname string
347 * @param $options string|array
350 public function estimateRowCount( $table, $vars='*', $conds='', $fname = 'DatabaseMysql::estimateRowCount', $options = array() ) {
351 $options['EXPLAIN'] = true;
352 $res = $this->select( $table, $vars, $conds, $fname, $options );
353 if ( $res === false ) {
356 if ( !$this->numRows( $res ) ) {
361 foreach ( $res as $plan ) {
362 $rows *= $plan->rows
> 0 ?
$plan->rows
: 1; // avoid resetting to zero
368 * @param $table string
369 * @param $field string
370 * @return bool|MySQLField
372 function fieldInfo( $table, $field ) {
373 $table = $this->tableName( $table );
374 $res = $this->query( "SELECT * FROM $table LIMIT 1", __METHOD__
, true );
378 $n = mysql_num_fields( $res->result
);
379 for( $i = 0; $i < $n; $i++
) {
380 $meta = mysql_fetch_field( $res->result
, $i );
381 if( $field == $meta->name
) {
382 return new MySQLField($meta);
389 * Get information about an index into an object
390 * Returns false if the index does not exist
392 * @param $table string
393 * @param $index string
394 * @param $fname string
395 * @return bool|array|null False or null on failure
397 function indexInfo( $table, $index, $fname = 'DatabaseMysql::indexInfo' ) {
398 # SHOW INDEX works in MySQL 3.23.58, but SHOW INDEXES does not.
399 # SHOW INDEX should work for 3.x and up:
400 # http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html
401 $table = $this->tableName( $table );
402 $index = $this->indexName( $index );
403 $sql = 'SHOW INDEX FROM ' . $table;
404 $res = $this->query( $sql, $fname );
412 foreach ( $res as $row ) {
413 if ( $row->Key_name
== $index ) {
418 return empty( $result ) ?
false : $result;
425 function selectDB( $db ) {
426 $this->mDBname
= $db;
427 return mysql_select_db( $db, $this->mConn
);
435 function strencode( $s ) {
436 $sQuoted = mysql_real_escape_string( $s, $this->mConn
);
438 if($sQuoted === false) {
440 $sQuoted = mysql_real_escape_string( $s, $this->mConn
);
446 * MySQL uses `backticks` for identifier quoting instead of the sql standard "double quotes".
452 public function addIdentifierQuotes( $s ) {
453 return "`" . $this->strencode( $s ) . "`";
457 * @param $name string
460 public function isQuotedIdentifier( $name ) {
461 return strlen( $name ) && $name[0] == '`' && substr( $name, -1, 1 ) == '`';
468 $ping = mysql_ping( $this->mConn
);
473 mysql_close( $this->mConn
);
474 $this->mOpened
= false;
475 $this->mConn
= false;
476 $this->open( $this->mServer
, $this->mUser
, $this->mPassword
, $this->mDBname
);
483 * This will do a SHOW SLAVE STATUS
488 if ( !is_null( $this->mFakeSlaveLag
) ) {
489 wfDebug( "getLag: fake slave lagged {$this->mFakeSlaveLag} seconds\n" );
490 return $this->mFakeSlaveLag
;
493 return $this->getLagFromSlaveStatus();
499 function getLagFromSlaveStatus() {
500 $res = $this->query( 'SHOW SLAVE STATUS', __METHOD__
);
504 $row = $res->fetchObject();
508 if ( strval( $row->Seconds_Behind_Master
) === '' ) {
511 return intval( $row->Seconds_Behind_Master
);
516 * @deprecated in 1.19, use getLagFromSlaveStatus
520 function getLagFromProcesslist() {
521 wfDeprecated( __METHOD__
, '1.19' );
522 $res = $this->query( 'SHOW PROCESSLIST', __METHOD__
);
526 # Find slave SQL thread
527 foreach( $res as $row ) {
528 /* This should work for most situations - when default db
529 * for thread is not specified, it had no events executed,
530 * and therefore it doesn't know yet how lagged it is.
532 * Relay log I/O thread does not select databases.
534 if ( $row->User
== 'system user' &&
535 $row->State
!= 'Waiting for master to send event' &&
536 $row->State
!= 'Connecting to master' &&
537 $row->State
!= 'Queueing master event to the relay log' &&
538 $row->State
!= 'Waiting for master update' &&
539 $row->State
!= 'Requesting binlog dump' &&
540 $row->State
!= 'Waiting to reconnect after a failed master event read' &&
541 $row->State
!= 'Reconnecting after a failed master event read' &&
542 $row->State
!= 'Registering slave on master'
544 # This is it, return the time (except -ve)
545 if ( $row->Time
> 0x7fffffff ) {
556 * Wait for the slave to catch up to a given master position.
558 * @param $pos DBMasterPos object
559 * @param $timeout Integer: the maximum number of seconds to wait for synchronisation
560 * @return bool|string
562 function masterPosWait( DBMasterPos
$pos, $timeout ) {
563 $fname = 'DatabaseBase::masterPosWait';
564 wfProfileIn( $fname );
566 # Commit any open transactions
567 if ( $this->mTrxLevel
) {
568 $this->commit( __METHOD__
);
571 if ( !is_null( $this->mFakeSlaveLag
) ) {
572 $status = parent
::masterPosWait( $pos, $timeout );
573 wfProfileOut( $fname );
577 # Call doQuery() directly, to avoid opening a transaction if DBO_TRX is set
578 $encFile = $this->addQuotes( $pos->file
);
579 $encPos = intval( $pos->pos
);
580 $sql = "SELECT MASTER_POS_WAIT($encFile, $encPos, $timeout)";
581 $res = $this->doQuery( $sql );
583 if ( $res && $row = $this->fetchRow( $res ) ) {
584 wfProfileOut( $fname );
587 wfProfileOut( $fname );
593 * Get the position of the master from SHOW SLAVE STATUS
595 * @return MySQLMasterPos|bool
597 function getSlavePos() {
598 if ( !is_null( $this->mFakeSlaveLag
) ) {
599 return parent
::getSlavePos();
602 $res = $this->query( 'SHOW SLAVE STATUS', 'DatabaseBase::getSlavePos' );
603 $row = $this->fetchObject( $res );
606 $pos = isset( $row->Exec_master_log_pos
) ?
$row->Exec_master_log_pos
: $row->Exec_Master_Log_Pos
;
607 return new MySQLMasterPos( $row->Relay_Master_Log_File
, $pos );
614 * Get the position of the master from SHOW MASTER STATUS
616 * @return MySQLMasterPos|bool
618 function getMasterPos() {
619 if ( $this->mFakeMaster
) {
620 return parent
::getMasterPos();
623 $res = $this->query( 'SHOW MASTER STATUS', 'DatabaseBase::getMasterPos' );
624 $row = $this->fetchObject( $res );
627 return new MySQLMasterPos( $row->File
, $row->Position
);
636 function getServerVersion() {
637 return mysql_get_server_info( $this->mConn
);
644 function useIndexClause( $index ) {
645 return "FORCE INDEX (" . $this->indexName( $index ) . ")";
651 function lowPriorityOption() {
652 return 'LOW_PRIORITY';
658 public static function getSoftwareLink() {
659 return '[http://www.mysql.com/ MySQL]';
665 function standardSelectDistinct() {
670 * @param $options array
672 public function setSessionOptions( array $options ) {
673 if ( isset( $options['connTimeout'] ) ) {
674 $timeout = (int)$options['connTimeout'];
675 $this->query( "SET net_read_timeout=$timeout" );
676 $this->query( "SET net_write_timeout=$timeout" );
680 public function streamStatementEnd( &$sql, &$newLine ) {
681 if ( strtoupper( substr( $newLine, 0, 9 ) ) == 'DELIMITER' ) {
682 preg_match( '/^DELIMITER\s+(\S+)/' , $newLine, $m );
683 $this->delimiter
= $m[1];
686 return parent
::streamStatementEnd( $sql, $newLine );
690 * @param $lockName string
691 * @param $method string
692 * @param $timeout int
695 public function lock( $lockName, $method, $timeout = 5 ) {
696 $lockName = $this->addQuotes( $lockName );
697 $result = $this->query( "SELECT GET_LOCK($lockName, $timeout) AS lockstatus", $method );
698 $row = $this->fetchObject( $result );
700 if( $row->lockstatus
== 1 ) {
703 wfDebug( __METHOD__
." failed to acquire lock\n" );
709 * FROM MYSQL DOCS: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_release-lock
710 * @param $lockName string
711 * @param $method string
714 public function unlock( $lockName, $method ) {
715 $lockName = $this->addQuotes( $lockName );
716 $result = $this->query( "SELECT RELEASE_LOCK($lockName) as lockstatus", $method );
717 $row = $this->fetchObject( $result );
718 return $row->lockstatus
;
723 * @param $write array
724 * @param $method string
725 * @param $lowPriority bool
727 public function lockTables( $read, $write, $method, $lowPriority = true ) {
730 foreach( $write as $table ) {
731 $tbl = $this->tableName( $table ) .
732 ( $lowPriority ?
' LOW_PRIORITY' : '' ) .
736 foreach( $read as $table ) {
737 $items[] = $this->tableName( $table ) . ' READ';
739 $sql = "LOCK TABLES " . implode( ',', $items );
740 $this->query( $sql, $method );
744 * @param $method string
746 public function unlockTables( $method ) {
747 $this->query( "UNLOCK TABLES", $method );
751 * Get search engine class. All subclasses of this
752 * need to implement this if they wish to use searching.
756 public function getSearchEngine() {
757 return 'SearchMySQL';
764 public function setBigSelects( $value = true ) {
765 if ( $value === 'default' ) {
766 if ( $this->mDefaultBigSelects
=== null ) {
767 # Function hasn't been called before so it must already be set to the default
770 $value = $this->mDefaultBigSelects
;
772 } elseif ( $this->mDefaultBigSelects
=== null ) {
773 $this->mDefaultBigSelects
= (bool)$this->selectField( false, '@@sql_big_selects' );
775 $encValue = $value ?
'1' : '0';
776 $this->query( "SET sql_big_selects=$encValue", __METHOD__
);
780 * DELETE where the condition is a join. MySql uses multi-table deletes.
781 * @param $delTable string
782 * @param $joinTable string
783 * @param $delVar string
784 * @param $joinVar string
785 * @param $conds array|string
787 * @return bool|ResultWrapper
789 function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = 'DatabaseBase::deleteJoin' ) {
791 throw new DBUnexpectedError( $this, 'DatabaseBase::deleteJoin() called with empty $conds' );
794 $delTable = $this->tableName( $delTable );
795 $joinTable = $this->tableName( $joinTable );
796 $sql = "DELETE $delTable FROM $delTable, $joinTable WHERE $delVar=$joinVar ";
798 if ( $conds != '*' ) {
799 $sql .= ' AND ' . $this->makeList( $conds, LIST_AND
);
802 return $this->query( $sql, $fname );
806 * Determines how long the server has been up
810 function getServerUptime() {
811 $vars = $this->getMysqlStatus( 'Uptime' );
812 return (int)$vars['Uptime'];
816 * Determines if the last failure was due to a deadlock
820 function wasDeadlock() {
821 return $this->lastErrno() == 1213;
825 * Determines if the last failure was due to a lock timeout
829 function wasLockTimeout() {
830 return $this->lastErrno() == 1205;
834 * Determines if the last query error was something that should be dealt
835 * with by pinging the connection and reissuing the query
839 function wasErrorReissuable() {
840 return $this->lastErrno() == 2013 ||
$this->lastErrno() == 2006;
844 * Determines if the last failure was due to the database being read-only.
848 function wasReadOnlyError() {
849 return $this->lastErrno() == 1223 ||
850 ( $this->lastErrno() == 1290 && strpos( $this->lastError(), '--read-only' ) !== false );
856 * @param $temporary bool
857 * @param $fname string
859 function duplicateTableStructure( $oldName, $newName, $temporary = false, $fname = 'DatabaseMysql::duplicateTableStructure' ) {
860 $tmp = $temporary ?
'TEMPORARY ' : '';
861 $newName = $this->addIdentifierQuotes( $newName );
862 $oldName = $this->addIdentifierQuotes( $oldName );
863 $query = "CREATE $tmp TABLE $newName (LIKE $oldName)";
864 $this->query( $query, $fname );
868 * List all tables on the database
870 * @param $prefix string Only show tables with this prefix, e.g. mw_
871 * @param $fname String: calling function name
874 function listTables( $prefix = null, $fname = 'DatabaseMysql::listTables' ) {
875 $result = $this->query( "SHOW TABLES", $fname);
879 foreach( $result as $table ) {
880 $vars = get_object_vars($table);
881 $table = array_pop( $vars );
883 if( !$prefix ||
strpos( $table, $prefix ) === 0 ) {
884 $endArray[] = $table;
893 * @param $fName string
894 * @return bool|ResultWrapper
896 public function dropTable( $tableName, $fName = 'DatabaseMysql::dropTable' ) {
897 if( !$this->tableExists( $tableName, $fName ) ) {
900 return $this->query( "DROP TABLE IF EXISTS " . $this->tableName( $tableName ), $fName );
906 protected function getDefaultSchemaVars() {
907 $vars = parent
::getDefaultSchemaVars();
908 $vars['wgDBTableOptions'] = str_replace( 'TYPE', 'ENGINE', $GLOBALS['wgDBTableOptions'] );
909 $vars['wgDBTableOptions'] = str_replace( 'CHARSET=mysql4', 'CHARSET=binary', $vars['wgDBTableOptions'] );
914 * Get status information from SHOW STATUS in an associative array
916 * @param $which string
919 function getMysqlStatus( $which = "%" ) {
920 $res = $this->query( "SHOW STATUS LIKE '{$which}'" );
923 foreach ( $res as $row ) {
924 $status[$row->Variable_name
] = $row->Value
;
933 * Legacy support: Database == DatabaseMysql
935 * @deprecated in 1.16
937 class Database
extends DatabaseMysql
{}
943 class MySQLField
implements Field
{
944 private $name, $tablename, $default, $max_length, $nullable,
945 $is_pk, $is_unique, $is_multiple, $is_key, $type;
947 function __construct ( $info ) {
948 $this->name
= $info->name
;
949 $this->tablename
= $info->table
;
950 $this->default = $info->def
;
951 $this->max_length
= $info->max_length
;
952 $this->nullable
= !$info->not_null
;
953 $this->is_pk
= $info->primary_key
;
954 $this->is_unique
= $info->unique_key
;
955 $this->is_multiple
= $info->multiple_key
;
956 $this->is_key
= ( $this->is_pk ||
$this->is_unique ||
$this->is_multiple
);
957 $this->type
= $info->type
;
970 function tableName() {
971 return $this->tableName
;
984 function isNullable() {
985 return $this->nullable
;
988 function defaultValue() {
989 return $this->default;
996 return $this->is_key
;
1002 function isMultipleKey() {
1003 return $this->is_multiple
;
1007 class MySQLMasterPos
implements DBMasterPos
{
1010 function __construct( $file, $pos ) {
1011 $this->file
= $file;
1015 function __toString() {
1016 return "{$this->file}/{$this->pos}";