MySQL common error analysis and solution Summary

MySQL common error analysis and solution Summary

1. Can't connect to MySQL server on 'localhost' (10061)
Translation: cannot connect to mysql on localhost
Analysis: This indicates 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. If the sub-server load is too high and the request is not available, this error will also be generated.
Solution: Since mysql is not started, start mysql on this machine. If the startup fails, most of them are caused by problems with your my. ini configuration. You can reconfigure it.
If the mysql server load is abnormal, run mysqladmin-uroot-p123 processlist In the mysql/bin directory to view the current mysql process.

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

3. Access denied for user: 'roota @ localhost' (Using password: YES)
Translation: The user's roota access to localhost is denied (not allowed to pass)
Analysis: this error is caused. Generally, the database username and password are incorrect compared to the mysql server.
Solution: carefully check the./config. inc. php under your forum and find $ dbuser and $ dbpw. Then reset and save the settings.

4. Access denied for user: 'Red @ localhost' to database 'newbbs'
User red does not have the permission to operate the database newbbs on the localhost Server
Analysis: This prompt is different from question 3. This error is caused by database operations. For example, in select update. This is because the user has no right to operate the database. For example, the select Operation Records Y in mysql. user. Select_priv and can operate N.
Solution: if it is your own independent host, update the corresponding user records of mysql. user. For example, the user to be updated here is red. You can also directly modify./config. inc. php to configure a user with database operation permissions.
Alternatively, run the following command to update the authorization grant all privileges on dbname. * to 'user' @ 'localhost' identified by 'Password'
Tip: to update the records in the mysql database, you must restart the mysql server to make the update take effect.
Flush privileges;

5. No Database Selected
Translation: no database is selected
Analysis: There are two causes
$ Dbname in config. inc. php is set incorrectly. As a result, the database does not exist at all. Therefore, false is returned when $ db-> select_db ($ dbname );
This is the same as issue 4 above. database users do not have the select permission and will also cause such errors. When you find that the configuration of config. inc. php is correct, but the error is still prompted, it must be the case.
Solution: Remedies
Open config. inc. php and find $ dbname to verify the configuration and save it.
Solution to problem 4

6. Can't open file: 'xxx _ forums. myi'. (errno: 145)
Translation: cannot open xxx_forums.MYI
Problem Analysis:
In this case, you cannot open cdb_forums.MYI. The possible causes include:
1. The server shuts down abnormally, the database space is full, or some other unknown reasons cause damage to the database table.
2. This error occurs when database files are directly copied and moved in unix-like operating systems due to file group issues.
Solution:
1. Repair Data Tables
You can use the following two methods to repair data tables: (the first method is only applicable to independent host Users)
1) Using myisamchk, MySQL comes with a dedicated user data table check and Repair Tool-myisamchk. Change the current directory to MySQL/bin. Generally, the myisamchk command can be run only under this directory. Common repair commands: myisamchk-r data file directory/data table name. MYI;
2) use phpMyAdmin to fix the data table. phpMyAdmin provides the data table repair function. after entering a table, click "operation" and click "repair table" in "Table maintenance" below.
Note: You must back up the database before performing the preceding two restoration methods.
2. Modify the file group (only applicable to independent host Users)
1) When copying a database file, the database file is not set to an account running MySQL that can be read and written (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 the SQL statement is executed. For example, SELECT * FROM xxx_members WHERE uid = 'xx'. If the xxx_members table does not exist in the $ dbname database, this error is prompted. There are three situations to discuss:
When installing the plug-in or hack, I modified the program file and forgot to upgrade the database accordingly.
Incomplete backups are used in the background, and the imported data is not imported into the database of the Forum with the corresponding version installed.
Solution: the solution is the same as the remedy, and the solution is different for different reasons.
Carefully compare the installation instructions provided by the plug-in author to add 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 the plug-in.
Do not wear big shoes. In short, make the program file and database support.

8. Unknown column 'column _ name' in 'field list'
Translation: Unknown field name column_name
Analysis: this error occurs when the field name is not in the specified table when the SQL statement is executed. The specific causes can be divided into the following two types:
When installing the plug-in or hack, I modified the program file and forgot to upgrade the database accordingly.
The program file is not compatible with the database. For example, this error is certainly returned when the d2.5 database is configured for the d4.1 program.
Solution: The cause is the same as that of problem 8. Therefore, the solution is the same.

9. You have an error in your SQL syntax
Translation: There is a syntax error in your SQL
Analysis: There is no SQL syntax error in the standard program of the Forum. There are two possible causes for this error:
Install plug-ins or modify programs without authorization.
Database Export and Import in different database versions. For example, the data in MySQL4.1 contains functions not available in MySQL4.0 in the Export Statement, such as Character Set setting, if these SQL statements are imported to MySQL4.0, an SQL syntax error occurs.
Solution:
Check carefully to see where the error occurred and correct it. If it is not feasible, replace the program with the standard program.
Pay attention when backing up the database. If you do not plan to import data to other versions of mysql, you do not need to consider it. Otherwise, you need to set it specially. You can follow the prompts to set the desired format by using DZ4.1 background data backup. The independent host can also export it everywhere in the format of mysql4.0.
Mysqldump-uroot-p-default-character-set = latin1-set-charset = gbk-skip-opt databse> test. SQL

10. Duplicate entry 'xxx' for key 1
Insert xxx to repeat 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 occurs.
This generally occurs when you write data to a database, such as Discuz! 4.1 The Forum program requires that the username of all Members must be unique, that is, the username index is unique. If you forcibly insert an existing username record into the cdb_members table, this error will be reported, or update the username of a record to an existing username.
This error may also occur when the table structure is changed. For example, Discuz! In the 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, because 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, this error will be thrown.
When exporting data, the same record may be repeatedly exported due to some reasons (the author is not clear yet). This error is inevitable when the backup data is imported.
The value of auto_increment is modified, so that "next Autoindex" is an existing record.
Solution: the first is to destroy the unique index. The second is to take out duplicate data records and keep only one record. Obviously, the first approach is not advisable. Then, based on the two ideas, we can find the following solutions, which correspond to the above I ii iii
Omitted
Follow the information in the error message to delete duplicate records in the database. Only one record is retained. And then proceed with the upgrade operation.
The probability of this situation is very small. You can open the backup document in a text editor to find duplicate information. Remove unnecessary ones and retain only one.
Query the maximum auto_increment record in the table, and set auto_incerment to be greater than its size.
PS: repaire table "table name", which can solve the problem temporarily.

11. Duplicate key name 'xxx'
Translation: duplicate index names
Analysis: if the index to be created already exists, this error is triggered most of the time during the upgrade. The error may be caused by repeated upgrades. It may also be the index that the user added without authorization, which is exactly the same as that in the upgrade file.
Solution: Check whether the existing index is the same as the index to be added. If the existing index is the same, skip this SQL statement. If the existing index is different, Delete the existing one and execute it again.

12. Duplicate column name 'xxx'
Translation: field name xxx already exists
Analysis: the added field xxx already exists, and most occurs during the upgrade process, which is the same as issue 12.
Solution: Check whether the existing fields are identical to the attributes of the fields to be added. If the fields are the same, skip the SQL statement and delete the fields if they are different. Then, execute the Upgrade Program.

13. Table 'xxx' already exists
Translation: Data Table xxx already exists
Analysis: the xxx table already exists in the database. This error occurs when you try to create a table with this name again. It also occurs in the upgrade of the Forum. Similar to question 12.
Solution: Check whether the existing table is exactly the same as the table to be created. In the same way, you can skip this SQL statement. Otherwise, Delete the existing table and continue to execute the upgrade file.

14. Can't create database 'xxx'. Database exists
Translation: you cannot create a database xxx. The database already exists.
Analysis: the database names under a mysql instance must be unique. Otherwise, this error occurs.
Solution: Change the name of an existing database or the name of the database to be created.

15. Summary (for Issue 11 \ 12 \ 13 \ 14 \ 15)
A keyword duplicate is hidden in the error message)
So what can't be repeated in a mysql database?
Database
Table in the same database
Column in the same data table
Index key under the same data table
The fields recorded in the same data table with UNIQUE indexes (unique primary) cannot be repeated.

16. Unknown system variable 'names'
Translation: Unknown system variable NAMES
Analysis: Mysql does not support Character Set setting. This error occurs when you forcibly set the character set.
Solution: remove the set names 'xxx' statement from the SQL statement.

17. Lost connection to MySQL server during query
Translation: MySQL server lost connection during Query
Analysis: remote connection to the database sometimes causes this problem. The connection is lost when the MySQL server executes an SQL statement.
Solution: Generally, it does not need to be processed. If this happens frequently, consider improving the hardware environment.

18. User 'red' has exceeded the 'max _ updates' resource (current value: 500)
Translation: The msql user red has exceeded 'max _ updates' (maximum number of updates), 'max _ questions '(maximum number of queries), and 'max _ connections' (maximum number of connections ), currently set to 500
Analysis: There is a mysql database under the mysql database. One of the tables is the user. Each record corresponds to the authorization of a mysql user. The max_questions max_updates max_connections field records the maximum number of queries, the maximum number of updates, and the maximum number of connections. This error occurs when any current parameter is greater than any set value.
Solution: independent host users can directly modify the authorization table. After the modification, restart mysql or follow the new authorization table to go To the mysql prompt and execute
Flush privileges;
Remember to have a semicolon ';' next to it ';'
If the problem persists for VM users, contact the space provider for a solution.

19. Too many Too worker connections (1040) connections
Translation: Maximum number of connections reached
Problem Analysis:
The number of connections exceeds the value set by mysql. It is related to max_connections and wait_timeout. The greater the value of wait_timeout, the longer the idle waiting time for the connection, which leads to the larger number of current connections.
Solution:
1. For VM users, contact the Space Provider to optimize the configuration of the MySQL server;
2. for independent host users, contact the server administrator to optimize the configuration of the MySQL server. For details, refer:
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, perform overall server optimization.

For more details, please continue to read the highlights on the next page:

1 2 Next Page