search

faa-swim / fns-client

The System Wide Information Service (SWIM) Federal NOTAM System (FNS) Java Messaging Service (JMS) Reference Implementation (FnsClient) provides an example implementation on how to establish and maintain a local instance of the FNS NOTAM Database through the use of the FNS Initial Load (FIL) and SWIM FNS JMS services. FIL provides all active NOTAMS, via SFTP, that is required to initialize a NOTAM database and the SWIM JMS service provides, via JMS, NOTAM updates to keep the NOTAM database current. FIL also provides for re-initialization of a NOTAM database in the case of JMS service interruption.
Apache License 2.0
12 stars 6 forks source link

Running the client creates database conflicts #10

Closed clarkewing closed 3 months ago

clarkewing commented 3 months ago

Configuration

Ubuntu 22.04 (LTS) x64 with PostgreSQL 16

Description of the issue

When starting the client after an initial run, the following output is generated:

2024-03-29T09:07:27.924 [mainhread] INFO  [us.dot.faa.swim.fns.FnsClient] - Loading FnsClient Config and Initalizing
2024-03-29T09:07:28.425 [mainhread] INFO  [us.dot.faa.swim.fns.FnsClient] - Starting FnsClient
2024-03-29T09:07:29.967 [mainhread] INFO  [us.dot.faa.swim.fns.notamdb.NotamDb] - Creating new NOTAMS Table
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: relation "notams" already exists
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
    at us.dot.faa.swim.fns.notamdb.NotamDb.createNotamTable(NotamDb.java:174)
    at us.dot.faa.swim.fns.FnsClient.start(FnsClient.java:343)
    at us.dot.faa.swim.fns.FnsClient.main(FnsClient.java:455)

Deleting the associated notams table and restarting the client resolves the initial error but then results in the following:

2024-03-29T09:43:15.613 [mainhread] INFO  [us.dot.faa.swim.fns.FnsClient] - Loading FnsClient Config and Initalizing
2024-03-29T09:43:16.074 [mainhread] INFO  [us.dot.faa.swim.fns.FnsClient] - Starting FnsClient
2024-03-29T09:43:17.334 [mainhread] INFO  [us.dot.faa.swim.fns.notamdb.NotamDb] - Creating new NOTAMS Table
2024-03-29T09:43:17.359 [mainhread] INFO  [us.dot.faa.swim.fns.FnsClient] - Starting JMS Consumer
2024-03-29T09:43:17.711 [mainhread] INFO  [com.solacesystems.jcsmp.protocol.impl.TcpClientChannel] - Connecting to host 'orig=tcps://ems2.swim.faa.gov:55443, scheme=tcps://, host=ems2.swim.faa.gov, port=55443' (host 1 of 1, smfclient 2, attempt 1 of 2147483647, this_host_attempt: 1 of 1)
2024-03-29T09:43:18.182 [mainhread] INFO  [com.solacesystems.jcsmp.protocol.impl.TcpClientChannel] - Connected to host 'orig=tcps://ems2.swim.faa.gov:55443, scheme=tcps://, host=ems2.swim.faa.gov, port=55443' (smfclient 2)
2024-03-29T09:43:18.513 [mainhread] INFO  [com.solacesystems.jcsmp.protocol.impl.TcpClientChannel] - Channel Closed (smfclient 2)
2024-03-29T09:43:18.574 [mainhread] INFO  [com.solacesystems.jcsmp.protocol.impl.TcpClientChannel] - Connecting to host 'orig=tcps://ems2.swim.faa.gov:55443, scheme=tcps://, host=ems2.swim.faa.gov, port=55443' (host 1 of 1, smfclient 4, attempt 1 of 2147483647, this_host_attempt: 1 of 2147483648)
2024-03-29T09:43:18.955 [mainhread] INFO  [com.solacesystems.jcsmp.protocol.impl.TcpClientChannel] - Connected to host 'orig=tcps://ems2.swim.faa.gov:55443, scheme=tcps://, host=ems2.swim.faa.gov, port=55443' (smfclient 4)
2024-03-29T09:43:19.091 [mainhread] INFO  [com.solacesystems.jcsmp.protocol.impl.TcpClientChannel] - Connecting to host 'orig=tcps://ems2.swim.faa.gov:55443, scheme=tcps://, host=ems2.swim.faa.gov, port=55443' (host 1 of 1, smfclient 6, attempt 1 of 2147483647, this_host_attempt: 1 of 1)
2024-03-29T09:43:19.470 [mainhread] INFO  [com.solacesystems.jcsmp.protocol.impl.TcpClientChannel] - Connected to host 'orig=tcps://ems2.swim.faa.gov:55443, scheme=tcps://, host=ems2.swim.faa.gov, port=55443' (smfclient 6)
2024-03-29T09:43:19.646 [mainhread] INFO  [com.solacesystems.jcsmp.protocol.impl.TcpClientChannel] - Channel Closed (smfclient 6)
2024-03-29T09:43:19.768 [mainhread] INFO  [us.dot.faa.swim.fns.FnsClient] - JMS Consumer Started
2024-03-29T09:43:19.769 [mainhread] INFO  [us.dot.faa.swim.fns.FnsClient] - Recent Correlation Id not found in NotamDb, starting NotamDb initalization from FIL
2024-03-29T09:44:19.769 [mainhread] INFO  [us.dot.faa.swim.fns.FnsClient] - Initalizing Database
2024-03-29T09:44:19.770 [mainhread] INFO  [us.dot.faa.swim.fns.fil.FilClient] - Connecting to FNS Initial Load SFTP server
2024-03-29T09:44:23.970 [mainhread] INFO  [us.dot.faa.swim.fns.notamdb.NotamDb] - Creating new NOTAMS Table
2024-03-29T09:44:23.980 [mainhread] ERROR [us.dot.faa.swim.fns.FnsClient] - NotamDb initalization failed due to:org.postgresql.util.PSQLException: ERROR: relation "notams" already exists

[Repetition omitted for brevity]

2024-03-29T09:44:27.704 [mainhread] INFO  [us.dot.faa.swim.fns.FnsClient] - Initalizing Database
2024-03-29T09:44:27.705 [mainhread] INFO  [us.dot.faa.swim.fns.fil.FilClient] - Connecting to FNS Initial Load SFTP server
2024-03-29T09:44:31.292 [mainhread] INFO  [us.dot.faa.swim.fns.notamdb.NotamDb] - Creating new NOTAMS Table
2024-03-29T09:44:31.295 [mainhread] ERROR [us.dot.faa.swim.fns.FnsClient] - NotamDb initalization failed due to:org.postgresql.util.PSQLException: ERROR: relation "notams" already exists

Deleting the notams table again while the client is running allows execution to continue normally and the database to be populated.

Effects

When running the client as a daemon, it is unable to restart on its own should the process get interrupted.

Possible solution?

My knowledge of Java is next to none, but reading through the source, I believe the issue has to do with the notTableExists() method not returning false when it should be returning true, generating a conflict when PostgreSQL attempts to create an already existing table.

clarkewing commented 3 months ago

After some trial and error, I was able to identify case-sensitivity as the cause of the issue.

For anyone encountering similar issues, what worked for me was the following:

### postgres database config
notamDb.driver="org.postgresql.Driver"
notamDb.connectionUrl="jdbc:postgresql://127.0.0.1/aim_fns"
notamDb.username="#####"
notamDb.password="#####"
notamDb.schema="public"
notamDb.table="notams"

Notice that the database name (in notamDb.connectionUrl), schema, and table are all lowercased. This is what fixed the issue for me.