B4J Question web server: error appear when one days without connection

[yfleury]

I have this error in release when I return to web page after one day.

HTTP ERROR 500 java.lang.RuntimeException: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 92 914 945 milliseconds ago. The last packet sent successfully to the server was 92 914 945 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Where I put the

autoReconnect=true

I guess it a session timeout?

 

[Erel]

1. Let the connection pool handle the connections. You don't need auto reconnect.
2. Do you actually see any problem? It is expected that connections in the pool will fail from time to time.

 

[yfleury]

2. Do you actually see any problem? It is expected that connections in the pool will fail from time to time.

Every 24 hours

 

[yfleury]

In debug mode, I have white page after 24 hours. the source off web page have one blank line. nothing.

In the log, I have this error

Error occurred on line: 28 (index)
com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 134Â 833Â 503 milliseconds ago. The last packet sent successfully to the server was 134Â 833Â 507 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
    at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1414)
    at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1399)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:678)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
    at anywheresoftware.b4j.objects.SQL.ExecNonQuery(SQL.java:161)
    at b4j.example.index._handle(index.java:104)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:629)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at anywheresoftware.b4j.object.JServlet$Handle.run(JServlet.java:146)
    at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:47)
    at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:43)
    at anywheresoftware.b4a.shell.ShellBA.startMessageLoop(ShellBA.java:121)
    at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:180)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:309)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:111)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:100)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:98)
    at b4j.example.main.main(main.java:29)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 134Â 833Â 503 milliseconds ago. The last packet sent successfully to the server was 134Â 833Â 507 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

The index code is

'Handler class
Sub Class_Globals
End Sub

Public Sub Initialize
   
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    If req.GetSession.GetAttribute("Erreur")=Null Then
        req.GetSession.SetAttribute("Erreur"," ")
       
    End If
    If req.GetSession.IsNew Then
    req.GetSession.MaxInactiveInterval=1200 'Inactivité de la session en seconde
    req.GetSession.SetAttribute("Erreur"," ")
    End If
    Dim html, header, footer As String
    Dim sss As StringBuilder
    sss.Initialize
    Dim Erreur As String
   
    html= File.ReadString(File.DirApp, "www/index.php")
    html=html.Replace("[titre]", "Ouvrir une session")
    html=html.Replace("[Erreur]", req.GetSession.GetAttribute("Erreur"))
    footer= File.ReadString(File.DirApp, "www/footer.php")
    req.GetSession.SetAttribute("Erreur", " ")
    Main.Mysql.ExecNonQuery($"insert into logger (qui, dateheure, page) values ("${req.RemoteAddress}", "${DateTime.Date(DateTime.Now)}", "${"Login"}") "$)

    sss.Append(html).Append(footer).Append("<br>")
'    Log (sss)
    resp.ContentType="text/html"
    resp.Write(sss)
   
   
End Sub

 

[Erel]

I don't see the connection pool in the stack trace.

 

[yfleury]

I have this in Main

#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True

#End Region

'change based on the jdbc jar file
#AdditionalJar: mysql-connector-java-8.0.18
'#AdditionalJar: postgresql-9.4.1207


Sub Process_Globals
    Private srvr As Server
    Public Mysql As SQL
End Sub

Sub AppStart (Args() As String)
    DateTime.DateFormat="yyyy/MM/dd HH.mm.ss"
    srvr.Initialize("srvr")
    srvr.Port = 51042
    srvr.StaticFilesFolder = File.Combine(File.DirApp, "www")
    srvr.AddHandler("/guessmynumber/guess", "GuessMyNumber", False)
    srvr.AddHandler("/guessmynumber/reset", "ResetMyNumber", False)
    srvr.AddWebSocket("/guessmynumber_ws/ws", "WSGuessMyNumber")
    srvr.AddHandler("", "index", False)
    srvr.AddHandler("/bienvenu", "bienvenu", False)
    srvr.AddHandler("/admin", "Admin", False)
    srvr.AddHandler("/rh", "rh", False)
    srvr.AddHandler("/paie", "paie", False)
    srvr.AddHandler("/rappel", "rappel", False)
    srvr.AddHandler("/fin", "fin", False)
    srvr.AddHandler("/loggeur", "loggeur", False)
    Mysql.Initialize2("com.mysql.jdbc.Driver","jdbc:mysql://localhost:3308/rappeltravail?characterEncoding=utf8&useTimezone=true&serverTimezone=America/New_York","user","password")

    srvr.Start
StartMessageLoop
    'open browser and navigate to: http://127.0.0.1:51042/
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
   
    Dim html, header, footer As Object
    html= File.ReadString(File.DirApp, "www/index.php")
    header= File.ReadString(File.DirApp, "www/header.php")
    footer= File.ReadString(File.DirApp, "www/footer.php")
'    Log (html)
    Dim sss As StringBuilder
    sss.Initialize
    sss.Append(header).Append(html).Append(footer).Append("<br>")
    resp.ContentType="text/html"
    resp.Write(sss)
   
End Sub

 

[Erel]

You are not using jRDC2. The title is misleading.

And you are not using a connection pool which makes this code very fragile. Switch to a connection pool.

 

[yfleury]

You are not using jRDC2. The title is misleading.

And you are not using a connection pool which makes this code very fragile. Switch to a connection pool.

Sorry about the misleading.
It's the first time I ear about connection pool. I don't know how to do it.