Pystardog virtual graph postgres driver error

Kevin_Vanslette · April 8, 2020, 9:43pm

Hi Everybody!

I am new to stardog and pystardog and can't figure out how to resolve the following error pertaining to the creation of a new virtual graph for a postgresql db :

From pystardog:
"stardog.exceptions.StardogException: [500] 000012: java.sql.SQLException: Unable to load class: $STARDOG_EXT/postgresql-42.2.5.jar from ClassLoader:sun.misc.Launcher$AppClassLoader@619a5dff;ClassLoader:java.net.URLClassLoader@6947cf45".

I am running a stardog server out of a docker container and have the appropriate environment variables set up (from what I can tell) for jbcd.username,password, drive, and url; however it seems to not like the driver. I am running python on my local machine. I was able to create databases using pystardog just fine.

Here is my pystardog script:

    import stardog
    import time
    import os, os.path

    conn_details = {
      'endpoint': 'http://localhost:5820',
      'username': 'admin',
      'password': 'admin'
    }
    with stardog.Admin(**conn_details) as admin:
        map=admin.new_virtual_graph('map', stardog.content.File('map.ttl'),{
'jdbc.username': '$DB_USER',
'jdbc.password': '$DB_PASS',
'jdbc.driver': '$STARDOG_EXT/postgresql-42.2.5.jar',
'jdbc.url': '$PSQL_ADDR:$PSQL_PORT'})

I have verified that the jar file is located in $STARDOG_EXT in the stardog docker container and the other environment variables are fed into the stardog container once it is spun up.

Any help trouble shooting this issue would mean a lot.

Thanks!

jess · April 8, 2020, 9:46pm

Hi Kevin!

The jdbc.driver property refers to the JDBC driver class name. You can either remove it entirely (class will be matched to the URL) or set it org.postgresql.Driver.

Hope this helps,
Jess

Kevin_Vanslette · April 8, 2020, 10:06pm

Thanks Jess!

It looks like setting the driver to org.postgresql.Driver loaded the driver correctly... thanks! Is this solution located in the stardog/docs somewhere?

Although I was able to load the driver, I now have new issues:
java.sql.SQLException: Driver:org.postgresql.Driver@242867cf returned null for URL:$PSQL_ADDR:$PSQL_PORT

Would you suspect this is postgres server side issue rather than a pystardog issue?

Thanks again!

jess · April 8, 2020, 10:25pm

Kevin,
Admittedly the settings are Java-centric. You need to use a JDBC URL here, eg. jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true. More docs at PostgreSQL JDBC's Connecting to the Database page. Let me know if this works.

Jess

jess · April 8, 2020, 10:47pm

Additionally, it's possible to setup your virtual graphs using Stardog Studio and do your application development in Python. Assuming you aren't doing significant management of virtual graphs in your application code.

Jess

arwesterinen · April 9, 2020, 3:33pm

Jess, We are getting "database 'xxx' does not exist' ... Is there anything that we can do to debug this.

I fear that the problem is something in the Postgres setup or the way that Stardog is connecting.

Andrea

jess · April 9, 2020, 3:35pm

That sounds like a Stardog error. Are you able to share the stack trace from the stardog.log?

Kevin_Vanslette · April 9, 2020, 3:59pm

Here are the calls and their corresponding stack traces:

admin.new_virtual_graph('map', stardog.content.File('map.ttl'), {'jdbc.driver': 'org.postgresql.Driver', 'jdbc.url':'jdbc:postgresql://10.987.65.432/XXX/', 'jdbc.username':'foo', 'jdbc.password':'bar'})

Apr 09, 2020 3:43:32 PM org.apache.tomcat.jdbc.pool.ConnectionPool init
SEVERE: Unable to create initial connections of pool.
org.postgresql.util.PSQLException: FATAL: database "XXX" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2559)
at org.postgresql.core.v3.QueryExecutorImpl.(QueryExecutorImpl.java:133)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:250)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:454)
at org.postgresql.Driver.connect(Driver.java:256)
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:319)
at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:212)
at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:744)
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:676)
at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:483)
at org.apache.tomcat.jdbc.pool.ConnectionPool.(ConnectionPool.java:154)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
at com.complexible.stardog.virtual.vega.rdbms.RdbmsDataSource.lambda$connect$0(RdbmsDataSource.java:58)
at com.complexible.stardog.virtual.vega.RuntimeDriverLoader.executeUnderClassLoader(RuntimeDriverLoader.java:130)
at com.complexible.stardog.virtual.vega.rdbms.RdbmsDataSource.connect(RdbmsDataSource.java:56)
at com.complexible.stardog.virtual.vega.rdbms.RdbmsVirtualGraphFactory.create(RdbmsVirtualGraphFactory.java:65)
at com.complexible.stardog.virtual.vega.rdbms.RdbmsVirtualGraphFactory.create(RdbmsVirtualGraphFactory.java:49)
at com.complexible.stardog.virtual.DefaultVirtualGraphRegistry.createVirtualGraph(DefaultVirtualGraphRegistry.java:365)
at com.complexible.stardog.virtual.DefaultVirtualGraphRegistry.createVirtualGraph(DefaultVirtualGraphRegistry.java:346)
at com.complexible.stardog.virtual.DefaultVirtualGraphRegistry.add(DefaultVirtualGraphRegistry.java:211)
at com.complexible.stardog.virtual.SecuredVirtualGraphRegistry.add(SecuredVirtualGraphRegistry.java:149)
at com.complexible.stardog.protocols.http.server.virtual.admin.VirtualGraphHttpService.addVG(VirtualGraphHttpService.java:414)
at com.complexible.stardog.protocols.http.server.virtual.admin.VirtualGraphHttpService.add(VirtualGraphHttpService.java:213)
at com.stardog.http.server.undertow.jaxrs.ExtractRoutes.lambda$handleIt$5(ExtractRoutes.java:192)
at org.apache.shiro.subject.support.SubjectRunnable.doRun(SubjectRunnable.java:120)
at org.apache.shiro.subject.support.SubjectRunnable.run(SubjectRunnable.java:108)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

We also tried without the slash at the end of the db; however, it only gives an error in pystardog:

Traceback (most recent call last):
File "C:/.....", line 13, in
admin.new_virtual_graph('map', stardog.content.File('map.ttl'), {'jdbc.driver': 'org.postgresql.Driver', 'jdbc.url':'jdbc:postgresql://10.987.65.432/XXX', 'jdbc.username':'foo', 'jdbc.password':'bar'})
File "C:\Condapath\envs\myenv\lib\site-packages\stardog\admin.py", line 257, in new_virtual_graph
self.admin.new_virtual_graph(
File "C:\Condapath\envs\myenv\lib\site-packages\stardog\http\admin.py", line 115, in new_virtual_graph
self.client.post('/admin/virtual_graphs', json=meta)
File "C:\Condapath\envs\myenv\lib\site-packages\stardog\http\client.py", line 29, in post
return self.__wrap(self.session.post(self.url + path, **kwargs))
File "C:\Condapath\envs\myenv\lib\site-packages\stardog\http\client.py", line 51, in __wrap
raise exceptions.StardogException('[{}] {}: {}'.format(
stardog.exceptions.StardogException: [400] QEIVR2: Expected ':', found '
' [L4]

Process finished with exit code 1

Thanks!

Kevin_Vanslette · April 9, 2020, 4:03pm

We get the same error with or without the port number

jess · April 9, 2020, 4:04pm

Can you run \l in psql and verify that the database with that name exists?

arwesterinen · April 9, 2020, 4:11pm

Jess, I can connect with all of the same settings as Kevin using psycopg2. No problem.

The database does exist. Please take a look at the last error trace from Python. There is something happening in PyStardog's admin.py.

Andrea

jess · April 9, 2020, 4:12pm

Which version of Stardog are you using?

arwesterinen · April 9, 2020, 4:12pm

Latest ... We are using 7.2.1

arwesterinen · April 9, 2020, 4:13pm

Could the problem be in the PyStardog code?

Andrea

jess · April 9, 2020, 4:14pm

The request to create a virtual graph is relatively straightforward. Are there no additional errors in the log when Pystardog throws the exception?

arwesterinen · April 9, 2020, 4:20pm

Jess, We get the error database "xxx/" does not exist - when we use the .../xxx/?user=... format of the virtual graph. That tells me that you are incorrectly parsing the jdbc.url.

If we specifically pull the ?user out and add the jdbc.username and .password properties, and then we issue the command without the ending slash for the database name, we get the error, stardog.exceptions.StardogException: [400] QEIVR2: Expected ':', found '
' [L4] - which might indicate a need for a port or something about the property values. There is no other output in the Stardog.log for this problem.

So, there might be two different problems.

Andrea

jess · April 9, 2020, 4:28pm

There cannot be a trailing slash after the database name. This is specified in the PostgreSQL JDBC documentation. It's their code parsing the URL.

The exception thrown by the Python code is propagated back from the server. It could be a problem parsing the mappings. What format are the mappings in? You may need to set mappings.syntax=R2RML in the map (with the JDBC properties).

arwesterinen · April 9, 2020, 4:37pm

Jess,
We are using SMS2.
Does that mean that we should include (in our options file) ... mappings.syntax=SMS2??
Andrea

jess · April 9, 2020, 4:37pm

It should be specified in the last argument to the new_virtual_graph() call.

arwesterinen · April 9, 2020, 4:54pm

Jess, Thanks for the help on this ... we now have the same errors in our python code as in Stardog Studio.

This seems to be related to the syntax of our SMS2 mapping definition.

Andrea

Topic		Replies	Views
Can't load postgresql driver Support	2	890	October 11, 2019
Virtual import jdbc connection fails Support	3	1065	March 6, 2019
Virtual graph based on a SQL database Support	2	695	May 31, 2018
While creating Virtual graph we are facing following issue: Support	18	627	July 24, 2020
PyStardog - Setting connection pool details for a virtual graph Support	3	397	June 12, 2020

Pystardog virtual graph postgres driver error

Related topics