Shulou(Shulou.com)06/01 Report--

Atomikos + druid multi-source database connection timeout recovery problem how to solve, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Abnormal performance

If the data is not queried for a period of time, an exception that the database cannot connect occurs when the first connection is initiated overnight the next morning:

/ / the following error usually occurs. Exception 1: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 37350882 milliseconds ago. The last packet sent successfully to the server was 37350882 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.// exception II: ERROR [com.alibaba.druid.pool.DruidPooledStatement:357]-CommunicationsException, druid version 1.1.10, jdbcUrl: jdbc:mysql://XX.XX.XX.XX:3306/txsmarthome?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true&noAccessToProcedureBodies=true&autoReconnect=true&failOverReadOnly=false&pinGlobalTxToPhysicalConnection=true, testWhileIdle true, idle millis 39540140, minIdle 50, poolingCount 0, timeBetweenEvictionRunsMillis 60000, lastValidIdleMillis 39540140 Driver com.mysql.jdbc.Driver, exceptionSorter com.alibaba.druid.pool.vendor.MySqlExceptionSorter// exception case 3: java.sql.SQLException: connection holder is nulldruid exception

The first two exceptions mentioned above occur because the database connection exceeds the wait_timeout time configured by mysql, and the connection is reclaimed by mysql. However, the druid connection pool does not know that the connection has been reclaimed, so an error is reported.

Connection pooling has a failed reconnection feature. Druid reconnects to the last abnormal connection the next time it requests to connect to the database, so each subsequent request is abnormal until all connections in the pool are reconnected. That is, the number of consecutive failures is the number of minIdle configurations.

If you connect to multiple databases, the request for each database will fail to connect minIdle times.

Connection pool configuration spring: datasource: druid: db1: # check whether the database connection is valid every hour Timing starts 1 minute after successful connection removeAbandonedTimeout: 3600 # delete reconnect if connection fails removeAbandoned: true # output log when deleted logAbandoned: true # Test query results testOnReturn: true

The above configuration ensures that invalid connections are deleted in a limited time.

Database configuration

After the command line connects to the database, execute: show global variables like "% timeout%"

Mysql > show global variables like "timeout%" +-- +-+ | Variable_name | Value | +-+-+ | connect_timeout | 10 | | delayed_insert_timeout | | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | innodb_lock_wait_timeout | 7200 | innodb_rollback_on_timeout | OFF | | interactive_timeout | 3600 | lock_wait_timeout | 31536000 | net_read_timeout | 30 | net_write_timeout | 60 | | rpl_stop_slave_timeout | | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 3600 | +-+-+ |

The validity period of the link to the above database is 1 hour, and MySQL defaults to 8 hours. You can use the command to modify it without restarting mysql:

# modify it to 12 hours. Note that the following two configuration items must be consistent to take effect: mysql > set global interactive_timeout=43200;Query OK, 0 rows affectedmysql > set global wait_timeout=43200;Query OK, 0 rows affected.

Need to pay attention to the link pool removeAbandonedTimeout

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.