OHDSI Home | Forums | Wiki | Github

Errors when deploying WebAPI with new settings.xml file

Hi there,

I’ve managed to deploy WebAPI on Tomcat successfully. But now i needed to use WebAPI security., I created a new war file with updated settings. Now, Flyway is reporting issues regarding existing tables in the WebAPI schema.

Is this a regular occurrence? If so, is it necessary to drop existing tables every time I redeploy WebAPI? Could you guide me on which specific tables require dropping?

Apologies for my inquiry; I’m new to the OHDSI ecosystem.

Thank you.

No, you shouldn’t need to drop tables between updates, in fact that is the supported use case. Is this the first time you ever restarted your WebAPI instance because if you’ve restarted WebAPI before you made the security configuration change, then you would have seen that Flyway noticed that all tables were up to date and did nothing. If that’s the case, then there must have been an additional change made to the configuration that led to the issue.

Can you report the specific error message you are receiving?

I’ve restarted the WebAPI a number of times before I made the changes to the settings/xml file, and it worked fine. But this was the first time that I had to re-build the war file and redeploy it.
Here is an example of messages I kept getting:

BlockquoteSQL State : 42P07
Error Code : 0
Message : ERROR: relation “schema_version” already exists
Line : 17
Statement : CREATE TABLE “webapi”.“schema_version” (
“installed_rank” INT NOT NULL,
“version” VARCHAR(50),
“description” VARCHAR(200) NOT NULL,
“type” VARCHAR(20) NOT NULL,
“script” VARCHAR(1000) NOT NULL,
“checksum” INTEGER,
“installed_by” VARCHAR(100) NOT NULL,
“installed_on” TIMESTAMP NOT NULL DEFAULT now(),
“execution_time” INTEGER NOT NULL,

I recreated the schema and the WebAPI deployment was successful. But i just wanted to know if this is how it’s supposed to work.

Thank you for your help.

In your WebAPI Postgres instance, what schemas are there? It seems to think that webapi.schema_version needs to be created, but I’m not sure why that would be because it wouldn’t create it if it already existed…it’s as if it looked in one schema and didn’t find it (not webapi) and then tries to create the table where it already exists (in the webapi schema).

Yes, it keep getting the error messages for all tables in the existing webapi schema. Very strange,

Apologies, I think you may have missed my question:

In addition, if you have a schema_version table in your webapi schema, can you please paste the contents of that table here? It should show which migrations have completed.

Finally, reset your logs and check them: it should report in the logs right at the top of the log, that Flyway fires up and reports an existing schema version, which schema it is trying to migrate, and which version it thinks it has.

Since I recreated the webapi schema, I no longer have the schema_version table contents from when the errors occurred. However, I plan to rebuild the webapi.war with a new settings.xml file and redeploy it. I’ll update you with a comment here once it’s completed.

Thank you once again for all the valuable tips. They’ve been extremely helpful