MantisBT

View Issue Details Jump to Notes ] Wiki ] Related Changesets ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0014385mantisbtdb postgresqlpublic2012-06-12 08:542013-04-06 09:23
Reporterdregad 
Assigned Todregad 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOSOS Version
Product Version1.2.11 
Target Version1.2.12Fixed in Version1.2.12 
Summary0014385: Impossible to create a new project with fresh install on PostgreSQL
DescriptionThis 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 InformationSimilar 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 (...)
Tags2.0.x check
Attached Files

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

-  Notes
User avatar (0032081)
dregad (developer)
2012-06-12 09:11

The issue has been introduced by commit b8d4b503.
User avatar (0032083)
dregad (developer)
2012-06-12 09:23
edited on: 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;


User avatar (0032136)
dregad (developer)
2012-06-21 12:34

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 [^]
User avatar (0032192)
dregad (developer)
2012-06-27 07:07

@Artefact2 - would appreciate if you could test the fix mentioned in my previous post. Let me know if you need assistance for that.
User avatar (0032193)
Artefact2 (reporter)
2012-06-27 07:20

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!
User avatar (0032195)
dregad (developer)
2012-06-27 08:39

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.
User avatar (0032196)
Artefact2 (reporter)
2012-06-27 08:53

Seems to work now, thanks!
User avatar (0032197)
dregad (developer)
2012-06-27 09:05

Excellent. Let me know if you face any other issues with postgres on 1.2.11
User avatar (0032198)
dregad (developer)
2012-06-27 09:27

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 [^]
User avatar (0036163)
grangeway (developer)
2013-04-05 17:56

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

- Related Changesets
MantisBT: master-1.2.x b8d4b503
Timestamp: 2012-05-24 09:21:29
Author: 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
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
Timestamp: 2012-06-18 06:52:48
Author: 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
mod - core/project_api.php Diff ] File ]
mod - core/project_hierarchy_api.php Diff ] File ]
MantisBT: master-1.2.x ef24c0f3
Timestamp: 2012-06-27 05:30:34
Author: 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
mod - core/project_api.php Diff ] File ]

- Issue History
Date Modified Username Field Change
2012-06-12 08:54 dregad New Issue
2012-06-12 09:01 dregad Description Updated View Revisions
2012-06-12 09:01 dregad Additional Information Updated View Revisions
2012-06-12 09:02 dregad Description Updated View Revisions
2012-06-12 09:02 dregad Additional Information Updated View Revisions
2012-06-12 09:11 dregad Note Added: 0032081
2012-06-12 09:12 dregad Relationship added related to 0014288
2012-06-12 09:18 dregad Relationship added related to 0014375
2012-06-12 09:18 dregad Changeset attached => MantisBT master-1.2.x b8d4b503
2012-06-12 09:23 dregad Note Added: 0032083
2012-06-12 09:31 dregad Note Edited: 0032083 View Revisions
2012-06-20 13:54 dregad Relationship added related to 0014398
2012-06-21 12:34 dregad Note Added: 0032136
2012-06-21 12:34 dregad Status new => confirmed
2012-06-21 12:34 dregad Target Version => 1.2.12
2012-06-22 10:04 dregad Additional Information Updated View Revisions
2012-06-27 06:22 atrol Relationship added has duplicate 0014427
2012-06-27 07:07 dregad Note Added: 0032192
2012-06-27 07:07 dregad Assigned To => dregad
2012-06-27 07:07 dregad Status confirmed => assigned
2012-06-27 07:20 Artefact2 Note Added: 0032193
2012-06-27 08:39 dregad Note Added: 0032195
2012-06-27 08:53 Artefact2 Note Added: 0032196
2012-06-27 09:05 dregad Note Added: 0032197
2012-06-27 09:22 dregad Changeset attached => MantisBT master-1.2.x ba71cf96
2012-06-27 09:22 dregad Changeset attached => MantisBT master-1.2.x ef24c0f3
2012-06-27 09:22 dregad Status assigned => resolved
2012-06-27 09:22 dregad Resolution open => fixed
2012-06-27 09:22 dregad Fixed in Version => 1.2.12
2012-06-27 09:27 dregad Note Added: 0032198
2012-11-10 18:54 dregad Status resolved => closed
2013-04-05 17:56 grangeway Status closed => acknowledged
2013-04-05 17:56 grangeway Note Added: 0036163
2013-04-05 19:27 grangeway Relationship added related to 0015721
2013-04-06 03:40 dregad Status acknowledged => closed
2013-04-06 07:23 grangeway Status closed => acknowledged
2013-04-06 09:22 dregad Tag Attached: 2.0.x check
2013-04-06 09:23 dregad Status acknowledged => closed


MantisBT 1.2.16dev master-1.2.x-8c2bd07 [^]
Copyright © 2000 - 2013 MantisBT Team
Time: 0.1191 seconds.
memory usage: 2,917 KB
Powered by Mantis Bugtracker