SQLite Forum

How to distinguish fatal errors

How to distinguish fatal errors

(1) By anonymous on 2021-01-06 16:30:15 [link] [source]

If I'm not mistaken, there are some fatal errors which are not recoverable. e.g SQLITE_IOERR, SQLITE_FULL.

1- Is there a list of these error codes which are not recoverable?

2 - Also, regarding SQLITE_FULL :
  a) When disk is full, it returns SQLITE_FULL.
  b) When 'max_page_count' is set and it hits this limit, it returns SQLITE_FULL.
  c) When 'temp_store' is full, it returns SQLITE_FULL.

Case 'b' and case 'c' look recoverable to me, so it should be safe to continue (?). If, so how do I distinguish 'b' and 'c' from case 'a' ?

(2) By Keith Medcalf (kmedcalf) on 2021-01-06 17:50:49 in reply to 1 [link] [source]

You know that you created the situation because you created it.

Seems pretty straightforward to me.

(3) By anonymous on 2021-01-06 18:11:32 in reply to 2 [link] [source]

What do you mean? I don't know if it failed because 'temp_store' was not big enough or disk is full or max_page_count limit caused that.

I execute a query, it may fail with one of the scenarios from a, b, or c up above. I need to distinguish between them to continue or abort. 'temp_store' SQLITE_FULL error should be recoverable I think, otherwise, you run a single statement which fails because of not enough space in temp_store, then should application abort?

(4) By Larry Brasfield (LarryBrasfield) on 2021-01-06 18:55:50 in reply to 1 [source]

It would be a bug if any of the SQLITE_FULL errors were not "recoverable" in the sense that the database continues to have the ACID attributes. Clearing the underlying resource shortfalls should allow the database to be used per its basic API. Not clearing them might lead to success (depending on other system activity) or the same "recoverable" error.

A separate question is how to distinguish among the error's causes. Most operating systems provide a way to discover free space on filesystems, which should allow you to distinguish between cause b and the others. And cause b can be ascertained by using the max_page_count pragma and page_count pragma judiciously. (This latter approach will need to take into account how many pages your failed insert or update was going to use.)

(5) By Keith Medcalf (kmedcalf) on 2021-01-06 20:45:47 in reply to 3 [link] [source]

If you do not know the answer to that question then you are a pretty crap poor programmer. So if you are executing a direct insert or update and it fails with SQLITE_FULL then the database is full EITHER because the disk is full OR because you as the programmer set an artificial limit.

If you are executing a pure SELECT and you get SQLITE_FULL it likely means the temporary store is full (since you could not possibly be filling up anything else).

So it seems pretty clear that YOU, as the person having design authority for the program which is receiving the error indication, must know exactly what the error is referring to.

If you don't, then that is your own fault and you should take up knitting rather than computer programming to save the rest of us what will surely be the bug ridden results of your efforts.

(6) By anonymous on 2021-01-06 21:19:24 in reply to 4 [link] [source]


According to discussion here, it's not recoverable. This is why I ask a reliable way to handle this issue.

Discovering free space on filesystems is not reliable unfortunately as it may change any moment, e.g another process deletes something, or sqlite tries to allocate 2 mb and fails, then I check free space, it says there is 1 mb, so how can I decide if disk is full then?

Thanks, for max_page_count pragma and page_count pragma suggestions, yes, it looks like the best approach for case b.

So, as a result, I was expecting there is a way to get that return code somehow to decide how to proceed.

(7) By Larry Brasfield (LarryBrasfield) on 2021-01-06 22:35:44 in reply to 6 [link] [source]

For "filesystem nearly full" situations, where there are potentially other processes consuming and freeing space on the underlying device(s), you will have the same uncertainty regarding subsequent retries whether or not you are able to get an error code more specific than SQLITE_FULL. Using the OS calls to determine available free space is not going to change that.

In the thread you cite, (and your original post in this thread, I suppose), the term "recoverable" is used differently, and more restrictively, than I interpreted it. Of course there is going to be no recovery if that means succeeding with the same operation where nothing else changes.

Considering the disk full case(s):

As was mentioned in that other thread, recovery will normally require freeing some disk space, something that your program is not likely able to do in a desirable manner. It will need to give up or pause while some more knowledgeable person or program deals with and perhaps alleviates the problem.

Considering the page count limit reached case:

I think that if you have set the page count limit lower than its maximum, with the intention of using that as trip-wire to do some sort of cleanup likely to reduce used page count, then you need not worry about precisely how much larger the page count can get relative to need. Recovery will be to do whatever cleanup of log data, materialized views, caches, and etc. that will get the page count back down to a "usable" level. Even if this is done when additional pages were low and that was not the direct cause of the SQLITE_FULL problem, it will free up disk space also which has a good chance of alleviating the error.

I am somewhat puzzled by your problem at this point. I think some real (or imagined likely) use cases would bring this discussion to a more concrete basis. Your phrase, "reliable way to handle this issue", leads me to wonder what "reliable" can possibly mean in this context. It begs for a clearer definition.

(8) By anonymous on 2021-01-06 23:23:49 in reply to 7 [link] [source]

Sorry, let me explain it another way.

I'm not talking about closing sqlite, re opening and verifying it's not corrupt.

In the other thread I posted, it says SQLITE_FULL is reported once. It suggests aborting the app because you can't progress anymore or run another query and it says next insert has failed silently. This is okay, I can close() sqlite and quit on that "fatal" case.

There are other cases for SQLITE_FULL, so I'd like to continue to operate for these cases if possible.

Pseudo code for my app :

// assume endless loop
while (query = next_query()) {
  rc = sqlite_exec(query);
    if (rc == SQLITE_FULL) {
      abort("disk is full");


What I'm trying to achieve is : 

// assume endless loop
while (query = next_query()) {
    rc = sqlite_exec(query);
    if (rc == SQLITE_FULL) {
        if (temp_store_was_full() or page_limit_reached()) {
            // Assuming it's safe to continue in these cases. (?)
            // E.g select tried to use excessive amount of 'temp_store'
            // so, it's okay to fail this query and continue with the next one,
            // I don't care if the next one will fail as well.
        abort("disk is full");


for page_limit_reached() :

I can do what you suggested, check page_count and max_page_count pragma, hopefully these pragmas will work correctly. (Silent fail of insert and comment about API being not dependable sounds scary).

(9) By Larry Brasfield (LarryBrasfield) on 2021-01-07 03:54:33 in reply to 8 [link] [source]

At the default page size of 2^12 bytes and default page count limit of 2^30 bytes, a database has to reach 2^42 bytes (over 4 Terabytes) to hit your page limit. Yours is a very unusual application if that limit is a legitimate concern. And both of those defaults can be increased.

Your use case, which I can only guess at from your pseudo-code, is most unusual. If the database does get too big for its self-imposed limit, any portion of the query sequence which includes DML, (which I presume it can although your code provides for no parameter substitution), or even DDL, can just fail, doing nothing except to note the failure. I would never have guessed this. And given that such failure is to be accepted, I am still left wondering what "reliably" means.

Your case where the temp store reaches a full condition is strange as well. That is either like a plain "disk is full" (if the temp store is on the same filesystem as the database) or it is something you might alleviate for some types of temp store backing devices. Yet you want it treated like the improbable page limited case, with submitted SQL statements having no effect on the database.

I think you have presented a question of "How do I make my preconceived solution work?" rather than "How do I solve this problem: ...?" My imagination is too limited to devise an actual, real world use case for which your pseudo-code is a solution. For that reason, I feel unqualified to offer advice or suggestions.