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 <aaronplo...@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 <aaronplo...@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 <che...@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 <aaronplo...@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 <che...@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 <jord...@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 <aaronplo...@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 <aaronplo...@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 <che...@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 <che...@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 <aaronplo...@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 >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>>