Jump to content

MySQL Support


dcrdev
Abobader
Message added by Abobader,

Last warning: Attacking other members & team will cause a ban for your account.

Recommended Posts

13 minutes ago, cascan07454 said:

After reading more of this thread's older posts I'm feeling quite a bit less confident that this is something Emby's Dev Team plans to roll out in anything approaching a reasonable amount of time. I'm just going to move to Jellyfin. Their Git commits leave a lot more to be excited about than what I'm seeing in Emby's beta changelog...even more so compounded by the pretty negative responses from the devs here.

Probably doesn't matter though since I paid for a lifetime rather than month-to-month, but this is another household of users lost to Emby...

Hi, I'm sorry to hear this. For the record, we have never said no to this and I'm certainly not opposed to it. We just currently have other things on our plate that we think more users want. That's not to say that this might not get it's turn at some point.

Link to comment
Share on other sites

1 hour ago, Luke said:

Hi, I'm sorry to hear this. For the record, we have never said no to this and I'm certainly not opposed to it. We just currently have other things on our plate that we think more users want. That's not to say that this might not get it's turn at some point.

That's awesome to hear! Hope it becomes a reality!

Link to comment
Share on other sites

 

12 hours ago, dcol said:

My take on this is more from a maintenance perspective. The way the Emby database works is fine when it works, but when it has some issues it would be much easier fixing with a better database than having to recreate a library with 250K entries which I have had to do countless times and is very frustrating and time consuming. Emby's database gets confused too easily when changing directory locations or moving things around between libraries. My vote is for easing the maintenance rather than for speed.

Why do you think that a different database backend would make any change to the cause of the problem you are describing?

Link to comment
Share on other sites

dcol

Because, for me, I know MySQL and could work with it. If I try to make changes now, it always screws up Emby.

Link to comment
Share on other sites

14 hours ago, cascan07454 said:

Currently SQLite. But they openly discuss the plans to add MariaDB/MySQL and other drop in DB plugins once they upgrade their version of .NET and EF respectively. My point being they have it on the roadmap and actually seem intent on pursuing it...I'm hoping Emby is ACTUALLY planning to do the same...

 

3 hours ago, cascan07454 said:

After reading more of this thread's older posts I'm feeling quite a bit less confident that this is something Emby's Dev Team plans to roll out in anything approaching a reasonable amount of time. I'm just going to move to Jellyfin. Their Git commits leave a lot more to be excited about than what I'm seeing in Emby's beta changelog...even more so compounded by the pretty negative responses from the devs here.

Probably doesn't matter though since I paid for a lifetime rather than month-to-month, but this is another household of users lost to Emby...

I find it kind of sad to hear that somebody would want to make decisions solely based on promises without substance.
My personal view on this has always been that users would value distinct responses with detailed reasoning more than indeterminate promises for the future.

I also never said 'no' to this. I just tried to provide some insight into the decision-making process to make it more transparent and better understandable. Im starting to doubt whether it was worth to respond at all and whether it would have been better to just let the promise stand alone that it might come at some time in the future.

A major new part that is coming to Emby soon is built on EF core with more than two dozen data tables. This is not just an "intent on pursuing" - it already exists. It's not a replacement for the library db and the primary motivation is not about changing the database implementation. But when you are looking for steps into a direction where db independence would be possible, then there is one.

Link to comment
Share on other sites

9 minutes ago, dcol said:

Because, for me, I know MySQL and could work with it. If I try to make changes now, it always screws up Emby.

I think I know what you mean, but this would rather require a different database model instead of a different database engine. 🙂 

  • Like 1
Link to comment
Share on other sites

15 minutes ago, softworkz said:
27 minutes ago, dcol said:

Because, for me, I know MySQL and could work with it. If I try to make changes now, it always screws up Emby.

I think I know what you mean, but this would rather require a different database model instead of a different database engine. 🙂 

BTW: To be honest - users intending to make their own changes to the database is more like a horror-scenario from a developer's perspective..

No matter whether we would support lots of or just a single database engine: the best way will always be to look at the situation you are describing and try to find a built-in way to deal with it.

Link to comment
Share on other sites

22 minutes ago, rsvg said:

Not related to their reasoning, but there's no built in way to horizontally scale an sqlite db

Please describe what kind of setup you mean and the intention behind.

Link to comment
Share on other sites

multiple nodes all using the same (non-embedded) database. Ability to scale the parts of the system independently.

Link to comment
Share on other sites

Just now, rsvg said:

multiple nodes all using the same (non-embedded) database. Ability to scale the parts of the system independently.

Hi.  While that is all technically really cool, for what purpose in a personal media server used by just you and your family?

  • Agree 1
Link to comment
Share on other sites

32 minutes ago, rsvg said:

multiple nodes all using the same (non-embedded) database. Ability to scale the parts of the system independently.

You mean multiple Emby Server installations accessing the same DB? It doesn't work like this.
A common database is surely a prerequisite for this, but the much bigger part is the server development which is required to have such setups working properly. 

Everybody who is interested in that kind of functionality should stop asking for multiple db engine support!

Make a new request about what you really want.

By requesting multi-db support, you won't get there.

Link to comment
Share on other sites

dcol

There is no need for me to change databases. I was just venting my frustrations on the one currently available. It is real touchy and you all know that. How many of us had to rebuild new libraries when multiple changes are made. If the libraries are left alone it works fine. My point on wanting a better database.

Link to comment
Share on other sites

24 minutes ago, dcol said:

There is no need for me to change databases. I was just venting my frustrations on the one currently available. It is real touchy and you all know that. How many of us had to rebuild new libraries when multiple changes are made. If the libraries are left alone it works fine. My point on wanting a better database.

Could you please describe which changes you are making (and how) which "screws up Emby"?

Link to comment
Share on other sites

1 hour ago, softworkz said:

You mean multiple Emby Server installations accessing the same DB? It doesn't work like this.
A common database is surely a prerequisite for this, but the much bigger part is the server development which is required to have such setups working properly. 

Everybody who is interested in that kind of functionality should stop asking for multiple db engine support!

Make a new request about what you really want.

By requesting multi-db support, you won't get there.

Yes, and we've had this discussion in this very thread before.  Simply switching database engines does not get you this kind of functionality.

Link to comment
Share on other sites

1 hour ago, softworkz said:

You mean multiple Emby Server installations accessing the same DB? It doesn't work like this.
A common database is surely a prerequisite for this, but the much bigger part is the server development which is required to have such setups working properly. 

Everybody who is interested in that kind of functionality should stop asking for multiple db engine support!

Make a new request about what you really want.

By requesting multi-db support, you won't get there.

Well, you said yourself that it would be a prerequisite. That’s why I’m personally requesting it. I think the other issues that would come from a multiple node setup might be able to be mitigated, such as running the “cron” jobs on only a single node. Admittedly though this is just an initial conversation on the first blocker I’ve come across in this theory. But as I mentioned above I’ve already worked around this personally. 

Link to comment
Share on other sites

sydlexius

I don't have much skin in this game, but I would love the ability to replicate my Emby DB to a reporting server.  While REST is useful in many cases for reporting, I'm much more confident with TSQL and PSQL.  I also wouldn't have to care about the implications of schema additions and modifications on a separate instance.

Link to comment
Share on other sites

dcol

@softworkz, I have placed many topics in the past on my issues with corruption  in the Emby database. Last time I think all I did was try to move a library to another drive and the entire library lost its metadata. Another time I deleted plugins and added others which also messed up the metadata. It seems as if any major changes to libraries the database cannot handle.

Link to comment
Share on other sites

3 minutes ago, rsvg said:

Well, you said yourself that it would be a prerequisite. That’s why I’m personally requesting it. I think the other issues that would come from a multiple node setup might be able to be mitigated, such as running the “cron” jobs on only a single node. Admittedly though this is just an initial conversation on the first blocker I’ve come across in this theory. But as I mentioned above I’ve already worked around this personally. 

Rest assured that it won't work in any way.

Multiple servers accessing the same database simultaneously can only come as a feature so please create a different feature request for this.

Link to comment
Share on other sites

3 minutes ago, dcol said:

@softworkz, I have placed many topics in the past on my issues with corruption  in the Emby database. Last time I think all I did was try to move a library to another drive and the entire library lost its metadata. Another time I deleted plugins and added others which also messed up the metadata. It seems as if any major changes to libraries the database cannot handle.

OK, let's not mix up topics too much, please PM me a link to one of these and I'll respond there.

Link to comment
Share on other sites

4 hours ago, sydlexius said:

I don't have much skin in this game, but I would love the ability to replicate my Emby DB to a reporting server.  While REST is useful in many cases for reporting, I'm much more confident with TSQL and PSQL.  I also wouldn't have to care about the implications of schema additions and modifications on a separate instance.

This is really easy.

Depending on your environment, you'll need to install one or another SQLite database provider. There are many - you need to know from where you want to work with the data.

Create a copy of library.db (you can do this while Emby is running)

Now you have a "snapshot" (the copy) and you can connect to it in whichever way you need.

 

Link to comment
Share on other sites

sydlexius
3 hours ago, softworkz said:

This is really easy.

Depending on your environment, you'll need to install one or another SQLite database provider. There are many - you need to know from where you want to work with the data.

Create a copy of library.db (you can do this while Emby is running)

Now you have a "snapshot" (the copy) and you can connect to it in whichever way you need.

 

My biggest annoyance with SQLite is the inability to use update queries + joins.  The omission of this capability boggles the mind.

Link to comment
Share on other sites

15 hours ago, sydlexius said:

My biggest annoyance with SQLite is the inability to use update queries + joins.  The omission of this capability boggles the mind.

This is no longer true. Since version 3.3, you can use UPDATE-FROM: https://www.sqlite.org/lang_update.html#upfrom

My personal #1 dislike is the lack of  typing for column data (all variant, the DDL types have just informative character)

Edited by softworkz
Link to comment
Share on other sites

But I would also like to make one thing very clear:

The Emby library database is NOT made to be safe for external modifications!

Please refrain from making any modifications to the data tables.

There exist different architectural models and approaches for how an application implements storage of data in a database.
I can't go through all aspects right now, just focusing on one aspect that is important to consider: database consistency

What is required to make a change to the database valid and consistent?

Consistency itself is a multifold matter - there's consistency at the database level, like indexes, keys and relations, which is ideally taken care by the dbs itself (1).
Then there can be consistency requirements from the model, e.g. like a new record in table A requires a related record in table B, otherwise it wouldn't be included in certain results (2). Finally there are consistency requirements at the applications level which are required by the business logic (3).

When you want to have a database where external modifications are desired, you either need to make sure that #2 and #3 are implemented at the DB level or that consistency requirements like #2 and #3 do not exist.

None of the above applies to the Emby database. There exist requirements like #2 and #3 and these are implemented and ensured by the application logic.

In turn, external modifications to the DB are very likely to cause inconsistent states and failure.

Summary

Reading Emby databases => Yay
Writing to Emby databases => Nay

Link to comment
Share on other sites

  • 1 month later...

I am no engeineer but i think this is exactly what i want emby to have too. I use my emby for both videos and photos and i have a massive library of photos (nearly 2 million). Emby is not usable with my library indexed, searching becomes.. yeah you guessed it not possible. Opening the shares that have any of the photo paths setup takes a massive amount of time etc. My guess is that it is the limitations of SQLLite (my database is vacuumed and over 11gb) that gives me this terrible performance while a real dedicated database host would be able to do more complex and smart filter queries making it possible to gain massive amounts of performance for users like me.

  • Like 3
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...