View Issue Details

IDProjectCategoryView StatusLast Update
0016834mantisbtdb oraclepublic2016-06-12 00:42
ReporterzrybolaAssigned Todregad 
PriorityurgentSeveritycrashReproducibilityalways
Status closedResolutionfixed 
Product Version1.2.15 
Target Version1.3.0-rc.2Fixed in Version1.3.0-rc.2 
Summary0016834: Can't remove category because of CLOB in the query
Description

I have an issue deleting a category from a project running my Mantis on Oracle.

When deleting category, it checks the default category and prevents removing it. However, the query fails with the following exception:

Database query failed. Error received from database was #932: ORA-00932: inconsistent data types: expected -, found CLOB for the query: SELECT count(config_id) FROM m_config_tbl WHERE config_id=:0 AND value=:1.

Currently I am unable to delete the categories at all.

Additional Information

Seems a problem is in manage_proj_cat_delete.php file around the lines 43-51.

I have tried to convert the $f_category_id to string to be able to compare with CLOB but the same expcetion was thrown.

TagsNo tags attached.

Relationships

related to 0020761 closeddregad Add new API functions to check+ensure that a category can be deleted 

Activities

zrybola

zrybola

2014-01-13 11:33

reporter   ~0039039

Seems I found a solution, however it is definitelly db vendor specific. I needed to add "to_char" function for the "value" column before comparing with the second parameter. Now it worked and I was able to remove categories.

However, I do not know the limitations of "to_char" function if I do not lose any longer values...

Please, if you can check this situation and confirm my suggested solution or bring some better. Thanks.

grangeway

grangeway

2014-01-13 12:07

reporter   ~0039040

MSSQL/Oracle support is known broken in the 1.2 releases.

We plan on replacing the database API after the 1.3 release to try to provide proven support for other database platforms moving forward.

For now, I'd suggest for best results using mantis with MySQL

dregad

dregad

2014-01-13 19:49

developer   ~0039044

Please confirm if this issue is reproducible using a nightly build of 1.3 ("master") branch [1]

[1] http://mantisbt.org/builds.php

grangeway

grangeway

2014-05-16 15:01

reporter   ~0040462

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul

zrybola

zrybola

2014-05-19 06:33

reporter   ~0040571

Dear Paul, I would gladly help with testing or even developing the fixes for other databases but I can hardly found time for it. Therefore, I had just solved the problems locally and will wait for some new versions.

atrol

atrol

2014-06-16 15:42

developer   ~0040818

Reminder sent to: dregad, grangeway

Not sure, maybe this issue should be moved back to project mantis.
If so, grangeway could make a copy of it in project "Track DBAL replacement"

macdoncj

macdoncj

2016-02-26 13:38

reporter   ~0052581

Recently came across this error with MSSQL2012. Updated the following line in manage_proj_cat_delete.php:
$t_query = 'SELECT count(config_id) from {config} WHERE config_id = ' . db_param() . ' AND value = ' . db_param();

to

$t_query = 'SELECT count(config_id) from {config} WHERE config_id = ' . db_param() . ' AND value LIKE CONVERT(VARCHAR, ' . db_param() . ')';

I am now able to delete categories without an error.

dregad

dregad

2016-02-27 09:30

developer   ~0052587

Thanks for your update, which tells us the problem is not Oracle-specific (since you're using MSSQL) and also that it affects 1.3.x as well as 1.2.x.

I had a quick look at the code, and I believe that converting the parameter's type at SQL level is not the right approach.

$f_category_id is of type int, but the config table's config_id column is defined as VARCHAR(64). It would therefore make more sense to cast it to string

See proposed fix in PR https://github.com/mantisbt/mantisbt/pull/726 - kindly test and let me know if that resolves the problem.

macdoncj

macdoncj

2016-02-29 10:25

reporter   ~0052604

The proposed fix did not resolve the problem. I still receive the error when casting $f_category_id as a string.

cwolcott

cwolcott

2016-02-29 11:05

reporter   ~0052606

I agree that changing the parameter type at the SQL level is not the right approach, but the problem in MSSQL is that the value column is TEXT and the original code was passing the second parameter as an INT.

$t_default_cat_count = db_result( db_query( $t_query, array( 'default_category_for_moves', $f_category_id )));

So we received a MSSQL error of "Operand type clash: text is incompatible with int.

So your next suggestion was the following code which pass the second parameter as a STRING.

$t_default_cat_count = db_result( db_query( $t_query, array( 'default_category_for_moves', (string)$f_category_id )));

Now we receive a MSSQL error of "The data types text and varchar are incompatible in the equal to operator.

dregad

dregad

2016-02-29 11:41

developer   ~0052607

Thanks for the feedback. I'm sorry, for some reason I thought the column was defined as VARCHAR - I should have checked the schema definition.

The problem is, there is no clean, portable way to do this at SQL level because the type cast is DB-specific (e.g. TO_CHAR in Oracle, CONVERT in MSSQL, CAST in PostgreSQL, etc).

Since we're only interested in the record count, another approach could be to execute a query to retrieve all config table records for 'default_category_for_moves', and then check how many we got - if 0 then we can delete.

I'll work on that alternative implementation later.

dregad

dregad

2016-02-29 19:13

developer   ~0052610

Updated PR https://github.com/mantisbt/mantisbt/pull/726 - please test.

macdoncj

macdoncj

2016-03-01 09:54

reporter   ~0052625

Confirmed working for both 1.3.0-rc1 and 1.3.0-rc2-dev master-2a92f07 with MSSQL 2012. Thanks for the support!

Related Changesets

MantisBT: master f44b1e91

2016-02-27 09:24:03

dregad

Details Diff
Fix deletion of default category on MSSQL/Oracle

$f_category_id is of type int, but the config table's config_id column
is defined as VARCHAR(64). This causes a type mismatch on some RDBMS:

- Oracle (ORA-00932: inconsistent data types: expected -, found CLOB)
- MSSQL

Problem resolved by casting the variable to string when passing it to
db_query().

Fixes #16384
mod - manage_proj_cat_delete.php Diff File

MantisBT: master ee223f37

2016-02-27 09:24:03

dregad

Details Diff
Fix deletion of default category on MSSQL/Oracle (take 2)

Use new config_is_defined() API to check whether the category to delete
is defined as default_category_for_moves, instead of executing a SQL
statement against the config table.

This prevents a type mismatch error on some RDBMS:

- Oracle (ORA-00932: inconsistent data types: expected -, found CLOB)
- MSSQL (Operand type clash: text is incompatible with int.)
- PostgreSQL (unconfirmed)

Fixes #16384
mod - manage_proj_cat_delete.php Diff File

Issue History

Date Modified Username Field Change
2014-01-13 11:24 zrybola New Issue
2014-01-13 11:33 zrybola Note Added: 0039039
2014-01-13 12:07 grangeway Note Added: 0039040
2014-01-13 19:49 dregad Note Added: 0039044
2014-01-13 19:49 dregad Status new => feedback
2014-05-16 15:01 grangeway Note Added: 0040462
2014-05-16 16:36 grangeway Assigned To => grangeway
2014-05-16 16:36 grangeway Status feedback => assigned
2014-05-19 06:33 zrybola Note Added: 0040571
2014-05-23 15:08 grangeway Project mantisbt => @24@
2014-06-16 15:42 atrol Note Added: 0040818
2014-11-07 14:36 atrol Assigned To grangeway =>
2014-11-07 14:36 atrol Status assigned => new
2014-11-07 14:36 atrol Project @24@ => mantisbt
2016-02-26 13:38 macdoncj Note Added: 0052581
2016-02-27 09:30 dregad Assigned To => dregad
2016-02-27 09:30 dregad Status new => assigned
2016-02-27 09:30 dregad Note Added: 0052587
2016-02-27 09:31 dregad Status assigned => feedback
2016-02-29 10:25 macdoncj Note Added: 0052604
2016-02-29 11:05 cwolcott Note Added: 0052606
2016-02-29 11:41 dregad Status feedback => assigned
2016-02-29 11:41 dregad Note Added: 0052607
2016-02-29 19:13 dregad Note Added: 0052610
2016-02-29 19:13 dregad Status assigned => feedback
2016-03-01 09:54 macdoncj Note Added: 0052625
2016-03-27 11:13 dregad Relationship added related to 0020761
2016-03-27 11:16 dregad Changeset attached => MantisBT master f44b1e91
2016-03-27 11:16 dregad Changeset attached => MantisBT master ee223f37
2016-03-27 11:17 dregad Status feedback => resolved
2016-03-27 11:17 dregad Resolution open => fixed
2016-03-27 11:17 dregad Fixed in Version => 1.3.0-rc.2
2016-03-27 11:17 dregad Target Version => 1.2.20
2016-03-27 14:25 atrol Target Version 1.2.20 => 1.3.0-rc.2
2016-03-27 19:06 axosoft3 Issue cloned: 0020763
2016-06-12 00:42 vboctor Status resolved => closed