= PySqlite [pypi:pysqlite PySqlite] is a Python binding for the [http://www.sqlite.org SQLite] light-weight database engine, which is Trac's default DatabaseBackend. == Installation === The short version If you are using Python 2.5 and up, you already have a working version of pysqlite 2, bundled as `sqlite3`. If you are using an older version of Python or you would like to benefit from the latest version of pysqlite, grab Windows installer or the source .tar.gz from the official [https://pypi.python.org/pypi/pysqlite pysqlite site]. If you need to build from this .tar.gz, simply do after unpacking: {{{ python setup.py build_static install }}} This will fetch the latest SQLite version and bundle it within the extension. [[PageOutline(2-4,Contents,pullout)]] === The SQLite library Trac supports '''[http://www.sqlite.org/version3.html SQLite 3.x]'''. Pay attention to [http://www.sqlite.org/formatchng.html database format changes] when upgrading the SQLite library, see below for [wiki:PySqlite#UpgradingSQLite details]. Note that SQLite v2.x is no longer supported in Trac 0.12.x, use it at your own risk with earlier versions of Trac. The [http://www.sqlite.org/wal.html WAL mode] introduced with SQLite 3.7.0 also works great with Trac. ==== Downloading SQLite The latest stable version of SQLite can be obtained on the [http://www.sqlite.org/download.html SQLite download] page. However, if you install the pysqlite bindings from a package, it will usually come with the SQLite code bundled. If you build the pysqlite bindings by yourself, you also have the possibility to use the `build_static` mode, which will download the latest "amalgamation" code of SQLite and build it together with the extension code. ==== Building SQLite yourself '''Note:''' If you want to use Trac in a multi-threaded setup by using either TracModPython or TracStandalone, build a '''thread-safe version of SQLite''' by using the `--enable-threadsafe` configuration switch. If you use a non thread-safe library, which is unfortunately what you get by default on non-Windows platforms, you face the risk of persistent database locks, see #2170. === The Pysqlite2 bindings '''Note:''' PySQLite 1.0.x and SQLite2 are not supported. Detailed information about older releases: * Trac 0.12.x requires a PySqlite 2.x release: * version >= 2.0.5 preferred, * 2.0.3 has been reported to cause crashes on Windows (#2688) * version '''2.0.7''' required if using SQLite >= 3.3.3 * The ''2.1.3'' version appears to work well with Trac. This release branch features a new statement cache and a better handling of concurrent write operations. * The ''2.2.0'' version appears to work with Trac as well (I tested the 2.4 egg from initd on Windows, and on Linux I built it from source, linking with sqlite-3.3.4; so both are working perfectly) * The ''2.3.2'' version is the one which ships with Python 2.5, available there as the `sqlite3` package. Trac tries to use it if the `pysqlite2` package is installed. * You need the ''2.3.3'' version if you use Apache and `mod_cache`, see Pysqlite:#174. * The ''2.8.0'' version requires a "recent" SQLite version, at least 3.7.4, which is the first version to introduce the function `sqlite3_stmt_readonly`, which is now required. {{{#!div style="border: 1pt dotted; margin: 1em" **Version 2.5.2 - 2.5.4 Warning:** A known bug in pysqlite versions 2.5.2-4 prohibits upgrades of Trac databases from 0.11.x to 0.12. Please use versions 2.5.5 and newer or 2.5.1 and older. See #9434 for more detail. }}} {{{#!div style="border: 1pt dotted; margin: 1em" **Mac OS X Warning:** the Apple-supplied SQLite contains additional code to support file locking on network filesystems like AFP or SMB. This is not presently (3.3.6) in the mainline sources, so if you build your own SQLite from source it will not function correctly on such filesystems - typically it gives the error "{{{database is locked}}}". [http://alastairs-place.net/blog/2006/07/10/sqlite-and-mac/ A patch] used to be available, but you're better off using the Apple supplied version (presently 3.1.3). }}} ==== Downloading Pysqlite The source tarballs for the versions listed above are available from the [pypi:pysqlite pysqlite site]. ==== Building Pysqlite The recommended way to build the SQLite bindings is to do a `static_build`, which will download the latest sqlite3 amalgamation file, build and link it inside the Pysqlite module: {{{#!sh $ tar xvfz .tar.gz $ cd $ python setup.py build_static install }}} Your system may require the development headers. Without these you will get various GCC related errors when attempting to build: {{{#!sh $ apt-get install libsqlite3-dev }}} ===== Building on Windows [https://github.com/ghaering/pysqlite/issues/89 pysqlite issue 89] lists the trouble you'd get when trying to build PySqlite on Windows. Either follow the advice found there, or use the somewhat simpler steps described below. First you need to obtain the SQLite amalgamation source files. The simplest way to do this is to use the amalgamation .zip you'll find in http://www.sqlite.org/download.html, but that will only work if you want the latest released version of SQLite, which is the typical use case. If you need another version, you can get it from the SQLite source repository: 1. retrieve the code from [http://www.sqlite.org/getthecode.html the fossil repository] 2. prepare the amalgamation source, as explained in [http://www.sqlite.org/amalgamation.html#amalgbuild Building The Amalgamation From Canonical Source Code] At this point you have the `sqlite3.h` and `sqlite3.c` source files. 1. Extract the .tar.gz distribution of PySqlite, or clone the repository, to a `pysqlite` directory 2. In `pysqlite`, create a subdirectory named "internal" 3. Copy the amalgamation files `sqlite3.h` and `sqlite3.c` into that directory 4. Change directory to `pysqlite/internal`, build the amalgamation static library (assuming you have the shell set up correctly to use `cl` and `lib`): {{{#!shell cl -c sqlite3.c lib sqlite3.obj -out:sqlite3.lib }}} 5. Now go back to the `pysqlite` directory and build the standard way: `python setup.py install`: due to a bug explained in the issue 89, the "internal" directory is present in both the include path and the library path... Crude but it works ;-) == Upgrading SQLite === Upgrading SQLite from 2.x to 3.x It is not advised to use SQLite 2.x, because support for this version and the corresponding PySqlite 1.0.x bindings will be dropped in Trac version [milestone:0.12]. If you happen to use that version, you will need to upgrade. The database formats used by SQLite 2.x and SQLite 3.x are incompatible. If you upgrade your SQLite version - and this can also happen implicitly if you upgrade from PySQLite 1.0.x to 1.1.x or 2.x - then you must convert your database. To do this, install both SQLite 2.8 and SQLite 3.x. They have different filenames, so can coexist in the same directory. Then use the following commands (Windows): {{{ $ mv trac.db trac2.db $ sqlite trac2.db .dump | sqlite3 trac.db }}} Then when you're happy with the conversion and tested everything, you can delete the trac2.db file. === Upgrading SQLite from 3.x.y to 3.x.z Similar to above, the following shows you how to upgrade from SQLite 3.2.8 to 3.3.4: {{{ $ mv trac.db trac-old.db $ LD_LIBRARY_PATH=/opt/sqlite-3.2.8/lib /opt/sqlite-3.2.8/bin/sqlite3 \ trac-old.db .dump | \ LD_LIBRARY_PATH=/opt/sqlite-3.3.4/lib /opt/sqlite-3.3.4/bin/sqlite3 trac.db }}} == Troubleshooting From time to time, there are reports about problems related to Pysqlite and/or SQLite, and here are a few of those with resolutions. === Determine actual SQLite and PySqlite version The SQLite and PySqlite versions are displayed on the //About Trac// page for users that have the `CONFIG_VIEW` permission. === Test that bindings are imported Another way to test the bindings with PySqlite 2 is to use: {{{ >>> from pysqlite2 import dbapi2 as sqlite }}} This test should not fail. === Check if database is ok Sometimes `vacuum` helps to fix inconsistencies in the database: {{{ $ sqlite3 trac-parent/mytracinstance/db/trac.db sqlite> PRAGMA integrity_check; ... some errors ... sqlite> VACUUM; sqlite> PRAGMA integrity_check; ok }}} See also [http://www.sqlite.org/pragma.html pragma], [http://www.sqlite.org/sqlite.html command line], [http://www.sqlite.org/lang_vacuum.html vacuum]. === Common errors ==== `OperationalError: unsupported file format` This probably is symptomatic of a mismatch between the SQLite library and the SQLite database format. See Trac-ML:7540. And this might be caused by a different version of PHP's pdo_sqlite module when you use Trac in mod_python mode. Check your pdo_sqlite module's version, or if OK, you may simply disable it, see `/etc/php.d/pdo_sqlite.ini`. Note that sqlite 3.3.x and 3.2.x are not compatible. ==== `DatabaseError: file is encrypted or is not a database` There are typically three situations in which this somewhat cryptic error message can be seen: - when trying to modify the database file and the '''write''' permission is missing; fix the permissions. - when accessing a SQLite 2.x database file with a SQLite 3.x library; see [#UpgradingSQLitefrom2.xto3.x above] for the upgrade instructions. - when accessing a database that has been created in or explicitly converted to WAL mode, using a version of SQLite older than 3.7.0, ie with no WAL support; upgrade your bindings to use a recent SQLite. ==== `OperationalError: SQL logic error or missing database` ''This can indicate that the database was corrupted.'' A procedure similar to upgrading can be used in order to recover such a database: {{{ sqlite3 corrupted.db .dump | sqlite3 recovered.db }}} See #2598, for example. ''This can also be the symptom of errors due to constraint violations'' And this ''might'' correspond to an open bug. See #2902 and #2570. ==== `OperationalError: unable to open database file` Besides the obvious reason of missing '''read''' permissions on the file, it can also happen when the server process has already opened too many files and is unable to open new ones. See #8551 for a detailed explanation. You can tell immediately between the two causes depending on the occurrence of the error: if the error is systematic, it's a permission problem, if it's only transient and happening under load, then it's likely an operating system limitation. ==== `OperationalError: database is locked` There are numerous reasons why you can get this. First, if this only happens occasionally and if the Trac server is still reachable after a second attempt, then it's not really a problem. This simply can happen and indicates that some other user was writing to the database at the same time your request triggered an attempted to write. There are probably a few things that could be enhanced in the future to handle this situation, like automatic retry, see #3446. Also, if this error is somehow inevitable with SQLite, we should make the error message a bit more "user friendly" (#3503). The lock error is also much more frequent if SQLite is used in a multi-threaded environment, like TracStandalone or TracModPython, but the library was not compiled to be thread-safe. See above, [#BuildingSQLiteyourself building from source], and #2170. The real problem with this occurs when ''all'' requests to Trac end up with this error. This indicates a permanent lock situation, which is not normal. Here are the known possible reasons for this: * there was a crash related to some other part of the system, like due to the svn bindings, and the SQLite journal file (which is the materialization of the lock), was left behind. Simply removing the journal file will take care of the lock situation. Of course, you'll have to fix the faulty part of system in order to get rid of the crashes (#1590). * The Pysqlite version is older than 2.0.5 and/or Trac is pre0.9. Upgrading Trac and Pysqlite will solve the issue (#2345). See also: SQLite:DatabaseIsLocked. ==== `ProgrammingError: library routine called out of sequence` This happens on MacOS X, see #2969. See also: SQLite:LibraryRoutineCalledOutOfSequence. ==== `Warning: You can only execute one statement at a time.` This is typically an error which happens when the pysqlite package you're using is loading at runtime a `sqlite3.so` library which has a different version than the one against which pysqlite was built, eg see #2993. ==== `DatabaseError: database is full` Besides the obvious reason (no space left on the partition where the trac db resides), you probably ran out of space in either `/tmp` or `/var/tmp`, where SQLite apparently needs to write too. See #2356, #ps20. ==== `NameError: global name 'sqlite' is not defined` This error usually comes from an invalid PySqlite installation: the Python interpreter is not able to import the sqlite module. {{{ ... File ".../python2.3/site-packages/trac/db.py", line 321, in init_db cnx = sqlite.connect(path, timeout=int(params.get('timeout', 10000))) NameError: global name 'sqlite' is not defined ... }}} * Check that the sqlite Python module can be load from a Python interpreter: {{{ python >>> import sqlite }}} * Check that the Python intepreter that is run from the web server can find this module.[[BR]] The default path for this module is `/site-packages`. If you've installed the module in another directory, make sure the `sys.path` variable is set up with this directory (more on this in TracCgi, TracModPython, TracFastCgi). A common mistake is to install PySqlite for one Python interpreter, and run the server with another Python interpreter: both interpreters do not use the same paths to search for modules. Another possibility is the pysqlite2 installer forgot to make "_sqlite.so" dll unreadable by anyone other than root. Fix by `chmod 775 _sqlite.so` inside `$PYTHON_PATH/site-packages/pysqlite2/`. Also make sure you ld.so.cache (`ldconfig --print-cache` if using Debian) finds the correct path for installed libraries that Trac depends on. If it cannot be modified, set set your LD_LIBRARY_PATH to point where SQLite dlls are installed. ==== `_sqlite.so: Undefined symbol "PyGILState_Ensure"` If after the import test you encounter this message, it most likely means that your Python was not compiled with threads support. On FreeBSD this is easily verified by doing a {{{grep -i threads /var/db/ports/python24/options}}}. If it comes back with a {{{WITHOUT_THREADS=true}}}, you need to configure Python to be compiled with threads by executing {{{make config}}} in the appropriate Python ports directory and subsequently recompile and replace Python. ==== `InterfaceError: Cursor needed to be reset because of commit/rollback and can no longer be fetched from.` When upgrading trac databases from 0.11.x to 0.12, the trac-admin upgrade script will die with this error due to a bug in PySqlite versions 2.5.2-2.5.4. It is advised to use either a newer or older release to successfully upgrade. See ticket #9434 for details. === Other issues Segmentation faults can happen in multi-threaded servers using a SQLite library which has ''not'' been configured to be multi-thread safe, see [#BuildingSQLiteyourself above]. ==== Summary of Known Issues [[TicketQuery(status=!closed&keywords=~sqlite&type=defect)]]