There is a sqlExecute method that works great if a program with one thread both writes and reads and updates data. There is a program that makes requests to the database for several timers that create different threads. And in this program an exception often crashes.

Fatal error encountered during command execution.

Fatal error encountered attempting to read the resultset.

The error crashes on the line reader = command.ExecuteReader()

Then Catch exception As MySqlException

There are 600 entries in the table, 5-10 entries are available on request. The database cannot be local and network delays, read requests work instantly.

Initially, command.CommandTimeout was 1000 , increased to 10000 and then to 99999 , zero sense.

Next, before calling the line on which the error crashes, put the following code.

 If GConn.model.Connection.State <> ConnectionState.Open Then Stop End If

In order to check whether the connection is open, and the Stop command does not receive an execution when an error occurs, that is, the connection "seems to be open."

This is a GConn.model.Connection connection GConn.model.Connection

Then my fantasies dried up ...

The method itself:

  Public Function sqlExecute(ByVal str_sql As String, Optional ByVal type As Integer = 0) As Object Try StartCommand: Dim reader As MySqlDataReader Dim command As New MySqlCommand With { .Connection = GConn.model.Connection, .CommandText = str_sql } command.CommandTimeout = 99999 command.Prepare() If GConn.model.Connection.State = ConnectionState.Closed Then GConn.model.Connection = New MySqlConnection(GConn.model.Connstring) ReOpen1: Try GConn.model.Connection.Open() Catch exception3 As Exception If exception3.Message = "Unable to connect to any of the specified MySQL hosts." Then Threading.Thread.Sleep(5000) GoTo ReOpen1 End If End Try GoTo StartCommand End If Select Case type Case 0 command.ExecuteNonQuery() command.Dispose() Return command.LastInsertedId Case 1 If GConn.model.Connection.State <> ConnectionState.Open Then Stop End If reader = command.ExecuteReader() command.Dispose() Return reader Case 2 End Select Catch exception As MySqlException Return Nothing Catch exception2 As Exception If exception2.Message = "The connection is not open." Then If GConn.model.Connection.State = ConnectionState.Open Then GConn.model.Connection.Close() End If If GConn.model.Connection.State = ConnectionState.Closed Then GConn.model.Connection = New MySqlConnection(GConn.model.Connstring) ReOpen2: Try GConn.model.Connection.Open() Catch exception3 As Exception If exception3.Message = "Unable to connect to any of the specified MySQL hosts." Then Threading.Thread.Sleep(5000) GoTo ReOpen2 End If End Try GoTo StartCommand End If End If End Try Return Nothing End Function

Zarosy type:

 SELECT COUNT(*) AS cnt FROM `jos_md_visa_pool` WHERE UNIX_TIMESTAMP(created) > UNIX_TIMESTAMP() - 80 SELECT * FROM `jos_md_visa_pool` WHERE 1 AND UNIX_TIMESTAMP(created) > (UNIX_TIMESTAMP() - 80) AND `is_used` = '0'

    1 answer 1

    The problem was that a singleton connection to the database was used in different threads, and you cannot make several requests to the database on the same connection at the same time.

    This is a GConn.model.Connection connection GConn.model.Connection