0

I have the following INSERT method in a C# web project. If I run the project without MySQL connection poling everything works fine, but when I activate Pooling=True in the DB connection string this method stops working, the insert statements never complete.

I realized how to modify the code to make it work, but I would like to understand what is happening and I hope you could help.

When I comment line //myR.Close(); everything works fine.

using MySql.Data.MySqlClient;
//query example consulta="INSERT INTO users (id, name) VALUES (1, 'Rob');

public static MySqlConnection GetWriteConnection()
    {
        string connStr = MySqlConnectionStrings.WriteConnectionString;
        MySqlConnection conn = new MySqlConnection(connStr);
        return conn;
    }

public static MySqlConnection GetReadConnection()
    {
        string connStr = MySqlConnectionStrings.ReadConnectionString;
        MySqlConnection conn = new MySqlConnection(connStr);
        return conn;
    }

public static bool Insert(string consulta)
    {
        MySqlConnection conn = BdaHelper.GetWriteConnection();
        conn.Open();

        using (conn)
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                MySqlCommand micomando = new MySqlCommand(consulta, conn);
                micomando.ExecuteNonQuery(); //still not working

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

    }

My app has also multi-thread concurrency and two types of database connections, one specifically for only-read purposes and other different for write. When an insert statement fails I don't get any error simply the change doesn't commit in the database. Reading the article in the comments I don't think this applies to this issue but I would add an example of my main program:

MySqlConnection readConnection = BdaHelper.GetReadConnection();

    using (readConnection)
    {
        var users = GetUsers(readConnection);
        var credentials = GetCredentials(readConnection);

        //Example is the query that fails don't giving any exception
        Insert("INSERT INTO login_log (id_user, date) VALUES (1, now())");
    }
  • May the problem be caused because there are two concurrent connections?
  • I shouldn't reuse read connection, even is a different connection than the write connection?
4
  • Does this answer your question? C# MySQL Connection Pooling Commented May 25, 2020 at 14:04
  • 1
    What does BdaHelper.GetWriteConnection do? If it doesn't create a new connection every time it's called then that's likely your issue. Commented May 25, 2020 at 14:55
  • 1
    There are several ways you could improve this code. If you don't need a MySqlDataReader (e.g., for an INSERT query), use micomando.ExecuteNonQuery(). When you do use a reader, call while (myR.Read()) to read the rows. Always use new MySqlConnection and MySqlDataReader objects in a using block, e.g., using (var myR = micomando.ExecuteReader()). You probably have object leaks that are exhausting your connection pool because you're not cleaning up your objects properly. Commented May 25, 2020 at 19:21
  • Thank you for the answers, I was not using ExecuteNonQuery because I was testing a differenst approachs but as you said is the most convenient for this case. I don't know it the corrurrent thread post has to do with this issue because MySQL connections are different and READ query allways works fine, the issue is only with READ. Something importa is that even the connections are different connects with the same database same. Commented May 26, 2020 at 8:04

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.