[DO NOT USE] Microsoft SQL Server/MSSQL support (tracking)
Closed, InvalidPublic

Description

Converted to project: MSSQL

Details

Reference
bz9767
There are a very large number of changes, so older changes are hidden. Show Older Changes

dj.bauch wrote:

includes\db\DatabaseADODB.php

Updated PHP code for the MS SQL Server MediaWiki version.

Attached:

ayg wrote:

(In reply to comment #21)

My Microsoft SQL Server code is also using DATETIME columns internally where
needed, but is exposing those tables with DATETIME columns only through views
that convert to VARCHAR(14). Each of those views also has INSERT and UPDATE
triggers to do the reverse conversion from VARCHAR(14) back to DATETIME.

This seems unnecessarily elaborate. You can just override Database::timestamp.

I am uploading my
latest version of the SQL code, which now also replaces the LOGGING table with
a view. This lets me avoid having to modify the PHP code that adds logging
records so that it can continue to believe that it's updating the log_id
IDENTITY column.

Um, why do you need to do that?

dj.bauch wrote:

Patch for includes/specials/SpecialMostCategories.php

This patch is a trial balloon. It illustrates a fix to the GROUP BY SQL clause. This is what I get from Beyond Compare when I export a difference report in "Unified Diff" format. This most closely resembles in format what I've seen in another MediaWiki bugzilla page. Is this the format expected for a patch? Do I want to remove those first couple of lines, or are they OK as-is?

Attached:

ayg wrote:

Generally the output of the command "svn diff" is preferred. If you do use an ordinary diff -u command, or something similar, it should be relative to the installation directory of MediaWiki or some subdirectory thereof, not to the filesystem root; otherwise it won't apply cleanly. I'm also not sure if it's a great idea to use backslashes, since I don't know if GNU patch will accept them.

Anyway, I've switched over all the uses of GROUP BY 1,2,3 and similar that grep found, in r37805. Should make it into the 1.13 release if it doesn't cause problems.

dj.bauch wrote:

(In reply to comment #24)

(In reply to comment #21)
> My Microsoft SQL Server code is also using DATETIME columns internally where
> needed, but is exposing those tables with DATETIME columns only through views
> that convert to VARCHAR(14). Each of those views also has INSERT and UPDATE
> triggers to do the reverse conversion from VARCHAR(14) back to DATETIME.

This seems unnecessarily elaborate. You can just override Database::timestamp.

I sort of took the approach you recommend at first (actually fiddling with wfTimestamp in GlobalFunctions.php) but I couldn't get everything to work that way. In my mind, I wasted quite a bit of time on that approach. The SQL in the views actually seems much less elaborate than the regexes and so forth in the wfTimestamp function. It also has the benefit of actually working for me.

> I am uploading my
> latest version of the SQL code, which now also replaces the LOGGING table with
> a view. This lets me avoid having to modify the PHP code that adds logging
> records so that it can continue to believe that it's updating the log_id
> IDENTITY column.

Um, why do you need to do that?

I only made that change today. The only motivation is that I then have to modify one less PHP file. SQL Server, unlike MySQL, will not let you INSERT a value for an IDENTITY (AUTO_INCREMENT) column. By vectoring through the view, the PHP thinks it's inserting that field, but it's stripped out by the INSERT trigger.

dj.bauch wrote:

(In reply to comment #26)

Generally the output of the command "svn diff" is preferred. If you do use an
ordinary diff -u command, or something similar, it should be relative to the
installation directory of MediaWiki or some subdirectory thereof, not to the
filesystem root; otherwise it won't apply cleanly. I'm also not sure if it's a
great idea to use backslashes, since I don't know if GNU patch will accept
them.

Anyway, I've switched over all the uses of GROUP BY 1,2,3 and similar that grep
found, in r37805. Should make it into the 1.13 release if it doesn't cause
problems.

Thanks, they (SpecialMostcategories.php, SpecialMostimages.php, SpecialMostlinked.php, SpecialMostlinkedCategories.php, SpecialMostlinkedtemplates.php, SpecialMostrevisions.php, SpecialWantedcategories.php, and SpecialWantedpages.php) look the same in r37805 as in my working copy. I had not had a chance to confirm that the semantics is the same between MySQL and SQL Server for those changes, but they look right to me.

I'll have to work on the diff problem. I'm actually using TortoiseSVN with Beyond Compare as my "diff". Probably means nothing to a Linux person -- but the bottom line is that I don't currently have the "svn diff" command. My trial balloon was just that, to see how close I was with what I'm already using. I'll look for some better exemplars of what the diffs should look like on the bugzilla and mimic those.

ayg wrote:

(In reply to comment #27)

I sort of took the approach you recommend at first (actually fiddling with
wfTimestamp in GlobalFunctions.php) but I couldn't get everything to work that
way. In my mind, I wasted quite a bit of time on that approach. The SQL in the
views actually seems much less elaborate than the regexes and so forth in the
wfTimestamp function. It also has the benefit of actually working for me.

You really should not need to do any kind of elaborate regex. You should only need to do something like add two lines to wfTimestamp in GlobalFunctions.php, like case TS_MSSQL: return gmdate( 'Ymd H:i:s', $uts ) . ' UTC'; or whatever format is required, and then have the timestamp function consist of return wfTimestamp(TS_MSSQL,$ts);

I only made that change today. The only motivation is that I then have to
modify one less PHP file. SQL Server, unlike MySQL, will not let you INSERT a
value for an IDENTITY (AUTO_INCREMENT) column. By vectoring through the view,
the PHP thinks it's inserting that field, but it's stripped out by the INSERT
trigger.

This is what the nextSequenceValue() function is for. Add an empty class named something like MsSqlSequenceValue, have nextSequenceValue return new MsSqlSequenceValue;, and silently drop any value of that class that's provided in select() or wherever.

Patches like this should not go into the software. You should not have to hack on views and stuff to reinterpret MySQL input on the database side. Any incompatibility should be handled by a) improving the abstraction layer and b) fixing queries in the software (but carefully!). The two cases you have a problem with are already dealt with by PostgreSQL, which uses its own timestamp format and has its own unique way of doing auto-increment, and whoever does the MSSQL support should emulate that approach for these cases and others. (But in practice I imagine none of the core developers is going to be reviewing that code much, so if anyone gets commit access it's their lookout.)

(In reply to comment #28)

I'll have to work on the diff problem. I'm actually using TortoiseSVN with
Beyond Compare as my "diff". Probably means nothing to a Linux person -- but
the bottom line is that I don't currently have the "svn diff" command.

I used to use TortoiseSVN when I still used Windows, actually. You want to select Create Patch from the appropriate context menu; that basically runs svn diff.

dj.bauch wrote:

Trial balloon number 2

Patch for includes/specials/SpecialNewimages created with TortoiseSVN Create Patch command. Is this the desired format?

Attached:

ayg wrote:

Yes, but that patch is completely unacceptable. Database logic must not be mixed into the code. That's what the abstraction layer is for. You need to override makeSelectOptions to output TOP in the right place when the LIMIT option is provided. Queries like these that use $db->query() instead of the wrappers like select() should be converted to use the wrappers if it's a problem. (You could also try to work around raw queries by trying to parse them, but that's really not ideal.)

dj.bauch wrote:

(In reply to comment #31)

Yes, but that patch is completely unacceptable. Database logic must not be
mixed into the code. That's what the abstraction layer is for. You need to
override makeSelectOptions to output TOP in the right place when the LIMIT
option is provided. Queries like these that use $db->query() instead of the
wrappers like select() should be converted to use the wrappers if it's a
problem. (You could also try to work around raw queries by trying to parse
them, but that's really not ideal.)

Thanks, I was really more concerned about the format of the patch than the patch itself, although it does reflect the way my code looks right now. When that file is "fixed" to use the select wrapper rather than the query method I'll be among the first to breathe a sigh of relief. It seems like less and less of the database logic is embedded in the MediaWiki code since 1.9.3, when I first started my port, and with each release I need to make fewer and fewer changes to make it work with SQL Server. DatabaseADODB.php does override makeSelectOptions, but it currently isn't doing anything useful with the LIMIT option. I'll fix that tomorrow, because I'm done for tonight.

ayg wrote:

Are you sure you want to continue to work on this? We do have some MSSQL support in 1.13, and apparently Microsoft is interested in making it good for later versions. If you do continue work, I suggest you a) base your work on the existing DatabaseMssql.php, and b) submit individual small patches for individual things that are broken about it.

I'm repurposing this to be a tracking bug, since we already have basic support for MSSQL. New bugs should be filed, blocking this, for any specific issues that arise. I don't suggest you expect to get patches accepted until we have someone with commit access working on MSSQL, since as I said, not many of us have an MSSQL install to test with. You could ask for commit access yourself, of course, as Joel is.

dj.bauch wrote:

(In reply to comment #33)

Are you sure you want to continue to work on this? We do have some MSSQL
support in 1.13, and apparently Microsoft is interested in making it good for
later versions. If you do continue work, I suggest you a) base your work on
the existing DatabaseMssql.php, and b) submit individual small patches for
individual things that are broken about it.

I do. Perhaps I have not been clear, but I have everything working already, including quite a few extensions and have a wiki that's in use with about 200,000 articles in it. The main thing I don't have working to my satisfaction, but which does not seem to be related to SQL Server, is the rendering of images that have non-ASCII characters in their file names.

I'm repurposing this to be a tracking bug, since we already have basic support
for MSSQL. New bugs should be filed, blocking this, for any specific issues
that arise. I don't suggest you expect to get patches accepted until we have
someone with commit access working on MSSQL, since as I said, not many of us
have an MSSQL install to test with. You could ask for commit access yourself,
of course, as Joel is.

I suppose if I could get full functionality out of the MSSQL interface, I'd be willing to switch (back) to it. I adopted the ADODB interface, however, because I got stuck when I tried to use MSSQL. It was a dead end back then -- but that was a slightly earlier release of PHP. Perhaps the problems I experienced with it have been corrected since then.

a-joelpe wrote:

(In reply to comment #34)

(In reply to comment #33)
> Are you sure you want to continue to work on this? We do have some MSSQL
> support in 1.13, and apparently Microsoft is interested in making it good for
> later versions. If you do continue work, I suggest you a) base your work on
> the existing DatabaseMssql.php, and b) submit individual small patches for
> individual things that are broken about it.

I do. Perhaps I have not been clear, but I have everything working already,
including quite a few extensions and have a wiki that's in use with about
200,000 articles in it. The main thing I don't have working to my satisfaction,
but which does not seem to be related to SQL Server, is the rendering of images
that have non-ASCII characters in their file names.

Our biggest concern is unicode support since it requires the database to use nvarchar for everything which cuts the storable character size in half and causes indexing problems. We're not sure what the best balance is for that yet.

> I'm repurposing this to be a tracking bug, since we already have basic support
> for MSSQL. New bugs should be filed, blocking this, for any specific issues
> that arise. I don't suggest you expect to get patches accepted until we have
> someone with commit access working on MSSQL, since as I said, not many of us
> have an MSSQL install to test with. You could ask for commit access yourself,
> of course, as Joel is.

I suppose if I could get full functionality out of the MSSQL interface, I'd be
willing to switch (back) to it. I adopted the ADODB interface, however, because
I got stuck when I tried to use MSSQL. It was a dead end back then -- but that
was a slightly earlier release of PHP. Perhaps the problems I experienced with
it have been corrected since then.

Our patch uses the new sqlsrv driver recently released at: http://www.microsoft.com/sql/technologies/php/default.mspx

ayg wrote:

(In reply to comment #34)

As I say, I suggest you e-mail Tim Starling or Brion Vibber and ask for commit access if you want to get this committed to the software. Almost no one who currently has commit access is likely to be able to review your patches. When someone does get commit access, or multiple someones, it's up to them to decide how they want it to work, I guess, since we appear to have three separate implementations at this point. (But it can't stick $wgDBType in the core code or anything like that.)

(In reply to comment #35)

Our biggest concern is unicode support since it requires the database to use
nvarchar for everything which cuts the storable character size in half and
causes indexing problems. We're not sure what the best balance is for that yet.

The default MySQL schema just has all of the text columns set to binary, and stores raw UTF-8 bytes in them. This works very nicely: there's no wasted space, you can deal with all code points (even outside the BMP, which MySQL utf8 can't, at least historically), and it's not like any string operations are done on the database side that really need to use characters instead of bytes.

a-joelpe wrote:

(In reply to comment #36)

(In reply to comment #34)

As I say, I suggest you e-mail Tim Starling or Brion Vibber and ask for commit
access if you want to get this committed to the software. Almost no one who
currently has commit access is likely to be able to review your patches. When
someone does get commit access, or multiple someones, it's up to them to decide
how they want it to work, I guess, since we appear to have three separate
implementations at this point. (But it can't stick $wgDBType in the core code
or anything like that.)

We're using a separate namespace (mssqlnative) so these two can be in the code base concurrently since they use different drivers anyway. I don't know if you want to add the ADOdb dependency just for one driver though, but they can exist separately.

(In reply to comment #35)
> Our biggest concern is unicode support since it requires the database to use
> nvarchar for everything which cuts the storable character size in half and
> causes indexing problems. We're not sure what the best balance is for that yet.

The default MySQL schema just has all of the text columns set to binary, and
stores raw UTF-8 bytes in them. This works very nicely: there's no wasted
space, you can deal with all code points (even outside the BMP, which MySQL
utf8 can't, at least historically), and it's not like any string operations are
done on the database side that really need to use characters instead of bytes.

There is a problem with making the fields binary, you can't index binary fields, so that idea was one of the first to go sadly.

dj.bauch wrote:

Adds DatabaseADODB.php to AutoLoader

One-line addition to AutoLoader adds recognition of DatabaseADODB. I am continuing with ADODB for now because the new Microsoft connector for PHP is a Community Technology Preview (CTP) and not authorized for production use and the ADODB connector is working fine.

Attached:

a-joelpe wrote:

(In reply to comment #38)

Created an attachment (id=5093) [details]
Adds DatabaseADODB.php to AutoLoader

One-line addition to AutoLoader adds recognition of DatabaseADODB. I am
continuing with ADODB for now because the new Microsoft connector for PHP is a
Community Technology Preview (CTP) and not authorized for production use and
the ADODB connector is working fine.

RTM is closer than you think =]

I'm working on final bugs as we speak as well in my driver using the new sqlsrv driver

a-joelpe wrote:

We're proud to announce the release of the sqlsrv PHP driver, you can learn more about the release here:
http://blogs.msdn.com/sqlphp/archive/2008/07/28/microsoft-sql-server-2005-driver-for-php-v1-0-has-released.aspx

Who should be our contact for submitting our patch? You may have heard about our contribution to ADOdb:
http://www.theregister.co.uk/2008/07/25/microsoft_gpl/

You may not have known that our submission is through email. Who can I talk to about our patch?

ayg wrote:

(In reply to comment #40)

Who should be our contact for submitting our patch? You may have heard about
our contribution to ADOdb:
http://www.theregister.co.uk/2008/07/25/microsoft_gpl/

You may not have known that our submission is through email. Who can I talk to
about our patch?

As I said in comment #15, you should talk to Tim Starling (tstarling@wikimedia.org) or Brion Vibber (brion@wikimedia.org). I would suggest again that you ask for commit access instead of just asking that the patch be submitted. That way you can maintain the patch yourself when it inevitably breaks due to some MySQL-centrism that someone adds.

If you don't have commit access, you'll have to repeatedly bug people to commit your patches if you want to maintain the support. This is not only slow and annoying, it's also pointless, since you probably aren't going to be able to find anyone who has an MSSQL installation to test the patch on. If everyone is just going to blindly submit your patches without providing independent testing and scrutiny, you may as well be able to do so without the middleman.

As far as submitting patches without commit access, they're generally submitted on Bugzilla, e.g., here.

GarrettS wrote:

As I said in comment #15, you should talk to Tim Starling
(tstarling@wikimedia.org) or Brion Vibber (brion@wikimedia.org). I would
suggest again that you ask for commit access instead of just asking that the
patch be submitted. That way you can maintain the patch yourself when it
inevitably breaks due to some MySQL-centrism that someone adds.

If you don't have commit access, you'll have to repeatedly bug people to commit
your patches if you want to maintain the support. This is not only slow and
annoying, it's also pointless, since you probably aren't going to be able to
find anyone who has an MSSQL installation to test the patch on. If everyone is
just going to blindly submit your patches without providing independent testing
and scrutiny, you may as well be able to do so without the middleman.

As far as submitting patches without commit access, they're generally submitted
on Bugzilla, e.g., here.

Howdy Simetrical,

I'm Garrett Serack, I'm the Open Source Community Developer here at Microsoft. (Joel works for me)

We've been talking this thru a lot, and I completely agree with you, I think we're better off submitting the patches via subversion, rather than email, posting or ... whatever. The 'email' patches has up until now, been what was 'convenient', but by no means a requirement :D

I'll be dropping Tim and/or Brion a mail today on this, and requesting access.

thanks,

Garrett Serack

dj.bauch wrote:

(In reply to comment #42)

I've also requested commit access from Brion, so I expect to hear something real soon now. I've downloaded the SQL Server 2005 driver from Microsoft and started trying to get the AdventureWorks sample from the documentation going. So far, it hasn't worked for me -- but the error messages I'm seeing look like:
SQLSTATE: IM002
Code: 0
Message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
The SQL Server driver isn't sitting on top of ODBC is it?

I think I'd prefer it if we have *one* MSSQL driver rather than two. :)

Would it be possible to compare codebases and combine if necessary to a single version?

a-joelpe wrote:

I think for the time being that keeping them separate would be wise. The new driver isn't part of the default php installation yet and requires PHP 5+ as well as being hosted on windows. As time passes and adoption grows we can merge them, but for now we should keep things separated

dj.bauch wrote:

(In reply to comment #44)

I think I'd prefer it if we have *one* MSSQL driver rather than two. :)

Would it be possible to compare codebases and combine if necessary to a single
version?

I'd be willing to work on keeping things as simple as possible. There are a few things that I would imagine would have to be fairly common to both versions anyway, like the database schema. I'm anxious to share my code, and I'm in the process of looking at the new ADODB library that was released on 11 July 2008. I'm also looking at the new SQL Server driver from Microsoft. I would also like, at some point, to contribute back my Lucene.Net version of the full text search service.

GarrettS wrote:

(In reply to comment #43)

The SQL Server driver isn't sitting on top of ODBC is it?

It does sit on top of the ODBC driver; this was the most efficient and flexible way to build the driver. The SQL Server team assures me it's not a performance problem. :D

We can certainly work together; Joel is about to disappear for 10 days on Vacation, when he gets back, we can see where we can sync our efforts.

Garrett

dj.bauch wrote:

This week I downloaded the new ADODB driver from SourceForge
(http://adodb.sourceforge.net/). Some minor tweaks to the library are required
in order to make it work with MediaWiki. Only a few changes are required, as
discussed on my blog at
http://mediawikiworker.blogspot.com/2008/07/mediawiki-113-and-adodb-505.html
While I was working on that today, I see that the mssqlnative driver showed up on
sourceforge. I'll see how that one goes...

rstefanov wrote:

Hello,

I tried to connect my instance to mssql using ADODB. As I can see mediawiki 1.13.0rc2 contains the DatabaseMssql.php layer. However I wasn’t able to find DatabaseADODB.php? I installed the adodb layer in my root Mediawiki/adodb but it doesn’t seem to be working properly. Are there any instructions of how to integrate ADODB with mediawiki?

Many Thanks
Rocco

(In reply to comment #49)

Hello,
I tried to connect my instance to mssql using ADODB. As I can see mediawiki
1.13.0rc2 contains the DatabaseMssql.php layer. However I wasn’t able to find
DatabaseADODB.php? I installed the adodb layer in my root Mediawiki/adodb but
it doesn’t seem to be working properly. Are there any instructions of how to
integrate ADODB with mediawiki?

MSSQL support is experimental, and will probably be broken in some situations. ADODB doesn't seem to be supported at all at the moment, and DatabaseADODB.php was abandoned.

dj.bauch wrote:

ADODB isn't supported, but it works fine. It isn't broken, and DatabaseADODB.php has not been abandoned by me, I just don't have any permission to get it included.

dj.bauch wrote:

MS SQL Server script

Updated the MS SQL server script (tables.sql) to fix problems uncovered when testing with a new MediaWiki installation. Supports the tables needed for MediaWiki 1.13.

attachment tables.sql ignored as obsolete

dj.bauch wrote:

MS SQL Server Script

This update to tables.sql fixes a problem that sometimes occurs when deleting a page from the wiki. The logging table view now trims the comment to 255 characters. Sometimes the comment was longer than 255 due to escaped quotes (counted by PHP as single characters, I suppose, but by SQL Server as two characters).

attachment tables.sql ignored as obsolete

cp8 wrote:

Anybody progressing on this bug? My understanding is that for any of the 2 alternatives described changes in config/index.php and/or includes/db/DatabaseMssql.php are required. Any ideas how this is progressing? Thanks!

a-joelpe wrote:

(In reply to comment #54)

Anybody progressing on this bug? My understanding is that for any of the 2
alternatives described changes in config/index.php and/or
includes/db/DatabaseMssql.php are required. Any ideas how this is progressing?
Thanks!

Yes, we're nearing the end of our dev cycle for releasing support for the new sqlsrv driver

dj.bauch wrote:

Yes. Mediawiki is fully functional on Microsoft Windows with SQL Server and IIS. I do not have the ability to check in any of my changes to the Subversion repository or I'd have done so by now. There are not enormous changes to be made, but I expect that most of what I've done is applicable whether using the new Microsoft sqlsrv driver or (as I've done) the AdoDB library.

dj.bauch wrote:

MS SQL Server Script

Includes a change for the category table. cat_title must use COLLATE SQL_Latin1_General

attachment tables.sql ignored as obsolete

dj.bauch wrote:

Patch for MediaWiki 1.1.4.0 to adapt to MS SQL support

This patch contains the changes to be applied to the 1.14.0 release to adapt the release to run on Windows with Microsoft SQL Server 2000 or SQL Server 2005. I've made it to simplify the installation for other users based on the experiences I gained going through the installation with another user. Yes, there is at least one other user that I'm aware of. I had been tracking changes against the trunk rather than against the releases -- but since the trunk is always changing it was easier to have him install the 1.14.0 release and then patch in the changes.

Attached:

dj.bauch wrote:

MS SQL Server Script

Updated MS SQL script (./maintenance/sqlserver/tables.sql) to be used in conjunction with 1.14.0 patch. This script also creates the new tables (change_tag, tag_summary, valid_tag) introduced since 1.14.0.

attachment tables.sql ignored as obsolete

IAlex added a comment.Apr 8 2009, 8:15 PM

The patch (id 6005) seems to contains some strange modifications:

  • AutoLoader.php: where's the file?
  • Defines.php: this has nothing to do in core
  • Revision.php: don't comment that out, it's needed for PostgreSQL
  • SpecialShortpages.php, SpecialWantedcategories.php, SpecialWantedpages.php: why commenting out the link existence batch?
  • SpecialWantedtemplates.php: already fixed in r47618
  • SpecialListfiles.php: already fixed in r48350

dj.bauch wrote:

(In reply to comment #60)

The patch (id 6005) seems to contains some strange modifications:

  • AutoLoader.php: where's the file?
  • Defines.php: this has nothing to do in core
  • Revision.php: don't comment that out, it's needed for PostgreSQL
  • SpecialShortpages.php, SpecialWantedcategories.php, SpecialWantedpages.php: why commenting out the link existence batch?
  • SpecialWantedtemplates.php: already fixed in r47618
  • SpecialListfiles.php: already fixed in r48350

Yes, the modifications are strange in that they are not necessarily intended to replace the MySQL/PostgreSQL code in the trunk. They're only useful to someone trying to run on SQL Server. The file missing from autoloader.php is an attachment to the bug report (DatabaseADODB.php). It doesn't get picked up when I make the diff because it didn't exist before. The extra lines from my defines.php are for my extra namespaces and are optional. I'm assuming the commented out line you're referring to is the attempt to insert an ID in the text table. It won't work in my text table because the column is an auto-number column. I could make a view to eliminate this issue if necessary. The link existence batches don't work with the ADODB database adapter and not doing them didn't change the desired outcome. I may alter the ADODB database adapter to allow them to work at some time, but I don't own that code or have commit access on that project. Those last two already fixed issues were actually ones that I asked for but couldn't make myself (see comment #13 to this bug), since I don't have commit access. If I remember correctly, Tim Starling made the fixes back then.
In addition to the patch file, you would need the tables.sql attachment above, the databaseadodb.php attachment above, and a patch to the ADODB library (which I could place here too, I suppose).

ayg wrote:

I guess you should re-request commit access. I don't think you were denied because anyone wanted to deny you -- probably the request just got lost at some point, because granting commit access is one facet of the MediaWiki development process that seems to be particularly dysfunctional right now. If you still want it, I'd remind Brion and/or Tim every month or two if we don't get a saner system in place soon.

yedidia wrote:

OS: win2003, MSSQL 2000 (8.0)
first thanks for the patch,
I've troubles w/ installation
patched 1.14.0 w/ your patch, updated tables.sql , created db and user on mssql side then got nice installation page.

but after filling all info - got back the installation page w/o any error.

no tables were created..

any way to debug ? what can be done ?

dj.bauch wrote:

(In reply to comment #63)

OS: win2003, MSSQL 2000 (8.0)
first thanks for the patch,
I've troubles w/ installation
patched 1.14.0 w/ your patch, updated tables.sql , created db and user on mssql
side then got nice installation page.

but after filling all info - got back the installation page w/o any error.

no tables were created..

any way to debug ? what can be done ?

The ADODB library needs patches to three files. I am uploading the necessary patch for ADODB 5.05 to this bug report right now, since that is the version I am currently using with my wiki. The current release of adodb is 5.08, so I will download and build a patch for that one too. In the mean time, if you get adodb505 from sourceforge and apply the patch, hopefully you will get past this point and the tables will be created.

dj.bauch wrote:

Patch for the ADODB libarary, version 5.05

Minor patches to the ADODB library for PHP, version 5.05 so that it supports the MS SQL connection for MediaWiki.

Attached:

yedidia wrote:

After some fixes on php.ini (most important is to set a file for error log for not getting 500 errors), my installation is moving.
first I found that tables.sql has to be in maintenance/sqlserver (and not in mssql dir). then after creating some tables it stops.

refreshing the installation give this info:
There are already MediaWiki tables in this database. Checking if updates are needed...
...ipblocks table does not exist, skipping new field patch
...ipblocks table does not exist, skipping new field patch
Creating interwiki table:

then it stop...

could be that tables.sql that is provided here is broken ?

(In reply to comment #66)

After some fixes on php.ini (most important is to set a file for error log for
not getting 500 errors), my installation is moving.
first I found that tables.sql has to be in maintenance/sqlserver (and not in
mssql dir). then after creating some tables it stops.

refreshing the installation give this info:
There are already MediaWiki tables in this database. Checking if updates are

needed...

...ipblocks table does not exist, skipping new field patch
...ipblocks table does not exist, skipping new field patch
Creating interwiki table:

then it stop...

could be that tables.sql that is provided here is broken ?

There is no maintenance/sqlserver directory, and maintenance/mssql/tables.sql has an entry for ipblocks.

yedidia wrote:

(In reply to comment #67)

(In reply to comment #66)
> After some fixes on php.ini (most important is to set a file for error log for
> not getting 500 errors), my installation is moving.
> first I found that tables.sql has to be in maintenance/sqlserver (and not in
> mssql dir). then after creating some tables it stops.
>
>
> refreshing the installation give this info:
> There are already MediaWiki tables in this database. Checking if updates are
> needed...
> ...ipblocks table does not exist, skipping new field patch
> ...ipblocks table does not exist, skipping new field patch
> Creating interwiki table:
>
> then it stop...
>
> could be that tables.sql that is provided here is broken ?
>

There is no maintenance/sqlserver directory, and maintenance/mssql/tables.sql
has an entry for ipblocks.

but DatabaseADODB.php has this on line 1360:
dbsource( "../maintenance/sqlserver/tables.sql", $this);

And for some reason the table creation fail at the middle, these table were created:

  • user2
  • user_newtalk
  • user_groups
  • text
  • templatelinks
  • revision2
  • pagelinks
  • page2
  • imagelinks
  • categorylinks
  • archive

And these views were created:

  • page
  • revision
  • user

There are missing about 20-30 other tables...

dj.bauch wrote:

(In reply to comment #68)

(In reply to comment #67)
> (In reply to comment #66)
> > After some fixes on php.ini (most important is to set a file for error log for
> > not getting 500 errors), my installation is moving.
> > first I found that tables.sql has to be in maintenance/sqlserver (and not in
> > mssql dir). then after creating some tables it stops.
> >
> >
> > refreshing the installation give this info:
> > There are already MediaWiki tables in this database. Checking if updates are
> > needed...
> > ...ipblocks table does not exist, skipping new field patch
> > ...ipblocks table does not exist, skipping new field patch
> > Creating interwiki table:
> >
> > then it stop...
> >
> > could be that tables.sql that is provided here is broken ?
> >
>
> There is no maintenance/sqlserver directory, and maintenance/mssql/tables.sql
> has an entry for ipblocks.
>
but DatabaseADODB.php has this on line 1360:

dbsource( "../maintenance/sqlserver/tables.sql", $this);

And for some reason the table creation fail at the middle, these table were
created:

  • user2
  • user_newtalk
  • user_groups
  • text
  • templatelinks
  • revision2
  • pagelinks
  • page2
  • imagelinks
  • categorylinks
  • archive

    And these views were created:
  • page
  • revision
  • user

    There are missing about 20-30 other tables...

I am starting to fix this. The problem is that the way the tables are initially created has changed since I created my wiki. I'll have to update ./maintenance/updaters.php and add a SQL Server case to the function archive($name) so that it looks in "$IP/maintenance/archives/sqlserver". Right now it is trying to use the MySQL archives (since the only other option currently is postgress. I'll have to populate that directory under maintenance. If you just run the SQL in ./maintenance/sqlserver/tables.sql, you should get all the current tables.

dj.bauch wrote:

MS SQL Server Script

There were a couple of mistakes in the SQL Server script, but that wasn't the root of the problem, since the install was attempting to run a bunch of patch SQL scripts from the MySQL directory. Replace the tables.sql with this updated one. The root of the problem is addressed by a change to updaters.inc, which I'll upload next.

attachment tables.sql ignored as obsolete

dj.bauch wrote:

Replacement updaters.inc to dbsource SQL Server script

Replace ./maintenance/updaters.inc with this modestly changed one. If the database type is 'adodb' it will dbsource the MS SQL Server Script rather than try to run the patch scripts from ./maintenance/archives. No patching is necessary anyway, since there won't be an exisiting SQL Server wiki database. I just tested this and the MS SQL Server Script on a fresh box, and it's working for me.

Attached:

dj.bauch wrote:

(In reply to comment #66)

After some fixes on php.ini (most important is to set a file for error log for
not getting 500 errors), my installation is moving.
first I found that tables.sql has to be in maintenance/sqlserver (and not in
mssql dir). then after creating some tables it stops.

refreshing the installation give this info:
There are already MediaWiki tables in this database. Checking if updates are

needed...

...ipblocks table does not exist, skipping new field patch
...ipblocks table does not exist, skipping new field patch
Creating interwiki table:

then it stop...

could be that tables.sql that is provided here is broken ?

This problem is fixed in the updated tables.sql I just uploaded. You are right, there was an error in the SQL for creating the interwiki table. I stumbled on this because it was a type-conversion error rather than a syntax error. A couple of columns had to be varchar rather than varbinary.

yedidia wrote:

(In reply to comment #72)

(In reply to comment #66)
> After some fixes on php.ini (most important is to set a file for error log for
> not getting 500 errors), my installation is moving.
> first I found that tables.sql has to be in maintenance/sqlserver (and not in
> mssql dir). then after creating some tables it stops.
>
>
> refreshing the installation give this info:
> There are already MediaWiki tables in this database. Checking if updates are
> needed...
> ...ipblocks table does not exist, skipping new field patch
> ...ipblocks table does not exist, skipping new field patch
> Creating interwiki table:
>
> then it stop...
>
> could be that tables.sql that is provided here is broken ?
>
This problem is fixed in the updated tables.sql I just uploaded. You are right,
there was an error in the SQL for creating the interwiki table. I stumbled on
this because it was a type-conversion error rather than a syntax error. A
couple of columns had to be varchar rather than varbinary.

Wonderful! the installation was smooth and all the tables were created.
LocalSettings.php exist also.

now after moving LocalSettings.php to the right place I get a timeout on main page, changed max_execution_time on php.ini to 300 sec (!) and still it get a timeout.
this is what I get on the error_log:
[06-May-2009 12:46:00] PHP Notice: Undefined index: REQUEST_METHOD in C:\Inetpub\wiki\mediawiki-1.14.0.patched\includes\WebRequest.php on line 410
[06-May-2009 12:46:00] PHP Notice: Undefined index: SERVER_PROTOCOL in C:\Inetpub\wiki\mediawiki-1.14.0.patched\includes\OutputHandler.php on line 117
[06-May-2009 12:46:09] PHP Notice: Undefined index: REQUEST_METHOD in C:\Inetpub\wiki\mediawiki-1.14.0.patched\includes\WebRequest.php on line 410
[06-May-2009 12:51:09] PHP Fatal error: Maximum execution time of 300 seconds exceeded in C:\php\adodb5\adodb.inc.php on line 3593

Adding 'tracking' keyword.

*Bulk BZ Change: +Patch to open bugs with patches attached that are missing the keyword*

dj.bauch wrote:

MS SQL Server Script for MediaWiki 1.16.5

At the Microsoft SQL Server JumpIn Camp, I updated the script to support MediaWiki 1.16.5. I tested on SQL Server 2008, SQL Server Denali, and SQL Server Azure. It also still works on SQL Server 2005. The code now uses Microsoft's PDO driver for SQL Server rather than ADODB, but the SQL script is not affected by that.

Attached:

dj.bauch wrote:

includes\db\DatabaseMSPDO.php

Database subclass for use with Microsoft SQL Server PDO driver. This class works with both traditional SQL Server connections as well as with SQL Server Azure. It doesn't exactly obsolete DatabaseADODB.php, although I suppose that practically, it does.

Attached:

So that "MSSQL native" thing is obsolete and everybody should use PDO? Should we throw away our current MSSQL backend that bitrots for a year now? A few quick comments on attached code:

  1. We don't accept patches with new functionality against released versions. Only trunk. And for such complex things, we don't accept patches at all. Instead, we give commit access to people interested in continuously maintaining these.
  2. Database class is a legacy alias for DatabaseMysql, inherit from DatabaseBase.
  3. Code duplication in constructor and newFromParams().
  4. wfProfileIn("dbconnect-$server") is superfuluous, no heavy operations between it and parent wfProfileIn(METHOD).
  5. Due to (2), you have to resort to overloading query() and performing messy manual SQL rewrites, don't do that. Change SQL abstraction functions instead.
  6. Same thing for copy-paste inheritance of virtually every function.
  7. Manual magic quotes fighting in strencode() and qstr() is not needed, magic_quotes_gpc is handled in WebRequest while installer refuses to install with magic_quotes_runtime on.
  8. Lots of commented-out code.
  9. Code doesn't follow our coding conventions outlined at http://www.mediawiki.org/wiki/Manual:Coding_conventions

dj.bauch wrote:

(In reply to comment #78)

So that "MSSQL native" thing is obsolete and everybody should use PDO? Should
we throw away our current MSSQL backend that bitrots for a year now? A few
quick comments on attached code:

  1. We don't accept patches with new functionality against released versions. Only trunk. And for such complex things, we don't accept patches at all. Instead, we give commit access to people interested in continuously maintaining these.
  2. Database class is a legacy alias for DatabaseMysql, inherit from DatabaseBase.
  3. Code duplication in constructor and newFromParams().
  4. wfProfileIn("dbconnect-$server") is superfuluous, no heavy operations between it and parent wfProfileIn(METHOD).
  5. Due to (2), you have to resort to overloading query() and performing messy manual SQL rewrites, don't do that. Change SQL abstraction functions instead.
  6. Same thing for copy-paste inheritance of virtually every function.
  7. Manual magic quotes fighting in strencode() and qstr() is not needed, magic_quotes_gpc is handled in WebRequest while installer refuses to install with magic_quotes_runtime on.
  8. Lots of commented-out code.
  9. Code doesn't follow our coding conventions outlined at http://www.mediawiki.org/wiki/Manual:Coding_conventions

Thanks for those comments. I'll endeavor to fix those issues. As to MSSQL native, I never have used that. I haven't had any luck getting that or PDO for SQL Server working on Windows XP, so I used the ADODB library in the past. I started on the PDO driver by making a copy of my old ADODB driver code, so that explains some of the legacy stuff that now needs to be cleaned up. MSSQL native and PDO work fine for me on Windows 7 and on Windows Azure, and with SQL Azure. ADODB doesn't do as well with SQL Azure, so right now I either use ADODB on XP with SQL Server 2005 or 2008 or I use PDO on Windows 7 or Azure with SQL Server 2005, 2008, Denali, or Azure

-need-review, already reviewed.

sumanah wrote:

DJ, did you have time to work on these issues?

DJ Bauch, would you have time to work on aforementioned issues with your patches?

demon added a comment.Nov 15 2013, 7:04 PM

WONTFIX per reason I outlined 22093 comment 30

Reopening per bug 22093 comment 31

I did some work to support SQL Server access from Linux this summer (https://bugs.php.net/bug.php?id=64338), so you don't have to have a Windows box to implement support for SQL Server. Azure access should work just fine.

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 18 2015, 5:21 PM

Note that this tracking task will soon be converted into a Phabricator project as proposed in T164895: Please create a tag for SQLite related tasks

tstarling renamed this task from Microsoft SQL Server/MSSQL support (tracking) to [DO NOT USE] Microsoft SQL Server/MSSQL support (tracking).May 19 2017, 10:14 AM
tstarling closed this task as Invalid.
tstarling lowered the priority of this task from Low to Lowest.
tstarling updated the task description. (Show Details)
tstarling removed subtasks: T164018: Error during install of 1.28.1 with MSSQL server: Implicit conversion from data type varchar to varbinary(max) is not allowed., T162781: Warning: Declaration of DatabaseMssql::ignoreErrors should be compatible with Database::ignoreErrors, T161237: MSSQL Error when using limit statement in Cargo Query, T160917: Error on "Wanted Pages" with MSSQL, T158530: Mediawiki 1.27.1 could not do Full text searching and failed to give correct searching result, T158766: Error during document revision compare, T155260: Cargo not creating tables with MS SQLServer database, T153876: MSSQL: Database error after upgrading to 1.28.0, T145635: Error installing with SQL Server 2014 Express: Index 'oi_name_archive_name' has more than 900 bytes long, T118973: getTitleProtection MSSQL Error MSSQL Error, T118961: Missing MSSQL Field in installer, T75390: When using MS SQL Server, article links remain red after creation., T73207: MS SQL Driver doesn't handle escaping for LIKE patterns correctly, T73027: RegExp search and replace doesn't work with MS Sql SERVER, T73024: MS Sql Driver has compatibility bugs (see description), T67813: MSSQL installer broken; updateRowExists returns always false, T67808: MSSQL allows opening connections without $wgDBuser being set, T67757: Explicitly name check constraints for Microsoft SQL Server schema, T74171: Cant replace image when using SQL Server backend: varchar vs. varbinary, T24093: Native Microsoft SQL Server Support, T17493: syntax errors with MSSQL.