- users-list - monetdb.org

ODBC Error on Insert
by Doherty, Adam 06 Jun '18

06 Jun '18

Hi All: I'm having some issues using ODBC with C#. I am trying to insert the contents of a DataTable into Monet using a foreach loop with MonetDB ODBC client under Windows. This is the error I receive... System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [MonetDB][ODBC Driver 11.27.11]syntax error, unexpected ',' in: "execute 5 (timestamp '2018-06-04 21:22:25',8651,'EVENT','','SYSTEM','1810_INFRA_" This is my loop which is wrapped in a transaction - foreach (DataRow row in events.Rows) { cmd.CommandText = String.Format("INSERT INTO chronicle.events_{0}_zone_{1} VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", year, zone); cmd.Parameters.AddWithValue("@date_time", row["Date_Time"]); cmd.Parameters.AddWithValue("@fracsec", row["FracSec"]); cmd.Parameters.AddWithValue("@event_type", row["Event_Type"]); Truncated for brevity. I'm not sure where the error in my insert statement is because the error message itself appear to be truncated. Very frustrating as it hangs on the first row. Adam

2 1

new MonetDB SQL overview documentation
by martin van dinther 31 May '18

31 May '18

1 0

JIT implementation of the rmath extension
by Mark Clements 30 May '18

30 May '18

The JIT functions by Mark Raasveldt provide a powerful alternative approach to bring C and C++ code into MonetDB. I have provided an alternative implementation of the MonetDB-rmath extension using JIT functions. The JIT implementation is considerably shorter and simpler, and slightly more general - where the code can deal with function arguments that are any combination of scalars and BATs. However, there are indications that the functions are considerably slower. As a minor point, I needed to rename the functions (e.g. using r_ as a prefix) -- otherwise the function names would collide with the function signatures in Rmath.h. To use the JIT-based rmath extension: git clone https://github.com/mclements/MonetDB-rmath.git git checkout jit make sudo make install monetdb create test monetdb release test monetdb set embedc=yes test mclient -d test -s "select r_pnorm(1.96,0,1);" As a challenge for JIT-based extensions, new C functions (e.g. new functions that call Rmath.h, and for which we want to call the new functions in MonetDB) will need a header file that is available to MonetDB. A fragile (non-portable?) solution to find the MonetDB include folder is to use: MONETDBINCLUDE = $(patsubst -I%,%,$(firstword $(shell pkg-config --cflags-only-I monetdb5))) in the Makefile. This assumes that the MonetDB include folder is the first include folder in CFLAGS. The extension-specific header for any new functions can then be copied to that folder: cp rmath_ext.h $(MONETDBINCLUDE) I hope that this is useful. Sincerely, Mark.

1 0

Error: Failed to import - Leftover Data
by Doherty, Adam 23 May '18

23 May '18

Hi all: I am receiving an error "Failed to import table ... Leftover data ..." I am using a simple BASH script and the mclient tool to import my data from hundreds of CSV files. The fields are pipe | separated, text values are surrounded with double-quotes, and rows end with LF (\n). This is the command I am using to process the files - for csvfile in `ls | grep -i 2018.*\.csv$`; do mclient -d mydatabase -s "COPY INTO table FROM '/path/to/csv/files/$csvfile' USING DELIMITERS '|', '\n', '\"'"; done I have a .monetdb file setup in my home directory so as not to need the username and password. Thanks kindly, Adam Doherty

3 5

MonetDB Memory usage and release cached memory
by Murthy, Gautham 17 May '18

17 May '18

Hello, How do we make MonetDB release memory after usage. We are seeing high cached memory on the DB Servers and We also see queries failing with the below error - Memory Allocation Failure : Total Memory - 1123348 Physical Memory - 1123348 Virtual Memory - 2667052 How do we overcome this issue? Thank You, Gautham

1 0

Problem/bug with remote tables
by Bernát Marcell 10 May '18

10 May '18

Hello everyone, I believe I've encountered a bug with the remote table functionality. Here are the steps to replicate it. Tested with MonetDB 11.29.3, both on Ubuntu 14.04.5 LTS and Ubuntu 16.04.4 LTS. The problem persists even after restarting the DB farms and the MonetDB daemons. On node 1 Create a normal table, insert some data and check its contents: create table testtable(id int not null, name text, valid boolean); insert into testtable (id, name) values (1, '1'); insert into testtable (id, name) values (2, '2'); insert into testtable (id) values (3); select * from testtable; +------+------+-------+ | id | name | valid | +======+======+=======+ | 1 | 1 | null | | 2 | 2 | null | | 3 | null | null | +------+------+-------+ 3 tuples (6.446ms) On node 2 Create a remote table and check its contents to make sure it works: create remote table testtable(id int not null, name text, valid boolean) on 'mapi:monetdb://node1:50000/dbfarm'; select * from testtable; +------+------+-------+ | id | name | valid | +======+======+=======+ | 1 | 1 | null | | 2 | 2 | null | | 3 | null | null | +------+------+-------+ 3 tuples (6.446ms) Then try to select anything with a WHERE clause: select * from testtable where name is null; +------+------+-------+ | id | name | valid | +======+======+=======+ | 3 | null | null | +------+------+-------+ 1 tuple (5.186ms) It seemingly works fine, but now when you try to select anything else, you get the same result: select * from testtable; +------+------+-------+ | id | name | valid | +======+======+=======+ | 3 | null | null | +------+------+-------+ 1 tuple (4.466ms) If you log out from mclient and then back in, select * works fine again, so there's no data loss. However, even if you try the opposite of the previous query (is not null instead of is null), you get the same result: select * from testtable where name is not null; +------+------+-------+ | id | name | valid | +======+======+=======+ | 3 | null | null | +------+------+-------+ 1 tuple (5.358ms) Can someone else please try this to confirm it's a bug and not something on my end? Thanks in advance. Best regards, Marcell

3 4

Announcement: MonetDB-rmath
by Mark Clements 09 May '18

09 May '18

Dear all, A first version for MonetDB-rmath is now available. It includes approximately 90% of the functions in Rmath.h, including [rpdq](norm|unif|gamma|beta|lnorm|chisq|nchisq|f|t|binom|cauchy|exp|geom|hyper| nbinom|pois|weibull|logis|wilcox|signrank) and another 40 functions. The current implementation uses m4 for code generation and requires the r-mathlib package under Debian. One can clone the GitHub repository and install using: git clone https://github.com/mclements/MonetDB-rmath.git cd MonetDB-rmath make sudo make install You will then need to use a fresh database. An example script is: -- calculate the quantile and cumulative distribution function for a unit normal select qnorm(0.025,0,1), pnorm(-1.96,0,1); -- calculate E(Z^2) for Z~Normal(0,1) select sum(R_pow(value,2)*0.01*dnorm(value,0,1)) from sys.generate_series(-5.0,5.0,0.01); -- random numbers select set_seed(1,2); select rpois(value*0+100) from sys.generate_series(1,10); -- ok select rpois(100) from sys.generate_series(1,10); -- repeats the values 10 times:-< All of the functions are currently scalars. Next steps include adding BAT functions, including some of the constants at the beginning of Rmath.h, and adding some statistical tests. As an example, I have included poissonci(y,boundary) for an exact Poisson confidence interval for count y for a specific boundary (1=left, 2=right) with default confidence level of 95%: select poissonci(10,1), poissonci(10,2); The equivalent code in R would be poisson.test(10). For confidence intervals, it would be nice to have a type that is a pair of doubles. Sincerely, Mark.

2 2

Buggy attempt at MonetDB-rmath
by Mark Clements 03 May '18

03 May '18

I am trying to adapt MonetDB-gsl to use the rmath library. My code is at: https://github.com/mclements/MonetDB-rmath Under Ubuntu 16.04 LTS, I used: sudo apt install r-mathlib git clone https://github.com/mclements/MonetDB-rmath cd MonetDB-rmath make sudo make install // start up MonetDB server with a test database mclient -d test -s "select sys.pchisq(20.0,5.0);" This compiles but when I try "select sys.pchisq(20.0,5.0);" I get the error: SELECT: no such binary operator 'pchisq(decimal,decimal)' Any guidance would be appreciated. (You may ask why one would want to do this when one could use embedded R - I wanted to see how fast and easy it was to use the rmath library.) Kindly, Mark.

2 3

Copy a set database files and register on another database
by Murthy, Gautham 24 Apr '18

24 Apr '18

Hello, On MonetDB, Can we list the files for a schema and copy the files and register to another database ? We would want to know if we can - List the files for a specific table or a schema(all tables under a schema)? Copy the files to target database and register them on the other database Thank You,

1 0

MonetDB Read-Only without restarting the database
by Murthy, Gautham 24 Apr '18

24 Apr '18

Hello, This is regarding read-only mode in MonetDB. Can we mark the database read-only without restarting the database ? If Yes, How do we do it ? What are the options available to copy a database without restarting to another node or DB Farm? Msqldump the export and import is serialized, How do I increase the performance of exp/imp? Thank You, Gautham

1 0