database too large?

[elmegil]

<-------------------------Subsonic Help Request---------------------->
Problem Description:

My subsonic stopped working recently; I was able to use it in the car to my phone on Monday, but today it was not working, it would just spin and give network errors.

When I got home I did several things (see troubleshooting section below) and was unable to get it to work with any address, not my external nor my internal address. I get the following in the browser:

HTTP ERROR: 503

SERVICE_UNAVAILABLE
RequestURI=/

Powered by jetty://

I have gone through the logs and in subsonic_sh.log I find this:

java.sql.SQLException: java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]

I looked and my database is just barely shy of 2G in size:

# ls -l db/*.data
-rw-r--r-- 1 root root 2147483592 Jan 23 19:23 db/subsonic.data

2G is actually 2147483648 bytes. (1024 * 1024 * 1024 * 2).

I attempted to use the 64 bit java rather than the 32 bit java, by adding -d64 to the command line strings, but this has not helped, so I am assuming it must be a file system call that is complaining, not a limitation of the JVM itself.

I see that 4.7 has a "clean database" option, but I was unaware of a need to do that sort of thing and since I can't bring it online...


Troubleshooting Steps:

Verified I could not access via my external address with web browser
Verified I could not access via my internal address with web browser
rebooted my server
rebooted my wireless router
confirmed my dyndns.org settings
used canyouseeme.org to verify open port (yes)
Tried using -d64 on the java invocation to force 64 bit instead of 32 bit operation

My Day Job is in IT, and I've worked with Unix servers for 25 years. Just so you have some expectation of how technical you can be when you talk to me

Playback Application and version: Android and Browser (Chrome)
Subsonic Version: phone version is listed as 3.6 (android)
Server Version: subsonic.log:[2013-01-23 20:17:50,401] INFO VersionService - Resolved local Subsonic version to: 4.7
Hardware Platform: Open Solaris on x86 This can for all intents and purposes be treated like Linux. I know Linux enough to be able to translate command requests.
Java Memory Limit: 150m
Here are all the args:
argv[0]: /usr/jdk/instances/jdk1.6.0/bin/amd64/java
argv[1]: -Xmx150m
argv[2]: -Dsubsonic.home=/var/subsonic
argv[3]: -Dsubsonic.host=0.0.0.0
argv[4]: -Dsubsonic.port=4040
argv[5]: -Dsubsonic.httpsPort=0
argv[6]: -Dsubsonic.contextPath=/
argv[7]: -Dsubsonic.defaultMusicFolder=/var/music
argv[8]: -Dsubsonic.defaultPodcastFolder=/var/music/Podcast
argv[9]: -Dsubsonic.defaultPlaylistFolder=/var/playlists
argv[10]: -Djava.awt.headless=true
argv[11]: -verbose:gc
argv[12]: -jar
argv[13]: subsonic-booter-jar-with-dependencies.jar

Problem Filename: N/A
Output from ffmpeg -i: N/A
Last ten log file lines: I have omitted the java stack traces here

Code: Select all

29086 [main] ERROR org.mortbay.log - Failed startup of context org.mortbay.jetty.webapp.WebAppContext@52a34783{/,jar:file:/var/subsonic/subsonic.war!/}
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'podcastService' defined in ServletContext resource [/WEB-INF/applicationContext-service.xml]: Invocation of init method failed; nested exception is org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is java.sql.SQLException: java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]

Caused by: org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is java.sql.SQLException: java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
Caused by: java.sql.SQLException: java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
29089 [main] WARN org.mortbay.log - Nested in org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'podcastService' defined in ServletContext resource [/WEB-INF/applicationContext-service.xml]: Invocation of init method failed; nested exception is org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is java.sql.SQLException: java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]:
java.sql.SQLException: java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
29105 [main] INFO org.mortbay.log - Started SelectChannelConnector@0.0.0.0:4040
Subsonic running on: http://localhost:4040/
29134 [main] INFO org.springframework.remoting.rmi.RmiServiceExporter - Looking for RMI registry at port '9412'
29140 [main] INFO org.springframework.remoting.rmi.RmiServiceExporter - Could not detect RMI registry - creating new one
29166 [main] INFO org.springframework.remoting.rmi.RmiServiceExporter - Binding service 'SubsonicDeployerService' to RMI registry: RegistryImpl[UnicastServerRef [liveRef: [endpoint:[192.168.0.100:9412](local),objID:[0:0:0, 0]]]]

a typical stack trace in its entirety:

Code: Select all

java.sql.SQLException: java.io.IOException: S1000 Data file size limit is reached in statement [insert into media_file (id, path, folder, type, format, title, album, artist, album_artist, disc_number, track_number, year, genre, bit_rate, variable_bit_rate, duration_seconds, file_size, width, height, cover_art_path, parent_path, play_count, last_played, comment, created, changed, last_scanned, children_last_updated, present, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
        at org.hsqldb.jdbc.Util.throwError(Unknown Source)
        at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
        at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:858)
        at net.sourceforge.subsonic.dao.AbstractDao.update(AbstractDao.java:71)
        at net.sourceforge.subsonic.dao.MediaFileDao.createOrUpdateMediaFile(MediaFileDao.java:151)
        at net.sourceforge.subsonic.service.MediaFileService.getMediaFile(MediaFileService.java:118)
        at net.sourceforge.subsonic.service.MediaFileService.getMediaFile(MediaFileService.java:79)
        at net.sourceforge.subsonic.service.MediaFileService.getMediaFile(MediaFileService.java:140)
        at net.sourceforge.subsonic.service.PodcastService.addMediaFileIdToEpisodes(PodcastService.java:213)
        at net.sourceforge.subsonic.service.PodcastService.getEpisodes(PodcastService.java:184)
        at net.sourceforge.subsonic.service.PodcastService.init(PodcastService.java:100)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1414)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1375)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1335)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
        at java.security.AccessController.doPrivileged(Native Method)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:429)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380)
        at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:255)
        at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:199)
        at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:45)
        at org.mortbay.jetty.handler.ContextHandler.startContext(ContextHandler.java:530)
        at org.mortbay.jetty.servlet.Context.startContext(Context.java:135)
        at org.mortbay.jetty.webapp.WebAppContext.startContext(WebAppContext.java:1218)
        at org.mortbay.jetty.handler.ContextHandler.doStart(ContextHandler.java:500)
        at org.mortbay.jetty.webapp.WebAppContext.doStart(WebAppContext.java:448)
        at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
        at org.mortbay.jetty.handler.HandlerWrapper.doStart(HandlerWrapper.java:117)
        at org.mortbay.jetty.Server.doStart(Server.java:217)
        at org.mortbay.component.AbstractLifeCycle.start(AbstractLifeCycle.java:40)
        at net.sourceforge.subsonic.booter.deployer.SubsonicDeployer.deployWebApp(SubsonicDeployer.java:131)
        at net.sourceforge.subsonic.booter.deployer.SubsonicDeployer.<init>(SubsonicDeployer.java:65)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:100)
        at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:61)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateBean(AbstractAutowireCapableBeanFactory.java:877)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:839)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:440)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
        at java.security.AccessController.doPrivileged(Native Method)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:264)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:429)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:728)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:380)
        at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
        at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
        at net.sourceforge.subsonic.booter.Main.<init>(Main.java:24)
        at net.sourceforge.subsonic.booter.Main.main(Main.java:63)
29105 [main] INFO org.mortbay.log - Started SelectChannelConnector@0.0.0.0:4040
Subsonic running on: http://localhost:4040/

<-------------------------Subsonic Help Request---------------------->

[gurutech]

May need to upgrade java to 1.7.

[hakko]

This is an excellent error report! I wish more people took the time to be this clear.

The root cause of this is that Subsonic uses an old, outdated database called HSQLDB. The version is (last time I checked) 1.8.0.7 which dates back to 2006. The database itself seems quite fragile and there has obviously been bugs discovered (and some of them fixed) since 2006.

If you google for "hsqldb 2 gb", you get a variety of results. It seems that you can increase the 2GB limit, but to be honest, that just seems like delaying the next crash if your database is steadily growing and has now reached the size of 2 GB.

The best solution would be to convince Sindre to start using something better (at least the latest version of HSQLDB, to get the last years of bug fixes). I for one don't like HSQLDB at all so I've forked the project and run it on PostgreSQL instead.

To help you keep going with your current install, I recommend this thread which explains how to make a backup of your db (from the latest savepoint), scratch the database and import your users etc: viewtopic.php?f=6&t=10338

[hakko]

May need to upgrade java to 1.7.

Why would this help?

[elmegil]

Thanks folks. I did computer support for a number of years, so I appreciate the value of a good problem report, and I try to do it right myself. Glad to see I was successful

Most of the posts about increasing the size for HSQLDB involve "SET FILES SCALE 8" (which should let you go to 16Gb).

For some reason that isn't a recognized command:

java.sql.SQLException: error in script file line: 87 Unexpected token: FILES in statement [SET FILES]

However they also mention hsqldb.cache_file_scale which is in the subsonic.properties file, set to 1.

I tried setting it to 8, and got out of memory errors, go figure

Setting it to 2 also gets out of memory errors, unfortunately:

java.sql.SQLException: error in script file line: 58 out of memory

I've looked at line 58 of the script file and don't see anything special about it. I tried setting the cache properties lower to reduce the memory footprint and that hasn't helped.



I've tried the database recovery methods, but instead of selecting specific things to include from the script file, I've been trimming out things that seem obviously a problem, I can get it to come up and I can log in, but I get no actual data about the albums (no random albums, no navigation, but it DOES say "1,328 artists
2,928 albums
31,515 songs
258.86 GB
3,727 hours" at the bottom.

I'm going to mess with it further and may fall back to the other method of recovery.

Thanks again, this is much farther than I could have gotten on my own.

[elmegil]

Clean DB has recovered the artist list on the left, random is still not functioning correctly. But if I look at the log, it's still doing a lot of stuff, so I should probably wait before being too concerned....

[BKKKPewsey]

The random won't work until the indexing has finished.
However there does seem to be a problem with your db size as it's way too big.
My 4.7 db with over 125k songs (1.4TB) is just under 200MB so something is making your db grow.
You may want to have a look via db.view and see if you have some (a lot) of entries that shouldn't be there.
What you could do is backup/rename your old db then do a rescan (eg. create a new db) and compare the size difference.

[elmegil]

Is there a way to back up the database when the database won't let you start the service?

[daneren2005]

Is there a way to back up the database when the database won't let you start the service?

You can backup the database when the service isn't started just fine since it is stored in a single file. The only problem is it is a kind of worthless backup since it doesn't work with the service at this moment anyways. It can still be useful for size comparison though and possibly restoring it back if you later find a different solution.