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 * Defines methods independent on used MySQL extension.
32 abstract class DatabaseMysqlBase
extends DatabaseBase
{
42 * @param $server string
44 * @param $password string
45 * @param $dbName string
47 * @throws DBConnectionError
49 function open( $server, $user, $password, $dbName ) {
50 global $wgAllDBsAreLocalhost, $wgDBmysql5, $wgSQLMode;
51 wfProfileIn( __METHOD__
);
53 # Debugging hack -- fake cluster
54 if ( $wgAllDBsAreLocalhost ) {
55 $realServer = 'localhost';
57 $realServer = $server;
60 $this->mServer
= $server;
62 $this->mPassword
= $password;
63 $this->mDBname
= $dbName;
65 wfProfileIn( "dbconnect-$server" );
67 # The kernel's default SYN retransmission period is far too slow for us,
68 # so we use a short timeout plus a manual retry. Retrying means that a small
69 # but finite rate of SYN packet loss won't cause user-visible errors.
71 $this->installErrorHandler();
73 $this->mConn
= $this->mysqlConnect( $realServer );
74 } catch (Exception
$ex) {
75 wfProfileOut( "dbconnect-$server" );
76 wfProfileOut( __METHOD__
);
79 $error = $this->restoreErrorHandler();
81 wfProfileOut( "dbconnect-$server" );
83 # Always log connection errors
84 if ( !$this->mConn
) {
86 $error = $this->lastError();
88 wfLogDBError( "Error connecting to {$this->mServer}: $error\n" );
89 wfDebug( "DB connection error\n" .
90 "Server: $server, User: $user, Password: " .
91 substr( $password, 0, 3 ) . "..., error: " . $error . "\n" );
93 wfProfileOut( __METHOD__
);
94 return $this->reportConnectionError( $error );
97 if ( $dbName != '' ) {
99 $success = $this->selectDB( $dbName );
102 wfLogDBError( "Error selecting database $dbName on server {$this->mServer}\n" );
103 wfDebug( "Error selecting database $dbName on server {$this->mServer} " .
104 "from client host " . wfHostname() . "\n" );
106 wfProfileOut( __METHOD__
);
107 return $this->reportConnectionError( "Error selecting database $dbName" );
111 // Tell the server we're communicating with it in UTF-8.
112 // This may engage various charset conversions.
114 $this->query( 'SET NAMES utf8', __METHOD__
);
116 $this->query( 'SET NAMES binary', __METHOD__
);
118 // Set SQL mode, default is turning them all off, can be overridden or skipped with null
119 if ( is_string( $wgSQLMode ) ) {
120 $mode = $this->addQuotes( $wgSQLMode );
121 $this->query( "SET sql_mode = $mode", __METHOD__
);
124 $this->mOpened
= true;
125 wfProfileOut( __METHOD__
);
130 * Open a connection to a MySQL server
132 * @param $realServer string
133 * @return mixed Raw connection
134 * @throws DBConnectionError
136 abstract protected function mysqlConnect( $realServer );
139 * @param $res ResultWrapper
140 * @throws DBUnexpectedError
142 function freeResult( $res ) {
143 if ( $res instanceof ResultWrapper
) {
146 wfSuppressWarnings();
147 $ok = $this->mysqlFreeResult( $res );
150 throw new DBUnexpectedError( $this, "Unable to free MySQL result" );
157 * @param $res Raw result
160 abstract protected function mysqlFreeResult( $res );
163 * @param $res ResultWrapper
164 * @return object|bool
165 * @throws DBUnexpectedError
167 function fetchObject( $res ) {
168 if ( $res instanceof ResultWrapper
) {
171 wfSuppressWarnings();
172 $row = $this->mysqlFetchObject( $res );
175 $errno = $this->lastErrno();
176 // Unfortunately, mysql_fetch_object does not reset the last errno.
177 // Only check for CR_SERVER_LOST and CR_UNKNOWN_ERROR, as
178 // these are the only errors mysql_fetch_object can cause.
179 // See http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html.
180 if ( $errno == 2000 ||
$errno == 2013 ) {
181 throw new DBUnexpectedError( $this, 'Error in fetchObject(): ' . htmlspecialchars( $this->lastError() ) );
187 * Fetch a result row as an object
189 * @param $res Raw result
192 abstract protected function mysqlFetchObject( $res );
195 * @param $res ResultWrapper
197 * @throws DBUnexpectedError
199 function fetchRow( $res ) {
200 if ( $res instanceof ResultWrapper
) {
203 wfSuppressWarnings();
204 $row = $this->mysqlFetchArray( $res );
207 $errno = $this->lastErrno();
208 // Unfortunately, mysql_fetch_array does not reset the last errno.
209 // Only check for CR_SERVER_LOST and CR_UNKNOWN_ERROR, as
210 // these are the only errors mysql_fetch_array can cause.
211 // See http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html.
212 if ( $errno == 2000 ||
$errno == 2013 ) {
213 throw new DBUnexpectedError( $this, 'Error in fetchRow(): ' . htmlspecialchars( $this->lastError() ) );
219 * Fetch a result row as an associative and numeric array
221 * @param $res Raw result
224 abstract protected function mysqlFetchArray( $res );
227 * @throws DBUnexpectedError
228 * @param $res ResultWrapper
231 function numRows( $res ) {
232 if ( $res instanceof ResultWrapper
) {
235 wfSuppressWarnings();
236 $n = $this->mysqlNumRows( $res );
238 // Unfortunately, mysql_num_rows does not reset the last errno.
239 // We are not checking for any errors here, since
240 // these are no errors mysql_num_rows can cause.
241 // See http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html.
242 // See https://bugzilla.wikimedia.org/42430
247 * Get number of rows in result
249 * @param $res Raw result
252 abstract protected function mysqlNumRows( $res );
255 * @param $res ResultWrapper
258 function numFields( $res ) {
259 if ( $res instanceof ResultWrapper
) {
262 return $this->mysqlNumFields( $res );
266 * Get number of fields in result
268 * @param $res Raw result
271 abstract protected function mysqlNumFields( $res );
274 * @param $res ResultWrapper
278 function fieldName( $res, $n ) {
279 if ( $res instanceof ResultWrapper
) {
282 return $this->mysqlFieldName( $res, $n );
286 * Get the name of the specified field in a result
288 * @param $res Raw result
292 abstract protected function mysqlFieldName( $res, $n );
295 * @param $res ResultWrapper
299 function dataSeek( $res, $row ) {
300 if ( $res instanceof ResultWrapper
) {
303 return $this->mysqlDataSeek( $res, $row );
307 * Move internal result pointer
309 * @param $res Raw result
313 abstract protected function mysqlDataSeek( $res, $row );
318 function lastError() {
319 if ( $this->mConn
) {
320 # Even if it's non-zero, it can still be invalid
321 wfSuppressWarnings();
322 $error = $this->mysqlError( $this->mConn
);
324 $error = $this->mysqlError();
328 $error = $this->mysqlError();
331 $error .= ' (' . $this->mServer
. ')';
337 * Returns the text of the error message from previous MySQL operation
339 * @param $conn Raw connection
342 abstract protected function mysqlError( $conn = null );
345 * @param $table string
346 * @param $uniqueIndexes
348 * @param $fname string
349 * @return ResultWrapper
351 function replace( $table, $uniqueIndexes, $rows, $fname = __METHOD__
) {
352 return $this->nativeReplace( $table, $rows, $fname );
356 * Estimate rows in dataset
357 * Returns estimated count, based on EXPLAIN output
358 * Takes same arguments as Database::select()
360 * @param $table string|array
361 * @param $vars string|array
362 * @param $conds string|array
363 * @param $fname string
364 * @param $options string|array
367 public function estimateRowCount( $table, $vars = '*', $conds = '', $fname = __METHOD__
, $options = array() ) {
368 $options['EXPLAIN'] = true;
369 $res = $this->select( $table, $vars, $conds, $fname, $options );
370 if ( $res === false ) {
373 if ( !$this->numRows( $res ) ) {
378 foreach ( $res as $plan ) {
379 $rows *= $plan->rows
> 0 ?
$plan->rows
: 1; // avoid resetting to zero
385 * @param $table string
386 * @param $field string
387 * @return bool|MySQLField
389 function fieldInfo( $table, $field ) {
390 $table = $this->tableName( $table );
391 $res = $this->query( "SELECT * FROM $table LIMIT 1", __METHOD__
, true );
395 $n = $this->mysqlNumFields( $res->result
);
396 for ( $i = 0; $i < $n; $i++
) {
397 $meta = $this->mysqlFetchField( $res->result
, $i );
398 if ( $field == $meta->name
) {
399 return new MySQLField( $meta );
406 * Get column information from a result
408 * @param $res Raw result
412 abstract protected function mysqlFetchField( $res, $n );
415 * Get information about an index into an object
416 * Returns false if the index does not exist
418 * @param $table string
419 * @param $index string
420 * @param $fname string
421 * @return bool|array|null False or null on failure
423 function indexInfo( $table, $index, $fname = __METHOD__
) {
424 # SHOW INDEX works in MySQL 3.23.58, but SHOW INDEXES does not.
425 # SHOW INDEX should work for 3.x and up:
426 # http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html
427 $table = $this->tableName( $table );
428 $index = $this->indexName( $index );
430 $sql = 'SHOW INDEX FROM ' . $table;
431 $res = $this->query( $sql, $fname );
439 foreach ( $res as $row ) {
440 if ( $row->Key_name
== $index ) {
444 return empty( $result ) ?
false : $result;
452 function strencode( $s ) {
453 $sQuoted = $this->mysqlRealEscapeString( $s );
455 if ( $sQuoted === false ) {
457 $sQuoted = $this->mysqlRealEscapeString( $s );
463 * MySQL uses `backticks` for identifier quoting instead of the sql standard "double quotes".
469 public function addIdentifierQuotes( $s ) {
470 return "`" . $this->strencode( $s ) . "`";
474 * @param $name string
477 public function isQuotedIdentifier( $name ) {
478 return strlen( $name ) && $name[0] == '`' && substr( $name, -1, 1 ) == '`';
485 $ping = $this->mysqlPing();
490 $this->closeConnection();
491 $this->mOpened
= false;
492 $this->mConn
= false;
493 $this->open( $this->mServer
, $this->mUser
, $this->mPassword
, $this->mDBname
);
498 * Ping a server connection or reconnect if there is no connection
502 abstract protected function mysqlPing();
507 * This will do a SHOW SLAVE STATUS
512 if ( !is_null( $this->mFakeSlaveLag
) ) {
513 wfDebug( "getLag: fake slave lagged {$this->mFakeSlaveLag} seconds\n" );
514 return $this->mFakeSlaveLag
;
517 return $this->getLagFromSlaveStatus();
523 function getLagFromSlaveStatus() {
524 $res = $this->query( 'SHOW SLAVE STATUS', __METHOD__
);
528 $row = $res->fetchObject();
532 if ( strval( $row->Seconds_Behind_Master
) === '' ) {
535 return intval( $row->Seconds_Behind_Master
);
540 * @deprecated in 1.19, use getLagFromSlaveStatus
544 function getLagFromProcesslist() {
545 wfDeprecated( __METHOD__
, '1.19' );
546 $res = $this->query( 'SHOW PROCESSLIST', __METHOD__
);
550 # Find slave SQL thread
551 foreach ( $res as $row ) {
552 /* This should work for most situations - when default db
553 * for thread is not specified, it had no events executed,
554 * and therefore it doesn't know yet how lagged it is.
556 * Relay log I/O thread does not select databases.
558 if ( $row->User
== 'system user' &&
559 $row->State
!= 'Waiting for master to send event' &&
560 $row->State
!= 'Connecting to master' &&
561 $row->State
!= 'Queueing master event to the relay log' &&
562 $row->State
!= 'Waiting for master update' &&
563 $row->State
!= 'Requesting binlog dump' &&
564 $row->State
!= 'Waiting to reconnect after a failed master event read' &&
565 $row->State
!= 'Reconnecting after a failed master event read' &&
566 $row->State
!= 'Registering slave on master'
568 # This is it, return the time (except -ve)
569 if ( $row->Time
> 0x7fffffff ) {
580 * Wait for the slave to catch up to a given master position.
582 * @param $pos DBMasterPos object
583 * @param $timeout Integer: the maximum number of seconds to wait for synchronisation
584 * @return bool|string
586 function masterPosWait( DBMasterPos
$pos, $timeout ) {
588 wfProfileIn( $fname );
590 # Commit any open transactions
591 if ( $this->mTrxLevel
) {
592 $this->commit( $fname );
595 if ( !is_null( $this->mFakeSlaveLag
) ) {
596 $status = parent
::masterPosWait( $pos, $timeout );
597 wfProfileOut( $fname );
601 # Call doQuery() directly, to avoid opening a transaction if DBO_TRX is set
602 $encFile = $this->addQuotes( $pos->file
);
603 $encPos = intval( $pos->pos
);
604 $sql = "SELECT MASTER_POS_WAIT($encFile, $encPos, $timeout)";
605 $res = $this->doQuery( $sql );
607 if ( $res && $row = $this->fetchRow( $res ) ) {
608 wfProfileOut( $fname );
611 wfProfileOut( $fname );
616 * Get the position of the master from SHOW SLAVE STATUS
618 * @return MySQLMasterPos|bool
620 function getSlavePos() {
621 if ( !is_null( $this->mFakeSlaveLag
) ) {
622 return parent
::getSlavePos();
625 $res = $this->query( 'SHOW SLAVE STATUS', 'DatabaseBase::getSlavePos' );
626 $row = $this->fetchObject( $res );
629 $pos = isset( $row->Exec_master_log_pos
) ?
$row->Exec_master_log_pos
: $row->Exec_Master_Log_Pos
;
630 return new MySQLMasterPos( $row->Relay_Master_Log_File
, $pos );
637 * Get the position of the master from SHOW MASTER STATUS
639 * @return MySQLMasterPos|bool
641 function getMasterPos() {
642 if ( $this->mFakeMaster
) {
643 return parent
::getMasterPos();
646 $res = $this->query( 'SHOW MASTER STATUS', 'DatabaseBase::getMasterPos' );
647 $row = $this->fetchObject( $res );
650 return new MySQLMasterPos( $row->File
, $row->Position
);
660 function useIndexClause( $index ) {
661 return "FORCE INDEX (" . $this->indexName( $index ) . ")";
667 function lowPriorityOption() {
668 return 'LOW_PRIORITY';
674 public function getSoftwareLink() {
675 return '[http://www.mysql.com/ MySQL]';
679 * @param $options array
681 public function setSessionOptions( array $options ) {
682 if ( isset( $options['connTimeout'] ) ) {
683 $timeout = (int)$options['connTimeout'];
684 $this->query( "SET net_read_timeout=$timeout" );
685 $this->query( "SET net_write_timeout=$timeout" );
689 public function streamStatementEnd( &$sql, &$newLine ) {
690 if ( strtoupper( substr( $newLine, 0, 9 ) ) == 'DELIMITER' ) {
691 preg_match( '/^DELIMITER\s+(\S+)/', $newLine, $m );
692 $this->delimiter
= $m[1];
695 return parent
::streamStatementEnd( $sql, $newLine );
699 * Check to see if a named lock is available. This is non-blocking.
701 * @param string $lockName name of lock to poll
702 * @param string $method name of method calling us
706 public function lockIsFree( $lockName, $method ) {
707 $lockName = $this->addQuotes( $lockName );
708 $result = $this->query( "SELECT IS_FREE_LOCK($lockName) AS lockstatus", $method );
709 $row = $this->fetchObject( $result );
710 return ( $row->lockstatus
== 1 );
714 * @param $lockName string
715 * @param $method string
716 * @param $timeout int
719 public function lock( $lockName, $method, $timeout = 5 ) {
720 $lockName = $this->addQuotes( $lockName );
721 $result = $this->query( "SELECT GET_LOCK($lockName, $timeout) AS lockstatus", $method );
722 $row = $this->fetchObject( $result );
724 if ( $row->lockstatus
== 1 ) {
727 wfDebug( __METHOD__
. " failed to acquire lock\n" );
733 * FROM MYSQL DOCS: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_release-lock
734 * @param $lockName string
735 * @param $method string
738 public function unlock( $lockName, $method ) {
739 $lockName = $this->addQuotes( $lockName );
740 $result = $this->query( "SELECT RELEASE_LOCK($lockName) as lockstatus", $method );
741 $row = $this->fetchObject( $result );
742 return ( $row->lockstatus
== 1 );
747 * @param $write array
748 * @param $method string
749 * @param $lowPriority bool
752 public function lockTables( $read, $write, $method, $lowPriority = true ) {
755 foreach ( $write as $table ) {
756 $tbl = $this->tableName( $table ) .
757 ( $lowPriority ?
' LOW_PRIORITY' : '' ) .
761 foreach ( $read as $table ) {
762 $items[] = $this->tableName( $table ) . ' READ';
764 $sql = "LOCK TABLES " . implode( ',', $items );
765 $this->query( $sql, $method );
770 * @param $method string
773 public function unlockTables( $method ) {
774 $this->query( "UNLOCK TABLES", $method );
779 * Get search engine class. All subclasses of this
780 * need to implement this if they wish to use searching.
784 public function getSearchEngine() {
785 return 'SearchMySQL';
792 public function setBigSelects( $value = true ) {
793 if ( $value === 'default' ) {
794 if ( $this->mDefaultBigSelects
=== null ) {
795 # Function hasn't been called before so it must already be set to the default
798 $value = $this->mDefaultBigSelects
;
800 } elseif ( $this->mDefaultBigSelects
=== null ) {
801 $this->mDefaultBigSelects
= (bool)$this->selectField( false, '@@sql_big_selects' );
803 $encValue = $value ?
'1' : '0';
804 $this->query( "SET sql_big_selects=$encValue", __METHOD__
);
808 * DELETE where the condition is a join. MySql uses multi-table deletes.
809 * @param $delTable string
810 * @param $joinTable string
811 * @param $delVar string
812 * @param $joinVar string
813 * @param $conds array|string
814 * @param bool|string $fname bool
815 * @throws DBUnexpectedError
816 * @return bool|ResultWrapper
818 function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = __METHOD__
) {
820 throw new DBUnexpectedError( $this, 'DatabaseBase::deleteJoin() called with empty $conds' );
823 $delTable = $this->tableName( $delTable );
824 $joinTable = $this->tableName( $joinTable );
825 $sql = "DELETE $delTable FROM $delTable, $joinTable WHERE $delVar=$joinVar ";
827 if ( $conds != '*' ) {
828 $sql .= ' AND ' . $this->makeList( $conds, LIST_AND
);
831 return $this->query( $sql, $fname );
835 * @param string $table
837 * @param array $uniqueIndexes
839 * @param string $fname
840 * @param array $options
843 public function upsert(
844 $table, array $rows, array $uniqueIndexes, array $set, $fname = __METHOD__
846 if ( !count( $rows ) ) {
847 return true; // nothing to do
849 $rows = is_array( reset( $rows ) ) ?
$rows : array( $rows );
851 $table = $this->tableName( $table );
852 $columns = array_keys( $rows[0] );
854 $sql = "INSERT INTO $table (" . implode( ',', $columns ) . ') VALUES ';
855 $rowTuples = array();
856 foreach ( $rows as $row ) {
857 $rowTuples[] = '(' . $this->makeList( $row ) . ')';
859 $sql .= implode( ',', $rowTuples );
860 $sql .= " ON DUPLICATE KEY UPDATE " . $this->makeList( $set, LIST_SET
);
862 return (bool)$this->query( $sql, $fname );
866 * Determines how long the server has been up
870 function getServerUptime() {
871 $vars = $this->getMysqlStatus( 'Uptime' );
872 return (int)$vars['Uptime'];
876 * Determines if the last failure was due to a deadlock
880 function wasDeadlock() {
881 return $this->lastErrno() == 1213;
885 * Determines if the last failure was due to a lock timeout
889 function wasLockTimeout() {
890 return $this->lastErrno() == 1205;
894 * Determines if the last query error was something that should be dealt
895 * with by pinging the connection and reissuing the query
899 function wasErrorReissuable() {
900 return $this->lastErrno() == 2013 ||
$this->lastErrno() == 2006;
904 * Determines if the last failure was due to the database being read-only.
908 function wasReadOnlyError() {
909 return $this->lastErrno() == 1223 ||
910 ( $this->lastErrno() == 1290 && strpos( $this->lastError(), '--read-only' ) !== false );
916 * @param $temporary bool
917 * @param $fname string
919 function duplicateTableStructure( $oldName, $newName, $temporary = false, $fname = __METHOD__
) {
920 $tmp = $temporary ?
'TEMPORARY ' : '';
921 $newName = $this->addIdentifierQuotes( $newName );
922 $oldName = $this->addIdentifierQuotes( $oldName );
923 $query = "CREATE $tmp TABLE $newName (LIKE $oldName)";
924 $this->query( $query, $fname );
928 * List all tables on the database
930 * @param string $prefix Only show tables with this prefix, e.g. mw_
931 * @param string $fname calling function name
934 function listTables( $prefix = null, $fname = __METHOD__
) {
935 $result = $this->query( "SHOW TABLES", $fname );
939 foreach ( $result as $table ) {
940 $vars = get_object_vars( $table );
941 $table = array_pop( $vars );
943 if ( !$prefix ||
strpos( $table, $prefix ) === 0 ) {
944 $endArray[] = $table;
953 * @param $fName string
954 * @return bool|ResultWrapper
956 public function dropTable( $tableName, $fName = __METHOD__
) {
957 if ( !$this->tableExists( $tableName, $fName ) ) {
960 return $this->query( "DROP TABLE IF EXISTS " . $this->tableName( $tableName ), $fName );
966 protected function getDefaultSchemaVars() {
967 $vars = parent
::getDefaultSchemaVars();
968 $vars['wgDBTableOptions'] = str_replace( 'TYPE', 'ENGINE', $GLOBALS['wgDBTableOptions'] );
969 $vars['wgDBTableOptions'] = str_replace( 'CHARSET=mysql4', 'CHARSET=binary', $vars['wgDBTableOptions'] );
974 * Get status information from SHOW STATUS in an associative array
976 * @param $which string
979 function getMysqlStatus( $which = "%" ) {
980 $res = $this->query( "SHOW STATUS LIKE '{$which}'" );
983 foreach ( $res as $row ) {
984 $status[$row->Variable_name
] = $row->Value
;
998 class MySQLField
implements Field
{
999 private $name, $tablename, $default, $max_length, $nullable,
1000 $is_pk, $is_unique, $is_multiple, $is_key, $type;
1002 function __construct( $info ) {
1003 $this->name
= $info->name
;
1004 $this->tablename
= $info->table
;
1005 $this->default = $info->def
;
1006 $this->max_length
= $info->max_length
;
1007 $this->nullable
= !$info->not_null
;
1008 $this->is_pk
= $info->primary_key
;
1009 $this->is_unique
= $info->unique_key
;
1010 $this->is_multiple
= $info->multiple_key
;
1011 $this->is_key
= ( $this->is_pk ||
$this->is_unique ||
$this->is_multiple
);
1012 $this->type
= $info->type
;
1025 function tableName() {
1026 return $this->tableName
;
1039 function isNullable() {
1040 return $this->nullable
;
1043 function defaultValue() {
1044 return $this->default;
1051 return $this->is_key
;
1057 function isMultipleKey() {
1058 return $this->is_multiple
;
1062 class MySQLMasterPos
implements DBMasterPos
{
1065 function __construct( $file, $pos ) {
1066 $this->file
= $file;
1070 function __toString() {
1071 return "{$this->file}/{$this->pos}";