View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0015440mantisbtdb postgresqlpublic2013-01-29 14:322014-05-16 15:02
Reporterjdraughn Assigned Todregad  
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionnot fixable 
Product Version1.2.14 
Summary0015440: schema not updated - error assuming smallint is boolean
Description

A SQL error was introduced between 1.2.10 and 1.2.11 where a database column is of type "smallint" and the SQL is attempting to compare it to "true".

The page which constantly fails is manage_user_page.php

The field is mantis_user_table.enabled which is a smallint.

The problem arises with the additional code added at #179 within manage_user_page.php



if( 1 == $c_show_disabled ) {

$t_show_disabled_cond = '';

} else {

$t_show_disabled_cond = ' AND enabled = ' . db_prepare_bool(true);

}

*** NOTE: An fresh install of 1.2.11 does not reproduce this error.

I believe the 'upgrade' process is not updating the database correctly as most "smallint" were not updated to "boolean".

Performing a diff between the upgraded database and the fresh install shows the following tables were not updated:



# mantis_bug_table

217c213

<     sticky smallint DEFAULT 0 NOT NULL,




sticky boolean DEFAULT false NOT NULL,




# mantis_custom_field_table

469,476c461,468

<     require_report smallint DEFAULT 0 NOT NULL,

<     require_update smallint DEFAULT 0 NOT NULL,

<     display_report smallint DEFAULT 1 NOT NULL,

<     display_update smallint DEFAULT 1 NOT NULL,

<     require_resolved smallint DEFAULT 0 NOT NULL,

<     display_resolved smallint DEFAULT 0 NOT NULL,

<     display_closed smallint DEFAULT 0 NOT NULL,

<     require_closed smallint DEFAULT 0 NOT NULL,




require_report boolean DEFAULT false NOT NULL,
require_update boolean DEFAULT false NOT NULL,
display_report boolean DEFAULT false NOT NULL,
display_update boolean DEFAULT true NOT NULL,
require_resolved boolean DEFAULT false NOT NULL,
display_resolved boolean DEFAULT false NOT NULL,
display_closed boolean DEFAULT false NOT NULL,
require_closed boolean DEFAULT false NOT NULL,




# mantis_filters_table

549c541

<     is_public smallint,




is_public boolean,




# mantis_news_table

587c579

<     announcement smallint DEFAULT 0 NOT NULL,




announcement boolean DEFAULT false NOT NULL,




# mantis_project_table

700c688

<     enabled smallint DEFAULT 1 NOT NULL,




enabled boolean DEFAULT true NOT NULL,




# mantis_project_version_table

755c743

<     released smallint DEFAULT 1 NOT NULL,




released boolean DEFAULT true NOT NULL,




# mantis_sponsorship_table

795c783

<     paid smallint DEFAULT 0 NOT NULL,




paid boolean DEFAULT false NOT NULL,




# mantis_user_pref_table

911,919c899,907

<     email_on_new smallint DEFAULT 0 NOT NULL,

<     email_on_assigned smallint DEFAULT 0 NOT NULL,

<     email_on_feedback smallint DEFAULT 0 NOT NULL,

<     email_on_resolved smallint DEFAULT 0 NOT NULL,

<     email_on_closed smallint DEFAULT 0 NOT NULL,

<     email_on_reopened smallint DEFAULT 0 NOT NULL,

<     email_on_bugnote smallint DEFAULT 0 NOT NULL,

<     email_on_status smallint DEFAULT 0 NOT NULL,

<     email_on_priority smallint DEFAULT 0 NOT NULL,




email_on_new boolean DEFAULT false NOT NULL,
email_on_assigned boolean DEFAULT false NOT NULL,
email_on_feedback boolean DEFAULT false NOT NULL,
email_on_resolved boolean DEFAULT false NOT NULL,
email_on_closed boolean DEFAULT false NOT NULL,
email_on_reopened boolean DEFAULT false NOT NULL,
email_on_bugnote boolean DEFAULT false NOT NULL,
email_on_status boolean DEFAULT false NOT NULL,
email_on_priority boolean DEFAULT false NOT NULL,




# mantis_user_table

1017,1018c1005,1006

<     enabled smallint DEFAULT 1 NOT NULL,

<     protected smallint DEFAULT 0 NOT NULL,




enabled boolean DEFAULT true NOT NULL,
protected boolean DEFAULT false NOT NULL,

Additional Information

This occurred as I attempted to upgrade our copy of v1.1.6 install:

1.1.6
1.1.8
1.2.0
1.2.4
1.2.5
1.2.6
1.2.7
1.2.8
1.2.9
1.2.10

1.2.10->1.2.11 (broke)
1.2.10->1.2.12 (broke)
1.2.10->1.2.14-master-1.2.x-ee369f (broke)

1.2.11->1.2.12 (broke)
1.2.11->1.2.14-master-1.2.x-ee369f (broke)

TagsNo tags attached.
Attached Files
mantis-1.0.7.dump.gz (3,726 bytes)
mantis_migration_pgsql.txt (8,511 bytes)    
ALTER TABLE mantis_bug_file_table ALTER COLUMN content DROP NOT NULL;
ALTER TABLE mantis_project_file_table ALTER COLUMN content DROP NOT NULL;

ALTER TABLE mantis_bug_table ALTER COLUMN sticky DROP DEFAULT;
ALTER TABLE mantis_bug_table ALTER sticky TYPE bool USING CASE WHEN sticky=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_bug_table ALTER COLUMN sticky SET DEFAULT FALSE;

ALTER TABLE mantis_custom_field_table ALTER COLUMN advanced DROP DEFAULT;
ALTER TABLE mantis_custom_field_table ALTER advanced TYPE bool USING CASE WHEN advanced=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_custom_field_table ALTER COLUMN advanced SET DEFAULT FALSE;

ALTER TABLE mantis_custom_field_table ALTER COLUMN require_report DROP DEFAULT;
ALTER TABLE mantis_custom_field_table ALTER require_report TYPE bool USING CASE WHEN require_report=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_custom_field_table ALTER COLUMN require_report SET DEFAULT FALSE;

ALTER TABLE mantis_custom_field_table ALTER COLUMN require_update DROP DEFAULT;
ALTER TABLE mantis_custom_field_table ALTER require_update TYPE bool USING CASE WHEN require_update=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_custom_field_table ALTER COLUMN require_update SET DEFAULT FALSE;

ALTER TABLE mantis_custom_field_table ALTER COLUMN display_report DROP DEFAULT;
ALTER TABLE mantis_custom_field_table ALTER display_report TYPE bool USING CASE WHEN display_report=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_custom_field_table ALTER COLUMN display_report SET DEFAULT TRUE;

ALTER TABLE mantis_custom_field_table ALTER COLUMN display_update DROP DEFAULT;
ALTER TABLE mantis_custom_field_table ALTER display_update TYPE bool USING CASE WHEN display_update=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_custom_field_table ALTER COLUMN display_update SET DEFAULT TRUE;

ALTER TABLE mantis_custom_field_table ALTER COLUMN require_resolved DROP DEFAULT;
ALTER TABLE mantis_custom_field_table ALTER require_resolved TYPE bool USING CASE WHEN require_resolved=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_custom_field_table ALTER COLUMN require_resolved SET DEFAULT FALSE;

ALTER TABLE mantis_custom_field_table ALTER COLUMN display_resolved DROP DEFAULT;
ALTER TABLE mantis_custom_field_table ALTER display_resolved TYPE bool USING CASE WHEN display_resolved=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_custom_field_table ALTER COLUMN display_resolved SET DEFAULT FALSE;

ALTER TABLE mantis_custom_field_table ALTER COLUMN display_closed DROP DEFAULT;
ALTER TABLE mantis_custom_field_table ALTER display_closed TYPE bool USING CASE WHEN display_closed=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_custom_field_table ALTER COLUMN display_closed SET DEFAULT FALSE;

ALTER TABLE mantis_custom_field_table ALTER COLUMN require_closed DROP DEFAULT;
ALTER TABLE mantis_custom_field_table ALTER require_closed TYPE bool USING CASE WHEN require_closed=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_custom_field_table ALTER COLUMN require_closed SET DEFAULT FALSE;

ALTER TABLE mantis_filters_table ALTER COLUMN is_public DROP DEFAULT;
ALTER TABLE mantis_filters_table ALTER is_public TYPE bool USING CASE WHEN is_public=0 THEN FALSE ELSE TRUE END;

ALTER TABLE mantis_news_table ALTER COLUMN announcement DROP DEFAULT;
ALTER TABLE mantis_news_table ALTER announcement TYPE bool USING CASE WHEN announcement=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_news_table ALTER COLUMN announcement SET DEFAULT FALSE;

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

ALTER TABLE mantis_project_version_table ALTER COLUMN released DROP DEFAULT;
ALTER TABLE mantis_project_version_table ALTER released TYPE bool USING CASE WHEN released=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_project_version_table ALTER COLUMN released SET DEFAULT TRUE;

ALTER TABLE mantis_sponsorship_table ALTER COLUMN paid DROP DEFAULT;
ALTER TABLE mantis_sponsorship_table ALTER paid TYPE bool USING CASE WHEN paid=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_sponsorship_table ALTER COLUMN paid SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN advanced_report DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER advanced_report TYPE bool USING CASE WHEN advanced_report=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN advanced_report SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN advanced_view DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER advanced_view TYPE bool USING CASE WHEN advanced_view=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN advanced_view SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN advanced_update DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER advanced_update TYPE bool USING CASE WHEN advanced_update=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN advanced_update SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER redirect_delay TYPE bool USING CASE WHEN redirect_delay=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_new DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER email_on_new TYPE bool USING CASE WHEN email_on_new=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_new SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_assigned DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER email_on_assigned TYPE bool USING CASE WHEN email_on_assigned=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_assigned SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_feedback DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER email_on_feedback TYPE bool USING CASE WHEN email_on_feedback=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_feedback SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_resolved DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER email_on_resolved TYPE bool USING CASE WHEN email_on_resolved=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_resolved SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_closed DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER email_on_closed TYPE bool USING CASE WHEN email_on_closed=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_closed SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_reopened DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER email_on_reopened TYPE bool USING CASE WHEN email_on_reopened=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_reopened SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_bugnote DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER email_on_bugnote TYPE bool USING CASE WHEN email_on_bugnote=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_bugnote SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_status DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER email_on_status TYPE bool USING CASE WHEN email_on_status=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_status SET DEFAULT FALSE;

ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_priority DROP DEFAULT;
ALTER TABLE mantis_user_pref_table ALTER email_on_priority TYPE bool USING CASE WHEN email_on_priority=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_pref_table ALTER COLUMN email_on_priority SET DEFAULT FALSE;

ALTER TABLE mantis_user_table ALTER COLUMN enabled DROP DEFAULT;
ALTER TABLE mantis_user_table ALTER enabled TYPE bool USING CASE WHEN enabled=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_table ALTER COLUMN enabled SET DEFAULT TRUE;

ALTER TABLE mantis_user_table ALTER COLUMN protected DROP DEFAULT;
ALTER TABLE mantis_user_table ALTER protected TYPE bool USING CASE WHEN protected=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mantis_user_table ALTER COLUMN protected SET DEFAULT FALSE;
mantis_migration_pgsql.txt (8,511 bytes)   

Relationships

Relationship GraphDependency Graph
has duplicate 0014375 closeddregad Postgres SQL error: smallint vs. boolean 

Activities

jdraughn

jdraughn

2013-01-29 14:33

reporter   ~0034956

Tested against PostgreSQL 8.4.15
dregad

dregad

2013-01-30 03:16

developer   ~0034963

Thanks for the detailed and comprehensive bug report. I'll have a look as time allows, and will get back to you.
dregad

dregad

2013-01-30 05:29

developer   ~0034975

I tried to reproduce the problem here by installing a fresh 1.1.6 instance, but I was not able to complete the installation, it failed here:



Schema CreateTableSQL ( mantis_bug_table )             BAD


CREATE TABLE mantis_bug_table (

[snip - removed other columns]

sticky                   BOOLEAN DEFAULT 0 NOT NULL,

PRIMARY KEY (id)

)


ERROR:  column "sticky" is of type boolean but default expression is of type integer

HINT:  You will need to rewrite or cast the expression.

Therefore I am not sure how you got up and running with 1.1.6 on Postgres in the first place... Did you possibly generate the SQL, tweak it and ran it manually ? If that is the case, it could explain the discrepancy between your schema and the one generated by the MantisBT installer, which has always declared boolean columns using ADOdb's 'L' (Logical) type (translating to boolean under PostgreSQL).

I did not check whether there were changes to ADOdb library between 1.1.6 and 1.2.x, but I doubt it.

Note that I'm using postgres 9.1, I don't have access to an 8.4 (but I don't think that's relevant to the issue)
jdraughn

jdraughn

2013-01-30 15:23

reporter   ~0034984

Additional Information

This install was a 1.0.7 (July '07) install which was upgraded 1.1.1 (Oct '08) -> 1.1.2 (Dec '08) -> 1.1.5 (Dec '08) -> 1.1.6 (Sept '11)

It is currently running on a soon-to-be-decommissioned Fedora Core 4 running PostgreSQL 8.0.3.

I vaguely recall the install procedure being one of the initial versions which supported PostgreSQL. I also recall stopping at 1.1.6 due to errors (possibly the same here).

I will test various installs, diff the schemas and report back.
jdraughn

jdraughn

2013-01-30 19:36

reporter   ~0034988

I believe it was the jump between 1.0.7 and 1.1.1. A quick file diff shows adodb was updated between those revisions.

Performing fresh installations:

1.0.7 installs on PostgreSQL 8.0.03
1.1.1+ fails with the same error you specified
1.2.0+ fails with "php is too old"

Due to the "experimental" nature of the PostgreSQL support back in 1.0.x/1.1.x, it seems the best course of action, for me, is:

DB dump my v1.1.6 (current)
Manually clean the tables and data
Import db dump
Upgrade from v1.1.6 using standard upgrade procedures.

Thoughts?
jdraughn

jdraughn

2013-01-30 19:39

reporter   ~0034989

Attached a "pg_dump -d" of the 1.0.7 database fresh install for reference.

"sticky" as referenced above is created as a smallint.
dregad

dregad

2013-01-31 06:48

developer   ~0034991

jdraughn,

I confirm that this is caused by ADOdb.

I checked the code bundled with MantisBT 1.0.7 (version 4.60), and found out that the PostgreSQL data dictionary class (datadict-postgres.inc.php) maps the ADOdb "L" type to SMALLINT (see ActualType() method). This was changed with commit f544322a (in Mantis 1.1.0), so your assumption is correct.

In other words, our installer/upgrader is not able to cope with data type changes in underlying ADOdb library. I believe that fixing this would not be simple (since have to respect the schema versions/sequence, and require a few (ugly) hacks to schema.php to including call to functions prior to executing DDL statements.

With regards to your upgrade path, I believe your overall approach is sound. This is what I would do:

  • make a copy of your 1.1.6 instance (a)
  • perform a fresh install of 1.1.6
  • compare (a) and (b)
  • apply differences to your instance (this will probably require changing type of "L" columns from SMALLINT columns to BOOLEAN and updating data as appropriate, maybe others changes as well ?)
  • run standard upgrade process

There could potentially be other issues moving from 1.1.x to 1.2.x series, as the ADOdb was upgraded from 4.x to 5.x in 1.2.0rc1. If that is the case and you get other errors, maybe you'll want to upgrade in multiple steps, first from 1.1.6 to release-1.2.0a3, execute the above steps again, and continue until you get to 1.2.14.
jdraughn

jdraughn

2013-01-31 10:45

reporter   ~0034996

Thanks for your input. I will update within a day or so with the outcome, if desired.

I suppose you can close the ticket due to the legacy of the code.
jdraughn

jdraughn

2013-01-31 10:54

reporter   ~0034997

Just a side thought. A test case on schema generations against prior schema for supported DBMS after updates to ADODb.
dregad

dregad

2013-02-01 04:26

developer   ~0035005

A test case on schema generations against prior schema for supported DBMS after updates to ADODb.

In an ideal world that would be what we would do. Unfortunately, the only information we have when looking at a Mantis DB, is the schema version, which does not tell us anything about the release that actually executed the individual installation steps.

For example, given a database on schema version 60 (MantisBT 1.1.0) that you want to upgrade to 1.2.14, the code has no way of knowing whether installation steps <= 51 (MantisBT 1.0.7) were executed with 1.0.7 code and later upgraded to 1.1.0, or it it was installed directly as 1.1.0.

I think the only way of achieving this would be, when reaching the points where significant ADOdb upgrades took place, to

  1. issue a generic warning to inform administrator to carefully check their schema because there could be discrepancies, or

  2. individually check the database schema for known cases and either

    • try and fix them automatically (might be difficult while maintaining the schema version's sequence)
    • let the admin fix them manually

  3. is not very user-friendly and possibly confusing, while 2. may miss some cases
jdraughn

jdraughn

2013-03-22 17:32

reporter   ~0035952

I attached the file I used as reference to update my v1.1.6.

As there are errors from 1.1.6->1.1.8 installs to postgresql, I printed the schema commands and manually cleaned them up (as they set "DEFAULT 0" for BOOLEAN fields).

Using the diffs of the schema between my 1.1.6 and the cleaned up 1.1.6, I created the file attached "mantis_migration_pgsql.txt" to clean up my production database.

Logins FAILED at this point, as it was looking for "enabled=0" or similar. I updated to 1.1.8 to similar issues. The problem did not go away until I upgraded to 1.2.0.

After successful login via 1.2.0, I immediately upgraded to 1.2.14 without any apparent issues thus far.
dregad

dregad

2013-03-22 19:04

developer   ~0035953

Many thanks for sharing your experiences and the details of the steps you followed to work around the problem. Hopefully it will be useful to others in the future.

I hope you won't mind, considering that this issue is quite a rare case that would only affect pgsql users upgrading from very old Mantis versions, and implementing a fix would not only be quite complex but also unlikely to be fully satisfactory (see note 0015440:0035005), I will now resolve this issue.

Feel free to reopen if you face other issues related to type change.
grangeway

grangeway

2014-05-16 15:02

reporter   ~0040519

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