13.5. Database Administration Statements

MySQL 5.0

previous page next page

13.5. Database Administration Statements

13.5.1. Account Management Statements

MySQL account information is stored in the tables of the database. This database and the access control system are discussed extensively in Chapter 5, Database Administration, which you should consult for additional details.

Important: Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

13.5.1.1.  Syntax

CREATE USER  [IDENTIFIED BY [PASSWORD] '']
    [,  [IDENTIFIED BY [PASSWORD] '']] ...

The statement was added in MySQL 5.0.2. This statement creates new MySQL accounts. To use it, you must have the global privilege or the privilege for the database. For each account, creates a new record in the table that has no privileges. An error occurs if the account already exists. Each account is named using the same format as for the statement; for example, . The user and host parts of the account name correspond to the and column values of the table row for the account.

The account can be given a password with the optional clause. The value and the password are given the same way as for the statement. In particular, to specify the password in plain text, omit the keyword. To specify the password as the hashed value as returned by the function, include the keyword. See Section 13.5.1.3, “ Syntax”.

13.5.1.2.  Syntax

DROP USER  [, ] ...

The statement removes one or more MySQL accounts. To use it, you must have the global privilege or the privilege for the database. Each account is named using the same format as for the statement; for example, . The user and host parts of the account name correspond to the and column values of the table row for the account.

as present in MySQL 5.0.0 removes only accounts that have no privileges. In MySQL 5.0.2, it was modified to remove account privileges as well. This means that the procedure for removing an account depends on your version of MySQL.

As of MySQL 5.0.2, you can remove an account and its privileges as follows: 

DROP USER ;

The statement removes privilege rows for the account from all grant tables.

In MySQL 5.0.0 and 5.0.1, deletes only MySQL accounts that have no privileges. In these MySQL versions, it serves only to remove each account record from the table. To remove a MySQL account completely (including all of its privileges), you should use the following procedure, performing these steps in the order shown:

  1. Use to determine what privileges the account has. See Section 13.5.4.12, “ Syntax”.

  2. Use to revoke the privileges displayed by . This removes rows for the account from all the grant tables except the table, and revokes any global privileges listed in the table. See Section 13.5.1.3, “ Syntax”.

  3. Delete the account by using to remove the table record.

Important: does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed. Once the session is closed, the user is dropped, and that user's next attempt to log in will fail. This is by design.

13.5.1.3.  Syntax

GRANT  [()] [,  [()]] ...
    ON [] { | * | *.* | .*}
    TO  [IDENTIFIED BY [PASSWORD] '']
        [,  [IDENTIFIED BY [PASSWORD] '']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER '' [AND]]
        [ISSUER '' [AND]]
        [SUBJECT '']]
    [WITH  [] ...]

 =
    TABLE
  | FUNCTION
  | PROCEDURE

 =
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR 
  | MAX_UPDATES_PER_HOUR 
  | MAX_CONNECTIONS_PER_HOUR 
  | MAX_USER_CONNECTIONS

The statement enables system administrators to create MySQL user accounts and to grant rights to from accounts. To use , you must have the privilege, and you must have the privileges that you are granting. The statement is related and enables administrators to remove account privileges. See Section 13.5.1.5, “ Syntax”.

MySQL account information is stored in the tables of the database. This database and the access control system are discussed extensively in Chapter 5, Database Administration, which you should consult for additional details.

Important: Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features. Whenever you update to a new version of MySQL, you should update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

If the grant tables hold privilege rows that contain mixed-case database or table names and the system variable is set to a non-zero value, cannot be used to revoke these privileges. It will be necessary to manipulate the grant tables directly. ( will not create such rows when is set, but such rows might have been created prior to setting the variable.)

Privileges can be granted at several levels:

  • Global level

    Global privileges apply to all databases on a given server. These privileges are stored in the table. and grant and revoke only global privileges.

  • Database level

    Database privileges apply to all objects in a given database. These privileges are stored in the and tables. .* and .* grant and revoke only database privileges.

  • Table level

    Table privileges apply to all columns in a given table. These privileges are stored in the table. and grant and revoke only table privileges.

  • Column level

    Column privileges apply to single columns in a given table. These privileges are stored in the table. When using , you must specify the same columns that were granted.

  • Routine level

    The , , , and privileges apply to stored routines (functions and procedures). They can be granted at the global and database levels. Also, except for , these privileges can be granted at the routine level for individual routines and are stored in the table.

The clause was added in MySQL 5.0.6. It should be specified as , , or when the following object is a table, a stored function, or a stored procedure.

For the and statements, can be specified as any of the following:

Privilege Meaning
Sets all simple privileges except
Enables use of
Enables stored routines to be altered or dropped
Enables use of
Enables creation of stored routines
Enables use of
Enables use of , , , and .
Enables use of
Enables use of
Enables use of
Enables the user to run stored routines
Enables use of and
Enables use of and
Enables use of
Enables use of on tables for which you have the privilege
Enables use of
Not implemented
Enables use of
Enables the user to ask where slave or master servers are
Needed for replication slaves (to read binary log events from the master)
Enables use of
shows all databases
Enables use of
Enables use of mysqladmin shutdown
Enables use of , , , and statements, the mysqladmin debug command; allows you to connect (once) even if is reached
Enables use of
Synonym for “no privileges”
Enables privileges to be granted

The privilege is not operational until MySQL 5.0.3. and were added in MySQL 5.0.1. , , and were added in MySQL 5.0.3.

The privilege currently is unused.

can be specified when you want to create a user that has no privileges.

Use to determine what privileges an account has. See Section 13.5.4.12, “ Syntax”.

You can assign global privileges by using syntax or database-level privileges by using .* syntax. If you specify and you have selected a default database, the privileges are granted in that database. (Warning: If you specify and you have not selected a default database, the privileges granted are global.)

The , , , , , , , and privileges are administrative privileges that can only be granted globally (using syntax).

Other privileges can be granted globally or at more specific levels.

The values that you can specify for a table are , , , , , , , , , and .

The values that you can specify for a column (that is, when you use a clause) are , , and .

The values that you can specify at the routine level are , , and . is not a routine-level privilege because you must have this privilege to create a routine in the first place.

For the global, database, table, and routine levels, assigns only the privileges that exist at the level you are granting. For example, .* is a database-level statement, so it does not grant any global-only privileges such as .

MySQL allows you to grant privileges even on database objects that do not exist. In such cases, the privileges to be granted must include the privilege. This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for database objects that are to be created at a later time.

Important: MySQL does not automatically revoke any privileges when you drop a table or database. However, if you drop a routine, any routine-level privileges granted for that routine are revoked.

Note: the ‘’ and ‘’ wildcards are allowed when specifying database names in statements that grant privileges at the global or database levels. This means, for example, that if you want to use a ‘’ character as part of a database name, you should specify it as ‘’ in the statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, .

To accommodate granting rights to users from arbitrary hosts, MySQL supports specifying the value in the form @. If a or value is legal as an unquoted identifier, you need not quote it. However, quotes are necessary to specify a string containing special characters (such as ‘’), or a string containing special characters or wildcard characters (such as ‘’); for example, . Quote the username and hostname separately.

You can specify wildcards in the hostname. For example, @'%.loc.gov' applies to for any host in the domain, and @'144.155.166.%' applies to for any host in the class C subnet.

The simple form is a synonym for @'%'.

MySQL does not support wildcards in usernames. Anonymous users are defined by inserting entries with into the table or by creating a user with an empty name with the statement: 

GRANT ALL ON test.* TO ''@'localhost' ...

When specifying quoted values, quote database, table, column, and routine names as identifiers, using backticks (‘’). Quote hostnames, usernames, and passwords as strings, using single quotes (‘’).

Warning: If you allow anonymous users to connect to the MySQL server, you should also grant privileges to all local users as @localhost. Otherwise, the anonymous user account for in the table (created during MySQL installation) is used when named users try to log in to the MySQL server from the local machine. For details, see Section 5.8.5, “Access Control, Stage 1: Connection Verification”.

You can determine whether this applies to you by executing the following query, which lists any anonymous users: 

SELECT Host, User FROM mysql.user WHERE User='';

If you want to delete the local anonymous user account to avoid the problem just described, use these statements: 

DELETE FROM mysql.user WHERE Host='localhost' AND User='';
FLUSH PRIVILEGES;

supports hostnames up to 60 characters long. Database, table, column, and routine names can be up to 64 characters. Usernames can be up to 16 characters. Note: The allowable length for usernames cannot be changed by altering the table, and attempting to do so results in unpredictable behavior which may even make it impossible for users to log in to the MySQL server. You should never alter any of the tables in the database in any manner whatsoever except by means of the procedure prescribed by MySQL AB that is described in Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

The privileges for a table, column, or routine are formed additively as the logical of the privileges at each of the privilege levels. For example, if the table specifies that a user has a global privilege, the privilege cannot be denied by an entry at the database, table, or column level.

The privileges for a column can be calculated as follows: 

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges

In most cases, you grant rights to a user at only one of the privilege levels, so life is not normally this complicated. The details of the privilege-checking procedure are presented in Section 5.8, “The MySQL Access Privilege System”.

If you grant privileges for a username/hostname combination that does not exist in the table, an entry is added and remains there until deleted with a statement. In other words, may create table entries, but does not remove them; you must do that explicitly using or .

Warning: If you create a new user but do not specify an clause, the user has no password. This is very insecure. As of MySQL 5.0.2, you can enable the SQL mode to prevent from creating a new user if it would otherwise do so, unless is given to provide the new user a non-empty password.

If a new user is created or if you have global grant privileges, the user's password is set to the password specified by the clause, if one is given. If the user already had a password, this is replaced by the new one.

Passwords can also be set with the statement. See Section 13.5.1.6, “ Syntax”.

In the clause, the password should be given as the literal password value. It is unnecessary to use the function as it is for the statement. For example: 

GRANT ... IDENTIFIED BY 'mypass';

If you do not want to send the password in clear text and you know the hashed value that would return for the password, you can specify the hashed value preceded by the keyword : 

GRANT ...
IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

In a C program, you can get the hashed value by using the C API function.

If you grant privileges for a database, an entry in the table is created if needed. If all privileges for the database are removed with , this entry is deleted.

The privilege enables the account to see database names by issuing the statement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the option.

If a user has no privileges for a table, the table name is not displayed when the user requests a list of tables (for example, with a statement).

The clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. You should be careful to whom you give the privilege, because two users with different privileges may be able to join privileges!

You cannot grant another user a privilege which you yourself do not have; the privilege enables you to assign only those privileges which you yourself possess.

Be aware that when you grant a user the privilege at a particular privilege level, any privileges the user possesses (or may be given in the future) at that level can also be granted by that user to other users. Suppose that you grant a user the privilege on a database. If you then grant the privilege on the database and specify , that user can give to other users not only the privilege, but also . If you then grant the privilege to the user on the database, the user can grant , , and .

For a non-administrative user, you should not grant the privilege globally or for the database. If you do that, the user can try to subvert the privilege system by renaming tables!

The , , and options limit the number of queries, updates, and logins a user can perform during any given one-hour period. If is (the default), this means that there is no limitation for that user.

The option, implemented in MySQL 5.0.3, limits the maximum number of simultaneous connections that the account can make. If is (the default), the system variable determines the number of simultaneous connections for the account.

Note: To specify any of these resource-limit options for an existing user without affecting existing privileges, use .

See Section 5.9.4, “Limiting Account Resources”.

MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the username and password. To specify SSL-related options for a MySQL account, use the clause of the statement. (For background information on the use of SSL with MySQL, see Section 5.9.7, “Using Secure Connections”.)

There are a number of different possibilities for limiting connection types for a given account:

  • If the account has no SSL or X509 requirements, unencrypted connections are allowed if the username and password are valid. However, encrypted connections can also be used, at the client's option, if the client has the proper certificate and key files.

  • The option tells the server to allow only SSL-encrypted connections for the account. Note that this option can be omitted if there are any access-control rows that allow non-SSL connections. 

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret' REQUIRE SSL;

  • means that the client must have a valid certificate but that the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates. 

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret' REQUIRE X509;

  • ' places the restriction on connection attempts that the client must present a valid X509 certificate issued by CA '. If the client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X509 certificates always implies encryption, so the option is unnecessary in this case. 

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/
    O=MySQL Finland AB/CN=Tonu Samuel/[email protected]';

    Note that the ' value should be entered as a single string.

  • ' places the restriction on connection attempts that the client must present a valid X509 certificate containing the subject . If the client presents a certificate that is valid but has a different subject, the server rejects the connection. 

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/[email protected]';

    Note that the ' value should be entered as a single string.

  • ' is needed to ensure that ciphers and key lengths of sufficient strength are used. SSL itself can be weak if old algorithms using short encryption keys are used. Using this option, you can ask that a specific cipher method is used to allow a connection. 

    GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

The , , and options can be combined in the clause like this: 

GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
  IDENTIFIED BY 'goodsecret'
  REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
    O=MySQL demo client certificate/
    CN=Tonu Samuel/[email protected]'
  AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
    O=MySQL Finland AB/CN=Tonu Samuel/[email protected]'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

The keyword is optional between options.

The order of the options does not matter, but no option can be specified twice.

When mysqld starts, all privileges are read into memory. For details, see Section 5.8.7, “When Privilege Changes Take Effect”.

Note that if you are using table, column, or routine privileges for even one user, the server examines table, column, and routine privileges for all users and this slows down MySQL a bit. Similarly, if you limit the number of queries, updates, or connections for any users, the server must monitor these values.

The biggest differences between the standard SQL and MySQL versions of are:

  • In MySQL, privileges are associated with the combination of a hostname and username and not with only a username.

  • Standard SQL does not have global or database-level privileges, nor does it support all the privilege types that MySQL supports.

  • MySQL does not support the standard SQL or privileges.

  • Standard SQL privileges are structured in a hierarchical manner. If you remove a user, all privileges the user has been granted are revoked. This is also true in MySQL 5.0.2 and up if you use . Before 5.0.2, the granted privileges are not automatically revoked; you must revoke them yourself. See Section 13.5.1.2, “ Syntax”.

  • In standard SQL, when you drop a table, all privileges for the table are revoked. In standard SQL, when you revoke a privilege, all privileges that were granted based on that privilege are also revoked. In MySQL, privileges can be dropped only with explicit statements or by manipulating values stored in the MySQL grant tables.

  • In MySQL, it is possible to have the privilege for only some of the columns in a table. In this case, you can still execute statements on the table, provided that you omit those columns for which you do not have the privilege. The omitted columns are set to their implicit default values if strict SQL mode is not enabled. In strict mode, the statement is rejected if any of the omitted columns have no default value. (Standard SQL requires you to have the privilege on all columns.) Section 5.2.5, “The Server SQL Mode”, discusses strict mode. Section 11.1.4, “Data Type Default Values”, discusses implicit default values.

13.5.1.4.  Syntax

RENAME USER  TO 
    [,  TO ] ...

The statement renames existing MySQL accounts. To use it, you must have the global privilege or the privilege for the database. An error occurs if any old account does not exist or any new account exists. Each account is named using the same format as for the statement; for example, . The user and host parts of the account name correspond to the and column values of the table row for the account.

The statement was added in MySQL 5.0.2.

13.5.1.5.  Syntax

REVOKE  [()] [,  [()]] ...
    ON [] { | * | *.* | .*}
    FROM  [, ] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM  [, ] ...

The statement enables system administrators to revoke privileges from MySQL accounts. To use , you must have the privilege, and you must have the privileges that you are revoking.

For details on the levels at which privileges exist, the allowable values, and the syntax for specifying users and passwords, see Section 13.5.1.3, “ Syntax”

If the grant tables hold privilege rows that contain mixed-case database or table names and the system variable is set to a non-zero value, cannot be used to revoke these privileges. It will be necessary to manipulate the grant tables directly. ( will not create such rows when is set, but such rows might have been created prior to setting the variable.)

To revoke all privileges, use the following syntax, which drops all global, database-, table-, and column-level privileges for the named user or users: 

REVOKE ALL PRIVILEGES, GRANT OPTION FROM  [, ] ...

To use this syntax, you must have the global privilege or the privilege for the database.

13.5.1.6.  Syntax

SET PASSWORD [FOR ] = PASSWORD('')

The statement assigns a password to an existing MySQL user account.

With no clause, this statement sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.

With a clause, this statement sets the password for a specific account on the current server host. Only clients that have the privilege for the database can do this. The value should be given in @ format, where and are exactly as they are listed in the and columns of the table entry. For example, if you had an entry with and column values of and , you would write the statement like this: 

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('');

That is equivalent to the following statements: 

UPDATE mysql.user SET Password=PASSWORD('')
  WHERE User='bob' AND Host='%.loc.gov';
FLUSH PRIVILEGES;

Note: If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not use the preceding or statement without reading Section 5.8.9, “Password Hashing as of MySQL 4.1”, first. The password format changed in MySQL 4.1, and under certain circumstances it is possible that if you change your password, you might not be able to connect to the server afterward.

You can see which account the server authenticated you as by executing .

13.5.2. Table Maintenance Statements

13.5.2.1.  Syntax

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE  [, ] ...

analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for and . For the table is locked with a write lock. This statement works with , , and tables. For tables, this statement is equivalent to using myisamchk --analyze.

For more information on how the analysis works within, see Section 14.2.16, “Restrictions on Tables”.

MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant.

This statement requires and privileges for the table.

returns a result set with the following columns:

Column Value
The table name
Always
One of , , , or
The message

You can check the stored key distribution with the statement. See Section 13.5.4.13, “ Syntax”.

If the table has not changed since the last statement, the table is not analyzed again.

statements are written to the binary log unless the optional keyword (or its alias ) is used. This is done so that statements used on a MySQL server acting as a replication master will be replicated by default to the replication slave.

13.5.2.2.  Syntax

BACKUP TABLE  [, ] ... TO ''

Note: This statement is deprecated. We are working on a better replacement for it that will provide online backup capabilities. In the meantime, the mysqlhotcopy script can be used instead.

copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. The statement works only for tables. It copies the definition and data files. The index file can be rebuilt from those two files. The directory should be specified as a full pathname. To restore the table, use .

During the backup, a read lock is held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot (preventing any of them from being changed during the backup operation), issue a statement first, to obtain a read lock for all tables in the group.

returns a result set with the following columns:

Column Value
The table name
Always
One of , , , or
The message

13.5.2.3.  Syntax

CHECK TABLE  [, ] ... [] ...

 = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

checks a table or tables for errors. works for , , and (as of MySQL 5.0.16) tables. For tables, the key statistics are updated as well.

As of MySQL 5.0.2, can also check views for problems, such as tables that are referenced in the view definition that no longer exist.

returns a result set with the following columns:

Column Value
The table name
Always
One of , , , or
The message

Note that the statement might produce many rows of information for each checked table. The last row has a value of and the normally should be . If you don't get , or you should normally run a repair of the table. See Section 5.10.4, “Table Maintenance and Crash Recovery”. means that the storage engine for the table indicated that there was no need to check the table.

The option checks whether the named tables are compatible with the current version of MySQL. This option was added in MySQL 5.0.19. With , the server checks each table to determine whether there have been any incompatible changes in any of the table's data types or indexes since the table was created. If not, the check succeeds. Otherwise, if there is a possible incompatibility, the server runs a full check on the table (which might take some time). If the full check succeeds, the server marks the table's file with the current MySQL version number. Marking the file ensures that further checks for the table with the same version of the server will be fast.

Incompatibilities might occur because the storage format for a data type has changed or because its sort order has changed. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.

Currently, discovers these incompatibilities:

  • The indexing order for end-space in columns for and tables changed between MySQL 4.1 and 5.0.

  • The storage method of the new data type changed between MySQL 5.0.3 and 5.0.5.

The other check options that can be given are shown in the following table. These options apply only to checking tables and are ignored for tables and views.

Type Meaning
Do not scan the rows to check for incorrect links.
Check only tables that have not been closed properly.
Check only tables that have been changed since the last check or that have not been closed properly.
Scan rows to verify that deleted links are valid. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.
Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but takes a long time.

If none of the options , , or are specified, the default check type for dynamic-format tables is . This has the same result as running myisamchk --medium-check on the table. The default check type also is for static-format tables, unless or is specified. In that case, the default is . The row scan is skipped for and because the rows are very seldom corrupted.

You can combine check options, as in the following example that does a quick check on the table to determine whether it was closed properly: 

CHECK TABLE test_table FAST QUICK;

Note: In some cases, changes the table. This happens if the table is marked as “corrupted” or “not closed properly” but does not find any problems in the table. In this case, marks the table as okay.

If a table is corrupted, it is most likely that the problem is in the indexes and not in the data part. All of the preceding check types check the indexes thoroughly and should thus find most errors.

If you just want to check a table that you assume is okay, you should use no check options or the option. The latter should be used when you are in a hurry and can take the very small risk that does not find an error in the data file. (In most cases, under normal usage, MySQL should find any error in the data file. If this happens, the table is marked as “corrupted” and cannot be used until it is repaired.)

and are mostly intended to be used from a script (for example, to be executed from cron) if you want to check tables from time to time. In most cases, is to be preferred over . (The only case when it is not preferred is when you suspect that you have found a bug in the code.)

is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

Some problems reported by cannot be corrected automatically:

  • .

    This means that you have a row in the table where the index column contains the value 0. (It is possible to create a row where the column is 0 by explicitly setting the column to 0 with an statement.)

    This is not an error in itself, but could cause trouble if you decide to dump the table and restore it or do an on the table. In this case, the column changes value according to the rules of columns, which could cause problems such as a duplicate-key error.

    To get rid of the warning, simply execute an statement to set the column to some value other than 0.

13.5.2.4.  Syntax

CHECKSUM TABLE  [, ] ... [ QUICK | EXTENDED ]

reports a table checksum.

With , the live table checksum is reported if it is available, or otherwise. This is very fast. A live checksum is enabled by specifying the table option when you create the table; currently, this is supported only for tables. See Section 13.1.5, “ Syntax”.

With , the entire table is read row by row and the checksum is calculated. This can be very slow for large tables.

If neither nor is specified, MySQL returns a live checksum if the table storage engine supports it and scans the table otherwise.

For a non-existent table, returns and, as of MySQL 5.0.3, generates a warning.

13.5.2.5.  Syntax

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE  [, ] ...

should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have , , , or columns). Deleted rows are maintained in a linked list and subsequent operations reuse old row positions. You can use to reclaim the unused space and to defragment the data file.

This statement requires and privileges for the table.

In most setups, you need not run at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables.

works only for , , and tables.

For tables, works as follows:

  1. If the table has deleted or split rows, repair the table.

  2. If the index pages are not sorted, sort them.

  3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

For tables, currently is mapped to . See Section 13.5.2.1, “ Syntax”.

For tables, is mapped to , which rebuilds the table to update index statistics and free unused space in the clustered index.

You can make work on other storage engines by starting mysqld with the or option. In this case, is just mapped to .

returns a result set with the following columns:

Column Value
The table name
Always
One of , , , or
The message

Note that MySQL locks the table during the time is running.

statements are written to the binary log unless the optional keyword(or its alias ) is used. This is done so that statements used on a MySQL server acting as a replication master will be replicated by default to the replication slave.

13.5.2.6.  Syntax

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE
     [, ] ... [QUICK] [EXTENDED] [USE_FRM]

repairs a possibly corrupted table. By default, it has the same effect as myisamchk --recover . works for and for tables. See Section 14.1, “The Storage Engine”, and Section 14.8, “The Storage Engine”.

This statement requires and privileges for the table.

Normally, you should never have to run this statement. However, if disaster strikes, is very likely to get back all your data from a table. If your tables become corrupted often, you should try to find the reason for it, to eliminate the need to use . See Section A.4.2, “What to Do If MySQL Keeps Crashing”, and Section 14.1.4, “ Table Problems”.

Warning: If the server dies during a operation, it is essential after restarting it that you immediately execute another statement for the table before performing any other operations on it. (It is always a good idea to start by making a backup.) In the worst case, you might have a new clean index file without information about the data file, and then the next operation you perform could overwrite the data file. This is an unlikely but possible scenario.

returns a result set with the following columns:

Column Value
The table name
Always
One of , , , or
The message

The statement might produce many rows of information for each repaired table. The last row has a value of and normally should be . If you do not get , you should try repairing the table with myisamchk --safe-recover. ( does not yet implement all the options of myisamchk.) With myisamchk --safe-recover, you can also use options that does not support, such as .

If is given, tries to repair only the index tree. This type of repair is like that done by myisamchk --recover --quick.

If you use , MySQL creates the index row by row instead of creating one index at a time with sorting. This type of repair is like that done by myisamchk --safe-recover.

There is also a mode available for . Use this if the index file is missing or if its header is corrupted. In this mode, MySQL re-creates the file using information from the file. This kind of repair cannot be done with myisamchk. Note: Use this mode only if you cannot use regular modes. The header contains important table metadata (in particular, current value and ) that are lost in . Don't use if the table is compressed because this information is also stored in the file.

statements are written to the binary log unless the optional keyword (or its alias ) is used. This is done so that statements used on a MySQL server acting as a replication master will be replicated by default to the replication slave.

13.5.2.7.  Syntax

RESTORE TABLE  [, ] ... FROM ''

restores the table or tables from a backup that was made with . Existing tables are not overwritten; if you try to restore over an existing table, an error occurs. Just as for , currently works only for tables. The directory should be specified as a full pathname.

The backup for each table consists of its format file and data file. The restore operation restores those files, and then uses them to rebuild the index file. Restoring takes longer than backing up due to the need to rebuild the indexes. The more indexes the table has, the longer it takes.

returns a result set with the following columns:

Column Value
The table name
Always
One of , , , or
The message

13.5.3. SET Syntax

SET  [, ] ...

:
       = 
    | [GLOBAL | SESSION]  = 
    | [@@global. | @@session. | @@] =

The statement assigns values to different types of variables that affect the operation of the server or your client. Older versions of MySQL employed , but this syntax is deprecated in favor of without .

This section describes use of for assigning values to system variables or user variables. For general information about these types of variables, see Section 5.2.2, “Server System Variables”, and Section 9.3, “User-Defined Variables”. System variables also can be set at server startup, as described in Section 5.2.3, “Using System Variables”.

Some variants of syntax are used in other contexts:

The following discussion shows the different syntaxes that you can use to set variables. The examples use the assignment operator, but the operator also is allowable.

A user variable is written as and can be set as follows: 

SET @ = ;

Many system variables are dynamic and can be changed while the server runs by using the statement. For a list, see Section 5.2.3.2, “Dynamic System Variables”. To change a system variable with , refer to it as , optionally preceded by a modifier:

  • To indicate explicitly that a variable is a global variable, precede its name by or . The privilege is required to set global variables.

  • To indicate explicitly that a variable is a session variable, precede its name by , , or . Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client.

  • and are synonyms for and .

  • If no modifier is present, changes the session variable.

A statement can contain multiple variable assignments, separated by commas. If you set several system variables, the most recent or modifier in the statement is used for following variables that have no modifier specified.

Examples: 

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

When you assign a value to a system variable with , you cannot use suffix letters in the value (as can be done with startup options). However, the value can take the form of an expression: 

SET sort_buffer_size = 10 * 1024 * 1024;

The syntax for system variables is supported for compatibility with some other database systems.

If you change a session system variable, the value remains in effect until your session ends or until you change the variable to a different value. The change is not visible to other clients.

If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the statement).

To prevent incorrect usage, MySQL produces an error if you use with a variable that can only be used with or if you do not specify (or ) when setting a global variable.

To set a variable to the value or a value to the compiled-in MySQL default value, use the keyword. For example, the following two statements are identical in setting the session value of to the global value: 

SET max_join_size=DEFAULT;
SET @@session.max_join_size=@@global.max_join_size;

Not all system variables can be set to . In such cases, use of results in an error.

You can refer to the values of specific global or sesson system variables in expressions by using one of the -modifiers. For example, you can retrieve values in a statement like this: 

SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

When you refer to a system variable in an expression as (that is, when you do not specify or ), MySQL returns the session value if it exists and the global value otherwise. (This differs from = , which always refers to the session value.)

To display system variables names and values, use the statement. (See Section 13.5.4.24, “ Syntax”.)

The following list describes options that have non-standard syntax or that are not described in the list of system variables found in Section 5.2.2, “Server System Variables”. Although the options described here are not displayed by , you can obtain their values with (with the exception of and ). For example: 

mysql> 
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

The lettercase of thse options does not matter.

  • Set the autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0 you have to use to accept a transaction or to cancel it. By default, client connections begin with set to 1. If you change mode from 0 to 1, MySQL performs an automatic of any open transaction. Another way to begin a transaction is to use a or statement. See Section 13.4.1, “, , and Syntax”.

  • If set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower, but the error is full does not occur for operations that require a large temporary table. The default value for a new connection is 0 (use in-memory temporary tables). Normally, you should never need to set this variable, because in-memory tables are automatically converted to disk-based tables as required. (Note: This variable was formerly named .)

  • | DEFAULT}

    This maps all strings from and to the client with the given mapping. You can add new mappings by editing in the MySQL source distribution. sets three session system variables: and are set to the given character set, and to the value of . See Section 10.4, “Connection Character Sets and Collations”.

    The default mapping can be restored by using the value . The default depends on the server configuration.

    Note that the syntax for differs from that for setting most other options.

  • If set to 1 (the default), foreign key constraints for tables are checked. If set to 0, they are ignored. Disabling foreign key checking can be useful for reloading tables in an order different from that required by their parent/child relationships. See Section 14.2.6.4, “ Constraints”.

  • This variable is a synonym for the variable. It exists for compatibility with other database systems. You can read its value with , and set it using .

  • Set the value to be used by the following or statement when inserting an value. This is mainly used with the binary log.

  • Set the value to be returned from . This is stored in the binary log when you use in a statement that updates a table. Setting this variable does not update the value returned by the C API function.

  • ' [COLLATE ''} | DEFAULT}

    sets the three session system variables , , and to the given character set. Setting to also sets to the default collation for . The optional clause may be used to specify a collation explicitly. See Section 10.4, “Connection Character Sets and Collations”.

    The default mapping can be restored by using a value of . The default depends on the server configuration.

    Note that the syntax for differs from that for setting most other options.

  • This option is a modifier, not a variable. It can be used to influence the effect of variables that set the character set, the collation, and the time zone. is primarily used for replication purposes: mysqlbinlog uses to modify temporarily the values of character set, collation, and time zone variables to reflect at rollforward what they were originally. is available as of MySQL 5.0.

    You cannot use with other than the allowed set of variables; if you try, you get an error like this: 

    mysql> 
ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes
internal to the MySQL server

    If is used with the allowed variables, it changes the variables as requested, but only for the next non- statement. After that, the server resets all character set, collation, and time zone-related system variables to their previous values. Example: 

    mysql> 

mysql> 

mysql> 
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin5            |
| collation_connection     | latin5_turkish_ci |
+--------------------------+-------------------+

mysql> 
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
+--------------------------+-------------------+

  • If set to 1 (the default), you can find the last inserted row for a table that contains an column by using the following construct: 

    WHERE  IS NULL

    This behavior is used by some ODBC programs, such as Access.

  • If set to 0, MySQL aborts statements that are likely to take a very long time to execute (that is, statements for which the optimizer estimates that the number of examined rows exceeds the value of ). This is useful when an inadvisable statement has been issued. The default value for a new connection is 1, which allows all statements.

    If you set the system variable to a value other than , is set to 0.

  • If set to 1, forces results from statements to be put into temporary tables. This helps MySQL free the table locks early and can be beneficial in cases where it takes a long time to send results to the client. The default value is 0.

  • If set to 0, no logging is done to the binary log for the client. The client must have the privilege to set this option. The default value is 1.

  • If set to 1, no logging is done to the general query log for this client. The client must have the privilege to set this option. The default value is 0.

  • This variable is deprecated, and is mapped to .

  • If set to 1 (the default), warnings of level are recorded. If set to 0, warnings are suppressed. mysqldump includes output to set this variable to 0 so that reloading the dump file does not produce warnings for events that do not affect the integrity of the reload operation. was added in MySQL 5.0.3.

  • If set to 1 (the default), the server quotes identifiers for and statements. If set to 0, quoting is disabled. This option is enabled by default so that replication works for identifiers that require quoting. See Section 13.5.4.6, “ Syntax”, and Section 13.5.4.4, “ Syntax”.

  • If set to 1, MySQL aborts or statements that do not use a key in the clause or a clause. This makes it possible to catch or statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

  • | DEFAULT}

    The maximum number of rows to return from statements. The default value for a new connection is “unlimited.” If you have changed the limit, the default value can be restored by using a value of .

    If a has a clause, the takes precedence over the value of .

    does not apply to statements executed within stored routines. It also does not apply to statements that do not produce a result set to be returned to the client. These include statements in subqueries, , and .

  • This variable controls whether single-row statements produce an information string if warnings occur. The default is 0. Set the value to 1 to produce an information string.

  • | DEFAULT}

    Set the time for this client. This is used to get the original timestamp if you use the binary log to restore rows. should be a Unix epoch timestamp, not a MySQL timestamp.

    affects the value returned by but not by . This means that timestamp settings in the binary log have no effect on invocations of . The server can be started with the option to cause to be an alias for , in which case affects both functions.

  • If set to 1 (the default), uniqueness checks for secondary indexes in tables are performed. If set to 0, storage engines are allowed to assume that duplicate keys are not present in input data. If you know for certain that your data does not contain uniqueness violations, you can set this to 0 to speed up large table imports to .

    Note that setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still allowed to check for them and issue duplicate-key errors if it detects them.

13.5.4. SHOW Syntax

has many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following: 

SHOW [FULL] COLUMNS FROM  [FROM ] [LIKE '']
SHOW CREATE DATABASE 
SHOW CREATE FUNCTION 
SHOW CREATE PROCEDURE 
SHOW CREATE TABLE 
SHOW DATABASES [LIKE '']
SHOW ENGINE  {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [,] ]
SHOW FUNCTION STATUS [LIKE '']
SHOW GRANTS FOR 
SHOW INDEX FROM  [FROM ]
SHOW INNODB STATUS
SHOW PROCEDURE STATUS [LIKE '']
SHOW [BDB] LOGS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW [GLOBAL | SESSION] STATUS [LIKE '']
SHOW TABLE STATUS [FROM ] [LIKE '']
SHOW [OPEN] TABLES [FROM ] [LIKE '']
SHOW TRIGGERS
SHOW [GLOBAL | SESSION] VARIABLES [LIKE '']
SHOW WARNINGS [LIMIT [,] ]

The statement also has forms that provide information about replication master and slave servers and are described in Section 13.6, “Replication Statements”: 

SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW MASTER STATUS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS

If the syntax for a given statement includes a ' part, ' is a string that can contain the SQL ‘’ and ‘’ wildcard characters. The pattern is useful for restricting statement output to matching values.

Several statements also accept a clause that provides more flexibility in specifying which rows to display. See Section 20.18, “Extensions to Statements”.

13.5.4.1.  Syntax

SHOW CHARACTER SET [LIKE '']

The statement shows all available character sets. It takes an optional clause that indicates which character set names to match. For example: 

mysql> 
+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | cp1252 West European        | latin1_swedish_ci |      1 |
| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
+---------+-----------------------------+-------------------+--------+

The column shows the maximum number of bytes required to store one character.

13.5.4.2.  Syntax

SHOW COLLATION [LIKE '']

The output from includes all available character sets. It takes an optional clause whose indicates which collation names to match. For example: 

mysql> 
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         |          |       0 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       0 |
| latin1_danish_ci  | latin1  | 15 |         |          |       0 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       0 |
| latin1_general_ci | latin1  | 48 |         |          |       0 |
| latin1_general_cs | latin1  | 49 |         |          |       0 |
| latin1_spanish_ci | latin1  | 94 |         |          |       0 |
+-------------------+---------+----+---------+----------+---------+

The column indicates whether a collation is the default for its character set. indicates whether the character set is compiled into the server. is related to the amount of memory required to sort strings expressed in the character set.

13.5.4.3.  Syntax

SHOW [FULL] COLUMNS FROM  [FROM ] [LIKE '']

displays information about the columns in a given table. It also works for views as of MySQL 5.0.1.

If the data types differ from what you expect them to be based on your statement, note that MySQL sometimes changes data types when you create or alter a table. The conditions for which this occurs are described in Section 13.1.5.1, “Silent Column Specification Changes”.

The keyword causes the output to include the privileges you have as well as any per-column comments for each column.

You can use as an alternative to the FROM syntax. In other words, these two statements are equivalent: 

mysql> 
mysql>

is a synonym for . You can also list a table's columns with the mysqlshow command.

The statement provides information similar to . See Section 13.3.1, “ Syntax”.

13.5.4.4.  Syntax

SHOW CREATE {DATABASE | SCHEMA}

Shows the statement that creates the given database. is a synonym for as of MySQL 5.0.2. 

mysql> 
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

mysql> 
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

quotes table and column names according to the value of the option. See Section 13.5.3, “ Syntax”.

13.5.4.5.  and Syntax

SHOW CREATE {PROCEDURE | FUNCTION}

This statement is a MySQL extension. Similar to , it returns the exact string that can be used to re-create the named routine. 

mysql> 
*************************** 1. row ***************************
       Function: hello
       sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
                 RETURN CONCAT('Hello, ',s,'!')

13.5.4.6.  Syntax

SHOW CREATE TABLE

Shows the statement that creates the given table. As of MySQL 5.0.1, this statement also works with views. 

mysql> 
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM

quotes table and column names according to the value of the option. See Section 13.5.3, “ Syntax”.

13.5.4.7.  Syntax

SHOW CREATE VIEW

This statement shows a statement that creates the given view. 

mysql> 
+------+----------------------------------------------------+
| View | Create View                                        |
+------+----------------------------------------------------+
| v    | CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b` |
+------+----------------------------------------------------+

This statement was added in MySQL 5.0.1.

Prior to MySQL 5.0.11, the output columns from this statement were shown as and .

Use of requires the privilege and the privilege for the view in question.

You can also obtain information about view objects from , which contains a table. See Section 20.15, “The Table”.

13.5.4.8.  Syntax

SHOW {DATABASES | SCHEMAS} [LIKE '']

lists the databases on the MySQL server host. is a synonym for as of MySQL 5.0.2.

You see only those databases for which you have some kind of privilege, unless you have the global privilege. You can also get this list using the mysqlshow command.

If the server was started with the option, you cannot use this statement at all unless you have the privilege.

13.5.4.9.  Syntax

SHOW ENGINE  {LOGS | STATUS }

displays log or status information about a storage engine. The following statements currently are supported: 

SHOW ENGINE BDB LOGS
SHOW ENGINE INNODB STATUS

displays status information about existing log files. It returns the following fields:

  • The full path to the log file.

  • The log file type ( for Berkeley DB log files).

  • The status of the log file ( if the file can be removed, or if the file is needed by the transaction subsystem)

displays extensive information about the state of the storage engine.

The Monitors provide additional information about processing. See Section 14.2.11.1, “ and the Monitors”.

Older (and now deprecated) synonyms for these statements are and .

13.5.4.10.  Syntax

SHOW [STORAGE] ENGINES

displays status information about the server's storage engines. This is particularly useful for checking whether a storage engine is supported, or to see what the default engine is. is a deprecated synonym. 

mysql> 
*************************** 1. row ***************************
 Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
 Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
 Engine: HEAP
Support: YES
Comment: Alias for MEMORY
*************************** 4. row ***************************
 Engine: MERGE
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 5. row ***************************
 Engine: MRG_MYISAM
Support: YES
Comment: Alias for MERGE
*************************** 6. row ***************************
 Engine: ISAM
Support: NO
Comment: Obsolete storage engine, now replaced by MyISAM
*************************** 7. row ***************************
 Engine: MRG_ISAM
Support: NO
Comment: Obsolete storage engine, now replaced by MERGE
*************************** 8. row ***************************
 Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 9. row ***************************
 Engine: INNOBASE
Support: YES
Comment: Alias for INNODB
*************************** 10. row ***************************
 Engine: BDB
Support: YES
Comment: Supports transactions and page-level locking
*************************** 11. row ***************************
 Engine: BERKELEYDB
Support: YES
Comment: Alias for BDB
*************************** 12. row ***************************
 Engine: NDBCLUSTER
Support: NO
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 13. row ***************************
 Engine: NDB
Support: NO
Comment: Alias for NDBCLUSTER
*************************** 14. row ***************************
 Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 15. row ***************************
 Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 16. row ***************************
 Engine: CSV
Support: NO
Comment: CSV storage engine
*************************** 17. row ***************************
 Engine: FEDERATED
Support: YES
Comment: Federated MySQL storage engine
*************************** 18. row ***************************
 Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)

The value indicates whether the particular storage engine is supported, and which is the default engine. For example, if the server is started with the option, the value for the row has the value . See Chapter 14, Storage Engines and Table Types.

13.5.4.11.  Syntax

SHOW ERRORS [LIMIT [,] ]
SHOW COUNT(*) ERRORS

This statement is similar to , except that instead of displaying errors, warnings, and notes, it displays only errors.

The clause has the same syntax as for the statement. See Section 13.2.7, “ Syntax”.

The statement displays the number of errors. You can also retrieve this number from the variable: 

SHOW COUNT(*) ERRORS;
SELECT @@error_count;

For more information, see Section 13.5.4.25, “ Syntax”.

13.5.4.12.  Syntax

SHOW GRANTS FOR

This statement lists the statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account. The account is named using the same format as for the statement; for example, . The user and host parts of the account name correspond to the and column values of the table row for the account. 

mysql> 
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements: 

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

As of MySQL 5.0.24, if (or any of the equivalent syntaxes) is used in context, such as within a stored procedure that is defined with ), the grants displayed are those of the definer and not the invoker.

displays only the privileges granted explicitly to the named account. Other privileges might be available to the account, but they are not displayed. For example, if an anonymous account exists, the named account might be able to use its privileges, but will not display them.

13.5.4.13.  Syntax

SHOW INDEX FROM  [FROM ]

returns table index information. The format resembles that of the call in ODBC.

returns the following fields:

  • The name of the table.

  • 0 if the index cannot contain duplicates, 1 if it can.

  • The name of the index.

  • The column sequence number in the index, starting with 1.

  • The column name.

  • How the column is sorted in the index. In MySQL, this can have values ‘’ (Ascending) or (Not sorted).

  • An estimate of the number of unique values in the index. This is updated by running or myisamchk -a. is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

  • The number of indexed characters if the column is only partly indexed, if the entire column is indexed.

  • Indicates how the key is packed. if it is not.

  • Contains if the column may contain . If not, the column contains as of MySQL 5.0.3, and before that.

  • The index method used (, , , ).

  • Various remarks.

You can use . as an alternative to the FROM syntax. These two statements are equivalent: 

SHOW INDEX FROM mytable FROM mydb;
SHOW INDEX FROM mydb.mytable;

is a synonym for . You can also list a table's indexes with the mysqlshow -k command.

13.5.4.14.  Syntax

SHOW INNODB STATUS

In MySQL 5.0, this is a deprecated synonym for . See Section 13.5.4.9, “ Syntax”.

13.5.4.15.  Syntax

SHOW [BDB] LOGS

In MySQL 5.0, this is a deprecated synonym for . See Section 13.5.4.9, “ Syntax”.

13.5.4.16.  Syntax

SHOW OPEN TABLES [FROM ] [LIKE '']

lists the non- tables that are currently open in the table cache. See Section 7.4.8, “How MySQL Opens and Closes Tables”.

returns the following fields:

  • The database containing the table.

  • The table name.

  • The number of times the table currently is in use by queries. If the count is zero, the table is open, but not currently being used.

  • Whether the table name is locked. Name locking is used for operations such as dropping or renaming tables.

The and clauses may be used as of MySQL 5.0.12.

13.5.4.17.  Syntax

SHOW PRIVILEGES

shows the list of system privileges that the MySQL server supports. The exact list of privileges depends on the version of your server. 

mysql> 
*************************** 1. row ***************************
Privilege: Alter
Context: Tables
Comment: To alter the table
*************************** 2. row ***************************
Privilege: Alter routine
Context: Functions,Procedures
Comment: To alter or drop stored functions/procedures
*************************** 3. row ***************************
Privilege: Create
Context: Databases,Tables,Indexes
Comment: To create new databases and tables
*************************** 4. row ***************************
Privilege: Create routine
Context: Functions,Procedures
Comment: To use CREATE FUNCTION/PROCEDURE
*************************** 5. row ***************************
Privilege: Create temporary tables
Context: Databases
Comment: To use CREATE TEMPORARY TABLE
...

13.5.4.18.  and Syntax

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE '']

This statement is a MySQL extension. It returns characteristics of routines, such as the database, name, type, creator, and creation and modification dates. If no pattern is specified, the information for all stored procedures or all stored functions is listed, depending on which statement you use. 

mysql> 
*************************** 1. row ***************************
           Db: test
         Name: hello
         Type: FUNCTION
      Definer: testuser@localhost
     Modified: 2004-08-03 15:29:37
      Created: 2004-08-03 15:29:37
Security_type: DEFINER
      Comment:

You can also get information about stored routines from the table in . See Section 20.14, “The Table”.

13.5.4.19.  Syntax

SHOW [FULL] PROCESSLIST

shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). See Section 13.5.5.3, “ Syntax”. If you do not use the keyword, only the first 100 characters of each statement are shown in the field.

This statement is very useful if you get the “too many connections” error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the privilege, to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users).

The output of may look like this: 

mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 3112
User: replikator
Host: artemis:2204
db: NULL
Command: Binlog Dump
Time: 2144
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 3113
User: replikator
Host: iconnect2:45781
db: NULL
Command: Binlog Dump
Time: 2086
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 5. row ***************************
Id: 3123
User: stefan
Host: localhost
db: apollon
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
5 rows in set (0.00 sec)

The columns have the following meaning:

  • The connection identifier.

  • The MySQL user who issued the statement. If this is , it refers to a non-client thread spawned by the server to handle tasks internally. This could be the I/O or SQL thread used on replication slaves or a delayed-row handler. For , there is no host specified in the column.

  • The hostname of the client issuing the statement (except for where there is no host). reports the hostname for TCP/IP connections in : format to make it easier to determine which client is doing what.

  • The default database, if one is selected, otherwise .

  • The value of that column corresponds to the commands of the client/server protocol. See Section 5.2.4, “Server Status Variables”

    The value may be one of the following: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

  • The time in seconds between the start of the statement or command and now.

  • An action, event, or state, which can be one of the following: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

    The most common values are described in the rest of this section. Most of the other values are useful only for finding bugs in the server. See also Section 6.3, “Replication Implementation Details”, for additional information about process states for replication servers.

    For the statement, the value of is .

  • The statement that the thread is executing, or if it is not executing any statement.

Some values commonly seen in the output from :

  • The thread is performing a table check operation.

  • Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.

  • A replication slave is connecting to its master.

  • If a statement has different and criteria, the rows are sorted by group and copied to a temporary table.

  • The server is copying to a temporary table in memory.

  • The server is copying to a temporary table on disk. The temporary result set was larger than and the thread is changing the temporary table from in-memory to disk-based format to save memory.

  • The thread is creating a temporary table to hold a part of the result for the query.

  • The server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving fields and offsets to be used for deleting from the other (reference) tables.

  • The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.

  • The thread is executing and is waiting for all threads to close their tables.

  • The server is preparing to perform a natural-language full-text search.

  • Someone has sent a statement to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.

  • The query is locked by another query.

  • The thread is processing rows for a statement and also is sending data to the client.

  • The thread is doing a sort to satisfy a .

  • The thread is doing a sort to satisfy a .

  • The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an or a statement can prevent opening a table until the statement is finished.

  • The server is reading a packet from the network.

  • The query was using in such a way that MySQL could not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.

  • The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.

  • The repair code is using a sort to create indexes.

  • The repair code is using creating keys one by one through the key cache. This is much slower than .

  • The thread is doing a first phase to find all matching rows before updating them. This has to be done if the is changing the index that is used to find the involved rows.

  • The thread is waiting for the client to send a new statement to it.

  • The server is calculating statistics to develop a query execution plan.

  • The thread is waiting to get an external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the option.

  • The state of a thread that has become associated with a client connection but for which authentication of the client user has not yet been done.

  • The handler is trying to get a lock for the table to insert rows.

  • The thread is searching for rows to update and is updating them.

  • The server is executing the first part of a multiple-table update. It is updating only the first table, and saving fields and offsets to be used for updating the other (reference) tables.

  • The server is executing the second part of a multiple-table update and updating the matched rows from the other tables.

  • The thread is waiting on a .

  • The server is acting as an SQL node in a MySQL Cluster, and is connected to a cluster management node.

  • The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

    This notification takes place if another thread has used or one of the following statements on the table in question: , , , , , or .

  • The handler has processed all pending inserts and is waiting for new ones.

  • The server is writing a packet to the network.

Most states correspond to very quick operations. If a thread stays in any of these states for many seconds, there might be a problem that needs to be investigated.

13.5.4.20.  Syntax

SHOW [GLOBAL | SESSION] STATUS [LIKE '']

provides server status information. This information also can be obtained using the mysqladmin extended-status command.

Partial output is shown here. The list of names and values may be different for your server. The meaning of each variable is given in Section 5.2.4, “Server Status Variables”. 

mysql> 
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 0          |
| Aborted_connects         | 0          |
| Bytes_received           | 155372598  |
| Bytes_sent               | 1176560426 |
| Connections              | 30023      |
| Created_tmp_disk_tables  | 0          |
| Created_tmp_tables       | 8340       |
| Created_tmp_files        | 60         |
...
| Open_tables              | 1          |
| Open_files               | 2          |
| Open_streams             | 0          |
| Opened_tables            | 44600      |
| Questions                | 2026873    |
...
| Table_locks_immediate    | 1920382    |
| Table_locks_waited       | 0          |
| Threads_cached           | 0          |
| Threads_created          | 30022      |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 80380      |
+--------------------------+------------+

With a clause, the statement displays only rows for those variables with names that match the pattern: 

mysql> 
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| Key_blocks_used    | 14955    |
| Key_read_requests  | 96854827 |
| Key_reads          | 162040   |
| Key_write_requests | 7589728  |
| Key_writes         | 3813196  |
+--------------------+----------+

The and options are new in MySQL 5.0.2. With the modifier, displays the status values for all connections to MySQL. With , it displays the status values for the current connection. If no modifier is present, the default is . is a synonym for .

Some status variables have only a global value. For these, you get the same value for both and .

Note: Before MySQL 5.0.2, returned global status values. Because the default as of 5.0.2 is to return session values, this is incompatible with previous versions. To issue a statement that will retrieve global status values for all versions of MySQL, write it like this: 

SHOW /*!50002 GLOBAL */ STATUS;

13.5.4.21.  Syntax

SHOW TABLE STATUS [FROM ] [LIKE '']

works likes , but provides a lot of information about each table. You can also get this list using the mysqlshow --status command.

As of MySQL 5.0.1, this statement also displays information about views.

returns the following fields:

  • The name of the table.

  • The storage engine for the table. See Chapter 14, Storage Engines and Table Types.

  • The version number of the table's file.

  • The row storage format (, , , , ). Starting with MySQL/InnoDB 5.0.3, the format of tables is reported as or . Prior to 5.0.3, tables are always in the format.

  • The number of rows. Some storage engines, such as , store the exact count. For other storage engines, such as , this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use to obtain an accurate count.

    The value is for tables in the database.

  • The average row length.

  • The length of the data file.

  • The maximum length of the data file. This is the total number of bytes of data that can be stored in the table, given the data pointer size used.

  • The length of the index file.

  • The number of allocated but unused bytes.

  • The next value.

  • When the table was created.

  • When the data file was last updated. For some storage engines, this value is . For example, stores multiple tables in its tablespace and the data file timestamp does not apply.

  • When the table was last checked. Not all storage engines update this time, in which case the value is always .

  • The table's character set and collation.

  • The live checksum value (if any).

  • Extra options used with .

  • The comment used when creating the table (or information as to why MySQL could not access the table information).

In the table comment, tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace. If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table.

For tables, the , , and values approximate the actual amount of allocated memory. The allocation algorithm reserves memory in large amounts to reduce the number of allocation operations.

Beginning with MySQL 5.0.3, for tables, the output of this statement shows appropriate values for the and columns, with the exception that columns are not taken into account. In addition, the number of replicas is now shown in the column (as ).

For views, all the fields displayed by are except that indicates the view name and says .

13.5.4.22.  Syntax

SHOW [FULL] TABLES [FROM ] [LIKE '']

lists the non- tables in a given database. You can also get this list using the mysqlshow command.

Before MySQL 5.0.1, the output from contains a single column of table names. Beginning with MySQL 5.0.1, this statement also lists any views in the database. As of MySQL 5.0.2, the modifier is supported such that displays a second output column. Values for the second column are for a table and for a view.

Note: If you have no privileges for a table, the table does not show up in the output from or mysqlshow db_name.

13.5.4.23.  Syntax

SHOW TRIGGERS [FROM ] [LIKE ]

lists the triggers currently defined on the MySQL server. This statement requires the privilege. It was implemented in MySQL 5.0.10.

For the trigger as defined in Section 18.3, “Using Triggers”, the output of this statement is as shown here: 

mysql> 
*************************** 1. row ***************************
  Trigger: ins_sum
    Event: INSERT
    Table: account
Statement: SET @sum = @sum + NEW.amount
   Timing: BEFORE
  Created: NULL
 sql_mode:
  Definer: myname@localhost

Note: When using a clause with , the expression to be matched () is compared with the name of the table on which the trigger is declared, and not with the name of the trigger: 

mysql> 
Empty set (0.01 sec)

A brief explanation of the columns in the output of this statement is shown here:

  • The name of the trigger.

  • The event that causes trigger activation: one of , , or .

  • The table for which the trigger is defined.

  • The statement to be executed when the trigger is activated. This is the same as the text shown in the column of .

  • One of the two values or .

  • Currently, the value of this column is always .

  • The SQL mode in effect when the trigger executes. This column was added in MySQL 5.0.11.

  • The account that created the trigger. This column was added in MySQL 5.0.17.

You must have the privilege to execute .

See also Section 20.16, “The Table”.

13.5.4.24.  Syntax

SHOW [GLOBAL | SESSION] VARIABLES [LIKE '']

shows the values of MySQL system variables. This information also can be obtained using the mysqladmin variables command.

With the modifier, displays the values that are used for new connections to MySQL. With , it displays the values that are in effect for the current connection. If no modifier is present, the default is . is a synonym for .

If the default system variable values are unsuitable, you can set them using command options when mysqld starts, and most can be changed at runtime with the statement. See Section 5.2.3, “Using System Variables”, and Section 13.5.3, “ Syntax”.

Partial output is shown here. The list of names and values may be different for your server. Section 5.2.2, “Server System Variables”, describes the meaning of each variable, and Section 7.5.2, “Tuning Server Parameters”, provides information about tuning them. 

mysql> 
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| auto_increment_increment        | 1                                   |
| auto_increment_offset           | 1                                   |
| automatic_sp_privileges         | ON                                  |
| back_log                        | 50                                  |
| basedir                         | /                                   |
| bdb_cache_size                  | 8388600                             |
| bdb_home                        | /var/lib/mysql/                     |
| bdb_log_buffer_size             | 32768                               |
...
| max_connections                 | 100                                 |
| max_connect_errors              | 10                                  |
| max_delayed_threads             | 20                                  |
| max_error_count                 | 64                                  |
| max_heap_table_size             | 16777216                            |
| max_join_size                   | 4294967295                          |
| max_relay_log_size              | 0                                   |
| max_sort_length                 | 1024                                |
...
| time_zone                       | SYSTEM                              |
| timed_mutexes                   | OFF                                 |
| tmp_table_size                  | 33554432                            |
| tmpdir                          |                                     |
| transaction_alloc_block_size    | 8192                                |
| transaction_prealloc_size       | 4096                                |
| tx_isolation                    | REPEATABLE-READ                     |
| updatable_views_with_limit      | YES                                 |
| version                         | 5.0.19-Max                          |
| version_comment                 | MySQL Community Edition - Max (GPL) |
| version_compile_machine         | i686                                |
| version_compile_os              | pc-linux-gnu                        |
| wait_timeout                    | 28800                               |
+---------------------------------+-------------------------------------+

With a clause, the statement displays only rows for those variables with names that match the pattern. To obtain the row for a specific variable, use a clause as shown: 

SHOW VARIABLES LIKE 'max_join_size';
SHOW SESSION VARIABLES LIKE 'max_join_size';

To get a list of variables whose name match a pattern, use the ‘’ wildcard character in a clause: 

SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';

Wildcard characters can be used in any position within the pattern to be matched. Strictly speaking, because ‘’ is a wildcard that matches any single character, you should escape it as ‘’ to match it literally. In practice, this is rarely necessary.

13.5.4.25.  Syntax

SHOW WARNINGS [LIMIT [,] ]
SHOW COUNT(*) WARNINGS

shows the error, warning, and note messages that resulted from the last statement that generated messages, or nothing if the last statement that used a table generated no messages. A related statement, , shows only the errors. See Section 13.5.4.11, “ Syntax”.

The list of messages is reset for each new statement that uses a table.

The statement displays the total number of errors, warnings, and notes. You can also retrieve this number from the variable: 

SHOW COUNT(*) WARNINGS;
SELECT @@warning_count;

The value of might be greater than the number of messages displayed by if the system variable is set so low that not all messages are stored. An example shown later in this section demonstrates how this can happen.

The clause has the same syntax as for the statement. See Section 13.2.7, “ Syntax”.

The MySQL server sends back the total number of errors, warnings, and notes resulting from the last statement. If you are using the C API, this value can be obtained by calling . See Section 22.2.3.71, “”.

Warnings are generated for statements such as and DML statements such as , , , and .

The following statement results in a note: 

mysql> 
mysql> 
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1051 | Unknown table 'no_such_table' |
+-------+------+-------------------------------+

Here is a simple example that shows a syntax warning for and conversion warnings for : 

mysql> 
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> 
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'TYPE=storage_engine' is deprecated, use
         'ENGINE=storage_engine' instead
1 row in set (0.00 sec)

mysql> 
    -> 
Query OK, 3 rows affected, 4 warnings (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 4

mysql> 
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'b' at row 1
*************************** 2. row ***************************
  Level: Warning
   Code: 1263
Message: Data truncated, NULL supplied to NOT NULL column 'a' at row 2
*************************** 3. row ***************************
  Level: Warning
   Code: 1264
Message: Data truncated, out of range for column 'a' at row 3
*************************** 4. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'b' at row 3
4 rows in set (0.00 sec)

The maximum number of error, warning, and note messages to store is controlled by the system variable. By default, its value is 64. To change the number of messages you want stored, change the value of . In the following example, the statement produces three warning messages, but only one is stored because has been set to 1: 

mysql> 
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> 
Query OK, 0 rows affected (0.00 sec)

mysql> 
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> 
+-----------------+
| @@warning_count |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.01 sec)

mysql> 
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1263 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

To disable warnings, set to 0. In this case, still indicates how many warnings have occurred, but none of the messages are stored.

As of MySQL 5.0.3, you can set the session variable to 0 to cause -level warnings not to be recorded.

13.5.5. Other Administrative Statements

13.5.5.1.  Syntax

CACHE INDEX
   [, ] ...
  IN 

:
   [[INDEX|KEY] ([, ] ...)]

The statement assigns table indexes to a specific key cache. It is used only for tables.

The following statement assigns indexes from the tables , , and to the key cache named : 

mysql> 
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

The syntax of enables you to specify that only particular indexes from a table should be assigned to the cache. The current implementation assigns all the table's indexes to the cache, so there is no reason to specify anything other than the table name.

The key cache referred to in a statement can be created by setting its size with a parameter setting statement or in the server parameter settings. For example: 

mysql>

Key cache parameters can be accessed as members of a structured system variable. See Section 5.2.3.1, “Structured System Variables”.

A key cache must exist before you can assign indexes to it: 

mysql> 
ERROR 1284 (HY000): Unknown key cache 'non_existent_cache'

By default, table indexes are assigned to the main (default) key cache created at the server startup. When a key cache is destroyed, all indexes assigned to it become assigned to the default key cache again.

Index assignment affects the server globally: If one client assigns an index to a given cache, this cache is used for all queries involving the index, no matter which client issues the queries.

13.5.5.2.  Syntax

FLUSH [LOCAL | NO_WRITE_TO_BINLOG]  [, ] ...

The statement clears or reloads various internal caches used by MySQL. To execute , you must have the privilege.

The statement is similar to . See Section 13.5.5.5, “ Syntax”.

can be any of the following:

  • Empties the host cache tables. You should flush the host tables if some of your hosts change IP number or if you get the error message ' is blocked. When more than errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host tables allows the host to attempt to connect again. See Section A.2.5, “' is blocked”. You can start mysqld with to avoid this error message.

  • Reloads the DES keys from the file that was specified with the option at server startup time.

  • Closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. On Unix, this is the same thing as sending a signal to the mysqld server (except on some Mac OS X 10.3 versions where mysqld ignores and ).

    If the server was started with the option, causes the error log is renamed with a suffix of and mysqld creates a new empty log file. No renaming occurs if the option was not given.

  • (DEPRECATED). Deletes all binary logs, resets the binary log index file and creates a new binary log. Deprecated in favor of , supported for backwards compatility only See Section 13.6.1.2, “ Syntax”.

  • Reloads the privileges from the grant tables in the database.

  • Defragment the query cache to better utilize its memory. does not remove any queries from the cache, unlike .

  • (DEPRECATED). Resets all replication slave parameters, including relay log files and replication position in the master's binary logs. Deprecated in favor of , supported for backwards compatility only. See Section 13.6.2.5, “ Syntax”.

  • Resets most status variables to zero. This is something you should use only when debugging a query. See Section 1.8, “How to Report Bugs or Problems”.

  • [, ] ...]

    When no tables are named, closes all open tables and forces all tables in use to be closed. This also flushes the query cache. With one or more table names, flushes only the given tables. also removes all query results from the query cache, like the statement.

  • Closes all open tables and locks all tables for all databases with a read lock until you execute . This is very convenient way to get backups if you have a filesystem such as Veritas that can take snapshots in time.

  • Resets all per-hour user resources to zero. This enables clients that have reached their hourly connection, query, or update limits to resume activity immediately. does not apply to the limit on maximum simultaneous connections. See Section 13.5.1.3, “ Syntax”.

statements are written to the binary log unless the optional keyword (or its alias ) is used. This is done so that statements used on a MySQL server acting as a replication master will be replicated by default to the replication slave.

Note: , , , and are not logged in any case because they would cause problems if replicated to a slave.

You can also access some of these statements with the mysqladmin utility, using the , , , , or commands.

Using statements within stored functions or triggers is not supported in MySQL 5.0. However, you may use in stored procedures, so long as these are not called from stored functions or triggers. See Section I.1, “Restrictions on Stored Routines and Triggers”.

See also Section 13.5.5.5, “ Syntax”, for information about how the statement is used with replication.

13.5.5.3.  Syntax

KILL [CONNECTION | QUERY]

Each connection to mysqld runs in a separate thread. You can see which threads are running with the statement and kill a thread with the statement.

In MySQL 5.0.0, allows the optional or modifier:

  • is the same as with no modifier: It terminates the connection associated with the given .

  • terminates the statement that the connection is currently executing, but leaves the connection itself intact.

If you have the privilege, you can see all threads. If you have the privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements.

You can also use the mysqladmin processlist and mysqladmin kill commands to examine and kill threads.

Note: You cannot use with the Embedded MySQL Server library, because the embedded server merely runs inside the threads of the host application. It does not create any connection threads of its own.

When you use , a thread-specific kill flag is set for the thread. In most cases, it might take some time for the thread to die, because the kill flag is checked only at specific intervals:

  • In , and loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.

  • During , the kill flag is checked before each block of rows are read from the original table. If the kill flag was set, the statement is aborted and the temporary table is deleted.

  • During or operations, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted. Note that if you are not using transactions, the changes are not rolled back.

  • aborts and returns .

  • An thread quickly flushes (inserts) all rows it has in memory and then terminates.

  • If the thread is in the table lock handler (state: ), the table lock is quickly aborted.

  • If the thread is waiting for free disk space in a write call, the write is aborted with a “disk full” error message.

  • Warning: Killing a or operation on a table results in a table that is corrupted and unusable. Any reads or writes to such a table fail until you optimize or repair it again (without interruption).

13.5.5.4.  Syntax

LOAD INDEX INTO CACHE
   [, ] ...

:
  
    [[INDEX|KEY] ([, ] ...)]
    [IGNORE LEAVES]

The statement preloads a table index into the key cache to which it has been assigned by an explicit statement, or into the default key cache otherwise. is used only for tables.

The modifier causes only blocks for the non-leaf nodes of the index to be preloaded.

The following statement preloads nodes (index blocks) of indexes for the tables and : 

mysql> 
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

This statement preloads all index blocks from . It preloads only blocks for the non-leaf nodes from .

The syntax of enables you to specify that only particular indexes from a table should be preloaded. The current implementation preloads all the table's indexes into the cache, so there is no reason to specify anything other than the table name.

13.5.5.5.  Syntax

RESET  [, ] ...

The statement is used to clear the state of various server operations. You must have the privilege to execute .

acts as a stronger version of the statement. See Section 13.5.5.2, “ Syntax”.

can be any of the following:

previous page start next page