0

I have setup TiDB for my .net core app. I'm connecting TiDB with the MYSQL connector in the .net core app. Below is my connection string

server=server1,server2,server3;Port=4000;UID=test_user;PWD=****;database=Test_database;ConnectionReset=false;

As I have specified all the nodes in the connection string with comma separator and when tried to put some load it's always going to the first node. Ideally requests load should have been distributed across all the nodes.

I ran the SHOW FULL PROCESSLIST command to check app requests which are showing in the first node only.

Here is my source code to connect TiDB.

public async Task<Error> ExecuteStatementAsync(Context ctx, MySqlCommand command)
        {
            Error error = default;
            try
            {               
                using (MySqlConnection connection = new MySqlConnection(_connectionString))
                {
                    MySqlTransaction trans = default;
                    try
                    {
                        await connection.OpenAsync();
                        trans = connection.BeginTransaction();
                        using MySqlCommand cmd = command;
                        cmd.Connection = connection;
                        cmd.Transaction = trans;
                        cmd.Prepare();
                        await cmd.ExecuteNonQueryAsync();
                        trans.Commit();
                        return null;
                    }
                    catch (Exception ex)
                    {
                        // populate error
                        if (trans != null)
                        {
                            trans.Rollback();
                        }

                        return error;
                    }
                }
            }
            catch (Exception ex)
            {
                // populate error
                return error;
            }
        }

public async Task<(DataTable, Error)> SelectAsync(Context ctx, MySqlCommand command, string queryIdentifier)
        {
            Error error = default;
            try
            {
                using (MySqlConnection connection = new MySqlConnection(_connectionString))
                {
                    DataTable dt = new DataTable();
                    MySqlCommand cmd = command;
                    try
                    {
                        await connection.OpenAsync();
                        cmd.Connection = connection;
                        cmd.Prepare();
                        using (DbDataReader reader = await cmd.ExecuteReaderAsync())
                        {
                            dt.Load(reader);
                        }
                        return (dt, null);
                    }
                    catch (Exception ex)
                    {
                        return (null, error);
                    }
                }
            }
            catch (Exception ex)
            {
                return (null, error);
            }
        }

Is there anything wrong with the code or connection string? Why it's happening so?

bilusi taylors
  • 106
  • 7
suresh rajput
  • 169
  • 2
  • 9
  • Are you using MySqlConnector or MySql.Data? Are you using more than one connection at a time simultaneously? Connections to the server are pooled in MySqlConnector, so if you open one, close it, open one, close it, open one, close it, you'll just pull the same connection off the pool repeatedly. – Bradley Grainger Mar 02 '21 at 23:04
  • @BradleyGrainger Yes m using MySqlConnector for connection. – suresh rajput Mar 30 '21 at 05:08
  • Are you using more than one connection at a time simultaneously? – Bradley Grainger Mar 30 '21 at 15:55
  • @BradleyGrainger There are concurrent request being executed in app. M using connection pooling. – suresh rajput Apr 01 '21 at 09:23

0 Answers0