Page MenuHomePhabricator

syntax errors with MSSQL
Closed, InvalidPublic

Description

Author: graymatter79

Description:
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


Version: 1.13.x
Severity: normal
OS: Windows Server 2003

Details

Reference
bz15493

Event Timeline

bzimport raised the priority of this task from to Normal.Nov 21 2014, 10:21 PM
bzimport added a project: MediaWiki-Rdbms.
bzimport set Reference to bz15493.
bzimport added a subscriber: Unknown Object (MLST).
bzimport created this task.Sep 5 2008, 11:19 PM

graymatter79 wrote:

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

IAlex added a comment.Sep 8 2008, 6:02 PM

Note that the MSSQL support is not ready yet. You shouldn't use it.

mediawiki wrote:

Diff against SVN 45842

attachment mssql.diff ignored as obsolete

mediawiki wrote:

Prepatched DatabaseMssql.php

attachment DatabaseMssql.php ignored as obsolete

mediawiki wrote:

Prepatched tables.sql

attachment tables.sql ignored as obsolete

mediawiki wrote:

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.

mediawiki wrote:

Patched DatabaseMssql.php

Attached:

mediawiki wrote:

Patched tables.sql

Attached:

mediawiki wrote:

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.

Attached:

  • 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.

Reedy added a comment.Apr 21 2010, 4:54 PM

MS SQL Server support removed in r65404

Pending fix of something such as bug 22093