[
https://issues.apache.org/jira/browse/CASSANDRA-21139?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Brad Schoening updated CASSANDRA-21139:
---------------------------------------
Description:
We have hundreds of application teams and several dozen of them miss-prepare
statements by using literals instead of bind markers.
I.e.,
{code:sql}
// wrong
session.prepare("select * from users where ID = 996");
session.prepare("select * from users where ID = 997");
session.prepare("select * from users where ID = 998");
session.prepare("select * from users where ID = 999");
// correct
session.prepare("select * from users where ID = ?");
{code}
The problem causes the prepared statement cache to constantly overflow, and
will print a prepared statements discarded WARN message in the Cassandra log.
At present, we use a wack-a-mole approach to discuss the problem with each
development team individually, and hope they fix it and train the entire team
on how to prepare statements correctly.
Also, finding the root cause of the issue today requires having the knowledge
and access to look at the system.prepared_statements table.
Guardrails would seem a good approach here, where the guard could WARN or
REJECT when a statement was prepared using a WHERE clause and no bind markers.
Note, this should not prevent users from creating prepared statements without a
WHERE clause or with one or more literal values so long as there was at least
one bind marker. Thus, the following would remain valid:
{code:sql}
select * from users
select * from users where TYPE=5 and ID = ?
{code}
was:
We have hundreds of application teams and several dozen of them miss-prepare
statements by using literals instead of bind markers.
I.e.,
{code:sql}
// wrong
select * from users where ID = 996
select * from users where ID = 997
select * from users where ID = 998
select * from users where ID = 999
// correct
select * from users where ID = ?
{code}
The problem causes the prepared statement cache to constantly overflow, and
will print a prepared statements discarded WARN message in the Cassandra log.
At present, we use a wack-a-mole approach to discuss the problem with each
development team individually, and hope they fix it and train the entire team
on how to prepare statements correctly.
Also, finding the root cause of the issue today requires having the knowledge
and access to look at the system.prepared_statements table.
Guardrails would seem a good approach here, where the guard could WARN or
REJECT when a statement was prepared using a WHERE clause and no bind markers.
Note, this should not prevent users from creating prepared statements without a
WHERE clause or with one or more literal values so long as there was at least
one bind marker. Thus, the following would remain valid:
{code:sql}
select * from users
select * from users where TYPE=5 and ID = ?
{code}
> Guardrail for miss-prepared statements
> --------------------------------------
>
> Key: CASSANDRA-21139
> URL: https://issues.apache.org/jira/browse/CASSANDRA-21139
> Project: Apache Cassandra
> Issue Type: Improvement
> Components: Feature/Guardrails
> Reporter: Brad Schoening
> Priority: Normal
>
> We have hundreds of application teams and several dozen of them miss-prepare
> statements by using literals instead of bind markers.
> I.e.,
> {code:sql}
> // wrong
> session.prepare("select * from users where ID = 996");
> session.prepare("select * from users where ID = 997");
> session.prepare("select * from users where ID = 998");
> session.prepare("select * from users where ID = 999");
> // correct
> session.prepare("select * from users where ID = ?");
> {code}
> The problem causes the prepared statement cache to constantly overflow, and
> will print a prepared statements discarded WARN message in the Cassandra log.
> At present, we use a wack-a-mole approach to discuss the problem with each
> development team individually, and hope they fix it and train the entire team
> on how to prepare statements correctly.
> Also, finding the root cause of the issue today requires having the knowledge
> and access to look at the system.prepared_statements table.
> Guardrails would seem a good approach here, where the guard could WARN or
> REJECT when a statement was prepared using a WHERE clause and no bind markers.
> Note, this should not prevent users from creating prepared statements without
> a WHERE clause or with one or more literal values so long as there was at
> least one bind marker. Thus, the following would remain valid:
> {code:sql}
> select * from users
> select * from users where TYPE=5 and ID = ?
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
