[quote=243683:@Christian Schmitz]In one of my apps, I have a wrapper for SQLExecute which checks errors.
If error is connection lost, it connects again and performs query on new connection.
This way people don’t really notice the reconnect.[/quote]
Do you have an example of such a class and are you willing to share it with us please?
BTW; i am searching the net for a list of mysql error codes like “2013: Lost connection to the mySql server during the query.” but can’t find any. Does anyone have a link to such a list, please?
[code]#tag Class
Protected Class WAGmySQLConnector
Inherits MySQLCommunityServer #tag Method, Flags = &h21
Private Function Connection() As Boolean
self.IsConnected = self.Connect
Return self.IsConnected
End Function
#tag EndMethod
#tag Method, Flags = &h0
Sub ExecuteIt(ps As PreparedSQLStatement)
ps.SQLExecute
If self.Error Then
Select Case self.ErrorCode
Case 2013 // Lost connection to MySQL server during query
If Connection Then
ps.SQLExecute
End If
Case 48879 // A query is already in progress
End Select
End If
End Sub
#tag EndMethod
#tag Method, Flags = &h0
Function SelectIt(ps As PreparedSQLStatement) As RecordSet
Dim rs As RecordSet
rs = ps.SQLSelect
If self.Error Then
Select Case self.ErrorCode
Case 2013 // Lost connection to MySQL server during query
If Connection Then
rs = ps.SQLSelect
End If
Case 48879 // A query is already in progress
End Select
End If
Return rs
End Function
#tag EndMethod
#tag Property, Flags = &h0
IsConnected As Boolean = False
#tag EndProperty
#tag ViewBehavior
#tag ViewProperty
Name="Index"
Visible=true
Group="ID"
InitialValue="-2147483648"
Type="Integer"
#tag EndViewProperty
#tag ViewProperty
Name="IsConnected"
Group="Behavior"
InitialValue="False"
Type="Boolean"
#tag EndViewProperty
#tag ViewProperty
Name="Left"
Visible=true
Group="Position"
InitialValue="0"
Type="Integer"
#tag EndViewProperty
#tag ViewProperty
Name="Name"
Visible=true
Group="ID"
Type="String"
#tag EndViewProperty
#tag ViewProperty
Name="Port"
Visible=true
Type="Integer"
EditorType="Integer"
#tag EndViewProperty
#tag ViewProperty
Name="SecureAuth"
Visible=true
Type="Boolean"
EditorType="Boolean"
#tag EndViewProperty
#tag ViewProperty
Name="SSLAuthority"
Visible=true
Type="FolderItem"
EditorType="FolderItem"
#tag EndViewProperty
#tag ViewProperty
Name="SSLAuthorityDirectory"
Visible=true
Type="FolderItem"
EditorType="FolderItem"
#tag EndViewProperty
#tag ViewProperty
Name="SSLCertificate"
Visible=true
Type="FolderItem"
EditorType="FolderItem"
#tag EndViewProperty
#tag ViewProperty
Name="SSLCipher"
Visible=true
Type="String"
EditorType="String"
#tag EndViewProperty
#tag ViewProperty
Name="SSLKey"
Visible=true
Type="FolderItem"
EditorType="FolderItem"
#tag EndViewProperty
#tag ViewProperty
Name="SSLMode"
Visible=true
Type="Boolean"
EditorType="Boolean"
#tag EndViewProperty
#tag ViewProperty
Name="Super"
Visible=true
Group="ID"
Type="String"
#tag EndViewProperty
#tag ViewProperty
Name="TimeOut"
Visible=true
Type="Integer"
EditorType="Integer"
#tag EndViewProperty
#tag ViewProperty
Name="Top"
Visible=true
Group="Position"
InitialValue="0"
Type="Integer"
#tag EndViewProperty
#tag EndViewBehavior
End Class #tag EndClass
[/code]
This is until now, just compromise solution while i am looking for better solutions.
This looks similar to what we have.
In the connect I would do some setup like set text encoding.
And I normally use a subclass of Database, so I can still access error property and just overwrite the SQLExecute/SQLSelect methods.
I will now further improve my Class, by adding an Error Handler i can use from within SelectIt and ExecuteIt. So i do not have to maintain Error handling for Select and Execute statements separately. Something like
[code]Select Case self.ErrorCode
Case 2013 // Lost connection to MySQL server during query
Return True
Case 2006 // MySQL server has gone away
Return True
Case 48879 // A query is already in progress
End Select
Return False[/code]
In the SelectIT Method, i can then use something like this
[code]If self.Error Then
If HandleError Then
If Connection Then
rs = ps.SQLSelect
End If
End If
End If
[/code]
I subclassed the MySQLCommunityServer because i already have MySQLCommunityServer Subclasses for each Database in my App, which use Constructor and Destructor to automatically connect to the Server. These Subclasses will now Subclass my new WAGmySQLConnector Class.
I think the hardest part in my App will be to find a “good” solution for “Error 48879: A query is already in progress”. I am not sure if i should just use a
While self.ErrorCode = 48879
...
Wend
loop? Because if not all querries are in Threads, my App could freeze.
I have to, because i work with multiple Servers at the same time and each time i make a new connection it slows down (sometimes freezes) the app. Since i use only 1 connection as often as i can, my App is performing blazing fast.
Using 1 connection across many threads is going to cause you a world of hurt if you get several client apps getting results sets
You can end up with all kinds of problems
MySQL will Return strange errors if you run a new query and acccess the recorders from last query.
One connection per thread.
You can make a pool of connections where you recycle connections.
That is an optimization we did.
[quote=333175:@Norman Palardy]Problem is that this only protects the queries and NOT the result sets etc
It will cause issues eventually[/quote]
As long as you consume the result set within the semaphore, you would be ok. It’s certainly not ideal, of course.
