You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@cassandra.apache.org by Aaron Ploetz <aa...@gmail.com> on 2022/10/17 20:35:21 UTC

Denylisting with a composite partition key

I have this table definition:

CREATE TABLE stackoverflow.weather_sensor_data (
    city text,
    month int,
    recorded_time timestamp,
    temp float,
    PRIMARY KEY ((city, month), recorded_time)
) WITH CLUSTERING ORDER BY (recorded_time DESC)

Sample data looks like this:

> SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN' AND
month=202111;

 city            | month  | recorded_time                   | temp
-----------------+--------+---------------------------------+------
 Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
 Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
 Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
 Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
 Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2

(5 rows)

Using JMX Term, I've tried to denylist that partition, but I must have the
syntax for composite keys incorrect:

$>bean org.apache.cassandra.db:type=StorageProxy
$>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
MN',202210"
#IllegalArgumentException: Operation denylistKey with 4 parameters doesn't
exist in bean org.apache.cassandra.db:type=StorageProxy

Obviously, it's reading the space between "Minneapolis," and "MN" as a
delimiter.  What's the right way to handle commas, spaces, and composite
keys for this?

Also, is there another way to accomplish this without using JMX?

Thanks,

Aaron

Re: Denylisting with a composite partition key

Posted by Cheng Wang via user <us...@cassandra.apache.org>.

and yes, you need to set the consistency level to ONE in the cassandra.yaml
if it's running in your local machine

denylist_consistency_level: ONE


On Tue, Oct 25, 2022 at 10:41 AM Cheng Wang <ch...@netflix.com> wrote:

> Awesome! That's great to hear!
> Pls feel free to let me know if you have any questions!
>
> Thanks,
> Cheng
>
> On Tue, Oct 25, 2022 at 10:36 AM Aaron Ploetz <aa...@gmail.com>
> wrote:
>
>> Works!
>>
>> So I was running on my *local*, and all of my attempts to add to the
>> denylist were failing because the denylist_consistency_level was set to
>> QUORUM:
>>
>> WARN  [main] 2022-10-25 11:57:27,238 NoSpamLogger.java:108 - Attempting
>> to load denylist and not enough nodes are available for a QUORUM refresh.
>> Reload the denylist when unavailable nodes are recovered to ensure your
>> denylist remains in sync.
>> INFO  [main] 2022-10-25 11:57:27,238 PartitionDenylist.java:163 -
>> Insufficient nodes while loading partition denylist cache. Scheduled retry
>> in 5 seconds.
>>
>> Adjusted that setting to ONE, and now everything is working.
>>
>> Thanks again, Cheng!
>>
>> Aaron
>>
>>
>> On Fri, Oct 21, 2022 at 9:48 AM Aaron Ploetz <aa...@gmail.com>
>> wrote:
>>
>>> Awesome.  Thank you, Cheng!  I’ll give this a shot and let you know.
>>>
>>> Thanks,
>>>
>>> Aaron
>>>
>>>
>>> On Oct 21, 2022, at 12:45 AM, Cheng Wang <ch...@netflix.com> wrote:
>>>
>>> 
>>> Hi Aaron,
>>>
>>> After reading through the code, I finally figured out the issue. So back
>>> to your original question where you failed to run
>>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>>> MN',202210"
>>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>
>>> It's not a Cassandra issue since it failed at the JMX parser stage,
>>> even before it goes to the Cassandra internal StorageProxy::denylistKey
>>> method.
>>> Yes, you got the right gist. It's because of the extra space between
>>> "Minneapolis" and "MN". To avoid the error, you need to add "\\" before the
>>> white space as below :
>>>
>>> $>run denylistKey "stackoverflow" "weather_sensor_data" "Minneapolis,\\
>>> MN:202210"
>>>
>>> #calling operation denylistKey of mbean
>>> org.apache.cassandra.db:type=StorageProxy with params [stackoverflow,
>>> weather_sensor_data, Minneapolis, MN:202210]
>>>
>>> #operation returns:
>>>
>>> true
>>>
>>> And yes, the composite key is delimited by ":" . You can find more
>>> examples from the test code as it used composite keys
>>>
>>> https://github.com/apache/cassandra/blob/trunk/test/unit/org/apache/cassandra/service/PartitionDenylistTest.java
>>>
>>> And then when you select the key, it will get denylisted:
>>>
>>> cqlsh> select ks_name, table_name, key from
>>> system_distributed.partition_denylist;
>>>
>>>
>>>  ks_name       | table_name          | key
>>>
>>>
>>> ---------------+---------------------+------------------------------------------------------
>>>
>>>  stackoverflow | weather_sensor_data |
>>> 0x000f4d696e6e6561706f6c69732c204d4e000004000315e200
>>>
>>>
>>> (2 rows)
>>>
>>> cqlsh> select * from stackoverflow.weather_sensor_data where city =
>>> 'Minneapolis, MN' AND month = 202210;
>>>
>>> InvalidRequest: Error from server: code=2200 [Invalid query]
>>> message="Unable to read denylisted partition
>>> [0xDecoratedKey(8132598509541575594,
>>> 000f4d696e6e6561706f6c69732c204d4e000004000315e200)] in
>>> stackoverflow/weather_sensor_data"
>>>
>>> cqlsh>
>>>
>>>
>>> For your second question why even a single partition key doesn't work,
>>> my apologies my previous answer on direct insert to the s
>>> ystem_distributed.partition_denylist like
>>> insert into system_distributed.partition_denylist (ks_name, table_name,
>>> key) values ('stackoverflow', 'weather_sensor_data',
>>> textAsBlob('''Minneapolis, MN'', 202210'));
>>> is wrong. It's because the way Cassandra stores the key in the system
>>> table is it converts the string into a hexadecimal literal. You can find
>>> the source code here
>>>
>>> https://github.com/apache/cassandra/blob/trunk/src/java/org/apache/cassandra/schema/PartitionDenylist.java
>>>  And check out the function addKeyToDenylist
>>> final String insert = String.format("INSERT INTO system_distributed.
>>> partition_denylist (ks_name, table_name, key) VALUES ('%s', '%s', 0x%s)"
>>> ,
>>> keyspace, table, ByteBufferUtil.bytesToHex(key));
>>>
>>> You can see that it's different from the way of textAsBlob. A better
>>> (yet general) explanation is here
>>>
>>> https://stackoverflow.com/questions/31994880/why-cassandra-cql-query-with-hexadecimal-literal-works-but-textasbinary-does-not
>>>
>>> So... the short answer is, I get back my previous answer. My apologies!
>>> It's not recommended to directly insert rows into
>>> the system_distributed.partition_denylist table.You'd better go with the
>>> JMX interface since it will go to the StorageProxy which handles more logic
>>> like the hex string conversion and other error handling stuff.
>>>
>>> Hope the answer is helpful! Pls feel free to let me know if you have any
>>> questions!
>>>
>>> Thanks,
>>> Cheng
>>>
>>>
>>> On Thu, Oct 20, 2022 at 7:07 AM Aaron Ploetz <aa...@gmail.com>
>>> wrote:
>>>
>>>> No worries, Cheng!
>>>>
>>>> So I actually pivoted a little and adjusted my example table to use a
>>>> single integer-based partition key.
>>>>
>>>> aaron@cqlsh:stackoverflow> SELECT ks_name, table_name, blobAsint(key)
>>>> FROM system_distributed.partition_denylist WHERE ks_name='stackoverflow'
>>>> AND table_name='weather_sensor_data_by_month';
>>>>
>>>>  ks_name       | table_name                   | system.blobasint(key)
>>>> ---------------+------------------------------+-----------------------
>>>>  stackoverflow | weather_sensor_data_by_month |                202210
>>>>
>>>> But even with the yaml changes, it still allows me to SELECT that
>>>> partition.
>>>>
>>>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data_by_month
>>>> WHERE month=202210 LIMIT 1;
>>>>
>>>>  month  | city           | recorded_time                   | temp
>>>> --------+----------------+---------------------------------+------
>>>>  202210 | Minneapolis,MN | 2022-10-17 11:30:00.000000+0000 |    1
>>>>
>>>> Just wondering what I should do to get it to deny access?
>>>>
>>>> Thanks,
>>>>
>>>> Aaron
>>>>
>>>>
>>>>
>>>> On Wed, Oct 19, 2022 at 5:34 PM Cheng Wang <ch...@netflix.com> wrote:
>>>>
>>>>> Hi Aaron,
>>>>>
>>>>> Sorry for the late reply, was dealing with a production issue (maybe
>>>>> another topic for Cassandra Summit :-)). Are you running on your local
>>>>> machine? Then yes, you do need to enable the config for all the following
>>>>>
>>>>> enable_partition_denylist: true
>>>>>
>>>>> enable_denylist_writes: true
>>>>> enable_denylist_reads: true
>>>>> enable_denylist_range_reads: true
>>>>>
>>>>> I am not sure enable_partition_denylist will enable for the rest of
>>>>> three @Jordan West <jo...@netflix.com>  maybe you can confirm for
>>>>> that? But it's better to enable for all just to be safe.
>>>>> I will play by my side and get back to you soon about the
>>>>> composite keys.
>>>>>
>>>>> Thanks
>>>>> Cheng
>>>>>
>>>>> On Wed, Oct 19, 2022 at 1:42 PM Aaron Ploetz <aa...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Just checking, but for this to work, do I have to mess with these
>>>>>> settings in the YAML at all?
>>>>>>
>>>>>> partition_denylist_enabled: true
>>>>>> denylist_reads_enabled: true
>>>>>>
>>>>>> They're commented out by default.
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Aaron
>>>>>>
>>>>>>
>>>>>> On Mon, Oct 17, 2022 at 4:53 PM Aaron Ploetz <aa...@gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Thanks for the help with the INSERT, Cheng!  I'm further along than
>>>>>>> before.  But it still must not be matching up quite right, because I can
>>>>>>> still select that partition.
>>>>>>>
>>>>>>> I have several different combinations of the two keys (and I removed
>>>>>>> the space) of "Minneapolis,MN" and 202210.  Here's what I've got out there
>>>>>>> so far:
>>>>>>>
>>>>>>> aaron@cqlsh:stackoverflow> select ks_name, table_name,
>>>>>>> blobAsText(key) from system_distributed.partition_denylist;
>>>>>>>
>>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>>> ---------------+---------------------+--------------------------
>>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis,MN', 202210
>>>>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN',202210
>>>>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN':202210
>>>>>>>  stackoverflow | weather_sensor_data |   Minneapolis,MN, 202210
>>>>>>>  stackoverflow | weather_sensor_data |     Minneapolis,MN202210
>>>>>>>  stackoverflow | weather_sensor_data |    Minneapolis,MN:202210
>>>>>>>
>>>>>>> (6 rows)
>>>>>>>
>>>>>>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data WHERE
>>>>>>> city='Minneapolis,MN' AND month=202210;
>>>>>>>
>>>>>>>  city           | month  | recorded_time                   | temp
>>>>>>> ----------------+--------+---------------------------------+------
>>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:30:00.000000+0000 |    1
>>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:25:00.000000+0000 |    1
>>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:20:00.000000+0000 |    1
>>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:15:00.000000+0000 |    1
>>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:10:00.000000+0000 |    2
>>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:05:00.000000+0000 |    2
>>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:00:00.000000+0000 |    2
>>>>>>>
>>>>>>> (7 rows)
>>>>>>>
>>>>>>> As you can see, I can still select the partition.  I was really
>>>>>>> hoping one of those combinations would do it.
>>>>>>>
>>>>>>> Looking at the StorageProxyTest.java in the project, I saw that it
>>>>>>> was delimited by a colon ":", which is why I tried that, too.
>>>>>>>
>>>>>>> Still looking for the right way to enter both of those keys.
>>>>>>>
>>>>>>> Thanks,
>>>>>>>
>>>>>>> Aaron
>>>>>>>
>>>>>>>
>>>>>>> On Mon, Oct 17, 2022 at 4:40 PM Cheng Wang <ch...@netflix.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Another approach is, instead of using $$, you can put additional
>>>>>>>> pair of single quote around the 'Minneapolis, MN'
>>>>>>>>
>>>>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>>>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>>>>>> textAsBlob('''Minneapolis, MN'', 202210'));
>>>>>>>>
>>>>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>>>>>> system_distributed.partition_denylist;
>>>>>>>>
>>>>>>>>
>>>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>>>>
>>>>>>>> ---------------+---------------------+---------------------------
>>>>>>>>
>>>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>>>>
>>>>>>>> On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> Hi Aaron,
>>>>>>>>>
>>>>>>>>> Yes, you can directly insert into the system_distributed.partition_denylist
>>>>>>>>> instead of using JMX. Jordan wrote a blog post for denylist
>>>>>>>>>
>>>>>>>>> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>>>>>>>>>
>>>>>>>>> And the syntax error, one way around is to put $$ around like below
>>>>>>>>>
>>>>>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>>>>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>>>>>>> textAsBlob($$'Minneapolis, MN', 202210$$));
>>>>>>>>>
>>>>>>>>> There is post about this
>>>>>>>>>
>>>>>>>>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
>>>>>>>>> .
>>>>>>>>>
>>>>>>>>> and then you can verify the insert by doing
>>>>>>>>>
>>>>>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>>>>>>> system_distributed.partition_denylist;
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>>>>>
>>>>>>>>> ---------------+---------------------+---------------------------
>>>>>>>>>
>>>>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>>>>> Pls let me know if it works for you.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <
>>>>>>>>> aaronploetz@gmail.com> wrote:
>>>>>>>>>
>>>>>>>>>> I have this table definition:
>>>>>>>>>>
>>>>>>>>>> CREATE TABLE stackoverflow.weather_sensor_data (
>>>>>>>>>>     city text,
>>>>>>>>>>     month int,
>>>>>>>>>>     recorded_time timestamp,
>>>>>>>>>>     temp float,
>>>>>>>>>>     PRIMARY KEY ((city, month), recorded_time)
>>>>>>>>>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>>>>>>>>>
>>>>>>>>>> Sample data looks like this:
>>>>>>>>>>
>>>>>>>>>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN'
>>>>>>>>>> AND month=202111;
>>>>>>>>>>
>>>>>>>>>>  city            | month  | recorded_time                   | temp
>>>>>>>>>>
>>>>>>>>>> -----------------+--------+---------------------------------+------
>>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>>>>>>>>>
>>>>>>>>>> (5 rows)
>>>>>>>>>>
>>>>>>>>>> Using JMX Term, I've tried to denylist that partition, but I must
>>>>>>>>>> have the syntax for composite keys incorrect:
>>>>>>>>>>
>>>>>>>>>> $>bean org.apache.cassandra.db:type=StorageProxy
>>>>>>>>>> $>run denylistKey stackoverflow weather_sensor_data
>>>>>>>>>> "'Minneapolis, MN',202210"
>>>>>>>>>> #IllegalArgumentException: Operation denylistKey with 4
>>>>>>>>>> parameters doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>>>>>>>>
>>>>>>>>>> Obviously, it's reading the space between "Minneapolis," and "MN"
>>>>>>>>>> as a delimiter.  What's the right way to handle commas, spaces, and
>>>>>>>>>> composite keys for this?
>>>>>>>>>>
>>>>>>>>>> Also, is there another way to accomplish this without using JMX?
>>>>>>>>>>
>>>>>>>>>> Thanks,
>>>>>>>>>>
>>>>>>>>>> Aaron
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>

Re: Denylisting with a composite partition key

Posted by Cheng Wang via user <us...@cassandra.apache.org>.

Awesome! That's great to hear!
Pls feel free to let me know if you have any questions!

Thanks,
Cheng

On Tue, Oct 25, 2022 at 10:36 AM Aaron Ploetz <aa...@gmail.com> wrote:

> Works!
>
> So I was running on my *local*, and all of my attempts to add to the
> denylist were failing because the denylist_consistency_level was set to
> QUORUM:
>
> WARN  [main] 2022-10-25 11:57:27,238 NoSpamLogger.java:108 - Attempting to
> load denylist and not enough nodes are available for a QUORUM refresh.
> Reload the denylist when unavailable nodes are recovered to ensure your
> denylist remains in sync.
> INFO  [main] 2022-10-25 11:57:27,238 PartitionDenylist.java:163 -
> Insufficient nodes while loading partition denylist cache. Scheduled retry
> in 5 seconds.
>
> Adjusted that setting to ONE, and now everything is working.
>
> Thanks again, Cheng!
>
> Aaron
>
>
> On Fri, Oct 21, 2022 at 9:48 AM Aaron Ploetz <aa...@gmail.com>
> wrote:
>
>> Awesome.  Thank you, Cheng!  I’ll give this a shot and let you know.
>>
>> Thanks,
>>
>> Aaron
>>
>>
>> On Oct 21, 2022, at 12:45 AM, Cheng Wang <ch...@netflix.com> wrote:
>>
>> 
>> Hi Aaron,
>>
>> After reading through the code, I finally figured out the issue. So back
>> to your original question where you failed to run
>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>> MN',202210"
>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>
>> It's not a Cassandra issue since it failed at the JMX parser stage,  even
>> before it goes to the Cassandra internal StorageProxy::denylistKey method.
>> Yes, you got the right gist. It's because of the extra space between
>> "Minneapolis" and "MN". To avoid the error, you need to add "\\" before the
>> white space as below :
>>
>> $>run denylistKey "stackoverflow" "weather_sensor_data" "Minneapolis,\\
>> MN:202210"
>>
>> #calling operation denylistKey of mbean
>> org.apache.cassandra.db:type=StorageProxy with params [stackoverflow,
>> weather_sensor_data, Minneapolis, MN:202210]
>>
>> #operation returns:
>>
>> true
>>
>> And yes, the composite key is delimited by ":" . You can find more
>> examples from the test code as it used composite keys
>>
>> https://github.com/apache/cassandra/blob/trunk/test/unit/org/apache/cassandra/service/PartitionDenylistTest.java
>>
>> And then when you select the key, it will get denylisted:
>>
>> cqlsh> select ks_name, table_name, key from
>> system_distributed.partition_denylist;
>>
>>
>>  ks_name       | table_name          | key
>>
>>
>> ---------------+---------------------+------------------------------------------------------
>>
>>  stackoverflow | weather_sensor_data |
>> 0x000f4d696e6e6561706f6c69732c204d4e000004000315e200
>>
>>
>> (2 rows)
>>
>> cqlsh> select * from stackoverflow.weather_sensor_data where city =
>> 'Minneapolis, MN' AND month = 202210;
>>
>> InvalidRequest: Error from server: code=2200 [Invalid query]
>> message="Unable to read denylisted partition
>> [0xDecoratedKey(8132598509541575594,
>> 000f4d696e6e6561706f6c69732c204d4e000004000315e200)] in
>> stackoverflow/weather_sensor_data"
>>
>> cqlsh>
>>
>>
>> For your second question why even a single partition key doesn't work, my
>> apologies my previous answer on direct insert to the s
>> ystem_distributed.partition_denylist like
>> insert into system_distributed.partition_denylist (ks_name, table_name,
>> key) values ('stackoverflow', 'weather_sensor_data',
>> textAsBlob('''Minneapolis, MN'', 202210'));
>> is wrong. It's because the way Cassandra stores the key in the system
>> table is it converts the string into a hexadecimal literal. You can find
>> the source code here
>>
>> https://github.com/apache/cassandra/blob/trunk/src/java/org/apache/cassandra/schema/PartitionDenylist.java
>>  And check out the function addKeyToDenylist
>> final String insert = String.format("INSERT INTO system_distributed.
>> partition_denylist (ks_name, table_name, key) VALUES ('%s', '%s', 0x%s)",
>> keyspace, table, ByteBufferUtil.bytesToHex(key));
>>
>> You can see that it's different from the way of textAsBlob. A better (yet
>> general) explanation is here
>>
>> https://stackoverflow.com/questions/31994880/why-cassandra-cql-query-with-hexadecimal-literal-works-but-textasbinary-does-not
>>
>> So... the short answer is, I get back my previous answer. My apologies!
>> It's not recommended to directly insert rows into
>> the system_distributed.partition_denylist table.You'd better go with the
>> JMX interface since it will go to the StorageProxy which handles more logic
>> like the hex string conversion and other error handling stuff.
>>
>> Hope the answer is helpful! Pls feel free to let me know if you have any
>> questions!
>>
>> Thanks,
>> Cheng
>>
>>
>> On Thu, Oct 20, 2022 at 7:07 AM Aaron Ploetz <aa...@gmail.com>
>> wrote:
>>
>>> No worries, Cheng!
>>>
>>> So I actually pivoted a little and adjusted my example table to use a
>>> single integer-based partition key.
>>>
>>> aaron@cqlsh:stackoverflow> SELECT ks_name, table_name, blobAsint(key)
>>> FROM system_distributed.partition_denylist WHERE ks_name='stackoverflow'
>>> AND table_name='weather_sensor_data_by_month';
>>>
>>>  ks_name       | table_name                   | system.blobasint(key)
>>> ---------------+------------------------------+-----------------------
>>>  stackoverflow | weather_sensor_data_by_month |                202210
>>>
>>> But even with the yaml changes, it still allows me to SELECT that
>>> partition.
>>>
>>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data_by_month
>>> WHERE month=202210 LIMIT 1;
>>>
>>>  month  | city           | recorded_time                   | temp
>>> --------+----------------+---------------------------------+------
>>>  202210 | Minneapolis,MN | 2022-10-17 11:30:00.000000+0000 |    1
>>>
>>> Just wondering what I should do to get it to deny access?
>>>
>>> Thanks,
>>>
>>> Aaron
>>>
>>>
>>>
>>> On Wed, Oct 19, 2022 at 5:34 PM Cheng Wang <ch...@netflix.com> wrote:
>>>
>>>> Hi Aaron,
>>>>
>>>> Sorry for the late reply, was dealing with a production issue (maybe
>>>> another topic for Cassandra Summit :-)). Are you running on your local
>>>> machine? Then yes, you do need to enable the config for all the following
>>>>
>>>> enable_partition_denylist: true
>>>>
>>>> enable_denylist_writes: true
>>>> enable_denylist_reads: true
>>>> enable_denylist_range_reads: true
>>>>
>>>> I am not sure enable_partition_denylist will enable for the rest of
>>>> three @Jordan West <jo...@netflix.com>  maybe you can confirm for
>>>> that? But it's better to enable for all just to be safe.
>>>> I will play by my side and get back to you soon about the
>>>> composite keys.
>>>>
>>>> Thanks
>>>> Cheng
>>>>
>>>> On Wed, Oct 19, 2022 at 1:42 PM Aaron Ploetz <aa...@gmail.com>
>>>> wrote:
>>>>
>>>>> Just checking, but for this to work, do I have to mess with these
>>>>> settings in the YAML at all?
>>>>>
>>>>> partition_denylist_enabled: true
>>>>> denylist_reads_enabled: true
>>>>>
>>>>> They're commented out by default.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Aaron
>>>>>
>>>>>
>>>>> On Mon, Oct 17, 2022 at 4:53 PM Aaron Ploetz <aa...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Thanks for the help with the INSERT, Cheng!  I'm further along than
>>>>>> before.  But it still must not be matching up quite right, because I can
>>>>>> still select that partition.
>>>>>>
>>>>>> I have several different combinations of the two keys (and I removed
>>>>>> the space) of "Minneapolis,MN" and 202210.  Here's what I've got out there
>>>>>> so far:
>>>>>>
>>>>>> aaron@cqlsh:stackoverflow> select ks_name, table_name,
>>>>>> blobAsText(key) from system_distributed.partition_denylist;
>>>>>>
>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>> ---------------+---------------------+--------------------------
>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis,MN', 202210
>>>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN',202210
>>>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN':202210
>>>>>>  stackoverflow | weather_sensor_data |   Minneapolis,MN, 202210
>>>>>>  stackoverflow | weather_sensor_data |     Minneapolis,MN202210
>>>>>>  stackoverflow | weather_sensor_data |    Minneapolis,MN:202210
>>>>>>
>>>>>> (6 rows)
>>>>>>
>>>>>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data WHERE
>>>>>> city='Minneapolis,MN' AND month=202210;
>>>>>>
>>>>>>  city           | month  | recorded_time                   | temp
>>>>>> ----------------+--------+---------------------------------+------
>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:30:00.000000+0000 |    1
>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:25:00.000000+0000 |    1
>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:20:00.000000+0000 |    1
>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:15:00.000000+0000 |    1
>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:10:00.000000+0000 |    2
>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:05:00.000000+0000 |    2
>>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:00:00.000000+0000 |    2
>>>>>>
>>>>>> (7 rows)
>>>>>>
>>>>>> As you can see, I can still select the partition.  I was really
>>>>>> hoping one of those combinations would do it.
>>>>>>
>>>>>> Looking at the StorageProxyTest.java in the project, I saw that it
>>>>>> was delimited by a colon ":", which is why I tried that, too.
>>>>>>
>>>>>> Still looking for the right way to enter both of those keys.
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Aaron
>>>>>>
>>>>>>
>>>>>> On Mon, Oct 17, 2022 at 4:40 PM Cheng Wang <ch...@netflix.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Another approach is, instead of using $$, you can put additional
>>>>>>> pair of single quote around the 'Minneapolis, MN'
>>>>>>>
>>>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>>>>> textAsBlob('''Minneapolis, MN'', 202210'));
>>>>>>>
>>>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>>>>> system_distributed.partition_denylist;
>>>>>>>
>>>>>>>
>>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>>>
>>>>>>> ---------------+---------------------+---------------------------
>>>>>>>
>>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>>>
>>>>>>> On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi Aaron,
>>>>>>>>
>>>>>>>> Yes, you can directly insert into the system_distributed.partition_denylist
>>>>>>>> instead of using JMX. Jordan wrote a blog post for denylist
>>>>>>>>
>>>>>>>> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>>>>>>>>
>>>>>>>> And the syntax error, one way around is to put $$ around like below
>>>>>>>>
>>>>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>>>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>>>>>> textAsBlob($$'Minneapolis, MN', 202210$$));
>>>>>>>>
>>>>>>>> There is post about this
>>>>>>>>
>>>>>>>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
>>>>>>>> .
>>>>>>>>
>>>>>>>> and then you can verify the insert by doing
>>>>>>>>
>>>>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>>>>>> system_distributed.partition_denylist;
>>>>>>>>
>>>>>>>>
>>>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>>>>
>>>>>>>> ---------------+---------------------+---------------------------
>>>>>>>>
>>>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>>>> Pls let me know if it works for you.
>>>>>>>>
>>>>>>>>
>>>>>>>> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> I have this table definition:
>>>>>>>>>
>>>>>>>>> CREATE TABLE stackoverflow.weather_sensor_data (
>>>>>>>>>     city text,
>>>>>>>>>     month int,
>>>>>>>>>     recorded_time timestamp,
>>>>>>>>>     temp float,
>>>>>>>>>     PRIMARY KEY ((city, month), recorded_time)
>>>>>>>>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>>>>>>>>
>>>>>>>>> Sample data looks like this:
>>>>>>>>>
>>>>>>>>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN'
>>>>>>>>> AND month=202111;
>>>>>>>>>
>>>>>>>>>  city            | month  | recorded_time                   | temp
>>>>>>>>> -----------------+--------+---------------------------------+------
>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>>>>>>>>
>>>>>>>>> (5 rows)
>>>>>>>>>
>>>>>>>>> Using JMX Term, I've tried to denylist that partition, but I must
>>>>>>>>> have the syntax for composite keys incorrect:
>>>>>>>>>
>>>>>>>>> $>bean org.apache.cassandra.db:type=StorageProxy
>>>>>>>>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>>>>>>>>> MN',202210"
>>>>>>>>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>>>>>>>>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>>>>>>>
>>>>>>>>> Obviously, it's reading the space between "Minneapolis," and "MN"
>>>>>>>>> as a delimiter.  What's the right way to handle commas, spaces, and
>>>>>>>>> composite keys for this?
>>>>>>>>>
>>>>>>>>> Also, is there another way to accomplish this without using JMX?
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>>
>>>>>>>>> Aaron
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>

Re: Denylisting with a composite partition key

Posted by Aaron Ploetz <aa...@gmail.com>.

Works!

So I was running on my *local*, and all of my attempts to add to the
denylist were failing because the denylist_consistency_level was set to
QUORUM:

WARN  [main] 2022-10-25 11:57:27,238 NoSpamLogger.java:108 - Attempting to
load denylist and not enough nodes are available for a QUORUM refresh.
Reload the denylist when unavailable nodes are recovered to ensure your
denylist remains in sync.
INFO  [main] 2022-10-25 11:57:27,238 PartitionDenylist.java:163 -
Insufficient nodes while loading partition denylist cache. Scheduled retry
in 5 seconds.

Adjusted that setting to ONE, and now everything is working.

Thanks again, Cheng!

Aaron


On Fri, Oct 21, 2022 at 9:48 AM Aaron Ploetz <aa...@gmail.com> wrote:

> Awesome.  Thank you, Cheng!  I’ll give this a shot and let you know.
>
> Thanks,
>
> Aaron
>
>
> On Oct 21, 2022, at 12:45 AM, Cheng Wang <ch...@netflix.com> wrote:
>
> 
> Hi Aaron,
>
> After reading through the code, I finally figured out the issue. So back
> to your original question where you failed to run
> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
> MN',202210"
> #IllegalArgumentException: Operation denylistKey with 4 parameters doesn't
> exist in bean org.apache.cassandra.db:type=StorageProxy
>
> It's not a Cassandra issue since it failed at the JMX parser stage,  even
> before it goes to the Cassandra internal StorageProxy::denylistKey method.
> Yes, you got the right gist. It's because of the extra space between
> "Minneapolis" and "MN". To avoid the error, you need to add "\\" before the
> white space as below :
>
> $>run denylistKey "stackoverflow" "weather_sensor_data" "Minneapolis,\\
> MN:202210"
>
> #calling operation denylistKey of mbean
> org.apache.cassandra.db:type=StorageProxy with params [stackoverflow,
> weather_sensor_data, Minneapolis, MN:202210]
>
> #operation returns:
>
> true
>
> And yes, the composite key is delimited by ":" . You can find more
> examples from the test code as it used composite keys
>
> https://github.com/apache/cassandra/blob/trunk/test/unit/org/apache/cassandra/service/PartitionDenylistTest.java
>
> And then when you select the key, it will get denylisted:
>
> cqlsh> select ks_name, table_name, key from
> system_distributed.partition_denylist;
>
>
>  ks_name       | table_name          | key
>
>
> ---------------+---------------------+------------------------------------------------------
>
>  stackoverflow | weather_sensor_data |
> 0x000f4d696e6e6561706f6c69732c204d4e000004000315e200
>
>
> (2 rows)
>
> cqlsh> select * from stackoverflow.weather_sensor_data where city =
> 'Minneapolis, MN' AND month = 202210;
>
> InvalidRequest: Error from server: code=2200 [Invalid query]
> message="Unable to read denylisted partition
> [0xDecoratedKey(8132598509541575594,
> 000f4d696e6e6561706f6c69732c204d4e000004000315e200)] in
> stackoverflow/weather_sensor_data"
>
> cqlsh>
>
>
> For your second question why even a single partition key doesn't work, my
> apologies my previous answer on direct insert to the s
> ystem_distributed.partition_denylist like
> insert into system_distributed.partition_denylist (ks_name, table_name,
> key) values ('stackoverflow', 'weather_sensor_data',
> textAsBlob('''Minneapolis, MN'', 202210'));
> is wrong. It's because the way Cassandra stores the key in the system
> table is it converts the string into a hexadecimal literal. You can find
> the source code here
>
> https://github.com/apache/cassandra/blob/trunk/src/java/org/apache/cassandra/schema/PartitionDenylist.java
>  And check out the function addKeyToDenylist
> final String insert = String.format("INSERT INTO system_distributed.
> partition_denylist (ks_name, table_name, key) VALUES ('%s', '%s', 0x%s)",
> keyspace, table, ByteBufferUtil.bytesToHex(key));
>
> You can see that it's different from the way of textAsBlob. A better (yet
> general) explanation is here
>
> https://stackoverflow.com/questions/31994880/why-cassandra-cql-query-with-hexadecimal-literal-works-but-textasbinary-does-not
>
> So... the short answer is, I get back my previous answer. My apologies!
> It's not recommended to directly insert rows into
> the system_distributed.partition_denylist table.You'd better go with the
> JMX interface since it will go to the StorageProxy which handles more logic
> like the hex string conversion and other error handling stuff.
>
> Hope the answer is helpful! Pls feel free to let me know if you have any
> questions!
>
> Thanks,
> Cheng
>
>
> On Thu, Oct 20, 2022 at 7:07 AM Aaron Ploetz <aa...@gmail.com>
> wrote:
>
>> No worries, Cheng!
>>
>> So I actually pivoted a little and adjusted my example table to use a
>> single integer-based partition key.
>>
>> aaron@cqlsh:stackoverflow> SELECT ks_name, table_name, blobAsint(key)
>> FROM system_distributed.partition_denylist WHERE ks_name='stackoverflow'
>> AND table_name='weather_sensor_data_by_month';
>>
>>  ks_name       | table_name                   | system.blobasint(key)
>> ---------------+------------------------------+-----------------------
>>  stackoverflow | weather_sensor_data_by_month |                202210
>>
>> But even with the yaml changes, it still allows me to SELECT that
>> partition.
>>
>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data_by_month
>> WHERE month=202210 LIMIT 1;
>>
>>  month  | city           | recorded_time                   | temp
>> --------+----------------+---------------------------------+------
>>  202210 | Minneapolis,MN | 2022-10-17 11:30:00.000000+0000 |    1
>>
>> Just wondering what I should do to get it to deny access?
>>
>> Thanks,
>>
>> Aaron
>>
>>
>>
>> On Wed, Oct 19, 2022 at 5:34 PM Cheng Wang <ch...@netflix.com> wrote:
>>
>>> Hi Aaron,
>>>
>>> Sorry for the late reply, was dealing with a production issue (maybe
>>> another topic for Cassandra Summit :-)). Are you running on your local
>>> machine? Then yes, you do need to enable the config for all the following
>>>
>>> enable_partition_denylist: true
>>>
>>> enable_denylist_writes: true
>>> enable_denylist_reads: true
>>> enable_denylist_range_reads: true
>>>
>>> I am not sure enable_partition_denylist will enable for the rest of
>>> three @Jordan West <jo...@netflix.com>  maybe you can confirm for
>>> that? But it's better to enable for all just to be safe.
>>> I will play by my side and get back to you soon about the
>>> composite keys.
>>>
>>> Thanks
>>> Cheng
>>>
>>> On Wed, Oct 19, 2022 at 1:42 PM Aaron Ploetz <aa...@gmail.com>
>>> wrote:
>>>
>>>> Just checking, but for this to work, do I have to mess with these
>>>> settings in the YAML at all?
>>>>
>>>> partition_denylist_enabled: true
>>>> denylist_reads_enabled: true
>>>>
>>>> They're commented out by default.
>>>>
>>>> Thanks,
>>>>
>>>> Aaron
>>>>
>>>>
>>>> On Mon, Oct 17, 2022 at 4:53 PM Aaron Ploetz <aa...@gmail.com>
>>>> wrote:
>>>>
>>>>> Thanks for the help with the INSERT, Cheng!  I'm further along than
>>>>> before.  But it still must not be matching up quite right, because I can
>>>>> still select that partition.
>>>>>
>>>>> I have several different combinations of the two keys (and I removed
>>>>> the space) of "Minneapolis,MN" and 202210.  Here's what I've got out there
>>>>> so far:
>>>>>
>>>>> aaron@cqlsh:stackoverflow> select ks_name, table_name,
>>>>> blobAsText(key) from system_distributed.partition_denylist;
>>>>>
>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>> ---------------+---------------------+--------------------------
>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis,MN', 202210
>>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN',202210
>>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN':202210
>>>>>  stackoverflow | weather_sensor_data |   Minneapolis,MN, 202210
>>>>>  stackoverflow | weather_sensor_data |     Minneapolis,MN202210
>>>>>  stackoverflow | weather_sensor_data |    Minneapolis,MN:202210
>>>>>
>>>>> (6 rows)
>>>>>
>>>>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data WHERE
>>>>> city='Minneapolis,MN' AND month=202210;
>>>>>
>>>>>  city           | month  | recorded_time                   | temp
>>>>> ----------------+--------+---------------------------------+------
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:30:00.000000+0000 |    1
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:25:00.000000+0000 |    1
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:20:00.000000+0000 |    1
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:15:00.000000+0000 |    1
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:10:00.000000+0000 |    2
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:05:00.000000+0000 |    2
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:00:00.000000+0000 |    2
>>>>>
>>>>> (7 rows)
>>>>>
>>>>> As you can see, I can still select the partition.  I was really hoping
>>>>> one of those combinations would do it.
>>>>>
>>>>> Looking at the StorageProxyTest.java in the project, I saw that it was
>>>>> delimited by a colon ":", which is why I tried that, too.
>>>>>
>>>>> Still looking for the right way to enter both of those keys.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Aaron
>>>>>
>>>>>
>>>>> On Mon, Oct 17, 2022 at 4:40 PM Cheng Wang <ch...@netflix.com> wrote:
>>>>>
>>>>>> Another approach is, instead of using $$, you can put additional pair
>>>>>> of single quote around the 'Minneapolis, MN'
>>>>>>
>>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>>>> textAsBlob('''Minneapolis, MN'', 202210'));
>>>>>>
>>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>>>> system_distributed.partition_denylist;
>>>>>>
>>>>>>
>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>>
>>>>>> ---------------+---------------------+---------------------------
>>>>>>
>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>>
>>>>>> On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi Aaron,
>>>>>>>
>>>>>>> Yes, you can directly insert into the system_distributed.partition_denylist
>>>>>>> instead of using JMX. Jordan wrote a blog post for denylist
>>>>>>>
>>>>>>> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>>>>>>>
>>>>>>> And the syntax error, one way around is to put $$ around like below
>>>>>>>
>>>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>>>>> textAsBlob($$'Minneapolis, MN', 202210$$));
>>>>>>>
>>>>>>> There is post about this
>>>>>>>
>>>>>>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
>>>>>>> .
>>>>>>>
>>>>>>> and then you can verify the insert by doing
>>>>>>>
>>>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>>>>> system_distributed.partition_denylist;
>>>>>>>
>>>>>>>
>>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>>>
>>>>>>> ---------------+---------------------+---------------------------
>>>>>>>
>>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>>> Pls let me know if it works for you.
>>>>>>>
>>>>>>>
>>>>>>> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> I have this table definition:
>>>>>>>>
>>>>>>>> CREATE TABLE stackoverflow.weather_sensor_data (
>>>>>>>>     city text,
>>>>>>>>     month int,
>>>>>>>>     recorded_time timestamp,
>>>>>>>>     temp float,
>>>>>>>>     PRIMARY KEY ((city, month), recorded_time)
>>>>>>>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>>>>>>>
>>>>>>>> Sample data looks like this:
>>>>>>>>
>>>>>>>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN'
>>>>>>>> AND month=202111;
>>>>>>>>
>>>>>>>>  city            | month  | recorded_time                   | temp
>>>>>>>> -----------------+--------+---------------------------------+------
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>>>>>>>
>>>>>>>> (5 rows)
>>>>>>>>
>>>>>>>> Using JMX Term, I've tried to denylist that partition, but I must
>>>>>>>> have the syntax for composite keys incorrect:
>>>>>>>>
>>>>>>>> $>bean org.apache.cassandra.db:type=StorageProxy
>>>>>>>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>>>>>>>> MN',202210"
>>>>>>>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>>>>>>>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>>>>>>
>>>>>>>> Obviously, it's reading the space between "Minneapolis," and "MN"
>>>>>>>> as a delimiter.  What's the right way to handle commas, spaces, and
>>>>>>>> composite keys for this?
>>>>>>>>
>>>>>>>> Also, is there another way to accomplish this without using JMX?
>>>>>>>>
>>>>>>>> Thanks,
>>>>>>>>
>>>>>>>> Aaron
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>

Re: Denylisting with a composite partition key

Posted by Aaron Ploetz <aa...@gmail.com>.

Awesome.  Thank you, Cheng!  I’ll give this a shot and let you know.

Thanks,

Aaron


> On Oct 21, 2022, at 12:45 AM, Cheng Wang <ch...@netflix.com> wrote:
> 
> 
> Hi Aaron,
> 
> After reading through the code, I finally figured out the issue. So back to your original question where you failed to run 
> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis, MN',202210"
> #IllegalArgumentException: Operation denylistKey with 4 parameters doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
> 
> It's not a Cassandra issue since it failed at the JMX parser stage,  even before it goes to the Cassandra internal StorageProxy::denylistKey method. 
> Yes, you got the right gist. It's because of the extra space between "Minneapolis" and "MN". To avoid the error, you need to add "\\" before the white space as below :
> 
> $>run denylistKey "stackoverflow" "weather_sensor_data" "Minneapolis,\\ MN:202210"
> #calling operation denylistKey of mbean org.apache.cassandra.db:type=StorageProxy with params [stackoverflow, weather_sensor_data, Minneapolis, MN:202210]
> #operation returns: 
> true
> 
> And yes, the composite key is delimited by ":" . You can find more examples from the test code as it used composite keys
> https://github.com/apache/cassandra/blob/trunk/test/unit/org/apache/cassandra/service/PartitionDenylistTest.java
> 
> And then when you select the key, it will get denylisted:
> cqlsh> select ks_name, table_name, key from system_distributed.partition_denylist;
> 
>  ks_name       | table_name          | key
> ---------------+---------------------+------------------------------------------------------
>  stackoverflow | weather_sensor_data | 0x000f4d696e6e6561706f6c69732c204d4e000004000315e200
> 
> (2 rows)
> cqlsh> select * from stackoverflow.weather_sensor_data where city = 'Minneapolis, MN' AND month = 202210;
> InvalidRequest: Error from server: code=2200 [Invalid query] message="Unable to read denylisted partition [0xDecoratedKey(8132598509541575594, 000f4d696e6e6561706f6c69732c204d4e000004000315e200)] in stackoverflow/weather_sensor_data"
> cqlsh> 
> 
> 
> For your second question why even a single partition key doesn't work, my apologies my previous answer on direct insert to the system_distributed.partition_denylist like
> insert into system_distributed.partition_denylist (ks_name, table_name, key) values ('stackoverflow', 'weather_sensor_data', textAsBlob('''Minneapolis, MN'', 202210'));
> is wrong. It's because the way Cassandra stores the key in the system table is it converts the string into a hexadecimal literal. You can find the source code here
> https://github.com/apache/cassandra/blob/trunk/src/java/org/apache/cassandra/schema/PartitionDenylist.java
>  And check out the function addKeyToDenylist
>  final String insert = String.format("INSERT INTO system_distributed.partition_denylist (ks_name, table_name, key) VALUES ('%s', '%s', 0x%s)",
>                                             keyspace, table, ByteBufferUtil.bytesToHex(key));
> 
> You can see that it's different from the way of textAsBlob. A better (yet general) explanation is here
> https://stackoverflow.com/questions/31994880/why-cassandra-cql-query-with-hexadecimal-literal-works-but-textasbinary-does-not
> 
> So... the short answer is, I get back my previous answer. My apologies!  It's not recommended to directly insert rows into the system_distributed.partition_denylist table.You'd better go with the JMX interface since it will go to the StorageProxy which handles more logic like the hex string conversion and other error handling stuff. 
> 
> Hope the answer is helpful! Pls feel free to let me know if you have any questions!
> 
> Thanks,
> Cheng
> 
> 
>> On Thu, Oct 20, 2022 at 7:07 AM Aaron Ploetz <aa...@gmail.com> wrote:
>> No worries, Cheng!
>> 
>> So I actually pivoted a little and adjusted my example table to use a single integer-based partition key.
>> 
>> aaron@cqlsh:stackoverflow> SELECT ks_name, table_name, blobAsint(key) FROM system_distributed.partition_denylist WHERE ks_name='stackoverflow' AND table_name='weather_sensor_data_by_month';
>> 
>>  ks_name       | table_name                   | system.blobasint(key)
>> ---------------+------------------------------+-----------------------
>>  stackoverflow | weather_sensor_data_by_month |                202210
>> 
>> But even with the yaml changes, it still allows me to SELECT that partition.
>> 
>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data_by_month WHERE month=202210 LIMIT 1;
>> 
>>  month  | city           | recorded_time                   | temp
>> --------+----------------+---------------------------------+------
>>  202210 | Minneapolis,MN | 2022-10-17 11:30:00.000000+0000 |    1
>> 
>> Just wondering what I should do to get it to deny access?
>> 
>> Thanks,
>> 
>> Aaron
>> 
>> 
>> 
>>> On Wed, Oct 19, 2022 at 5:34 PM Cheng Wang <ch...@netflix.com> wrote:
>>> Hi Aaron,
>>> 
>>> Sorry for the late reply, was dealing with a production issue (maybe another topic for Cassandra Summit :-)). Are you running on your local machine? Then yes, you do need to enable the config for all the following
>>> enable_partition_denylist: true
>>> 
>>> enable_denylist_writes: true
>>> enable_denylist_reads: true
>>> enable_denylist_range_reads: true
>>> I am not sure enable_partition_denylist will enable for the rest of three @Jordan West  maybe you can confirm for that? But it's better to enable for all just to be safe.
>>> I will play by my side and get back to you soon about the composite keys. 
>>> 
>>> Thanks
>>> Cheng
>>>  
>>>> On Wed, Oct 19, 2022 at 1:42 PM Aaron Ploetz <aa...@gmail.com> wrote:
>>>> Just checking, but for this to work, do I have to mess with these settings in the YAML at all?
>>>> 
>>>> partition_denylist_enabled: true
>>>> denylist_reads_enabled: true
>>>> 
>>>> They're commented out by default.
>>>> 
>>>> Thanks,
>>>> 
>>>> Aaron
>>>> 
>>>> 
>>>>> On Mon, Oct 17, 2022 at 4:53 PM Aaron Ploetz <aa...@gmail.com> wrote:
>>>>> Thanks for the help with the INSERT, Cheng!  I'm further along than before.  But it still must not be matching up quite right, because I can still select that partition.
>>>>> 
>>>>> I have several different combinations of the two keys (and I removed the space) of "Minneapolis,MN" and 202210.  Here's what I've got out there so far:
>>>>> 
>>>>> aaron@cqlsh:stackoverflow> select ks_name, table_name, blobAsText(key) from system_distributed.partition_denylist;
>>>>> 
>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>> ---------------+---------------------+--------------------------
>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis,MN', 202210
>>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN',202210
>>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN':202210
>>>>>  stackoverflow | weather_sensor_data |   Minneapolis,MN, 202210
>>>>>  stackoverflow | weather_sensor_data |     Minneapolis,MN202210
>>>>>  stackoverflow | weather_sensor_data |    Minneapolis,MN:202210
>>>>> 
>>>>> (6 rows)
>>>>> 
>>>>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data WHERE city='Minneapolis,MN' AND month=202210;
>>>>> 
>>>>>  city           | month  | recorded_time                   | temp
>>>>> ----------------+--------+---------------------------------+------
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:30:00.000000+0000 |    1
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:25:00.000000+0000 |    1
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:20:00.000000+0000 |    1
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:15:00.000000+0000 |    1
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:10:00.000000+0000 |    2
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:05:00.000000+0000 |    2
>>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:00:00.000000+0000 |    2
>>>>> 
>>>>> (7 rows)
>>>>> 
>>>>> As you can see, I can still select the partition.  I was really hoping one of those combinations would do it.
>>>>> 
>>>>> Looking at the StorageProxyTest.java in the project, I saw that it was delimited by a colon ":", which is why I tried that, too.
>>>>> 
>>>>> Still looking for the right way to enter both of those keys.
>>>>> 
>>>>> Thanks,
>>>>> 
>>>>> Aaron
>>>>> 
>>>>> 
>>>>>> On Mon, Oct 17, 2022 at 4:40 PM Cheng Wang <ch...@netflix.com> wrote:
>>>>>> Another approach is, instead of using $$, you can put additional pair of single quote around the 'Minneapolis, MN' 
>>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name, table_name, key) values ('stackoverflow', 'weather_sensor_data', textAsBlob('''Minneapolis, MN'', 202210'));
>>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from system_distributed.partition_denylist;                                    
>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>> ---------------+---------------------+---------------------------
>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>> 
>>>>>>> On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com> wrote:
>>>>>>> Hi Aaron, 
>>>>>>> 
>>>>>>> Yes, you can directly insert into the system_distributed.partition_denylist instead of using JMX. Jordan wrote a blog post for denylist
>>>>>>> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>>>>>>> 
>>>>>>> And the syntax error, one way around is to put $$ around like below
>>>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name, table_name, key) values ('stackoverflow', 'weather_sensor_data', textAsBlob($$'Minneapolis, MN', 202210$$));
>>>>>>> 
>>>>>>> There is post about this
>>>>>>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark.
>>>>>>> 
>>>>>>> and then you can verify the insert by doing 
>>>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from system_distributed.partition_denylist;                                    
>>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>>> ---------------+---------------------+---------------------------
>>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>>> 
>>>>>>> Pls let me know if it works for you.
>>>>>>> 
>>>>>>> 
>>>>>>>> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com> wrote:
>>>>>>>> I have this table definition:
>>>>>>>> 
>>>>>>>> CREATE TABLE stackoverflow.weather_sensor_data (
>>>>>>>>     city text,
>>>>>>>>     month int,
>>>>>>>>     recorded_time timestamp,
>>>>>>>>     temp float,
>>>>>>>>     PRIMARY KEY ((city, month), recorded_time)
>>>>>>>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>>>>>>> 
>>>>>>>> Sample data looks like this:
>>>>>>>> 
>>>>>>>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN' AND month=202111;
>>>>>>>> 
>>>>>>>>  city            | month  | recorded_time                   | temp
>>>>>>>> -----------------+--------+---------------------------------+------
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>>>>>>> 
>>>>>>>> (5 rows)
>>>>>>>> 
>>>>>>>> Using JMX Term, I've tried to denylist that partition, but I must have the syntax for composite keys incorrect:
>>>>>>>> 
>>>>>>>> $>bean org.apache.cassandra.db:type=StorageProxy
>>>>>>>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis, MN',202210"
>>>>>>>> #IllegalArgumentException: Operation denylistKey with 4 parameters doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>>>>>> 
>>>>>>>> Obviously, it's reading the space between "Minneapolis," and "MN" as a delimiter.  What's the right way to handle commas, spaces, and composite keys for this?
>>>>>>>> 
>>>>>>>> Also, is there another way to accomplish this without using JMX?
>>>>>>>> 
>>>>>>>> Thanks,
>>>>>>>> 
>>>>>>>> Aaron
>>>>>>>> 
>>>>>>>> 
>>>>>>>>

Re: Denylisting with a composite partition key

Posted by Cheng Wang via user <us...@cassandra.apache.org>.

Hi Aaron,

After reading through the code, I finally figured out the issue. So back to
your original question where you failed to run
$>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
MN',202210"
#IllegalArgumentException: Operation denylistKey with 4 parameters doesn't
exist in bean org.apache.cassandra.db:type=StorageProxy

It's not a Cassandra issue since it failed at the JMX parser stage,  even
before it goes to the Cassandra internal StorageProxy::denylistKey method.
Yes, you got the right gist. It's because of the extra space between
"Minneapolis" and "MN". To avoid the error, you need to add "\\" before the
white space as below :

$>run denylistKey "stackoverflow" "weather_sensor_data" "Minneapolis,\\
MN:202210"

#calling operation denylistKey of mbean
org.apache.cassandra.db:type=StorageProxy with params [stackoverflow,
weather_sensor_data, Minneapolis, MN:202210]

#operation returns:

true

And yes, the composite key is delimited by ":" . You can find more examples
from the test code as it used composite keys
https://github.com/apache/cassandra/blob/trunk/test/unit/org/apache/cassandra/service/PartitionDenylistTest.java

And then when you select the key, it will get denylisted:

cqlsh> select ks_name, table_name, key from
system_distributed.partition_denylist;


 ks_name       | table_name          | key

---------------+---------------------+------------------------------------------------------

 stackoverflow | weather_sensor_data |
0x000f4d696e6e6561706f6c69732c204d4e000004000315e200


(2 rows)

cqlsh> select * from stackoverflow.weather_sensor_data where city =
'Minneapolis, MN' AND month = 202210;

InvalidRequest: Error from server: code=2200 [Invalid query]
message="Unable to read denylisted partition
[0xDecoratedKey(8132598509541575594,
000f4d696e6e6561706f6c69732c204d4e000004000315e200)] in
stackoverflow/weather_sensor_data"

cqlsh>


For your second question why even a single partition key doesn't work, my
apologies my previous answer on direct insert to the s
ystem_distributed.partition_denylist like
insert into system_distributed.partition_denylist (ks_name, table_name,
key) values ('stackoverflow', 'weather_sensor_data',
textAsBlob('''Minneapolis, MN'', 202210'));
is wrong. It's because the way Cassandra stores the key in the system table
is it converts the string into a hexadecimal literal. You can find the
source code here
https://github.com/apache/cassandra/blob/trunk/src/java/org/apache/cassandra/schema/PartitionDenylist.java
 And check out the function addKeyToDenylist
final String insert = String.format("INSERT INTO system_distributed.
partition_denylist (ks_name, table_name, key) VALUES ('%s', '%s', 0x%s)",
keyspace, table, ByteBufferUtil.bytesToHex(key));

You can see that it's different from the way of textAsBlob. A better (yet
general) explanation is here
https://stackoverflow.com/questions/31994880/why-cassandra-cql-query-with-hexadecimal-literal-works-but-textasbinary-does-not

So... the short answer is, I get back my previous answer. My apologies!
It's not recommended to directly insert rows into
the system_distributed.partition_denylist table.You'd better go with the
JMX interface since it will go to the StorageProxy which handles more logic
like the hex string conversion and other error handling stuff.

Hope the answer is helpful! Pls feel free to let me know if you have any
questions!

Thanks,
Cheng


On Thu, Oct 20, 2022 at 7:07 AM Aaron Ploetz <aa...@gmail.com> wrote:

> No worries, Cheng!
>
> So I actually pivoted a little and adjusted my example table to use a
> single integer-based partition key.
>
> aaron@cqlsh:stackoverflow> SELECT ks_name, table_name, blobAsint(key)
> FROM system_distributed.partition_denylist WHERE ks_name='stackoverflow'
> AND table_name='weather_sensor_data_by_month';
>
>  ks_name       | table_name                   | system.blobasint(key)
> ---------------+------------------------------+-----------------------
>  stackoverflow | weather_sensor_data_by_month |                202210
>
> But even with the yaml changes, it still allows me to SELECT that
> partition.
>
> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data_by_month
> WHERE month=202210 LIMIT 1;
>
>  month  | city           | recorded_time                   | temp
> --------+----------------+---------------------------------+------
>  202210 | Minneapolis,MN | 2022-10-17 11:30:00.000000+0000 |    1
>
> Just wondering what I should do to get it to deny access?
>
> Thanks,
>
> Aaron
>
>
>
> On Wed, Oct 19, 2022 at 5:34 PM Cheng Wang <ch...@netflix.com> wrote:
>
>> Hi Aaron,
>>
>> Sorry for the late reply, was dealing with a production issue (maybe
>> another topic for Cassandra Summit :-)). Are you running on your local
>> machine? Then yes, you do need to enable the config for all the following
>>
>> enable_partition_denylist: true
>>
>> enable_denylist_writes: true
>> enable_denylist_reads: true
>> enable_denylist_range_reads: true
>>
>> I am not sure enable_partition_denylist will enable for the rest of three @Jordan
>> West <jo...@netflix.com>  maybe you can confirm for that? But it's
>> better to enable for all just to be safe.
>> I will play by my side and get back to you soon about the composite keys.
>>
>> Thanks
>> Cheng
>>
>> On Wed, Oct 19, 2022 at 1:42 PM Aaron Ploetz <aa...@gmail.com>
>> wrote:
>>
>>> Just checking, but for this to work, do I have to mess with these
>>> settings in the YAML at all?
>>>
>>> partition_denylist_enabled: true
>>> denylist_reads_enabled: true
>>>
>>> They're commented out by default.
>>>
>>> Thanks,
>>>
>>> Aaron
>>>
>>>
>>> On Mon, Oct 17, 2022 at 4:53 PM Aaron Ploetz <aa...@gmail.com>
>>> wrote:
>>>
>>>> Thanks for the help with the INSERT, Cheng!  I'm further along than
>>>> before.  But it still must not be matching up quite right, because I can
>>>> still select that partition.
>>>>
>>>> I have several different combinations of the two keys (and I removed
>>>> the space) of "Minneapolis,MN" and 202210.  Here's what I've got out there
>>>> so far:
>>>>
>>>> aaron@cqlsh:stackoverflow> select ks_name, table_name, blobAsText(key)
>>>> from system_distributed.partition_denylist;
>>>>
>>>>  ks_name       | table_name          | system.blobastext(key)
>>>> ---------------+---------------------+--------------------------
>>>>  stackoverflow | weather_sensor_data | 'Minneapolis,MN', 202210
>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN',202210
>>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN':202210
>>>>  stackoverflow | weather_sensor_data |   Minneapolis,MN, 202210
>>>>  stackoverflow | weather_sensor_data |     Minneapolis,MN202210
>>>>  stackoverflow | weather_sensor_data |    Minneapolis,MN:202210
>>>>
>>>> (6 rows)
>>>>
>>>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data WHERE
>>>> city='Minneapolis,MN' AND month=202210;
>>>>
>>>>  city           | month  | recorded_time                   | temp
>>>> ----------------+--------+---------------------------------+------
>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:30:00.000000+0000 |    1
>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:25:00.000000+0000 |    1
>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:20:00.000000+0000 |    1
>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:15:00.000000+0000 |    1
>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:10:00.000000+0000 |    2
>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:05:00.000000+0000 |    2
>>>>  Minneapolis,MN | 202210 | 2022-10-17 11:00:00.000000+0000 |    2
>>>>
>>>> (7 rows)
>>>>
>>>> As you can see, I can still select the partition.  I was really hoping
>>>> one of those combinations would do it.
>>>>
>>>> Looking at the StorageProxyTest.java in the project, I saw that it was
>>>> delimited by a colon ":", which is why I tried that, too.
>>>>
>>>> Still looking for the right way to enter both of those keys.
>>>>
>>>> Thanks,
>>>>
>>>> Aaron
>>>>
>>>>
>>>> On Mon, Oct 17, 2022 at 4:40 PM Cheng Wang <ch...@netflix.com> wrote:
>>>>
>>>>> Another approach is, instead of using $$, you can put additional pair
>>>>> of single quote around the 'Minneapolis, MN'
>>>>>
>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>>> textAsBlob('''Minneapolis, MN'', 202210'));
>>>>>
>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>>> system_distributed.partition_denylist;
>>>>>
>>>>>
>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>
>>>>> ---------------+---------------------+---------------------------
>>>>>
>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>
>>>>> On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com> wrote:
>>>>>
>>>>>> Hi Aaron,
>>>>>>
>>>>>> Yes, you can directly insert into the system_distributed.partition_denylist
>>>>>> instead of using JMX. Jordan wrote a blog post for denylist
>>>>>>
>>>>>> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>>>>>>
>>>>>> And the syntax error, one way around is to put $$ around like below
>>>>>>
>>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>>>> textAsBlob($$'Minneapolis, MN', 202210$$));
>>>>>>
>>>>>> There is post about this
>>>>>>
>>>>>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
>>>>>> .
>>>>>>
>>>>>> and then you can verify the insert by doing
>>>>>>
>>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>>>> system_distributed.partition_denylist;
>>>>>>
>>>>>>
>>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>>
>>>>>> ---------------+---------------------+---------------------------
>>>>>>
>>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>>> Pls let me know if it works for you.
>>>>>>
>>>>>>
>>>>>> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> I have this table definition:
>>>>>>>
>>>>>>> CREATE TABLE stackoverflow.weather_sensor_data (
>>>>>>>     city text,
>>>>>>>     month int,
>>>>>>>     recorded_time timestamp,
>>>>>>>     temp float,
>>>>>>>     PRIMARY KEY ((city, month), recorded_time)
>>>>>>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>>>>>>
>>>>>>> Sample data looks like this:
>>>>>>>
>>>>>>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN' AND
>>>>>>> month=202111;
>>>>>>>
>>>>>>>  city            | month  | recorded_time                   | temp
>>>>>>> -----------------+--------+---------------------------------+------
>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>>>>>>
>>>>>>> (5 rows)
>>>>>>>
>>>>>>> Using JMX Term, I've tried to denylist that partition, but I must
>>>>>>> have the syntax for composite keys incorrect:
>>>>>>>
>>>>>>> $>bean org.apache.cassandra.db:type=StorageProxy
>>>>>>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>>>>>>> MN',202210"
>>>>>>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>>>>>>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>>>>>
>>>>>>> Obviously, it's reading the space between "Minneapolis," and "MN" as
>>>>>>> a delimiter.  What's the right way to handle commas, spaces, and composite
>>>>>>> keys for this?
>>>>>>>
>>>>>>> Also, is there another way to accomplish this without using JMX?
>>>>>>>
>>>>>>> Thanks,
>>>>>>>
>>>>>>> Aaron
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>

Re: Denylisting with a composite partition key

Posted by Aaron Ploetz <aa...@gmail.com>.

No worries, Cheng!

So I actually pivoted a little and adjusted my example table to use a
single integer-based partition key.

aaron@cqlsh:stackoverflow> SELECT ks_name, table_name, blobAsint(key) FROM
system_distributed.partition_denylist WHERE ks_name='stackoverflow' AND
table_name='weather_sensor_data_by_month';

 ks_name       | table_name                   | system.blobasint(key)
---------------+------------------------------+-----------------------
 stackoverflow | weather_sensor_data_by_month |                202210

But even with the yaml changes, it still allows me to SELECT that partition.

aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data_by_month WHERE
month=202210 LIMIT 1;

 month  | city           | recorded_time                   | temp
--------+----------------+---------------------------------+------
 202210 | Minneapolis,MN | 2022-10-17 11:30:00.000000+0000 |    1

Just wondering what I should do to get it to deny access?

Thanks,

Aaron



On Wed, Oct 19, 2022 at 5:34 PM Cheng Wang <ch...@netflix.com> wrote:

> Hi Aaron,
>
> Sorry for the late reply, was dealing with a production issue (maybe
> another topic for Cassandra Summit :-)). Are you running on your local
> machine? Then yes, you do need to enable the config for all the following
>
> enable_partition_denylist: true
>
> enable_denylist_writes: true
> enable_denylist_reads: true
> enable_denylist_range_reads: true
>
> I am not sure enable_partition_denylist will enable for the rest of three @Jordan
> West <jo...@netflix.com>  maybe you can confirm for that? But it's
> better to enable for all just to be safe.
> I will play by my side and get back to you soon about the composite keys.
>
> Thanks
> Cheng
>
> On Wed, Oct 19, 2022 at 1:42 PM Aaron Ploetz <aa...@gmail.com>
> wrote:
>
>> Just checking, but for this to work, do I have to mess with these
>> settings in the YAML at all?
>>
>> partition_denylist_enabled: true
>> denylist_reads_enabled: true
>>
>> They're commented out by default.
>>
>> Thanks,
>>
>> Aaron
>>
>>
>> On Mon, Oct 17, 2022 at 4:53 PM Aaron Ploetz <aa...@gmail.com>
>> wrote:
>>
>>> Thanks for the help with the INSERT, Cheng!  I'm further along than
>>> before.  But it still must not be matching up quite right, because I can
>>> still select that partition.
>>>
>>> I have several different combinations of the two keys (and I removed the
>>> space) of "Minneapolis,MN" and 202210.  Here's what I've got out there so
>>> far:
>>>
>>> aaron@cqlsh:stackoverflow> select ks_name, table_name, blobAsText(key)
>>> from system_distributed.partition_denylist;
>>>
>>>  ks_name       | table_name          | system.blobastext(key)
>>> ---------------+---------------------+--------------------------
>>>  stackoverflow | weather_sensor_data | 'Minneapolis,MN', 202210
>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN',202210
>>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN':202210
>>>  stackoverflow | weather_sensor_data |   Minneapolis,MN, 202210
>>>  stackoverflow | weather_sensor_data |     Minneapolis,MN202210
>>>  stackoverflow | weather_sensor_data |    Minneapolis,MN:202210
>>>
>>> (6 rows)
>>>
>>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data WHERE
>>> city='Minneapolis,MN' AND month=202210;
>>>
>>>  city           | month  | recorded_time                   | temp
>>> ----------------+--------+---------------------------------+------
>>>  Minneapolis,MN | 202210 | 2022-10-17 11:30:00.000000+0000 |    1
>>>  Minneapolis,MN | 202210 | 2022-10-17 11:25:00.000000+0000 |    1
>>>  Minneapolis,MN | 202210 | 2022-10-17 11:20:00.000000+0000 |    1
>>>  Minneapolis,MN | 202210 | 2022-10-17 11:15:00.000000+0000 |    1
>>>  Minneapolis,MN | 202210 | 2022-10-17 11:10:00.000000+0000 |    2
>>>  Minneapolis,MN | 202210 | 2022-10-17 11:05:00.000000+0000 |    2
>>>  Minneapolis,MN | 202210 | 2022-10-17 11:00:00.000000+0000 |    2
>>>
>>> (7 rows)
>>>
>>> As you can see, I can still select the partition.  I was really hoping
>>> one of those combinations would do it.
>>>
>>> Looking at the StorageProxyTest.java in the project, I saw that it was
>>> delimited by a colon ":", which is why I tried that, too.
>>>
>>> Still looking for the right way to enter both of those keys.
>>>
>>> Thanks,
>>>
>>> Aaron
>>>
>>>
>>> On Mon, Oct 17, 2022 at 4:40 PM Cheng Wang <ch...@netflix.com> wrote:
>>>
>>>> Another approach is, instead of using $$, you can put additional pair
>>>> of single quote around the 'Minneapolis, MN'
>>>>
>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>> textAsBlob('''Minneapolis, MN'', 202210'));
>>>>
>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>> system_distributed.partition_denylist;
>>>>
>>>>
>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>
>>>> ---------------+---------------------+---------------------------
>>>>
>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>
>>>> On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com> wrote:
>>>>
>>>>> Hi Aaron,
>>>>>
>>>>> Yes, you can directly insert into the system_distributed.partition_denylist
>>>>> instead of using JMX. Jordan wrote a blog post for denylist
>>>>>
>>>>> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>>>>>
>>>>> And the syntax error, one way around is to put $$ around like below
>>>>>
>>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>>> textAsBlob($$'Minneapolis, MN', 202210$$));
>>>>>
>>>>> There is post about this
>>>>>
>>>>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
>>>>> .
>>>>>
>>>>> and then you can verify the insert by doing
>>>>>
>>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>>> system_distributed.partition_denylist;
>>>>>
>>>>>
>>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>>
>>>>> ---------------+---------------------+---------------------------
>>>>>
>>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>>> Pls let me know if it works for you.
>>>>>
>>>>>
>>>>> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> I have this table definition:
>>>>>>
>>>>>> CREATE TABLE stackoverflow.weather_sensor_data (
>>>>>>     city text,
>>>>>>     month int,
>>>>>>     recorded_time timestamp,
>>>>>>     temp float,
>>>>>>     PRIMARY KEY ((city, month), recorded_time)
>>>>>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>>>>>
>>>>>> Sample data looks like this:
>>>>>>
>>>>>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN' AND
>>>>>> month=202111;
>>>>>>
>>>>>>  city            | month  | recorded_time                   | temp
>>>>>> -----------------+--------+---------------------------------+------
>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>>>>>
>>>>>> (5 rows)
>>>>>>
>>>>>> Using JMX Term, I've tried to denylist that partition, but I must
>>>>>> have the syntax for composite keys incorrect:
>>>>>>
>>>>>> $>bean org.apache.cassandra.db:type=StorageProxy
>>>>>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>>>>>> MN',202210"
>>>>>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>>>>>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>>>>
>>>>>> Obviously, it's reading the space between "Minneapolis," and "MN" as
>>>>>> a delimiter.  What's the right way to handle commas, spaces, and composite
>>>>>> keys for this?
>>>>>>
>>>>>> Also, is there another way to accomplish this without using JMX?
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Aaron
>>>>>>
>>>>>>
>>>>>>
>>>>>>

Re: Denylisting with a composite partition key

Posted by Cheng Wang via user <us...@cassandra.apache.org>.

Hi Aaron,

Sorry for the late reply, was dealing with a production issue (maybe
another topic for Cassandra Summit :-)). Are you running on your local
machine? Then yes, you do need to enable the config for all the following

enable_partition_denylist: true

enable_denylist_writes: true
enable_denylist_reads: true
enable_denylist_range_reads: true

I am not sure enable_partition_denylist will enable for the rest of
three @Jordan
West <jo...@netflix.com>  maybe you can confirm for that? But it's better
to enable for all just to be safe.
I will play by my side and get back to you soon about the composite keys.

Thanks
Cheng

On Wed, Oct 19, 2022 at 1:42 PM Aaron Ploetz <aa...@gmail.com> wrote:

> Just checking, but for this to work, do I have to mess with these settings
> in the YAML at all?
>
> partition_denylist_enabled: true
> denylist_reads_enabled: true
>
> They're commented out by default.
>
> Thanks,
>
> Aaron
>
>
> On Mon, Oct 17, 2022 at 4:53 PM Aaron Ploetz <aa...@gmail.com>
> wrote:
>
>> Thanks for the help with the INSERT, Cheng!  I'm further along than
>> before.  But it still must not be matching up quite right, because I can
>> still select that partition.
>>
>> I have several different combinations of the two keys (and I removed the
>> space) of "Minneapolis,MN" and 202210.  Here's what I've got out there so
>> far:
>>
>> aaron@cqlsh:stackoverflow> select ks_name, table_name, blobAsText(key)
>> from system_distributed.partition_denylist;
>>
>>  ks_name       | table_name          | system.blobastext(key)
>> ---------------+---------------------+--------------------------
>>  stackoverflow | weather_sensor_data | 'Minneapolis,MN', 202210
>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN',202210
>>  stackoverflow | weather_sensor_data |  'Minneapolis,MN':202210
>>  stackoverflow | weather_sensor_data |   Minneapolis,MN, 202210
>>  stackoverflow | weather_sensor_data |     Minneapolis,MN202210
>>  stackoverflow | weather_sensor_data |    Minneapolis,MN:202210
>>
>> (6 rows)
>>
>> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data WHERE
>> city='Minneapolis,MN' AND month=202210;
>>
>>  city           | month  | recorded_time                   | temp
>> ----------------+--------+---------------------------------+------
>>  Minneapolis,MN | 202210 | 2022-10-17 11:30:00.000000+0000 |    1
>>  Minneapolis,MN | 202210 | 2022-10-17 11:25:00.000000+0000 |    1
>>  Minneapolis,MN | 202210 | 2022-10-17 11:20:00.000000+0000 |    1
>>  Minneapolis,MN | 202210 | 2022-10-17 11:15:00.000000+0000 |    1
>>  Minneapolis,MN | 202210 | 2022-10-17 11:10:00.000000+0000 |    2
>>  Minneapolis,MN | 202210 | 2022-10-17 11:05:00.000000+0000 |    2
>>  Minneapolis,MN | 202210 | 2022-10-17 11:00:00.000000+0000 |    2
>>
>> (7 rows)
>>
>> As you can see, I can still select the partition.  I was really hoping
>> one of those combinations would do it.
>>
>> Looking at the StorageProxyTest.java in the project, I saw that it was
>> delimited by a colon ":", which is why I tried that, too.
>>
>> Still looking for the right way to enter both of those keys.
>>
>> Thanks,
>>
>> Aaron
>>
>>
>> On Mon, Oct 17, 2022 at 4:40 PM Cheng Wang <ch...@netflix.com> wrote:
>>
>>> Another approach is, instead of using $$, you can put additional pair of
>>> single quote around the 'Minneapolis, MN'
>>>
>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>> textAsBlob('''Minneapolis, MN'', 202210'));
>>>
>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>> system_distributed.partition_denylist;
>>>
>>>
>>>  ks_name       | table_name          | system.blobastext(key)
>>>
>>> ---------------+---------------------+---------------------------
>>>
>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>
>>> On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com> wrote:
>>>
>>>> Hi Aaron,
>>>>
>>>> Yes, you can directly insert into the system_distributed.partition_denylist
>>>> instead of using JMX. Jordan wrote a blog post for denylist
>>>>
>>>> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>>>>
>>>> And the syntax error, one way around is to put $$ around like below
>>>>
>>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>>> textAsBlob($$'Minneapolis, MN', 202210$$));
>>>>
>>>> There is post about this
>>>>
>>>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
>>>> .
>>>>
>>>> and then you can verify the insert by doing
>>>>
>>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>>> system_distributed.partition_denylist;
>>>>
>>>>
>>>>  ks_name       | table_name          | system.blobastext(key)
>>>>
>>>> ---------------+---------------------+---------------------------
>>>>
>>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>>> Pls let me know if it works for you.
>>>>
>>>>
>>>> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com>
>>>> wrote:
>>>>
>>>>> I have this table definition:
>>>>>
>>>>> CREATE TABLE stackoverflow.weather_sensor_data (
>>>>>     city text,
>>>>>     month int,
>>>>>     recorded_time timestamp,
>>>>>     temp float,
>>>>>     PRIMARY KEY ((city, month), recorded_time)
>>>>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>>>>
>>>>> Sample data looks like this:
>>>>>
>>>>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN' AND
>>>>> month=202111;
>>>>>
>>>>>  city            | month  | recorded_time                   | temp
>>>>> -----------------+--------+---------------------------------+------
>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>>>>
>>>>> (5 rows)
>>>>>
>>>>> Using JMX Term, I've tried to denylist that partition, but I must have
>>>>> the syntax for composite keys incorrect:
>>>>>
>>>>> $>bean org.apache.cassandra.db:type=StorageProxy
>>>>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>>>>> MN',202210"
>>>>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>>>>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>>>
>>>>> Obviously, it's reading the space between "Minneapolis," and "MN" as a
>>>>> delimiter.  What's the right way to handle commas, spaces, and composite
>>>>> keys for this?
>>>>>
>>>>> Also, is there another way to accomplish this without using JMX?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Aaron
>>>>>
>>>>>
>>>>>
>>>>>

Re: Denylisting with a composite partition key

Posted by Aaron Ploetz <aa...@gmail.com>.

Just checking, but for this to work, do I have to mess with these settings
in the YAML at all?

partition_denylist_enabled: true
denylist_reads_enabled: true

They're commented out by default.

Thanks,

Aaron


On Mon, Oct 17, 2022 at 4:53 PM Aaron Ploetz <aa...@gmail.com> wrote:

> Thanks for the help with the INSERT, Cheng!  I'm further along than
> before.  But it still must not be matching up quite right, because I can
> still select that partition.
>
> I have several different combinations of the two keys (and I removed the
> space) of "Minneapolis,MN" and 202210.  Here's what I've got out there so
> far:
>
> aaron@cqlsh:stackoverflow> select ks_name, table_name, blobAsText(key)
> from system_distributed.partition_denylist;
>
>  ks_name       | table_name          | system.blobastext(key)
> ---------------+---------------------+--------------------------
>  stackoverflow | weather_sensor_data | 'Minneapolis,MN', 202210
>  stackoverflow | weather_sensor_data |  'Minneapolis,MN',202210
>  stackoverflow | weather_sensor_data |  'Minneapolis,MN':202210
>  stackoverflow | weather_sensor_data |   Minneapolis,MN, 202210
>  stackoverflow | weather_sensor_data |     Minneapolis,MN202210
>  stackoverflow | weather_sensor_data |    Minneapolis,MN:202210
>
> (6 rows)
>
> aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data WHERE
> city='Minneapolis,MN' AND month=202210;
>
>  city           | month  | recorded_time                   | temp
> ----------------+--------+---------------------------------+------
>  Minneapolis,MN | 202210 | 2022-10-17 11:30:00.000000+0000 |    1
>  Minneapolis,MN | 202210 | 2022-10-17 11:25:00.000000+0000 |    1
>  Minneapolis,MN | 202210 | 2022-10-17 11:20:00.000000+0000 |    1
>  Minneapolis,MN | 202210 | 2022-10-17 11:15:00.000000+0000 |    1
>  Minneapolis,MN | 202210 | 2022-10-17 11:10:00.000000+0000 |    2
>  Minneapolis,MN | 202210 | 2022-10-17 11:05:00.000000+0000 |    2
>  Minneapolis,MN | 202210 | 2022-10-17 11:00:00.000000+0000 |    2
>
> (7 rows)
>
> As you can see, I can still select the partition.  I was really hoping one
> of those combinations would do it.
>
> Looking at the StorageProxyTest.java in the project, I saw that it was
> delimited by a colon ":", which is why I tried that, too.
>
> Still looking for the right way to enter both of those keys.
>
> Thanks,
>
> Aaron
>
>
> On Mon, Oct 17, 2022 at 4:40 PM Cheng Wang <ch...@netflix.com> wrote:
>
>> Another approach is, instead of using $$, you can put additional pair of
>> single quote around the 'Minneapolis, MN'
>>
>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>> textAsBlob('''Minneapolis, MN'', 202210'));
>>
>> cqlsh> select ks_name, table_name, blobAsText(key) from
>> system_distributed.partition_denylist;
>>
>>
>>  ks_name       | table_name          | system.blobastext(key)
>>
>> ---------------+---------------------+---------------------------
>>
>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>
>> On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com> wrote:
>>
>>> Hi Aaron,
>>>
>>> Yes, you can directly insert into the system_distributed.partition_denylist
>>> instead of using JMX. Jordan wrote a blog post for denylist
>>>
>>> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>>>
>>> And the syntax error, one way around is to put $$ around like below
>>>
>>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>>> textAsBlob($$'Minneapolis, MN', 202210$$));
>>>
>>> There is post about this
>>>
>>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
>>> .
>>>
>>> and then you can verify the insert by doing
>>>
>>> cqlsh> select ks_name, table_name, blobAsText(key) from
>>> system_distributed.partition_denylist;
>>>
>>>
>>>  ks_name       | table_name          | system.blobastext(key)
>>>
>>> ---------------+---------------------+---------------------------
>>>
>>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>>> Pls let me know if it works for you.
>>>
>>>
>>> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com>
>>> wrote:
>>>
>>>> I have this table definition:
>>>>
>>>> CREATE TABLE stackoverflow.weather_sensor_data (
>>>>     city text,
>>>>     month int,
>>>>     recorded_time timestamp,
>>>>     temp float,
>>>>     PRIMARY KEY ((city, month), recorded_time)
>>>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>>>
>>>> Sample data looks like this:
>>>>
>>>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN' AND
>>>> month=202111;
>>>>
>>>>  city            | month  | recorded_time                   | temp
>>>> -----------------+--------+---------------------------------+------
>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>>>
>>>> (5 rows)
>>>>
>>>> Using JMX Term, I've tried to denylist that partition, but I must have
>>>> the syntax for composite keys incorrect:
>>>>
>>>> $>bean org.apache.cassandra.db:type=StorageProxy
>>>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>>>> MN',202210"
>>>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>>>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>>
>>>> Obviously, it's reading the space between "Minneapolis," and "MN" as a
>>>> delimiter.  What's the right way to handle commas, spaces, and composite
>>>> keys for this?
>>>>
>>>> Also, is there another way to accomplish this without using JMX?
>>>>
>>>> Thanks,
>>>>
>>>> Aaron
>>>>
>>>>
>>>>
>>>>

Re: Denylisting with a composite partition key

Posted by Aaron Ploetz <aa...@gmail.com>.

Thanks for the help with the INSERT, Cheng!  I'm further along than
before.  But it still must not be matching up quite right, because I can
still select that partition.

I have several different combinations of the two keys (and I removed the
space) of "Minneapolis,MN" and 202210.  Here's what I've got out there so
far:

aaron@cqlsh:stackoverflow> select ks_name, table_name, blobAsText(key) from
system_distributed.partition_denylist;

 ks_name       | table_name          | system.blobastext(key)
---------------+---------------------+--------------------------
 stackoverflow | weather_sensor_data | 'Minneapolis,MN', 202210
 stackoverflow | weather_sensor_data |  'Minneapolis,MN',202210
 stackoverflow | weather_sensor_data |  'Minneapolis,MN':202210
 stackoverflow | weather_sensor_data |   Minneapolis,MN, 202210
 stackoverflow | weather_sensor_data |     Minneapolis,MN202210
 stackoverflow | weather_sensor_data |    Minneapolis,MN:202210

(6 rows)

aaron@cqlsh:stackoverflow> SELECT * FROM weather_sensor_data WHERE
city='Minneapolis,MN' AND month=202210;

 city           | month  | recorded_time                   | temp
----------------+--------+---------------------------------+------
 Minneapolis,MN | 202210 | 2022-10-17 11:30:00.000000+0000 |    1
 Minneapolis,MN | 202210 | 2022-10-17 11:25:00.000000+0000 |    1
 Minneapolis,MN | 202210 | 2022-10-17 11:20:00.000000+0000 |    1
 Minneapolis,MN | 202210 | 2022-10-17 11:15:00.000000+0000 |    1
 Minneapolis,MN | 202210 | 2022-10-17 11:10:00.000000+0000 |    2
 Minneapolis,MN | 202210 | 2022-10-17 11:05:00.000000+0000 |    2
 Minneapolis,MN | 202210 | 2022-10-17 11:00:00.000000+0000 |    2

(7 rows)

As you can see, I can still select the partition.  I was really hoping one
of those combinations would do it.

Looking at the StorageProxyTest.java in the project, I saw that it was
delimited by a colon ":", which is why I tried that, too.

Still looking for the right way to enter both of those keys.

Thanks,

Aaron


On Mon, Oct 17, 2022 at 4:40 PM Cheng Wang <ch...@netflix.com> wrote:

> Another approach is, instead of using $$, you can put additional pair of
> single quote around the 'Minneapolis, MN'
>
> cqlsh> insert into system_distributed.partition_denylist (ks_name,
> table_name, key) values ('stackoverflow', 'weather_sensor_data',
> textAsBlob('''Minneapolis, MN'', 202210'));
>
> cqlsh> select ks_name, table_name, blobAsText(key) from
> system_distributed.partition_denylist;
>
>  ks_name       | table_name          | system.blobastext(key)
>
> ---------------+---------------------+---------------------------
>
>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>
> On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com> wrote:
>
>> Hi Aaron,
>>
>> Yes, you can directly insert into the system_distributed.partition_denylist
>> instead of using JMX. Jordan wrote a blog post for denylist
>>
>> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>>
>> And the syntax error, one way around is to put $$ around like below
>>
>> cqlsh> insert into system_distributed.partition_denylist (ks_name,
>> table_name, key) values ('stackoverflow', 'weather_sensor_data',
>> textAsBlob($$'Minneapolis, MN', 202210$$));
>>
>> There is post about this
>>
>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
>> .
>>
>> and then you can verify the insert by doing
>>
>> cqlsh> select ks_name, table_name, blobAsText(key) from
>> system_distributed.partition_denylist;
>>
>>
>>  ks_name       | table_name          | system.blobastext(key)
>>
>> ---------------+---------------------+---------------------------
>>
>>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
>> Pls let me know if it works for you.
>>
>>
>> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com>
>> wrote:
>>
>>> I have this table definition:
>>>
>>> CREATE TABLE stackoverflow.weather_sensor_data (
>>>     city text,
>>>     month int,
>>>     recorded_time timestamp,
>>>     temp float,
>>>     PRIMARY KEY ((city, month), recorded_time)
>>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>>
>>> Sample data looks like this:
>>>
>>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN' AND
>>> month=202111;
>>>
>>>  city            | month  | recorded_time                   | temp
>>> -----------------+--------+---------------------------------+------
>>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>>
>>> (5 rows)
>>>
>>> Using JMX Term, I've tried to denylist that partition, but I must have
>>> the syntax for composite keys incorrect:
>>>
>>> $>bean org.apache.cassandra.db:type=StorageProxy
>>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>>> MN',202210"
>>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>>
>>> Obviously, it's reading the space between "Minneapolis," and "MN" as a
>>> delimiter.  What's the right way to handle commas, spaces, and composite
>>> keys for this?
>>>
>>> Also, is there another way to accomplish this without using JMX?
>>>
>>> Thanks,
>>>
>>> Aaron
>>>
>>>
>>>
>>>

Re: Denylisting with a composite partition key

Posted by Cheng Wang via user <us...@cassandra.apache.org>.

Another approach is, instead of using $$, you can put additional pair of
single quote around the 'Minneapolis, MN'

cqlsh> insert into system_distributed.partition_denylist (ks_name,
table_name, key) values ('stackoverflow', 'weather_sensor_data',
textAsBlob('''Minneapolis, MN'', 202210'));

cqlsh> select ks_name, table_name, blobAsText(key) from
system_distributed.partition_denylist;

 ks_name       | table_name          | system.blobastext(key)

---------------+---------------------+---------------------------

 stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210

On Mon, Oct 17, 2022 at 2:30 PM Cheng Wang <ch...@netflix.com> wrote:

> Hi Aaron,
>
> Yes, you can directly insert into the system_distributed.partition_denylist
> instead of using JMX. Jordan wrote a blog post for denylist
>
> https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html
>
> And the syntax error, one way around is to put $$ around like below
>
> cqlsh> insert into system_distributed.partition_denylist (ks_name,
> table_name, key) values ('stackoverflow', 'weather_sensor_data',
> textAsBlob($$'Minneapolis, MN', 202210$$));
>
> There is post about this
>
> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
> .
>
> and then you can verify the insert by doing
>
> cqlsh> select ks_name, table_name, blobAsText(key) from
> system_distributed.partition_denylist;
>
>  ks_name       | table_name          | system.blobastext(key)
>
> ---------------+---------------------+---------------------------
>
>  stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
> Pls let me know if it works for you.
>
>
> On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com>
> wrote:
>
>> I have this table definition:
>>
>> CREATE TABLE stackoverflow.weather_sensor_data (
>>     city text,
>>     month int,
>>     recorded_time timestamp,
>>     temp float,
>>     PRIMARY KEY ((city, month), recorded_time)
>> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>>
>> Sample data looks like this:
>>
>> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN' AND
>> month=202111;
>>
>>  city            | month  | recorded_time                   | temp
>> -----------------+--------+---------------------------------+------
>>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>>
>> (5 rows)
>>
>> Using JMX Term, I've tried to denylist that partition, but I must have
>> the syntax for composite keys incorrect:
>>
>> $>bean org.apache.cassandra.db:type=StorageProxy
>> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
>> MN',202210"
>> #IllegalArgumentException: Operation denylistKey with 4 parameters
>> doesn't exist in bean org.apache.cassandra.db:type=StorageProxy
>>
>> Obviously, it's reading the space between "Minneapolis," and "MN" as a
>> delimiter.  What's the right way to handle commas, spaces, and composite
>> keys for this?
>>
>> Also, is there another way to accomplish this without using JMX?
>>
>> Thanks,
>>
>> Aaron
>>
>>
>>
>>

Re: Denylisting with a composite partition key

Posted by Cheng Wang via user <us...@cassandra.apache.org>.

Hi Aaron,

Yes, you can directly insert into the system_distributed.partition_denylist
instead of using JMX. Jordan wrote a blog post for denylist
https://cassandra.apache.org/_/blog/Apache-Cassandra-4.1-Denylisting-Partitions.html

And the syntax error, one way around is to put $$ around like below

cqlsh> insert into system_distributed.partition_denylist (ks_name,
table_name, key) values ('stackoverflow', 'weather_sensor_data',
textAsBlob($$'Minneapolis, MN', 202210$$));

There is post about this
https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/escape_char_r.html#:~:text=Column%20names%20that%20contain%20characters,using%20a%20single%20quotation%20mark
.

and then you can verify the insert by doing

cqlsh> select ks_name, table_name, blobAsText(key) from
system_distributed.partition_denylist;

 ks_name       | table_name          | system.blobastext(key)

---------------+---------------------+---------------------------

 stackoverflow | weather_sensor_data | 'Minneapolis, MN', 202210
Pls let me know if it works for you.


On Mon, Oct 17, 2022 at 1:35 PM Aaron Ploetz <aa...@gmail.com> wrote:

> I have this table definition:
>
> CREATE TABLE stackoverflow.weather_sensor_data (
>     city text,
>     month int,
>     recorded_time timestamp,
>     temp float,
>     PRIMARY KEY ((city, month), recorded_time)
> ) WITH CLUSTERING ORDER BY (recorded_time DESC)
>
> Sample data looks like this:
>
> > SELECT * FROM weather_sensor_data WHERE city='Minneapolis, MN' AND
> month=202111;
>
>  city            | month  | recorded_time                   | temp
> -----------------+--------+---------------------------------+------
>  Minneapolis, MN | 202111 | 2021-11-01 08:35:00.000000+0000 |    3
>  Minneapolis, MN | 202111 | 2021-11-01 08:30:00.000000+0000 |    3
>  Minneapolis, MN | 202111 | 2021-11-01 08:25:00.000000+0000 |    2
>  Minneapolis, MN | 202111 | 2021-11-01 08:20:00.000000+0000 |    2
>  Minneapolis, MN | 202111 | 2021-11-01 08:15:00.000000+0000 |    2
>
> (5 rows)
>
> Using JMX Term, I've tried to denylist that partition, but I must have the
> syntax for composite keys incorrect:
>
> $>bean org.apache.cassandra.db:type=StorageProxy
> $>run denylistKey stackoverflow weather_sensor_data "'Minneapolis,
> MN',202210"
> #IllegalArgumentException: Operation denylistKey with 4 parameters doesn't
> exist in bean org.apache.cassandra.db:type=StorageProxy
>
> Obviously, it's reading the space between "Minneapolis," and "MN" as a
> delimiter.  What's the right way to handle commas, spaces, and composite
> keys for this?
>
> Also, is there another way to accomplish this without using JMX?
>
> Thanks,
>
> Aaron
>
>
>
>