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, $wgDBmysql5, $wgSQLMode;
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;
87 if ( $this->mFlags
& DBO_SSL
) {
88 $connFlags |
= MYSQL_CLIENT_SSL
;
90 if ( $this->mFlags
& DBO_COMPRESS
) {
91 $connFlags |
= MYSQL_CLIENT_COMPRESS
;
94 wfProfileIn( "dbconnect-$server" );
96 # The kernel's default SYN retransmission period is far too slow for us,
97 # so we use a short timeout plus a manual retry. Retrying means that a small
98 # but finite rate of SYN packet loss won't cause user-visible errors.
100 if ( ini_get( 'mysql.connect_timeout' ) <= 3 ) {
105 $this->installErrorHandler();
106 for ( $i = 0; $i < $numAttempts && !$this->mConn
; $i++
) {
110 if ( $this->mFlags
& DBO_PERSISTENT
) {
111 $this->mConn
= mysql_pconnect( $realServer, $user, $password, $connFlags );
113 # Create a new connection...
114 $this->mConn
= mysql_connect( $realServer, $user, $password, true, $connFlags );
116 #if ( $this->mConn === false ) {
118 #wfLogDBError("Connect loop error $iplus of $max ($server): " . mysql_errno() . " - " . mysql_error()."\n");
121 $error = $this->restoreErrorHandler();
123 wfProfileOut( "dbconnect-$server" );
125 # Always log connection errors
126 if ( !$this->mConn
) {
128 $error = $this->lastError();
130 wfLogDBError( "Error connecting to {$this->mServer}: $error\n" );
131 wfDebug( "DB connection error\n" .
132 "Server: $server, User: $user, Password: " .
133 substr( $password, 0, 3 ) . "..., error: " . $error . "\n" );
135 wfProfileOut( __METHOD__
);
136 return $this->reportConnectionError( $error );
139 if ( $dbName != '' ) {
140 wfSuppressWarnings();
141 $success = mysql_select_db( $dbName, $this->mConn
);
144 wfLogDBError( "Error selecting database $dbName on server {$this->mServer}\n" );
145 wfDebug( "Error selecting database $dbName on server {$this->mServer} " .
146 "from client host " . wfHostname() . "\n" );
148 wfProfileOut( __METHOD__
);
149 return $this->reportConnectionError( "Error selecting database $dbName" );
153 // Tell the server we're communicating with it in UTF-8.
154 // This may engage various charset conversions.
156 $this->query( 'SET NAMES utf8', __METHOD__
);
158 $this->query( 'SET NAMES binary', __METHOD__
);
160 // Set SQL mode, default is turning them all off, can be overridden or skipped with null
161 if ( is_string( $wgSQLMode ) ) {
162 $mode = $this->addQuotes( $wgSQLMode );
163 $this->query( "SET sql_mode = $mode", __METHOD__
);
166 $this->mOpened
= true;
167 wfProfileOut( __METHOD__
);
174 protected function closeConnection() {
175 return mysql_close( $this->mConn
);
179 * @param $res ResultWrapper
180 * @throws DBUnexpectedError
182 function freeResult( $res ) {
183 if ( $res instanceof ResultWrapper
) {
186 wfSuppressWarnings();
187 $ok = mysql_free_result( $res );
190 throw new DBUnexpectedError( $this, "Unable to free MySQL result" );
195 * @param $res ResultWrapper
196 * @return object|bool
197 * @throws DBUnexpectedError
199 function fetchObject( $res ) {
200 if ( $res instanceof ResultWrapper
) {
203 wfSuppressWarnings();
204 $row = mysql_fetch_object( $res );
207 $errno = $this->lastErrno();
208 // Unfortunately, mysql_fetch_object 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_object 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 fetchObject(): ' . htmlspecialchars( $this->lastError() ) );
219 * @param $res ResultWrapper
221 * @throws DBUnexpectedError
223 function fetchRow( $res ) {
224 if ( $res instanceof ResultWrapper
) {
227 wfSuppressWarnings();
228 $row = mysql_fetch_array( $res );
231 $errno = $this->lastErrno();
232 // Unfortunately, mysql_fetch_array does not reset the last errno.
233 // Only check for CR_SERVER_LOST and CR_UNKNOWN_ERROR, as
234 // these are the only errors mysql_fetch_object can cause.
235 // See http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html.
236 if( $errno == 2000 ||
$errno == 2013 ) {
237 throw new DBUnexpectedError( $this, 'Error in fetchRow(): ' . htmlspecialchars( $this->lastError() ) );
243 * @throws DBUnexpectedError
244 * @param $res ResultWrapper
247 function numRows( $res ) {
248 if ( $res instanceof ResultWrapper
) {
251 wfSuppressWarnings();
252 $n = mysql_num_rows( $res );
254 // Unfortunately, mysql_num_rows does not reset the last errno.
255 // We are not checking for any errors here, since
256 // these are no errors mysql_num_rows can cause.
257 // See http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-row.html.
258 // See https://bugzilla.wikimedia.org/42430
263 * @param $res ResultWrapper
266 function numFields( $res ) {
267 if ( $res instanceof ResultWrapper
) {
270 return mysql_num_fields( $res );
274 * @param $res ResultWrapper
278 function fieldName( $res, $n ) {
279 if ( $res instanceof ResultWrapper
) {
282 return mysql_field_name( $res, $n );
288 function insertId() {
289 return mysql_insert_id( $this->mConn
);
293 * @param $res ResultWrapper
297 function dataSeek( $res, $row ) {
298 if ( $res instanceof ResultWrapper
) {
301 return mysql_data_seek( $res, $row );
307 function lastErrno() {
308 if ( $this->mConn
) {
309 return mysql_errno( $this->mConn
);
311 return mysql_errno();
318 function lastError() {
319 if ( $this->mConn
) {
320 # Even if it's non-zero, it can still be invalid
321 wfSuppressWarnings();
322 $error = mysql_error( $this->mConn
);
324 $error = mysql_error();
328 $error = mysql_error();
331 $error .= ' (' . $this->mServer
. ')';
339 function affectedRows() {
340 return mysql_affected_rows( $this->mConn
);
344 * @param $table string
345 * @param $uniqueIndexes
347 * @param $fname string
348 * @return ResultWrapper
350 function replace( $table, $uniqueIndexes, $rows, $fname = 'DatabaseMysql::replace' ) {
351 return $this->nativeReplace( $table, $rows, $fname );
355 * Estimate rows in dataset
356 * Returns estimated count, based on EXPLAIN output
357 * Takes same arguments as Database::select()
359 * @param $table string|array
360 * @param $vars string|array
361 * @param $conds string|array
362 * @param $fname string
363 * @param $options string|array
366 public function estimateRowCount( $table, $vars = '*', $conds = '', $fname = 'DatabaseMysql::estimateRowCount', $options = array() ) {
367 $options['EXPLAIN'] = true;
368 $res = $this->select( $table, $vars, $conds, $fname, $options );
369 if ( $res === false ) {
372 if ( !$this->numRows( $res ) ) {
377 foreach ( $res as $plan ) {
378 $rows *= $plan->rows
> 0 ?
$plan->rows
: 1; // avoid resetting to zero
384 * @param $table string
385 * @param $field string
386 * @return bool|MySQLField
388 function fieldInfo( $table, $field ) {
389 $table = $this->tableName( $table );
390 $res = $this->query( "SELECT * FROM $table LIMIT 1", __METHOD__
, true );
394 $n = mysql_num_fields( $res->result
);
395 for( $i = 0; $i < $n; $i++
) {
396 $meta = mysql_fetch_field( $res->result
, $i );
397 if( $field == $meta->name
) {
398 return new MySQLField( $meta );
405 * Get information about an index into an object
406 * Returns false if the index does not exist
408 * @param $table string
409 * @param $index string
410 * @param $fname string
411 * @return bool|array|null False or null on failure
413 function indexInfo( $table, $index, $fname = 'DatabaseMysql::indexInfo' ) {
414 # SHOW INDEX works in MySQL 3.23.58, but SHOW INDEXES does not.
415 # SHOW INDEX should work for 3.x and up:
416 # http://dev.mysql.com/doc/mysql/en/SHOW_INDEX.html
417 $table = $this->tableName( $table );
418 $index = $this->indexName( $index );
420 $sql = 'SHOW INDEX FROM ' . $table;
421 $res = $this->query( $sql, $fname );
429 foreach ( $res as $row ) {
430 if ( $row->Key_name
== $index ) {
434 return empty( $result ) ?
false : $result;
441 function selectDB( $db ) {
442 $this->mDBname
= $db;
443 return mysql_select_db( $db, $this->mConn
);
451 function strencode( $s ) {
452 $sQuoted = mysql_real_escape_string( $s, $this->mConn
);
454 if( $sQuoted === false ) {
456 $sQuoted = mysql_real_escape_string( $s, $this->mConn
);
462 * MySQL uses `backticks` for identifier quoting instead of the sql standard "double quotes".
468 public function addIdentifierQuotes( $s ) {
469 return "`" . $this->strencode( $s ) . "`";
473 * @param $name string
476 public function isQuotedIdentifier( $name ) {
477 return strlen( $name ) && $name[0] == '`' && substr( $name, -1, 1 ) == '`';
484 $ping = mysql_ping( $this->mConn
);
489 mysql_close( $this->mConn
);
490 $this->mOpened
= false;
491 $this->mConn
= false;
492 $this->open( $this->mServer
, $this->mUser
, $this->mPassword
, $this->mDBname
);
499 * This will do a SHOW SLAVE STATUS
504 if ( !is_null( $this->mFakeSlaveLag
) ) {
505 wfDebug( "getLag: fake slave lagged {$this->mFakeSlaveLag} seconds\n" );
506 return $this->mFakeSlaveLag
;
509 return $this->getLagFromSlaveStatus();
515 function getLagFromSlaveStatus() {
516 $res = $this->query( 'SHOW SLAVE STATUS', __METHOD__
);
520 $row = $res->fetchObject();
524 if ( strval( $row->Seconds_Behind_Master
) === '' ) {
527 return intval( $row->Seconds_Behind_Master
);
532 * @deprecated in 1.19, use getLagFromSlaveStatus
536 function getLagFromProcesslist() {
537 wfDeprecated( __METHOD__
, '1.19' );
538 $res = $this->query( 'SHOW PROCESSLIST', __METHOD__
);
542 # Find slave SQL thread
543 foreach( $res as $row ) {
544 /* This should work for most situations - when default db
545 * for thread is not specified, it had no events executed,
546 * and therefore it doesn't know yet how lagged it is.
548 * Relay log I/O thread does not select databases.
550 if ( $row->User
== 'system user' &&
551 $row->State
!= 'Waiting for master to send event' &&
552 $row->State
!= 'Connecting to master' &&
553 $row->State
!= 'Queueing master event to the relay log' &&
554 $row->State
!= 'Waiting for master update' &&
555 $row->State
!= 'Requesting binlog dump' &&
556 $row->State
!= 'Waiting to reconnect after a failed master event read' &&
557 $row->State
!= 'Reconnecting after a failed master event read' &&
558 $row->State
!= 'Registering slave on master'
560 # This is it, return the time (except -ve)
561 if ( $row->Time
> 0x7fffffff ) {
572 * Wait for the slave to catch up to a given master position.
574 * @param $pos DBMasterPos object
575 * @param $timeout Integer: the maximum number of seconds to wait for synchronisation
576 * @return bool|string
578 function masterPosWait( DBMasterPos
$pos, $timeout ) {
579 $fname = 'DatabaseBase::masterPosWait';
580 wfProfileIn( $fname );
582 # Commit any open transactions
583 if ( $this->mTrxLevel
) {
584 $this->commit( __METHOD__
);
587 if ( !is_null( $this->mFakeSlaveLag
) ) {
588 $status = parent
::masterPosWait( $pos, $timeout );
589 wfProfileOut( $fname );
593 # Call doQuery() directly, to avoid opening a transaction if DBO_TRX is set
594 $encFile = $this->addQuotes( $pos->file
);
595 $encPos = intval( $pos->pos
);
596 $sql = "SELECT MASTER_POS_WAIT($encFile, $encPos, $timeout)";
597 $res = $this->doQuery( $sql );
599 if ( $res && $row = $this->fetchRow( $res ) ) {
600 wfProfileOut( $fname );
603 wfProfileOut( $fname );
608 * Get the position of the master from SHOW SLAVE STATUS
610 * @return MySQLMasterPos|bool
612 function getSlavePos() {
613 if ( !is_null( $this->mFakeSlaveLag
) ) {
614 return parent
::getSlavePos();
617 $res = $this->query( 'SHOW SLAVE STATUS', 'DatabaseBase::getSlavePos' );
618 $row = $this->fetchObject( $res );
621 $pos = isset( $row->Exec_master_log_pos
) ?
$row->Exec_master_log_pos
: $row->Exec_Master_Log_Pos
;
622 return new MySQLMasterPos( $row->Relay_Master_Log_File
, $pos );
629 * Get the position of the master from SHOW MASTER STATUS
631 * @return MySQLMasterPos|bool
633 function getMasterPos() {
634 if ( $this->mFakeMaster
) {
635 return parent
::getMasterPos();
638 $res = $this->query( 'SHOW MASTER STATUS', 'DatabaseBase::getMasterPos' );
639 $row = $this->fetchObject( $res );
642 return new MySQLMasterPos( $row->File
, $row->Position
);
651 function getServerVersion() {
652 return mysql_get_server_info( $this->mConn
);
659 function useIndexClause( $index ) {
660 return "FORCE INDEX (" . $this->indexName( $index ) . ")";
666 function lowPriorityOption() {
667 return 'LOW_PRIORITY';
673 public static function getSoftwareLink() {
674 return '[http://www.mysql.com/ MySQL]';
678 * @param $options array
680 public function setSessionOptions( array $options ) {
681 if ( isset( $options['connTimeout'] ) ) {
682 $timeout = (int)$options['connTimeout'];
683 $this->query( "SET net_read_timeout=$timeout" );
684 $this->query( "SET net_write_timeout=$timeout" );
688 public function streamStatementEnd( &$sql, &$newLine ) {
689 if ( strtoupper( substr( $newLine, 0, 9 ) ) == 'DELIMITER' ) {
690 preg_match( '/^DELIMITER\s+(\S+)/', $newLine, $m );
691 $this->delimiter
= $m[1];
694 return parent
::streamStatementEnd( $sql, $newLine );
698 * Check to see if a named lock is available. This is non-blocking.
700 * @param string $lockName name of lock to poll
701 * @param string $method name of method calling us
705 public function lockIsFree( $lockName, $method ) {
706 $lockName = $this->addQuotes( $lockName );
707 $result = $this->query( "SELECT IS_FREE_LOCK($lockName) AS lockstatus", $method );
708 $row = $this->fetchObject( $result );
709 return ( $row->lockstatus
== 1 );
713 * @param $lockName string
714 * @param $method string
715 * @param $timeout int
718 public function lock( $lockName, $method, $timeout = 5 ) {
719 $lockName = $this->addQuotes( $lockName );
720 $result = $this->query( "SELECT GET_LOCK($lockName, $timeout) AS lockstatus", $method );
721 $row = $this->fetchObject( $result );
723 if( $row->lockstatus
== 1 ) {
726 wfDebug( __METHOD__
. " failed to acquire lock\n" );
732 * FROM MYSQL DOCS: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_release-lock
733 * @param $lockName string
734 * @param $method string
737 public function unlock( $lockName, $method ) {
738 $lockName = $this->addQuotes( $lockName );
739 $result = $this->query( "SELECT RELEASE_LOCK($lockName) as lockstatus", $method );
740 $row = $this->fetchObject( $result );
741 return ( $row->lockstatus
== 1 );
746 * @param $write array
747 * @param $method string
748 * @param $lowPriority bool
751 public function lockTables( $read, $write, $method, $lowPriority = true ) {
754 foreach( $write as $table ) {
755 $tbl = $this->tableName( $table ) .
756 ( $lowPriority ?
' LOW_PRIORITY' : '' ) .
760 foreach( $read as $table ) {
761 $items[] = $this->tableName( $table ) . ' READ';
763 $sql = "LOCK TABLES " . implode( ',', $items );
764 $this->query( $sql, $method );
769 * @param $method string
772 public function unlockTables( $method ) {
773 $this->query( "UNLOCK TABLES", $method );
778 * Get search engine class. All subclasses of this
779 * need to implement this if they wish to use searching.
783 public function getSearchEngine() {
784 return 'SearchMySQL';
791 public function setBigSelects( $value = true ) {
792 if ( $value === 'default' ) {
793 if ( $this->mDefaultBigSelects
=== null ) {
794 # Function hasn't been called before so it must already be set to the default
797 $value = $this->mDefaultBigSelects
;
799 } elseif ( $this->mDefaultBigSelects
=== null ) {
800 $this->mDefaultBigSelects
= (bool)$this->selectField( false, '@@sql_big_selects' );
802 $encValue = $value ?
'1' : '0';
803 $this->query( "SET sql_big_selects=$encValue", __METHOD__
);
807 * DELETE where the condition is a join. MySql uses multi-table deletes.
808 * @param $delTable string
809 * @param $joinTable string
810 * @param $delVar string
811 * @param $joinVar string
812 * @param $conds array|string
813 * @param bool|string $fname bool
814 * @throws DBUnexpectedError
815 * @return bool|ResultWrapper
817 function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = 'DatabaseBase::deleteJoin' ) {
819 throw new DBUnexpectedError( $this, 'DatabaseBase::deleteJoin() called with empty $conds' );
822 $delTable = $this->tableName( $delTable );
823 $joinTable = $this->tableName( $joinTable );
824 $sql = "DELETE $delTable FROM $delTable, $joinTable WHERE $delVar=$joinVar ";
826 if ( $conds != '*' ) {
827 $sql .= ' AND ' . $this->makeList( $conds, LIST_AND
);
830 return $this->query( $sql, $fname );
834 * Determines how long the server has been up
838 function getServerUptime() {
839 $vars = $this->getMysqlStatus( 'Uptime' );
840 return (int)$vars['Uptime'];
844 * Determines if the last failure was due to a deadlock
848 function wasDeadlock() {
849 return $this->lastErrno() == 1213;
853 * Determines if the last failure was due to a lock timeout
857 function wasLockTimeout() {
858 return $this->lastErrno() == 1205;
862 * Determines if the last query error was something that should be dealt
863 * with by pinging the connection and reissuing the query
867 function wasErrorReissuable() {
868 return $this->lastErrno() == 2013 ||
$this->lastErrno() == 2006;
872 * Determines if the last failure was due to the database being read-only.
876 function wasReadOnlyError() {
877 return $this->lastErrno() == 1223 ||
878 ( $this->lastErrno() == 1290 && strpos( $this->lastError(), '--read-only' ) !== false );
884 * @param $temporary bool
885 * @param $fname string
887 function duplicateTableStructure( $oldName, $newName, $temporary = false, $fname = 'DatabaseMysql::duplicateTableStructure' ) {
888 $tmp = $temporary ?
'TEMPORARY ' : '';
889 $newName = $this->addIdentifierQuotes( $newName );
890 $oldName = $this->addIdentifierQuotes( $oldName );
891 $query = "CREATE $tmp TABLE $newName (LIKE $oldName)";
892 $this->query( $query, $fname );
896 * List all tables on the database
898 * @param string $prefix Only show tables with this prefix, e.g. mw_
899 * @param string $fname calling function name
902 function listTables( $prefix = null, $fname = 'DatabaseMysql::listTables' ) {
903 $result = $this->query( "SHOW TABLES", $fname);
907 foreach( $result as $table ) {
908 $vars = get_object_vars( $table );
909 $table = array_pop( $vars );
911 if( !$prefix ||
strpos( $table, $prefix ) === 0 ) {
912 $endArray[] = $table;
921 * @param $fName string
922 * @return bool|ResultWrapper
924 public function dropTable( $tableName, $fName = 'DatabaseMysql::dropTable' ) {
925 if( !$this->tableExists( $tableName, $fName ) ) {
928 return $this->query( "DROP TABLE IF EXISTS " . $this->tableName( $tableName ), $fName );
934 protected function getDefaultSchemaVars() {
935 $vars = parent
::getDefaultSchemaVars();
936 $vars['wgDBTableOptions'] = str_replace( 'TYPE', 'ENGINE', $GLOBALS['wgDBTableOptions'] );
937 $vars['wgDBTableOptions'] = str_replace( 'CHARSET=mysql4', 'CHARSET=binary', $vars['wgDBTableOptions'] );
942 * Get status information from SHOW STATUS in an associative array
944 * @param $which string
947 function getMysqlStatus( $which = "%" ) {
948 $res = $this->query( "SHOW STATUS LIKE '{$which}'" );
951 foreach ( $res as $row ) {
952 $status[$row->Variable_name
] = $row->Value
;
964 class MySQLField
implements Field
{
965 private $name, $tablename, $default, $max_length, $nullable,
966 $is_pk, $is_unique, $is_multiple, $is_key, $type;
968 function __construct( $info ) {
969 $this->name
= $info->name
;
970 $this->tablename
= $info->table
;
971 $this->default = $info->def
;
972 $this->max_length
= $info->max_length
;
973 $this->nullable
= !$info->not_null
;
974 $this->is_pk
= $info->primary_key
;
975 $this->is_unique
= $info->unique_key
;
976 $this->is_multiple
= $info->multiple_key
;
977 $this->is_key
= ( $this->is_pk ||
$this->is_unique ||
$this->is_multiple
);
978 $this->type
= $info->type
;
991 function tableName() {
992 return $this->tableName
;
1005 function isNullable() {
1006 return $this->nullable
;
1009 function defaultValue() {
1010 return $this->default;
1017 return $this->is_key
;
1023 function isMultipleKey() {
1024 return $this->is_multiple
;
1028 class MySQLMasterPos
implements DBMasterPos
{
1031 function __construct( $file, $pos ) {
1032 $this->file
= $file;
1036 function __toString() {
1037 return "{$this->file}/{$this->pos}";