Can't create keyspace with Python driver

Question

question

dstathis asked Nov 5, '21 stefano.lottini edited Nov 9, '21

Can't create keyspace with Python driver

Hello,

I am having some issues with the Cassandra python driver. I was originally using the query

conn.execute(f"CREATE KEYSPACE IF NOT EXISTS {db_name} WITH REPLICATION = {
  { 'class' : 'SimpleStrategy', 'replication_factor' : {replication} }}")

But that was open to injection so I changed the code to

replication_string = f"{
  { 'class' : 'SimpleStrategy', 'replication_factor' : {replication} }}"
conn.execute("CREATE KEYSPACE IF NOT EXISTS %s WITH REPLICATION = %s", (db_name, replication_string))

This is now causing an error

cassandra.protocol.SyntaxException: <Error from server: code=2000 [Syntax error in CQL query] message="line 1:30 no viable alternative at input 'juju_db_lma_cstress' (CREATE KEYSPACE IF NOT EXISTS ['juju_db_lma_cstres]...)">

Does anyone know why I'm getting this error? It looks like it should be the same to me.

Note: in this particular case "replication" is 1 and "db_name" is "juju_db_lma_cstress"

python driver

10 |1000

Attachments: Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Answer 1 · 2021-11-08T23:52:23Z

Erick Ramirez answered Nov 8, '21 Erick Ramirez commented Nov 9, '21

It looks like the CQL statement is getting jumbled. One thing you can try is to construct the DDL string and pass it to execute() after it has been fully constructed so you are guaranteed that it doesn't get garbled.

However, our general recommendation is to use cqlsh to make changes to the schema instead of doing it programatically to avoid schema disagreements. Cheers!

2 Share

10 |1000

Attachments: Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

dstathis · Nov 09, 2021 at 06:21 AM

Preconstructing the string is what I was doing at first but it is open to injection that way and is thus insecure. Is there some way to have the library show me the full string it ends up executing?

We need to automate this step so doing it manually is unfortunately not an option for us.

0 ·

Erick Ramirez ♦♦ dstathis · Nov 09, 2021 at 08:49 AM

Just to clarify, what I meant was to generate the DDL statement as:

ddl_string = <generate_create_keyspace_string_here>

then execute with:

conn.execute(ddl_string)

0 ·

Answer 2 · 2021-11-09T09:51:34Z

stefano.lottini answered Nov 9, '21 stefano.lottini edited Nov 9, '21

The placeholder syntax for the "execute" method is for inserting literal values in your statements (for "WHERE" clauses, "VALUES" specifications and so on).

It does not cover keyspace names or table names, and the technical reason for that is that string literal in CQL are escaped with single quotes, while table/schema names require double quotes when they need quoting (e.g. mixed-case names).

Behind this technical point, and more importantly, is the remark by Erick above, that is: one should not run schema changes programmatically as this may easily lead to nasty schema disagreements. DDL statements are better executed manually (with an eye on the cluster health at that).

So, technically one has to construct a DDL string completely before invoking "execute", but even better and considering best practices, it is strongly suggested to revise the data model and find a solution that does not require creation of keyspaces in an automated way in the code.

Share

10 |1000

Attachments: Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

question