MySQL common errors and solutions

1. Can't connect to MySQL server on 'localhost'(10061)?

Translation: Can't connect to mysql on localhost? Analysis: This shows that the "localhost" computer exists, but the MySQL service is not 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 process of mysql.

2. Unknown MySQL ServerHost 'localhosadst' (11001)

Translation: Unknown MySQL server localhosadst? Analysis: Server localhosasdst does not exist. Or can't 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 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, in selectupdate 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 it with a user with database operation permissions? Or update the authorization grantall privileges on dbname.* to 'user'@'localhost' through the following command 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 false is returned when $db->select_db($dbname); is the same as question 4 above. The database user does not have select permissions, 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 configuration, and save it? Same as the solution to question 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. Possible reasons for this situation are: 1. Server Abnormal shutdown, database space is full, or some other unknown reasons, causing 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 group ownership of the file. 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, MySQL comes with special user data table checking and repair Tool - 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" at the bottom Click "Repair Table" in "Table Maintenance". ?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 was 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: The xxxxx table does not exist? Analysis: The table is not found when executing the sql statement, such as: SELECT * FROMxxx_members WHERE uid='XX' If the table xxx_members does not exist in the $dbname library, then this error will be prompted. Specifically, it can be discussed in the following three situations: When installing plug-ins or hacks, the program files are modified, but the database is forgotten to be upgraded accordingly. ?Incomplete backup is used in the background, and when importing data, it is not imported into the database of the forum that has the corresponding version installed. ?Solution: The same prescribe the right medicine, but different treatment methods for different reasons. ?Carefully check the installation instructions provided by the plug-in author and complete the missing operations on the database. If the problem still cannot be solved, you should doubt the availability of the plug-in. Consult the plug-in author or uninstall it. ?Don’t be arrogant, wear shoes as big as your feet. 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: 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 use by the d4.1 program, this error will definitely occur. ?Solution: The cause is the same as 1 and 3 of question 8, so the solution is also the same.

9. You have an error in yourSQL syntax

Translation: There is a syntax error in your SQL? Analysis: Forum standard The program has no SQL syntax errors. So there are generally two types of causes for this error? Installing plug-ins or modifying the program without authorization. ?Database export and import of different database versions. For example, the data of MySQL4.1 contains functions that MySQL4.0 does not have in the exported statement, such as the settings of character set. At this time, if these sql are imported into MySQL4 When .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. ?Be careful 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 1

Translation: Insert xxx to duplicate index 1? Analysis: If the index is primaryunique, then the field corresponding to the data in the data table must ensure that it is 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 record is forcibly inserted into the cdb_members table This error will be posted, 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 be triggered. ?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 iiii? Briefly? 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 during upgrades. 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: The field name xxx is duplicated?Analysis: The added field xxx already exists, and it mostly occurs during the upgrade process , which is the same as the generation of question twelve. ?Solution: Check whether the existing field is exactly the same as the field property to be added. If they are the same, you can skip and not execute this SQL statement. If they are different, delete the field. Then proceed with the upgrade procedure.

13. Table 'xxx' alreadyexists

? Translation: Data table xxx already exists? Analysis: Table xxx already exists in the library, try to create this name again 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 databasexxx, the database already exists? Analysis : A database name under mysql must be unique, 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 for mysql database, what cannot be repeated? ?Database database? Data table table under the same database? Field column under the same data table? Index key under the same data table? These fields in the records of the same data table cannot be repeated when the index is unique (UNIQUEPRIMARY)

16. Unknown system variable'NAMES'

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


17. Lost connection toMySQL server during query?

Translation: MySQL server Loss of connection during query? Analysis: Remote connection to the database sometimes has this problem. This is caused by the MySQL server losing 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 the 'max_updates' (maximum Update times), '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 for mysql, and there is a table for user in it 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 users of Virtual Host always have this problem, please contact The space provider negotiates and resolves the issue.

19. Too many connections (1040) Too many connections?

Translation: The maximum number of connections is reached? Problem analysis: The number of connections exceeds the value set by mysql, which is 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. For virtual host users, please contact the space provider to optimize the configuration of the MySQL server; 2. For independent host users, please Contact the server administrator to optimize the configuration of the MySQL server. Please refer to: Modify the parameters in MySQL config file my.ini or my.cnf: max_connections= 1000 wait_timeout = 10 Restart MySQL after modification. If If this error is reported frequently, please do some overall optimization of the server.

20. There is no such grantdefined for user '%s' on host '%s'?

Error number: 1141?Problem analysis:?MySQL current The user does not have permission to access 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. 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 administrator to open the modification for you. Are the permissions of the library name and the server itself normal?


22. Error reading file '%s'(errno: %d)?error.:1023?

Problem analysis:?Database file cannot be read. ?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: There is an exception 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 and execute '****' in the MySQL command console. Mysqladmin flush-hosts' can be unblocked, or restart the MySQL database


24. dropping database (can'tdelete '%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.


25. Got error 28 from tablehandler?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 newthread; 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 new threads. Generally there are two reasons: 1. The server system memory overflows. ?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 improve the overall load capacity.


27. Error: Client does notsupport authentication protocol requested by server; consider upgrading MySQLclient?error.:1251?

Problem analysis: 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 you have problems under Windows, just jump to the bottom to see the solution, because MySQL is in Windows is installed together with client and server). Solution: 1. Windows platform: Mainly change the encryption method of the account connected to MySQL. MySQL 4.1/5.0 is encrypted through PASSWORD. It 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 SETPassword=OLD_PASSWORD(' new_password') WHERE Host='some_host' ANDUser='some_user';?2.Linux/Unix platform? Under the Linux platform, first determine whether the MySQL client has been installed. This is very simple to install using rpm. 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? Under normal circumstances, it can 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 is generally due to 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 has been started. If not, please start the MySQL service; if it has been started and it is a Linux system, please check the path of the MySQL socket, and then open config.inc.php to find ?$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 the MySQL socket is /tmp/mysql.sock? Then change it to the following:?$dbhost= 'localhost:/temp/mysql.sock';


29. Can't connect to MySQLserver on 'localhost'?error.:2003?

Problem analysis: The MySQL service is not started. Generally, MySQL cannot be started under abnormal circumstances. Caused by startup, such as no available disk space, incorrect MySQL basedir path setting in my.ini, etc. ?Solution: ?1. Check whether there is any remaining free space on 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 MySQLserver 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 resources of the server itself are tight, virtual host users please contact The space provider confirmed that independent host users should 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: Adjusted Mantis upload The size of the attachment was not adjusted in the MySQL configuration file. Solution: 1. For independent host users, please adjust as follows: Find the MySQL configuration file (my.cnf or my.ini) and add a sentence to the [mysqld] section (if it exists, just adjust its value): ?max_allowed_packet=10M? Just restart the MySQL service. The setting here is 10MB.

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