Modify

Opened 4 years ago

Closed 3 years ago

Last modified 8 weeks ago

#6902 closed defect (fixed)

[Patch] Sqlite error while performing upgrade

Reported by: josh@… Owned by: coling
Priority: normal Component: WorkLogPlugin
Severity: normal Keywords: ProgrammingError, closed, cursor
Cc: Trac Release: 0.11

Description

While attempting to perform a series of upgrades to install the Worklog plugin, I kept getting an error telling me

pysqlite2.dbapi2.ProgrammingError: Cannot operate on a closed cursor.

Ostensibly coming from line 129 of api.py

While I get the feeling that there may have been something in my server configuration that was the actual cause of it, I found that resetting cursor after the legacy support section of do_db_upgrade() fixed it:

    def do_db_upgrade(self):
        # Legacy support hack (supports upgrades from revisions r2495 or before)
        db = self.env.get_db_cnx()
        cursor = db.cursor()
        if self.db_installed_version == 0:
            try:
                cursor.execute('SELECT * FROM work_log LIMIT 1')
                db.commit()
                # We've succeeded so we actually have version 1
                self.db_installed_version = 1
            except:
                db.rollback()
        # End Legacy support hack

        # Do the staged updates
        cursor = db.cursor()
        try:
            # This var is to deal with a problem case with pgsql and the "user"
            # keyword. We need to skip over new installations but not upgrades
            # for other db backends.
            skip = False
            
            if self.db_installed_version < 1:
                print 'Creating work_log table'
                cursor.execute('CREATE TABLE work_log ('
                               'worker     TEXT,'
                               'ticket     INTEGER,'
                               'lastchange INTEGER,'
                               'starttime  INTEGER,'
                               'endtime    INTEGER'
                               ')')
                skip = True

            if self.db_installed_version < 2:
                print 'Updating work_log table (v2)'
                cursor.execute('ALTER TABLE work_log '
                               'ADD COLUMN comment TEXT')

            if self.db_installed_version < 3:
                print 'Updating work_log table (v3)'
                if not skip:
                    # This whole section is just to rename the "user" column to "worker"
                    # This column used to be created in step 1 above, but we
                    # can no longer do this in order to support pgsql.
                    # This step is skipped if step 1 was also run (e.g. new installs)
                    # The below seems to be the only way to rename (or drop) a column on sqlite *sigh*
                    cursor.execute('CREATE TABLE work_log_tmp ('
                                   'worker     TEXT,'
                                   'ticket     INTEGER,'
                                   'lastchange INTEGER,'
                                   'starttime  INTEGER,'
                                   'endtime    INTEGER,'
                                   'comment    TEXT'
                                   ')')
                    cursor.execute('INSERT INTO work_log_tmp (worker, ticket, lastchange, starttime, endtime, comment) '
                                   'SELECT user, ticket, lastchange, starttime, endtime, comment FROM work_log')
                    cursor.execute('DROP TABLE work_log')
                    cursor.execute('ALTER TABLE work_log_tmp RENAME TO work_log')

            #if self.db_installed_version < 4:
            #    print 'Updating work_log table (v4)'
            #    cursor.execute('...')
            
            # Updates complete, set the version
            cursor.execute("UPDATE system SET value=%s WHERE name=%s", 
                           (self.db_version, self.db_version_key))
            db.commit()
        except Exception, e:
            self.log.error("WorklogPlugin Exception: %s" % (e,));
            db.rollback()
            raise e

Attachments (0)

Change History (3)

comment:1 Changed 3 years ago by rjollos

  • Summary changed from Sqlite error while performing upgrade (And Fix) to [Patch] Sqlite error while performing upgrade

comment:2 Changed 3 years ago by coling

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

(In [9537]) Reset db cursor after read. Fixes a problem on install/upgrade with sqlite.

Due to separation of read and write db's in 0.12, this is already
fixed there.
Closes #6902 (thanks for pinging me rjollos)

comment:3 Changed 8 weeks ago by rjollos

  • Keywords ProgrammingError closed cursor added

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from coling. Next status will be 'closed'.
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.