Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 10.0.20
-
Fix Version/s: 5.5.45
-
Component/s: Data Manipulation - Delete, Data Manipulation - Update, Replication
-
Labels:
Description
Let's create a simple table on our master server:
CREATE DATABASE slave_conversion_test;
CREATE TABLE slave_conversion_test.tab (
id int(10) unsigned NOT NULL,
data varchar(50),
PRIMARY KEY(id)
);
And then we can insert some data. To reproduce this issue, we need to make sure that the most significant bit of one of the integer values is '1':
INSERT INTO slave_conversion_test.tab (id, data) VALUES (1, 'str'); INSERT INTO slave_conversion_test.tab (id, data) VALUES (2147483647, 'str'); INSERT INTO slave_conversion_test.tab (id, data) VALUES (4294967295, 'str');
Now let's change the 'id' column to 'bigint' on the slave:
STOP SLAVE; ALTER TABLE slave_conversion_test.tab MODIFY id BIGINT NOT NULL;
We also need to set slave_type_conversions to ALL_NON_LOSSY to make this work:
SET GLOBAL slave_type_conversions=ALL_NON_LOSSY; START SLAVE;
Now back on the master, let's try to update these rows:
UPDATE slave_conversion_test.tab SET data='newstr' WHERE id=2147483647; UPDATE slave_conversion_test.tab SET data='newstr' WHERE id=4294967295;
Now what data do we see on the slave:
MariaDB [(none)]> SELECT * FROM slave_conversion_test.tab; +------------+--------+ | id | data | +------------+--------+ | 1 | str | | 2147483647 | newstr | | 4294967295 | str | +------------+--------+ 3 rows in set (0.00 sec)
The row with 'id' value 4294967295 created an error on the slave:
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.65
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000004
Read_Master_Log_Pos: 1088
Relay_Log_File: master-relay-bin.000005
Relay_Log_Pos: 599
Relay_Master_Log_File: mysqld-bin.000004
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: 1032
Last_Error: Could not execute Update_rows_v1 event on table slave_conversion_test.tab; Can't find record in 'tab', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysqld-bin.000004, end_log_pos 1061
Skip_Counter: 0
Exec_Master_Log_Pos: 884
Relay_Log_Space: 1821
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: 1032
Last_SQL_Error: Could not execute Update_rows_v1 event on table slave_conversion_test.tab; Can't find record in 'tab', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysqld-bin.000004, end_log_pos 1061
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
The slave seems to want to interpret the id value in the Update_rows_v1 event as a negative integer, since the most significant bit is 1.
The master in this case is MySQL 5.5, if that makes a difference.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
http://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html#replication-features-attribute-promotion tells us "Neither ALL_SIGNED nor ALL_UNSIGNED has any effect if at least one of ALL_LOSSY or ALL_NONLOSSY is not also used."
master> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
master> insert into t1 values (pow(2,31)+100);
Query OK, 1 row affected (0.00 sec)
slave> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483749 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
slave> set global slave_type_conversions='all_non_lossy,all_unsigned';
Query OK, 0 rows affected (0.00 sec)
slave> start slave;
Query OK, 0 rows affected (0.00 sec)
slave> select * from t1;
+------------+
| id |
+------------+
| 2147483748 |
+------------+
1 row in set (0.00 sec)
Pull requests are available for MariaDB 10.0 and 10.1 that add ALL_SIGNED and ALL_UNSIGNED to slave_type_conversions:
Patches:
https://github.com/MariaDB/server/pull/80
https://github.com/MariaDB/server/pull/81
I've set the Fix version to 10.1 because I doubt it can be added to a post-GA version, but the final decision will not be mine.
I have now created a fix for the replication break problem for next
5.5 release.
I did not use the solution used by MySQL 5.6 as suggested by
https://mariadb.atlassian.net/browse/MDEV-8432.
This because:
- There is no guarantee that all alter table modifications on a slave will
always be signed or unsigned. - One can't change the behaviour per table on the master.
- There will always be cases of replication errors if master and slave has
different sign handling for a column. This is already true for integer
of the same size and will be true for integer of different sizes.
Instead I am using the following approach:
- If there is a need of conversion on the slave for an integer, assume
that the slave has the same signed/unsigned attribute as the master.
Replication already assumes that the above is always true for integer
of the same size on master and slave so it's logical to extend this
assumption for the case where the integer size is different between
slave and master.
This means that one can safely change a column on the slave from an
INT to a BIGINT or from an UNSIGNED INT to an unsigned bigint.
Changing an UNSIGNED INT to an SIGNED BIGINT will cause replication
failures when the high bit of the UNSIGNED INT is set.
Fix pushed to 5.5 tree
This also affects Delete_rows_v1 events.
On master:
On slave:
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.65 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqld-bin.000004 Read_Master_Log_Pos: 1282 Relay_Log_File: master-relay-bin.000005 Relay_Log_Pos: 803 Relay_Master_Log_File: mysqld-bin.000004 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: 1032 Last_Error: Could not execute Delete_rows_v1 event on table slave_conversion_test.tab; Can't find record in 'tab', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysqld-bin.000004, end_log_pos 1255 Skip_Counter: 0 Exec_Master_Log_Pos: 1088 Relay_Log_Space: 2015 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: 1032 Last_SQL_Error: Could not execute Delete_rows_v1 event on table slave_conversion_test.tab; Can't find record in 'tab', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysqld-bin.000004, end_log_pos 1255 Replicate_Ignore_Server_Ids: Master_Server_Id: 3 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)