| Anonymous | Login | Signup for a new account | 2013-05-26 05:41 EDT | ![]() |
| Main | My View | View Issues | Change Log | Roadmap | Wiki | ManTweet | Repositories |
| View Issue Details [ Jump to Notes ] [ Wiki ] [ Related Changesets ] | [ Issue History ] [ Print ] | ||||||||
| ID | Project | Category | View Status | Date Submitted | Last Update | ||||
| 0014385 | mantisbt | db postgresql | public | 2012-06-12 08:54 | 2013-04-06 09:23 | ||||
| Reporter | dregad | ||||||||
| Assigned To | dregad | ||||||||
| Priority | normal | Severity | major | Reproducibility | always | ||||
| Status | closed | Resolution | fixed | ||||||
| Platform | OS | OS Version | |||||||
| Product Version | 1.2.11 | ||||||||
| Target Version | 1.2.12 | Fixed in Version | 1.2.12 | ||||||
| Summary | 0014385: 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 (...)
| ||||||||
| Tags | 2.0.x check | ||||||||
| Attached Files | |||||||||
Relationships |
||||||||||||||||||||||||||
|
||||||||||||||||||||||||||
Notes |
|
|
dregad (developer) 2012-06-12 09:11 |
The issue has been introduced by commit b8d4b503. |
|
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;
|
|
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 [^] |
|
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. |
|
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! |
|
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. |
|
Artefact2 (reporter) 2012-06-27 08:53 |
Seems to work now, thanks! |
|
dregad (developer) 2012-06-27 09:05 |
Excellent. Let me know if you face any other issues with postgres on 1.2.11 |
|
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 [^] |
|
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 |