Another thing that i think is interesting regards MERGE TABLES is that a remote host dies you to DROP TABLE in your MERGE TABLE via the ALTER ...

I like this as it allows you good house keeping also you should already know the host down via your monitoring systems of choice.

However until you ALTER TABLE mytable DROP TABLE tablename; you are hindered for example in a production envirionment as you need to remove it otherwise you can't query the table in question.

BTW i have never seen anything as amazing as REMOTE / MERGE tables and the performance and possibilities are mind blowing.

Just a few thoughts :)

Regards,

Brian Hood  

On Tue, Nov 17, 2015 at 10:26 PM, Brian Hood <brianh6854@googlemail.com> wrote:
Hi All,

It seems when i run my SQL in sequence i can repeated break things with a MAL error.

If this isn't a bug please can you point me in the right direction but looks like one with me.

Master has 4 remote tables and a MERGE table ippacket with all the remote table added.

All mserver5 instances are the same version / release.

Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP1)
Database: MonetDB v11.21.11 (Jul2015-SP1), 'mapi:monetdb://mdb-master-01:50000/threatmonitor'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>\d
MERGE TABLE   threatmonitor.ippacket
REMOTE TABLE  threatmonitor.wifi_ippacket
REMOTE TABLE  threatmonitor.wifi_ippacket2
REMOTE TABLE  threatmonitor.wifi_ippacket3
REMOTE TABLE  threatmonitor.wifi_ippacket4

sql>select count(*) from ippacket limit 10;
+------+
| L1   |
+======+
|  951 |
+------+
1 tuple (44.499ms)
sql>select guid, recv_date, ip_dst from ippacket limit 10
more>;
(mapi:monetdb://monetdb@172.17.0.5/threatmonitor) 'user.l4' undefined in:     (rmt3424_X_3_bat_oid_str:bat[:oid,:str],rmt3425_X_6_bat_oid_str:bat[:oid,:str],rmt3427_X_7_bat_oid_str:bat[:oid,:str]) := user.l4();

The second time i run the same query and its absolutely fine.

sql>select guid, recv_date, ip_dst from ippacket limit 10
more>;
+--------------------------------------+---------------------------+----------------+
| guid                                 | recv_date                 | ip_dst         |
+======================================+===========================+================+
| 5f4c9bcb-e4b7-0af1-2919-fdef5623d20a | 2015-07-20 19:52:45 +0000 | 10.130.19.42   |
| 2238c353-33cd-5174-31b6-ca57b4decade | 2015-07-20 19:52:45 +0000 | 216.58.209.227 |
| 8ac3dde3-92ce-ff27-f6b1-f296b0d139f5 | 2015-07-20 19:52:45 +0000 | 10.130.19.42   |
| 7a204771-8623-e498-5f45-ba1c58adb3da | 2015-07-20 19:52:45 +0000 | 85.25.200.151  |
| 496ab33b-3ae9-56b5-9108-209413dcddeb | 2015-07-20 19:52:45 +0000 | 85.25.200.151  |
| a1537072-8545-74eb-3b7e-9615a4c534ac | 2015-07-20 19:52:45 +0000 | 10.130.19.42   |
| 91124c5a-0854-7f21-e60a-f175e45d68d1 | 2015-07-20 19:52:45 +0000 | 10.130.19.42   |
| acefc01b-6ef8-6197-f5c0-27194e5749db | 2015-07-20 19:52:45 +0000 | 85.25.200.151  |
| f0694ee8-2e7f-e019-a96e-450489ae8f37 | 2015-07-20 19:52:45 +0000 | 10.130.19.42   |
| 78c246a9-eba2-79b5-fb87-59609f44a7d9 | 2015-07-20 19:52:45 +0000 | 85.25.200.151  |
+--------------------------------------+---------------------------+----------------+
10 tuples (63.443ms)
sql>

I've attached my example schema.

Regards,

Brian Hood