question

dstathis avatar image
dstathis asked stefano.lottini edited

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

2 Answers

Erick Ramirez avatar image
Erick Ramirez answered Erick Ramirez commented

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 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

dstathis avatar image dstathis commented ·

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 Likes 0 ·
Erick Ramirez avatar image Erick Ramirez ♦♦ dstathis commented ·

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 Likes 0 ·
stefano.lottini avatar image
stefano.lottini answered stefano.lottini edited

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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