$dbc->CommandTimeout = 90;Although this fixed the timeout problem, it brought out a latent problem. For the Special pages that return true from QueryPage::isExpensive, some of them -- including this one -- were trying to cache the results in a temporary table. This clashed with ADOdb's handling of OFFSET and LIMIT clauses in the SQL, so I have disabled the caching of results from "expensive" Special pages by not performing any activity in the overriden QueryPage::preProcessResults methods for these pages (Special:ShortPages, Special:LongPages, and Special:WantedPages in particular). Down the road, perhaps I'll think of a slightly more clever way to handle SQL OFFSET and LIMIT clauses and I can re-enable the preProcessResults methods.
2008-10-17
An issue of timing
For the Microsoft SQL Server version of the MediaWiki engine (at version 1.13 or so), everything was working except the Special:WantedPages and Special:AllPages. With the new design of Special:AllPages, I can't make heads or tails out of the SQL being generated, so I just reverted to the old behavior and it's fine. Special:WantedPages was different. The SQL was fine, but as the size of the wiki has grown (closing in on 200,000 articles), the database server was timing out before returning any results to the PHP. This problem was relatively easy to fix, using a not-widely-known property of the ADO Connection object, Connection::CommandTimeout. The default for Connection::CommandTimeout is 30 seconds, and no results were being returned for the (expensive) query until about 35 seconds or so. Once a row comes back from the server, ADO doesn't care how much time it takes to complete the query, but it's pretty sensitive to how long it takes for results to START coming back. I went into the ADOdb connector code and in the file adodb-ado5.inc.php added right before the return from the method ADODB_ado::_connect the single line
2008-08-06
Another recommended change for ADODB
I'm trying to minimize the number of changes needed to the wiki code, which is still fairly indecisive about retrieving data from the database via column names or column numbers. To make things as simple as possible, I set the fetch mode to ADODB_FETCH_BOTH rather than toggling back and forth between ADODB_FETCH_ASSOC and ADODB_FETCH_NUM, as I had been doing in the past. To make this work right, you'll need to make a change to the file adodb-ado5.inc.php. Change line 654 from
$this->fields = $this->GetRowAssoc(ADODB_ASSOC_CASE);to read
$this->fields = array_merge($this->fields, $this->GetRowAssoc(ADODB_ASSOC_CASE));Having done this, you'll have an array with both numeric keys and string keys.
2008-07-30
MediaWiki 1.13 and ADOdb 5.05
As of today, MediaWiki at approximately the 1.13 level is working while using the new ADOdb library that was released on 11 July 2008. I say approximately 1.13 because I actually work with the MediaWiki edge code from SubVersion, which is now identified as 1.14alpha. Far fewer changes are required these days to keep everything humming along. Thanks to MediaWiki guru "Simetrical" for incorporating some of my recommended changes, since I still don't have commit access to the shared repository.
Very minor changes were required to the ADOdb library, very similar to the changes that I'd made to the 4.94 release that I had been using. These changes are actually very simple.
First, after downloading and unzipping adodb5.05, find the file ./adodb.inc.php and change line 954 from
$array_2d = is_array($element0) && !is_object(reset($element0));to read just
$array_2d = is_array($element0);. If you don't make that change, you'll see error messages whenever you try to save a new article in your wiki. The last part of that statement apparently has to do with something unique to oci8 (Oracle?) descriptors, but wreaks havoc when used in conjunction with SQL Server. Second, also in ./adodb.inc.php, you'll need to modify the function FetchObject. MediaWiki code always expects objects returned from the database to have attributes named with all-lowercase letters. To make that happen, change line 3571 from
function FetchObject($isupper=true)to
function FetchObject($isupper=false)and change line 3588 from
else $n = $name;to
else $n = strtolower($name);. Without that change, you'll witness lots of member not found error messages. Third, in ./drivers/adodb-ado_mssql.inc.php, you'll have to change line 49 from
return $this->GetOne('select SCOPE_IDENTITY()');
to
return $this->GetOne('select @@IDENTITY');
or you'll get NULL for the last ID used after inserts.
Finally, to get the various pagers to work, you'll have to modify ./drivers/adodb-ado5.inc.php around line 423. Change the code from
if ($this->_currentRow > $row) return false;
@$rs->Move((integer)$row - $this->_currentRow-1); //adBookmarkFirst
to
$fReturn = $rs->Supports( 0x200 /*adMovePrevious*/ );
if (! $fReturn )
return false;
if ( $row == 0 ) {
@$rs->MoveFirst();
} else {
@$rs->Move((integer)$row - $this->_currentRow-1); //adBookmarkFirst
}
$this->_currentRow = $row;
Without that change, most of the special pages won't work. Note that SQL Server 2000 and SQL Server 2005 will both respond that they support adMovePrevious.
That's it, the rest of the changes can be found attached to the MediaWiki BugZilla report 9767, or may become part of the MediaWiki code base. The main attachments of interest are the SQL code and the DatabaseADODB.php file. These are fairly up-to-date as I write this, although I've evolved my schema somewhat since uploading the SQL code. It generally falls a little behind because I make the schema changes through Microsoft SQL Server Management Studio and then wind up reverse-engineering those changes into the SQL file.
2008-05-13
Getting ready to go into production
Things have come a long way since my last post. The wiki has been running continuously on a test box for a couple of months with no major glitches. It is now based on version 1.12, but is running on SQL Server 2005 instead of SQL Server 2000. Everything works except for images that have funny characters in the names. Even those sort of work (the thumbnails get generated and stored in the appropriate place), but IIS 6 seems unable to server the files from there. The code is stable enough so the boss has decided to put the thing into production. There's still some pieces in this screenshot that show some of the parts of the wiki that are still in development (The collaboration box shows some debugging messages from the presence / chat server). The screw behind the logo means that this is the development version and won't be in the production version.
2007-06-08
Hitting the transaction limit
It looks like it will be necessary to occasionally break down and re-establish the connection to the database in the
importDump.php utility. Running it for too long results in the following:C:\Inetpub\wwwroot\wiki193\mediawiki\maintenance>"C:\Program Files\Zend\Core\bin
\php.exe" ImportDump.php s:\enwiki-20070402-pages-articles.xml
100 (50.09 pages/sec 50.09 revs/sec)
200 (51.75 pages/sec 51.75 revs/sec)
300 (50.43 pages/sec 50.43 revs/sec)
400 (50.78 pages/sec 50.78 revs/sec)
500 (51.62 pages/sec 51.62 revs/sec)
600 (52.31 pages/sec 52.31 revs/sec)
700 (51.62 pages/sec 51.62 revs/sec)
800 (51.35 pages/sec 51.35 revs/sec)
900 (51.24 pages/sec 51.24 revs/sec)
1000 (51.57 pages/sec 51.57 revs/sec)
1100 (51.85 pages/sec 51.85 revs/sec)
1200 (52.11 pages/sec 52.11 revs/sec)
1300 (51.62 pages/sec 51.62 revs/sec)
1400 (51.11 pages/sec 51.11 revs/sec)
1500 (50.16 pages/sec 50.16 revs/sec)
1600 (50.30 pages/sec 50.30 revs/sec)
1700 (50.09 pages/sec 50.09 revs/sec)
1800 (50.31 pages/sec 50.31 revs/sec)
1900 (50.39 pages/sec 50.39 revs/sec)
2000 (50.37 pages/sec 50.37 revs/sec)
2100 (50.34 pages/sec 50.34 revs/sec)
2200 (50.30 pages/sec 50.30 revs/sec)
2300 (50.41 pages/sec 50.41 revs/sec)
2400 (50.51 pages/sec 50.51 revs/sec)
2500 (49.89 pages/sec 49.89 revs/sec)
2600 (50.17 pages/sec 50.17 revs/sec)
2700 (50.34 pages/sec 50.34 revs/sec)
2800 (50.08 pages/sec 50.08 revs/sec)
2900 (50.18 pages/sec 50.18 revs/sec)
3000 (49.94 pages/sec 49.94 revs/sec)
3100 (48.71 pages/sec 48.71 revs/sec)
3200 (48.58 pages/sec 48.58 revs/sec)
3300 (48.43 pages/sec 48.43 revs/sec)
3400 (48.49 pages/sec 48.49 revs/sec)
3500 (48.51 pages/sec 48.51 revs/sec)
3600 (48.54 pages/sec 48.54 revs/sec)
3700 (48.52 pages/sec 48.52 revs/sec)
3800 (25.39 pages/sec 25.39 revs/sec)
3900 (15.09 pages/sec 15.09 revs/sec)
4000 (10.41 pages/sec 10.41 revs/sec)
4100 (8.62 pages/sec 8.62 revs/sec)
4200 (6.98 pages/sec 6.98 revs/sec)
4300 (4.98 pages/sec 4.98 revs/sec)
4400 (3.79 pages/sec 3.79 revs/sec)
4500 (3.20 pages/sec 3.20 revs/sec)
4600 (2.66 pages/sec 2.66 revs/sec)
4700 (2.45 pages/sec 2.45 revs/sec)
4800 (2.25 pages/sec 2.25 revs/sec)
4900 (2.13 pages/sec 2.13 revs/sec)
5000 (1.99 pages/sec 1.99 revs/sec)
5100 (1.83 pages/sec 1.83 revs/sec)
5200 (1.70 pages/sec 1.70 revs/sec)
5300 (1.61 pages/sec 1.61 revs/sec)
5400 (1.56 pages/sec 1.56 revs/sec)
5500 (1.49 pages/sec 1.49 revs/sec)
5600 (1.43 pages/sec 1.43 revs/sec)
5700 (1.37 pages/sec 1.37 revs/sec)
5800 (1.34 pages/sec 1.34 revs/sec)
5900 (1.30 pages/sec 1.30 revs/sec)
6000 (1.28 pages/sec 1.28 revs/sec)
6100 (1.23 pages/sec 1.23 revs/sec)
6200 (1.19 pages/sec 1.19 revs/sec)
6300 (1.13 pages/sec 1.13 revs/sec)
6400 (1.09 pages/sec 1.09 revs/sec)
6500 (1.04 pages/sec 1.04 revs/sec)
6600 (1.01 pages/sec 1.01 revs/sec)
6700 (0.98 pages/sec 0.98 revs/sec)
6800 (0.96 pages/sec 0.96 revs/sec)
6900 (0.95 pages/sec 0.95 revs/sec)
7000 (0.94 pages/sec 0.94 revs/sec)
7100 (0.91 pages/sec 0.91 revs/sec)
7200 (0.89 pages/sec 0.89 revs/sec)
7300 (0.87 pages/sec 0.87 revs/sec)
7400 (0.86 pages/sec 0.86 revs/sec)
7500 (0.86 pages/sec 0.86 revs/sec)
7600 (0.87 pages/sec 0.87 revs/sec)
7700 (0.87 pages/sec 0.87 revs/sec)
7800 (0.88 pages/sec 0.88 revs/sec)
7900 (0.86 pages/sec 0.86 revs/sec)
8000 (0.86 pages/sec 0.86 revs/sec)
exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for SQL Server
Description: Cannot start more transactions on this session.' in C:\PHP\includes\adodb\drivers\adodb-ado5.inc.php:290
Stack trace:
#0 C:\PHP\includes\adodb\drivers\adodb-ado5.inc.php(290): com->BeginTrans()
#1 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\DatabaseADODB.php(1135): ADODB_ado->BeginTrans()
#2 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\JobQueue.php(170): DatabaseADODB->begin()
#3 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\HTMLCacheUpdate.php(81): Job::batchInsert(Array)
#4 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\HTMLCacheUpdate.php(46): HTMLCacheUpdate->insertJobs(Object(ResultWrapper))
#5 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\Title.php(2424): HTMLCacheUpdate->doUpdate()
#6 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\Article.php(2644): Title->touchLinks()
#7 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\SpecialImport.php(363): Article::onArticleCreate(Object(Title))
#8 [internal function]: WikiRevision->importOldRevision()
#9 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\DatabaseADODB.php(1048): call_user_func_array(Array, Array)
#10 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\SpecialImport.php(510): DatabaseADODB->deadlockLoop(Array)
#11 [internal function]: WikiImporter->importRevision(Object(WikiRevision))
#12 C:\Inetpub\wwwroot\wiki193\mediawiki\maintenance\importDump.php(58): call_user_func(Array, Object(WikiRevision))
#13 [internal function]: BackupReader->handleRevision(Object(WikiRevision), Object(WikiImporter))
#14 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\SpecialImport.php(761): call_user_func_array(Array, Array)
#15 [internal function]: WikiImporter->out_revision(Resource id #55, 'revision')
#16 C:\Inetpub\wwwroot\wiki193\mediawiki\includes\SpecialImport.php(426): xml_parse(Resource id #55, ']] and [[Botswa...', 0)
#17 C:\Inetpub\wwwroot\wiki193\mediawiki\maintenance\importDump.php(109): WikiImporter->doImport()
#18 C:\Inetpub\wwwroot\wiki193\mediawiki\maintenance\importDump.php(91): BackupReader->importFromHandle(Resource id #54)
#19 C:\Inetpub\wwwroot\wiki193\mediawiki\maintenance\importDump.php(129): BackupReader->importFromFile('s:\bauch\enwiki...')
#20 {main}
C:\Inetpub\wwwroot\wiki193\mediawiki\maintenance>2007-06-07
Testing (and fixing) the Maintenance Utilities
With the wiki up and stable for a couple of weeks now, it was time to try to push some limits. To do this, I downloaded a dump of the English Wikipedia. The command-line tool
ImportDump.php works for a while (albeit slower than I would have liked). Here's what prints to the console:
C:\Inetpub\wwwroot\wiki193\mediawiki\maintenance>"C:\Program Files\Zend\Core\bin
\php.exe" ImportDump.php s:\enwiki-20070402-pages-articles.xml
100 (3.27 pages/sec 3.27 revs/sec)
200 (1.90 pages/sec 1.90 revs/sec)
300 (1.40 pages/sec 1.40 revs/sec)
400 (1.13 pages/sec 1.13 revs/sec)
500 (1.04 pages/sec 1.04 revs/sec)
600 (1.01 pages/sec 1.01 revs/sec)
700 (0.79 pages/sec 0.79 revs/sec)
800 (0.74 pages/sec 0.74 revs/sec)
900 (0.68 pages/sec 0.68 revs/sec)
1000 (0.66 pages/sec 0.66 revs/sec)
1100 (0.63 pages/sec 0.63 revs/sec)
1200 (0.62 pages/sec 0.62 revs/sec)
1300 (0.59 pages/sec 0.59 revs/sec)
1400 (0.59 pages/sec 0.59 revs/sec)
A database query syntax error has occurred.
The last attempted database query was:
"COMMIT"
from within function "Database::deadlockLoop".
MySQL returned error "3902: e (jmolafbwikidev)"
DatabaseADODB.php. In particular, in the deadlockLoop() method. It also looks like my indexes on the page table need a little tweeking.
After that, rebuildrecentchanges.php doesn't want to work at all
C:\Inetpub\wwwroot\wiki193\mediawiki\maintenance>"C:\Program Files\Zend\Core\bin
\php.exe" rebuildrecentchanges.php
PHP Notice: Undefined variable: wgDBadminuser in C:\Inetpub\wwwroot\wiki193\med
iawiki\maintenance\rebuildrecentchanges.php on line 15
PHP Notice: Undefined variable: wgDBadminpassword in C:\Inetpub\wwwroot\wiki193
\mediawiki\maintenance\rebuildrecentchanges.php on line 16
Loading from page and revision tables...
A database query syntax error has occurred.
The last attempted database query was:
"INSERT INTO [recentchanges] (rc_timestamp,rc_cur_time,rc_user,rc_user_text,rc_
namespace,rc_title,rc_comment,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_
last_oldid,rc_type) SELECT TOP 5000 rev_timestamp,rev_timestamp,rev_user,rev_us
er_text,page_namespace,page_title,rev_comment,rev_minor_edit,0,page_is_new,page_
id,rev_id,0, IF(page_is_new != 0, 1, 0) FROM [page],[revision] WHERE (rev_tim
estamp > '20070531214411') AND (rev_page=page_id) ORDER BY rev_timestamp DES
C"
from within function "rebuildRecentChangesTablePass1".
MySQL returned error "170: n (jmolafbwikidev)"
conditional from the Database class to the DatabaseADODB class, where it is implemented as:
function conditional( $cond, $trueVal, $falseVal ) {
return " CASE WHEN $cond THEN $trueVal ELSE $falseVal END ";
}C:\Inetpub\wwwroot\wiki193\mediawiki\maintenance>"C:\Program Files\Zend\Core\bin
\php.exe" rebuildrecentchanges.php
PHP Notice: Undefined variable: wgDBadminuser in C:\Inetpub\wwwroot\wiki193\med
iawiki\maintenance\rebuildrecentchanges.php on line 15
PHP Notice: Undefined variable: wgDBadminpassword in C:\Inetpub\wwwroot\wiki193
\mediawiki\maintenance\rebuildrecentchanges.php on line 16
Loading from page and revision tables...
Updating links...
A database query syntax error has occurred.
The last attempted database query was:
"SELECT rev_id FROM [revision] WHERE rev_page=87 AND rev_timestamp<'200706052
02808' ORDER BY rev_timestamp DESC LIMIT 1"
from within function "".
MySQL returned error "170: n (jmolafbwikidev)"
rebuildRecentChangesTablePass2() in rebuildrecentchanges.inc as follows:
if ( $wgDBtype == 'adodb' ) {
$sql2 = "SELECT TOP 1 rev_id FROM $revision " .
"WHERE rev_page={$lastCurId} ".
"AND rev_timestamp < '{$emit}' ORDER BY rev_timestamp DESC";
} else {
$sql2 = "SELECT rev_id FROM $revision " .
"WHERE rev_page={$lastCurId} ".
"AND rev_timestamp<'{$emit}' ORDER BY rev_timestamp DESC LIMIT 1";
}2007-06-05
Changes required
In addition to the obvious (addition of the code to support Microsoft SQL Server), some other files need to change, and a few other files need to be added to the system.
includes/Autoloader.php (change)
This file needs just a couple of additions, as follows
In
includes/Database.php
To this base class, I've added two new abstract methods
These are used very little, and only by a couple of the
includes/GlobalFunctions.php In
In includes/MagicWord.php, I changed a line in
In includes/Pager.php, method
includes/Autoloader.php (change)
This file needs just a couple of additions, as follows
static $localClasses = array(
# Includes
'DatabaseADODB' => 'includes/DatabaseADODB.php',
...
'SearchADODB' => 'includes/SearchADODB.php',
...
includes/BagOStuff.php (change)In
SqlBagOStuff::get($key), the SQL needs a little fix, from
"SELECT value,exptime FROM $0 WHERE keyname='$1'", $key);
to
"SELECT value,exptime FROM $0 WHERE keyname=$1", $key);
SqlBagOStuff::delete($key,$time=0).
In the methods _serialize(&$data) and _unserialize($serial), I've had to remove the calls to gzdeflate and ginflate, since I couldn't get them to behave consistently.includes/Database.php
To this base class, I've added two new abstract methods
public function setFetchModeAssoc() {
}
public function setFetchModeNum() {
}
These are used very little, and only by a couple of the
Special: pages. By default, the ADODB connection operates in numeric fetch mode (i.e., it returns records in an array subscripted by numbers). After a call to setFetchModeAssoc the connection operates in associative fetch mode (i.e., it returns records in an array subscripted by field names). This is different from the MySQL connection, which returns arrays that can be indexed both ways.includes/GlobalFunctions.php In
function wfShellExec($cmd, &$retval=null), I changed the line
$cmd = '"' . $cmd . '"';
$cmd = 'cmd /C ' . '"' . str_replace( '/', '\\', $cmd) . '"';
In includes/MagicWord.php, I changed a line in
MagicWord::initRegex() from
$case = $this-mCaseSensitive ? '' : 'iu';
$case = $this->mCaseSensitive ? '' : 'i';
matchAndRemove(&$text) and matchStartAndRemove(&$text)In includes/Pager.php, method
IndexPager::reallyDoQuery($offset,$limit,$ascending) the line
$res = $this->mDb->select( $tables, $fields, $conds, $fname, $options );
$this->mDb->setFetchModeAssoc();
$res = $this->mDb->select( $tables, $fields, $conds, $fname, $options );
$this->mDb->setFetchModeNum();
Subscribe to:
Posts (Atom)
