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. ...

SQL Thread Error : Could not parse relay log event entry.

What problems ??

When you use MySQL replication, you may see replication errors :

 Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. 

Then, SQL thread stop running.

What for ?

1. The relay log is corrupted is corrupted for any reason.
2. for network problems
3. The master's binary log is corrupted for any reason.

You can check relay log by running 'mysqlbinlog'

What Can I do for fixing problems?

1. STOP SLAVE;
2. SHOW SLAVE STATUS;==> Checking Relay_Master_Log_File, Exec_Master_Log_Pos
3. RESET SLAVE;
4. CHANGE MASTER TO
    MASTER_HOST = 'xxx',
    MASTER_PORT=3306,
    MASTER_USER='xx',
    MASTER_PASSWORD='xxx',
    MASTER_LOG_FILE='Relay_Master_Log_File's VALUE',
    MASTER_LOG_POS='Exec_Master_Log_Pos's VALUE';
5. START SLAVE;