Error “Can not find the Java Runtime Environment needed by EXAplus GUI. Trying to use system wide virtual machine (from registry) instead. No 64 bit java virtual machine found in registry.”

Don’t you just love that new machine setup process…said nobody ever. Today I got hold of my new machine at work (which is great, thanks guys!). First job is to get all of the programs on that I need.

After the essentials, I got round to installing Exaplus. You can get it from here

I ran the install and got this error on completion:

Can not find the Java Runtime Environment needed by EXAplus GUI. Trying to use system wide virtual machine (from registry) instead. No 64 bit java virtual machine found in registry.

This rang a bell, and was quite similar to this error I’ve had before.

This time round, it was because I didn’t have the 64 bit Java. To fix it, I got the 64 bit download for Java from here.

The install did not fail, it was just the opening of Exaplus that was causing the error. With 64 bit Java installed, Exaplus loaded fine.,

 

 

Error: [27002] constraint violation – primary key (PK_ on table TABLE) (Session: )

You might get this error if you enter in a duplicate value in your primary key column. Seems pretty standard and fair enough. But what about when you’re not?

This error can occur when you have an IDENTITY primary key column, that you have entered values into.

For example my table looked something like this:

CREATE TABLE DIM_TBL_STORE (
STOREID DECIMAL(18,0) IDENTITY NOT NULL ENABLE,
STORENAME VARCHAR(250) UTF8);
ALTER TABLE DIM_TBL_STORE ADD CONSTRAINT PK_DIM_TBL_STORE PRIMARY KEY(STOREID) ENABLE;

We can then check the value of the identity column; COLUMN_IDENTITY returns 0 as expected. All good.

select * from SYS.EXA_ALL_COLUMNS
where Column_schema = 'ROSE3'
AND COLUMN_TABLE = 'DIM_TBL_STORE'
AND COLUMN_NAME = 'STOREID';

Let’s then add some core stores with set IDs :

INSERT INTO DIM_TBL_STORE
SELECT 1, "StoreA"
UNION SELECT 2, "StoreB"
UNION SELECT 3, "StoreC"

SELECT * FROM DIM_TBL_STORE

This shows the 3 rows as expected.

What is unexpected is that the identity value is still zero. You can check this by running the exa_all_columns query, or looking at the info for the table in exaplus.

IdentityColumnWithZero

So next, let’s add a new store, this time not specifying a value for storeid.

INSERT INTO DIM_TBL_STORE (STORENAME)
SELECT 'STORED';

From this we get the error

Error: [27002] constraint violation - primary key (PK_ on table TABLE) (Session: )

Oops. Now we can’t enter any data, because the identity value being offered up by the table is 1. But 1 is taken! So, in fact, are 2 and 3. We need value 4.

To do this, the Identity value needs to be reset to a value after the max identity of the table.

ALTER TABLE DIM_TBL_STORE
ALTER COLUMN storeid SET IDENTITY 4;

Checking the exa_all_columns table, you can now see the value being 4.

The insert will now succeed.

In SQL server, we would have got around this by toggling IDENTITY_INSERT on or off, and SQL Server would have dealt with securing us the next unique value. With Exasol we’ve got to manage it a bit more.

Error “insufficient privileges for using connection”

First of all, if you’d like to know how to setup a connection from scratch, please check out this post I wrote on setting up an SFTP connection in Exasol.

However, if you’ve just got the error “insufficient privileges for using connection”, chances are you forgot to grant permissions to some other user, who has tried to use this connection.

GRANT CONNECTION MY_SFTP_CONNECTION to ANOTHER_EXASOL_USER;

This will allow the user to use the connection in conjunction with any other database object they have permissions to use.

HOW TO: Make sure EXAPlus aborts execution on Error

ErrorHandling

It surprised me to learn that by default, on executing multiple commands in EXAPlus, the execution does not stop on error.

I was happily executing batches of code and thinking that everything was fine, as it all executed so quickly, and the last message displaying in the log panel was successful, so therefore, everything worked out…right? WRONG!

My code had a bug in one of the middle scripts, but despite the execution encountering the error, the execution as a whole continued.

Luckily, this was highlighted to me at exaxp16, by one of the support team.

So, here’s how to set the flag, to abort execution on error in EXAPlus:

In EXAPlus, go to SQL > Error Handling > All Errors > Select Abort

Error “Can not find the Java Runtime Environment needed by EXAplus.” when running from the command line

To run scripts in EXASOL from the command line, you might try to run the following code.

C:\Users\You>"C:\Program Files (x86)\EXASOL\EXASolution-5.0\EXAplus\Exaplus.exe" 
-c SERVER_IP_HERE -u USER_HERE -p PASSWORD_HERE

For me, this resulted in the following error.

Can not find the Java Runtime Environment needed by EXAplus.
Trying to use system wide virtual machine (from registry) instead.
No 32 bit java virtual machine found in registry.

I had Java installed, but not the version Exasol was looking for.

To solve this, I then downloaded and installed the latest version of java from https://java.com/en/download/manual.jsp , then restarted my computer.

 

Error: [64134] Unable to get a stable set of rows in the source tables

When performing an UPDATE statement you may get this error message:

[64134] Unable to get a stable set of rows in the source tables (Session: )

This is because when EXASOL is performing the UPDATE, under the covers it is really doing a MERGE. You can read more about this in Chapter 2 of the Exasol manual, but the highlight is as follows:

Internally, this statement is transformed into a MERGE statement. Therefore, error
messages can refer to the MERGE command. If one row is updated multiple times,
the new value must be identical. Otherwise you will get the error message “Unable
to get a stable set of rows in the source tables”.

Essentially, make sure that a row is only being updated once.