Problem description  

Thick and Thin Clients 3.6.x releases fix the way many sync events are handled in the desktop client, and in some cases this exposes some old/hidden issues with older team shares that had mis-formed sync events.

This issue could become visible when a new Client 3.6.x is registered/subscribed to a root having this rare database inconsistency. 

If this happens, the affected client will be unable to sync and you will see errors: "Bad event log" in the client log and "event missing values" in the server log. 

These errors in all cases are related to three non-critical issues with database consistency, which could appear combined in a single root (mostly encountered on roots created before 2018):

• There are events present from deleted file and folder records
• There are old file/folder creation or rename events that have an empty "new_name" field
• There are old file/folder move events that have an empty "new_folder_id" field

Here is a manual sanitization procedure to clean up these mis-formed sync events and resolve the database inconsistencies. 

Check if a root needs sanitization

If you suspect that a root is having problems with its events, you can run the following database requests to diagnose it, replacing "[RootID]" with the root ID number without brackets. 

None of these queries should return more than zero rows in the normal scenario:

Invalid file creation events with no name
SELECT count(*) FROM event WHERE root_id = [RootID] AND type=0 AND new_name is NULL;

Invalid file rename events with no name
SELECT count(*) FROM event WHERE root_id = [RootID] AND type=3 AND new_name is NULL;

Invalid folder creation events with no name
SELECT count(*) FROM event WHERE root_id = [RootID] AND type=6 AND new_name is NULL;

Invalid folder rename events with no name
SELECT count(*) FROM event WHERE root_id = [RootID] AND type=7 AND new_name is NULL;

Orphaned events (related file doesn't exist)
SELECT count(*) FROM event WHERE root_id = [RootID] AND file_id IS NOT NULL AND NOT EXISTS (SELECT id FROM file WHERE root_id = [RootID] AND file.id = event.file_id);

Orphaned events (related folder doesn't exist)
SELECT count(*) FROM event WHERE root_id = [RootID] AND folder_id IS NOT NULL AND NOT EXISTS (SELECT id FROM folder WHERE root_id = [RootID] AND folder.id = event.folder_id);

Orphaned revisions (related file doesn't exist)
SELECT count(*) FROM revision WHERE root_id = [RootID] AND file_id IS NOT NULL AND NOT EXISTS (SELECT id FROM file WHERE root_id = [RootID] AND file.id = revision.file_id);

Sanitization steps 

If you found problems with events in a root, follow these steps to sanitize it.  

2. Make a backup of the root database of the problematic root, and copy it somewhere else - in case you later need to restore it to the original state for any reason. 

To locate the database file, go to http://hostname/system/roots, then locate the root you need and click on "Root Info" button ("i" icon).  
   

Then, scroll the page down to "Database path".

3. Run the following database requests to sanitize the root, replacing "[RootID]" with the root ID number without brackets.

DELETE FROM change_source_user WHERE root_id = [RootID] AND event_id IN (SELECT id FROM event AS orig_evt WHERE orig_evt.root_id = [RootID] AND orig_evt.file_id IS NOT NULL AND orig_evt.deleted = 1 AND orig_evt.id < (SELECT id FROM event AS all_evts WHERE all_evts.root_id = [RootID] AND all_evts.file_id = orig_evt.file_id AND all_evts.type = 0 AND all_evts.deleted = 0 ORDER BY id DESC LIMIT 1));

DELETE FROM change_source_user WHERE root_id = [RootID] AND event_id IN (SELECT id FROM event WHERE root_id = [RootID] AND file_id IS NOT NULL AND NOT EXISTS (select id from file WHERE root_id = [RootID] AND file.id = event.file_id));

DELETE FROM event WHERE root_id = [RootID] AND file_id IS NOT NULL AND NOT EXISTS (select id from file WHERE root_id = [RootID] AND file.id = event.file_id);

DELETE FROM event WHERE root_id = [RootID] AND folder_id IS NOT NULL AND NOT EXISTS (select id from folder WHERE root_id = [RootID] AND folder.id = event.folder_id);

DELETE FROM revision WHERE root_id = [RootID] AND file_id IS NOT NULL AND NOT EXISTS (select id from file WHERE root_id = [RootID] AND file.id = revision.file_id);

DELETE FROM event AS orig_evt WHERE orig_evt.root_id = [RootID] AND orig_evt.file_id IS NOT NULL AND orig_evt.deleted = 1 AND orig_evt.id < (SELECT id FROM event AS all_evts WHERE all_evts.root_id = [RootID] AND all_evts.file_id = orig_evt.file_id AND all_evts.type = 0 AND all_evts.deleted = 0 ORDER BY id DESC LIMIT 1);

DELETE FROM event AS orig_evt WHERE orig_evt.root_id = [RootID] AND orig_evt.folder_id IS NOT NULL AND orig_evt.deleted = 1 AND orig_evt.id < (SELECT id FROM event AS all_evts WHERE all_evts.root_id = [RootID] AND all_evts.folder_id = orig_evt.folder_id AND all_evts.type = 6 AND all_evts.deleted = 0 ORDER BY id DESC LIMIT 1);

UPDATE event SET new_folder_id = (SELECT folder_id FROM file WHERE root_id = [RootID] AND id = event.file_id) WHERE root_id = [RootID] AND type IN (0, 3) AND new_name IS NULL AND new_folder_id IS NULL AND (SELECT folder_id FROM file WHERE root_id = [RootID] AND id = event.file_id) IS NOT NULL;

UPDATE event SET new_folder_id = (SELECT folder_id FROM file WHERE root_id = [RootID] AND id = event.file_id) WHERE root_id = [RootID] AND type IN (0, 3) AND new_name LIKE 'invalid_event%' AND new_folder_id IS NULL AND (SELECT folder_id FROM file WHERE root_id = [RootID] AND id = event.file_id) IS NOT NULL;

UPDATE event SET new_folder_id = (SELECT folder_id FROM folder WHERE root_id = [RootID] AND id = event.folder_id) WHERE root_id = [RootID] AND type IN (6, 7) AND new_name IS NULL AND new_folder_id IS NULL AND (SELECT folder_id FROM folder WHERE root_id = [RootID] AND id = event.folder_id) IS NOT NULL;

UPDATE event SET new_folder_id = (SELECT folder_id FROM folder WHERE root_id = [RootID] AND id = event.folder_id) WHERE root_id = [RootID] AND type IN (6, 7) AND new_name LIKE 'invalid_event%' AND new_folder_id IS NULL AND (SELECT folder_id FROM folder WHERE root_id = [RootID] AND id = event.folder_id) IS NOT NULL;

UPDATE event SET new_name = (SELECT name FROM file WHERE root_id = [RootID] AND id = event.file_id) WHERE root_id = [RootID] AND type IN (0, 3) AND new_name IS NULL;

UPDATE event SET new_name = (SELECT name FROM file WHERE root_id = [RootID] AND id = event.file_id) WHERE root_id = [RootID] AND type IN (0, 3) AND new_name LIKE 'invalid_event%';

UPDATE event SET new_name = (SELECT name FROM folder WHERE root_id = [RootID] AND id = event.folder_id) WHERE root_id = [RootID] AND type IN (6, 7) AND new_name IS NULL;

UPDATE event SET new_name = (SELECT name FROM folder WHERE root_id = [RootID] AND id = event.folder_id) WHERE root_id = [RootID] AND type IN (6, 7) AND new_name LIKE 'invalid_event%';

Here is a description of what exactly this set of queries does:

Per our experience, 85% of deleted events in all cases belong to points 1 and 2. These are basically just leftovers.

If you would like to keep the obsolete history, you can use a slightly modified version of the script instead, where points 4 and 5 are excluded:

DELETE FROM change_source_user WHERE root_id = [RootID] AND event_id IN (SELECT id FROM event WHERE root_id = [RootID] AND file_id IS NOT NULL AND NOT EXISTS (select id from file WHERE root_id = [RootID] AND file.id = event.file_id));

DELETE FROM event WHERE root_id = [RootID] AND file_id IS NOT NULL AND NOT EXISTS (select id from file WHERE root_id = [RootID] AND file.id = event.file_id);

DELETE FROM event WHERE root_id = [RootID] AND folder_id IS NOT NULL AND NOT EXISTS (select id from folder WHERE root_id = [RootID] AND folder.id = event.folder_id);

DELETE FROM revision WHERE root_id = [RootID] AND file_id IS NOT NULL AND NOT EXISTS (select id from file WHERE root_id = [RootID] AND file.id = revision.file_id);

UPDATE event SET new_folder_id = (SELECT folder_id FROM file WHERE root_id = [RootID] AND id = event.file_id) WHERE root_id = [RootID] AND type IN (0, 3) AND new_name IS NULL AND new_folder_id IS NULL AND (SELECT folder_id FROM file WHERE root_id = [RootID] AND id = event.file_id) IS NOT NULL;

UPDATE event SET new_folder_id = (SELECT folder_id FROM file WHERE root_id = [RootID] AND id = event.file_id) WHERE root_id = [RootID] AND type IN (0, 3) AND new_name LIKE 'invalid_event%' AND new_folder_id IS NULL AND (SELECT folder_id FROM file WHERE root_id = [RootID] AND id = event.file_id) IS NOT NULL;

UPDATE event SET new_folder_id = (SELECT folder_id FROM folder WHERE root_id = [RootID] AND id = event.folder_id) WHERE root_id = [RootID] AND type IN (6, 7) AND new_name IS NULL AND new_folder_id IS NULL AND (SELECT folder_id FROM folder WHERE root_id = [RootID] AND id = event.folder_id) IS NOT NULL;

UPDATE event SET new_folder_id = (SELECT folder_id FROM folder WHERE root_id = [RootID] AND id = event.folder_id) WHERE root_id = [RootID] AND type IN (6, 7) AND new_name LIKE 'invalid_event%' AND new_folder_id IS NULL AND (SELECT folder_id FROM folder WHERE root_id = [RootID] AND id = event.folder_id) IS NOT NULL;

UPDATE event SET new_name = (SELECT name FROM file WHERE root_id = [RootID] AND id = event.file_id) WHERE root_id = [RootID] AND type IN (0, 3) AND new_name IS NULL;

UPDATE event SET new_name = (SELECT name FROM file WHERE root_id = [RootID] AND id = event.file_id) WHERE root_id = [RootID] AND type IN (0, 3) AND new_name LIKE 'invalid_event%';

UPDATE event SET new_name = (SELECT name FROM folder WHERE root_id = [RootID] AND id = event.folder_id) WHERE root_id = [RootID] AND type IN (6, 7) AND new_name IS NULL;

UPDATE event SET new_name = (SELECT name FROM folder WHERE root_id = [RootID] AND id = event.folder_id) WHERE root_id = [RootID] AND type IN (6, 7) AND new_name LIKE 'invalid_event%';

4. Once the database requests have been executed, deactivate Maintenance Mode of the root.  
You can do that from the list of roots in system console http://hostname/system/roots. Locate the root you need and click "More" → "Toggle Maintenance Mode".

Alternatively, use the following URL (where 2080 is the port of your AnchorServer backend):

http://hostname:2080/root/maintenance/[RootID]/0/

5. In some cases, especially when the number of invalid events is high, some of the clients might still remain stuck (unable to sync) as their local copy of the events sequence differs too much from the remote copy. For such cases, we recommended resetting the local rsub DBs on the affected machines after running the procedure above to ensure they will sync down the updated and correct event history. To do so, simply unregister and re-register the client on that machine.

SUPPORT | 720-204-4500 | 800-352-0248

• Contact Axcient Support at https://partner.axcient.com/login or call 800-352-0248
• Have you tried our Support chat for quick questions?
• Free certification courses are available in the Axcient x360Portal under Training
• Sign up for free, live,  one-on-one training to learn about Axcient products
• Subscribe to Axcient Status page for updates and scheduled maintenance

1755