Import fails with SocketTimeoutException (through duplicate key value) or ExceptionReport: null

[johnjohndoe]

I am using the latest development version at commit d678a8b. I am trying to import a single row. This process fails in different scenarios. I boiled it down to the following.

Test files

These files can be used to reproduce the errors:

• sensordata.csv.52n-sos-import-config-0.5.xml
• sensordata-dummy-1.csv

Shell command

I use the following command to execute the importer and write the console output to a log file.

$ java -jar ./feeder/target/52n-sos-importer-feeder-bin.jar \
    -c sensordata.csv.52n-sos-import-config-0.5.xml \
    -d sensordata-dummy-1.csv > /tmp/sensordata-dummy-1a.log

The names of the data file and log files change within the scenarios and subsequent runs.

Scenario 1

The SOS instance has enabled the following insert operations: InsertObservation, InsertSensor. InsertResult and InsertResultTemplate are disabled as shown in the screenshot.

At the 1st run the import fails with a SocketTimeoutException: Read timed out for each sensor value. When I run the same import a 2nd time the line is skipped.

• Console output for the 1st run
• Console output for the 2nd run

The failure of the 1st run can be reproduced by simply renaming the data input file.

Scenario 2

In the scenario I renamed the data file to avoid that the row is skipped.

The SOS instance has enabled the following insert operations: InsertObservation, InsertSensor, InsertResult and InsertResultTemplate as shown in the screenshot.

At the 1st run the import fails with org.n52.oxf.ows.ExceptionReport: null. When I run the same import a 2nd time the line is skipped.

• Console output for the 1st run
• Console output for the 2nd run

[johnjohndoe]

With the help of @CarstenHollmann and the SOS server log level set to TRACE I found an BatchUpdateException and ... duplicate key value ... in /var/log/tomcat7/52n-sos-webapp.log:

ERROR [http-bio-8080-exec-15] [SqlExceptionHelper.java:146] 
  Batch entry 0 insert into ... was aborted.  Call getNextException to see the cause.
WARN [http-bio-8080-exec-15] [SqlExceptionHelper.java:144] SQL Error: 0, SQLState: 23505
ERROR [http-bio-8080-exec-15] [SqlExceptionHelper.java:146] 
  ERROR: duplicate key value violates unique constraint "observationidentity"
  Detail: Key (seriesid, phenomenontimestart, phenomenontimeend, resulttime)=
  (112, 2015-01-02 00:00:00, 2015-01-02 00:00:00, 2015-01-02 00:00:00) already exists.
ERROR [http-bio-8080-exec-15] [BatchingBatch.java:137] HHH000315: Exception executing batch [could not execute batch]
WARN [http-bio-8080-exec-15] [ExceptionLogger.java:73] Exception thrown
org.n52.sos.exception.ows.NoApplicableCodeException: Observation with same values already contained in database
    at org.n52.sos.ds.hibernate.InsertObservationDAO.checkContainsAndThrow(InsertObservationDAO.java:281) ~[hibernate-dao-4.3.0.jar:na]
    at org.n52.sos.ds.hibernate.InsertObservationDAO.insertObservation(InsertObservationDAO.java:239) ~[hibernate-dao-4.3.0.jar:na]
    at org.n52.sos.request.operator.SosInsertObservationOperatorV20.receive(SosInsertObservationOperatorV20.java:116) ~[transactional-v20-4.3.0.jar:na]
    at org.n52.sos.request.operator.SosInsertObservationOperatorV20.receive(SosInsertObservationOperatorV20.java:92) ~[transactional-v20-4.3.0.jar:na]
    ...

Caused by: org.hibernate.exception.ConstraintViolationException: could not execute batch
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    ...
    at org.n52.sos.ds.hibernate.InsertObservationDAO.insertObservation(InsertObservationDAO.java:217) ~[hibernate-dao-4.3.0.jar:na]
    ... 64 common frames omitted

Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into ... was aborted.  
Call getNextException to see the cause.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2740) ~[postgresql-9.3-1101-jdbc4.jar:na]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1891) ~[postgresql-9.3-1101-jdbc4.jar:na]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405) ~[postgresql-9.3-1101-jdbc4.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2889) ~[postgresql-9.3-1101-jdbc4.jar:na]
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1135) ~[c3p0-0.9.2.1.jar:0.9.2.1]
    at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:123) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final]
    ... 77 common frames omitted

Looking at the sensordata.csv.52n-sos-import-config-0.5.xml we discovered that I was using the same identifier for multiple columns in the .csv file.

---

I am going to create a new configuration file. Please decide if you want to use the above information to enhance the error output of the importer.