13.5. Database Administration Statements

13.5.1. Account Management Statements

MySQL account information is stored in the tables of the mysql 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. CREATE USER Syntax

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

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

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

13.5.1.2. DROP USER Syntax

DROP USER user [, user] ...

The DROP USER statement removes one or more MySQL accounts. To use it, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table row for the account.

DROP USER 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 user;

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

In MySQL 5.0.0 and 5.0.1, DROP USER deletes only MySQL accounts that have no privileges. In these MySQL versions, it serves only to remove each account record from the user 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 SHOW GRANTS to determine what privileges the account has. See Section 13.5.4.12, “SHOW GRANTS Syntax”.

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

  3. Delete the account by using DROP USER to remove the user table record.

Important: DROP USER 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. GRANT Syntax

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

object_type =
    TABLE
  | FUNCTION
  | PROCEDURE

with_option =
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

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

MySQL account information is stored in the tables of the mysql 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 lower_case_table_names system variable is set to a non-zero value, REVOKE cannot be used to revoke these privileges. It will be necessary to manipulate the grant tables directly. (GRANT will not create such rows when lower_case_table_names 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 mysql.user table. GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.

  • Database level

    Database privileges apply to all objects in a given database. These privileges are stored in the mysql.db and mysql.host tables. GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke only database privileges.

  • Table level

    Table privileges apply to all columns in a given table. These privileges are stored in the mysql.tables_priv table. GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke only table privileges.

  • Column level

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

  • Routine level

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

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

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

PrivilegeMeaning
ALL [PRIVILEGES]Sets all simple privileges except GRANT OPTION
ALTEREnables use of ALTER TABLE
ALTER ROUTINEEnables stored routines to be altered or dropped
CREATEEnables use of CREATE TABLE
CREATE ROUTINEEnables creation of stored routines
CREATE TEMPORARY TABLESEnables use of CREATE TEMPORARY TABLE
CREATE USEREnables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEWEnables use of CREATE VIEW
DELETEEnables use of DELETE
DROPEnables use of DROP TABLE
EXECUTEEnables the user to run stored routines
FILEEnables use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEXEnables use of CREATE INDEX and DROP INDEX
INSERTEnables use of INSERT
LOCK TABLESEnables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESSEnables use of SHOW FULL PROCESSLIST
REFERENCESNot implemented
RELOADEnables use of FLUSH
REPLICATION CLIENTEnables the user to ask where slave or master servers are
REPLICATION SLAVENeeded for replication slaves (to read binary log events from the master)
SELECTEnables use of SELECT
SHOW DATABASESSHOW DATABASES shows all databases
SHOW VIEWEnables use of SHOW CREATE VIEW
SHUTDOWNEnables use of mysqladmin shutdown
SUPEREnables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached
UPDATEEnables use of UPDATE
USAGESynonym for “no privileges
GRANT OPTIONEnables privileges to be granted

The EXECUTE privilege is not operational until MySQL 5.0.3. CREATE VIEW and SHOW VIEW were added in MySQL 5.0.1. CREATE USER, CREATE ROUTINE, and ALTER ROUTINE were added in MySQL 5.0.3.

The REFERENCES privilege currently is unused.

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

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

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

The FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative privileges that can only be granted globally (using ON *.* syntax).

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

The priv_type values that you can specify for a table are SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, ALTER, CREATE VIEW and SHOW VIEW.

The priv_type values that you can specify for a column (that is, when you use a column_list clause) are SELECT, INSERT, and UPDATE.

The priv_type values that you can specify at the routine level are ALTER ROUTINE, EXECUTE, and GRANT OPTION. CREATE ROUTINE 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, GRANT ALL assigns only the privileges that exist at the level you are granting. For example, GRANT ALL ON db_name.* is a database-level statement, so it does not grant any global-only privileges such as FILE.

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 CREATE 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 GRANT 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 GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ....

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

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

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

MySQL does not support wildcards in usernames. Anonymous users are defined by inserting entries with User='' into the mysql.user table or by creating a user with an empty name with the GRANT 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 user_name@localhost. Otherwise, the anonymous user account for localhost in the mysql.user 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;

GRANT 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 mysql.user 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 mysql 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 OR of the privileges at each of the privilege levels. For example, if the mysql.user table specifies that a user has a global SELECT 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 mysql.user table, an entry is added and remains there until deleted with a DELETE statement. In other words, GRANT may create user table entries, but REVOKE does not remove them; you must do that explicitly using DROP USER or DELETE.

Warning: If you create a new user but do not specify an IDENTIFIED BY clause, the user has no password. This is very insecure. As of MySQL 5.0.2, you can enable the NO_AUTO_CREATE_USER SQL mode to prevent GRANT from creating a new user if it would otherwise do so, unless IDENTIFIED BY 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 IDENTIFIED BY 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 SET PASSWORD statement. See Section 13.5.1.6, “SET PASSWORD Syntax”.

In the IDENTIFIED BY clause, the password should be given as the literal password value. It is unnecessary to use the PASSWORD() function as it is for the SET PASSWORD 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 PASSWORD() would return for the password, you can specify the hashed value preceded by the keyword PASSWORD:

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

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

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

The SHOW DATABASES privilege enables the account to see database names by issuing the SHOW DATABASE 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 --skip-show-database 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 SHOW TABLES statement).

The WITH GRANT OPTION 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 GRANT OPTION 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 GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.

Be aware that when you grant a user the GRANT OPTION 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 INSERT privilege on a database. If you then grant the SELECT privilege on the database and specify WITH GRANT OPTION, that user can give to other users not only the SELECT privilege, but also INSERT. If you then grant the UPDATE privilege to the user on the database, the user can grant INSERT, SELECT, and UPDATE.

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

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

The MAX_USER_CONNECTIONS count option, implemented in MySQL 5.0.3, limits the maximum number of simultaneous connections that the account can make. If count is 0 (the default), the max_user_connections 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 GRANT USAGE ON *.* ... WITH MAX_....

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 REQUIRE clause of the GRANT 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 REQUIRE SSL 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;
    
  • REQUIRE X509 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;
    
  • REQUIRE ISSUER 'issuer' places the restriction on connection attempts that the client must present a valid X509 certificate issued by CA 'issuer'. 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 SSL 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=tonu@example.com';
    

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

  • REQUIRE SUBJECT 'subject' places the restriction on connection attempts that the client must present a valid X509 certificate containing the subject 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=tonu@example.com';
    

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

  • REQUIRE CIPHER 'cipher' 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 SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE 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=tonu@example.com'
  AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
    O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

The AND keyword is optional between REQUIRE 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 GRANT 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 TRIGGER or UNDER 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 DROP USER. Before 5.0.2, the granted privileges are not automatically revoked; you must revoke them yourself. See Section 13.5.1.2, “DROP USER 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 REVOKE statements or by manipulating values stored in the MySQL grant tables.

  • In MySQL, it is possible to have the INSERT privilege for only some of the columns in a table. In this case, you can still execute INSERT statements on the table, provided that you omit those columns for which you do not have the INSERT 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 INSERT 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. RENAME USER Syntax

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

The RENAME USER statement renames existing MySQL accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql 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 GRANT statement; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table row for the account.

The RENAME USER statement was added in MySQL 5.0.2.

13.5.1.5. REVOKE Syntax

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...

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

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

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

If the grant tables hold privilege rows that contain mixed-case database or table names and the lower_case_table_names system variable is set to a non-zero value, REVOKE cannot be used to revoke these privileges. It will be necessary to manipulate the grant tables directly. (GRANT will not create such rows when lower_case_table_names 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 user [, user] ...

To use this REVOKE syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database.

13.5.1.6. SET PASSWORD Syntax

SET PASSWORD [FOR user] = PASSWORD('some password')

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

With no FOR 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 FOR clause, this statement sets the password for a specific account on the current server host. Only clients that have the UPDATE privilege for the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the mysql.user table entry. For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this:

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

That is equivalent to the following statements:

UPDATE mysql.user SET Password=PASSWORD('newpass')
  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 SET PASSWORD or UPDATE 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 SELECT CURRENT_USER().

13.5.2. Table Maintenance Statements

13.5.2.1. ANALYZE TABLE Syntax

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

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

For more information on how the analysis works withinInnoDB, see Section 14.2.16, “Restrictions on InnoDB 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 SELECT and INSERT privileges for the table.

ANALYZE TABLE returns a result set with the following columns:

ColumnValue
TableThe table name
OpAlways analyze
Msg_typeOne of status, error, info, or warning
Msg_textThe message

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

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

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

13.5.2.2. BACKUP TABLE Syntax

BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'

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.

BACKUP TABLE 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 MyISAM tables. It copies the .frm definition and .MYD data files. The .MYI index file can be rebuilt from those two files. The directory should be specified as a full pathname. To restore the table, use RESTORE TABLE.

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 LOCK TABLES statement first, to obtain a read lock for all tables in the group.

BACKUP TABLE returns a result set with the following columns:

ColumnValue
TableThe table name
OpAlways backup
Msg_typeOne of status, error, info, or warning
Msg_textThe message

13.5.2.3. CHECK TABLE Syntax

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

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

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

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

CHECK TABLE returns a result set with the following columns:

ColumnValue
TableThe table name
OpAlways check
Msg_typeOne of status, error, info, or warning
Msg_textThe message

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

The FOR UPGRADE option checks whether the named tables are compatible with the current version of MySQL. This option was added in MySQL 5.0.19. With FOR UPGRADE, 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 .frm file with the current MySQL version number. Marking the .frm 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, FOR UPGRADE discovers these incompatibilities:

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

  • The storage method of the new DECIMAL 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 MyISAM tables and are ignored for InnoDB tables and views.

TypeMeaning
QUICKDo not scan the rows to check for incorrect links.
FASTCheck only tables that have not been closed properly.
CHANGEDCheck only tables that have been changed since the last check or that have not been closed properly.
MEDIUMScan 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.
EXTENDEDDo 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 QUICK, MEDIUM, or EXTENDED are specified, the default check type for dynamic-format MyISAM tables is MEDIUM. This has the same result as running myisamchk --medium-check tbl_name on the table. The default check type also is MEDIUM for static-format MyISAM tables, unless CHANGED or FAST is specified. In that case, the default is QUICK. The row scan is skipped for CHANGED and FAST 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, CHECK TABLE changes the table. This happens if the table is marked as “corrupted” or “not closed properly” but CHECK TABLE does not find any problems in the table. In this case, CHECK TABLE 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 QUICK option. The latter should be used when you are in a hurry and can take the very small risk that QUICK 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.)

FAST and CHANGED 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, FAST is to be preferred over CHANGED. (The only case when it is not preferred is when you suspect that you have found a bug in the MyISAM code.)

EXTENDED 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 CHECK TABLE cannot be corrected automatically:

  • Found row where the auto_increment column has the value 0.

    This means that you have a row in the table where the AUTO_INCREMENT index column contains the value 0. (It is possible to create a row where the AUTO_INCREMENT column is 0 by explicitly setting the column to 0 with an UPDATE 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 ALTER TABLE on the table. In this case, the AUTO_INCREMENT column changes value according to the rules of AUTO_INCREMENT columns, which could cause problems such as a duplicate-key error.

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

13.5.2.4. CHECKSUM TABLE Syntax

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

CHECKSUM TABLE reports a table checksum.

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

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

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

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

13.5.2.5. OPTIMIZE TABLE Syntax

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

OPTIMIZE 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 VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

This statement requires SELECT and INSERT privileges for the table.

In most setups, you need not run OPTIMIZE TABLE 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.

OPTIMIZE TABLE works only for MyISAM, BDB, and InnoDB tables.

For MyISAM tables, OPTIMIZE TABLE 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 BDB tables, OPTIMIZE TABLE currently is mapped to ANALYZE TABLE. See Section 13.5.2.1, “ANALYZE TABLE Syntax”.

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

You can make OPTIMIZE TABLE work on other storage engines by starting mysqld with the --skip-new or --safe-mode option. In this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.

OPTIMIZE TABLE returns a result set with the following columns:

ColumnValue
TableThe table name
OpAlways optimize
Msg_typeOne of status, error, info, or warning
Msg_textThe message

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

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

13.5.2.6. REPAIR TABLE Syntax

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

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

This statement requires SELECT and INSERT privileges for the table.

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

Warning: If the server dies during a REPAIR TABLE operation, it is essential after restarting it that you immediately execute another REPAIR TABLE 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.

REPAIR TABLE returns a result set with the following columns:

ColumnValue
TableThe table name
OpAlways repair
Msg_typeOne of status, error, info, or warning
Msg_textThe message

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

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

If you use EXTENDED, 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 USE_FRM mode available for REPAIR TABLE. Use this if the .MYI index file is missing or if its header is corrupted. In this mode, MySQL re-creates the .MYI file using information from the .frm file. This kind of repair cannot be done with myisamchk. Note: Use this mode only if you cannot use regular REPAIR modes. The .MYI header contains important table metadata (in particular, current AUTO_INCREMENT value and Delete link) that are lost in REPAIR ... USE_FRM. Don't use USE_FRM if the table is compressed because this information is also stored in the .MYI file.

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

13.5.2.7. RESTORE TABLE Syntax

RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'

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

The backup for each table consists of its .frm format file and .MYD data file. The restore operation restores those files, and then uses them to rebuild the .MYI 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.

RESTORE TABLE returns a result set with the following columns:

ColumnValue
TableThe table name
OpAlways restore
Msg_typeOne of status, error, info, or warning
Msg_textThe message

13.5.3. SET Syntax

SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | [GLOBAL | SESSION] system_var_name = expr
    | [@@global. | @@session. | @@]system_var_name = expr

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

This section describes use of SET 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 SET syntax are used in other contexts:

The following discussion shows the different SET 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 @var_name and can be set as follows:

SET @var_name = expr;

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

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

  • To indicate explicitly that a variable is a session variable, precede its name by SESSION, @@session., 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.

  • LOCAL and @@local. are synonyms for SESSION and @@session..

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

A SET statement can contain multiple variable assignments, separated by commas. If you set several system variables, the most recent GLOBAL or SESSION 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 SET, 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 @@var_name 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 SET GLOBAL statement).

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

To set a SESSION variable to the GLOBAL value or a GLOBAL value to the compiled-in MySQL default value, use the DEFAULT keyword. For example, the following two statements are identical in setting the session value of max_join_size 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 DEFAULT. In such cases, use of DEFAULT 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 SELECT statement like this:

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

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

To display system variables names and values, use the SHOW VARIABLES statement. (See Section 13.5.4.24, “SHOW VARIABLES 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 SHOW VARIABLES, you can obtain their values with SELECT (with the exception of CHARACTER SET and SET NAMES). For example:

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

The lettercase of thse options does not matter.

  • AUTOCOMMIT = {0 | 1}

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

  • BIG_TABLES = {0 | 1}

    If set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower, but the error The table tbl_name is full does not occur for SELECT 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 SQL_BIG_TABLES.)

  • CHARACTER SET {charset_name | DEFAULT}

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

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

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

  • FOREIGN_KEY_CHECKS = {0 | 1}

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

  • IDENTITY = value

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

  • INSERT_ID = value

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

  • LAST_INSERT_ID = value

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

  • NAMES {'charset_name' [COLLATE 'collation_name'} | DEFAULT}

    SET NAMES sets the three session system variables character_set_client, character_set_connection, and character_set_results to the given character set. Setting character_set_connection to charset_name also sets collation_connection to the default collation for charset_name. The optional COLLATE 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 DEFAULT. The default depends on the server configuration.

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

  • ONE_SHOT

    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. ONE_SHOT is primarily used for replication purposes: mysqlbinlog uses SET ONE_SHOT to modify temporarily the values of character set, collation, and time zone variables to reflect at rollforward what they were originally. ONE_SHOT is available as of MySQL 5.0.

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

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

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

    mysql> SET ONE_SHOT character_set_connection = latin5;
    
    mysql> SET ONE_SHOT collation_connection = latin5_turkish_ci;
    
    mysql> SHOW VARIABLES LIKE '%_connection';
    +--------------------------+-------------------+
    | Variable_name            | Value             |
    +--------------------------+-------------------+
    | character_set_connection | latin5            |
    | collation_connection     | latin5_turkish_ci |
    +--------------------------+-------------------+
    
    mysql> SHOW VARIABLES LIKE '%_connection';
    +--------------------------+-------------------+
    | Variable_name            | Value             |
    +--------------------------+-------------------+
    | character_set_connection | latin1            |
    | collation_connection     | latin1_swedish_ci |
    +--------------------------+-------------------+
    
  • SQL_AUTO_IS_NULL = {0 | 1}

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

    WHERE auto_increment_column IS NULL
    

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

  • SQL_BIG_SELECTS = {0 | 1}

    If set to 0, MySQL aborts SELECT 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 max_join_size). This is useful when an inadvisable WHERE statement has been issued. The default value for a new connection is 1, which allows all SELECT statements.

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

  • SQL_BUFFER_RESULT = {0 | 1}

    If set to 1, SQL_BUFFER_RESULT forces results from SELECT 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.

  • SQL_LOG_BIN = {0 | 1}

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

  • SQL_LOG_OFF = {0 | 1}

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

  • SQL_LOG_UPDATE = {0 | 1}

    This variable is deprecated, and is mapped to SQL_LOG_BIN.

  • SQL_NOTES = {0 | 1}

    If set to 1 (the default), warnings of Note level are recorded. If set to 0, Note 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. SQL_NOTES was added in MySQL 5.0.3.

  • SQL_QUOTE_SHOW_CREATE = {0 | 1}

    If set to 1 (the default), the server quotes identifiers for SHOW CREATE TABLE and SHOW CREATE DATABASE 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, “SHOW CREATE TABLE Syntax”, and Section 13.5.4.4, “SHOW CREATE DATABASE Syntax”.

  • SQL_SAFE_UPDATES = {0 | 1}

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

  • SQL_SELECT_LIMIT = {value | DEFAULT}

    The maximum number of rows to return from SELECT 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 SQL_SELECT_LIMIT value of DEFAULT.

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

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

  • SQL_WARNINGS = {0 | 1}

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

  • TIMESTAMP = {timestamp_value | DEFAULT}

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

    SET TIMESTAMP affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE(). The server can be started with the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(), in which case SET TIMESTAMP affects both functions.

  • UNIQUE_CHECKS = {0 | 1}

    If set to 1 (the default), uniqueness checks for secondary indexes in InnoDB 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 InnoDB.

    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

SHOW 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 tbl_name [FROM db_name] [LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION STATUS [LIKE 'pattern']
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE STATUS [LIKE 'pattern']
SHOW [BDB] LOGS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TRIGGERS
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]

The SHOW 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 SHOW statement includes a LIKE 'pattern' part, 'pattern' is a string that can contain the SQL ‘%’ and ‘_’ wildcard characters. The pattern is useful for restricting statement output to matching values.

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

13.5.4.1. SHOW CHARACTER SET Syntax

SHOW CHARACTER SET [LIKE 'pattern']

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

mysql> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| 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 Maxlen column shows the maximum number of bytes required to store one character.

13.5.4.2. SHOW COLLATION Syntax

SHOW COLLATION [LIKE 'pattern']

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

mysql> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| 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 Default column indicates whether a collation is the default for its character set. Compiled indicates whether the character set is compiled into the server. Sortlen is related to the amount of memory required to sort strings expressed in the character set.

13.5.4.3. SHOW COLUMNS Syntax

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

SHOW COLUMNS 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 CREATE TABLE 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 FULL keyword causes the output to include the privileges you have as well as any per-column comments for each column.

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

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

SHOW FIELDS is a synonym for SHOW COLUMNS. You can also list a table's columns with the mysqlshow db_name tbl_name command.

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

13.5.4.4. SHOW CREATE DATABASE Syntax

SHOW CREATE {DATABASE | SCHEMA} db_name

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

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

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

SHOW CREATE DATABASE quotes table and column names according to the value of the SQL_QUOTE_SHOW_CREATE option. See Section 13.5.3, “SET Syntax”.

13.5.4.5. SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

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

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 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. SHOW CREATE TABLE Syntax

SHOW CREATE TABLE tbl_name

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

mysql> SHOW CREATE TABLE t\G
*************************** 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

SHOW CREATE TABLE quotes table and column names according to the value of the SQL_QUOTE_SHOW_CREATE option. See Section 13.5.3, “SET Syntax”.

13.5.4.7. SHOW CREATE VIEW Syntax

SHOW CREATE VIEW view_name

This statement shows a CREATE VIEW statement that creates the given view.

mysql> SHOW CREATE VIEW v;
+------+----------------------------------------------------+
| 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 Table and Create Table.

Use of SHOW CREATE VIEW requires the SHOW VIEW privilege and the SELECT privilege for the view in question.

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

13.5.4.8. SHOW DATABASES Syntax

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']

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

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

If the server was started with the --skip-show-database option, you cannot use this statement at all unless you have the SHOW DATABASES privilege.

13.5.4.9. SHOW ENGINE Syntax

SHOW ENGINE engine_name {LOGS | STATUS }

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

SHOW ENGINE BDB LOGS
SHOW ENGINE INNODB STATUS

SHOW ENGINE BDB LOGS displays status information about existing BDB log files. It returns the following fields:

  • File

    The full path to the log file.

  • Type

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

  • Status

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

SHOW ENGINE INNODB STATUS displays extensive information about the state of the InnoDB storage engine.

The InnoDB Monitors provide additional information about InnoDB processing. See Section 14.2.11.1, “SHOW ENGINE INNODB STATUS and the InnoDB Monitors”.

Older (and now deprecated) synonyms for these statements are SHOW [BDB] LOGS and SHOW INNODB STATUS.

13.5.4.10. SHOW ENGINES Syntax

SHOW [STORAGE] ENGINES

SHOW 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. SHOW TABLE TYPES is a deprecated synonym.

mysql> SHOW ENGINES\G
*************************** 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 Support value indicates whether the particular storage engine is supported, and which is the default engine. For example, if the server is started with the --default-table-type=InnoDB option, the Support value for the InnoDB row has the value DEFAULT. See Chapter 14, Storage Engines and Table Types.

13.5.4.11. SHOW ERRORS Syntax

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

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

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

The SHOW COUNT(*) ERRORS statement displays the number of errors. You can also retrieve this number from the error_count variable:

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

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

13.5.4.12. SHOW GRANTS Syntax

SHOW GRANTS FOR user

This statement lists the GRANT 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 GRANT statement; for example, 'jeffrey'@'localhost'. The user and host parts of the account name correspond to the User and Host column values of the user table row for the account.

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| 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 SHOW GRANTS FOR CURRENT_USER (or any of the equivalent syntaxes) is used in DEFINER context, such as within a stored procedure that is defined with SQL SECURITY DEFINER), the grants displayed are those of the definer and not the invoker.

SHOW GRANTS 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 SHOW GRANTS will not display them.

13.5.4.13. SHOW INDEX Syntax

SHOW INDEX FROM tbl_name [FROM db_name]

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

SHOW INDEX returns the following fields:

  • Table

    The name of the table.

  • Non_unique

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

  • Key_name

    The name of the index.

  • Seq_in_index

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

  • Column_name

    The column name.

  • Collation

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

  • Cardinality

    An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality 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.

  • Sub_part

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

  • Packed

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

  • Null

    Contains YES if the column may contain NULL. If not, the column contains NO as of MySQL 5.0.3, and '' before that.

  • Index_type

    The index method used (BTREE, FULLTEXT, HASH, RTREE).

  • Comment

    Various remarks.

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

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

SHOW KEYS is a synonym for SHOW INDEX. You can also list a table's indexes with the mysqlshow -k db_name tbl_name command.

13.5.4.14. SHOW INNODB STATUS Syntax

SHOW INNODB STATUS

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

13.5.4.15. SHOW LOGS Syntax

SHOW [BDB] LOGS

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

13.5.4.16. SHOW OPEN TABLES Syntax

SHOW OPEN TABLES [FROM db_name] [LIKE 'pattern']

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

SHOW OPEN TABLES returns the following fields:

  • Database

    The database containing the table.

  • Table

    The table name.

  • In_use

    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.

  • Name_locked

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

The FROM and LIKE clauses may be used as of MySQL 5.0.12.

13.5.4.17. SHOW PRIVILEGES Syntax

SHOW PRIVILEGES

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> SHOW PRIVILEGES\G
*************************** 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. SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS Syntax

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

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> SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 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 ROUTINES table in INFORMATION_SCHEMA. See Section 20.14, “The INFORMATION_SCHEMA ROUTINES Table”.

13.5.4.19. SHOW PROCESSLIST Syntax

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the PROCESS 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, “KILL Syntax”. If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info 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 SUPER 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 SHOW PROCESSLIST 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:

  • Id

    The connection identifier.

  • User

    The MySQL user who issued the statement. If this is system user, 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 system user, there is no host specified in the Host column.

  • Host

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

  • db

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

  • Command

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

    The Command value may be one of the following: Binlog Dump, Change user, Close stmt, Connect, Connect Out, Create DB, Debug, Delayed insert, Drop DB, Error, Execute, Fetch, Field List, Init DB, Kill, Long Data, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time

  • Time

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

  • State

    An action, event, or state, which can be one of the following: After create, Analyzing, Changing master, Checking master version, Checking table, Connecting to master, Copying to group table, Copying to tmp table, Creating delayed handler, Creating index, Creating sort index, Creating table from master dump, Creating tmp table, Execution of init_command, FULLTEXT initialization, Finished reading one binlog; switching to next binlog, Flushing tables, Killed, Killing slave, Locked, Making temp file , Opening master dump table, Opening table, Opening tables, Processing request, Purging old relay logs, Queueing master event to the relay log, Reading event from the relay log, Reading from net, Reading master dump table data, Rebuilding the index on master dump table, Reconnecting after a failed binlog dump request, Reconnecting after a failed master event read, Registering slave on master, Removing duplicates, Reopen tables, Repair by sorting, Repair done, Repair with keycache, Requesting binlog dump, Rolling back, Saving state, Searching rows for update, Sending binlog event to slave, Sending data, Sorting for group, Sorting for order, Sorting index, Sorting result, System lock, Table lock, Thread initialized, Updating, User lock, Waiting for INSERT, Waiting for master to send event, Waiting for master update, Waiting for slave mutex on exit, Waiting for table, Waiting for tables, Waiting for the next event in relay log, Waiting on cond, Waiting to finalize termination, Waiting to reconnect after a failed binlog dump request, Waiting to reconnect after a failed master event read, Writing to net, allocating local table, cleaning up, closing tables, converting HEAP to MyISAM, copy to tmp table, creating table, deleting from main table, deleting from reference tables, discard_or_import_tablespace, end, freeing items, got handler lock, got old table, info, init, insert, logging slow query, login, preparing, purging old relay logs, query end, removing tmp table, rename, rename result table, reschedule, setup, starting slave, statistics, storing row into queue, unauthenticated user, update, updating, updating main table, updating reference tables, upgrading lock, waiting for delay_list, waiting for handler insert, waiting for handler lock, waiting for handler open

    The most common State values are described in the rest of this section. Most of the other State 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 SHOW PROCESSLIST statement, the value of State is NULL.

  • Info

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

Some State values commonly seen in the output from SHOW PROCESSLIST:

  • Checking table

    The thread is performing a table check operation.

  • Closing tables

    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.

  • Connect Out

    A replication slave is connecting to its master.

  • Copying to group table

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

  • Copying to tmp table

    The server is copying to a temporary table in memory.

  • Copying to tmp table on disk

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

  • Creating tmp table

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

  • deleting from main table

    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.

  • deleting from reference tables

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

  • Flushing tables

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

  • FULLTEXT initialization

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

  • Killed

    Someone has sent a KILL 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.

  • Locked

    The query is locked by another query.

  • Sending data

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

  • Sorting for group

    The thread is doing a sort to satisfy a GROUP BY.

  • Sorting for order

    The thread is doing a sort to satisfy a ORDER BY.

  • Opening tables

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

  • Reading from net

    The server is reading a packet from the network.

  • Removing duplicates

    The query was using SELECT DISTINCT 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.

  • Reopen table

    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.

  • Repair by sorting

    The repair code is using a sort to create indexes.

  • Repair with keycache

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

  • Searching rows for update

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

  • Sleeping

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

  • statistics

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

  • System lock

    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 --skip-external-locking option.

  • unauthenticated user

    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.

  • Upgrading lock

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

  • Updating

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

  • updating main table

    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.

  • updating reference tables

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

  • User Lock

    The thread is waiting on a GET_LOCK().

  • Waiting for tables

    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 FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

  • waiting for handler insert

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

  • Writing to net

    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. SHOW STATUS Syntax

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

SHOW STATUS 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> SHOW STATUS;
+--------------------------+------------+
| 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 LIKE clause, the statement displays only rows for those variables with names that match the pattern:

mysql> SHOW STATUS LIKE 'Key%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| Key_blocks_used    | 14955    |
| Key_read_requests  | 96854827 |
| Key_reads          | 162040   |
| Key_write_requests | 7589728  |
| Key_writes         | 3813196  |
+--------------------+----------+

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

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

Note: Before MySQL 5.0.2, SHOW STATUS 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 SHOW STATUS statement that will retrieve global status values for all versions of MySQL, write it like this:

SHOW /*!50002 GLOBAL */ STATUS;

13.5.4.21. SHOW TABLE STATUS Syntax

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

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

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

SHOW TABLE STATUS returns the following fields:

  • Name

    The name of the table.

  • Engine

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

  • Version

    The version number of the table's .frm file.

  • Row_format

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

  • Rows

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

    The Rows value is NULL for tables in the INFORMATION_SCHEMA database.

  • Avg_row_length

    The average row length.

  • Data_length

    The length of the data file.

  • Max_data_length

    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.

  • Index_length

    The length of the index file.

  • Data_free

    The number of allocated but unused bytes.

  • Auto_increment

    The next AUTO_INCREMENT value.

  • Create_time

    When the table was created.

  • Update_time

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

  • Check_time

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

  • Collation

    The table's character set and collation.

  • Checksum

    The live checksum value (if any).

  • Create_options

    Extra options used with CREATE TABLE.

  • Comment

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

In the table comment, InnoDB 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 MEMORY tables, the Data_length, Max_data_length, and Index_length 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 NDB Cluster tables, the output of this statement shows appropriate values for the Avg_row_length and Data_length columns, with the exception that BLOB columns are not taken into account. In addition, the number of replicas is now shown in the Comment column (as number_of_replicas).

For views, all the fields displayed by SHOW TABLE STATUS are NULL except that Name indicates the view name and Comment says view.

13.5.4.22. SHOW TABLES Syntax

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

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

Before MySQL 5.0.1, the output from SHOW TABLES 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 FULL modifier is supported such that SHOW FULL TABLES displays a second output column. Values for the second column are BASE TABLE for a table and VIEW for a view.

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

13.5.4.23. SHOW TRIGGERS Syntax

SHOW TRIGGERS [FROM db_name] [LIKE expr]

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

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

mysql> SHOW TRIGGERS LIKE 'acc%'\G
*************************** 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 LIKE clause with SHOW TRIGGERS, the expression to be matched (expr) is compared with the name of the table on which the trigger is declared, and not with the name of the trigger:

mysql> SHOW TRIGGERS LIKE 'ins%';
Empty set (0.01 sec)

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

  • Trigger

    The name of the trigger.

  • Event

    The event that causes trigger activation: one of 'INSERT', 'UPDATE', or 'DELETE'.

  • Table

    The table for which the trigger is defined.

  • Statement

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

  • Timing

    One of the two values 'BEFORE' or 'AFTER'.

  • Created

    Currently, the value of this column is always NULL.

  • sql_mode

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

  • Definer

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

You must have the SUPER privilege to execute SHOW TRIGGERS.

See also Section 20.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

13.5.4.24. SHOW VARIABLES Syntax

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

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

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

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 SET statement. See Section 5.2.3, “Using System Variables”, and Section 13.5.3, “SET 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> SHOW VARIABLES;
+---------------------------------+-------------------------------------+
| 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 LIKE 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 LIKE 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 LIKE 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. SHOW WARNINGS Syntax

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

SHOW 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, SHOW ERRORS, shows only the errors. See Section 13.5.4.11, “SHOW ERRORS Syntax”.

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

The SHOW COUNT(*) WARNINGS statement displays the total number of errors, warnings, and notes. You can also retrieve this number from the warning_count variable:

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

The value of warning_count might be greater than the number of messages displayed by SHOW WARNINGS if the max_error_count 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 LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.7, “SELECT 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 mysql_warning_count(). See Section 22.2.3.71, “mysql_warning_count().

Warnings are generated for statements such as LOAD DATA INFILE and DML statements such as INSERT, UPDATE, CREATE TABLE, and ALTER TABLE.

The following DROP TABLE statement results in a note:

mysql> DROP TABLE IF EXISTS no_such_table;
mysql> SHOW WARNINGS;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1051 | Unknown table 'no_such_table' |
+-------+------+-------------------------------+

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

mysql> CREATE TABLE t1 (a TINYINT NOT NULL, b CHAR(4)) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS\G
*************************** 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> INSERT INTO t1 VALUES(10,'mysql'),(NULL,'test'),
    -> (300,'Open Source');
Query OK, 3 rows affected, 4 warnings (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 4

mysql> SHOW WARNINGS\G
*************************** 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 max_error_count system variable. By default, its value is 64. To change the number of messages you want stored, change the value of max_error_count. In the following example, the ALTER TABLE statement produces three warning messages, but only one is stored because max_error_count has been set to 1:

mysql> SHOW VARIABLES LIKE 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SET max_error_count=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t1 MODIFY b CHAR;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

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

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

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

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

13.5.5. Other Administrative Statements

13.5.5.1. CACHE INDEX Syntax

CACHE INDEX
  tbl_index_list [, tbl_index_list] ...
  IN key_cache_name

tbl_index_list:
  tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]

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

The following statement assigns indexes from the tables t1, t2, and t3 to the key cache named hot_cache:

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| 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 CACHE INDEX 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 CACHE INDEX statement can be created by setting its size with a parameter setting statement or in the server parameter settings. For example:

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

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> CACHE INDEX t1 IN non_existent_cache;
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. FLUSH Syntax

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

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

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

flush_option can be any of the following:

  • HOSTS

    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 Host 'host_name' is blocked. When more than max_connect_errors 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, “Host 'host_name' is blocked. You can start mysqld with --max_connect_errors=999999999 to avoid this error message.

  • DES_KEY_FILE

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

  • LOGS

    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 SIGHUP signal to the mysqld server (except on some Mac OS X 10.3 versions where mysqld ignores SIGHUP and SIGQUIT).

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

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

  • PRIVILEGES

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

  • QUERY CACHE

    Defragment the query cache to better utilize its memory. FLUSH QUERY CACHE does not remove any queries from the cache, unlike RESET QUERY CACHE.

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

  • STATUS

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

  • {TABLE | TABLES} [tbl_name [, tbl_name] ...]

    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. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

  • TABLES WITH READ LOCK

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

  • USER_RESOURCES

    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. FLUSH USER_RESOURCES does not apply to the limit on maximum simultaneous connections. See Section 13.5.1.3, “GRANT Syntax”.

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

Note: FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK 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 flush-hosts, flush-logs, flush-privileges, flush-status, or flush-tables commands.

Using FLUSH statements within stored functions or triggers is not supported in MySQL 5.0. However, you may use FLUSH 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, “RESET Syntax”, for information about how the RESET statement is used with replication.

13.5.5.3. KILL Syntax

KILL [CONNECTION | QUERY] thread_id

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

In MySQL 5.0.0, KILL allows the optional CONNECTION or QUERY modifier:

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

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

If you have the PROCESS privilege, you can see all threads. If you have the SUPER 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 KILL 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 KILL, 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 SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.

  • During ALTER TABLE, 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 UPDATE or DELETE 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.

  • GET_LOCK() aborts and returns NULL.

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

  • If the thread is in the table lock handler (state: Locked), 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 REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM 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. LOAD INDEX INTO CACHE Syntax

LOAD INDEX INTO CACHE
  tbl_index_list [, tbl_index_list] ...

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

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

The IGNORE LEAVES 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 t1 and t2:

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| 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 t1. It preloads only blocks for the non-leaf nodes from t2.

The syntax of LOAD INDEX INTO CACHE 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. RESET Syntax

RESET reset_option [, reset_option] ...

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

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

reset_option can be any of the following: