Synapse: Add MySQL-Support

[krombel]

I would like to use a MySQL-Database as backend for synapse.

In #123 I can see that someone worked on the support for MySQL but switched then to PostgreSQL.

What was the reason for that? May it be possible to add support for synapse now?

Thank you

[ChazyTheBest]

I would also like MySQL support.

[ara4n]

https://matrix.org/jira/browse/SYN-404 has the details of why synapse hasn't got mysql support, and why we don't have any plans to implement it any time soon.

We can't stop other people from trying though :)

[MurzNN]

Seems that the blocker is:

Synapse requires the ability to store full UTF8, which means using the MySQL charset utf8mb4 (not utf8, which only works for 3 byte utf8). MySQL only supports binary or case insensitive collations for utf8mb4, so we are forced to use binary collations.

But new stable MySQL 5.5 version have full support of utf8mb4 collation, not only binary, but utf8mb4_unciode_ci and other ways. Does now we can implement MySQL backend support in Synapse?

[ShellCode33]

It'd be great indeed :) I'm also using MySQL and forced to use sqlite which I'm pretty sure slows down performance

[ara4n]

If someone wanted to go through porting the schema & queries to mysql they'd be welcome to. however, synapse does not have a backend abstraction built into it currently, and i strongly suspect this would be a lot of fragile work without much benefit. we certainly wouldn't be able to support it from the core team.

[Protected]

It's useful when you have existing mysql/mariadb infrastructure that you could use but no postgres infrastructure.

[ptman]

what is SYN-404 by github issue id?

[aggarwaldev]

@ara4n Can we please reconsider support for mysql?
mysql 8 has brought massive performance boost to the DB.

[V1TA5]

+1

[bmiller59]

I recommend supporting mysql by implementing the backend abstraction / ORM approach @ara4n mentioned earlier. A backend abstraction would open the door to other data backends in the future, which I think will be desirable.

[menturion]

+1
Can't believe that there is no kind of abstraction layer.

[mandrillmaze]

+1

[ptman]

@menturion the layer will be available quicker if someone writes it

And please, no +1 comments, use reactions instead

[bmiller59]

@ptman Could someone who is familiar with the code base provide a quick high level outline of a strategy to efficiently implement a backend data layer abstraction? That might inspire someone to take up the challenge.

[pmdevita]

I'm potentially interested in taking this up, can someone give me an overview of what would need to be done?

[FreeManNumOne]

@pmdevita Are you adding mysql storage?

[richvdh]

I'm going to close this. It's unlikely we'd accept PRs to add mysql support to synapse due to the maintenance overhead this would add.

[army1349]

I'm going to close this. It's unlikely we'd accept PRs to add mysql support to synapse due to the maintenance overhead this would add.

Is there now maintenance overhead because of SQLite+PostgreSQL?

[ptman]

AFAIK sqlite doesn't have strong typing and postgresql is closer to sqlite than mysql.

[richvdh]

Is there now maintenance overhead because of SQLite+PostgreSQL?

yes, and it's significant. adding a third database would make the situation even worse.

[army1349]

I took a look and now I understand. This was never designed for multiple databases.

[chpatton013]

yes, and it's significant. adding a third database would make the situation even worse.

You really should reconsider this position. Putting in the leg-work to use an abstraction layer for database access will solve this problem for all future implementations, not just the one you like at the moment.

[karolyi]

SQLAlchemy might be the perfect tool to abstract off the postgresql-specific queries into an actual ORM that is DB-independent:

https://www.sqlalchemy.org/features.html