Accessing remote monetdb

[krimp]

I am trying to access a monetdb-server on another machine in order to initiate a local embedded database with some initial values (C/C++).

However, the cache.c example accessing a remote database seems not to be up to date w.r.t the interface.

E.g. in

monetdbe_open(&remote, "monetdb://localhost:5000/sf1?user=monetdb&password=monetdb");

clearly a NULL is missing in the monetdbe_open(..., NULL) for the Option. However, adding the missing NULL does not seems to make the example work w.r.t be able to connect to the remote database.

Digging into the monetdbe.c, it seems to me like in order to force the monetdbe_open() to try to connect to a remote database, the remote-structure in the Option-structure needs to be filled in.

It would be of great help if you please could update the cache.c example and/or clarify how to connect to a remote named database.

If the option + remote struct has to be used: The remote struct seems to only have variable names for host, port, username, pw and language. How do one enter the database name ("sf1" in the cache-example or "demo" in the example below)?

To be specific, in the full monetdb-version we have e.g:
dbh = mapi_connect("192.168.1.21", 50000, "monetdb", "monetdb", "sql", "demo");

How would the equivalent monetdbe look like?

[krimp]

Did some debugging:

Seems like both url and options have to be properly initialized in order to get monetdbe_open() to try to connect a remote database:

monetdbe_open(&remote, "mapi:monetdb://localhost:50000/demo",&options);

username and pw must be initialized in options...

Added some printouts in monetdbe.c (at line ~695 and onwards):

            if (p == NULL) {
                mdbe->msg = createException(MAL, "monetdbe.Ok", MAL_MALLOC_FAIL);
                printf("msg2: %s\n", mdbe->msg);
                return -2;
            }

            if ( (mdbe->msg = chkProgram(c->usermodule, mb)) != MAL_SUCCEED ) {
                printf("msg3: %s\n",mdbe->msg );
                return -2;
            }

            MalStkPtr stk = prepareMALstack(mb, mb->vsize);
            stk->keepAlive = TRUE;
            if ( (mdbe->msg = runMAL(c, mb, 0, stk)) != MAL_SUCCEED ) {
                printf("msg4: %s\n",mdbe->msg );
                return -2;
            }

With url, username and pw in options as above the following error msg was returned form runMAL():

msg4: TypeException:remote.connect:(mapi:monetdb://monetdb@localhost/demo) 'sql.set_protocol' undefined in: sql.set_protocol(3:int);

[bernardom29]

So, first of all, a disclaimer: this part of the connection API is still a work in progress, so the syntax for connecting to a remote database may be changed in the future. This is why some of the older tests in the example repository feature old syntax (like the cache.c example you mentioned). When the API is stable, these examples will be updated.

Currently, to connect to a remote database, you need to fill in the monetdbe_remote structure (found within monetdbe_options) that is passed to monetdbe_open().

Here's an example:

        monetdbe_options *opts = malloc(sizeof(monetdbe_options));
        monetdbe_remote *remote = malloc(sizeof(monetdbe_remote));

        remote->host = "localhost";
        remote->port = 50000;
        remote->username = "monetdb";
        remote->password = "monetdb";
        //You can set this to NULL, as it's not used.
        remote->lang = NULL;
        opts->remote = remote;

        //You can set more options

        monetdbe_database *db = malloc(sizeof(monetdbe_database));
        int error_code = monetdbe_open(db, NULL opts);

I suspect that you didn't set one of the variables in monetdbe_remote, but if the problem persists or you have any other question, please ask.

[krimp]

I did it almost exactly as in your example. I used

    monetdbe_options opts;
    monetdbe_remote remote
    remote.host = "localhost";
    remote.port = 50000;
    remote.username = "monetdb";
    remote.password = "monetdb";
    remote.lang = NULL;
    opts.remote = &remote;

    monetdbe_database db; 
    int error_code = monetdbe_open(&db, "mapi:monetdb://localhost:50000/demo", &opts);

I also tried your example (in C++):

        monetdbe_options *opts = (monetdbe_options *)malloc(sizeof(monetdbe_options));
        monetdbe_remote *remote = (monetdbe_remote *)malloc(sizeof(monetdbe_remote));

        remote->host = "localhost";
        remote->port = 50000;
        remote->username = "monetdb";
        remote->password = "monetdb";
        remote->lang = NULL;
        opts->remote = remote;

        monetdbe_database *db = (monetdbe_database *)malloc(sizeof(monetdbe_database));
        int error_code = monetdbe_open(db, "mapi:monetdb://localhost:50000/demo", opts);        

In both tests the following error msg was returned from runMAL() in monetdbe.c:

            MalStkPtr stk = prepareMALstack(mb, mb->vsize);
            stk->keepAlive = TRUE;
            if ( (mdbe->msg = runMAL(c, mb, 0, stk)) != MAL_SUCCEED ) {
                printf("msg4: %s\n",mdbe->msg );
                return -2;
            }

msg4: TypeException:remote.connect:(mapi:monetdb://monetdb@localhost/demo) 'sql.set_protocol' undefined in: sql.set_protocol(3:int);

This was on a Ubuntu 18.04 LTS, 64 bit VM in Azure.

[aris-koning]

First of all, I want to let you know that we appreciate your efforts to test the proxy feature in monetdbe 👍

In the current API (latest default branch), when setting up a remote configuration, you have to be sure that the second parameter url of monetdbe_open is NULL. At the current state of the API effectively all of the remote configuration is consolidated into the monetdbe_remote struct. Please check again @bernardom29's updated comment above for more details.

That said, this sql.set_protocol is worrying me. I am not sure why that is happening. I'll come back on that later.

[aris-koning]

@krimp Which version of monetdb are using for the server side?

[krimp]

With the NULL as the second argument in monetdb_open(), chkProgram(c->usermodule, mb) in monetdbe.c returns error msg: "MALException:pushStr:Can not allocate string variable" in:

            if ( (mdbe->msg = chkProgram(c->usermodule, mb)) != MAL_SUCCEED ) {
                printf("msg3: %s\n",mdbe->msg );
                return -2;
            }

The monetDB-server is Jun-2020-SP1 running on a Ubuntu 18.04 LTS, 64 bit VM in Azure.

PS! I realize @bernardom29 has changed his answer, but it does not work in my application.

[krimp]

As an update I got the same connection error as in the Azure VM in monetdbe.c. with monetdbe running on an RPi4B+ (32bit, 4GB ram) and the remote server on an another RPi4B+ (64Bit, 8GB ram) with monetDB Oct2020-SP3 with Ubuntu 20.04 LTS 64 bit...

err4: TypeException:remote.connect:(mapi:monetdb://monetdb@ubuntuRPi/nmpc) 'sql.set_protocol' undefined in: sql.set_protocol(3:int); # select: reverse sorted; hashselect; select: fullscan equi strelim

[MitchellWeg]

Whats the version of MonetDB running on the 32bit system? if it's the June2020 version, it won't work since June2020 does not support MonetDBe. Currently remote only works on default, and it's still a work-in-progress.

[krimp]

The 32bit system is running MonetDB/e...

What do you mean by " Currently remote only works on default"?

[njnes]

with default we mean the default branch of our MonetDB hg repo. (https://dev.monetdb.org/hg)

[krimp]

I downloaded the "default" branch on both a 64 bit Ubuntu and a 32 bit Buster. It seem to me that monetdb/e is a part of monetdb, and that the libmonetdbe.so is generated when compiling the full monetdb...

With the "default" ( MonetDB v11.40.0) I experience the following:

RPi4B+ 64bit Ubuntu 20.04 LTS:
MonetDB v11.40.0 compiles and installs without issues

RPi4B+ 32bit Buster:
MonetDB v11.40.0 does not compile with cmake flag STRICT = ON. Error in function ‘store_init’:
.../MonetDBe/sql/storage/store.c:1892:18: error: conversion from ‘long long unsigned int’ to ‘long unsigned int’ changes value from ‘9223372036854775808’ to ‘0’ [-Werror=overflow]
.transaction = ATOMIC_VAR_INIT(TRANSACTION_ID_BASE)

With cmake flag STRICT = OFF MonetDB v11.40.0 compiles and installs without issues on the 32bit machine, and my application using monetdb/e connects to the remote monetDB server (on the 64 bit Ubuntu machine).
However, it segfaults in:

char *msg = monetdbe_query(dbR, "SELECT * FROM param;", &result, NULL);

Some more of the test code:

'
....
static monetdbe_database dbR = NULL;
static monetdbe_remote dbRemote;
static monetdbe_options dbOptionsR;

dbRemote.host     = "192.168.1.71" ;//monetDB_ipAddress;
dbRemote.port     = 50000;          //monetDB_portNum;
dbRemote.database = "demo";         //monetDB_database;
dbRemote.username = "monetdb";      //monetDB_user;
dbRemote.password = "monetdb";      //monetDB_pw;

dbOptionsR.remote = &dbRemote;

int statusDB = monetdbe_open(&dbR, NULL, &dbOptionsR);
if (statusDB < 0) {
    printf("Failed to connect to remote monetdb: ErrNo %i\n",statusDB);
}
else {
    printf("Remote monetdb connected: %i\n",statusDB);
    char *query = "select * from param order by t desc;";
    char *msg   = monetdbe_query(dbR, query, &result, NULL);
    if ( result->nrows > 0) {
     	       printf("Selected : %i rows\n",result->nrows);
    }
    monetdbe_cleanup_result(dbR, result);
    monetdbe_close(dbR);
}

'
So my conclusion is that remote does not work on the default branch (MonetDB v11.40.0).

[njnes]

Just tested on 'buster' 32 bit. When using a 32 bit server and monetdbe, it works fine. Same for both 64 bits.
The problem is the binary protocol between the monetdbe and mserver5 its currently not capable of changing results from a 64 bit server into the 32 bit monetdbe.

[njnes]

it maybe better to start a new issue just on the 32/64 bit issue.

[krimp]

I ended up with a temporary workaround for accessing the 64bit remote server database from the 32bit application by using the mapi library. No issues going that route.