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.

MySQL - FLUSH TABLES WITH READ LOCK

By issuing this command, it is possible to obtain a global lock.

mysql> FLUSH TABLES WITH READ LOCK ;

This command can be maintained consistent data at the time the instruction is executed.


Follow the example.

First, Execute query (flush with read lock).


session1> flush tables with read lock ; Query OK, 0 rows affected (0.00 sec)

And then execute insert query  during flush command executing.


session2>insert into inno_xxx values (1,' xxx') ; -- Waiting... session2 is blocked.

In this situation, execute "show processlist;" on session3.

session3> show full processlist ;
+------+------+-----------+---------+---------+------+------------------------------+-----------------------------------------+
| Id   | User | Host      | db      | Command | Time | State                        | Info                                    |
+------+------+-----------+---------+---------+------+------------------------------+-----------------------------------------+
| 7220 | root | localhost | baktest | Query   |    2 | Waiting for global read lock | insert into inno_xxx values (1,'xxx') |
| 7224 | root | localhost | NULL    | Query   |    0 | init                         | show full processlist                   |
| 7237 | root | localhost | NULL    | Sleep   |    2 |                              | NULL                                    |
+------+------+-----------+---------+---------+------+------------------------------+-----------------------------------------+
3 rows in set (0.00 sec)

session2 ( thread id 7220 ) state is waiting for global read lock.


The lock is release when executing unlock command on session1.

session1> UNLOCK TABLE ;

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

What Problems? 

When you create index in Innodb table, you may see this error statement.

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

When you see this error statement, You can not create index in InnoDB table.

What for?

This is InnoDB restriction.
http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
You can find InnoDB Index Restrictions.

What can I do for fixing problems?

1. Decrease index key size.
max key lenght is 767 bytes. So, euckr is 383 character, utf8 is 255 character, utf8mb4 is 191 character. ( 1 character = 2 byte in euckr =3 byte in utf8 =4byte in utf8mb4 )

2. Changing InnoDB Fileformat, enabling innodb_large_prefix.
When you use Barracuda file format, You can create index for 3072 byte.

You can check system variables.
innodb_file_format_max=Barracuda
innodb_file_format=Barracuda

And, When you create table, you must set row format is dynamic or compressed.

In detail,  You can check this url.
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

MyISAM index corrupt error

What problems??

When you use MyISAM table, you may see error :

[ERROR] /db/mysql/bin/mysqld: Incorrect key file for table './dbstat/global_status.MYI'; try to repair it

What for ?

When MyISAM index file is corrupted, You can see errors.

What Can I do for fixing problems?

mysql> repair table [tablenm]

when you execute "repair table xxx", You may lost some data.

About MySQL ERROR (1025)

When I was change the table, the following error was returned.

ERROR 1025 (HY000): Error on rename of './test/#sql-2fa8_1' to './test/test2' (errno: 150)

This case may be occur if foreign key constraint  is on the table.

Try to test for this case.

Parent table is PARENT_TBL and child table is CHILD_TBL.

CHILD_TBL is references an id of PARENT_TBL.

1. Create tables, PARENT_TBL and CHILD_TBL . CREATE TABLE `PARENT_TBL` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; CREATE TABLE `CHILD_TBL` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `cvalue` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `fk_parent_tbl_id` (`pid`), CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ; 2. Data input ( PARENT_TBL ) insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; insert into PARENT_TBL (value) values ('test') ; +----+-------+ | id | value | +----+-------+ | 1 | test | | 2 | test | | 3 | test | | 4 | test | | 5 | test | | 6 | test | +----+-------+ 3. Data input ( CHILD_TBL ) INSERT INTO CHILD_TBL (pid, cvalue) values (1, 'child_test') ; INSERT INTO CHILD_TBL (pid, cvalue) values (1, 'child_test') ; INSERT INTO CHILD_TBL (pid, cvalue) values (1, 'child_test') ; +----+-----+-------------+ | id | pid | cvalue | +----+-----+-------------+ | 2 | 1 | child_test | | 3 | 1 | child_test | | 4 | 1 | child_test | +----+-----+-------------+

If you specify a value that is not in PARENT_TBL by pid error occurs.

INSERT INTO CHILD_TBL (pid, cvalue) values (7, 'child_test') ;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`CHILD_TBL`, CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

And then let's perform an alter to change the id column type. 

mysql> alter table PARENT_TBL change id id bigint NOT NULL AUTO_INCREMENT ; ERROR 1025 (HY000): Error on rename of './test/#sql-72f5_330c7' to './test/PARENT_TBL' (errno: 150)

The error occurs.

In this case, drop fk constraint on the child table before altering the parent table. (temporarily)

1. Drop FK constraint on CHILD_TBL alter table CHILD_TBL drop foreign key fk_parent_tbl_id ; Query OK, 3 rows affected (0.19 sec) Records: 3 Duplicates: 0 Warnings: 0 show create table CHILD_TBL \G *************************** 1. row *************************** Table: CHILD_TBL Create Table: CREATE TABLE `CHILD_TBL` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `cvalue` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `fk_parent_tbl_id` (`pid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 2. Change the column in PARENT_TBL mysql> alter table PARENT_TBL change id id bigint NOT NULL AUTO_INCREMENT ; Query OK, 6 rows affected (0.12 sec) Records: 6 Duplicates: 0 Warnings: 0 3. Add columns referenced in CHILD_TBL alter table CHILD_TBL change pid pid bigint NOT NULL ; Query OK, 3 rows affected (0.29 sec) Records: 3 Duplicates: 0 Warnings: 0 4. If you do not modify the column, an error occurs when you add back the CONSTRAINT. ERROR 1005 (HY000): Can't create table 'test.#sql-72f5_330c7' (errno: 150). 5. Add original FK constraint to CHILD_TBL mysql> alter table CHILD_TBL ADD CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ; Query OK, 3 rows affected (0.12 sec) Records: 3 Duplicates: 0 Warnings: 0

When alter command finished, you should check the schema is changed to bigint.

CREATE TABLE `PARENT_TBL` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `value` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 CREATE TABLE `CHILD_TBL` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` bigint(20) NOT NULL, `cvalue` varchar(20) NOT NULL, PRIMARY KEY (`id`), KEY `fk_parent_tbl_id` (`pid`), CONSTRAINT `fk_parent_tbl_id` FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

If you want to find more errors, see the following command:

mysql> show engine indoor status \G ... ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 150825 9:56:45 Error in foreign key constraint of table test/#sql-72f5_330c7: FOREIGN KEY (`pid`) REFERENCES `PARENT_TBL` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition. ...