Suggest collection for future reference! MySQL common error code description

Time:2020-10-21

Suggest collection for future reference! MySQL common error code description

Let’s first show you a few examples of error analysis and solutions.

1.ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/data/mysql/mysql.sock’

  • Problem analysis: the database may not be started or the port is prohibited by the firewall.
  • Solution: start the database or firewall, open the database listening port.

2.ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

  • Problem analysis: the password is incorrect or you do not have permission to access.
  • resolvent:

1) Modification my.cnf Main configuration file, add skip grant tables under [mysqld], and restart the database. Finally, the command to change the password is as follows:

mysql> use mysql;
mysql> update user set password=password("123456") where user="root";

Then delete the skip grant tables parameter just added, restart the database, and use the new password to login.

2) The order is as follows:

mysql> grant all on *.* to 'root'@'mysql-server' identified by '123456';

3. The client reports too many connections

  • Problem analysis: the number of connections exceeds the maximum connection limit of MySQL.
  • resolvent:
  • 1. In my.cnf Add the number of connections in the configuration file, and then restart the MySQL service. max_ connections = 10000
  • 2. Temporary modification of the maximum number of connections will not take effect after restart. Need to be in my.cnf Modify the configuration file, and the next restart will take effect.
set GLOBAL max_connections=10000;

4.Warning: World-writable config file ‘/etc/my.cnf’ is ignored ERROR! MySQL is running but PID file could not be found

  • Problem analysis: MySQL configuration file / etc/ my.cnf Wrong permissions.
  • resolvent:
chmod 644 /et/my.cnf

5.InnoDB: Error: page 14178 log sequence number 29455369832 InnoDB: is in the future! Current system log sequence number 29455369832

  • Problem analysis: InnoDB data file is damaged.
  • Solution: modify my.cnf Configuration file, add InnoDB under [mysqld]_ force_ Recovery = 4. After starting the database, back up the data file, and then remove the parameter to recover the data using the backup file.

6. Slave of slave Library_ IO_ Running is No

  • Problem analysis: the server ID values of master database and slave database are the same
  • Solution: modify the server ID value of the slave database to be different from the master database and lower than the master database. After modification, restart and synchronize again!

7. Slave of slave Library_ IO_ Running is no problem

  • Problem analysis: there are many reasons for the slave database thread to be No. the main reason is the primary key conflict or the master database deleting or updating data. The slave database cannot find records and the data is modified. Generally, there are 1007, 1032, 1062, 1452 and so on.
  • Solution 1:
mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;
  • Solution 2: set user permissions, set read-only permissions from the library
set global read_only=true;

8.Error initializing relay log position: I/O error reading the header from the binary log

  • Analysis problem: relay bin of slave library is damaged
  • Solution: manually repair, find the binlog and POS points of synchronization again, and then resynchronize.
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx; 

Maintenance of MySQL operation and maintenance or DBA all know, often encounter some error messages, there are some code similar to 10xx.

Replicate_Wild_Ignore_Table:
         Last_Errno: 1032
         Last_Error: Could not execute Update_rows event on table xuanzhi.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000004, end_log_pos 3704

However, if you don’t go into it or have encountered it before, it’s really not clear what the specific meaning of these codes is? This has also caused certain obstacles to our troubleshooting.

Therefore, today, the migrant worker brother sorted out some common error codes in the master-slave synchronization process, and its specific description.

MySQL common error code Description:

  • 130: the file format is incorrect.
  • 145: file cannot be opened
  • 1005: failed to create table
  • 1006: failed to create database
  • 1007: database already exists, failed to create database
  • 1008: database does not exist, failed to delete database
  • 1009: database file cannot be deleted, resulting in database deletion failure
  • 1010: failed to delete database due to unable to delete data directory
  • 1011: failed to delete database file
  • 1012: cannot read records in system table
  • 1020: the record has been modified by another user
  • 1021: the remaining space of hard disk is insufficient, please increase the available space of hard disk
  • 1022: duplicate keyword, failed to change record
  • 1023: error closing
  • 1024: error reading file
  • 1025: error changing name
  • 1026: error writing file
  • 1032: record does not exist
  • 1036: the data table is read-only and cannot be modified
  • 1037: the system is out of memory. Please restart the database or the server
  • 1038: there is not enough memory for sorting. Please increase the sort buffer
  • 1040: the maximum number of connections to the database has been reached. Please increase the number of available connections to the database
  • 1041: system out of memory
  • 1042: invalid host name
  • 1043: invalid connection
  • 1044: the current user does not have access to the database
  • 1045: unable to connect to database, wrong user name or password
  • 1048: field cannot be empty
  • 1049: database does not exist
  • 1050: data table already exists
  • 1051: data table does not exist
  • 1054: field does not exist
  • 1065: invalid SQL statement, SQL statement is empty
  • 1081: socket connection cannot be established
  • 1114: the data table is full and cannot hold any records
  • 1116: too many open datasheets
  • 1129: database exception, please restart the database
  • 1130: failed to connect to database. You do not have permission to connect to database
  • 1133: database user does not exist
  • 1141: the current user does not have access to the database
  • 1142: the current user does not have access to the data table
  • 1143: the current user does not have access to fields in the data table
  • 1146: data table does not exist
  • 1147: user access to data table not defined
  • 1149: syntax error in SQL statement
  • 1158: network error, read error, please check the network connection
  • 1159: network error, read timeout, please check the network connection
  • 1160: network error, write error, please check the network connection
  • 1161: network error, write timeout, please check the network connection
  • 1062: duplicate field value, failed to stock in
  • 1169: duplicate field value, failed to update record
  • 1177: failed to open data table
  • 1180: commit transaction failed
  • 1181: rollback transaction failed
  • 1203: the connection between the current user and the database has reached the maximum number of connections to the database. Please increase the number of available database connections or restart the database
  • 1205: locking timeout
  • 1211: the current user does not have permission to create a user
  • 1216: foreign key constraint check failed, sub table record update failed
  • 1217: foreign key constraint check failed, deletion or modification of main table record failed
  • 1226: the current user has used more resources than allowed. Please restart the database or the server
  • 1227: insufficient permissions, you do not have permission to perform this operation
  • 1235: MySQL version is too low to have this function
  • 1250: the client does not support the authentication protocol required by the server. Please consider upgrading the client.
  • 1251: client does not support authentication protocol requested by server; consider upgrading MySQL clientquote:
  • 1267: illegal mixed character set.
  • 2002: wrong server port.
  • 2003: MySQL service is not started, please start it.
  • 2008: MySQL client ran out of memory error points to MySQL customer mysql. The reason for this error is simple: the customer does not have enough memory to store all the results.
  • 2013: this problem sometimes occurs when connecting to the database remotely. The MySQL server loses the connection when executing an SQL statement.
  • 10048: it is suggested that my.ini Modify the maximum number of connections in the file and add mysql_ The connect() method has been changed to MySQL_ Pconnect() method. To modify MySQL_ Pconnect(), you can use SQL in the data directory of the forum_ config.php In p c o n e c t = 0; / / whether persistent connection is modified to pconnect = 0; / / whether persistent connection is modified to pconnect = 0; / / whether persistent connection is modified to pconnect = 1; enable anti refresh, and do not refresh too fast
  • 10055: there is no cache space available. Check whether your disk C is full and clear some useless files. You can turn on “process optimization” in “forum core settings” and “core function settings” in the background, and turn off “gzip compressed output”. Look for 10055 (no cache space available)
  • 10061: start the MySQL service on this machine. If the service fails to start, it must be yours my.ini If there is an error in the file, the MySQL service cannot start normally. After you delete it, MySQL will run according to its default configuration, and there will be no problem

Recommend some MySQL articles to you, it’s worth learning!!!

As soon as I got on the job, I met such a big bug as MySQL! I almost walked away with the pot on my back

Great! Wall crack recommend a MySQL automatic operation and maintenance tool!

Remember! Never use UTF-8 in MySQL

Why is it not recommended to deploy the database in the docker container?

fabulous! 7000 words learning notes, MySQL from entry to give up

High availability principle and practice of official tool MySQL router

The above is the common error code description for you today. I hope it can be helpful and reference for your daily work or study. You can point out your praise and forward to share a wave of support. Your support is the biggest motivation for migrant workers to write.

Suggest collection for future reference! MySQL common error code description

Recommended Today

Blog based on beego, go blog

Go Blog A beego based development, can quickly create personal blog, CMS system Include functions see Official website of go bloggo-blog.cn Demo siteleechan.online Update log time function January 23, 2020 New top post function February 2, 2020 New custom navigation function February 4, 2020 New site announcement function February 6, 2020 New link module February […]