Re: Informix logs read-only queries? can't handle 'not-exists'?

>From: uunet!thylacine.cs.wisc.edu!rchoi (Ron Choi) >Subject: Informix logs read-only queries? can't handle 'not-exists'? >Date: 11 Nov 92 18:41:42 GMT >X-Informix-List-Id: <news.2114> >i'm running Online 5.0 and seemed to have discovered a bug (or bugs). >i ran the following query overnight: >select count(*) >from relA >where (relA.x10A = 0 or relA.x10A = 5) and not exists ( > select relB.evenAcct > from relB > where (relB.x100B = 0 or relB.x100B = 49) and> relB.evenAcct = relA.acct > ); >and got hundreds of "Logical Log Files are Full -- Backup is Needed". This is a correct message: if your logical logs are full, you need a backup, and the system (meaning OnLine) is not going to be able to do anything until you arrange for the logs to be backed up. >the worst thing is that this corrupted the database. This shouldn't have happened. What form did the corruption take? How did you do the backup of the logs? How did you terminate the query process? And a whole lot of other questions need to be answered? >first of all, the above query is read-only, so it should not create log >records (unless informix is doing something weird and supporting cascade >aborts) Informix-OnLine has to log any disc space which is allocated, even for temporary tables (and implicit temporary tables). This "not exists" query is probably using a temporary table, which requires logging. Have you looked at the output of SET EXPLAIN to see what it says? Without knowing too much about your query, would this alternative formulation (which avoids a correlated sub-select) also work? select count(*) from relA where (relA.x10A = 0 or relA.x10A = 5) and relA.acct not in ( select relB.evenAcct from relB where relB.x100B = 0 or relB.x100B = 49 ); I suspect (though I have no proof) that this would work faster. >secondly, the database had logging turned off (with 'tbtape -N >databasename'), but it still logs anyway! *** OnLine always uses its logical logs. *** Note that even if the database itself has no logging, the logical logs are filled with information about things like allocating and freeing space, and therefore must be maintained (meaning backed up) correctly. The simplest way of doing that is to arrange for the logs to be backed up to /dev/null. This is recognised by OnLine as a special case, and the logs are marked as freed as required. Before you decide to do this, you should consider what it means for recovery -- you will only be able to recover to the state at the start of the last archive. Alternatively, you need to ensure that your logical logs are backed up to a tape device, prbably using tbtape -c for your overnight run. >and a query sure shouldn't corrupt the database. Granted. >has anyone seen this before? i can reproduce it; i ran this twice already >with the same result. and yes, i was the only person accessing the database >that night. How much logical log did you have configured? How big are the tables? What is the output of SET EXPLAIN? Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>