阅读 1377

MYSQL-追根朔源 你的表情(🚀🚀🚀utf8mb4)插入对了吗?

问题背景

线上异常监控发现项目中有sql异常,查看发现是表情插入异常

image-20200601173013557

项目环境

线上MYSQL 版本:
	polarDB:5.6.16-log
测试MYSQL 版本:
	5.7.25-28-log
  5.7.25-28-log
druid: 
	1.1.10
druid-spring-boot-starter:
	1.1.10
mysql-connector-java:
	5.1.42
复制代码

问题处理

  • 下意识直接想到数据库没有用utf8mb4 类型,直接查看数据库表结构发现数据库表确实是utf8mb4
CREATE TABLE `t_course_resource` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '课程关联的资源id',
  `c_course_uid` char(32) NOT NULL DEFAULT '' CO',
  `c_uid` varchar(256) NOT NULL DEFAULT '' COMMENT '资源id',
  `c_name` varchar(64) NOT NULL DEFAULT '' COMMENT '资源名',
  `c_description` varchar(128) NOT NULL DEFAULT '' COMMENT '资源描述',
  `c_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
  `c_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
  `c_is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '记录是否被删除',
  PRIMARY KEY (`id`),
  KEY `idx_course_uid` (`c_course_uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4220171 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程资源'
复制代码
  • 为了验证数据库端没有问题,直接提交流水线,走数据库更新一条sql,提交表情字符串,发现是可以正确执行的。

image-20200601173143462

上述两步验证可以暂时排除数据库端的问题。

  • 测试环境和开发环境尝试用代码提交带表情的字符串,发现是可以的,对比配置中心和代码除了数据库地址完全一样。(线上是polarDB,测试是自建)

  • 查看阿里云数据库文档,看到有这么一句话,发现不建议配置characterEncoding,然后瞎猜算命去掉配置,测试了下,发现还是不行。

    image-20200601174552744

  • 询问DBA,DBA建议尝试用 connectionInitSqls = set names utf8mb4 配置测试下,没有研究过这个配置,也不清楚有什么用,但是DBA大佬都建议了,就尝试了下,确实可以,吹一句DBA大佬真香。

sql 验证

  • ​ set name utf8mb4 作用分析和验证:

  • 测试表:

    CREATE TABLE `t_test_encoding` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `name` int(11) DEFAULT NULL,
         PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    复制代码
  • 1. 连接数据数据库 ,执行 show global variables like 'character%'; 查看全局的字符集配置
    复制代码

    image-20200601194219169

  • 2. 执行 show variables like 'character%'; 查看当前会话的字符集配置
       执行插入表情操作 INSERT INTO `t_demo` ( `name`) VALUES ('测试表情🆚🏷❤️😌');发现报错。
    复制代码

    image-20200601194631780

    image-20200602110002199

  • 3. 执行 set names utf8mb4; 设置当前会话的字符集
    4. 再次执行 show variables like 'character%'; 查看当前会话的字符集配置
    	 再次执行插入表情操作 INSERT INTO `t_demo` ( `name`) VALUES ('测试表情🆚🏷❤️😌'); 插入OK
    复制代码

    image-20200601194811367

  • 5. 执行 show global variables like 'character%'; 查看全局的字符集配置
    复制代码

    image-20200601194853052

总结:

1. 是否能够插入表情,不仅仅要求相关的字段是utf8mb4编码格式,utf8mb4仅仅只是前提
2. 除了要求字段的格式是utf8mb4,还要求当前的session会话连接的编码同时是utf8mb4
3. set name utf8mb4  只对当前会话有效,不影响其他连接和全局的配置
复制代码

原因分析

  • 问题到这就结束了吗?当然没有,身为一个有追求的猴子,怎么可能这样就完了,肯定要研究透这个问题,现在还有以下两点想不明白的。

    • connectionInitSqls 是用来干嘛的,为什么配置一个这个就可以了?
    • 为什么测试环境和开发环境可以,线上不行?(程序员界历史难题,为什么我本地是可以,上服务器就挂 [流泪] [流泪] [流泪])
  • 接下来我们就看看这两个问题的分析:

  • 问题1:

    • connectionInitSqls 是druid 数据库特有的配置(查看官方文档说明该参数是在初始化连接时执行的sql),那这样就很好理解了,当我们执行set name utf8mb4之后就在当前session 中一直使用utf8mb4的编码格式

      image-20200601175817141

    • 接下来分析 druid 数据源数据库连接初始化, connectionInitSqls 执行分析。

      • druid 数据源初始化连接:(获取配置的连接初始化执行的sql,并依次执行),也就意味着我们如果配置了utf8mb4 ,那么数据库连接初始化的时候就会执行set names utf8mb4。

        • 我们在配置中心配置下connectionInitSqls

          spring.datasource.druid.connection-init-sqls = set names utf8mb4
          复制代码
        • 我们先启动项目,抓取初始化连接时和MYSQL 服务端通信的数据包看下

          image-20200602101736618

          image-20200602101636190

      • 重点重点重点(通过抓包我们发现)

          1. 数据源和mysql的初始化连接过程并不只包含3次握手,其中还有很多次的 query-response --- sql 操作
          1. 在完成一次连接初始化之后,我们发现执行了两次字符集设置 SET NAMES utf8 和 set names utf8mb4。其中第二次 set names utf8mb4才是我们通过设置 connection-init-sqls,这次的设置也是当前会话最终生效的字符集设置,其中第一次怎么来的,先卖个关子,等会再来分析(这个问题其实就和测试开发环境为什么可以有关)
      • 接下来我们看下druid 数据源层是如何设置这个字符集的,通过druid-spring-boot-starter 的自动配置源码, 我们debug 很容易就发现 connectionInitSqls 的执行逻辑

        @Configuration
        @ConditionalOnClass(DruidDataSource.class)
        @AutoConfigureBefore(DataSourceAutoConfiguration.class)
        @EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
        @Import({DruidSpringAopConfiguration.class,
            DruidStatViewServletConfiguration.class,
            DruidWebStatFilterConfiguration.class,
            DruidFilterConfiguration.class})
        public class DruidDataSourceAutoConfigure {
        
            private static final Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceAutoConfigure.class);
        
            @Bean(initMethod = "init")
            @ConditionalOnMissingBean
            public DataSource dataSource() {
                LOGGER.info("Init DruidDataSource");
                return new DruidDataSourceWrapper();
            }
        }
        复制代码

        image-20200602100634192

        image-20200602100815075

      • 我们 debug断点到 Collection initSqls = getConnectionInitSqls();,再次启动项目,抓包发现 只有一次 SET NAMES utf8mb4。当我们放行这次断点,就会发现,set names utf8mb4,到此为止就很明了了, druid 数据源在初始化连接之后,还会进行配置的initSql初始化,在这次初始化里,用户设置了set names utf8mb4 之后,就会指定了最终的字符集。

        image-20200602102925962

  • 问题2:

    • 为什么测试环境和开发环境可以呢?(细心的同学可能发现,我们在解析问题1的时候已经提到了,测试环境的问题可能和第一次的set names 有关)接下来我们测试下。

    • 将数据库连接改到开发环境, 重启项目,取消connectionInitSqls,debug 断点到 Collection initSqls = getConnectionInitSqls(),抓包。

      image-20200602105346805

    • 发现只有一次SET NAMES,而且第一次就是 utf8mb4,这就意味着,即使我们不通过 druid 的 connectionInitSqls,那这次的session会话连接,也是OK的。

    • 到这里,很多同学就可能觉得开发环境的第一次 SET NAMES utf8mb4 应该是合开发环境的全局字符集配置有关,我们连接开发环境执行下sql 验证下, 确实是这样的。

      show global variables like 'character%';
      复制代码

      image-20200602111011779

    • 那我们现在知道了,第一次的字符集设置和mysql服务端的全局配置有关,👌,到这就完了吗?,没有我们继续

    • 我们知道设置当前会话的字符集设置是客户端主动设置的,那么客户端有什么理由这样设置呢?,我们看源码发现druid没有做这样的操作,在druid设置之前,这个动作已经发生了,那我们猜应该是在mysql-connector-java里设置的,接下来看源码验证。

    • 我们debug的路径是这样的(省略了查找最终代码的过程),给大家展示一个debug的路径,大家可以根据这个路径去查找

      image-20200602111824623

      image-20200602112025888

    • 我们发现是否SET NAMES utf8mb4 取决于下面的条件, 我们来分析下这两个条件:

      boolean utf8mb4Supported = versionMeetsMinimum(5, 5, 2);
      boolean useutf8mb4 = utf8mb4Supported && (CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex));
      
      /**
           * Does the version of the MySQL server we are connected to meet the given
           * minimums?
           * 
           * @param major
           * @param minor
           * @param subminor
           */
          boolean versionMeetsMinimum(int major, int minor, int subminor) {
              if (getServerMajorVersion() >= major) {
                  if (getServerMajorVersion() == major) {
                      if (getServerMinorVersion() >= minor) {
                          if (getServerMinorVersion() == minor) {
                              return (getServerSubMinorVersion() >= subminor);
                          }
      
                          // newer than major.minor
                          return true;
                      }
      
                      // older than major.minor
                      return false;
                  }
      
                  // newer than major
                  return true;
              }
      
              return false;
          }
      复制代码
  • 分析条件

    第一个条件 MySQL server version是否比 5.5.2 大
    第二个条件 CharsetMapping.UTF8MB4_INDEXES 是否包含 this.io.serverCharsetIndex
    
    只有两个条件同时满足才可以设置 utf8mb4,否则就是utf8
    第一个很好理解,而且我们MYSQL SERVER的版本也满足
    
    主要是看下第二个条件
    先看下 UTF8MB4_INDEXES 放的是什么?
    复制代码

    image-20200602113052419

        private static final String MYSQL_CHARSET_NAME_utf8mb4 = "utf8mb4";
        Collation[] collation = new Collation[MAP_SIZE];
        collation[1] = new Collation(1, "big5_chinese_ci", 1, MYSQL_CHARSET_NAME_big5);
        ........
        collation[45] = new Collation(45, "utf8mb4_general_ci", 1, MYSQL_CHARSET_NAME_utf8mb4);   
    复制代码
    • 我这边摘取了部分源码,详细的可以看 com.mysql.jdbc.CharsetMapping 类

    • 通过上述代码,我们发现 UTF8MB4_INDEXES, 存放的其实是 45一个整数值

    • 再回过头看这个条件,我们只需要知道 this.io.serverCharsetIndex 这个值是多少就可以了

      CharsetMapping.UTF8MB4_INDEXES.contains(this.io.serverCharsetIndex)

    • 再次跟进代码 com.mysql.jdbc.MysqlIO#doHandshake (省略其他代码)

    		/**
         * Initialize communications with the MySQL server. Handles logging on, and
         * handling initial connection errors.
         *  初始化与MySQL服务器的通信。处理登录和初始连接错误。
         * @param user
         * @param password
         * @param database
         * 
         * @throws SQLException
         * @throws CommunicationsException
         */
        void doHandshake(String user, String password, String database) throws SQLException {
            ....
            ....  
    
            if ((versionMeetsMinimum(4, 1, 1) || ((this.protocolVersion > 9) && (this.serverCapabilities & CLIENT_PROTOCOL_41) != 0))) {
    
                /* New protocol with 16 bytes to describe server characteristics */
                // read character set (1 byte)
                this.serverCharsetIndex = buf.readByte() & 0xff;
                // read status flags (2 bytes)
                this.serverStatus = buf.readInt();
                checkTransactionState(0);
            }
        }
    复制代码
    • 发现这个属性是在和服务端初始化连接时设置的,再次抓包握手代码,发现3次握手完毕后,服务端有一个相应,回复了 Server Language: utf8mb4 COLLATE utf8mb4_general_ci (45) 服务器端的字符集设置

      image-20200602114237561

      45 是怎么来的呢,其实是我们数据库 information_schema.COLLATIONS 表的id字段

      image-20200602114750230

    • 现在就很明了了,初始化连接时, mysql-connector-java 会获取当前数据库的mysql 全局字符配置,然后根据MYSQL SERVER的版本和MYSQL SERVER 的字符集配置来决定使用哪个字符集进行设置 SET NAMES

总结:

  1. 是否能够插入表情,不仅取决于字段的编码格式,还有当前session会话的编码格式

  2. 设置utf8mb4

    1. 设置全局的 字符集设置 show global variables like 'character%'; -DBA可能不会帮你搞,在线上跑了这么久,你让我改这个?

    2. 设置当前会话的字符集设置 druid connectionInitSqls

    3. 如果没有这个配置,可以在连接池初始化之后手动执行一次 set names utf8mb4

    4. 升级mysql-connector-java 到 5.1.47,5.1.47 版本的 characterEncoding 参数设置为 UTF8/UTF-8 的时候, 会直接映射到 utf8mb4, 不像低版本那样还需要依赖数据库返回的编码, 也不用重启数据库即可生效 dev.mysql.com/doc/relnote…

      image-20200602120352363