Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.20
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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Affects Version/s | 10.0.20 [ 19201 ] | |
| Affects Version/s | 10.0.20-galera [ 19302 ] |
| Component/s | Data Manipulation - Delete [ 11300 ] |
Apparently it doesn't matter if the column is signed or unsigned on the slave. Either way, it fails.
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Summary | Replication failure: slave uses its own UNSIGNED/SIGNED attribute with slave_type_conversions instead of master's | Slave cannot replicate signed integer-type values with high bit set to 1 |
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 DEFAULT CHARSET=latin1
|
1 row in set (0.00 sec)
|
|
|
master> insert into t1 (id) values (pow(2,31)+1);
|
Query OK, 1 row affected (0.00 sec)
|
|
|
master> select * from t1;
|
+------------+
|
| id |
|
+------------+
|
| 2147483649 |
|
+------------+
|
1 row in set (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 DEFAULT CHARSET=latin1
|
1 row in set (0.00 sec)
|
|
|
slave> select * from t1;
|
+-------------+
|
| id |
|
+-------------+
|
| -2147483647 |
|
+-------------+
|
1 row in set (0.00 sec)
|
|
|
slave> select @@slave_type_conversions;
|
+--------------------------+
|
| @@slave_type_conversions |
|
+--------------------------+
|
| ALL_LOSSY,ALL_NON_LOSSY |
|
+--------------------------+
|
1 row in set (0.00 sec)
|
Perhaps what is needed is a backport of the ALL_SIGNED and ALL_UNSIGNED options for slave_type_conversions from MySQL 5.6?
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.
| Fix Version/s | 10.1 [ 16100 ] | |
| Assignee | Sergei Golubchik [ serg ] | |
| Summary | Slave cannot replicate signed integer-type values with high bit set to 1 | [PATCH] Slave cannot replicate signed integer-type values with high bit set to 1 |
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 Version/s | 5.5.45 [ 19405 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Assignee | Sergei Golubchik [ serg ] | Michael Widenius [ monty ] |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
I trashed the branch that contained my original commit for this, so if we ever decide to port the ALL_SIGNED and ALL_UNSIGNED options for slave_type_conversions from MySQL 5.6, then here is the diff from that commit:
From f5170256e9314f4ac4df879297f0195a0eb38710 Mon Sep 17 00:00:00 2001
|
From: Geoff Montee <gmontee@localhost.localdomain>
|
Date: Tue, 7 Jul 2015 17:41:29 -0400
|
Subject: [PATCH] MDEV-8432: Merges ALL_SIGNED/ALL_UNSIGNED values for
|
slave_type_conversions from MYSQL 5.6 into MariaDB 10.0.
|
|
|
---
|
sql/rpl_utility.cc | 14 +++++++++++++-
|
sql/sql_class.h | 4 +++-
|
sql/sys_vars.cc | 12 +++++++++---
|
3 files changed, 25 insertions(+), 5 deletions(-)
|
|
|
diff --git a/sql/rpl_utility.cc b/sql/rpl_utility.cc
|
index 146bf3b0c0e4..1fbd825d83b9 100644
|
--- a/sql/rpl_utility.cc
|
+++ b/sql/rpl_utility.cc
|
@@ -943,6 +943,18 @@ TABLE *table_def::create_conversion_table(THD *thd, rpl_group_info *rgi,
|
conversion table.
|
*/
|
uint const cols_to_create= MY_MIN(target_table->s->fields, size());
|
+
|
+ // Default value : treat all values signed
|
+ bool unsigned_flag= FALSE;
|
+
|
+ // Check if slave_type_conversions contains ALL_UNSIGNED
|
+ unsigned_flag= slave_type_conversions_options &
|
+ (1ULL << SLAVE_TYPE_CONVERSIONS_ALL_UNSIGNED);
|
+
|
+ // Check if slave_type_conversions contains ALL_SIGNED
|
+ unsigned_flag= unsigned_flag && !(slave_type_conversions_options &
|
+ (1ULL << SLAVE_TYPE_CONVERSIONS_ALL_SIGNED));
|
+
|
for (uint col= 0 ; col < cols_to_create; ++col)
|
{
|
Create_field *field_def=
|
@@ -1008,7 +1020,7 @@ TABLE *table_def::create_conversion_table(THD *thd, rpl_group_info *rgi,
|
max_length,
|
decimals,
|
TRUE, // maybe_null
|
- FALSE, // unsigned_flag
|
+ unsigned_flag, // unsigned_flag
|
pack_length);
|
field_def->charset= target_table->field[col]->charset();
|
field_def->interval= interval;
|
diff --git a/sql/sql_class.h b/sql/sql_class.h
|
index a8d8444571e3..e773f7dd2a11 100644
|
--- a/sql/sql_class.h
|
+++ b/sql/sql_class.h
|
@@ -89,7 +89,9 @@ enum enum_slave_run_triggers_for_rbr { SLAVE_RUN_TRIGGERS_FOR_RBR_NO,
|
SLAVE_RUN_TRIGGERS_FOR_RBR_YES,
|
SLAVE_RUN_TRIGGERS_FOR_RBR_LOGGING};
|
enum enum_slave_type_conversions { SLAVE_TYPE_CONVERSIONS_ALL_LOSSY,
|
- SLAVE_TYPE_CONVERSIONS_ALL_NON_LOSSY};
|
+ SLAVE_TYPE_CONVERSIONS_ALL_NON_LOSSY,
|
+ SLAVE_TYPE_CONVERSIONS_ALL_UNSIGNED,
|
+ SLAVE_TYPE_CONVERSIONS_ALL_SIGNED};
|
enum enum_mark_columns
|
{ MARK_COLUMNS_NONE, MARK_COLUMNS_READ, MARK_COLUMNS_WRITE};
|
enum enum_filetype { FILETYPE_CSV, FILETYPE_XML };
|
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
|
index 94466db5fd92..f6677f9e357e 100644
|
--- a/sql/sys_vars.cc
|
+++ b/sql/sys_vars.cc
|
@@ -2769,12 +2769,18 @@ static Sys_var_enum Slave_run_triggers_for_rbr(
|
DEFAULT(SLAVE_RUN_TRIGGERS_FOR_RBR_NO));
|
#endif //RBR_TRIGGERS
|
|
-static const char *slave_type_conversions_name[]= {"ALL_LOSSY", "ALL_NON_LOSSY", 0};
|
+const char *slave_type_conversions_name[]=
|
+ {"ALL_LOSSY", "ALL_NON_LOSSY", "ALL_UNSIGNED", "ALL_SIGNED", 0};
|
+
|
static Sys_var_set Slave_type_conversions(
|
"slave_type_conversions",
|
"Set of slave type conversions that are enabled. Legal values are:"
|
- " ALL_LOSSY to enable lossy conversions and"
|
- " ALL_NON_LOSSY to enable non-lossy conversions."
|
+ " ALL_LOSSY to enable lossy conversions,"
|
+ " ALL_NON_LOSSY to enable non-lossy conversions,"
|
+ " ALL_UNSIGNED to treat all integer column type data to be unsigned values, and"
|
+ " ALL_SIGNED to treat all integer column type data to be signed values."
|
+ " Default treatment is ALL_SIGNED. If ALL_SIGNED and ALL_UNSIGNED both are"
|
+ " specifed, ALL_SIGNED will take high priority than ALL_UNSIGNED."
|
" If the variable is assigned the empty set, no conversions are"
|
" allowed and it is expected that the types match exactly.",
|
GLOBAL_VAR(slave_type_conversions_options), CMD_LINE(REQUIRED_ARG),
|
| Workflow | MariaDB v3 [ 70506 ] | MariaDB v4 [ 149342 ] |
This also affects Delete_rows_v1 events.
On master:
DELETE FROM slave_conversion_test.tab WHERE id=4294967295;On slave:
MariaDB [(none)]> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.65Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysqld-bin.000004Read_Master_Log_Pos: 1282Relay_Log_File: master-relay-bin.000005Relay_Log_Pos: 803Relay_Master_Log_File: mysqld-bin.000004Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1032Last_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 1255Skip_Counter: 0Exec_Master_Log_Pos: 1088Relay_Log_Space: 2015Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1032Last_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 1255Replicate_Ignore_Server_Ids:Master_Server_Id: 3Master_SSL_Crl:Master_SSL_Crlpath:Using_Gtid: NoGtid_IO_Pos:1 row in set (0.00 sec)