Prepared statements not being closed

[majenkotech]

When a prepared statement is created then immediately executed in the module the statement is not being closed.

Closing a statement

Prepared statements are created server-side, and thus consume server resources. If you don't need a statement anymore, you can call connection::close_statement or connection::async_close_statement to instruct the server to deallocate it.

Prepared statements are managed by the server on a per-connection basis. Once you close your connection with the server, all prepared statements you have created using this connection will be automatically deallocated.

If you are creating your prepared statements at the beginning of your program and keeping them alive until the connection is closed, then there is no need to call close_statement(), as closing the connection will do the cleanup for you. If you are creating and destroying prepared statements dynamically, then it is advised to use close_statement() to prevent excessive resource usage in the server.

Finally, note that statement's destructor does not perform any server-side deallocation of the statement. This is because closing a statement involves a network operation that may block or fail.

Example, in execute_prepared():

        if (type == TCP){
                mysql::statement prep_stmt = tcp_conn->prepare_statement(query, ec, diag);
                SQL_EXCEPTION(ec, diag, &last_error, Ref<SqlResult>());
                tcp_conn->execute(prep_stmt.bind(args.begin(), args.end()), result, ec, diag);
        }

The statement is created (on the server), executed, then the reference to that statement is discarded. It should be closed after executing.

The result as it stands is:

# SQL EXCEPTION Caught!
# ERR: SQLException in: modules/mysql/scr/mysql.cpp in function: execute_prepared() on line 291
# ERR: Code: 1461
# ERR: Description: er_max_prepared_stmt_count_reached [mysql.common-server:1461]
# Server error: Can't create more than max_prepared_stmt_count statements (current value: 16382)