[ZODB-Dev] Relstorage pack problems

[Shane Hathaway]

Shane Hathaway wrote:
> Assuming your bad script caused your problem, it is likely that packing 
> will still mess up your database, since you still probably have mixed-up 
> object_state rows.  Don't pack until I've had a chance to look again.

Here is some more analysis.  Now that I understand you accidentally 
merged two databases into one by forcing copyTransactionsFrom() to run 
when it shouldn't, I looked for the transactions you merged.  First I 
looked for the OIDs with a confused transaction ID.


=> select zoid from current_object where tid != (select max(tid) from 
object_state where object_state.zoid = current_object.zoid);
  zoid
------
     7
    10
    12
    11
     9
     8
(6 rows)


Then I listed all non-current transaction IDs for those objects.


=>  select zoid, tid from object_state where zoid in (7,8,9,10,11,12) 
and tid != (select tid from current_object where current_object.zoid = 
object_state.zoid);
  zoid |        tid
------+--------------------
     8 | 250499913748614178
     9 | 250499913748614178
    10 | 250499913748614178
    11 | 250499913748614178
    12 | 250499913748614178
     7 | 250499913748614178
(6 rows)


Based on this information and the information in my last email, I can 
deduce that you fortunately merged only two transactions from another 
database and that while the merge caused conflicts, these objects 
haven't been otherwise modified.  Note that the bad database merge could 
have happened at any time, not necessarily November 17 when these 
transactions were created.  Anyone with access to your database and your 
broken script could cause this problem again.  Fix the script quickly.

Here are the two bad transactions:

250499913441768123 | initial database creation
250499913748614178 | /manage_main\012\012Created Zope Application

You need to delete all traces of these two transactions from your 
database.  Before you do, please ensure nothing is actually using them. 
  The query below should not return any rows.


select * from current_object where tid in (250499913441768123, 
250499913748614178);


Assuming that query returns no rows, here is how you can remove the bad 
transactions:


update object_state set prev_tid = 0 where prev_tid in 
(250499913441768123, 250499913748614178);
delete from object_state where tid in (250499913441768123, 
250499913748614178);
delete from object_ref where tid in (250499913441768123, 
250499913748614178);
delete from object_refs_added where tid in (250499913441768123, 
250499913748614178);
delete from transaction where tid in (250499913441768123, 
250499913748614178);
commit;


Once you've done that, you should see no more anomalies in current_object:


=>  select zoid from current_object where tid != (select max(tid) from 
object_state where object_state.zoid = current_object.zoid);
  zoid
------
(0 rows)


I used several shortcuts for this solution, particularly the statement 
that sets prev_tid to 0.  If you had merged a more complex database, I 
wouldn't have been able to use shortcuts.

I'm glad to know RelStorage didn't do anything wrong after all.  Perhaps 
the copyTransactionsFrom() method could work harder to prevent a mishap 
like this, but that method is part of the ZODB API, not RelStorage, so I 
don't have as much control over it.

However, I still don't want you to pack yet because my experiments with 
packing your database has revealed some unexpected behavior.  I'm going 
to look into it.

Shane