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

Recently encountered a problem in the company, enter the company's game product official website to sign up for an ordinary user account, log in to the official website

Then click to enter the forum of the game product, which cannot be automatically redirected to the forum to achieve automatic login.

So I went to the official website to sign up for an ordinary user account, logged in to the official website, tested it, and found that it really couldn't jump to the forum automatically.

Log in to the server database of the forum and see that there is already user data just registered in the database, but the password is not synchronized.

After analysis and troubleshooting together with the development, an error reporting program was found.

Com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

one。 Abnormal information and solutions

Com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception iscom.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureThe last packet successfully received from the server was 6388 milliseconds ago. The last packet sent successfully to the server was 1504 milliseconds ago.at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin (DataSourceTransactionManager.java:240)

Exception analysis: the communication between the program and MySQL failed, that is, the connection failed.

After opening the database connection for the program, when doing the database operation, it was found that the connection was closed by MySQL.

At the MySQL layer, the wait time for connections (wait_timeout) defaults to 8 hours by default on the MySQL5 configuration

Connecting for more than 8 hours will cause mysql to consider the connection timeout invalid and close it.

Mysql > mysql > show global variables like 'wait_timeout'; +-+-+ | Variable_name | Value | +-+-+ | wait_timeout | 28800 | +-+-+ 1 row in set (sec) 28800 seconds, that is, 8 hours.

Solution (the order of trying the solution can be: (1) (3) (2)):

(1) in the configuration of jdbc connecting to url, you can attach "autoReconnect=true", but this only works for previous versions of mysql5.

(2) since the problem is caused by the small default value of mysql5's global variable wait_timeout, let's just make it bigger.

Looking at the manual of mysql5, it is found that the maximum value for wait_timeout is 24 days / 365 days (windows/linux)

Take windows as an example, suppose we want to set it to 21 days, we just need to modify the mysql5 configuration file "my.ini" (mysql5 installation dir)

Add a line: wait_timeout=1814400, you need to restart mysql5.

Linux system profile: / etc/my.cnf

(3) We can turn on the validateQuery and testOnBorrow (testOnReturn) of the database connection pool, so as to test whether the current use is good or not before each time it is taken out of the connection pool and ready to use (or after using it and before putting it into the connection pool). If it doesn't work, the system will automatically destory it.

Or the testWhileIdle item is set to allow background threads to periodically check the availability of connections in the connection pool.

two。 Fundamental solution: code optimization

Finally, it is found that the IP of the server is not bleached, so you need to add the whitelist of IP.

[root@localhost ~] # cat / etc/sysconfig/iptables

# # db # #

-An INPUT-s server IP1/32-p tcp-m state-- state NEW-m tcp-- dport 3306-j ACCEPT

-An INPUT-s server IP2/32-p tcp-m state-- state NEW-m tcp-- dport 3306-j ACCEPT

Through the problem of redirect login on the company's website, here's how Linux sets up the IP whitelist blacklist.

Anti-IP whitelist / etc/hosts.allow and blacklist / etc/hosts.deny can be added.

Configuration file format reference:

Modify / etc/hosts.allow file

#

# hosts.allow This file describes the names of the hosts which are

# allowed to use the local INET services, as decided

# by the'/ usr/sbin/tcpd' server.

#

Sshd:210.13.218.*:allow

Sshd:222.77.15.*:allow

The above means that two ip segments 210and 222are allowed to connect to the sshd service (this inevitably requires the use of the hosts.deny file)

Of course: allow can be omitted. Www.111cn.net

Of course, if the administrator is concentrated in an IP, it is easier to write this way.

All:218.24.129.110// he said to accept all the requests of 110s ip!

/ etc/hosts.deny file, which is a list of denial of service, with the following contents:

#

# hosts.deny This file describes the names of the hosts which are

# * not* allowed to use the local INET services, as decided

# by the'/ usr/sbin/tcpd' server.

#

# The portmap line is redundant, but it is left to remind you that

# the new secure portmap uses hosts.deny and hosts.allow. In particular

# you should know that NFS uses portmap!

Sshd:all:deny

Note: sshd:all:deny denies all sshd remote connections. Deny can be omitted

Therefore: when hosts.allow and host.deny conflict, the hosts.allow setting shall prevail

Note that after modification: www.111Cn.net

Only service xinetd restart can make the change take effect.

/ etc/hosts.allow (allowed) and / etc/hosts.deny (prohibited) are the configuration files for the tcpd server

The tcpd server can control the access of external IP to native services

The linux system will check the / etc/hosts.deny rule first, and then check the / etc/hosts.allow rule. If there is a conflict, it will be handled according to the / etc/hosts.allow rule.

For example:

1. Prohibit all ip from accessing the ssh function of linux

You can add a line of sshd:all:deny to / etc/hosts.deny

two。 Prohibit an ip (192.168.11.112) from accessing ssh functions

You can add a line of sshd:192.168.11.112 to / etc/hosts.deny

3. If there are sshd:192.168.11.112 rules in both / etc/hosts.deny and / etc/hosts.allow, 192.168.11.112 can access the host's ssh service

Summary: this method can control some unauthorized access, but not once and for all! When we look at the service log, we may see a lot of scan records, not directly aimed at root users, then controlling your access list is very useful!

Add whitelist to firewall under Linux

Install yum install iptables-services on a Linux system

Then vim / etc/sysconfig/iptables

# Generated by iptables-save v1.4.7 on Sun Aug 28 12:14:02 2016

* filter

: INPUT ACCEPT [0:0]

: FORWARD ACCEPT [0:0]

: OUTPUT ACCEPT [0:0]

-N whitelist

-A whitelist-s 8.8.8.8-j ACCEPT

-A whitelist-s x.x.x.x-j ACCEPT

-A whitelist-s x.x.x.x-j ACCEPT

-A whitelist-s x.x.x.x-j ACCEPT

-A whitelist-s x.x.x.x-j ACCEPT

-A whitelist-s x.x.x.x-j ACCEPT

-An INPUT-m state-- state RELATED,ESTABLISHED-j ACCEPT

-An INPUT-p icmp-j ACCEPT

-An INPUT-I lo-j ACCEPT

-An INPUT-m state-- state NEW-m tcp-p tcp-- dport 22-j whitelist

-An INPUT-m state-- state NEW-m tcp-p tcp-- dport 80-j whitelist

-An INPUT-m state-- state NEW-m tcp-p tcp-- dport 443-j whitelist

-An INPUT-m state-- state NEW-m tcp-p tcp-- dport 25-j whitelist

-An INPUT-j REJECT-- reject-with icmp-host-prohibited

-A FORWARD-j REJECT-- reject-with icmp-host-prohibited

COMMIT

Above is the IP list of the whitelist

Here are some ports opened for the contents of the whitelist

Write ACCEPT's on it.

Write the contents of REJECT below.

In this way, the IP on the whitelist can access our restricted ports and services, while the IP that is not on the whitelist will be denied

-An INPUT-m state-- state NEW-m tcp-p tcp-- dport 22-j whitelist is opened for whitelist ports, that is, only whitelist IP can be accessed through this port.

-An INPUT-m state-- state NEW-m tcp-p tcp-- dport 22-j ACCEPT this is all white open, that is, any machine can be accessed through this port

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.