matrix-synapse 1.86.0 unable to launch: sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id

[JonBoyleCoding]

Describe the bug

The upgrade to matrix-synapse 1.86.0 is causing a failure when used on at least a number of systems outside of nix matrix-org/synapse#15809

sqlite3.IntegrityError: UNIQUE constraint failed: temp_user_filters.user_id is the specific error

I'm having this issue as well when I did the upgrade. I have identical output to the issue above. This version has been backported to 22.11 and 23.05.

This is an original software error, I do not know what can be done on this end - thought it was worth reporting here in case anyone has the same error.

Steps To Reproduce

Steps to reproduce the behavior:

1. Upgrade from matrix-synapse 1.85.2 to the current 23.05/22.11 commits
2. When service starts, the above issue happens causing the server to not be able to start

Expected behavior

Service is able to start.

Screenshots

If applicable, add screenshots to help explain your problem.

Additional context

I have currently locked my nix flake to commit to 4bbecb6 to keep at the previous version for now.

Notify maintainers

@Ma27 @fadenb @mguentner @Ralith @dandellion @sumnerevans

[anoadragon453]

The issue was introduced in Synapse v1.86.0. The solution is to rollback to v1.85.0 until a fix (wip: matrix-org/synapse#15817) is introduced in a new release; likely v1.87.0, expected to release July 4th 2023.

[egasimus]

Ouch, this just bit me. Figured a way to roll back with overrideAttrs, watch out - the build will run the whole Synapse test suite.

This is an original software error,

There is an element of the packaging community failing to catch it and delivering broken software as "stable", though.

[egasimus]

This rollback expression worked for me:

services.matrix-synapse.package = let
  version = "1.85.2";
  hash1 = "sha256-pFafBsisBPfpDnFYWcimUuBgfFVPZzLna3yHeqIBAAE";
  hash2 = "sha256-dnno+5Ma0YNYpmj3oZ5UG22uAanKwVT67BwQW+mHoFc=";
in pkgs.matrix-synapse.overrideAttrs (old: rec {
  inherit version;
  src = pkgs.fetchFromGitHub {
    owner = "matrix-org"; repo = "synapse"; rev = "v${version}"; hash = hash1;
  };
  cargoDeps = pkgs.rustPlatform.fetchCargoTarball {
    inherit src;
    name = "matrix-synapse-${version}"; hash = hash2;
  };
})

[JonBoyleCoding]

This is an original software error,

There is an element of the packaging community failing to catch it and delivering broken software as "stable", though.

Indeed. Presumably this works fine when not upgrading a database so a test for this would be tricky to implement.

Either way, at least there's a workaround.

[crawford]

@egasimus, you can avoid the rebuild by using an overlay to override the package with the version from the older, working release:

{
  nixpkgs.overlays = let
    pinned = import (builtins.fetchTarball {
      name = "nixos-22.11-working-matrix";
      url = "https://github.com/nixos/nixpkgs/archive/03091c9ffd7033ea855d0216111c4a9975c5780f.tar.gz";
      sha256 = "1xawkcf31r7a9hdrrqpsyz9c9lnchy2msv2pkcsvcxkxay9sjm7c";
    }) {
      config = config.nixpkgs.config;
    };
  in [
    (final: prev: {
      matrix-synapse = pinned.matrix-synapse;
    })
  ];
}

[Ma27]

Indeed. Presumably this works fine when not upgrading a database so a test for this would be tricky to implement.

Well, upgrades are of course tested by maintainers before merging this. However, we all use postgres I guess.

[egasimus]

Thanks for the tip @crawford! Overlays previously seemed like, well, overkill - but now I understand them a little better. Saved for future reference and looking forward to the upstream fix 👋

[Lakr233]

Same here, and going back for now.

[dali99]

SQLite is only acceptable for testing purposes
SQLite should not be used in
a production server

Maybe we should add a services.matrix-synapse.YesIWantToUseSqliteThisIsNotAProductionServer to at least notify old stateVersion users that their database is not production capable.

As an aside I feel bad for @dandellion who keeps getting mentioned in issues instead of me >.>

[crawford]

Where did you see that note about database recommendations? I've been using Synapse for years with a SQLite database. I didn't realize I was living on the edge.

[Ma27]

E.g. in https://matrix-org.github.io/synapse/latest/setup/installation.html#using-postgresql, but also at the very top is a brief note: https://matrix-org.github.io/synapse/latest/usage/configuration/config_documentation.html.

Maybe we should add a services.matrix-synapse.YesIWantToUseSqliteThisIsNotAProductionServer to at least notify old stateVersion users that their database is not production capable.

Adding a note to the manual is fine, but I don't think we should implement safe-guards for every single thing people might do wrong. This is IMHO not our responsibility as maintainers anymore.

[crawford]

Ah, thanks. It looks like those notes were added after I did my installation (added in 2021 and 2022). I'll have to figure out the migration process.

I think a note in the upgrade instructions of the manual would be sufficient - just something to retroactively draw attention to the deprecation upstream.

[dali99]

Adding a note to the manual is fine, but I don't think we should implement safe-guards for every single thing people might do wrong. This is IMHO not our responsibility as maintainers anymore.

Then we should remove the automatic switch on state version and force sqlite users to configure it themselves. The fact that we default to this behavior makes it a little our responsibility

[Ma27]

Then we should remove the automatic switch on state version and force sqlite users to configure it themselves. The fact that we default to this behavior makes it a little our responsibility

Which behavior specifically?

Also, sqlite is the upstream default as well:

Do note however that the database defaults to SQLite, which is not recommended for production usage.

(source: https://matrix-org.github.io/synapse/latest/usage/configuration/config_documentation.html)

[dali99]

nixpkgs/nixos/modules/services/matrix/synapse.nix

Line 473 in 0ad0cc5

default = if versionAtLeast config.system.stateVersion "18.03"

Since we no longer automatically provision the database, we can just remove this stateversion switch, default to postgres, and just fail early for sqlite users if not explicitly set.