IntegrityConstraintViolation when trying to modify inventory data of offline player through API

[ApocalypsjeNL]

When trying to modify the contents of the current data (inventory) of an offline Player, it fails to save the modifications due to an SQL constraint violation. When the player is online, the data modifies as intended and is correctly saved to the database.

Attached are an image of the current data snapshots of a player, first taken when modified when the player was online (snapshots 4-5) and when the player was offline (snapshot 1)

The thrown stacktrace:

[19:14:38 ERROR]: [HuskSync] Failed to set user data in the database
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'c75d7e5d-d9d2-43b3-8122-78b9899a7bdb-3d849e7f-04f3-41df-b486-...' for key 'PRIMARY'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:555) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:339) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at net.william278.husksync.libraries.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.libraries.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.database.MySqlDatabase.createSnapshot(MySqlDatabase.java:384) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.database.Database.addSnapshot(Database.java:180) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.sync.DataSyncer.addSnapshotToDatabase(DataSyncer.java:142) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.sync.DataSyncer.lambda$saveData$0(DataSyncer.java:118) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.event.EventDispatcher.lambda$fireEvent$0(EventDispatcher.java:46) ~[HuskSync-3.4.jar:?]
        at org.bukkit.craftbukkit.v1_19_R3.scheduler.CraftTask.run(CraftTask.java:101) ~[purpur-1.19.4.jar:git-Purpur-1985]
        at org.bukkit.craftbukkit.v1_19_R3.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:57) ~[purpur-1.19.4.jar:git-Purpur-1985]
        at com.destroystokyo.paper.ServerSchedulerReportingWrapper.run(ServerSchedulerReportingWrapper.java:22) ~[purpur-1.19.4.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]
        at java.lang.Thread.run(Thread.java:833) ~[?:?]

Output of /husksync status

> husksync status
[19:15:19 INFO]: HuskSync | System status report:
[19:15:19 INFO]: • Plugin Version: v3.4
[19:15:19 INFO]: • Platform Type: Bukkit
[19:15:19 INFO]: • Language: en-gb
[19:15:19 INFO]: • Minecraft Version: 1.19.4-R0.1-SNAPSHOT
[19:15:19 INFO]: • Java Version: 17.0.2
[19:15:19 INFO]: • Java Vendor: Oracle Corporation
[19:15:19 INFO]: • Sync Mode: Lockstep
[19:15:19 INFO]: • Delay Latency: 500ms
[19:15:19 INFO]: • Server Name: dev-game-1
[19:15:19 INFO]: • Database Type: MySQL
[19:15:19 INFO]: • Is Database Local: No
[19:15:19 INFO]: • Using Redis Sentinel: No
[19:15:19 INFO]: • Using Redis Password: No
[19:15:19 INFO]: • Redis Using Ssl: No
[19:15:19 INFO]: • Is Redis Local: No
[19:15:19 INFO]: • Data Types: husksync:inventory ✔, husksync:ender_chest ✔, husksync:advancements ✔, husksync:location ❌, husksync:health ✔, husksync:hunger ✔, husksync:game_mode ✔, husksync:potion_effects ✔, husksync:statistics ❌, husksync:experience ✔, husksync:persistent_data ✔

Java code that is used for the interaction with HuskSync

private BukkitHuskSyncAPI huskSyncAPI;

public HuskSyncHandler() {
    this.huskSyncAPI = BukkitHuskSyncAPI.getInstance();
}

// Called for online player modifications
public CompletableFuture<List<ItemStack>> clearInventory(Player player) {
    return this.clearInventory(this.huskSyncAPI.getUser(player));
}

// Called for offline player modifications
public CompletableFuture<List<ItemStack>> clearInventory(UUID uuid) {
    return this.huskSyncAPI.getUser(uuid).thenCompose(user -> user.map(this::clearInventory).orElseGet(() -> CompletableFuture.completedFuture(Collections.emptyList())));
}

private CompletableFuture<List<ItemStack>> clearInventory(User user) {
    CompletableFuture<List<ItemStack>> completableFuture = new CompletableFuture<>();

    this.huskSyncAPI.editCurrentInventory(user, items -> {
        DataSnapshot.Packed beforeModificationSnapshot =
                this.huskSyncAPI
                        .snapshotBuilder()
                        .saveCause(DataSnapshot.SaveCause.of("Plugin Modification Backup"))
                        .inventory(items)
                        .buildAndPack();

        this.huskSyncAPI.addSnapshot(user, beforeModificationSnapshot);

        List<ItemStack> drops = this.filterInventory(items.getContents());
        completableFuture.complete(drops);
    });

    return completableFuture;
}

[WiIIiam278]

Ah yeah. I see what's happening.

If editCurrentInventory can't resolve an online or networked user, it fetches their latest snapshot from the database (as it should). The issue is, I forgot that since editCurrentInventory for an online/networked user creates a new snapshot to reflect their absolute latest changes and fetching the latest snapshot doesn't, it's attempting to add a snapshot with the same ID as their last snapshot (with modifications applied) when editing an offline user.