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 <matt.k...@aimia.com> 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&user=mq_admin&password=abcd&useSSL=false&autoReconnect=true&tcpKeepAlive=true&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 >
