View Issue Details

IDProjectCategoryView StatusLast Update
0016392mantisbtdb postgresqlpublic2014-12-08 00:34
ReporterdregadAssigned Todregad 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version 
Target Version1.3.0-beta.1Fixed in Version1.3.0-beta.1 
Summary0016392: Bool columns in pgsql system created before MantisBT 1.1.0 have smallint type in DB
Description

The ADOdb library bundled with MantisBT releases prior to 1.1.0 created type "L" columns in PostgreSQL as SMALLINT, whereas later versions (4.96+) created them as BOOLEAN.

This leads to inconsistencies in the data model, and due to PostgreSQL's strong type and lack of automatic type cast between int and bool can trigger errors in Mantis.

Steps To Reproduce
  1. Check out release 1.0.7
  2. Install mantis on postgres

Check that all "L" columns (e.g. mantis_filters_table.is_public) are actually SMALLINT type

  1. checkout release 1.2.15
  2. upgrade Mantis

"L" columns are still SMALLINT

  1. fresh install mantis with 1.2.15

"L" columns are BOOLEAN

TagsNo tags attached.

Relationships

child of 0014398 closeddregad Support for PostgreSQL broken in 1.3 

Activities

dregad

dregad

2013-09-19 18:58

developer   ~0038083

To fix this, we must check all DB colums which we expect to be of BOOLEAN type, and if the actual type is different then the table structure must be updated.

This should be done in the installer as part of the upgrade process.

Systems newly installed with 1.1.0 or above are not affected by this.

grangeway

grangeway

2013-09-20 18:11

reporter   ~0038089

This is where we originally patched adodb to handle this, but those changes were pulled.

We need to decide whether in 2.x we want to use boolean or small int for these fields.

The obvious fix for 1.2.15 is too revert adodb back to the release where we included functionality to handle this, until we release 2.x

dregad

dregad

2013-09-23 04:46

developer   ~0038093

The obvious fix for 1.2.15 is too revert adodb back to the release where we included functionality to handle this, until we release 2.x

1.2.15 has the fix in, it's only been pulled in master; the issue here is when upgrading from old releases of Mantis (pre 1.1.0) and the only fix is to apply some script on the DB to convert the columns, patching the code will do nothing (unless you mean reverting ADOdb type for L columns back to smallint, which IMO is not an option).

grangeway

grangeway

2013-09-30 16:22

reporter   ~0038154

smallint is the correct data type for adodb to be using:

L: Integer field suitable for storing booleans (0 or 1)

The use of "boolean" is really a mistake given that definition. Smallint is the easy solution - however, I think we might actually want 'bit' in postgresql as the correct field type in this scenario.

dregad

dregad

2013-09-30 17:06

developer   ~0038156

smallint is the correct data type for adodb to be using:
L: Integer field suitable for storing booleans (0 or 1)

Possibly, but the fact is that ADOdb has been using BOOLEAN since 4.96. I'm sure you'll agree that it's not worth the effort to go and argue the case with John Lim to change back to SMALLINT or anything else...

Related Changesets

MantisBT: master e2e0a9d4

2013-09-08 12:47:44

dregad

Details Diff
Install: special processing for pgsql boolean columns

The upgrade of ADOdb library to version 4.96 with Mantis release
1.1.0 introduced a change in the way type 'L' columns are created
(SMALLINT before, BOOLEAN after).

This requires special handling to fix the database schema, because the
normal schema upgrade steps do not cover it (since from the MantisBT
installer's perspective the column type has not changed).

Fixes 0016392
mod - admin/install.php Diff File
mod - core/install_helper_functions_api.php Diff File

MantisBT: master ddba1d9f

2013-09-08 13:12:27

dregad

Details Diff
Install: pgsql bool check now handles nullable columns

Issue 0016392
mod - admin/install.php Diff File
mod - core/install_helper_functions_api.php Diff File

Issue History

Date Modified Username Field Change
2013-09-19 18:56 dregad New Issue
2013-09-19 18:56 dregad Status new => assigned
2013-09-19 18:56 dregad Assigned To => dregad
2013-09-19 18:56 dregad Relationship added child of 0014398
2013-09-19 18:58 dregad Note Added: 0038083
2013-09-19 19:09 dregad Steps to Reproduce Updated View Revisions
2013-09-20 18:11 grangeway Note Added: 0038089
2013-09-23 04:46 dregad Note Added: 0038093
2013-09-30 16:22 grangeway Note Added: 0038154
2013-09-30 17:06 dregad Note Added: 0038156
2014-03-24 10:25 dregad Changeset attached => MantisBT master e2e0a9d4
2014-03-24 10:25 dregad Changeset attached => MantisBT master ddba1d9f
2014-03-24 10:25 dregad Status assigned => resolved
2014-03-24 10:25 dregad Resolution open => fixed
2014-03-24 10:25 dregad Fixed in Version => 1.3.0-beta.1
2014-12-08 00:34 vboctor Status resolved => closed