= PySqlite = [http://initd.org/tracker/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 SQLite library === Trac 0.9 works best with '''[http://www.sqlite.org/version3.html SQLite 3.x]''', like SQLite 3.2.8 or SQLite 3.3.x. Pay attention to [http://www.sqlite.org/formatchng.html database format changes] when upgrading the SQLite library. See below how to [wiki:PySqlite#Upgradingsqlite upgrade] your database, if needed. ''It should still be possible to use the old SQLite 2.8.x version: everything seems to work (except the search, see #2960) You should then use an equally old Pysqlite version, namely [http://initd.org/pub/software/pysqlite/releases/1.0/1.0.1 1.0.1].'' ==== Downloading SQLite ==== The latest stable version of SQLite can be obtained on the [http://www.sqlite.org/download.html SQLite download] page. ==== Building SQLite yourself ==== '''Note:''' If you want to use Trac in a multi-threaded setup by using either TracModPython or TracStandalone, be sure to 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 to get persistent database locks (see #2170). === The Pysqlite2 bindings === The most stable versions are '''2.0.7''' and '''2.1.3'''. '''2.2.0''' is probably rock solid as well, but it's a bit too early to tell. Detailed release information: * either the 1.x release branch of PySqlite * version 1.1.6 as of this writing, * version ''1.1.7'' required if using SQLite >= 3.3.3 * or, better, the newer 2.0.x release branch * version >= 2.0.5 preferred, * 2.0.3 has been reported to cause [ticket:2688 crashes] on Windows * 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 brand new ''2.2.0'' version appears to work with Trac as well (I tested the 2.4 egg from initrd on Windows, and on Linux I built it from source, linking with sqlite-3.3.4; so far both are working perfectly) ==== Downloading Pysqlite ==== The versions listed above are available from the [http://initd.org/tracker/pysqlite/wiki#Downloads initd.org Downloads] page. == Upgrading SQLite == === Upgrading SQLite from 2.x to 3.x === The following information is copied from http://dev.ctor.org/pkcs1/wiki/TracUpgrade The database formats used by SQLite 2.x and sqlite 3.x are incompatible. If you upgrade your SQLite version (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 === It's almost the same as above. The following shows you how to upgrade from 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. This section willl guide you through understanding and fixing those issues, should those problems also happen to you. === `OperationalError: unsupported file format` === ''This probably is symptomatic of a mismatch between the SQLite library and the SQLite database format.'' See [http://permalink.gmane.org/gmane.comp.version-control.subversion.trac.general/7540 Trac-ML:7540] === `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: 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 triggerd an attempted to write. There are probably a few things that could be enhanced in the future to handle this situation, like automatic retry (or improve the session code). 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 [wiki:PySqlite#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 (e.g. see #1590). * The Pysqlite version is older than 2.0.5 and/or Trac is pre0.9. Upgrading Trac and Pysqlite will solve the issue (see #2345) === `ProgrammingError: library routine called out of sequence` === This happens on MacOS X, and is still an open issue (see #2969) === `Segmentation Fault` === Some old Python 2.3 versions (like on SuSE 9.0) have a bug related to gc and weakrefs, which might be triggered by Trac. ([http://projects.edgewall.com/trac/ticket/2170#change_8 details]) '''P.S. Note that despite of all of the above, for some (most?) users, SQLite/Pysqlite works flawlessly :)'''