| Bug #77841 | There is an bug on slave about 'Last_Error' and ‘Last_SQL_Error’ , | ||
|---|---|---|---|
| Submitted: | 27 Jul 2015 9:36 | Modified: | 28 Jul 2015 3:01 |
| Reporter: | Hotpu Technology | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | MySQL5.6.11 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Last_Error, Last_SQL_Error, Reolication | ||
[27 Jul 2015 22:11]
MySQL Verification Team
Thank you for the bug report. I couldn't repeat with 5.6.25 version please upgrade:
slave >show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: tikal-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: tikal-relay-bin.000007
Relay_Log_Pos: 283
Relay_Master_Log_File: tikal-bin.000003
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: 120
Relay_Log_Space: 619
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: ef2800ef-319a-11e5-90f9-ecf4bbfa653d
Master_Info_File: c:\repli\mysql-5.6.25-slave\data\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)
slave >use y
Database changed
slave >show create table test_data\G
*************************** 1. row ***************************
Table: test_data
Create Table: CREATE TABLE `test_data` (
`name` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
slave >alter table test_data change column name name varchar(32);
Query OK, 0 rows affected (0.72 sec)
Records: 0 Duplicates: 0 Warnings: 0
slave >show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: tikal-bin.000003
Read_Master_Log_Pos: 563
Relay_Log_File: tikal-relay-bin.000007
Relay_Log_Pos: 726
Relay_Master_Log_File: tikal-bin.000003
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: 563
Relay_Log_Space: 1062
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: ef2800ef-319a-11e5-90f9-ecf4bbfa653d
Master_Info_File: c:\repli\mysql-5.6.25-slave\data\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)
slave >select * from test_data;
+-------+
| name |
+-------+
| hotpu |
+-------+
1 row in set (0.00 sec)
slave >show create table test_data\G
*************************** 1. row ***************************
Table: test_data
Create Table: CREATE TABLE `test_data` (
`name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
slave >show variables like '%char%';
+--------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | c:\repli\mysql-5.6.25-slave\share\charsets\ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)
master >show variables like '%char%';
+--------------------------+----------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | c:\repli\mysql-5.6.25-master\share\charsets\ |
+--------------------------+----------------------------------------------+
8 rows in set (0.00 sec)
master >create database y;
Query OK, 1 row affected (0.13 sec)
master >use y
Database changed
master >CREATE TABLE `test_data` (
-> `name` varchar(16) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (33.00 sec)
master >insert into test_data values('hotpu');
Query OK, 1 row affected (0.17 sec)
[28 Jul 2015 3:01]
Hotpu Technology
you should execute this SQL 'insert into test_data values('hotpu');' on the master ,then show slave status\G on the slave
[28 Jul 2015 10:07]
MySQL Verification Team
Thank you for the feedback. Yes that what I did I just printed here in bad order. Please check 5.6.25. Thanks.
Description: If I change the length of column on the slave(not master),then I insert an row into the same column on master,we will trigger an error on the replication.For details , see 'How to repeat'. How to repeat: 1. suppose i have a replication that has no error,and my character set like this: root@localhost : test 01:07:51> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.01 sec) 2. create a test table on the master(it also create the same table on the slave by the replication) root@localhost : test 01:04:54> create table test_data(name varchar(16)); Query OK, 0 rows affected (0.13 sec) root@localhost : test 01:05:07> show create table test_data; +-----------+---------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+---------------------------------------------------------------------------------------------------+ | test_data | CREATE TABLE `test_data` ( `name` varchar(16) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-----------+---------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 3. we will change the length of 'name' on the slave. root@localhost : test 01:06:36> show tables; +----------------+ | Tables_in_test | +----------------+ | test_data | +----------------+ 1 row in set (0.00 sec) root@localhost : test 01:06:40> show create table test_data; +-----------+---------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+---------------------------------------------------------------------------------------------------+ | test_data | CREATE TABLE `test_data` ( `name` varchar(16) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-----------+---------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) root@localhost : test 01:07:02> alter table test_data change column name name varchar(32); Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost : test 01:07:21> show create table test_data; +-----------+---------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+---------------------------------------------------------------------------------------------------+ | test_data | CREATE TABLE `test_data` ( `name` varchar(32) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-----------+---------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 4. now ,insert an row into the table of test_data on the master. root@localhost : test 01:07:36> insert into test_data values('hotpu'); Query OK, 1 row affected (0.00 sec) 5. on the slave,we exeute the SQL 'SHOW SLAVE STATUS\G;' root@localhost : test 01:07:56> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.63.138 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000013 Read_Master_Log_Pos: 1414 Relay_Log_File: mysql-relay-bin.000037 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000013 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1677 Last_Error: Column 0 of table 'test.test_data' cannot be converted from type 'varchar(48)' to type 'varchar(32)' Skip_Counter: 0 Exec_Master_Log_Pos: 1154 Relay_Log_Space: 879 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1677 Last_SQL_Error: Column 0 of table 'test.test_data' cannot be converted from type 'varchar(48)' to type 'varchar(32)' Replicate_Ignore_Server_Ids: Master_Server_Id: 140 Master_UUID: c3d0ef05-ddf2-11e4-af7a-000c29ac1e20 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 150728 01:07:51 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: c5d8bbea-ddf2-11e4-af7a-000c29ac1e20:1-2 Auto_Position: 0 1 row in set (0.00 sec) Please pay attention to 'Last_Error' and 'Last_SQL_Error' On the master,the type of 'name' is VARCHAR(16),but the error description is 'VARCHAR(48)' If I change the character set from 'utf8' to 'latin1',the error description is true. So I think the error description of the type on the master is related to character set.