Re: [dba-users] Bad Basic code causes Runtime Error

[Andrew Jensen]

Frank Schönheit - Sun Microsystems Germany wrote: Hi Andrew, [inconsistent data] The situation with the data is more of a problem however. I don't want to beat a dead horse here, and I would be stupid to run this kind of a routine against a live database, for the fist time. But the crash recovery needs to be consistent. In my temp table I had two records. I watched both be deleted. One was recovered. The one that was lost, is really lost. The records where inserted into a second table. Both where lost. A table had a columns value updated twice, it recovered this changed value. Sigh. Yes, we should address our recovery inconsistencies, unfortunately, this is not a trivial problem. Well, this is not a particularly good example to use for the state of the error recovery routines. First off I was close to adding this issue with a summary line of 'Basic should not allow one to shoot ones self in the head', as this is what it does in essence. Moreso however, the next step is to have this code run in an isolated connection, as it is actually implementing a business level transaction that must insert to one table, update a second and finally delete from a third - all three steps making up one transaction. The fact that this recovery state was reflected with autocommit turned on can give a very false impression. ( I should note also, that when I re-ran the code against the 2.0.2 RC4 release code yesterday the recovered database was indeed different - a cursory review shows that both temporary records where recovered, both inserts to the permanent table appear to have been backed out , I have not done the calculation to see if the inventory amount was corrected properly however..) Sometime this weekend I will turn off the servers on my PC and run a corrected version of the code, with explicit commit/rollback functions in place and try to test the resiliency by crashing the OS while it runs. If what appears to be the case with the 2.0.2 code base proves true, then a hardy 'ata boy' is in order to you and your team. [Heck you guys deserve one any way] I will send a report of how this goes as a response to this email after I run the tests. Drew