线上异常监控发现项目中有sql异常,查看发现是表情插入异常
线上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
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='课程资源'
上述两步验证可以暂时排除数据库端的问题。
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%'; 查看全局的字符集配置
2\. 执行 show variables like 'character%'; 查看当前会话的字符集配置
执行插入表情操作 INSERT INTO `t_demo` ( `name`) VALUES ('测试表情❤️');发现报错。
3\. 执行 set names utf8mb4; 设置当前会话的字符集
4\. 再次执行 show variables like 'character%'; 查看当前会话的字符集配置
再次执行插入表情操作 INSERT INTO `t_demo` ( `name`) VALUES ('测试表情❤️'); 插入OK
5\. 执行 show global variables like 'character%'; 查看全局的字符集配置 复制代码
总结:
1\. 是否能够插入表情,不仅仅要求相关的字段是utf8mb4编码格式,utf8mb4仅仅只是前提
2\. 除了要求字段的格式是utf8mb4,还要求当前的session会话连接的编码同时是utf8mb4
3\. set name utf8mb4 只对当前会话有效,不影响其他连接和全局的配置
问题到这就结束了吗?当然没有,身为一个有追求的猴子,怎么可能这样就完了,肯定要研究透这个问题,现在还有以下两点想不明白的。
接下来我们就看看这两个问题的分析:
问题1:
druid 数据源初始化连接:(获取配置的连接初始化执行的sql,并依次执行),也就意味着我们如果配置了utf8mb4 ,那么数据库连接初始化的时候就会执行set names utf8mb4。
我们在配置中心配置下connectionInitSqls
spring.datasource.druid.connection-init-sqls = set names utf8mb4
我们先启动项目,抓取初始化连接时和MYSQL 服务端通信的数据包看下
重点重点重点(通过抓包我们发现)
接下来我们看下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();
}
}
我们 debug断点到 Collection initSqls = getConnectionInitSqls();,再次启动项目,抓包发现 只有一次 SET NAMES utf8mb4。当我们放行这次断点,就会发现,set names utf8mb4,到此为止就很明了了, druid 数据源在初始化连接之后,还会进行配置的initSql初始化,在这次初始化里,用户设置了set names utf8mb4 之后,就会指定了最终的字符集。
问题2:
show global variables like 'character%';
我们发现是否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 放的是什么?
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);
/**
* 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) 服务器端的字符集设置
45 是怎么来的呢,其实是我们数据库 information_schema.COLLATIONS 表的id字段