Error loading any music?

[theVDude]

Exception org.springframework.jdbc.BadSqlGrammarException Message PreparedStatementCallback; bad SQL grammar [select id, path, folder, type, format, title, 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, last_modified, last_scanned, children_last_updated, present, version from media_file where type=? and present order by created desc limit ? offset ?]; nested exception is java.sql.SQLException: Column not found: LAST_MODIFIED in statement [select id, path, folder, type, format, title, 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, last_modified, last_scanned, children_last_updated, present, version from media_file where type=? and present order by created desc limit ? offset ?] Java version Sun Microsystems Inc. 1.6.0_24 Operating system Linux 3.2.0-2-686-pae Server jetty-6.1.x Memory Used 54 of 78 MB Stack trace org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select id, path, folder, type, format, title, 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, last_modified, last_scanned, children_last_updated, present, version from media_file where type=? and present order by created desc limit ? offset ?]; nested exception is java.sql.SQLException: Column not found: LAST_MODIFIED in statement [select id, path, folder, type, format, title, 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, last_modified, last_scanned, children_last_updated, present, version from media_file where type=? and present order by created desc limit ? offset ?] at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:220) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:670) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:678) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:710) at net.sourceforge.subsonic.dao.AbstractDao.query(AbstractDao.java:83) at net.sourceforge.subsonic.dao.MediaFileDao.getNewestAlbums(MediaFileDao.java:206) at net.sourceforge.subsonic.service.MediaFileService.getNewestAlbums(MediaFileService.java:271) at net.sourceforge.subsonic.controller.HomeController.getNewest(HomeController.java:159) at net.sourceforge.subsonic.controller.HomeController.handleRequestInternal(HomeController.java:95) at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:166) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:81) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:173) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:271) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:110) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:249) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at net.sourceforge.subsonic.filter.ResponseHeaderFilter.doFilter(ResponseHeaderFilter.java:47) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at net.sourceforge.subsonic.filter.RequestEncodingFilter.doFilter(RequestEncodingFilter.java:43) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at net.sourceforge.subsonic.filter.ParameterDecodingFilter.doFilter(ParameterDecodingFilter.java:54) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at net.sourceforge.subsonic.filter.BootstrapVerificationFilter.doFilter(BootstrapVerificationFilter.java:55) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181) at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:712) at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:139) at org.mortbay.jetty.Server.handle(Server.java:313) at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:506) at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:830) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:514) at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211) at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:381) at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:396) at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:442) Caused by: java.sql.SQLException: Column not found: LAST_MODIFIED in statement [select id, path, folder, type, format, title, 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, last_modified, last_scanned, children_last_updated, present, version from media_file where type=? and present order by created desc limit ? offset ?] at org.hsqldb.jdbc.Util.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source) at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1322) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:585) ... 64 more

No matter what I use as my media directory, this is what I get. :/ Anyone have any reason why?

[timoreimann]

The error states that the column LAST_MODIFIED is missing which shouldn't be the case. (At least, it exists on my installation of Supersonic.)

What version of Supersonic are you running? Did you install from git or somewhere else?

[timoreimann]

Also, could you please check your database as to whether the column is really missing? This page explains how to execute SQL statements against your HSQLDB instance. Just fire something like

SELECT TOP 1 * FROM media_file

and let us know about the returning heading line, i.e., the available columns. (Database content isn't needed; there just seems to be no easy way to retrieve a HSQLDB table schema via SQL which is why I'm proposing to go via a SELECT statement.)

[timoreimann]

@theVDude, please open up a separate issue regarding setting the home directory so that we can take care of things.

[theVDude]

Doesn't work if I have the home directory as /home/robert/.supersonic, that's when it stops working. Looking into what you said.

[theVDude]

Last modified is most definitely not there, I just installed it from git, cloned it about 30 minutes ago or. I did do "sudo vim /usr/bin/supersonic" and changed the home directory there.

[theVDude]

Okay, so I uninstalled, reinstalled, shut it down, and did "sudo cp -r /var/supersonic /home/robert/.supersonic && sudo chown -R robert:robert /home/robert/.supersonic", edited /usr/bin/supersonic to point to the new directory, and it worked. Now to see if I can get it to work with my old .subsonic folder so I don't have to make everyone's accounts again.

[theVDude]

Apparently subsonic 4.7b1 didn't create that field, so I can't use my old .subsonic directory.

[timoreimann]

You could try to extend the table schema by the missing column manually. A few weeks ago, I played around with Sqltool which would be one of many tools to use for such a purpose. Maybe you want to give it a try and see how far it goes.

[theVDude]

I'm not deleting my .subsonic directory. I think just adding it might be enough, if not filling it with any date would suffice. I'll look into it!

[theVDude]

ACTUALLY RESOLVED! from db.view, you can do 'ALTER TABLE media_file ADD COLUMN last_modified DATETIME BEFORE last_scanned' and it'll add the column which lets it work again. Something else is wrong with it now, but it's a start!

[timoreimann]

Glad you managed to figure it out -- thanks for sharing!

[theVDude]

ACTUALLY FULLY RESOLVED:

ALTER TABLE media_file ADD COLUMN last_modified TIMESTAMP DEFAULT NOW NOT NULL BEFORE last_scanned

The last modified times will be off, but it will scan properly now and you will let you browse your library again.