MySQL从无符号整数复制到无符号bigint

发表于： 2022-09-09 23:02:04
分类：数据库文章分享
浏览：浏览(72)

我们经常看到表中的int列需要更改为unsigned int，然后由于值超出范围而更改为unsinsigned bigint。有时，甚至可能存在阻止我们直接更改表或在主服务器上应用pt online架构更改的阻止程序，这需要轮换解决方案：首先在副本上应用更改，将写入切换到副本，然后将更改应用到上一个主服务器。在这种情况下，MySQL将不得不在一段时间内将无符号int复制到无符号bigint。

有人可能认为MySQL应该能够将无符号整数复制到无符号bigint是显而易见的，因为无符号BIGNT的大小更大（8字节），它覆盖了无符号整数（4字节）。这在一定程度上是正确的，但在实践中也有一些技巧。本博客将通过场景向您展示这些技巧。

让我们了解从主副本中的无符号int复制到副本中的未符号bigint时可能面临的场景和问题。对于场景一和场景二，我们将只关注binlog_format=ROW，因为使用binlog_ format=STATEMENT，“如果在源上运行的语句也将在副本上成功执行，那么它也应该成功复制”–MySQL doc。然而，对于场景三，我们测试了binlog_format=ROW和binlog_ format=STATEMENT。

首先，创建表。这里我们有一个表test_replication_different_type，主表中有无符号int，副本中有无签名bigint。

主要：服务器版本：MySQL 8.0.28

表：

CREATE TABLE `test_replication_differ_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `replicate_col` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

副本：服务器版本：MySQL 8.0.28

表：

CREATE TABLE `test_replication_differ_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `replicate_col` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

场景一：插入主数据中的无符号整数并复制到无符号bigint

mysql> insert into test.test_replication_differ_type(id,replicate_col) values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.test_replication_dif
fer_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
+----+---------------+

复制时：

root@localhost [test]> show replica status\G
...
<strong>Last_SQL_Errno: 1677</strong>
Last_SQL_Error: Column 1 of table 'test.test_replication_differ_type' cannot be converted from type 'int' to type 'bigint(20)'

分析和解决方案：

我们立即看到阻止复制的错误。现在有一个配置选项“slave_type_conversions”，它控制副本上使用的类型转换模式。复制错误的原因是，在复制副本上，默认情况下未设置slave_type_conversions变量。

root@localhost [test]> show variables like 'slave_type%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_type_conversions |       |
+------------------------+-------+

将slave_type_conversions设置为ALL_LOSSY模式将不起作用，因为：“要求有损转换或根本不允许转换；例如，仅启用此模式允许将INT列转换为TINYINT（有损转换），但不允许将TINYINT列转换为INT列（无损）。”—MySQL文档

root@localhost [test]> set global slave_type_conversions='ALL_LOSSY';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]> start slave;
Query OK, 0 rows affected (0.01 sec)
root@localhost [test]> show slave status\G
...
Last_Errno: 1677
Last_Error: Column 1 of table 'test.test_replication_differ_type' cannot be converted from type 'int' to type 'bigint(20)'

将slave_type_conversions设置为模式ALL_NON_LOSSY将起作用，因为该模式：“该模式允许不需要截断或对源值进行其他特殊处理的转换；也就是说，它允许目标类型比源类型范围更广的转换。”––MySQL文档

root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY';
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]> start slave;
Query OK, 0 rows affected (0.01 sec)
root@localhost [test]> show slave status\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0root@localhost [test]> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
+----+---------------+

不要被名称混淆，ALL_LOSSY和ALL_NON_loss不是排他性的，相反，它们可以并行添加以允许两种模式：

复制时：

root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY, ALL_LOSSY';

On Primary:

mysql> insert into test.test_replication_differ_type(id,replicate_col) values(2,2);
Query OK, 1 row affected (0.00 sec)
root@localhost [test]> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
|  2 |             2 |
+----+---------------+

复制时：

root@localhost [test]> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
|  2 |             2 |
+----+---------------+

因此，如果要使用不同的数据类型进行复制，请考虑在全局变量slave_type_conversions中适当使用模式ALL_NON_LOSSY和/或ALL_loss。

场景二：插入一个超出有符号整数范围的值并复制到无符号bigint

带符号整数的范围值为（-214748364821477483647）。让我们尝试将2147483647+1插入到主节点上的无符号整数列中，并观察它是如何复制的。

mysql> insert into test.test_replication_differ_type(id, replicate_col) values(3, 2147483647);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test.test_replication_differ_type(id, replicate_col) values(4, 2147483648);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
|  2 |             2 |
|  3 |    2147483647 |
|  4 |    <strong>2147483648</strong> |
+----+---------------+
4 rows in set (0.00 sec)

复制时：

root@localhost [test]> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
|  2 |             2 |
|  3 |    2147483647 |
|  4 |            <strong> 0</strong> |
+----+---------------+
4 rows in set (0.00 sec)

我们在主服务器上插入了2147483648，但在副本上得到了0。

分析和解决方案：

检查primary的binlog文件，我们可以看到无符号int值2147483648被写为@2=-2147483638（214748364），如下所示。

root@db2:/home/vagrant# mysqlbinlog -vvv /var/lib/mysql/mysql-bin.000010 > test_convert.sql
root@db2:/home/vagrant# cat test_convert.sql
...
# at 2635
#220821  4:55:56 server id 2  end_log_pos 2679 CRC32 0x85dfff8a Write_rows: table id 113 flags: STMT_END_F
BINLOG '
3LoBYxMCAAAASAAAAEsKAAAAAHEAAAAAAAEABHRlc3QAHHRlc3RfcmVwbGljYXRpb25fZGlmZmVy
X3R5cGUAAgMDAAKLYMRs
3LoBYx4CAAAALAAAAHcKAAAAAHEAAAAAAAEAAgAC//wEAAAAAAAAgIr/34U=
'/*!*/;
### INSERT INTO `test`.`test_replication_differ_type`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
<strong>###   @2=-2147483648 (2147483648) /* INT meta=0 nullable=1 is_null=0 */</strong>
# at 2679
#220821  4:55:56 server id 2  end_log_pos 2710 CRC32 0x532d66ec Xid = 89
COMMIT/*!*/;

但是，“当提升整数类型时，它的有符号性不会保留。默认情况下，副本将所有这些值视为有符号值”，因此-2147483648（214748364）将被视为带符号值-2147483 648，并最终变为0，因为无符号bigint的最小值为0。

我们需要告诉MySQL副本将值-2147483648（214748364）视为无符号整数，而不是有符号整数，方法是向变量slave_type_conversions添加模式ALL_unsigned，如下示例：

root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY,ALL_UNSIGNED';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.test_replication_differ_type(id,replicate_col) values(5,2147483649);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
|  2 |             2 |
|  3 |    2147483647 |
|  4 |    2147483648 |
| <strong> 5 |    2147483649</strong> |
+----+---------------+
5 rows in set (0.00 sec)

root@localhost [test]> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
|  2 |             2 |
|  3 |    2147483647 |
|  4 |             0 |
| <strong> 5 |    2147483649</strong> |
+----+---------------+
5 rows in set (0.00 sec)

我们也可以将模式添加为ALL_SIGNED，但这一次，它不同于并行的ALL_ LOSSY和ALLUNON_LOSSY。ALL_SIGNED比ALL_UNSIGNED具有更高的优先级，这意味着MySQL将在可能的情况下首先将值视为有符号，否则将该值视为无符号。

root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY,ALL_LOSSY,ALL_UNSIGNED,ALL_SIGNED';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.test_replication_differ_type(id,replicate_col) values(6,2147483650);
Query OK, 1 row affected (0.01 sec)mysql> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
|  2 |             2 |
|  3 |    2147483647 |
|  4 |    2147483648 |
|  5 |    2147483649 |
| <strong> 6 |    2147483650</strong> |
+----+---------------+
6 rows in set (0.00 sec)

root@localhost [test]> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
|  2 |             2 |
|  3 |    2147483647 |
|  4 |             0 |
|  5 |    2147483649 |
|  <strong>6 |             0 |</strong>
+----+---------------+
6 rows in set (0.00 sec)

场景三：具有额外列的副本，但并非所有公共列都具有相同的数据类型。

文档中写道：“此外，当表的副本比源副本的列更多时，表的公共列必须在两个表中使用相同的数据类型。”–MySQL文档

让我们来验证这是否是行为。

root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY,ALL_LOSSY,ALL_UNSIGNED';
Query OK, 0 rows affected (0.00 sec)root@localhost [test]> alter table test.test_replication_differ_type add column extra_col int unsigned;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@localhost [test]> show create table test.test_replication_differ_type\G
*************************** 1. row ***************************
      Table: test_replication_differ_type
Create Table: CREATE TABLE `test_replication_differ_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `replicate_col` bigint(20) unsigned DEFAULT NULL,
  <strong>`extra_col` int(10) unsigned DEFAULT NULL,</strong>
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table test.test_replication_differ_type\G
*************************** 1. row ***************************
      Table: test_replication_differ_type
Create Table: CREATE TABLE `test_replication_differ_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `replicate_col` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)mysql> insert into test.test_replication_differ_type(id,replicate_col) values(7,2147483651);
Query OK, 1 row affected (0.00 sec)
root@localhost [test]> select * from test.test_replication_differ_type;
+----+---------------+
| id | replicate_col |
+----+---------------+
|  1 |             1 |
|  2 |             2 |
|  3 |    2147483647 |
|  4 |    2147483648 |
|  5 |    2147483649 |
|  6 |    2147483650 |
<strong>|  7 |    2147483651 |</strong>
+----+---------------+
7 rows in set (0.00 sec)

root@localhost [test]> select * from test.test_replication_differ_type;
+----+---------------+-----------+
| id | replicate_col | extra_col |
+----+---------------+-----------+
|  1 |             1 |      NULL |
|  2 |             2 |      NULL |
|  3 |    2147483647 |      NULL |
|  4 |             0 |      NULL |
|  5 |    2147483649 |      NULL |
|  6 |             0 |      NULL |
<strong>|  7 |    2147483651 |      NULL |</strong>
+----+---------------+-----------+
7 rows in set (0.00 sec)

现在，正如我们的实验所说，文档中的陈述是不正确的。事实是，即使replicate_col在副本上具有未签名的数据类型bigint（20），而不是在主副本上具有无签名的int（10），并且副本具有额外的列extra_ col，它仍然可以很好地复制。

我已经向Oracle MySQL提交了错误报告。

结论

在这篇博客中，我演示了如何设置变量slave_type_conversions、ALL_NON_LOSSY和ALL_loss模式之间的目的和区别，以及ALL_UNSIGNED和ALL_ SIGNED。

具体来说，为了让MySQL正确地从无符号int复制到无符号bigint，我们需要设置变量slave_type_conversions='ALL_NON_LOSSY，ALL_unsigned’或slave-type_conversions=‘ALL_nonn_loss，ALL_ unsigneds’

此外，请注意，当复制副本比主副本具有额外列时，只要公共列具有相同的名称和顺序，并且在额外列之前，即使公共列数据类型与主副本不同，复制仍然可以顺利进行。

关于作者

Edwin Wang
拥有20年RDBMS（即MySQL、Oracle等）经验的DBA，目前在Percona担任高级MySQL数据库管理员，负责不同的环境和场景，包括数据库安装/配置/维护、故障排除、设计、性能调整、数据库高可用性架构和其他基础设施相关问题、AWS云、ansible、GCP等。

原文标题：MySQL Replicate From Unsigned-int to Unsigned-bigint
原文作者：Edwin Wang
原文链接：https://www.percona.com/blog/mysql-replicate-from-unsigned-int-to-unsigned-bigint/

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论