Artemis does not reconnect to MySQL after connection timeout

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Artemis does not reconnect to MySQL after connection timeout

mk666aim
We're trying to use MySQL 5.7 backend for JDBC persistent store.
This works fine, until the point where the connection goes stale due to the
server timeout.
This is our current set up in broker.xml:

<store>
   <database-store>
     
<jdbc-connection-url>jdbc:mysql://xxx:3306/artemis_datasource?create=true&amp;user=mq_admin&amp;password=abcd&amp;useSSL=false&amp;autoReconnect=true&amp;tcpKeepAlive=true&amp;autoReconnectForPools=true</jdbc-connection-url>
      <bindings-table-name>BINDINGS_TABLE</bindings-table-name>
      <message-table-name>MESSAGE_TABLE</message-table-name>
      <page-store-table-name>P_MSG_TBL</page-store-table-name>
     
<large-message-table-name>LARGE_MESSAGES_TABLE</large-message-table-name>
     
<node-manager-store-table-name>NODE_MANAGER_TABLE</node-manager-store-table-name>
     
<jdbc-driver-class-name>com.mysql.cj.jdbc.Driver</jdbc-driver-class-name>
   </database-store>
</store>

MySQL default setting of the *wait_timeout* parameter is 8 hours.
We are not able to get Artemis to reconnect again after this timeout is
reached and we were forced to set up a cron-triggered restart to mitigate
this.

Error that we get is as follows:

*The last packet successfully received from the server was 43417 seconds
ago.The last packet sent successfully to the server was 43417 seconds ago,
which 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
*

This issue is easily rectifiable in the old ActiveMQ, as this uses
Spring-cofngiured datasource, e.g.:
    <bean id="mysql-ds" class="org.apache.commons.dbcp2.BasicDataSource"
destroy-method="close">
      <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
      <property name="url"
value="jdbc:mysql://xxx:3306/mq_datasource?useSSL=false"/>
      <property name="username" value="user"/>
      <property name="password" value="pass"/>
      <property name="poolPreparedStatements" value="true"/>
      <property name="initialSize" value="5"/>
      <property name="maxIdle" value="10"/>
      <property name="maxTotal" value="50"/>
      <property name="removeAbandonedOnMaintenance" value="true"/>
      <property name="removeAbandonedOnBorrow" value="true"/>
   </bean>


The *autoReconnect* / *autoReconnectForPools* parameters to the driver URL
did not make any difference, and in addition they are not actually
recommended by MySQL maintainers.





--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
Reply | Threaded
Open this post in threaded view
|

Re: Artemis does not reconnect to MySQL after connection timeout

mk666aim
Bumping this one.
I know that the JDBC persistence is under development, but a simple
connection maintenance should be considered an essential feature.
If Artemis can't stay up while connected to a MySQL server, then the feature
is not just experimental, but unusable...




mk666aim wrote

> We're trying to use MySQL 5.7 backend for JDBC persistent store.
> This works fine, until the point where the connection goes stale due to
> the
> server timeout.
> This is our current set up in broker.xml:
> <store>
>    
> <database-store>
>      
> <jdbc-connection-url>
> jdbc:mysql://xxx:3306/artemis_datasource?create=true&amp;user=mq_admin&amp;password=abcd&amp;useSSL=false&amp;autoReconnect=true&amp;tcpKeepAlive=true&amp;autoReconnectForPools=true
> </jdbc-connection-url>
>      
> <bindings-table-name>
> BINDINGS_TABLE
> </bindings-table-name>
>      
> <message-table-name>
> MESSAGE_TABLE
> </message-table-name>
>      
> <page-store-table-name>
> P_MSG_TBL
> </page-store-table-name>
>      
> <large-message-table-name>
> LARGE_MESSAGES_TABLE
> </large-message-table-name>
>      
> <node-manager-store-table-name>
> NODE_MANAGER_TABLE
> </node-manager-store-table-name>
>      
> <jdbc-driver-class-name>
> com.mysql.cj.jdbc.Driver
> </jdbc-driver-class-name>
>    
> </database-store>
> </store>
> MySQL default setting of the *wait_timeout* parameter is 8 hours.
> We are not able to get Artemis to reconnect again after this timeout is
> reached and we were forced to set up a cron-triggered restart to mitigate
> this.
>
> Error that we get is as follows:
>
> *The last packet successfully received from the server was 43417 seconds
> ago.The last packet sent successfully to the server was 43417 seconds ago,
> which 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
> *
>
> This issue is easily rectifiable in the old ActiveMQ, as this uses
> Spring-cofngiured datasource, e.g.:
>    
> <bean id="mysql-ds" class="org.apache.commons.dbcp2.BasicDataSource"
> destroy-method="close">
>      
> <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
>      
> <property name="url"
> value="jdbc:mysql://xxx:3306/mq_datasource?useSSL=false"/>
>      
> <property name="username" value="user"/>
>      
> <property name="password" value="pass"/>
>      
> <property name="poolPreparedStatements" value="true"/>
>      
> <property name="initialSize" value="5"/>
>      
> <property name="maxIdle" value="10"/>
>      
> <property name="maxTotal" value="50"/>
>      
> <property name="removeAbandonedOnMaintenance" value="true"/>
>      
> <property name="removeAbandonedOnBorrow" value="true"/>
>    
> </bean>
>
> The *autoReconnect* / *autoReconnectForPools* parameters to the driver URL
> did not make any difference, and in addition they are not actually
> recommended by MySQL maintainers.
>
>
>
>
>
> --
> Sent from:
> http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html





--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
Reply | Threaded
Open this post in threaded view
|

Re: Artemis does not reconnect to MySQL after connection timeout

jbertram
I'm not terribly familiar with the JDBC support in Artemis or MySQL so
please bear with me.  I've got a handful of questions:

  1) Does MySQL just terminate any connection that exceeds the wait_timeout
no matter what or does it terminate connections that haven't had any
activity for more than the wait_timeout?
  2) Have you tried increasing the MySQL server configured values for
client timeouts? If so, what was the result? If not, why not?
  3) When the database connection fails what does the broker actually do? I
couldn't determine that from your previous emails.
  4) What is the fundamental difference between the configuration that
doesn't work in Artemis and the one that does work in 5.x? I read a bit on
Apache DBCP and it looks like it will test connections by default before
handing them out. That's probably the reason it works.
  5) You indicated that the error message recommends setting
'autoReconnect=true' on the connection URL, but later you say it's not
actually recommended by MySQL maintainers. This appears to be a
contradiction. Can you shed any light on this? If 'autoReconnect=true' is
not actually recommended by the MySQL maintainers what do they recommend as
a means to avoid this kind of error?
  6) Is there a specific reason you're using JDBC instead of local journal
storage? If so, could you elaborate on that reason?

Thanks!


Justin

On Fri, Aug 30, 2019 at 11:26 AM mk666aim <[hidden email]> wrote:

> Bumping this one.
> I know that the JDBC persistence is under development, but a simple
> connection maintenance should be considered an essential feature.
> If Artemis can't stay up while connected to a MySQL server, then the
> feature
> is not just experimental, but unusable...
>
>
>
>
> mk666aim wrote
> > We're trying to use MySQL 5.7 backend for JDBC persistent store.
> > This works fine, until the point where the connection goes stale due to
> > the
> > server timeout.
> > This is our current set up in broker.xml:
> > <store>
> >
> > <database-store>
> >
> > <jdbc-connection-url>
> >
> jdbc:mysql://xxx:3306/artemis_datasource?create=true&amp;user=mq_admin&amp;password=abcd&amp;useSSL=false&amp;autoReconnect=true&amp;tcpKeepAlive=true&amp;autoReconnectForPools=true
> > </jdbc-connection-url>
> >
> > <bindings-table-name>
> > BINDINGS_TABLE
> > </bindings-table-name>
> >
> > <message-table-name>
> > MESSAGE_TABLE
> > </message-table-name>
> >
> > <page-store-table-name>
> > P_MSG_TBL
> > </page-store-table-name>
> >
> > <large-message-table-name>
> > LARGE_MESSAGES_TABLE
> > </large-message-table-name>
> >
> > <node-manager-store-table-name>
> > NODE_MANAGER_TABLE
> > </node-manager-store-table-name>
> >
> > <jdbc-driver-class-name>
> > com.mysql.cj.jdbc.Driver
> > </jdbc-driver-class-name>
> >
> > </database-store>
> > </store>
> > MySQL default setting of the *wait_timeout* parameter is 8 hours.
> > We are not able to get Artemis to reconnect again after this timeout is
> > reached and we were forced to set up a cron-triggered restart to mitigate
> > this.
> >
> > Error that we get is as follows:
> >
> > *The last packet successfully received from the server was 43417 seconds
> > ago.The last packet sent successfully to the server was 43417 seconds
> ago,
> > which 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
> > *
> >
> > This issue is easily rectifiable in the old ActiveMQ, as this uses
> > Spring-cofngiured datasource, e.g.:
> >
> > <bean id="mysql-ds" class="org.apache.commons.dbcp2.BasicDataSource"
> > destroy-method="close">
> >
> > <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
> >
> > <property name="url"
> > value="jdbc:mysql://xxx:3306/mq_datasource?useSSL=false"/>
> >
> > <property name="username" value="user"/>
> >
> > <property name="password" value="pass"/>
> >
> > <property name="poolPreparedStatements" value="true"/>
> >
> > <property name="initialSize" value="5"/>
> >
> > <property name="maxIdle" value="10"/>
> >
> > <property name="maxTotal" value="50"/>
> >
> > <property name="removeAbandonedOnMaintenance" value="true"/>
> >
> > <property name="removeAbandonedOnBorrow" value="true"/>
> >
> > </bean>
> >
> > The *autoReconnect* / *autoReconnectForPools* parameters to the driver
> URL
> > did not make any difference, and in addition they are not actually
> > recommended by MySQL maintainers.
> >
> >
> >
> >
> >
> > --
> > Sent from:
> > http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
>
>
>
>
>
> --
> Sent from:
> http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html
>
Reply | Threaded
Open this post in threaded view
|

Re: Artemis does not reconnect to MySQL after connection timeout

ChrisSu
I exactly have the same problem like mk666aim! Is there any solution for
this? Compare this stackoverflow thread:
https://stackoverflow.com/questions/60884155/activemq-artemis-jdbc-store-add-connection-pool-for-db-connections

Currently I send keep alive messages to my queues to keep alive the brokers
db connection to the database.

ANswers to your questions:

1) MySQL terminates connections without traffic after 8h.
2) This is not a solution
3) An MySQL connection is thrown, and caught by the brokers general
exception handlern. This restarts the whole messagebroker and disconnects
all clients.
4) As i understand, it's currently not (or no longer) possible to configure
a javax.sql.DataSource which is handled by any connection pool (hikaro,
c3po, ...). Obviously this was possible before via spring xml config.
5) Even if autoReconnect is set to true, the MySQL driver thows an exception
which causes the messagebroker to restart.




--
Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-f2341805.html