Table and Server Administration Statements and Functions - SQL Statements and Functions - MySQL in a Nutshell (2008)

MySQL in a Nutshell (2008)

Part II. SQL Statements and Functions

Chapter 7. Table and Server Administration Statements and Functions

The following SQL statements are covered in this chapter:

ALTER SERVER, ANALYZE TABLE, BACKUP TABLE, CACHE INDEX, CHECK TABLE, CHECKSUM TABLE, CREATE SERVER, FLUSH, KILL, LOAD INDEX INTO CACHE, LOCK TABLES, OPTIMIZE TABLE, REPAIR TABLE, RESET, RESTORE TABLE, SET, SHOW ENGINE, SHOW ENGINES, SHOW OPEN TABLES, SHOW PLUGINS, SHOW PROCESSLIST, SHOW STATUS, SHOW TABLE STATUS, SHOW VARIABLES, UNLOCK TABLES.

The following functions are also covered in this chapter as they relate to data manipulation:

CONNECTION_ID(), GET_LOCK(), IS_FREE_LOCK(), IS_USED_LOCK(), RELEASE_LOCK(), UUID(), VERSION().

Statements and Clauses in Alphabetical Order

The following is a list of MySQL statements and clauses related to table and server administration, in alphabetical order. To understand how this book presents SQL syntax and describes SQL statements, as well as for information related to examples, please see the introduction to Part II. The examples in this chapter involve a fictitious database for a computer consulting firm that maintains work requests for computer maintenance. Some examples involve a fictitious database of a vendor.

Name

ALTER SERVER

Synopsis

ALTER SERVER server

OPTIONS

({ HOST host

| DATABASE database

| USER user

| PASSWORD password

| SOCKET socket

| OWNER owner

| PORT port_number }, ...)

Use this statement to change the settings for a server created for a FEDERATE storage engine. Servers are created with the CREATE SERVER statement. See the description of that statement later in this chapter for more information on the options. The SUPER privilege is required to be able to use this statement. Here is an example:

ALTER SERVER testing

OPTIONS(USER 'test_user2');

Name

ANALYZE TABLE

Synopsis

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

Use this statement to store information that can be useful later when the MySQL optimizer chooses the order for consulting indexes during a query. Multiple tables can be specified in a comma-separated list. The statement works on MyISAM and InnoDB tables. Unless theNO_WRITE_TO_BINLOG option is given, the statement is written to the binary log file and will be executed by slaves if using replication. The LOCAL option is synonymous with this option. For MyISAM tables, this statement places a read lock on the tables; for InnoDB, a write lock. This statement requires SELECT and INSERT privileges. Here is an example:

ANALYZE TABLE workreq;

+----------------------+---------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------------+---------+----------+----------+

| workrequests.workreq | analyze | status | OK |

+----------------------+---------+----------+----------+

The message type in the results can be status, error, info, or warning. If the table hasn’t changed since it was last analyzed, the message text will read, “Table is already up to date” and the table won’t be analyzed.

This statement is equivalent to using myisamchk --analyze at the command line for MyISAM tables. To analyze all tables (MyISAM and InnoDB), you can use the mysqlcheck utility from the command line like so:

mysqlcheck --user=russell -p --analyze --all-databases

If you want to see the stored key distribution that the ANALYZE TABLE statement creates, execute the SHOW INDEXES statement.

Name

BACKUP TABLE

Synopsis

BACKUP TABLE table[, ...] TO '/path'

This statement makes a backup copy of a MyISAM table. However, it has been deprecated because it does not work reliably. It’s recommended that you use mysqlhotcopy (see Chapter 16) until this statement is replaced.

You can specify additional tables in a comma-separated list. The absolute path to the directory to which MySQL is to copy files appears within quotes after the TO keyword.

The statement copies each table’s .frm file and .MYD file, which contain the table structure and the table data, respectively. The .MYI file containing the index is not copied, but it will be rebuilt with the RESTORE TABLE statement when restoring the table. Here is an example:

BACKUP TABLE clients TO '/tmp/backup';

+----------------------+--------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------------+--------+----------+----------+

| workrequests.clients | backup | status | OK |

+----------------------+--------+----------+----------+

If the backup succeeds, the results will look like the preceding output and two files will be created for each table backed up: a .frm file and a .MYD file. If MySQL does not have the filesystem privileges necessary to write to the backup directory, or if a file with the same name is already in the directory, the backup will fail. In that case, the results set will include one row with an error message type and another with a status type and the message text stating, “Operation failed.”

Name

CACHE INDEX

Synopsis

CACHE INDEX table[[INDEX|KEY] (index, ...), ...] IN cache

This statement tells MySQL to cache the given indexes to a specific index cache, which can be created with the SET GLOBAL statement. This statement is used only on MyISAM tables. Multiple tables may be listed in a comma-separated list. To specify only certain indexes of a table, give them in a comma-separated list in parentheses after the table name. The INDEX or KEY keyword may be given for clarity and compatibility with other database products. Note that the naming of specific indexes for a table is ignored in the current versions of MySQL; the option is for a future release. For now, all indexes are assigned to the named cache, which is the same as specifying no indexes.

To create an additional cache, issue a SET GLOBAL statement with the key_buffer_size variable like this:

SET GLOBAL my_cache.key_buffer_size = 100*1024;

CACHE INDEX workreq, clients IN my_cache \G

*************************** 1. row ***************************

Table: workrequests.workreq

Op: assign_to_keycache

Msg_type: status

Msg_text: OK

*************************** 2. row ***************************

Table: workrequests.clients

Op: assign_to_keycache

Msg_type: status

Msg_text: OK

In this example, the first line creates a cache called my_cache with a buffer size of 100 megabytes. The second line assigns the indexes for the two tables named to my_cache. As long as this cache exists, all queries by all users will use this cache. If you attempt to create a cache index without setting the global variable first, you will receive an error stating that it’s an unknown key cache. If the key cache is eliminated for any reason, the indexes will be assigned back to the default key cache for the server.

Name

CHECK TABLE

Synopsis

CHECK TABLE table[, ...] [CHANGED|QUICK|FAST|MEDIUM|EXTENDED|FOR UPGRADE]

Use this statement to check tables for errors; as of version 5.1.9 of MySQL, it works with the MyISAM, InnoDB, ARCHIVE, and CSV storage engines. If errors are discovered, you should run the REPAIR TABLE statement to repair the table. Multiple tables may be given in a comma-separated list. This statement requires SELECT privileges.

There are several ways to control checking, specified after the list of tables:

CHANGED

Checks only tables that have been changed since the last check.

QUICK

Checks tables for errors, but won’t scan individual rows for linking problems.

FAST

Checks only tables that have not been closed properly.

MEDIUM

Determines the key checksum for the rows and compares the results against the checksum for the keys. This option also checks rows to ensure that links were deleted properly.

EXTENDED

Thoroughly checks each row for errors. It takes a long time to complete.

FOR UPGRADE

Checks a table against the version of MySQL in use. If the table was created from an earlier version and there have been changes to the new version that make the table incompatible, the statement will then begin the EXTENDED method to thoroughly check the table. If it’s successful, it will note that the table has already been checked so that future checks can avoid the time-consuming check. This option is available starting with version 5.1.7 of MySQL.

Here is an example of how you can use this statement:

CHECK TABLE workreq MEDIUM;

+----------------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------------+-------+----------+----------+

| workrequests.workreq | check | status | OK |

+----------------------+-------+----------+----------+

If an error is found with an InnoDB table, the server is shut down to prevent more problems. Check the error log for details to resolve the problem.

Name

CHECKSUM TABLE

Synopsis

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

This statement returns a MyISAM table’s live checksum value, a value that can be optionally maintained to improve a table’s repairability. To enable live checksum for a table, use the CREATE TABLE or ALTER TABLE statements with a table option of CHECKSUM=1.

Multiple tables may be given in a comma-separated list. If the QUICK option is employed, the live table checksum will be returned, if available. If not, NULL will be returned. Normally one would use the QUICK option when the table is probably fine. The EXTENDED option instructs the server to check each row. You should use this option only as a last resort. If no option is specified, the QUICK option is the default, if available. If not, the EXTENDED option is the default. The checksum value can be different if the row format changes, which can happen between versions of MySQL. Here is an example of this statement’s use and its results:

CHECKSUM TABLE workreq;

+----------------------+-----------+

| Table | Checksum |

+----------------------+-----------+

| workrequests.workreq | 195953487 |

+----------------------+-----------+

Name

CREATE SERVER

Synopsis

CREATE SERVER 'server'

FOREIGN DATA WRAPPER mysql

OPTIONS

({ HOST host

| DATABASE database

| USER user

| PASSWORD password

| SOCKET socket

| OWNER owner

| PORT port_number }, ...)

This statement creates a server for use by the FEDERATED storage engine. The server created is registered in the server table in the mysql database. The server name given cannot exceed 63 characters and is case-insensitive. The only acceptable wrapper name is mysql. Multiple options may be given, separated by commas. The PORT option requires a numeric literal, whereas the other options require character literals. So don’t put the port number within quotes. SUPER privilege is required to be able to use this statement. Here is an example of this statement:

CREATE SERVER testing

FOREIGN DATA WRAPPER mysql

OPTIONS (USER 'test_user', HOST '10.1.1.100',

DATABASE 'test', PORT 3307);

SELECT * FROM mysql.servers

WHERE Server_name = 'testing' \G

*************************** 1. row ***************************

Server_name: testing

Host: 10.1.1.100

Db: test

Username: test_user

Password:

Port: 3307

Socket:

Wrapper: mysql

Owner:

CREATE TABLE table1 (col_id INT, col_1 VARCHAR(25))

ENGINE=FEDERATED CONNECTION='testing';

A server created with this statement can be altered with the ALTER SERVER statement. Once created, servers can be accessed by setting the ENGINE clause in either the CREATE TABLE statement or the same clause of the ALTER TABLE statement for existing tables.

Name

FLUSH

Synopsis

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

Options:

DES_KEY_FILE, HOSTS, LOGS, PRIVILEGES, QUERY_CACHE,

STATUS, TABLE, TABLES, TABLES WITH READ LOCK, USER_RESOURCES

Use this statement to clear temporary caches in MySQL. It requires RELOAD privileges. Multiple options may be given in a comma-separated list.

To prevent this statement from writing to the binary log file, include the NO_WRITE_TO_BINLOG keyword or its alias, LOCAL. The DES_KEY_FILE option reloads the DES encryption file. HOSTS clears the hosts cache, which is used to minimize host/IP address lookups. The hosts cache may need to be flushed if a host has been blocked from accessing the server. LOGS is used to close all of the log files and reopen them. The PRIVILEGES option reloads the grant table for users. This is necessary if the user table in the mysql database is modified manually, without a GRANTstatement. QUERY CACHE instructs the server to defragment the query cache. The STATUS option resets the status variables that report information about the caches.

The TABLE option, followed by one or more table names, forces the given tables to be closed. This will terminate any active queries on the given tables. The TABLES option, without any table names listed, causes all tables to be closed, all queries to be terminated, and the query cache to be flushed. This option is actually the same as TABLE with no table name.

Use the TABLES WITH READ LOCK option to close all tables and lock them with a global read lock. This should be considered when dealing with transactional tables and implicit commits of changes. This option will allow users to view the data, but not to update it or to insert records. The lock will remain in place until the UNLOCK TABLES statement is executed.

USER_RESOURCES resets all user resources. You can use this when users have been locked out due to exceeding usage limits.

The mysqladmin utility may be used to execute this statement with several of its options. See Chapter 16 for information on this utility.

Two options for this statement have been deprecated: MASTER and SLAVE. RESET MASTER and RESET SLAVE should be used instead.

As of version 5.1 of MySQL, the FLUSH statement cannot be called by a stored function or a trigger, although it can be included in a stored procedure.

Name

KILL

Synopsis

KILL [CONNECTION|QUERY] thread

Use this statement to terminate a client connection to MySQL. You can use the SHOW PROCESSLIST statement to obtain a connection thread identifier for use in this statement. As of version 5 of MySQL, you can use CONNECTION or QUERY keywords to distinguish between terminating a connection or terminating just the current query associated with the given connection.

Some processes cannot be terminated immediately. Instead, this statement flags the process for termination. The system may not check the flag until the process is completed. This will occur with statements such as REPAIR TABLE. Besides, you shouldn’t attempt to terminate the execution of the REPAIR TABLE or the OPTIMIZE TABLE statements. That will corrupt a MyISAM table. The utility mysqladmin with the options processlist and KILL may be used from the command line to execute these related statements.

Here is an example of the SHOW PROCESSLIST and the KILL statements used together:

SHOW PROCESSLIST \G

...

Id: 14397

User: reader

Host: localhost

db: russell_dyer

Command: Query

Time: 7

State: Sending data

Info: SELECT COUNT(*) AS hits

FROM apache_log

WHERE SUBDATE(NOW(), INT....

KILL QUERY 14397;

The results of the SHOW PROCESSLIST are truncated. Using the thread identifier 14397 from the results, the KILL statement is used with the QUERY keyword to terminate the SQL statement that’s running, without terminating the client connection. If the CONNECTION keyword or no keyword is given, the entire connection is terminated. In that case, if the client attempts to issue another SQL statement, it receives a 2006 error message stating that the MySQL server has gone away. Then it typically will try to reconnect to the server, establish a new thread, and run the requested query.

Name

LOAD INDEX INTO CACHE

Synopsis

LOAD INDEX INTO CACHE

table [[INDEX|KEY] (index[, ...)] [IGNORE LEAVES]

[, ...]

Use this statement to preload a table’s index into a given key cache for a MyISAM table. The syntax allows one or more indexes to be specified in a comma-separated list in parentheses, in order to preload just the specified indexes, but presently MySQL simply loads all the indexes for the table into the cache. The keywords INDEX and KEY are interchangeable and optional; they do not affect the results. The IGNORE LEAVES clause instructs MySQL not to preload leaf nodes of the index. Here is an example of how you can use this statement:

LOAD INDEX INTO CACHE workreq;

+----------------------+--------------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------------+--------------+----------+----------+

| workrequests.workreq | preload_keys | status | OK |

+----------------------+--------------+----------+----------+

Name

LOCK TABLES

Synopsis

LOCK TABLES table [AS alias]

{READ [LOCAL]|[[LOW_PRIORITY] WRITE]} [, ...]

Use this statement to lock the given tables for exclusive use by the current connection thread. A READ lock allows the locked tables to be read by all threads, but it does not allow writes to the tables, even by the thread that locked them. A READ LOCAL lock allows all threads to read the tables that are locked while the locking connection can execute INSERT statements. Until the lock is released, though, direct data manipulation by command-line utilities should be avoided. A WRITE lock prohibits other threads from reading from or writing to locked tables, but it permits reads and writes by the locking thread. SQL statements for tables that are locked with the WRITE option have priority over statements involving tables with a READ lock. However, the LOW_PRIORITY keyword may be given before the WRITE to instruct the server to wait until there are no queries on the tables being locked.

Only locked tables may be accessed by a locking thread. Therefore, all tables to be used must be locked. To illustrate this, assume a new programmer has been hired. The programmer’s information must be added to the programmers table. The wk_schedule table that contains the records for scheduling work also needs to be adjusted to assign work to the new programmer and away from others. Here is how you might lock the relevant tables:

LOCK TABLES workreq READ, programmers READ LOCAL,

wk_schedule AS work LOW_PRIORITY WRITE;

In this example, the workreq table is locked with a READ keyword so that no new work requests may be added while the table for the programmers’ work schedules is being updated, but the work requests may still be viewed by other users. The programmers table is locked for writing with the READ LOCAL keyword, because one record needs to be inserted for the new programmer’s personal information. The wk_schedule table is locked for exclusive use by the current thread.

For convenience, you can give a table an alias with the AS keyword. In the example, the wk_schedule table is referred to as work for subsequent SQL statements until the tables are unlocked. During this time, the thread can refer to the table only by this name in all other SQL statements.

You can release locks with the UNLOCK TABLES statements. A START TRANSACTION statement also unlocks tables, as does the issuing of another TABLE LOCKS statement. Therefore, all tables to be locked should be named in one statement. Additional tables can be added to the end of theTABLE LOCKS statement in a comma-separated list.

You can lock all tables with a FLUSH TABLES WITH READ LOCK statement. You can use the GET_LOCK() and RELEASE_LOCK() functions as alternatives to the LOCK TABLES and UNLOCK TABLES covered in this chapter.

Name

OPTIMIZE TABLE

Synopsis

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

Use this statement to optimize the data contained in a table. Optimization is useful when many rows have been deleted from a table. It’s also useful to run this statement periodically with a table that contains several variable-character-width columns (i.e., VARCHAR, BLOB, and TEXT columns). This statement generally works only with MyISAM, BDB, and InnoDB tables. It may work on other tables, however, if the mysqld daemon is started with the --skip-new option or the --safe-mode option. See Chapter 15 for more information on setting server startup options.

This statement also repairs some row problems and sort indexes. It temporarily locks the tables involved while optimizing. Multiple tables can be listed for optimization in a comma-separated list. To prevent the activities of this statement from being recorded in the binary log file, use theNO_WRITE_TO_BINLOG keyword or its alias, LOCAL. Here is an example of the statement’s use:

OPTIMIZE LOCAL TABLE workreq, clients;

+----------------------+----------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------------+----------+----------+----------+

| workrequests.workreq | optimize | status | OK |

| workrequests.clients | optimize | status | OK |

+----------------------+----------+----------+----------+

Here, two tables are optimized successfully and the activity is not written to the binary log file.

Name

REPAIR TABLE

Synopsis

REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE

table[, ...] [QUICK] [EXTENDED] [USE_FRM]

Use this statement to repair corrupted MyISAM tables. Multiple tables may be given in a comma-separated list. To prevent this statement from recording its activities in the binary log file, give the NO_WRITE_TO_BINLOG keyword or its LOCAL alias. The QUICK keyword instructs MySQL to repair the table indexes only. The EXTENDED keyword rebuilds the indexes one row at a time. This option takes longer, but it can be more effective, especially with rows containing duplicate keys.

Before running this statement, make a backup of the table. If a table continues to have problems, there may be other problems (e.g., filesystem problems) that you should consider. Here is an example of this statement:

REPAIR TABLE systems QUICK EXTENDED;

+----------------------+--------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------------+--------+----------+----------+

| workrequests.systems | repair | status | OK |

+----------------------+--------+----------+----------+

In this example, the repair is successful. This is indicated by the OK in the Msg_text field. If it is unsuccessful, you could try the USE_FRM option with this statement. That option will create a new index file (.MYI) using the table schema file (.frm). It won’t be able to determine the current value for AUTO_INCREMENT columns or for DELETE LINK, so it shouldn’t be used unless the original .MYI file is lost. Incidentally, if the MySQL server dies while the REPAIR TABLE statement is running, you should run the statement again as soon as the server is back up, before running any other SQL statements.

Name

RESET

Synopsis

RESET {MASTER|SLAVE|QUERY CACHE}[, ...]

Use this statement to reset certain server settings and files. It’s similar to the FLUSH statement, but more powerful for its specific uses. The RELOAD privilege is required to use it. Multiple options may be given in a comma-separated list. Currently, you can reset the MASTER, QUERY CACHE, and SLAVE options. See the RESET MASTER and the RESET SLAVE statements in Chapter 8 for detailed explanations of each option. The QUERY CACHE option clears the cache containing SQL query results.

Name

RESTORE TABLE

Synopsis

RESTORE TABLE table[, ...] FROM '/path'

This statement restores a table that was saved to the filesystem by the BACKUP TABLE statement. Multiple tables may be given in a comma-separated list. The absolute path to the directory containing the backup files must appear within quotes. If the tables already exist in the database, an error message will be generated and the restore will fail. If it’s successful, the table indexes will be built automatically. This is necessary because the BACKUP TABLE statement doesn’t back up the index files. Here is an example of this statement:

RESTORE TABLE clients, programmers FROM '/tmp/backup';

+--------------------------+---------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+--------------------------+---------+----------+----------+

| workrequests.clients | restore | status | OK |

+--------------------------+---------+----------+----------+

| workrequests.programmers | restore | status | OK |

+--------------------------+---------+----------+----------+

In this example, the statement is successful in restoring the .frm and .MYD files located in the backup directory and regenerating the .MYI files.

Name

SET

Synopsis

SET [GLOBAL|@@global.|SESSION|@@session.] variable = expression

This statement sets a system or user variable for global or session use. Global variables relate to all users. Session variables are available only to the connection thread that creates the variable. For system variables to be recognized as global, the GLOBAL keyword is used. Alternatively, the variable can be preceded by @@global. to signify that it is global. For system variables that are limited to the current session, use the SESSION keyword, or place @@session or just @@ immediately in front of the variable name. The default for variables is to limit them to the session, making them local. LOCAL and @@local are aliases for SESSION and @@session, respectively. Here are a couple of examples involving system variables, one using the keyword method and the other using the variable prefix method:

SET GLOBAL concurrent_insert = 1;

SET @@session.interactive_timeout=40000;

The first statement disables concurrent inserts without having to restart the server. The second statement changes the interactive timeout to a higher value than normal. This setting is for the current client connection only. For other clients, this variable will still contain the default value.

To see a list of system variables and their values, use the SHOW VARIABLES statement. For a description of these variables, see Appendix C. For examples involving user variables, see the description of the SET statement in Chapter 6.

Name

SHOW ENGINE

Synopsis

SHOW ENGINE engine {STATUS|MUTEX}

Use this statement to display details of the status of a given storage engine. This statement provides information on table and record locks for transactions, waiting locks, pending requests, buffer statistics and activity, and logs related to the engine.

Currently, the engines that may be given are INNODB, NDB, and NDBCLUSTER. These last two keywords are interchangeable. Prior to version 5.1.12 of MySQL, the option of BDB was permitted. In later versions, the BDB engine is not supported and a warning message is generated when it is used with this statement. The MUTEX option is available only for the InnoDB engine. For the NDB engine, an empty results set is returned if there are no operations at the time.

Name

SHOW ENGINES

Synopsis

SHOW [STORAGE] ENGINES

This statement lists the table types or storage engines available for the version of MySQL running on the server. It states which are disabled on the server and which are enabled, as well as which is the default type. It also provides comments on each type. The STORAGE keyword is optional and has no effect on the results. This SQL statement replaces SHOW TABLE TYPES, which produced the same results, but is deprecated. Here is an example of this statement:

SHOW ENGINES \G

*************************** 1. row ***************************

Engine: ndbcluster

Support: DISABLED

Comment: Clustered, fault-tolerant tables

Transactions: YES

XA: NO

Savepoints: NO

*************************** 2. row ***************************

Engine: MRG_MYISAM

Support: YES

Comment: Collection of identical MyISAM tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 3. row ***************************

Engine: BLACKHOLE

Support: YES

Comment: /dev/null storage engine (anything you write to it disappears)

Transactions: NO

XA: NO

Savepoints: NO

*************************** 4. row ***************************

Engine: CSV

Support: YES

Comment: CSV storage engine

Transactions: NO

XA: NO

Savepoints: NO

*************************** 5. row ***************************

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 6. row ***************************

Engine: FEDERATED

Support: YES

Comment: Federated MySQL storage engine

Transactions: YES

XA: NO

Savepoints: NO

*************************** 7. row ***************************

Engine: ARCHIVE

Support: YES

Comment: Archive storage engine

Transactions: NO

XA: NO

Savepoints: NO

*************************** 8. row ***************************

Engine: InnoDB

Support: YES

Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

*************************** 9. row ***************************

Engine: MyISAM

Support: DEFAULT

Comment: Default engine as of MySQL 3.23 with great performance

Transactions: NO

XA: NO

Savepoints: NO

9 rows in set (0.00 sec)

Name

SHOW OPEN TABLES

Synopsis

SHOW OPEN TABLES [FROM database] [LIKE 'pattern'|WHERE expression]

Use this statement to display a list of tables that are open, i.e., that are in the table cache. The list does not include any temporary tables. The LIKE clause can be used to limit the tables displayed by a naming pattern. Similarly, the WHERE clause may be used to refine the results set. Here is an example of this statement:

SHOW OPEN TABLES

FROM college LIKE '%student%';

+----------+--------------------+--------+-------------+

| Database | Table | In_use | Name_locked |

+----------+--------------------+--------+-------------+

| college | student_surveys | 0 | 0 |

| college | students | 0 | 0 |

| college | student_exams | 0 | 0 |

| college | student_exams_past | 0 | 0 |

+----------+--------------------+--------+-------------+

Name

SHOW PLUGINS

Synopsis

SHOW PLUGINS

Use this statement to display a list of plugins on the server. This statement is available as of version 5.1.5 of MySQL, but with the name SHOW PLUGIN. It was changed to SHOW PLUGINS as of version 5.1.9. Here is an example:

SHOW PLUGINS;

+------------+--------+----------------+---------+---------+

| Name | Status | Type | Library | License |

+------------+--------+----------------+---------+---------+

| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |

| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |

| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |

| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |

| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |

| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |

| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |

| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |

| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |

| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |

| ndbcluster | ACTIVE | STORAGE ENGINE | NULL | GPL |

+------------+--------+----------------+---------+---------+

Name

SHOW PROCESSLIST

Synopsis

SHOW [FULL] PROCESSLIST

This statement displays a list of connection threads running on the MySQL server. The statement requires SUPER privileges to be able to see all threads. Otherwise, only threads related to the current connection are shown. The FULL keyword shows the full text of the information field. Here is an example:

SHOW PROCESSLIST\G

*************************** 1. row ***************************

Id: 1

User: root

Host: localhost

db: workrequests

Command: Query

Time: 0

State: NULL

Info: SHOW PROCESSLIST

You can use this statement to determine a thread identification number to be used with the KILL statement.

Name

SHOW STATUS

Synopsis

SHOW [GLOBAL|LOCAL|SESSION] STATUS [LIKE 'pattern'|WHERE expression]

This statement displays status information and variables from the server. You can reduce the number of variables shown with the LIKE clause, based on a naming pattern for the variable name. Similarly, the WHERE clause may be used to refine the results set. Here is an example of how you can use this statement with the LIKE clause:

SHOW STATUS LIKE '%log%';

+------------------------------+-------+

| Variable_name | Value |

+------------------------------+-------+

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Com_show_binlog_events | 0 |

| Com_show_binlogs | 0 |

| Com_show_engine_logs | 0 |

| Innodb_log_waits | 0 |

| Innodb_log_write_requests | 0 |

| Innodb_log_writes | 1 |

| Innodb_os_log_fsyncs | 3 |

| Innodb_os_log_pending_fsyncs | 0 |

| Innodb_os_log_pending_writes | 0 |

| Innodb_os_log_written | 512 |

| Tc_log_max_pages_used | 0 |

| Tc_log_page_size | 0 |

| Tc_log_page_waits | 0 |

+------------------------------+-------+

The results show any system variable in which the variable name has the word log in it. This is a new server installation, so the results have small or zero values. If we wanted to eliminate the InnoDB logs from the results, we could use the WHERE clause like so:

SHOW STATUS

WHERE Variable_name LIKE '%log%'

AND Variable_name NOT LIKE '%Innodb%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Com_show_binlog_events | 0 |

| Com_show_binlogs | 0 |

| Com_show_engine_logs | 0 |

| Tc_log_max_pages_used | 0 |

| Tc_log_page_size | 0 |

| Tc_log_page_waits | 0 |

+------------------------+-------+

Notice that when using the WHERE clause, the field name in the results must be given. In this case, the field name Variable_name is given. You could also give the field name Value to limit the results to entries of a certain value or range of values:

SHOW GLOBAL STATUS

WHERE Variable_name LIKE '%log%'

AND Variable_name LIKE '%Innodb%'

AND Value > 100;

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Innodb_os_log_written | 512 |

+-----------------------+-------+

In this example, we are looking for log entries for InnoDB with values over 100. The results consist of just one entry.

You can change many variables at server startup using options for the MySQL server daemon. See Chapter 15 for more details. You can change some of them while the daemon is running with the SET statement, without having to restart the server. That statement is covered earlier in this chapter.

Name

SHOW TABLE STATUS

Synopsis

SHOW TABLE STATUS [FROM database] [LIKE 'pattern'|WHERE expression]

This statement displays status information on a set of tables from a database. To obtain the status of tables from a database other than the current default one, use the FROM clause. The results will include information on all of the tables of the database unless the LIKE clause is used to limit the tables displayed by a naming pattern. Similarly, the WHERE clause may be used to refine the results set. As an alternative to this statement, you can use the utility mysqlshow with the --status option, as described in Chapter 16. Here’s an example of this statement using the LIKE clause:

SHOW TABLE STATUS FROM workrequests LIKE 'workreq'\G

*************************** 1. row ***************************

Name: workreq

Engine: MyISAM

Version: 7

Row_format: Dynamic

Rows: 543

Avg_row_length: 983

Data_length: 534216

Max_data_length: 4294967295

Index_length: 6144

Data_free: 120

Auto_increment: 5772

Create_time: 2002-04-23 14:41:58

Update_time: 2004-11-26 16:01:46

Check_time: 2004-11-28 17:21:20

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

This example shows results for only one table because a specific table name is given in the LIKE clause without the % wildcard. To find a group of tables, but to limit the results more, you can use the WHERE clause. Here is an example:

SHOW TABLE STATUS FROM workrequests

WHERE Rows > 1000;

This example lists all tables from the given database that contain more than 1,000 rows of data. Notice that we’re using the field name Rows from the results set to limit the results. Any field name can be used in this way and multiple fields may be given, separated by the AND parameter of theWHERE clause.

As for the results themselves, most are obvious from their field names. The Row_format field can have a value of Compact, Compressed, Dynamic, Fixed, or Redundant. InnoDB tables are either Compact or Redundant. The Rows field gives an accurate count with MyISAM tables, but not with InnoDB.

The Data_length field gives the size of the datafile associated with the table. Max_data_length is the maximum size allowed for the datafile. These two values are estimates for MEMORY tables. The Auto_increment value shows the value for the column that uses AUTO_INCREMENT.

When used with views, this statement returns NULL values for almost all fields.

You can change some of these variables or table options using the ALTER TABLE statement in Chapter 5.

Name

SHOW VARIABLES

Synopsis

SHOW [GLOBAL|LOCAL|SESSION] VARIABLES [LIKE 'pattern'|WHERE expression]

This statement displays the system variables for the MySQL server. The SESSION keyword displays values for current sessions or connections. This is the default and is synonymous with LOCAL. The GLOBAL keyword shows variables that relate to new connections. You can limit the variables with the LIKE clause and a naming pattern for the variables. Similarly, the WHERE clause can be used to refine the results set. Here is an example of this statement with the LIKE clause:

SHOW GLOBAL VARIABLES LIKE 'version%';

+-------------------------+------------------------------+

| Variable_name | Value |

+-------------------------+------------------------------+

| version | 5.1.16-beta |

| version_comment | MySQL Community Server (GPL) |

| version_compile_machine | i686 |

| version_compile_os | pc-linux-gnu |

+-------------------------+------------------------------+

In this example, the variables shown are limited to global variables whose names begin with the word version. Suppose that we wanted to see only the two variables of these results that contain a numeric value. We could do this by using the WHERE clause like so:

SHOW GLOBAL VARIABLES

WHERE Variable_name LIKE 'version%'

AND Value REGEXP '[0-9]';

+-------------------------+-------------+

| Variable_name | Value |

+-------------------------+-------------+

| version | 5.1.16-beta |

| version_compile_machine | i686 |

+-------------------------+-------------+

Notice that, for the WHERE clause, we specify the field names of the results set: Variable_name and Value. In this case, we’re also using the LIKE and REGEXP string comparison functions to narrow the results.

You can change many of the variables at server startup with options for the MySQL server daemon. See Chapter 15 for more details. You can change some of them while the daemon is running with the SET statement, without having to restart the server. That statement is covered earlier in this chapter.

Name

UNLOCK TABLES

Synopsis

UNLOCK TABLES

Use this statement to unlock tables that were locked by the current connection thread with the LOCK TABLES statement or by FLUSH TABLES WITH READ LOCK. UNLOCK TABLES implicitly commits any active transactions if any tables were locked with LOCK TABLES. When performing a large amount of changes to data in MyISAM tables, it can be useful and faster to lock the tables first. This way the key cache isn’t flushed after each SQL statement. Instead, the server flushes the key cache when executing UNLOCK TABLES. Here is an example:

UNLOCK TABLES;

Functions in Alphabetical Order

The following is a list of MySQL functions related to the tasks in this chapter, in alphabetical order.

Name

CONNECTION_ID()

Synopsis

CONNECTION_ID()

This function returns the MySQL connection or thread identification number for the MySQL session. There are no arguments. Connection identifiers are unique. Here is an example:

SELECT CONNECTION_ID( );

+------------------+

| CONNECTION_ID( ) |

+------------------+

| 11266 |

+------------------+

Name

GET_LOCK()

Synopsis

GET_LOCK(string, seconds)

This function attempts to get a lock on the name given in the first argument. The number of seconds to attempt the lock is given in the second argument. If successful, it returns 1. If the function is unsuccessful because the attempt times out, it returns 0. If the lock fails due to an error of any kind, NULL is returned. The function RELEASE_LOCK() may be used to release a lock. A lock is also released when the same client issues another GET_LOCK() or when the client’s connection is terminated. Here is an example:

SELECT GET_LOCK('my_lock', 10);

+-------------------------+

| GET_LOCK('my_lock', 10) |

+-------------------------+

| 1 |

+-------------------------+

Name

IS_FREE_LOCK()

Synopsis

IS_FREE_LOCK(string)

Use this function to determine whether the name of the lock given in parentheses is free and available as a lock name. The function returns 1 if the lock name is free, and 0 if it’s not (because it is in use by another client). The function returns NULL if there is an error. Locks are created byGET_LOCK(). This function is available as of version 4.0.2 of MySQL. Here is an example:

SELECT IS_FREE_LOCK('my_lock');

+-------------------------+

| IS_FREE_LOCK('my_lock') |

+-------------------------+

| 0 |

+-------------------------+

The results here indicate that the lock is not free.

Name

IS_USED_LOCK()

Synopsis

IS_USED_LOCK(string)

This function determines whether the name given is already in use as a lock name. If the lock name is in use, it returns the connection identifier of the client holding the lock. It returns NULL if it is not in use. Locks are created by GET_LOCK(). This function is available as of version 4.1.0 of MySQL. Here is an example:

SELECT IS_USED_LOCK('my_lock');

+-------------------------+

| IS_USED_LOCK('my_lock') |

+-------------------------+

| 1 |

+-------------------------+

The results here indicate that the lock is in use and the connection identifier of the client is 1.

Name

RELEASE_LOCK()

Synopsis

RELEASE_LOCK(string)

This function releases a lock created by GET_LOCK(). The name of the lock is given in parentheses. If successful, 1 is returned; if unsuccessful, 0 is returned. If the lock specified does not exist, NULL is returned. Here is an example:

SELECT RELEASE_LOCK('my_lock');

+-------------------------+

| RELEASE_LOCK('my_lock') |

+-------------------------+

| 1 |

+-------------------------+

As an alternative to using SELECT, you can use the DO statement. In this case, no results are returned, but the lock is released:

DO RELEASE_LOCK('my_lock');

Name

UUID()

Synopsis

UUID()

This function returns a Universal Unique Identifier (UUID), a 128-bit number composed of five hexadecimal numbers. This number is intended to be unique per invocation and is based on values that are both temporal and spatial. There are no arguments for the function. It’s available as of version 4.1.2 of MySQL. Here is an example:

SELECT UUID( );

+----------------------------------------+

| UUID( ) |

+----------------------------------------+

| '8bde367a-caeb-0933-1031-7730g3321c32' |

+----------------------------------------+

The first three hexadecimal sets of numbers are based on the date and time of the execution of the statement. The fourth set is based on time regardless of daylight saving time. The last set is a unique number, an IEEE 802 node number related to the computer generating the number. For instance, for some operating systems it could be the network card’s Media Access Control (MAC) address.

Name

VERSION()

Synopsis

VERSION()

This function returns the MySQL server version. There are no arguments for the function. Here is an example:

SELECT VERSION( );

+-------------+

| VERSION( ) |

+-------------+

| 5.1.16-beta |

+-------------+