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
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.
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),
|
Pull requests are available for MariaDB 10.0 and 10.1 that add ALL_SIGNED and ALL_UNSIGNED to slave_type_conversions:
https://github.com/MariaDB/server/pull/80
https://github.com/MariaDB/server/pull/81