Is jaydebeapi threadsafe and embeddable in Web app like Django?

[romainr]

I have a very simple code below that works well in the Python shell. When I put the code in a Django view (we are looking around for offering JDBC in the Hue UI project), the first execution of a cursor works, however the second one always fails with:

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test

which is apparently a famous error when "Class.forName("com.mysql.jdbc.Driver").newInstance();" is not called before connecting.

Would you have any idea about what is the problem? I tried to reload the module, call again _prepare_jpype() but nothing, it always fail after one query.

I can provide more info as needed. Thanks!

A more detailed trace:

Traceback Switch to copy-and-paste view

/home/romain/projects/hue/build/env/local/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/core/handlers/base.py in get_response
                    response = wrapped_callback(request, *callback_args, **callback_kwargs) ...
▶ Local vars
/home/romain/projects/hue/build/env/local/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/transaction.py in inner
                return func(*args, **kwargs) ...
▶ Local vars
/home/romain/projects/hue/apps/oozie/src/oozie/views/editor2.py in list_editor_workflows
  db = jaydebeapi.connect(jclassname, driver_args, jars=jars, libs=libs) ...
▶ Local vars
/usr/local/lib/python2.7/dist-packages/jaydebeapi/__init__.py in connect
    jconn = _jdbc_connect(jclassname, jars, libs, *driver_args) ...
▶ Local vars
/usr/local/lib/python2.7/dist-packages/jaydebeapi/__init__.py in _jdbc_connect_jpype
        _init_types(types_map)
    global _java_array_byte
    if _java_array_byte is None:
        def _java_array_byte(data):
            return jpype.JArray(jpype.JByte, 1)(data)
    # register driver for DriverManager
    jpype.JClass(jclassname)
    return jpype.java.sql.DriverManager.getConnection(*driver_args) ...
def _get_classpath():
    """Extract CLASSPATH from system environment as JPype doesn't seem
    to respect that variable.
    """
    try:
▼ Local vars
Variable    Value
libs    
[]
driver_args 
('jdbc:mysql://localhost:3306/test', 'root', 'root')
jars    
[]
jpype   
<module 'jpype' from '/usr/local/lib/python2.7/dist-packages/jpype/__init__.pyc'>
jclassname  
'com.mysql.jdbc.Driver

On Unbuntu 12.04

sudo pip install JayDeBeApi
sudo pip install  JPype1

sudo apt-get install libmysql-java

Simple code to put in a Django view

import jaydebeapi

user = 'root'
password = 'root'

host = 'localhost'
port = 3306
database = 'test'

autocommit = True
jclassname = "com.mysql.jdbc.Driver"
url = "jdbc:mysql://{host}:{port}/{database}".format(host=host, port=port, database=database)
driver_args = [url, user, password]
jars = None
libs = None

db = jaydebeapi.connect(jclassname, driver_args, jars=jars, libs=libs)
db.jconn.setAutoCommit(autocommit)

curs = db.cursor()
curs.execute('show tables')

data = curs.fetchmany(100)
description = curs.description

curs.close()
db.close()

[romainr]

Just saw https://github.com/baztian/jaydebeapi/issues/1, might be related

[baztian]

Your code above should fail as jars is None. Is that really the code you're executing? Or did you specify a CLASSPATH env variable?

Make sure you either set jars to the full path of you jar (or list of jars) or specify a CLASSPATH environment variable (something like CLASSPATH=/foo/bar/libs/* python yourdjangoapp.py)

I recommend calling the connect method once in a method that is called during django startup. After that the jars and libs parameters are ignored. (Advanced: You could also consider using connection pooling later on. One has reported successfully using sqlalchemy connection pool together with jaydebeapi.)

Back to your question regarding thread safety: jaydebeapi might cause some trouble in multi threading scenarios currently. If you encounter problems you can try to work around that calling

import jpype
if not jpype.isThreadAttachedToJVM():
        jpype.attachThreadToJVM()

before executing the jaydebeapi calls. However thread handling in jpype is not rock solid as well. See originell/jpype#84

Please report back on what you came up with. I really like to hide the thread attachment stuff from jpype for jaydebeapi users but don't currently know how to do it the right way.

[romainr]

Thanks for the help.

Yes, I was using the CLASSPATH (I also tried with the jars list).

If I do only one connect I am getting a

#
# A fatal error has been detected by the Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0x00007f1d833276c7, pid=5521, tid=139764409825024
#
# JRE version: Java(TM) SE Runtime Environment (7.0_80-b15) (build 1.7.0_80-b15)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (24.80-b11 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C  [_jpype.so+0x466c7]  JPJavaEnv::IsAssignableFrom(_jclass*, _jclass*)+0x37
#
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
#
# An error report file with more information is saved as:
# /home/romain/projects/hue/hs_err_pid5521.log
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.java.com/bugreport/crash.jsp
#
Aborted (core dumped)

Going to try a few more things

[baztian]

This looks like you have to call jpype.attachThreadToJVM(). JPype should do better handling this error.

[romainr]

Nice that fixes it!

Now the next step is that we would like to support various credentials (e.g. use the credentials of the logged-in user in the Web app for issuing the queries with his real username, not the Web server).

As we can connect only once, seems like some kind of impersonation would be needed?

[baztian]

It is very uncommon to use DB credentials to manage access to data offered through a web app. You have greater flexibility if you have one db-user for you app and then manage access rights in you app.

Anyways. There should be no problem to open more than one connection to you database. Simply open a connection per user with the corresponding credential.s

[armThanut]

I call jpype.attachThreadToJVM(), on OSX Sierra result is "Segmentation fault: 11"

[ghost]

I use CherryPy which uses a thread pool to handle requests. The first call to jaydebeapi.connect works fine, but additional calls from other threads fail with 'java.sql.SQLExceptionPyRaisable: java.sql.SQLException: No suitable driver found for jdbc...'. I traced it down to the classloader which doesn't get the path to the driver in the new threads. These lines before every call to jaydebeapi.connect fixes it for me:

    import jpype
    if jpype.isJVMStarted() and not jpype.isThreadAttachedToJVM():
        jpype.attachThreadToJVM()
        jpype.java.lang.Thread.currentThread().setContextClassLoader(jpype.java.lang.ClassLoader.getSystemClassLoader())

Maybe you can confirm this and add the fourth line to _jdbc_connect_jpype after the attachThreadToJVM?

[rama-mullapudi]

I am getting same driver not found error when using Threadpool. Tried your suggestion of loading classes to current thread , but I am getting this error AttributeError: type object 'java.lang.Thread' has no attribute 'currentThread'

[turnovec]

I had problems when running my Django project with "classic" ./manage.py runserver. But when I'm runnning it with Gunicorn (with two workers), it's working...

[SrushtiGangireddy]

Worked like a charm. Spent lot of time on this. Appreciate it. Thanks.

[JULYRAINING]

I use CherryPy which uses a thread pool to handle requests. The first call to jaydebeapi.connect works fine, but additional calls from other threads fail with 'java.sql.SQLExceptionPyRaisable: java.sql.SQLException: No suitable driver found for jdbc...'. I traced it down to the classloader which doesn't get the path to the driver in the new threads. These lines before every call to jaydebeapi.connect fixes it for me:

    import jpype
    if jpype.isJVMStarted() and not jpype.isThreadAttachedToJVM():
        jpype.attachThreadToJVM()
        jpype.java.lang.Thread.currentThread().setContextClassLoader(jpype.java.lang.ClassLoader.getSystemClassLoader())

Maybe you can confirm this and add the fourth line to _jdbc_connect_jpype after the attachThreadToJVM? perfect! It took me a whole day

[xanjay]

I am still getting below error while running in flask with gunicorn: python3.8/site-packages/_jpype.cpython-38-x86_64-linux-gnu.so already loaded in another classloader

PS: The above error occurs randomly after gunicorn service restart.