percona toolkit系列 02(pt-osc的--null-to-not-null参数说明)

最新推荐文章于 2024-06-06 08:22:05 发布

请给我的爱人一杯mojito

最新推荐文章于 2024-06-06 08:22:05 发布

阅读量225

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.csdn.net/baidu_34007305/article/details/114368476

版权

Mysql 专栏收录该内容

86 篇文章 1 订阅

订阅专栏

背景

承接percona toolkit系列 01(pt-osc)中，进行--null-to-not-null参数扩展

写在前面
(推荐阅读原文)写本篇的原因是，看到一篇更好的原文。链接为https://www.cnblogs.com/ivictor/p/14347901.html
link

原文精华

为了使pt-osc顺利运行,需要注意以下内容:
1.表设计时不要包含null，可代替为default 0(属于前期的开发规范)

2.--null-to-not-null选项添加，是忽略1048错误。结构为，对于字符类型的列，会填充空字符，
对于数字类型的列，会填充0。(需要确认)

3.要将一个列的属性从null直接修改为not null default xxx。
除非：3.1.  该列不存在null值(在数据拷贝的过程中，如果拷贝的数据中，该列存在null值，pt-online-schema-change会直接报错退出1048。)。
3.2.  在DDL的过程中，没有类似于“insert into slowtech.t1(id) values(1)”的业务SQL出现。(要保证2个条件满足)

4.总结，在字段包含null值，不接受第2步的结果时，需要执行下面的推荐做法。
(无法避免大量数据更新的危险性,推荐业务低峰期操作，因为DDL+DML可能造成死锁)

业务需求

一个DDL，将列的属性从null调整为not null default xxx，

alter table slowtech.t1 modify name varchar(10) not null default 'slowtech';
通过平台执行（平台调用的是pt-online-schema-change）。

但在执行的过程中，业务SQL报错，提示“ERROR 1048 (23000): Column 'name' cannot be null”。

pt-online-schema-change原理

在这里插入图片描述

从原理图中可以看到，

对于全量数据的同步，pt-online-schema-change是以chunk为单位分批来拷贝的。
对于增量数据的同步，pt-online-schema-change是通过触发器来实现的。

重现下问题场景

mysql> create table slowtech.t1(id int primary key,name varchar(10));

mysql> create table slowtech._t1_new(id int primary key,name varchar(10));

mysql> alter table slowtech._t1_new modify name varchar(10) not null default 'slowtech';

mysql> create trigger slowtech.`pt_osc_slowtech_t1_ins` after insert on `slowtech`.`t1` for each row replace into `slowtech`.`_t1_new` (`id`, `name`) values (new.`id`, new.`name`);

mysql> insert into slowtech.t1(id) values(1);
ERROR 1048 (23000): Column 'name' cannot be null

问题完美呈现，有的童鞋可能会有疑问，t1的name列默认不是null么？为什么不允许null值的插入？

问题原因
问题出在触发器上面。
触发器会将业务SQL（“insert into slowtech.t1(id) values(1)”）和触发操作（“replace into slowtech._t1_new (id, name) values(1, null)”）放到一个事务内执行。
“insert into slowtech.t1(id) values(1)”并不违反t1表的约束，但违反了_t1_new表的约束。

通过上面的分析，我们得到了两点启示:

1. 类似DDL（将列的属性从null修改为not null default 'abc'）要注意。
2.从原理上看，既然涉及到全量数据+增量数据的同步，都会存在这种问题，
不单单是pt-online-schema-change，包括Online DDL，gh-ost同样如此。
只不过，触发器这种方案会将业务SQL和触发操作耦合在一起，相对来说，对业务有一定的侵入性。

既然触发器会将业务SQL和触发操作放到一个事务内执行，如果pt-online-schema-change异常退出，留下了触发器和中间表（_t1_new），在清理现场时，应首先删除触发器，再删除中间表。

如果首先删除中间表，会导致针对原表的所有DML操作失败。

mysql> drop table slowtech._t1_new;

mysql> insert into slowtech.t1 values(1,'victor');
ERROR 1146 (42S02): Table 'slowtech._t1_new' doesn't exist

数据拷贝也有坑

在执行DDL之前，还有一段小插曲。

在执行DDL之前，开发提单将该列的null值修改为了默认值。这样就导致了，
问题是在业务SQL插入的过程中暴露的，而不是在数据拷贝过程中暴露。

在数据拷贝的过程中，如果拷贝的数据中，该列存在null值，pt-online-schema-change会直接报错退出。
mysql> create table slowtech.t1(id int primary key,name varchar(10));

mysql> insert into slowtech.t1(id) values(1);

# pt-online-schema-change h=xxxxx,u=root,p=123456,D=slowtech,t=t1 --alter "modify name varchar(10) not null default 'slowtech'" --execute
No slaves found.  See --recursion-method if host xxxx has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `slowtech`.`t1`...
Creating new table...
Created new table slowtech._t1_new OK.
Altering new table...
Altered `slowtech`.`_t1_new` OK.
2020-09-07T09:13:25 Creating triggers...
2020-09-07T09:13:25 Created triggers OK.
2020-09-07T09:13:25 Copying approximately 1 rows...
2020-09-07T09:13:25 Dropping triggers...
2020-09-07T09:13:25 Dropped triggers OK.
2020-09-07T09:13:25 Dropping new table...
2020-09-07T09:13:25 Dropped new table OK.
`slowtech`.`t1` was not altered.
        (in cleanup) 2020-09-07T09:13:25 Error copying rows from `slowtech`.`t1` to `slowtech`.`_t1_new`: 2020-09-07T09:13:25 Copying rows caused a MySQL error 1048:
    Level: Warning
     Code: 1048
  Message: Column 'name' cannot be null
    Query: INSERT LOW_PRIORITY IGNORE INTO `slowtech`.`_t1_new` (`id`, `name`) SELECT `id`, `name` FROM `slowtech`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 9234 copy table*/
2020-09-07T09:13:25 Dropping triggers...
2020-09-07T09:13:25 Dropped triggers OK.
`slowtech`.`t1` was not altered.

上述报错，pt-online-schema-change加个参数即可规避（--null-to-not-null）。在实现上，该参数会忽略1048错误，此时，对于字符类型的列，会填充空字符，对于数字类型的列，会填充0。

create table slowtech.t1(id int primary key,name varchar(10));

create table slowtech._t1_new(id int primary key,name varchar(10));

alter table slowtech._t1_new modify name varchar(10) not null default 'slowtech';

insert into slowtech.t1(id) values(1);
select * from slowtech.t1;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.00 sec)

insert low_priority ignore into slowtech._t1_new (id, name) select id, name from slowtech.t1 lock in share mode;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

show warnings;
+---------+------+------------------------------+
| Level   | Code | Message                      |
+---------+------+------------------------------+
| Warning | 1048 | Column 'name' cannot be null |
+---------+------+------------------------------+
1 row in set (0.00 sec)

select * from slowtech._t1_new;
+----+------+
| id | name |
+----+------+
|  1 |      |
+----+------+
1 row in set (0.00 sec)

所以，线上使用该参数要注意，要确认被填充的值是否符合自己的预期行为。从目前的分析来看，要将一个列的属性从null直接修改为not null default xxx，几乎是不可能的，除非:

该列不存在null值。
在DDL的过程中，没有类似于“insert into slowtech.t1(id) values(1)”的业务SQL出现。

结论

很显然，这两个条件很难同时满足。既然如此，这个需求还能实现吗？能！只不过比较复杂。

下面，看看具体的实施步骤。

1. 首先，将列的属性调整为null default xxx，这样做的目的是为了避免增量同步过程中，
2. 类似“insert into slowtech.t1(id) values(1)”的业务SQL，产生新的null值。

3. 其次，手动将null值调整为默认值。需要注意的是，如果记录数较多，这一步的操作难度也是极大的。

4. 最后，将列的属性调整为not null default xxx。

对于not null default xxx的正确理解

在很多数据库规范里面，都推荐将列定义为not null default xxx，
但很多童鞋，对这段定义的实际效果却相当模糊。

下面具体来说说，这段定义的实际作用。这段定义实际上由两部分组成：

1.  not null，约束，指的是不可显式插入null值，如，
create table slowtech.t1(id int primary key,name varchar(10) not null default 'slowtech');

insert into slowtech.t1 values(1,null);
ERROR 1048 (23000): Column 'name' cannot be null

2.  default 'slowtech'，如果在插入时，没有显式指定值，则以默认值填充。
insert into slowtech.t1(id) values(1);

select * from slowtech.t1;
+----+----------+
| id | name     |
+----+----------+
|  1 | slowtech |
+----+----------+
1 row in set (0.00 sec)

可以看到，这两部分其实没有任何关系，对于一个列，我们同样可以定义为null default xxx。

再次感谢原作者的分享，再次推荐他的文章
https://www.cnblogs.com/ivictor/p/14347901.html
link

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删

请给我的爱人一杯mojito

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
4
评论
percona toolkit系列 02(pt-osc的--null-to-not-null参数说明)

背景承接percona toolkit系列 01(pt-osc)中，进行--null-to-not-null参数扩展写在前面(推荐阅读原文)写本篇的原因是，看到一篇更好的原文。链接为https://www.cnblogs.com/ivictor/p/14347901.htmllink原文精华为了使pt-osc顺利运行,需要注意以下内容:1.表设计是不要包含null，可代替为default 02.--null-to-not-null选项添加，是忽略1048错误。结构为，对于字符类型的列，会填充
复制链接

扫一扫