858911 - Oracle fails with "ORA-04043: object T_GROUP_CONCAT does not exist" when installing Bugzilla for the first time

Status: RESOLVED FIXED

(Bugzilla :: Installation & Upgrading, defect)

Description

[Frédéric Buclin]

This is a regression due to bug 780053, affecting Bugzilla 4.2.4 and newer. The first time you install Bugzilla using Oracle, T_GROUP_CONCAT doesn't exist yet and so "DROP TYPE T_GROUP_CONCAT" fails:

DBD::Oracle::db do failed: ORA-04043: object T_GROUP_CONCAT does not exist (DBD ERROR: OCIStmtExecute) [for Statement "DROP TYPE T_GROUP_CONCAT"] at Bugzilla/DB/Oracle.pm line 387, <DATA> line 577.
        Bugzilla::DB::Oracle::do(undef, 'DROP TYPE T_GROUP_CONCAT') called at Bugzilla/DB/Oracle.pm line 534
        Bugzilla::DB::Oracle::bz_setup_database('Bugzilla::DB::Oracle=HASH(0xab02468)') called at ./checksetup.pl line 118

So we need a conditional DROP, i.e. only execute this command if T_GROUP_CONCAT exists.

Comment 1

[Frédéric Buclin]

Attachment: patch, v1

Comment 2

[andershol]

An alternative could be to just catching the exception:

BEGIN
   EXECUTE IMMEDIATE 'DROP TYPE T_GROUP_CONCAT';
EXCEPTION
   WHEN OTHERS THEN IF SQLCODE != -4043 THEN RAISE; END IF;
END;

But since "CREATE OR REPLACE TYPE" is used, is the drop even needed?

Comment 3

[Frédéric Buclin]

(In reply to andershol from comment #2)
> But since "CREATE OR REPLACE TYPE" is used, is the drop even needed?

That's a question for you. You wrote that code, see bug 780053 comment 5:

"Adds the "MAP MEMBER FUNCTION" (copied from David), and makes the "CREATE OR REPLACE TYPE T_CLOB_DELIM" run on every run to update old installations, but this made it nessesary to add a "DROP TYPE T_GROUP_CONCAT" since this type depended on T_CLOB_DELIM."

Comment 4

[andershol]

(In reply to Frédéric Buclin from comment #3)
> (In reply to andershol from comment #2)
> > But since "CREATE OR REPLACE TYPE" is used, is the drop even needed?
> That's a question for you. You wrote that code, see bug 780053 comment 5:

This bug is evidence that I was wrong in that bug, so I guess I was just wondering how wrong I was (and I believe I had overlooked the "OR REPLACE" at the time). Anyway, it seems safer to drop it first as you did in your patch, so I shouldn't have been intervening with "micro-optimizations", sorry.

Comment 5

[David Taylor]

The CREATE OR REPLACE TYPE T_CLOB_DELIM is unrelated, and looks fine.

The DROP is for a different type, T_GROUP_CONCAT, that references T_CLOB_DELIM.  As it won't exist in a new install, it needs to be conditional (or the exception caught as you said).

The patch looks sensible to me...

Comment 6

[David Lawrence [:dkl]]

Comment on attachment 734228 [details] [diff] [review]
patch, v1

Review of attachment 734228 [details] [diff] [review]:
-----------------------------------------------------------------

Works as expected. r=dkl

Comment 7

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/DB/Oracle.pm
Committed revision 8613.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.4/
modified Bugzilla/DB/Oracle.pm
Committed revision 8546.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.2/
modified Bugzilla/DB/Oracle.pm
Committed revision 8208.