Question How To DEALLOCATE PREPARE Statement Using Mysql.Data Client?

[SiamIT]

Greetings..

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 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

 

[cjard]

Sounds like the use of prepare is wrong, or unnecessary, but we can't see the relevant code

Post entire code of DbWriter class

 

[SiamIT]

Sounds like the use of prepare is wrong, or unnecessary, but we can't see the relevant code

Post entire code of DbWriter class

@cjard here is the class code as you have asked for:

using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;

using MySql.Data.MySqlClient;

namespace DatabaseProject
{
    public class DatabaseWriter
    {
        #region Variables
        private string _host;
        private string _name;
        private string _user;
        private string _password;

        private MySqlConnection _engine;
        private MySqlCommand _CMD;

        #endregion

        #region Properties

        public string Host
        {
            get
            {
                return _host;
            }
            set
            {
                _host = value;
            }
        }

        public string Name
        {
            get
            {
                return _name;
            }
            set
            {
                _name = value;
            }
        }

        public string User
        {
            get
            {
                return _user;
            }
            set
            {
                _user = value;
            }
        }

        public string Password
        {
            get
            {
                return _password;
            }
            set
            {
                _password = value;
            }
        }

        #endregion

        #region Methods

        public DatabaseWriter()
        {
            //do nothing
        }

        public bool Connect()
        {
            //trying to connect to server
            string ConnectionInfo = $"server={_host};uid={_user};pwd={_password};database={_name};Convert Zero Datetime=True";
            try
            {
                _engine = new MySqlConnection
                {
                    ConnectionString = ConnectionInfo
                };
                _engine.Open();
            }
            catch(MySqlException ex)
            {
                return false;
            }
            //connection good
            _CMD = new MySqlCommand()
            {
                Connection = _engine,
            };

            return true;
        }

        public void Disconnect()
        {
            _CMD.Dispose();
            _engine.Close();
            _engine.Dispose();
        }

        private void Prepare(string query, List<KeyValuePair<string, string>> args)
        {
            _CMD.CommandText = query;
            //setting default params value
            _CMD.Parameters.Clear();
            if(args != null)
            {
                foreach(KeyValuePair<string, string> arg in args)
                {
                    _CMD.Parameters.Add(new MySqlParameter(arg.Key, arg.Value));
                }
            }
            _CMD.Prepare();
        }

        private bool Write(string query, List<KeyValuePair<string, string>> args)
        {
            Prepare(query, args);
            //execute the query
            try
            {
                _CMD.ExecuteNonQuery();
            }
            catch(MySqlException ex)
            {
                return false;
            }

            return true;
        }

        public Chilkat.JsonObject Read(string query, List<KeyValuePair<string, string>> args)
        {
            Prepare(query, args);
            //execute the query
            try
            {
                MySqlDataReader reader = _CMD.ExecuteReader();
                Chilkat.JsonObject oJsonData = new Chilkat.JsonObject();
                Chilkat.JsonArray oJDataArray = oJsonData.AppendArray("data");
                while(reader.Read())
                {
                    oJDataArray.AddObjectAt(-1);
                    Chilkat.JsonObject oJDataItem = oJDataArray.ObjectAt(oJDataArray.Size - 1);
                    for(int iColumnLoop = 0; iColumnLoop < reader.FieldCount; iColumnLoop++)
                    {
                        oJDataItem.AppendString(reader.GetName(iColumnLoop), reader.GetString(iColumnLoop));
                    }
                }
                reader.Close();
                reader.Dispose();
                return oJsonData;
            }
            catch(MySqlException ex)
            {
                return null;
            }
        }

        public long Count(string query, List<KeyValuePair<string, string>> args)
        {
            Prepare(query, args);
            //execute the query
            try
            {
                return (long)_CMD.ExecuteScalar();
            }
            catch(MySqlException ex)
            {
                return -1;
            }
        }

        #endregion
    }
}

thanks in advance
best regards

 

[Skydiver]

Too busy right now to give a more detailed reply. Short answer is that you should only be using prepared statements for a query that you will keep re-using. I suggest commenting out line 125 for now. Right now, the way your class is written, it being used as a general purpose wrapper which calls prepare for all queries.

See your lines 23-28 in post #1 where you just pass in the same dbEngine into your InsertOrUpdateSalesOrder() method. Since the only public methods of your wrapper is just for Write(), Read(), or Count(), all of those prepared statements will stack up until you close your wrapper.

 

[cjard]

Hmm yes.. I haven't really dug into it much, but generally this is how you're supposed to use prepared statements (pseudo code)

var cmd = new Command("INSERT INTO person VALUES(@name, @age)");
cmd.Prepare();

cmd.Parameters.AddWithValue("@name", "dummy value");
cmd.Parameters.AddWithValue("@age", 0);

foreach(var p in listOfOneMillionPeople){
    cmd.Parameters["@name"].Value = p.Name;
    cmd.Parameters["@age"].Value = p.Age;
    cmd.ExecuteNonQuery();
}

That is "create, prepare, execute, execute, execute..."

And not like

foreach(var p in listOfOneMillionPeople){
    var cmd = new Command("INSERT INTO person VALUES(@name, @age)");
    cmd.Prepare();

    cmd.Parameters.AddWithValue("@name", p.Name);
    cmd.Parameters.AddWithValue("@age", p.Age);
    cmd.Parameters["@name"].Value = p.Name;
    cmd.Parameters["@age"].Value = p.Age;
    cmd.ExecuteNonQuery();
}

That is to say, not like "create, prepare, execute, create, prepare, execute, create, prepare, execute.."

Fine manual: MySQL :: MySQL Connector/NET Developer Guide :: 5.4 Preparing Statements in Connector/NET

 

[cjard]

But seriously, throw all this away and use dapper, or entity framework core. It's painful to see people struggling like crazy with some desperately outdated, ineffective "helper" class.

That's more like a "hinderer" class..