“It’s the errors, stupid…”

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English version:

This post’s title is an allusion to “It’s the economy, stupid” phrase that become popular in one of the USA presidential campaigns. It seems appropriate for today, and for the short problem description I’m going to describe.
Today, while working on a customer site, I was confronted with the following scenario:
A developer complained that two programs working against a non-logged database (yes, some customers still use them…) raised errors (-243/111) while DELETEing an overlapped result set. The customer DBAs replied the same as I probably would: “That can happen even on a non-logged database. Please use some sort of “SET LOCK MODE TO WAIT…’ “
But the program was already using it… So… why does it happen?!

Well… we all know that there are some differences between logged and non-logged databases, but a complete list is hard to find. One is that we cannot change the isolation level in a non-logged database. It raises an error stating that the only mode allowed is DIRTY READ. But it does accept the SET LOCK MODE WAIT statement, so we would expect it to enforce it…
In short, what was I missing, apart from a possible, but improbable bug? The answer is, I’m missing a careful analysis of the error codes! And I should be ashamed, because I have recommended many times that people MUST always look carefully into the errors, before trying to create explanations for problems that they haven’t qualified. Precisely what I was doing… So let’s see some facts:

  1. The instruction causing he error was something like:
    DELETE FROM some_table WHERE indexed_field = VALUE
    And the query plan used the index on the column
  2. Error -243 reads:
    -243    Could not position within a table table-name.

    The database server cannot set the file position to a particular row
    within the file that represents a table. Check the accompanying ISAM
    error code for more information. A hardware error might have occurred,
    or the table or index might have been corrupted (truncated). Unless the
    ISAM error code or an operating-system message points to another cause,
    run the oncheck utility (secheck with IBM Informix SE or tbcheck with
    IBM Informix OnLine) to check and repair table and index.

  3. Error -111 reads:
    -111    ISAM error: no record found.

    The ISAM processor cannot locate the requested record. For C-ISAM
    programs, no record was found with the requested index value or record
    number, depending on the retrieval mode in use. Make sure that the
    correct index is in use. For SQL products, see the SQL error message or
    return code. Probably no row was found for thi…

Auteur : noreply@blogger.com (Fernando Nunes)

No comments yet.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.