can't update database

[sigel]

MusicCabinet configuration

It seems like you recently installed or upgraded MusicCabinet. Press 'Upgrade database' to load the latest version.

After I click Update Database I get this:

http://pastebin.com/nTfyggsu


Error

Subsonic encountered an internal error. You can report this error in the Subsonic Forum. Please include the information below.
Exception org.springframework.jdbc.BadSqlGrammarException
Message StatementCallback; bad SQL grammar [create or replace function load_1000() returns int as $$ begin -- Util schema: meta functionality for database create schema util; create table util.musiccabinet_version (update_id integer not null, insert_time timestamp not null default now()); -- Music schema: abstract information on artist, tracks, (global) ratings and relations create schema music; create table music.tag (id serial primary key, tag_name text not null); create table music.artist (id serial primary key, artist_name text); create table music.track (artist_id integer references music.artist (id), id serial primary key, track_name text); create table music.trackrelation (source_id integer references music.track (id) not null, target_id integer references music.track (id) not null, weight float not null); create table music.trackrelation_import (source_id integer not null, target_id integer references music.track (id), target_artist_name text not null, target_track_name text not null, target_artist_id integer references music.artist (id), weight float not null); create table music.artistrelation (source_id integer references music.artist (id) not null, target_id integer references music.artist (id) not null, weight float not null); create table music.artistrelation_import (source_id integer not null, target_id integer references music.artist (id), target_artist_name text not null, weight float not null); create table music.artisttoptrack (artist_id integer references music.artist (id) not null, track_id integer references music.track (id) not null, rank smallint not null); create table music.artisttoptrack_import (artist_id integer references music.artist (id) not null, track_id integer references music.track (id), track_name text not null, rank smallint not null); create table music.artisttoptag (artist_id integer references music.artist (id) not null, tag_id integer references music.tag (id) not null, tag_count smallint not null); create table music.artisttoptag_import (artist_id integer references music.artist (id) not null, tag_id integer references music.tag (id), tag_name text not null, tag_count smallint not null); create unique index artistrelation_sourceid_targetid on music.artistrelation (source_id, target_id); create unique index tag_tagname_id on music.tag (tag_name, id); create unique index artisttoptag_tagid_artistid_tagcount on music.artisttoptag (tag_id, artist_id, tag_count); -- Library schema: personal information, such as local music files, personal ratings, invoked web service calls create schema library; create table library.musicfile (track_id integer references music.track (id) not null, id serial primary key, path text, created timestamp, last_modified timestamp, external_id text); create table library.musicfile_import (artist_name text, artist_id integer references music.artist (id), track_name text, track_id integer references music.track (id), path text, created timestamp, last_modified timestamp, external_id text); create table library.musicdirectory (artist_id integer references music.artist (id) not null, id serial primary key, path text, root boolean); create table library.musicdirectory_import (artist_id integer references music.artist (id), artist_name text not null, path text, root boolean); create table library.trackplaycount (track_id integer references music.track (id) not null, play_count integer); create table library.trackplaycount_import (artist_name text, artist_id integer references music.artist (id), track_name text, track_id integer references music.track (id), play_count integer); create table library.webservice_calltype (id smallint primary key, description text); create table library.webservice_history (artist_id integer references music.artist (id), track_id integer references music.track (id), calltype_id smallint references library.webservice_calltype (id), page smallint, invocation_time timestamp default now()); create table library.artisttoptrackplaycount (music_file_id integer references library.musicfile (id) not null, artist_id integer not null references music.artist (id), rank smallint not null, play_count integer not null); insert into library.webservice_calltype (id, description) values (0, 'Last.fm library.getTracks (used for track playcount)'); insert into library.webservice_calltype (id, description) values (1, 'Last.fm artist.getSimilar'); insert into library.webservice_calltype (id, description) values (2, 'Last.fm artist.getTopTracks'); insert into library.webservice_calltype (id, description) values (3, 'Last.fm track.getSimilar'); insert into library.webservice_calltype (id, description) values (4, 'Last.fm artist.getTopTags'); create unique index musicfile_path_trackid on library.musicfile (path, track_id); create unique index musicdirectory_path_artistid on library.musicdirectory (path, artist_id); insert into util.musiccabinet_version (update_id) values (1000); return 0; end; $$ language plpgsql;]; nested exception is org.postgresql.util.PSQLException: ERROR: language "plpgsql" does not exist Hint: Use CREATE LANGUAGE to load the language into the database.
Java version Oracle Corporation 1.7.0_09
Operating system Linux 2.6.32-042stab059.7
Server jetty/8.y.z-SNAPSHOT
Memory Used 57 of 91 MB
Stack trace org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create or replace function load_1000() returns int as $$ begin -- Util schema: meta functionality for database create schema util; create table util.musiccabinet_version (update_id integer not null, insert_time timestamp not null default now()); -- Music schema: abstract information on artist, tracks, (global) ratings and relations create schema music; create table music.tag (id serial primary key, tag_name text not null); create table music.artist (id serial primary key, artist_name text); create table music.track (artist_id integer references music.artist (id), id serial primary key, track_name text); create table music.trackrelation (source_id integer references music.track (id) not null, target_id integer references music.track (id) not null, weight float not null); create table music.trackrelation_import (source_id integer not null, target_id integer references music.track (id), target_artist_name text not null, target_track_name text not null, target_artist_id integer references music.artist (id), weight float not null); create table music.artistrelation (source_id integer references music.artist (id) not null, target_id integer references music.artist (id) not null, weight float not null); create table music.artistrelation_import (source_id integer not null, target_id integer references music.artist (id), target_artist_name text not null, weight float not null); create table music.artisttoptrack (artist_id integer references music.artist (id) not null, track_id integer references music.track (id) not null, rank smallint not null); create table music.artisttoptrack_import (artist_id integer references music.artist (id) not null, track_id integer references music.track (id), track_name text not null, rank smallint not null); create table music.artisttoptag (artist_id integer references music.artist (id) not null, tag_id integer references music.tag (id) not null, tag_count smallint not null); create table music.artisttoptag_import (artist_id integer references music.artist (id) not null, tag_id integer references music.tag (id), tag_name text not null, tag_count smallint not null); create unique index artistrelation_sourceid_targetid on music.artistrelation (source_id, target_id); create unique index tag_tagname_id on music.tag (tag_name, id); create unique index artisttoptag_tagid_artistid_tagcount on music.artisttoptag (tag_id, artist_id, tag_count); -- Library schema: personal information, such as local music files, personal ratings, invoked web service calls create schema library; create table library.musicfile (track_id integer references music.track (id) not null, id serial primary key, path text, created timestamp, last_modified timestamp, external_id text); create table library.musicfile_import (artist_name text, artist_id integer references music.artist (id), track_name text, track_id integer references music.track (id), path text, created timestamp, last_modified timestamp, external_id text); create table library.musicdirectory (artist_id integer references music.artist (id) not null, id serial primary key, path text, root boolean); create table library.musicdirectory_import (artist_id integer references music.artist (id), artist_name text not null, path text, root boolean); create table library.trackplaycount (track_id integer references music.track (id) not null, play_count integer); create table library.trackplaycount_import (artist_name text, artist_id integer references music.artist (id), track_name text, track_id integer references music.track (id), play_count integer); create table library.webservice_calltype (id smallint primary key, description text); create table library.webservice_history (artist_id integer references music.artist (id), track_id integer references music.track (id), calltype_id smallint references library.webservice_calltype (id), page smallint, invocation_time timestamp default now()); create table library.artisttoptrackplaycount (music_file_id integer references library.musicfile (id) not null, artist_id integer not null references music.artist (id), rank smallint not null, play_count integer not null); insert into library.webservice_calltype (id, description) values (0, 'Last.fm library.getTracks (used for track playcount)'); insert into library.webservice_calltype (id, description) values (1, 'Last.fm artist.getSimilar'); insert into library.webservice_calltype (id, description) values (2, 'Last.fm artist.getTopTracks'); insert into library.webservice_calltype (id, description) values (3, 'Last.fm track.getSimilar'); insert into library.webservice_calltype (id, description) values (4, 'Last.fm artist.getTopTags'); create unique index musicfile_path_trackid on library.musicfile (path, track_id); create unique index musicdirectory_path_artistid on library.musicdirectory (path, artist_id); insert into util.musiccabinet_version (update_id) values (1000); return 0; end; $$ language plpgsql;]; nested exception is org.postgresql.util.PSQLException: ERROR: language "plpgsql" does not exist Hint: Use CREATE LANGUAGE to load the language into the database. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428) at com.github.hakko.musiccabinet.dao.jdbc.JdbcDatabaseAdministrationDao.loadDatabaseUpdate(JdbcDatabaseAdministrationDao.java:208) at com.github.hakko.musiccabinet.service.DatabaseAdministrationService.loadNewDatabaseUpdates(DatabaseAdministrationService.java:105) at net.sourceforge.subsonic.controller.MusicCabinetSettingsController.doSubmitAction(MusicCabinetSettingsController.java:70) at org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:449) at org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:415) at org.springframework.web.servlet.mvc.SimpleFormController.onSubmit(SimpleFormController.java:387) at org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:272) at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:268) 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:900) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789) at javax.servlet.http.HttpServlet.service(HttpServlet.java:755) at javax.servlet.http.HttpServlet.service(HttpServlet.java:848) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:648) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1336) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:311) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:101) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:146) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:173) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1307) at net.sourceforge.subsonic.filter.RequestEncodingFilter.doFilter(RequestEncodingFilter.java:43) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1307) at net.sourceforge.subsonic.filter.ParameterDecodingFilter.doFilter(ParameterDecodingFilter.java:54) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1307) at net.sourceforge.subsonic.filter.BootstrapVerificationFilter.doFilter(BootstrapVerificationFilter.java:54) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1307) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:453) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137) at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:559) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1072) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:382) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1006) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116) at org.eclipse.jetty.server.Server.handle(Server.java:365) at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:485) at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:937) at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:998) at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:856) at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:240) at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82) at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:627) at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:51) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543) at java.lang.Thread.run(Thread.java:722) Caused by: org.postgresql.util.PSQLException: ERROR: language "plpgsql" does not exist Hint: Use CREATE LANGUAGE to load the language into the database. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366) at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:108) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:421) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396) ... 75 more

[hakko]

Are you running PostgreSQL 9.1+ as required by the installation instructions? The last time somebody reported this error, it turned out they were running CentOS default version of 8 something.

[sigel]

Upgraded, new message now is:

org.springframework.dao.DataAccessResourceFailureException
StatementCallback; SQL [select load_1026()]; ERROR: could not open extension control file "/usr/share/postgresql/9.1/extension/pg_trgm.control": No such file or directory Where: SQL statement "create extension if not exists pg_trgm" PL/pgSQL function "load_1026" line 2 at SQL statement; nested exception is org.postgresql.util.PSQLException: ERROR: could not open extension control file "/usr/share/postgresql/9.1/extension/pg_trgm.control": No such file or directory Where: SQL statement "create extension if not exists pg_trgm" PL/pgSQL function "load_1026" line 2 at SQL statement

[hakko]

I don't know what OS you're running, but PostgreSQL for Linux flavors seems to be distributed without additional modules used by MusicCabinet. From the install page:

Linux users might have to run

> sudo apt-get install postgresql-contrib

or equiv. Windows and Mac users are fine with the default installation.

So, depending on your package manager, you have to somehow get that extra module. I have a very high confidence for people running Linux to know their way around so the guidelines are a bit sparse.

[sigel]

ok tried that, but now its telling me I have 8.4 is started again, I'll just have to wait for my sysadmin to come online and do this.. sorry to be a pain, I wouldn't be using the normal subsonic if it wasn't for the easy .deb package If I had the connection at home i'd be running it on my windows, but its way faster on the linux server

[hakko]

No problem, maybe I should say sorry for distributing software that's not exactly a 1-click installer. If I had a company doing this, there would be installers for everyone.. but now's it just a hobby project and I've prioritized working on the code base, leaving the head-aches of getting it running on Raspberries and NASes etc to each and everyone. But I agree it's a bit tough as you can't exactly Google search the error messages, it assumes some experience tweaking shell scripts and Java.

[resno]

What distro are you running?

From what I see, you've gotta get version 9.

[sigel]

Hey guys, just wanted to say i had psql9.1 reinstalled for me and its working fine now. Waiting for scan to finish, very excited!

Thanks for the help everyone

[sigel]

Anyone use this with DSub? In Library mode when I click on an artist it lists the albums for a different artist than I clicked on, seems to be off by like 2 or so spots.

[shadow.8]

Anyone use this with DSub? In Library mode when I click on an artist it lists the albums for a different artist than I clicked on, seems to be off by like 2 or so spots.

I use Music Cabinet daily with dSub and I haven't had any issues like that...

[sigel]

good to hear, so its just on my end...the app did update today for me

[hakko]

If you were previously using vanilla Subsonic from the same dSub, it could be a caching thing (if it cached your artists from vanilla and now uses the same ids when fetching data from MusicCabinet). Could you try first going to Radio or Genres (and configure what genres to use in your genre cloud), and then go back to dSub and switch between media folders? The media folders in dSub should then be exchanged for a list of the genres you've chosen in the web UI. When switching between them, it should force reading the artist list from MusicCabinet again.

[sigel]

That did it sir, thank you. wow this is cool, didn't think I was gonna have the new features in the app as well.

[hakko]

Yup I decided it would be smarter to allow people to stay with iSub/dSub as their authors have put in a lot of effort already and do a great job. I could write my own app but I think it's more efficient to focus on the server side. (but ideally, we should start working even more together, currently I squeeze my new functionality into the existing API. But I could of course add new API methods too)

Now just make sure to add a lot of music to your server! The artist radio and the genre radio will work better the more you have.

And there's a hidden feature at the bottom of the artist list, "Find missing albums" that allows you to import MusicBrainz data to help you find missing albums in your discography. Getting missing albums will also do for a better artist radio as the database gets more tracks to choose from.