1

I have application, which connects to MySQL database. Everything is fine when app runs on Windows Server 2016, but lastly I migrated it to Azure App Service as continous Web Job and I get completely randomly errors:

MySql.Data.MySqlClient.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts. ---> 
MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired.  
The timeout period elapsed prior to completion of the operation or the server is not responding.
   at MySql.Data.Common.StreamCreator.GetTcpStream(MySqlConnectionStringBuilder settings)
   at MySql.Data.Common.StreamCreator.GetStream(MySqlConnectionStringBuilder settings)
   at MySql.Data.MySqlClient.NativeDriver.Open()
   at MySql.Data.MySqlClient.NativeDriver.Open()
   at MySql.Data.MySqlClient.Driver.Open()
   at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)
   at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection()
   at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()
   at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver()
   at MySql.Data.MySqlClient.MySqlPool.GetConnection()
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Dapper.SqlMapper.<ExecuteImplAsync>d__39.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()``

I try to create connection every time when my app retrieves message from queue. I use Dapper and piece of my code, which connects to MySQL looks like this:

using (IDbConnection connection = new MySqlConnection(connectionString))
{
    return await connection.QueryAsync<Resource>(this._getContactsToSync);
}

MySQL version is 5.5 - quite old, but it's customer external source and I can't do anything with that. I tested three versions of MySQL drivers for .NET (8.0, 6.9, 6.10) - error occurs with every of them.

I found somewhere information, that this situation could be caused by to many open sockets, but I tested app locally with netstat command and everything looks good.

My issue is similar to this: Random connection problems from Azure C# app to remote MySQL server, but there is'nt any helpful answer.

I've never done anything with MySQL, so I'm not an expert with this tool. Maybe someone has similar situation???

robr
  • 55
  • 8
  • Try to add your IP to whitelist in hosting admin panel – LinkedListT Jan 17 '20 at 14:10
  • I added every outbound IP address from Azure Web App to whitelist in MySQL. I investigated logs from Azure in Wireshark and I noticed that in situation when error occurs there is a problem with creating new TCP connection. It looks like my request sometimes doesn't go out from Azure (???) – robr Jan 17 '20 at 14:16
  • Try removing 'port' in connection string – LinkedListT Jan 17 '20 at 14:19
  • I don't have port in connection string: `Server=123.123.123.123; Database=xxxxx; Uid=xxxxx; Pwd=xxxxxx; Convert Zero Datetime=True;maximumpoolsize=20;Connection Timeout=10;` – robr Jan 17 '20 at 14:21
  • Maybe one of the fixes in this thread could help. https://stackoverflow.com/questions/17993657/unable-to-connect-to-any-of-the-specified-mysql-hosts-c-sharp-mysql – LinkedListT Jan 17 '20 at 14:28
  • Unfortunately, it didn't help. I noticed today that when I use _tcpping_ on MySQL port in Azure Kudu console I receive timeouts from destination. Customer said that, he doesn't receive timeouted packets, I don't know where they are lost. Maybe somewhere in Azure? – robr Jan 20 '20 at 19:13
  • then, make sure every variable is defined in your connection string. including port. – LinkedListT Jan 20 '20 at 19:47
  • they are. I'm logging them before creating of connection. – robr Jan 20 '20 at 19:57
  • try specifying port as a variable. `server=127.0.0.1;userid=azure;password=XXXX;database=localdb;Port=NNNN ` – LinkedListT Jan 21 '20 at 20:49
  • i got logs from mysql server and there is a lot of information like that: _/var/log/mysql/mysql_error.log:111111 11:11:11 [Warning] Aborted connection 999 to db: 'xxxxxx' user: 'xxxxx' host: '111.111.111.111' (Got an error reading communication packets)_ – robr Jan 23 '20 at 14:41
  • can you post the full log – LinkedListT Jan 23 '20 at 14:45
  • This looks like the issue you're facing. https://stackoverflow.com/questions/11860486/mysql-how-to-diagnose-cause-of-warning-aborted-connection-got-timeout-read – LinkedListT Jan 23 '20 at 14:48
  • Unfortunately I don't have full logs :( File which I received contains logs like I posted above. – robr Jan 23 '20 at 14:49

0 Answers0