Error on Using ODK Briefcase CLI to pull data

Odil · July 29, 2020, 9:27am

Using ODK Briefcase v1.17.4 in Ubuntu 18.04 with RStudio and Opencpu

R code to pull data from aggregate:

  ## Pull request
  pullRequest <- paste("unset DISPLAY\n java -jar odkBriefcase_latest.jar",
                       '--pull_central',
                       '--storage_directory', <my_storage_directory>,
                       '--odk_url', <my_server>,
                       '--project_id', <project_id>,
                       '--odk_email', <my_email>,
                       '--odk_password', <my_password>)

  ## Call the function
  system(pullRequest, intern = TRUE)

R code for export into csv file:

  ## Export request
  exportRequest <- paste('unset DISPLAY\n java -jar odkBriefcase_latest.jar',
                         '--export',
                         '--storage_directory', <my_storage_directory>,
                         '--form_id', <my_formid>,
                         '--export_directory', <dir_to_export>,
                         '--export_filename', <my_formid>,
                         '--overwrite_csv_export')
## Call the function
system(exportRequest, intern = TRUE)

Both request codes are working when they run from RStudio. But only exportRequest code is working when I try to run it via opencpu.
The pull request code returns following errors in briefcase.log:

2020-07-29 08:50:17,995 [main] ERROR org.opendatakit.briefcase.Launcher - Error
java.io.UncheckedIOException: javax.net.ssl.SSLHandshakeException: No X509TrustManager implementation available
	at org.opendatakit.briefcase.reused.http.CommonsHttp.uncheckedExecute(CommonsHttp.java:149)
	at org.opendatakit.briefcase.reused.http.CommonsHttp.execute(CommonsHttp.java:96)
	at org.opendatakit.briefcase.operations.PullFormFromAggregate.pullFormFromAggregate(PullFormFromAggregate.java:102)
	at org.opendatakit.briefcase.operations.PullFormFromAggregate.lambda$static$0(PullFormFromAggregate.java:69)
	at org.opendatakit.common.cli.Cli.lambda$run$4(Cli.java:130)
	at java.base/java.lang.Iterable.forEach(Iterable.java:75)
	at org.opendatakit.common.cli.Cli.run(Cli.java:127)
	at org.opendatakit.briefcase.Launcher.main(Launcher.java:90)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.eclipse.jdt.internal.jarinjarloader.JarRsrcLoader.main(JarRsrcLoader.java:58)
Caused by: javax.net.ssl.SSLHandshakeException: No X509TrustManager implementation available
	at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:131)
	at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:326)
	at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:269)
	at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:264)
	at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:645)
	at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.onCertificate(CertificateMessage.java:464)
	at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.consume(CertificateMessage.java:360)
	at java.base/sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:392)
	at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:444)
	at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:422)
	at java.base/sun.security.ssl.TransportContext.dispatch(TransportContext.java:183)
	at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:171)
	at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1403)
	at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1309)
	at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:440)
	at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:411)
	at org.apache.http.conn.ssl.SSLConnectionSocketFactory.createLayeredSocket(SSLConnectionSocketFactory.java:396)
	at org.apache.http.conn.ssl.SSLConnectionSocketFactory.connectSocket(SSLConnectionSocketFactory.java:355)
	at org.apache.http.impl.conn.DefaultHttpClientConnectionOperator.connect(DefaultHttpClientConnectionOperator.java:142)
	at org.apache.http.impl.conn.PoolingHttpClientConnectionManager.connect(PoolingHttpClientConnectionManager.java:373)
	at org.apache.http.impl.execchain.MainClientExec.establishRoute(MainClientExec.java:381)
	at org.apache.http.impl.execchain.MainClientExec.execute(MainClientExec.java:237)
	at org.apache.http.impl.execchain.ProtocolExec.execute(ProtocolExec.java:185)
	at org.apache.http.impl.execchain.RetryExec.execute(RetryExec.java:89)
	at org.apache.http.impl.execchain.RedirectExec.execute(RedirectExec.java:111)
	at org.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:185)
	at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:83)
	at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:56)
	at org.apache.http.client.fluent.Request.internalExecute(Request.java:173)
	at org.apache.http.client.fluent.Executor.execute(Executor.java:262)
	at org.opendatakit.briefcase.reused.http.CommonsHttp.uncheckedExecute(CommonsHttp.java:142)
	... 12 common frames omitted
Caused by: java.security.cert.CertificateException: No X509TrustManager implementation available
	at java.base/sun.security.ssl.DummyX509TrustManager.checkServerTrusted(SSLContextImpl.java:1706)
	at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:629)
	... 38 common frames omitted
2020-07-29 08:50:18,062 [sentry-pool-1-thread-2] WARN  i.s.connection.AbstractConnection - An exception due to the connection occurred, a lockdown will be initiated.
io.sentry.connection.ConnectionException: An exception occurred while submitting the event to the Sentry server.
	at io.sentry.connection.HttpConnection.doSend(HttpConnection.java:192)
	at io.sentry.connection.AbstractConnection.send(AbstractConnection.java:71)
	at io.sentry.connection.AsyncConnection$EventSubmitter.run(AsyncConnection.java:173)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.net.SocketException: java.security.NoSuchAlgorithmException: Error constructing implementation (algorithm: Default, provider: SunJSSE, class: sun.security.ssl.SSLContextImpl$DefaultSSLContext)
	at java.base/javax.net.ssl.DefaultSSLSocketFactory.throwException(SSLSocketFactory.java:263)
	at java.base/javax.net.ssl.DefaultSSLSocketFactory.createSocket(SSLSocketFactory.java:270)
	at java.base/sun.net.www.protocol.https.HttpsClient.createSocket(HttpsClient.java:413)
	at java.base/sun.net.NetworkClient.doConnect(NetworkClient.java:162)
	at java.base/sun.net.www.http.HttpClient.openServer(HttpClient.java:474)
	at java.base/sun.net.www.http.HttpClient.openServer(HttpClient.java:569)
	... 25 frames omitted
Caused by: java.security.KeyStoreException: problem accessing trust store
	at java.base/sun.security.ssl.TrustManagerFactoryImpl.engineInit(TrustManagerFactoryImpl.java:73)
	at java.base/javax.net.ssl.TrustManagerFactory.init(TrustManagerFactory.java:278)
	at java.base/sun.security.ssl.SSLContextImpl$DefaultManagersHolder.getTrustManagers(SSLContextImpl.java:1053)
	at java.base/sun.security.ssl.SSLContextImpl$DefaultManagersHolder.<clinit>(SSLContextImpl.java:1023)
	at java.base/sun.security.ssl.SSLContextImpl$DefaultSSLContext.<init>(SSLContextImpl.java:1198)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at java.base/java.security.Provider.newInstanceUtil(Provider.java:154)
	at java.base/java.security.Provider$Service.newInstance(Provider.java:1891)
	at java.base/sun.security.jca.GetInstance.getInstance(GetInstance.java:236)
	at java.base/sun.security.jca.GetInstance.getInstance(GetInstance.java:164)
	at java.base/javax.net.ssl.SSLContext.getInstance(SSLContext.java:168)
	at java.base/javax.net.ssl.SSLContext.getDefault(SSLContext.java:99)
	at java.base/javax.net.ssl.SSLSocketFactory.getDefault(SSLSocketFactory.java:123)
	at org.apache.http.conn.ssl.SSLConnectionSocketFactory.getSystemSocketFactory(SSLConnectionSocketFactory.java:193)
	at org.apache.http.client.fluent.Executor.<clinit>(Executor.java:78)
	at org.opendatakit.briefcase.reused.http.CommonsHttp.of(CommonsHttp.java:70)
	at org.opendatakit.briefcase.operations.PullFormFromAggregate.lambda$pullFormFromAggregate$2(PullFormFromAggregate.java:98)
	at java.base/java.util.Optional.orElseGet(Optional.java:369)
	at org.opendatakit.briefcase.operations.PullFormFromAggregate.pullFormFromAggregate(PullFormFromAggregate.java:98)
	at org.opendatakit.briefcase.operations.PullFormFromAggregate.lambda$static$0(PullFormFromAggregate.java:69)
	at org.opendatakit.common.cli.Cli.lambda$run$4(Cli.java:130)
	at java.base/java.lang.Iterable.forEach(Iterable.java:75)
	at org.opendatakit.common.cli.Cli.run(Cli.java:127)
	at org.opendatakit.briefcase.Launcher.main(Launcher.java:90)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.eclipse.jdt.internal.jarinjarloader.JarRsrcLoader.main(JarRsrcLoader.java:58)
Caused by: java.security.PrivilegedActionException: null
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/sun.security.ssl.TrustStoreManager$TrustAnchorManager.loadKeyStore(TrustStoreManager.java:363)
	at java.base/sun.security.ssl.TrustStoreManager$TrustAnchorManager.getTrustedCerts(TrustStoreManager.java:313)
	at java.base/sun.security.ssl.TrustStoreManager.getTrustedCerts(TrustStoreManager.java:55)
	at java.base/sun.security.ssl.TrustManagerFactoryImpl.engineInit(TrustManagerFactoryImpl.java:49)
	... 31 common frames omitted
Caused by: java.io.FileNotFoundException: /usr/lib/jvm/java-11-openjdk-amd64/lib/security/cacerts (Permission denied)
	at java.base/java.io.FileInputStream.open0(Native Method)
	at java.base/java.io.FileInputStream.open(FileInputStream.java:219)
	at java.base/java.io.FileInputStream.<init>(FileInputStream.java:157)
	at java.base/sun.security.action.OpenFileInputStreamAction.run(OpenFileInputStreamAction.java:51)
	at java.base/sun.security.action.OpenFileInputStreamAction.run(OpenFileInputStreamAction.java:37)
	... 36 common frames omitted
2020-07-29 08:50:18,062 [sentry-pool-1-thread-1] WARN  i.s.connection.AbstractConnection - An exception due to the connection occurred, a lockdown will be initiated.
io.sentry.connection.ConnectionException: An exception occurred while submitting the event to the Sentry server.
	at io.sentry.connection.HttpConnection.doSend(HttpConnection.java:192)
	at io.sentry.connection.AbstractConnection.send(AbstractConnection.java:71)
	at io.sentry.connection.AsyncConnection$EventSubmitter.run(AsyncConnection.java:173)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.net.SocketException: java.security.NoSuchAlgorithmException: Error constructing implementation (algorithm: Default, provider: SunJSSE, class: sun.security.ssl.SSLContextImpl$DefaultSSLContext)
	at java.base/javax.net.ssl.DefaultSSLSocketFactory.throwException(SSLSocketFactory.java:263)
	at java.base/javax.net.ssl.DefaultSSLSocketFactory.createSocket(SSLSocketFactory.java:270)
	at java.base/sun.net.www.protocol.https.HttpsClient.createSocket(HttpsClient.java:413)
	at java.base/sun.net.NetworkClient.doConnect(NetworkClient.java:162)
	at java.base/sun.net.www.http.HttpClient.openServer(HttpClient.java:474)
	at java.base/sun.net.www.http.HttpClient.openServer(HttpClient.java:569)
	at java.base/sun.net.www.protocol.https.HttpsClient.<init>(HttpsClient.java:265)
	at java.base/sun.net.www.protocol.https.HttpsClient.New(HttpsClient.java:372)	
	... 24 common frames omitted
Caused by: java.security.NoSuchAlgorithmException: Error constructing implementation (algorithm: Default, provider: SunJSSE, class: sun.security.ssl.SSLContextImpl$DefaultSSLContext)
	at java.base/java.security.Provider$Service.newInstance(Provider.java:1898)
	at java.base/sun.security.jca.GetInstance.getInstance(GetInstance.java:236)
	at java.base/sun.security.jca.GetInstance.getInstance(GetInstance.java:164)
	at java.base/javax.net.ssl.SSLContext.getInstance(SSLContext.java:168)
	at java.base/javax.net.ssl.SSLContext.getDefault(SSLContext.java:99)
	at java.base/javax.net.ssl.SSLSocketFactory.getDefault(SSLSocketFactory.java:123)
	at java.base/javax.net.ssl.HttpsURLConnection.getDefaultSSLSocketFactory(HttpsURLConnection.java:335)
	at java.base/javax.net.ssl.HttpsURLConnection.<init>(HttpsURLConnection.java:292)
	at java.base/sun.net.www.protocol.https.HttpsURLConnectionImpl.<init>(HttpsURLConnectionImpl.java:100)
	at java.base/sun.net.www.protocol.https.Handler.openConnection(Handler.java:62)
	at java.base/sun.net.www.protocol.https.Handler.openConnection(Handler.java:57)
	at java.base/java.net.URL.openConnection(URL.java:1074)
	at io.sentry.connection.HttpConnection.getConnection(HttpConnection.java:119)
	at io.sentry.connection.HttpConnection.doSend(HttpConnection.java:151)
	... 5 common frames omitted
........
Caused by: java.io.FileNotFoundException: /usr/lib/jvm/java-11-openjdk-amd64/lib/security/cacerts (Permission denied)
	at java.base/java.io.FileInputStream.open0(Native Method)
	at java.base/java.io.FileInputStream.open(FileInputStream.java:219)
	at java.base/java.io.FileInputStream.<init>(FileInputStream.java:157)
	at java.base/sun.security.action.OpenFileInputStreamAction.run(OpenFileInputStreamAction.java:51)
	at java.base/sun.security.action.OpenFileInputStreamAction.run(OpenFileInputStreamAction.java:37)
	... 36 common frames omitted

Does anybody have some hints where should I look for the solution?
Thank you.

LN · July 30, 2020, 12:32am

It would be helpful to get a little bit of context on what you're trying to achieve and why you've made the tool decisions you have. It looks like you're pulling from Central and exporting to CSV. Why use Briefcase for that? Why not fetch the CSV from Central or use ruODK?

The error points to some issue with your Java setup related to certificates. Can you make sure you're using the same Java binary in both cases?

You can search for the Caused by lines to get some other troubleshooting ideas. I would recommend starting with java.io.FileNotFoundException: /usr/lib/jvm/java-11-openjdk-amd64/lib/security/cacerts (Permission denied).

Odil · July 30, 2020, 7:57am

Thank you @LN for your comments.
I have a very complex form with 3 levels of nested repeat groups, that ends with 30 stand alone tables when I try to download them from Central Server. I have a front end interface where users can see and download questionnaire results in several formats, including, SPSS, STATA, Rds, Excel, etc. monitor data collection process. Interface also can check the new submissions and download them near to real time.

Using Central's download CSV option is not very useful because: a) it tries to download all tables at once and it takes some time to generate the zipped file; b) when I have many submission (tested with about 3000 for above mentioned complex form) it doesn't bring all observations for repeat tables (although I have a server with 2 CPUs 4GB / 60GB Disk).

ruODK also downloads all submission at once, as Central Server is not offering pagination or partially download option for OData yet.

All these leads to unnecessarily extra resource consumption.

As a temporarily solution I'm now using Briefcase. My workaround to this for the time being is to download each submission xml and using briefcase for only exporting them into csv files.

I'm planning to share this application with ODK Community in coming months, where each can try to use and test it.

LN · July 30, 2020, 9:17pm

Thanks for the extra context, @Odil! We have various ideas for data access improvements in Central and general changes to how forms relate to real-world entities so it's very helpful to know what users are trying to do and feeling stuck on.

Are you doing a household survey, something else? If you're up for sharing what your three levels of nesting represent, that might also provide useful insights. If you've described this before, feel free to point me to another post.

How are you building these? Do you use libraries/tools made for conversion from CSV? Do you have custom code tailored to your form?

Can you say more about what that means? Could it be related to https://github.com/getodk/central-backend/issues/271? We haven't been able to reproduce this and it would be very helpful to get an example.

That's a good idea.

Very cool, I look forward to it!

Odil · July 31, 2020, 7:27am

Hi @LN,

This is not a HH survey, it's about registering plots and crop area, where I calculate also KML for each plot/crop. I can send you the XLSForm is you think that would help with understanding and fixing issue 271 of central-backend. And yes, seems I'm facing that same issue with my survey.

Regarding SPSS, STATA, labelled Excel and other formats I'm using csv and in R generating categorical dataset first (assigning factors to each select_one and select_multiple field's value based on xlsform's survey and choices sheets) then when I have labelled r dataset I'm saving it into different formats (for that there are several libraries available, such as, foreign, haven, xlsx, etc.)

Matthew_White · August 24, 2020, 1:01pm

I just wanted to chime in that this sounds very interesting. It sounds like when a user of your application initiates the download of an RDS file, that causes an R script to run. Is that right? Do you use a service to pass along HTTP requests to R?

LN · August 24, 2020, 8:53pm

Sorry about the delay in getting back to you about this, @Odil. Please do share the XLSForm with me in a private message and hopefully we can get to the root of this soon.

Odil · August 25, 2020, 8:09am

That's right, the front end application linked with R in background via OpenCPU which provides a HTTP API for data analysis based on R.

Odil · September 5, 2020, 9:26am

I posted about this near to real time monitoring and dashboard application under Showcases.

Matthew_White · October 5, 2020, 10:20pm

I wanted to mention here that we've released ODK Central v1.0.3, which resolves the Backend issue 271.