MySQL FAQ collection

First you should try to find out whether the mysqld daemon is dead or whether your problem is related to your customer. You can use Mysqladmin version to check how long your MYSQLD server has been performing properly, and if Mysqld is dead, you can find its cause in the file "mysql-data-directory/' hostname '. Err".
Some common errors when using MySQL

MySQL server has gone away

The common reason is that the server timed out and closed the connection. By default, if nothing happens, the server closes the connection after 8 hours. You can change the time limit by setting the Wait_timeout variable when you start Mysqld.

If the mysqld gets a packet that is too large or unhealthy, it thinks the customer is wrong and closes the connection.
Can ' t connect to [local] MySQL server

Usually means that no MySQL server is running on the system or when trying to connect to the MYSQLD server, you are using an incorrect socket file or TCP/IP port.

Check (using PS) if a process named Mysqld is started on the server

If a mysqld process is running, you can check the server by trying these different connections

Shell> mysqladmin Version

Shell> mysqladmin variables

shell> mysqladmin-h ' hostname ' version variables

shell> mysqladmin-h ' hostname ' –port=3306 version

shell> mysqladmin-h ' IP for your host ' version

Shell> Mysqladmin–socket=/tmp/mysql.sock Version

Note that the hostname command uses the anti-quote "'" instead of the positive quotation mark "'", which causes the hostname output (that is, the current hostname) to be substituted into the mysqladmin command.

Host ' ... ' is blocked error

Host ' hostname ' is blocked because of many connection errors.

Unblock with ' mysqladmin flush-hosts '

This means that mysqld has been given a large number (max_connect_errors) of host ' hostname ' in the middle of a connection request that was interrupted. After max_connect_errors a failed request, MYSQLD identified an error (like a hacker attack) and blocked further connections to the site until someone executed the command mysqladmin flush-hosts.

By default, mysqld blocks a host after 10 connection errors. You can easily adjust it by starting the server like this:

Shell> safe_mysqld-o max_connect_errors=10000 &

Too Many connections error

means that a max_connections client is already connected to the MYSQLD server.

If you need more connections than the default (100), then you should restart mysqld with a larger max_connections variable value.

Out of memory errors

Mysql:out of memory at line, ' MALLOC.C '

mysql:needed 8136 Byte (8k), Memory in use:12481367 bytes (12189k)

ERROR 2008:mysql client ran out of memory

Note that the error points to MySQL client MySQL. The reason for this error is simple, and the customer does not have enough memory to store all the results.

First check that your query is correct

Packet too large error

A MySQL client or mysqld server gets a packet longer than Max_allowed_packet bytes

You can start the client program by specifying a larger buffer with mysql–set-variable=max_allowed_packet=8m.
The table is full error

This error occurs when the memory temp table becomes larger than the tmp_table_size byte.
Commands out of the sync in client error

Calling client functions in the wrong order!
Ignoring user error

Found wrong password for user: '[email protected]_host '; Ignoring user

This means that when mysqld starts or when it loads the permission table again, it finds an entry in the user table that has an invalid password. As a result, entries are simply ignored by the permission system.

Table ' xxx ' doesn ' t exist error

Database and table name pieces are case-sensitive! You can use show tables to check which table you have in the current database.

Run SQL commands from a text file

You can put the SQL command in a file and tell MySQL to read its input from the file: Create a text file "Text_file" that contains the command to execute. Then call MySQL as follows:

shell> MySQL Database < text_file

Or

shell> MySQL < text_file

Launches a text file with the use DB_NAME statement.

How to reset a forgotten password

If you forget the password of the root user of MySQL, you can restore it using the following method:

Shut down the MYSQLD server by sending a Kill (not kill-9) to the MYSQLD server. The PID is stored in a. pid file, usually in the MySQL database directory:

Kill ' Cat/mysql-data-directory/hostname.pid '

You must be a UNIX root user or the same user who is running the server to do this.

Restart mysqld using the –skip-grant-tables option.

Connect the MYSQLD server with mysql-h hostname mysql and change the password with a grant command. See 7.26 Grant and REVOKE syntax. You can also use mysqladmin-h hostname-u user password ' new password '.

Load the permissions table with mysqladmin-h hostname flush-privileges or flush privileges with SQL commands.

Problems using the Date column

The format of the date value is ' Yyyy-mm-dd '.

Change the order of columns in a table

In an application, you should never retrieve columns based on their location using SELECT *, because the order of the columns returned is never guaranteed; a simple change to the database can cause the application to fail quite dramatically.

You can change it using the following methods:

Create a new table in the correct column order.

Execute INSERT INTO new_table SELECT Fields-in-new_table-order from Old_table.

Delete or rename old_table.

ALTER TABLE new_table RENAME old_table.

Database replication

MySQL (to date) does not have database replication, but has some information on how to implement it.

The most common way to replicate a database is to use the update log.

Database backup

To get a consistent backup, make a lock TABLES on the related table. You only need a read lock, and when you make a copy of the file in the database directory, this allows other threads to continue querying the table. If you want to make a SQL-level backup, you can select into OUTFILE.

Another way to back up a database is to use the Mysqldump program:

Make a full backup of your database:

Shell> Mysqldump–tab=/path/to/some/dir–opt–full
You can also simply copy all the table files ("*.frm", "*"). MYD "and" *. MYI "file) as long as the server is not updating anything.
Stop mysqld If it is running, and then start it with the –log-update option. You will get a log file called "HOSTNAME.N", where n is a number incremented each time you perform mysqladmin refresh or mysqladmin flush-logs, flush logs statements, or restart the server. These log files provide you with the information you need to change the replication database after you perform the mysqldump.

If you have to recover something, try to first restore your table with Myisamchk-r. This should handle 99.9% of all cases. If Myisamchk fails, try the following procedure:

Restore the original mysqldump backup.

Execute the following command to run the changes in the update log again:

shell> ls-1-t-r hostname. [0-9]* | Xargs Cat | Mysql
LS is used to get all the log files in the correct order.
You can also make a selective backup with the SELECT * into OUTFILE ' file_name ' from tbl_name and use the load DATA INFILE ' file_name ' REPLACE ... Recovery. In order to avoid duplicate records, you need a primary key or unique key in the table. When a new record is duplicated on a unique key value with an old record, the Replace keyword causes the old record to be replaced with a new record.
{Mosliteadsense4joomla ad_align= "center"} Author: Tom
Topic: MySQL FAQs Collection
Time: 2006-9-13 Wednesday, PM 11:08 (GMT

MySQL always crashes

First you should try to find out whether the mysqld daemon is dead or whether your problem is related to your customer. You can use Mysqladmin version to check how long your MYSQLD server has been performing properly, and if Mysqld is dead, you can find its cause in the file "mysql-data-directory/' hostname '. Err".
Some common errors when using MySQL

MySQL server has gone away

The common reason is that the server timed out and closed the connection. By default, if nothing happens, the server closes the connection after 8 hours. You can change the time limit by setting the Wait_timeout variable when you start Mysqld.

If the mysqld gets a packet that is too large or unhealthy, it thinks the customer is wrong and closes the connection.
Can ' t connect to [local] MySQL server

Usually means that no MySQL server is running on the system or when trying to connect to the MYSQLD server, you are using an incorrect socket file or TCP/IP port.

Check (using PS) if a process named Mysqld is started on the server

If a mysqld process is running, you can check the server by trying these different connections

Shell> mysqladmin Version

Shell> mysqladmin variables

shell> mysqladmin-h ' hostname ' version variables

shell> mysqladmin-h ' hostname ' –port=3306 version

shell> mysqladmin-h ' IP for your host ' version

Shell> Mysqladmin–socket=/tmp/mysql.sock Version

Note that the hostname command uses the anti-quote "'" instead of the positive quotation mark "'", which causes the hostname output (that is, the current hostname) to be substituted into the mysqladmin command.

Host ' ... ' is blocked error

Host ' hostname ' is blocked because of many connection errors.

Unblock with ' mysqladmin flush-hosts '

This means that mysqld has been given a large number (max_connect_errors) of host ' hostname ' in the middle of a connection request that was interrupted. After max_connect_errors a failed request, MYSQLD identified an error (like a hacker attack) and blocked further connections to the site until someone executed the command mysqladmin flush-hosts.

By default, mysqld blocks a host after 10 connection errors. You can easily adjust it by starting the server like this:

Shell> safe_mysqld-o max_connect_errors=10000 &

Too Many connections error

means that a max_connections client is already connected to the MYSQLD server.

If you need more connections than the default (100), then you should restart mysqld with a larger max_connections variable value.

Out of memory errors

Mysql:out of memory at line, ' MALLOC.C '

mysql:needed 8136 Byte (8k), Memory in use:12481367 bytes (12189k)

ERROR 2008:mysql client ran out of memory

Note that the error points to MySQL client MySQL. The reason for this error is simple, and the customer does not have enough memory to store all the results.

First check that your query is correct

Packet too large error

A MySQL client or mysqld server gets a packet longer than Max_allowed_packet bytes

You can start the client program by specifying a larger buffer with mysql–set-variable=max_allowed_packet=8m.
The table is full error

This error occurs when the memory temp table becomes larger than the tmp_table_size byte.
Commands out of the sync in client error

Calling client functions in the wrong order!
Ignoring user error

Found wrong password for user: '[email protected]_host '; Ignoring user

This means that when mysqld starts or when it loads the permission table again, it finds an entry in the user table that has an invalid password. As a result, entries are simply ignored by the permission system.

Table ' xxx ' doesn ' t exist error

Database and table name pieces are case-sensitive! You can use show tables to check which table you have in the current database.

Run SQL commands from a text file

You can put the SQL command in a file and tell MySQL to read its input from the file: Create a text file "Text_file" that contains the command to execute. Then call MySQL as follows:

shell> MySQL Database < text_file

Or

shell> MySQL < text_file

Launches a text file with the use DB_NAME statement.

How to reset a forgotten password

If you forget the password of the root user of MySQL, you can restore it using the following method:

Shut down the MYSQLD server by sending a Kill (not kill-9) to the MYSQLD server. The PID is stored in a. pid file, usually in the MySQL database directory:

Kill ' Cat/mysql-data-directory/hostname.pid '

You must be a UNIX root user or the same user who is running the server to do this.

Restart mysqld using the –skip-grant-tables option.

Connect the MYSQLD server with mysql-h hostname mysql and change the password with a grant command. See 7.26 Grant and REVOKE syntax. You can also use mysqladmin-h hostname-u user password ' new password '.

Load the permissions table with mysqladmin-h hostname flush-privileges or flush privileges with SQL commands.

Problems using the Date column

The format of the date value is ' Yyyy-mm-dd '.

Change the order of columns in a table

In an application, you should never retrieve columns based on their location using SELECT *, because the order of the columns returned is never guaranteed; a simple change to the database can cause the application to fail quite dramatically.

You can change it using the following methods:

Create a new table in the correct column order.

Execute INSERT INTO new_table SELECT Fields-in-new_table-order from Old_table.

Delete or rename old_table.

ALTER TABLE new_table RENAME old_table.

Database replication

MySQL (to date) does not have database replication, but has some information on how to implement it.

The most common way to replicate a database is to use the update log.

Database backup

To get a consistent backup, make a lock TABLES on the related table. You only need a read lock, and when you make a copy of the file in the database directory, this allows other threads to continue querying the table. If you want to make a SQL-level backup, you can select into OUTFILE.

Another way to back up a database is to use the Mysqldump program:

Make a full backup of your database:

Shell> Mysqldump–tab=/path/to/some/dir–opt–full
You can also simply copy all the table files ("*.frm", "*"). MYD "and" *. MYI "file) as long as the server is not updating anything.
Stop mysqld If it is running, and then start it with the –log-update option. You will get a log file called "HOSTNAME.N", where n is a number incremented each time you perform mysqladmin refresh or mysqladmin flush-logs, flush logs statements, or restart the server. These log files provide you with the information you need to change the replication database after you perform the mysqldump.

If you have to recover something, try to first restore your table with Myisamchk-r. This should handle 99.9% of all cases. If Myisamchk fails, try the following procedure:

Restore the original mysqldump backup.

Execute the following command to run the changes in the update log again:

shell> ls-1-t-r hostname. [0-9]* | Xargs Cat | Mysql
LS is used to get all the log files in the correct order.
You can also make a selective backup with the SELECT * into OUTFILE ' file_name ' from tbl_name and use the load DATA INFILE ' file_name ' REPLACE ... Recovery. In order to avoid duplicate records, you need a primary key or unique key in the table. When a new record is duplicated on a unique key value with an old record, the Replace keyword causes the old record to be replaced with a new record.

MySQL FAQ collection