Summary of MySQL common error analysis and solutions

Related free learning recommendations:mysql video tutorial

1. Can't connect to MySQL server on 'localhost' (10061)
Translation: Can't connect to mysql on localhost
Analysis: This means that the "localhost" computer exists, but There is no MySQL service provided on this machine.
You need to start the MySQL service on this machine. This error will also occur if the machine load is too high and there is no time for corresponding requests.
Solution: Since it has not been started, start mysql on this machine. If the startup fails, it is mostly because there is a problem with your my.ini configuration. Just reconfigure it.
If you feel that mysql load is abnormal, you can go to the mysql/bin directory and execute mysqladmin -uroot -p123 processlist to view the current mysql process.

2. Unknown MySQL Server Host ‘localhosadst’ (11001)
Translation: Unknown MySQL Server localhosadst
Analysis: Server localhosasdst does not exist. Or unable to connect at all
Solution: Carefully check ./config.inc.php under your forum to find $dbhost and reset it to the correct mysql server address.

3. Access denied for user: 'roota@localhost' (Using password: YES)
Translation: User roota's access to localhost is denied (not allowed to pass)
Analysis : This error is caused by the general database username and password being incorrect relative to the mysql server
Solution: Carefully check ./config.inc.php under your forum, find $dbuser and $dbpw, verify, reset and save.

4. Access denied for user: 'red@localhost' to database 'newbbs'
Translation: User red does not have permission to operate the database newbbs on the localhost server
Analysis: This tip is different from question three. That one was blocked when connecting to the database, and this error was caused when operating on the database. For example, select update and so on. This is because the user does not have the corresponding rights to operate the database. For example, the select operation is recorded in mysql.user.Select_priv. Y can be operated and N cannot be operated.
Solution: If it is your own independent host, update the corresponding user record of mysql.user. For example, the user to be updated here is red. Or directly modify ./config.inc.php to configure a user with permission to operate the database
Or update the authorization grant through the following command grant all privileges on dbname.* to 'user'@'localhost' identified by ' password'
Tip: If you update the records in the mysql database, you must restart the mysql server for the update to take effect
FLUSH PRIVILEGES;

5. No Database Selected
Translation : No database is selected
Analysis: There are two reasons
The $dbname setting in config.inc.php is incorrect. As a result, the database does not exist at all, so $db->select_db($dbname); returns false
It is the same as question 4 above. The database user does not have select permission, which will also cause such an error. When you find that there is no problem with the settings of config.inc.php, but this error is still prompted, this must be the case.
Solution: Prescribe the right medicine
Open config.inc.php, find $dbname, verify the reconfiguration and save it
Same as the solution to problem 4

6. Can't open file: ' xxx_forums.MYI'. (errno: 145)
Translation: Unable to open xxx_forums.MYI
Problem analysis:
This situation is caused by the inability to open cdb_forums.MYI, which may cause this situation The reasons are:
1. The server shut down abnormally, the space where the database is located is full, or some other unknown reasons caused damage to the database table.
2. Directly copying and moving the database file under a Unix-like operating system will cause this error due to the file's group ownership.
Solution:
1. Repair the data table
You can use the following two methods to repair the data table: (The first method is only suitable for independent host users)
1) Use myisamchk, which comes with MySQL A tool dedicated to checking and repairing user data tables - myisamchk. Change the current directory to MySQL/bin. Generally, the myisamchk command can only be run under this directory. Commonly used repair commands are: myisamchk -r data file directory/data table name.MYI;
2) Repair through phpMyAdmin. phpMyAdmin has the function of repairing data tables. After entering a certain table, click "Operation". Click "Repair Table" in "Table Maintenance" below.
Note: The database must be backed up before executing the above two repair methods.
2. Modify the group of the file (only suitable for independent host users)
1) During the process of copying the database file, the database file is not set to be readable and writable by the account running MySQL (generally applicable to Linux and FreeBSD users) .

7. Table 'test.xxx_sessions' doesn't exist
Translation: Table xxxxx does not exist
Analysis: The table was not found when executing the sql statement, such as: SELECT * FROM xxx_members WHERE uid='XX' If table xxx_members does not exist in the $dbname database, this error will be prompted. The specific situation can be discussed in the following three situations:
The program file was modified when installing the plug-in or hack, but the database was forgotten to be upgraded accordingly.
An incomplete backup is used in the background, and when the data is imported, it is not imported into the database of the forum that has the corresponding version installed.
Solution: The same solution is given to the problem, but different treatment methods are used for different reasons.
Carefully check the installation instructions provided by the plug-in author and fill in the missing operations on the database. If the problem still cannot be solved, then you should doubt the availability of the plug-in. Consult the plug-in author or uninstall it.
Don’t be pretentious, wear shoes as big as your feet are. In short, the program file and the database can be matched.

8. Unknown column 'column_name' in 'field list'
Translation: Unknown field name column_name
Analysis: In This error occurs when a field name that does not exist in the specified table appears when executing a sql statement. The specific reasons can be divided into the following two types
The program files were modified when installing plug-ins or hacks, but the database was forgotten to be upgraded accordingly.
The program file and database do not match. For example, if the d2.5 database is configured for the d4.1 program, this error will definitely occur.
Solution: The cause is the same as 1 and 3 of Problem 8, so the solution is also the same.

9. You have an error in your SQL syntax
Translation: There is a syntax error in your SQL
Analysis: The forum’s standard procedure is that there is no SQL syntax error of. Therefore, the reasons for this error generally fall into two categories: installing plug-ins or modifying the program without authorization.
Different database versions are exported and imported. For example, the data of MySQL4.1 contains functions that MySQL4.0 does not have in the exported statement, such as character set settings. At this time, if these SQLs are imported into MySQL4.0 A sql syntax error will occur.
Solution:
Check carefully to see where the error is and correct it. If it doesn't work, replace the erroneous program with a standard program.
Please pay attention when backing up the database. If you do not plan to pour it into other versions of mysql, there is no need to consider it. Otherwise, special settings are required. Using the background data backup of DZ4.1, you can follow the prompts to set the desired format. Independent hosts can also export it to mysql4.0 format when they are anywhere.
mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt databse > test.sql

10. Duplicate entry 'xxx' for key 1Translation: Insert xxx to duplicate index 1
Analysis: If the index is primary unique, then the field corresponding to the data in the data table must ensure the uniqueness of each record. Otherwise this error will occur.
Generally occurs when writing operations to the database. For example, the Discuz! 4.1 forum program requires that the username of all members must be unique, that is, the index of username is unique. At this time, if an existing username is forcibly inserted into the cdb_members table This error will be sent to the record, or the username of a record will be updated to an existing username.
This error may also occur when changing the table structure. For example, in the Discuz! 4.0 forum database, the index type of cdb_members.username is index. At this time, records with the same username are allowed to exist. When upgrading to 4.1, the index of username needs to be changed from the original index to unique. If there is a record with the same username in cdb_members at this time, this error will occur.
When exporting data, sometimes the same record is exported repeatedly due to some reasons (the author is not clear yet), so it is inevitable that this error will occur when importing the backup data.
Modified the value of auto_increment, causing the "next Autoindex" to be an existing record
Solution: Two ideas, one is to destroy the unique index. The second is to get rid of duplicate data records and keep only one. Obviously the first way of thinking is not advisable. Then according to the second idea, we come up with the following solutions, which correspond to the above i ii iii
slightly
. Follow the information in the error prompt to delete the duplicate records in the database and keep only one. Then proceed with the upgrade operation.
The probability of this happening is very small. You can use a text editor to open the backup document and look for duplicate information. Remove the excess and keep only one strip.
Query the record with the largest auto_increment in the table, and set auto_increment to be one greater than it.
PS: repair table "table name", which can temporarily solve the problem.

11. Duplicate key name 'xxx'
Translation: Duplicate index name
Analysis: The index to be created already exists, which will cause this error. This error mostly occurs in When upgrading. It may be an error caused by repeated upgrades that have already been upgraded. It is also possible that the index added by the previous user without authorization happens to be the same as the one in the upgrade file.
Solution: Check whether the existing index and the index to be added are the same. If they are the same, you can skip this SQL statement. If they are different, delete the existing index and execute it again.

12. Duplicate column name 'xxx'
Translation: Duplicate field name xxx
Analysis: The added field xxx already exists, mostly occurs during the upgrade process, and The generation of question twelve is the same.
Solution: Check whether the existing fields have exactly the same attributes as the fields to be added. If they are the same, you can skip the SQL statement and not execute it. If they are different, delete the field. Then proceed with the upgrade procedure.

13. Table 'xxx' already exists
Translation: Data table xxx already exists
Analysis: Table 'xxx' already exists in the library, try to create it again with this name table will cause this error. The same happens in forum upgrades. Similar to question twelve.
Solution: Check whether the existing table is exactly the same as the table to be created. If it is the same, you can skip executing this SQL. Otherwise, please delete the existing table first, and then continue to execute the upgrade file.

14. Can't create database 'xxx'. Database exists
Translation: Can't create database xxx, the database already exists
Analysis: A database name under mysql Uniqueness must be guaranteed, otherwise this error will occur.
Solution: Rename the existing database or rename the database to be created, so as to prevent their names from conflicting.

15. Summary (for questions 11\12\13\14\15)
There is a keyword duplicate hidden in the error message of such questions
So what cannot be repeated for the MySQL database?
Database database
Data table under the same database
Field column under the same data table
Index key under the same data table
The same data table has a unique index (UNIQUE PRIMARY) In this case, these fields in the record cannot be repeated

16. Unknown system variable 'NAMES'
Translation: Unknown system variable NAMES
Analysis: Mysql version does not support Character set setting, this error will occur if the character set is forcibly set.
Solution: Remove the SET NAMES 'xxx' statement in the sql statement

17. Lost connection to MySQL server during query
Translation: MySQL server loses connection during query Period
Analysis: This problem sometimes occurs when connecting to the database remotely. The MySQL server lost the connection while executing a SQL statement.
Solution: Generally, there is no need to deal with it. If it occurs frequently, consider improving the hardware environment.

18. User 'red' has exceeded the 'max_updates' resource (current value: 500)
Translation: msql user red has exceeded 'max_updates' (maximum number of updates) ), 'max_questions' (maximum number of queries), 'max_connections' (maximum number of connections), currently set to 500
Analysis: There is a library under the mysql database called mysql, and there is a table in it for user Each record corresponds to the authorization of a mysql user. The fields max_questions max_updates max_connections respectively record the maximum number of queries, the maximum number of updates, and the maximum number of connections. This error will occur when any of the current parameters is greater than any set value.
Solution: Independent host users can directly modify the authorization table. After modification, restart mysql or update the authorization table, enter the mysql prompt and execute
FLUSH PRIVILEGES;
Remember to have a semicolon after it ';'
If the user of the virtual host always has this problem, you can contact The space provider negotiates and resolves the issue.

19. Too many connections (1040) Too many connections
Translation: The maximum number of connections has been reached
Problem analysis:
The number of connections exceeds the value set by mysql , related to both max_connections and wait_timeout. The larger the value of wait_timeout, the longer the idle wait for the connection, which will result in a larger number of current connections.
Solution:
1. Virtual host users please contact the space provider to optimize the configuration of the MySQL server;
2. Independent host users please contact the server administrator to optimize the configuration of the MySQL server. Please refer to:
Modify the parameters in the MySQL configuration file my.ini or my.cnf:
max_connections= 1000
wait_timeout = 10
Restart MySQL after modification. If this error is reported frequently, please optimize the overall server.

20. There is no such grant defined for user '%s' on host '%s'
Error number: 1141
Problem analysis:
MySQL No current user access to the database.
Solution:
1. Virtual host users, please contact the space provider to confirm whether the account provided to you has the authority to authorize the database.
2. For independent host users, please contact the server administrator to confirm whether the database account provided to you has the authority to manage this database.

21. Error on rename of '%s' to '%s' (errno: %d)
error.:1025
Problem analysis:
Please check whether your program has statements that modify the database table name.
Solution:
1. Please check where in your program you need to modify the database table name;
2. If your actual application really needs to modify the database table name, please contact the space provider or server The administrator has given you the permission to modify the library name and whether the server itself is normal.

22. Error reading file '%s' (errno: %d)
error.:1023
Problem analysis:
The database file cannot be read Pick.
Solution:
1. Virtual host users please contact the space provider to check whether the database is intact.
2. Independent host users please contact the server administrator to check whether MySQL itself is normal and whether MySQL can read the file. Linux users can check whether the owner of the MySQL database file is correct and whether the file itself is damaged.

23. Host '*****' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
error.:1129
Problem analysis:
An exception occurred in the database, please restart the database.
Solution:
1. Due to many connection errors, the host '****' is blocked. Virtual host users please contact the space provider for processing. Independent host users please contact the server administrator. In MySQL command control Execute 'mysqladmin flush-hosts' offline to unblock, or restart the MySQL database

24. dropping database (can't delete '%s', errno: %d)
error.:1009
Problem analysis:
The database file cannot be deleted, resulting in failure to delete the database.
Solution:
1. Check whether the database management account you use has permission to delete data.
2. Check whether the database exists.

Twenty-five, Got error 28 from table handler
error.:1030
Problem analysis:
The disk space where the database is located is full.
Solution:
1. Virtual host users please contact the space provider to increase the disk space where MySQL is located or clean up some useless files;
2. Independent host users please contact the server administrator to increase the disk space where MySQL is located or Clean up some useless files

26. Can't create a new thread; if you are not out of available memory, you can consult the manual for a possible OS-dependent bug.
error.:11/35
Problem analysis:
Database server problem, database operation cannot create a new thread. There are generally two reasons:
1. Server system memory overflow.
2. The environment software is damaged or the system is damaged.
Solution:
1. Virtual host users please contact the space provider to see if the memory and system of the database server are normal.
2. Independent host users, please contact the server administrator to check whether the server's memory and system are normal. If the server's memory is tight, please check which processes consume the server's memory, and consider whether to increase the server's memory to increase the overall load. ability.

27. Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client
error.:1251
Problem analysis:
If you If you encounter the above problems after upgrading MySQL to version 4.1 or above, please first make sure your MySQL Client is version 4.1 or higher (if there are problems under Windows, you can jump directly to the solution below, because MySQL in Windows is client and server together) installed).
Solution:
1. Windows platform
The main thing is to change the encryption method of the account connected to MySQL. MySQL 4.1/5.0 is encrypted through PASSWORD. This can be solved by the following two methods:
1) mysql->SET PASSWORD FOR 'some_user'@'some_host'=OLD_PASSWORD('new_password');
2) mysql->UPDATE mysql.user SET Password=OLD_PASSWORD('new_password') WHERE Host='some_host' AND User='some_user';
2. Linux/Unix platform
Under the Linux platform, first determine whether the MySQL client has been installed. Use rpm to install this. It's very simple. The Linux code is:
rpm -ivh MySQL-client-4.1.15-0.i386.rpm
Then when compiling php, add:
–with-mysql=/your/ path/to/mysql
can usually be solved. If this error still occurs, you can do it as follows:
mysql->SET PASSWORD FOR 'some_user'@'some_host'=OLD_PASSWORD('new_password');
mysql->UPDATE mysql. user SET Password=OLD_PASSWORD('new_password') WHERE Host='some_host' AND User='some_user';

28. Error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
error.:2002
Problem analysis:
This error usually occurs for the following two reasons:
1. The MySQL server is not started.
2. The MySQL server is started, but the socket file cannot be found.
Solution:
1. Virtual host users, please contact the space provider to confirm whether the database starts normally.
2. For independent host users, please check whether the MySQL service is turned on. If not, please start the MySQL service. If it is already turned on and it is a Linux system, please check the path of the MySQL socket, and then open config.inc. PHP finds
$dbhost = 'localhost'; Add a colon ':' and the path of the MySQL socket after the hostname.
For example, if the MySQL server is localhost
The path of MySQL socket is /tmp/mysql.sock
Then change it to the following:
$dbhost = 'localhost:/temp/mysql.sock';

29. Can't connect to MySQL server on 'localhost'
error.:2003
Problem analysis:
The MySQL service is not started, usually after MySQL cannot start due to abnormal circumstances, such as no available disk space, MySQL basedir path setting in my.ini is incorrect, etc.
Solution:
1. Check whether there is any remaining free space in the disk space, and try to keep enough disk space available.
2. Check whether the parameters such as basedir in my.ini are set correctly, and then restart the MySQL service.

30. Lost connection to MySQL server during query
error.:2013
Problem analysis:
The connection to the MySQL server was lost during the database query.
Solution:
1. Please confirm whether there are very inefficient programs in your program, such as some plug-ins. You can uninstall the plug-ins and check whether the server is normal;
2. The server itself is resource intensive , virtual host users please contact the space provider for confirmation, independent host users please contact the server administrator to check whether the server is normal.

31. Got a packet bigger than \'max_allowed_packet\' bytes
Error number: 1153
Problem analysis: Adjusting the size of Mantis' upload attachment did not Adjust the MySQL configuration file.
Solution:
1. Independent host users please adjust as follows:
Find the MySQL configuration file (my.cnf or my.ini)
Add a sentence in the [mysqld] section (if it exists , just adjust the value):
max_allowed_packet=10M
Just restart the MySQL service. The setting here is 10MB.
2. Virtual host users please contact the space provider to adjust this parameter.

More related free learning recommendations:mysql tutorial(Video)

The above is the detailed content of Summary of MySQL common error analysis and solutions. For more information, please follow other related articles on the PHP Chinese website!