The total number of locks exceeds the lock table size

What Problems? 

When you run an operation on large number of rows within a table that use the innodb storage engine, You may see errors.

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

What for?

When an operation's row number is large and InnoDB's buffer pool size is small, You will see this error.

What can I do for fixing Problem?

You should increase innodb_buffer_pool_size's value.

or

You should decrease operation's size.

MySQL InnoDB Huge page Error

What Problems? 

When you configure large-pages system variables enable, You may see errors.

InnoDB: HugeTLB: Warning: Failed to allocate 4194304 bytes. errno 12
InnoDB HugeTLB: Warning: Using conventional memory pool

What for?

In MySQL, InnoDB support large pages to allocate memory for its buffer pool.  So, When you confugre large-pages variables enable for using large page, The system must be configure 4MB as Huge page.
If huge page's size is less than 4 MB, You must increase greater than 4MB.

What can I do for fixing Problem?

You should increase greater than 4MB.
You can find current page size.

grep -i huge /proc/meminfo

HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

You can see more detail this url.
http://dev.mysql.com/doc/refman/5.6/en/large-page-support.html

MySQL 5.7 - General Tablespaces

General tablespace is new type of InnoDB tablespace which introduced in MySQL 5.7.6.

1. General tablespace stores several tables like shared tablespace.

2. General tablespace supports Antelope and Barracuda file format.

3. General tablespace file uses .ibd extention.

4. You can create table in general tablespace executing CREATE TABLE ... TABLESPACE command.

4. You can move table each tablespace executing ALTER TABLE ... TABLESPACE command.

Create General Tablespace

CREATE TABLESPACE tablespace_name

   ADD DATAFILE ‘file_name’

  [FILE_BLOCK_SIZE = value]

    [ ENGINE [=] engine_name ]

for example,

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE ‘ts1.ibd’  Engine=InnoDB ;

You can specify a particular directory path. 

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE ‘./my_tablespace/ts1.ibd’ Engine=InnoDB ;

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE ‘/my/tablespace/directory/ts1/ibd’ Engine=InnoDB ;

Create table in General tablespace

After create general tablespace, you can create or move table in general tablespace with this command.

CREATE TABLE:

mysql> CREATE TABLE t1 ( c1 INT PRIMARY KEY ) TABLESPACE ts1  ROW_FORMAT = COMPACT ;

or

ALTER TABLE:

mysql> ALTER TABLE t2 TABLESPACE ts1; 

General tablespace supports all row format - REDUNDANT, COMPACT, DYNAMIC, COMPRESSED .

When compressed table stored general tablespace, which tablespace can not store non-compressed table, because each table has different page size. 

General tablespace must be specified with FILE_BLOCK_SIZE option for storing compressed table. ( ROW_FORMAT = COMPRESSED )

For example, innodb_page_size is 16K , FILE_BLOCK_SIZE is 8K , then KEY_BLOCK_SIZE of the table must be 8.

Follow the examples.

First, create general tablespace. And storing compressed table.

CREATE TABLESPACE `ts2` ADD DATAFILE ‘ts2.ibd’ FILE_BLOCK_SIZE = 8192 Engine = InnoDB ;

CREATE TABLE t4 ( c1 INT PRIMARY KEY ) TABLESPACE t2 ROW_FORMAT = COMPRESSED  KEY_BLOCK_SIZE= 8  ;

This general tablespace's FILE_BLOCK_SIZE is 8192, and compressed table has 8KB KEY_BLOCK_SIZE.

Drop General tablespace

When drop general tablespace, execute "DROP TABLESPACE ..." command.

General tablespace will be dropped must be empty. 

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB; 

Query OK, 0 rows affected (0.01 sec) 

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts10 Engine=InnoDB; 

Query OK, 0 rows affected (0.02 sec) 

mysql> DROP TABLE t1; 

Query OK, 0 rows affected (0.01 sec) 

mysql> DROP TABLESPACE ts1; 

Query OK, 0 rows affected (0.01 sec) 

MySQL 5.6.14 Replication Bug with grant command

When I executed "GRANT" command with `database` option on Master DB - MySQL 5.6.14, error occur on Slave DB and replication stop.
It is bug only on MySQL 5.6.14. (http://bugs.mysql.com/bug.php?id=68892)

The error message is below.



mysql> show slave status \G

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

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.242.147
             Slave_IO_Running: Yes
            Slave_SQL_Running: No           <-- SQL Thread stop.
            ...
            ...
            Last_SQL_Errno: 1590
            Last_SQL_Error: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log    <-- LOST_EVENTS error.
            ...



"GRANT" command with database option ( 'database_name'.'*' ) occur the error 1589 on Slave DB.




mysql> grant show databases on test.* to 'hong'@'10.10.10.10';

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES


mysql> grant reload  on test.* to 'test'@'10.10.10.10';

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES


mysql> grant process on test.* to 'test'@'10.10.10.10';

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES


mysql> grant file on test.* to 'test'@'10.10.10.10';

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES


## Error is not occur.

mysql> grant select, insert, update, delete on  test.* to 'test'@'10.10.10.10';

Query OK, 0 rows affected (0.00 sec)


## Add file right, then error is occur.

mysql> grant select, insert, update, delete, file on  test.* to 'test'@'10.10.10.10';

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES


## No error with all database (*.*)

mysql> > grant select, insert, update, delete, file on  *.* to 'test'@'10.10.10.10';

Query OK, 0 rows affected (0.00 sec)





Read the binary log file when the error occur.


# at 1207
#140108 16:45:25 server id 136  end_log_pos 1264 CRC32 0x276edfa9
# Incident: LOST_EVENTS                                 <---
RELOAD DATABASE; # Shall generate syntax error          <---



This bug fixed on MySQL 5.6.15.

MySQL Lock error when executing mysqldump program.

What Problems? 

When you execute mysqldump program, You may have a lock problem in MySQL Instance.
Then, Service session may not update data in MySQL.

What for?

You see example image.

Session id 253372(in Blue box.) is executing select query for 4319 sec. Session ID 257695 is blue box session's next session. ( in Purple box) Session's executing time is 1109.
The Purple box's Session's Query is very important. It is not simple query. It is FLUSH TABLES.
"FLUSH TABLES" needs exclusive lock about all tables singly.
Because of not finishing  select query, Purple box's session is waiting for finishing Session ID 253372(in Blue Box)
So, Session ID 257695 (in Purple Box)'s status is "Waiting for table flush" . State of the session with the query running afterwards is "Wating for table flush".

So, When you execute "FLUSH TABLES" ?
1. When you execute "FLUSH TABLES " explicitly
2. When you execute "mysqldump " with snapshot options.

What can I do for fixing Problem?

If you see this situation, You should kill session Long select query or "FLUSH TABLES"
The important thing is not to execute long select query when you execute myqldump in snapshot options.