openlink / virtuoso-opensource

Virtuoso is a high-performance and scalable Multi-Model RDBMS, Data Integration Middleware, Linked Data Deployment, and HTTP Application Server Platform
https://vos.openlinksw.com
Other
858 stars 210 forks source link

Issue with the JDBC 4 and 4.1 Drivers #216

Open jplu opened 10 years ago

jplu commented 10 years ago

Hello,

I put here my actual issue about the Virtuoso JDBC drivers currently discussed on the mailing list with @HughWilliams . So to resume I use the last version of Virtuoso contained in develop/7 branch. I compiled it with these lines in the configure :

./configure --prefix=/var/usr/virtuoso --program-transform-name="s/isql/isql-v/" --enable-dbpedia-vad --enable-rdfmappers-vad --enable-conductor-vad --enable-rdb2rdf-vad --enable-fct-vad --enable-ods-vad --enable-perl --with-readline

And at the end I also recompiled the virtjena2.jar file with this new classpath :

JENA_CLASSPATH = .:lib/junit-4.5.jar:lib/jena-arq-2.11.2.jar:lib/jena-iri-1.0.2.jar:lib/jena-core-2.11.2.jar:lib/jena-core-2.11.2-tests.jar:$(top_srcdir)/libsrc/JDBCDriverType4/virtjdbc4_1.jar:lib/jcl-over-slf4j-1.7.6.jar:lib/log4j-1.2.17.jar:lib/slf4j-api-1.7.6.jar:lib/slf4j-log4j12-1.7.6.jar:lib/xercesImpl-2.11.0.jar:lib/xml-apis-1.4.01.jar

To get this version :

julien-plu@data:~$ java -jar virtuoso-opensource/binsrc/jena2/virt_jena2.jar
OpenLink Virtuoso(TM) Provider for Jena(TM) Version 2.10.1 [Build 1.10]

The JDBC drivers are those provided by default in the repository :

julien-plu@data:~$ java -jar virtuoso-opensource/libsrc/JDBCDriverType4/virtjdbc4_1.jar
OpenLink Virtuoso(TM) Driver for JDBC(TM) Version 4.1 [Build 3.70]

And :

julien-plu@data:~$ java -jar virtuoso-opensource/libsrc/JDBCDriverType4/virtjdbc4.jar
OpenLink Virtuoso(TM) Driver for JDBC(TM) Version 4.x [Build 3.70]

The code that I try to execute is as simple as this :

import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;

public class Test {
    public static void main(String[] args) {
        try {
            Connection conn = DriverManager.getConnection("jdbc:virtuoso://localhost:1111/", "dba", "dba");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

And an error occurs each time I try to use these drivers as you can see it there with virtjdbc4_1.jar :

julien-plu@data:~$ javac -cp "virtuoso-opensource/libsrc/JDBCDriverType4/virtjdbc4_1.jar:." Test.java
julien-plu@data:~$ java -cp "virtuoso-opensource/libsrc/JDBCDriverType4/virtjdbc4_1.jar:." Test
virtuoso.jdbc4.VirtuosoException: Virtuoso Communications Link Failure (timeout) : Connection to the server lost
      at virtuoso.jdbc4.VirtuosoFuture.nextResult(VirtuosoFuture.java:63)
      at virtuoso.jdbc4.VirtuosoResultSet.process_result(VirtuosoResultSet.java:213)
      at virtuoso.jdbc4.VirtuosoResultSet.<init>(VirtuosoResultSet.java:56)
      at virtuoso.jdbc4.VirtuosoPreparedStatement.<init>(VirtuosoPreparedStatement.java:33)
      at virtuoso.jdbc4.VirtuosoConnection.prepareStatement(VirtuosoConnection.java:738)
      at virtuoso.jdbc4.VirtuosoConnection.prepareStatement(VirtuosoConnection.java:676)
      at virtuoso.jdbc4.VirtuosoConnection.<init>(VirtuosoConnection.java:173)
      at virtuoso.jdbc4.Driver.connect(Driver.java:59)
      at java.sql.DriverManager.getConnection(DriverManager.java:571)
      at java.sql.DriverManager.getConnection(DriverManager.java:215)
      at Test.main(Test.java:8)

And with virtjdbc4.jar :

julien-plu@data:~$ javac -cp "virtuoso-opensource/libsrc/JDBCDriverType4/virtjdbc4.jar:." Test.java
julien-plu@data:~$ java -cp "virtuoso-opensource/libsrc/JDBCDriverType4/virtjdbc4.jar:." Test
virtuoso.jdbc4.VirtuosoException: Virtuoso Communications Link Failure (timeout) : Connection to the server lost
      at virtuoso.jdbc4.VirtuosoFuture.nextResult(VirtuosoFuture.java:63)
      at virtuoso.jdbc4.VirtuosoResultSet.process_result(VirtuosoResultSet.java:213)
      at virtuoso.jdbc4.VirtuosoResultSet.<init>(VirtuosoResultSet.java:56)
      at virtuoso.jdbc4.VirtuosoPreparedStatement.<init>(VirtuosoPreparedStatement.java:33)
      at virtuoso.jdbc4.VirtuosoConnection.prepareStatement(VirtuosoConnection.java:738)
      at virtuoso.jdbc4.VirtuosoConnection.prepareStatement(VirtuosoConnection.java:676)
      at virtuoso.jdbc4.VirtuosoConnection.<init>(VirtuosoConnection.java:173)
      at virtuoso.jdbc4.Driver.connect(Driver.java:59)
      at java.sql.DriverManager.getConnection(DriverManager.java:571)
      at java.sql.DriverManager.getConnection(DriverManager.java:215)
      at Test.main(Test.java:8)

My Virtuoso as I said is the one from the develop/7 branch :

julien-plu@data:~$ isql-v
OpenLink Interactive SQL (Virtuoso), version 0.9849b.
Type HELP; for help and EXIT; to exit.
SQL> status('');
Connected to OpenLink Virtuoso
Driver: 07.10.3211 OpenLink Virtuoso ODBC Driver
REPORT
VARCHAR
_________________________________________________________

OpenLink Virtuoso  Server
Version 07.10.3211-pthreads for Linux as of Jul 11 2014
Started on: 2014-07-13 02:14 GMT+2

And my Java version is :

julien-plu@data:~$ java -version
java version "1.7.0_55"
Java(TM) SE Runtime Environment (build 1.7.0_55-b13)
Java HotSpot(TM) 64-Bit Server VM (build 24.55-b03, mixed mode)

Now here what is written inside the virtuoso.log file after each try to execute this little Java script :

02:04:09 Malformed data received from IP [127.0.0.1] : Box length too large. Disconnecting the client

Trace_on() and the option CallstackOnException setted at 2 in the virtuoso.ini file don't give any more information than the line above.

Hope all these informations will help.

Best.

Julien.

HughWilliams commented 10 years ago

Adding information from my responses to the email thread:

This JDBC program indicating the JDBC driver (virtjdbc4_1.jar) is causing the issue, did you compile this from source also ?

Running against the latest develop/7 build you sample program does run without error for me:

[virtuoso@masala vos]# javac -cp "/2d2/git/virt-develop7/libsrc/JDBCDriverType4/virtjdbc4_1.jar:." Test.java [virtuoso@masala vos]# java -cp "/2d2/git/virt-develop7/libsrc/JDBCDriverType4/virtjdbc4_1.jar:." Test [virtuoso@masala vos]# [virtuoso@masala vos]# more Test.java import java.sql.*; import java.sql.DriverManager; import java.sql.Connection;

public class Test { public static void main(String[] args) { try { Connection conn = DriverManager.getConnection("jdbc:virtuoso://localhost:1234/", "dba", "dba"); } catch (SQLException ex) { ex.printStackTrace(); } } }

[root@masala vos]#

[root@masala vos]# isql 1234 Connected to OpenLink Virtuoso Driver: 07.10.3211 OpenLink Virtuoso ODBC Driver OpenLink Interactive SQL (Virtuoso), version 0.9849b. Type HELP; for help and EXIT; to exit. SQL> status(''); REPORT VARCHAR


OpenLink Virtuoso Server Version 07.10.3211-pthreads for Linux as of Jul 12 2014 Started on: 2014-07-13 01:14 GMT+2

my JDBC Driver version which is the same as yours as would be expected being from the repo:

[virtuoso@masala vos]# java -jar /2d2/git/virt-develop7/libsrc/JDBCDriverType4/virtjdbc4_1.jar OpenLink Virtuoso(TM) Driver for JDBC(TM) Version 4.1 [Build 3.70]

My Java version is:

[virtuoso@masala vos]# java -version java version "1.7.0_05-icedtea" OpenJDK Runtime Environment (rhel-2.2.1.el6_3-x86_64) OpenJDK 64-Bit Server VM (build 23.0-b21, mixed mode)

The the problem would appear to be in the JDBC Client (although we are both using the same jar), The JDBC/Java runtime (as we are running slightly different versions) or the Virtuoso develop/7 server build, as although we are both compiling against the same develop/7 branch. might your build options to be different to mine ?

Can you confirm the Linux Distro and version being used as well as a copy of the Makefile see we can see what options are set for the compile ?

jplu commented 10 years ago
This JDBC program indicating the JDBC driver (virtjdbc4_1.jar) is causing the issue, did you compile this from source also ?

No, I took the one provided by default in the repository.

might your build options to be different to mine ?

I gave my configure options in my first thread, but here again :

./configure --prefix=/var/usr/virtuoso --program-transform-name="s/isql/isql-v/" --enable-dbpedia-vad --enable-rdfmappers-vad --enable-conductor-vad --enable-rdb2rdf-vad --enable-fct-vad --enable-ods-vad --enable-perl --with-readline

And if you really want the generated makefile, here a copy : http://data.lirmm.fr/download/Makefile

My linux distro is Ubuntu 14.04.

jplu commented 10 years ago

Hello !

I thought maybe that my virutoso.ini could help :

;
;  virtuoso.ini
;
;  Configuration file for the OpenLink Virtuoso VDBMS Server
;
;  To learn more about this product, or any other product in our
;  portfolio, please check out our web site at:
;
;      http://virtuoso.openlinksw.com/
;
;  or contact us at:
;
;      general.information@openlinksw.com
;
;  If you have any technical questions, please contact our support
;  staff at:
;
;      technical.support@openlinksw.com
;

;
;  Database setup
;
[Database]
DatabaseFile            = /var/usr/virtuoso/var/lib/virtuoso/db/virtuoso.db
ErrorLogFile            = /var/usr/virtuoso/var/lib/virtuoso/db/virtuoso.log
LockFile            = /var/usr/virtuoso/var/lib/virtuoso/db/virtuoso.lck
TransactionFile         = /var/usr/virtuoso/var/lib/virtuoso/db/virtuoso.trx
xa_persistent_file      = /var/usr/virtuoso/var/lib/virtuoso/db/virtuoso.pxa
ErrorLogLevel           = 7
FileExtend          = 200
MaxCheckpointRemap      = 10000
Striping            = 0
TempStorage         = TempDatabase

[TempDatabase]
DatabaseFile            = /var/usr/virtuoso/var/lib/virtuoso/db/virtuoso-temp.db
TransactionFile         = /var/usr/virtuoso/var/lib/virtuoso/db/virtuoso-temp.trx
MaxCheckpointRemap      = 10000
Striping            = 0

;
;  Server parameters
;
[Parameters]
ServerPort          = 1111
LiteMode            = 0
DisableUnixSocket       = 1
DisableTcpSocket        = 0
MaxQueryMem                     = 8G           ; memory allocated to query processor
VectorSize                      = 1000         ; initial parallel query vector (array of query operations) size
MaxVectorSize                   = 1000000      ; query vector size threshold.
AdjustVectorSize                = 0
ThreadsPerQuery                 = 8
AsyncQueueMaxThreads            = 10
;SSLServerPort          = 2111
;SSLCertificate         = cert.pem
;SSLPrivateKey          = pk.pem
;X509ClientVerify       = 0
;X509ClientVerifyDepth      = 0
;X509ClientVerifyCAFile     = ca.pem
ServerThreads           = 100
CheckpointInterval      = 60
O_DIRECT            = 0
CaseMode            = 2
MaxStaticCursorRows     = 5000
CheckpointAuditTrail        = 0
AllowOSCalls            = 0
SchedulerInterval       = 10
DirsAllowed         = ., /var/usr/virtuoso/share/virtuoso/vad, /var/www/download
ThreadCleanupInterval       = 0
ThreadThreshold         = 10
ResourcesCleanupInterval    = 0
FreeTextBatchSize       = 100000
SingleCPU           = 0
VADInstallDir           = /var/usr/virtuoso/share/virtuoso/vad/
PrefixResultNames               = 0
RdfFreeTextRulesSize        = 100
IndexTreeMaps           = 256
MaxMemPoolSize                  = 200000000
PrefixResultNames               = 0
MacSpotlight                    = 0
MaxSortedTopRows                = 400000

;;
;; When running with large data sets, one should configure the Virtuoso
;; process to use between 2/3 to 3/5 of free system memory and to stripe
;; storage on all available disks.
;;
;; Uncomment next two lines if there is 2 GB system memory free
;       NumberOfBuffers          = 170000
;       MaxDirtyBuffers          = 130000
;; Uncomment next two lines if there is 4 GB system memory free
;       NumberOfBuffers          = 340000
;       MaxDirtyBuffers          = 250000
;; Uncomment next two lines if there is 8 GB system memory free
;       NumberOfBuffers          = 680000
;       MaxDirtyBuffers          = 500000
;; Uncomment next two lines if there is 16 GB system memory free
;       NumberOfBuffers          = 1360000
;       MaxDirtyBuffers          = 1000000
;; Uncomment next two lines if there is 32 GB system memory free
;       NumberOfBuffers          = 2720000
;       MaxDirtyBuffers          = 2000000
;; Uncomment next two lines if there is 48 GB system memory free
;       NumberOfBuffers          = 4000000
;       MaxDirtyBuffers          = 3000000
;; Uncomment next two lines if there is 64 GB system memory free
;       NumberOfBuffers          = 5450000
;       MaxDirtyBuffers          = 4000000
;;
;; Note the default settings will take very little memory
;; but will not result in very good performance
;;
NumberOfBuffers          = 4000000
MaxDirtyBuffers          = 3000000

[HTTPServer]
ServerPort          = 8890
ServerRoot          = /var/usr/virtuoso/var/lib/virtuoso/vsp
ServerThreads           = 100
DavRoot             = DAV
EnabledDavVSP           = 0
HTTPProxyEnabled        = 0
TempASPXDir         = 0
DefaultMailServer       = localhost:25
ServerThreads           = 10
MaxKeepAlives           = 10
KeepAliveTimeout        = 10
MaxCachedProxyConnections   = 10
ProxyConnectionCacheTimeout = 15
HTTPThreadSize          = 280000
HttpPrintWarningsInOutput   = 0
Charset             = UTF-8
;HTTPLogFile                = logs/http.log

[AutoRepair]
BadParentLinks          = 0

[Client]
SQL_PREFETCH_ROWS       = 100
SQL_PREFETCH_BYTES      = 16000
SQL_QUERY_TIMEOUT       = 0
SQL_TXN_TIMEOUT         = 0
;SQL_NO_CHAR_C_ESCAPE       = 1
SQL_UTF8_EXECS          = 1
;SQL_NO_SYSTEM_TABLES       = 0
;SQL_BINARY_TIMESTAMP       = 1
;SQL_ENCRYPTION_ON_PASSWORD = -1

[VDB]
ArrayOptimization       = 0
NumArrayParameters      = 10
VDBDisconnectTimeout        = 1000
KeepConnectionOnFixedThread = 0

[Replication]
ServerName          = db-DATA
ServerEnable            = 1
QueueMax            = 50000

;
;  Striping setup
;
;  These parameters have only effect when Striping is set to 1 in the
;  [Database] section, in which case the DatabaseFile parameter is ignored.
;
;  With striping, the database is spawned across multiple segments
;  where each segment can have multiple stripes.
;
;  Format of the lines below:
;    Segment<number> = <size>, <stripe file name> [, <stripe file name> .. ]
;
;  <number> must be ordered from 1 up.
;
;  The <size> is the total size of the segment which is equally divided
;  across all stripes forming  the segment. Its specification can be in
;  gigabytes (g), megabytes (m), kilobytes (k) or in database blocks
;  (b, the default)
;
;  Note that the segment size must be a multiple of the database page size
;  which is currently 8k. Also, the segment size must be divisible by the
;  number of stripe files forming  the segment.
;
;  The example below creates a 200 meg database striped on two segments
;  with two stripes of 50 meg and one of 100 meg.
;
;  You can always add more segments to the configuration, but once
;  added, do not change the setup.
;
[Striping]
Segment1            = 100M, db-seg1-1.db, db-seg1-2.db
Segment2            = 100M, db-seg2-1.db
;...

;[TempStriping]
;Segment1           = 100M, db-seg1-1.db, db-seg1-2.db
;Segment2           = 100M, db-seg2-1.db
;...

;[Ucms]
;UcmPath            = <path>
;Ucm1               = <file>
;Ucm2               = <file>
;...

[Zero Config]
ServerName          = virtuoso (DATA)
;ServerDSN          = ZDSN
;SSLServerName          = 
;SSLServerDSN           = 

[Mono]
;MONO_TRACE         = Off
;MONO_PATH          = <path_here>
;MONO_ROOT          = <path_here>
;MONO_CFG_DIR           = <path_here>
;virtclr.dll            =

[URIQA]
DynamicLocal            = 1
DefaultHost         = localhost:8890

[SPARQL]
;ExternalQuerySource        = 1
;ExternalXsltSource         = 1
;DefaultGraph           = http://localhost:8890/dataspace
;ImmutableGraphs            = http://localhost:8890/dataspace
ShortenLongURIs         = 1
ResultSetMaxRows            = 100000000000
MaxQueryCostEstimationTime  = 400000    ; in seconds
MaxQueryExecutionTime       = 60000 ; in seconds
DefaultQuery                = select distinct ?Concept where {[] a ?Concept} LIMIT 100
DeferInferenceRulesInit     = 0  ; controls inference rules loading
;PingService            = http://rpc.pingthesemanticweb.com/

[Plugins]
LoadPath            = /var/usr/virtuoso/lib/virtuoso/hosting
Load1               = plain, wikiv
Load2               = plain, mediawiki
Load3               = plain, creolewiki
;Load4          = plain, im
;Load5      = plain, wbxml2
;Load6          = plain, hslookup
;Load7          = attach, libphp5.so
;Load8          = Hosting, hosting_php.so
;Load9          = Hosting,hosting_perl.so
;Load10     = Hosting,hosting_python.so
;Load11     = Hosting,hosting_ruby.so
;Load12             = msdtc,msdtc_sample
jplu commented 10 years ago

Hi,

I confirm that happens with this virtuoso.ini file, if I let the one provided by default it works. So What's going wrong with that customized virtuoso.ini file ?

HughWilliams commented 10 years ago

The problem seems to be the setting of the "SQL_UTF8_EXECS =1" in the "[Clients]" section of the INI file. Is there a reason this is set as JDBC in inherently UTF-8 by default so should not need to set it I think.

Will check with development as either way the error condition should be better handled ...

jplu commented 10 years ago

I need that because I use regex on a lot a french words on my sparql queries so I want Virtuoso configured in UTF-8 by default, as I don't use only JDBC to query it.

But nice to see that maybe the problem has been found :-)

HughWilliams commented 10 years ago

It will be fixed ...

smalinin commented 10 years ago

Fixed, patch will be in GIT soon.

diff --git a/libsrc/JDBCDriverType4/virtuoso/jdbc2/Driver.java b/libsrc/JDBCDriverType4/virtuoso/jdbc2/Driver.java
index 37a7c29..2d7c4ee 100644
--- a/libsrc/JDBCDriverType4/virtuoso/jdbc2/Driver.java
+++ b/libsrc/JDBCDriverType4/virtuoso/jdbc2/Driver.java
@@ -62,7 +62,7 @@ public class Driver implements java.sql.Driver
    // The major and minor version number
    protected static final int major = 3;

-   protected static final int minor = 72;
+   protected static final int minor = 73;

    // Some variables
    private String host = "localhost";
diff --git a/libsrc/JDBCDriverType4/virtuoso/jdbc2/VirtuosoExplicitString.java b/libsrc/JDBCDriverType4/virtuoso/jdbc2/VirtuosoExplicitString.java
index 2b0ba5f..f8c80e5 100644
--- a/libsrc/JDBCDriverType4/virtuoso/jdbc2/VirtuosoExplicitString.java
+++ b/libsrc/JDBCDriverType4/virtuoso/jdbc2/VirtuosoExplicitString.java
@@ -41,6 +41,16 @@ public class VirtuosoExplicitString
       this.dtp = dtp;
       this.bytes = bytes;
       this.str = null;
+      if (dtp == VirtuosoTypes.DV_STRING || dtp == VirtuosoTypes.DV_SHORT_STRING_SERIAL ||
+            dtp == VirtuosoTypes.DV_STRICT_STRING || dtp == VirtuosoTypes.DV_C_STRING ||
+            dtp == VirtuosoTypes.DV_BLOB || dtp == VirtuosoTypes.DV_ANY)
+        {
+            if (bytes.length < 256)
+                this.dtp = VirtuosoTypes.DV_SHORT_STRING_SERIAL;
+            else
+                this.dtp = VirtuosoTypes.DV_STRING;
+        }
+
     }

   protected VirtuosoExplicitString (String str, int dtp, VirtuosoConnection con) throws VirtuosoException
jplu commented 10 years ago

Cool ! Thanks :) I will test when the commit will be made.

HughWilliams commented 9 years ago

Hi Can you confirm if this problem is still an issue for you particularly with the latest Virtuoso 7.2 release where the fix should be available now ?

depressiveRobot commented 9 years ago

We have a similar problem with the setting SQL_UTF8_EXECS =1, Virtuoso Jena JDBC driver and Virtuoso 7.1.x / 7.2.x. Activating this options breaks the following query when using the Virtuoso Jena libraries:

SELECT *
WHERE {
    ?s ?p ?label .
    FILTER (contains (lcase(str(?label)), lcase('ü')))
}

Source code:

VirtGraph graph = new VirtGraph("jdbc:virtuoso://localhost:1111", "dba", "dba");
Query sparql = QueryFactory.create("SELECT * WHERE { ?s ?p ?label . FILTER (contains (lcase(str(?label)), lcase('ü'))) }");
VirtuosoQueryExecution vqe = VirtuosoQueryExecutionFactory.create(sparql, graph);
ResultSet results = vqe.execSelect();

Error:

com.hp.hpl.jena.shared.JenaException: Can not create ResultSet.:virtuoso.jdbc4.VirtuosoException: SL001: The SPARQL 1.1 LCASE() function needs a string value as 1st argument
    at virtuoso.jena.driver.VirtuosoQueryExecution.execSelect(Unknown Source)
    ...

However sending the query using the SPARQL endpoint or isql produces no error.

HughWilliams commented 9 years ago

What is the version of the Jena and JDBC Driver being used:

java -jar virt_jen2.jar
java -jar virtjdbc4.jar
depressiveRobot commented 9 years ago

We are using the corresponding Java libs delivered with each Virtuoso release:

Virtuoso 7.1.0: virt_jena2.jar - OpenLink Virtuoso(TM) Provider for Jena(TM) Version 2.10.1 [Build 1.9] virtjdbc4.jar - OpenLink Virtuoso(TM) Driver for JDBC(TM) Version 4.x [Build 3.65]

Virtuoso 7.2.0.1: virt_jena2.jar - OpenLink Virtuoso(TM) Provider for Jena(TM) Version 2.12.0 [Build 1.15] virtjdbc4.jar - OpenLink Virtuoso(TM) Driver for JDBC(TM) Version 4.x [Build 3.76]

smalinin commented 9 years ago

I have tried to execute query above via JDBC:

SELECT *
WHERE {
    ?s ?p ?label .
    FILTER (contains (lcase(str(?label)), lcase('ü')))
}

1) with SQL_UTF8_EXECS =1 in virtuoso.ini It throws the exception like above

VirtuosoException: SL001: The SPARQL 1.1 LCASE() function needs a string value as 1st argument

2) with SQL_UTF8_EXECS =0 in virtuoso.ini the query works fine and returns the properly resultSet from DBMS.

So option SQL_UTF8_EXECS =1 must be deleted from virtuoso.ini Note also, that when you works via Jena provider, it uses the JDBC URL connection string like: "jdbc:virtuoso://localhost:1111/charset=UTF-8" , so when you set SQL_UTF8_EXECS =1 also, your query is twice converted to UTF-8 and you got the error from DBMS.