Hi all,
Not really a bug report because I did not manage to figure out the cause.
However, after upgrading from FC31 to FC32 I could not login any more, due
to SELinux problems. Auto-relabeling did not work, nothing really...
... until I did dnf uninstall MonetDB-selinux.
I came to this point because trying to give systemd services the correct
labels with restorecon failed with an error referencing a monetdb specific
file.
I do not have the details unfortunately, but if you get problems, beware
that MonetDB SELinux package and systemd may interfere in some way beyond
my knowledge of these services.
Best regards,
Arjen
PS: Some output from logs:
sudo ausearch -c monetdb -m AVC,SELINUX_ERR
[..]
----
time->Sat May 2 20:57:01 2020
type=AVC msg=audit(1588445821.693:203): avc: denied { open } for
pid=1232 comm="monetdbd" path="/etc/resolv.conf" dev="dm-0" ino=3409775
scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:default_t:s0 tclass=file permissive=1
----
time->Sat May 2 21:12:56 2020
type=AVC msg=audit(1588446776.043:1194): avc: denied { execute } for
pid=2861 comm="(monetdbd)" name="monetdbd" dev="dm-0" ino=2147256
scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1
trawcon="unconfined_u:object_r:monetdbd_exec_t:s0"
----
time->Sat May 2 21:12:56 2020
type=AVC msg=audit(1588446776.043:1195): avc: denied { execute_no_trans }
for pid=2861 comm="(monetdbd)" path="/usr/bin/monetdbd" dev="dm-0"
ino=2147256 scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1
trawcon="unconfined_u:object_r:monetdbd_exec_t:s0"
----
time->Sat May 2 21:12:56 2020
type=AVC msg=audit(1588446776.044:1196): avc: denied { map } for
pid=2861 comm="monetdbd" path="/usr/bin/monetdbd" dev="dm-0" ino=2147256
scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1
trawcon="unconfined_u:object_r:monetdbd_exec_t:s0"
----
time->Sat May 2 21:12:56 2020
type=AVC msg=audit(1588446776.714:1197): avc: denied { remove_name } for
pid=1232 comm="monetdbd" name="merovingian.pid" dev="tmpfs" ino=34369
scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:unlabeled_t:s0 tclass=dir permissive=1
trawcon="system_u:object_r:monetdbd_var_run_t:s0"
----
time->Sat May 2 21:12:56 2020
type=AVC msg=audit(1588446776.714:1198): avc: denied { unlink } for
pid=1232 comm="monetdbd" name="merovingian.pid" dev="tmpfs" ino=34369
scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1
----
time->Sat May 2 21:12:56 2020
type=AVC msg=audit(1588446776.714:1199): avc: denied { write } for
pid=1232 comm="monetdbd" name=".merovingian_lock" dev="dm-0" ino=5899443
scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:unlabeled_t:s0 tclass=file permissive=1
trawcon="system_u:object_r:monetdbd_lock_t:s0"
----
time->Sat May 2 21:13:15 2020
type=AVC msg=audit(1588446795.214:1209): avc: denied { read } for
pid=2925 comm="(monetdbd)" name="passwd" dev="dm-0" ino=524514
scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0
tclass=file permissive=1
----
time->Sat May 2 21:13:15 2020
type=AVC msg=audit(1588446795.214:1210): avc: denied { open } for
pid=2925 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0"
ino=524514 scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1
----
time->Sat May 2 21:13:15 2020
type=AVC msg=audit(1588446795.214:1211): avc: denied { map } for
pid=2925 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0"
ino=524514 scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1
----
time->Sat May 2 21:14:24 2020
type=AVC msg=audit(1588446864.487:1281): avc: denied { read } for
pid=3072 comm="(monetdbd)" name="passwd" dev="dm-0" ino=524514
scontext=system_u:system_r:init_t:s0 tcontext=system_u:object_r:var_t:s0
tclass=file permissive=1
----
time->Sat May 2 21:14:24 2020
type=AVC msg=audit(1588446864.487:1282): avc: denied { open } for
pid=3072 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0"
ino=524514 scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1
----
time->Sat May 2 21:14:24 2020
type=AVC msg=audit(1588446864.487:1283): avc: denied { map } for
pid=3072 comm="(monetdbd)" path="/var/lib/sss/mc/passwd" dev="dm-0"
ino=524514 scontext=system_u:system_r:init_t:s0
tcontext=system_u:object_r:var_t:s0 tclass=file permissive=1
--
====================================================================
ICIS, office M1.00.05 Radboud University
Mercator 1 Faculty of Science
Toernooiveld 212 arjen(a)cs.ru.nl
NL-6525 EC Nijmegen, The Netherlands +31-(0)24-365 2354
===================== http://www.informagus.nl/ ====================
--
====================================================================
ICIS, office M1.00.05 Radboud University
Mercator 1 Faculty of Science
Toernooiveld 212 arjen(a)cs.ru.nl
NL-6525 EC Nijmegen, The Netherlands +31-(0)24-365 2354
===================== http://www.informagus.nl/ ====================
Hi,
How to pass/set mserver5 options via monetdb(d) ?
I’m trying to get started monetdb in in-memory mode. mserver5 has option
--in-memory Run database in-memory only
however all attempts to pass it were unsuccessful.
--
- -
Hi all –
I have a monetdb instance with a lot of tables and a lot of data. This data is organized into schemas. While logging on today as the admistrator, I inadvertently typed:
sql>schema maizeFullGenome;
syntax error, unexpected SCHEMA in: "schema"
sql>
Instead of “set schema maizeFullGenome;”
This has apparently overwritten the data I had in my maizeFullGenome Schema. Is there anyway to get this back besides finding an old copy of the db? The following shows the schema as now gone:
sql>set schema maizeFullGenome;
operation successful
sql>\d
sql>
Yes, a very stupid mistake.
Lynn
Hi,
I just wanted to check if there is a way to grant access to system tables and views such as sys.tables and sys.schemas to the users other than granting "sysadmin" role. I am not sure what kind of privileges the sysadmin role possess so would like to find if there are other roles or privileges available to access system tables and views.
[cid:image001.jpg@01D64952.B2EE5CC0]
Suresh Devarakonda
84.51<http://www.8451.com/>°
TDO - Database Support Team
D: 513.632.3692
M: 859.512.1839
A: 100 W. 5th Street, Cincinnati, Ohio 45202
Cincinnati | Chicago | New York | Portland
Facebook<https://www.facebook.com/8451group/?ref=aymt_homepage_panel> LinkedIn<https://www.linkedin.com/company/84-51/?trk=biz-companies-cym> Instagram<https://www.instagram.com/84.51/> Twitter<https://twitter.com/8451group>
Please consider the environment before printing this email.
Hello,
Could you please explain why predicates with subqueries in UDFs do not work?
For example, I have the following statements:
create table itab (x int, y int);
insert into itab values (1, 0);
insert into itab values (2, 1);
create function test1(n int)
returns int
begin
while (n > 9) do
set n = n -1;
end while;
return n;
end;
create function test2(n int)
returns int
begin
while (n > (select 9 from itab) ) do
set n = n -1;
end while;
return n;
end;
create function test3(n int)
returns int
begin
while (exists (select x from itab where x < n)) do
set n = n -1;
end while;
return n;
end;
The function test1 can be successfully created and executed, but functions
test2 and test3 cannot be created and executed.
Thank you and best regards,
Oksana
On Mon, Jun 22, 2020 at 08:11:26PM +0400, Miro Mannino wrote:
> Dear Niels Nes,
>
> We would like to insert data column by column, for various reasons.
>
> The problem is that if we create a larger table with column T1 and
> column T2, and insert 1000 rows on column T1, then the next 1000 insert
> on T2 are going to be appended, resulting in 1000 rows of (val, NULL)
> followed by another 1000 rows of (NULL, val). We could do that if there
> is an option to INSERT INTO from the first row as well (ignoring
> non-null values).
>
> In general, we are trying to use MonetDB taking advantage of a column
> store DB, but we can't really insert a batch of values for one column
> after another column due to the usual semantic of INSERT INTO that
> works really in rows.
>
> Looking forward to hearing from you,
Column by column inserts is indeed a problem with the sql language being
row oriented. We do have some solution, ie binary copy into, but that
maybe a bit too much for you.
See https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/BinaryBulkLoad
Niels
>
> Miro
>
>
>
> On Mon, Jun 22, 2020 at 8:02 PM Niels Nes <Niels.Nes(a)cwi.nl> wrote:
>
> Min
>
> Why don't you just create one larger table?
>
> Niels
>
> On 22 June 2020 14:59:44 CEST, Min Kyu Jung <mkj280(a)nyu.edu> wrote:
>
> To whom this may concern,
>
> I am Min, and I am currently utilizing MonetDB for a project
> and I was wondering if there are any optimizations made in
> MonetDB such that two tables with the same number of rows can
> be joined together?
> For example, consider the following two table:
> CREATE TABLE t1(rowNumber, val);
> WITH SERIES AS( SELECT * FROM generate_series(0, 10000))
> INSERT INTO t1 SELECT ROW_NUMBER() over (), rand() FROM series;
> CREATE TABLE t2(rowNumber, val);
> WITH SERIES AS( SELECT * FROM generate_series(0, 10000))
> INSERT INTO t2 SELECT ROW_NUMBER() over (), rand() FROM series;
>
> In this situation, I currently join the table as follows:
> CREATE TABLE t3(row#, val1, val2);
> INSERT INTO t3 SELECT ROW_NUMBER(), t1.val, t2.val FROM t1
> INNER JOIN t2 on t1.rowNumber = t2.rowNumber;
>
> Given that MonetDB is column-stored, I was wondering if there
> is a simpler/faster/more optimized way of stitching the two
> tables into one table.
>
> To be clearer, as of right now, I make sure all tables have the
> rowNumber column. However, is there a way where I can join
> multiple tables (all with the same number of rows) without
> pre-generating rowNumber column for each table?
> An example is this:
> INSERT INTO t3 SELECT t1.val, t2.val FROM t1 INNER JOIN t2 on
> row_number(t1) = row_number(t2);
>
> Sincerely,
> Min
>
--
Niels Nes, CTO, MonetDB Solutions
url: https://www.monetdbsolutions.com e-mail: Niels.Nes(a)monetdbsolutions.com
To whom this may concern,
I am Min, and I am currently utilizing MonetDB for a project and I was
wondering if there are any optimizations made in MonetDB such that two
tables with the same number of rows can be joined together?
For example, consider the following two table:
CREATE TABLE t1(rowNumber, val);
WITH SERIES AS( SELECT * FROM generate_series(0, 10000))
INSERT INTO t1 SELECT ROW_NUMBER() over (), rand() FROM series;
CREATE TABLE t2(rowNumber, val);
WITH SERIES AS( SELECT * FROM generate_series(0, 10000))
INSERT INTO t2 SELECT ROW_NUMBER() over (), rand() FROM series;
In this situation, I currently join the table as follows:
CREATE TABLE t3(row#, val1, val2);
INSERT INTO t3 SELECT ROW_NUMBER(), t1.val, t2.val FROM t1 INNER JOIN t2 on
t1.rowNumber = t2.rowNumber;
Given that MonetDB is column-stored, I was wondering if there is a
simpler/faster/more optimized way of stitching the two tables into one
table.
To be clearer, as of right now, I make sure all tables have the rowNumber
column. However, is there a way where I can join multiple tables (all with
the same number of rows) without pre-generating rowNumber column for each
table?
An example is this:
INSERT INTO t3 SELECT t1.val, t2.val FROM t1 INNER JOIN t2 on
row_number(t1) = row_number(t2);
Sincerely,
Min
The MonetDB team at MonetDB BV is pleased to announce the
Jun2020 feature release of the MonetDB suite of programs.
More information about MonetDB can be found on our website at
<https://www.monetdb.org/>.
For details on this release, please see the release notes at
<https://www.monetdb.org/Downloads/ReleaseNotes>.
As usual, the download location is <https://www.monetdb.org/downloads/>.
Jun 2020 feature release (11.37.7)
MonetDB5 Server
* The mserver5 option --verbose (-v) was removed. A similar effect
can be had by issuing the query CALL
logging.setcomplevel('SQL_TRANS', 'INFO'); as the monetdb user.
* The functions algebra.intersect, algebra.semijoin, and
algebra.crossproduct have an extra argument, bool max_one, which
indicates that there must be no more than one match in the join.
* The example module opt_sql_append is not installed in the binary
packages anymore.
* Added mserver5 option (--dbtrace=<path>) in order to be able to
specify the output file any produced traces.
* Removed function bat.setKey().
* algebra.rangejoin now has two extra arguments: anti:bit and
symmetric:bit.
* Added session identifier, number of workers and memory claim to the
sysmon queue.
* The worker (number of threads), memory (in MB) and optimizer
pipeline limits can now be set per user session basis. The query
and session timeouts are now set in seconds.
* With required privileges an user can set resource limits for a
session.
* There are now versions of group.(sub)group(done) that produce a
single output containing just the groups.
* algebra.join and algebra.leftjoin now have forms which return a
single column. The column that is returned is the left column of
the two column version.
* The MAL profiler now assigns the SQL TRACE output to the client
record thereby avoiding the interaction with other queries, but
loosing insight of competing queries. The stethoscope should be
used for that.
SELinux Support
* There was a problem with the MonetDB SELinux support on Fedora 32.
That is fixed in this release. In order to do a proper upgrade of
the package if you have already installed MonetDB-selinux on Fedora
32, you may need to uninstall (dnf remove) the old package and then
install the new.
Whole System
* A new system to deal with debug output has been implemented. There
is now an option --dbtrace to mserver5 that takes a file argument
to which debug output is written. The default value is the file
mdbtrace.log inside the database directory. This option can also be
set through the monetdb program.
* The home directory of the automatically created monetdb user was
changed from /var/MonetDB to /var/lib/monetdb (RPM based systems
only). This home directory is (currently) not used for anything,
though.
* Python 2 support has been removed. There is now only support for
using Python 3.
* Removed support for bam and sam files.
* Added mserver5 option (--set raw_strings=true|false) and monetdb
database property (raw_strings=yes|no) to control interpretation of
strings.
Merovingian
* On Fedora and RHEL systems (not RHEL 6), if monetdbd runs under
systemd, when the package is updated, monetdbd (and hence any
mserver5 process it runs) is restarted.
* Added dbtrace mserver5 option to the daemon in order to set
mserver5's output directory for the produced traces.
Client Package
* The monetdb-client-tools (Debian/Ubuntu) and MonetDB-client-tools
(Fedora/RH) containing the stethoscope, tachograph, and tomograph
has been removed. A completely new version of stethoscope will be
released to replace the old version.
* Removed the possibility of using the MD5 checksum for
authentication purposes. It was never actively used but was there
as an option. Now the option has been removed.
* Add port and host as fields in the .monetdb file.
* Added 'sessionid' column to system function sys.queue(), so each
query gets tagged with the current session identifier
* Allow monetdb user to control session and query time out and
selectively stopping a client sessions with a soft termination
request.
ODBC Driver
* The NUMERIC and FLOAT types are now handled fully. Before only
DECIMAL, FLOAT, and DOUBLE were handled fully.
* Some bugs were fixed in the passing back and forth between
application and server of values of type GUID (UUID).
MonetDB Common
* The functions BATintersect, BATsemijoin, and BATsubcross have an
extra argument, bool max_one, which indicates that there must be no
more than one match in the join.
* The "unique" property on BATs was removed. The property indicated
that all values in a BAT *had* to be distinct, but this was not
actually used.
* A new type of candidate list has been introduced. Candidate lists
are used internally to specify which rows of a column participate
in an operation. Before, candidate lists always contained a list of
candidate row IDs. The new candidate list type specifies a list of
row IDs that should NOT be considered (negative candidates).
* The maximum number of BATs in the system has been increased for 64
bit architectures.
* The hash tables used internally by the system now uses a technique
based on Linear Hashing which allows them to grow gracefully. This
means that hash tables aren't removed and recreated nearly as often
anymore. This also meant that the hash table had to be split into
two files, which means that after an upgrade the hash tables have
to be recreated.
* Implemented a version of BATproject, called BATproject2, with two
"right" arguments which conceptually follow each other.
* Removed MT_mmap and MT_munmap from the list of exported functions.
Use GDKmmap and GDKmunmap with the same parameters instead.
* Changed the interface of the atom "fix" and "unfix" functions. They
now return a value of type gdk_return to indicate success/failure.
* Removed the tunique property. The tunique property indicated that
all values in the column had to be distinct. It was removed because
it wasn't used.
* BATrangeselect now has two extra arguments: anti and symmetric
(both bool).
SQL Frontend
* Updating the value of a sequence now requires privilege on its own
schema.
* The sys.querylog_enable(threshold integer) now actually enables the
querylog and uses a threshold in milliseconds.
* Removed UNION JOIN statements. They were dropped by the SQL:2003
standard, plus MonetDB implementation was not fully compliant.
* The OFFSET value in the COPY INTO query now counts uninterpreted
newlines. Before it counted "unquoted record separators" which
meant that if you had a single quote on a line that you want to
skip, you could not use the feature.
* Added support for FROM RANGE MINVALUE TO RANGE MAXVALUE and FROM
RANGE MINVALUE TO RANGE MAXVALUE WITH NULL VALUES cases in
partitioned tables by range (before they weren't).
* Removed support for Python 2. Python 2 itself is no longer
supported. Use Python 3 instead. Functions that were declared as
LANGUAGE PYTHON2 or LANGUAGE PYTHON2_MAP are changed to LANGUAGE
PYTHON and LANGUAGE PYTHON_MAP respectively (without changing the
Python code).
* Implemented 'covar_pop' and 'covar_samp' aggregate functions, as
well as their window function counterparts. Implemented
'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop', 'corr' and
'group_concat' window function correspondents.
* Extended SQL catalog with CREATE WINDOW syntax for user-defined
SQL:2003 window functions. At the moment, window functions must be
defined on the backend engine, i.e. on this case MAL. In the
current implementation, the backend code generation creates two
additional columns of type lng with the start and end offsets for
each row.
* Added prepared_statements_args view, which details the arguments
for the prepared statements created in the current session.
* Added sys.prepared_statements view, which lists the available
prepared statements in the current session.
* Added deallocate statements with the syntax 'DEALLOCATE [PREPARE] {
number | ALL }', to close an existing prepared statement or all,
through the SQL layer. Previously this feature was available via
MAPI exclusively with the "release" command.
* Added support for raw strings using the syntax r'' or R''. This
means that C-like escapes will remain uninterpreted within those
strings. For instance SELECT r'\"' returns a string of length two.
The user needs to escape single quotes by doubling them: SELECT
r''''.
* Implemented ROLLUP, CUBE and GROUPING SETS from SQL:1999. They
define grouping subsets used with the GROUP BY clause in order to
compute partial groupings. Also, the GROUPING aggregate was added.
This aggregate is a bitmask identifying the grouping columns not
present in the generated grouping row when used with the operators
described above.
* Updated user session procedures by adding the possibility to set
properties based on a session identifier. Optimizer pipeline:
sys.setoptimizer(int, string) Number of worker threads:
sys.setworkerlimit(int, int) Memory limits (in MB):
sys.setmemorylimit(int, int) Query timeout (in ms):
sys.setquerytimeout(int, int) Session timeout (in ms):
sys.setsessiontimeout(int, int) The first argument corresponds to
the id of the session to modify, and these procedures are bound to
the monetdb user exclusively. The versions of the mentioned
procedures with just the second argument were added as well, where
the changes are reflected in the current user session, and
therefore every user can call them.
* The procedures sys.settimeout(bigint),
sys.settimeout(bigint,bigint) and sys.session(bigint) are now
deprecated. Instead use sys.setquerytimeout and
sys.setsessiontimeout mentioned above.
* Added SQL procedure sys.hot_snapshot() which can be used to write a
snapshot of the database to a tar file. For example,
sys.hot_snapshot('/tmp/snapshot.tar'). If compression support is
compiled in, snapshots can also be compressed
('/tmp/snapshot.tar.gz'). The tar file expands to a single
directory with the same name as the database that was snapshotted.
This directory can be passed directly as the --dbpath argument of
mserver5 or it can be copied into an existing dbfarm and started
from monetdbd.
Bug Fixes
* 6298: unexpectedly slow execution of SELECT length(fieldname) FROM
tablename LIMIT 1 queries
* 6401: Suspected memory leak in mserver5 when creating/dropping
tables
* 6687: Count distinct very slow and use too much the hard drive
* 6723: columns aliases duplicates should not be allowed. automatic
aliasing required.
* 6731: Add system view to allow querying of available prepared
statements and their parameters
* 6732: Add SQL command to close a specific prepared statement
* 6750: Executing a query on a non-existing column on a remote table
crashes the remote server
* 6785: function sys.isaURL(url) should have been declared as
sys.isaURL(string)
* 6808: reveal the alarm.sleep procedure in SQL
* 6813: function not_uniques(bigint) returns error when called
* 6818: usage of multiple column expressions in where-clause (f(a),
f(b)) in (select a, b) causes assertion failure on mserver5
* 6821: Failed to start monetdb with embedded python
* 6828: Server crashes when executing a window query with ordering by
EXTRACT date
* 6846: Global temporary table not accessible in other connections /
sessions
* 6847: A simple way of speeding up impscheck for dense canditers
* 6850: Idle timestamp not set
* 6851: json parser doesn't parse integers correctly
* 6863: thash files not released upon drop table
* 6864: (I)LIKE with multiple % doen't find matches
