| 13.3. MySQL Transactional and Locking Statements MySQL supports local transactions (within a given client session) through statements such as SET autocommit, START TRANSACTION, COMMIT, and ROLLBACK. See Section 13.3.1, "START TRANSACTION, COMMIT, and ROLLBACK Syntax". XA transaction support enables MySQL to participate in distributed transactions as well. See Section 13.3.7, "XA Transactions". 13.3.1. START TRANSACTION, COMMIT, andROLLBACK SyntaxSTART TRANSACTION [WITH CONSISTENT SNAPSHOT]BEGIN [WORK]COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]SET autocommit = {0 | 1} These statements provide control over use of transactions: START TRANSACTION or BEGIN start a new transaction. COMMIT commits the current transaction, making its changes permanent. ROLLBACK rolls back the current transaction, canceling its changes. SET autocommit disables or enables the default autocommit mode for the current session.
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back. To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement: START TRANSACTION;SELECT @A:=SUM(salary) FROM table1 WHERE type=1;UPDATE table2 SET summary=@A WHERE type=1;COMMIT; With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state. You can also begin a transaction like this: START TRANSACTION WITH CONSISTENT SNAPSHOT; The WITH CONSISTENT SNAPSHOT option starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 14.3.9.2, "Consistent Nonlocking Reads". The WITH CONSISTENT SNAPSHOT option does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits consistent read (REPEATABLE READ or SERIALIZABLE). Important Many APIs used for writing MySQL client applications (such as JDBC) provide their own methods for starting transactions that can (and sometimes should) be used instead of sending a START TRANSACTION statement from the client. See Chapter 22, Connectors and APIs, or the documentation for your API, for more information. To disable autocommit mode explicitly, use the following statement: SET autocommit=0; After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB or NDBCLUSTER) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes. autocommit is a session variable and must be set for each session. To disable autocommit mode for each new connection, see the description of the autocommit system variable at Section 5.1.4, "Server System Variables". BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a transaction. START TRANSACTION is standard SQL syntax and is the recommended way to start an ad-hoc transaction. The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN ... END compound statement. The latter does not begin a transaction. See Section 13.6.1, "BEGIN ... END Compound-Statement Syntax". Note Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. Begin a transaction in this context with START TRANSACTION instead. The optional WORK keyword is supported for COMMIT and ROLLBACK, as are the CHAIN and RELEASE clauses. CHAIN and RELEASE can be used for additional control over transaction completion. The value of the completion_type system variable determines the default completion behavior. See Section 5.1.4, "Server System Variables". The AND CHAIN clause causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction. The RELEASE clause causes the server to disconnect the current client session after terminating the current transaction. Including the NO keyword suppresses CHAIN or RELEASE completion, which can be useful if the completion_type system variable is set to cause chaining or release completion by default. Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, "Statements That Cause an Implicit Commit", for more information. Beginning a transaction also causes table locks acquired with LOCK TABLES to be released, as though you had executed UNLOCK TABLES. Beginning a transaction does not release a global read lock acquired with FLUSH TABLES WITH READ LOCK. For best results, transactions should be performed using only tables managed by a single transaction-safe storage engine. Otherwise, the following problems can occur: If you use tables from more than one transaction-safe storage engine (such as InnoDB), and the transaction isolation level is not SERIALIZABLE, it is possible that when one transaction commits, another ongoing transaction that uses the same tables will see only some of the changes made by the first transaction. That is, the atomicity of transactions is not guaranteed with mixed engines and inconsistencies can result. (If mixed-engine transactions are infrequent, you can use SET TRANSACTION ISOLATION LEVEL to set the isolation level to SERIALIZABLE on a per-transaction basis as necessary.) If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode. If you issue a ROLLBACK statement after updating a nontransactional table within a transaction, an ER_WARNING_NOT_COMPLETE_ROLLBACK warning occurs. Changes to transaction-safe tables are rolled back, but not changes to nontransaction-safe tables.
Each transaction is stored in the binary log in one chunk, upon COMMIT. Transactions that are rolled back are not logged. (Exception: Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with a ROLLBACK statement at the end to ensure that modifications to the nontransactional tables are replicated.) See Section 5.2.4, "The Binary Log". You can change the isolation level for transactions with the SET TRANSACTION statement. See Section 13.3.6, "SET TRANSACTION Syntax". Rolling back can be a slow operation that may occur implicitly without the user having explicitly asked for it (for example, when an error occurs). Because of this, SHOW PROCESSLIST displays Rolling back in the State column for the session, not only for explicit rollbacks performed with the ROLLBACK statement but also for implicit rollbacks. 13.3.2. Statements That Cannot Be Rolled Back Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines. You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement. 13.3.3. Statements That Cause an Implicit Commit The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement. As of MySQL 5.5.3, most of these statements also cause an implicit commit after executing; for additional details, see the end of this section. Data definition language (DDL) statements that define or modify database objects. ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, RENAME TABLE, TRUNCATE TABLE. ALTER FUNCTION, CREATE FUNCTION and DROP FUNCTION also cause an implicit commit when used with stored functions, but not with UDFs. (ALTER FUNCTION can only be used with stored functions.) ALTER TABLE, CREATE TABLE, and DROP TABLE do not commit a transaction if the TEMPORARY keyword is used. (This does not apply to other operations on temporary tables such as CREATE INDEX, which do cause a commit.) However, although no implicit commit occurs, neither can the statement be rolled back. Therefore, use of such statements will violate transaction atomicity: For example, if you use CREATE TEMPORARY TABLE and then roll back the transaction, the table remains in existence. The CREATE TABLE statement in InnoDB is processed as a single transaction. This means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction. CREATE TABLE ... SELECT causes an implicit commit before and after the statement is executed when you are creating nontemporary tables. (No commit occurs for CREATE TEMPORARY TABLE ... SELECT.) This is to prevent an issue during replication where the table could be created on the master after a rollback, but fail to be recorded in the binary log, and therefore not replicated to the slave. For more information, see Bug #22865. Statements that implicitly use or modify tables in the mysql database. CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD. Transaction-control and locking statements. BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES. UNLOCK TABLES commits a transaction only if any tables currently have been locked with LOCK TABLES to acquire nontransactional table locks. A commit does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCK because the latter statement does not acquire table-level locks. Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms. Statements that cause an implicit commit cannot be used in an XA transaction while the transaction is in an ACTIVE state. The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN ... END compound statement. The latter does not cause an implicit commit. See Section 13.6.1, "BEGIN ... END Compound-Statement Syntax". Data loading statements. LOAD DATA INFILE. LOAD DATA INFILE causes an implicit commit only for tables using the NDB storage engine. For more information, see Bug #11151. Administrative statements. ANALYZE TABLE, CACHE INDEX, CHECK TABLE, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE.
As of MySQL 5.5.3, most statements that previously caused an implicit commit before executing also do so after executing. The intent is to handle each such statement in its own special transaction because it cannot be rolled back anyway. The following list provides additional details pertaining to this change: 13.3.4. SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASESAVEPOINT SyntaxSAVEPOINT identifierROLLBACK [WORK] TO [SAVEPOINT] identifierRELEASE SAVEPOINT identifier InnoDB supports the SQL statements SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT and the optional WORK keyword for ROLLBACK. The SAVEPOINT statement sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set. The ROLLBACK TO SAVEPOINT statement rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted. If the ROLLBACK TO SAVEPOINT statement returns the following error, it means that no savepoint with the specified name exists: ERROR 1305 (42000): SAVEPOINT identifier does not exist The RELEASE SAVEPOINT statement removes the named savepoint from the set of savepoints of the current transaction. No commit or rollback occurs. It is an error if the savepoint does not exist. All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint. A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored. 13.3.5. LOCK TABLES and UNLOCKTABLES SyntaxLOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITEUNLOCK TABLES MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session. Locks may be used to emulate transactions or to get more speed when updating tables. This is explained in more detail later in this section. LOCK TABLES explicitly acquires table locks for the current client session. Table locks can be acquired for base tables or views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked. For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically. If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly, as described in Section 13.3.5.2, "LOCK TABLES and Triggers". UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks. Another use for UNLOCK TABLES is to release the global read lock acquired with the FLUSH TABLES WITH READ LOCK statement, which enables you to lock all tables in all databases. See Section 13.7.6.3, "FLUSH Syntax". (This is a very convenient way to get backups if you have a file system such as Veritas that can take snapshots in time.) A table lock protects only against inappropriate reads or writes by other sessions. The session holding the lock, even a read lock, can perform table-level operations such as DROP TABLE. Truncate operations are not transaction-safe, so an error occurs if the session attempts one during an active transaction or while holding a table lock. The following discussion applies only to non-TEMPORARY tables. LOCK TABLES is permitted (but ignored) for a TEMPORARY table. The table can be accessed freely by the session within which it was created, regardless of what other locking may be in effect. No lock is necessary because no other session can see the table. For information about other conditions on the use of LOCK TABLES and statements that cannot be used while LOCK TABLES is in effect, see Section 13.3.5.3, "Table-Locking Restrictions and Conditions" Rules for Lock Acquisition To acquire table locks within the current session, use the LOCK TABLES statement. The following lock types are available: READ [LOCAL] lock: The session that holds the lock can read the table (but not write it). Multiple sessions can acquire a READ lock for the table at the same time. Other sessions can read the table without explicitly acquiring a READ lock. The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 8.10.3, "Concurrent Inserts".) However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ.
[LOW_PRIORITY] WRITE lock: The session that holds the lock can read and write the table. Only the session that holds the lock can access the table. No other session can access it until the lock is released. Lock requests for the table by other sessions block while the WRITE lock is held. The LOW_PRIORITY modifier has no effect as of MySQL 5.5.3. Before 5.5.3, it affects lock scheduling if the WRITE lock request must wait, as described later.
If the LOCK TABLES statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired. A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2 because it was not locked in the LOCK TABLES statement: mysql> LOCK TABLES t1 READ;mysql> SELECT COUNT(*) FROM t1;+----------+| COUNT(*) |+----------+| 3 |+----------+mysql> SELECT COUNT(*) FROM t2;ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES Tables in the INFORMATION_SCHEMA database are an exception. They can be accessed without being locked explicitly even while a session holds table locks obtained with LOCK TABLES. You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias: mysql> LOCK TABLE t WRITE, t AS t1 READ;mysql> INSERT INTO t SELECT * FROM t;ERROR 1100: Table 't' was not locked with LOCK TABLESmysql> INSERT INTO t SELECT * FROM t AS t1; The error occurs for the first INSERT because there are two references to the same name for a locked table. The second INSERT succeeds because the references to the table use different names. If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias: mysql> LOCK TABLE t READ;mysql> SELECT * FROM t AS myalias;ERROR 1100: Table 'myalias' was not locked with LOCK TABLES Conversely, if you lock a table using an alias, you must refer to it in your statements using that alias: mysql> LOCK TABLE t AS myalias READ;mysql> SELECT * FROM t;ERROR 1100: Table 't' was not locked with LOCK TABLESmysql> SELECT * FROM t AS myalias; WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it. Before MySQL 5.5.3, the LOW_PRIORITY modifier can be given to affect locking behavior as follows (as of 5.5.3, it has no effect): A request for a LOW_PRIORITY WRITE lock permits subsequent READ lock requests by other sessions to be satisfied first if they occur while the LOW_PRIORITY WRITE request is waiting. You should use LOW_PRIORITY WRITE locks only if you are sure that eventually there will be a time when no sessions have a READ lock. For InnoDB tables in transactional mode (autocommit = 0), a waiting LOW_PRIORITY WRITE lock acts like a regular WRITE lock and causes subsequent READ lock requests to wait. LOCK TABLES acquires locks as follows: Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined. If a table is to be locked with a read and a write lock, put the write lock request before the read lock request. Lock one table at a time until the session gets all locks.
This policy ensures that table locking is deadlock free. There are, however, other things you need to be aware of about this policy: If you are using a LOW_PRIORITY WRITE lock for a table, it means only that MySQL waits for this particular lock until there are no other sessions that want a READ lock. When the session has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other sessions wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transaction-safe tables. Rules for Lock Release When the table locks held by a session are released, they are all released at the same time. A session can release its locks explicitly, or locks may be released implicitly under certain conditions. If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional). If the client reconnects, the locks will no longer be in effect. In addition, if the client had an active transaction, the server rolls back the transaction upon disconnect, and if reconnect occurs, the new session begins with autocommit enabled. For this reason, clients may wish to disable auto-reconnect. With auto-reconnect in effect, the client is not notified if reconnect occurs but any table locks or current transaction will have been lost. With auto-reconnect disabled, if the connection drops, an error occurs for the next statement issued. The client can detect the error and take appropriate action such as reacquiring the locks or redoing the transaction. See Section 22.8.12, "Controlling Automatic Reconnection Behavior". Note If you use ALTER TABLE on a locked table, it may become unlocked. For example, if you attempt a second ALTER TABLE operation, the result may be an error Table 'tbl_name' was not locked with LOCK TABLES. To handle this, lock the table again prior to the second alteration. See also Section C.5.7.1, "Problems with ALTER TABLE". 13.3.5.1. Interaction of Table Locking and Transactions LOCK TABLES and UNLOCK TABLES interact with the use of transactions as follows: LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. UNLOCK TABLES implicitly commits any active transaction, but only if LOCK TABLES has been used to acquire table locks. For example, in the following set of statements, UNLOCK TABLES releases the global read lock but does not commit the transaction because no table locks are in effect: FLUSH TABLES WITH READ LOCK;START TRANSACTION;SELECT ... ;UNLOCK TABLES; Beginning a transaction (for example, with START TRANSACTION) implicitly commits any current transaction and releases existing table locks. FLUSH TABLES WITH READ LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits. For example, START TRANSACTION does not release the global read lock. See Section 13.7.6.3, "FLUSH Syntax". Other statements that implicitly cause transactions to be committed do not release existing table locks. For a list of such statements, see Section 13.3.3, "Statements That Cause an Implicit Commit". The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. For example, if you need to write to table t1 and read from table t2, you can do this: SET autocommit=0;LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...COMMIT;UNLOCK TABLES; When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have autocommit = 1, because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if autocommit = 1, to help old applications avoid unnecessary deadlocks. ROLLBACK does not release table locks.
13.3.5.2. LOCK TABLES and Triggers If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly: The locks are taken as the same time as those acquired explicitly with the LOCK TABLES statement. The lock on a table used in a trigger depends on whether the table is used only for reading. If so, a read lock suffices. Otherwise, a write lock is used. If a table is locked explicitly for reading with LOCK TABLES, but needs to be locked for writing because it might be modified within a trigger, a write lock is taken rather than a read lock. (That is, an implicit write lock needed due to the table's appearance within a trigger causes an explicit read lock request for the table to be converted to a write lock request.)
Suppose that you lock two tables, t1 and t2, using this statement: LOCK TABLES t1 WRITE, t2 READ; If t1 or t2 have any triggers, tables used within the triggers will also be locked. Suppose that t1 has a trigger defined like this: CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROWBEGIN UPDATE t4 SET count = count+1 WHERE id = NEW.id AND EXISTS (SELECT a FROM t3); INSERT INTO t2 VALUES(1, 2);END; The result of the LOCK TABLES statement is that t1 and t2 are locked because they appear in the statement, and t3 and t4 are locked because they are used within the trigger: t1 is locked for writing per the WRITE lock request. t2 is locked for writing, even though the request is for a READ lock. This occurs because t2 is inserted into within the trigger, so the READ request is converted to a WRITE request. t3 is locked for reading because it is only read from within the trigger. t4 is locked for writing because it might be updated within the trigger.
13.3.5.3. Table-Locking Restrictions and Conditions You can safely use KILL to terminate a session that is waiting for a table lock. See Section 13.7.6.4, "KILL Syntax". You should not lock any tables that you are using with INSERT DELAYED. An INSERT DELAYED in this case results in an error because the insert must be handled by a separate thread, not by the session which holds the lock. LOCK TABLES and UNLOCK TABLES cannot be used within stored programs. Tables in the performance_schema database cannot be locked with LOCK TABLES, except the setup_xxx tables. The following statements are prohibited while a LOCK TABLES statement is in effect: For some operations, system tables in the mysql database must be accessed. For example, the HELP statement requires the contents of the server-side help tables, and CONVERT_TZ() might need to read the time zone tables. The server implicitly locks the system tables for reading as necessary so that you need not lock them explicitly. These tables are treated as just described: mysql.help_categorymysql.help_keywordmysql.help_relationmysql.help_topicmysql.procmysql.time_zonemysql.time_zone_leap_secondmysql.time_zone_namemysql.time_zone_transitionmysql.time_zone_transition_type If you want to explicitly place a WRITE lock on any of those tables with a LOCK TABLES statement, the table must be the only one locked; no other table can be locked with the same statement. Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other session can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage: If you are going to run many operations on a set of MyISAM tables, it is much faster to lock the tables you are going to use. Locking MyISAM tables speeds up inserting, updating, or deleting on them because MySQL does not flush the key cache for the locked tables until UNLOCK TABLES is called. Normally, the key cache is flushed after each SQL statement. The downside to locking the tables is that no session can update a READ-locked table (including the one holding the lock) and no session can access a WRITE-locked table other than the one holding the lock. If you are using tables for a nontransactional storage engine, you must use LOCK TABLES if you want to ensure that no other session modifies the tables between a SELECT and an UPDATE. The example shown here requires LOCK TABLES to execute safely: LOCK TABLES trans READ, customer WRITE;SELECT SUM(value) FROM trans WHERE customer_id=some_id;UPDATE customer SET total_value=sum_from_previous_statement WHERE customer_id=some_id;UNLOCK TABLES; Without LOCK TABLES, it is possible that another session might insert a new row in the trans table between execution of the SELECT and UPDATE statements.
You can avoid using LOCK TABLES in many cases by using relative updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function. See Section 1.8.5.3, "Transaction and Atomic Operation Differences". You can also avoid locking tables in some cases by using the user-level advisory lock functions GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex_lock() and pthread_mutex_unlock() for high speed. See Section 12.15, "Miscellaneous Functions". See Section 8.10.1, "Internal Locking Methods", for more information on locking policy. 13.3.6. SET TRANSACTION SyntaxSET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE } This statement sets the transaction isolation level, used for operations on InnoDB tables. Scope of the Isolation Level You can set the isolation level globally, for the current session, or for the next transaction: With the GLOBAL keyword, the statement sets the default transaction level globally for all subsequent sessions. Existing sessions are unaffected. With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session. Without any SESSION or GLOBAL keyword, the statement sets the isolation level for the next (not started) transaction performed within the current session.
A change to the global default isolation level requires the SUPER privilege. Any session is free to change its session isolation level (even in the middle of a transaction), or the isolation level for its next transaction. SET TRANSACTION ISOLATION LEVEL without GLOBAL or SESSION is not permitted while there is an active transaction: mysql> START TRANSACTION;Query OK, 0 rows affected (0.02 sec)mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;ERROR 1568 (25001): Transaction isolation level can't be changedwhile a transaction is in progress To set the global default isolation level at server startup, use the --transaction-isolation=level option to mysqld on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the permissible values are READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to set the default isolation level to REPEATABLE READ, use these lines in the [mysqld] section of an option file: [mysqld]transaction-isolation = REPEATABLE-READ It is possible to check or set the global and session transaction isolation levels at runtime by using the tx_isolation system variable: SELECT @@GLOBAL.tx_isolation, @@tx_isolation;SET GLOBAL tx_isolation='REPEATABLE-READ';SET SESSION tx_isolation='SERIALIZABLE'; Details and Usage of Isolation Levels InnoDB supports each of the transaction isolation levels described here using different locking strategies. You can enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial data where ACID compliance is important. Or you can relax the consistency rules with READ COMMITTED or even READ UNCOMMITTED, in situations such as bulk reporting where precise consistency and repeatable results are less important than minimizing the amount of overhead for locking. SERIALIZABLE enforces even stricter rules than REPEATABLE READ, and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks. For full information about how these isolation levels work with InnoDB transactions, see Section 14.3.9, "The InnoDB Transaction Model and Locking". In particular, for additional information about InnoDB record-level locks and how it uses them to execute various types of statements, see Section 14.3.9.4, "InnoDB Record, Gap, and Next-Key Locks" and Section 14.3.9.6, "Locks Set by Different SQL Statements in InnoDB". The following list describes how MySQL supports the different transaction levels. The list goes from the most commonly used level to the least used. REPEATABLE READ This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 14.3.9.2, "Consistent Nonlocking Reads". For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. READ COMMITTED A somewhat Oracle-like isolation level with respect to consistent (nonlocking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See Section 14.3.9.2, "Consistent Nonlocking Reads". For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100). For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For range-type searches, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because "phantom rows" must be blocked for MySQL replication and recovery to work. Note In MySQL 5.5, if the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging. READ UNCOMMITTED SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a "dirty read." Otherwise, this isolation level works like READ COMMITTED. SERIALIZABLE This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)
Support for XA transactions is available for the InnoDB storage engine. The MySQL XA implementation is based on the X/Open CAE document Distributed Transaction Processing: The XA Specification. This document is published by The Open Group and available at http://www.opengroup.org/public/pubs/catalog/c193.htm. Limitations of the current XA implementation are described in Section E.6, "Restrictions on XA Transactions". On the client side, there are no special requirements. The XA interface to a MySQL server consists of SQL statements that begin with the XA keyword. MySQL client programs must be able to send SQL statements and to understand the semantics of the XA statement interface. They do not need be linked against a recent client library. Older client libraries also will work. Currently, among the MySQL Connectors, MySQL Connector/J 5.0.0 supports XA directly (by means of a class interface that handles the Xan SQL statement interface for you). XA supports distributed transactions; that is, the ability to permit multiple separate transactional resources to participate in a global transaction. Transactional resources often are RDBMSs but may be other kinds of resources. A global transaction involves several actions that are transactional in themselves, but that all must either complete successfully as a group, or all be rolled back as a group. In essence, this extends ACID properties "up a level" so that multiple ACID transactions can be executed in concert as components of a global operation that also has ACID properties. (However, for a distributed transaction, you must use the SERIALIZABLE isolation level to achieve ACID properties. It is enough to use REPEATABLE READ for a nondistributed transaction, but not for a distributed transaction.) Some examples of distributed transactions: An application may act as an integration tool that combines a messaging service with an RDBMS. The application makes sure that transactions dealing with message sending, retrieval, and processing that also involve a transactional database all happen in a global transaction. You can think of this as "transactional email." An application performs actions that involve different database servers, such as a MySQL server and an Oracle server (or multiple MySQL servers), where actions that involve multiple servers must happen as part of a global transaction, rather than as separate transactions local to each server. A bank keeps account information in an RDBMS and distributes and receives money through automated teller machines (ATMs). It is necessary to ensure that ATM actions are correctly reflected in the accounts, but this cannot be done with the RDBMS alone. A global transaction manager integrates the ATM and database resources to ensure overall consistency of financial transactions.
Applications that use global transactions involve one or more Resource Managers and a Transaction Manager: A Resource Manager (RM) provides access to transactional resources. A database server is one kind of resource manager. It must be possible to either commit or roll back transactions managed by the RM. A Transaction Manager (TM) coordinates the transactions that are part of a global transaction. It communicates with the RMs that handle each of these transactions. The individual transactions within a global transaction are "branches" of the global transaction. Global transactions and their branches are identified by a naming scheme described later.
The MySQL implementation of XA MySQL enables a MySQL server to act as a Resource Manager that handles XA transactions within a global transaction. A client program that connects to the MySQL server acts as the Transaction Manager. To carry out a global transaction, it is necessary to know which components are involved, and bring each component to a point when it can be committed or rolled back. Depending on what each component reports about its ability to succeed, they must all commit or roll back as an atomic group. That is, either all components must commit, or all components must roll back. To manage a global transaction, it is necessary to take into account that any component or the connecting network might fail. The process for executing a global transaction uses two-phase commit (2PC). This takes place after the actions performed by the branches of the global transaction have been executed. In the first phase, all branches are prepared. That is, they are told by the TM to get ready to commit. Typically, this means each RM that manages a branch records the actions for the branch in stable storage. The branches indicate whether they are able to do this, and these results are used for the second phase. In the second phase, the TM tells the RMs whether to commit or roll back. If all branches indicated when they were prepared that they will be able to commit, all branches are told to commit. If any branch indicated when it was prepared that it will not be able to commit, all branches are told to roll back.
In some cases, a global transaction might use one-phase commit (1PC). For example, when a Transaction Manager finds that a global transaction consists of only one transactional resource (that is, a single branch), that resource can be told to prepare and commit at the same time. 13.3.7.1. XA Transaction SQL Syntax To perform XA transactions in MySQL, use the following statements: XA {START|BEGIN} xid [JOIN|RESUME]XA END xid [SUSPEND [FOR MIGRATE]]XA PREPARE xidXA COMMIT xid [ONE PHASE]XA ROLLBACK xidXA RECOVER For XA START, the JOIN and RESUME clauses are not supported. For XA END the SUSPEND [FOR MIGRATE] clause is not supported. Each XA statement begins with the XA keyword, and most of them require an xid value. An xid is an XA transaction identifier. It indicates which transaction the statement applies to. xid values are supplied by the client, or generated by the MySQL server. An xid value has from one to three parts: xid: gtrid [, bqual [, formatID ]] gtrid is a global transaction identifier, bqual is a branch qualifier, and formatID is a number that identifies the format used by the gtrid and bqual values. As indicated by the syntax, bqual and formatID are optional. The default bqual value is '' if not given. The default formatID value is 1 if not given. gtrid and bqual must be string literals, each up to 64 bytes (not characters) long. gtrid and bqual can be specified in several ways. You can use a quoted string ('ab'), hex string (0x6162, X'ab'), or bit value (b'nnnn'). formatID is an unsigned integer. The gtrid and bqual values are interpreted in bytes by the MySQL server's underlying XA support routines. However, while an SQL statement containing an XA statement is being parsed, the server works with some specific character set. To be safe, write gtrid and bqual as hex strings. xid values typically are generated by the Transaction Manager. Values generated by one TM must be different from values generated by other TMs. A given TM must be able to recognize its own xid values in a list of values returned by the XA RECOVER statement. XA START xid starts an XA transaction with the given xid value. Each XA transaction must have a unique xid value, so the value must not currently be used by another XA transaction. Uniqueness is assessed using the gtrid and bqual values. All following XA statements for the XA transaction must be specified using the same xid value as that given in the XA START statement. If you use any of those statements but specify an xid value that does not correspond to some existing XA transaction, an error occurs. One or more XA transactions can be part of the same global transaction. All XA transactions within a given global transaction must use the same gtrid value in the xid value. For this reason, gtrid values must be globally unique so that there is no ambiguity about which global transaction a given XA transaction is part of. The bqual part of the xid value must be different for each XA transaction within a global transaction. (The requirement that bqual values be different is a limitation of the current MySQL XA implementation. It is not part of the XA specification.) The XA RECOVER statement returns information for those XA transactions on the MySQL server that are in the PREPARED state. (See Section 13.3.7.2, "XA Transaction States".) The output includes a row for each such XA transaction on the server, regardless of which client started it. XA RECOVER output rows look like this (for an example xid value consisting of the parts 'abc', 'def', and 7): mysql> XA RECOVER;+----------+--------------+--------------+--------+| formatID | gtrid_length | bqual_length | data |+----------+--------------+--------------+--------+| 7 | 3 | 3 | abcdef |+----------+--------------+--------------+--------+ The output columns have the following meanings: formatID is the formatID part of the transaction xid gtrid_length is the length in bytes of the gtrid part of the xid bqual_length is the length in bytes of the bqual part of the xid data is the concatenation of the gtrid and bqual parts of the xid
13.3.7.2. XA Transaction States An XA transaction progresses through the following states: Use XA START to start an XA transaction and put it in the ACTIVE state. For an ACTIVE XA transaction, issue the SQL statements that make up the transaction, and then issue an XA END statement. XA END puts the transaction in the IDLE state. For an IDLE XA transaction, you can issue either an XA PREPARE statement or an XA COMMIT ... ONE PHASE statement: XA PREPARE puts the transaction in the PREPARED state. An XA RECOVER statement at this point will include the transaction's xid value in its output, because XA RECOVER lists all XA transactions that are in the PREPARED state. XA COMMIT ... ONE PHASE prepares and commits the transaction. The xid value will not be listed by XA RECOVER because the transaction terminates.
For a PREPARED XA transaction, you can issue an XA COMMIT statement to commit and terminate the transaction, or XA ROLLBACK to roll back and terminate the transaction.
Here is a simple XA transaction that inserts a row into a table as part of a global transaction: mysql> XA START 'xatest';Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO mytable (i) VALUES(10);Query OK, 1 row affected (0.04 sec)mysql> XA END 'xatest';Query OK, 0 rows affected (0.00 sec)mysql> XA PREPARE 'xatest';Query OK, 0 rows affected (0.00 sec)mysql> XA COMMIT 'xatest';Query OK, 0 rows affected (0.00 sec) Within the context of a given client connection, XA transactions and local (non-XA) transactions are mutually exclusive. For example, if XA START has been issued to begin an XA transaction, a local transaction cannot be started until the XA transaction has been committed or rolled back. Conversely, if a local transaction has been started with START TRANSACTION, no XA statements can be used until the transaction has been committed or rolled back. Note that if an XA transaction is in the ACTIVE state, you cannot issue any statements that cause an implicit commit. That would violate the XA contract because you could not roll back the XA transaction. You will receive the following error if you try to execute such a statement: ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executedwhen global transaction is in the ACTIVE state Statements to which the preceding remark applies are listed at Section 13.3.3, "Statements That Cause an Implicit Commit". 13.4. Replication Statements Replication can be controlled through the SQL interface using the statements described in this section. One group of statements controls master servers, the other controls slave servers. 13.4.1. SQL Statements for Controlling Master Servers This section discusses statements for managing master replication servers. Section 13.4.2, "SQL Statements for Controlling Slave Servers", discusses statements for managing slave servers. In addition to the statements described here, the following SHOW statements are used with master servers in replication. For information about these statements, see Section 13.7.5, "SHOW Syntax". 13.4.1.1. PURGE BINARY LOGS SyntaxPURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } The binary log is a set of files that contain information about data modifications made by the MySQL server. The log consists of a set of binary log files, plus an index file (see Section 5.2.4, "The Binary Log"). The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. BINARY and MASTER are synonyms. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list. This statement has no effect if the server was not started with the --log-bin option to enable binary logging. Examples: PURGE BINARY LOGS TO 'mysql-bin.010';PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; The BEFORE variant's datetime_expr argument should evaluate to a DATETIME value (a value in 'YYYY-MM-DD hh:mm:ss' format). This statement is safe to run while slaves are replicating. You need not stop them. If you have an active slave that currently is reading one of the log files you are trying to delete, this statement does nothing and fails with an error. However, if a slave is not connected and you happen to purge one of the log files it has yet to read, the slave will be unable to replicate after it reconnects. To safely purge binary log files, follow this procedure: On each slave server, use SHOW SLAVE STATUS to check which log file it is reading. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS. Determine the earliest log file among all the slaves. This is the target file. If all the slaves are up to date, this is the last log file on the list. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.) Purge all log files up to but not including the target file.
You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days (see Section 5.1.4, "Server System Variables"). If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master. PURGE BINARY LOGS TO and PURGE BINARY LOGS BEFORE both fail with an error when binary log files listed in the .index file had been removed from the system by some other means (such as using rm on Linux). (Bug #18199, Bug #18453) To handle such errors, edit the .index file (which is a simple text file) manually to ensure that it lists only the binary log files that are actually present, then run again the PURGE BINARY LOGS statement that failed. 13.4.1.2. RESET MASTER SyntaxRESET MASTER Deletes all binary log files listed in the index file, resets the binary log index file to be empty, and creates a new binary log file. This statement is intended to be used only when the master is started for the first time. RESET MASTER can prove useful when you first set up the master and the slave, so that you can verify the setup as follows: Start the master and slave, and start replication (see Section 16.1.1, "How to Set Up Replication"). Execute a few test queries on the master. Check that the queries were replicated to the slave. When replication is running correctly, issue STOP SLAVE followed by RESET SLAVE on the slave, then verify that any unwanted data no longer exists on the slave. Issue RESET MASTER on the master to clean up the test queries.
After verifying the setup and getting rid of any unwanted and log files generated by testing, you can start the slave and begin replicating. 13.4.1.3. SET sql_log_bin SyntaxSET sql_log_bin = {0|1} The sql_log_bin variable controls whether logging to the binary log is done. The default value is 1 (do logging). To change logging for the current session, change the session value of this variable. The session user must have the SUPER privilege to set this variable. Beginning with MySQL 5.5.5, it is no longer possible to set @@session.sql_log_bin within a transaction or subquery. (Bug #53437) 13.4.2. SQL Statements for Controlling Slave Servers This section discusses statements for managing slave replication servers. Section 13.4.1, "SQL Statements for Controlling Master Servers", discusses statements for managing master servers. In addition to the statements described here, SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS are also used with replication slaves. For information about these statements, see Section 13.7.5.35, "SHOW SLAVE STATUS Syntax", and Section 13.7.5.33, "SHOW RELAYLOG EVENTS Syntax". 13.4.2.1. CHANGE MASTER TO SyntaxCHANGE MASTER TO option [, option] ...option: MASTER_BIND = 'interface_name' | MASTER_HOST = 'host_name' | MASTER_USER = 'user_name' | MASTER_PASSWORD = 'password' | MASTER_PORT = port_num | MASTER_CONNECT_RETRY = interval | MASTER_HEARTBEAT_PERIOD = interval | MASTER_LOG_FILE = 'master_log_name' | MASTER_LOG_POS = master_log_pos | RELAY_LOG_FILE = 'relay_log_name' | RELAY_LOG_POS = relay_log_pos | MASTER_SSL = {0|1} | MASTER_SSL_CA = 'ca_file_name' | MASTER_SSL_CAPATH = 'ca_directory_name' | MASTER_SSL_CERT = 'cert_file_name' | MASTER_SSL_KEY = 'key_file_name' | MASTER_SSL_CIPHER = 'cipher_list' | MASTER_SSL_VERIFY_SERVER_CERT = {0|1} | IGNORE_SERVER_IDS = (server_id_list)server_id_list: [server_id [, server_id] ... ] CHANGE MASTER TO changes the parameters that the slave server uses for connecting to the master server, for reading the master binary log, and reading the slave relay log. It also updates the contents of the master.info and relay-log.info files. To use CHANGE MASTER TO, the slave replication threads must be stopped (use STOP SLAVE if necessary). Options not specified retain their value, except as indicated in the following discussion. Thus, in most cases, there is no need to specify options that do not change. For example, if the password to connect to your MySQL master has changed, you just need to issue these statements to tell the slave about the new password: STOP SLAVE; -- if replication was runningCHANGE MASTER TO MASTER_PASSWORD='new3cret';START SLAVE; -- if you want to restart replication MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide information to the slave about how to connect to its master: MASTER_HOST and MASTER_PORT are the host name (or IP address) of the master host and its TCP/IP port. Note Replication cannot use Unix socket files. You must be able to connect to the master MySQL server using TCP/IP. If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it. Setting MASTER_HOST='' (that is, setting its value explicitly to an empty string) is not the same as not setting MASTER_HOST at all. Beginning with MySQL 5.5, trying to set MASTER_HOST to an empty string fails with an error. Previously, setting MASTER_HOST to an empty string caused START SLAVE subsequently to fail. (Bug #28796) MASTER_USER and MASTER_PASSWORD are the user name and password of the account to use for connecting to the master. In MySQL 5.5.20 and later, MASTER_USER cannot be made empty; setting MASTER_USER = '' or leaving it unset when setting a value for for MASTER_PASSWORD causes an error (Bug #13427949). Currently, a password used for a replication slave account is effectively limited to 32 characters in length; the password can be longer, but any excess characters are truncated. This is not due to any limit imposed by the MySQL Server generally, but rather is an issue specific to MySQL Replication. (For more information, see Bug #43439.) The text of a running CHANGE MASTER TO statement, including values for MASTER_USER and MASTER_PASSWORD, can be seen in the output of a concurrent SHOW PROCESSLIST statement.
The MASTER_SSL_xxx options provide information about using SSL for the connection. They correspond to the --ssl-xxx options described in Section 6.3.8.4, "SSL Command Options", and Section 16.3.7, "Setting Up Replication Using SSL". These options can be changed even on slaves that are compiled without SSL support. They are saved to the master.info file, but are ignored if the slave does not have SSL support enabled. MASTER_CONNECT_RETRY specifies how many seconds to wait between connect retries. The default is 60. The number of reconnection attempts is limited by the --master-retry-count server option; for more information, see Section 16.1.3, "Replication and Binary Logging Options and Variables". The MASTER_BIND option is available in MySQL Cluster NDB 7.2 and later, but is not supported in mainline MySQL 5.5. MASTER_BIND is for use on replication slaves having multiple network interfaces, and determines which of the slave's network interfaces is chosen for connecting to the master. MASTER_HEARTBEAT_PERIOD sets the interval in seconds between replication heartbeats. Whenever the master's binary log is updated with an event, the waiting period for the next heartbeat is reset. interval is a decimal value having the range 0 to 4294967 seconds and a resolution in milliseconds; the smallest nonzero value is 0.001. Heartbeats are sent by the master only if there are no unsent events in the binary log file for a period longer than interval. Setting interval to 0 disables heartbeats altogether. The default value for interval is equal to the value of slave_net_timeout divided by 2. Setting @@global.slave_net_timeout to a value less than that of the current heartbeat interval results in a warning being issued. The effect of issuing RESET SLAVE on the heartbeat interval is to reset it to the default value. MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates at which the slave I/O thread should begin reading from the master the next time the thread starts. RELAY_LOG_FILE and RELAY_LOG_POS are the coordinates at which the slave SQL thread should begin reading from the relay log the next time the thread starts. If you specify either of MASTER_LOG_FILE or MASTER_LOG_POS, you cannot specify RELAY_LOG_FILE or RELAY_LOG_POS. If neither of MASTER_LOG_FILE or MASTER_LOG_POS is specified, the slave uses the last coordinates of the slave SQL thread before CHANGE MASTER TO was issued. This ensures that there is no discontinuity in replication, even if the slave SQL thread was late compared to the slave I/O thread, when you merely want to change, say, the password to use. CHANGE MASTER TO deletes all relay log files and starts a new one, unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay log files are kept; the relay_log_purge global variable is set silently to 0. Prior to MySQL 5.5, RELAY_LOG_FILE required an absolute path. In MySQL 5.5, the path can be relative, in which case the path is assumed to be relative to the slave's data directory. (Bug #12190) IGNORE_SERVER_IDS was added in MySQL 5.5. This option takes a comma-separated list of 0 or more server IDs. Events originating from the corresponding servers are ignored, with the exception of log rotation and deletion events, which are still recorded in the relay log. In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more than once. Thus, this option is useful in circular replication when one of the servers in the circle is removed. Suppose that you have a circular replication setup with 4 servers, having server IDs 1, 2, 3, and 4, and server 3 fails. When bridging the gap by starting replication from server 2 to server 4, you can include IGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you issue on server 4 to tell it to use server 2 as its master instead of server 3. Doing so causes it to ignore and not to propagate any statements that originated with the server that is no longer in use. If a CHANGE MASTER TO statement is issued without any IGNORE_SERVER_IDS option, any existing list is preserved; RESET SLAVE also has no effect on the server ID list. To clear the list of ignored servers, it is necessary to use the option with an empty list: CHANGE MASTER TO IGNORE_SERVER_IDS = (); If IGNORE_SERVER_IDS contains the server's own ID and the server was started with the --replicate-same-server-id option enabled, an error results. Also beginning with MySQL 5.5, the master.info file and the output of SHOW SLAVE STATUS are extended to provide the list of servers that are currently ignored. For more information, see Section 16.2.2.2, "Slave Status Logs", and Section 13.7.5.35, "SHOW SLAVE STATUS Syntax". Beginning with MySQL 5.5.5, invoking CHANGE MASTER TO causes the previous values for MASTER_HOST, MASTER_PORT, MASTER_LOG_FILE, and MASTER_LOG_POS to be written to the error log, along with other information about the slave's state prior to execution. CHANGE MASTER TO is useful for setting up a slave when you have the snapshot of the master and have recorded the master binary log coordinates corresponding to the time of the snapshot. After loading the snapshot into the slave to synchronize it to the slave, you can run CHANGE MASTER TO MASTER_LOG_FILE='log_name', MASTER_LOG_POS=log_pos on the slave to specify the coordinates at which the slave should begin reading the master binary log. The following example changes the master server the slave uses and establishes the master binary log coordinates from which the slave begins reading. This is used when you want to set up the slave to replicate the master: CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10; The next example shows an operation that is less frequently employed. It is used when the slave has relay log files that you want it to execute again for some reason. To do this, the master need not be reachable. You need only use CHANGE MASTER TO and start the SQL thread (START SLAVE SQL_THREAD): CHANGE MASTER TO RELAY_LOG_FILE='slave-relay-bin.006', RELAY_LOG_POS=4025; You can even use the second operation in a nonreplication setup with a standalone, nonslave server for recovery following a crash. Suppose that your server has crashed and you have restored it from a backup. You want to replay the server's own binary log files (not relay log files, but regular binary log files), named (for example) myhost-bin.*. First, make a backup copy of these binary log files in some safe place, in case you don't exactly follow the procedure below and accidentally have the server purge the binary log. Use SET GLOBAL relay_log_purge=0 for additional safety. Then start the server without the --log-bin option, Instead, use the --replicate-same-server-id, --relay-log=myhost-bin (to make the server believe that these regular binary log files are relay log files) and --skip-slave-start options. After the server starts, issue these statements: CHANGE MASTER TO RELAY_LOG_FILE='myhost-bin.153', RELAY_LOG_POS=410, MASTER_HOST='some_dummy_string';START SLAVE SQL_THREAD; The server reads and executes its own binary log files, thus achieving crash recovery. Once the recovery is finished, run STOP SLAVE, shut down the server, delete the master.info and relay-log.info files, and restart the server with its original options. Specifying the MASTER_HOST option (even with a dummy value) is required to make the server think it is a slave. The following table shows the maximum permissible length for the string-valued options. 13.4.2.2. MASTER_POS_WAIT() SyntaxSELECT MASTER_POS_WAIT('master_log_file', master_log_pos [, timeout]) This is actually a function, not a statement. It is used to ensure that the slave has read and executed events up to a given position in the master's binary log. See Section 12.15, "Miscellaneous Functions", for a full description. 13.4.2.3. RESET SLAVE SyntaxRESET SLAVE [ALL] RESET SLAVE makes the slave forget its replication position in the master's binary log. This statement is meant to be used for a clean start: It deletes the master.info and relay-log.info files, all the relay log files, and starts a new relay log file. To use RESET SLAVE, the slave replication threads must be stopped (use STOP SLAVE if necessary). Note All relay log files are deleted, even if they have not been completely executed by the slave SQL thread. (This is a condition likely to exist on a replication slave if you have issued a STOP SLAVE statement or if the slave is highly loaded.) In MySQL 5.5 (unlike the case in MySQL 5.1 and earlier), RESET SLAVE does not change any replication connection parameters such as master host, master port, master user, or master password, which are retained in memory. This means that START SLAVE can be issued without requiring a CHANGE MASTER TO statement following RESET SLAVE. Connection parameters are reset if the slave mysqld is shut down following RESET SLAVE. In MySQL 5.5.16 and later, you can instead use RESET SLAVE ALL to reset these connection parameters (Bug #11809016). If the slave SQL thread was in the middle of replicating temporary tables when it was stopped, and RESET SLAVE is issued, these replicated temporary tables are deleted on the slave. 13.4.2.4. SET GLOBAL sql_slave_skip_counter SyntaxSET GLOBAL sql_slave_skip_counter = N This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement. This statement is valid only when the slave threads are not running. Otherwise, it produces an error. When using this statement, it is important to understand that the binary log is actually organized as a sequence of groups known as event groups. Each event group consists of a sequence of events. For transactional tables, an event group corresponds to a transaction. For nontransactional tables, an event group corresponds to a single SQL statement.
Note A single transaction can contain changes to both transactional and nontransactional tables. When you use SET GLOBAL sql_slave_skip_counter to skip events and the result is in the middle of a group, the slave continues to skip events until it reaches the end of the group. Execution then starts with the next event group. Beginning with MySQL 5.5.5, issuing this statement causes the previous values of RELAY_LOG_FILE, RELAY_LOG_POS, and sql_slave_skip_counter to be written to the error log. 13.4.2.5. START SLAVE SyntaxSTART SLAVE [thread_types]START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_posSTART SLAVE [SQL_THREAD] UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_posthread_types: [thread_type [, thread_type] ... ]thread_type: IO_THREAD | SQL_THREAD START SLAVE with no thread_type options starts both of the slave threads. The I/O thread reads events from the master server and stores them in the relay log. The SQL thread reads events from the relay log and executes them. START SLAVE requires the SUPER privilege. If START SLAVE succeeds in starting the slave threads, it returns without any error. However, even in that case, it might be that the slave threads start and then later stop (for example, because they do not manage to connect to the master or read its binary log, or some other problem). START SLAVE does not warn you about this. You must check the slave's error log for error messages generated by the slave threads, or check that they are running satisfactorily with SHOW SLAVE STATUS. START SLAVE sends an acknowledgment to the user after both the I/O thread and the SQL thread have started. However, the I/O thread may not yet have connected. For this reason, a successful START SLAVE causes SHOW SLAVE STATUS to show Slave_SQL_Running=Yes, but this does not guarantee that Slave_IO_Running=Yes (because Slave_IO_Running=Yes only if the I/O thread is running and connected). For more information, see Section 13.7.5.35, "SHOW SLAVE STATUS Syntax", and Section 16.1.4.1, "Checking Replication Status". You can add IO_THREAD and SQL_THREAD options to the statement to name which of the threads to start. An UNTIL clause may be added to specify that the slave should start and run until the SQL thread reaches a given point in the master binary log or in the slave relay log. When the SQL thread reaches that point, it stops. If the SQL_THREAD option is specified in the statement, it starts only the SQL thread. Otherwise, it starts both slave threads. If the SQL thread is running, the UNTIL clause is ignored and a warning is issued. For an UNTIL clause, you must specify both a log file name and position. Do not mix master and relay log options. Any UNTIL condition is reset by a subsequent STOP SLAVE statement, a START SLAVE statement that includes no UNTIL clause, or a server restart. The UNTIL clause can be useful for debugging replication, or to cause replication to proceed until just before the point where you want to avoid having the slave replicate an event. For example, if an unwise DROP TABLE statement was executed on the master, you can use UNTIL to tell the slave to execute up to that point but no farther. To find what the event is, use mysqlbinlog with the master binary log or slave relay log, or by using a SHOW BINLOG EVENTS statement. If you are using UNTIL to have the slave process replicated queries in sections, it is recommended that you start the slave with the --skip-slave-start option to prevent the SQL thread from running when the slave server starts. It is probably best to use this option in an option file rather than on the command line, so that an unexpected server restart does not cause it to be forgotten. The SHOW SLAVE STATUS statement includes output fields that display the current values of the UNTIL condition. In old versions of MySQL (before 4.0.5), this statement was called SLAVE START. This usage is still accepted in MySQL 5.5 for backward compatibility, but is deprecated and is removed in MySQL 5.6. 13.4.2.6. STOP SLAVE SyntaxSTOP SLAVE [thread_types]thread_types: [thread_type [, thread_type] ... ]thread_type: IO_THREAD | SQL_THREAD Stops the slave threads. STOP SLAVE requires the SUPER privilege. Recommended best practice is to execute STOP SLAVE on the slave before stopping the slave server (see Section 5.1.12, "The Shutdown Process", for more information). When using the row-based logging format: You should execute STOP SLAVE on the slave prior to shutting down the slave server if you are replicating any tables that use a nontransactional storage engine (see the Note later in this section). In MySQL 5.5.9 and later, you can also use STOP SLAVE SQL_THREAD for this purpose. Like START SLAVE, this statement may be used with the IO_THREAD and SQL_THREAD options to name the thread or threads to be stopped. Note In MySQL 5.5, STOP SLAVE waits until the current replication event group affecting one or more non-transactional tables has finished executing (if there is any such replication group), or until the user issues a KILL QUERY or KILL CONNECTION statement. (Bug #319, Bug #38205) In old versions of MySQL (before 4.0.5), this statement was called SLAVE STOP. This usage is still accepted in MySQL 5.5 for backward compatibility, but is deprecated and is removed in MySQL 5.6. 13.5. SQL Syntax for Prepared Statements MySQL 5.5 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol implemented in MySQL 4.1, provided that you use an appropriate client programming interface. Candidate interfaces include the MySQL C API client library (for C programs), MySQL Connector/J (for Java programs), and MySQL Connector/Net. For example, the C API provides a set of function calls that make up its prepared statement API. See Section 22.8.4, "C API Prepared Statements". Other language interfaces can provide support for prepared statements that use the binary protocol by linking in the C client library, one example being the mysqli extension, available in PHP 5.0 and later. An alternative SQL interface to prepared statements is available. This interface is not as efficient as using the binary protocol through a prepared statement API, but requires no programming because it is available directly at the SQL level: You can use it when no programming interface is available to you. You can use it from any program that enables you to send SQL statements to the server to be executed, such as the mysql client program. You can use it even if the client is using an old version of the client library. The only requirement is that you be able to connect to a server that is recent enough to support SQL syntax for prepared statements.
SQL syntax for prepared statements is intended to be used for situations such as these: You want to test how prepared statements work in your application before coding it. An application has problems executing prepared statements and you want to determine interactively what the problem is. You want to create a test case that describes a problem you are having with prepared statements, so that you can file a bug report. You need to use prepared statements but do not have access to a programming API that supports them.
SQL syntax for prepared statements is based on three SQL statements: The following examples show two equivalent ways of preparing a statement that computes the hypotenuse of a triangle given the lengths of the two sides. The first example shows how to create a prepared statement by using a string literal to supply the text of the statement: mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';mysql> SET @a = 3;mysql> SET @b = 4;mysql> EXECUTE stmt1 USING @a, @b;+------------+| hypotenuse |+------------+| 5 |+------------+mysql> DEALLOCATE PREPARE stmt1; The second example is similar, but supplies the text of the statement as a user variable: mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';mysql> PREPARE stmt2 FROM @s;mysql> SET @a = 6;mysql> SET @b = 8;mysql> EXECUTE stmt2 USING @a, @b;+------------+| hypotenuse |+------------+| 10 |+------------+mysql> DEALLOCATE PREPARE stmt2; Here is an additional example that demonstrates how to choose the table on which to perform a query at runtime, by storing the name of the table as a user variable: mysql> USE test;mysql> CREATE TABLE t1 (a INT NOT NULL);mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);mysql> SET @table = 't1';mysql> SET @s = CONCAT('SELECT * FROM ', @table);mysql> PREPARE stmt3 FROM @s;mysql> EXECUTE stmt3;+----+| a |+----+| 4 || 8 || 11 || 32 || 80 |+----+mysql> DEALLOCATE PREPARE stmt3; A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically. A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends. To guard against too many prepared statements being created simultaneously, set the max_prepared_stmt_count system variable. To prevent the use of prepared statements, set the value to 0. The following SQL statements can be used as prepared statements: ALTER TABLEANALYZE TABLECACHE INDEXCALLCHANGE MASTERCHECKSUM {TABLE | TABLES}COMMIT{CREATE | DROP} DATABASE{CREATE | RENAME | DROP} USERCREATE INDEXCREATE TABLEDELETEDODROP INDEXDROP TABLEFLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}GRANTINSERTINSTALL PLUGINKILLLOAD INDEX INTO CACHEOPTIMIZE TABLERENAME TABLEREPAIR TABLEREPLACERESET {MASTER | SLAVE | QUERY CACHE}REVOKESELECTSETSHOW BINLOG EVENTSSHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}SHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS}SHOW {MASTER | BINARY} LOGSSHOW {MASTER | SLAVE} STATUSSLAVE {START | STOP}TRUNCATE TABLEUNINSTALL PLUGINUPDATE Other statements are not yet supported. Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. Exceptions are noted in Section E.1, "Restrictions on Stored Programs". Metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed. For more information, see Section 13.5.4, "Automatic Prepared Statement Repreparation". Placeholders can be used for the arguments of the LIMIT clause when using prepared statements. See Section 13.2.9, "SELECT Syntax". In prepared CALL statements used with PREPARE and EXECUTE, placeholder support for OUT and INOUT parameters is available beginning with MySQL 5.5. See Section 13.2.1, "CALL Syntax", for an example and a workaround for earlier versions. Placeholders can be used for IN parameters regardless of version. SQL syntax for prepared statements cannot be used in nested fashion. That is, a statement passed to PREPARE cannot itself be a PREPARE, EXECUTE, or DEALLOCATE PREPARE statement. SQL syntax for prepared statements is distinct from using prepared statement API calls. For example, you cannot use the mysql_stmt_prepare() C API function to prepare a PREPARE, EXECUTE, or DEALLOCATE PREPARE statement. SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers. However, a cursor cannot be used for a dynamic statement that is prepared and executed with PREPARE and EXECUTE. The statement for a cursor is checked at cursor creation time, so the statement cannot be dynamic. SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ";" characters). Prepared statements use the query cache under the conditions described in Section 8.9.3.1, "How the Query Cache Operates". To write C programs that use the CALL SQL statement to execute stored procedures that contain prepared statements, the CLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure. CLIENT_MULTI_RESULTS can be enabled when you call mysql_real_connect(), either explicitly by passing the CLIENT_MULTI_RESULTS flag itself, or implicitly by passing CLIENT_MULTI_STATEMENTS (which also enables CLIENT_MULTI_RESULTS). For additional information, see Section 13.2.1, "CALL Syntax". PREPARE stmt_name FROM preparable_stmt The PREPARE statement prepares a statement and assigns it a name, stmt_name, by which to refer to the statement later. Statement names are not case sensitive. preparable_stmt is either a string literal or a user variable that contains the text of the statement. The text must represent a single SQL statement, not multiple statements. Within the statement, "?" characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The "?" characters should not be enclosed within quotation marks, even if you intend to bind them to string values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth. If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned and no statement with the given name exists. A prepared statement is executed with EXECUTE and released with DEALLOCATE PREPARE. The scope of a prepared statement is the session within which it is created. Other sessions cannot see it. For examples, see Section 13.5, "SQL Syntax for Prepared Statements". EXECUTE stmt_name [USING @var_name [, @var_name] ...] After preparing a statement with PREPARE, you execute it with an EXECUTE statement that refers to the prepared statement name. If the prepared statement contains any parameter markers, you must supply a USING clause that lists user variables containing the values to be bound to the parameters. Parameter values can be supplied only by user variables, and the USING clause must name exactly as many variables as the number of parameter markers in the statement. You can execute a given prepared statement multiple times, passing different variables to it or setting the variables to different values before each execution. For examples, see Section 13.5, "SQL Syntax for Prepared Statements". 13.5.4. Automatic Prepared Statement Repreparation Metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed. This applies to prepared statements processed at the SQL level (using the PREPARE statement) and those processed using the binary client/server protocol (using the mysql_stmt_prepare() C API function). The server attempts repreparation up to three times. An error occurs if all attempts fail. Metadata changes occur for DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables. Repreparation also occurs after referenced tables or views are flushed from the table definition cache, either implicitly to make room for new entries in the cache, or explicitly due to FLUSH TABLES. Table content changes (for example, with INSERT or UPDATE) do not cause repreparation, nor do SELECT statements. Repreparation is automatic, but to the extent that it occurs, diminishes prepared statement performance. Repreparation uses the default database and SQL mode that were in effect for the original preparation. The Com_stmt_reprepare status variable tracks the number of repreparations. 13.6. MySQL Compound-Statement Syntax This section describes the syntax for the BEGIN ... END compound statement and other statements that can be used in the body of stored programs: Stored procedures and functions, triggers, and events. These objects are defined in terms of SQL code that is stored on the server for later invocation (see Chapter 19, Stored Programs and Views). A compound statement is a block that can contain other blocks; declarations for variables, condition handlers, and cursors; and flow control constructs such as loops and conditional tests. 13.6.1. BEGIN ... ENDCompound-Statement Syntax[begin_label:] BEGIN [statement_list]END [end_label] BEGIN ... END syntax is used for writing compound statements, which can appear within stored programs (stored procedures and functions, triggers, and events). A compound statement can contain multiple statements, enclosed by the BEGIN and END keywords. statement_list represents a list of one or more statements, each terminated by a semicolon (;) statement delimiter. The statement_list itself is optional, so the empty compound statement (BEGIN END) is legal. BEGIN ... END blocks can be nested. Use of multiple statements requires that a client is able to send statement strings containing the ; statement delimiter. In the mysql command-line client, this is handled with the delimiter command. Changing the ; end-of-statement delimiter (for example, to //) permit ; to be used in a program body. For an example, see Section 19.1, "Defining Stored Programs". A BEGIN ... END block can be labeled. See Section 13.6.2, "Statement Label Syntax". The optional [NOT] ATOMIC clause is not supported. This means that no transactional savepoint is set at the start of the instruction block and the BEGIN clause used in this context has no effect on the current transaction. 13.6.2. Statement Label Syntax[begin_label:] BEGIN [statement_list]END [end_label][begin_label:] LOOP statement_listEND LOOP [end_label][begin_label:] REPEAT statement_listUNTIL search_conditionEND REPEAT [end_label][begin_label:] WHILE search_condition DO statement_listEND WHILE [end_label] Labels are permitted for BEGIN ... END blocks and for the LOOP, REPEAT, and WHILE statements. Label use for those statements follows these rules: begin_label must be followed by a colon. begin_label can be given without end_label. If end_label is present, it must be the same as begin_label. end_label cannot be given without begin_label. Labels at the same nesting level must be distinct. Labels can be up to 16 characters long.
To refer to a label within the labeled construct, use an ITERATE or LEAVE statement. The following example uses those statements to continue iterating or terminate the loop: CREATE PROCEDURE doiterate(p1 INT)BEGIN label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1;END; The scope of a block label does not include the code for handlers declared within the block. For details, see Section 13.6.7.2, "DECLARE ... HANDLER Syntax". The DECLARE statement is used to define various items local to a program: DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements. Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations. 13.6.4. Variables in Stored Programs System variables and user-defined variables can be used in stored programs, just as they can be used outside stored-program context. In addition, stored programs can use DECLARE to define local variables, and stored routines (procedures and functions) can be declared to take parameters that communicate values between the routine and its caller. For information about the scope of local variables and how MySQL resolves ambiguous names, see Section 13.6.4.2, "Local Variable Scope and Resolution". 13.6.4.1. Local Variable DECLARESyntaxDECLARE var_name [, var_name] ... type [DEFAULT value] This statement declares local variables within stored programs. To provide a default value for a variable, include a DEFAULT clause. The value can be specified as an expression; it need not be a constant. If the DEFAULT clause is missing, the initial value is NULL. Local variables are treated like stored routine parameters with respect to data type and overflow checking. See Section 13.1.15, "CREATE PROCEDURE and CREATE FUNCTION Syntax". Variable declarations must appear before cursor or handler declarations. Local variable names are not case sensitive. Permissible characters and quoting rules are the same as for other identifiers, as described in Section 9.2, "Schema Object Names". The scope of a local variable is the BEGIN ... END block within which it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name. 13.6.4.2. Local Variable Scope and Resolution The scope of a local variable is the BEGIN ... END block within which it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name. Local variables are in scope only during stored program execution. References to them are not permitted within prepared statements because those are global to the current session and the variables might have gone out of scope when the statement is executed. For example, SELECT ... INTO local_var cannot be used as a prepared statement. A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. Consider the following procedure definition: CREATE PROCEDURE sp1 (x VARCHAR(5))BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; DECLARE newname VARCHAR(5); DECLARE xid INT; SELECT xname, id INTO newname, xid FROM table1 WHERE xname = xname; SELECT newname;END; MySQL interprets xname in the SELECT statement as a reference to the xname variable rather than the xname column. Consequently, when the procedure sp1()is called, the newname variable returns the value 'bob' regardless of the value of the table1.xname column. Similarly, the cursor definition in the following procedure contains a SELECT statement that refers to xname. MySQL interprets this as a reference to the variable of that name rather than a column reference. CREATE PROCEDURE sp2 (x VARCHAR(5))BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; DECLARE newname VARCHAR(5); DECLARE xid INT; DECLARE done TINYINT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT xname, id FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; read_loop: LOOP FETCH FROM cur1 INTO newname, xid; IF done THEN LEAVE read_loop; END IF; SELECT newname; END LOOP; CLOSE cur1;END; See also Section E.1, "Restrictions on Stored Programs". 13.6.5. Flow Control Statements MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions. Many of these constructs contain other statements, as indicated by the grammar specifications in the following sections. Such constructs may be nested. For example, an IF statement might contain a WHILE loop, which itself contains a CASE statement. MySQL does not support FOR loops. CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list]END CASE Or: CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list]END CASE The CASE statement for stored programs implements a complex conditional construct. For the first syntax, case_value is an expression. This value is compared to the when_value expression in each WHEN clause until one of them is equal. When an equal when_value is found, the corresponding THEN clause statement_list executes. If no when_value is equal, the ELSE clause statement_list executes, if there is one. This syntax cannot be used to test for equality with NULL because NULL = NULL is false. See Section 3.3.4.6, "Working with NULL Values". For the second syntax, each WHEN clause search_condition expression is evaluated until one is true, at which point its corresponding THEN clause statement_list executes. If no search_condition is equal, the ELSE clause statement_list executes, if there is one. If no when_value or search_condition matches the value tested and the CASE statement contains no ELSE clause, a Case not found for CASE statement error results. Each statement_list consists of one or more SQL statements; an empty statement_list is not permitted. To handle situations where no value is matched by any WHEN clause, use an ELSE containing an empty BEGIN ... END block, as shown in this example. (The indentation used here in the ELSE clause is for purposes of clarity only, and is not otherwise significant.) DELIMITER |CREATE PROCEDURE p() BEGIN DECLARE v INT DEFAULT 1; CASE v WHEN 2 THEN SELECT v; WHEN 3 THEN SELECT 0; ELSE BEGIN END; END CASE; END; | IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list]END IF The IF statement for stored programs implements a basic conditional construct. If the search_condition evaluates to true, the corresponding THEN or ELSEIF clause statement_list executes. If no search_condition matches, the ELSE clause statement_list executes. Each statement_list consists of one or more SQL statements; an empty statement_list is not permitted. An IF ... END IF block, like all other flow-control blocks used within stored programs, must be terminated with a semicolon, as shown in this example: DELIMITER //CREATE FUNCTION SimpleCompare(n INT, m INT) RETURNS VARCHAR(20) BEGIN DECLARE s VARCHAR(20); IF n > m THEN SET s = '>'; ELSEIF n = m THEN SET s = '='; ELSE SET s = '<'; END IF; SET s = CONCAT(n, ' ', s, ' ', m); RETURN s; END //DELIMITER ; As with other flow-control constructs, IF ... END IF blocks may be nested within other flow-control constructs, including other IF statements. Each IF must be terminated by its own END IF followed by a semicolon. You can use indentation to make nested flow-control blocks more easily readable by humans (although this is not required by MySQL), as shown here: DELIMITER //CREATE FUNCTION VerboseCompare (n INT, m INT) RETURNS VARCHAR(50) BEGIN DECLARE s VARCHAR(50); IF n = m THEN SET s = 'equals'; ELSE IF n > m THEN SET s = 'greater'; ELSE SET s = 'less'; END IF; SET s = CONCAT('is ', s, ' than'); END IF; SET s = CONCAT(n, ' ', s, ' ', m, '.'); RETURN s; END //DELIMITER ; In this example, the inner IF is evaluated only if n is not equal to m. [begin_label:] LOOP statement_listEND LOOP [end_label] LOOP implements a simple loop construct, enabling repeated execution of the statement list, which consists of one or more statements, each terminated by a semicolon (;) statement delimiter. The statements within the loop are repeated until the loop is terminated. Usually, this is accomplished with a LEAVE statement. Within a stored function, RETURN can also be used, which exits the function entirely. Neglecting to include a loop-termination statement results in an infinite loop. A LOOP statement can be labeled. For the rules regarding label use, see Section 13.6.2, "Statement Label Syntax". Example: CREATE PROCEDURE doiterate(p1 INT)BEGIN label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1; SET @x = p1;END; [begin_label:] REPEAT statement_listUNTIL search_conditionEND REPEAT [end_label] The statement list within a REPEAT statement is repeated until the search_condition expression is true. Thus, a REPEAT always enters the loop at least once. statement_list consists of one or more statements, each terminated by a semicolon (;) statement delimiter. A REPEAT statement can be labeled. For the rules regarding label use, see Section 13.6.2, "Statement Label Syntax". Example: mysql> delimiter //mysql> CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> SET @x = 0; -> REPEAT -> SET @x = @x + 1; -> UNTIL @x > p1 END REPEAT; -> END -> //Query OK, 0 rows affected (0.00 sec)mysql> CALL dorepeat(1000)//Query OK, 0 rows affected (0.00 sec)mysql> SELECT @x//+------+| @x |+------+| 1001 |+------+1 row in set (0.00 sec) RETURN expr The RETURN statement terminates execution of a stored function and returns the value expr to the function caller. There must be at least one RETURN statement in a stored function. There may be more than one if the function has multiple exit points. This statement is not used in stored procedures, triggers, or events. The LEAVE statement can be used to exit a stored program of those types. [begin_label:] WHILE search_condition DO statement_listEND WHILE [end_label] The statement list within a WHILE statement is repeated as long as the search_condition expression is true. statement_list consists of one or more SQL statements, each terminated by a semicolon (;) statement delimiter. A WHILE statement can be labeled. For the rules regarding label use, see Section 13.6.2, "Statement Label Syntax". Example: CREATE PROCEDURE dowhile()BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO ... SET v1 = v1 - 1; END WHILE;END; MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties: Cursor declarations must appear before handler declarations and after variable and condition declarations. Example: CREATE PROCEDURE curdemo()BEGIN DECLARE done INT DEFAULT FALSE; DECLARE a CHAR(16); DECLARE b, c INT; DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur1; OPEN cur2; read_loop: LOOP FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF done THEN LEAVE read_loop; END IF; IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF; END LOOP; CLOSE cur1; CLOSE cur2;END; 13.6.6.1. Cursor CLOSE SyntaxCLOSE cursor_name This statement closes a previously opened cursor. For an example, see Section 13.6.6, "Cursors". An error occurs if the cursor is not open. If not closed explicitly, a cursor is closed at the end of the BEGIN ... END block in which it was declared. 13.6.6.2. Cursor DECLARESyntaxDECLARE cursor_name CURSOR FOR select_statement This statement declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor. To fetch the rows later, use a FETCH statement. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement. The SELECT statement cannot have an INTO clause. Cursor declarations must appear before handler declarations and after variable and condition declarations. A stored program may contain multiple cursor declarations, but each cursor declared in a given block must have a unique name. For an example, see Section 13.6.6, "Cursors". For information available through SHOW statements, it is possible in many cases to obtain equivalent information by using a cursor with an INFORMATION_SCHEMA table. 13.6.6.3. Cursor FETCH SyntaxFETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ... This statement fetches the next row for the SELECT statement associated with the specified cursor (which must be open), and advances the cursor pointer. If a row exists, the fetched columns are stored in the named variables. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement. If no more rows are available, a No Data condition occurs with SQLSTATE value '02000'. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). For an example, see Section 13.6.6, "Cursors". 13.6.6.4. Cursor OPEN SyntaxOPEN cursor_name This statement opens a previously declared cursor. For an example, see Section 13.6.6, "Cursors". 13.6.7. Condition Handling Conditions may arise during stored program execution that require special handling, such as exiting the current program block or continuing execution. Handlers can be defined for general conditions such as warnings or exceptions, or for specific conditions such as a particular error code. Specific conditions can be assigned names and referred to that way in handlers. To name a condition, use the DECLARE ... CONDITION statement. To declare a handler, use the DECLARE ... HANDLER statement. See Section 13.6.7.1, "DECLARE ... CONDITION Syntax", and Section 13.6.7.2, "DECLARE ... HANDLER Syntax". To raise a condition, use the SIGNAL statement. To modify condition information within a condition handler, use RESIGNAL. See Section 13.6.7.1, "DECLARE ... CONDITION Syntax", and Section 13.6.7.2, "DECLARE ... HANDLER Syntax". Another statement related to conditions is GET DIAGNOSTICS. The GET DIAGNOSTICS statement is not supported until MySQL 5.6. Before MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked. The following discussion demonstrates the issue and provides a workaround. Suppose that a table t1 is empty. The following procedure selects from it, raising a No Data condition: CREATE PROCEDURE p1()BEGIN DECLARE a INT; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET @handler_invoked = 1; END; SELECT c1 INTO a FROM t1;END; As can be seen from the following sequence of statements, the condition is not cleared by handler invocation (otherwise, the SHOW WARNINGS output would be empty). But as can be seen by the value of @handler_invoked, the handler was indeed invoked (otherwise its value would be 1). mysql> SET @handler_invoked = 0;Query OK, 0 rows affected (0.00 sec)mysql> CALL p1();Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+---------+------+-----------------------------------------------------+| Level | Code | Message |+---------+------+-----------------------------------------------------+| Warning | 1329 | No data - zero rows fetched, selected, or processed |+---------+------+-----------------------------------------------------+1 row in set (0.00 sec)mysql> SELECT @handler_invoked;+------------------+| @handler_invoked |+------------------+| 1 |+------------------+1 row in set (0.00 sec) There are two ways to work around this issue: Add an extra dummy statement that clears warnings at the end of the condition handler: CREATE PROCEDURE p1()BEGIN DECLARE a INT; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET @handler_invoked = 1; SELECT 1 FROM (SELECT 1) AS t; END; SELECT c1 INTO a FROM t1;END; This works for CONTINUE and EXIT handlers. Put an extra dummy statement that clears warnings following the statement that causes handler activation: CREATE PROCEDURE p1()BEGIN DECLARE a INT; DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN SET @handler_invoked = 1; END; SELECT c1 INTO a FROM t1; SELECT 1 FROM (SELECT 1) AS t;END; This works only for CONTINUE handlers.
This issue is resolved as of MySQL 5.6.3 and no workaround is needed. 13.6.7.1. DECLARE ...CONDITION SyntaxDECLARE condition_name CONDITION FOR condition_valuecondition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value The DECLARE ... CONDITION statement declares a named error condition, associating a name with a condition that needs specific handling. The name can be referred to in a subsequent DECLARE ... HANDLER statement (see Section 13.6.7.2, "DECLARE ... HANDLER Syntax"). Condition declarations must appear before cursor or handler declarations. The condition_value for DECLARE ... CONDITION can be a MySQL error code (a number) or an SQLSTATE value (a 5-character string literal). You should not use MySQL error code 0 or SQLSTATE values that begin with '00', because those indicate success rather than an error condition. For a list of MySQL error codes and SQLSTATE values, see Section C.3, "Server Error Codes and Messages". Using names for conditions can help make stored program code clearer. For example, this handler applies to attempts to drop a nonexistent table, but that is apparent only if you know the meaning of MySQL error code 1051: DECLARE CONTINUE HANDLER FOR 1051 BEGIN -- body of handler END; By declaring a name for the condition, the purpose of the handler is more readily seen: DECLARE no_such_table CONDITION FOR 1051;DECLARE CONTINUE HANDLER FOR no_such_table BEGIN -- body of handler END; Here is a named condition for the same condition, but based on the corresponding SQLSTATE value rather than the MySQL error code: DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';DECLARE CONTINUE HANDLER FOR no_such_table BEGIN -- body of handler END; Condition names referred to in SIGNAL or use RESIGNAL statements must be associated with SQLSTATE values, not MySQL error codes. 13.6.7.2. DECLARE ...HANDLER SyntaxDECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statementhandler_action: CONTINUE | EXIT | UNDOcondition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION The DECLARE ... HANDLER statement specifies a handler that deals with one or more conditions. If one of these conditions occurs, the specified statement executes. statement can be a simple statement such as SET var_name = value, or a compound statement written using BEGIN and END (see Section 13.6.1, "BEGIN ... END Compound-Statement Syntax"). Handler declarations must appear after variable or condition declarations. The handler_action value indicates what action the handler takes after execution of the handler statement: CONTINUE: Execution of the current program continues. EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block. UNDO: Not supported.
The condition_value for DECLARE ... HANDLER indicates the specific condition or class of conditions that activates the handler: A MySQL error code (a number) or an SQLSTATE value (a 5-character string literal). You should not use MySQL error code 0 or SQLSTATE values that begin with '00', because those indicate success rather than an error condition. For a list of MySQL error codes and SQLSTATE values, see Section C.3, "Server Error Codes and Messages". A condition name previously specified with DECLARE ... CONDITION. A condition name can be associated with a MySQL error code or SQLSTATE value. See Section 13.6.7.1, "DECLARE ... CONDITION Syntax". SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'. NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value '02000'. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). For an example, see Section 13.6.6, "Cursors". This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows. SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.
If a condition occurs for which no handler has been declared, the action taken depends on the condition class: For SQLEXCEPTION conditions, the stored program terminates at the statement that raised the condition, as if there were an EXIT handler. If the program was called by another stored program, the calling program handles the condition using the handler selection rules applied to its own handlers. For SQLWARNING conditions, the program continues executing, as if there were a CONTINUE handler. For NOT FOUND conditions, if the condition was raised normally, the action is CONTINUE. If it was raised by SIGNAL or RESIGNAL, the action is EXIT.
The following example uses a handler for SQLSTATE '23000', which occurs for a duplicate-key error: mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));Query OK, 0 rows affected (0.00 sec)mysql> delimiter //mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; -> SET @x = 1; -> INSERT INTO test.t VALUES (1); -> SET @x = 2; -> INSERT INTO test.t VALUES (1); -> SET @x = 3; -> END; -> //Query OK, 0 rows affected (0.00 sec)mysql> CALL handlerdemo()//Query OK, 0 rows affected (0.00 sec)mysql> SELECT @x// +------+ | @x | +------+ | 3 | +------+ 1 row in set (0.00 sec) Notice that @x is 3 after the procedure executes, which shows that execution continued to the end of the procedure after the error occurred. If the DECLARE ... HANDLER statement had not been present, MySQL would have taken the default action (EXIT) after the second INSERT failed due to the PRIMARY KEY constraint, and SELECT @x would have returned 2. To ignore a condition, declare a CONTINUE handler for it and associate it with an empty block. For example: DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END; The scope of a block label does not include the code for handlers declared within the block. Therefore, the statement associated with a handler cannot use ITERATE or LEAVE to refer to labels for blocks that enclose the handler declaration. Consider the following example, where the REPEAT block has a label of retry: CREATE PROCEDURE p ()BEGIN DECLARE i INT DEFAULT 3; retry: REPEAT BEGIN DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN ITERATE retry; # illegal END; IF i < 0 THEN LEAVE retry; # legal END IF; SET i = i - 1; END; UNTIL FALSE END REPEAT;END; The retry label is in scope for the IF statement within the block. It is not in scope for the CONTINUE handler, so the reference there is invalid and results in an error: ERROR 1308 (42000): LEAVE with no matching label: retry To avoid references to outer labels in handlers, use one of these strategies: To leave the block, use an EXIT handler. If no block cleanup is required, the BEGIN ... END handler body can be empty: DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END; Otherwise, put the cleanup statements in the handler body: DECLARE EXIT HANDLER FOR SQLWARNING BEGIN block cleanup statements END; To continue execution, set a status variable in a CONTINUE handler that can be checked in the enclosing block to determine whether the handler was invoked. The following example uses the variable done for this purpose: CREATE PROCEDURE p ()BEGIN DECLARE i INT DEFAULT 3; DECLARE done INT DEFAULT FALSE; retry: REPEAT BEGIN DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN SET done = TRUE; END; IF done OR i < 0 THEN LEAVE retry; END IF; SET i = i - 1; END; UNTIL FALSE END REPEAT;END;
13.6.7.3. RESIGNAL SyntaxRESIGNAL [condition_value] [SET signal_information_item [, signal_information_item] ...]condition_value: SQLSTATE [VALUE] sqlstate_value | condition_namesignal_information_item: condition_information_item_name = simple_value_specificationcondition_information_item_name: CLASS_ORIGIN | SUBCLASS_ORIGIN | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAMEcondition_name, simple_value_specification: (see following discussion) RESIGNAL passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored procedure or function, trigger, or event. RESIGNAL may change some or all information before passing it on. RESIGNAL is related to SIGNAL, but instead of originating a condition as SIGNAL does, RESIGNAL relays existing condition information, possibly after modifying it. RESIGNAL makes it possible to both handle an error and return the error information. Otherwise, by executing an SQL statement within the handler, information that caused the handler's activation is destroyed. RESIGNAL also can make some procedures shorter if a given handler can handle part of a situation, then pass the condition "up the line" to another handler. No special privileges are required to execute the RESIGNAL statement. For condition_value and signal_information_item, the definitions and rules are the same for RESIGNAL as for SIGNAL (see Section 13.6.7.4, "SIGNAL Syntax"). The RESIGNAL statement takes condition_value and SET clauses, both of which are optional. This leads to several possible uses: RESIGNAL alone: RESIGNAL; RESIGNAL with new signal information: RESIGNAL SET signal_information_item [, signal_information_item] ...; RESIGNAL with a condition value and possibly new signal information: RESIGNAL condition_value [SET signal_information_item [, signal_information_item] ...];
These use cases all cause changes to the diagnostics and condition areas: A diagnostics area contains one or more condition areas. A condition area contains condition information items, such as the SQLSTATE value, MYSQL_ERRNO, or MESSAGE_TEXT.
The maximum number of condition areas in a diagnostics area is determined by the value of the max_error_count system variable. 13.6.7.3.1. RESIGNAL Alone A simple RESIGNAL alone means "pass on the error with no change." It restores the last diagnostics area and makes it the current diagnostics area. That is, it "pops" the diagnostics area stack. Within a condition handler that catches a condition, one use for RESIGNAL alone is to perform some other actions, and then pass on without change the original condition information (the information that existed before entry into the handler). Example: DROP TABLE IF EXISTS xx;delimiter //CREATE PROCEDURE p ()BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET @error_count = @error_count + 1; IF @a = 0 THEN RESIGNAL; END IF; END; DROP TABLE xx;END//delimiter ;SET @error_count = 0;SET @a = 0;CALL p(); The DROP TABLE xx statement fails. The diagnostics area stack looks like this: DA 1. ERROR 1051 (42S02): Unknown table 'xx' Then execution enters the EXIT handler. It starts by pushing the top of the diagnostics area stack, which now looks like this: DA 1. ERROR 1051 (42S02): Unknown table 'xx'DA 2. ERROR 1051 (42S02): Unknown table 'xx' Usually a procedure statement clears the first diagnostics area (also called the "current" diagnostics area). BEGIN is an exception, it does not clear, it does nothing. SET is not an exception, it clears, performs the operation, and then produces a result of "success." The diagnostics area stack now looks like this: DA 1. ERROR 0000 (00000): Successful operationDA 2. ERROR 1051 (42S02): Unknown table 'xx' At this point, if @a = 0, RESIGNAL pops the diagnostics area stack, which now looks like this: DA 1. ERROR 1051 (42S02): Unknown table 'xx' And that is what the caller sees. If @a is not 0, the handler simply ends, which means that there is no more use for the last diagnostics area (it has been "handled"), so it can be thrown away. The diagnostics area stack looks like this: DA 1. ERROR 0000 (00000): Successful operation The details make it look complex, but the end result is quite useful: Handlers can execute without destroying information about the condition that caused activation of the handler. 13.6.7.3.2. RESIGNAL with New Signal Information RESIGNAL with a SET clause provides new signal information, so the statement means "pass on the error with changes": RESIGNAL SET signal_information_item [, signal_information_item] ...; As with RESIGNAL alone, the idea is to pop the diagnostics area stack so that the original information will go out. Unlike RESIGNAL alone, anything specified in the SET clause changes. Example: DROP TABLE IF EXISTS xx;delimiter //CREATE PROCEDURE p ()BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET @error_count = @error_count + 1; IF @a = 0 THEN RESIGNAL SET MYSQL_ERRNO = 5; END IF; END; DROP TABLE xx;END//delimiter ;SET @error_count = 0;SET @a = 0;CALL p(); Remember from the previous discussion that RESIGNAL alone results in a diagnostics area stack like this: DA 1. ERROR 1051 (42S02): Unknown table 'xx' The RESIGNAL SET MYSQL_ERRNO = 5 statement results in this stack instead: DA 1. ERROR 5 (42S02): Unknown table 'xx' In other words, it changes the error number, and nothing else. The RESIGNAL statement can change any or all of the signal information items, making the first condition area of the diagnostics area look quite different. 13.6.7.3.3. RESIGNAL with a Condition Value andOptional New Signal Information RESIGNAL with a condition value means "push a condition into the current diagnostics stack area." If the SET clause is present, it also changes the error information. RESIGNAL condition_value [SET signal_information_item [, signal_information_item] ...]; This form of RESIGNAL restores the last diagnostics area and makes it the current diagnostics area. That is, it "pops" the diagnostics area stack, which is the same as what a simple RESIGNAL alone would do. However, it also changes the diagnostics area depending on the condition value or signal information. Example: DROP TABLE IF EXISTS xx;delimiter //CREATE PROCEDURE p ()BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET @error_count = @error_count + 1; IF @a = 0 THEN RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=5; END IF; END; DROP TABLE xx;END//delimiter ;SET @error_count = 0;SET @a = 0;SET @@max_error_count = 2;CALL p();SHOW ERRORS; This is similar to the previous example, and the effects are the same, except that if RESIGNAL happens the current condition area looks different at the end. (The reason the condition is added rather than replaced is the use of a condition value.) The RESIGNAL statement includes a condition value (SQLSTATE '45000'), so it "pushes" a new condition area, resulting in a diagnostics area stack that looks like this: DA 1. (condition 1) ERROR 5 (45000) Unknown table 'xx' (condition 2) ERROR 1051 (42S02): Unknown table 'xx' The result of CALL p() and SHOW ERRORS for this example is: mysql> CALL p();ERROR 5 (45000): Unknown table 'xx'mysql> SHOW ERRORS;+-------+------+----------------------------------+| Level | Code | Message |+-------+------+----------------------------------+| Error | 5 | Unknown table 'xx' || Error | 1051 | Unknown table 'xx' |+-------+------+----------------------------------+ 13.6.7.3.4. RESIGNAL Requires an Active Handler All forms of RESIGNAL require that a handler be active when it executes. If no handler is active, RESIGNAL is illegal and a resignal when handler not active error occurs. For example: mysql> CREATE PROCEDURE p () RESIGNAL;Query OK, 0 rows affected (0.00 sec)mysql> CALL p();ERROR 1739 (0K000): RESIGNAL when handler not active Here is a more difficult example: delimiter //CREATE FUNCTION f () RETURNS INTBEGIN RESIGNAL; RETURN 5;END//CREATE PROCEDURE p ()BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @a=f(); SIGNAL SQLSTATE '55555';END//delimiter ;CALL p(); At the time the RESIGNAL executes, there is a handler, even though the RESIGNAL is not defined inside the handler. A statement such as the one following may appear bizarre because RESIGNAL apparently is not in a handler: CREATE TRIGGER t_bi BEFORE INSERT ON t FOR EACH ROW RESIGNAL; But it does not matter. RESIGNAL does not have to be technically "in" (that is, contained in), a handler declaration. The requirement is that a handler must be active. SIGNAL condition_value [SET signal_information_item [, signal_information_item] ...]condition_value: SQLSTATE [VALUE] sqlstate_value | condition_namesignal_information_item: condition_information_item_name = simple_value_specificationcondition_information_item_name: CLASS_ORIGIN | SUBCLASS_ORIGIN | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAMEcondition_name, simple_value_specification: (see following discussion) SIGNAL is the way to "return" an error. SIGNAL provides error information to a handler, to an outer portion of the application, or to the client. Also, it provides control over the error's characteristics (error number, SQLSTATE value, message). Without SIGNAL, it is necessary to resort to workarounds such as deliberately referring to a nonexistent table to cause a routine to return an error. No special privileges are required to execute the SIGNAL statement. The condition_value in a SIGNAL statement indicates the error value to be returned. It can be an SQLSTATE value (a 5-character string literal) or a condition_name that refers to a named condition previously defined with DECLARE ... CONDITION (see Section 13.6.7.1, "DECLARE ... CONDITION Syntax"). An SQLSTATE value can indicate errors, warnings, or "not found." The first two characters of the value indicate its error class, as discussed in Section 13.6.7.4.1, "Signal Condition Information Items". Some signal values cause statement termination; see Section 13.6.7.4.2, "Effect of Signals on Handlers, Cursors, and Statements". The SQLSTATE value for a SIGNAL statement should not start with '00' because such values indicate success and are not valid for signaling an error. This is true whether the SQLSTATE value is specified directly in the SIGNAL statement or in a named condition referred to in the statement. If the value is invalid, a Bad SQLSTATE error occurs. To signal a generic SQLSTATE value, use '45000', which means "unhandled user-defined exception." The SIGNAL statement optionally includes a SET clause that contains multiple signal items, in a comma-separated list of condition_information_item_name = simple_value_specification assignments. Each condition_information_item_name may be specified only once in the SET clause. Otherwise, a Duplicate condition information item error occurs. Valid simple_value_specification designators can be specified using stored procedure or function parameters, stored program local variables declared with DECLARE, user-defined variables, system variables, or literals. A character literal may include a _charset introducer. For information about permissible condition_information_item_name values, see Section 13.6.7.4.1, "Signal Condition Information Items". The following procedure signals an error or warning depending on the value of pval, its input parameter: CREATE PROCEDURE p (pval INT)BEGIN DECLARE specialty CONDITION FOR SQLSTATE '45000'; IF pval = 0 THEN SIGNAL SQLSTATE '01000'; ELSEIF pval = 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred'; ELSEIF pval = 2 THEN SIGNAL specialty SET MESSAGE_TEXT = 'An error occurred'; ELSE SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'A warning occurred', MYSQL_ERRNO = 1000; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1001; END IF;END; If pval is 0, p() signals a warning because SQLSTATE values that begin with '01' are signals in the warning class. The warning does not terminate the procedure, and can be seen with SHOW WARNINGS after the procedure returns. If pval is 1, p() signals an error and sets the MESSAGE_TEXT condition information item. The error terminates the procedure, and the text is returned with the error information. If pval is 2, the same error is signaled, although the SQLSTATE value is specified using a named condition in this case. If pval is anything else, p() first signals a warning and sets the message text and error number condition information items. This warning does not terminate the procedure, so execution continues and p() then signals an error. The error does terminate the procedure. The message text and error number set by the warning are replaced by the values set by the error, which are returned with the error information. SIGNAL is typically used within stored programs, but it is a MySQL extension that it is permitted outside that context. For example, if you invoke the mysql client program, you can enter any of these statements at the prompt: mysql> SIGNAL SQLSTATE '77777';mysql> CREATE TRIGGER t_bi BEFORE INSERT ON t -> FOR EACH ROW SIGNAL SQLSTATE '77777';mysql> CREATE EVENT e ON SCHEDULE EVERY 1 SECOND -> DO SIGNAL SQLSTATE '77777'; SIGNAL executes according to the following rules: If the SIGNAL statement indicates a particular SQLSTATE value, that value is used to signal the condition specified. Example: CREATE PROCEDURE p (divisor INT)BEGIN IF divisor = 0 THEN SIGNAL SQLSTATE '22012'; END IF;END; If the SIGNAL statement uses a named condition, the condition must be declared in some scope that applies to the SIGNAL statement, and must be defined using an SQLSTATE value, not a MySQL error number. Example: CREATE PROCEDURE p (divisor INT)BEGIN DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'; IF divisor = 0 THEN SIGNAL divide_by_zero; END IF;END; If the named condition does not exist in the scope of the SIGNAL statement, an Undefined CONDITION error occurs. If SIGNAL refers to a named condition that is defined with a MySQL error number rather than an SQLSTATE value, a SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE error occurs. The following statements cause that error because the named condition is associated with a MySQL error number: DECLARE no_such_table CONDITION FOR 1051;SIGNAL no_such_table; If a condition with a given name is declared multiple times in different scopes, the declaration with the most local scope applies. Consider the following procedure: CREATE PROCEDURE p (divisor INT)BEGIN DECLARE my_error CONDITION FOR SQLSTATE '45000'; IF divisor = 0 THEN BEGIN DECLARE my_error CONDITION FOR SQLSTATE '22012'; SIGNAL my_error; END; END IF; SIGNAL my_error;END; If divisor is 0, the first SIGNAL statement executes. The innermost my_error condition declaration applies, raising SQLSTATE '22012'. If divisor is not 0, the second SIGNAL statement executes. The outermost my_error condition declaration applies, raising SQLSTATE '45000'. Signals can be raised within exception handlers: CREATE PROCEDURE p ()BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'An error occurred'; END; DROP TABLE no_such_table;END; CALL p() reaches the DROP TABLE statement. There is no table named no_such_table, so the error handler is activated. The error handler destroys the original error ("no such table") and makes a new error with SQLSTATE '99999' and message An error occurred. 13.6.7.4.1. Signal Condition Information Items The following table lists the names of diagnostics area condition information items that can be set in a SIGNAL (or RESIGNAL) statement. All items are standard SQL except MYSQL_ERRNO, which is a MySQL extension. Item Name Definition--------- ----------CLASS_ORIGIN VARCHAR(64)SUBCLASS_ORIGIN VARCHAR(64)CONSTRAINT_CATALOG VARCHAR(64)CONSTRAINT_SCHEMA VARCHAR(64)CONSTRAINT_NAME VARCHAR(64)CATALOG_NAME VARCHAR(64)SCHEMA_NAME VARCHAR(64)TABLE_NAME VARCHAR(64)COLUMN_NAME VARCHAR(64)CURSOR_NAME VARCHAR(64)MESSAGE_TEXT VARCHAR(128)MYSQL_ERRNO SMALLINT UNSIGNED The character set for character items is UTF-8. It is illegal to assign NULL to a condition information item in a SIGNAL statement. A SIGNAL statement always specifies an SQLSTATE value, either directly, or indirectly by referring to a named condition defined with an SQLSTATE value. The first two characters of an SQLSTATE value are its class, and the class determines the default value for the condition information items: Class = '00' (success) Illegal. SQLSTATE values that begin with '00' indicate success and are not valid for SIGNAL. Class = '01' (warning) MESSAGE_TEXT = 'Unhandled user-defined warning';MYSQL_ERRNO = ER_SIGNAL_WARN Class = '02' (not found) MESSAGE_TEXT = 'Unhandled user-defined not found';MYSQL_ERRNO = ER_SIGNAL_NOT_FOUND Class > '02' (exception) MESSAGE_TEXT = 'Unhandled user-defined exception';MYSQL_ERRNO = ER_SIGNAL_EXCEPTION
For legal classes, the other condition information items are set as follows: CLASS_ORIGIN = SUBCLASS_ORIGIN = '';CONSTRAINT_CATALOG = CONSTRAINT_SCHEMA = CONSTRAINT_NAME = '';CATALOG_NAME = SCHEMA_NAME = TABLE_NAME = COLUMN_NAME = '';CURSOR_NAME = ''; The error values that are accessible after SIGNAL executes are the SQLSTATE value raised by the SIGNAL statement and the MESSAGE_TEXT and MYSQL_ERRNO items. These values are available from the C API: From SQL, the output from SHOW WARNINGS and SHOW ERRORS indicates the MYSQL_ERRNO and MESSAGE_TEXT values in the Code and Message columns. Other condition information items can be set, but currently have no effect, in the sense that they are not accessible from error returns. For example, you can set CLASS_ORIGIN in a SIGNAL statement, but cannot see it after SIGNAL executes. In MySQL 5.6, condition information can be inspected with the GET DIAGNOSTICS statement. 13.6.7.4.2. Effect of Signals on Handlers, Cursors, and Statements Signals have different effects on statement execution depending on the signal class. The class determines how severe an error is. MySQL ignores the value of the sql_mode system variable; in particular, strict SQL mode does not matter. MySQL also ignores IGNORE: The intent of SIGNAL is to raise a user-generated error explicitly, so a signal is never ignored. In the following descriptions, "unhandled" means that no handler for the signaled SQLSTATE value has been defined with DECLARE ... HANDLER. Class = '00' (success) Illegal. SQLSTATE values that begin with '00' indicate success and are not valid for SIGNAL. Class = '01' (warning) The value of the warning_count system variable goes up. SHOW WARNINGS shows the signal. SQLWARNING handlers catch the signal. If the signal is unhandled in a function, statements do not end. Class = '02' (not found) NOT FOUND handlers catch the signal. There is no effect on cursors. If the signal is unhandled in a function, statements end. Class > '02' (exception) SQLEXCEPTION handlers catch the signal. If the signal is unhandled in a function, statements end. Class = '40' Treated as an ordinary exception.
Example: mysql> delimiter //mysql> CREATE FUNCTION f () RETURNS INT -> BEGIN -> SIGNAL SQLSTATE '01234'; -- signal a warning -> RETURN 5; -> END//mysql> delimiter ;mysql> CREATE TABLE t (s1 INT);mysql> INSERT INTO t VALUES (f()); The result is that a row containing 5 is inserted into table t. The warning that is signaled can be viewed with SHOW WARNINGS. Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
| |
