0014288: Manage User not reachable due to error in PostgreSQL - MantisBT - Signup temporarily disabled due to spam

ID	Project	Category	View Status	Date Submitted	Last Update

0014288	mantisbt	db postgresql	public	2012-05-23 10:20	2014-09-23 18:05

Reporter	awenkhh	Assigned To	dregad
Priority	high	Severity	crash	Reproducibility	always
Status	closed	Resolution	fixed
Platform	Linux	OS	Linux	OS Version	2.6.32-5-686
Product Version	1.2.10
Target Version	1.2.11	Fixed in Version	1.2.11

Summary	0014288: Manage User not reachable due to error in PostgreSQL
Description	It is not possible to reach the page manage_user_page.php when using PostgreSQL (8.3/8.4). The problem can be found in the table mantis_user_table columns enabled and protected. The type of the columns is boolean and the default value is 'true' or 'false'. But the software is trying to compare the value true (resp.false) with 1 or 0. If one wants to fix that fast do this: ALTER TABLE mantis_user_table ALTER COLUMN enabled SET DEFAULT NULL; ALTER TABLE mantis_user_table ALTER COLUMN enabled TYPE integer USING 1; ALTER TABLE mantis_user_table ALTER COLUMN enabled SET DEFAULT 0; same for column protected
Tags	No tags attached.

dregad 2012-05-24 09:47 developer ~0031889	I can reproduce this on my dev box (Ubuntu 12.04 with PostgreSQL 9.3.1) APPLICATION ERROR 0000401 Database query failed. Error received from database was #-1: ERROR: operator does not exist: boolean = integer LINE 4: AND enabled = 1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. for the query: SELECT count() as usercnt FROM mantis_user_table WHERE (1 = 1) AND enabled = 1. Call stack ./core/database_api.php 405 - - trigger_error ( <string>'401', <integer>256 ) ./manage_user_page.php 186 - - db_query_bound ( <string>'SELECT count() as usercnt FROM mantis_user_table WHERE (1 = 1) AND enabled = 1', <NULL>NULL )

dregad 2012-05-24 13:00 developer ~0031893	Please test the following code, which should fix the problem. https://github.com/dregad/mantisbt/tree/fix-14288

dregad 2012-05-24 13:10 developer ~0031894 Last edited: 2012-05-24 13:11	Reminder sent to: rombert @rombert, as I made a change to soap api that I am not able to test, I would appreciate if you could have a look at this and make sure it is not introducing any regressions in the display of filters. Note that the change was made to remain consistent with filter api, as the code in mc_api.php/mci_filter_db_get_available_queries() is basically a duplicate of filter_db_get_available_queries() (which I tested successfully). Test cases: to be done for PostgreSQL and another RDBMS contents of filters list for current user should be identical between 1.2.x and the fix-14288 branch In other words, for an absolute definition, the filters displayed should be: all available public ones, as well as the user's private filters (for all projects and the current project). Thanks in advance for your help.

rombert 2012-05-24 15:01 reporter ~0031895	@dregad - the changes look fine to me ; I've tested using the SOAP API and the behaviour is exactly the same as before the change.

dregad 2012-05-25 07:32 developer ~0031907	Thanks for your help rombert. awenkhh, I'm waiting for your confirmation that this resolves your issue.

dregad 2012-05-29 18:57 developer ~0031922	Without feedback from you, I assume that you are OK with the change, which will be included in the next version of MantisBT.

grangeway 2013-04-05 17:57 reporter ~0036247	Marking as 'acknowledged' not resolved/closed to track that change gets ported to master-2.0.x branch

grangeway 2014-05-16 15:02 reporter ~0040518	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

MantisBT: master 32559580 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
	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 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