A summary of common error analysis and solution methods for MySQL transfer

Source: Internet
Author: User
Tags file copy mysql client mysql version phpmyadmin server memory

One, Can ' t connect to MySQL server on ' localhost ' (10061)
Translation: Cannot connect to MySQL on localhost
Analysis: This indicates that the "localhost" computer is present, but the MySQL service is not available on this machine.
Need to start the MySQL service on this machine, if the load is too high, the corresponding request will also produce this error.
Solution: Since it is not activated then go to start the machine MySQL. If the startup is unsuccessful, the majority is due to a problem with your my.ini configuration. Reconfigure it.
If you feel the MySQL load is abnormal, you can go to the Mysql/bin directory to perform mysqladmin-uroot-p123 processlist to see the current MySQL process.

Second, Unknown MySQL Server Host ' localhosadst ' (11001)
Translator: unknown MySQL server localhosadst
Analysis: Server LOCALHOSASDST does not exist. Or you can't connect at all.
FIX: Double check your forum below./config.inc.php find $dbhost reset to the correct MySQL server address.

Third, Access denied for user: ' [email protected] ' (Using password:yes)
Translator: User Roota access localhost denied (no pass allowed)
Analysis: Causes this error general database user name and password relative to MySQL server is incorrect
Resolution: Double check your forum below./config.inc.php Find $dbuser, $DBPW verify and Reset save.

Iv. Access denied for user: ' [e-mail protected] ' to database ' Newbbs '
Translation: User red does not have permission to manipulate database on localhost server Newbbs
Analysis: This hint and the question three are different. That was blocked when the database was connected, and the error was caused when the database was being manipulated. For example, select Update and so on. This is because the user does not have the appropriate power to manipulate the database. For example, Select this operation in the Mysql.user.Select_priv record Y can operate n cannot be manipulated.
Workaround: If it is your own standalone host then update Mysql.user's corresponding user record, for example, the user here to update is red. or directly modify the./config.inc.php to configure a user with permissions to the database operation
or update the grant all privileges on dbname.* to ' user ' @ ' localhost ' identified by ' password ' with the following command
Tip: Update the records in the MySQL library be sure to restart the MySQL server for the update to take effect
FLUSH privileges;

Five, No Database Selected
Translation: No database is selected on
Analysis: There are two reasons for the resulting
Config.inc.php inside the $dbname set the wrong. The database does not exist at all, so the $db->select_db ($dbname); Returns False when the
As with the above problem four, the database user does not have SELECT permissions, which can also cause such errors. When you find that config.inc.php's settings have no problem, but still prompt for this error, that must be the case.
Solution: The Remedy
Open config.inc.php Find $dbname verify re-configuration and save
Solution to the same problem four

VI, Can ' t Open file: ' Xxx_forums. MYI '. (errno:145)
Translation: Xxx_forums cannot be opened. MYI
Problem Analysis:
This situation does not open cdb_forums. MYI caused this situation may be caused by:
1, the server is not properly shut down, the database is full of space, or some other unknown reason, the database table caused damage.
2, the Unix-like operating system directly to the database file copy movement will be due to the file is a group problem caused by this error.
Workaround:
1, fix the data table
you can repair the data table in two ways: (the first method is only for standalone host users)
1) using Myisamchk, MySQL comes with a dedicated user data sheet inspection and Repair tool--MYISAMCHK. Change the current directory to Mysql/bin below, under normal circumstances only under this can run the MYISAMCHK command. Common repair commands are: Myisamchk-r data file directory/data table name. MYI;
2) through PhpMyAdmin repair, phpMyAdmin with the function of repairing the data table, enter into a table, click "Action", in the bottom of "Table maintenance" click "Repair Table".
Note: You must back up the database before you perform both of these fixes.
2, modify the genus Group of files (only for standalone host users)
1) The database file is not set up as a MySQL run account to read and write (typically for  Linux  and FreeBSD users) while copying the database files.

Seven, Table ' test.xxx_sessions ' doesn ' t exist
Translation: xxxxx table does not exist
Parse: The table was not found when executing the SQL statement, for example: SELECT * from xxx_members where uid= ' XX ' here if the table Xxx_members does not exist in the $dbname library, then this error will be prompted. The specific can be divided into the following three situations to discuss:
When installing plug-ins or hack, the program files are changed, and the database is upgraded accordingly.
A non-full backup is used in the background, and importing data is not imported into a database that has the appropriate version of the Forum installed.
Solution: The same remedy, different reasons for different treatment methods.
Carefully control the installation instructions provided by the plug-in, the omission of the database to complement the operation, if still does not solve the problem, you should be suspicious of the plug-in usability. Check with the plugin author or uninstall it.
Do not pigtailed, how big the foot is to wear how big shoes. In short, the program files and database matching can be.

Viii. Unknown column ' column_name ' in ' Field list '
Translation: Unknown field name column_name
Parsing: This error occurs when the Execute SQL statement is a field name that does not appear in the specified table. Specific causes can be divided into the following two kinds of
When installing plug-ins or hack, the program files are changed, and the database is upgraded accordingly.
Program files and databases do not match, such as d2.5 database configuration to d4.1 program to use this error will definitely occur.
Resolution: Causes and issues eight of 1 and 3 are the same, so the workaround is the same.

Nine, you have the error in your SQL syntax
Translation: There is a syntax error in your SQL
Parse: Forum standard program is not SQL syntax error. So the cause of this error is generally two types of
install plug-ins or unauthorized modification of the program.
Different database version database export import, such as MySQL4.1 's data in the exported statement contains MySQL4.0 features, such as Character set settings, if you import these SQL into the MySQL4.0 will produce SQL syntax error.
FIX:
Check carefully to see where the error is, fix it, and simply replace the faulty program with a standard program.
in the database backup should be aware that if you do not plan to pour into other versions of MySQL is not special considerations, and vice versa to special settings. Using DZ4.1 background Data backup, you can follow the prompts to set the desired format. Standalone hosts can also be exported to mysql4.0 format when they are everywhere.
Mysqldump-uroot-p--default-character-set=latin1--set-charset=gbk--skip-opt databse > Test.sql

10, Duplicate entry ' xxx ' for key 1
translation: inserting XXX causes index 1 to repeat
Analysis: If the index is primary unique 22 kinds, Then the data table data corresponding to this field must be guaranteed the uniqueness of each record. Otherwise, this error will be generated.
generally occurs when writing to the database, such as the discuz!4.1 Forum program requires that all members of the user name username must be unique, that is, username index is unique, when forced to cdb_ The record of inserting an existing username in the members table will send this error, or update the username of a record to an existing username. This error can also be caused by the
changing the table structure. For example, the index type of cdb_members.username in the database of the discuz!4.0 Forum is index, which allows records with the same username to exist. When upgrading to 4.1, because the index of the username is changed from the original index to unique. If there is a record of the same username in Cdb_members, then this error will be raised.
When exporting data is sometimes caused by the fact that the same record was repeatedly exported for some reason (the author is not yet clear), it is inevitable that this backup data will appear when importing. The
modifies the value of auto_increment, causing "next AutoIndex" to be an existing record
: Two ideas, one is to destroy the uniqueness of the index. The second is to kill the duplicate data records and keep only one. Obviously the first way of thinking is undesirable. Then according to the two ideas we have the following solutions, corresponding to the above I II III
slightly
follow the information in the error message to the database to delete duplicate records, only one can be retained. Then proceed with the upgrade operation.
The probability of this happening is small, and you can open the backup document with a text editor and look for duplicate information. Take it out of the way, just keep one. The
queries the auto_increment largest record in the table, setting the auto_incerment to be larger than the first.
Ps:repaire table name to temporarily resolve the issue.

Xi. Duplicate key Name ' xxx '
Translation: Duplicate index name
Analysis: The index to be created already exists, this error is raised, and this error occurs more often in the upgrade. may have been upgraded, the error caused by the repeated upgrade. It is also possible that the previous user's unauthorized addition of the index, just as in the upgrade file so the same.
Workaround: Look at the existing index and whether the index to be added is the same, you can skip the SQL statement, if it is not the same then delete the existing one, then execute.

12. Duplicate column name ' xxx '
Translation: Field name XXX repeat
Analysis: Added field XXX already exists, mostly occurs during the upgrade process, and the production of issue 12 is the same.
Workaround: See if the field that already exists is exactly the same as the field property that will be added, and if it is the same, you can skip the execution of this SQL and delete the field if it is not. Then proceed with the upgrade process.

13, Table ' xxx ' already exists
Translation: Data table xxx already exists
Analysis: The XXX table already exists in the library, and again trying to create the name of the table will cause this error. The same goes for the upgrade of the forum. Similar to question 12.
Workaround: See if the table already exists is exactly the same as the table that will be created, and you can skip not executing the SQL, or delete the existing table before continuing with the upgrade file.

14, Can ' t create database ' xxx '. Database exists
Translation: Cannot create database XXX, database already exists
Analysis: A MySQL database name below must be unique, otherwise there will be this error.
FIX: Rename a database that already exists or rename the database that will be created, without having their name conflicting.

XV, summary (for problem 11\12\13\14\15)
This type of problem is hidden in the error message a keyword duplicate (repeat)
So what can't be duplicated for a MySQL database?
Database
Data tables under the same database table
Field column under the same data table
Index key under the same data table
These fields in the record cannot be duplicated in the same data table in the case of the index unique (unique PRIMARY)

16. Unknown system variable ' NAMES '
Translation: Unknown system variable names
Analysis: The MySQL version does not support character set setting, this error occurs when the character set is forced.
Workaround: Remove the set NAMES ' xxx ' statement from the SQL statement

17. Lost connection to MySQL server during query
Translation: MySQL server loses connection during query
Analysis: Remote connection to the database is sometimes problematic. The MySQL server lost its connection when executing an SQL statement.
Solution: Generally do not need how to deal with, if the frequent occurrence so 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 ' (max connections), currently set to 500
Analysis: There is a library under MySQL database for MySQL, which has a table for user the record in each of these corresponds to a MySQL user's authorization. Where the field max_questions max_updates max_connections records the maximum number of maximum queries for maximum number of updates, the error is generated when any one of the current parameters is greater than any one of the set values.
Workaround: The standalone host user can modify the authorization table directly. After modifying, restart MySQL or follow the new authorization form into the MySQL prompt
FLUSH privileges;
Remember to have a semicolon in the back '; '
If the user of the virtual host always appears this problem can find the space quotient to resolve.

19, Too many connections (1040) Too many links
Translation: Maximum number of connections reached
Problem Analysis:
The number of connections exceeds the MySQL setting value, which is related to Max_connections and wait_timeout. The larger the value of the wait_timeout, the longer the idle wait for the connection, which causes the current number of connections to become larger
Workaround:
1. Virtual host users contact the space provider to optimize the configuration of MySQL server;
2. Independent host users please contact the server administrator to optimize the configuration of the MySQL server, for reference:
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 regularly report this error, please do the overall optimization of the server.

20, there is no such grant defined for the user '%s ' on host '%s '
Error Number: 1141
Problem Analysis:
MySQL Current user does not have permission to access the database.
Workaround:
1, the virtual host users please contact the space quotient, to confirm that the account provided to you is authorized database permissions.
2, independent host users please contact the server administrator to confirm that 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:
Check to see if your program has a statement that modifies the database table name.
Workaround:
1. Please check where in your program you need to modify the database table name;
2. If your actual application does need to be modified to the database table name, contact the space provider or server administrator to open the permissions for you 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.
Workaround:
1. The virtual host user please contact the space provider to see if the database is intact.
2. Independent host users please contact the server administrator to check if MySQL itself is normal, MySQL can read files, Linux users can check the MySQL database file owner is correct and its own files are damaged.

23, Host ' * * * * * * is blocked because of many connection errors; Unblock with ' mysqladmin flush-hosts '
error.:1129
Problem Analysis:
The database has an exception, please restart the database.
Workaround:
1. Due to a lot of connection errors, host ' * * * * is blocked, virtual host users contact the space quotient processing, independent host users please contact the server administrator, under the command console of MySQL execute ' mysqladmin flush-hosts ' unblock, or restart MySQL Database

24, dropping database (can ' t delete '%s ', errno:%d)
error.:1009
Problem Analysis:
The database file could not be deleted, causing the database to fail.
Workaround:
1. Check if the database management account you are using has permission to delete data.
2. Check to see if the database exists.

25. Got Error from table handler
error.:1030
Problem Analysis:
The disk space on which the database resides is full.
Workaround:
1. The virtual host user contact the space quotient to increase the disk space where MySQL resides or to clean up some useless files;
2. Standalone host users please contact the server administrator to increase the disk space where MySQL resides or to clean up some useless files

26, Can ' t create a new thread; If you aren't out of available memory, you can consult the manual for a possible os-dependent bug.
Error.:11/35
Problem Analysis:
Database server problem, the database operation cannot create a new thread. There are generally two reasons:
1. Server system memory overflow.
2. Environmental software damage or system damage.
Workaround:
1. Virtual Host user please contact the space quotient database server memory and system is normal.
2. Independent host users please contact the server administrator to check the server's memory and system is normal, if the server 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 not support authentication protocol requested by server; Consider upgrading MySQL Client
error.:1251
Problem Analysis:
If you are experiencing the above problems after upgrading MySQL to 4.1 or above, please make sure that your MySQL client is 4.1 or higher (Windows has a problem and you just jump to the bottom to see the workaround, because MySQL in Windows is the Client and s Erver together).
Workaround:
1. Windows Platform
The main is to change the encryption of the account connected to MySQL, MySQL 4.1/5.0 is encrypted by PASSWORD this way. This can be resolved 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
Linux platform to first determine whether to install the MySQL client, this with RPM installation is 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 be solved under normal circumstances. If this error still occurs, you can do this in the following way:
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 generally occurs because of the following two reasons:
The 1.MySQL server is not turned on.
The 2.MySQL server is turned on, but the socket file cannot be found.
Workaround:
1. Virtual Host user, contact the space provider to confirm that the database is starting normally.
2. Independent host users, please check if the MySQL service is turned on, do not open, please start the MySQL service, if it is turned on, and is a Linux system, please check the path of the MySQL socket, and then open config.inc.php found
$dbhost = ' localhost '; After hostname, add the colon ': ' and the path to the MySQL socket.
For example, MySQL server is localhost
The path to the 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:
MySQL service does not start, generally in the case of abnormal MySQL can not boot, such as no disk space available, My.ini mysql basedir path settings error.
Workaround:
1. Check the disk space for any remaining free space and try to keep enough disk space available.
2. Check that the Basedir parameters in the My.ini are set correctly, and then restart the MySQL service.

30. Lost connection to MySQL server during query
error.:2013
Problem Analysis:
A connection to the MySQL server was lost during the database query.
Workaround:
1. Please ensure that your program is inefficient program, such as some plug-ins, you can uninstall the plugin, check the server is normal;
2. The server itself is resource-intensive, virtual host users please contact the space provider to confirm, independent host users please contact the server administrator, check whether the server is normal.

31, Got a packet bigger than \ ' max_allowed_packet\ ' bytes
Error Number: 1153
Problem Analysis: Adjusted the size of the Mantis upload attachment, but did not adjust the MySQL configuration file.
Workaround:
1, independent host users, please adjust the following methods:
Find the MySQL configuration file (my.cnf or My.ini)
Add a sentence in the [mysqld] section (if present, adjust its value):
max_allowed_packet=10m
Restarting the MySQL service is possible. This is set to 10MB.
2, the virtual Host user please contact the space quotient to adjust this parameter.

A summary of common error analysis and solution methods for MySQL transfer

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.