activejdbc migrator force to create `schema_version` table if it exits

Question

Today I am going to setup another development env by dumping the demo data from one server and restoring to another one. Also I copy the code base to the development server. When I run mvn validate which will invoke migration, it outputs

[INFO] Scanning for projects...
[INFO]
[INFO] ----------------------< com.ft:xjobs-server >-----------------------
[INFO] Building API server for xJobs service 0.2-SNAPSHOT
[INFO] --------------------------------[ war ]---------------------------------
[INFO]
[INFO] --- db-migrator-maven-plugin:2.5-j8:migrate (dev_migrations) @ xjobs-server ---
[INFO] Sourcing database configuration from file: /srv/apps/xjobs-server/src/main/resources/database.properties
[INFO] Environment: development
[INFO] Migrating jdbc:postgresql://localhost:5432/xjobs_deve using migrations at /srv/apps/xjobs-server/src/migrations/
[INFO] Creating schema version table for POSTGRESQL DB
[INFO] Executing: create table schema_version (version varchar(32) not null unique, applied_on timestamp not null, duration int not null)
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  0.864 s
[INFO] Finished at: 2021-07-26T00:42:50+08:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.javalite:db-migrator-maven-plugin:2.5-j8:migrate (dev_migrations) on project xjobs-server: Execution dev_migrations of goal org.javalite:db-migrator-maven-plugin:2.5-j8:migrate failed: org.javalite.activejdbc.DBException: org.postgresql.util.PSQLException: ERROR: relation "schema_version" already exists, query: create table schema_version (version varchar(32) not null unique, applied_on timestamp not null, duration int not null) -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/PluginExecutionException

And the javalite version

<javalite.version>2.5-j8</javalite.version>

I have checked schema_version table, it does exist, and it contains all migration sequence numbers. I don't understand why the migrator still needs to create the table again.

-- UPDATE

some config in pom.xml

    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <javalite.version>2.5-j8</javalite.version>
        <jetty.version>9.4.24.v20191120</jetty.version>
        <environments>development</environments>
    </properties>
    ...
    <plugin>
        <groupId>org.javalite</groupId>
        <artifactId>db-migrator-maven-plugin</artifactId>
        <version>${javalite.version}</version>
        <dependencies>
        <dependency>
                    <groupId>org.postgresql</groupId>
                    <artifactId>postgresql</artifactId>
                    <version>42.2.18</version>
                </dependency>
        </dependencies>
        <configuration>
            <configFile>${project.basedir}/src/main/resources/database.properties</configFile>
            <environments>${environments}</environments>
        </configuration>
        <executions>
            <execution>
                <id>dev_migrations</id>
                <phase>validate</phase>
                <goals>
                    <goal>migrate</goal>
                </goals>
            </execution>
        </executions>
    </plugin>

score 1 · Accepted Answer · answered Jul 26 '21 at 17:46

I created an example to test exactly the scenario you have. Fortunately, the JavaLite Migrator is working as expected.

Here is the link to the example: https://github.com/javalite/javalite-examples/tree/master/postgresql-example

No matter how many times you run the migrator, it works as expected every time:

[INFO] --- db-migrator-maven-plugin:2.5-j8:migrate (dev_migrations) @ postgresql-example ---
[INFO] Sourcing database configuration from file: /home/igor/projects/javalite/javalite-examples/postgresql-example/src/main/resources/database.properties
[INFO] Environment: development.test
[INFO] Migrating jdbc:postgresql://localhost/postgres using migrations at /home/igor/projects/javalite/javalite-examples/postgresql-example/src/migrations/
[INFO] Trying migrations at: /home/igor/projects/javalite/javalite-examples/postgresql-example/src/migrations 
[INFO] No new migrations are found
[INFO] Environment: development
[INFO] Migrating jdbc:postgresql://localhost/postgres using migrations at /home/igor/projects/javalite/javalite-examples/postgresql-example/src/migrations/
[INFO] Trying migrations at: /home/igor/projects/javalite/javalite-examples/postgresql-example/src/migrations 
[INFO] No new migrations are found
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  0.460 s
[INFO] Finished at: 2021-07-26T12:39:06-05:00

So, why are you having an issue? It is possible that you have multiple schemas in the database, and improperly configured visibility, such that under one condition, you see the schema_version table and under another, you do not. There have been reported similar cases before for Oracle, and the fix was related to eliminating leaking table visibility under different users. This is where you need to focus to fix the issue.

Thanks @ipolevoy. According to your suggestion, I checked the owner of database/table is not current user. After change the owner, and it works! — Jack Tang, Jul 27 '21 at 06:13

activejdbc migrator force to create `schema_version` table if it exits

1 Answers1