Documentation Source Text

      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}
chng {2013-??-?? (3.7.17)} {
<li>Add support for [memory-mapped I/O].


<li>Add the [sqlite3_strglob()] convenience interface.
<li>Report rollback recovery in the [error log] as SQLITE_NOTICE_RECOVER_ROLLBACK.
    Change the error log code for WAL recover from 
    SQLITE_OK to SQLITE_NOTICE_RECOVER_WAL.
<li>Report the risky uses of [unlinked database files] and 
   [database filename aliasing] as SQLITE_WARNING messages in the [error log].
}

chng {2012-04-12 (3.7.16.2)} {
<li>Fix a bug (present since version 3.7.13) that could result in database corruption
    on windows if two or more processes try to access the same database file at the
    same time and immediately after third process crashed in the middle of committing
    to that same file.  See ticket 

<title>The Error And Warning Log</title>
<tcl>hd_keywords {errlog} {error log}</tcl>

<h1 align="center">The Error And Warning Log</h1>

<p>SQLite can be configured to invoke a callback function containing
an error code and a terse error message whenever anomalies occur.
The mechanism is very helpful in tracking obscure problems that
occur rarely and in the field.  Application developers are encouraged
to take advantage of the error logging facility of SQLite in their
products, as it presents hardly any overhead at all, but can be a
huge aid for debugging.</p>

<h2>Setting Up The Error Logging Callback</h2>

<p>There can only be a single error logging callback per process.
The error logging callback is registered at start-time using C-code
similar to the following:

<blockquote><pre>
[sqlite3_config]([SQLITE_CONFIG_LOG], errLogCallback, pData);
</pre></blockquote>

<p>The error logger callback function might look something like this:</p>

<blockquote><pre>
void errorLogCallback(void *pArg, int iErrCode, const char *zMsg){
  fprintf(stderr, "(%d) %s\n", iErrCode, zMsg);
}
</pre></blockquote>

<p>The example above illustrates the signature of the error logger callback,
at least.  In an embedded application, one usually does not print
messages on stderr.  Instead, one might store the messages in a
preallocated circular buffer where they can be accessed when diagnostic
information is needed during debugging.  Or perhaps the messages can be
sent to [http://en.wikipedia.org/wiki/Syslog | Syslog].  Somehow, the
messages need to be stored where they are accessible to developers debugging
issues, not displayed to end users.</p>

<p>Do not misunderstand: There is nothing technically wrong with displaying 
the error logger messages to end users.  The messages do not contain
sensitive or private information that must be protected from unauthorized
viewing.  Rather the messages are technical in nature and are not useful
or meaningful to the typical end user.  These messages are intended for
database geeks.  Display them accordingly.</p>

<h2>Interface Details</h2>

<p>The third argument to the [sqlite3_config]([SQLITE_CONFIG_LOG],...) call
(the "pData" argument in the example above) is a pointer to arbitrary
data.  SQLite passes this pointer through to the first argument of the
error logger callback.  The pointer can be used to pass application-specific 
setup or state information, if desired.  Or it can simply be a NULL 
pointer which is ignored by the callback.</p>

<p>The second argument to the error logger callback is an integer
[extended error code].  The third argument to the error logger is the
text of the error message.  The error message text is stored in a fixed-length
stack buffer in the calling function and so will only be valid for the
duration of the error logger callback function.  The error logger should
make a copy of this message into persistent storage if retention of the
message is needed.</p>

<p>The error logger callback should be treated like a signal handler.
The application should do something with the error, quickly, then return
as soon as possible.  No other SQLite APIs should be invoked, directly or
indirectly, from the error logger.  In particular, the error logger callback
is invoked when a memory allocation fails, so it is generally a bad idea
to try to allocate memory inside the error logger.</p>

<p>Applications can use the [sqlite3_log(E,F,..)] API to send new messages
to the log, if desired.</p>

<h2>Variety of Error Messages</h2>

<p>The error messages that might be sent to the error logger and their
exact format is subject to changes from one release to the next.  So
applications should not depend on any particular error message text or
error codes.  Things do not change capriciously, but they do sometimes
changes.</p>

<p>The following is a partial list of the kinds of messages that might
appear in the error logger callback.</p>

<ul>
<li><p>
Any time there is an error either compiling an SQL statement 
(using [sqlite3_prepare_v2()] or its siblings) or running an SQL
statement (using [sqlite3_step()]) that error is logged.
</p>

<li><p>
When a schema change occurs that requires a prepared statement to be reparsed
and reprepared, that event is logged with the error code SQLITE_SCHEMA.
The reparse and reprepare is normally automatic (assuming that
[sqlite3_prepare_v2()] has been used to prepared the statements originally,
which is recommended) and so these logging events are normally the only
way to know that reprepares are taking place.</p>

<li><p>
SQLITE_NOTICE messages are logged whenever a database has to be recovered
because the previous writer crashed without completing its transaction.
The error code is SQLITE_NOTICE_RECOVER_ROLLBACK when recovering a
[rollback journal] and SQLITE_NOTICE_RECOVER_WAL when recovering a 
[write-ahead log].
</p>

<li><p>
SQLITE_WARNING messages are logged when database files are renamed or
aliased in ways that can lead to database corruption.
(See [unlink corruption | 1] and [database filename aliasing | 2] for
additional information.)
</p>

<li><p>
Out of memory (OOM) errors conditions generate error logging events
with the SQLITE_NOMEM error code and a message that says how many bytes
of memory were requested by the failed allocation.
</p>

<li><p>I/O errors in the OS-interface generate error logging events.
The message to these events gives the line number in the source code where
the error originated and the filename associated with the event when
there is a corresponding file. </p>

<li><p>When database corruption is detected, an SQLITE_CORRUPT error
logger callback is invoked.  As with I/O errors, the error message text
contains the line number in the original source code where the error
was first detected.</p>

<li><p>
An error logger callbacked is invoked on SQLITE_MISUSE errors.
</ul>

<p>SQLite strives to keep error logger traffic low and only send messages
to the error logger when there really is something wrong.  Applications
might take this further and deliberately ignore certain classes of error
messages that they do not care about.  For example, an application that
makes frequent database schema changes might want to ignore all
SQLITE_SCHEMA errors.</p>

<h2>Summary</h2>

<p>The use of the error logger callback is highly recommended.
The debugging information that the error logger provides has proven
very useful in tracking down obscure problems that occurs with applications
after they get into the field.  The error logger callback has also 
proven useful in catching errors occasional errors that the application
misses because of inconsistent checking of API return codes.
Developers are encouraged to implement an error logger callback early
in the development cycle in order to spot unexpected behavior quickly,
and to leave the error logger callback turned on through deployment.
If the error logger never finds a problem, then no harm is done.  
But failure to set up an appropriate error logger might leave the
developres wishing they had after mysterious and unreproducible
bug reports start coming back from the field.</p>

database at the same time, then database corruption might result.</p>

<tcl>hd_fragment posix_close_bug</tcl>
<h3>2.2 Posix advisory locks canceled by a separate thread doing close()</h3>

<p>The default locking mechanism used by SQLite on unix platforms is
POSIX advisory locking.  Unfortunately, POSIX advisory locking has design
quirks that make it prone to misuse and failure. In particular, any
thread in the same process with a file descriptor that is holding a POSIX
advisory lock can override that lock using a different file descriptor.
One particularly pernicious problem is that the <tt>close()</tt> system
call will cancel all POSIX advisory locks on the same file for all
threads and all file descriptors in the process.</p>

<p>So, for example, suppose a multi-thread process has

<p>It is important that all connections to the same database file use 
the same locking protocol.
If one application is using POSIX advisory locks and another application
is using dot-file locking, then the two applications will not see each
others locks and will not be able to coordinate database access, possibly
leading to database corruption.</p>

<tcl>hd_fragment unlink {unlink corruption} {unlinked database files}</tcl>
<h3>2.4 Unlinking or renaming a database file while in use</h3>

<p>If two processes have open connections to the same database file and
one process closes its connection, unlinks the file, then creates a new
database file in its place with the same name and reopens the new file,
then the two processes will be talking to different database files with 
the same name.  (Note that this is only possible on Posix and Posix-like
systems that permit a file to be unlinked while it is still open for
reading and writing.  Windows does not allow this to occur.)
Since rollback journals and WAL files are based on the name of the database
file, the two different database files will share the same rollback
journal or WAL file.  A rollback or recovery for one of the databases
might use content from the other database, resulting in corruption.</p>

<p>A similar problem occurs if a database file is renamed while it is
opened and a new file is created with the old name.</p>

<p>Beginning with SQLite [version 3.7.17], the unix OS interface will
send SQLITE_WARNING messages to the [error log] if a database file is unlinked
while it is still in use.</p>

<tcl>hd_fragment alias {database filename aliasing}</tcl>
<h3>2.5 Multiple links to the same file</h3>

<p>If a single database file has multiple links (either hard or soft links)
then that is just another way of saying that the file has multiple names.
If two or more processes open the database using different names, then
they will use different rollback journals and WAL files.  That means that
if one process crashes, the other process will be unable to recover the
transaction in progress because it will be looking in the wrong place
for the appropriate journal.</p>

<p>Beginning with SQLite [version 3.7.17], the unix OS interface will
send SQLITE_WARNING messages to the [error log] if a database file has 
multiple hard links.  As of this writing, SQLite still does not yet detect 
and warn about the use of database files through soft links.</p>


<h2>3.0 Failure to sync</h2>

<p>In order to guarantee that database files are always consistent, SQLite
will occasionally ask the operating system to flush all pending writes to
persistent storage then wait for that flush to complete.  This is 
accomplished using the <tt>fsync()</tt> system call under unix and

historical bugs in SQLite (now fixed) that could cause database corruption.
And there may be yet a few more that remain undiscovered.  Because of the
extensive testing and widespread use of SQLite, bugs that result in
database corruption tend to be very obscure.  The likelihood
of an application encountering an SQLite bug is small.  To illustrate this,
an account is given below 
of all database-corruption bugs found in SQLite during the
two-year period from 2009-04-01 to 2013-04-15.
This account should give the reader an intuitive sense of the
kinds of bugs in SQLite that manage to slip through testing procedures
and make it into a release.</p>


<h3>7.1 False corruption reports due to database shrinkage</h3>

<p>If a database were originally created using SQLite version 3.7.0,
then written by SQLite version 3.6.23.1 such that the size of the database
file increased, then written again by SQLite version 3.7.0, the database
file could go corrupt.</p>

<p>This problem was fixed on 2010-08-04 for SQLite version 3.7.1.</p>

<h3>7.6 Race condition in recovery on windows system.</h3>

<p>SQLite version 3.7.16.2 fixes a subtle race condition in the locking
logic on Windows systems.  When a database file is in need
of recovery because the previous process writing to it crashed in the
middle of a transaction and two or more processes try to open the 
that database at the same time, then the race condition might cause
one of those processes to get a false indication that the recovery 
has already completed, allowing that process to continue using the
database file without running recovery first.  If that process writes
to the file, then the file might go corrupt.  This race condition
had apparently existing in all prior versions of SQLite for Windows going
back to 2004.  But the race was very tight.  Practically speaking, you
need a fast multi-core machine in which you launch two processes to run
recovery at the same moment on two separate cores.  This defect was
on windows systems only and did not effect the posix OS interface.</p>

<title>Memory-Mapped I/O</title>
<tcl>hd_keywords {mmap} {memory-mapped I/O}</tcl>

<h1 align="center">Memory-Mapped I/O</h1>

<p>The default mechanism by which SQLite accesses and updates database disk
files is the xRead() and xWrite() methods of the
[sqlite3_io_methods] VFS object.  These methods are typically implemented as
"read()" and "write()" system calls which cause the operating system
to copy disk content between the kernel buffer cache and user space.</p>

<p>Beginning with [version 3.7.17], SQLite now also has the option of 
accessing disk content directly using memory-mapped I/O and the new
xFetch() and xUnfetch() methods on [sqlite3_io_methods].</p>

<p>There are advantages and disadvantages to using memory-mapped I/O:
Advantages include:</p>

<ol>
<li><p>Many operations, especially I/O intensive operations, can be much
    faster since content does need to be copied between kernel space
    and user space.  In some cases, performance can nearly double.</p>

<li><p>The SQLite library may need less RAM since it is shares pages with
    the operating-system page cache and does not always need its own copy of
    working pages.</p>
</ol>

<p>But there are also disadvantages:</p>

<ol>
<li><p>A stray pointer or buffer overflow in the application program
    might change the content of mapped memory, potentially corrupting the
    database file.  Bugs in application program that overwrite memory
    assigned to the SQLite library could, in theory, also cause corruption
    in read/write mode, but that would require that the overwrite
    be followed by a call to xWrite() and in practice the buggy application
    usually errors out or crashes before that point.  With memory-mapped
    I/O, the database corruption occurs immediately and is thus more of a
    risk.</p>

<li><p>An I/O error on a memory-mapped file cannot be caught and dealt with by
    SQLite.  Instead, the I/O error causes a signal which, if not caught
    by the application, results in a program crash.</p>

<li><p>The operating system must have a unified buffer cache in order for
    the memory-mapped I/O extension to work correctly, especially in
    situations where two processes are accessing the same database
    file and one process is using memory-mapped I/O while the other
    is not.  Not all operating systems have a unified buffer cache.
    In some operating systems that claim to have a unified buffer cache,
    the implementation is buggy and can lead to corrupt databases.</p>

<li><p>Performance does not always increase with memory-mapped I/O.  In fact,
    it is possible to construct test cases where performance is reduced
    by the use of memory-mapped I/O, though this is hard to do.</p>
</ol>

<p>Because of the potential disadvantages, memory-mapped I/O is turned
off by default.  To active memory-mapped I/O, use the [mmap_size pragma]
and set the mmap_size to some large number, usually 256MB or larger, depending
on how much address space your application can spare.  The rest is
automatic.  The [PRAGMA mmap_size] statement will be a silent no-op on
systems that do not support memory-mapped I/O.</p>

<h2>How Memory-Mapped I/O Works</h2>

<p>To read a page of database content using the legacy xRead() method,
SQLite first allocates a page-size chunk of heap memory then invokes
the xRead() method which causes the database page content to be copied
into the newly allocated heap memory.  This involves (at a minimum)
a copy of the entire page.</p>

<p>But if SQLite wants to access a page of the databse file and
memory mapped I/O is enabled, it first calls the xFetch() method.
The xFetch() method asks the operating system to return a pointer to
the requested page, if possible.  If the requested page has been or
can be mapped into the applications address space, then xFetch returns
a pointer to that page for SQLite to use without having to copy anything.
Skipping the copy step is what makes memory mapped I/O faster.</p>

<p>SQLite does not assume that the xFetch() method will work.  If
a call to xFetch() returns a NULL pointer (indicating that the requested
pages is not currently mapped into the applications address space) then
SQLite silently falls back to using xRead().  An error is only reported
if xRead() also fails.</p>

<p>When updating the database file, SQLite always makes a copy of the
page content into heap memory before modifying the page.  This is necessary
since the changes are not suppose to be visible to other processes until
after the transaction commits and so the changes must occur in private space.
After all needed changes are completed, xWrite() is used to move the content
back into the database file.  The current xWrite() implementions for both
unix and windows check to see if section of the file being written is 
mapped into the applications address space, and if it is the write operation
is implemented using memcpy() rather than invoking a "write()" system call,
but that is just an implementation detail.  A memory copy occurs either way.
So the use of memory mapped I/O does not significantly change the performance
of database changes.  Memory mapped I/O is mostly a benefit for queries.</p>

<h2>Configuring Memory-Mapped I/O</h2>

<p>The "mmap_size" is the maximum number of bytes of the database file that
SQLite will try to map into the process address space at one time.  The
"mmap_size" applies separately to each database file, so the total amount
of process address space that could potentially be used is the "mmap_size"
times the number of open database files.</p>

<p>If mmap_size is set to N then all current implementation map the first
N bytes of the database file and use legacy xRead() calls for any content
beyond N bytes.  If the database file is smaller then N, then the entire
file is mapped.  In the future, new OS interfaces could, in theory, map
regions of the file other than the first N bytes, but no such OS interface
implementation currently exists.</p>

<p>The mmap_size is set separately for each database file using the
"[PRAGMA mmap_size]" statement.  The usual default mmap_size is zero,
meaning that memory mapped I/O is disabled by default.  However, the
default mmap_size can be increased either at compile-time using
the [SQLITE_DEFAULT_MMAP_SIZE] macro or at start-time using the
[sqlite3_config]([SQLITE_CONFIG_MMAP_SIZE],...) interface.</p>

<p>SQLite also maintains a hard upper bound on the mmap_size.  Attempts
to increase the mmap_size above this hard upper bound (using
[PRAGMA mmap_size] will automatically reduce the mmap_size to the hard
upper bound.  If the hard upper bound is zero, then memory mapped I/O
is impossible.  The hard upper bound can be set at compile-time using
the [SQLITE_MAX_MMAP_SIZE] macro.  If [SQLITE_MAX_MMAP_SIZE] is set to
zero, then the code used to implement memory mapped I/O is omitted from
the build.  The hard upper bound is automatically set to zero on certain
platforms (ex: OpenBSD) where memory mapped I/O does not work due to the
lack of a unified buffer cache.</p>

<p>If the hard upper bound on mmap_size is non-zero at compilation time,
it may still be reduced or zeroed at start-time using the
[sqlite3_config]([SQLITE_CONFIG_MMAP_SIZE],X,Y) interface.  The X and
Y parameters must both be 64-bit signed integers. The X parameter
is the default mmap_size of the process and the Y is the new hard upper bound.
The hard upper bound cannot be increased above its compile-time setting
using [SQLITE_CONFIG_MMAP_SIZE] but it can be reduced or zeroed.</p>