MantisBT

View Issue Details Jump to Notes ] Wiki ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0009720mantisbtdb postgresqlpublic2008-10-20 20:112014-05-16 15:02
Reporterolegos 
Assigned Todregad 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionduplicate 
PlatformOSOS Version
Product Version1.2.1 
Target VersionFixed in Version 
Summary0009720: Error upgrading from 1.1.x (PostgreSQL)
DescriptionTrying to upgrade from 1.1.3 to 1.2.0a2 (really, trunk r5703). Using PostgreSQL 8.3 ("Running pgsql version PostgreSQL 8.3.3 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7)"). Executing admin/install.php, and after getting through 0009701, I'm running into the following error in step "Schema Array ( mantis_user_pref_table )":

BAD
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay TYPE INTEGER
ERROR: column "integer" of relation "mantis_user_pref_table" does not exist

I don't see anything wrong with the query, and I can execute it without errors in psql (same host, same postgres user):

mantis=> ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay TYPE INTEGER ;
ALTER TABLE
mantis=>
TagsNo tags attached.
Attached Filesdiff file icon schema.php.diff [^] (1,390 bytes) 2011-06-09 05:33 [Show Content]

- Relationships
duplicate of 0014398resolveddregad Support for PostgreSQL broken in 1.3 
related to 0014399closeddregad Lack of detailed error message during schema creation 

-  Notes
User avatar (0019621)
olegos (reporter)
2008-10-21 12:10

I can see it now. Using the "Print SQL Queries" option, I see that the problem query is not the one printed, but two down:

ALTER TABLE mantis_user_pref_table ALTER COLUMN INTEGER SET NOT NULL;

So there are two problems here: this query is bad, and the error message prints wrong query.
User avatar (0019622)
olegos (reporter)
2008-10-21 12:59
edited on: 2008-10-21 13:00

After getting through the above error (by hacking the code to skip mods to mantis_user_pref_table, since I can see that everything it's trying to do is already done), here's the next error:

Schema AddColumnSQL ( mantis_bug_table ) :
BAD
ALTER TABLE mantis_bug_table ADD COLUMN due_date TIMESTAMP 00:00:01'
ERROR: column "due_date" of relation "mantis_bug_table" does not exist

The problem queries are:

ALTER TABLE mantis_bug_table ADD COLUMN due_date TIMESTAMP 00:00:01' ;
UPDATE mantis_bug_table SET due_date='1970-01-01;
ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET DEFAULT '1970-01-01;

There are obvious unbalanced quotes in each. Also, I can't figure out what that "00:00:01" after TIMESTAMP in the first query was supposed to do -- according to postgres doc, the only thing that can be there is a number 0 to 6, and/or time zone stuff (http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html [^] ).

After fixing these queries, install completed successfully.

User avatar (0025517)
rombert (developer)
2010-05-17 15:58

I'm hitting this as well on a clean install.
User avatar (0025518)
rombert (developer)
2010-05-17 15:59

We should really look into getting this fixed. @olegos: can you provide a patch which corrects the installation?
User avatar (0025525)
olegos (reporter)
2010-05-17 18:27

I'm sorry I don't have a patch. This was quite a long time ago and I don't remember for sure, but I think as this upgrade turned into an iterative process I ended up just commenting out all steps up to and including the failing one in the upgrade script, fixing and executing a problem query manually with psql, then re-running the upgrade. I did try to document all problems I've encountered here. The problem with producing a patch for me was that while I could figure out what needed to be done for my installation, I wasn't clear whether the same would be the right thing to do in all cases, esp. since most of the code was shared with other DB's and I only have experience with PG.
User avatar (0028442)
gfantone (reporter)
2011-03-18 10:21

hi,
Same config and same problem so if you edit the sql and change the line
ALTER TABLE mantis_user_pref_table ALTER COLUMN INTEGER SET NOT NULL;
into
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay SET NOT NULL;
and the lines

ALTER TABLE mantis_bug_table ADD COLUMN due_date TIMESTAMP 00:00:01' ;

UPDATE mantis_bug_table SET due_date='1970-01-01;

ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET DEFAULT '1970-01-01;

into
 
UPDATE mantis_bug_table SET due_date='1970-01-01;

ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET DEFAULT '1970-01-01 00:00:01';

ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET NOT NULL;
It's ok for the database
User avatar (0028954)
aanno (reporter)
2011-06-09 05:31

With mantis-1.2.4, I've got the same problem with a fresh install with postgresql db as well.

I circumvent the problem with the following steps:

1.
I patched /usr/share/mantis/www/admin/schema.php with:

 
--- schema.php_orig 2011-06-09 10:48:15.021330320 +0200
+++ schema.php 2011-06-09 10:58:12.371330413 +0200
@@ -383,7 +383,7 @@
        enabled L NOTNULL DEFAULT \" '0' \"
        ", Array( 'mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS' ) ) );
 
-$upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_user_pref_table' ), "redirect_delay I NOTNULL DEFAULT 0" ) );
+# $upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_user_pref_table' ), "redirect_delay I NOTNULL DEFAULT 0" ) );
 
 /* apparently mysql now has a STRICT mode, where setting a DEFAULT value on a blob/text is now an error, instead of being silently ignored */
 if ( isset( $f_db_type ) && ( $f_db_type == 'mysql' || $f_db_type == 'mysqli' ) ) {
@@ -426,8 +426,8 @@
        " ) );
 $upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_project_version_table' ), "
        obsolete L NOTNULL DEFAULT \" '0' \"" ) );
-$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_bug_table' ), "
- due_date T NOTNULL DEFAULT '" . db_null_date() . "' " ) );
+#$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_bug_table' ), "
+# due_date T NOTNULL DEFAULT '" . db_null_date() . "' " ) );
 
 $upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_custom_field_table' ), "
   filter_by L NOTNULL DEFAULT \" '1' \"" ) );

2.
I run the install with the browser [http://localhost/mantis/admin/install.php [^]].

3.
This leads to an error concerning 'due_date'.

4.
I run the following SQL on the mantis db:
alter table mantis_bug_table add COLUMN due_date timestamp not null

5.
I rerun the installer with the browser [http://localhost/mantis/admin/install.php [^]] to 'upgrade' the installation.
User avatar (0028955)
rombert (developer)
2011-06-09 06:52

Thanks. Can you provide the errors reported?
User avatar (0028967)
aanno (reporter)
2011-06-10 06:30

1. Problem
User to create by install script has insufficient rights:

BAD
Database user doesn't have access to the database ( FEHLER: Syntaxfehler bei »'mantis'« LINE 1: CREATE USER 'mantis' WITH PASSWORD 'mantis123' ^ )
BAD
Database ERROR. Try again, uninstall manually the Database if it was created, and return to the installation.

Workaround: Create user manually:

CREATE ROLE mantis LOGIN ENCRYPTED PASSWORD 'md532783c2352bbb609188bc11f55c17bae'
  CREATEDB
   VALID UNTIL 'infinity';

2. Problem caused by
$upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_user_pref_table' ), "redirect_delay I NOTNULL DEFAULT 0" ) );

Schema AlterColumnSQL ( mantis_user_pref_table )
BAD
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay TYPE INTEGER
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay SET DEFAULT 0
ALTER TABLE mantis_user_pref_table ALTER COLUMN INTEGER SET NOT NULL
FEHLER: Spalte »integer« von Relation »mantis_user_pref_table« existiert nicht
BAD
Database ERROR. Try again, uninstall manually the Database if it was created, and return to the installation.

3. Problem caused by
$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_bug_table' ), "due_date T NOTNULL DEFAULT '" . db_null_date() . "' " ) );


Schema AddColumnSQL ( mantis_bug_table )
BAD
ALTER TABLE mantis_bug_table ADD COLUMN due_date TIMESTAMP 00:00:01'
UPDATE mantis_bug_table SET due_date='1970-01-01
ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET DEFAULT '1970-01-01
ALTER TABLE mantis_bug_table ALTER COLUMN due_date SET NOT NULL
FEHLER: Spalte »due_date« von Relation »mantis_bug_table« existiert nicht
BAD
Database ERROR. Try again, uninstall manually the Database if it was created, and return to the installation.

4. Problem

APPLICATION ERROR 0000401

Database query failed. Error received from database was #-1: FEHLER: Spalte »due_date« existiert nicht
LINE 1: SELECT id, date_submitted,due_date,last_updated FROM mantis_...
^ for the query: SELECT id, date_submitted,due_date,last_updated FROM mantis_bug_table.

Please use the "Back" button in your web browser to return to the previous page. There you can correct whatever problems were identified in this error or select another action. You can also click an option from the menu bar to go directly to a new section.

Workaround:
alter table mantis_bug_table add COLUMN due_date timestamp not null
User avatar (0032141)
dregad (developer)
2012-06-22 05:08

I hit the exact same problem trying to fix PostgreSQL support for 1.3.x (see 0014398). The error is actually caused by a bug in ADOdb which generates incorrect SQL.

Feel free to have a look at the work-in-progress github branch (not sure how easily it would apply to 1.2 - use at your own risk, and please report any issues directly in 0014398).

The problem with the error message listed to the wrong statement has already been fixed (see 0014399).
User avatar (0040528)
grangeway (developer)
2014-05-16 15:02

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

- Issue History
Date Modified Username Field Change
2008-10-20 20:11 olegos New Issue
2008-10-21 01:03 vboctor Category upgrade => db postgresql
2008-10-21 12:10 olegos Note Added: 0019621
2008-10-21 12:59 olegos Note Added: 0019622
2008-10-21 13:00 olegos Note Edited: 0019622
2010-05-17 15:58 rombert Note Added: 0025517
2010-05-17 15:58 rombert Assigned To => rombert
2010-05-17 15:58 rombert Status new => confirmed
2010-05-17 15:59 rombert Note Added: 0025518
2010-05-17 15:59 rombert Product Version 1.2.0a2 => 1.2.1
2010-05-17 15:59 rombert Target Version => 1.2.2
2010-05-17 18:27 olegos Note Added: 0025525
2010-07-29 10:41 jreese Target Version 1.2.2 => 1.2.3
2010-07-30 08:36 rombert Assigned To rombert =>
2010-09-14 10:55 jreese Target Version 1.2.3 => 1.2.4
2010-12-14 21:05 jreese Target Version 1.2.4 => 1.2.5
2011-03-18 10:21 gfantone Note Added: 0028442
2011-04-05 12:25 jreese Target Version 1.2.5 => 1.2.6
2011-06-09 05:31 aanno Note Added: 0028954
2011-06-09 05:33 aanno File Added: schema.php.diff
2011-06-09 05:40 aanno Sponsorship Added aanno: US$ 10
2011-06-09 05:40 aanno Sponsorship Total 0 => 10
2011-06-09 06:52 rombert Note Added: 0028955
2011-06-09 06:52 rombert Sponsorship Total 10 => 0
2011-06-09 06:52 rombert Description Updated View Revisions
2011-06-10 06:30 aanno Note Added: 0028967
2011-07-26 09:53 jreese Target Version 1.2.6 => 1.2.7
2011-08-22 10:49 jreese Target Version 1.2.7 => 1.2.8
2011-09-06 10:33 jreese Target Version 1.2.8 => 1.2.9
2012-03-04 09:23 atrol Target Version 1.2.9 => 1.2.10
2012-04-02 02:33 atrol Target Version 1.2.10 => 1.2.11
2012-06-06 23:54 jreese Target Version 1.2.11 => 1.2.12
2012-06-22 05:08 dregad Note Added: 0032141
2012-06-22 05:08 dregad Relationship added duplicate of 0014398
2012-06-22 05:08 dregad Status confirmed => resolved
2012-06-22 05:08 dregad Resolution open => duplicate
2012-06-22 05:08 dregad Assigned To => dregad
2012-06-22 05:09 dregad Target Version 1.2.12 =>
2012-06-22 05:09 dregad Description Updated View Revisions
2012-06-22 05:10 dregad Relationship added related to 0014399
2012-07-02 03:20 atrol Status resolved => closed
2014-05-16 15:02 grangeway Note Added: 0040528


MantisBT 1.2.17 [^]
Copyright © 2000 - 2014 MantisBT Team
Time: 0.1130 seconds.
memory usage: 3,162 KB
Powered by Mantis Bugtracker