Incremental backup restore failure due to invalid view ASA 12

[JimDiaz]

SQL Anywhere Network Server Version 12.0.1.4104

This is not actually a question, just documentation of an issue we have found.

I was restoring from a incremental backup log file in which some DDL operations were performed. These operation were passed through using SQL Remote to a full subscription which runs under ASA 9.0.2.3951.

The DDL operations were executed on the production databases both consolidated and remote without error.

When the log files were applied via the standard restore command to the 12.0.1 and 9.0.2 offsite backup files the 9.0.2 restored without issue, the 12.0.1 failed reporting

... recovery 73% complete


Performance warning: View "V_FB_FeedbackItems" in database "Redacted Database Name" was invalidated due to DDL operations on one of its referenced objects
 ERROR  Assertion failed: 100904 (12.0.1.4104)[Redacted Database Name]
Failed to redo a database operation (id=4, page_no=0x661, offset=0xec8) - Error: Cannot use view 'V_FB_FeedbackItems' because it 
    ... recovery 78% complete
<<

This also created a Internal Error report which I have sent to SAP using the "Send Error Report" option, always wondered what happened to these...

I'm not entirely sure why I'm reporting this since most users no longer have access to the EBF's but maybe someone else can benefit from this information.

This is I believe the second issue I have reported with 12.0.1 recovery, we have had others. I believe these issues as well as the permissions issues we see ( other posts ) in 12.0.1 are reason enough to keep the EBF's available.

As a user since the Watcom days and someone who loves the ASA or SQL Anywhere product I am sorry to see the direction taken as of late. I am considering alternatives which I never thought I would. Is anyone interested in developing an open source message based replication alternative based on an open source database? I am.

Thanks Jim Diaz Emprise Corporation

[Former Member]

If your "Send Error Report" succeeded a minidump would be sent to the SAP system set up to capture that. If successful a submission number should be presented and the issue can be investigate starting with that number. You can list off your submissions by running dbsupport -ls and that list can provide a starting point for this as well. If you do not get a submission list then the minidump is still on your system (with a file name that include date and time info) and that can be provided to support.

This particular situation should be rare but would seem to be possible. The DDL in the recovery is causing the view identified to be marked invalid. This is related to a new feature added to version 10; namely "Support for view dependencies"1.

The next part (the reason for the assertion) would seem most likely to be related to some trigger action that (eventually) involves that view; possible the trigger definition itself or a procedure or function that references it.

You may want to investigate if the partially recovered database ends up with a truly broken view. Or just an invalidated one. That information will help to identify if there is a weakness that needs looking into or not.

Such passthrough operations could cause such side effects. One preventative measure would be to drop or correct any views that would break due to such DDL operations. Another would be to avoid the use of view in trigger actions.

HTH

[JimDiaz]

Thanks. Reviewing the database indicates the view was invalid. I'll take a look for a trigger dependency. I guess I don't understand why an assertion error would occur since it didn't occur when the operations were performed on the live consolidated SQL Anywhere 12.0.1 database. Shouldn't the assertion error have occurred there as well? This was not a passthrough only.

Thanks,

Jim