【20181101】MySQL text类型的column设置为NOT NULL 导致主从1364 -云博客

【20181101】MySQL text类型的column设置为NOT NULL 导致主从1364

# 数据库 2024-05-05 06:56 0 25 来源：云博客

环境

系统版本： CentOS release 6.8 (Final)

MySQL版本：5.6.29-log MySQL Community Server (GPL)

MySQL主从配置信息

binlog format ：MIXED
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

问题描述

从库show slave status监控的时候发现sql_thread进程已经变成NO，并且爆出了1362错误，仔细查看报错的是一条insert into语句，并且抛出了一个详细的错误，大致的意思就是字段column_1设置了NOT NULL但是没有插入值并且没有默认值。然后仔细检查了一下表结构，具体信息如下

字段column_1的确设置的是NOT NULL并且没有设置default 默认值。
column_1字段是是text类型，所以是无法设置defaut默认值。

本地环境手动执行验证

在看到这个报错的信息的时候，直接google了一下，大部分答案显示都是由于sql_mode的原因所导致的，要想解决这个问题，必须将执行：

mysql> set global sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

但是我在slave获取得到的sql_mode就是 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,这个时候我在自己本地执行这个insert 插入语句验证是否可以插入成功，发现是可以插入的。

猜想

这个时候我就想这个是否在主从复制的过程中，slave在执行这个insert插入的时候sql_mode发生了更改了，这个时候我解析slave的relay log信息获取得到：

# at 271#181019 12:13:00 server id 5403005 end_log_pos 319 CRC32 0x755a7204 GTID [commit=yes]SET @@SESSION.GTID_NEXT= ‘8e91de47-8f0b-11e8-824c-246e9699bb48:1408667354‘/*!*/;# at 319#181019 12:13:00 server id 5403005 end_log_pos 440 CRC32 0x1add7880 Query thread_id=47560669 exec_time=0 error_code=0SET TIMESTAMP=1539922380/*!*/;SET @@session.pseudo_thread_id=47560669/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1344274432/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8mb4 *//*!*/;SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;SET @@session.time_zone=‘SYSTEM‘/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 440#181019 12:13:00 server id 5403005 end_log_pos 689 CRC32 0x1537384f Query thread_id=47560669 exec_time=0 error_code=0use `finance_fixin_product`/*!*/;SET TIMESTAMP=1539922380/*!*/;.............../*!*/;

可以很明显的得到在上面binlog中有一个 SET @@session.sql_mode=1344274432/!/; 操作，那么这操作将赋予sql_mode什么值呢？

mysql> SET @@session.sql_mode=1344274432;Query OK, 0 rows affected (0.00 sec)mysql> select @@global.sql_mode;+--------------------------------------------+| @@global.sql_mode |+--------------------------------------------+| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+--------------------------------------------+1 row in set (0.00 sec)mysql> select @@sql_mode;+----------------------------------------------------------------+| @@sql_mode |+----------------------------------------------------------------+| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+----------------------------------------------------------------+1 row in set (0.00 sec)

执行操作之后，突然发现sql_mode多出来一个STRICT_TRANS_TABLES，这个时候大致可以获取得到端倪了，因为在binlog在执行这条SQL的设置了session的sql_mode为严格模式，所以导致在sql_thread重放的时候出现了这个问题。

但是这个时候又冒出了一个新的问题，因为线上的表结构和架构和测试环境一模一样，并且线上运行了这么久的时间也没有出现这个问题，带着这个疑问我再次解析了线上binlog的日志信息：

#181101 10:12:26 server id 5403005 end_log_pos 420 CRC32 0x2fe38b78 Query thread_id=51519927 exec_time=0 error_code=0SET TIMESTAMP=1541038346/*!*/;SET @@session.pseudo_thread_id=51519927/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1344274432/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8mb4 *//*!*/;SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;SET @@session.time_zone=‘SYSTEM‘/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 420#181101 10:12:26 server id 5403005 end_log_pos 538 CRC32 0xb848f658 Table_map: `finance_fixin_cgb_gateway`.`t_cgb_receive_message` mapped to number 2056756# at 538#181101 10:12:26 server id 5403005 end_log_pos 740 CRC32 0x42756468 Write_rows: table id 2056756 flags: STMT_END_F### INSERT INTO ### SET### @1=5300431### @2=‘PA0052‘### @3=‘b0009201811010033433728‘### @4=‘10.201.5.129‘### @5=2433### @6=‘128‘### @7=‘0009a201811010143659612590571520‘### @8=‘000920180907OR23805082566791168‘### @9=‘0‘### @10=‘0000‘### @11=‘0000-操作成功。‘### @12=‘2018-11-01 10:12:26‘### @13=‘2018-11-01 10:12:26‘# at 740#181101 10:12:26 server id 5403005 end_log_pos 771 CRC32 0xf5516de4 Xid = 12837058229COMMIT/*!*/;

对比之后发现，线上的binlog格式是ROW格式，并且通过查看insert语句发现在insert插入的时候没有默认值会默认设置为‘ ‘，所以slave是不会报错的。