Skip navigation links
14.2.4 InnoDB Performance Tuning and Troubleshooting »
Section Navigation      [Toggle]

14.2.3.14. InnoDB Error Handling

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.

14.2.3.14.1. InnoDB Error Codes

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.

14.2.3.14.2. Operating System Error Codes

To print the meaning of an operating system error number, use the perror program that comes with the MySQL distribution.

  • Linux System Error Codes

    The following table provides a list of some common Linux system error codes. For a more complete list, see Linux source code.

    NumberMacroDescription
    1EPERMOperation not permitted
    2ENOENTNo such file or directory
    3ESRCHNo such process
    4EINTRInterrupted system call
    5EIOI/O error
    6ENXIONo such device or address
    7E2BIGArg list too long
    8ENOEXECExec format error
    9EBADFBad file number
    10ECHILDNo child processes
    11EAGAINTry again
    12ENOMEMOut of memory
    13EACCESPermission denied
    14EFAULTBad address
    15ENOTBLKBlock device required
    16EBUSYDevice or resource busy
    17EEXISTFile exists
    18EXDEVCross-device link
    19ENODEVNo such device
    20ENOTDIRNot a directory
    21EISDIRIs a directory
    22EINVALInvalid argument
    23ENFILEFile table overflow
    24EMFILEToo many open files
    25ENOTTYInappropriate ioctl for device
    26ETXTBSYText file busy
    27EFBIGFile too large
    28ENOSPCNo space left on device
    29ESPIPEFile descriptor does not allow seeking
    30EROFSRead-only file system
    31EMLINKToo many links

  • Windows System Error Codes

    The following table provides a list of some common Windows system error codes. For a complete list, see the Microsoft Web site.

    NumberMacroDescription
    1ERROR_INVALID_FUNCTIONIncorrect function.
    2ERROR_FILE_NOT_FOUNDThe system cannot find the file specified.
    3ERROR_PATH_NOT_FOUNDThe system cannot find the path specified.
    4ERROR_TOO_MANY_OPEN_FILESThe system cannot open the file.
    5ERROR_ACCESS_DENIEDAccess is denied.
    6ERROR_INVALID_HANDLEThe handle is invalid.
    7ERROR_ARENA_TRASHEDThe storage control blocks were destroyed.
    8ERROR_NOT_ENOUGH_MEMORYNot enough storage is available to process this command.
    9ERROR_INVALID_BLOCKThe storage control block address is invalid.
    10ERROR_BAD_ENVIRONMENTThe environment is incorrect.
    11ERROR_BAD_FORMATAn attempt was made to load a program with an incorrect format.
    12ERROR_INVALID_ACCESSThe access code is invalid.
    13ERROR_INVALID_DATAThe data is invalid.
    14ERROR_OUTOFMEMORYNot enough storage is available to complete this operation.
    15ERROR_INVALID_DRIVEThe system cannot find the drive specified.
    16ERROR_CURRENT_DIRECTORYThe directory cannot be removed.
    17ERROR_NOT_SAME_DEVICEThe system cannot move the file to a different disk drive.
    18ERROR_NO_MORE_FILESThere are no more files.
    19ERROR_WRITE_PROTECTThe media is write protected.
    20ERROR_BAD_UNITThe system cannot find the device specified.
    21ERROR_NOT_READYThe device is not ready.
    22ERROR_BAD_COMMANDThe device does not recognize the command.
    23ERROR_CRCData error (cyclic redundancy check).
    24ERROR_BAD_LENGTHThe program issued a command but the command length is incorrect.
    25ERROR_SEEKThe drive cannot locate a specific area or track on the disk.
    26ERROR_NOT_DOS_DISKThe specified disk or diskette cannot be accessed.
    27ERROR_SECTOR_NOT_FOUNDThe drive cannot find the sector requested.
    28ERROR_OUT_OF_PAPERThe printer is out of paper.
    29ERROR_WRITE_FAULTThe system cannot write to the specified device.
    30ERROR_READ_FAULTThe system cannot read from the specified device.
    31ERROR_GEN_FAILUREA device attached to the system is not functioning.
    32ERROR_SHARING_VIOLATIONThe process cannot access the file because it is being used by another process.
    33ERROR_LOCK_VIOLATIONThe process cannot access the file because another process has locked a portion of the file.
    34ERROR_WRONG_DISKThe wrong diskette is in the drive. Insert %2 (Volume Serial Number: %3) into drive %1.
    36ERROR_SHARING_BUFFER_EXCEEDEDToo many files opened for sharing.
    38ERROR_HANDLE_EOFReached the end of the file.
    39ERROR_HANDLE_DISK_FULLThe disk is full.
    87ERROR_INVALID_PARAMETERThe parameter is incorrect.
    112ERROR_DISK_FULLThe disk is full.
    123ERROR_INVALID_NAMEThe file name, directory name, or volume label syntax is incorrect.
    1450ERROR_NO_SYSTEM_RESOURCESInsufficient system resources exist to complete the requested service.
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices