MERGE TABLE - CANNOT DROP PARTITION - users-list

MERGE TABLE - CANNOT DROP PARTITION

older
Guide to installing from source on...

Ariel Abadi

23 Oct 2019 23 Oct '19

4:16 p.m.

Hi! Can someone help me? Im trying to REMOVE a table from a merge table, when I run the command, it states "operation successful", but the table is NOT removed. Here you can see an example... I have run it several times, but the table is still there :( [image: image.png]

Attachments:

attachment.html (text/html — 1.3 KB)
image.png (image/png — 22.0 KB)

Show replies by date

ferreira

23 Oct 23 Oct

4:32 p.m.

Hello Ariel, this looks like a bug. Can you send the reproducible steps? Best regards, Pedro On 10/23/19 6:16 PM, Ariel Abadi wrote:

...

Hi! Can someone help me? Im trying to REMOVE a table from a merge table, when I run the command, it states "operation successful", but the table is NOT removed. Here you can see an example... I have run it several times, but the table is still there :(

image.png

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Ariel Abadi

4:42 p.m.

The steps are very simple... 1) I have created the MERGE TABLE CREATE MERGE TABLE cubm_201909 AS (SELECT * FROM cub) WITH NO DATA; 2) I have attached the table: ALTER TABLE cubm_201909 ADD TABLE cub8_20190901; 3) Wanted to REMOVE the table: ALTER TABLE cubm_201909 DROP TABLE cub8_20190901; This new MERGE table has inside 30 tables attached, none of them were removed. We are using MonetDB v11.31.7 (Aug2018) Thks Ariel On Wed, Oct 23, 2019 at 1:32 PM ferreira < pedro.ferreira@monetdbsolutions.com> wrote:

...

Hello Ariel, this looks like a bug. Can you send the reproducible steps?

Best regards,

Pedro On 10/23/19 6:16 PM, Ariel Abadi wrote:

Hi! Can someone help me? Im trying to REMOVE a table from a merge table, when I run the command, it states "operation successful", but the table is NOT removed. Here you can see an example... I have run it several times, but the table is still there :(

[image: image.png]

_______________________________________________ users-list mailing listusers-list@monetdb.orghttps://www.monetdb.org/mailman/listinfo/users-list

ferreira

4:54 p.m.

Ok, Aug2018 is an old version, can you upgrade to Apr2019? On 10/23/19 6:42 PM, Ariel Abadi wrote:

...

ALTER TABLE cubm_201909 DROP TABLE cub8_20190901;

Ariel Abadi

24 Oct 24 Oct

11:54 a.m.

Hi ! I tried to upgrade from 11.31.7 to the latest version. In order to do that (as I learned in the past, I did it step by step), this means that I have upgraded from one version to another. 11.31.7 -> 11.31.11 *OK!* 11.31.11 -> 11.31.13 *OK!* 11.31.13 -> 11.33.3 --> *ERROR!* The database never started, I run the monetdbd xxx, and started with no problem. BUT when I tried to start the DB (monetdb start -a) ... it never started (it kept thinking for a long time). Even Though I saw it was running .... seems to be "hanged". [image: image.png] After a while I killed it via kill -9 xxxx ... And when I started again I found the following error which made my DB unusable. 2019-10-24 07:22:25 ERR cli_mx[4277]: #main thread:!ERROR:ParseException:SQLparser:42000!DROP PROCEDURE: no such procedure 'querylog_enable' (smallint(16)) 2019-10-24 07:22:25 ERR cli_mx[4277]: !ParseException:SQLparser:42000!DROP PROCEDURE: no such procedure 'querylog_enable' (smallint(16)) 2019-10-24 07:22:25 MSG cli_mx[4277]: # MonetDB/SQL module loaded Is something Im doing wrong ? I need to upgrade this DB, cause is too big to start doing dump & restore. Any help will be appreciated. Thks Ariel On Wed, Oct 23, 2019 at 1:54 PM ferreira < pedro.ferreira@monetdbsolutions.com> wrote:

...

Ok, Aug2018 is an old version, can you upgrade to Apr2019?

On 10/23/19 6:42 PM, Ariel Abadi wrote:

...
ALTER TABLE cubm_201909 DROP TABLE cub8_20190901;

Ying Zhang

28 Oct 28 Oct

2:21 p.m.

...

On 24 Oct 2019, at 13:54, Ariel Abadi wrote:

Hi ! I tried to upgrade from 11.31.7 to the latest version. In order to do that (as I learned in the past, I did it step by step), this means that I have upgraded from one version to another.

11.31.7 -> 11.31.11 OK! 11.31.11 -> 11.31.13 OK!

11.31.13 -> 11.33.3 --> ERROR! The database never started, I run the monetdbd xxx, and started with no problem. BUT when I tried to start the DB (monetdb start -a) ... it never started (it kept thinking for a long time). Even Though I saw it was running .... seems to be "hanged".

After a while I killed it via kill -9 xxxx ... And when I started again I found the following error which made my DB unusable. 2019-10-24 07:22:25 ERR cli_mx[4277]: #main thread:!ERROR:ParseException:SQLparser:42000!DROP PROCEDURE: no such procedure 'querylog_enable' (smallint(16)) 2019-10-24 07:22:25 ERR cli_mx[4277]: !ParseException:SQLparser:42000!DROP PROCEDURE: no such procedure 'querylog_enable' (smallint(16)) 2019-10-24 07:22:25 MSG cli_mx[4277]: # MonetDB/SQL module loaded

Hai Ariel, Are you still having problem upgrading your database (please don’t forget to make backups beforehand)? The error messages look like your previous upgrade was unfinished, but it might have partial effect on your database. So, when you start it again, the upgrade code is trying to do its work again. The errors don’t look critical to me, since it tried to remove something that not there any more. Since the log said at the end that "MonetDB/SQL module loaded”, it seems to have finished. What’s the status of your database now? Regards, Jennie

...

Is something Im doing wrong ?

I need to upgrade this DB, cause is too big to start doing dump & restore. Any help will be appreciated.

Thks Ariel

On Wed, Oct 23, 2019 at 1:54 PM ferreira wrote: Ok, Aug2018 is an old version, can you upgrade to Apr2019?

On 10/23/19 6:42 PM, Ariel Abadi wrote:

...
ALTER TABLE cubm_201909 DROP TABLE cub8_20190901;

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Ariel Abadi

2:30 p.m.

Hi! I tried to do it a couple of times. Eventhough the message in module loaded.. the truth is that the database is NOT starting anymore. As per somebody else information I would need to dump and restore the entire database which for us is a very painful process. The worst thing, is that we had the chance to upgrade a couple of versions (now we are on 11.31.13) and we are still having the same problem for removing a partition from a Merged Table. So, my doubt/concern is I will do the whole dump and restore, on the new version... and I will have to cross my fingers to see if the problem is still there.... Thks Ariel On Mon, Oct 28, 2019 at 11:24 AM Ying Zhang wrote:

...

...
On 24 Oct 2019, at 13:54, Ariel Abadi wrote:

Hi ! I tried to upgrade from 11.31.7 to the latest version. In order to do that (as I learned in the past, I did it step by step), this means that I have upgraded from one version to another.

11.31.7 -> 11.31.11 OK! 11.31.11 -> 11.31.13 OK!

11.31.13 -> 11.33.3 --> ERROR! The database never started, I run the monetdbd xxx, and started with no problem. BUT when I tried to start the DB (monetdb start -a) ... it never started (it kept thinking for a long time). Even Though I saw it was running .... seems to be "hanged".

After a while I killed it via kill -9 xxxx ... And when I started again I found the following error which made my DB unusable. 2019-10-24 07:22:25 ERR cli_mx[4277]: #main thread:!ERROR:ParseException:SQLparser:42000!DROP PROCEDURE: no such procedure 'querylog_enable' (smallint(16)) 2019-10-24 07:22:25 ERR cli_mx[4277]: !ParseException:SQLparser:42000!DROP PROCEDURE: no such procedure 'querylog_enable' (smallint(16)) 2019-10-24 07:22:25 MSG cli_mx[4277]: # MonetDB/SQL module loaded

Hai Ariel,

Are you still having problem upgrading your database (please don’t forget to make backups beforehand)?

The error messages look like your previous upgrade was unfinished, but it might have partial effect on your database. So, when you start it again, the upgrade code is trying to do its work again. The errors don’t look critical to me, since it tried to remove something that not there any more. Since the log said at the end that "MonetDB/SQL module loaded”, it seems to have finished.

What’s the status of your database now?

Regards, Jennie

...
Is something Im doing wrong ?

I need to upgrade this DB, cause is too big to start doing dump &

...
Any help will be appreciated.

Thks Ariel

On Wed, Oct 23, 2019 at 1:54 PM ferreira <

restore. pedro.ferreira@monetdbsolutions.com> wrote:

...
Ok, Aug2018 is an old version, can you upgrade to Apr2019?

On 10/23/19 6:42 PM, Ariel Abadi wrote:

...
ALTER TABLE cubm_201909 DROP TABLE cub8_20190901;

users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Martin van Dinther

23 Oct 23 Oct

6:15 p.m.

Hi Ariel, An ALTER TABLE cubm_201909 DROP TABLE cub8_20190901; command does not drop the table cub8_20190901 itself, only removes the partition table from a MERGE TABLE set. To drop the cub8_20190901 itself use: DROP TABLE cub8_20190901; See also: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/DataPartitioning However running multiple "ALTER TABLE cubm_201909 DROP TABLE cub8_20190901;" commands sequentially should definitely give an error from the second one onwards. If not, that is a bug. Try reproducing the issue in a clean new test schema or test database. If reproducable, log it in Bugzilla. If not reproducable, potentially your current data dictionary / database is corrupted. Make a dump of the database asap and recreate a second database using the dump. Regards, Martin On 10/23/19 6:42 PM, Ariel Abadi wrote:

...

The steps are very simple...

1) I have created the MERGE TABLE

CREATE MERGE TABLE cubm_201909 AS (SELECT * FROM cub) WITH NO DATA;

2) I have attached the table:

ALTER TABLE cubm_201909 ADD TABLE cub8_20190901;

3) Wanted to REMOVE the table:

ALTER TABLE cubm_201909 DROP TABLE cub8_20190901;

This new MERGE table has inside 30 tables attached, none of them were removed.

We are using MonetDB v11.31.7 (Aug2018)

Thks Ariel

On Wed, Oct 23, 2019 at 1:32 PM ferreira mailto:pedro.ferreira@monetdbsolutions.com> wrote:

Hello Ariel, this looks like a bug. Can you send the reproducible steps?

Best regards,

Pedro

On 10/23/19 6:16 PM, Ariel Abadi wrote:

...
Hi! Can someone help me? Im trying to REMOVE a table from a merge table, when I run the command, it states "operation successful", but the table is NOT removed. Here you can see an example... I have run it several times, but the table is still there :(

image.png

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1959

Age (days ago)

1964

Last active (days ago)

List overview

Download

7 comments

4 participants

participants (4)

Ariel Abadi
ferreira
Martin van Dinther
Ying Zhang

MERGE TABLE - CANNOT DROP PARTITION

tags

participants (4)