- users-list - monetdb.org

[MonetDB-users] Announcement: New Aug2011-SP2 Bugfix release of MonetDB suite
by MonetDB Release Engineer 26 Oct '11

26 Oct '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Aug2011-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at http://www.monetdb.org/. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Aug 2011-SP2 bugfix release Client Package * ODBC: Implemented a workaround in SQLTables for bug 2908. * Small improvement to mclient's table rendering for tables without any rows. Previously, the column names in the header could be squeezed to very small widths, degrading readability. * Python DB API connect() function now supports PEP 249-style arguments user and host, bug #2901 * mclient now checks the result of encoding conversions using the iconv library. * Fixed a source of crashes in mclient when a query on the command line using the -s option is combined with input on standard input (e.g. in the construct mclient -s 'COPY INTO t FROM STDIN ...' < file.csv). * Fixed bug 2897 where slow (network) reads could cause blocks to not be fully read in one go, causing errors in the subsequent use of those blocks. With thanks to Rémy Chibois. * Fixed a bug in mclient where processing queries from files could result in ghost empty results to be reported in the output Merovingian * Resolved problem where monetdbd would terminate abnormally when databases named 'control', 'discovery' or 'merovingian' were stopped. * monetdbd get status now also reports the version of the running monetdbd * Improved response time of 'monetdb start' when the database fails to start. * Fixed a bug in monetdbd where starting a failing database could incorrectly be reported as a 'running but dead' database. * To avoid confusion, all occurrences of merovingian were changed into monetdbd for error messages sent to a client. Bug Fixes * 2346: mkey.bulk_rotate_xor_hash MAL exception * 2712: un-rolled-back savepoints cause crash upon scenario exit * 2774: COPY INTO of double (~1.0e-310) cannot be imported. * 2890: Slow execution of COUNT in combination with DISTINCT * 2894: No consistent behavior of implicit cast of integer to double * 2897: Mapi.pm: short reads not handled when reading blocks from server * 2898: order by for multiple (two) columns fails for large tables when second ordering is 'desc': "mat.refine_reverse' undefined" * 2900: rand(int) should not return NULL * 2901: Make keyword arguments in Python DB API connect() function more standard * 2904: TypeException after calling UDF in other than sys schema (when it is initialised) * 2906: "ALTER TABLE mytable SET READ ONLY" results in server segfault * 2908: SQL query produces error with MAL statements * 2909: query result is not displayed when history = true

1 0

[MonetDB-users] Newbie just joined this group :)
by Tony Bai 26 Oct '11

26 Oct '11

Hi guys, I just joined this amazing group, May I asking a stupid question about Monetdb if I haven't run out my 'ask stupid question' budget? My question are as following: * Regarding to the work cluster, Does Monetdb support smoothly expand while I have to add new server to the work cluster in necessary? How to add a new server to a work cluster? * Does Monetdb supply any analytical functions? Do we have any documents or materials related to what kind of analytical functions can be used? BTW: Any advices or suggestions regarding building data mart with Monetdb would be highly appreciated! Thanks in advance, Tony

1 0

[MonetDB-users] Examples of usage
by Jeremy O'Sullivan 25 Oct '11

25 Oct '11

Hello, I am presently evaluating MonetDB's performance for a client using big data sets. We are very happy with the impressive results. Something that is harder to evaluate is how wide spread is MonetDB's usage. There are some references on the home page, but as it says in the introductory passage, most usage applications are anonymous. Would anyone like to share examples of applications that are using MonetDB ? Regards, Jeremy

2 1

[MonetDB-users] Network unreachable errors in log
by Jeremy O'Sullivan 21 Oct '11

21 Oct '11

Hello, The log file, merovingian.log, contains many occurrences of the below: 2011-10-21 17:20:47 ERR discovery[4680]: error while sending broadcast message: Network is unreachable The database seems to be performing ok. What causes this issue, would this affect the performance of the database ? Regards, Jeremy

2 1

Re: [MonetDB-users] ODBC connection issues
by Jeremy O'Sullivan 20 Oct '11

20 Oct '11

Hi, I have downloaded the patch for the ODBC driver on windows 2003 32 bit (after uninstalling the existing odbc driver) and tried to view the schema from excel, through the excel odbc connection. I get the exact same error as before. Regards, Jeremy

3 2

Re: [MonetDB-users] ODBC connection issues
by Jeremy OSullivan 20 Oct '11

20 Oct '11

Hi, The dialogue comes back with the same error message. I tried it in access as well. I am not able to see the schema, not to mind query it. The log may be different, I have attached the output. I asked my colleague to download and try on his machine, he got the same issue. Regards, Jeremy MSACCESS f68-f54 ENTER SQLAllocConnect HENV 09E41788 HDBC * 00139820 MSACCESS f68-f54 EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS) HENV 09E41788 HDBC * 0x00139820 ( 0x09e426b8) MSACCESS f68-f54 ENTER SQLSetConnectOption HDBC 09E426B8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x00000014 MSACCESS f68-f54 EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS) HDBC 09E426B8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x00000014 (BADMEM) MSACCESS f68-f54 ENTER SQLSetConnectAttrW SQLHDBC 09E426B8 SQLINTEGER 30002 <unknown> SQLPOINTER [Unknown attribute 30002] SQLINTEGER -3 MSACCESS f68-f54 EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS) SQLHDBC 09E426B8 SQLINTEGER 30002 <unknown> SQLPOINTER [Unknown attribute 30002] SQLINTEGER -3 MSACCESS f68-f54 ENTER SQLDriverConnectW HDBC 09E426B8 HWND 0018019E WCHAR * 0x4BF77CD4 [ -3] "******\ 0" SWORD -3 WCHAR * 0x4BF77CD4 SWORD 2 SWORD * 0x00000000 UWORD 1 <SQL_DRIVER_COMPLETE> MSACCESS f68-f54 EXIT SQLDriverConnectW with return code 1 (SQL_SUCCESS_WITH_INFO) HDBC 09E426B8 HWND 0018019E WCHAR * 0x4BF77CD4 [ -3] "******\ 0" SWORD -3 WCHAR * 0x4BF77CD4 SWORD 2 SWORD * 0x00000000 UWORD 1 <SQL_DRIVER_COMPLETE> DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (0) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 9 <SQL_ODBC_API_CONFORMANCE> PTR 0x0013981C SWORD 2 SWORD * 0x00139824 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 9 <SQL_ODBC_API_CONFORMANCE> PTR 0x0013981C (1) SWORD 2 SWORD * 0x00139824 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 6 <SQL_DRIVER_NAME> PTR 0x00139828 SWORD 200 SWORD * 0x00139824 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 6 <SQL_DRIVER_NAME> PTR 0x00139828 [ 28] "MonetDBODBClib" SWORD 200 SWORD * 0x00139824 (28) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 46 <SQL_TXN_CAPABLE> PTR 0x001393DC SWORD 2 SWORD * 0x001393A0 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 46 <SQL_TXN_CAPABLE> PTR 0x001393DC (2) SWORD 2 SWORD * 0x001393A0 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR> PTR 0x039C6138 SWORD 2 SWORD * 0x001393A0 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR> PTR 0x039C6138 (0) SWORD 2 SWORD * 0x001393A0 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR> PTR 0x039C613A SWORD 2 SWORD * 0x001393A0 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR> PTR 0x039C613A (0) SWORD 2 SWORD * 0x001393A0 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 1 <SQL_ACTIVE_STATEMENTS> PTR 0x001393E4 SWORD 2 SWORD * 0x001393B8 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 1 <SQL_ACTIVE_STATEMENTS> PTR 0x001393E4 (0) SWORD 2 SWORD * 0x001393B8 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 17 <SQL_DBMS_NAME> PTR 0x001397E4 SWORD 200 SWORD * 0x001397E0 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 17 <SQL_DBMS_NAME> PTR 0x001397E4 [ 14] "MonetDB" SWORD 200 SWORD * 0x001397E0 (14) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 0 <SQL_ACTIVE_CONNECTIONS> PTR 0x001398E0 SWORD 2 SWORD * 0x001398E8 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 0 <SQL_ACTIVE_CONNECTIONS> PTR 0x001398E0 (0) SWORD 2 SWORD * 0x001398E8 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 25 <SQL_DATA_SOURCE_READ_ONLY> PTR 0x001398EC SWORD 20 SWORD * 0x001398E8 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 25 <SQL_DATA_SOURCE_READ_ONLY> PTR 0x001398EC [ 2] "N" SWORD 20 SWORD * 0x001398E8 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 46 <SQL_TXN_CAPABLE> PTR 0x001398E4 SWORD 2 SWORD * 0x001398C0 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 46 <SQL_TXN_CAPABLE> PTR 0x001398E4 (2) SWORD 2 SWORD * 0x001398C0 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR> PTR 0x001398DC SWORD 2 SWORD * 0x001398C0 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 23 <SQL_CURSOR_COMMIT_BEHAVIOR> PTR 0x001398DC (0) SWORD 2 SWORD * 0x001398C0 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR> PTR 0x001398D8 SWORD 2 SWORD * 0x001398C0 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 24 <SQL_CURSOR_ROLLBACK_BEHAVIOR> PTR 0x001398D8 (0) SWORD 2 SWORD * 0x001398C0 (2) MSACCESS f68-f54 ENTER SQLGetInfoW HDBC 09E426B8 UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR> PTR 0x001398EC SWORD 20 SWORD * 0x001398E8 MSACCESS f68-f54 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 09E426B8 UWORD 29 <SQL_IDENTIFIER_QUOTE_CHAR> PTR 0x001398EC [ 2] """ SWORD 20 SWORD * 0x001398E8 (2) MSACCESS f68-f54 ENTER SQLAllocStmt HDBC 09E426B8 HSTMT * 00137C38 MSACCESS f68-f54 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) HDBC 09E426B8 HSTMT * 0x00137C38 ( 0x09e41a28) MSACCESS f68-f54 ENTER SQLGetStmtOption HSTMT 09E41A28 UWORD 0 PTR 0x00137B40 MSACCESS f68-f54 EXIT SQLGetStmtOption with return code -1 (SQL_ERROR) HSTMT 09E41A28 UWORD 0 PTR 0x00137B40 DIAG [HYC00] [MonetDB][ODBC Driver 11.5.6]Optional feature not implemented (0) MSACCESS f68-f54 ENTER SQLErrorW HENV 09E41788 HDBC 09E426B8 HSTMT 09E41A28 WCHAR * 0x00137AEC (NYI) SDWORD * 0x00137AB8 WCHAR * 0x0374A000 SWORD 4095 SWORD * 0x00137AAC MSACCESS f68-f54 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 09E41788 HDBC 09E426B8 HSTMT 09E41A28 WCHAR * 0x00137AEC (NYI) SDWORD * 0x00137AB8 (0) WCHAR * 0x0374A000 [ 61] "[MonetDB][ODBC Driver 11.5.6]Optional feature not implemented" SWORD 4095 SWORD * 0x00137AAC (61) MSACCESS f68-f54 ENTER SQLErrorW HENV 09E41788 HDBC 09E426B8 HSTMT 09E41A28 WCHAR * 0x00137AEC (NYI) SDWORD * 0x00137AB8 WCHAR * 0x0374A08C SWORD 4025 SWORD * 0x00137AAC MSACCESS f68-f54 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND) HENV 09E41788 HDBC 09E426B8 HSTMT 09E41A28 WCHAR * 0x00137AEC (NYI) SDWORD * 0x00137AB8 WCHAR * 0x0374A08C SWORD 4025 SWORD * 0x00137AAC MSACCESS f68-f54 ENTER SQLTablesW HSTMT 09E41A28 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x09E42640 [ -3] "'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'\ 0" SWORD -3 MSACCESS f68-f54 EXIT SQLTablesW with return code -1 (SQL_ERROR) HSTMT 09E41A28 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x09E42640 [ -3] "'TABLE','VIEW','SYSTEM TABLE','ALIAS','SYNONYM'\ 0" SWORD -3 DIAG [HY000] [MonetDB][ODBC Driver 11.5.6]TypeException:user.s2_2[34]:'algebra.markT' undefined in: _74:any := algebra.markT(_73:bit, _69:oid) TypeException:user.s2_2[56]:'algebra.kunion' undefined in: _102:any := algebra.kunion(_101:bat[:oid,:oid], _74:any) TypeException:user.s2_2[57]:'algebra.markT' undefined in: _103:any := algebra.markT(_102:any, _68:oid) TypeException:user.s2_2[62]:'algebra.markT' undefined in: _110:any := algebra.markT(_109:bit, _104:oid) TypeException:user.s2_2[63]:'algebra.kunion' undefined in: _111:any := algebra.kunion(_110:any, _103:any) TypeException:user.s2_2[80]:'algebra.semijoin' undefined in: _133:any := algebra.semijoin(_132:bat[:oid,:void], _111:any) TypeException:user.s2_2[81]:'algebra.markT' undefined in: _134:any := algebra.markT(_133:any, _67:oid) TypeException:user.s2_2[82]:'bat.reverse' undefined in: _135:any := bat.reverse(_134:any) TypeException:user.s2_2[83]:'algebra.leftjoin' undefined in: _136:any := algebra.leftjoin(_135:any, _64:bat[:oid,:int]) TypeException:user.s2_2[84]:'bat.reverse' undefined in: _137:any := bat.reverse(_136:any) TypeException:user.s2_2[96]:'algebra.join' undefined in: _156:any := algebra.join(_155:bat[:oid,:int], _137:any) TypeException:user.s2_2[97]:'algebra.markT' undefined in: _157:any := algebra.markT(_156:any, _58:oid) TypeException:user.s2_2[98]:'bat.reverse' undefined in: _158:any := bat.reverse(_157:any) TypeException:user.s2_2[99]:'algebra.leftjoin' undefined in: _159:any := algebra.leftjoin(_158:any, _57:bat[:oid,:str]) TypeException:user.s2_2[119]:'algebra.markT' undefined in: _185:any := algebra.markT(_184:bit, _180:oid) TypeException:user.s2_2[195]:'algebra.kunion' undefined in: _273:any := algebra.kunion(_272:bat[:oid,:oid], _185:any) TypeException:user.s2_2[196]:'algebra.markT' undefined in: _274:any := algebra.markT(_273:any, _179:oid) TypeException:user.s2_2[201]:'algebra.markT' undefined in: _281:any := algebra.markT(_280:bit, _275:oid) TypeException:user.s2_2[202]:'algebra.kunion' undefined (0) MSACCESS f68-f54 ENTER SQLErrorW HENV 09E41788 HDBC 09E426B8 HSTMT 09E41A28 WCHAR * 0x00137B00 (NYI) SDWORD * 0x00137ACC WCHAR * 0x0374A000 SWORD 4095 SWORD * 0x00137AC0 MSACCESS f68-f54 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 09E41788 HDBC 09E426B8 HSTMT 09E41A28 WCHAR * 0x00137B00 (NYI) SDWORD * 0x00137ACC (0) WCHAR * 0x0374A000 [ 4095] "[MonetDB][ODBC Driver 11.5.6]TypeException:user.s2_2[34]:'algebra.markT' undefined in: _74:any := algebra.markT(_73:bit, _69:oid)\ aTypeException:user.s2_2[56]:'algebra.kunion' undefined in: _102:any := algebra.kunion(_101:bat[:oid,:oid], _74:any)\ aTypeException:user.s2_2[57]:'algebra.markT' undefined in: _103:any := algebra.markT(_102:any, _68:oid)\ aTypeException:user.s2_2[62]:'algebra.markT' undefined in: _110:any := algebra.markT(_109:bit, _104:oid)\ aTypeException:user.s2_2[63]:'algebra.kunion' undefined in:\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0..." SWORD 4095 SWORD * 0x00137AC0 (8055) MSACCESS f68-f54 ENTER SQLErrorW HENV 09E41788 HDBC 09E426B8 HSTMT 09E41A28 WCHAR * 0x00137B00 (NYI) SDWORD * 0x00137ACC WCHAR * 0x0374A410 SWORD 3575 SWORD * 0x00137AC0 MSACCESS f68-f54 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND) HENV 09E41788 HDBC 09E426B8 HSTMT 09E41A28 WCHAR * 0x00137B00 (NYI) SDWORD * 0x00137ACC WCHAR * 0x0374A410 SWORD 3575 SWORD * 0x00137AC0 MSACCESS f68-f54 ENTER SQLFreeStmt HSTMT 09E41A28 UWORD 1 <SQL_DROP> MSACCESS f68-f54 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 09E41A28 UWORD 1 <SQL_DROP> MSACCESS f68-f54 ENTER SQLDisconnect HDBC 09E426B8 MSACCESS f68-f54 EXIT SQLDisconnect with return code 0 (SQL_SUCCESS) HDBC 09E426B8 MSACCESS f68-f54 ENTER SQLFreeConnect HDBC 09E426B8 MSACCESS f68-f54 EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS) HDBC 09E426B8

1 0

[MonetDB-users] Problems with the query log
by Viktor Rosenfeld 20 Oct '11

20 Oct '11

Hi, I get errors, when I try to use the query history feature as described here: http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/History After history is set to true, I get the following error message every time I execute a query: invalid/unknown response from server, ignoring output ? = 0:0 profiler.setFootprintFlag(); ? = 0:0 profiler.setMemoryFlag(); ? = 0:0 inblock := profiler.getDiskReads(); ? = 0:0 oublock := profiler.getDiskWrites(); ? = 0:0 xtime := alarm.usec(); (remaining output omitted, use \fraw to examine in detail) This only stops if I set history back to false. I'm using the latest MonetDB 11.5.3 on OS X 10.6. Below is an example session. Cheers, Viktor -----8< sample session >8----- $ mclient -d demo -u monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011-SP1) Database: MonetDB v11.5.3, 'demo' Type \q to quit, \? for a list of available commands auto commit mode: on sql>set history=true; sql>select 1; invalid/unknown response from server, ignoring output ? = 0:0 profiler.setFootprintFlag(); ? = 0:0 profiler.setMemoryFlag(); ? = 0:0 inblock := profiler.getDiskReads(); ? = 0:0 oublock := profiler.getDiskWrites(); ? = 0:0 xtime := alarm.usec(); (remaining output omitted, use \fraw to examine in detail) sql>select * from queryHistory; invalid/unknown response from server, ignoring output ? = 0:0 profiler.setFootprintFlag(); ? = 0:0 profiler.setMemoryFlag(); ? = 0:0 inblock := profiler.getDiskReads(); ? = 0:0 oublock := profiler.getDiskWrites(); ? = 0:0 xtime := alarm.usec(); (remaining output omitted, use \fraw to examine in detail) sql>set history=false; invalid/unknown response from server, ignoring output ? = 0:0 profiler.setFootprintFlag(); ? = 0:0 profiler.setMemoryFlag(); ? = 0:0 inblock := profiler.getDiskReads(); ? = 0:0 oublock := profiler.getDiskWrites(); ? = 0:0 xtime := alarm.usec(); (remaining output omitted, use \fraw to examine in detail) sql>select * from queryHistory; +---------+----------------------------+---------+-----------------------------+-------+----------+ | id | defined | name | query | parse | optimize | +=========+============================+=========+=============================+=======+==========+ | 1035769 | 2011-10-18 15:51:36.000000 | monetdb | select 1; | 236 | 208 | | 1035885 | 2011-10-18 15:51:47.000000 | monetdb | select * from queryhistory; | 300 | 860 | | 1036047 | 2011-10-18 15:51:50.000000 | monetdb | set history=false; | 130 | 466 | +---------+----------------------------+---------+-----------------------------+-------+----------+ 3 tuples (1.613ms) sql>

2 2

Re: [MonetDB-users] ODBC connection issues
by Jeremy O'Sullivan 20 Oct '11

20 Oct '11

Hi, How do I get the patch, there is no attachment on the bug. Is there a new .msi ? Regards, Jeremy

2 1

[MonetDB-users] Problem with YEAR, MONTH, DAY functions
by Roula Avg 20 Oct '11

20 Oct '11

Hello, sorry to ask again but i have problem with YEAR, MONTH and DAY functions. According to the references YEAR for example can take as argument a date or a timestamp or a month_interval value. So if i run the query: sql> select year(current_timestamp); or sql> select year( '2002-03-21') it should return 2011 and 2002. Instead of this, i take: *syntax error,unexpected YEAR in: "select year". *What am i doing wrong? Thank you very much in advance

3 2

[MonetDB-users] MonetDB Temporary tables
by ultanocarroll 19 Oct '11

19 Oct '11

Hi, I'm trying to create an adapter for Apache Jena[1] to MonetDB[2]. Part of this process involves creating temporary tables in Monet. During the Jena db setup process these tables are created with SQL statements. For most RDBMS these take the form of CREATE GLOBAL TEMPORARY... However, with MonetDB (Aug 11 release on Ubuntu 11.04) I have been unable to use this statement form, or CREATE LOCAL, or CREATE TEMPORARY TABLE. Monet always returns "insufficient privileges", a la : sql>create global temporary table t (col1 int); CREATE TABLE: insufficient privileges for user 'jena' in schema 'tmp' sql>create local temporary table t (col1 int); CREATE TABLE: insufficient privileges for user 'jena' in schema 'tmp' sql>create temporary table t (col1 int); CREATE TABLE: insufficient privileges for user 'jena' in schema 'tmp' (Jena's table is a little more sophisticated but raises the same error ! :-) I have created the user for this operation, an accompanying schema with authorization and reset the users schema to this, as per Getting Started [3]. The only operation I can seem to get to work in place of temporary tables is to use CREATE TABLE. However, when I do this, Jena unit tests fail when it tries to re-create, for a second group of tests, these temp tables, with the exception java.sql.SQLException: CREATE TABLE: name 'nnodetriples' already in use Jena then tries to drop the tables (using execSilent) and create anew, but at this point gets another exception. Caused by: java.sql.SQLException: current transaction is aborted (please ROLLBACK) Any suggestions on this ? 1. It seems to me the root of the problem is that the global temp tables cannot be created, or that the 'already in use' exception is escalating to a transaction abort, incorrectly. 2. Do I need (can I) grant the user privileges to the tmp schema ? [1] http://incubator.apache.org/jena/ [2] https://issues.apache.org/jira/browse/JENA-134 [3] http://www.monetdb.org/Downloads/Tutorial

2 1

Re: [MonetDB-users] ODBC connection issues
by Jeremy O'Sullivan 19 Oct '11

19 Oct '11

Hello, Regarding the ODBC error, I tried connecting to MonetDB using excel and got the same error as I got from OBI EE, see log below: Regards, Jeremy MSQRY32 a18-890 ENTER SQLAllocEnv HENV * 3009DDD4 MSQRY32 a18-890 EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS) HENV * 0x3009DDD4 ( 0x00aa1788) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 31 <unknown> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1C4 WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1B6 MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 31 <unknown> WCHAR * 0x00AA1830 [ 11] "dBASE" SWORD 34 SWORD * 0x0012F1C4 (11) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1B6 (51) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1C4 WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1B6 MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 11] "Excel" SWORD 34 SWORD * 0x0012F1C4 (11) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1B6 (54) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1C4 WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1B6 MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 18] "MS Access" SWORD 34 SWORD * 0x0012F1C4 (18) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1B6 (40) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1C4 WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1B6 MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 100 (SQL_NO_DATA_FOUND) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1C4 WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1B6 MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 32 <unknown> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 32 <unknown> WCHAR * 0x00AA1830 [ 12] "Analyt" SWORD 34 SWORD * 0x0012F1BC (12) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (16) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 20] "IncomeAndE" SWORD 34 SWORD * 0x0012F1BC (20) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (40) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 11] "Forec" SWORD 34 SWORD * 0x0012F1BC (11) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (40) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 17] "IncomeEx" SWORD 34 SWORD * 0x0012F1BC (17) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (40) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 10] "DFB F" SWORD 34 SWORD * 0x0012F1BC (10) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (54) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 10] "DFB F" SWORD 34 SWORD * 0x0012F1BC (10) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (54) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 16] "DFB_KPI_" SWORD 34 SWORD * 0x0012F1BC (16) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (54) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 5] "Wa" SWORD 34 SWORD * 0x0012F1BC (5) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (54) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 12] "Profit" SWORD 34 SWORD * 0x0012F1BC (12) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (30) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 0 (SQL_SUCCESS) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 [ 7] "Mon" SWORD 34 SWORD * 0x0012F1BC (7) WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE (19) MSQRY32 a18-890 ENTER SQLDataSourcesW HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 EXIT SQLDataSourcesW with return code 100 (SQL_NO_DATA_FOUND) HENV 00AA1788 UWORD 1 <SQL_FETCH_NEXT> WCHAR * 0x00AA1830 SWORD 34 SWORD * 0x0012F1BC WCHAR * 0x00000000 SWORD 0 SWORD * 0x0012F1AE MSQRY32 a18-890 ENTER SQLAllocConnect HENV 00AA1788 HDBC * 0012E698 MSQRY32 a18-890 EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS) HENV 00AA1788 HDBC * 0x0012E698 ( 0x00aa1ba8) MSQRY32 a18-890 ENTER SQLSetConnectOption HDBC 00AA1BA8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x0000002D MSQRY32 a18-890 EXIT SQLSetConnectOption with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 SQLINTEGER 103 <SQL_LOGIN_TIMEOUT> SQLPOINTER 0x0000002D (BADMEM) MSQRY32 a18-890 ENTER SQLDriverConnectW HDBC 00AA1BA8 HWND 0002019A WCHAR * 0x4BF77CD4 [ -3] "******\ 0" SWORD -3 WCHAR * 0x4BF77CD4 SWORD 2 SWORD * 0x00000000 UWORD 1 <SQL_DRIVER_COMPLETE> MSQRY32 a18-890 EXIT SQLDriverConnectW with return code 1 (SQL_SUCCESS_WITH_INFO) HDBC 00AA1BA8 HWND 0002019A WCHAR * 0x4BF77CD4 [ -3] "******\ 0" SWORD -3 WCHAR * 0x4BF77CD4 SWORD 2 SWORD * 0x00000000 UWORD 1 <SQL_DRIVER_COMPLETE> DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (0) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 2 <SQL_DATA_SOURCE_NAME> PTR 0x00AA1CD8 SWORD 2048 SWORD * 0x0012E694 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 2 <SQL_DATA_SOURCE_NAME> PTR 0x00AA1CD8 [ 14] "MonetDB" SWORD 2048 SWORD * 0x0012E694 (14) MSQRY32 a18-890 ENTER SQLAllocStmt HDBC 00AA1BA8 HSTMT * 0012E68C MSQRY32 a18-890 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 HSTMT * 0x0012E68C ( 0x00aa25c8) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 1 <SQL_ACTIVE_STATEMENTS> PTR 0x0012E640 SWORD 2 SWORD * 0x0012E596 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 1 <SQL_ACTIVE_STATEMENTS> PTR 0x0012E640 (0) SWORD 2 SWORD * 0x0012E596 (2) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 25 <SQL_DATA_SOURCE_READ_ONLY> PTR 0x00AA3038 SWORD 2048 SWORD * 0x0012E694 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 25 <SQL_DATA_SOURCE_READ_ONLY> PTR 0x00AA3038 [ 2] "N" SWORD 2048 SWORD * 0x0012E694 (2) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 6 <SQL_DRIVER_NAME> PTR 0x00AA3038 SWORD 2048 SWORD * 0x0012E694 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 6 <SQL_DRIVER_NAME> PTR 0x00AA3038 [ 28] "MonetDBODBClib" SWORD 2048 SWORD * 0x0012E694 (28) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 14 <SQL_SEARCH_PATTERN_ESCAPE> PTR 0x00AA3038 SWORD 2048 SWORD * 0x0012E694 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 14 <SQL_SEARCH_PATTERN_ESCAPE> PTR 0x00AA3038 SWORD 2048 SWORD * 0x0012E694 (0) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 74 <SQL_CORRELATION_NAME> PTR 0x0012E680 SWORD 2 SWORD * 0x0012E694 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 74 <SQL_CORRELATION_NAME> PTR 0x0012E680 (2) SWORD 2 SWORD * 0x0012E694 (2) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 75 <SQL_NON_NULLABLE_COLUMNS> PTR 0x0012E680 SWORD 2 SWORD * 0x0012E694 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 75 <SQL_NON_NULLABLE_COLUMNS> PTR 0x0012E680 (1) SWORD 2 SWORD * 0x0012E694 (2) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 41 <SQL_CATALOG_NAME_SEPARATOR> PTR 0x00AA3038 SWORD 2048 SWORD * 0x0012E694 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 41 <SQL_CATALOG_NAME_SEPARATOR> PTR 0x00AA3038 SWORD 2048 SWORD * 0x0012E694 (0) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 84 <SQL_FILE_USAGE> PTR 0x0012E654 SWORD 2 SWORD * 0x0012E694 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 84 <SQL_FILE_USAGE> PTR 0x0012E654 (0) SWORD 2 SWORD * 0x0012E694 (2) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 14 <SQL_SEARCH_PATTERN_ESCAPE> PTR 0x00AA1D08 SWORD 130 SWORD * 0x0012DFE0 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 14 <SQL_SEARCH_PATTERN_ESCAPE> PTR 0x00AA1D08 SWORD 130 SWORD * 0x0012DFE0 (0) MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 42 <SQL_CATALOG_TERM> PTR 0x00AA1D08 SWORD 130 SWORD * 0x0012E000 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 42 <SQL_CATALOG_TERM> PTR 0x00AA1D08 SWORD 130 SWORD * 0x0012E000 (0) MSQRY32 a18-890 ENTER SQLFreeStmt HSTMT 00AA25C8 UWORD 0 <SQL_CLOSE> MSQRY32 a18-890 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 00AA25C8 UWORD 0 <SQL_CLOSE> MSQRY32 a18-890 ENTER SQLFreeStmt HSTMT 00AA25C8 UWORD 2 <SQL_UNBIND> MSQRY32 a18-890 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 00AA25C8 UWORD 2 <SQL_UNBIND> MSQRY32 a18-890 ENTER SQLGetInfoW HDBC 00AA1BA8 UWORD 32 <SQL_MAX_SCHEMA_NAME_LEN> PTR 0x0012DFF0 SWORD 2 SWORD * 0x0012DFE0 MSQRY32 a18-890 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 UWORD 32 <SQL_MAX_SCHEMA_NAME_LEN> PTR 0x0012DFF0 (255) SWORD 2 SWORD * 0x0012DFE0 (2) MSQRY32 a18-890 ENTER SQLTablesW HSTMT 00AA25C8 WCHAR * 0x00AA2590 SWORD 0 WCHAR * 0x00AA2550 [ -3] "%\ 0" SWORD -3 WCHAR * 0x00AA1DA0 SWORD 0 WCHAR * 0x00AA1DB8 SWORD 0 MSQRY32 a18-890 EXIT SQLTablesW with return code 0 (SQL_SUCCESS) HSTMT 00AA25C8 WCHAR * 0x00AA2590 SWORD 0 WCHAR * 0x00AA2550 [ -3] "%\ 0" SWORD -3 WCHAR * 0x00AA1DA0 SWORD 0 WCHAR * 0x00AA1DB8 SWORD 0 MSQRY32 a18-890 ENTER SQLBindCol HSTMT 00AA25C8 UWORD 2 SWORD 1 <SQL_C_CHAR> PTR 0x0012E01C SQLLEN 65 SQLLEN * 0x0012DFFC MSQRY32 a18-890 EXIT SQLBindCol with return code 0 (SQL_SUCCESS) HSTMT 00AA25C8 UWORD 2 SWORD 1 <SQL_C_CHAR> PTR 0x0012E01C SQLLEN 65 SQLLEN * 0x0012DFFC (11148488) MSQRY32 a18-890 ENTER SQLFetch HSTMT 00AA25C8 MSQRY32 a18-890 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 00AA25C8 MSQRY32 a18-890 ENTER SQLFetch HSTMT 00AA25C8 MSQRY32 a18-890 EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 00AA25C8 MSQRY32 a18-890 ENTER SQLFetch HSTMT 00AA25C8 MSQRY32 a18-890 EXIT SQLFetch with return code 100 (SQL_NO_DATA_FOUND) HSTMT 00AA25C8 MSQRY32 a18-890 ENTER SQLFreeStmt HSTMT 00AA25C8 UWORD 0 <SQL_CLOSE> MSQRY32 a18-890 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 00AA25C8 UWORD 0 <SQL_CLOSE> MSQRY32 a18-890 ENTER SQLFreeStmt HSTMT 00AA25C8 UWORD 2 <SQL_UNBIND> MSQRY32 a18-890 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 00AA25C8 UWORD 2 <SQL_UNBIND> MSQRY32 a18-890 ENTER SQLTablesW HSTMT 00AA25C8 WCHAR * 0x00000000 [ -3] <empty string> SWORD -3 WCHAR * 0x00000000 [ -3] <empty string> SWORD -3 WCHAR * 0x00000000 [ -3] <empty string> SWORD -3 WCHAR * 0x00AA1DA0 [ 24] "'TABLE','VIEW','SYNONYM'" SWORD 24 MSQRY32 a18-890 EXIT SQLTablesW with return code -1 (SQL_ERROR) HSTMT 00AA25C8 WCHAR * 0x00000000 [ -3] <empty string> SWORD -3 WCHAR * 0x00000000 [ -3] <empty string> SWORD -3 WCHAR * 0x00000000 [ -3] <empty string> SWORD -3 WCHAR * 0x00AA1DA0 [ 24] "'TABLE','VIEW','SYNONYM'" SWORD 24 DIAG [HY000] [MonetDB][ODBC Driver 11.5.3]TypeException:user.s3_2[50]:'algebra.markT' undefined in: _96:any := algebra.markT(_95:bit, _90:oid) TypeException:user.s3_2[51]:'algebra.kunion' undefined in: _97:any := algebra.kunion(_96:any, _89:bat[:oid,:oid]) TypeException:user.s3_2[52]:'algebra.markT' undefined in: _98:any := algebra.markT(_97:any, _82:oid) TypeException:user.s3_2[53]:'algebra.semijoin' undefined in: _99:any := algebra.semijoin(_98:any, _81:bat[:oid,:void]) TypeException:user.s3_2[73]:'algebra.kunion' undefined in: _127:any := algebra.kunion(_126:bat[:oid,:oid], _99:any) TypeException:user.s3_2[74]:'algebra.markT' undefined in: _128:any := algebra.markT(_127:any, _66:oid) TypeException:user.s3_2[75]:'bat.reverse' undefined in: _129:any := bat.reverse(_128:any) TypeException:user.s3_2[76]:'algebra.leftjoin' undefined in: _130:any := algebra.leftjoin(_129:any, _63:bat[:oid,:int]) TypeException:user.s3_2[77]:'bat.reverse' undefined in: _131:any := bat.reverse(_130:any) TypeException:user.s3_2[89]:'algebra.join' undefined in: _147:any := algebra.join(_146:bat[:oid,:int], _131:any) TypeException:user.s3_2[90]:'algebra.markT' undefined in: _148:any := algebra.markT(_147:any, _57:oid) TypeException:user.s3_2[91]:'bat.reverse' undefined in: _149:any := bat.reverse(_148:any) TypeException:user.s3_2[92]:'algebra.leftjoin' undefined in: _150:any := algebra.leftjoin(_149:any, _56:bat[:oid,:str]) TypeException:user.s3_2[140]:'algebra.markT' undefined in: _213:any := algebra.markT(_212:bit, _208:oid) TypeException:user.s3_2[141]:'algebra.kunion' undefined in: _214:any := algebra.kunion(_213:any, _207:bat[:oid,:oid]) TypeException:user.s3_2[142]:'algebra.markT' undefined in: _215:any := algebra.markT(_214:any, _194:oid) TypeException:user.s3_2[143]:'algebra.semijoin' undefined in: _216:any := algebra.semijoin(_215:any, _193:bat[:oid,:void]) TypeException:user.s3_2[173]:'algebra.kunion' undefined in: _257:any := algebra.kunion(_256:bat[:oid,:oid], _216:any) TypeException:user.s3 (0) MSQRY32 a18-890 ENTER SQLErrorW HENV 00AA1788 HDBC 00AA1BA8 HSTMT 00AA25C8 WCHAR * 0x0012E4E0 (NYI) SDWORD * 0x0012E564 WCHAR * 0x0012E0E0 SWORD 512 SWORD * 0x0012E56C MSQRY32 a18-890 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 00AA1788 HDBC 00AA1BA8 HSTMT 00AA25C8 WCHAR * 0x0012E4E0 (NYI) SDWORD * 0x0012E564 (0) WCHAR * 0x0012E0E0 [ 512] "[MonetDB][ODBC Driver 11.5.3]TypeException:user.s3_2[50]:'algebra.markT' undefined in: _96:any := algebra.markT(_95:bit, _90:oid)\ aTypeException:user.s3_2[51]:'algebra.kunion' undefined in: _97:any := algebra.kunion(_96:any, _89:bat[:oid,:oid])\ aTypeException:user.s3_2[52]:'algebra.markT' undefined in: _98:any := algebra.markT(_97:any, _82:oid)\ aTypeException:user.s3_2[53]:'algebra.semijoin' undefined in: _99:any := algebra.semijoin(_98:any, _81:bat[:oid,:void])\ aTypeException:user.s3_2[73]:'algebra.kunion' un\ 0" SWORD 512 SWORD * 0x0012E56C (7860) MSQRY32 a18-890 ENTER SQLErrorW HENV 00AA1788 HDBC 00AA1BA8 HSTMT 00AA25C8 WCHAR * 0x0012E4E0 (NYI) SDWORD * 0x0012E564 WCHAR * 0x0012E0E0 SWORD 512 SWORD * 0x0012E56C MSQRY32 a18-890 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND) HENV 00AA1788 HDBC 00AA1BA8 HSTMT 00AA25C8 WCHAR * 0x0012E4E0 (NYI) SDWORD * 0x0012E564 WCHAR * 0x0012E0E0 SWORD 512 SWORD * 0x0012E56C MSQRY32 a18-890 ENTER SQLFreeStmt HSTMT 00AA25C8 UWORD 1 <SQL_DROP> MSQRY32 a18-890 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 00AA25C8 UWORD 1 <SQL_DROP> MSQRY32 a18-890 ENTER SQLDisconnect HDBC 00AA1BA8 MSQRY32 a18-890 EXIT SQLDisconnect with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 MSQRY32 a18-890 ENTER SQLFreeConnect HDBC 00AA1BA8 MSQRY32 a18-890 EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS) HDBC 00AA1BA8 MSQRY32 a18-890 ENTER SQLFreeEnv HENV 00AA1788 MSQRY32 a18-890 EXIT SQLFreeEnv with return code 0 (SQL_SUCCESS) HENV 00AA1788

2 2

[MonetDB-users] Bulk-importing boolean columns
by Viktor Rosenfeld 19 Oct '11

19 Oct '11

Hi, I need to bulk-import data that was bulk-exported from PostgreSQL. I have only limited control over the raw data. I'm using the following command: COPY INTO table FROM '/path/to/filename' USING DELIMITERS '\t','\n',''; This generally works. The problem is boolean data which is exported by PostgreSQL using the values "t" and "f" (unquoted). MonetDB expects "true" and "false" here. My current work-around is opening the file in Vim and substituting the values (%s/\tt\t/\ttrue\t/g and %s/\tf\t/\tfalse\t/g). Is there a way to make MonetDB accept the original PostgreSQL data? Cheers, Viktor

4 6

[MonetDB-users] Date and time Functions
by Roula Avg 19 Oct '11

19 Oct '11

Hello, i am looking for a manual about all the date and time functions supported in Monetdb. In the official site there is no such manual, can anyone help me please? Thank you very much

3 3

[MonetDB-users] test - ignore <eom>
by nat lu 19 Oct '11

19 Oct '11

1 0

[MonetDB-users] ODBC connection issues
by Jeremy OSullivan 19 Oct '11

19 Oct '11

Hello, I have issues connecting to monetdb using the supplied odbc driver via Oracle's OBI EE admin tool. I am on Windows 2008, 32 bit. When I connect, I get the attached error. I have also attached the odbc configuration. Any help would be greatly appreciated ! Regards, Jeremy

4 4

[MonetDB-users] Insert query into second database
by Seleen 17 Oct '11

17 Oct '11

Hello, I have two databases "mydb" and "test". I want to get a sub-set of values from 'mydb' into 'test'. My code: "insert into test.result select sum(chargfeeprepaid) from mydb.data where callingpartyno = 200 union select sum(chargefeeprepaid) from mydb.sms where callingpartyno = 300;" This works fine in MySQL...but in Monetdb i get the error: INSERT INTO: no such scheme 'test'. Where did i go wrong and what is the correct syntax to do this in monetdb? Greetings Seleen -- View this message in context: http://old.nabble.com/Insert-query-into-second-database-tp32667556p32667556… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] JDBC: Problem with PreparedStatement parameter in Buffer
by George Garbis 10 Oct '11

10 Oct '11

Hi all, I am trying to use PreparedStatement to get a buffer of a geometry invoking the commands bellow PreparedStatement st = con.prepareStatement("CREATE TABLE geom (g > geometry)"); > st.execute(); > st = con.prepareStatement("INSERT INTO geom VALUES(GeomFromText('POINT(1 > 1)', 2100))"); > st.execute(); > st = con.prepareStatement("SELECT Buffer(t.g, ?) AS buffer, t.g AS point > FROM geom AS t"); // This line throws an SQLException > st.setFloat(1, (float)1.0); > ResultSet results = st.executeQuery(); > However the fifth line throws an SQLException with this message: > types geometry(0,0) and double(53,0) are not equal > Replacing the question mark with a number (e.g. 1.0) the program executes properly. Why is this exception thrown? Is something wrong in my code? Thanks in advance, George Garbis

2 2

[MonetDB-users] Monetdb installation on Debian(lenny) doesn't work
by Benji07 05 Oct '11

05 Oct '11

System information : Debian GNU/Linux 5.0.8 (lenny) Hardware Platform : x86_64 GNU/Linux(amd64) I followed the next steps to install Monetdb : 1 - Add next lines in /etc/apt/sources.list : deb http://dev.monetdb.org/downloads/deb/ lenny monetdb deb-src http://dev.monetdb.org/downloads/deb/ lenny monetdb 2 - Install the MonetDB GPG public key 3 - Install the MonetDB packages sudo apt-get update But I get the next message : Failed to fetch http://dev.monetdb.org/downloads/deb/dists/lenny/Release Unable to find expected entry monetdb/binary-amd64/Packages in Meta-index file (malformed Release file?) The file 'Packages' seem to be missing (among others) in this directory, I think the error is related to that. http://dev.monetdb.org/downloads/deb/dists/lenny/monetdb/binary-amd64/ For install of Debian Squeeze, I didn't have any problem. Thanks for you help. -- View this message in context: http://old.nabble.com/Monetdb-installation-on-Debian%28lenny%29-doesn%27t-w… Sent from the monetdb-users mailing list archive at Nabble.com.

2 2

[MonetDB-users] Trigger definitions - ambiguous identifier error
by Benji07 04 Oct '11

04 Oct '11

I've the following table on my database : \d sys.subscriber_data CREATE TABLE "sys"."subscriber_data" ( "subs_id" BIGINT, "imsi" VARCHAR(16), "mccmnc" INTEGER, "msisdn" VARCHAR(16), "imei" VARCHAR(16), "time_idx_print" BIGINT, "time_idx_lastmodified" BIGINT, "time_idx_purge" BIGINT, "merge_subs_id" BIGINT ); I want to create a specific trigger to delete old data in table before insert new data. create trigger oninsert_subscriber before insert on subscriber_data referencing NEW ROW AS new_ident for each row begin atomic delete from subscriber_data where subscriber_data.subs_id = new_ident.subs_id; end; When I executed the statement, I get this error message : SELECT: identifier 'subs_id' ambiguous I don't understand how to use alias in this case to reference old and new data. I wrote the statement according to the documentation: http://www.monetdb.org/Documentation/SQLreference/Triggers However, I found this line in feature overview (unsupported) page T211 Basic trigger capability http://www.monetdb.org/Documentation/Manuals/SQLreference/Features/unsuppor… Does it mean triggers are not yet entirely supported by Monetdb? Thanks for your help. -- View this message in context: http://old.nabble.com/Trigger-definitions---ambiguous-identifier-error-tp32… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] SQL-MAL correlation
by Liang Bing 30 Sep '11

30 Sep '11

Hallo MonetDB-Team, I have been trying to understand MonetDB-MAL better. I have prepared a table called TEST1. sql>SELECT * FROM TEST1; +-----+ | een | +=====+ +-----+ 0 tuples (1.452ms) In order to see the internal process I use the command EXPLAIN. sql>EXPLAIN SELECT * FROM TEST1; +---------------------------------------------------------------------------------------------------+ | mal | +===================================================================================================+ | function user.s3_2{autoCommit=true}():void; | | barrier _32 := language.dataflow(); | | _2 := sql.mvc(); | | _12:bat[:oid,:str] := sql.bind(_2,"sys","test1","een",0); | | _10:bat[:oid,:str] := sql.bind(_2,"sys","test1","een",2); | | _14 := algebra.kdifference(_12,_10); | | _15 := algebra.kunion(_14,_10); | | _9:bat[:oid,:str] := sql.bind(_2,"sys","test1","een",1); | | _16 := algebra.kunion(_15,_9); | | _5:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","test1",1); | | _8 := bat.reverse(_5); | | _17 := algebra.kdifference(_16,_8); | | exit _32; | | _18 := sql.resultSet(1,1,_17); | | sql.rsColumn(_18,"sys.test1","een","char",2,0,_17); | | _22 := io.stdout(); | | sql.exportResult(_22,_18); | | end s3_2; | +---------------------------------------------------------------------------------------------------+ 18 tuples (1.240ms) Then I have tried to run the command from the lower level. Basically it is almost a copy of the above result. mal>function user.abc{autoCommit=true}():void; mal>barrier _32 := language.dataflow(); mal>_2 := sql.mvc(); mal>_12:bat[:oid,:str] := sql.bind(_2,"sys","test1","een",0); mal>_10:bat[:oid,:str] := sql.bind(_2,"sys","test1","een",2); mal>_14 := algebra.kdifference(_12,_10); mal>_15 := algebra.kunion(_14,_10); mal>_9:bat[:oid,:str] := sql.bind(_2,"sys","test1","een",1); mal>_16 := algebra.kunion(_15,_9); mal>_5:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","test1",1); mal>_8 := bat.reverse(_5); mal>_17 := algebra.kdifference(_16,_8); mal>exit _32; mal>_18 := sql.resultSet(1,1,_17); mal>sql.rsColumn(_18,"sys.test1","een","char",2,0,_17); mal>_22 := io.stdout(); mal>sql.exportResult(_22,_18); mal>end abc; MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = end abc; ERROR = !TypeException:user.abc[13]:'X17' may not be used before being initialized !TypeException:user.abc[14]:'X17' may not be used before being initialized I expected to get a result like this: +-----+ | een | +=====+ +-----+ But instead, I got an error message. Is there probably anyone who could tell me what the error message means and what I need to change in the MAL-syntax? Another question: Maybe you could give a hint where I can find good references about SQL-MAL correlation. Thank you in advance! Kind regards, Liang Bing -- View this message in context: http://old.nabble.com/SQL-MAL-correlation-tp32557812p32557812.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Announcement: New Aug2011-SP1 bugfix release of MonetDB suite
by Fabian Groffen 30 Sep '11

30 Sep '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Aug2011-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at http://www.monetdb.org/. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Aug 2011-SP1 bugfix release SQL * Fixed a bug when using default values for interval columns. Bug 2877. * A bug was fixed where deleted rows weren't properly accounted for in all operations. This was bug 2882. * A bug was fixed which caused an update to an internal table to happen too soon. The bug could be observed on a multicore system with a query INSERT INTO t (SELECT * FROM t) when the table t is "large enough". This was bug 2883. Client Package * Fixed Perl DBD rowcount for larger results, bug #2889 * Perl: We now distinguish properly between TABLE and GLOBAL TEMPORARY (the latter are recognized by being in the "tmp" schema). * Perl: fixed a bunch of syntax errors. This fixes bug 2884. With thanks to Rémy Chibois. * Perl: Fixed DBD::monetdb table_info and tabletype_info. This fixes bug 2885. With thanks to Rémy Chibois. * mclient: fix display of varchar columns with only NULL values. * Fixed a bug in mclient/msqldump where an internal error occurred during dump when there are GLOBAL TEMPORARY tables. MonetDB5 Server * Fixed a problem where MAL variables weren't properly cleared before reuse of the data strucutre. This problem could cause the data flow scheduler to generate dependencies between instructions that didn't actually exist, which in turn could cause circular dependencies among instructions with deadlock as a result. Bugs 2865 and 2888. Bug Fixes * 2865: mserver5 --set gdk_nr_threads=2 --forcemito: deadlock during first SQL client connect on virgin (empty) DB * 2877: assertion failed when creating table with default interval * 2879: A SELECT query fails to produce some results * 2882: Delete of some records don't work on Monetdb * 2883: insert into t (select * from t) bogus in multithreaded execution * 2884: Mapi.pm and GetInfo.pm syntax fixes for array usage * 2885: DBD::monetdb table_info error * 2887: Division by zero error, when should not divide at 0 * 2888: test/null/null_in_case.sql hangs server * 2889: Mapi::getBlock is erasing query count on second call

1 0

[MonetDB-users] copy ... from stdin in perl script
by Eugene Prokopiev 28 Sep '11

28 Sep '11

Hi, I have table t1(f1 integer) I can copy to it by executing this file via mclient: $ cat /tmp/f1.sql copy into t1 from stdin; 1 2 3 But I can't copy to it by this perl script: $ cat /tmp/f1.pl #!/usr/bin/perl -w use DBI; my $dbh = DBI->connect('dbi:monetdb:database=db', 'dbadmin', "dbpwd"); $dbh->do("copy into t1 from stdin;\n1\n2\n3\n"); I got: Missing argument in sprintf at /usr/lib64/perl5/DBD/monetdb/GetInfo.pm line 25. No query result at /usr/lib64/perl5/MonetDB/CLI/MapiLib.pm line 80. Use of uninitialized value in die at /usr/lib64/perl5/MonetDB/CLI/MapiLib.pm line 180. (in cleanup) Died at /usr/lib64/perl5/MonetDB/CLI/MapiLib.pm line 180. Why can it be? -- Thanks, Eugene Prokopiev

2 9

[MonetDB-users] CSV Data Fault still used ?
by Stéphane NOUHAUD 26 Sep '11

26 Sep '11

Hi, I try to use CSV Data Fault (Documented here http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/CSVvault) without great succes... Just quick question is CSV Data Fault function still included in last version of MonetDB ? Except that MonetDB is really fantastic software. Thanks for sharing it ! Regards, -- *Stéphane NOUHAUD* * *

1 0

[MonetDB-users] Parallel COPY INTO
by zack_pcd 22 Sep '11

22 Sep '11

I have a large table with about 50 billion records. The files to be loaded are not in CSV format. The files are processed in java and the data uploaded to the database using COPY INTO through the MapiSocket interface. I do not have the record count readily available. My question is this: If I open multiple MapiSockets and upload multiple streams of data at the same time to the same table, is this supported and stable? I am asking because the database appears to become unresponsive after the bulk load. (mclient seems to hang on connect) If I stop and the start the database after the bulk load, I get: "failed to fork mserver: database 'test_db' has inconsistent state (sabaoth administration reports running, but process seems gone)" -- View this message in context: http://old.nabble.com/Parallel-COPY-INTO-tp32503834p32503834.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] Partitioning on Monetdb
by Benji07 21 Sep '11

21 Sep '11

Does Monetdb supports basic table partitioning using MAPI? I need to split one large table into smaller physical pieces and improve query performance. I didn't find help in documentation. Thanks for help -- View this message in context: http://old.nabble.com/Partitioning-on-Monetdb-tp32493404p32493404.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

[MonetDB-users] EPS / SVG of new MonetDB logo
by Stefan de Konink 20 Sep '11

20 Sep '11

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Hi, The new logo is online on the new website for a while now, is there any EPS/SVG or other vector format available? Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.18 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAk547rYACgkQYH1+F2Rqwn14VwCgioRrllvOlEUwHJke6eLxdXca rm0An2CwkBr1KoUWmgYewB64U5B49GtS =ef+A -----END PGP SIGNATURE-----

2 3

[MonetDB-users] GUID
by Michal Dvorak 20 Sep '11

20 Sep '11

Hi MonetDB-Team, are you going to implement 128b GUID? If not, any idea how to bypass it? 2x Int64 or BLOB(128)? Thank you very much for your answer J Best regards, Michal

3 2

[MonetDB-users] Maximum Parallelism and Recommended Hardware for MonetDB
by brien colwell 20 Sep '11

20 Sep '11

I set up a MonetDB server for a data mining team on a single node. The node's specs are 32 cores (4x8 AMD 2.3GHz) 128GiB RAM, with the dbfarm on a RAID0 2xSSD (I think they are Intel SSDs). MonetDB on this hardware works well for aggregates and joins on integers, but chugs considerably on inline views with strings and joins with strings. The team usually has 2-3 concurrent jobs at any given time, some materializing new tables, with lots of bulk loading ("copy into ... from file ..."). A typical table size is 2GiB, and queries may join 5-10 tables. When I look at process activity on the node, I usually see each DB's mserver5 process pegged on CPU, using just a single core. Since I rarely see more than one core used per mserver5, I'm starting to think either I haven't configured MonetDB to favor parallel plans, or I chose the wrong hardware for MonetDB. So, as questions to everyone on the list, I'm curious 1) Are there knobs in MonetDB to favor parallel execution plans? 2) What's the ideal hardware for a single-node MonetDB server to support a workload of 2-5 concurrent queries? 3) What's the ideal disk configuration for the above? Brien

2 1

[MonetDB-users] How to compress a table
by Alexis Berlemont 20 Sep '11

20 Sep '11

Hi, I am currently trying to evaluate MonetDB as a solution to hold significant amounts of data. Because of limited storage capacities, I am trying to enable compression in MonetDB. I did not manage to find any tutorial on that subject, so I did not go very far. I cannot even tell that I wholly understand the problem. According to the page "Documentation->User Guide->compression", I understood that I had to inject a compression optimizer inside a pipeline. This "thing" is supposed to be possible via the setting of a sql environment variable named "optimizer". In the (generated) source file monetdb5/optimizer/opt_pipes.c, I found a default + compression pipeline. Then, in a mclient session, I typed: sql>set optimizer='inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,compression,dataflow,history,multiplex,garbageCollector'; After that, I tried to use a sql function which was supposed to compress (thanks to the zlib) a table: sql.gzcompress sql>call gzcompress(myschema, mytable); Unfortunately, I am stuck here with the error message 'SELECT: identifier 'myschema' unknown'. I think I located the error message in "sql/server/rel_select.c" but I did not manage to deduce anything interesting. Do you have any idea ? Thanks in advance. Alexis.

2 1

[MonetDB-users] How to change in/out date format
by Eugene Prokopiev 20 Sep '11

20 Sep '11

Hi, is it possible to use "yy-MM-dd HH:mm:ss.SSS" date format via something like SET DATESTYLE in COPY INTO ... FROM ... with mclient to reuse existing files? -- Thanks, Eugene Prokopiev

2 1

[MonetDB-users] query time
by anagnostes 20 Sep '11

20 Sep '11

Hi. I would like to know if it's possible to get the query time using the input "\<file" and redirecting the output to "\>file" in Windows. When i use the input file it nevers throws the query time. So i can't know it. Thank you. -- View this message in context: http://old.nabble.com/query-time-tp32232809p32232809.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Memory leaks in the latest trunk?
by Stefan de Konink 20 Sep '11

20 Sep '11

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Hello, Recently I decided it was again a good idea(tm) to 'test' the latest MonetDB trunk on an updated dataset. Sadly over the last 6 days I observed a growing memory usage, today resulting in a segmentation fault. For now nothing more than mserver5[26142]: segfault at 8 ip 00007f1e1e592a31 sp 00007f1e00ca63b8 error 4 in libc-2.13.so[7f1e1e515000+17d000] is known to me, which is development wise totally useless. But an other question did arise: is testing currently taking memory into a count? Especially longer tests might show greater usage of memory. It is obviously no problem replay all the queries that ran against the database and valgrind it... but there might have been work done from development respective that can shed light over the problems. Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.17 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAk5OiAAACgkQYH1+F2Rqwn3d+ACdH5ySymgx46EON89qdnlYof39 ZHsAn1LjL9eOHncOjM99+wAupwMC6OR3 =BsVB -----END PGP SIGNATURE-----

2 1

[MonetDB-users] count slower than expected?
by Joris Slob 20 Sep '11

20 Sep '11

Hello mailing list! This is my first attempt to ask a question here, so please excuse my ignorance. For large tables (>1M rows), I encounter strange behavior when using the COUNT function in SQL in the mclient. When I do the following query: SELECT DISTINCT(predicate) FROM facts; I get a nice resultset of 31 tuples in 20ms. When I try the following query: SELECT COUNT(DISTINCT(predicate)) FROM facts; I get my answer in 565ms. It seems weird that the database doesn't 'optimize' this count to: SELECT COUNT(*) FROM (SELECT DISTINCT(predicate) FROM facts) AS b; I get my answer in 20ms. The facts table is defined as follows: CREATE TABLE "sys"."facts" ( "subject" CHAR(2048), "predicate" CHAR(2048), "object" CHAR(2048) ); This is my first attempt to make a knowledge store and I am aware there is lots I can do to optimize how I store the data. I was just surprised that the count took significantly longer than getting all the results out and counting them. Here is the version information of MonetDB I am using: MonetDB 5 server v11.3.7 "Apr2011-SP2" (32-bit, 32-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 1.0GiB available memory, 2 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 0.9.8k 25 Mar 2009 (compiled with OpenSSL 0.9.8k 25 Mar 2009) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: root(a)ottar.ins.cwi.nl (i486-pc-linux-gnu) Compilation: gcc -Wall -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : /usr/bin/ld -Wl,-Bsymbolic-functions Greetings, Joris

2 1

[MonetDB-users] omission in documentation?
by Sander Kruseman 20 Sep '11

20 Sep '11

Hi, I'm unable to find documentation on 'mapi_open' on the new website. I read the 5 available man pages, but found no mention of it. The man page for monetdb does however, mention monetdb.conf(5) but I cannot find that one either. Am I overlooking something? regards, Sander Kruseman

2 1

[MonetDB-users] Announcement: New Apr2011-SP2 bugfix release of MonetDB suite
by Sjoerd Mullender 16 Sep '11

16 Sep '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Aug2011 feature release of the MonetDB suite of programs. The default width for OID columns in the database is now 64 bits for all 64-bit architecture. In releases up to this release, the default width on 64-bit Windows was still 32 bits. This means that with the current release it is possible to have tables with more than about 2 million rows even on Windows (as long as you use a 64-bit build). More information (including release notes) on this release is available at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. -- Sjoerd Mullender

1 0

[MonetDB-users] Calling a stored procedure from java
by elarios 13 Sep '11

13 Sep '11

Hi, Please, I would like to know the correct sintaxis for calling a stored procedures in MonetDB from a Java application. I was using some code that works fine in MySQL but this doesn't work in MonetDB. The code inside a method is: ------------------------------------------------------------------- Connection con = null; ResultSet rs = null; CallableStatement cs = null; cs = con.prepareCall("{call hts.spInsertFile(?)}"); File fl = new File("/home//Test/example.pdf"); imgfile = new FileInputStream(fl); cs.setBinaryStream(1,imgfile); rs = cs.executeQuery(); ------------------------------------------------------------------- When I execute the stored procedure from Netbeans IDE the stored procedure works fine. I've made a trace in the application and the statement: con.prepareCall("{call hts.spInsertFile(?)}"); returns null and finally throws an exception: Exception in thread "main" java.lang.NullPointerException at testmonetdb.DBase.set_file(DBase.java:86) at testmonetdb.MonetDBTest.main(MonetDBTest.java:34) The MonetDB version I'm using is: MonetDB 5 server v11.3.7 "Apr2011-SP2" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 3.9GiB available memory, 4 available cpu cores Libraries: libpcre: 8.12 2011-01-15 (compiled with 8.10) openssl: OpenSSL 1.0.0d 8 Feb 2011 (compiled with OpenSSL 1.0.0d-fips 8 Feb 2011) libxml2: 2.7.7 (compiled with 2.7.7) Compiled by: mockbuild@ (x86_64-unknown-linux-gnu) Compilation: gcc -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : /usr/bin/ld -m elf_x86_64 I'm also using Netbeans IDE 7.1 with the jdbcclient.jar (http://dev.monetdb.org/downloads/Java/Oct2010-SP1/) Thanks in advance for your help, Enrique. -- View this message in context: http://old.nabble.com/Calling-a-stored-procedure-from-java-tp32431606p32431… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Does MonetDB support materialized view or not?
by Ken Cheng 03 Sep '11

03 Sep '11

Hi All, Does MonetDB support materialized view or not? Yours sincerely, Ken Cheng

3 3

[MonetDB-users] stack protector error
by Michael Sioutis 27 Aug '11

27 Aug '11

Hello, I get an error when trying to compile latest MonetDB default branch on my Ubuntu 10.04 64bit machine. bootstrap: OK configure (../configure --prefix=/opt/MonetDB/ --enable-monetdb5 --enable-sql --enable-rdf --enable-geom --enable-bits=64 --enable-debug --enable-datacell): OK make -j2: ERROR The error is: ../../gdk/gdk_batop.mx: In function ‘BATsetprop’: ../../gdk/gdk_batop.mx:2890: error: not protecting function: no buffer at least 8 bytes long I think flags -fstack-protector-all and -Wstack-protector are to blame, they default to 8 bytes. I can skip the problem if I set 8 byte long dummy buffers to EVERY function that doesn't have such a buffer, e.g., char mybuf[8]; memset(buf, 0, sizeof(buf)); I also thought of setting the buffer size to 1, by passing --param spp-buffer-size=1 as a compiler option, but then I get various other errors. gcc version: gcc (Ubuntu 4.4.3-4ubuntu5) 4.4.3 Copyright (C) 2009 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. Mike

2 8

[MonetDB-users] Current Pathfinder list/team
by Anton Kolev 21 Aug '11

21 Aug '11

Gentlemen, Which is the proper current list (or personal contact) for the Pathfinder related questions? Are there recent work after Mar2011 branch [1]? Thank you in advance, Anton [1] http://dev.monetdb.org/hg/MonetDB/file/932a58fecbde/pathfinder

2 1

[MonetDB-users] Does MonetDB support regular expression predicates?
by Viktor Rosenfeld 21 Aug '11

21 Aug '11

Hi, does MonetDB support regular expression matching on strings? It's supposedly part of SQL:99, but I couldn't find anything about this on the homepage or the mailing list. I only found the Data Manipulation reference, which lists LIKE predicates. Cheers, Viktor

2 1

[MonetDB-users] Are there any unit test for each checkin/fix
by Ken Cheng 20 Aug '11

20 Aug '11

Hi All, Are there any unit test for regression test for each checkin/fix? Seems I can not find any test related source check in history. Yours sincerely, Ken Cheng

2 1

[MonetDB-users] Compiling MonetDB with JDBC on OS X 10.6
by Viktor Rosenfeld 18 Aug '11

18 Aug '11

Hi, I've tried building the latest MonetDB sources on OS X 10.6.8 and it fails with the following error message: checking for ant... /usr/bin/ant checking for java... /usr/bin/java checking for javac... /usr/bin/javac checking for jar... /usr/bin/jar checking for javadoc... /usr/bin/javadoc configure: error: MonetDB JDBC requires ant and Java 1.5 I used the following build options: ./configure --prefix=/Users/he-sk/unix --enable-strict --disable-assert --disable-debug --enable-optimize --enable-jdbc Searching on the mailing list, I found a post saying that the MonetDB JDBC driver will not build with Java 1.6 (the default on OS X 10.6), because the JDBC API changed from 1.5 to 1.6. However, the post is from September 2008, almost 3 years ago. Is this still the case? Or am I running into a different problem? List post: http://old.nabble.com/Installation-Problems-to19607306.html#a19645299 If MonetDB JDBC does require 1.5, I'm thinking of following the steps outlined here: http://wiki.oneswarm.org/index.php/OS_X_10.6_Snow_Leopard Does anybody have experience with this procedure or does anybody know an alternative? Also, I'm about to upgrade to OS X Lion (10.7). Is anybody running the MonetDB JDBC driver on Lion yet? Are you running it from sources or did you install the binary MonetDB distribution? Cheers, Viktor

2 2

[MonetDB-users] How to update one table from another
by Eugene Prokopiev 12 Aug '11

12 Aug '11

Hi, I have two tables: table1 ----------- id data ----------- 0 0 1 1 2 2 ----------- table2 ----------- id data ----------- 2 2 3 3 ----------- I need to increment data in big table1 by data from small table2 if it exists or insert absent data from table2 to table1. So result must looks like: table1 ----------- id data ----------- 0 0 1 1 2 4 3 3 ----------- I used loop by table2 rows and inserts or updates table1 in this loop in PostgreSQL. What is the most effective way to do it in MonetDB? -- Thanks, Eugene Prokopiev

2 2

[MonetDB-users] JOIN for functions
by Eugene Prokopiev 09 Aug '11

09 Aug '11

Hi, Is it possible to join functions which returns tables? I've got error: sql>select sumdate from summary_ip_in('wan') limit 1; +------------+ | sumdate | +============+ | 2-08-11 | +------------+ 1 tuple (1m 14s) sql>select sumdate from summary_ip_out('wan') limit 1; +------------+ | sumdate | +============+ | 2-08-11 | +------------+ 1 tuple (10m 59s) sql>select sumdate from summary_ip_in('wan') inner join summary_ip_out('wan') on summary_ip_in.sumdate = summary_ip_out.sumdate limit 1; SELECT: no such column 'summary_ip_in.sumdate' sql>select sumdate from summary_ip_in('wan') inner join summary_ip_out('wan') on sumdate = sumdate limit 1; SELECT: identifier 'sumdate' ambiguous What's wrong? -- Thanks, Eugene Prokopiev

2 2

[MonetDB-users] Check for inet equals
by Eugene Prokopiev 06 Aug '11

06 Aug '11

Hi, How to select from table by value in column with INET type? For example, I have table: $ msqldump -d billing -- msqldump dump database Mon Aug 1 11:03:13 2011 -- MonetDB v11.3.3, 'b0' START TRANSACTION; SET SCHEMA "sys"; CREATE TABLE "sys"."transfer_ip" ( "packetdate" TIMESTAMP, "proto" INTEGER, "src" INET, "sport" INTEGER, "sintf" VARCHAR(15), "dst" INET, "dport" INTEGER, "dintf" VARCHAR(15), "packets" BIGINT, "bytes" BIGINT, "prefix" VARCHAR(5), "processed" BOOLEAN ); COMMIT; COPY 10293965 RECORDS INTO "sys"."transfer_ip" FROM stdin USING DELIMITERS '\t','\n','"'; ^C I tried to select in such ways: # mclient -d billing Welcome to mclient, the MonetDB/SQL interactive terminal (Apr2011-SP1) Database: MonetDB v11.3.3, 'billing' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from transfer_ip where src = '10.7.1.10' limit 10; SQLException:str_cast:value too long for type (var)char(9) sql>select * from transfer_ip where src = '10.7.1.10':inet limit 10; unexpected symbol (:) sql>select * from transfer_ip where src << '10.7.1.10' limit 10; types inet(0,0) and tinyint(8,0) are not equal for column 'src' What it the right way? -- Thanks, Eugene Prokopiev

2 5

[MonetDB-users] openssl not found when it is present
by Medha Atre 04 Aug '11

04 Aug '11

I am trying to compile MonetDB-11.3.7 on Ubuntu 10.04 distribution (2.6.32-33 64 bit kernel). It fails with "openssl" not found error: I am running configure script with following options: $ ./configure --prefix=/work2/MonetDB-install-11.3.7 --enable-optimize --enable-sql --enable-monetdb5 <snip> checking for iconv... yes checking for working iconv... yes checking for iconv declaration... extern size_t iconv (iconv_t cd, char * *inbuf, size_t *inbytesleft, char * *outbuf, size_t *outbytesleft); checking for library containing uuid_generate... no checking for openssl... no configure: error: OpenSSL library not found but required for MonetDB5 $ openssl libraries are present on my system. I tried: $ cd /lib $ ll *ssl* -rw-r--r-- 1 root root 333856 2011-02-09 20:50 libssl.so.0.9.8 $ $ $ sudo apt-get install openssl Reading package lists... Done Building dependency tree Reading state information... Done openssl is already the newest version. 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. $ Can someone please let me know if I am missing any step or need to do something else for the configure script to find SSL libraries? Thanks in anticipation. Medha

4 5

[MonetDB-users] Long running query with count(distinct ...)
by Eugene Prokopiev 02 Aug '11

02 Aug '11

Hi, I have table with 81 887 010 rows. Select count from this table works fine, but I can't wait for the results of select count(distinct ...) from this table. Why can it be? sql>explain select count(src) from transfer_ip; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +==================================================================================================================================================================================+ | function user.s1_1{autoCommit=true}():void; | | barrier _55 := language.dataflow(); | | _2 := sql.mvc(); | | _39:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,0,2); | | _43 := algebra.selectNotNil(_39); | | _35:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,0,2); | | _45 := algebra.kdifference(_43,_35); | | _41 := algebra.selectNotNil(_35); | | _47 := algebra.kunion(_45,_41); | | _6:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","transfer_ip",1); | | _9 := bat.reverse(_6); | | _49 := algebra.kdifference(_47,_9); | | _51 := aggr.count(_49); | | _40:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,1,2); | | _44 := algebra.selectNotNil(_40); | | _38:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,1,2); | | _46 := algebra.kdifference(_44,_38); | | _42 := algebra.selectNotNil(_38); | | _48 := algebra.kunion(_46,_42); | | _52 := aggr.count(_48); | | _10:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",1); | | _11 := algebra.selectNotNil(_10); | | _53 := aggr.count(_11); | | _50 := mat.pack(_51,_52,_53); | | _54 := algebra.selectNotNil(_50); | | _14 := aggr.sum(_54); | | exit _55; | | sql.exportValue(1,"sys.transfer_ip","L4","wrd",64,0,6,_14,""); | | end s1_1; | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 29 tuples (12.925ms) sql>explain select count(distinct src) from transfer_ip; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +==================================================================================================================================================================================+ | function user.s2_1{autoCommit=true}():void; | | barrier _56 := language.dataflow(); | | _2 := sql.mvc(); | | _42:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,0,2); | | _46 := algebra.selectNotNil(_42); | | _38:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,0,2); | | _48 := algebra.kdifference(_46,_38); | | _44 := algebra.selectNotNil(_38); | | _50 := algebra.kunion(_48,_44); | | _6:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","transfer_ip",1); | | _9 := bat.reverse(_6); | | _52 := algebra.kdifference(_50,_9); | | _53 := bat.reverse(_52); | | _43:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,1,2); | | _47 := algebra.selectNotNil(_43); | | _41:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,1,2); | | _49 := algebra.kdifference(_47,_41); | | _45 := algebra.selectNotNil(_41); | | _51 := algebra.kunion(_49,_45); | | _54 := bat.reverse(_51); | | _10:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",1); | | _11 := algebra.selectNotNil(_10); | | _55 := bat.reverse(_11); | | _14 := mat.pack(_53,_54,_55); | | _15 := algebra.kunique(_14); | | _16 := bat.reverse(_15); | | _17 := aggr.count(_16); | | exit _56; | | sql.exportValue(1,"sys.transfer_ip","L5","wrd",64,0,6,_17,""); | | end s2_1; | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 30 tuples (1.238ms) -- Thanks, Eugene Prokopiev

3 4

[MonetDB-users] Vacuum command missing
by Eugene Prokopiev 01 Aug '11

01 Aug '11

Hi, I can't find VACUUM command in http://www.monetdb.org/Documentation, I see only vacuum cleaning algorithm mention in http://www.monetdb.org/Documentation/Manuals/SQLreference/Transactions. So, how can phisicaly delete rows after marking them as deleted? -- Thanks, Eugene Prokopiev

2 3

[MonetDB-users] RDF support in MonetDB-11.3.7?
by Medha Atre 31 Jul '11

31 Jul '11

Hello, While compiling MonetDB-11.3.7 from sources, in the configure options, I saw an option "--enable-rdf". I was wondering how this option can be used to load RDF data directly into MonetDB? MonetDB's documentation on the web could not help much. Any pointers will be appreciated. Thanks in anticipation. Medha

3 4

[MonetDB-users] Announcement: New Apr2011-SP2 bugfix release of MonetDB suite
by Sjoerd Mullender 29 Jul '11

29 Jul '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2011-SP2 bug fix release of the MonetDB suite of programs. This is the first release with support for Fedora 15. Fedora 13 is no longer supported. One important bug fix has to do with columns with string (char/varchar) values. There was a bug that could cause exponential growth of the file holding the data for the column. This bug has been fixed. If you have a database that is much larger than you would expect for the amount of data, this might be the cause. The remedy is to dump and restore that database (or just the affected tables). Apart from using up too much space, this bug didn't have any other consequences. More information (including release notes) on this release is available at <http://www.monetdb.org/OldReleaseNotes/Apr2011>. As usual, the download location is <http://dev.monetdb.org/downloads/>. -- Sjoerd Mullender

1 0

[MonetDB-users] Problem with Create Function sintaxis
by elarios＠liacs.nl 20 Jul '11

20 Jul '11

Hi, I'm using MonetDB over Fedora 15 and the driver monetdb-1.20-jdbc.jar I have problems with the sintaxis of the following function: ----------------------- create function hts.Get_idimage() returns int begin declare idimag int; select max(Imag_id) into idimag from hts.Image; returns idimag+1; end; ----------------------- the error message is: ----------------------- Error: syntax error, unexpected $end, expecting WHILE in: "create function hts.get_idimage() returns int begin declare idimag int;" SQLState: null ErrorCode: 0 Error occured in: create function hts.Get_idimage() returns int begin declare idimag int ----------------------- Can you give me some clues of how to fix this sintaxis error? I've checked the SQL 2003 framework and the script looks fine. Thanks in advance for your help. Enrique.

4 4

[MonetDB-users] Table with 1 000 000 000 rows
by Eugene Prokopiev 20 Jul '11

20 Jul '11

Hi, is it possible to store 1 000 000 000 rows in one table or I need to use some partitioning algorithm? -- Thanks, Eugene Prokopiev

3 4

[MonetDB-users] Normalization
by Eugene Prokopiev 20 Jul '11

20 Jul '11

Hi, need I use normalization for tables in colum-oriented databases or it will only decreses perfomance without reducing space usage and other advantages? -- Thanks, Eugene Prokopiev

2 1

[MonetDB-users] Information about Loader
by Lowterm 19 Jul '11

19 Jul '11

Hi, I need some information about MonetDB Loader. Where can I find it? Is there any documentation about it? Thanks -- View this message in context: http://old.nabble.com/Information-about-Loader-tp32084241p32084241.html Sent from the monetdb-users mailing list archive at Nabble.com.

4 6

[MonetDB-users] Clustering, Replication, MultiThreading, Restore
by joel vennin 10 Jul '11

10 Jul '11

Hi, I've just discover MonetDB. I've found some documentation but i've still some questions. 1) About replication, i'm unable to find a documentation about replication support inside MonetDB, if it is not supported do you plan to support it natively, or is there some special script to launch in order to have database in sync. 2) About clustering, my understanding about clustering is to allow one client to launch a sql query on a database through merovingian (if i understood). The query is analysed by all nodes of the cluster and then it return the answer. However, it seems it is not the case, if I follow the documentation on the website about clustering, it seems that I will be connected through a round robin fashion to one of the node. Once connected my query will be executed only on that node. 3) About multithreading, I mean multi query execution per user, I think MonetDb support the fact that multi user can connect to the database and launch different query on it. But is it possible to configure the number of concurrent connection, to setup the number of thread that handle queries ? 4) If MonetDb crash, is it possible that db files become corrupted ? In this case, is there a script to fix it ? Thank you for your help Joel

3 5

[MonetDB-users] how store tiff image in monet db?
by rose26 08 Jul '11

08 Jul '11

-- View this message in context: http://old.nabble.com/how-store-tiff-image-in-monet-db--tp32014566p32014566… Sent from the monetdb-users mailing list archive at Nabble.com.

4 3

[MonetDB-users] Installation problem in Fedora 15
by elarios＠liacs.nl 06 Jul '11

06 Jul '11

Hi, I'm interested in using MonetDB in my project and I'm a beginner in this topic, so I'm sorry for the silly questions that I can make. The problem is: I'm trying to install MonetDB-SQL Server in Fedora 15, but when I execute the command: yum install MonetDB-SQL-server5 MonetDB-client The output makes reference to a repository that doesn't exist: http://dev.monetdb.org/downloads/Fedora/15/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found : http://dev.monetdb.org/downloads/Fedora/15/x86_64/repodata/repomd.xml Trying other mirror. http://dev.monetdb.org/downloads/Fedora/15/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found : http://dev.monetdb.org/downloads/Fedora/15/x86_64/repodata/repomd.xml Trying other mirror. Error: Cannot retrieve repository metadata (repomd.xml) for repository: monetdb. Please verify its path and try again Should I downgrade my operating system to Fedora 14? Do you have any suggestions? Thanks for your time and help, Best regards, Enrique.

3 5

[MonetDB-users] PhD opening at CWI - Adaptive indexing techniques for modern database systems
by Stratos Idreos 23 Jun '11

23 Jun '11

Subject: Adaptive indexing techniques for modern database systems. The Centrum Wiskunde & Informatica (CWI) has a full time vacancy for a talented PhD candidate in the Database Architectures group (http://www.cwi.nl/en/research-groups/Database-Architectures) The position is in the broad area of adaptive indexing techniques for modern database systems. Adaptive indexing is a major challenge in database research; it tries to completely alleviate the need for indexing and tuning steps leading to database systems that require no physical design set-up and can cope immediately with any workload. This leads to database systems that are instantly usable and thus more applicable to numerous new applications that now consider databases too slow, too complex and too old. This position will focus specifically on adaptive indexing for extremely large databases where traditional indexing represents a major bottleneck. The Database Architectures group is one of the world-wide leading groups in system-oriented database research, e.g., it received the 10-year best paper award at VLDB 2009 for their pioneering research on hardware-aware database technology and the column-store model and the 2011 ACM SIGMOD Jim Gray Dissertation award for the database cracking work. The group has a unique methodology that combines fundamental database research with active participation of all group members in database engineering, centred around the open-source analytical database system MonetDB (http://www.monetdb.org/) An internship at one of the partnering database research labs world-wide is considered part of the PhD track. REQUIREMENTS The PhD candidates must have a master degree in computer science (or a 5 year diploma), (specialisation databases, distributed systems, software engineering) mathematics, or related fields. Preferable qualifications for candidates include proven research talent, system programming skills (C/C++), practical experience with using and implementing database systems, and software development in a team. Candidates are expected to have an excellent command of English, and good academic writing and presentation skills. CENTRUM WISKUNDE & INFORMATICA (CWI) Founded in 1946, the Centrum Wiskunde & Informatica (CWI) is the national research institute for mathematics and computer science in the Netherlands. It is located at the Science Park Amsterdam and is part of the “Netherlands Organisation for Scientific Research” (NWO). The institute is internationally focused and renowned for its high quality research, a substantial fraction of which is carried out in the framework of (inter)national programs. Over 160 researchers conduct pioneering research and share their acquired knowledge with society. More than 30 researchers are employed as professors at universities. The institute has generated twenty-one spin-off companies. CWI maintains excellent relations with industry and the academic world, both in the Netherlands as well as abroad. After their research careers at CWI, an increasing number of young staff members find employment in these sectors or in spin-off companies that are based on research results from CWI. Of course, library and computing facilities are first-rate. CWI's non-scientific services to its personnel include career planning, training and courses, assistance in finding housing, and tailor-made solutions to problems that may occasionally arise. TERMS AND CONDITIONS The terms of employment are in accordance with the Dutch Collective Labour Agreement for Research Institutes ("CAO-onderzoeksinstellingen"). Moreover CWI offers attractive working conditions, including flexible scheduling and help with housing for expat employees. The gross monthly salary, for an employee on a full time basis, is €2,037 during the first year and increases to €2,610 over a four year period. CWI also offers excellent and flexible terms of employment, including an employee pension fund. INFORMATION Additional information can be obtained from: dr. Stratos Idreos, email stratos.idreos(a)cwi.nl, telephone +31(0)20 592 4169. or dr. Stefan Manegold, email stefan.manegold(a)cwi.nl, telephone +31(0)20 592 4212, or prof. dr. Martin Kersten, email martin.kersten(a)cwi.nl, telephone +31(0)20 592 4066. APPLICATION Please send your application before 31 July 2011 to: pd(a)cwi.nl. Applications should include a detailed CV, a motivation letter, a list of your M.SC. courses and grades, a copy of your master thesis, and preferably a list of publications.

1 0

[MonetDB-users] Parallelization
by Michael Kusber 20 Jun '11

20 Jun '11

Hi, I'm playing with parallization of databases and I am a little confused about my result on MonetDB. Scenario: 15 Millions of Entries and i want to find the number of entries where the attributes are in a certain sector (i know, a simple select would do the work but i wanted a simple example) i have java code that get all 15 Mio. Data from the database via JDBC and iterates over them with a loop without multithreading. --> 13 ms then i created a stored function: CREATE FUNCTION isInRangexy(x DECIMAL(10,7), y DECIMAL(10,7)) RETURNS BOOL BEGIN ATOMIC IF x > 50 AND y < 50 THEN RETURN true; ELSE RETURN false; END IF; END Then I call it with: SELECT COUNT(*) FROM location WHERE isInRange(x,y) It works, but it is more than 3 times slower than the first java option. I had thought i would get benefit from the fact that i don't need to transfer all the data to the application and of some parallelization and other optimization from monetdb. Did I made a mistake? Can I use another example to get the anticipated results? thanks, michael

2 1

[MonetDB-users] Stored procedures
by Michael Kusber 19 Jun '11

19 Jun '11

Hi, is it possible to achieve the following using SQL stored procedures à la "CREATE PROCEDURE XYZ"? I want to write a function that iterates over a table and adds 2 on a certain attribute. i am realizing that i could do this by an UPDATE, but I wanted to see, whether it would be executed more parallel inside a stored procedure. Is there a way of formulating this in a procedure? do i need to define a function in MAL? would this result in more parallel execution? thanks, michael

2 1

[MonetDB-users] Data compression in MonetDB
by Ken Cheng 18 Jun '11

18 Jun '11

Hi All, I am going to build a DW based on MonetDB. After check on the internet I found that MonetDB does not do data compression. Same data set in MonetDb is much bigger than MySQL and infoBright. Are there any plan to do data compression in the MonetDB? Yours sincerely, Ken Cheng

2 3

[MonetDB-users] Windowing functions ?
by Fabian Pijcke 16 Jun '11

16 Jun '11

Hello everyone, After visiting the MonetDB website, I didn't saw any information about the windowing functions of SQL (ranking, selecting max according to a defined order, etc.) A (very) short introduction can be found on wikipedia: http://en.wikipedia.org/wiki/Select_%28SQL%29#Window_function I know it is implemented into PostGreSQL, Oracle and Microsoft SQL Server. Is there any chance to see this function in MonetDB in the future ? Thanks in advance, sorry for my poor english ... :( Fabian Pijcke

2 2

[MonetDB-users] Create Database - Load Data
by Lowterm 15 Jun '11

15 Jun '11

Hi, how can I create a new Database under Windows and how can I load data in a table form a external file? Thanks -- View this message in context: http://old.nabble.com/Create-Database---Load-Data-tp31653813p31653813.html Sent from the monetdb-users mailing list archive at Nabble.com.

8 24

[MonetDB-users] PhD position at CWI Database Architecture group
by Stefan Manegold 14 Jun '11

14 Jun '11

CWI is an internationally renowned research institute in mathematics and computer science, located in Amsterdam, The Netherlands. The focus is on fundamental research problems, inspired by societal needs. Research is carried out in 18 research groups and a substantial fraction is carried out in the framework of (inter)national programs. VACANCY CWI has a vacancy for a talented PhD candidate in the DB Architectures group (http://www.cwi.nl/en/research-groups/Database-Architectures) DISTRIBUTED DATABASE ARCHITECTURES FOR SCIENCE APPLICATIONS The Database Architectures group is one of the world-wide leading groups in system-oriented database research, e.g., it received the 10-year best paper award at VLDB 2009 for their pioneering research on hardware-aware database technology and the column-store model. The group has a unique methodology that combines fundamental database research with active participation of all group members in database engineering, centered around the open-source analytical database system MonetDB (http://www.monetdb.org/) You will be expected to write both papers and contribute to this code base. The focus of the group is on hard core data management and for this position specifically on database architectures to support large scale scientific applications in the context of the SciLens project (http://www.scilens.org) The goal is to leverage the potential power of a 330 node dedicated database machine to improve science in, e.g., astronomy and seismology. (http://www.scilens.org/content/platform) CWI maintains excellent relations with industry and the academic world, both in the Netherlands as well as abroad. An internship at one of the partnering database research labs world-wide is considered part of the PhD track. After their research careers at CWI, many staff members find employment in these sectors or in spin-off companies based on research results from CWI. Of course, library and computing facilities are first-class. CWI's non-scientific services to its personnel include career planning, training and courses, assistance in finding housing, and tailor-made solutions to problems that may occasionally arise. REQUIREMENTS The PhD candidates must have a master degree in computer science, (specialization distributed systems, database, software engineering) mathematics, or related fields. Preferable qualifications for candidates include proven research talent, system programming skills (C/C++), practical experience with database systems, and software development in a team. One should bring good academic writing and presentation skills. Candidates are expected to have an excellent command of English. TERMS AND CONDITIONS The terms of employment are in accordance with the Dutch "CAO-onderzoeksinstellingen". Moreover, CWI offers attractive working conditions, including flexibility and help with housing for foreigners. The gross monthly salary (on a full time basis) will be â¬ 2,037 during the first year and will reach â¬ 2,610 during the fourth year. INFORMATION Additional information can be obtained from prof.dr. Martin Kersten, email martin.kersten(a)cwi.nl, telephone +31(0)20 592 4066, or dr. Stefan Manegold, email stefan.manegold(a)cwi.nl, telephone +31(0)20 592 4212. -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

1 0

[MonetDB-users] Select Query crashes the server with "--single-user" option
by Ramak 13 Jun '11

13 Jun '11

Hi, I am trying to use MonetDB in single user mode. While I could load the data using "COPY INTO" command and run a few select queries successfully, the moment i run a query with group by on more than one column, the server crashes saying " ---- >Segmentation fault". Is there a way to get around it? or, am I using it the wrong way? I could recreate the problem doing this--- 1. Start server with "sudo mserver5 --dbfarm='/home/user/work/tmp/data/test' --single-user" 2. start mclient (and create a simple table and fire a select query with group by on multiple columns) >create table test ( market_size int not null, market int not null, state int not null); >select count(*) from test group by market_size, state; -- server crashes on the above query. I am using MonetDB 5 server v11.3.3 "Apr2011-SP1" (64-bit, 64-bit oids) Thanks, Ram -- View this message in context: http://old.nabble.com/Select-Query-crashes-the-server-with-%22--single-user… Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

[MonetDB-users] Production Database Crash - Analysis / Recovery?
by Philippe Hanrigou 12 Jun '11

12 Jun '11

My database is crashing quite systematically in production during some data import (which is in the form as a serie of DELETE ... WHERE / COPY INTO). The relevant information from the merovigian.log seems to be: 2011-06-02 04:53:27 MSG prod_reporting[25449]: !SQLException:SQLinit:Catalogue initialization failed 2011-06-02 04:53:27 MSG prod_reporting[25449]: !ERROR: HEAPextend: failed to extend to 3316460814336 for 11/40/114026theap A) What does this error mean? ----------------------------------------- A lack of memory? I am quite confused as the entire database is barely 5G on disk and there is 7G of RAM on this machine, which is dedicated solely to MonetDB. Moreover I only DELETE/COPY INTO in a single table at a time and the biggest table is barely 1.6G. Cleary there is some memory dynamic that I am not understanding. B) How do I recover the crashed database? ----------------------------------------------------------- It would not start anymore: 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/monetdb5/dbfarm/prod_reporting/.mapi.sock 2011-06-02 04:55:19 MSG prod_reporting[1665]: # MonetDB/SQL module loaded 2011-06-02 04:55:19 MSG merovingian[1644]: database 'prod_reporting' (1665) was killed by signal SIGSEGV 2011-06-02 04:55:29 ERR control[1644]: (local): failed to fork mserver: database 'prod_reporting' has crashed after starting, manual intervention needed, check merovingian's logfile for details Thanks in advance, - Philippe -- more complete extract from merovigian.log --- 2011-06-02 04:40:38 MSG merovingian[639]: proxying client ip-10-204-61-105.ec2.internal:52864 for database 'prod_reporting' to mapi:monetdb:///var/monetdb5/dbfarm/prod_reporting/.mapi.sock?database=prod_reporting 2011-06-02 04:40:38 MSG merovingian[639]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2011-06-02 04:53:09 MSG control[639]: (local): served status list 2011-06-02 04:53:23 MSG merovingian[639]: caught SIGTERM, starting shutdown sequence 2011-06-02 04:53:24 MSG control[639]: control channel closed 2011-06-02 04:53:27 MSG merovingian[639]: sending process 25449 (database 'prod_reporting') the TERM signal 2011-06-02 04:53:27 MSG prod_reporting[25449]: !SQLException:SQLinit:Catalogue initialization failed 2011-06-02 04:53:27 MSG prod_reporting[25449]: !ERROR: HEAPextend: failed to extend to 3316460814336 for 11/40/114026theap 2011-06-02 04:53:27 MSG merovingian[639]: database 'prod_reporting' (25449) has exited with exit status 0 2011-06-02 04:53:27 MSG merovingian[639]: database 'prod_reporting' has shut down 2011-06-02 04:53:27 MSG merovingian[639]: Merovingian 1.4 stopped 2011-06-02 04:55:16 MSG merovingian[1644]: Merovingian 1.4 (Apr2011-SP1) starting 2011-06-02 04:55:16 MSG merovingian[1644]: monitoring dbfarm /var/monetdb5/dbfarm 2011-06-02 04:55:16 MSG merovingian[1644]: accepting connections on TCP socket 0.0.0.0:50000 2011-06-02 04:55:16 MSG merovingian[1644]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000 2011-06-02 04:55:16 MSG discovery[1644]: listening for UDP messages on 0.0.0.0:50000 2011-06-02 04:55:16 MSG control[1644]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50001 2011-06-02 04:55:16 MSG control[1644]: accepting connections on TCP socket 0.0.0.0:50001 2011-06-02 04:55:16 MSG discovery[1644]: new neighbour ip-10-32-111-2 (ip-10-32-111-2.ec2.internal) 2011-06-02 04:55:16 MSG discovery[1644]: new database mapi:monetdb://ip-10-32-111-2:50000/prod_reporting (ttl=660s) 2011-06-02 04:55:16 MSG discovery[1644]: registered neighbour ip-10-32-111-2:50001 2011-06-02 04:55:19 MSG control[1644]: (local): served status list 2011-06-02 04:55:19 MSG merovingian[1644]: starting database 'prod_reporting', up min/avg/max: 1m/31m/1h, crash average: 0.00 0.10 0.03 (6-5=1) 2011-06-02 04:55:19 MSG prod_reporting[1665]: arguments: /usr/bin/mserver5 --set gdk_dbfarm=/var/monetdb5/dbfarm --dbname=prod_reporting --set merovingian_uri=mapi:monetdb://ip-10-32-111-2:50000/prod_reporting --set mapi_open=false --set mapi_port=0 --set mapi_usock=/var/monetdb5/dbfarm/prod_reporting/.mapi.sock --set monet_vault_key=/var/monetdb5/dbfarm/prod_reporting/.vaultkey --set monet_daemon=yes 2011-06-02 04:55:19 MSG prod_reporting[1665]: # MonetDB 5 server v11.3.3 "Apr2011-SP1" 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Serving database 'prod_reporting', using 2 threads 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Found 7.294 GiB available main-memory. 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Copyright (c) 1993-July 2008 CWI. 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved 2011-06-02 04:55:19 MSG prod_reporting[1665]: # Listening for UNIX domain connection requests on mapi:monetdb:///var/monetdb5/dbfarm/prod_reporting/.mapi.sock 2011-06-02 04:55:19 MSG prod_reporting[1665]: # MonetDB/SQL module loaded 2011-06-02 04:55:19 MSG merovingian[1644]: database 'prod_reporting' (1665) was killed by signal SIGSEGV 2011-06-02 04:55:29 ERR control[1644]: (local): failed to fork mserver: database 'prod_reporting' has crashed after starting, manual intervention needed, check merovingian's logfile for details 2011-06-02 05:03:18 MSG merovingian[1644]: database 'prod_reporting' has crashed after start on 2011-06-02 04:55:19, attempting restart, up min/avg/max: 1m/31m/1h, crash average: 1.00 0.20 0.07 (7-5=2)

4 8

[MonetDB-users] monetdb checkpoint
by Philippe Hanrigou 11 Jun '11

11 Jun '11

Hi, Online documentation at http://www.monetdb.org/Documentation/Cookbooks/ServerMgmt/ResourceLimits mentions: > The database checkpoint can be made using 'monetdb checkpoint' Yet checkpoint does not seem to be a valid monetdb command. Is it a typo, an historical vestige? Cheers, - Philippe

2 2

[MonetDB-users] Octopus usage in MonetDB
by Andrei Cojocaru 07 Jun '11

07 Jun '11

Hi all, I am trying to setup (and play around with) a MonetDB-Apr11 cluster since the dataset I am expecting to have is beyond what can fit in the memory one node (starting with at least a few hundred gigabytes). I have been following the instructions athttp://www.monetdb.org/Documentation/Cookbooks/Recipes/Cluster and have a few nodes setup. I ran all the commands given there on all the nodes except for the creation of the master database which I ran on only the master. Also, I set the pipe to octopus_pipe on the master database. In mclient, I ran the following commands (from the sample squid test for octopus in the MonetDB source code): create table squidA( Bid int, Cval int); create table squidD( Eid int, Fval int); -- let's fill the tables create procedure fill(lim int) begin declare i int; set i = 0; while i < lim do insert into squidA values(i,rand()); insert into squidD values(i,rand()); set i = i+1; end while; end; declare size int; set size=100; call fill(size); It all runs with no errors. However, when I run: select Bid from squidA, I get a screenful of errors. They are all a variation of: TypeException:octopus.tentacle_3_11[6]:'octopus.bind' undefined in: _18:bat[:oid,:int] := octopus.bind(_2:str, _8:str, _9:str, _13:str, _19:int, _16:int, _17:int, version:int) Would you be able to offer some guidance on how to get this running? I was considering setting up application level partitioning and run multiple MonetDB instances directly, but if octopus does a lot of that work I would prefer to save myself the development and debugging time. Thanks for any help you could provide. -Andrei

3 2

[MonetDB-users] Problem with Create Tables
by Lowterm 03 Jun '11

03 Jun '11

Hi, what's the meaning of this syntax error? Syntax error, unexpected CREATE, expecting SCOLON in: "create ..." I get this message with the "create table" if I load a Files with "sql>\< filename", which contains this statement. The create-statement works, if I insert it directly. The second error message ist: syntax error, unexpected ON, expecting ')' or ',' in: "create ..." in the following create statement: CREATE TABLE dim_warehouse ( is_current smallint NOT NULL, last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) but I can not see wath is wrong. Thanks -- View this message in context: http://old.nabble.com/Problem-with-Create-Tables-tp31757411p31757411.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 6

[MonetDB-users] Aggregating by a computed field?
by Synaptic Axon 01 Jun '11

01 Jun '11

Hello, I recently install MonetDB and have been trying to get a feel for its speed and storage. I ran into an issue with SELECT queries trying to GROUP BY a computed field. For example, imagine a table like this: CREATE TABLE foo ( ts TIMESTAMP NOT NULL, category VARCHAR(32) NOT NULL ); I want to count the number of categories by year, so I want to craft a query like this: sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY 1, 2 ORDER BY 3 DESC; syntax error, unexpected sqlINT in: "SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY 1, 2 ORDER BY 3 DESC" Unfortunately, MonetDB does not appear to support positional fields in the GROUP BY clause. So I tried rewriting it like this: sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY EXTRACT( year from ts ), category ORDER BY 3 DESC; syntax error, unexpected EXTRACT in: "SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY EXTRACT( year from ts ), category ORDER BY 3 DESC" Ok, how about using "year" instead of the extract( .. ) bit? sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY year, category ORDER BY 3 DESC; syntax error, unexpected YEAR in: "SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY year, category ORDER BY 3 DESC" What is the correct way to group by the computed field? Should I create a view? Is there a positional syntax for the GROUP BY clause I am overlooking? $ monetdb --version MonetDB Database Server Toolkit v0.8 (Apr2011) $ monetdbd --version MonetDB Database Server v1.4 (Apr2011) $ mclient --version mclient, the MonetDB interactive terminal (Apr2011) support for command-line editing compiled-in Thank you.

3 5

[MonetDB-users] Announcement: New Apr2011-SP1 bugfix release
by Fabian Groffen 31 May '11

31 May '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2011-SP1 bugfix release of the MonetDB suite of programs. At the time of this release, we also officially changed to our new website at http://www.monetdb.org/. This new website is our central source of information, and comes with our new logo and style. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>.

1 0

[MonetDB-users] Syntax for column tables
by Michael K 25 May '11

25 May '11

Hi, is there a "CREATE COLUMN TABLE xyz" like statement in MonetDB or will every "CREATE TABLE" create a column table? Can I create "normal" row store tables in MonetDB at all? I have read http://monetdb.cwi.nl/SQL/Documentation/Table-Definition.htmlbut did not found the information I was looking for. Kind regards. michael

3 2

[MonetDB-users] mclient credentials after install?
by Philippe Hanrigou 22 May '11

22 May '11

Hi, I am trying to get started with MonetDB in order to assess its response time for an in-house dataset. I managed to configure/make/install it on my MacBook Pro (I have joined the custom Portfile I used for this purpose at the end of this email in case it matters). Nevertheless, when I go through the "Getting started" instructions, mclient is asking for credentials that I never had a chance to explicitly set: ph7:~$ monetdbd start ph7:~$ monetdbd get all property value hostname Solaris.local dbfarm /opt/local/var/monetdb5/dbfarm mserver /opt/local/bin/mserver5 logfile merovingian.log pidfile merovingian.pid sockdir /tmp port 50000 controlport 50001 discoveryport 50000 exittimeout 60 forward proxy discoveryttl 600 passphrase <unknown> mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50001 ph7:~$ monetdb create demo created database in maintenance mode: demo ph7:~$ monetdb status name state uptime health last crash demo locked ph7:~$ monetdb release demo taken database out of maintenance mode: demo ph7:~$ monetdb status name state uptime health last crash demo stopped ph7:~$ mclient -d demo user(ph7): password: InvalidCredentialsException:checkCredentials:invalid credentials for user 'ph7' Which credentials am supposed to use to launch mclient? I am willing to try any workaround as, at this point, I am not concerned with security at all. Thanks in advance, - Philippe --------------------------------------------------------- # ph7's custom Portfile # $Id PortSystem 1.0 name MonetDB version 11.3.1 categories databases platforms darwin maintainers nomaintainer description MonetDB is an open-source database system for high-performance applications in data mining, OLAP, GIS, XML Query, text and multimedia retrieval. MonetDB often achieves a significant speed improvement for SQL and XQuery over other open-source systems. homepage http://www.monetdb.org master_sites http://dev.monetdb.org/downloads/sources/Latest/ distfiles MonetDB-${version}.tar.bz2 use_bzip2 yes checksums md5 f48869c87a56604d1c06f7d8243554f9 \ sha1 03a29a5e00a90d5294443ffb849167a2c108302e \ rmd160 c9bcdff333a60aad42dc8504c458335494e749f1 configure.args --enable-debug=no --enable-assert=no --enable-optimize=yes

3 3

[MonetDB-users] using MonetDB in embedded mode - mapi interface
by Ramak 21 May '11

21 May '11

Hi, I am trying to use monetDB in embedded mode. The sample at "http://monetdb.cwi.nl/SQL/Documentation/A-Simple-Example.html#A-Simple-Exam…" suggests that we can interface to the data base using "embedded_sql()" method in combination with "embeddedclient.h". I dont seem to find this method in the library "libmapi.so.3"; neither the include file in the code base. Is "embedded mode" still supported? is there an alternate way of connecting to embedded monetDB server? PS: I am using monedDB5 - April 2011 release. Thanks, Ram -- View this message in context: http://old.nabble.com/using-MonetDB-in-embedded-mode---mapi-interface-tp316… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Does a primary key based on a single column + numeric improve performance in MonetDB?
by Philippe Hanrigou 20 May '11

20 May '11

Hi, I am designing the schema of our brand new MonetDB database, and I am not sure whether my previous data modeling instincts are still relevant ;-). In particular, I am wondering: A) Does the definition of primary keys / foreign keys improve MonetDB performance? B) I need to do joins with a table whose natural key which is the combination of (bigint, varchar(51)). Is it best to: 1) just add these 2 columns to the dependent tables, and join on these 2 columns. 2) create a single varchar column that would be the concatenation of the bigint + varchar(51) and join on this single varchar column 3) Introduce a numeric+sequential surrogate key (presumably generated from a sequence), and join on the surrogate key. I am especially interested in the performance / acidental complexity tradeoffs in this situation. Thanks in advance, - Philippe

1 0

[MonetDB-users] MonetDB support for Upserts / On duplicate key ...
by Philippe Hanrigou 20 May '11

20 May '11

Hi, I am trying to operationalize our MonetDB usage and need some advice. What is the best way to do upserts in MonetDB? (https://secure.wikimedia.org/wikipedia/en/wiki/Upsert) Our application stores its data in a reference database (not MonetDB) and need to export some of its (derived) data in MonetDB for our real-time analytics needs. This "population" of MonetDB can happen in a couple of ways: A) In "real-time", on a "one row at a time" basis, when the application detects that it would be valuable to update the analytics information. B) Nightly in a batch oriented basis, to make sure that no information was lost due to a glitch/downtime during the day. C) We also periodically "refresh" all historical data in a batch oriented mode when we find bugs in the way we are deriving the data or what to derive new analytics from the same reference data. And whatever triggered the update, the rule is that the last update "wins" (and contains the full data). I am explaining this to illustrate why upserts would be very convenient. They would allow me to use the same code in all situations (A, B, C) and in "row" mode as well as in "batch" mode. If, as I suspect there is no out-of-the-box support for upserts, what is the best way to achieve them efficiently in batch mode. I am open to any (performant) solution, it does not have to be SQL based. Thanks in advance, - Philippe PS: Thanks a lot for MonetDB, I am completely amazed by its performance for our analytics queries.

1 0

Re: [MonetDB-users] MonetDB-11.3.1 on OpenBSD 4.9 i386 fails during make
by Michael Sioutis 20 May '11

20 May '11

It seems that in OpenBSD sys/sysctl.h requires sys/param.h (http://lists.gnu.org/archive/html/bug-autoconf/2005-08/msg00015.html) I've found this workaround, but I won't test it until tomorrow probably: http://lists.gnu.org/archive/html/bug-autoconf/2005-08/msg00015.html I also don't know exactly how to apply the above workaround (or any other) in the MonetDB Makefile(s ?). On Wed, May 4, 2011 at 6:27 PM, Michael Sioutis <sioutis(a)di.uoa.gr> wrote: > It seems that in OpenBSD sys/sysctl.h requires sys/param.h > (http://lists.gnu.org/archive/html/bug-autoconf/2005-08/msg00015.html) > I've found this workaround, but I won't test it until tomorrow > probably: http://lists.gnu.org/archive/html/bug-autoconf/2005-08/msg00015.html > > I also don't know exactly how to apply the above workaround (or any > other) in the MonetDB Makefile(s ?). > > On Wed, May 4, 2011 at 6:20 PM, Michael Sioutis <papito.dit(a)gmail.com> wrote: >> I can't find any of HAVE_SYS_SYSCTL_H and KERN_PROC_PATHNAME defined >> in my config.log. >> Also, I have a warning about sysctl.h, which is in the >> configuration.log I posted earlier (my first post). >> >> The important part is this: >> configure: WARNING: sys/sysctl.h: present but cannot be compiled >> configure: WARNING: sys/sysctl.h: check for missing prerequisite headers? >> configure: WARNING: sys/sysctl.h: see the Autoconf documentation >> configure: WARNING: sys/sysctl.h: section "Present But Cannot Be Compiled" >> configure: WARNING: sys/sysctl.h: proceeding with the compiler's result >> configure: WARNING: ## ------------------------------- ## >> configure: WARNING: ## Report this to info(a)monetdb.org ## >> configure: WARNING: ## ------------------------------- ## >> >> On Wed, May 4, 2011 at 6:06 PM, Fabian Groffen <Fabian.Groffen(a)cwi.nl> wrote: >>> Thanks for the summarisation! >>> >>> On 04-05-2011 17:56:54 +0300, Michael Sioutis wrote: >>>> To sum it up.. >>>> >>>> add >>>> #include <pthread.h> >>>> #include <pthread_np.h> >>> >>> hmm, do you really need pthread_np.h? That seems like a non-standard >>> header to me. >>> >>>> and... >>>> 2011-05-04 17:44:29 MSG mydb[19795]: !MALException:malInclude:could >>>> not open file: mal_init >>> >>> This indicates some bigger problem... >>> >>>> 2011-05-04 17:44:29 MSG mydb[19795]: #warning: unable to determine binary location, please use --set monet_mod_path=/path/to/... to allow finding modules >>> >>> ... and this causes it. >>> >>> Can you tell me if HAVE_SYS_SYSCTL_H and KERN_PROC_PATHNAME are defined >>> for you? >>> common/utils/mutils.c is apparently unable to find the executable path >>> on OpenBSD. >>> Perhaps KERN_PROC_PATHNAME is not in sys/sysctl.h on OpenBSD? >>> >>> ------------------------------------------------------------------------------ >>> WhatsUp Gold - Download Free Network Management Software >>> The most intuitive, comprehensive, and cost-effective network >>> management toolset available today. Delivers lowest initial >>> acquisition cost and overall TCO of any competing solution. >>> http://p.sf.net/sfu/whatsupgold-sd >>> _______________________________________________ >>> MonetDB-users mailing list >>> MonetDB-users(a)lists.sourceforge.net >>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >>> >> >

5 45

[MonetDB-users] Where [on file] does MonetDB persists the dB?
by Me Anonimous 19 May '11

19 May '11

I uploaded a 1M records, 67M file but can't find anything in the directory indicating dB storage... Thanks!

2 1

[MonetDB-users] feature request (srid support )
by George Garmpis 19 May '11

19 May '11

Hi, I try to use geometries in different reference systems but i don't get the expected results. I create a table with geometries and insert 2 points with different SRID. sql>create table geom(id int, g geometry); operation successful sql>insert into geom values(1, GeomFromText('POINT(100 100)', 4326)); operation successful sql>insert into geom values(2, GeomFromText('POINT(100 100)', 27700)); operation successful When i ask for these SRID i get the same (0) for every point. sql>select id, srid(g) from geom; +------+--------+ | id | srid_g | +======+=+ | 2 | 0 | | 1 | 0 | +------+--------+ Also, if i ask whether these points overlap i don't get the right result. sql>select equals(g1.g, g2.g) from geom g1, geom g2 where g1.id=1 and g2.id =2; +-----------------+ | overlaps_g | +========+ | true | +-----------------+ 1 tuple (4.119ms) I have noticed that function wkbFromText (in file geom/monetdb5/geom.mx), which is used in SQL functions GeomFromText, PointFromText e.t.c., returns a wkb datatype (defined in geom/lib/libgeom.mx) which has not any field for srid. Is srid stored somewhere else (e.g. as metadata of a geometry collumn)? Generally, does monetdb supports different reference systems? If no, will this feature be added ? -- George Garbis

1 0

[MonetDB-users] 'mkey.bulk_rotate_xor_hash' exception
by Steve Elder 18 May '11

18 May '11

With a very simple test, I get the 'mkey.bulk_rotate_xor_hash' exception. After this error, the only way I have found to recover is to drop the table, re-create and repopulate it. * System parameters: o MonetDB-11.3.1 o x86-64 ( note: build was missing a shared library that was subsequently ) o Solaris 10. o 96 GB memory * I create a simple table. create table fluid ( fluid bigint not null, userID int not null default 0, relativeUser bigint not null, typeID tinyint default 127, updateTime timestamp(0) default now(), constraint fluid_pk primary key( fluid, userID ), constraint user_fk1 foreign key ( userID ) references fuser ( userID ) ); create index rel_user_idx1 on fluid ( relativeUser ); create index userID_idx2 on fluid ( userID ); create index rel_user_user_idx1 on fluid ( relativeUser, userID ); create index update_idx1 on fluid ( updateTime ); * Populate it with records ( Note: the number of records does not seem to matter ). * Delete records. It does not appear to matter how many records I delete. Deleting 1 record or deleting all records seems to have the same effect. * Drop the primary key * Do "set optimizer='nov2009_pipe';" * Add the primary key. I always get the mkey.bulk_rotate_xor_hash exception. Here is a snapshot including a trace of a successful 'alter table drop constraint ...' command after NO records have been deleted. When I delete 1 ( or more records ), I get the exception with no trace output. sql>alter table fluid drop constraint fluid_pk; operation successful sql>set optimizer='nov2009_pipe'; sql>trace alter table fluid add constraint fluid_pk primary key( fluid, userID ); operation successful +------+--------------------------------------------------------------------------------------------------------------+ | tick | stmt | : s : : +======+==============================================================================================================+ | 7 | _2 := sql.mvc(); | | 451 | sql.catalog(29,"FLDB",42302696,0); | | 32 | _16:bat[:oid,:lng] <tmp_4741501>[52] := sql.bind(_2=48362568,"FLDB","fluid","fluid",0); | | 22 | _13:bat[:oid,:int] <tmp_4741500>[52] := sql.bind(_2=48362568,"FLDB","fluid","userID",0); | | 15 | _10:bat[:oid,:oid] <tmp_4741364>[0] := sql.bind_dbat(_2=48362568,"FLDB","fluid",1); | | 10 | _12<tmpr_4741364>[0] := bat.reverse(_10=nil:bat[:oid,:oid]); | | 20 | _15<tmp_4741350>[52] := algebra.kdifference(_13=nil:bat[:oid,:int],_12=<tmpr_4741364>[0]); | | 22 | _17<tmp_4741012>[52] := algebra.kdifference(_16=nil:bat[:oid,:lng],_12=nil); | | 11 | _19:bat[:oid,:wrd] <tmp_4741265>[52] := batcalc.hash(_17=<tmp_4741012>[52]); | | 29 | _22<tmp_4741636>[52] := bat.mirror(_17=<tmp_4741012>[52]); | | 16 | _21:bat[:oid,:wrd] <tmp_4741417>[52] := mkey.bulk_rotate_xor_hash(_19=nil:bat[:oid,:wrd],22,_15=<tmp_4741350 | : : >[52]); : | 8 | _23<tmpr_4741636>[52] := bat.reverse(_22=nil); | | 52 | _24<tmp_4741265>[52] := algebra.join(_23=nil,_21=nil:bat[:oid,:wrd]); | | 15 | (ext60<tmp_4741417>[52],grp58<tmp_4741636>[52]) := group.new(_24=<tmp_4741265>[52]); | | 19 | (ext63<tmp_4741417>[52],grp61<tmp_4741636>[52]) := group.derive(ext60=nil,grp58=nil,_17=nil); | | 10 | (ext66<tmp_4741417>[52],grp64<tmp_4741636>[52]) := group.done(ext63=nil,grp61=nil,_15=nil); | | 9 | _31<tmp_4740027>[52] := bat.mirror(ext66=nil); | | 10 | _32:bat[:oid,:wrd] <tmp_4741417>[52] := aggr.count(grp64=nil,grp64=nil,_31=nil); | | 17 | return sum := aggr.sum(b=<tmp_4741417>[52],true); | | 44 | _33:wrd := aggr.sum(_32=<tmp_4741417>:bat[:oid,:wrd][52]); | | 5 | _34 := calc.isnil(_33=52:wrd); | | 7 | _37 := aggr.count(_32=nil:bat[:oid,:wrd]); | | 6 | _36 := calc.ifthenelse(_34=false,0:wrd,_33=52:wrd); | | 5 | _38 := calc.!=(_37=52:wrd,_36=52:wrd); | | 2827 | barrier _53 := language.dataflow(); | | 6 | sql.assert(_38=false,"UPDATE: PRIMARY KEY constraint 'fluid.fluid_pk' violated"); | | 27 | sql.update(_2=48362568,"FLDB","fluid","fluid_pk",_24=nil); | | 5 | sql.exportOperation(""); | | 3724 | user.s1_1(); | +------+--------------------------------------------------------------------------------------------------------------+ 29 tuples (50.262ms) sql>set optimizer='default_pipe'; sql>alter table fluid drop constraint fluid_pk; operation successful sql>delete from fluid where relativeUser = 11111; 1 affected row (1.557ms) sql>trace alter table fluid add constraint fluid_pk primary key( fluid, userID ); MALException:mkey.bulk_rotate_xor_hash:operation failed Operands not synced on head I need to periodically do bulk loads ( 'copy into' ), but also need to be able to periodically delete records from the same table. Is there a resolution to this problem? Thanks Steve

2 4

[MonetDB-users] mclient credentials after install?
by Philippe Hanrigou 17 May '11

17 May '11

Hi, I am trying to get started with MonetDB in order to assess its response time for an in-house dataset. I managed to configure/make/install it on my MacBook Pro (I have joined the custom Portfile I used for this purpose at the end of this email in case it matters). Nevertheless, when I go through the "Getting started" instructions, mclient is asking for credentials that I never had a chance to explicitly set: ph7:~$ monetdbd start ph7:~$ monetdbd get all property value hostname Solaris.local dbfarm /opt/local/var/monetdb5/dbfarm mserver /opt/local/bin/mserver5 logfile merovingian.log pidfile merovingian.pid sockdir /tmp port 50000 controlport 50001 discoveryport 50000 exittimeout 60 forward proxy discoveryttl 600 passphrase <unknown> mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50001 ph7:~$ monetdb create demo created database in maintenance mode: demo ph7:~$ monetdb status name state uptime health last crash demo locked ph7:~$ monetdb release demo taken database out of maintenance mode: demo ph7:~$ monetdb status name state uptime health last crash demo stopped ph7:~$ mclient -d demo user(ph7): password: InvalidCredentialsException:checkCredentials:invalid credentials for user 'ph7' Which credentials I am supposed to use to launch mclient? I am willing to try any workaround as, at this point, I am not concerned with security at all. Thanks in advance, - Philippe --------------------------------------------------------- # ph7's custom Portfile # $Id PortSystem 1.0 name MonetDB version 11.3.1 categories databases platforms darwin maintainers nomaintainer description MonetDB is an open-source database system for high-performance applications in data mining, OLAP, GIS, XML Query, text and multimedia retrieval. MonetDB often achieves a significant speed improvement for SQL and XQuery over other open-source systems. homepage http://www.monetdb.org master_sites http://dev.monetdb.org/downloads/sources/Latest/ distfiles MonetDB-${version}.tar.bz2 use_bzip2 yes checksums md5 f48869c87a56604d1c06f7d8243554f9 \ sha1 03a29a5e00a90d5294443ffb849167a2c108302e \ rmd160 c9bcdff333a60aad42dc8504c458335494e749f1 configure.args --enable-debug=no --enable-assert=no --enable-optimize=yes

1 0

[MonetDB-users] Problem with optimizer ?
by Georgios Garmpis 15 May '11

15 May '11

Hi, Please find attached a query (and corresponding schema) that results in mserver5 using more than 1GB of memory and terminating after 6 mins with this output: MAPI = (monetdb) /tmp/.s.monetdb.50000 ACTION= read_line QUERY = PLAN SELECT DISTINCT h3.obj, CASE WHEN u_ENDPOINT.value IS NOT NULL THEN u_ENDPOINT.value WHEN l_ENDPOINT.value IS NOT NULL THEN l_ENDPOINT.value WHEN ll_ENDPOINT.value IS NOT NULL THEN ll_ENDPOINT.value WHEN lu_ENDPOINT.value IS NOT NULL THEN lu_ENDPOINT.value END, NULL FROM triples t0 INNER JOIN triples h1 ON (h1.pred = 21 AND h1.obj = 42 AND h1.subj = t0.subj) INNER JOIN triples h2 ON (h2.pred = 5 AND h2.subj = t0.subj) INNER JOIN triples h3 ON (h3.pred = 20 AND h3.subj = t0.s ERROR = !Connection terminated Is it a problem of optimizer? Any guidelines to deal with it will be appreciated. I also attached merovigian.log. -- George Garbis

4 10

[MonetDB-users] Repeated mmap/munmap calls
by Steve Elder 13 May '11

13 May '11

We are running the April '11 version of MonetDB on multi-core x86-64 hardware running Solaris 10 OS. We are running on a machine with 96GB of memory. The ulimit for memory shows 'unlimited'. We have an application that we believe should be appropriate for MonetDB fast response-time characteristics. We have a very simple schema that includes the following table: create table tuid ( TUID bigint not null, clientID int not null default 0, mafID bigint not null, addrMatch tinyint default 127, lastUsed timestamp(0) default now(), constraint tuid_pk primary key( TUID, clientID ), constraint client_fk1 foreign key ( clientID ) references client ( clientID ) ); The above table currently contains 13.65 million records ( mostly bulk loaded via 'copy into' ). The primary key was dropped and re-added via appropriate 'alter table' statements. There are other 5 other tables in the schema - all with less than 100 records. All 5 tables have fewer than 5 columns. The total size of the database directory in the dbfarm directory is 1.3 GB. Ultimately, the application could have between 500 million and 1 billion records in the above table. Except for one of the other tables, the number of records will be small ( less than 1000 ). We are running a Java application that uses the JDBC interface to query and update the table. Files of 1-3 million records are processed by the application in a given run. Less than 5% of the records require an update to above table ( no other tables are currently updated or inserted to ). When the application is processing these records, we run 'prstat' against 'mserver5' and see via the 'prstat' command the processing load appears to be distributed fairly evenly across many threads. However, occasionally at some point, one thread appears to be using most of the CPU. During these intervals, 'truss' shows 'mserver5' making repeated calls to mmap and munmap. (The calls to mmap have the same value for the mmap size parameter.) These intervals appear to last for many minutes. During these intervals, query response times slow to a crawl. As a result, the overall throughput of the application is disappointing. Given that we are actually accessing a fairly small number of columns all of which should be in memory ( we believe ), it would appear that MonetDB is not functioning properly. It is our belief that MonetDB should be a good fit for this particular application. I should note that we have 3 different MonetDB databases running on the machine ( thus requiring 3 instances of mserver5 ). However, two of the databases are idle when we run the above application. Given the amount of memory available the size of the database, the number of columns being queries ( and/or updated ), and the amount of memory on the machine, the behavior we are observing does not appear to be consistent with descriptions of MonetDB's expected behavior. We have 3 questions: * Is what we are observing concerning the frequency of mmap and munmap calls typical where the mmap size parameter has the same value? * Why would we be mapping columns into memory so frequently when we are not adding records? * Is it typical to have periods where one thread appears to be handling the entire load? * What should we look for concerning the tuning of the x86-64 platform or MonetDB to address these issues? Thanks Steve

1 0

[MonetDB-users] simple load?
by Zoltan Tomory 11 May '11

11 May '11

Sorry to be a bother, but I wonder how hard it is to load a schema or table from ascii text or an .mdb "access" file. I am using the convenient simple console client that is included. Upon searching the list archives, I found some leads which lead me to panic and reflexively consider using Java's Robot class to perform row by row insert operations from the text file. I'm hopeful that there is another way which is both elegant and understandable to a newbie. Thanks in advance. I have loved using MonetDB to organize my information Zoltan -- View this message in context: http://old.nabble.com/simple-load--tp31587056p31587056.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 2

[MonetDB-users] Alter Table
by Henning Grosch 11 May '11

11 May '11

Is it somehow possible to to add a column with alter table behind an specific column? Example: ALTER TABLE xxx ADD COLUMN newCol int AFTER oldCol; Henning

2 1

[MonetDB-users] wrong results for group by queries
by Daniel Boesswetter 05 May '11

05 May '11

Hi, we are using MonetDB (Oct-2010) for analyzing a 250G data warehouse and have found at least one "thing" which might be a serious bug. The simplest scenario that I have found to describe it, is as follows: SELECT comp.id as compid, comp.name, affnets.name as affid FROM toc INNER JOIN app ON toc.application_id=app.id INNER JOIN comp ON app.company_id = comp.id INNER JOIN affnets ON toc.affiliate_network_id=affnets.id GROUP BY compid, comp.name, affid This query is supposed to return all unique company/affnet pairs which occur in the fact table "toc". However, it returns only the unique company-names, each with a random (the first?) affnet. (Of course, this could be accomplished with DISTINCT, but our aim to to aggregate other fields). If I change the query to select app.company_id as compid, comp.name, affnets.name as affid ... or select comp.id as compid, comp.name, affnets.name as affid ... i.e. semantically the same but only with fields from 2 directly joined tables in the GROUP BY, then the result is correct. Question: Is this a bug or are we missing something? Thank you in advance! Daniel

4 10

[MonetDB-users] Records deleted still appear in: select count(*) from table
by Osmin Castillo 05 May '11

05 May '11

Hi guys: After delete some records from a table using a "delete from table where field=value" criteria, why the "select cont(0) from table" still shows the same records count? Maybe the records are only "marked" and not really removed from the table? Thanks in advance Osmin

3 8

[MonetDB-users] System-wide installation as of April11 release
by brien colwell 05 May '11

05 May '11

Hi all, I recently upgraded one machine to the April11 release of MonetDB. In the April11 release, what is the recommended way to start monetdb with the system, as it used to be done with the rc*.d scripts? For me, maybe a bug, the /etc/init.d/monetdb-sql script is broken (Starting MonetDB SQL server: unable to open 'merovingian.log': Permission denied ... and other missing file, permission errors). Currently I can start the server using 'sudo monetdbd', but of course I want the system-wide installation to start with the system. Thanks, Brien

3 2

[MonetDB-users] Inserted table in triggers
by Vassilis Papakonstantinou 04 May '11

04 May '11

Hello, I want to use a trigger to get the last inserted row of a table so i can insert some fields of this row to another table. Is there something like the INSERTED table in sql server so i can get the row? Thanks Vassilis

1 0

[MonetDB-users] MonetDB-11.3.1 on OpenBSD 4.9 i386 fails during make
by Michael Sioutis 04 May '11

04 May '11

Hello, I didn't now whether I should send a bug report about this, because I am not sure whether the problem occurs because of a bug in the MonetDB source code or my current unfamiliarity with OpenBSD. I downloaded the April2011 tarball from here: http://dev.monetdb.org/downloads/sources/Apr2011/ I installed all required libraries/programs and configuration went fine (except some warning parts which I will attach). The configurations is as follows: MonetDB is configured as follows: * Compilation specifics: Host: i386-unknown-openbsd4.9 Compiler: gcc CFLAGS: -g -O2 $(X_CFLAGS) -Wno-declaration-after-statement X_CFLAGS: LDFLAGS: * Enabled/disabled components: common is enabled monetdb5 is enabled sql is enabled geom is disabled (geos library required for geom module) fits is enabled rdf is disabled (by default) unixodbc is enabled java is enabled jdbc is enabled control is enabled testing is enabled * Important options: OID size: 32 bits When I run "make" is crashes giving me these erros (they are also attached): gcc: ../../../common/options/monet_options.c: No such file or directory gcc: no input files *** Error code 1 ... *** Error code 1 ... Mike

2 9

[MonetDB-users] Fwd: wkb representation of a geometry
by Georgios Garmpis 04 May '11

04 May '11

Hi, I need to get the Well Known Binary representation of a geometry but I didn't find any function in documentation to do this. Any ideas? -- George Garbis

4 11

[MonetDB-users] How does MonetDB work
by anjenny 04 May '11

04 May '11

Hey, my name is anjenny and im doin a little research on MonetDB for a project. My Question is How does MonetDB actually work? Greetings anjenny -- View this message in context: http://old.nabble.com/How-does-MonetDB-work-tp31440282p31440282.html Sent from the monetdb-users mailing list archive at Nabble.com.

5 7

[MonetDB-users] Time Interval Overlap
by brien colwell 29 Apr '11

29 Apr '11

Hi all, I have a table that stores a time interval as two timestamps, and want to construct the most efficient query to check whether two intervals in the table overlap. The naive way, using two range conditions [1], seems to choke for large tables. The M5 SQL manual mentions an OVERLAPS operator that, as far as I've read [2], seems to use an optimized interval join algorithm. Can anyone clarify: 1. How to convert a start timestamp and an end timestamp into an interval type 2. How to use the OVERLAPS operator Many thanks. Brien [0] create table intervals as ( id bigint, series_id bigint, start_time timestamp, end_time timestamp ) [1] select a.id as a_id b.id as b_id from intervals a inner join intervals b on b.series_id = a.series_id and b.id < a.id and b.start_time < a.end_time and a.start_time < b.end_time [2] http://monetdb.cwi.nl/XQuery/Documentation/StandOff-Extension.html

1 0

[MonetDB-users] rowcount in monetdb
by Vassilis Papakonstantinou 29 Apr '11

29 Apr '11

Hello all, Is there a way to take the number of rows affected by the last statement just like @@ROWCOUNT in SQL Server or SQL%ROWCOUNT in Oracle? Thanks, Vassilis

3 8

Re: [MonetDB-users] Discontinuation of MonetDB XQuery
by Peter Boncz 29 Apr '11

29 Apr '11

Hi Ryad, There have been plans to port XQuery to MonetDB5, but there has been lack of time and manpower to make this possible. This shortage of resources might change if the database research community regains interest in XQuery in a future. At CWI, a research project, engineering effort must always be justified by ongoing research projects that profit from it. For the moment, it is hard to predict when there will be XML database research projects again, but I would not rule it out. For the moment, you can also just continue to use MonetDB/XQuery. It had not changed much anyway in the past two years, so the change is not dramatic. The software is still available in open source in this April branch of the MonetDB software. So one can still download it in binary, check out the source, change it, compile it and even redistribute it according to the license. CWI for the moment just will not create new releases for it. Peter From: Ryad Ben-El-Kezadri [mailto:ryad.bek@gmail.com] Sent: Friday, April 29, 2011 9:49 AM To: monetdb-users(a)lists.sourceforge.net Subject: [MonetDB-users] Discontinuation of MonetDB XQuery Hello I am a MonetDB XQuery supporter (http://permalink.gmane.org/gmane.comp.db.monetdb.user/3824) and I am sad that it is discontinued. I was very enthusiastic about the software so I was rather excepting an upgrade to MonetDB5. However I am confident that it will be easy to turn my application to MonetDB5. As a basic XQuery user, my questions are threefold: 1)Are there some explanations for this change (flaws in the design of XQuery 2.0, lack of users, performance limitation)? 2)Is MonetDB5 SQL able to store GB of data (as MonetDB XQuery)? Will we observe performance gains for (time ordered) one dimensional data? 3)Is there an equivalent to the XRPC API? Warm regards Ryad

1 0

[MonetDB-users] Discontinuation of MonetDB XQuery
by Ryad Ben-El-Kezadri 29 Apr '11

29 Apr '11

Hello I am a MonetDB XQuery supporter ( http://permalink.gmane.org/gmane.comp.db.monetdb.user/3824) and I am sad that it is discontinued. I was very enthusiastic about the software so I was rather excepting an upgrade to MonetDB5. However I am confident that it will be easy to turn my application to MonetDB5. As a basic XQuery user, my questions are threefold: 1)Are there some explanations for this change (flaws in the design of XQuery 2.0, lack of users, performance limitation)? 2)Is MonetDB5 SQL able to store GB of data (as MonetDB XQuery)? Will we observe performance gains for (time ordered) one dimensional data? 3)Is there an equivalent to the XRPC API? Warm regards Ryad

2 1

[MonetDB-users] Announcement: New Apr2011 feature release of MonetDB suite
by Sjoerd Mullender 28 Apr '11

28 Apr '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2011 feature release of the MonetDB suite of programs. As announced one month ago to the day (March 28, 2011), this release marks the start of a new era: development of MonetDB4 and MonetDB/XQuery has ceased. Development of the Pathfinder compiler continues unabated at the University of Tübingen [1], but there is no longer a link being maintained with MonetDB. For details on this release, please see the release notes at <http://monetdb.cwi.nl/Development/Releases/Apr2011/>. [1] http://www.pathfinder-xquery.org/ -- Sjoerd Mullender

1 0

[MonetDB-users] invisible join in Monetdb
by Souman Mandal 26 Apr '11

26 Apr '11

Hi, I am a M-tech student at IIT Bombay. Currently I am doing a project to compare the performance of a column store database with implementation of invisible join and without implementation of invisible join. Now I want to use MonetDB for my project. But I have couple of doubts: > Does invisible join is already implemented in MonetDB? > Where can I get the documentation of the source code? > Though I am running mserver5 process, still if I try to execute the command 'monetdb create test' I am getting a error "monetdb: cannot perform: MonetDB Database Server (merovingian) is not running". How to solve this problem. I really need some help. Waiting for some reply. Thanks & Regards, Souman Mandal

2 1

[MonetDB-users] values of type char(n) not always blank padded?
by Ying Zhang 24 Apr '11

24 Apr '11

Hi, I'm wondering if the following results of querying values of the type char(n) (here n = 50), is expected or an error, since char(n) defines a fixed-length, blank padded string: sql>create table a2 (c1 char(5), c2 char(50)); operation successful sql>insert into a2 values ('abc', 'abc'), ('defg', 'defg'); 2 affected rows (2.848ms) sql>select c1, c2 from a2; +-------+----------------------------------------------------+ | c1 | c2 | +=======+====================================================+ | abc | abc | | defg | defg | +-------+----------------------------------------------------+ 2 tuples (1.655ms) sql>select '#' || c1 || '#' || c2 || '#' from a2; +------------------------------------------------------------+ | concat_single_value | +============================================================+ | #abc#abc# | | #defg#defg# | +------------------------------------------------------------+ 2 tuples (2.242ms) LQL The result of the first SELECT shows that c1 and c2 are blank padded (according to the SQL standard). The result of the second SELECT looks a bit strange, are the padding space characters removed or added after the last '#'? Is this the expected result or an error? Thanks, Jennie

2 1

[MonetDB-users] Using COPY with php_mapi and php_monetdb.php
by Henning Grosch 15 Apr '11

15 Apr '11

Hi, at the moment I use the odbc_drive to connect to my monetdb database. In one script I use COPY to get lots of data in my database. Everything works fine. Yesterday is downloaded the latest source files. There I found the php_mapi.inc and the php_monetdb.php with these two files I can now use monetdb in php without odbc. Now I am trying to use COPY and what happens is I get an error that the remote address canceled the socket connection. This only seems to happen with copy anything else works as excpected. So is it possible to use copy with a socket connection? Thanks. Henning

1 0