Changes between Initial Version and Version 3 of Ticket #82


Ignore:
Timestamp:
Jun 9, 2014, 5:03:36 PM (5 years ago)
Author:
schwa
Comment:

Due to the entries in the traceback (see description) I instrumented /usr/lib/python2.6/dist-packages/trac/db/sqlite_backend.py like this:

...
try:
    import pysqlite2.dbapi2 as sqlite
    have_pysqlite = 2
except ImportError:
    try:
        import sqlite3 as sqlite
        have_pysqlite = 2
    except ImportError:
        have_pysqlite = 0

# NEW
mylog = open("/var/local/trac/ftputil/log/my.log", "w")

if have_pysqlite == 2:
    # Force values to integers because PySQLite 2.2.0 had (2, 2, '0')
    sqlite_version = tuple([int(x) for x in sqlite.sqlite_version_info])
    sqlite_version_string = sqlite.sqlite_version

    class PyFormatCursor(sqlite.Cursor):
        def _rollback_on_error(self, function, *args, **kwargs):
            try:
                # NEW
                print >> mylog, function, args, kwargs
                mylog.flush()
                return function(self, *args, **kwargs)
            except sqlite.DatabaseError:
                self.cnx.rollback()
                raise
...

When inspecting the generated log file my.log after an Internal Server Error, I noticed that the last entry was

<method 'execute' of 'sqlite3.Cursor' objects> ('\n            SELECT name,value FROM session_attribute\n            WHERE sid=? and authenticated=?\n            ', (u'schwa', 1)) {}

I checked the database and found that the table session had nearly half a million rows and session_attribute over a million. A "query" for the indixes gives

sqlite> .schema session
CREATE TABLE session (
    sid text,
    authenticated integer,
    last_visit integer,
    UNIQUE (sid,authenticated)
);
CREATE INDEX session_authenticated_idx ON session (authenticated);
CREATE INDEX session_last_visit_idx ON session (last_visit);
sqlite> .schema session_attribute
CREATE TABLE session_attribute (
    sid text,
    authenticated integer,
    name text,
    value text,
    UNIQUE (sid,authenticated,name)
);

That is, session_attribute doesn't have any index, so the SELECT query from the custom log file might/will take very long.

After checking http://trac.edgewall.org/wiki/TracDev/DatabaseSchema and making a backup of the database, I stopped the webserver and deleted all rows from session_attribute, session and auth_cookie.

After restarting the web server, I could delete bogus wiki page versions easily. (These page versions had been created while changing and saving the Download page, which always gave me an Internal Server Error then. Neither had I been able to delete the old page versions before without causing an Internal Server Error.)

Legend:

Unmodified
Added
Removed
Modified
  • Ticket #82

    • Property Status changed from new to closed
    • Property Resolution changed from to fixed