Shulou(Shulou.com)05/31 Report--

Editor to share with you what are common mistakes in MySQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

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

Cannot connect to mysql on localhost

Analysis: this shows that the "localhost" computer exists, but no MySQL service is provided on this machine.

The MySQL service on this machine needs to be started, and this error will also occur if the machine is too loaded to respond to the request.

Solution: since it is not started, then start the mysql of this machine. If the startup is not successful, it is mostly due to a problem with your my.ini configuration. Just reconfigure it.

If you feel that the mysql load is abnormal, you can execute mysqladmin-uroot-p123 processlist under the mysql/bin directory to view the current process of mysql.

2. Unknown MySQL Server Host 'localhosadst' (11001)

Unknown MySQL server localhosadst

Analysis: server localhosasdst does not exist. Or can't connect at all.

Solution: double-check the. / config.inc.php below your forum to find $dbhost and reset it to the correct mysql server address.

3. Access denied for user: 'roota@localhost' (Using password: YES)

User roota access to localhost is denied (not allowed to pass)

Analysis: this error is caused by incorrect database username and password relative to mysql server.

Solution: carefully check your forum below. / config.inc.php to find $dbuser, $dbpw to verify and then reset to save.

4. Access denied for user: 'red@localhost' to database' newbbs'

User red does not have permission to operate database newbbs on the localhost server

Analysis: this tip is different from question 3. That is blocked when connecting to the database, and this error is caused when operating on the database. For example, in select update and so on. This is because the user does not have the right to operate the database. For example, select this operation records in mysql.user.Select_priv Y can operate N can not operate.

Solution: if it is your own independent host, update the corresponding user record of mysql.user, for example, the user you want to update here is red. Or directly modify. / config.inc.php to configure it with a user with permission to operate on the database

Or update the authorization grant all privileges on dbname.* to 'user'@'localhost' identified by' password' with the following command

Tip: if you update the records in the mysql library, you must restart the mysql server for the update to take effect.

FLUSH PRIVILEGES

5. No Database Selected

No database has been selected

Analysis: there are two reasons.

The $dbname setting in config.inc.php is incorrect. So that the database does not exist at all, so false is returned when $db- > select_db ($dbname);

As with question 4 above, database users do not have select permissions, which will also lead to such errors. When you find that there is nothing wrong with the setting of config.inc.php, but still prompt this error, that must be the case.

Solution: prescribe the right medicine to the case

Open config.inc.php to find $dbname to verify reconfiguration and save

The solution to the same problem four

6. Can't open file: 'xxx_forums.MYI'. (errno: 145)

Cannot open xxx_forums.MYI

Problem analysis:

This situation is caused by the inability to open cdb_forums.MYI. The possible reasons for this situation are:

1. The abnormal shutdown of the server, the full space of the database, or some other unknown reasons have caused damage to the database table.

2. Under the similar unix operating system, directly copying and moving the database file will cause this error because of the group problem of the file.

Solution:

1. Repair the data table

You can repair the datasheet in two ways: (the first method is only suitable for stand-alone host users)

1) using myisamchk, MySQL comes with a special tool for checking and repairing user data tables-myisamchk. Change the current directory to MySQL/bin, which is the only place to run the myisamchk command. The commonly used repair command is: myisamchk-r data file directory / data table name .MYI

2) through phpMyAdmin repair, phpMyAdmin has the function to repair the data table. After entering a table, click "Operation", and then click "repair Table" in "Table maintenance" below.

Note: the above two repair methods must back up the database before execution.

2. Modify the group of files (only suitable for independent host users)

1) in the process of copying the database file, the database file is not set to read and write to the account running by MySQL (generally applicable to Linux and FreeBSD users).

7. Table 'test.xxx_sessions' doesn't exist

Translation: xxxxx table does not exist

Analysis: the table was not found when executing the sql statement, such as: SELECT * FROM xxx_members WHERE uid='XX'. Here, if the table xxx_members does not exist in the $dbname library, it will prompt for this error. It can be divided into the following three situations:

When installing the plug-in or hack, you modified the program files and forgot to upgrade the database accordingly.

Incomplete backups are used in the background, and the data is not imported into the database where the corresponding version of the forum is installed.

Solution: also prescribe the right remedy to the case, different reasons and different treatment methods.

Carefully compare the installation instructions provided by the plug-in author to make up for the missing operations on the database, and if you still can't solve the problem, you should doubt the availability of the plug-in. Consult the plug-in author or uninstall it.

Don't get the wrong guy. Wear shoes as big as your feet are. In short, make the program file and database matching.

8. Unknown column 'column_name' in' field list'

Translation: unknown field name column_name

Analysis: this error occurs when a field name that is not in the specified table appears during the execution of the sql statement. The specific causes can be divided into the following two types

When installing the plug-in or hack, you modified the program files and forgot to upgrade the database accordingly.

The program file does not match the database, for example, the database of d2.5 is configured to the program of d4.1. This error is bound to occur.

Solution: the cause and problem 8 1 and 3 are the same, so the solution is the same.

IX. You have an error in your SQL syntax

There is a syntax error in your sql

Analysis: the standard procedure of the forum is free of sql syntax errors. So there are generally two kinds of reasons for this error.

Install plug-ins or modify programs without authorization.

Different database versions of database export and import, such as MySQL4.1 data in the export statement contains functions that MySQL4.0 does not have, such as character set settings, when importing these sql into MySQL4.0, it will produce sql syntax errors.

Resolve:

Check carefully to see where the error is and correct it. It is impossible to replace the wrong program with the standard procedure.

When backing up the database, it should be noted that there is no special consideration if you do not plan to pour it into other versions of mysql, otherwise you should have a special setting. Using the background data backup of DZ4.1, you can follow the prompts to set the desired format. Standalone hosts can also be exported to mysql4.0 format everywhere.

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 duplicate index 1

Analysis: if the index is primary unique, then the field corresponding to the data of the data table must ensure the uniqueness of each record. Otherwise, this error will occur.

Generally occurs in the database write operation, such as Discuzi4.1 Forum program requires all members of the user name username must be unique, that is, username index is unique, then if you forcibly insert an existing username record into the cdb_members table will send this error, or update a record of the username to an existing username.

This error can also be caused when changing the structure of the table. For example, the index type of cdb_members.username in the database of Discuzv4.0 Forum is index, which allows records with the same username to exist at this time, when upgrading to 4.1, because the index of username should be changed from the original index to unique. If there is a record of the same username in the cdb_members, this error will be raised.

When exporting data, sometimes the same record is exported repeatedly for some reason (the author is not clear yet), so it is inevitable that this error occurs when the backup data is imported.

Modified the value of auto_increment so that "next Autoindex" is an existing record

Solution: two ways of thinking, one is to destroy the unique index. The second is to kill the duplicate data records and keep only one. It is obvious that the first way of thinking is undesirable. So according to the second way of thinking, we can come up with the following solutions, corresponding to the iii iii above.

Slightly

Follow the information in the error prompt to delete the duplicate record in the database and keep only one entry. Continue with the upgrade operation after that.

The probability of this happening is very small, and you can open the backup document with a text editor to find duplicate information. Take off the excess and keep only one.

Query the record with the largest auto_increment in the table and set auto_incerment to be one larger than it.

PS:repaire table "table name", which can solve the problem temporarily.

Duplicate key name 'xxx'

Translation: duplicate index name

Analysis: if the index to be created already exists, this error will be raised, which often occurs during the upgrade. It may be an error caused by a repeated upgrade that has been upgraded. It is also possible that the index added by the user before is exactly the same as that in the upgrade file.

Solution: see if the existing index is the same as the index you want to add, and you can skip the sql statement. If not, delete the existing index now, and then execute it later.

Duplicate column name 'xxx'

Translation: duplicate field name xxx

Analysis: the added field xxx already exists, and most of it occurs during the upgrade process, which is the same as that of problem 12.

Solution: check to see if the existing field is exactly the same as the field property to be added. If it is the same, you can skip not executing the sql, and if it is different, delete the field. Continue with the upgrade procedure after that.

13. Table 'xxx' already exists

The datasheet xxx already exists

Analysis: the xxx table already exists in the library, and another attempt to create a table with that name will cause this error. It also happens in the upgrade of the forum. Similar to question 12.

Solution: see if the existing table is exactly the same as the one you are about to create. If so, you can skip not executing the sql, otherwise delete the existing table first, and then continue with the upgrade file.

14. Can't create database 'xxx'. Database exists

Cannot create database xxx, database already exists

Analysis: the database name under a mysql must be unique, otherwise there will be this error.

Solution: rename the existing database or the database you are about to create, so that their names do not conflict.

15. Summary (for question 11\ 12\ 13\ 14\ 15)

There is a keyword duplicate hidden in the error prompt of this kind of problem (repeat)

So what can't be repeated for a mysql database?

Database database

Data table table under the same database

Field column under the same data sheet

Index key under the same data table

These fields in the record cannot be duplicated when the index of the same data table is unique (UNIQUE PRIMARY).

Unknown system variable 'NAMES'

Translation: unknown system variable NAMES

Analysis: Mysql version does not support character set setting. This error will occur if you force the character set to be set at this time.

Solution: remove the SET NAMES 'xxx' statement from the sql statement

Seventeen. Lost connection to MySQL server during query

The MySQL server lost its connection during the query

Analysis: this problem sometimes occurs when connecting to a database remotely. The MySQL server lost the connection while executing a sql statement.

Solution: generally do not need to deal with, if it occurs frequently, then consider improving the hardware environment.

User 'red' has exceeded the' max_updates' resource (current value: 500)

Msql user red has exceeded 'max_updates' (maximum number of updates),' max_questions' (maximum number of queries), 'max_connections' (maximum number of connections), and is currently set to 500

Analysis: under the mysql database, there is a library for mysql, which has a table for user, and each entry corresponds to the authorization of a mysql user. The field max_questions max_updates max_connections records the maximum number of queries, the maximum number of updates and the maximum number of connections, respectively. This error occurs when any of the current parameters is greater than any of the set values.

Solution: independent host users can modify the authorization table directly. Restart mysql or follow the new authorization table after modification, and execute it at the mysql prompt

FLUSH PRIVILEGES

Remember to have a semicolon after it.

If the user of the virtual host always appears this problem, you can consult the space provider to solve it.

XIX. Too many connections (1040) too many links

Reach the maximum number of connections

Problem analysis:

The number of connections exceeds the value set by mysql, which is related to both max_connections and wait_timeout. The higher the value of wait_timeout, the longer the idle wait for connections, which results 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

two。 For standalone 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 you report this error frequently, please optimize the server as a whole.

There is no such grant defined for user'% s'on host'% s'

Error number: 1141

Problem analysis:

The current user of MySQL does not have access to the database.

Solution:

1. For 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 right to manage this database.

21, Error on rename of'% s'to'% s'(errno:% d)

Error.:1025

Problem analysis:

Please check that your program has statements to change the name of the database table.

Solution:

1. Please check where in your program you need to change the database table name

two。 If your actual application really needs to be modified to the database table name, please contact the space vendor or server administrator to give you the permission to modify the database 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.

Solution:

1. For virtual host users, please contact the space provider to see if the database is intact.

two。 For independent host users, please contact the server administrator to check whether MySQL itself is normal and whether MySQL can read files. Linux users can check whether the owner of MySQL database files is correct and whether their own files are corrupted.

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 should contact the space provider for processing. Independent host users should contact the server administrator and execute 'mysqladmin flush-hosts' to unblock' under the MySQL command console, or restart the MySQL database.

24, dropping database (can't delete'% slots, errno:% d)

Error.:1009

Problem analysis:

The database file could not be deleted, causing the deletion of the database to fail.

Solution:

1. Check whether the database management account you use has permission to delete data.

two。 Check to see if the database exists.

25 、 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

two。 Stand-alone 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:

There is a problem with the database server. The database operation cannot create a new thread. There are usually two reasons:

1. Server system memory overflow.

two。 Environment software damage or system damage.

Solution:

1. For virtual host users, please contact whether the memory and system of the space merchant database server are normal.

two。 Stand-alone 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, check which processes consume the server's memory. At the same time, consider whether to increase the server's memory to increase the overall load capacity.

27 、 Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Error.:1251

Problem analysis:

If you encounter the above problems after upgrading MySQL to version 4.1 or later, please make sure that your MySQL Client is version 4.1 or later (if there is a problem under Windows, you can skip to the following to see the solution, because MySQL is installed in Windows by client and server).

Solution:

1. Windows platform

The main purpose is to change the encryption method of the account connected to MySQL, which is encrypted by MySQL 4.1 and PASSWORD. It can be solved in two ways:

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 client of MySQL has been installed. This is easy to install with rpm. The Linux code is:

Rpm-ivh MySQL-client-4.1.15-0.i386.rpm

Then add the following when compiling php:

-with-mysql=/your/path/to/mysql

It can be solved under normal circumstances. If this error persists, you can do it in the following ways:

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 two reasons:

The 1.MySQL server is not turned on.

The 2.MySQL server is turned on, but the socket file cannot be found.

Solution:

1. Virtual host user, please contact the space provider to confirm whether the database is started normally.

two。 For independent host users, please check whether the MySQL service is enabled. If it is not enabled, please start the MySQL service. If it is enabled and it is a Linux system, please check the socket path of MySQL, and then open config.inc.php to find it.

$dbhost = 'localhost'; adds a colon after hostname': 'and the path to MySQL's socket.

For example, the MySQL server is localhost

The path to the socket of MySQL 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 failure to start the MySQL service is usually caused by the failure of MySQL to start under abnormal circumstances, such as no available disk space, incorrect basedir path setting of MySQL in my.ini, and so on.

Solution:

1. Check to see if there is any free disk space and try to keep enough disk space available.

two。 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 make sure that there are any inefficient programs in your program, such as some plug-ins. You can uninstall the plug-ins and check whether the server is working properly.

two。 The server itself is short of resources, virtual host users please contact the space provider to confirm, 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: resize the upload attachment of Mantis but not the configuration file of MySQL.

Solution:

1. For independent host users, please follow the following methods:

Find the configuration file (my.cnf or my.ini) for MySQL

Add a sentence to the [mysqld] section (if it exists, you can adjust its value):

Max_allowed_packet=10M

Just restart the MySQL service. 10MB is set here.

2. For virtual host users, please contact the space provider to adjust this parameter.

The above is all the contents of the article "what are the common mistakes in MySQL?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.