Problem description

This issue might come up after updating to Private Cloud 3.6.x. Thick and Thin Clients 3.6.x releases fix the way many sync events are handled in the desktop client. In some cases, this exposes some old/hidden issues with older team shares that had malformed 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

Below is a manual sanitization procedure to clean these malformed sync events and resolve the database inconsistencies. Scroll further down for an automated procedure.

Manually 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);

Manual sanitization steps

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

1. Put the root you wish to fix into Maintenance Mode so that it remains unchanged by users while you modify the database. This will prevent any deviations or other issues with the database you are modifying. To do that as a system admin, go to the list of roots http://hostname/system/roots, then search by root ID, locate the root you need, and click "More" -> "Toggle Maintenance Mode".
Alternatively, you can use the following URL to activate Maintenance Mode (where 2080 is the port of your AnchorServer backend): http://hostname:2080/root/maintenance/[RootID]/1/

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 database requests from this attached script to sanitize the root, replacing "[RootID]" with the root ID number without brackets: Sanitation_annotated_v26-04-2024_no_history_clen.sql

4. Every step in the script has a description of what exactly this query does. Optional steps in there are commented out and should not be executed normally. This updated version of the script keeps the history of the obsolete events and does its best to align it in such a way as not to block the sync. If you still have the sync blocked after running these queries, please ask x360Sync support for assistance.

5. Once the database requests have been executed, deactivate the Maintenance Mode of the root.

You can do that from the list of roots in the system console http://hostname/system/roots: locate the root you need and click "More" -> "Toggle Maintenance Mode".

Alternatively, you can use the following URL to activate Maintenance Mode (where 2080 is the port of your AnchorServer backend): http://hostname:2080/root/maintenance/[RootID]/0/

6. 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 sequence of the events 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.

Below is an automated sanitization procedure to clean malformed sync events and resolve the database inconsistencies.

Please note: the tools mentioned here are included in Private Cloud 3.6.10 and newer. Perform an update to utilize them.

Root sanitization script (locate and clean up invalid events)

Overview

The parameters of the script are listed below. Because the configuration file with the portal connection described sometimes has no constant place, we can set it with --portal-config key. By default, the script searches this config in conf\config.ini and if we run the script from the root directory of the Anchor Server, we don’t need to set it explicitly.

Usage

sanitize.py [-h] [--portal-config PORTAL_CONFIG] [--non-interactive] root_id

Sanitize root databases based on root ID. Requires psycopg2-binary and python3.

 positional arguments:

  root_id               Root ID for the database

 optional arguments:

  -h, --help            Show this help message and exit

  --portal-config PORTAL_CONFIG

Path to the config that contains the db with filestores

  --non-interactive     Run without interactive prompts

  --check                Only check whether the root has issues that require sanitization

Script can be started by using commands.

Check the root for invalid events and display detected issues in the log:

call C:\Anchor~1\penv\Scripts\python.exe C:\Anchor~1\web\lib\sanitize\sanitize.py --portal-config C:\Anchor~1\conf\config.ini --check 1

(where 1 is the root ID)

Clean up the database from invalid events found on the previous stage:

call C:\Anchor~1\penv\Scripts\python.exe C:\Anchor~1\web\lib\sanitize\sanitize.py --portal-config C:\Anchor~1\conf\config.ini --non-interactive 1

(where 1 is the root ID)

Auto-sanitize

For the automatization process there is a written batch file that searches for specific errors in the server logs, detects roots where these errors occur and starts the sanitize.py script on those roots. It should be located in the same place as the script itself (C:\Anchor~1\web\lib\sanitize\).

Example

Script can be started by using this command:

auto-sanitize.bat \O \D C

where

\O Check mode to ONLY check whether the root has issues that require sanitization and show the result (false by default)

\D DRIVE_LETTER - The drive where Anchor Server is installed (C by default)

Schedule auto-sanitize

If single runs of auto-sanitize do not resolve all issues with invalid events on your environment, you can schedule this auto-sanitize script to run regularly. For that, use commands as shown in the following examples (if the file is located elsewhere in your case, modify the path accordingly).

Start the script each day at 08:00:

SCHTASKS /Create /SC DAILY /ST 08:00 /TN "DailySanitizerTask" /TR "C:\Anchor~1\web\lib\sanitize\auto-sanitize.bat /D C"

Start the script each Monday at at 08:00:

SCHTASKS /Create /SC WEEKLY /D MON /ST 08:00 /TN "EachMondaySanitizerTask" /TR "C:\Anchor~1\web\lib\sanitize\auto-sanitize.bat /D C"

Start the script every hour:

SCHTASKS /Create /SC HOURLY /TN "HourlySanitizerTask" /TR "C:\Anchor~1\web\lib\sanitize\auto-sanitize.bat /D C"

Start the script each Wednesday and Saturday:

SCHTASKS /Create /SC WEEKLY /D WED,SAT /TN "WeeklySanitizerTask" /TR "C:\Anchor~1\web\lib\sanitize\auto-sanitize.bat /D C"

Start the script every 5 days:

SCHTASKS /Create /SC DAILY /MO 5 /TN "Every5DaysSanitizerTask" /TR "C:\Anchor~1\web\lib\sanitize\auto-sanitize.bat /D C"

Other parameters may be found in the official documentation on scheduling tasks on Windows Server.

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
• Subscribe to Axcient Status page for updates and scheduled maintenance

1809