MySQL Server side & Client Common error

directory (?) [+]

1. Client
2. Server-side

Client

1.ERROR (HY000) at line 1:lost connection to MySQL server during query

Using Select a large table, the following error occurs when a query is killed by the main library in the process

2.ERROR 2006 (HY000): MySQL server has gone away

If you are over wait_timeout or interactive_timeout, use this connection again
Admin>show databases;

Insert values () () If the size of the data volume exceeds the size of the max_allow_packets, it is also reported
ERROR 2006 (HY000): MySQL server has gone away

Error in server-side error log:
141111 19:30:16 [Warning] aborted connection to DB: ' Deal ' User: ' admin ' host: ' 10.0.0.0 ' (Got timeout reading commun Ication packets)

3.ERROR 1045 (28000): Access denied for user ' test ' @ ' 10.0.0.0 ' (using Password:yes)

1. Ping to connect to the database, see if you can ping;
2. Use MySQL client to try to connect, notice using-h-u-p-p specify host, user, port, for example Mysql-hdbxx-utest-p5002-p, then enter the password;
3. Emphasize, the 2nd step of the time use-p after the carriage return, and then enter the password, instead of the password as a direct input to the parameters, such a security, and the second is to avoid the password to transfer the character caused by the problem;

4.ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.1.1 ' (111)

In this case, the instructions are not connected to MySQL, try to ping the database to see if it can ping, and then see if MySQL server is started;

5.ERROR 1040 (HY000): Too Many connections

The number of connections on the current server side exceeds the maximum number of connections set Max_connections

6.Host ' host_name is blocked because of many connection errors. Unblock with ' mysqladmin flush-hosts '

If the client is connected to the server, the number of errors exceeds the server set max_connect_errors (not including the wrong password attempt), the error is reported, this is generally a network problem, if the network is confirmed to be occasional, You can increase max_connect_errors and perform flush-hosts

7.Communications link failure,the last packet successfully received by the server was * * * millisecond ago. The last packet successfully sent to the server was * * * * millisecond ago.

1) MySQL server default "Wait_timeout" is 8 hours (that is, the default value is 28,800 seconds by default), that is, a connection idle for more than 8 hours, MySQL will automatically disconnect the connection, In layman's words, a connection is automatically disconnected if there is no activity within 8 hours. The connection pool considers the connection to be valid (because the validity of the connection is not verified), which results in an error when the application requests the connection.

2) If the client connection is set to sockettimeout=1000 (that is, 1s), when the client connection sends a slow query (more than 1s), the client connection socket will be disconnected and will also be reported as "Communications link failure "Error

8.Could not the Create connection to database server. Attempted reconnect 3 times. Givingup.

1) If the back-end MySQL service is not available, please make sure that the backend MySQL service is correct and that the configuration parameters used by the connection are right;

2) There is also a case where the business uses the atlas, which is configured in the Business connection string (autoreconnect=true), when the connection pool is damaged (as described in 7 for possible corruption), When the business uses the damaged connection again, it initiates a reconnection (JDBC internal mechanism) and tries three times, but it is reported as an error (there is no problem confirming the connection parameters). This is due to the "SET SESSION TRANSACTION Isolation level repeatable READ" request that is sent on the JDBC internal re-connection, The request, which is not supported in Atlas 0.0.2 (upgradeable 0.1 resolution), only affects the current request and does not affect the next use of the connection.

Server-side

1. [Warning] aborted connection 104566 to DB: ' Test ' User: ' Root ' host: ' localhost ' (Got an error reading Communicatio N packets)

Obviously, MySQL thought that read a wrong package, and the connection aborted, in ErrLog note this warning, through show global status like '%aborted_clients% ' can see Aborted_ The clients value increases.

The warning occurs primarily because the client does not call Mysql_close () exit normally. Our tests for PHP, Python, and Perl are as follows:

1) Python code is as follows:

Running the program, kill or kill-9 the process in another term, will appear in MySQL ErrLog as above warning

Run the program, and then press CRTL + C to interrupt the program, which does not appear in MySQL ErrLog as above waring

2) The PHP code is as follows:

Running the program, kill or kill-9 the process in another term, will appear in MySQL ErrLog as above warning

Run the program, and then press CRTL + C to interrupt the program, appearing as above in MySQL ErrLog waring

3) The Perl code is as follows:

Running the program, kill or kill-9 the process in another term, will appear in MySQL ErrLog as above warning

Run the program, and then press CRTL + C to interrupt the program, appearing as above in MySQL ErrLog waring

Note : By testing, Python, PHP, and Perl are found to have different processing methods for CRTL + C (same as Kill-2), and only Python calls Mysql_close () gracefully off the MySQL connection for KILL-2 signal processing.

2.[warning] Access denied for user ' test ' @ ' localhost ' (using Password:yes)

Server-side reported this error, mainly the client connection when the use of the wrong password.

Create test tests, and give the test library the ability to change additions and deletions:

1) Use the wrong password in the terminal to connect MySQL results as follows:

You can see that the client does not have permission to connect to the ERROR at this time, and the server side ErrLog will appear as above warning

2) The database results with no permissions on the terminal connection are as follows:

The client does not have permission to connect to the MySQL library at this time, and no warning appears in server-side ErrLog

3.[warning] aborted connection 104643 to DB: ' Test ' User: ' Root ' host: ' localhost ' (Got timeout reading communication Packets)

The main is that the client connection is not operational within the Wait_timeout time range, and the server will disconnect this connection

1) First we set the Wait_timeout value to 10 seconds

2) wait for more than 10s server side error as follows:

3) Initiating operations in the MySQL client will be reported Error:mysql server has gone away

4.[warning] aborted connection 104660 to DB: ' Test ' User: ' Root ' host: ' localhost ' (Got a packet bigger than ' Max_allo Wed_packet ' bytes)

This warning is mainly caused by a client delivering a package larger than Max_allowed_packet of the specified size to the server side. The test is as follows:

1) First data is exported through mysqldump naming

The default mysqldump exports data to multiple insert into statements, each with a size close to 1M

2) Set max_allowed_packet = 524288

3) then use the MySQL command to import the Hostname.sql results as follows:

Server ErrLog will be reported as above warning

Transfer from Meituan wk

MySQL Server side & Client Common error