MySQL 扩展 VARCHAR 长度遭遇问题的总结

2024-01-31 317

最近，业务反馈有个扩展 VARCHAR 改表需求失败多次，需要干预处理一下。

作者：莫善，某互联网公司高级 DBA。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 3600 字，预计阅读需要 12 分钟。

背景介绍

最近，业务反馈有个扩展 VARCHAR 改表需求失败多次，需要干预处理一下。

经过排查分析得出，这是由于改表系统解析改表需求得出错误的改表方案导致，即这类改表可以满足快速改表操作（直接使用 ALTER TABLE），理论上任务下发后能马上改完，但是工单结果是执行触发 10 秒超时，最终工单失败。

原则上，VARCHAR 类型的扩展是可以满足快速改表的，我们的改表工单针对这类需求也是支持的，但是实际结果与预期不符，这到底是工单系统的 Bug？还是 MySQL 的坑呢？

本文就来总结一下 扩展 VARCHAR 长度可能会遇到的一些问题，以及我们给出的解决方案，仅供参考。

仅讨论 MySQL 5.7 及以后的版本。

MySQL Online DDL

Operation	Extending VARCHAR column size
In Place	Yes
Rebuilds Table	No
Permits Concurrent DML	Yes
Only Modifies Metadata	Yes

上表是 MySQL 官方文档中关于 Online DDL 章节中的一部分。可以看到关于 VARCHAR 类型的字段的扩展是可以原地改表，且仅仅改了元数据，理论上敲完回车就执行结束。

当然针对这种场景，还是有一些条件的，直接贴原话：

The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY).

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

VARCHAR 是变长类型，实际存储的内容不固定，需要 1 或者 2 个字节来表示实际长度，所以修改前和修改后，这个字节数要求是一致。

有了这个技术基础，我们的改表系统就针对这类需求做了优化，可以支持直接使用 ALTER TABLE 进行改表，如果是大表可以节省很多时间，提升效率，也因此遇到了很多问题，才有了这篇文章。

问题汇总

首先简单介绍一下我们的改表系统的处理逻辑，我们会根据业务的改表需求去选择最优的改表方案：

满足快速改表就直接使用 ALTER TABLE 进行操作。

比如，删除索引，修改表名/列名，修改默认值/注释，扩展 VARCHAR 长度，小表添加唯一索引以及 8.0 快速加列等等。
不满足快速改表就优先选择 gh-ost 进行改表

binlog format 不为 ROW 则不能使用 gh-ost，添加唯一索引必须使用 gh-ost。
不满足 gh-ost 都会选择 pt-osc 进行改表。

其中添加唯一索引会直接失败。

那么问题来了，我们是如何判断业务改表需求是不是扩展 VARCHAR？

其实思路也很简单，就是检查改表前后的 information_schema.columns 记录，用到的 SQL 如下：

select * from information_schema.columns where table_schema = 'db' and table_name = 'table' and column_name = 'col';

# 样例数据
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: information_schema
              TABLE_NAME: CHARACTER_SETS
             COLUMN_NAME: CHARACTER_SET_NAME
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: 
             IS_NULLABLE: NO
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 32
  CHARACTER_OCTET_LENGTH: 96
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8
          COLLATION_NAME: utf8_general_ci
             COLUMN_TYPE: varchar(32)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select
          COLUMN_COMMENT: 
   GENERATION_EXPRESSION:

DATA_TYPE 值是 VARCHAR
CHARACTER_MAXIMUM_LENGTH 的值，要求改表后要大于等于改表前的值
CHARACTER_OCTET_LENGTH 的值，要求改表前后这个值要么是都小于等于 255，要么是都大于 255
除 DATA_TYPE/COLUMN_TYPE/CHARACTER_MAXIMUM_LENGTH/CHARACTER_OCTET_LENGTH 字段外的其余字段要求改表前后保持一致

问题一：默认值问题

我们发现，如果还改了字段名、注释、默认值这种元数据信息，依旧是可以快速改表，于是乎就进行了优化，不再比较这三个属性 COLUMN_NAME|COLUMN_COMMENT|COLUMN_DEFAULT。

关于默认值，看起来有点复杂，最开始也是想跑偏了，认为判断 COLUMN_DEFAULT 的值就行，比较这个值前后要么都是 null，要么都不是 null。都不是 null 的情况下可以是任意值，比如可以用下面的逻辑判断改表前后是一致即可。

if(COLUMN_DEFAULT is null ,null,"")

但是有个问题，如果一个字段从 允许为 null 默认值为 1 变成 不允许为null 默认值也是 1，该值改表前后也是一致的，具体测试如下：

CREATE TABLE `tb_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rshost` varchar(20) DEFAULT '1' COMMENT '主机地址',
  `cpu_info` json DEFAULT NULL COMMENT 'cpu信息 json串',
  `mem_info` json DEFAULT NULL COMMENT 'mem信息 json串, 单位是GB',
  `io_info` json DEFAULT NULL COMMENT '磁盘io使用情况, 单位是KB',
  `net` json DEFAULT NULL COMMENT '网络使用情况, 单位是KB(speed单位是MB/S)',
  `a_time` datetime NOT NULL DEFAULT '2022-01-01 00:00:00',
  PRIMARY KEY (`id`),
  KEY `idx_a_time` (`a_time`),
  KEY `idx_rshost` (`rshost`)
) ENGINE=InnoDB AUTO_INCREMENT=623506728 DEFAULT CHARSET=utf8mb4
>select * from information_schema.columns where table_name = 'tb_test' and column_name = 'rshost'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: dbzz_monitor
              TABLE_NAME: tb_test
             COLUMN_NAME: rshost
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: 1
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 30
  CHARACTER_OCTET_LENGTH: 120
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_general_ci
             COLUMN_TYPE: varchar(30)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 主机地址
   GENERATION_EXPRESSION: 
1 row in set (0.00 sec)

>alter table tb_test modify `rshost` varchar(30) not null DEFAULT '1' COMMENT '主机地址';
Query OK, 1000000 rows affected (13.68 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

>select * from information_schema.columns where table_name = 'tb_test' and column_name = 'rshost'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: dbzz_monitor
              TABLE_NAME: tb_test
             COLUMN_NAME: rshost
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: 1
             IS_NULLABLE: NO
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 30
  CHARACTER_OCTET_LENGTH: 120
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8mb4
          COLLATION_NAME: utf8mb4_general_ci
             COLUMN_TYPE: varchar(30)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 主机地址
   GENERATION_EXPRESSION: 
1 row in set (0.00 sec)

可以看到 COLUMN_DEFAULT 这个列的值是非 null 且不变，按照上面的判断逻辑会认为可以快速改表，但是我们知道实际上这个需求是需要 copy 数据的。

其实，关于默认值问题使用 IS_NULLABLE 的值就可以完美解决, 如果是 null 到 not null 这个值会从 yes 变成 no；如果是 not null 到 null，这个值会从 no变成 yes。

所以最终解决方案仅比较 IS_NULLABLE 即可，只要改表前后一致就认为默认值这个属性满足快速改表。

在测试这个问题的时候发现一个现象：not null 到 null 可以使用 inplace 算法，但是需要 copy 数据；null 到 not null 不能使用 inplace，请看下面的用例：

-- not null --> null可以使用inplace
>alter table tb_test modify `rshost` varchar(30) DEFAULT '1' COMMENT '主机地址' ,ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (3.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- null --> not null不可以使用inplace
>alter table tb_test modify `rshost` varchar(30) not null DEFAULT '1' COMMENT '主机地址' ,ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY.
>

-- 可以使用下面的操作查看改表进度拷贝数据的情况，第一次使用需要开启此功能
-- UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
-- UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';

>SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+-----------------------------+----------------+----------------+
| EVENT_NAME                  | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------+----------------+----------------+
| stage/sql/copy to tmp table |         272289 |         978903 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)

-- 为了避免测试干扰，检查events_stages_history表之前可以先清空，切记不要对线上环境做此操作。
-- TRUNCATE TABLE performance_schema.events_stages_history;
>SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history;
+-----------------------------+----------------+----------------+
| EVENT_NAME                  | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------+----------------+----------------+
| stage/sql/copy to tmp table |        1000000 |         978903 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)

问题二：索引字段问题

过了一段时间又发现第二个问题，部分工单会触发执行 10 秒超时失败。

工单系统判断用户的改表需求，满足直接使用 ALTER TABLE 进行操作会有个 10 秒超时的兜底策略，来避免因为解析错误导致方案选择错误最终影响主从延迟。

另外，也建议带上 ALGORITHM=INPLACE, LOCK=NONE ，避免因为不是使用 inplace 导致 DML 阻塞。

这个问题排查了很久都没什么眉目，反反复复的查阅文档及测试，始终都认为这个需求一定是满足快速改表的方案。实在是想不明白到底是哪里的问题，还一度认为是 MySQL 的 Bug。

下面是一张 100w 记录表的测试用例：

> show create table tb_test\G
*************************** 1. row ***************************
       Table: tb_test
Create Table: CREATE TABLE `tb_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rshost` varchar(30) NOT NULL DEFAULT '1' COMMENT '主机地址',
  `cpu_info` json DEFAULT NULL COMMENT 'cpu信息 json串',
  `mem_info` json DEFAULT NULL COMMENT 'mem信息 json串, 单位是GB',
  `io_info` json DEFAULT NULL COMMENT '磁盘io使用情况, 单位是KB',
  `net` json DEFAULT NULL COMMENT '网络使用情况, 单位是KB(speed单位是MB/S)',
  `a_time` datetime NOT NULL DEFAULT '2022-01-01 00:00:00',
  PRIMARY KEY (`id`),
  KEY `idx_a_time` (`a_time`),
  KEY `idx_rshost` (`rshost`)
) ENGINE=InnoDB AUTO_INCREMENT=623506728 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

> select count(*) from tb_test;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.15 sec)

> alter table tb_test modify `rshost` varchar(31) NOT NULL DEFAULT '1' COMMENT '主机地址';
Query OK, 0 rows affected (3.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

> alter table tb_test modify `rshost` varchar(32) NOT NULL DEFAULT '1' COMMENT '主机地址', ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (3.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

> alter table tb_test drop index idx_rshost;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

> alter table tb_test modify `rshost` varchar(33) NOT NULL DEFAULT '1' COMMENT '主机地址', ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

> alter table tb_test modify `rshost` varchar(34) NOT NULL DEFAULT '1' COMMENT '主机地址', ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

>

可以看到 rshost 字段有一个索引，在扩展字段的时候虽然支持 inplace，但是实际上很慢，内部应该是重建索引了，后来将索引删除后就秒改了。

针对这个场景，我们的解决方案是使用 gh-ost/pt-osc 进行改表，那么问题来了，我们应该怎么判断目标字段是否是被索引了呢？

请看下面的 SQL，information_schema.STATISTICS 记录了一个表的所有索引字段信息，可以很方便的判断某个字段是否被索引。

select * from information_schema.STATISTICS where table_schema = 'db' and table_name = 'table' and column_name = 'col';

其他问题

这个问题也是执行 10 秒超时，也就是文章开头提到的业务反馈的问题，其实跟 问题二 差不多同期，但在解决了 问题二 后还是一直找不到原因及解决方案。

关于这个问题甚至都没法复现，不像 问题二 可以方便复现，当时在业务的线上库做操作又能 100% 复现，但是将他们的表及数据单独导出来放在测试环境就不行。

在业务库上测试是选了一个从库，不记录 binlog 的方式（set sql_log_bin = 0）。虽然不建议这么做，但是实属迫不得已，在测试环境复现不出来。

后来实在找不到原因，就跳过快速改表的方案使用改表工具进行处理，后来这个事情就算不了了之了。直到前几天业务突然找我，说之前的那个表能快速改表了。我赶紧去查看了工单详情，发现确实如业务所述，这回我就更加郁闷了，难不成是见鬼了？这玩意还自带歇业窗口的嘛？

本着严谨的态度，又去测了一下。确实是可以满足快速改表了，但是原因还是找不到，这感觉真的很难受。

最后，静下来认真梳理了一下，发现了一些猫腻。下面是我的测试思路：

1. 将线上的表导出并导入到测试环境

因为表本身就几个 G，不算大就使用了 mysqldump 进行导出导入。这个操作并非 100% 复原线上的环境，有个隐藏的变量被修改了，那就是这个表被重建了，这个跟之前业务用改表工具进行修改后的操作有点类似，所以就猜想，会不会是因为这个表本身存在空洞导致的呢。

最后通过拉历史备份，还原了一个环境进行了测试，果不其然不能快速改表。为了印证了想法，就去查了一下这个表的空洞。十分遗憾，这个表并没有存在空洞（空洞只有几 MB）。这回又郁闷了，还以为要破案了，但是不管怎么样既然怀疑是重建表能解决，那就开搞。

2. 重建前的状态

业务从 varchar(300) 扩展到 varchar(500)，其他属性没变更。

| 1170930999 | dba            | 192.168.1.100:47522  | dbzz_dbreport | Query            |      45 | altering table                                        | ALTER TABLE  t_recycle_express  MODIFY  address VARCHAR(500) NOT NULL DEFAULT '' COMMENT '地址', ALGORITHM=INPLACE, LOCK=NONE;             |

3. 重建后的状态

>ALTER TABLE t_recycle_express engine = innodb;
Query OK, 0 rows affected (18 min 52.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

>ALTER TABLE
    ->   t_recycle_express
    -> MODIFY
    ->   address VARCHAR(500) NOT NULL DEFAULT '' COMMENT '地址';
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

活久见，还真是重建表后就能解决了！虽然很郁闷，终究是有一个解决方案了，后期我们决定对此做个优化，将满足快速改表的工单又触发十秒超时的改为使用 gh-ost/pt-osc 重新执行，以此避免业务反复提交工单，应该能大大提升好感度。

这个问题虽然知道解决方案，但是依旧不知道原因，我猜测可能是跟统计信息不准确有关系（或者约束），要是有大佬知道原因，请告知一下。

总结

MySQL Online DDL 特性给 DBA 带来了很多的便利，提升了工作效率，我们可以基于官方的理论作为指导去优化我们的系统。但是实际情况是理论知识很简单，线上环境十分复杂，可能会遇到各种意料之外的事情，任何线上的操作都要给自己留好后路做好兜底，这是十分必要的。

我们的系统，如果没有添加 10 秒超时的兜底，那势必会因为解析错误导致选了错误的改表方案，然后导致从库延迟，可能会影响线上业务，想想都有点心慌。

这里有个注意事项，针对执行超时不能简单的使用 timeout 等属性进行控制，还需要添加检查逻辑，要到数据库里面去查一下任务是否真的已经终止了。避免因为 timeout 异常导致终止信号没有给到 MySQL，这种可能会引发一系列问题，切记切记。

以上，仅供参考。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

SQLE 获取

类型	地址
版本库	https://github.com/actiontech/sqle
文档	https://actiontech.github.io/sqle-docs/
发布信息	https://github.com/actiontech/sqle/releases
数据审核插件开发文档	https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse

微信关注我们

原文链接：https://my.oschina.net/actiontechoss/blog/10994106

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

BPF开发: 从Hello World开始

Part 1 概述 1.背景 BPF技术被列为近些年Linux内核领域最火热的新领域之一。它成功的给Linux内核赋予了少量的动态可编程性，可以在Linux内核运行时，实时修改内核的行为,但不需要重新编译和重启内核。据此，BPF在Linux世界中: 网络可观测性安全三大领域大放异彩，学习好BPF技术，对于Linux内核和应用开发者来说，是一件非常有意义的事情。 2. 什么是BPF？ BPF在Linux内核中，被实现为一个非常精简的虚拟机，具有几乎性能无损的执行效率。我们可以用类似C语言的语法，编写代码，编译成可以在BPF虚拟机中运行的汇编代码，实现其强大的逻辑处理能力。今天，我们将从 Hello World 开始，带您进入 BPF 的世界。 3. 开始BPF 按照计算机程序设计语言学习的传统，我们从经典的Hello World程序开始我们的eBPF开发之旅。首先简单对比下标准C程序和eBPF程序的异同。 C语言程序 # HelloWorld.c #include <stdio.h> int main() { printf("HelloWorld\n"); ...

2024-01-31

373

背景十年后数据库还是不敢拥抱NUMA，这篇经典的纠正大家对NUMA 认知的文章一晃发布快3年了，这篇文章的核心结论是：之所以有不同的NUMA Node 是不同的CPU Core 到不同的内存距离远近不一样所决定的，这是个物理距离程序跑在不同的核上要去读写内存可以让性能差异巨大，所以我们要尽量让一个程序稳定跑在一个Node 内默认打开NUMA Node 其实挺好的写这个续篇是我收到很多解释，因为跨Node 导致性能抖动，所以集团在物理机OS 的启动参数里设置了 numa=off ，也就是不管BIOS 中如何设置，我们只要在OS 层面设置一下 numa=off 就能让程序稳定下来不再抖了！我这几年也认为这是对的，只是让我有点不理解，既然不区分远近了，那物理上存在的远近距离(既抖动)如何能被消除掉的呢？所以这个续篇打算通过测试来验证下这个问题设置 BIOS 中有 numa node 设置的开关(注意这里是内存交错/交织)，不同的主板这个BIOS设置可能不一样，但是大同小异，基本都有这个参数 Linux 启动引导参数里也可以设置numa=on(默认值)/off ，linux ...

2024-01-31

379

资源下载

更多资源

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。