pt-online-schema-change案例

作者:神秘网友 发布时间:2021-02-23 17:20:12

pt-online-schema-change案例

#################################

最近碰到一个case,值得分享一下。

现象

一个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-OSC的实现原理

从原理图中可以看到,

1. 对于全量数据的同步,pt-online-schema-change是以chunk为单位分批来拷贝的。

2. 对于增量数据的同步,pt-online-schema-change是通过触发器来实现的。

结合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')要注意。

从原理上看,既然涉及到全量数据+增量数据的同步,都会存在这种问题,不单单是pt-online-schema-change,包括Online DDL,gh-ost同样如此

只不过,触发器这种方案会将业务SQL和触发操作耦合在一起,相对来说,对业务有一定的侵入性。

2. 既然触发器会将业务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'" --executeNo 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, 1Altering `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。

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 insert into slowtech.t1(id) values(1);
mysql select * from slowtech.t1;+----+------+| id | name |+----+------+| 1 | NULL |+----+------+1 row in set (0.00 sec)
mysql 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
mysql show warnings;+---------+------+------------------------------+| Level | Code | Message |+---------+------+------------------------------+|
Warning | 1048 | Column 'name' cannot be null |+---------+------+------------------------------+1 row in set (0.00 sec)
mysql select * from slowtech._t1_new;+----+------+| id | name |+----+------+| 1 | |+----+------+1 row in set (0.00 sec)

所以,线上使用该参数要注意,要确认被填充的值是否符合自己的预期行为。

从目前的分析来看,要将一个列的属性从null直接修改为not null default xxx,几乎是不可能的,除非:

1. 该列不存在null值。

2. 在DDL的过程中,没有类似于“insert into slowtech.t1(id) values(1)”的业务SQL出现。

结论

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

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

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

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

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

对于not null default xxx的正确理解

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

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

1.not null,约束,指的是不可显式插入null值,如,

mysql create table slowtech.t1(id int primary key,name varchar(10) not null default 'slowtech');

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

2. default 'slowtech',如果在插入时,没有显式指定值,则以默认值填充。

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

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

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

#####################

pt-online-schema-change案例 相关文章

  1. nginx——mime.types使用案例

    前言: 以前只是知道nginx配置文件里面有一个mime.types,但是一直没有用到过,这段时间有需求用到了这个,给大家分享下mime.types的使用。 需求: 开发人员在开发过程中输出一些调试文件,但是这些文件都是服务器上面,开发没有权限登录服务器获取日志,然

  2. CDH 20个实战案例

    1 CCA介绍 2 案例一 3 案例二 4 案例三 5 案例四 6 案例五 7 案例六 8 案例七 9 案例八 10 案例九 11 案例十 1. CCA介绍 Cloudera Certified Associate(CCA认证)是Cloudera面向初中级 Hadoop技术人员推出的认证考试。由于Cloudera的Hadoop发行版是目前 使用

  3. Ajax:原生js实现Ajax,jQuery的get方式实现Ajax,jQuery的post方式实现Ajax,jQuery的通用方式实现Ajax;JSON转换;综合案例:搜索联想;综合案例:瀑布流分页,点击按钮分页

    知识点梳理 课堂讲义 1、Ajax快速入门 1.1、AJAX介绍 AJAX(Asynchronous JavaScript And XML):异步的 JavaScript 和 XML。 本身不是一种新技术,而是多个技术综合。用于快速创建动态网页的技术。 一般的网页如果需要更新内容,必需重新加载个页面。 而 AJAX

  4. 2-18_运动员和教练案例代码实现

    1 package InterfacePlayerAndCoach; 2 3 public abstract class Person { 4 private String name; 5 private int age; 6 7 public Person() { 8 } 9 10 public Person(String name, int age) {11 this.name = name;12 this.age = age;13 }14 15 public Stri

  5. dockerfile案例三:ONBUILD用法

    ONBUILD指令可以为镜像添加触发器。其参数是任意一个Dockerfile 指令。 当我们在一个Dockerfile文件中加上ONBUILD指令,该指令对利用该Dockerfile构建镜像(比如为A镜像)不会产生实质性影响。 但是当我们编写一个新的Dockerfile文件来基于A镜像构建一个镜像

  6. dockerfile案例二:CMD与ENTRYPOINT

    CMD与ENTRYPOINT 相同点:都是指定一个容器启动时要运行的命令 不同点: Dockerfile 中可以有多个 CMD 指令,但只有最后一个生效,CMD 会被 docker run 之后的参数替换。 docker run 之后的参数会被当做参数追加给 ENTRYPOINT后的指令,之后形成新的命令组合

  7. 2021年2月21日浮动案例

    名词解释 ad 有广告的意思 item项目 本次案例的div制作是12306的广告栏所以div属性class属性值用的是ad_item margin 外边距 例子 1 margin:10px 5px 15px 20px; 上外边距是 10px 右外边距是 5px 下外边距是 15px 左外边距是 20px 例子 2 margin:10px 5px 15p

  8. 测试用例设计方法--判定表法

    测试用例方法-判定表法 判定表法案例 若用户欠费或关机,则不允许主被叫 等价类划分法和边界值分析法都是着重考虑单个输入的输入条件,但是没有考虑输入条件的各种组合,输入条件与输出条件之间的相互制约关系。所以要使用判定表法才能解决上述案例编写测试

  9. 多线程【死锁案例及死锁的排查】

    一、死锁案例 class HoldLockThread extends Thread{ private String lockA; private String lockB; public HoldLockThread(String lockA, String lockB){ this.lockA = lockA; this.lockB = lockB; } @Override public void run() { synchronized (lockA){

  10. Es5中的类

    es5中的类语法: 简单案例: function Person(){ //构造函数 this.name="Josie"; this.age=12 } var p = new Person() console.log(p.name)----输出 Josie 若在构造函数与原型链中添加方法 //构造函数中添加方法 function Person(){ this.name="Josie"; this

每天更新java,php,javaScript,go,python,nodejs,vue,android,mysql等相关技术教程,教程由网友分享而来,欢迎大家分享IT技术教程到本站,帮助自己同时也帮助他人!

Copyright 2020, All Rights Reserved. Powered by 跳墙网(www.tqwba.com)|网站地图|关键词