Issue with Virtual Schema and JDBC Adapter

harry
Contributor

Hi everyone!

I've been trying to register a virtual schema on an Exasol DB (within a docker container), but it get following error when creating the schema:

F-UDF-CL-LIB-1125: F-UDF-CL-SL-JAVA-1000: F-UDF-CL-SL-JAVA-1037: com.exasol.ExaCompilationException: F-UDF-CL-SL-JAVA-1158: /JDBC_ADAPTER.java:1: error: cannot access com.exasol package com.exasol; ^ zip END header not found

I am using the current docker-latest image (Exasol 7.0 if I am not mistaken). Furthermore, I followed the suggestions on the READMEs of the virtual schemas, that is: I created new bucket and uploaded virtual schema generic JDBC jar and my jdbc driver (also tested that they are correctly uploaded). Then I am running the following commands:

CREATE SCHEMA ADAPTER;

CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jar /buckets/newbucketfs/newbucketfs-bucket/virtual-schema-dist-9.0.3-generic-2.0.0.jar;
%jar /buckets/newbucketfs/newbucketfs-bucket/postgresql-42.2.18.jar;
/

CREATE OR REPLACE CONNECTION GENERIC_CONNECTION
TO 'jdbc:postgresql://myhost:5432'
USER 'usr'
IDENTIFIED BY '***';

CREATE VIRTUAL SCHEMA virt_schema
USING ADAPTER.JDBC_ADAPTER
WITH
CATALOG_NAME = 'db1'
CONNECTION_NAME = 'GENERIC_CONNECTION';

I have also tried using the specialized PostgreSQL virtual schema, but I get the exact same error.

Did I forget to run something? Could it be that my docker setup is faulty? Grateful for any hints!

1 ACCEPTED SOLUTION

exa-SebastianB
Team Exasol
Team Exasol

@harry, could you please validate all downloaded JAR files on your machine with the test mode of the zip tool?
https://linux.101hacks.com/archive-compression/validate-zip-file/

If they are clean on your machine, ssh into the docker instance and run the same command there too, to see if they got corrupted by the upload to the container. You can also compare the hash sums on your machine and on the container.
Another thing you should check just in case is if there is enough free space for the container, so that ZIP can be expanded.

View solution in original post

12 REPLIES 12

exa-MathiasHo
Community Manager
Community Manager

Hi @harry

Did the suggestion solutions help you or do you need further assistance?

harry
Contributor

Hi @exa-MathiasHo, first of all thanks everyone for your answers!

I have tried following all suggestions, but unfortunately my issue persists. I will describe my setup & steps again:

I run Exasol using the following docker-compose:

 

 

version: "3.7"

services:

  exasol:
    image: exasol/docker-db:latest
    container_name: exa1
    ports:
      - 9563:8563
      - 8899:8888
      - 8081:80
      - 6932:6932
      - 2580:2580
    privileged: true

networks:
  default:
    name: exa-net

 

 

 

First, as suggested by @exa-SebastianB and @mwellbro I upload my Postgres driver and virtual schema jars (downloaded from the Exasol's github repo releases) to the default bucketfs:

 

 

curl -v -X PUT -T postgresql-42.2.18.jar http://w:JPu0EEe9itW8YefGslpTT1@localhost:2580/default/drivers/jdbc/postgres/postgresql-42.2.18.jar
curl -v -X PUT -T system.cfg http://w:JPu0EEe9itW8YefGslpTT1@localhost:2580/default/drivers/jdbc/postgres/system.cfg
curl -v -X PUT -T virtual-schema-dist-9.0.3-generic-2.0.0.jar http://w:JPu0EEe9itW8YefGslpTT1@localhost:2580/default/virtualschemas/virtual-schema-dist-9.0.3-generic-2.0.0.jar
curl -v -X PUT -T virtual-schema-dist-9.0.3-generic-2.0.0.jar http://w:JPu0EEe9itW8YefGslpTT1@localhost:2580/default/virtualschemas/virtual-schema-dist-9.0.1-postgresql-2.0.0.jar

 

 

The content of my system.cfg file looks as follows:

 

 

DRIVERNAME=MY_POSTGRES_DRIVER
JAR=postgresql-42.2.18.jar
DRIVERMAIN=org.postgresql.Driver
PREFIX=jdbc:postgresql:
FETCHSIZE=100000
INSERTSIZE=-1

 

 

To make sure that my files are there, I list the bucket contents:

curl -v http://r:2zwJ6VNqGvd82YzUNYIbT6@localhost:2580/default/

and I get the following files, which shows that my files are uploaded correctly:

 

 

EXASolution-7.0.8/udfplugin_protegrity_rest
EXASolution-7.0.8/udfplugin_protegrity_710
EXASolution-7.0.8/udfplugin_protegrity
EXAClusterOS/ScriptLanguages-standard-exasol-7.0.0-20201109.tar.gz
virtualschemas/virtual-schema-dist-9.0.1-postgresql-2.0.0.jar
drivers/jdbc/postgres/postgresql-42.2.18.jar
EXASolution-7.0.8/udfplugin_protegrity_665_ua
EXASolution-7.0.8/udfplugin_protegrity_665
drivers/jdbc/postgres/system.cfg
EXASolution-7.0.8/udfplugin_protegrity_rest_ua
EXASolution-7.0.8/udfplugin_protegrity_ua
EXASolution-7.0.8/udfplugin_protegrity_710_ua
EXASolution-7.0.8/udfplugin_protegrity_664
EXASolution-7.0.8/udfplugin_protegrity_664_ua
virtualschemas/virtual-schema-dist-9.0.3-generic-2.0.0.jar

 

 

Then, I start issuing the Virtual Schema creation commands (tried both with DataGrip and Exaplus), also trying out the commented versions suggested by @exa-Anastasiia.

 

 

CREATE SCHEMA ADAPTER;

--/
CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jar /buckets/bfsdefault/default/virtualschemas/virtual-schema-dist-9.0.3-generic-2.0.0.jar;
%jar /buckets/bfsdefault/default/drivers/jdbc/postgres/postgresql-42.2.18.jar;
/
;

CREATE OR REPLACE CONNECTION GENERIC_CONNECTION
TO 'jdbc:postgresql://localhost:5432/'
USER 'postgres'
IDENTIFIED BY '******';

CREATE VIRTUAL SCHEMA virt_schema
USING ADAPTER.JDBC_ADAPTER
WITH
CATALOG_NAME = 'db1'
CONNECTION_NAME = 'GENERIC_CONNECTION';

 

 

  and then I get following error:

 

 

EXA: CREATE VIRTUAL SCHEMA virt_schema...
Error: [22002] VM error: F-UDF-CL-LIB-1125: F-UDF-CL-SL-JAVA-1000: F-UDF-CL-SL-JAVA-1037: 
com.exasol.ExaCompilationException: F-UDF-CL-SL-JAVA-1158: /JDBC_ADAPTER.java:1: error: cannot access com.exasol
package com.exasol;
^
  zip END header not found

 

 

Unfortunately I do not know yet the Exasol internals, in order to reason about where this error might be coming from, but it seems as some misconfiguration issue?

By the way, I also tried the https://github.com/exasol/virtual-schema-installer but there does not seem to exist a release yet. I tried to build the project with mvn package, but that wont work right now. I guess I can try again when there are some releases.

And again, thanks everyone for your suggestions up to now, those are really appreciated!

Best,

Harry

exa-SebastianB
Team Exasol
Team Exasol

@harry, could you please validate all downloaded JAR files on your machine with the test mode of the zip tool?
https://linux.101hacks.com/archive-compression/validate-zip-file/

If they are clean on your machine, ssh into the docker instance and run the same command there too, to see if they got corrupted by the upload to the container. You can also compare the hash sums on your machine and on the container.
Another thing you should check just in case is if there is enough free space for the container, so that ZIP can be expanded.

View solution in original post

harry
Contributor

Hi @exa-SebastianB. I can't believe it, but this was indeed the problem. The jar files downloaded through curl were broken... Thanks again everyone for your time!

Best,

Harry

exa-SebastianB
Team Exasol
Team Exasol

Don't beat yourself up about it. I learned an important lesson here too. We need to automate adding hash sum files to all of our release artifacts. At the moment we are doing that sporadically, but they should be available next to each distribution file by default.

A simple hash sum comparison would have avoided your whole problem, but for that we need to make sure that we provide those hashes in all cases.

And I just realized that we already have a ticket for that (https://github.com/exasol/release-droid/issues/30) looks like I have to raise the rank on that one.

exa-SebastianB
Team Exasol
Team Exasol

Hi @harry, since this is a message that I never witnessed before, I will ask one of our UDF experts. The error chain should help locate the part of the UDF where the error originates.

exa-MathiasHo
Community Manager
Community Manager

Hi @harry,

I have forwarded your situation once again internally. As this seems like there is no quick solution, please be a little patient, it might take a day or two to get the right answer.

Best regards,
Mathias

exa-Anastasiia
Team Exasol
Team Exasol

Hello @harry 

I can see two possible problems with the scripts you sent.

1. In some SQL clients an adapter script is not parsed correctly without comment (--/) before the script. This comment helps to parse the script correctly. A working example:

--/
CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jar /buckets/bfsdefault/virtualschemas/virtual-schema-dist-9.0.3-generic-2.0.0.jar;
%jar /buckets/bfsdefault/postgres/postgresql-42.2.6.jar;
/
;

 2. In the connection string a slash (/) is missing. A working example:

CREATE OR REPLACE CONNECTION GENERIC_CONNECTION
TO 'jdbc:postgresql://localhost:5432/'
USER 'postgres'
IDENTIFIED BY '';

 

Also as @exa-SebastianB mentioned, it's better to use Postgres Virtual Schema instead of Generic. But Generic should also work, I've just tested it.
Please let me know if you still have problems with it.

exa-SebastianB
Team Exasol
Team Exasol

Morning @harry ,

using the dedicated VS is always better than the generic one, since the generic VS implements no capabilities (like JOIN for example).

Your particular problem looks like an unresolved dependency to me. The JAR files of the Virtual Schema and the Driver must be uploaded to BucketFS. The driver must be in a specific bucket in a specific path and registered in a specific configuration file.
See https://github.com/exasol/docker-db#installing-custom-jdbc-drivers for details on driver installation. Please follow these steps. If all dependencies are satisfied, you should be able to create the VS.
Also please use the precompiled JAR from the GitHub releases, just to be sure.

Our @exa-Anastasiia is working on an installer by the way that does all these steps for the users, including downloading the VS from GitHub: https://github.com/exasol/virtual-schema-installer
The first release (which I expect in a couple of days) will cover MySQL and Postgres. Other dialects will follow soon.

mwellbro
Xpert

Hi @harry ,

as you mentioned you followed the READMEs of the virtual schema guides - as far as I understood it the docker way is a bit different, i.e.:

mwellbro_0-1626993824990.png

 

src: https://github.com/exasol/docker-db#installing-custom-jdbc-drivers

Maybe you need to pay special heed to the "you need to upload it into a subdirectory of drivers/jdbc of the default bucket (which is automatically created if you don't modify EXAConf)" passage, as well as the required ".cfg" file ? But I´m just guessing here.

Haven´t tried this yet, ramped up an AWS linux host,grabbed docker and pulled the exa-latest image , also got that to run but had to
put a stop to my experiments at that point - should I get it to run ( even though it feels somehow alien when you´ve worked with ExaOperation up to now ) I´ll post here again.

Cheers,
Malte

harry
Contributor

Hi @drumcircle ,

Thanks for your answer! I have tried issuing the above mentioned commands through SQL clients (DataGrip & Exaplus). Is there any specific option that I should look at?

Best,

Harry

drumcircle
Rising Star

Can you verify that your compiled project includes all the dependencies.  They need to be packaged with the (fat) jar as I recall.