[ODBC-220] ODBC Connector Prepares Statements on SQLExecDirect queries Created: 2019-02-06  Updated: 2019-02-21  Resolved: 2019-02-21

Status: Closed
Project: MariaDB Connector/ODBC
Component/s: General
Affects Version/s: 3.1.0, 3.0.8
Fix Version/s: N/A

Type: Bug Priority: Blocker
Reporter: Ross Assignee: Lawrin Novitsky
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS, ProxySQL

Issue Links:
Relates
relates to ODBC-222 Make SQLExecDirect to use text protoc... Closed

 Description   

When using ProxySQL and querying 'stats_mysql_prepared_statements_info' there are thousands of entries for statements that have been run using the ODBC SQLExecDirect statement.

For example:

SELECT col1 FROM table1 WHERE col2 = 'XXXXXXX'
SELECT col1 FROM table1 WHERE col2 = 'YYYYYYY'
SELECT col1 FROM table1 WHERE col2 = 'ZZZZZZZ'

This causes a problem on the MariaDB cluster as it reaches 'max_prepared_stmt_count'.

As the statement is already prepared and doesn't contain any '?' it should not send a prepared statement to the server, instead, it should just execute the statement as is.

When using MySQL ODBC connector, the issue does not happen. Only the direct query is sent to the server.

This issue creates a DDoS type attack on the MariaDB servers and stops further processing until the 'max_prepared_stmt_count' drops.

The SQLExecDirect ODBC method is described as:

"Direct execution works best for statements that will be executed a single time. Its major drawback is that the SQL statement is parsed every time it is executed. In addition, the application cannot retrieve information about the result set created by the statement (if any) until after the statement is executed; this is possible if the statement is prepared and executed in two separate steps."

Therefore it should never be prepared by the server. I have updated this to be a 'Blocker' due to the possible DDoS nature of this issue.

 Comments   
Comment by Lawrin Novitsky [ 2019-02-07 ]

This is not a bug. This is intended to be so and is documented I am not closing it only since we are considering to add text protocol support in future versions, and this your report may be good pro argument to do that in the new version.

Comment by Ross [ 2019-02-07 ]

It is a bug as it calls a prepared statement on a query that has values already assigned.

This causes the max_prepared_stmt_count to be reached, this is not the correct behaviour.

Comment by Ross [ 2019-02-07 ]

Additionally, MySQL ODBC Connector does not send prepared statements to the DB server for SQLExecDirect queries

Comment by Lawrin Novitsky [ 2019-02-07 ]

max_prepared_stmt_count is reached because applications do not close or re-use statement handlers. Or it is too small.
I still do not agree this is a bug - that is only how our connector is internally implemented. And what mysql's connector does is not really relevant here.
But I can agree that is potential task for one of new versions.

Comment by Ross [ 2019-02-07 ]

The application uses SQLExecDirect, which should not create a prepared statement as per SQLExecDirec

The application does not create the prepared statement, instead, it passes a 'statement' which should be executed once. Should the application be using an alternate method for this?

The application uses the following code:

https://github.com/asterisk/asterisk/blob/master/funcs/func_odbc.c
https://github.com/asterisk/asterisk/blob/master/res/res_odbc.c

Comment by Lawrin Novitsky [ 2019-02-08 ]

The only bug I can here think of, is maybe the connector should close statement handler right after fetching all results in case of SQLExecDirect. I need to think about that, but atm looks like that would be right thing to do. That should help with reaching max_prepared_stmt_count, I guess. The part that it should not use prepared statements for SQLExecDirect is a "task", not a "bug". Btw they are not used for upserts, but in case of statements returning resultsets, it's a bit more complicated.

Comment by Lawrin Novitsky [ 2019-02-12 ]

I changed this to the bug, meaning close of the stmt handler automatically. I am still not sure if it is actually possible to do. Well, it may be too complicated keeping in mind cursor and positioned operations. And I opened task for not using prepared statements in SQLExecDirect in the new version(in 3.2 atm). Linked to this issue.
Not touching "blocker" priority so far.

Comment by Lawrin Novitsky [ 2019-02-21 ]

Ok, it's not really possible to close stmt handler after execution. Well, it's possible, but requires too much work while having downsides that exceed the gains. And we don't see this as "creating ddos type attack", so I don't see the need to make that behavior(closing stmt handler after execution) optional.
While the task of making SQLExecDirect to use text protocol(aka mysql_query aka not using prepared statements) remains as part of 3.2 version development. The jira task for it is linked to this report.

Generated at Thu Feb 08 03:27:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.