[ERROR] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction'

In situation of table/row locking or deadlock on slave server, slave server let the transaction wait for time configured with  innodb_lock_wait_timeout and retry the transaction for the times configured in  slave_transaction_retries.  After all tries and lock timeout save server throws following error in logs.

130125 13:24:30 [ERROR] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'live'. Query: 'INSERT into users (user_id, login, password, first_name, last_name, email, is, created, changed, picture, last_login, token) values (1, 'asad9@yahoo.com', '35ce1d4eb0', 'Prince A', 'S', 'asad9@yahoo.com', 1, 13, 13, 'photo_13.jpg', 13, NULL)', Error_code: 1205

Get all the deadlocks and locking transaction details and tables involved in locking using:

#mysql >show engine innodb status\G;

To avoid further locking you need to do changes at application to prevent bulk updates and select simultaneously. Also change storage engine MyISAM which use table locking to InnoDB which use row level locking.

The simple solution for this problem is to increase the value for innodb_lock_wait_timeout and slave_transaction_retries.

 There are other possible reason for this error: table is corrupt so repair the table.

http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-innodb_lock_wait_timeout.html
http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#sysvar_slave_transaction_retries

mysqldump: Got error: 1146: Table 'tablename' doesn’t exist when doing LOCK TABLES

if you receive the error message while mysql database backup

There are few possible reason of this error:

• permission and ownership on tables files in /var/lib/mysql is not correct, ideal ownership should be to mysql user and permission 660.
• table not exist, check with show tables.
• table created in uppercase, set lower_case_table_names as per your application requirement.
• table is corrupt, repair the table.

if all above points are ok then use --skip-lock-tables parameter with mysqldump.

I am sure this will resolve the error.

 

mysqldump: Got error: 1066: Not unique table/alias: foo when using LOCK TABLES

mysqldump trying to lock lock table before starting a database dump but not able to lock and throwing this error.
After analyzing table names carefully i found few tables duplicate table exist with names in Uppercase and lowercase when lower_case_table_names=1

mysql> show tables;
+-----------------------------------+
| Tables_in_power         |
+-----------------------------------+
| Message_seq                       |
| User_seq                          |
| message_seq                |
| user_seq                |
+-----------------------------------+ 

Resolved by removing duplicate table message_seq  and user_seq.

This issue of duplicate tables occurs when application issue a CREATE TABLE command with table name in upper case or lower case  other than the existing tables with same name and lower_case_table_names=1.

For permanent resolution change  lower_case_table_names=0 if application don't have any dependency on it.

 

native code=1030 ** Got error 139 from storage engine

Recently in one of my application logs I see insert failing with "native code=1030 ** Got error 139 from storage engine" error, database was in production for more than 2 years and never see this error. This seems to be simple problem but due to its nature it may affect after you already have a running database in production which is in my case. This is storage engine specific error, InnoDB.
This is problem related to innodb row size limitations and storage of tablespace on disks.
In short InnoDB gives this error when it can't store all variable length column for a given row on single database page. So,

What is database page?
What is InnoDB row size limit?
How database page relate to row length?

Database pages are the internal basic structure to organize the data in the database files. The data file you define in configuration file logically concatenated to form the tablespace, The tablespace consists of database pages with default size 16 K. ( In Mysql 5.6 it is possible to change the page size to 4k or 8k in addition to original size 16k by putting  innodb_page_size=n variable in configuration file or you can set –innodb-page-size=n when starting mysqld, Previously, it could be done by recompiling the engine with a different value for UNIV_PAGE_SIZE_SHIFT and UNIV_PAGE_SIZE.) 

 Mysql store two rows on single page of 16 K, So the maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.
If a row length is less than half a page long ~ 8000 bytes, all of it is stored locally within the single database page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page. For a column chosen for off-page storage, InnoDB stores the first 768 bytes locally in the row, and the rest externally into overflow pages.

Now the real problem comes now, if you have more than 10 columns of variable length type TEXT,BLOB and each column exceeding database page size then InnoDB needs 768 x 11 = 8448 bytes to store variable lenght columns not counting other fixed length columns. This exceeds the limit and therefore you get the error. 

Few suggestions to solve this issue:

1. Upgrade to Barracuda format : Barracuda file format use 20 bytes of variables length columns on single database page rather than 768 bytes. so now more data in a row can be stored on a page without any size error.

SET GLOBAL innodb_file_format=Barracuda; 

SET GLOBAL innodb_file_per_table=ON; 

ALTER TABLE (your table) ROW_FORMAT=COMPRESSED;

mysql> show table status like 'company_info'\G
*************************** 1. row ***************************
           Name: company_info
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 13
 Avg_row_length: 32768
    Data_length: 425984
Max_data_length: 0
   Index_length: 32768
      Data_free: 0
 Auto_increment: 39
    Create_time: 2013-01-11 17:49:55
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 0 kB
1 row in set (0.00 sec)


mysql> alter table company_info row_format=compressed;
Query OK, 22 rows affected (0.83 sec)
Records: 22  Duplicates: 0  Warnings: 0

mysql> show table status like 'company_info'\G
*************************** 1. row ***************************
           Name: company_info
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 13
 Avg_row_length: 31507
    Data_length: 409600
Max_data_length: 0
   Index_length: 32768
      Data_free: 0
 Auto_increment: 39
    Create_time: 2013-01-11 18:36:45
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: InnoDB free: 0 kB
1 row in set (0.00 sec)

2. Limit the seize of variable length column, that might need change at application side.
3. Limit table to have upto 10 columns of variable length type.
4. Upgrade to latest mysql version 5.6 which have flexibility setting default database page size of 16K with innodb_page_size variable.