Re: Assert failed: multiple view entries

See my answers below;

Hendrik Schwachenwalde wrote:

> Thanks Toby,
> 
> I will try that.
> 
> Just for my information:
> 
> 1. Does deleting an entry from SYS_INFO.sUSRView
> reclaim the space used by the view to be deleted, or 
> does that leave the view data "orphaned"?

Deleting the table entry from the sUSRView table will reclaim all space 
used by the view.  Note that the amount of space a view requires is not 
that large.  Specifically, a view is the SQL query string, any query 
parameter data, the query plan, and some ownership information.


> 2. Is it advisable to run the repair tool right after
> the view deletion?

No.  I recommend that the repair tool is only used when the database is 
corrupt.  Either an index has become corrupt or the database does not 
start.  The repair tool should only be used by the database 
administrator and should not be a regularly scheduled event.


> Could a check for problems like this one be included
> into the repair tool? In Java you could for instance
> just delete the "clone" entries leaving the first
> entry intact while warning the user to check the data
> of that compromised VIEW.

The repair tool works by deleting all index information, sequentially 
accessing the table and finding all records, and then rebuilding the 
indexes.  The reason duplicate entries can occur when running the repair 
tool is because when a record is updated there is a state when the 
unmodified and modified records both appear in the table file.  If the 
repair tool discovers a partially updated record like this it includes 
both the records because it has no means to know which is the most 
current record.

I also think it would be useful if the repair tool notified the user of 
cloned records and I'll see about adding this if I can.


> Now to your questions:
> 
> 1. The SQL command sequence is essentially
>       DROP VIEW vWlisa0
>       CREATE VIEW vWlisa0
>       SELECT .. FROM permanent_table, vWlisa0 WHERE ..
>       DROP VIEW vWlisa
>    The view name includes the name under which the
> user has logged into the client program. The idea was
> that any number of users can run the prog
> simultaneously but that the client program would
> prevent it's user from running this code sequence
> concurrently. This concept was compromised when
> multiple instances of the client prog were run on
> different PCs using the same username. My guess is
> that to "simultaneous" requests from different clients
> (using separate connections) to CREATE VIEW might have
> caused the problem.

I tested this scenario on 1.0.2 and 1.0.3 and I was unable to create 
duplicate views.  I created a table then created two connections and 
turned auto commit off on each connection.  I then created a view with 
the same name in each connection which succeeded.  Committed the first 
connection also succeeded but committing the second connection caused a 
transaction conflict - namespace conflict.


> 2. The problem occurred and was detected before
> running the repair tool. Only after I saw duplicate
> entries in the SYSINFO table did I run the repair
> tool.

If you have any other information you can send me that you think might 
be causing this to happen I would appreciate it.  I'll dig into the code 
and see if I can find anything.

Toby.


>  --- Tobias Downer <[email protected]> wrote: 
> 
>>Hi,
>>
>>You can delete both the duplicate views from the
>>sUSRView system table 
>>with the following query;
>>
>>   DELETE FROM SYS_INFO.sUSRView
>>    WHERE "schema" = 'APP' AND "name" = 'vWlisa0'
>>
>>You should also delete the grant information for the
>>view from the 
>>sUSRGrant table with the following query;
>>
>>   DELETE FROM SYS_INFO.sUSRGrant
>>    WHERE "param" = 'APP.vWlisa0'
>>
>>Before modifying the system tables make sure you
>>backup the database. 
>>After you have made the changes, shutdown and
>>restart the database.
>>
>>Can you think of anything specific that may have
>>caused the duplicate 
>>view entries to occur?  Did you run the repair tool
>>before the error 
>>started?  In some cases the repair tool can cause
>>duplicate record 
>>entries to occur.
>>
>>Toby.



---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to [email protected]