Problems upgrading v4 database to v5 on Heroku with PostgresSQL

[Matt Raible]

Hello everyone,

I’m trying to test upgrading my 21-Points Health application from JHipster v4 to v5. I believe I have all the code upgraded, so now I’m trying to upgrade my database. I pulled down my PostgreSQL database from Heroku using the steps outlined in http://www.jhipster-book.com/#!/news/entry/upgrading-21-points-health-from-v1-to-v4. 

In other words, I copied it locally using the following command:

heroku pg:pull HEROKU_POSTGRESQL_GRAY_URL health --app health-by-points

Then I changed application-prod.yml to point to this “health” database and modified the owner to match what JHipster has (twentyonepoints) rather than the owner (mraible) of the tables created by “heroku pg:pull”. 

Then I tried to start my (upgraded) app with “./gradlew -Pprod”.

Now it just hangs at the following:

:: JHipster 🤓  :: Running Spring Boot 2.0.4.RELEASE ::

:: https://www.jhipster.tech ::

<===========--> 90% EXECUTING [5m 2s]

> :bootRun

You can see I’ve been waiting for more than 5 minutes and nothing has happened. Any ideas?

Thanks,

Matt

[Matt Raible]

To determine if this is an issue with v4 vs v5, I switched to my project’s master branch, which is running in production and should match the database I pulled down. On startup, I see these messages re-occuring:

2018-08-27 12:12:59.173  INFO 15779 --- [           main] liquibase                                : Waiting for changelog lock....

2018-08-27 12:13:09.176 DEBUG 15779 --- [           main] liquibase                                : Executing QUERY database command: SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1

So I deleted it:

health=# select * from databasechangeloglock;

 id | locked |       lockgranted       |                                   lockedby

----+--------+-------------------------+-------------------------------------------------------------------------------

  1 | t      | 2018-04-23 02:11:11.742 | 8504f3f5-49bd-416d-a41a-5858e0790985.prvt.dyno.rt.heroku.com (172.17.103.106)

(1 row)

health=# delete from databasechangeloglock;

DELETE 1

Which causes startup to fail (as soon as the sql executes):

2018-08-27 12:13:09.176 DEBUG 15779 --- [           main] liquibase                                : Executing QUERY database command: SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1

2018-08-27 12:13:09.180  WARN 15779 --- [           main] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.context.ApplicationContextException: Unable to start embedded container; nested exception is java.lang.RuntimeException: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'securityConfiguration' defined in file [/Users/mraible/dev/21-points.v5/build/classes/java/main/org/jhipster/health/config/SecurityConfiguration.class]: Unsatisfied dependency expressed through constructor parameter 1; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'userDetailsService' defined in file [/Users/mraible/dev/21-points.v5/build/classes/java/main/org/jhipster/health/security/DomainUserDetailsService.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userRepository': Cannot create inner bean '(inner bean)#6cd2cb2' of type [org.springframework.orm.jpa.SharedEntityManagerCreator] while setting bean property 'entityManager'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name '(inner bean)#6cd2cb2': Cannot resolve reference to bean 'entityManagerFactory' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/jhipster/health/config/DatabaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.LockException: liquibase.exception.DatabaseException: Expected single row from liquibase.statement.core.SelectFromDatabaseChangeLogLockStatement@7323dce7 but got 0

I started it back up and now it works!

Because that worked, I checked out my "jhipster-v5.2.1” branch and tried again.

This time I got a validation exception, which is OK because I know how to fix it.

Caused by: liquibase.exception.ValidationFailedException: Validation Failed:

     1 change sets check sum

          config/liquibase/changelog/20170725033721_added_entity_Preferences.xml::20170725033721-1::jhipster was: 7:7f8bf324d16147bb5491793a9f9f5063 but is now: 7:ab5fbd1635aed85867914b4216bada20

I updated my database with the following and restarted.

update databasechangelog set md5sum=null;

Now I get the following error:

Caused by: org.postgresql.util.PSQLException: ERROR: constraint "fk_points_user_id" for relation "points" already exists

Adding some preConditions to some of my constraints changelog files fixed this:

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
    <!--
        Added the constraints for entity Points.
    -->
    <changeSet id="20170725030037-2" author="jhipster">
        <preConditions onFail="MARK_RAN">
            <not>
                <foreignKeyConstraintExists schemaName="public" foreignKeyName="fk_points_user_id"/>
            </not>
        </preConditions>
        <addForeignKeyConstraint baseColumnNames="user_id"
                                 baseTableName="points"
                                 constraintName="fk_points_user_id"
                                 referencedColumnNames="id"
                                 referencedTableName="jhi_user"/>
    </changeSet>
</databaseChangeLog>

… and now it all works! Thanks for listening, y’all. :)

One thing I noticed (with both v4 and v5) is that the client is re-built each time when using the -Pprod profile. Is that expected? It’s kinda annoying when you’re trying to test prod stuff.

Thanks,

Matt

[Julien Dubois]

Hi Matt,

Just concerning the database lock: this is caused by Liquibase, to prevent having to database updates at the same time. Probably you had one failure, which kept the lock.... We normally have Maven goals to remove this lock easily, but honestly I've never used it, I just delete it manually like you did!

Julien

--
You received this message because you are subscribed to the Google Groups "JHipster dev team" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jhipster-dev...@googlegroups.com.
To post to this group, send email to jhipst...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jhipster-dev/231B8A55-28AF-4217-9B05-92C40D35E4F2%40raibledesigns.com.
For more options, visit https://groups.google.com/d/optout.

--

Julien Dubois

Twitter: @juliendubois

[Matt Raible]

I’m experiencing this again trying to upgrade, but I can’t get past liquibase failing on startup.

Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'auditEventsEndpoint' defined in class path resource [org/springframework/boot/actuate/autoconfigure/audit/AuditEventsEndpointAutoConfiguration.class]: Unsatisfied dependency expressed through method 'auditEventsEndpoint' parameter 0; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'customAuditEventRepository' defined in file [/Users/mraible/dev/21-points/build/classes/java/main/org/jhipster/health/repository/CustomAuditEventRepository.class]: Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'persistenceAuditEventRepository': Cannot create inner bean '(inner bean)#615bad16' of type [org.springframework.orm.jpa.SharedEntityManagerCreator] while setting bean property 'entityManager'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name '(inner bean)#615bad16': Cannot resolve reference to bean 'entityManagerFactory' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/jhipster/health/config/LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.LockException: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: Error executing SQL select count(*) from public.databasechangeloglock: ERROR: current transaction is aborted, commands ignored until end of transaction block

...

Caused by: org.postgresql.util.PSQLException: ERROR: relation "databasechangeloglock" already exists

The difference this time is there are no rows in my “databasechangeloglock” table:

health=# select * from databasechangeloglock;

 id | locked | lockgranted | lockedby

----+--------+-------------+----------

(0 rows)

Any ideas?

Thanks,

Matt

[Matt Raible]

I figured it out! It seems I forgot to do this step that I referenced in my initial email:

> Then I changed application-prod.yml to point to this “health” database and modified the owner to match what JHipster has (twentyonepoints) rather than the owner (mraible) of the tables created by “heroku pg:pull”. 

Once I ran the following on all my tables, it worked.

alter table points owner to TwentyOnePoints;

I wish there was a way to change all the tables’ owners. Maybe there is?

I also found I had to change the hibernate_sequences owner.

alter sequence hibernate_sequence owner to TwentyOnePoints;

Cheers y’all - I’m heading to production!

Matt