Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
Error handling in InnoDB is not always the same as specified in the SQL standard.
According to the standard, any error during an SQL statement should cause rollback of that statement. InnoDB sometimes rolls back only part of the statement, or the whole transaction.
The following items describe how InnoDB performs error handling:
If you run out of file space in a tablespace, a MySQL
Table is full error occurs and InnoDB rolls
back the SQL statement.
A transaction deadlock
causes InnoDB to roll back the entire transaction. Retry
the whole transaction when this happens.
A lock wait timeout causes InnoDB to roll back only the single
statement that was waiting for the lock and encountered the timeout. (To have the entire transaction
roll back, start the server with the --innodb_rollback_on_timeout option.) Retry the statement if using
the current behavior, or the entire transaction if using --innodb_rollback_on_timeout.
Both deadlocks and lock wait timeouts are normal on busy servers and it is necessary for applications to be aware that they may happen and handle them by retrying. You can make them less likely by doing as little work as possible between the first change to data during a transaction and the commit, so the locks are held for the shortest possible time and for the smallest possible number of rows. Sometimes splitting work between different transactions may be practical and helpful.
When a transaction rollback occurs due to a deadlock or lock wait timeout, it cancels the effect of
the statements within the transaction. But if the start-transaction statement was START TRANSACTION or BEGIN statement, rollback does not cancel that statement. Further
SQL statements become part of the transaction until the occurrence of COMMIT, ROLLBACK, or some SQL statement that causes an implicit commit.
A duplicate-key error rolls back the SQL statement, if you have not specified the
IGNORE option in your statement.
A row too long error rolls back the SQL statement.
Other errors are mostly detected by the MySQL layer of code (above the InnoDB storage engine level), and they roll back the corresponding SQL
statement. Locks are not released in a rollback of a single SQL statement.
During implicit rollbacks, as well as during the execution of an explicit ROLLBACK SQL statement, SHOW PROCESSLIST displays Rolling back in the State column for the relevant connection.
The following is a nonexhaustive list of common InnoDB-specific errors that you
may encounter, with information about why each occurs and how to resolve the problem.
1005 (ER_CANT_CREATE_TABLE)
Cannot create table. If the error message refers to error 150, table creation failed because a
foreign
key constraint was not correctly formed. If the error message refers to error –1, table
creation probably failed because the table includes a column name that matched the name of an
internal InnoDB table.
1016 (ER_CANT_OPEN_FILE)
Cannot find the InnoDB table from the InnoDB data files, although the .frm file for the table exists. See Section
14.2.4.7, "Troubleshooting InnoDB Data Dictionary Operations".
1114 (ER_RECORD_FILE_FULL)
InnoDB has run out of free space in the tablespace. Reconfigure the
tablespace to add a new data file.
1205 (ER_LOCK_WAIT_TIMEOUT)
Lock wait timeout expired. The statement that waited too long was rolled
back (not the entire transaction). You can increase the value of
the innodb_lock_wait_timeout configuration option if SQL
statements should wait longer for other transactions to complete, or decrease it if too many
long-running transactions are causing locking problems and reducing concurrency
on a busy system.
1206 (ER_LOCK_TABLE_FULL)
The total number of locks exceeds the amount of memory InnoDB
devotes to managing locks. To avoid this error, increase the value of innodb_buffer_pool_size. Within an individual application, a
workaround may be to break a large operation into smaller pieces. For example, if the error
occurs for a large INSERT,
perform several smaller INSERT
operations.
1213 (ER_LOCK_DEADLOCK)
The transaction
encountered a deadlock
and was automatically rolled
back so that your application could take corrective action. To recover from this error,
run all the operations in this transaction again. A deadlock occurs when requests for locks
arrive in inconsistent order between transactions. The transaction that was rolled back released
all its locks, and the other transaction can now get all the locks it requested. Thus when you
re-run the transaction that was rolled back, it might have to wait for other transactions to
complete, but typically the deadlock does not recur. If you encounter frequent deadlocks, make
the sequence of locking operations (LOCK TABLES, SELECT ... FOR UPDATE, and so on) consistent between the
different transactions or applications that experience the issue. See Section
14.2.3.10, "How to Cope with Deadlocks" for details.
1216 (ER_NO_REFERENCED_ROW)
You are trying to add a row but there is no parent row, and a foreign key constraint fails. Add the parent row first.
1217 (ER_ROW_IS_REFERENCED)
You are trying to delete a parent row that has children, and a foreign key constraint fails. Delete the children first.
To print the meaning of an operating system error number, use the perror program that comes with the MySQL distribution.
The following table provides a list of some common Linux system error codes. For a more complete list, see .
| Number | Macro | Description |
|---|---|---|
| 1 | EPERM |
Operation not permitted |
| 2 | ENOENT |
No such file or directory |
| 3 | ESRCH |
No such process |
| 4 | EINTR |
Interrupted system call |
| 5 | EIO |
I/O error |
| 6 | ENXIO |
No such device or address |
| 7 | E2BIG |
Arg list too long |
| 8 | ENOEXEC |
Exec format error |
| 9 | EBADF |
Bad file number |
| 10 | ECHILD |
No child processes |
| 11 | EAGAIN |
Try again |
| 12 | ENOMEM |
Out of memory |
| 13 | EACCES |
Permission denied |
| 14 | EFAULT |
Bad address |
| 15 | ENOTBLK |
Block device required |
| 16 | EBUSY |
Device or resource busy |
| 17 | EEXIST |
File exists |
| 18 | EXDEV |
Cross-device link |
| 19 | ENODEV |
No such device |
| 20 | ENOTDIR |
Not a directory |
| 21 | EISDIR |
Is a directory |
| 22 | EINVAL |
Invalid argument |
| 23 | ENFILE |
File table overflow |
| 24 | EMFILE |
Too many open files |
| 25 | ENOTTY |
Inappropriate ioctl for device |
| 26 | ETXTBSY |
Text file busy |
| 27 | EFBIG |
File too large |
| 28 | ENOSPC |
No space left on device |
| 29 | ESPIPE |
File descriptor does not allow seeking |
| 30 | EROFS |
Read-only file system |
| 31 | EMLINK |
Too many links |
The following table provides a list of some common Windows system error codes. For a complete list, see the .
| Number | Macro | Description |
|---|---|---|
| 1 | ERROR_INVALID_FUNCTION |
Incorrect function. |
| 2 | ERROR_FILE_NOT_FOUND |
The system cannot find the file specified. |
| 3 | ERROR_PATH_NOT_FOUND |
The system cannot find the path specified. |
| 4 | ERROR_TOO_MANY_OPEN_FILES |
The system cannot open the file. |
| 5 | ERROR_ACCESS_DENIED |
Access is denied. |
| 6 | ERROR_INVALID_HANDLE |
The handle is invalid. |
| 7 | ERROR_ARENA_TRASHED |
The storage control blocks were destroyed. |
| 8 | ERROR_NOT_ENOUGH_MEMORY |
Not enough storage is available to process this command. |
| 9 | ERROR_INVALID_BLOCK |
The storage control block address is invalid. |
| 10 | ERROR_BAD_ENVIRONMENT |
The environment is incorrect. |
| 11 | ERROR_BAD_FORMAT |
An attempt was made to load a program with an incorrect format. |
| 12 | ERROR_INVALID_ACCESS |
The access code is invalid. |
| 13 | ERROR_INVALID_DATA |
The data is invalid. |
| 14 | ERROR_OUTOFMEMORY |
Not enough storage is available to complete this operation. |
| 15 | ERROR_INVALID_DRIVE |
The system cannot find the drive specified. |
| 16 | ERROR_CURRENT_DIRECTORY |
The directory cannot be removed. |
| 17 | ERROR_NOT_SAME_DEVICE |
The system cannot move the file to a different disk drive. |
| 18 | ERROR_NO_MORE_FILES |
There are no more files. |
| 19 | ERROR_WRITE_PROTECT |
The media is write protected. |
| 20 | ERROR_BAD_UNIT |
The system cannot find the device specified. |
| 21 | ERROR_NOT_READY |
The device is not ready. |
| 22 | ERROR_BAD_COMMAND |
The device does not recognize the command. |
| 23 | ERROR_CRC |
Data error (cyclic redundancy check). |
| 24 | ERROR_BAD_LENGTH |
The program issued a command but the command length is incorrect. |
| 25 | ERROR_SEEK |
The drive cannot locate a specific area or track on the disk. |
| 26 | ERROR_NOT_DOS_DISK |
The specified disk or diskette cannot be accessed. |
| 27 | ERROR_SECTOR_NOT_FOUND |
The drive cannot find the sector requested. |
| 28 | ERROR_OUT_OF_PAPER |
The printer is out of paper. |
| 29 | ERROR_WRITE_FAULT |
The system cannot write to the specified device. |
| 30 | ERROR_READ_FAULT |
The system cannot read from the specified device. |
| 31 | ERROR_GEN_FAILURE |
A device attached to the system is not functioning. |
| 32 | ERROR_SHARING_VIOLATION |
The process cannot access the file because it is being used by another process. |
| 33 | ERROR_LOCK_VIOLATION |
The process cannot access the file because another process has locked a portion of the file. |
| 34 | ERROR_WRONG_DISK |
The wrong diskette is in the drive. Insert %2 (Volume Serial Number: %3)into drive %1. |
| 36 | ERROR_SHARING_BUFFER_EXCEEDED |
Too many files opened for sharing. |
| 38 | ERROR_HANDLE_EOF |
Reached the end of the file. |
| 39 | ERROR_HANDLE_DISK_FULL |
The disk is full. |
| 87 | ERROR_INVALID_PARAMETER |
The parameter is incorrect. |
| 112 | ERROR_DISK_FULL |
The disk is full. |
| 123 | ERROR_INVALID_NAME |
The file name, directory name, or volume label syntax is incorrect. |
| 1450 | ERROR_NO_SYSTEM_RESOURCES |
Insufficient system resources exist to complete the requested service. |