Errors in database migration scripts

Question

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?

Answer 1

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.

Answer 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.