[p4] Integrity error in MySQL database

[Andres Fuentes]

	Hello,

I'm having problems with an integrity error in MySQL database : duplicate entry in p4dti_replications table  (we're using P4DTI to synchronize Perforce with Bugzilla)

This error appears after a problem with the electric line...the server shutdown unexpectedly. Probably this causes the database error...and the p4dti bad execution.


I copy the error below :


********
(P4DTI-8658)  This is an automatically generated e-mail from the Perforce Defect
Tracking Integration replicator 'replicator0'.

(P4DTI-8647)  The replicator failed to poll successfully, because of the
following problem:

(P4DTI-891X)  Error (_mysql_exceptions.IntegrityError): (1062, "Duplicate entry
'6035607' for key 1")

(P4DTI-8523)  Here's a full Python traceback:

Exception:
IntegrityError: (1062, "Duplicate entry '6035607' for key 1")
Traceback (innermost last):
  File "/opt/p4dti/replicator.py", line 857, in carefully_poll_databases
    self.poll_databases()
    locals:
      self: <replicator.replicator instance at 0x405cab8c>
  File "/opt/p4dti/replicator.py", line 1203, in poll_databases
    changed_issues, _, dt_marker = self.dt.changed_entities()
    locals:
      self: <replicator.replicator instance at 0x405cab8c>
  File "/opt/p4dti/dt_bugzilla.py", line 559, in changed_entities
    replication = self.bugzilla.new_replication()
    locals:
      self: <dt_bugzilla.dt_bugzilla instance at 0x405cab4c>
  File "/opt/p4dti/bugzilla.py", line 1630, in new_replication
    { 'start': self.replication,
    locals:
      self: <bugzilla.bugzilla instance at 0x405b5e2c>
  File "/opt/p4dti/bugzilla.py", line 550, in insert_row_rid_sid
    self.insert_row(table, dict)
    locals:
      table: 'p4dti_replications'
      self: <bugzilla.bugzilla instance at 0x405b5e2c>
      dict: {'start': '2006-06-05 10:38:25', 'rid': 'replicator0', 'end': '0', 'sid': 'perforce0'}
  File "/opt/p4dti/bugzilla.py", line 539, in insert_row
    rows = self.execute(command)
    locals:
      self: <bugzilla.bugzilla instance at 0x405b5e2c>
      value: "'perforce0'"
      command: "insert p4dti_replications ( start, rid, end, sid ) values ( '2006-06-05 10:38:25', 'replicator0', '0', 'perforce0' );"
      key: 'sid'
      table: 'p4dti_replications'
      values: "'2006-06-05 10:38:25', 'replicator0', '0', 'perforce0'"
      columns: 'start, rid, end, sid'
      dict: {'start': '2006-06-05 10:38:25', 'rid': 'replicator0', 'end': '0', 'sid': 'perforce0'}
  File "/opt/p4dti/bugzilla.py", line 96, in execute
    self.cursor.execute(sql)
    locals:
      self: <bugzilla.bugzilla instance at 0x405b5e2c>
      sql: "insert p4dti_replications ( start, rid, end, sid ) values ( '2006-06-05 10:38:25', 'replicator0', '0', 'perforce0' );"
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 95, in execute
    return self._execute(query, args)
    locals:
      query: "insert p4dti_replications ( start, rid, end, sid ) values ( '2006-06-05 10:38:25', 'replicator0', '0', 'perforce0' );"
      self: <MySQLdb.cursors.Cursor instance at 0x405b598c>
      args: None
  File "/usr/lib/python2.3/site-packages/MySQLdb/cursors.py", line 114, in _execute
    self.errorhandler(self, exc, value)
    locals:
      exc: <class _mysql_exceptions.IntegrityError at 0x405b923c>
      self: <MySQLdb.cursors.Cursor instance at 0x405b598c>
      args: None
      value: <_mysql_exceptions.IntegrityError instance at 0x403c390c>
      query: "insert p4dti_replications ( start, rid, end, sid ) values ( '2006-06-05 10:38:25', 'replicator0', '0', 'perforce0' );"
      ListType: <type 'list'>
      exc_info: <built-in function exc_info>
      TupleType: <type 'tuple'>
  File "/usr/lib/python2.3/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler
    raise errorclass, errorvalue
    locals:
      errorclass: <class _mysql_exceptions.IntegrityError at 0x405b923c>
      cursor: <MySQLdb.cursors.Cursor instance at 0x405b598c>
      connection: <_mysql.connection open to 'localhost' at 8111484>
      errorvalue: <_mysql_exceptions.IntegrityError instance at 0x403c390c>
      error: (<class _mysql_exceptions.IntegrityError at 0x405b923c>, <_mysql_exceptions.IntegrityError instance at 0x403c390c>)
***********


Anybody knows a way to avert this kind of errors with database when an electric error happen? 
...maybe exists a trigger to control this integrity data errors or a subroutine to implement the p4dti scripts...



...I'm doing a query into MySQL db searching the problematic record whith duplicate key...but this record don't exist...
Anybody knows the better way to repare this corrupt table?




Thanks in advance


Andrés








-----Mensaje original-----
De: Steve Borrett [mailto:support at perforce.com]
Enviado el: lunes, 05 de junio de 2006 11:18
Para: Andres Fuentes
Asunto: RE: Help! I can't submit files to Perforce (CALL#1325949)


Hi Andres,

> maybe the problem was a network error...now it's working fine. Thank you for 
> your help anyway.

You're very welcome. I'm glad to hear things are working OK again now.
Please let us know if you have any more queries.

regards,

Steve.

********************************************
Steve Borrett
Technical Support
Perforce Software Europe

Have you heard about our Perforce European User Conference in September?
See  http://www.perforce.com/perforce/eucon2006/index.html for the provisional
agenda

Perforce Software UK has moved!  From May the 5th our new address is:

 Perforce Software UK Ltd.
 West Forest Gate
 Wellington Road
 Wokingham
 Berkshire RG40 2AQ
 UK 

Detailed Perforce Training options can be found at 
http://www.perforce.com/perforce/training.html

********************************************