hope you guys are fine?

OK.. i am using MySQL.Data client/library to access and use MySQL database. I was using happily it for sometimes on quite a few project. But suddenly facing a new issue that causing me hold on my current project. :(

Because current project makes some (looks like it's a lot) db queries. and i am facing following exception :

Can't create more than max_prepared_stmt_count statements (current value: 16382)

i am closing and disposing the db engine/connection every time i am done with it. But getting damn confused why i am still getting this error.

​here is the sample code just to give you idea.. (trimmed out unnecessary parts)

//this loop call an API with pagination and get API response
while(ContinueSalesOrderPage(apiClient, ref pageNum, days, out string response, window) == true)    
{
    //this handle the API date for the current page, it's normally 500 entry per page, and it throws the error on 4th page
    KeyValueTag error = HandleSalesOrderPageData(response, pageNum, out int numOrders, window);   
}

private KeyValueTag HandleSalesOrderPageData(string response, int pageNum, out int numOrders, WaitWindow window)
{
    numOrders = json.ArrayOf("List").Size;

    //init db
    DatabaseWriter dbEngine = new DatabaseWriter()
    {
        Host = dbHost,
        Name = dbName,
        User = dbUser,
        Password = dbPass,
    };

    //connecting to database
    bool pass = dbEngine.Connect();

    //loop through all the entry for the page, generally it's 500 entries
    for(int orderLoop = 0; orderLoop < numOrders; orderLoop  )
    {
        //this actually handle the queries, and per loop there could be 3 to 10  insert/update query using prepared statements
        KeyValueTag error = InsertOrUpdateSalesOrder(dbEngine, item, config, pageNum, orderLoop, numOrders, window);
    } 

    //here as you can see, i disconnect from db engine, and following method also close the db connection before hand
    dbEngine.Disconnect();

}

//code from DatabaseWriter class, as you see this method close and dispose the database properly
public void Disconnect()
{
    _CMD.Dispose();
    _engine.Close();
    _engine.Dispose();
}

so, as you can see i close/dispose the database connection on each page processing, but still it shows me that error on 4th page. FYI, 4th page data is not the matter i checked that. If i skip the page and only process the 4th page, it process successfully.

and after some digging more in google, i found prepare statement is saved in database server and that needs to be close/deallocate. But i can't find any way to do that using MySQL.Data Client :(

following page says:

https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

A prepared statement is specific to the session in which it was  created. If you terminate a session without deallocating a  previously prepared statement, the server deallocates it  automatically. 

but that seems incorrect, as i facing the error even after closing connection on each loop

so, i am at dead end and looking for some help here? 

thanks in advance

best regards

CodePudding user response：

From the official docs, the role of max_prepared_stmt_count is

This variable limits the total number of prepared statements in the server.

Therefore, you need to increase the value of the above variable, so as to increase the maximum number of allowed prepared statements in your MySQL server's configuration

1. Open the my.cnf file
2. Under the mysqld section, there is a variable max_prepared_stmt_count. Edit the value accordingly(remember the upper end of this value is 1048576)
3. Save and close the file. Restart MySQL service for changes to take place.

CodePudding user response：

You're probably running into bug 77421 in MySql.Data: by default, it doesn't reset connections.

This means that temporary tables, user-declared variables, and prepared statements are never cleared on the server.

You can fix this by adding Connection Reset = True; to your connection string.

Another fix would be to switch to MySqlConnector, an alternative ADO.NET provider for MySQL that fixes this and other bugs. (Disclaimer: I'm the lead author.)