v105.1.0 -> v105.2.0 Migration failed with PostgreSQL (embed JDBC) #316

orumin · 2019-10-21T08:09:07Z

Problem description

When jpsonic upgrading from v105.1.0 to v105.2.0, I got error message like bellow

liquibase.exception.MigrationFailedException: Migration failed for change set classpath:liquibase/jp105.2.0/add-order.xml::add-order_003::tesshu:
     Reason: liquibase.exception.DatabaseException: ERROR: relation "public.media_File" does not exist [Failed SQL: ALTER TABLE public."media_File" ADD _order INTEGER]
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.Liquibase.update(Liquibase.java:202) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.Liquibase.update(Liquibase.java:179) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:353) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:305) ~[liquibase-core-3.6.3.jar!/:na]
        at org.airsonic.player.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:25) ~[classes!/:10.5.0-SNAPSHOT]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1688) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1626) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:297) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:211) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1131) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1059) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:583) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:87) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:364) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1268) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:551) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:756) [spring-beans-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867) [spring-context-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) [spring-context-4.3.25.RELEASE.jar!/:4.3.25.RELEASE]
        at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:123) [spring-boot-1.5.22.RELEASE.jar!/:1.5.22.RELEASE]
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:666) [spring-boot-1.5.22.RELEASE.jar!/:1.5.22.RELEASE]
        at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:353) [spring-boot-1.5.22.RELEASE.jar!/:1.5.22.RELEASE]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:300) [spring-boot-1.5.22.RELEASE.jar!/:1.5.22.RELEASE]
        at org.springframework.boot.builder.SpringApplicationBuilder.run(SpringApplicationBuilder.java:135) [spring-boot-1.5.22.RELEASE.jar!/:1.5.22.RELEASE]
        at org.airsonic.player.Application.main(Application.java:229) [classes!/:10.5.0-SNAPSHOT]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_222]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_222]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_222]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_222]
        at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:48) [jpsonic.war:10.5.0-SNAPSHOT]
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:87) [jpsonic.war:10.5.0-SNAPSHOT]
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:50) [jpsonic.war:10.5.0-SNAPSHOT]
        at org.springframework.boot.loader.WarLauncher.main(WarLauncher.java:59) [jpsonic.war:10.5.0-SNAPSHOT]
Caused by: liquibase.exception.DatabaseException: ERROR: relation "public.media_File" does not exist [Failed SQL: ALTER TABLE public."media_File" ADD _order INTEGER]
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:356) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1229) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1211) ~[liquibase-core-3.6.3.jar!/:na]
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:600) ~[liquibase-core-3.6.3.jar!/:na]
        ... 47 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.media_File" does not exist
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266) ~[postgresql-42.2.5.jar!/:42.2.5]
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175) ~[commons-dbcp2-2.5.0.jar!/:2.5.0]
        at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175) ~[commons-dbcp2-2.5.0.jar!/:2.5.0]
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352) ~[liquibase-core-3.6.3.jar!/:na]
        ... 52 common frames omitted

Steps to reproduce

deploy this https://hub.docker.com/r/orumin/jpsonic docker image with docker-compose

version: '3'

services:
  jpsonic:
    image: orumin/jpsonic:105.1.0
    container_name: jpsonic
    environment:
      JAVA_OPTS: '-Duser.timezone=Asia/Tokyo -DDatabaseMysqlMaxlength=512 -DDatabaseConfigType=EMBED -DDatabaseConfigEmbedDriver=org.postgresql.Driver -DDatabaseConfigEmbedPassword=jpsonic -DDatabaseConfigEmbedUrl=jdbc:postgresql://db:5432/jpsonic?stringtype=unspecified -DDatabaseConfigEmbedUsername=jpsonic -DDatabaseUsertableQuote=\"'
    ports:
      - "4040:4040"
    ulimits:
      nofile:
        soft: "12280"
        hard: "12280"
    volumes:
      - /opt/jpsonic/data:/jpsonic/data:Z
      - /opt/jpsonic/playlists:/jpsonic/playlists:Z
      - /opt/jpsonic/podcasts:/jpsonic/podcasts:Z
      - /data/music:/jpsonic/music:z
    networks:
      - external_network
      - internal_network
    depends_on:
      - db
    restart: always

  db:
    image: postgres:9.5-alpine
    environment:
      POSTGRES_USER: jpsonic
      POSTGRES_PASSWORD: jpsonic
      POSTGRES_DB: jpsonic
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "postgres"]
    volumes:
      - /opt/jpsonic/postgres:/var/lib/postgresql/data
    networks:
      - internal_network
    restart: always

networks:
  external_network:
  internal_network:
    internal: true

using in normally
change docker image tag number from 105.1.0 to 105.2.0 in docker-compose.yml
docker-compose up -d
error produced

System information

Jpsonic version: v105.2.0
Operating system: host OS: Fedora 30
Operating system: guest OS base image: alpine 3.9
- Deails of my jpsonic docker image for more information https://github.com/orumin/jpsonic/blob/master/Dockerfile
Java version: OpenJDK 8.222.10-r0
Proxy server: Nginx 1.17
Client: Vivaldi latest stable
Language: Japanese

Additional notes

Include any extra notes here. Otherwise you may remove this section.

The text was updated successfully, but these errors were encountered:

orumin · 2019-10-21T08:13:32Z

I commit ALTER TABLE public."media_File" ADD _order INTEGER with psql command in manually.
Then, migration go to the next step, but error is reproduced with like below

liquibase.exception.MigrationFailedException: Migration failed for change set classpath:liquibase/jp105.2.0/add-order.xml::add-order_006::tesshu:
     Reason: liquibase.exception.DatabaseException: ERROR: relation "public.media_File" does not exist
  Position: 8 [Failed SQL: UPDATE public."media_File" SET _order = '-1' WHERE _order IS NULL]

I commit UPDATE public."media_File" SET _order = '-1' WHERE _order IS NULL with psql command and I confirmed _order column is set to '-1' in media_file table.
But error is continued and cannot start server.

orumin · 2019-10-23T08:21:45Z

This problem caused by PosgreSQL's table name handling.
i.e.
https://stackoverflow.com/questions/32758783/liquibase-how-to-set-default-schema-programaticaly-in-java/32758941#32758941

then, I try to solve that with below patch

diff --git a/jpsonic-main/src/main/resources/liquibase/jp105.2.0/add-order.xml b/jpsonic-main/src/main/resources/liquibase/jp105.2.0/add-order.xml
index 0c79f08a..3aebb5e9 100644
--- a/jpsonic-main/src/main/resources/liquibase/jp105.2.0/add-order.xml
+++ b/jpsonic-main/src/main/resources/liquibase/jp105.2.0/add-order.xml
@@ -9,7 +9,7 @@
 				<columnExists tableName="artist" columnName="_order" />
 			</not>
 		</preConditions>
-		<addColumn tableName="artist">
+		<addColumn tableName="artist" schemaName="public">
 			<column name="_order" type="int">
 				<constraints nullable="true" />
 			</column>
@@ -21,7 +21,7 @@
 				<columnExists tableName="album" columnName="_order" />
 			</not>
 		</preConditions>
-		<addColumn tableName="album">
+		<addColumn tableName="album" schemaName="public">
 			<column name="_order" type="int">
 				<constraints nullable="true" />
 			</column>
@@ -34,22 +34,22 @@
 					columnName="_order" />
 			</not>
 		</preConditions>
-		<addColumn tableName="media_File">
+		<addColumn tableName="media_File" schemaName="public">
 			<column name="_order" type="int">
 				<constraints nullable="true" />
 			</column>
 		</addColumn>
 	</changeSet>
 	<changeSet id="add-order_004" author="tesshu">
-		<addNotNullConstraint tableName="artist"
+		<addNotNullConstraint tableName="artist" schemaName="public"
 			columnName="_order" columnDataType="int" defaultNullValue="-1" />
 	</changeSet>
 	<changeSet id="add-order_005" author="tesshu">
-		<addNotNullConstraint tableName="album"
+		<addNotNullConstraint tableName="album" schemaName="public"
 			columnName="_order" columnDataType="int" defaultNullValue="-1" />
 	</changeSet>
 	<changeSet id="add-order_006" author="tesshu">
-		<addNotNullConstraint tableName="media_File"
+		<addNotNullConstraint tableName="media_File" schemaName="public"
 			columnName="_order" columnDataType="int" defaultNullValue="-1" />
 	</changeSet>

Although, this problem continued.

tesshucom · 2019-10-25T18:36:14Z

Let's investigate.
I will investigate in the near future with 9.5 .
It may take some time.
(In postgres 9.5.x are many bug fixes related to Alter Table.)

An urgent patch is to delete add-order_004, 005, 006.

The program inserts the value explicitly without relying on the default value.
Therefore, even if there are no restrictions, data integrity is guaranteed.

tesshucom · 2019-10-31T02:15:37Z

× media_File
〇 media_file
▂▅▇█▓▒░(‘ω’)░▒▓█▇▅▂　

@orumin
The modified version is released.
If you are in a hurry, run the query manually 😫

tesshucom added the probrem label Oct 27, 2019

tesshucom added this to the priority 1 milestone Oct 27, 2019

tesshucom self-assigned this Oct 27, 2019

tesshucom added the status: waiting-for-feedback We need additional information before we can continue label Oct 27, 2019

tesshucom added type: bug A general bug and removed probrem status: waiting-for-feedback We need additional information before we can continue labels Oct 31, 2019

tesshucom modified the milestones: priority 1, jpsonic 105.2.1 Oct 31, 2019

tesshucom pushed a commit that referenced this issue Oct 31, 2019

Fix #316

95ebc4f

tesshucom closed this as completed in ac86ea9 Nov 1, 2019

tesshucom added in: data Issues in data (jdbc, db, dao, liquibase) status: feedback-provided Feedback has been provided labels Apr 23, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

v105.1.0 -> v105.2.0 Migration failed with PostgreSQL (embed JDBC) #316

v105.1.0 -> v105.2.0 Migration failed with PostgreSQL (embed JDBC) #316

orumin commented Oct 21, 2019

orumin commented Oct 21, 2019

orumin commented Oct 23, 2019 •

edited

tesshucom commented Oct 25, 2019 •

edited

tesshucom commented Oct 31, 2019

v105.1.0 -> v105.2.0 Migration failed with PostgreSQL (embed JDBC) #316

v105.1.0 -> v105.2.0 Migration failed with PostgreSQL (embed JDBC) #316

Comments

orumin commented Oct 21, 2019

Problem description

Steps to reproduce

System information

Additional notes

orumin commented Oct 21, 2019

orumin commented Oct 23, 2019 • edited

tesshucom commented Oct 25, 2019 • edited

tesshucom commented Oct 31, 2019

orumin commented Oct 23, 2019 •

edited

tesshucom commented Oct 25, 2019 •

edited