Serviio

Hi, I'm using serviio with mysql. I have a DNS 320L with debian squeeze + serviio.

I'm using MySQL because my database 'll become very large, and the DNS 320L has only 256 MB ram, and I put my database on a AWS server that I own, so I don't need to bother with the memory usage of the database, and I can use general admin tools to tweak the data / database.

That was my need, you can use mysql for any reason you want.

Quick step-by-step for installing, after a clean install of serviio 1.4 (data migration is on your own ):

1. Stop Serviio
2. Go to lib folder (/usr/share/serviio/lib on my box) and put mysql-connector-java-X.Y.Z-bin.jar (I downloaded from http://dev.mysql.com/get/Downloads/Conn ... .28.tar.gz)
3. Open serviio.jar on the same folder with winrar (.jar is a zip)
4. Extract serviio.properties to somewhere and edit it
4.1. Change db_schema_url property to jdbc:mysql://<your mysql host>/<your database>?user=<your username>&password=<your password> (Reference syntax of the url at http://dev.mysql.com/doc/refman/5.0/en/ ... rties.html)
5. Put serviio.properties back to the .jar
6. Put my modified scripts on the sql folder of the .jar (overwrite all. If you want to use a prior version of Serviio, put only the relevant .sql files for your version)
7. Close winrar, start Serviio

I Hope it can help anyone.

(Edit: not fully funcional. Serviio rely on derby syntax, mainly FETCH FIRST 1 ROWS ONLY. Can the developers put it on the .properties file?)

This functionality has been requested in the past. I'm pleased to see someone has made it work. I am curious if it has caused any issues with the MediaBrowser?

If this can be accomplished without too much effort (I'm familiar with MySQL, so what's easy for me might not be for others), it opens the door to some sort of library administration tool, since Derby Embedded only allows a single connection, which Serviio itself uses. I may have to play with this on my test server.

Might be worth adding this to the wiki for better visibility.

Yes, it caused some issues with media browser, android client, dlna...

Needed to reverse enginner and alter some of the org.serviio.library.dao.* classes:

- Changed FETCH FIRST ROWS ONLY to MySQL compatible LIMIT on every usage
- Changed Derby function RANDOM() calls to MySQL RAND() calls

Working without erros so far.

I'm not using online media.

Bought the license yesterday, Serviio fits my needs.



I didn't put because it required reverse engineering. Can I share those modified classes or Can you put those as properties, so I don´t need to modify the classes on future releases?



I did it all in less than a hour

If you have the modified .class files, you only need to install mysql, alter the .jar (is a .zip) and the properties file. I think it is easy for most.
If you need to reverse the files on your own, may be hard.

@drungrin - Looks like you are not on beta tester group. The property changes are now added to 1.4.1 - do you mind joining the beta group and update the lib and/or sql scripts accordingly? I am very much interested on running serviio on mysql than derby.

thanks
NPuser

Sure. I'll do that today (after I research how to join the beta test group )

can I use mySql to browse serviio's library (metadata).
can you write few hints

Hi drungrin. I am attempting to test this with 1.4.1 beta. Sorry if this is a silly question - I know some SQL, but am new to MySQL. I am struggling with the step

I am using localhost:3306 as mysql host, and the username and password I have setup in MySQL, but what do you use as the database name (I have used serviio). Does this database need to exist prior to starting - if so how does one create it?

The error I get in the log is:

Can't create a new connection for jdbc:mysql://localhost:3306/serviio?user=xxx&password=xxx
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'serviio'

<Edit>ps. I also created a script-1.4.1.sql file as part of step 6

just as a note, you don't need to change the schema URL in a property, but pass the -DdbURL system property.

You don't need 1.4.1 sql file, there have not been any DB changes for 1.4.1

Thanks. Given you have to edit the serviio.jar file anyway, its no big deal either way... if on the other hand, the alternate sql files were included in serviio.jar as part of the distribution (in an alternative folder) then the user would not have to edit this file at all I know you didnt want to maintain them, but distribute them....

Thanks, wasn't sure whether it script selection was tied to the script file name. The one I created was simply a copy of the existing 1.4 one with a single line changed (that refers to the version number). Will remove it.

@drungrin - Still cannot get it working though (its on my test machine). Does serviio create the initial database or does it need to exist (if so how)?

Update - I hate when this happens. I got it working but not sure how. I used the -DdbURL system property. I also created a new Model in MYSQL Workbench called Serviio. I was also playing with the user name and password and the roles.

Anyway, is working in the sense it is adding data to the SQL library (where IS this file(s)... I cannot find it) however there is a problem - every time you do a refresh, or restart Serviio, the video items are added again (I have only tested with video so far). So far I have three copies of the same movie (for every movie) in the library. It doesn't appear to recognise that the item is already there. The log doesn't reveal much:

Is this happening for anyone else?

ps. As this thread relates to the beta testing rather than for the general population (and all participants are beta testers), I have moved it to the beta test area of the forum. I will move it back after if required.

Hi folks, it was carnival in Brazil and I was afk.

I'll check tomorrow with a clean install and check those issues, and check also if @DenyAll happens with me, on both the beta version and my patched version.

Too late to answer: You need the database already created .

Has anyone compared MySQL performance with H2 database?

I thought a java based database would be suitable for Serviio
Theres a very good comparison here - http://www.h2database.com/html/features.html#comparison

Zip has put this database on hold - https://bitbucket.org/xnejp03/serviio/i ... than-derby

OK, that looks impressive. Particularly the Mixed Mode operation. This would finally allow some sort of library administration application, since Serviio could access the DB in embedded mode and allow something else in as well.

I saw Zip's comments in the bitbucket entry, but I also see that they are now well beyond V 1.0 (currently Version 1.3.175). If the install concerns Zip had can be satisfied, I don't see a downside to this. Of course, I don't know anything about how Serviio is written, so I may be completely bass-ackwards. As someone with programming experience (not Java, though) I know some of my best improvements came from user suggestions.

The only problem is everyone would have to do a clean install - I can already see the backlash when people have to wait another day to index their TBs of movies.

Is speed the issue here? As I cache all the library responses anyway, so I don't think we'd see any massive performance improvements, especially when I implement this: https://bitbucket.org/xnejp03/serviio/i ... ations-was

Possibly changing the database on a major version, eg Serviio v2.0
H2 database looks to be best viable option.

Cant even imagine what new features will be by then!

There isn't really needed to do a clean install, since the database structure is the same, we can make serviio migrate the data.

Or we can write a wiki entry to manually do that, using squirrel sql + dbcopy plugin (I use them a lot)

http://sourceforge.net/projects/squirre ... 1702_2058/
http://dbcopyplugin.sourceforge.net/

I don't think H2 is a viable option since it isn't that popular.

For PHP, (din't did a deep research), you need to use ODBC, which isn't usually trivial (http://www.h2database.com/html/advanced ... dbc_driver).

One of the goals of MySQL port is to have a library management tool, and connectors for other platforms are easily available.

@DenyAll
That issue only happen on Windows. My Linux box is ok, on a test machine with windows it happened to me also.

The triggers on script script-1.0.sql were not created, I needed to create them manually with the same code from the script:



Just run this snippet as root after the database is initialized, before adding media folders to the library.

Unprivileged users cannot create triggers on some MySQL versions, but I'm using 5.6 that allows that.

I really dont know why the triggers weren't created.

Without those triggers the lc_file_path column isn't updated properly.

@drungrin - with added serviio db connection properties, do we still need to modify (reverse engineer) dao for this to work? Could you outline the steps for us so we can get this to work with mysql? please. I know steps are scattered through out this thread, it would make life easier if you could compile the steps, verify and post it here. We are more than eager to help you test/verify each step outlined if necessary.

Side note: Once we have the step outlined for MySQL, i am guessing we can use those steps as template to create a db in H2 as well.

thanks
NPuser

No need to reverse engineer using the 1.4.1 version.

You need to:
1. Add the -DdbURL=jdbc:mysql://127.0.0.1:3306/serviio?user=serviio&password=serviio parameter on the start script or .vmoptions on Windows (change host, db name and password to suit your environment)
2. Put my modified scripts on the sql folder of the .jar (download from first post)
3. Start serviio
4. Before any configuration, check on the MySQL database if the triggers for table media_item were created. If they are not there (My case and @DenyAll I suppose), run this script:



I still dont know why the triggers weren't created.