862070 - PG schema upgrade failed with single PG DB and multiple bugzilla SCHEMAS from bugzilla-4.0.11 to 4.2.7

Status: UNCONFIRMED

(Bugzilla :: Installation & Upgrading, defect)

Description

[olli hauer]

User Agent: Mozilla/5.0 (Windows NT 5.1; rv:20.0) Gecko/20100101 Firefox/20.0
Build ID: 20130409194949

Steps to reproduce:

I'm havving a strange issue updateing bugzilla from 4.0.10 to 4.2.5
Database postgresql-9.2.5 / p5-DBD-Pg-2.19.3

checksetup.pl and "Sanity Check" shows no issues on 4.0.10 

I created an empty bugzilla-4.0.10 and bugzilla-4.2.5 schema and noticed
on a clean schema there is no table or sequence bug_cf_browser / cf_browser
until someone enters browsers via the webfrontend.

However if the table/sequence exists checksetup from bugzilla-4.2.5 stops
with the following error message.


Actual results:

Adding new table audit_log...
Adding new table bug_tag...
Adding new table field_visibility...
Adding new table tag...
Fixing flags_id_seq to be associated with flags.id...
Fixing flagtypes_id_seq to be associated with flagtypes.id...
Fixing keyworddefs_id_seq to be associated with keyworddefs.id...
DBD::Pg::db selectrow_array failed: ERROR:  relation "cf_browser" does not exist [for Statement "SELECT pg_get_serial_sequence(?,?)"] at Bugzilla/DB/Pg.pm line 310
        Bugzilla::DB::Pg::bz_setup_database('Bugzilla::DB::Pg=HASH(0x8034cb2d0)') called at ./checksetup.pl line 148

Comment 1

[Frédéric Buclin]

What's the type of your cf_browser custom field?

Comment 2

[olli hauer]

Attachment: script to create the test scripts

The script will create a initial PgSQL multi project DB and the scripts to setup / upgrade the multi project installation with very less interaction.

Comment 3

[olli hauer]

I've hacked a script together to test an upgrade of bugzilla-4.0.10 to 4.2.5 in a multi project environment.

Do not use the a script on live database / prod. machine!

1) install PgSQL on a test machine and do a initdb

2) adjust the vars in the script and execute the script

3) $> psql -U pgsql -d template1 -f /tmp/bzPgSchemaTest/01_genSQL.sql

4) install bugzilla-4.0.10

5) $> cd bugzilla
   $> /tmp/bzPgSchemaTest/02_setup

6) $> cd bugzilla
   $> /tmp/bzPgSchemaTest/03_upgrade

7) upgrade to bugzilla-4.2.5

8) $> cd bugzilla
   $> /tmp/bzPgSchemaTest/03_upgrade


The upgrade will fail on the second schema.

To verify a fresh setup will work, keep bugzilla-4.2.5 installed
and repead the steps 3) 5) and 6).

=> Checksetup will run without issues.

Comment 4

[olli hauer]

> What's the type of your cf_browser custom field?
Seems the issue is not related to cf_browser, since bugzilla cannot update a multiple schema database without data (direct after checksetup)

Since no one has tested the provided script my last option is to migrate ~15 bugzilla schemas to dedicated databases.

Comment 5

[Frédéric Buclin]

I'm not sure what you are asking for. checksetup.pl will only upgrade one DB based on $ENV{PROJECT}. If you have several projects, you have to redefine $ENV{PROJECT} before running checksetup.pl again.

Comment 6

[olli hauer]

(In reply to Frédéric Buclin from comment #5)
> I'm not sure what you are asking for. checksetup.pl will only upgrade one DB
> based on $ENV{PROJECT}. If you have several projects, you have to redefine
> $ENV{PROJECT} before running checksetup.pl again.

That's also done in the provided test scripts, but it will fail as soon the second schema update is executed.

Comment 7

[Frédéric Buclin]

(In reply to olli hauer from comment #6)
> That's also done in the provided test scripts, but it will fail as soon the
> second schema update is executed.

Is that all done within the same script? If yes, you are probably affected by bug 843457.

Comment 8

[olli hauer]

(In reply to Frédéric Buclin from comment #7)
> Is that all done within the same script? If yes, you are probably affected
> by bug 843457.

Thanks for the hint with bug id 843457, I will try the fix and report back as soon as possible.

Comment 9

[olli hauer]

Attachment: bz40_to_42.log.gz

transcript bugzilla40 -> bugzilla42

Comment 10

[olli hauer]

Attachment: bz42_to_44.log.gz

transcript bugzilla42 -> bugzilla44 update

Comment 11

[olli hauer]

The patch from bug id 843457 does not solve this issue.

I've added two transcript files recorded with the script utility.

Steps done for both 
 - db dropped and recreated db with schema bugs_cust[0-n]
 - grant rights for the schema users

Additional steps
bz40_to_42:
 - initialize DB with bugzilla 40 (02_setup + 03_upgrade)
 - upgrade to bugzilla 42
 - apply patch from bug id 843457
 - upgrade database (03_upgrade)

bz42_to_44:
 - apply patch from bug id 843457
 - initialize DB with bugzilla 42 (02_setup + 03_upgrade)
 - upgrade to bugzilla 44
 - upgrade database (03_upgrade)

Both times the update will fail, In case dedicated databases are used the update will succeed.

Since schemas are used often with Oracle and PgSQL maybe it's a good Idea to add a warning to use a dedicated DB per PROJECT.

Most of my existing schemas have exists since bugzilla-2.20 and until now I wasn't running in such issues. Seems it's time to convert to dedicated databases.