cleanup by connection mysql

/* get the list of synced issues - has to be unquoted for mysql */
SELECT LR.ISSUE_URN AS this_issue, RR.ISSUE_URN AS remote_issue
FROM AO_08F1AF_TWIN_TRACE TT
LEFT JOIN AO_08F1AF_REPLICA LR ON TT.LOCAL_REPLICA_ID = LR.ID
LEFT JOIN AO_08F1AF_REPLICA RR ON TT.REMOTE_REPLICA_ID = RR.ID
JOIN AO_08F1AF_RELATION R ON TT.RELATION_ID = R.ID
WHERE R.NAME IN ('CONNECTION NAME');



// ------


cleanup-connection.sql
 
 
/* execute these only once that you've obtained and recorded the list of synced issues via the previous query */
/* TRACES FROM TWIN_TRACE, ISSUE_URN - LOCAL */
DELETE FROM AO_08F1AF_TRACE WHERE TWIN_TRACE_ID IN (SELECT TT.ID FROM AO_08F1AF_TWIN_TRACE TT JOIN AO_08F1AF_RELATION R ON TT.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME');
 
/* TRACES FROM SYNC EVENT, ISSUE_URN - LOCAL */
DELETE FROM AO_08F1AF_TRACE WHERE EVENT_ID IN (SELECT SE.ID FROM AO_08F1AF_SYNC_EVENT SE JOIN AO_08F1AF_RELATION R ON SE.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME');
 
/* REMOTE TRACES FROM SYNC REQUESTS, ISSUE_URN - REMOTE */
DELETE FROM AO_08F1AF_TRACE WHERE REQUEST_ID IN (SELECT SR.ID FROM AO_08F1AF_SYNC_REQUEST SR JOIN AO_08F1AF_RELATION R ON SR.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME');
 
 
/* CONNECT CONTEXT FROM SYNC EVENT, ISSUE_URN - LOCAL */
DELETE FROM AO_08F1AF_CONNECT_CONTEXT WHERE ID IN (SELECT SE.CONNECT_CONTEXT_ID FROM AO_08F1AF_SYNC_EVENT SE JOIN AO_08F1AF_RELATION R ON SE.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME');
 
/* CONNECT CONTEXT FROM SYNC REQUEST, ISSUE_URN - REMOTE */
DELETE FROM AO_08F1AF_CONNECT_CONTEXT WHERE ID IN (SELECT SR.CONNECT_CONTEXT_ID FROM AO_08F1AF_SYNC_REQUEST SR JOIN AO_08F1AF_RELATION R ON SR.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME');
 
/* ERRORS for LOCAL ISSUE, ISSUE_URN - LOCAL - rectified */
DELETE FROM AO_08F1AF_ERROR WHERE ID IN (select eid from (SELECT E.ID as eid FROM AO_08F1AF_ERROR E JOIN AO_08F1AF_RELATION R ON E.RELATION_ID = R.ID WHERE R.NAME = 'CONNECTION NAME') as e);
 
/* BLOB EVENTS, ISSUE_URN - LOCAL */
DELETE FROM
    AO_08F1AF_BLOB_EVENT
WHERE
    SYNC_EVENT_ID IN (
      SELECT SE.ID
      FROM AO_08F1AF_SYNC_EVENT SE
      JOIN AO_08F1AF_RELATION R ON SE.RELATION_ID = R.ID
      WHERE R.NAME = 'CONNECTION NAME'
    );
 
/* BLOB REQUESTS, ISSUE_URN - REMOTE */
DELETE FROM
    AO_08F1AF_BLOB_REQUEST
WHERE
    SYNC_REQUEST_ID IN (
      SELECT SR.ID
      FROM AO_08F1AF_SYNC_REQUEST SR
      JOIN AO_08F1AF_RELATION R ON SR.RELATION_ID = R.ID
      WHERE R.NAME = 'CONNECTION NAME'
    );
 
/* blob metadata for sync events, ISSUE_URN - LOCAL */
DELETE FROM
    AO_08F1AF_BLOB_METADATA
WHERE
    SYNC_EVENT_FOR_BLOB_ID IN (
      SELECT SE.ID
      FROM AO_08F1AF_SYNC_EVENT SE
      JOIN AO_08F1AF_RELATION R ON SE.RELATION_ID = R.ID
      WHERE R.NAME = 'CONNECTION NAME'
    );
 
/* blob metadata for sync requests, ISSUE_URN - REMOTE */
DELETE FROM
    AO_08F1AF_BLOB_METADATA
WHERE
    SYNC_REQUEST_FOR_BLOB_ID IN (
      SELECT SR.ID
      FROM AO_08F1AF_SYNC_REQUEST SR
      JOIN AO_08F1AF_RELATION R ON SR.RELATION_ID = R.ID
      WHERE R.NAME = 'CONNECTION NAME'
    );
 
/* sync requests, ISSUE_URN - REMOTE */
DELETE FROM
  AO_08F1AF_SYNC_REQUEST
WHERE
  RELATION_ID IN (
    SELECT R.ID FROM AO_08F1AF_RELATION R WHERE R.NAME = 'CONNECTION NAME'
  );
 
/* sync event, ISSUE_URN - LOCAL */
DELETE FROM
    AO_08F1AF_SYNC_EVENT
WHERE
    RELATION_ID IN (
      SELECT R.ID FROM AO_08F1AF_RELATION R WHERE R.NAME = 'CONNECTION NAME'
    );
 
/* TWIN_TRACE, ISSUE_URN - LOCAL */
DELETE FROM
  AO_08F1AF_TWIN_TRACE
WHERE
  RELATION_ID IN (
    SELECT R.ID FROM AO_08F1AF_RELATION R WHERE R.NAME = 'CONNECTION NAME'
  );