search

ma1uta / ma1sd

Federated Matrix Identity Server (formerly fork of kamax/mxisd)
GNU Affero General Public License v3.0
167 stars 55 forks source link

MSC2134 hash lookup database multiple 3pids mapping to a single mxid #29

Open stfnnklv opened 4 years ago

stfnnklv commented 4 years ago

Hello,

I noticed a unique key on mxid in table hashes. That way only one 3pid can be assigned to a single mxid. I have replaced that index with CREATE UNIQUE INDEX index_hashes_unique_1 ON hashes USING btree (mxid, medium, address); and it seems fine for the time being.

Kind regards, Stefan Nikolov

stfnnklv commented 4 years ago

On the other hand, in case the same 3pid (assigned to the same mxid) is obtained from more than one backend, the duplicating backend is entirely discarded.

Mar 05 22:21:29 matrix ma1sd[25927]: [pool-1-thread-1] ERROR io.kamax.mxisd.hash.HashEngine - Unable to update hashes of the provider: io.kamax.mxisd.backend.sql.synapse.SynapseSqlThreePidProvider@539c3e64
Mar 05 22:21:29 matrix ma1sd[25927]: java.lang.RuntimeException: java.sql.SQLException: Unable to run insert stmt on object io.kamax.mxisd.storage.ormlite.dao.HashDao@7a55d9c5: INSERT INTO "hashes" ("mxid" ,"medium" ,"address" ,"hash" ) VALUES (?,?,?,?)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:175)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.addHash(OrmLiteSqlStorage.java:398)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.hash.storage.SqlHashStorage.add(SqlHashStorage.java:25)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.hash.HashEngine.updateHashes(HashEngine.java:41)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.hash.rotation.HashRotationStrategy.trigger(HashRotationStrategy.java:14)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.lang.Thread.run(Thread.java:834)
Mar 05 22:21:29 matrix ma1sd[25927]: Caused by: java.sql.SQLException: Unable to run insert stmt on object io.kamax.mxisd.storage.ormlite.dao.HashDao@7a55d9c5: INSERT INTO "hashes" ("mxid" ,"medium" ,"address" ,"hash" ) VALUES (?,?,?,?)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:25)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:138)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.stmt.StatementExecutor.create(StatementExecutor.java:458)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.dao.BaseDaoImpl.create(BaseDaoImpl.java:329)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.lambda$addHash$18(OrmLiteSqlStorage.java:399)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:173)
Mar 05 22:21:29 matrix ma1sd[25927]:         ... 10 more
Mar 05 22:21:29 matrix ma1sd[25927]: Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "index_hashes_unique_1"
Mar 05 22:21:29 matrix ma1sd[25927]:   Detail: Key (mxid, medium, address)=(<redacted>, email, <redacted>) already exists.
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.jdbc.JdbcDatabaseConnection.insert(JdbcDatabaseConnection.java:188)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:91)
Mar 05 22:21:29 matrix ma1sd[25927]:         ... 14 more

Kind regards, Stefan Nikolov

stfnnklv commented 4 years ago

Thank you for fixing this. Unfortunately the second part of the issue is still present.

Log attached:

Mar 22 11:57:42 matrix ma1sd[32463]: [pool-1-thread-1] INFO io.kamax.mxisd.hash.engine.HashEngine - Start update hashes.
Mar 22 11:57:42 matrix ma1sd[32463]: [pool-1-thread-1] INFO io.kamax.mxisd.hash.engine.HashEngine - Populate hashes from the handler: io.kamax.mxisd.backend.ldap.LdapThreePidProvider
Mar 22 11:57:42 matrix ma1sd[32463]: [pool-1-thread-1] INFO io.kamax.mxisd.backend.ldap.LdapThreePidProvider - Found possible match, DN: uid=snikolov,cn=users,cn=accounts,dc=<redacted>,dc=<redacted>
Mar 22 11:57:42 matrix ma1sd[32463]: [pool-1-thread-1] DEBUG io.kamax.mxisd.hash.engine.HashEngine - Found 3PID: {"medium":"email","value":"snikolov@<redacted>","mxid":"@snikolov:<redacted>"}
Mar 22 11:57:42 matrix ma1sd[32463]: [pool-1-thread-1] INFO io.kamax.mxisd.hash.engine.HashEngine - Populate hashes from the handler: io.kamax.mxisd.backend.sql.synapse.SynapseSqlThreePidProvider
Mar 22 11:57:42 matrix ma1sd[32463]: [pool-1-thread-1] DEBUG io.kamax.mxisd.hash.engine.HashEngine - Found 3PID: {"medium":"email","value":"snikolov@<redacted>","mxid":"@snikolov:<redacted>"}
Mar 22 11:57:42 matrix ma1sd[32463]: [pool-1-thread-1] ERROR io.kamax.mxisd.hash.engine.HashEngine - Unable to update hashes of the provider: io.kamax.mxisd.backend.sql.synapse.SynapseSqlThreePidProvider@7c97cbe3
Mar 22 11:57:42 matrix ma1sd[32463]: java.lang.RuntimeException: java.sql.SQLException: Unable to run insert stmt on object io.kamax.mxisd.storage.ormlite.dao.HashDao@65525377: INSERT INTO "hashes" ("id" ,"mxid" ,"medium" ,"address" ,"hash" ) VALUES (?,?,?,?,?)
Mar 22 11:57:42 matrix ma1sd[32463]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:188)
Mar 22 11:57:42 matrix ma1sd[32463]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.addHash(OrmLiteSqlStorage.java:411)
Mar 22 11:57:42 matrix ma1sd[32463]:         at io.kamax.mxisd.hash.storage.SqlHashStorage.add(SqlHashStorage.java:25)
Mar 22 11:57:42 matrix ma1sd[32463]:         at io.kamax.mxisd.hash.engine.HashEngine.updateHashes(HashEngine.java:42)
Mar 22 11:57:42 matrix ma1sd[32463]:         at io.kamax.mxisd.hash.rotation.HashRotationStrategy.trigger(HashRotationStrategy.java:14)
Mar 22 11:57:42 matrix ma1sd[32463]:         at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
Mar 22 11:57:42 matrix ma1sd[32463]:         at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
Mar 22 11:57:42 matrix ma1sd[32463]:         at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
Mar 22 11:57:42 matrix ma1sd[32463]:         at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
Mar 22 11:57:42 matrix ma1sd[32463]:         at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
Mar 22 11:57:42 matrix ma1sd[32463]:         at java.base/java.lang.Thread.run(Thread.java:834)
Mar 22 11:57:42 matrix ma1sd[32463]: Caused by: java.sql.SQLException: Unable to run insert stmt on object io.kamax.mxisd.storage.ormlite.dao.HashDao@65525377: INSERT INTO "hashes" ("id" ,"mxid" ,"medium" ,"address" ,"hash" ) VALUES (?,?,?,?,?)
Mar 22 11:57:42 matrix ma1sd[32463]:         at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:25)
Mar 22 11:57:42 matrix ma1sd[32463]:         at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:138)
Mar 22 11:57:42 matrix ma1sd[32463]:         at com.j256.ormlite.stmt.StatementExecutor.create(StatementExecutor.java:458)
Mar 22 11:57:42 matrix ma1sd[32463]:         at com.j256.ormlite.dao.BaseDaoImpl.create(BaseDaoImpl.java:329)
Mar 22 11:57:42 matrix ma1sd[32463]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.lambda$addHash$18(OrmLiteSqlStorage.java:412)
Mar 22 11:57:42 matrix ma1sd[32463]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:186)
Mar 22 11:57:42 matrix ma1sd[32463]:         ... 10 more
Mar 22 11:57:42 matrix ma1sd[32463]: Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "hashes_mxid_medium_address_key"
Mar 22 11:57:42 matrix ma1sd[32463]:   Detail: Key (mxid, medium, address)=(@snikolov:<redacted>, email, snikolov@<redacted>) already exists.
Mar 22 11:57:42 matrix ma1sd[32463]:         at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
Mar 22 11:57:42 matrix ma1sd[32463]:         at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
Mar 22 11:57:42 matrix ma1sd[32463]:         at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
Mar 22 11:57:42 matrix ma1sd[32463]:         at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
Mar 22 11:57:42 matrix ma1sd[32463]:         at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
Mar 22 11:57:42 matrix ma1sd[32463]:         at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
Mar 22 11:57:42 matrix ma1sd[32463]:         at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)
Mar 22 11:57:42 matrix ma1sd[32463]:         at com.j256.ormlite.jdbc.JdbcDatabaseConnection.insert(JdbcDatabaseConnection.java:188)
Mar 22 11:57:42 matrix ma1sd[32463]:         at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:91)
Mar 22 11:57:42 matrix ma1sd[32463]:         ... 14 more

Kind regards, Stefan Nikolov

stfnnklv commented 4 years ago

Just noticed that I am unable to restart ma1sd.

[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: ChangelogDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: ThreePidInviteIO
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: HistoricalThreePidInviteIO
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: ThreePidSessionDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: ASTransactionDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: AccountDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: AcceptedDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Workaround for postgresql on dao: AcceptedDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Table exists, do nothing
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: HashDao
java.lang.RuntimeException: java.sql.SQLException: SQL statement failed: CREATE SEQUENCE "hashes_id_seq"
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:188)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.<init>(OrmLiteSqlStorage.java:111)
        at io.kamax.mxisd.Mxisd.build(Mxisd.java:122)
        at io.kamax.mxisd.Mxisd.start(Mxisd.java:229)
        at io.kamax.mxisd.HttpMxisd.start(HttpMxisd.java:100)
        at io.kamax.mxisd.MxisdStandaloneExec.main(MxisdStandaloneExec.java:109)
Caused by: java.sql.SQLException: SQL statement failed: CREATE SEQUENCE "hashes_id_seq"
        at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:25)
        at com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:423)
        at com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:399)
        at com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:383)
        at com.j256.ormlite.table.TableUtils.createTableIfNotExists(TableUtils.java:75)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.createDaoAndTable(OrmLiteSqlStorage.java:171)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.createDaoAndTable(OrmLiteSqlStorage.java:152)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.lambda$new$0(OrmLiteSqlStorage.java:120)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:186)
        ... 5 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "hashes_id_seq" already exists
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:138)
        at com.j256.ormlite.jdbc.JdbcCompiledStatement.runExecute(JdbcCompiledStatement.java:71)
        at com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:417)
        ... 12 more
[Thread-0] INFO App - ma1sd stopped

As a workaround I dropped hashes_id_seq.

Thank you, Stefan Nikolov

ma1uta commented 4 years ago

On the other hand, in case the same 3pid (assigned to the same mxid) is obtained from more than one backend, the duplicating backend is entirely discarded.

Mar 05 22:21:29 matrix ma1sd[25927]: [pool-1-thread-1] ERROR io.kamax.mxisd.hash.HashEngine - Unable to update hashes of the provider: io.kamax.mxisd.backend.sql.synapse.SynapseSqlThreePidProvider@539c3e64
Mar 05 22:21:29 matrix ma1sd[25927]: java.lang.RuntimeException: java.sql.SQLException: Unable to run insert stmt on object io.kamax.mxisd.storage.ormlite.dao.HashDao@7a55d9c5: INSERT INTO "hashes" ("mxid" ,"medium" ,"address" ,"hash" ) VALUES (?,?,?,?)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:175)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.addHash(OrmLiteSqlStorage.java:398)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.hash.storage.SqlHashStorage.add(SqlHashStorage.java:25)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.hash.HashEngine.updateHashes(HashEngine.java:41)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.hash.rotation.HashRotationStrategy.trigger(HashRotationStrategy.java:14)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
Mar 05 22:21:29 matrix ma1sd[25927]:         at java.base/java.lang.Thread.run(Thread.java:834)
Mar 05 22:21:29 matrix ma1sd[25927]: Caused by: java.sql.SQLException: Unable to run insert stmt on object io.kamax.mxisd.storage.ormlite.dao.HashDao@7a55d9c5: INSERT INTO "hashes" ("mxid" ,"medium" ,"address" ,"hash" ) VALUES (?,?,?,?)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:25)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:138)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.stmt.StatementExecutor.create(StatementExecutor.java:458)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.dao.BaseDaoImpl.create(BaseDaoImpl.java:329)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.lambda$addHash$18(OrmLiteSqlStorage.java:399)
Mar 05 22:21:29 matrix ma1sd[25927]:         at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:173)
Mar 05 22:21:29 matrix ma1sd[25927]:         ... 10 more
Mar 05 22:21:29 matrix ma1sd[25927]: Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "index_hashes_unique_1"
Mar 05 22:21:29 matrix ma1sd[25927]:   Detail: Key (mxid, medium, address)=(<redacted>, email, <redacted>) already exists.
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
Mar 05 22:21:29 matrix ma1sd[25927]:         at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.jdbc.JdbcDatabaseConnection.insert(JdbcDatabaseConnection.java:188)
Mar 05 22:21:29 matrix ma1sd[25927]:         at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:91)
Mar 05 22:21:29 matrix ma1sd[25927]:         ... 14 more

Kind regards, Stefan Nikolov

Triple (mxid, medium, address) should be unique. Could you explain what you means under "backend"?

ma1uta commented 4 years ago

Just noticed that I am unable to restart ma1sd.

[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: ChangelogDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: ThreePidInviteIO
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: HistoricalThreePidInviteIO
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: ThreePidSessionDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: ASTransactionDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: AccountDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: AcceptedDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Workaround for postgresql on dao: AcceptedDao
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Table exists, do nothing
[main] INFO io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage - Create the dao: HashDao
java.lang.RuntimeException: java.sql.SQLException: SQL statement failed: CREATE SEQUENCE "hashes_id_seq"
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:188)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.<init>(OrmLiteSqlStorage.java:111)
        at io.kamax.mxisd.Mxisd.build(Mxisd.java:122)
        at io.kamax.mxisd.Mxisd.start(Mxisd.java:229)
        at io.kamax.mxisd.HttpMxisd.start(HttpMxisd.java:100)
        at io.kamax.mxisd.MxisdStandaloneExec.main(MxisdStandaloneExec.java:109)
Caused by: java.sql.SQLException: SQL statement failed: CREATE SEQUENCE "hashes_id_seq"
        at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:25)
        at com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:423)
        at com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:399)
        at com.j256.ormlite.table.TableUtils.doCreateTable(TableUtils.java:383)
        at com.j256.ormlite.table.TableUtils.createTableIfNotExists(TableUtils.java:75)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.createDaoAndTable(OrmLiteSqlStorage.java:171)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.createDaoAndTable(OrmLiteSqlStorage.java:152)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.lambda$new$0(OrmLiteSqlStorage.java:120)
        at io.kamax.mxisd.storage.ormlite.OrmLiteSqlStorage.withCatcher(OrmLiteSqlStorage.java:186)
        ... 5 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "hashes_id_seq" already exists
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:138)
        at com.j256.ormlite.jdbc.JdbcCompiledStatement.runExecute(JdbcCompiledStatement.java:71)
        at com.j256.ormlite.table.TableUtils.doStatements(TableUtils.java:417)
        ... 12 more
[Thread-0] INFO App - ma1sd stopped

As a workaround I dropped hashes_id_seq.

Thank you, Stefan Nikolov

Could you please try run ma1sd on the last commit. Or the latest docker image

stfnnklv commented 4 years ago

Let's say you have both LDAP and SynapseSql identity lookup configured. If a user adds (or already has) the same 3pid in both databases, the mentioned exception occurs.

With the last commit I am able to restart ma1sd. Thanks.