Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
stfnnklv opened this issue Mar 5, 2020 · 6 comments
Open

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

stfnnklv opened this issue Mar 5, 2020 · 6 comments
Assignees
@ma1uta
Labels
bug Something isn't working

Comments

@stfnnklv
Copy link

stfnnklv commented Mar 5, 2020

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 stfnnklv changed the title MSC2134 hash lookup database unique index on mxid MSC2134 hash lookup database multiple 3pids mapping to a single mxid Mar 5, 2020
@stfnnklv
Copy link
Author

stfnnklv commented Mar 5, 2020 •
edited
Loading

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

@ma1uta ma1uta added the bug Something isn't working label Mar 22, 2020
@ma1uta ma1uta self-assigned this Mar 22, 2020
@ma1uta ma1uta closed this as completed in 5baeb42 Mar 22, 2020
@stfnnklv
Copy link
Author

stfnnklv commented Mar 22, 2020 •
edited
Loading

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

@ma1uta ma1uta reopened this Mar 29, 2020
@stfnnklv
Copy link
Author

stfnnklv commented Apr 9, 2020

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
Copy link
Owner

ma1uta commented May 6, 2020

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
Copy link
Owner

ma1uta commented May 6, 2020

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
Copy link
Author

stfnnklv commented May 7, 2020

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.

Craeckie referenced this issue in Craeckie/ma1sd Apr 8, 2022
@ginkel
Merge pull request #29 from tgbyte/dependabot/gradle/com.googlecode.l…
c283c99 
…ibphonenumber-libphonenumber-8.12.42

Bump libphonenumber from 8.12.41 to 8.12.42
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@ma1uta ma1uta

Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@ma1uta @stfnnklv