Modify

Opened 5 years ago

Closed 4 years ago

Last modified 10 months 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 4 years ago by rjollos

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

comment:2 Changed 4 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 10 months ago by rjollos

  • Keywords ProgrammingError closed cursor added

Add Comment

Modify Ticket

Action
as closed The owner will remain coling.
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.