Error when trying to delete priority

[jason]

When trying to delete a priority via WebAdmin:

Traceback (most recent call last):
  File "/usr/lib/python2.3/site-packages/Trac-0.11dev_r6319-py2.3.egg/trac/web/api.py", line 341, in send_error
    'text/html')
  File "/usr/lib/python2.3/site-packages/Trac-0.11dev_r6319-py2.3.egg/trac/web/chrome.py", line 608, in render_template
    if not req.session or not int(req.session.get('accesskeys', 0)):
  File "/usr/lib/python2.3/site-packages/Trac-0.11dev_r6319-py2.3.egg/trac/web/api.py", line 170, in __getattr__
    value = self.callbacks[name](self)
  File "/usr/lib/python2.3/site-packages/Trac-0.11dev_r6319-py2.3.egg/trac/web/main.py", line 255, in _get_session
    return Session(self.env, req)
  File "/usr/lib/python2.3/site-packages/Trac-0.11dev_r6319-py2.3.egg/trac/web/session.py", line 53, in __init__
    self.get_session(req.authname, authenticated=True)
  File "/usr/lib/python2.3/site-packages/Trac-0.11dev_r6319-py2.3.egg/trac/web/session.py", line 74, in get_session
    (sid, int(authenticated)))
  File "/usr/lib/python2.3/site-packages/Trac-0.11dev_r6319-py2.3.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.3/site-packages/Trac-0.11dev_r6319-py2.3.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: current transaction is aborted, commands ignored until end of transaction block

[Christian Boos]

That backtrace is a secondary error while trying to send the error which occurred when deleting the priority. Any hint about what could have caused that first failure? No existing priority?

(I can't trigger an error)

[justin@…]

I fixed it by taking out these lines in trac/ticket/model.py:

cursor.execute("UPDATE enum set value=value-1 where "

"type=%s and value>%s", (self.type, self._old_value))

Now I can delete priorities. But I can't fix the ordering to start from 1, which goofs up the coloring, as has been discussed in other tickets. I am using r6322.

[osimons]

Replying to justin@jetiprinters.com:

I fixed it by taking out these lines in trac/ticket/model.py:

cursor.execute("UPDATE enum set value=value-1 where "

"type=%s and value>%s", (self.type, self._old_value))

Oops. That seems to be my change at [6317] for #2876. Worked fine on my test, but apparently it isn't stable enough. I'll look into it within the next couple of hours.

Now I can delete priorities. But I can't fix the ordering to start from 1, which goofs up the coloring, as has been discussed in other tickets. I am using r6322.

No. I have no solution for the ordering other than to clean it using sql like you want it - or delete all priorities and recreate them. That should reset the order.

[jason]

No. I have no solution for the ordering other than to clean it using sql like you want it - or delete all priorities and recreate them. That should reset the order.

Right. If I delete and recreate all priorities, what happens to existing tickets?

[osimons]

Replying to jason:

Right. If I delete and recreate all priorities, what happens to existing tickets?

They are just stored as values equal to name in tickets and ticket history. There are no foreign keys, so deleting and recreating should be a harmless operation. Do make a backup though, just to be sure.

Replying to osimons:

Oops. That seems to be my change at [6317] for #2876. Worked fine on my test, but apparently it isn't stable enough. I'll look into it within the next couple of hours.

Changed to use existing API instead of making new SQL - SQL that was obviously wrong now that I notice field is stored as text… My mistake. Could you/someone try this patch:

trac/ticket/model.py

@@ -371 +371 @@
         self.env.log.info('Deleting %s %s' % (self.type, self.name))
         cursor.execute("DELETE FROM enum WHERE type=%s AND value=%s",
                        (self.type, self._old_value))
-        cursor.execute("UPDATE enum set value=value-1 where "
-                       "type=%s and value>%s", (self.type, self._old_value))
+        # Re-order any enums that have higher value than deleted (close gaps)
+        for enum in self.select(self.env):
+            try:
+                if int(enum.value) > int(self._old_value):
+                    enum.value = unicode(int(enum.value)-1)
+                    enum.update(db=db)
+            except ValueError:
+                pass # Ignore cast error for this non-essential operation
 
         if handle_ta:
             db.commit()

I am quite sure that is a good working solution, but wouldn't mind confirmation.

[sid]

#6494 was marked as duplicate of this ticket.

[osimons]

Patch (with comments from cmlenz) applied as [6323]. Sorry for the inconvenience - hope this does it :-)

[jason]

Replying to osimons:

Patch (with comments from cmlenz) applied as [6323]. Sorry for the inconvenience - hope this does it :-)

Seems OK now. And deleting / recreating all prioroties fixed up the ordering and ticket coloring.