View Issue Details

WikiRelated ChangesetsJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0014385mantisbtdb postgresqlpublic2012-06-12 08:542014-09-23 18:05
Reporterdregad Assigned Todregad  
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version1.2.11 
Target Version1.2.12Fixed in Version1.2.12 
Summary0014385: Impossible to create a new project with fresh install on PostgreSQL
Description

This problem is new with 1.2.11, and is caused by column mantis_project_table.inherit_global, which is defined as unsigned int in schema.php, but treated as boolean in the code.

APPLICATION ERROR 401


Database query failed. Error received from database was #-1:

ERROR: invalid input syntax for integer: "true" for the query:

INSERT INTO mantis_project_table

( name, status, enabled, view_state, file_path, description, inherit_global )

VALUES

( ?, ?, ?, ?, ?, ?, ?).


<Array> { 
    [0] => 'Test Project',
    [1] => 10,
    [2] => 'true',
    [3] => 10,
    [4] => '',
    [5] => '',
    [6] => 'true'
}


Call stack:

./mantisbt/core/project_api.php     307   db_query_bound (...)

./mantisbt/manage_proj_create.php   49    project_create (...)

Steps To Reproduce
  • install 1.2.11 and PostgreSQL (tested with 9.1)
  • run MantisBt install script
  • login as administrator and create a new project
  • error occurs
Additional Information

Similar behavior occurs when adding a subproject (this time due to mantis_project_hierarchy_table.inherit_parent), for the same reason:



APPLICATION ERROR 401


Database query failed. Error received from database was #-1:

ERROR: invalid input syntax for integer: "true" for the query:

INSERT INTO mantis_project_hierarchy_table

( child_id, parent_id, inherit_parent )

VALUES

( ?, ?, ? ).


<Array> {
    [0] => 2,
    [1] => 1,
    [2] => 'true'
}


Call stack:

./mantisbt/core/project_hierarchy_api.php   54   db_query_bound (...)

./mantisbt/manage_proj_subproj_add.php      43   project_hierarchy_add (...)

TagsNo tags attached.

Relationships

related to 0014288 closeddregad Manage User not reachable due to error in PostgreSQL 
related to 0015721 closedgrangeway Functionality to consider porting to master-2.0.x 
has duplicate 0014427 closedatrol Cannot create a project on a fresh install 
related to 0014375 closeddregad Postgres SQL error: smallint vs. boolean 
related to 0014398 closeddregad Support for PostgreSQL broken in 1.3 

Activities

dregad

dregad

2012-06-12 09:11

developer   ~0032081

The issue has been introduced by commit b8d4b503.
dregad

dregad

2012-06-12 09:23

developer   ~0032083

Last edited: 2012-06-12 09:31

Possible workarounds:

  • revert commit b8d4b503 (will undo fix for 0014288)
  • manually alter db schema (WARNING - not tested in "real life") 

    
ALTER TABLE mantis_project_table ALTER COLUMN inherit_global DROP DEFAULT;
    
ALTER TABLE mantis_project_table ALTER COLUMN inherit_global TYPE bool
    
USING CASE WHEN inherit_global = 0 THEN FALSE ELSE TRUE END;
    
ALTER TABLE mantis_project_table ALTER COLUMN inherit_global SET DEFAULT false;

ALTER TABLE mantis_project_hierarchy_table ALTER COLUMN inherit_parent DROP DEFAULT;
ALTER TABLE mantis_project_hierarchy_table ALTER COLUMN inherit_parent TYPE bool
USING CASE WHEN inherit_parent = 0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_project_hierarchy_table ALTER COLUMN inherit_parent SET DEFAULT false;
dregad

dregad

2012-06-21 12:34

developer   ~0032136

A fix for 1.2.x branch is available on github [1], testing and feedback would be appreciated.

For 1.3, the proper solution would be to amend the schema, but the ADOdb AlterColumnSQL function does not work properly (see upstream bug report [2] and also 0014398

[1] https://github.com/dregad/mantisbt/tree/fix-14385
[2] http://phplens.com/lens/lensforum/msgs.php?id=19204
dregad

dregad

2012-06-27 07:07

developer   ~0032192

@Artefact2 - would appreciate if you could test the fix mentioned in my previous post. Let me know if you need assistance for that.
Artefact2

Artefact2

2012-06-27 07:20

reporter   ~0032193

I tried your branch, and project creation works now. However, when I try to update the project (here I tried unticking the "inherit global categories"), I still have a similar error:

"Database query failed. Error received from database was #-1: ERROR: invalid input syntax for integer: "'0'" for the query: UPDATE mantis_project_table
SET name=?,
status=?,
enabled=?,
view_state=?,
file_path=?,
description=?,
inherit_global=?
WHERE id=?."

Thanks for the quick response, though, it's appreciated!
dregad

dregad

2012-06-27 08:39

developer   ~0032195

Thank you for testing.

The second issue you reported has the same root cause. I pushed another commit to the same branch to address it; kindly test again and let me know how it goes.
Artefact2

Artefact2

2012-06-27 08:53

reporter   ~0032196

Seems to work now, thanks!
dregad

dregad

2012-06-27 09:05

developer   ~0032197

Excellent. Let me know if you face any other issues with postgres on 1.2.11
dregad

dregad

2012-06-27 09:27

developer   ~0032198

Please note as mentioned in the commit message, that the fix applied in 1.2.x branch is just a workaround.

The permanent fix requires a schema change, and its implementation in 1.3.x branch has to be deferred to a later time, as it depends on the implementation of several bug fixes in the ADOdb library (see 0013713:0032135)

Work-in-progress for the 1.3 fixes: https://github.com/dregad/mantisbt/tree/adodb-upg-master
grangeway

grangeway

2013-04-05 17:56

reporter   ~0036163

Marking as 'acknowledged' not resolved/closed to track that change gets ported to master-2.0.x branch
grangeway

grangeway

2014-05-16 15:02

reporter   ~0040517

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

Related Changesets

MantisBT: master-1.2.x b8d4b503

2012-05-24 05:21

dregad


Details Diff		 Fix PostgreSQL errors with boolean fields

PostgreSQL triggers an error when comparing a boolean field with an
integer value. This happens frequently in MantisBT as AdoDB stores
boolean fields as integers for most RDBMS.

This could prevent for example manage_user_page.php from loading.

To fix the problem, db_prepare_bool() has been modified to return 'true'
or 'false' as appropriate when the DB is PostgreSQL, through use of
AdoDB qstr() function. Behavior for other RDMBS is unchanged.

The where clause condition in manage_user_page.php has been modified to
use db_prepare_bool() instead of hardcoding 'enabled = 1'.

Following code cleanup was also performed:
- Uses of db_prepare_bool() in filter_api.php and mc_api.php that
would have caused incorrect behavior with the modified function have
been removed
- Removed non-existant 2nd parameter in call to db_prepare_bool() in
filter_api.php
- Fix whitespace in mc_api.php

Fixes 0014288		 Affected Issues
0014288, 0014385
mod - api/soap/mc_api.php Diff File
mod - core/database_api.php Diff File
mod - core/filter_api.php Diff File
mod - manage_user_page.php Diff File

MantisBT: master-1.2.x ba71cf96

2012-06-18 02:52

dregad


Details Diff		 Fix PostgreSQL error when adding project/subproject

Release 1.2.11 (see commit b8d4b5039598248d0b0c78619450c51d4dc98df2 and
issue 0014288) introduced a regression preventing the user from creating
a new project or adding a subproject.

The error is caused by columns mantis_project_table.inherit_global and
mantis_project_hierarchy_table.inherit_parent, which are defined as
unsigned int in schema.php, but treated as boolean in the code. This is
a problem with PostgreSQL due to strict type checking, but not on MySQL
as type cast is done automatically.

This commit is a workaround for the problem (sending an int to the DB
instead of a bool if using PostgreSQL), as fixing the root cause would
require a schema change which is not possible in 1.2.x.

Fixes 0014385		 Affected Issues
0014288, 0014385
mod - core/project_api.php Diff File
mod - core/project_hierarchy_api.php Diff File

MantisBT: master-1.2.x ef24c0f3

2012-06-27 01:30

dregad


Details Diff		 Fix PostgreSQL error when updating a project

Release 1.2.11 (see commit b8d4b5039598248d0b0c78619450c51d4dc98df2 and
issue 0014288) introduced a regression preventing the user from updating
an existing project.

This commit is a workaround for the problem (sending an int to the DB
instead of a bool if using PostgreSQL), as fixing the root cause would
require a schema change which is not possible in 1.2.x.

Fixes 0014385		 Affected Issues
0014288, 0014385
mod - core/project_api.php Diff File