FAQ  •  Register  •  Login

Using Serviio with MySQL

<<

drungrin

Serviio newbie

Posts: 9

Joined: Wed Jan 08, 2014 4:41 pm

Post Wed Jan 08, 2014 4:58 pm

Using Serviio with MySQL

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 :D):

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?)
Attachments
sql.zip
Modified SQL Scripts for MySQL
(9.45 KiB) Downloaded 183 times
<<

atc98092

User avatar

DLNA master

Posts: 3461

Joined: Fri Aug 17, 2012 10:22 pm

Location: Washington (the state)

Post Thu Jan 09, 2014 2:08 am

Re: Using Serviio with MySQL

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. 8-)
Dan

Panasonic ST30 Plasma, Samsung JU7100 4K TV, Sony BDP-S3500, Sharp 4K Roku TV, Insignia Roku TV, Roku 4, Ultra and Stick, Nvidia Shield, Yamaha RX-V583 AVR.
Primary server: Intel i5-6400, 8 gig ram, Windows 10 Pro, 18 TB hard drive space | Test server Windows 10 Pro, AMD Phenom II X4 965, 8 gig ram

HOWTO: Enable debug logging HOWTO: Identify media file contents
<<

zip

User avatar

Serviio developer / Site Admin

Posts: 17095

Joined: Sat Oct 24, 2009 12:24 pm

Location: London, UK

Post Thu Jan 09, 2014 5:36 am

Re: Using Serviio with MySQL

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

drungrin

Serviio newbie

Posts: 9

Joined: Wed Jan 08, 2014 4:41 pm

Post Thu Jan 09, 2014 9:58 am

Re: Using Serviio with MySQL

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.

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


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?

atc98092 wrote: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. 8-)


I did it all in less than a hour :D

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.
<<

npuser

DLNA master

Posts: 172

Joined: Mon Feb 06, 2012 3:44 am

Location: Michigan

Post Wed Feb 26, 2014 5:31 pm

Re: Using Serviio with MySQL

@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
<<

drungrin

Serviio newbie

Posts: 9

Joined: Wed Jan 08, 2014 4:41 pm

Post Thu Feb 27, 2014 11:24 am

Re: Using Serviio with MySQL

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

Iveky

User avatar

DLNA master

Posts: 329

Joined: Thu Jan 31, 2013 5:17 pm

Location: Split, Croatia

Post Thu Feb 27, 2014 1:28 pm

Re: Using Serviio with MySQL

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

DenyAll

DLNA master

Posts: 2249

Joined: Fri Mar 08, 2013 11:16 pm

Location: Adelaide, Australia

Post Mon Mar 03, 2014 10:09 am

Re: Using Serviio with MySQL

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

Change db_schema_url property to jdbc:mysql://<your mysql host>/<your database>?user=<your username>&password=<your password>
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
DenyAll
Panasonic Viera FX800A | Panasonic Viera CS610A | Sony PS4 | Sony PS3 | Panasonic DMP-BD79 | Yamaha RX-V500D | iPad | Windows 10 | Serviio 1.10.1 Pro
WinHelper | MediaInfo

Beta Tester, Moderator
Please do not PM me for support as any solution cannot be shared with others.
<<

zip

User avatar

Serviio developer / Site Admin

Posts: 17095

Joined: Sat Oct 24, 2009 12:24 pm

Location: London, UK

Post Mon Mar 03, 2014 5:03 pm

Re: Using Serviio with MySQL

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
<<

DenyAll

DLNA master

Posts: 2249

Joined: Fri Mar 08, 2013 11:16 pm

Location: Adelaide, Australia

Post Mon Mar 03, 2014 11:27 pm

Re: Using Serviio with MySQL

zip wrote:just as a note, you don't need to change the schema URL in a property, but pass the -DdbURL system property.
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....

You don't need 1.4.1 sql file, there have not been any DB changes for 1.4.1
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)?
DenyAll
Panasonic Viera FX800A | Panasonic Viera CS610A | Sony PS4 | Sony PS3 | Panasonic DMP-BD79 | Yamaha RX-V500D | iPad | Windows 10 | Serviio 1.10.1 Pro
WinHelper | MediaInfo

Beta Tester, Moderator
Please do not PM me for support as any solution cannot be shared with others.
<<

DenyAll

DLNA master

Posts: 2249

Joined: Fri Mar 08, 2013 11:16 pm

Location: Adelaide, Australia

Post Tue Mar 04, 2014 1:52 am

Re: Using Serviio with MySQL

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:

  Code:
2014-03-04 11:58:46,257 DEBUG [LibraryAdditionsCheckerThread] Found file 'Thor (2011).avi', checking if it's already in the Library
2014-03-04 11:58:46,257 DEBUG [MediaItemDAOImpl] Checking if DB already contains media item F:\Video\Movies\Thor (2011).avi
2014-03-04 11:58:46,257 DEBUG [MediaItemDAOImpl] Looking up a media item for file path: F:\Video\Movies\Thor (2011).avi, ignore case: true
2014-03-04 11:58:46,259 DEBUG [MediaItemDAOImpl] Media item F:\Video\Movies\Thor (2011).avi doesn't exist in DB yet
2014-03-04 11:58:46,259 DEBUG [LibraryAdditionsCheckerThread] File not in Library, will add it
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.
DenyAll
Panasonic Viera FX800A | Panasonic Viera CS610A | Sony PS4 | Sony PS3 | Panasonic DMP-BD79 | Yamaha RX-V500D | iPad | Windows 10 | Serviio 1.10.1 Pro
WinHelper | MediaInfo

Beta Tester, Moderator
Please do not PM me for support as any solution cannot be shared with others.
<<

drungrin

Serviio newbie

Posts: 9

Joined: Wed Jan 08, 2014 4:41 pm

Post Fri Mar 07, 2014 2:04 am

Re: Using Serviio with MySQL

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 :D.
<<

Xmantium

User avatar

DLNA master

Posts: 1418

Joined: Sat Jul 23, 2011 2:12 pm

Location: Manchester, UK

Post Fri Mar 07, 2014 8:46 am

Re: Using Serviio with MySQL

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
Sony Bravia 2010 52NX803 - Sony PS3 - Xbox 360
Serviio running on QNAP NAS 639 Pro - 1.6 Intel Dual Core - 2GB RAM and QNAP NAS 121 - 2Ghz ARM Marvell - 1GB RAM
Console using ServiiDroid for Android, ServiiMetro and ServiiWP for Windows Phone and WebUI for iOS
<<

atc98092

User avatar

DLNA master

Posts: 3461

Joined: Fri Aug 17, 2012 10:22 pm

Location: Washington (the state)

Post Fri Mar 07, 2014 1:59 pm

Re: Using Serviio with MySQL

Xmantium wrote: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. :lol: As someone with programming experience (not Java, though) I know some of my best improvements came from user suggestions. :D
Dan

Panasonic ST30 Plasma, Samsung JU7100 4K TV, Sony BDP-S3500, Sharp 4K Roku TV, Insignia Roku TV, Roku 4, Ultra and Stick, Nvidia Shield, Yamaha RX-V583 AVR.
Primary server: Intel i5-6400, 8 gig ram, Windows 10 Pro, 18 TB hard drive space | Test server Windows 10 Pro, AMD Phenom II X4 965, 8 gig ram

HOWTO: Enable debug logging HOWTO: Identify media file contents
<<

zip

User avatar

Serviio developer / Site Admin

Posts: 17095

Joined: Sat Oct 24, 2009 12:24 pm

Location: London, UK

Post Fri Mar 07, 2014 4:34 pm

Re: Using Serviio with MySQL

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
<<

Xmantium

User avatar

DLNA master

Posts: 1418

Joined: Sat Jul 23, 2011 2:12 pm

Location: Manchester, UK

Post Fri Mar 07, 2014 6:14 pm

Re: Using Serviio with MySQL

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!
Sony Bravia 2010 52NX803 - Sony PS3 - Xbox 360
Serviio running on QNAP NAS 639 Pro - 1.6 Intel Dual Core - 2GB RAM and QNAP NAS 121 - 2Ghz ARM Marvell - 1GB RAM
Console using ServiiDroid for Android, ServiiMetro and ServiiWP for Windows Phone and WebUI for iOS
<<

drungrin

Serviio newbie

Posts: 9

Joined: Wed Jan 08, 2014 4:41 pm

Post Fri Mar 07, 2014 6:37 pm

Re: Using Serviio with MySQL

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.
<<

drungrin

Serviio newbie

Posts: 9

Joined: Wed Jan 08, 2014 4:41 pm

Post Wed Mar 12, 2014 3:06 am

Re: Using Serviio with MySQL

@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:

  Code:
DELIMITER |

CREATE TRIGGER MEDIA_ITEM_BEFORE_INSERT BEFORE INSERT ON MEDIA_ITEM
  FOR EACH ROW BEGIN
    SET NEW.LC_FILE_PATH = LOWER(NEW.FILE_PATH);
  END;
|

CREATE TRIGGER MEDIA_ITEM_BEFORE_UPDATE BEFORE UPDATE ON MEDIA_ITEM
  FOR EACH ROW BEGIN
    SET NEW.LC_FILE_PATH = LOWER(NEW.FILE_PATH);
  END;
|

DELIMITER ;


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.
<<

npuser

DLNA master

Posts: 172

Joined: Mon Feb 06, 2012 3:44 am

Location: Michigan

Post Thu Mar 13, 2014 11:01 pm

Re: Using Serviio with MySQL

@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
<<

drungrin

Serviio newbie

Posts: 9

Joined: Wed Jan 08, 2014 4:41 pm

Post Thu Mar 13, 2014 11:20 pm

Re: Using Serviio with MySQL

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:

  Code:
DELIMITER |

CREATE TRIGGER MEDIA_ITEM_BEFORE_INSERT BEFORE INSERT ON MEDIA_ITEM
  FOR EACH ROW BEGIN
    SET NEW.LC_FILE_PATH = LOWER(NEW.FILE_PATH);
  END;
|

CREATE TRIGGER MEDIA_ITEM_BEFORE_UPDATE BEFORE UPDATE ON MEDIA_ITEM
  FOR EACH ROW BEGIN
    SET NEW.LC_FILE_PATH = LOWER(NEW.FILE_PATH);
  END;
|

DELIMITER ;


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

Return to General discussion

Who is online

Users browsing this forum: No registered users and 5 guests

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.
Designed by ST Software for PTF.