users-list May 2012

users-list@monetdb.org

17 participants
18 discussions

[MonetDB-users] Different results with same query

by Matthieu Guamis

Hello, Running the same query on the same data leeds to different results between postgresql and monetdb. I have not been able to get a simple example to reproduce the problem. So here is what I observe: the query: select temps_mois.rfoperdmo as c1, sum((case when dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 from rfoper_temps_mois as temps_mois, dwhinv as dwhinv, rfovsn as rfovsn_0, rrhamv as rrhamv_1, rrhcov as rrhcov_2, rfoadv as rfoadv_3 where temps_mois.rfoper___rforefide = 'HPLUS' and dwhinv.dwhinv___rforefide = 'HPLUS' and dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' and dwhinv.dwhinvdtd = temps_mois.rfoperdtd and temps_mois.rfoperyea = '2011' and rfovsn___rforefide = 'HPLUS' and dwhinv.dwhinv___rfovsnide = rfovsn_0.rfovsnide and rfovsn_0.rfovsnide = '201111_reel' and rrhamv_1.rrhamv___rrhvemide='GRACOR' AND rrhamv_1.rrhamvrvs=1 AND rrhamv_1.rrhamv___rforefide= 'HPLUS' and dwhinv.dwhinv___rrhempide = rrhamv_1.rrhamvinf and rrhamv_1.rrhamvsup = 'CEMP' and rrhcov_2.rrhcov___rrhvcoide='CONTRATS' AND rrhcov_2.rrhcovrvs=1 AND rrhcov_2.rrhcov___rforefide= 'HPLUS' and dwhinv.dwhinv___rrhcntide = rrhcov_2.rrhcovinf and rrhcov_2.rrhcovsup = 'CONTRATS' and rfoadv_3.rfoadv___rfovdeide='STRC' AND rfoadv_3.rfoadvrvs=1 AND rfoadv_3.rfoadv___rforefide= 'HPLUS' and dwhinv.dwhinv_p2rfodstide = rfoadv_3.rfoadvinf and rfoadv_3.rfoadvsup = 'HPLUS' group by c1 order by c1 Postgresql result: c1 | m0 ----+----------------- 01 | 7111.5376967750 02 | 7100.9108821426 03 | 7150.2597967742 04 | 7151.4283666667 05 | 7109.1641451610 06 | 6976.2108421239 07 | 6329.7404193564 08 | 6302.9823032247 09 | 6426.9459633351 10 | 6519.6889580648 11 | 6549.5235033402 12 | 6492.5477161292 (12 lignes) Monetdb result: +------+----------------------+ | c1 | m0 | +======+======================+ | 12 | 1.000000 | | 11 | 1.000000 | | 10 | 2.000000 | | 09 | 0.366667 | | 08 | 1.000000 | | 07 | 1.000000 | | 06 | 1.000000 | | 05 | 3.000000 | | 04 | 4.000000 | | 03 | 15.000000 | | 02 | 9.964286 | | 01 | 1.000000 | +------+----------------------+ 12 tuples (212.346ms) As you see, all "m0" values are very different. I have no idea of what is causing this but if I modify the query in order to simplify the CASE...WHEN...ELSE part of the select, it produce the expected result: replacing sum((case when dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 by sum((case when dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 Its not exactly the same query but it must leeds to the same result due to my test datas (its the case in postgres) I join to this email two files with the TRACES, respectively for the request with bad/correct result (ko.txt/ok.txt) Config: Ubuntu Server 11.04 x64 MonetDB v11.7.9 (Dec2011-SP2), MonetDB Database Server v1.6 (Dec2011-SP2), MonetDB Database Server Toolkit v1.0 (Dec2011-SP2) Thank you very much for your help!! -- *Matthieu Guamis* *Logo Axège <http://www.axege.com/>* /Axège// 23,rue Saint-Simon 63000 Clermont-Ferrand/ Tél: +33 (0)4 63 05 95 40 Fax: +33 (0)4.73.70.65.29 Email: matthieu.guamis(a)axege.com <mailto:matthieu.guamis@axege.com>

9 years, 8 months

[MonetDB-users] Any plan to support Ubuntu Server 12.04

by Majid Azimi

hi guys, Is there any plan to build binaries for Ubuntu server 12.04(Precise Pangolin)? I didn't find debs for this release from repository?

10 years, 2 months

[MonetDB-users] Changing time zone after getting GMT time from database

by Majid Azimi

Hi guys, I have MonetDB database with table containing user table: CREATE TABLE user ( birth_date TIMESTAMP NOT NULL ); birth_date is saved in GMT without DST. (This is the default behavior of MonetDB). So I should change the TimeZone in my application. Here is my code: Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); Connectioncon =DriverManager.getConnection("jdbc:monetdb://localhost/online","monetdb","monetdb"); Statementst =con.createStatement(); ResultSetrs; Calendar c = Calendar.getInstance(TimeZone.getTimeZone("UTC")); rs =st.executeQuery("SELECT * FROM user"); while(rs.next()){ c.setTime(rs.getTimestamp("birth_date")); c.setTimeZone(TimeZone.getTimeZone("Asia/Tehran")); System.out.println(c.get(Calendar.YEAR)+"-"+c.get(Calendar.MONTH)+"-"+c.get(Calendar.DAY_OF_MONTH)+" "+c.get(Calendar.HOUR_OF_DAY)+":"+c.get(Calendar.MINUTE)+":"+c.get(Calendar.SECOND)); } But there is no difference between timestamps in database and timestamps after converting time zone in application. Could anyone help? Is this the wrong way to convert TimeZone? I'm using MonetDB version 11.9.5-20120516 on Debian 6 with openjdk 6. Here is the monetdbd getall /home/dbfarm: dbfarm /home/dbfarm/ status monetdbd[4187]1.6(Apr2012-SP1)is serving thisdbfarm mserver /usr/bin/mserver5 logfile /home/dbfarm//merovingian.log pidfile /home/dbfarm//merovingian.pid sockdir /tmp port 50000 exittimeout 60 forward proxy discovery yes discoveryttl 600 control yes passphrase {SHA512}ba3253876aed6bc22d4a6ff53d8406c6ad864195ed144ab5c87621b6c233b548baeae6956df346ec8c17f5ea10f35ee3cbc514797ed7ddd3145464e2a0bab413 mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000

10 years, 2 months

[MonetDB-users] Tuning MonetDB

by Majid Azimi

Hi guys, As MonetDB does rely on OS features to be tuned, Which settings should we optimize on OS specially on Linux? 1. Tuning network a. using jumbo b. socket buffer space c. TCP setting 2. Tuning VM a. huge page b. swappiness 3. Tuning File system a. which file system? ext3? ext4? XFS? b. using large blocks if database is almost read only does using ext2 make any benefit?

10 years, 2 months

[MonetDB-users] Announcement: New Apr2012-SP1 Bugfix release of MonetDB suite

by Fabian Groffen

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2012-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/>. Apr 2012-SP1 bugfix release Build Environment * Windows: we now use OpenSSL 1.0.1b. SQL * Databases that were upgraded from the Aug2011 release have an error in the catalog for SQL procedures. This is now fixed. Merovingian * Fixed a bug where connecting to a stopped multiplex-funnel would result in a 'there are no available connections' error. MonetDB Common * Implemented MT_getrss for Mac OS X systems, this allows the server to know about how much memory is currently in use. Bug Fixes * 2454: SQL: add various statistical functions * 2916: non admin user cannot create temporary tables * 2949: SQL exception during execution of a correct user defined function that just returns a relation table constructed by a simple SQL select query * 2958: Grant does't work with schema syntax * 2960: mclient terminates connection during insertion of a large number of records from a file * 2972: SQL URL functionality contains errors * 2987: Broken type inference when using sql round * 2992: SQL module truncates DECIMALS when passing integers to MAL module * 3000: Inconsistency between read-only and read-write modes after dropping a schema * 3028: Segmentation fault in seqscan_eq_bte_bte_tloc_void_void * 3046: mserver5 terminates with segmentation fault * 3063: datacell functions no longer work after restart of database * 3072: Database upgrade from Dec2011 to Apr2012 broken for storage() * 3073: VERY slow foreign key check (due to missing batcalc.isnil() ?) * 3074: Name resolution in procedures * 3077: No errors on UDF returning table with too many columns * 3079: Converting an Oracle schema DDL for MonetDB fails to create a column named "no".

10 years, 2 months

Re: [MonetDB-users] Using monetdb and sqlalchemy

by Sabst

Still stuck. If you know the answer please share it. Also if you think the two are not meant to work together, share it too. Anyone? > ----- Message transféré de sabst(a)free.fr ----- > Date : Thu, 10 May 2012 15:07:17 +0200 > De : sabst(a)free.fr > Adresse de retour :sabst@free.fr > Sujet : Using monetdb and sqlalchemy > À : monetdb-users(a)lists.sourceforge.net > > Hi, > I am just starting using monetdb and, even if I could find discussions about > Python/SqlAlchemy integration, I could not make it work. > > I installed python-monetdb module and have a running monetdb server. > > Now I expect my client application to work with monetdb as it does with > postgresql and instead of passing a connection string like: > postgres://user:password@somehost:port/db > I use: > monetdb://user:password@somehost/db > > But I get this error: > ArgumentError: Could not determine dialect for 'monetdb'. > > What are the steps required to integrate monetdb into SqlAlchemy? > > Certainly missing the obvious but I'm currently stuck. > > Thank you in advance for any help, > Stephane. > > ----- Fin du message transféré ----- > > > >

10 years, 2 months

[MonetDB-users] Monet on SUSE

by Hualin Wang

Dear All, I have trouble compliing the Monet on my SUSE 11 box. The server has an old PCRE installed, like 7.8. I download my own version of pcre 8.3, and complie it with utf-8 enabled. I set up the following three environment vars: pcre_LIBS, pcre_CFLAGS, PKG_CONFIG_PATH to point to the right place. But the Monet ./configure still complain utf-8 support no. By the way, the pcre check is yes. Which version of PCRE package I need install on SUSE? By the way, what this sentence means from monetdb.org "Make sure you install the xxx-dev versions on binary distributions of the necessary packages as well!" Thanks for any help/hints! Best Regards, Hualin

10 years, 2 months

[MonetDB-users] Bad SELECT performance after deletion of 1 record

by suncpp

Hi everyone, I had added about 30,000,000 records in one MonetDB table. The performance of executing SELECT-queries was really great. But after the deletion of only 1 record from the table, the performance had slowed down considerably. I have seen the topics here with alike problem but I have not found the solution. Could anyone help me to understand what the problem is? -- View this message in context: http://old.nabble.com/Bad-SELECT-performance-after-deletion-of-1-record-tp3… Sent from the monetdb-users mailing list archive at Nabble.com.

10 years, 2 months

[MonetDB-users] strange trigger behaviour

by Majid Azimi

Hi guys, I have created a table named users: CREATE TABLE names ( name VARCHAR(255) NOT NULL ); another table named names2 which is same as users. only table names are different. I have created a after insert trigger like this: CREATE TRIGGER trg_names_ai AFTER INSERT ON names REFERENCING NEW ROW AS new_row FOR EACH ROW WHEN new_row.name = '<some name>' INSERT INTO names2 VALUES(new_row.name); The problem is it doesn't care about WHEN clause. Any INSERT query will appear in names2. Can anyone help?

10 years, 2 months

[MonetDB-users] timeout while waiting for database 'xxx' to initialise the sql scenario

by Brian J. Cardiff

Hi all, I'm trying monetdb among with other vertical databases. After some experiments I am unable to start the database again. I'm not sure what the things I did cause the issue, but happens twice already. I have restart monetdbd and server without luck. Is there any way to solve this issue? $ monetdb start xxx starting database 'xxx'... FAILED start: starting 'xxx' failed: timeout while waiting for database 'xxx' to initialise the sql scenario The experiments consist on: 1. Bulk load .csv file with ~ 20*10^6 records 2. Do some aggregate functions with "SELECT AVG(..) FROM TBL WHERE ID IN (...)" with different amount of ids in list (10 - 5000) (yeap, ugly). 3. Add columns to TBL, Massive update columns in TBL, Drop columns in TBL. [version] MonetDB Database Server Toolkit v1.0 (Apr2012) MonetDB Database Server v1.6 (Apr2012) [morovingian.log] 2012-05-11 15:05:55 MSG xxx[637]: # Listening for UNIX domain connection requests on mapi:monetdb:///Users/bcardiff/Work/Xxx/db/xxx/.mapi.sock 2012-05-11 15:05:55 MSG xxx[637]: # MonetDB/GIS module loaded 2012-05-11 15:05:55 MSG xxx[637]: # MonetDB/SQL module loaded 2012-05-11 15:06:05 MSG merovingian[588]: sending process 637 (database 'xxx') the TERM signal 2012-05-11 15:06:05 MSG merovingian[588]: database 'xxx' has shut down 2012-05-11 15:06:05 MSG merovingian[588]: database 'xxx' (637) has exited with exit status 0 2012-05-11 15:06:05 ERR control[588]: (local): failed to fork mserver: timeout while waiting for database 'xxx' to initialise the sql scenario -- Brian J. Cardiff - Manas Technology Solutions [ar.phone] 4796.0232 #BCR(227) [us.phone] 312.612.1050 #BCR(227) [email] bcardiff(a)manas.com.ar [web] www.manas.com.ar [weblog] http://weblogs.manas.com.ar/bcardiff/

10 years, 2 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list May 2012