Execute method that supports SQL queries with parameters

[hoylen]

There needs to be an execute method that can take a SQL statement and a list of parameters: Future<StreamedResults> execute(String sql, Iterable values). Sometimes (often?) you want to run a query which has parameters (e.g. from user input), but don't want to create a prepared statement.

The old sqljocky5 v1.x had such a method.

In the Transaction class of sqljocky5 v2.2.1, there is an execute method that does not accept parameters (only a string) or the prepared method which does (which takes a string and an Iterable). But if you keep using the prepared method, eventually you reach the max_prepared_stmt_count limit in MySQL (and no more queries can be run). The prepared method shouldn't exist, since it creates prepared statements on the database that can't be deallocated (other than by closing the connection).

(The execute-with-parameters method is probably also needed for the Connection, but I only need it for a Transaction.)

[hoylen]

Until this gets fixed, here's a workaround for anyone else who has this problem:

The application should keep track of the total number of prepare and prepared invocations on a connection, and after too many have been created: close the connection and open another one to use.

Obviously, if you are using transactions, the close/reopen can't happen in the middle of it. You'll have to continue using the connection until the commit or rollback to do it. So it is still possible for a single transaction to still exceed the max_prepared_stmt_count. Using execute, when there are no parameters, can help reduce how quickly it reaches that limit.

This problem is more likely to be encountered if you reuse connections for executing many parameterised queries. For example, if you implement connection pooling in a long-running application.

The source of the problem in sqljocky5 v2.2.1 is caused by how the prepared method has been implemented: it creates a stored query and then executing it with the parameters. But it does not release/delete the stored query afterwards. If you use prepare to create and manage your own stored queries, the deallocate method on PrepareImpl cannot be used to release/delete it (the implementation just throws an UnimplementedError).

The prepareExecute method in sqljocky5 v1.x did correctly close the stored query after using it. But that version no longer works with Dart 2.x.

The max_prepared_stmt_count will depend on the particular MySQL/MariaDB being used. In my case, there's a maximum of about 16290 stored queries per connection. I close the connection after committing or rolling back a transaction and there has been 5000 or more stored queries. A higher number will reuse the connection more, but reduce the maximum number of parameterised queries that can be executed within one transaction.

In my workaround, I also track the SQL statements to reuse previously created prepared statements when possible. That helps reduce how quickly the limit is reached. (Note: you'll then start seeing "Cannot write to socket, it is closed" errors caused by a bug in the close method.)

[u007]

im facing the same issue:
Can't create more than max_prepared_stmt_count statements

[u007]

made a merge request. i think since prepared does not allow you to get hold of the prepared handler, its better to call prepare, and then execute and finally close.

#38