Conference 44.370::system_management

294.0. "RDB 3.0B Mandatory Patch" by SHAPES::DARNTONL () Thu Oct 26 1989 18:08

                   < Rdb/VMS V3.0B MANDATORY PATCH>

I have received this advanced information about a MANDATORY PATCH  for 
Rdb/VMS V3.0B.  This patch is not yet available, but we are taking steps to
obtain it.  Meanwhile, if you suffer any problems as described below, or if 
any of your Rdb databases fall into the risk group as described below,
please mail Peter Russell or, in his absence, Jerry Thompson, and we will
puch back to expedite to release of the patch.

________________________________________________________________________________

      TITLE: Rdb/VMS V3.0B: Deleting Metadata Can Cause NODBKEY Errors

PROBLEM:

Through a series of independent steps, it is possible to corrupt page 2,3 or
4 in a uniform area.  This problem will most likely occur in a development
environment where tables and indices are commonly being deleted and recreated.
The corruption will normally show up as NODBKEY errors.  This article contains
a MANDATORY patch to Rdb/VMS V3.0B to prevent the corruption from happening.

ANALYSIS:

The problem will only occur if pages 2,3,4 do not contain area bitmap pages
(ABM).  In order to free up pages 2,3,4 you must delete the relation or
index that owns these pages (see the steps for details).  If you have a
multifile database where you have not deleted any metadata from uniform
areas since database creation or RDO IMPORT time, you will not encounter
this problem.  If you have a single file database you will not see this 
problem.  The problem does not occur on RDB$SYSTEM uniform area.

The steps to reproduce the corruption listed below.  Anywhere a table is
mentioned, an index will also cause problems.  Index nodes are mentioned
in a few places to clearify the steps.

 Start with a multifile database.  Single file databases (ie, any Rdb/VMS
 V2.n database and V3.0 single file databases) will not have the bug because
 Rdb does not get a chance to reuse pages 2,3,4.

 Create a uniform area with at least four tables. It is possible to
 reproduce this problem with only one table in a uniform area.

 COMMIT FINISH.  This updates your transaction sequence number (TSN) and 
 transaction ID (TID) so that you can reuse deleted space (if any).

 STORE about 100 records in each table.  This fills in some of the data
 pages and create index nodes. 

 COMMIT FINISH.  Same reason as before.

 Drop the table which has the ABM starting at page 2.  The table  which was
 defined first in the area will have the ABM at page 2. The drop frees up
 pages 2,3,4 once the COMMIT and FINISH are done.

 NOTE: At this point if you immediately did a CREATE INDEX or CREATE TABLE,
 Rdb does not reuse pages 2,3,4 because your TSN and TID have not changed. 
 Instead pages 2,3,4 are available to be corrupted in the same way as 
 described below.  Only a COMMIT and FINISH would allow you to reuse pages
 2,3,4 as ABM pages for the new table/index and the COMMIT FINISH could be 
 used as a workaround.  This is a way to create the corruption with only one
 table/index in a uniform area.

 COMMIT FINISH. A must here in order to change the TSN and TID.

 Add another 100 records to one of the remaining tables. This will reclaim
 pages 2,3,4 for record type you just stored and properly store data on those
 pages.

 COMMIT FINISH (MUST)

 Delete all the records in the table which owns pages 2,3,4.  A variation
 would be to delete some records on pages 2,3,4.  Do not delete the table
 just the records within the table.

 COMMIT FINISH (MUST)

 Store any record from the remaining record types and you have pages 2,3,4
 being corrupted as mixed pages in uniform area.  The store is actually 
 starting from 0 (a non-existent page), then at 1 (a SPAM) and then data
 pages 2,3,4.  If pages 2,3,4 are full, the store correctly goes back to the
 SPAM and behaves normally.

 To complicate matters a little further do a DELETE FROM on the table which
 owns pages 2,3,4.  This will effectively delete all the lines on pages 2,3,4
 and now the indices pointing to the data will be corrupt also.  The indices 
 now have dbkeys which no longer point to data records.  

    
HOW TO DETERMINE IF YOU HAVE THE PROBLEM ALREADY:

A full RMU/VERIFY if detect this problem.

The corruption shows up mainly in sequential operations unless you have
deleted all the records from the table which owns pages 2,3 and 4. A SELECT
* on the table that owns pages 2,3,4 will return NODBKEY errors. The same
SELECT with an RSE will return the correct data if you have an index on the
table and the optimizer uses the index.  The level one index nodes will have
dbkeys pointing directly at the correct lines.  A SELECT * from the tables
which have stored records on pages 2,3,4 can return zero rows if sequential
retrieval or the correct rows if index retrieval. 

If pages 2,3,4 have had all their data deleted, any indices that contained
the correct dbkeys will now have bad pointers in the format LA:P:LL where LA =
logical area, P = 2, 3 or 4, LL = any line number.  Also, if index nodes 
where on pages 2,3,4, the bad dbkey format will be the same.

To check for the problem in uniform areas simply dump pages 2,3,4 and look
for different type records.  In a uniform area all record types or index node
types must be the same on all pages within a clump of pages.  If pages 2,3,4
contain ABM pages (the most common case), the uniform area is fine.  The
RMU dump command is:
   
  RMU/DUMP/AREA=uniform_area_name/START=2/END=4/OUTPUT=CHECK.LIS MF_PERSONNEL

You can search for "record type" in CHECK.LIS to looked for mixed record
types.  The search command would be:

  SEARCH CHECK.LIS "record type"

and the resulting list will should you all the record types on pages 2,3,4.
The record type should be the same for those pages.


HOW TO REPAIR THE MIXED PAGES

 If you have a database with mixed pages 2,3,4 in a uniform area, the best
 solution is to move the data and then delete all the records from the table
 that owns pages 2,3,4.  If the mixed data on pages 2,3,4 is index nodes,
 just delete the indices (leave the owner of 2,3,4 until last). To move the
 data records you must already have an index defined and then force the
 optimizer to use the index.  Apply the patch (avoid  further problems),
 create a new storage area with table(s) which match the bad ones, and
 insert into the new table(s) all the data from the old table(s).  Once the
 insert(s) are done delete all the records from the table which owns page
 2,3,4.  At this point you can reload the owner of 2,3,4 without further
 problems.

To move the data from a table in MF_PERSONNEL you could do the following SQL
command:

  INSERT INTO NEW_DEPT SELECT * FROM  DEPARTMENTS 
                                WHERE DEPARTMENT_CODE > 0;

The RSE 'WHERE DEPARTMENT_CODE > 0' influences the optimizer to use the index 
which is defined on DEPARTMENTS.