2

The following databases exist for each internally and independently developed software (web-based or client-server) application for a company:

  • Development (D1 and D2)
  • Integration
  • Staging
  • Testing
  • Production

The Production environment is shared between applications.

Migrating applications requires:

  • Run a script that changes the Production database for the new application version
  • Deploy a new version of the software application

My questions:

  1. Is it acceptable for the Production database update script to contain ERRORs?
  2. Why or why not?
Improve this question
1
  • The whole reason you migrate from one environment to another is to remove errors occurring in configuration/functionality etc. My suggestion is go back and clearly define the purpose of each environment, and this question will answer itself. Commented Feb 10, 2010 at 4:36

2 Answers 2

Reset to default
2

It is certainly unacceptable for the script to have unexpected errors in production. Just because it's production and we want to be sure that everything is okay.

As for expected errors, that's a matter of taste. Some DDL scripts look like this:

create table t23 
    (id number not null
     , description not null varchar2(30)
     , constraint t23_pk primary key (id))
/

prompt patch for ticket #42

alter table t23 
    add active_flag char(1) default 'N' not null
/

When this script is deployed as an upgrade into production the create fails, because the table already exists. Provided that is expected I think that is acceptable. But undoubtably it muddies the script logfile, which makes it harder to check that the upgrade succeeded. That is why many people would prefer a separate patch script which just applies the delta, and so consists of statements which should succeed.

edit

Is that an argument for having scripts that run clean? When upgrading a production system, do you really want the DBA (who may or may not know anything about the system) making decisions on what errors are okay?

It certainly can be such an argument. Different rules apply when the person who wrote the maintenance scripts also runs them in production compared to projects where patches are applied by outsourced DBAs in a hosted environment. In this latter case clarity is paramount.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Sometimes you may have looping dependencies (eg a function queries a view which calls the function) so may have 'transitory' errors as the script is applied but which do not leave the database in an error state.
Messages due to missing dependencies are warnings not errors. In my book, an error is something which prevents the creation of an object.
"But undoubtably it muddies the script logfile, which makes it harder to check that the upgrade succeeded." Is that an argument for having scripts that run clean? When upgrading a production system, do you really want the DBA (who may or may not know anything about the system) making decisions on what errors are okay? Otherwise you have to document the errors and ensure the documentation does not get out of sync with changes (fixes/updates) to the migration scripts.
2

Is there any error that you can't handle in a script? No (e.g. if you really must have the CREATE TABLE in there, you could put it in an EXECUTE IMMEDIATE wrapped in a PL/SQL block with an exception handler that only swallows the exceptions you expect to get) - so your scripts should handle all expected errors gracefully.

That way when the dba (or you, 3 months later when you've forgotten which errors can be safely ignored) doesn't have to worry.

Improve this answer

2 Comments

That is a good point. I was thinking about in-house systems, but that's certainly an argument for keeping scripts clean, in general.
+1, If it is important enough to go against production, it is important enough to code up exception handling. If there really is an error then definitely raise it and have someone handle it. If there are simple 'object already exists' type errors, it only takes a couple of lines of PL/SQL to wrap it safely so it doesn't choke if the object still exists.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.