[MDEV-8432] [PATCH] Slave cannot replicate signed integer-type values with high bit set to 1 Created: 2015-07-06  Updated: 2019-08-23  Resolved: 2015-07-17

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Data Manipulation - Update, Replication
Affects Version/s: 10.0.20
Fix Version/s: 5.5.45

Type: Bug Priority: Critical
Reporter: Geoff Montee (Inactive) Assignee: Michael Widenius
Resolution: Fixed Votes: 1
Labels: replication


 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.

 Comments   
Comment by Geoff Montee (Inactive) [ 2015-07-06 ]

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 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)

Comment by Geoff Montee (Inactive) [ 2015-07-07 ]

Apparently it doesn't matter if the column is signed or unsigned on the slave. Either way, it fails.

Comment by Kolbe Kegel (Inactive) [ 2015-07-07 ]

This behavior also exists in MySQL 5.6.

Comment by Kolbe Kegel (Inactive) [ 2015-07-07 ] 

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)

Comment by Kolbe Kegel (Inactive) [ 2015-07-07 ]

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

Comment by Kolbe Kegel (Inactive) [ 2015-07-07 ]

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)

Comment by Geoff Montee (Inactive) [ 2015-07-08 ]

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

Comment by Elena Stepanova [ 2015-07-08 ]

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.

Comment by Michael Widenius [ 2015-07-16 ]

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.

Comment by Michael Widenius [ 2015-07-17 ]

Fix pushed to 5.5 tree

Comment by Geoff Montee (Inactive) [ 2019-08-23 ]

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),

Generated at Thu Feb 08 07:27:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.