I have an old dataflow template and we update our databse security and now I need to stage and extra file to make it work | C2C Community

I have an old dataflow template and we update our databse security and now I need to stage and extra file to make it work

  • 16 August 2023
  • 4 replies

Userlevel 1

Hello everyone! 

I have an issue and this is killing me, I ma writing this in order to fina a solution.

My old architecture everything was working fine: 

  • we have some data in a JDBC that is accesible from a specific subnet
  • we extract data with cloud functions and write them in BigQuery

My new setup:

  • same database but with extra security and I have to use a certificate in order to connect
  • I have added some parameters to my connections string useSSL=true&requireSSL=true&verifyServerCertificate=true&serverTimezone=UTC&trustCertificateKeyStoreUrl=file:/extra_files/ca-root.pem
  • exactly the same setup for the rest of the function

The error I get and I just can’t get around it: java.sql.SQLException: Cannot create PoolableConnectionFactory (Cannot open file:/extra_files/ca-root.pem [/extra_files/ca-root.pem (No such file or directory)]

From this https://cloud.google.com/dataflow/docs/guides/templates/ssl-certificates it is that simple but my setup is just a little more complicate

4 replies

Userlevel 7
Badge +29

Hi @radu.sandulescu, good to see you in our community!

@Mati, as far as I remember you are quite familiar with Dataflow. Perhaps you can help?

Let me also tag @guillaume blaquiere and @antoine.castex in case they can give you a direction 

Userlevel 3

Hi, @Dimitris Petrakis and @radu.sandulescu, in my opinion the error is not related with Dataflow but with permisions. Here is my suggestion:

The error message indicates that the JDBC driver cannot find the file /extra_files/ca-root.pem. This file is required by the driver to establish a secure connection to the database.

To fix this error, you need to make sure that the file /extra_files/ca-root.pem exists and is accessible to the JDBC driver. You can do this by copying the file to the location where the JDBC driver is installed, or by setting the java.security.auth.login.config system property to the location of the file.

For more information, you can refer to the JDBC driver documentation.

Here are some additional things you can try:

  • Check the permissions on the file /extra_files/ca-root.pem. Make sure that the JDBC driver has read access to the file.
  • Try restarting the JVM.
  • Try using a different JDBC driver.

As i mentioned previously, the file must exist within the path and it says in the link you sent me:

```When you use this parameter, comma separated Cloud Storage paths or Secret Manager secrets are passed to the worker and saved in the /extra_files directory on each worker. When the files are saved in the /extra_files directory, other parameters can reference them. To reference the files, in the configuration properties, specify file paths relative to the /extra_files directory.```

If you check and the /extra_files is empty that is the reason, if the file is there the issue is that the JDBC cannot read it.

I hope this helps, if not we can schedule a brief call.




Userlevel 7
Badge +29

Wow, that was superfast! Thank you so much, @Mati !

@radu.sandulescu, let us know if @Mati’s answer was helpful 😎  

Userlevel 1

HI @Dimitris Petrakis glad to be here! @Mati thank you for your answer.


Indeed, the problem was that the file was not there and it was not there because I did not have the right class implemented. The process described in https://cloud.google.com/dataflow/docs/guides/templates/ssl-certificates works for only Google-provided templates.


I am using a custom dataflow template that did not have the extraFileToStage parameter implemented, because of that the file was missing. I had to implement a JvmInitializer like this and make sure it is in the classpath. JvmInitializer is executed in each worker before data processing begins. Hence, it should make sure that the certificate/file exists in the worker before the connection to the database is created.