none
Error while accessing MySQL from C# script using U-SQL from Azure Data Lake.

    Question

  • I have created function which takes connection string as input and get the data of table from My SQL. When I am calling the function which is giving following error. This is working fine on local execution. Please suggest

    ==========================================================

      "diagnosticCode": 195887111,
      "severity": "Error",
      "component": "RUNTIME",
      "source": "User",
      "errorId": "E_RUNTIME_USER_EXPRESSIONEVALUATION",
      "message": "Error while evaluating expression new MySQLConector.CustomConnector(Datasource, @\"Department\").GetConsoleDataWithModification(new SqlArray<string>(new string[]{\"Id\", \"Name\"}))",
      "description": "Inner exception from user expression: Unable to connect to any of the specified MySQL hosts.\nCurrent row dump: \tDatasource:\t\"Database=as_eae46ef4160d957;Data Source=XXXXXXXXXXXXXXX;User Id=XXXXX;Password=XXXX;\"\r\n\n",
      "resolution": "",
      "helpLink": "",
      "details": "==== Caught exception MySql.Data.MySqlClient.MySqlException\n\n   at MySql.Data.MySqlClient.NativeDriver.Open()\r\n\n   at MySql.Data.MySqlClient.Driver.Open()\r\n\n   at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)\r\n\n   at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection()\r\n\n   at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()\r\n\n   at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()\r\n\n   at MySql.Data.MySqlClient.MySqlPool.GetConnection()\r\n\n   at MySql.Data.MySqlClient.MySqlConnection.Open()\r\n\n   at MySQLConector.CustomConnector.GetConsoleDataWithModification(SqlArray`1 selectParams)\r\n\n   at ___Scope_Generated_Classes___.SqlFilterTransformer_13.Process(IRow row, IUpdatableRow output) in d:\\data\\CCS\\jobs\\f15bd382-7ee5-443f-bcde-d4a961ba0d83_v0\\__ScopeCodeGen__.dll.cs:line 254\n\n==== Inner exception System.Net.Sockets.SocketException\n\nNo such host is known\n\n   at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)\r\n\n   at System.Net.Dns.GetHostEntry(String hostNameOrAddress)\r\n\n   at MySql.Data.Common.MyNetworkStream.CreateStream(MySqlConnectionStringBuilder settings, Boolean unix)\r\n\n   at MySql.Data.Common.StreamCreator.GetTcpStream(MySqlConnectionStringBuilder settings)\r\n\n   at MySql.Data.MySqlClient.NativeDriver.Open()"

    =============================================================

    Code snippet I have given below.                    

    public SqlArray<SqlArray<string>> GetConsoleDataWithModification(SqlArray<string> selectParams)
            {
                //build select statement for table
                var sb = new StringBuilder("Select ");
                selectParams.AsEnumerable<string>().ToList<string>().ForEach(delegate (string selectparam) {
                    sb.Append(string.Format("{0} ,",selectparam));
                });
                sb.Remove(sb.Length - 1, 1);
                sb.Append(string.Format(" from {0}", _table));

                var rowList = new List<SqlArray<string>>();
                using (MySqlConnection conn = new MySqlConnection(_connString))
                {
                    conn.Open();
                    using (var cmd = new MySqlCommand(sb.ToString(), conn))
                    {
                        var reader = cmd.ExecuteReader();

                        while (reader.Read())
                        {
                            List<string> lst = new List<string>();
                            for (int i=0; i<reader.FieldCount; i++)
                            {
                                lst.Add(reader.GetValue(i).ToString());
                            }
                            var row = SqlArray.Create<string>(lst);
                            rowList.Add(row);
                        }
                        reader.Close();
                    }
                    conn.Close();
                }
                var result = SqlArray.Create<SqlArray<string>>(rowList);
                return result;
            }

    I have used the function in U-SQL to get data once I have registered the respective dll's and code is like following

    DECLARE @tablename string = "employee";

    REFERENCE ASSEMBLY master.[MySql.Data];
    REFERENCE ASSEMBLY master.[MySQLConector];


    @OUTPUT =
        SELECT new MySQLConector.CustomConnector(Datasource, @tablename).GetConsoleDataWithModification(new SqlArray<string>() {"Name", "Age","Salary","DepartmentID"}) AS Data
        FROM catalogdb.dbo.DataSourceDetails WHERE DBName == "azureemployeedb";

    @result =
        SELECT r[0]AS Name,
              Convert.ToInt32(r[1]) AS Age,
               Convert.ToInt32(r[2]) AS Salary,
               Convert.ToInt32(r[3]) AS DepartmentID
        FROM @OUTPUT
             CROSS APPLY
                 EXPLODE(Data) AS Refs(r);

    OUTPUT @result
    TO "/poc/output/result.txt"
    USING Outputters.Text();


    • Edited by Nemana Thursday, March 3, 2016 6:07 AM
    Thursday, March 3, 2016 6:06 AM

Answers

  • Nemana,

    Currently, UDO's do not have external network access when running in the service, however that behavior is allowed when running locally.  The team is considering ways to relax this constraint, but I don't have an update on any type of timing there.

    Please consider voting on the feedback idea here, as well as providing more details on key sources in the comments: https://feedback.azure.com/forums/327234-data-lake/suggestions/12494202-in-future-are-we-having-any-functionality-like-que

    ---mw


    Program Manager -- hadoop -- http://blogs.msdn.com/mwinkle


    Thursday, March 3, 2016 3:28 PM