Database Fried

[anonymous]

Hi there I need help! our trac database is broken. I tried a restore and no go which is odd.. what can i do?

Traceback (most recent call last):
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 387, in dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.3/site-packages/trac/web/main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.3/site-packages/trac/ticket/web_ui.py", line 127, in process_request
    self._do_create(req, db)
  File "/usr/lib/python2.3/site-packages/trac/ticket/web_ui.py", line 190, in _do_create
    ticket.insert(db=db)
  File "/usr/lib/python2.3/site-packages/trac/ticket/model.py", line 170, in insert
    "VALUES (%s,%s,%s)", [(tkt_id, name, self[name])
  File "/usr/lib/python2.3/site-packages/trac/db/util.py", line 57, in executemany
    return self.cursor.executemany(sql_escape_percent(sql), args)
  File "/usr/lib/python2.3/site-packages/sqlite/main.py", line 276, in executemany
    self.execute(query, _i)
  File "/usr/lib/python2.3/site-packages/sqlite/main.py", line 255, in execute
    self.rs = self.con.db.execute(SQL % parms)
IntegrityError: columns ticket, name are not unique

[anonymous]

oh this is when I try to create a new ticket!

[ThurnerRupert]

pls check out ​http://trac.edgewall.org/wiki/PySqlite#Checkifdatabaseisok, could be here in ticket/model.py and sqlite_backend.py:

 tkt_id = db.get_last_id(cursor, 'ticket')

and

    if have_pysqlite == 2:
        def get_last_id(self, cursor, table, column='id'):
            return cursor.lastrowid
    else:
        def get_last_id(self, cursor, table, column='id'):
            return self.cnx.db.sqlite_last_insert_rowid()

[anonymous]

none of these worked! any other ideas?

[Emmanuel Blot]

Replying to anonymous:

none of these worked! any other ideas?

Which version of Trac are you using? If you're using a 0.10.x release, please be sure you're running the latest one (0.10.4)

[anonymous]

I upgraded to 10.4 from 10.3 last night, same issue.

It looks like there is a duplicate entry in the database for some reason. is there a way to increment it by one so it starts with the next ticket number?

[Christian Boos]

Duplicate custom field?

[anonymous]

there are no custom fields to my knowlege, just a stock sqlite. I did take this over from another admin, I know we are using email2trac but I doubt that would have caused this. I can put the dbase someplace for you to download and look at if that would help

[Christian Boos]

Eventually, yes, but the conf/trac.ini file as well. And if you're using a global trac.ini file, that one too.

I don't know the implementation details of email2trac, but if that is adding a custom field and you happen to have one defined somewhere you didn't expect (in the global trac.ini?), that might explain the error you're seeing.

[anonymous]

strange.. nothings changed in over a year since implementation of it. Here is a tar file of our entire trac setup, the trac.db is under trac/gawker_trac_emv/db

ok I can't put a URL in, cause its spam, I'll reply to a msg you sent with the url

[anonymous]

hey there wondeing if there is an update on this?

what I don't understand if I restore to an earlier dbase it's still the same problem!

[Christian Boos]

I was able to reproduce the problem - stay tuned.

[Christian Boos]

Looks like you had several "incomplete" tickets:

sqlite> select max(id) from ticket;
2484
sqlite> select max(ticket) from ticket_custom;
2499
sqlite> select * from ticket_custom where ticket = 2485;
2485|ganja|None
2485|kinja|None
2485|sites|All Sites

The next New Ticket will pick the id 2485, and there are already values for the custom fields for that ticket. There are also attachments, by the way, but I'm not sure they are meaningfull (see select * from attachment where id > 2484;).

What you can do is to clean-up the ticket_custom table (delete from ticket_custom where id > 2484), then create a bunch of tickets (until 2499) and try to retrieve their meaning by looking at the attachments. Or simpler, if the attachments are garbage as well, also clean-up the attachment table the same way.

I've seen that you're using email2trac, so you may want to investigate in that direction for the original cause of the problem.

[anonymous]

thank you.. after I updated the ticket yesterday I decided to restore to an older version and clean up the extra tickets, but this is great info in case it happens again!

thanks guys