Bug #70587 Dot Net Distributed transaction not supported in MySql Server 5.6
Submitted: 10 Oct 2013 11:48
Reporter: Ranjeet Bisht Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: XA transactions Severity:S4 (Feature request)
Version:mySql Server 5.6 OS:Microsoft Windows (MS Windows 7 Professional)
Assigned to: CPU Architecture:Any
Tags: distributed transaction not supported currently in MySql connector, Distributed Transction, transaction, XA Transaction

[10 Oct 2013 11:48] Ranjeet Bisht
Description:
I am Senior developer using C# with asp.net facing distributed transaction not supported currently in MySql connector.

I am using MySQL ENGINE = InnoDB and In Connection string set AutoEnlist = True and providerName="MySql.Data.MySqlClient"

Server -- Web server as IIS
Platform ---> VS 2008
Database --> Oracle11g and MySql Server 5.6
MySql Connector --> connector Net 6.7.4 with version 2.0

I am trying to insert record in first MySql DB and than update in oracle DB but always get exception distributed transaction not supported currently in MySql connector.
Can anybody atleast tell me it is possible or not, if yes than How can i acheive it. Please see the code below...

protected void Button3_Click_SqlInsert_Another(object sender, EventArgs e)
        {
            CitiesAccess citiesAccess = new CitiesAccess();
            List<Cities> lstCities;
            lstCities = citiesAccess.GetCities();

            // Initialize the return value to zero and create a StringWriter to display results. 
            int returnValue = 0;

            string mySQL = ConfigurationManager.ConnectionStrings["MYSQLConnectionString"].ConnectionString.ToString();
            string myOracle = System.Configuration.ConfigurationManager.ConnectionStrings["DMSConnectionString"].ToString();          

            try
            {
                foreach (Cities cities in lstCities)
                {
                    // Create the TransactionScope to execute the commands, guaranteeing 
                    // that both commands can commit or roll back as a single unit of work. 
                    using (TransactionScope scope1 = new TransactionScope())
                    {
                        using (MySqlConnection mysqlCon = new MySqlConnection(mySQL))
                        {                            
                            mysqlCon.Open();
                            MySqlCommand cmd = new MySqlCommand(Constants.Cities.SP_INSERTUPDATECITIES);
                            //cmd.CommandTimeout = 0;
                            cmd.Connection = mysqlCon;
                            cmd.CommandType = CommandType.StoredProcedure;
                            
                            if (cities.CityID != 0)
                            {
                                cmd.Parameters.AddWithValue("in_city_id", cities.CityID);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("in_city_id", DBNull.Value);
                            }
                            //if (cities.CityID == 1)
                            //    cmd.Parameters["in_city_id"].Value = "sdsds";

                            cmd.Parameters.AddWithValue("in_city_code", cities.CityCode);
                            cmd.Parameters.AddWithValue("in_city_name", cities.CityName);
                            if (cities.DistrictId != 0)
                            {
                                cmd.Parameters.AddWithValue("in_district_id", cities.DistrictId);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("in_district_id", DBNull.Value);
                            }
                            cmd.Parameters.AddWithValue("in_servicable", cities.Servicable);
                            if (cities.CreatedBy != 0)
                            {
                                cmd.Parameters.AddWithValue("in_created_by", cities.CreatedBy);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("in_created_by", DBNull.Value);
                            }
                            cmd.Parameters.AddWithValue("in_creation_date", cities.CreationDate);
                            if (cities.LastUpdatedBy != 0)
                            {
                                cmd.Parameters.AddWithValue("in_last_updated_by", cities.LastUpdatedBy);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("in_last_updated_by", DBNull.Value);
                            }

                            cmd.Parameters.AddWithValue("in_last_updated_date", cities.LastUpdatedDate);
                            if (cities.LastUpdatedLogin != 0)
                            {
                                cmd.Parameters.AddWithValue("in_last_updated_login", cities.LastUpdatedLogin);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue("in_last_updated_login", DBNull.Value);
                            }
                            cmd.ExecuteNonQuery();

                            //////////////////////
                            using (OracleConnection MyOracleconn = new OracleConnection(myOracle))
                            {                                
                                MyOracleconn.Open();

                                OracleCommand cmdo = new OracleCommand("obs_update_cities_tflag_proc1", MyOracleconn);
                                cmdo.CommandType = CommandType.StoredProcedure;
                                OracleParameter inval = new OracleParameter("in_city_id", OracleType.VarChar, 50);
                                inval.Direction = ParameterDirection.Input;
                                inval.Value = cities.CityID;
                                if (cities.CityID == 2)
                                    inval.Value = "sdsd";
                                cmdo.Parameters.Add(inval);
                                cmdo.ExecuteNonQuery();
                            }
                        }
                        //// Now scope commit
                        scope1.Complete();                        
                    }
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
            }
        }

How to repeat:
Please try to insert records in 2 table of different databases in single transaction.

1st table should be in MySql and 2nd should be in Oracle
[17 Jul 2015 14:42] Georgi Kodinov
Putting a more concrete category.