locked
Dynamically change connection on database RRS feed

  • Question

  • User1151703306 posted

    Hi all, I need your help.

    am faced to an issue i can't find the solution, perhaps someone can help.

    Is it possible to dynamically change the MySQL connection used by the application when the first connection it's unavailable for stopped server ?

    I have the replicated clone server that should be queried when the first MySQL connection on main server it's unavailable.

    With which programming language can I get this if it is possible?

    do you have any example for make a separate function which return the connection?

    I use C# on ASPNET, Classic ASP and VBScript.

    Thanks in advance for any suggestion, really appreciated.

    Monday, October 14, 2019 9:09 AM

Answers

  • User-2054057000 posted

    What you can do it to add 2 connection strings on your web.config file and refer any of them in your .cs class using ADO.NET. Refer this tutorial - https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-strings-and-configuration-files

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 14, 2019 9:52 AM
  • User753101303 posted

    Seems your intent was to use :

    using (OdbcConnection conn=_GetDataConnection())

    For now you just call your method but you do nothing with the returned connection and you use a 3rd connection string in your main code. It could be optimized (for example keeping the last connection string name that worked to try it first).

    Also what will happen when the first server comes back online ? What are you using for now to keep server in sync? If built in feature, you usually add something in the connection string so that the driver is handling that for you.

    If not how do you ensure they are kept in sync before and after the problem or do you plan some manual intervention when it happens? 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 14, 2019 12:08 PM

All replies

  • User753101303 posted

    Hi,

    Regardless of what you are using you could have a list of connection strings and keep track if which one failed but it's likely a bit more complex than that (ie what are you doing once the failed server is back online etc...)

    Most often DBMS have support to handle that. Try perhaps https://mysqlhighavailability.com/

    For this kind of advanced question, a specialized MySQL Forum would be likely better...

    Monday, October 14, 2019 9:33 AM
  • User-2054057000 posted

    What you can do it to add 2 connection strings on your web.config file and refer any of them in your .cs class using ADO.NET. Refer this tutorial - https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-strings-and-configuration-files

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 14, 2019 9:52 AM
  • User1151703306 posted

    yogyogi

    What you can do it to add 2 connection strings on your web.config file and refer any of them in your .cs class using ADO.NET. Refer this tutorial - https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-strings-and-configuration-files

    Hi, thank you for help.

    I have tried this solution, but if conn1 is not available I have error : ERROR [HY000] [MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'XX.XX.XX.XXX' (10060) 

        string sql;
        DataSet dsProducts = new DataSet();
    
        private static OdbcConnection _GetDataConnection()
        {
            string conn1 = ConfigurationManager.ConnectionStrings["ConnectionStr1"].ConnectionString;
            string conn2 = ConfigurationManager.ConnectionStrings["ConnectionStr2"].ConnectionString;
    
            OdbcConnection conn = null;
    
            try
            {
                conn = new OdbcConnection(conn1);
                conn.Open();
            }
            catch (OdbcException)
            {
                conn = new OdbcConnection(conn2);
            }
    
            return conn;
        }
    
        protected void Page_Load(object sender, EventArgs e)
        {
    
            if (!Page.IsPostBack)
            {
                _GetDataConnection();
    
                ddl1.AppendDataBoundItems = true;
    
                sql = @String.Format(" SELECT ES FROM `adoTable`; ");
    
                using (OdbcConnection myConnectionString =
                  new OdbcConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
                {
                    using (OdbcCommand cmd =
                        new OdbcCommand(sql, myConnectionString))
                    {
    
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection.Open();
    
                        using (OdbcDataAdapter sda =
                            new OdbcDataAdapter(cmd))
                        {
                            sda.Fill(dsProducts);
                            ddl1.DataSource = dsProducts.Tables[0];
                            ddl1.DataTextField = "ES";
                            ddl1.DataValueField = "ES";
                            ddl1.DataBind();
    
                            if (ddl1.Items.Count > 1)
                            {
                                ddl1.Enabled = true;
                            }
                            else
                            {
                                ddl1.Enabled = false;
                            }
                        }
                    }
                }
            }
        }

    Monday, October 14, 2019 11:26 AM
  • User475983607 posted

    Web applications should not manage database downtime.   

    However, this a basic state management question.  Create a static variable to hold the connections string.  This can be accomplished by setting an Application variable, cache, or building a singleton. 

    Application["ConnectionString"] = "[the connection string]"

    Monday, October 14, 2019 11:49 AM
  • User1151703306 posted

    Thank you !

    Problem solved...

    but how do I know the name open connection?

    I have tried without success HttpContext.Current.Response.Write(conn.DataSource.ToString() + "<br /><br />"); 

    the output is blank 

    Can you help me ?

    string sql;
    DataSet dsProducts = new DataSet();
    public static OdbcConnection conn;
    
    private static OdbcConnection _GetDataConnection()
    {
        string conn1 = ConfigurationManager.ConnectionStrings["ConnectionStr1"].ConnectionString;
        string conn2 = ConfigurationManager.ConnectionStrings["ConnectionStr2"].ConnectionString;
    
        conn = null;
    
        try
        {
            conn = new OdbcConnection(conn1);
        }
        catch (OdbcException)
        {
            conn = new OdbcConnection(conn2);
        }
    
        HttpContext.Current.Response.Write(conn.DataSource.ToString() + "<br /><br />");
        return conn;
    }
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            _GetDataConnection();
    
            if (conn != null && conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Open();
            }
    
            ddl1.AppendDataBoundItems = true;
    
            sql = @String.Format(" SELECT ES FROM `adoTable`; ");
    
            using (conn)
            {
                using (OdbcCommand cmd =
                    new OdbcCommand(sql, conn))
                {
                    try
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection.Open();
    
                        using (OdbcDataAdapter sda =
                            new OdbcDataAdapter(cmd))
                        {
                            sda.Fill(dsProducts);
                            ddl1.DataSource = dsProducts.Tables[0];
                            ddl1.DataTextField = "ES";
                            ddl1.DataValueField = "ES";
                            ddl1.DataBind();
    
                            if (ddl1.Items.Count > 1)
                            {
                                ddl1.Enabled = true;
                            }
                            else
                            {
                                ddl1.Enabled = false;
                            }
                        }
                    }
                    catch (OdbcException)
                    {
                        cmd.Connection.Close();
                        cmd.Connection.Dispose();
                    }
                }
            }
        }
    }

    Monday, October 14, 2019 12:08 PM
  • User753101303 posted

    Seems your intent was to use :

    using (OdbcConnection conn=_GetDataConnection())

    For now you just call your method but you do nothing with the returned connection and you use a 3rd connection string in your main code. It could be optimized (for example keeping the last connection string name that worked to try it first).

    Also what will happen when the first server comes back online ? What are you using for now to keep server in sync? If built in feature, you usually add something in the connection string so that the driver is handling that for you.

    If not how do you ensure they are kept in sync before and after the problem or do you plan some manual intervention when it happens? 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 14, 2019 12:08 PM
  • User475983607 posted

    IMHO, this is a very poor design.  The best approach is using the tools available in MySQl.  As written, the code must timeout before switching.  As explained in my previous thread, you should use a static variable to hold the current connection string.  Cache works well because you can set a timeout to reset the connection to the default but that can be dangerous.  Application state works too be you'll need to write code to reset the connection. 

    There are other questions like how to sync the databases.  If you switch back and forth how will you sync the databases; things like keys?  It seems you need to take a deeper look at your approach.

    Monday, October 14, 2019 12:23 PM