541553 - [Oracle] During upgrade: ORA-04098: trigger 'BUGS_TIP.PRODUCTS_MILESTONEURL' is invalid and failed re-validation

Status: RESOLVED FIXED

(Bugzilla :: Installation & Upgrading, defect)

Description

[Max Kanat-Alexander]

Currently, the automatic upgrade script for the Oracle tip installation on landfill has been throwing the following error for weeks:

Adding new column 'allows_unconfirmed' to the 'products' table...
DBD::Oracle::db do failed: ORA-00604: error occurred at recursive SQL level 1
ORA-04098: trigger 'BUGS_TIP.PRODUCTS_MILESTONEURL' is invalid and failed re-validation (DBD ERROR: OCIStmtExecute) [for Statement "ALTER TABLE products ADD allows_unconfirmed integer DEFAULT 0 NOT NULL"] at Bugzilla/DB/Oracle.pm line 401
	Bugzilla::DB::Oracle::do('undef', 'ALTER TABLE products ADD allows_unconfirmed integer DEFAULT 0...') called at Bugzilla/DB.pm line 498
	Bugzilla::DB::bz_add_column('Bugzilla::DB::Oracle=HASH(0xb4d21bc)', 'products', 'allows_unconfirmed', 'HASH(0xd2813c8)') called at Bugzilla/Install/DB.pm line 3338
	Bugzilla::Install::DB::_add_allows_unconfirmed_to_product_table() called at Bugzilla/Install/DB.pm line 595
	Bugzilla::Install::DB::update_table_definitions('HASH(0x95eb8cc)') called at ./checksetup.pl line 192

Comment 1

[Xiaoou]

Fine, I think this is because when PRODUCTS.MILESTONEURL is not existed anymore.

I think I should do something on get_drop_table_ddl.

Comment 2

[Frédéric Buclin]

So I guess this bug should be assigned to you. :)

Comment 3

[Max Kanat-Alexander]

Hey Xiaoou. Any chance of getting a patch for this? This prevents anybody from using the Oracle-based test install of Bugzilla, and will also prevent us from releasing 3.6 until it's fixed.

Comment 4

[Max Kanat-Alexander]

Xiaoou: This is soon to be the only blocker left for 3.6. If it doesn't get fixed, we'll simply have to release without Oracle support.

Comment 5

[Xiaoou]

Attachment: v1

I apologize for the delay, I was on holiday.

Please have a look at the patch.

Comment 6

[Max Kanat-Alexander]

Comment on attachment 428052 [details] [diff] [review]
v1

Shouldn't dropping the trigger happen first, before the column is dropped?

Comment 7

[Xiaoou]

I think we don't need to consider the order, we just need to make sure that there is not that trigger when the column doesn't exist.

Comment 8

[Max Kanat-Alexander]

Okay. So how are we going to handle this for any installations that are currently in this state? The change to get_drop_column_ddl helps prevent it from happening, but what about situations where this has already happened, like on our current tip installation, or for anybody who tried to upgrade their Oracle install to the current development version?

Comment 9

[Jochen Wiedmann]

I managed to workaround this problem using 3.5.5 by applying the following patch as a workaround:

--- Bugzilla/Install/DB.pm.~1.83.~      2009-12-31 14:01:48.000000000 +0100
+++ Bugzilla/Install/DB.pm      2010-02-22 07:55:06.000000000 +0100
@@ -590,6 +590,10 @@

     _set_attachment_comment_types();

+    if ($dbh->bz_column_info('products', 'milestoneurl')) {
+        $dbh->bz_alter_column('products', 'milestoneurl',
+            {TYPE => 'TINYTEXT', NOTNULL => 0});
+    }
     $dbh->bz_drop_column('products', 'milestoneurl');

     _add_allows_unconfirmed_to_product_table();

Comment 10

[Xiaoou]

(In reply to comment #8)
> Okay. So how are we going to handle this for any installations that are
> currently in this state? The change to get_drop_column_ddl helps prevent it
> from happening, but what about situations where this has already happened, like
> on our current tip installation, or for anybody who tried to upgrade their
> Oracle install to the current development version?

For this case, shall we handle it by deleting the triggers manually or put some code in Bugzilla::Install::DB ?

Comment 11

[Max Kanat-Alexander]

(In reply to comment #10)
> For this case, shall we handle it by deleting the triggers manually or put some
> code in Bugzilla::Install::DB ?

  Put some code into Bugzilla::DB::Oracle::bz_setup_database.

Comment 12

[Max Kanat-Alexander]

  Well...could there be any other triggers that are causing a problem? If this is the only trigger that's a problem, then we can just delete this one manually inside of bz_setup_database.

  In any case, any chance of getting a new patch soon? This is getting close to being our only blocker for 3.6.

Comment 13

[Jochen Wiedmann]

What are the reasons for not simply taking my proposed workaround? Should be completely sufficient for releasing 3.6.

Comment 14

[Max Kanat-Alexander]

  Jochen: Ideally I want to make sure that there aren't any other similar triggers lying around the database that are going to cause trouble later, either for customizers or for us down the road.

Comment 15

[Jochen Wiedmann]

Max, I can follow you when we are talking about applying my workaround on the trunk, where one should indeed think in the medium to long term time frame as you do.

The 3.6 branch is another matter, IMO. Here my proposal should be sufficient to get rid of this bug as a blocker.

Comment 16

[Max Kanat-Alexander]

Jochen: Your argument is definitely reasonable, and if this becomes the last remaining blocker (it isn't, currently), it's something I will consider.

Comment 17

[Xiaoou]

Attachment: v2

drop triger PRODUCTS_MILESTONEURL in bz_setup_database

Comment 18

[Max Kanat-Alexander]

Comment on attachment 429476 [details] [diff] [review]
v2

Looks great! Thanks, Xiaoou! :-)

Comment 19

[Max Kanat-Alexander]

Committing to: bzr+ssh://bzr.mozilla.org/bugzilla/trunk/
modified Bugzilla/DB/Oracle.pm
modified Bugzilla/DB/Schema/Oracle.pm
Committed revision 7040.

Committing to: bzr+ssh://bzr.mozilla.org/bugzilla/3.6/                         
modified Bugzilla/DB/Oracle.pm
modified Bugzilla/DB/Schema/Oracle.pm
Committed revision 7009.