How to Install Mroonga with MySQL Ver. 5.6

1. Install Rpm Library 

1) Rpm 

You should install rpm.

gcc
gcc-c++
libgcc

libstdc++

libstdc++-devel

groonga-devel
groonga-libs
groonga-normalizer-mysql
groonga-tokenizer-mecab
mecab
mecab-ipadic

2) MySQL 

Installed MySQL
MySQL Source

3) Mroonga 

Mroonga Source

ex> http://packages.groonga.org

2. Install 

1) MySQL Install 

Install MySQL the way you want

2) MySQL source 

gunzip mysql-5.6.24.tar.gz

tar xf mysql-5.6.24.tar

3) Mroonga source

tar xf mroonga-5.10.tar

4) Compile Mroonga

cd mroonga-5.10

./configure --with-mysql-source=/db/mysql-5.6.24 --with-mysql-config=/usr/local/mysql/bin/mysql_config

make
make install

5) check 

cd /usr/local/mysql/lib/plugin/
ls -al | grep ha_m

-rw-r--r-- 1 root  root  10884114 Dec 16 15:54 ha_mroonga.a

-rwxr-xr-x 1 root  root      1005 Dec 16 15:54 ha_mroonga.la

lrwxrwxrwx 1 root  root        19 Dec 16 15:54 ha_mroonga.so -> ha_mroonga.so.0.0.0

lrwxrwxrwx 1 root  root        19 Dec 16 15:54 ha_mroonga.so.0 -> ha_mroonga.so.0.0.0

-rwxr-xr-x 1 root  root   3943353 Dec 16 15:54 ha_mroonga.so.0.0.0

3. Enable Mroonga plugin 

1) MySQL Restart 

The way you want

2) Execute Command 

INSTALL PLUGIN mroonga SONAME 'ha_mroonga.so';

CREATE FUNCTION last_insert_grn_id RETURNS INTEGER soname 'ha_mroonga.so';

CREATE FUNCTION mroonga_snippet RETURNS STRING soname 'ha_mroonga.so';

3) check mroonga

show plugins;
show engines;

4. Test 

The Way you want~~~~

http://mroonga.org/docs/install/others.html#build-from-source

MySQL Ver. 5.7 Time Zone New Variable log_timestamps

You can see new system varable log_timestamps in MySQL Ver. 5.7. It introduced MySQL Ver. 5.7.2 .

[log_timestamps] 

log_timestamps controls the timestamp time zone of error log, general query log and slow query log's time information.
You can set log_timestamps at runtime.
Value type is enumeration ( 'SYSTEM'.'UTC') .
Value format is YYYY-MM-DDhh:mm:ss.uuuuuu + z signifying Zulu time (UTC) or + hh:mm ( offset from UTC)

[Warnning]
If MySQL startup with --timezone to mysqld_safe, No effect log_timestamps.
You should use default-time-zone system variable instead of --timezone to mysqld_safe.

MySQL TimeZone Configuration

MySQL Time Zone.

1. 3 Level Time Zone. 

MySQL support 3 level time zone setting.

1). System Time Zone. 

System time zone is the highest level time zone. You can see system_time_zone system variable. If you don't setting anything, When the server starts, it attempts to determine the time zone of the machine.

When MySQL startup with the --timezone=XXX option to mysqld_safe, You can set system_time_zone variable. And, you can set system_time_zone system variable by setting the TZ environment variable.

2). Server Time Zone.

It means MySQL server's current time zone. System variables is time_zone. If system_time_zone doesn't set, the initial value of time_zone is SYSTEM.

You can set the global value of time_zone with --default-time-zone=XXX option to mysqld_safe. Or, You can write the following line in an option file

[mysqld]
default-time-zone='XXX'

You can set the global value of time_zone at runtime with this statement:

mysql> set global time_zone= XXX;

3). Connection Time Zone. 

Each client has its own time zone setting with this statement:

mysql> set time_zone = XXX; 

2. Configuration of Timezone. 

Value of Timezone can be given  several formats.

1) SYSTEM 

It means the time zone should be the same as the system's.

2) Offset from UTC 

such as '+00:00' , '+10:00' or '-6:00'

3) Named time zone 

such as 'Asia/Seoul' , 'US/Eastern' or 'Europe/Paris'
You must loading named time zone data in mysql schema.

Using Timestamp data with UTC type.

1. Using Function 

[root@oraclelinux6.localdomain][test]> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.01 sec)

[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-24 23:41:50 |
+---------------------+
1 row in set (0.00 sec)

[root@oraclelinux6.localdomain][test]> create table test ( ts timestamp not null) ;
Query OK, 0 rows affected (0.01 sec)

[root@oraclelinux6.localdomain][test]> insert into test( ts) values (utc_timestamp());
Query OK, 1 row affected (0.00 sec)

[root@oraclelinux6.localdomain][test]> select * from test;
+---------------------+
| ts                  |
+---------------------+
| 2015-06-25 06:41:39 |
+---------------------+
1 row in set (0.00 sec)

2. Setting time_zone value 

[root@oraclelinux6.localdomain][test]> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.01 sec)

[root@oraclelinux6.localdomain][test]> set session time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-25 06:44:20 |
+---------------------+
1 row in set (0.01 sec)

[root@oraclelinux6.localdomain][test]> set session time_zone='SYSTEM';
Query OK, 0 rows affected (0.00 sec)

[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-24 23:45:39 |
+---------------------+
1 row in set (0.00 sec)

[root@oraclelinux6.localdomain][test]> system date;
Wed Jun 24 23:45:47 PDT 2015
[root@oraclelinux6.localdomain][test]>

3. Loading Named Time Zone table 

You can use system's local's zoneinfo database. Or, You can use package that is available for download at the MySQL Developer Zone.

3.1 Using system's zoneinfo database. 

shell> cd /db/mysql/bin
shell> ./mysql_tzinfo_to_sql /usr/share/zoneino >> /tmp/timezone.sql
shell> ./mysql -p -u root
[root@oraclelinux6.localdomain][]> source /tmp/timezone.sql  

3.2 Using a package that is available for download at the MySQL Developer Zone. 

You Can see a package file list this url.
http://dev.mysql.com/downloads/timezones.html
Select one package for you. And then, Load MySQL's mysql schema.

[root@oraclelinux6.localdomain][]> use mysql;
[root@oraclelinux6.localdomain][mysql]> source /tmp/timezone_posix.sql

DATETIME and TIMESTAMP

1. DATA Type

DATETIME and TIMESTAMP datatype have year,month,day,hour,minute and seconds. Additionally, They can save fraction of second. They use data format with YYYY-MM-DD HH:MM:SS[.fraction]. DATETIME can save value from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999' . TIMESTAMP can save value from '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.99999' UTC.

2. Different DATETIME and TIMESTAMP 

DATETIME have no timezone information. TIMESTAMP have timezone information with time_zone system  variables. In detail,  TIMESTAMP have date and time data in UTC timezone. So, Before saving date and time data, MySQL convert timezone to UTC.

3. Setting default value 

After MySQL Version 5.6, DATETIME setting default time value.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

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.

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;