View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0021182mantisbtdb schemapublic2016-06-30 19:572016-07-12 13:13
Reporterkismert Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status newResolutionopen 
Product Version1.2.19 
Summary0021182: SQL Server: Error deleting category
Description

Error deleting category in:
http://localhost/mantisbt/manage_proj_cat_delete.php?id=7&project_id=0

Error:
Database query failed. Error received from database was #402: SQLState: 42000
Error Code: 402
Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]
The data types text and varchar are incompatible in the equal to operator.
For the query:
SELECT count(config_id) FROM mantis_config_table WHERE config_id = ? AND value = ?.

Steps To Reproduce

1) Install Mantis with SQL Server
2) Create a Category
3) Try to delete the Category

Additional Information

This boils down to a problem comparing TEXT fields to quoted strings, in this case
the [value] TEXT field.

Fix:
The TEXT and NTEXT datatypes are deprecated. Convert all TEXT fields in Mantis to
NVARCHAR(MAX).

Underlying cause:
schema.php uses the XL meta-datatype to indicate 'big string'.
datadict-mssqlnative.inc.php maps 'XL' to 'TEXT', and 'X2' to 'NTEXT'
(also datadict-mssql.inc.php)

Underlying fix:
In the two datdict files, map:

'XL' to 'VARCHAR(MAX)'
'X2' to 'NVARCHAR(MAX)'

Question: why keep VARCHAR and CHAR around at all? If we are using UTF-8 exclusively, then
why not short-circuit all to NVARCHAR and NCHAR?

Reference:
SQL SERVER – Fix: Error : 402 The data types ntext and varchar are incompatible in the equal to operator
http://blog.sqlauthority.com/2012/12/04/sql-server-fix-error-402-the-data-types-ntext-and-varchar-are-incompatible-in-the-equal-to-operator/

TagsNo tags attached.

Activities

kismert

kismert

2016-07-12 11:02

reporter   ~0053577

@atrol - would you like for me to take a crack at fixing this code? I know a fair amount of PHP, and have pretty good SQL Server skills. I think I know what is wrong here, and how to fix it.

I also have some motivation for getting Mantis to work properly here at our Windows/Office/SQL Server shop.

The Mantis code base looks well-written and easy to understand. I looked briefly at your latest development branch, and it doesn't look too different from the latest release version.

The only questions are:

1) Performing the fixes in a way that is compatible with your future goals.
2) Whether you want me mucking around with your code.

Let me know what you think.

Thanks,
-Ken
atrol

atrol

2016-07-12 13:10

developer   ~0053580

Last edited: 2016-07-12 13:13

@kismert, that sounds very good, especially as there is no Mantis core developer at the moment that uses SQL-Server.

Submitting a patch is always a good idea, as it increases the chances of improvement eventually making it into MantisBT core. All contributions are welcome and greatly appreciated.

Patch submissions can be made in several ways. In the order of preference:

  1. Send us a Pull Request on our Github repository [1]
  2. Attach a GIT patch to the issue
  3. Attach a Unified Diff, clearly specifying the patch's base release

Kindly avoid to upload entire modified PHP files.

Please make sure that your submissions adhere to our Coding Guidelines [2], if they don't your patch might be rejected.

Version 1.2.x is no longer supported.
That's why you should develop based on version 1.3.0 (Git branch master-1.3.x).
The changes will also be applied to master branch, to have it in next version 2.0.x.

[1] https://github.com/mantisbt/mantisbt
[2] http://www.mantisbt.org/wiki/doku.php/mantisbt:coding_guidelines