Mercurial > p > roundup > code
comparison roundup/backends/rdbms_common.py @ 6433:c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
Title: Import of retired node with username after active node fails
with unique constraint failure.
More fixes needed for mysql and postgresql.
mysql: add unique constraint for (keyvalue, __retired__) when
creating class in the database.
On schema change if class is changed, remove the unique
constraint too.
upgrade version of rdbms database from 5 to 6 to add constraint
to all version 5 databases that were created as version 5
and didn't get the unique constraint. Make no changes
on version 5 databases upgraded from version 4, the upgrade
process to 5 added the constraint. Make no changes
to other databases (sqlite, postgres) during upgrade from
version 5 to 6.
postgres: Handle the exception raised on unique constraint violation.
The exception invalidates the database connection so it
can't be used to recover from the exception.
Added two new database methods:
checkpoint_data - performs a db.commit under postgres
does nothing on other backends
restore_connection_on_error - does a db.rollback on
postgres, does nothing on other
backends
with the rollback() done on the connection I can use the
database connection to fixup the import that failed on the
unique constraint. This makes postgres slower but without the
commit after every imported object, the rollback will delete
all the entries done up to this point.
Trying to figure out how to make the caller do_import batch
and recover from this failure is beyond me.
Also dismissed having to process the export csv file before
importing. Pushing that onto a user just seems wrong. Also
since import/export isn't frequently done the lack of
surprise on having a failing import and reduced
load/frustration for the user seems worth it. Also the import
can be run in verbose mode where it prints out a row as it is
processed, so it may take a while, ut the user can get
feedback.
db_test-base.py: add test for upgrade from 5 to 6.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Thu, 10 Jun 2021 12:52:05 -0400 |
| parents | ada1edcc9132 |
| children | 30358e334232 91ab3e0ffcd0 |
comparison
equal
deleted
inserted
replaced
| 6432:97a45bfa62a8 | 6433:c1d3fbcdbfbd |
|---|---|
| 328 # commit | 328 # commit |
| 329 self.sql_commit() | 329 self.sql_commit() |
| 330 | 330 |
| 331 # update this number when we need to make changes to the SQL structure | 331 # update this number when we need to make changes to the SQL structure |
| 332 # of the backen database | 332 # of the backen database |
| 333 current_db_version = 5 | 333 current_db_version = 6 |
| 334 db_version_updated = False | 334 db_version_updated = False |
| 335 | 335 |
| 336 def upgrade_db(self): | 336 def upgrade_db(self): |
| 337 """ Update the SQL database to reflect changes in the backend code. | 337 """ Update the SQL database to reflect changes in the backend code. |
| 338 | 338 |
| 370 | 370 |
| 371 if version < 5: | 371 if version < 5: |
| 372 self.log_info('upgrade to version 5') | 372 self.log_info('upgrade to version 5') |
| 373 self.fix_version_4_tables() | 373 self.fix_version_4_tables() |
| 374 | 374 |
| 375 if version < 6: | |
| 376 self.log_info('upgrade to version 6') | |
| 377 self.fix_version_5_tables() | |
| 378 | |
| 375 self.database_schema['version'] = self.current_db_version | 379 self.database_schema['version'] = self.current_db_version |
| 376 self.db_version_updated = True | 380 self.db_version_updated = True |
| 377 return 1 | 381 return 1 |
| 378 | 382 |
| 379 def fix_version_3_tables(self): | 383 def fix_version_3_tables(self): |
| 396 c.execute('update _%s set __retired__=%s where id=%s' % (cn, | 400 c.execute('update _%s set __retired__=%s where id=%s' % (cn, |
| 397 self.arg, self.arg), (id, id)) | 401 self.arg, self.arg), (id, id)) |
| 398 | 402 |
| 399 if klass.key: | 403 if klass.key: |
| 400 self.add_class_key_required_unique_constraint(cn, klass.key) | 404 self.add_class_key_required_unique_constraint(cn, klass.key) |
| 405 | |
| 406 def fix_version_5_tables(self): | |
| 407 # Default (used by sqlite, postgres): NOOP | |
| 408 # mysql overrides this because it is missing | |
| 409 # _<class>_key_retired_idx index used to make | |
| 410 # sure that the key is unique if it was created | |
| 411 # as version 5. | |
| 412 pass | |
| 401 | 413 |
| 402 def _convert_journal_tables(self): | 414 def _convert_journal_tables(self): |
| 403 """Get current journal table contents, drop the table and re-create""" | 415 """Get current journal table contents, drop the table and re-create""" |
| 404 c = self.cursor | 416 c = self.cursor |
| 405 cols = ','.join('nodeid date tag action params'.split()) | 417 cols = ','.join('nodeid date tag action params'.split()) |
| 464 klass.index(nodeid) | 476 klass.index(nodeid) |
| 465 else: | 477 else: |
| 466 for nodeid in klass.list(): | 478 for nodeid in klass.list(): |
| 467 klass.index(nodeid) | 479 klass.index(nodeid) |
| 468 self.indexer.save_index() | 480 self.indexer.save_index() |
| 481 | |
| 482 def checkpoint_data(self): | |
| 483 """Call if you need to commit the state of the database | |
| 484 so you can try to fix the error rather than rolling back | |
| 485 | |
| 486 Needed for postgres when importing data. | |
| 487 """ | |
| 488 pass | |
| 489 | |
| 490 def restore_connection_on_error(self): | |
| 491 """on a database error/exception recover the db connection | |
| 492 if left in an unusable state (e.g. postgres requires | |
| 493 a rollback). | |
| 494 """ | |
| 495 pass | |
| 469 | 496 |
| 470 # Used here in the generic backend to determine if the database | 497 # Used here in the generic backend to determine if the database |
| 471 # supports 'DOUBLE PRECISION' for floating point numbers. | 498 # supports 'DOUBLE PRECISION' for floating point numbers. |
| 472 implements_double_precision = True | 499 implements_double_precision = True |
| 473 | 500 |
| 3185 has_node = self.hasnode(newid) | 3212 has_node = self.hasnode(newid) |
| 3186 if not has_node: | 3213 if not has_node: |
| 3187 self.db.addnode(self.classname, newid, d) # insert | 3214 self.db.addnode(self.classname, newid, d) # insert |
| 3188 else: | 3215 else: |
| 3189 self.db.setnode(self.classname, newid, d) # update | 3216 self.db.setnode(self.classname, newid, d) # update |
| 3217 self.db.checkpoint_data() | |
| 3190 # Blech, different db's return different exceptions | 3218 # Blech, different db's return different exceptions |
| 3191 # so I can't list them here as some might not be defined | 3219 # so I can't list them here as some might not be defined |
| 3192 # on a given system. So capture all exceptions from the | 3220 # on a given system. So capture all exceptions from the |
| 3193 # code above and try to correct it. If it's correctable its | 3221 # code above and try to correct it. If it's correctable its |
| 3194 # some form of Uniqueness Failure/Integrity Error otherwise | 3222 # some form of Uniqueness Failure/Integrity Error otherwise |
| 3195 # undo the fixup and pass on the error. | 3223 # undo the fixup and pass on the error. |
| 3196 except Exception as e: | 3224 except Exception as e: # nosec |
| 3197 logger.info('Attempting to handle import exception ' | 3225 logger.info('Attempting to handle import exception ' |
| 3198 'for id %s: %s' % (newid,e)) | 3226 'for id %s: %s' % (newid,e)) |
| 3199 | 3227 |
| 3200 keyname = self.db.user.getkey() | 3228 keyname = self.db.user.getkey() |
| 3201 if has_node or not keyname: # Not an integrity error | 3229 if has_node or not keyname: # Not an integrity error |
| 3202 raise | 3230 raise |
| 3231 self.db.restore_connection_on_error() | |
| 3203 activeid = self.db.user.lookup(d[keyname]) | 3232 activeid = self.db.user.lookup(d[keyname]) |
| 3204 self.db.sql(retired_sql, (-1, activeid)) # clear the active node | 3233 self.db.sql(retired_sql, (-1, activeid)) # clear the active node |
| 3205 # this can only happen on an addnode, so retry | 3234 # this can only happen on an addnode, so retry |
| 3206 try: | 3235 try: |
| 3207 # if this raises an error, let it propagate upward | 3236 # if this raises an error, let it propagate upward |