Click on the “Road of Technology for migrant workers” and choose “Top public account”.
Interesting and meaningful articles are delivered at the first time!
Original: http://blog.51cto.com/sumongodb/1949426
Today to give you a list of MySQL database, the most classic ten error cases, and with the solution to the problem and method, I hope to give just entered the line, or database enthusiasts some help, in the future encountered any error, we can be very calm to deal with. Learning any skill is a process of self-cultivation. Sink down and try to embrace the world of data!
Top 1: Too many Connections (Too many connections fail to connect to the database and services fail to run properly)
The problem reduction
mysql> show variables like '%max_connection%';| Variable_name | Value |max_connections | 151 | mysql> set global max_connections=1;Query OK, 0 rows affected (0.00 sec)[root@node4 ~]# mysql -uzs -p123456 -h 192.168.56.132ERROR 1040 (00000): Too many connectionsCopy the codeSolutions to the problem:
-
Max_connections = max_connections = max_connections = max_connections = max_connections = max_connections = max_connections ● The default value is 151, which can be adjusted according to the actual situation. There is a danger, however, because we are not sure whether the database can handle such a large connection pressure, like a person can eat only one steamed bread, but now he has to eat 10, he will not accept. Reaction to the server, there may be a possible outage. So this again reflects that when we launch a new business system, we should do a good stress test. Ensure that the database is optimized and adjusted later.
-
Second, you can limit the number of concurrent processes in Innodb. If Innodb_thread_concurrency = 0, you can change it to 16 or 64 depending on the server stress. If it is very large, you can first make it smaller to let the server pressure down, and then gradually increase, depending on your business. Personal advice can be adjusted to 16 first. MySQL performance will decline as the number of connections increases. Let development cooperate with setting thread pool, connection reuse. Thread pool has been added to the commercial edition of MySQL, and consider turning off the following parameters for monitoring programs that read tables under information_SCHEMA
innodb_stats_on_metadata=0set global innodb_stats_on_metadata=0Copy the codeTop 2 :(primary/secondary replication error type)
Last_SQL_Errno: 1062 (secondary database conflicting with primary database)
Last_Errno: 1062 Last_Error: Could not execute Write_rows event on table test.t; Duplicate entry '4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000014, end_log_pos 1505Copy the codeFor this error, we should first consider whether it is inThis was caused by a misoperation from the library. It turns out that we did an insert into a SQL statement in the slave database for a table with a primary key, causing the master and slave states to be abnormal when the master database inserts the same SQL. A primary key conflict occurred.
Solutions:
Error skipping can be performed in the slave library on the premise of ensuring the consistency of master and slave data. Generally, pt-slave-restart of Percona-Toolkit is used. Complete the following steps in the slave library
[root@zs bin]# ./pt-slave-restart -uroot -proot1232017-07-20T14:05:30 p=... ,u=root node4-relay-bin.000002 1506 1062Copy the code-
Then it is best to turn on the read_only parameter in the slave library to prevent writing from the slave library
Last_IO_Errno: 1593 (Server-ID conflict)
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; Please check the manual before using it. The server id is the same on both machines.Copy the codeWhen setting up the master-slave replication, we need to ensure that the server-IDS of the two machines are unique. The server id is the last bit of the server IP address and the port number of the MySQL server.
Solutions:
Configure different server ids on the primary and secondary servers.
Last_SQL_Errno: 1032 (Last_SQL_Errno: 1032)
Last_SQL_Error:Could not execute Update_rows event on table test.t;Copy the codeCan't find record in 't', Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000014, end_log_pos 1708
Solutions to the problem:
Based on the error information, we can obtain the error log and position number, and then we can find which SQL was executed by the master library, causing the master/slave error. Execute in primary library:
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /data/mysql/mysql-bin.000014 |grep -A 10 1708 > 1.logcat 1.logCopy the codeOnce the SQL statement is retrieved, it can be executed in reverse from the library. Complete the missing SQL statement from the slave library to solve the error message. In order to execute from the library:
mysql> insert into t (b) values ('ddd');Query OK, 1 row affected (0.01 sec)mysql> stop slave;Query OK, 0 rows affected (0.00 sec)mysql> exitBye[root@node4 bin]# ./pt-slave-restart -uroot -proot1232017-07-20T14:31:37 p=...,u=root node4-relay-bin.000005 283 1032 Copy the codeTop 3: An error occurs during MySQL installation
[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &[1] 3758[root@zs data]# 170720 14:41:24 mysqld_safe Logging to '/data/mysql/error.log'.170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql170720 14:41:25 mysqld_safe mysqld from pid file /data/mysql/node4.pid ended170720 14:41:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql2017-07-20 14:41:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details)./usr/local/mysql/bin/mysqld: File '/data/mysql/mysql-bin.index' not found (Errcode: 13 - Permission denied)2017-07-20 14:41:25 4388 [ERROR] AbortingCopy the codeSolution:
Encounter such error information, we should learn to always pay attention to the contents of the error log. The key error message Permission denied was seen. Verify that the current MySQL database data directory does not have permissions.
Solutions:
[root@zs data]# chown mysql:mysql -R mysql[root@zs data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &[1] 4402[root@zs data]# 170720 14:45:56 mysqld_safe Logging to '/data/mysql/error.log'.170720 14:45:56 mysqld_safe Mysql daemon with database from /data/mysqlCopy the codeHow do I avoid this kind of problem that I recommend during installation--user= MySQL;This avoids permission issues.
./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --defaults-file=/etc/my.cnf --user=mysqlCopy the codeTop 4: Database password forget problem
[root@zs ~]# mysql -uroot -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@zs ~]# mysql -uroot -pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)# The root password can be lost or forgotten.Copy the codeSolution:
Currently, we cannot access the database, so we need to consider whether we can skip permissions. Because in the database, the mysql database user table records our user information.
Solutions:
To start the MySQL database, run:
/ usr/local/mysql/bin/mysqld_safe - defaults - file = / etc/my CNF - skip - grant - tables & start like this, can not enter the password, direct access to the mysql database. Then change the root password you want to change. update mysql.user set password=password('root123') where user='root';Copy the codeTop 5: TRUNCate deletes data. As a result, the self-added ID is automatically cleared, and an error not found is displayed.
To solve this problem, the difference between TRUNCate and DELETE should be considered.
Take a look at the test run:
Create a table; CREATE TABLE `t` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` varchar(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY 'b' (' b ') ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8# insert into t (b) values ('aa'); Query OK, 1 row affected (0.00 SEC)mysql> insert into t (b) values ('bb'); Query OK, 1 row affected (0.00 SEC)mysql> insert into t (b) values ('cc'); Query OK, 1 row affected (0.00 SEC)mysql> select * from t; + + -- -- -- -- -- -- -- -- -- -- -- + | | a | b + + -- -- -- -- -- -- -- -- -- -- - + aa | | 300 | | 301 | bb | | 302 | cc | + -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC) # use first Delete Deletes all table information and inserts a new value.Copy the codeIt is found that TRUNCate resets the initial value of the increment, and the increment attribute is recorded from 1. When the primary key ID is used to query, an error is reported that this data is not present. It is recommended not to use TRUNCATE to delete a table. Although table Spaces can be reclaimed, the problem of self-increasing attributes is involved. We should not plunge into these holes easily.
MySQL > select * from MySQL; MySQL > select * from MySQL;
lower_case_table_names = 0; Default lower_case_table_names = 1; It is case-insensitive. If you cannot find a lower case name, change the name of the remote database to lower case, and vice versa. Note that all table names in Mybatis Mapper file should also be changed accordinglyCopy the codeTop 7: Chinese garbled characters always appear in the database
Solution:
For Chinese garbled code, remember the teacher told you the three unity can be. Also know that the character set encoding in the current mysql database is the default UTF8
Treatment methods:
1. Data terminal, that is, the tool we use to connect to the database is set to UTF82, operating system level; You can run the cat /etc/sysconfig/i18n command to query the information. Also set to UTF83, database level; Add character-set-server=utf8 under mysqld in the parameter file.
An error occurred when emojis were entered into the mysql database.
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x97\xF0\x9F... ' for column 'CONTENT' at row 1at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)Copy the codeSolution: for the expression of the problem of insertion, or character set problem. Solution: We can directly in the parameter file, add
Vim /etc/my.cnf[mysqld]init-connect='SET NAMES utf8mb4'character-set-server= UTf8mb4 note: utf8mb4 is a superset of UTf8.Copy the codeTop 8: If the binlog_format=statement format is used, cross-library operations result in data loss from the database and incorrect data information occurs when users access the database.
Mysql > set binlog-do-db=mydb1; mysql > set binlog-do-db=mydb1; mysql > set binlog-do-db=mydb1; insert into mydb1.t1 values ('bb'); This statement will not be synchronized to the slave library. But it works; use mydb1; insert into mydb1.t1 values ('bb'); Because this is done in the same library. In the production environment, it is recommended to use the binlog format row and be careful with the binlog-do-db parameter.Copy the codeTop 9: Error reported when MySQL database connection times out
org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08S01org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was43200 milliseconds ago.The last packet sent successfully to the server was 43200 milliseconds 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 'autoReconnect=true' to avoid this problem.org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with sessionorg.hibernate.exception.JDBCConnectionException: Could not execute JDBC batch updatecom.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08003org.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed. Connection was implicitly Closed due to underlying exception/error: ** BEGIN NESTED exception **# Most DBA students will probably be told by developers that your database reported this error. Let's find out what the problem is.Copy the codeThis problem is affected by two parameters, wait_timeout and interactive_timeout. The default configuration time for data is 28800 (8 hours), which means that after this time, the MySQL database will disconnect the connection on the database side to save resources. The MySQL server will disconnect the connection, but our program does not make any judgment when using the connection again, so it hangs.
Solution:
To understand the characteristics of these two parameters; These two parameters must be set at the same time, and their values must be consistent. We can increase this value slightly, 8 hours is too long to be used in a production environment. Because a connection doesn’t work for a long time, it takes up our connection count and consumes our system resources.
Solutions:
Can make proper judgment in the procedure; It is strongly recommended that you change the application logic at the end of the operation to close the connection properly; Then set a reasonable timeout value (depending on the business)
Top 10 ：can’t open file (errno:24)
Sometimes, when the database is running well, an error occurs that the database file cannot be opened.
Solution:
First we need to check the error log of the database. Then determine whether the table is corrupt, or permission issues. There may be insufficient disk space can not normally access the table; The limitations of the operating system should also be looked at; Use the Perror tool to view specific errors!
linux:/usr/local/mysql/bin # ./perror 24OS error code 24: Too many open filesCopy the codeThe maximum number of open files exceeded! Ulimit -n The maximum number of open files is 65535, which cannot be exceeded. That must be the maximum number of open files in the database exceeded the limit! Run the show variables like ‘open_files_limit’ command to check the maximum number of open files in MySQL. If the value is too small, change it to 2048 and restart MySQL. The application is normal
Treatment methods:
On the table. Chown mysql permission # Remove garbage data from diskCopy the codeIn the future, I will continue to summarize all kinds of error handling ideas and methods in MySQL, and I hope to work together with you. Communicate more!
See the old drivers here, forward this article out, so that more people become old drivers.
Recommended reading
Carefully arrange | public number article directory
Discussion on MongoDB performance optimization
How to improve server concurrent processing capability
Besides the high salary, why do you want to stay in your job?
Only this one, firmly grasp the “database connection pool”
Late | section 1024 programmers, sent out a great benefits
Fashion guide for Internet companies
, end,
— Writing is not easy, your forwarding is the biggest support for me —
Let’s have fun together
At present, more than 40,000 people are interested in joining us
Click on the menu “wechat group” to join the group to communicate with you!
Like, scan code attention to increase a reader to it!