Re: MySQL 5.6.21 signal 11 crashing instantaneously after START SLAVE
Some additional information here: in the middle of the night, after replication working for hours, I decided to ALTER TABLE ... FORCE as pointed out by Jonathon Coombes due to a doubt in losing or not my slave on 5.6. As I said, I'm using this 5.6.21 slave box to test out SSD Storage and it's performing much better than 5.5.39. In fact, if temporal data types breaks the replication because the new requirements on 5.6, it hasn't happened yet.
In order to test out this, I setup two machines with vagrant, both running centos 6.5, once with mysql 5.5.39 and the other running 5.6.21.
wagnerbianchi02:mysql55_and_mysql56 root# vagrant status
Current machine states:
mysql55 running (virtualbox)
mysql56 running (virtualbox)
This environment represents multiple VMs. The VMs are all listed
above with their current state. For more information about a specific
VM, run `vagrant status NAME`.
#: showing up mysqld version
[vagrant@mysql55 ~]$ mysqld --version
mysqld Ver 5.5.39 for Linux on x86_64 (MySQL Community Server (GPL))
[vagrant@mysql56 ~]$ mysqld --version
mysqld Ver 5.6.21 for Linux on x86_64 (MySQL Community Server (GPL))
Both mysql servers in *Classic Replication* (binary log position and file based). I remember that my servers are configured with SQL_MODE relaxed, that is, if you query by SQL_MODE, you'll have the below:
#: querying SQL_MODE on both servers
[vagrant@mysql55 ~]$ mysqld --version && mysql -u root -e "select @@sql_mode"
mysqld Ver 5.5.39 for Linux on x86_64 (MySQL Community Server (GPL))
+------------+
| @@sql_mode |
+------------+
| |
+------------+
[vagrant@mysql56 ~]$ mysqld --version && mysql -u root -e "select @@sql_mode"
mysqld Ver 5.6.21 for Linux on x86_64 (MySQL Community Server (GPL))
+------------+
| @@sql_mode |
+------------+
| |
+------------+
#: as 5.6 is the slave server...
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.50.100
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 266
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 436
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 732f183c-4d78-11e4-815d-0800274fb806
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
#: create test database and a table on with a temporal data type column on both servers, from the master
[vagrant@mysql55 ~]$ mysqld --version && mysql -u root -e "create database test;"
mysqld Ver 5.5.39-log for Linux on x86_64 (MySQL Community Server (GPL))
[vagrant@mysql55 ~]$ mysqld --version && mysql -u root -e "drop table if exists test.tb01; create table test.tb01(a datetime); desc test.tb01;"
mysqld Ver 5.5.39-log for Linux on x86_64 (MySQL Community Server (GPL))
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
[vagrant@mysql56 ~]$ mysqld --version && mysql -u root -e "desc test.tb01;"
mysqld Ver 5.6.21 for Linux on x86_64 (MySQL Community Server (GPL))
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
#: insert some rows in the previously created table
[vagrant@mysql55 ~]$ mysql -u root -e "select count(*) from test.tb01; insert into test.tb01 values (now()),(now());select count(*) from test.tb01;"
+----------+
| count(*) |
+----------+
| 0 |
+----------+
+----------+
| count(*) |
+----------+
| 2 |
+----------+
[vagrant@mysql55 ~]$ mysql -u root -e "select count(*) from test.tb01; insert into test.tb01 values (now()),(now());select count(*) from test.tb01;"
+----------+
| count(*) |
+----------+
| 2 |
+----------+
+----------+
| count(*) |
+----------+
| 4 |
+----------+
[vagrant@mysql55 ~]$ mysqld --version && mysql -u root -e "select * from test.tb01"
mysqld Ver 5.5.39-log for Linux on x86_64 (MySQL Community Server (GPL))
+---------------------+
| a |
+---------------------+
| 2014-10-28 12:29:12 |
| 2014-10-28 12:29:12 |
| 2014-10-28 12:29:14 |
| 2014-10-28 12:29:14 |
+---------------------+
[vagrant@mysql56 ~]$ mysqld --version && mysql -u root -e "select * from test.tb01"
mysqld Ver 5.6.21 for Linux on x86_64 (MySQL Community Server (GPL))
+---------------------+
| a |
+---------------------+
| 2014-10-28 12:29:12 |
| 2014-10-28 12:29:12 |
| 2014-10-28 12:29:14 |
| 2014-10-28 12:29:14 |
+---------------------+
#: finally checking the replication status on 5.6
[vagrant@mysql56 ~]$ mysqld --version && mysql -u root -e "show slave status\G"
mysqld Ver 5.6.21 for Linux on x86_64 (MySQL Community Server (GPL))
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.50.100
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 941
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1100
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 941
Relay_Log_Space: 1270
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 732f183c-4d78-11e4-815d-0800274fb806
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Perhaps the error regarding failing on START SLAVE is in relation to other unknown thing at this moment. I'm running a 5.6 on my production topology as a slave, serving as a reporting server. If i get a replication broken on that, I'm going to share it with you guys all the aspects. I suspect it's something that slave cannot execute, but, not sure if it's regarding temporal data type new format/space requirements.
Let's keep in touch, cheers!!
Wagner Bianchi - +55 31 8654-9510
Profile: bit.ly/toG94v
Blog: wagnerbianchi.com/blog
Twitter: @wagnerbianchijr
Skype: wbianchijr