Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you the analysis of common questions about MySql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1.MySQL failed to restart problem Resolution Warning: World-writable config file'/ etc/my.cnf' is ignored

Reason

[root@ttlsa ~] # service mysqld stop

Warning: World-writable config file'/ etc/my.cnf' is ignored

Warning: World-writable config file'/ etc/my.cnf' is ignored

MySQL manager or server PID file could not be found! [FAILED]

View permissions for my.cnf

[root@ttlsa ~] # ls-l / etc/my.cnf

-rwxrwxrwx 1 root root 4878 Jul 30 11:31 / etc/my.cnf

Permission 777, any user can change my.cnf, there are great security risks.

Solution:

Fix MySQL issu

[root@ttlsa ~] # chmod 644 / etc/my.cnf

My.cnf is set to be readable and writable by users, but not writable by other users.

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

Reason

Cannot connect to mysql on localhost

Solution:

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, go to 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.

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

Reason

Unknown MySQL server localhosadst, AC group (1017539290) 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.

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

Reason

User roota access to localhost is denied (not allowed), resulting in this error. Generally, the user name and password of the database are incorrect relative to the mysql server.

Solution:

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

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

Reason

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

This hint is different from question three. That was blocked when connecting to the database, and this error was 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.

6.No Database Selected

Reason

There are two reasons for this.

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:

an antidote against the disease

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

The solution to the same problem four

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

Reason

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)

During the process of copying the database file, the database file is not made readable and writable under the account running by MySQL (generally applicable to Linux and FreeBSD users).

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

Translation: xxxxx table does not exist

Reason

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 person, the size of the foot and the size of the shoes. In short, make the program file and database matching.

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

Reason

This error occurs when executing the sql statement with a field name that is not in the specified table. The specific causes can be divided into the following two types: when installing the plug-in or hack, you modified the program file 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 is the same as problem 8 1 and 3, so the solution is the same.

10.You have an error in your SQL syntax

Reason

The standard procedure of the forum is free of sql syntax errors. So there are generally two reasons for this error: installing plug-ins or modifying 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, there will be sql syntax errors.

Solution:

Check carefully to see where the error is and correct it. It is really 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

11.Duplicate entry 'xxx' for key 1

Insert xxx to duplicate index 1

Reason

If the index is primary unique, the exchange group (1017539290), then the field corresponding to the data of the data table must guarantee the property 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, that is, the index of username is unique, then if you forcibly insert an existing cdb_members table into the username record will send this error, or update a record of 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.

The value of auto_increment has been modified so that the "next Autoindex" is an existing record.

Solution:

There are two ways of thinking, one is to destroy the 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 a record with a large auto_increment in the table, and set the auto_incerment to be one larger than it.

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

12.Duplicate key name 'xxx'

Translation: duplicate index name

Reason

The index to be created already exists, which will cause this error, which often occurs when upgrading. 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:

To see if the existing index is the same as the index to be added, you can skip the sql statement, and if not, delete the existing index now, and then execute it later.

13.Duplicate column name 'xxx'

Translation: duplicate field name xxx

Reason

The added field xxx already exists, and most of it occurs during the upgrade process, which is the same as the 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 not, delete the field. Continue with the upgrade procedure after that.

14.Table 'xxx' already exists

The datasheet xxx already exists

Reason

The xxx table already exists in the library, and another attempt to create a table with that name will raise 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 going to create. If so, you can skip not executing the sql, otherwise delete the existing table first, and then continue with the upgrade file.

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

Cannot create database xxx, database already exists

Reason

The database name under a mysql must be guaranteed, or there will be this error.

Solution:

Rename the existing database or the database to be created, so as not to let their names conflict.

16. Summary (for question 1112131415)

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 of the same data table cannot be repeated in the case of index (UNIQUE PRIMARY).

17.Unknown system variable 'NAMES'

Translation: unknown system variable NAMES

Reason

The Mysql version does not support character set setting. This error occurs when you force a character set.

Solution:

Remove the SET NAMES 'xxx' statement from the sql statement

18.Lost connection to MySQL server during query

The MySQL server lost its connection during the query

Reason

This problem sometimes occurs when connecting to a 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, then consider improving the hardware environment.

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

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

Reason

Under the mysql database, there is a library called mysql, which has a table called user, and each entry corresponds to the authorization of a mysql user. The field max_questions max_updates max_connections records the number of large queries and updates respectively, and 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 FLUSH PRIVILEGES at the mysql prompt

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.

20.Too many connections (1040) too many links

Reach the number of connections in Dalian

Reason

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

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.

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

Error number: 1141

Reason

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.

22.Error on rename of'% s'to'% s'(errno:% d)

Error.:1025

Reason

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.

23.Error reading file'% s'(errno:% d)

Error.:1023

Reason

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.

24.Host'*'is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Error.:1129

Reason

There is an exception in the database. Please restart the database.

Solution:

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 console, or restart the MySQL database.

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

Error.:1009

Reason

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.

26.Got error 28 from table handler

Error.:1030

Reason

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

27.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

Reason

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.

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

Error.:1251

Reason

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 below 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'

29.Error: Can't connect to local MySQL server through socket'/ var/lib/mysql/mysql.sock'

Error.:2002

Reason

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'

30.Can't connect to MySQL server on 'localhost'

Error.:2003

Reason

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.

31.Lost connection to MySQL server during query

Error.:2013

Reason

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.

32.Got a packet bigger than 'max_allowed_packet' bytes

Error number: 1153

Cause communication group (1017539290)

Resized the upload attachment of Mantis but not the configuration file of MySQL.

Solution:

1. For independent host users, please adjust according to 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

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 the analysis of common MySql questions shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.