- users-list - monetdb.org

RE: users-list Digest, Vol 62, Issue 5
by Warren Master 13 Oct '17

13 Oct '17

Restarting the server resolved the issue. I did not look at the plans.. There still remains the isse that tables can be added multiple times to the same merge table causing duplicate rows to be returned In some cases we were unable to remove a table from a merge table without first dropping the merge table Additionally we also have issues with bulk copy into tables of merge tables GDKerror:!ERROR: Incompatible operands. - this occurs now and then.. the only fix is to drop the merge table , drop the affecting table that fails to bulk copy to , recreate it and recreate the merge table. When I tried to drop the table after removing from the merge table , the server would core dump., I was only able to drop the bad table after dropping the merge table, the alter table Merge Table drop table Sub Table did not work Warren S. Master Chief Technology Officer, TRG Management LP 280 Park Avenue - 30th floor/West - New York, NY 10017 Phone 212.984.3504 Email warren.master(a)rohatyngroup.com Web www.rohatyngroup.com Linkedin www.linkedin.com/in/warrenmaster -----Original Message----- From: users-list [mailto:users-list-bounces+warren.master=rohatyngroup.com@monetdb.org] On Behalf Of users-list-request(a)monetdb.org Sent: Friday, October 13, 2017 6:00 AM To: users-list(a)monetdb.org Subject: users-list Digest, Vol 62, Issue 5 Send users-list mailing list submissions to users-list(a)monetdb.org To subscribe or unsubscribe via the World Wide Web, visit https://www.monetdb.org/mailman/listinfo/users-list or, via email, send a message with subject or body 'help' to users-list-request(a)monetdb.org You can reach the person managing the list at users-list-owner(a)monetdb.org When replying, please edit your Subject line so it is more specific than "Re: Contents of users-list digest..." Today's Topics: 1. Re: Merge Table Issues (Niels Nes) ---------------------------------------------------------------------- Message: 1 Date: Thu, 12 Oct 2017 20:39:51 +0200 From: Niels Nes <Niels.Nes(a)cwi.nl> To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: Merge Table Issues Message-ID: <20171012183951.GA8701(a)niels.cwi.nl> Content-Type: text/plain; charset=utf-8 On Thu, Oct 05, 2017 at 07:29:08PM +0000, Warren Master wrote: > all_vw is a merge table with three tables all with identical structure Warren Do the plan's look correct, ie include the proper member tables? Niels > > > > intraday > > history > > currentyear > > ---------------------------------------------------------------------- > ----------------------- > > > > My merge tables do not return consistent results > > > > I have tried to refresh statistics > > > > ANALYZE sys. intraday (asofdate) MINMAX > > ANALYZE sys. history (asofdate) MINMAX > > ANALYZE sys. currentyear (asofdate) MINMAX > > > > still same issue > > > > the issue exists for a few dates that I checked, there are many days > that do work on the merge table , all dates work on the view and the > individual tables. > > I thought it might be an issue with statistics then the conversion > function made it work on the merge table , so now at a loss. > > > > > > > > the following query on the merge table return no rows > > ---------------------------------------------------------------------- > ----------------------- > > > > select fundid,sum(curdlttot) as fxt > > from vw where asofdate='2017-10-02' > > group by fundid > > > > The following does return data from the merge table albeit, much > slower ? after converting date value > > ---------------------------------------------------------------------- > ----------------------- > > select fundid,sum(curdlttot) as fxt > > from vw where asofdate='2017-10-02D' > > group by fundid > > > > select fundid,sum(curdlttot) as fxt > > from vw where asofdate= date_to_str(asofdate, '%Y-%m-%d') = > '2017-10-02' > > group by fundid > > > > > > the followings query on the subtable of the merge table returns data > with no issues or performance > > ---------------------------------------------------------------------- > ----------------------- > > select fundid,sum(curdlttot) as fxt > > from currentyear where asofdate='2017-10-02' > > group by fundid > > > > creating a view on the union of all three tables returns data as > expected > > > > create view test_all as > > select * from intraday > > union all > > select * from history > > union all > > select * from currentyear > > > > > > select fundid,sum(curdlttot) as fxt > > from test_all where asofdate='2017-10-02' > > group by fundid > > > > > > > > > > > > Warren S. Master > > Chief Technology Officer, CISO > > TRG Management LP > > 280 Park Avenue - 30th floor/West - New York, NY 10017 > > Phone 212.984.3504 > > Email warren.master(a)rohatyngroup.com > > Web www.rohatyngroup.com > > Linkedin www.linkedin.com/in/warrenmaster > > > > This e-mail communication and any attachments may contain confidential > and privileged information and are for use by the intended > addressee(s) only. If you are not the intended addressee, you have > received this communication in error. Any dissemination or > reproduction of this email or its contents is strictly prohibited and > may be unlawful. If you suspect you have received this communication > in error, please notify us immediately by replying to this message and > delete it from your computer.Thank you. > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list -- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/716e-mail: Niels.Nes(a)cwi.nl ------------------------------ Subject: Digest Footer _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list ------------------------------ End of users-list Digest, Vol 62, Issue 5 ***************************************** This e-mail communication and any attachments may contain confidential and privileged information and are for use by the intended addressee(s) only. If you are not the intended addressee, you have received this communication in error. Any dissemination or reproduction of this email or its contents is strictly prohibited and may be unlawful. If you suspect you have received this communication in error, please notify us immediately by replying to this message and delete it from your computer.Thank you.

1 0

Merge Table Issues
by Warren Master 12 Oct '17

12 Oct '17

all_vw is a merge table with three tables all with identical structure intraday history currentyear --------------------------------------------------------------------------------------------- My merge tables do not return consistent results I have tried to refresh statistics ANALYZE sys. intraday (asofdate) MINMAX ANALYZE sys. history (asofdate) MINMAX ANALYZE sys. currentyear (asofdate) MINMAX still same issue the issue exists for a few dates that I checked, there are many days that do work on the merge table , all dates work on the view and the individual tables. I thought it might be an issue with statistics then the conversion function made it work on the merge table , so now at a loss. the following query on the merge table return no rows --------------------------------------------------------------------------------------------- select fundid,sum(curdlttot) as fxt from vw where asofdate='2017-10-02' group by fundid The following does return data from the merge table albeit, much slower - after converting date value --------------------------------------------------------------------------------------------- select fundid,sum(curdlttot) as fxt from vw where asofdate='2017-10-02D' group by fundid select fundid,sum(curdlttot) as fxt from vw where asofdate= date_to_str(asofdate, '%Y-%m-%d') = '2017-10-02' group by fundid the followings query on the subtable of the merge table returns data with no issues or performance --------------------------------------------------------------------------------------------- select fundid,sum(curdlttot) as fxt from currentyear where asofdate='2017-10-02' group by fundid creating a view on the union of all three tables returns data as expected create view test_all as select * from intraday union all select * from history union all select * from currentyear select fundid,sum(curdlttot) as fxt from test_all where asofdate='2017-10-02' group by fundid Warren S. Master Chief Technology Officer, CISO TRG Management LP 280 Park Avenue - 30th floor/West - New York, NY 10017 Phone 212.984.3504 Email warren.master(a)rohatyngroup.com<mailto:warren.master@rohatyngroup.com> Web www.rohatyngroup.com<http://www.rohatyngroup.com/> Linkedin www.linkedin.com/in/warrenmaster<http://www.linkedin.com/in/warrenmaster> This e-mail communication and any attachments may contain confidential and privileged information and are for use by the intended addressee(s) only. If you are not the intended addressee, you have received this communication in error. Any dissemination or reproduction of this email or its contents is strictly prohibited and may be unlawful. If you suspect you have received this communication in error, please notify us immediately by replying to this message and delete it from your computer.Thank you.

2 1

mal function returns clob datatype
by Gatis Ozoliņš 10 Oct '17

10 Oct '17

I have string aggregation mal function with this definition: function group_concat(b:bat[:str],s:bat[:str]):str; It was working fine on Dec 2016-SP3, however we upgraded to Jul 2017-SP1 and now function is returning clob datatype. I looked at Changelog and code changes and couldn't find anything that would explain this. Is it feature or bug? Gatis

1 0

make[9]: *** No rule to make target `../../../../sql/backends/monet5/lib_sql.la', needed by `lib_myudfname.la'. Stop.
by Anton Kravchenko 09 Oct '17

09 Oct '17

Hi there, I will try to map my issue to something that you are familiar with. For example "MonetDB-11.23.3/sql/backends/monet5/vaults" module. 1) It has Makefile.ag which has (among other lines): lib__vault = { MODULE DIR = libdir/monetdb5 SOURCES = vault.c LIBS = ../../../../monetdb5/tools/libmonetdb5 \ ../../../../gdk/libbat $(curl_LIBS) } And that means that "libmonetdb.la" has to be created before "vaults" is built. 2) Let's say I have added "../../../../sql/backends/monet5/lib_sql" dependency lib__vault = { MODULE DIR = libdir/monetdb5 SOURCES = vault.c LIBS = ../../../../monetdb5/tools/libmonetdb5 \ * ../../../../sql/backends/monet5/lib_sql \* ../../../../gdk/libbat $(curl_LIBS) } As you can see "vaults" is leaving inside "MonetDB-11.23.3/sql/backends/monet5" directory. 3) Now my question is: How can one propagate into MonetDB-11.23.3/sql/backends/monet5/Makefile.am that "lib_sql.la" has to be created before "vaults" is built? p.s. On Windows, there is MonetDB-11.23.3/sql/backends/monet5/Makefile.msc used (instead Makefile.am). And I would just change order in "all-recursive all-msc" to "all-msc all-recursive": # build-all: $(BUILT_SOURCES) all-recursive all-msc build-all: $(BUILT_SOURCES) all-msc all-recursive Thank you, Anton

2 4

Error starting monetdb
by Corbit, Dann 06 Oct '17

06 Oct '17

When M5server.bat runs, it outputs this message: !FATAL: BBPinit: first != 0 (ID = 72). And exits. I do not have any data I need to save in my database. Is there a way to drop the old database and create a new one, so that I can again run tests?

1 0

How to improve query performance on a single node in MonetDB?
by Tomas Repik | Instarea 04 Oct '17

04 Oct '17

Hello list, recently I came across MonetDB as a columnar database and I have some issues with getting it perform well. I posted my question on StackOverflow <https://stackoverflow.com/questions/46558842/how-to-improve-query-performan…>, idealy post your answers there but, mailing it to the list is fine as well. Thank you Tomas > TL;DR > I've installed the latest (MonetDB 5 server v11v.27.5 "Jul2017-SP1") on > Windows 2012 Server and I'm trying to query large table 1,4 billion rows in > a reasonable time 2-3s. > Is this even possible with MonetDB? What could I do to improve the > performance? > Detailed description of what I've done so far: > Created table: > CREATE TABLE t939ba ( id INT, xa INT, xb INT, ya INT, yb INT, a1 TINYINT, > a2 TINYINT, a3 TINYINT, a4 TINYINT, a5 TINYINT, a6 TINYINT, a7 TINYINT, a8 > TINYINT, a9 TINYINT); > Loaded the data: > COPY 1450000000 OFFSET 2 RECORDS INTO tbl FROM 'D:\\es_export\\file.csv' > USING DELIMITERS ',' NULL AS '' LOCKED; > Run the query: > SELECT COUNT(DISTINCT id) FROM tbl WHERE a1=22 > AND xb>=143455 AND yb>=90911 AND xa<=143615 AND ya<=91007 > AND a2 IN (2, 3, 4) AND a3 IN (0, 1, 2, 3, 4) AND a4 IN (0, 1, 2) > AND a5 IN (-1, 1, 2, 3, 4, 5, 6, 7) AND a6 IN (-1, 11, 12, 13, 14); > It took *14m 52s* to get the result.

3 2

build error MonetDBv11.27.5 on Linux
by Anton Kravchenko 19 Sep '17

19 Sep '17

Hi there, I am able to build and install MonetDB v11.21.19 "Jul2015-SP4" by following and example from https://www.monetdb.org/wiki/MonetDB:Building_from_sources But when I try build MonetDB v11.27.5 on same machine I get this error (for a full log info see https://drive.google.com/drive/folders/0B7dMycp0Ku-NTlRsZW95R0xCNmM?usp=sha… ): /bin/sh ../../../../../libtool --tag=CC --mode=link gcc -DLIBPYAPI -g -O2 -D_REENTRANT -module -avoid-version -o lib_pyapi.la -rpath /home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/lib/monetdb5 lib_pyapi_la-pyapi.lo lib_pyapi_la-unicode.lo lib_pyapi_la-pytypes.lo lib_pyapi_la-type_conversion.lo lib_pyapi_la-formatinput.lo lib_pyapi_la-connection.lo lib_pyapi_la-pyloader.lo lib_pyapi_la-emit.lo lib_pyapi_la-conversion.lo ../../../../../monetdb5/tools/libmonetdb5.la ../../../../../gdk/libbat.la -lpython2.7 -lpthread -ldl -lutil -lm -Xlinker -export-dynamic libtool: link: gcc -shared -fPIC -DPIC .libs/lib_pyapi_la-pyapi.o .libs/lib_pyapi_la-unicode.o .libs/lib_pyapi_la-pytypes.o .libs/lib_pyapi_la-type_conversion.o .libs/lib_pyapi_la-formatinput.o .libs/lib_pyapi_la-connection.o .libs/lib_pyapi_la-pyloader.o .libs/lib_pyapi_la-emit.o .libs/lib_pyapi_la-conversion.o -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/monetdb5/tools/.libs -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/gdk/.libs -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/clients/mapilib/.libs -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/common/stream/.libs -Wl,-rpath -Wl,/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/lib -L/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/gdk/.libs -L/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/clients/mapilib/.libs -L/home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/common/stream/.libs ../../../../../monetdb5/tools/.libs/libmonetdb5.so /home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/gdk/.libs/libbat.so /home/SECDEV.LOCAL/akravchenko/MonetDB-Jul2017-SP1/BUILD/ clients/mapilib/.libs/libmapi.so -lpcre -lxml2 ../../../../../gdk/.libs/libbat.so /home/SECDEV.LOCAL/akravchenko /MonetDB-Jul2017-SP1/BUILD/common/stream/.libs/libstream.so -lsnappy -lssl -lcrypto -lz -lpython2.7 -lpthread -ldl -lutil -lm -g -O2 -Wl,-export-dynamic -pthread -Wl,-soname -Wl,lib_pyapi.so -o .libs/lib_pyapi.so /usr/bin/ld: cannot find -lpython2.7 collect2: ld returned 1 exit status make[11]: *** [lib_pyapi.la] Error 1 make[11]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5/UDF/pyapi' make[10]: *** [all] Error 2 make[10]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5/UDF/pyapi' make[9]: *** [all-recursive] Error 1 make[9]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5/UDF' make[8]: *** [all] Error 2 make[8]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5/UDF' make[7]: *** [all-recursive] Error 1 make[7]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5' make[6]: *** [all] Error 2 make[6]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends/monet5' make[5]: *** [all-recursive] Error 1 make[5]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends' make[4]: *** [all] Error 2 make[4]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql/backends' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/home/SECDEV.LOCAL/akravchenk o/MonetDB-Jul2017-SP1/BUILD' make: *** [all] Error 2 Thank you, Anton

2 4

Embedded Python is enabled but an error was thrown during initialization.
by Anton Kravchenko 15 Sep '17

15 Sep '17

Hi there, sql>create or replace function f1() returns table(v1 INT) more>LANGUAGE PYTHON { more> return [1,2,3] more>}; operation successful (3.658ms) sql>select * from f1(); Embedded Python is enabled but an error was thrown during initialization. Does one have to setup another python env variables (like it's done on MonetDB for Windows: PYTHONHOME, PYTHONPATH) on Linux? p.s. Here are is how I setup environment 1) installed MonetDB v11.27.5 (Jul2017-SP1) binaries on centos-release-7-4.1708.el7.centos.x86_64: sudo yum install https://dev.monetdb.org/downloads/epel/MonetDB-release-epel.noarch.rpm sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB-stream-11.27.5-2017… sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB-client-11.27.5-2017… sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB5-server-11.27.5-201… sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB-SQL-server5-11.27.5… sudo yum install https://www.monetdb.org/downloads/epel/7/x86_64/MonetDB-python2-11.27.5-201… 2) installed Anaconda2 Python 2.7.13 |Anaconda 4.4.0 (64-bit)| (default, Dec 20 2016, 23:09:15) [GCC 4.4.7 20120313 (Red Hat 4.4.7-1)] on linux2 export PATH="/home/akravchenko/anaconda2/bin:$PATH" 3) created and started Monet database: monetdbd create hnode1_dbfarm monetdbd set port=50001 hnode1_dbfarm monetdbd set control=yes hnode1_dbfarm monetdbd set passphrase=monetdb hnode1_dbfarm monetdbd start hnode1_dbfarm monetdb create hnode1_dbfarm_db1 *monetdb set embedpy=true hnode1_dbfarm_db1* monetdb start hnode1_dbfarm_db1 monetdb release hnode1_dbfarm_db1 4)monetdbd get all hnode1_dbfarm property value hostname somehostname dbfarm hnode1_dbfarm status monetdbd[31089] 1.7 (Jul2017-SP1) is serving this dbfarm mserver /usr/bin/mserver5 logfile hnode1_dbfarm/merovingian.log pidfile hnode1_dbfarm/merovingian.pid sockdir /tmp listenaddr localhost port 50001 exittimeout 60 forward proxy discovery yes discoveryttl 600 control yes passphrase {SHA512}a73f1d86383446438ac64f56e15ada38b41fbb18f029d2181723aeb2acac6a831f60e5fdbd64ac2c8c70e035dd44cbbe3b45565ef2d58feb2821a2078c7fad35 mapisock /tmp/.s.monetdb.50001 controlsock /tmp/.s.merovingian.50001 5) monetdb get all hnode1_dbfarm_db1 name prop source value hnode1_dbfarm_db1 name - hnode1_dbfarm_db1 hnode1_dbfarm_db1 type default database hnode1_dbfarm_db1 shared default yes hnode1_dbfarm_db1 nthreads default 32 hnode1_dbfarm_db1 optpipe default default_pipe hnode1_dbfarm_db1 readonly default no hnode1_dbfarm_db1 embedr default no hnode1_dbfarm_db1 embedpy local yes hnode1_dbfarm_db1 embedpy3 default no hnode1_dbfarm_db1 nclients default 64 hnode1_dbfarm_db1 dbextra default <unknown> Thank you, Anton

1 2

SQL_CALC_FOUND_ROWS equivalent?
by Alberto Ferrari 15 Sep '17

15 Sep '17

Hi, is there any function or trick in Monet equivalent to mysql "SELECT SQL_CALC_FOUND_ROWS ..." to get de total rows when limit is present ? ((pagination) So I could get de TOTAL rows/pages number without exec another query everytime I need paginate results. Thanks!

2 2

Re: Column rename issue
by Stefan Manegold 15 Sep '17

15 Sep '17

Hi, Add new column, copy/cast data from old to new column, drop old column. Stefan -------- Original message --------From: Amit Ambekar <amit(a)g-square.in> Date: 9/15/17 12:14 (GMT+01:00) To: users-list(a)monetdb.org Subject: Column rename issue Hi, how to update already created column name and type in monetdb ? _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0