Re: [sqlite] random, infrequent disk I/O errors

[Greg Janée]

For the record, this problem was due to a locking problem between two  
processes trying to access the same SQLite database, in which a locked  
database was being mis-reported by SQLite as a disk I/O error.   
Furthermore, the problem went away when I upgraded to 3.7.15.2.


On Feb 27, 2013, at 9:01 AM, Greg Janee wrote:




From: sqlite-users-boun...@sqlite.org [sqlite-users- 
boun...@sqlite.org] on behalf of Patrik Nilsson [nipatriknils...@gmail.com 
]

Sent: Sunday, February 10, 2013 11:11 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] random, infrequent disk I/O errors

Hello,

This letter is a help getting you started to finding your error.

There are many SQLITE_IOERR-errors, for example SQLITE_IOERR_NOMEM  
which

means out of memory.

I have noticed in my application that I sometimes get out of memory  
when

calling g_string_new() after modifying the whole table, i.e. adding a
new column.

After thinking about what it is I compiled the sqlite-code with
SQLITE_ENABLE_MEMORY_MANAGEMENT and called sqlite3_release_memory()
after doing much change to the database. I haven't got that error  
since.


Even though this change is relatively recently I have made test
stresses, but as for now it seems to work.

"The sqlite3_release_memory() interface attempts to free N bytes of  
heap

memory by deallocating non-essential memory allocations held by the
database library."

Line 23566 in sqlite.c v3.7.15.1:
/*
** This routine translates a standard POSIX errno code into something
** useful to the clients of the sqlite3 functions.  Specifically, it  
is
** intended to translate a variety of "try again" errors into  
SQLITE_BUSY

** and a variety of "please close the file descriptor NOW" errors into
** SQLITE_IOERR
**
** Errors during initialization of locks, or file system support for  
locks,

** should handle ENOLCK, ENOTSUP, EOPNOTSUPP separately.
*/

Line 1021 in sqlite.c v3.7.15.1:
#define SQLITE_IOERR_READ  (SQLITE_IOERR | (1<<8))
#define SQLITE_IOERR_SHORT_READ(SQLITE_IOERR | (2<<8))
#define SQLITE_IOERR_WRITE (SQLITE_IOERR | (3<<8))
#define SQLITE_IOERR_FSYNC (SQLITE_IOERR | (4<<8))
#define SQLITE_IOERR_DIR_FSYNC (SQLITE_IOERR | (5<<8))
#define SQLITE_IOERR_TRUNCATE  (SQLITE_IOERR | (6<<8))
#define SQLITE_IOERR_FSTAT (SQLITE_IOERR | (7<<8))
#define SQLITE_IOERR_UNLOCK(SQLITE_IOERR | (8<<8))
#define SQLITE_IOERR_RDLOCK(SQLITE_IOERR | (9<<8))
#define SQLITE_IOERR_DELETE(SQLITE_IOERR | (10<<8))
#define SQLITE_IOERR_BLOCKED   (SQLITE_IOERR | (11<<8))
#define SQLITE_IOERR_NOMEM (SQLITE_IOERR | (12<<8))
#define SQLITE_IOERR_ACCESS(SQLITE_IOERR | (13<<8))
#define SQLITE_IOERR_CHECKRESERVEDLOCK (SQLITE_IOERR | (14<<8))
#define SQLITE_IOERR_LOCK  (SQLITE_IOERR | (15<<8))
#define SQLITE_IOERR_CLOSE (SQLITE_IOERR | (16<<8))
#define SQLITE_IOERR_DIR_CLOSE (SQLITE_IOERR | (17<<8))
#define SQLITE_IOERR_SHMOPEN   (SQLITE_IOERR | (18<<8))
#define SQLITE_IOERR_SHMSIZE   (SQLITE_IOERR | (19<<8))
#define SQLITE_IOERR_SHMLOCK   (SQLITE_IOERR | (20<<8))
#define SQLITE_IOERR_SHMMAP(SQLITE_IOERR | (21<<8))
#define SQLITE_IOERR_SEEK  (SQLITE_IOERR | (22<<8))
#define SQLITE_IOERR_DELETE_NOENT  (SQLITE_IOERR | (23<<8))

Best Regards,
Patrik


On 02/10/2013 05:28 PM, Greg Janée wrote:
Hello, I'm running a web service that uses SQLite that throws a  
disk I/O

exception every once in a while, meaning once every few weeks.

Details: SQLite 3.7.0.1, being called from an Apache/Django/Python
multi-threaded application running on Solaris 10.  The database  
file is

on a local filesystem, and is <200MB.  Disk space is not an issue on
that filesystem.

Everything appears fine, and then one transaction out of the blue  
gets a
disk I/O error when doing a "BEGIN IMMEDIATE".  The database passes  
an

integrity check.  These disk I/O errors don't seem to correlate with
anything.  When one happens, there isn't a lot of activity on the  
system

(individual transactions occurring once every few seconds, say, so
threading/locking/contention wouldn't seem to be an issue), and the
transactions immediately before and after the failed transaction
succeeded just fine.  If there is any correlation, they all seem to
happen in the middle of the night.  I've checked with my sysadmin,  
and

he can't think of anything (backups, virus scans, etc.) that might be
happening at that time.

I've searched for help on this topic and have come up with  
nothing.  It

should be clear from the above that directory permissions are not the
problem.

Any ideas?  U

Re: [sqlite] random, infrequent disk I/O errors

[Patrik Nilsson]

Hello,

This letter is a help getting you started to finding your error.

There are many SQLITE_IOERR-errors, for example SQLITE_IOERR_NOMEM which
means out of memory.

I have noticed in my application that I sometimes get out of memory when
calling g_string_new() after modifying the whole table, i.e. adding a
new column.

After thinking about what it is I compiled the sqlite-code with
SQLITE_ENABLE_MEMORY_MANAGEMENT and called sqlite3_release_memory()
after doing much change to the database. I haven't got that error since.

Even though this change is relatively recently I have made test
stresses, but as for now it seems to work.

"The sqlite3_release_memory() interface attempts to free N bytes of heap
memory by deallocating non-essential memory allocations held by the
database library."

Line 23566 in sqlite.c v3.7.15.1:
/*
** This routine translates a standard POSIX errno code into something
** useful to the clients of the sqlite3 functions.  Specifically, it is
** intended to translate a variety of "try again" errors into SQLITE_BUSY
** and a variety of "please close the file descriptor NOW" errors into
** SQLITE_IOERR
**
** Errors during initialization of locks, or file system support for locks,
** should handle ENOLCK, ENOTSUP, EOPNOTSUPP separately.
*/

Line 1021 in sqlite.c v3.7.15.1:
#define SQLITE_IOERR_READ  (SQLITE_IOERR | (1<<8))
#define SQLITE_IOERR_SHORT_READ(SQLITE_IOERR | (2<<8))
#define SQLITE_IOERR_WRITE (SQLITE_IOERR | (3<<8))
#define SQLITE_IOERR_FSYNC (SQLITE_IOERR | (4<<8))
#define SQLITE_IOERR_DIR_FSYNC (SQLITE_IOERR | (5<<8))
#define SQLITE_IOERR_TRUNCATE  (SQLITE_IOERR | (6<<8))
#define SQLITE_IOERR_FSTAT (SQLITE_IOERR | (7<<8))
#define SQLITE_IOERR_UNLOCK(SQLITE_IOERR | (8<<8))
#define SQLITE_IOERR_RDLOCK(SQLITE_IOERR | (9<<8))
#define SQLITE_IOERR_DELETE(SQLITE_IOERR | (10<<8))
#define SQLITE_IOERR_BLOCKED   (SQLITE_IOERR | (11<<8))
#define SQLITE_IOERR_NOMEM (SQLITE_IOERR | (12<<8))
#define SQLITE_IOERR_ACCESS(SQLITE_IOERR | (13<<8))
#define SQLITE_IOERR_CHECKRESERVEDLOCK (SQLITE_IOERR | (14<<8))
#define SQLITE_IOERR_LOCK  (SQLITE_IOERR | (15<<8))
#define SQLITE_IOERR_CLOSE (SQLITE_IOERR | (16<<8))
#define SQLITE_IOERR_DIR_CLOSE (SQLITE_IOERR | (17<<8))
#define SQLITE_IOERR_SHMOPEN   (SQLITE_IOERR | (18<<8))
#define SQLITE_IOERR_SHMSIZE   (SQLITE_IOERR | (19<<8))
#define SQLITE_IOERR_SHMLOCK   (SQLITE_IOERR | (20<<8))
#define SQLITE_IOERR_SHMMAP(SQLITE_IOERR | (21<<8))
#define SQLITE_IOERR_SEEK  (SQLITE_IOERR | (22<<8))
#define SQLITE_IOERR_DELETE_NOENT  (SQLITE_IOERR | (23<<8))

Best Regards,
Patrik


On 02/10/2013 05:28 PM, Greg Janée wrote:
> Hello, I'm running a web service that uses SQLite that throws a disk I/O
> exception every once in a while, meaning once every few weeks.
> 
> Details: SQLite 3.7.0.1, being called from an Apache/Django/Python
> multi-threaded application running on Solaris 10.  The database file is
> on a local filesystem, and is <200MB.  Disk space is not an issue on
> that filesystem.
> 
> Everything appears fine, and then one transaction out of the blue gets a
> disk I/O error when doing a "BEGIN IMMEDIATE".  The database passes an
> integrity check.  These disk I/O errors don't seem to correlate with
> anything.  When one happens, there isn't a lot of activity on the system
> (individual transactions occurring once every few seconds, say, so
> threading/locking/contention wouldn't seem to be an issue), and the
> transactions immediately before and after the failed transaction
> succeeded just fine.  If there is any correlation, they all seem to
> happen in the middle of the night.  I've checked with my sysadmin, and
> he can't think of anything (backups, virus scans, etc.) that might be
> happening at that time.
> 
> I've searched for help on this topic and have come up with nothing.  It
> should be clear from the above that directory permissions are not the
> problem.
> 
> Any ideas?  Unfortunately, the (standard) Python SQLite wrapper I'm
> using doesn't provide access to any more information (if there is any to
> be had).
> 
> Thanks in advance,
> -Greg
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] random, infrequent disk I/O errors

[Dominique Pellé]

Greg Janée wrote:

> Hello, I'm running a web service that uses SQLite that throws a disk I/O
> exception every once in a while, meaning once every few weeks.
...snip...
> Any ideas?  Unfortunately, the (standard) Python SQLite wrapper I'm using
> doesn't provide access to any more information (if there is any to be had).


I've never tried it, but doing a Google search
for "python errno" suggests that you can access
errno in Python:

http://stackoverflow.com/questions/661017/access-to-errno-from-python

Hopefully that can give better information about the error
(file system full, etc.)

Regards
Dominique
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] random, infrequent disk I/O errors

[Simon Slavin]

On 10 Feb 2013, at 4:28pm, Greg Janée  wrote:

> Any ideas?  Unfortunately, the (standard) Python SQLite wrapper I'm using 
> doesn't provide access to any more information (if there is any to be had).

That would make diagnosis difficult.  Please check to see whether you can 
enable extended result codes:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] random, infrequent disk I/O errors

[Greg Janée]

Hello, I'm running a web service that uses SQLite that throws a disk I/ 
O exception every once in a while, meaning once every few weeks.


Details: SQLite 3.7.0.1, being called from an Apache/Django/Python  
multi-threaded application running on Solaris 10.  The database file  
is on a local filesystem, and is <200MB.  Disk space is not an issue  
on that filesystem.


Everything appears fine, and then one transaction out of the blue gets  
a disk I/O error when doing a "BEGIN IMMEDIATE".  The database passes  
an integrity check.  These disk I/O errors don't seem to correlate  
with anything.  When one happens, there isn't a lot of activity on the  
system (individual transactions occurring once every few seconds, say,  
so threading/locking/contention wouldn't seem to be an issue), and the  
transactions immediately before and after the failed transaction  
succeeded just fine.  If there is any correlation, they all seem to  
happen in the middle of the night.  I've checked with my sysadmin, and  
he can't think of anything (backups, virus scans, etc.) that might be  
happening at that time.


I've searched for help on this topic and have come up with nothing.   
It should be clear from the above that directory permissions are not  
the problem.


Any ideas?  Unfortunately, the (standard) Python SQLite wrapper I'm  
using doesn't provide access to any more information (if there is any  
to be had).


Thanks in advance,
-Greg

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users