Last modified: 2010-05-15 16:03:50 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 15493 - syntax errors with MSSQL
syntax errors with MSSQL
Status: RESOLVED INVALID
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
1.13.x
All Windows Server 2003
: Normal normal (vote)
: ---
Assigned To: Nobody - You can work on this!
:
Depends on:
Blocks: mssql
  Show dependency treegraph
 
Reported: 2008-09-05 23:19 UTC by GrayMatter
Modified: 2010-05-15 16:03 UTC (History)
6 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments
Diff against SVN 45842 (63.50 KB, patch)
2009-01-17 17:33 UTC, Nathanael
Details
Prepatched DatabaseMssql.php (21.48 KB, patch)
2009-01-17 17:34 UTC, Nathanael
Details
Prepatched tables.sql (18.77 KB, application/octet-stream)
2009-01-17 17:34 UTC, Nathanael
Details
Patched DatabaseMssql.php (21.38 KB, text/plain)
2009-01-21 06:52 UTC, Nathanael
Details
Patched tables.sql (19.02 KB, application/octet-stream)
2009-01-21 06:53 UTC, Nathanael
Details
Diff against trunk r45949 (64.73 KB, patch)
2009-01-21 06:55 UTC, Nathanael
Details

Description GrayMatter 2008-09-05 23:19:41 UTC
The specific errors below are from trying to edit a page, but the errors are also preventing other functions such as creating user accounts.


A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was: 
(SQL query hidden)
from within function "MediaWikiBagOStuff::_doquery". MySQL returned error "241: Syntax error converting datetime from character string.".
Retrieved from "http://aries/mediawiki13/index.php?title=Main_Page"

PHP Warning: mssql_query() [function.mssql-query]: message: Syntax error converting datetime from character string. (severity 16) in C:\inetpub\wwwroot\mediawiki13\includes\db\DatabaseMssql.php on line 147 PHP Warning: mssql_query() [function.mssql-query]: message: Syntax error converting datetime from character string. (severity 16) in C:\inetpub\wwwroot\mediawiki13\includes\db\DatabaseMssql.php on line 147 PHP Warning: mssql_query() [function.mssql-query]: Query failed in C:\inetpub\wwwroot\mediawiki13\includes\db\DatabaseMssql.php on line 147 PHP Warning: mssql_query() [function.mssql-query]: message: Syntax error converting datetime from character string. (severity 16) in C:\inetpub\wwwroot\mediawiki13\includes\db\DatabaseMssql.php on line 147 PHP Warning: mssql_query() [function.mssql-query]: message: Syntax error converting datetime from character string. (severity 16) in C:\inetpub\wwwroot\mediawiki13\includes\db\DatabaseMssql.php on line 147 PHP Warning: mssql_query() [function.mssql-query]: message: Syntax error converting datetime from character string. (severity 16) in C:\inetpub\wwwroot\mediawiki13\includes\db\DatabaseMssql.php on line 147
Comment 1 GrayMatter 2008-09-08 17:41:01 UTC
v1.13.1 is having the same issues...

PHP Warning: mssql_query() [function.mssql-query]: message: Syntax error converting datetime from character string. (severity 16) in C:\inetpub\wwwroot\mediawiki-1.13.1\includes\db\DatabaseMssql.php on line 147 

PHP Warning: mssql_query() [function.mssql-query]: message: Syntax error converting datetime from character string. (severity 16) in C:\inetpub\wwwroot\mediawiki-1.13.1\includes\db\DatabaseMssql.php on line 147 

PHP Warning: mssql_query() [function.mssql-query]: message: Syntax error converting datetime from character string. (severity 16) in C:\inetpub\wwwroot\mediawiki-1.13.1\includes\db\DatabaseMssql.php on line 147 

PHP Notice: Undefined variable: data in C:\inetpub\wwwroot\mediawiki-1.13.1\includes\Article.php on line 432 PHP Notice: Trying to get property of non-object in C:\inetpub\wwwroot\mediawiki-1.13.1\includes\Article.php on line 432 

PHP Warning: mssql_query() [function.mssql-query]: message: Syntax error converting datetime from character string. (severity 16) in C:\inetpub\wwwroot\mediawiki-1.13.1\includes\db\DatabaseMssql.php on line 147 
Comment 2 Alexandre Emsenhuber [IAlex] 2008-09-08 18:02:11 UTC
Note that the MSSQL support is not ready yet. You shouldn't use it.
Comment 3 Nathanael 2009-01-17 17:33:00 UTC
Created attachment 5691 [details]
Diff against SVN 45842
Comment 4 Nathanael 2009-01-17 17:34:04 UTC
Created attachment 5692 [details]
Prepatched DatabaseMssql.php
Comment 5 Nathanael 2009-01-17 17:34:41 UTC
Created attachment 5693 [details]
Prepatched tables.sql
Comment 6 Nathanael 2009-01-17 17:35:26 UTC
The existing MSSQL support is not yet ready for prime time. I have attached a diff against SVN revision 45842 to improve this. I have also attached already patched versions of includes/db/DatabaseMssql.php and maintenance/mssql/tables.sql for easy reference. Testing on Windows Server 2003 with IIS6 and SQL Server 2005 the only fault I am explicitly aware of is the inability to create infinite duration blocks. Further testing will certainly reveal others.

I am keen to get this into the tree as I believe it is at a reasonable point to allow others to begin to test, and against which future work can proceed incrementally.

Summary of changes:

* includes/db/DatabaseMssql.php : Correct various existing faults and reuse existing functionality within Database.php wherever possible.

* maintenance/mssql/tables.sql : Data types adjusted and referential integrity elements added (modelled off the Postgres version).

* includes/db/Database.php
  includes/Revision.php : Introduce EXPLICIT_ID option to support undelete operations where an identity field is set explicitly. MSSQL requires specific action in this case.

* includes/specials/SpecialRecentchanges.php
  includes/specials/SpecialRecentchangeslinked.php : MSSQL objects to ORDER BY on the sub-queries (it remains present on the union results). The hardcoded LIMIT option is removed from the union results but remains on the sub-queries. This allows the LIMIT to be performed in an MSSQL compatible manner.

* includes/specials/SpecialListfiles.php : MSSQL requires GROUP BY lists to include all non-aggregate fields. I believe Postgres will also require this.

* includes/specials/SpecialAncientpages.php : Add support for MSSQL datetime to Unix time conversion.

* includes/specials/SpecialNewimages.php : Rewrite the query logic to use the abstracted interfaces to allow the database abstraction mechanisms to work.
Comment 7 Nathanael 2009-01-21 06:52:39 UTC
Created attachment 5711 [details]
Patched DatabaseMssql.php
Comment 8 Nathanael 2009-01-21 06:53:24 UTC
Created attachment 5712 [details]
Patched tables.sql
Comment 9 Nathanael 2009-01-21 06:55:06 UTC
Created attachment 5713 [details]
Diff against trunk r45949

Issues resolved:

* Re uploading an image now works, would previously fail due to incomplete support for the MySQL IGNORE option.

* Added abstracted support for union queries to restore the original functionality of SpecialRecentchanges and SpecialRecentchangeslinked.


Updated summary of changes:

* includes/db/DatabaseMssql.php : Correct various existing faults and reuse
existing functionality within Database.php wherever possible.

* maintenance/mssql/tables.sql : Data types adjusted and referential integrity
elements added (modelled off the Postgres version).

* includes/db/Database.php : Introduce EXPLICIT_ID option to support undelete
operations where an identity field is set explicitly. MSSQL requires specific
action in this case. Add unionSQLText function to allow the database abstraction
mechanisms to apply to unions.

* includes/Revision.php : Specify the EXPLICIT_ID option

* includes/specials/SpecialRecentchanges.php
  includes/specials/SpecialRecentchangeslinked.php : Use the unionSQLText
function instead of a hardcoded union query including options not understood
by MSSQL.

* includes/specials/SpecialListfiles.php : MSSQL requires GROUP BY lists to
include all non-aggregate fields. I believe Postgres will also require this.

* includes/specials/SpecialAncientpages.php : Add support for MSSQL datetime to
Unix time conversion.

* includes/specials/SpecialNewimages.php : Rewrite the query logic to use the
abstracted interfaces.
Comment 10 Tim Starling 2009-02-25 01:19:45 UTC
* Adding extra fields to the GROUP BY in MySQL has the potential to instantly break Wikimedia, because it removes an index hint and forces it to rely on the buggy optimizer. We've seen this happen before with PostgreSQL support.
* The referential integrity in the PostgreSQL schema is buggy (e.g. bug #16487), and I think it's generally more trouble than it's worth. I'd like to see it removed completely, not used as a model.
* Is there meant to be a DatabaseMssql::unionSQLText() function? Because I'm not seeing it.

I think I'd prefer to see the MSSQL support split out to an extension for 1.15, since as it's currently structured, it will need constant maintenance to keep it working, which nobody appears to be offering to do. The changes to Database.php, Revision.php and the special pages might perhaps be acceptable, except for the special case in SpecialAncientpages which needs to be factored out.
Comment 11 Sam Reed (reedy) 2010-04-21 16:54:06 UTC
MS SQL Server support removed in r65404

Pending fix of something such as bug 22093

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links