DROP BLOB TABLE error

CrateDB
1

When trying to DROP a blob table (v 4.2.6) I receive the following error:

SQLActionException[ClusterBlockException: blocked by: [FORBIDDEN/12/index read-only / allow delete (api)];]

Already tried the following without success:

DROP TABLE "blob"."mytable"
DROP BLOB TABLE "blob"."mytable"

Also tried modifying settings:

ALTER TABLE "blob"."document" SET ("blocks.read_only_allow_delete" = FALSE)
ALTER BLOB TABLE "blob"."document" SET ("blocks.read_only_allow_delete" = FALSE)

But get errors:

SQLActionException[OperationOnInaccessibleRelationException: The relation "blob.mytable" doesn't support or allow ALTER operations.]
or
SQLActionException[SQLParseException: Invalid property "blocks.read_only_allow_delete" passed to [ALTER | CREATE] TABLE statement]
3

Hi @rps-v,

seems you found a bug :lady_beetle: (congrats :sweat_smile:). Unfortunately it seems there is not really a workaround for this either :pensive:

Can you open an issue on crate/crate for this?

If you do not have a GitHub account let me know and I can create the issue for you

Thanks

2 Likes
4

I created an issue on GitHub : Cannot DROP BLOB TABLE · Issue #12534 · crate/crate · GitHub

2 Likes
5

Hi @rps-v,

quick update: a PR to fix this was merged yesterday

It was backported to CrateDB 4.7 and CrateDB 4.8 and will be updated with the next patch release. I expect that a testing release will land in a couple days.

2 Likes
6

Great news !

I would like to update a cluster to the latest release (from 4.2.6) but Crate warns me that a BLOB table needs to be recreated (and I cannot drop it…). Will this be an issue if an update is attempted ?

7

@rps-v Can you share the warning you see regarding BLOB tables needs to be recreated?

We believe this warning is only relevant for major version upgrades (i.e. upgrading to 5.x) and upgrading to 4.7 or 4.8 should work fine. Before upgrading I recommend to create a backup just in case something does not work as expected.

Potentially it’s also worth first restoring this blob table and some sample data on a cloned testing system for a update test-run before updating your production system.

1 Like
8

Here is the warning from Cluster Checks :

The following tables need to be recreated for compatibility with future major versions of CrateDB: [ blob.mytable ] System information — CrateDB: Reference

Will try to update first on a test system and see how it goes.

9

@rps-v: With the following query you can check with which version your table was created:

SELECT 
  table_schema, table_name, version 
FROM information_schema.tables 
WHERE 
  table_schema NOT IN ('pg_catalog', 'sys', 'information_schema') 
LIMIT 100;

When blob.mytable was created with 4.x it should be compatible with all 4.x CrateDB versions.

10

The table was created on Crate 3.x, here is the result:

created: 3.2.0
upgraded: 4.2.6
11

Tables created with 3.x will need to be re-created before upgrading to 5.x. Upgrades to newer minor versions (4.3…4.8) can be done without re-creating the table first.

I would recommend to upgrade from 4.2.6 => 4.3.4 => 4.8.x (fix will be included in upcomping 4.7.3 and 4.8.1)

1 Like
12

FYI: CrateDB 4.7.3 and 4.8.1 are released. The official docker image will likely be available by tomorrow.

2 Likes