MySqlConnector.MySqlException (0x80004005)

[Globulopolis]

Hi! After rebooting server, Query Logs (MySQL) App can't connect to mariadb server.

[2025-07-01 15:50:29 Local] DNS App [Query Logs (MySQL)]: MySqlConnector.MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.
   at MySqlConnector.Core.ServerSession.OpenTcpSocketAsync(ConnectionSettings cs, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 1283
   at MySqlConnector.Core.ServerSession.ConnectAsync(ConnectionSettings cs, MySqlConnection connection, Int64 startingTimestamp, ILoadBalancer loadBalancer, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 427
   at MySqlConnector.Core.ServerSession.ConnectAndRedirectAsync(ILogger connectionLogger, ILogger poolLogger, IConnectionPoolMetadata pool, ConnectionSettings cs, ILoadBalancer loadBalancer, MySqlConnection connection, Action`4 logMessage, Int64 startingTimestamp, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 697
   at MySqlConnector.Core.ServerSession.ConnectAndRedirectAsync(ILogger connectionLogger, ILogger poolLogger, IConnectionPoolMetadata pool, ConnectionSettings cs, ILoadBalancer loadBalancer, MySqlConnection connection, Action`4 logMessage, Int64 startingTimestamp, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 702
   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int64 startingTimestamp, Int32 timeoutMilliseconds, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 116
   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, Int64 startingTimestamp, Int32 timeoutMilliseconds, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ConnectionPool.cs:line 150
   at MySqlConnector.MySqlConnection.CreateSessionAsync(ConnectionPool pool, Int64 startingTimestamp, Activity activity, Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 1092
   at MySqlConnector.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlConnection.cs:line 567
   at MySqlConnector.MySqlConnection.Open() in /_/src/MySqlConnector/MySqlConnection.cs:line 522
   at QueryLogsMySql.App.InitializeAsync(IDnsServer dnsServer, String config) in Z:\Technitium\Projects\DnsServer\Apps\QueryLogsMySqlApp\App.cs:line 321
   at QueryLogsMySql.App.InitializeAsync(IDnsServer dnsServer, String config) in Z:\Technitium\Projects\DnsServer\Apps\QueryLogsMySqlApp\App.cs:line 527
   at DnsServerCore.Dns.Applications.DnsApplication.InitializeAsync() in Z:\Technitium\Projects\DnsServer\DnsServerCore\Dns\Applications\DnsApplication.cs:line 208

Go to the application settings and click Save. After that, the logs are written normally.

[ShreyasZare]

Thanks for the post. Is this issue reproducible, i.e, if you restart the server again, does the same issue occur? If you restart just the DNS service, does the issue occur or OS reboot is only causing it?

[Globulopolis]

Is this issue reproducible

Yes. After every OS reboot. Restarting mariadb only or dns.service only does not cause this error.

[ShreyasZare]

Thanks for the info. Is MariaDB running on the same server or its hosted somewhere else?

[Globulopolis]

Is MariaDB running on the same server or its hosted somewhere else?

Same server.

PS! OS Centos 10 Stream Mariadb 10.11

[ShreyasZare]

The issue is coming since the MariaDB server is not yet running when the DNS app is being initialized on startup. This causes the error you shared and thus the app's internal timers do not start essentially causing the app to not start. But when you save the config again, the same initialize call is done but this time the DB connects and the timers are initialized and it starts writing logs.

You can resolve this issue by ensuring that the DB server is ready before the DNS server starts.

[Globulopolis]

I fixed the problem as follows:

1. Open /etc/systemd/system/dns.service
2. In [Unit] section add row:

After=mysql.service mysqld.service

3. Run systemctl daemon-reload

I'm not very familiar with Linux, but it would be great if the technitium installation script could make these changes interactively.

[ShreyasZare]

Good to know you got it working.

[cfarence]

I've experienced this same behavior with SQL hosted outside the node. I have 3 DNS nodes that call the same DB server hosted inside Kubernetes. Sometimes it needs to move the pod to a different node and can cause disruptions to the SQL connection.

Since it doesn't retry the connection unless the app is reloaded. I'm guessing the best approach is to have each node have it's own SQL install?

[ShreyasZare]

I've experienced this same behavior with SQL hosted outside the node. I have 3 DNS nodes that call the same DB server hosted inside Kubernetes. Sometimes it needs to move the pod to a different node and can cause disruptions to the SQL connection.

Since it doesn't retry the connection unless the app is reloaded. I'm guessing the best approach is to have each node have it's own SQL install?

@cfarence Thanks for the post. The issue here will only occur if the DB cannot be reached when the Query Logs app starts or when you save its config. Once the app starts working (after DB connection succeeds), the timers will keep running and keep retrying even if there is any kind of error during bulk insert operation. Once the DB is back online, the app will again try to insert logs when the timer triggers in few seconds.

[Globulopolis]

After two week usage(maxLogDays is set to 14) I have a lot of rows in database.

This screenshots from mysql client. But when I try to view logs on Query Logs tab I got Command Timeout error. And error in log:

[2025-07-11 16:14:27 Local] DNS App [Query Logs (MySQL)]: MySqlConnector.MySqlException (0x80004005): The Command Timeout expired before the operation completed.
 ---> System.Net.Sockets.SocketException (125): Operation canceled
   at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.ThrowException(SocketError error, CancellationToken cancellationToken)
   at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.System.Threading.Tasks.Sources.IValueTaskSource<System.Int32>.GetResult(Int16 token)
   at MySqlConnector.Protocol.Serialization.SocketByteHandler.DoReadBytesAsync(Memory`1 buffer) in /_/src/MySqlConnector/Protocol/Serialization/SocketByteHandler.cs:line 89
   at MySqlConnector.Protocol.Serialization.SocketByteHandler.DoReadBytesAsync(Memory`1 buffer) in /_/src/MySqlConnector/Protocol/Serialization/SocketByteHandler.cs:line 98
   at MySqlConnector.Protocol.Serialization.BufferedByteReader.ReadBytesAsync(IByteHandler byteHandler, ArraySegment`1 buffer, Int32 totalBytesToRead, IOBehavior ioBehavior) in /_/src/MySqlConnector/Protocol/Serialization/BufferedByteReader.cs:line 34
   at MySqlConnector.Protocol.Serialization.ProtocolUtility.ReadPayloadAsync(BufferedByteReader bufferedByteReader, IByteHandler byteHandler, Func`1 getNextSequenceNumber, ArraySegmentHolder`1 previousPayloads, ProtocolErrorBehavior protocolErrorBehavior, IOBehavior ioBehavior) in /_/src/MySqlConnector/Protocol/Serialization/ProtocolUtility.cs:line 420
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 1070
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 37
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 131
   at MySqlConnector.MySqlDataReader.InitAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 487
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 309
   at QueryLogsMySql.App.BulkInsertLogsAsync() in Z:\Technitium\Projects\DnsServer\Apps\QueryLogsMySqlApp\App.cs:line 277
   at QueryLogsMySql.App.BulkInsertLogsAsync() in Z:\Technitium\Projects\DnsServer\Apps\QueryLogsMySqlApp\App.cs:line 277
   at QueryLogsMySql.App.BulkInsertLogsAsync() in Z:\Technitium\Projects\DnsServer\Apps\QueryLogsMySqlApp\App.cs:line 277

Mariadb config for 4 vCPU and 8Gb RAM.

[ShreyasZare]

Thanks for the details. It seems that the cleanup timer too is getting errors when attempting to delete old entries. Try to find any error logs which are repeating every 15 mins to confirm if that's the case.

I would suggest that you configure the maxLogRecords config option too to limit the number of entries in the database. I think due to large amount of entries, the DB is taking time to process the pagination query that the Query Logs viewer uses. Limiting the number of entries should help ease the issue.

[Globulopolis]

Try to find any error logs which are repeating every 15 mins to confirm if that's the case.

I looked at the system logs and didn't find any recurring errors or anything related to dns and mariadb. There is nothing suspicious in the mariadb error logs either.

due to large amount of entries, the DB is taking time to process the pagination query that the Query Logs viewer uses

But at the same time, the mysql client works without problems. The requests come from another PC and the client shows new records (with a limit of 1000 rows per page) in almost 1 second.

would suggest that you configure the maxLogRecords config option too to limit the number of entries in the database

And what value should I set at least approximately?

[ShreyasZare]

I looked at the system logs and didn't find any recurring errors or anything related to dns and mariadb. There is nothing suspicious in the mariadb error logs either.

In that case, it could be that all the records are within the 14 day period.

But at the same time, the mysql client works without problems. The requests come from another PC and the client shows new records (with a limit of 1000 rows per page) in almost 1 second.

The app uses pagination query which may be taking time. I would suggest that you try the same query the app makes using the mysql client and see how it responds.

And what value should I set at least approximately?

It depends on how many records you wish to limit. Currently you have 12mn records which may be the reason for the pagination query to time out. Try setting to a lower value you wish to retain records for.

[Globulopolis]

In that case, it could be that all the records are within the 14 day period.

Where is no errors at all in that period.

The app uses pagination query which may be taking time. I would suggest that you try the same query the app makes using the mysql client and see how it responds.

No indexes, filesort, temporary tables. This is really bad and extra slow query. With WHERE clause this query is more slower. I'd suggest to refactor this part to separate queries. One for count result with selected filters and second query to select rows with selected filters.

And when I click on Query Log tab, app running same slow query.

It depends on how many records you wish to limit. Currently you have 12mn records which may be the reason for the pagination query to time out. Try setting to a lower value you wish to retain records for.

As I understand this value is limit selected rows? Or this limit rows recorded to database?

[ShreyasZare]

No indexes, filesort, temporary tables. This is really bad and extra slow query. With WHERE clause this query is more slower. I'd suggest to refactor this part to separate queries. One for count result with selected filters and second query to select rows with selected filters.

I have limited experience with pagination queries. Do you have any specific suggestion on how to improve the query performance here? Can you give some example which will work better for use with pagination? Since you have a large dataset, it would be nice if you can test this new approach to see if its performing well.

As I understand this value is limit selected rows? Or this limit rows recorded to database?

The maxLogRecords limit will cause older records to be deleted every 15 mins.

[Globulopolis]

Do you have any specific suggestion on how to improve the query performance here?

1. Count total results with filters from form:

SELECT
  COUNT(`dlid`)
FROM
  `DnsQueryLogs`.`dns_logs`
WHERE
  where_expression;

2. Select rows with filters from form:

SELECT
  `dlid`,
  `server`,
  `timestamp`,
  `client_ip`,
  `protocol`,
  `response_type`,
  `response_rtt`,
  `rcode`,
  `qname`,
  `qtype`,
  `qclass`,
  `answer`
FROM
  `DnsQueryLogs`.`dns_logs`
WHERE
  where_expression
ORDER BY order_expression
LIMIT 0, 50;

LIMIT is a key. 0, 50 will display 50 rows starting from first row. For second page in result set this will be 50, 50 and so on. But you need to do some magic to make a proper pager(calculate valid page number). Now you make a queryLogs(2) to display second page, instead you need to do queryLogs(50), queryLogs(100) for third page.

[ShreyasZare]

Thanks for the details. From what I read, using LIMIT to do pagination is not efficient, i.e. if the OFFSET value is large then the query loads all the data in memory and skips the records till the offset value and delivers the rest. Which is why I had avoided to use it.

Source: https://qsli.github.io/2016/09/30/pagination/PPC2009_mysql_pagination.pdf

But the above presentation does not have a solution for the current use-case since we require option to allow user to jump to an arbitrary page.

[Globulopolis]

From what I read, using LIMIT to do pagination is not efficient, i.e. if the OFFSET value is large then the query loads all the data in memory and skips the records till the offset value and delivers the rest.

Scan, but not load in memory.

Source: https://qsli.github.io/2016/09/30/pagination/PPC2009_mysql_pagination.pdf

Really old. Look at this article https://dev.mysql.com/doc/refman/8.4/en/limit-optimization.html

Look at this: This is you query

Details

And this is my

Details

[ShreyasZare]

Thanks for the details. You are testing with offset value 0 so LIMIT will always perform better. Since you have 12mn records, try the offset value of 10000000 and repeat the test to see how it compares.

[Globulopolis]

Thanks for the details. You are testing with offset value 0 so LIMIT will always perform better. Since you have 12mn records, try the offset value of 10000000 and repeat the test to see how it compares.

Test on 1m dataset(logging is currently disabled on main server). If you need tests on extra large dataset let me know, and then I turn on logger on main server and run test again.

Details

Details

[ShreyasZare]

Thanks for the info. I guess 1mn is not large enough to give the command timeout issue. Will do these tests locally too once and see how it can be improved.