By 兰春

Updated:

Contents
  1. 1. 前提
  2. 2. 场景一
  3. 3. 场景二
  4. 4. 场景三

前提

上周遇到次奇葩的同步错误，error 1048 ， 看似是简单的not null导致，但是为什么master可以执行，slave不行呢？为什么5.1的slave可以，5.6的slave不行呢？ 带着很多疑问，准备来一窥究竟

1
[ERROR] Slave SQL: Error 'Column 'type_id' cannot be null' on query. Default database: ''. Query: 'insert into if_dw_stats.da_upload_nh_score_rank_result(city_id,city_name,comm_id,region_name,paid,comm_name_nh,region_id_num,region_id,subregion_id_num,subregion_id,vcuv,vcuv_z,call_vcuv,call_vcuv_z,orders_vcuv,orders_vcuv_z,peitao,peitao_z,result_score,rank,type_id,type_name,pinyin,cal_dt) values (N), 其中N>9000;

这里总结一下我遇到过的错误，分三种情况,虽然都是由于null引起，但是1048才是重点。

  • timestamp字段类型，为什么master执行成功，同步到slave报错？
  • int字段类型，5.1（master）<—- 5.6(slave)，同步报错？
  • int字段类型，5.6（master）<—- 5.6(slave)，同步报错？

接下来，开始进入主题

场景一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
* DB架构： Master(5.1) <-- Slave(5.6)
 
* 表结构如下 ：
dbadmin:abc> desc lc_time;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)
* master
dbadmin:abc> select @@global.explicit_defaults_for_timestamp;
+------------------------------------------+
| @@global.explicit_defaults_for_timestamp |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)
	
dbadmin:abc> insert into lc_time values(null);
Query OK, 1 row affected (0.02 sec)
dbadmin:abc> select * from lc_time;
+---------------------+
| id                  |
+---------------------+
| 2014-11-25 13:02:14 |
+---------------------+
1 row in set (0.00 sec)	
*slave
dbadmin:abc> select @@global.explicit_defaults_for_timestamp;
+------------------------------------------+
| @@global.explicit_defaults_for_timestamp |
+------------------------------------------+
|                                        1 |
+------------------------------------------+
1 row in set (0.00 sec)
dbadmin:abc> insert into lc_time values(null);
ERROR 1048 (23000): Column 'id' cannot be null

  • 结论：master上explicit_defaults_for_timestamp=0，slave上explicit_defaults_for_timestamp=1，会出现这种错误。

  • 解决方案：

    1. 保证master和slave explicit_defaults_for_timestamp 一致。

    2. 前端过滤掉null。

场景二

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
* DB架构
		master(5.1)
			| 
    -------------------------------------
    |					|	
  slave A(5.1)        slave B(5.6)
  
* 表结构
dbadmin:abc> show create table abc;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| abc   | CREATE TABLE `abc` (
  `id` int(11) DEFAULT NULL,
  `id2` int(11) NOT NULL DEFAULT '6'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
* 核心参数： master 和 slave A，B 的sql_mode 都是 '';
* 症状：在master上执行一条SQL语句 insert into abc values(1,0),(1,null);
  结果 Slave A 正常， Slave B 报error 1048，Error 'Column 'id2' cannot be null' on query， 这是为什么呢？
  Question1：为什么insert into abc values(1,null)失败？insert into abc values(1,0),(1,null);成功？
  Question2：为什么5.1 slave可以，5.6slave 不行？
  Question3：手动去slave B上执行同样的insert，为什么可以执行成功？
  如果你已经知道为什么，可以忽略下面的分析。
       	
* 分析：
	细心的读者已经发现，第一个问题的答案已经在sql_mode链接中。接下来，测试过程中发现：insert into abc values(1,0),(1,null); 在sql_mode=''的时候，不管是5.1还是5.6都会成功执行。那么问题只有一个，sql_mode出了问题。查看master binlog后发现：在insert语句之前，多了这个可以执行的注释：SET @@session.sql_mode=2097152。我们来看看：
dbadmin:abc> SET @@session.sql_mode=2097152;
Query OK, 0 rows affected (0.00 sec)
dbadmin:abc> select @@session.sql_mode;
+---------------------+
| @@session.sql_mode  |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)
这下，似乎发现了蛛丝马迹，那么问题又来了。
SET @@session.sql_mode=2097152; 从何而来？是程序写的？还是mysql自带的？
经过一番折腾，定位到此SQL来自java jdbc 。
以下代码摘自 java ConnectionIMPL.java
	private void setupServerForTruncationChecks() throws SQLException {
		if (getJdbcCompliantTruncation()) {
			if (versionMeetsMinimum(5, 0, 2)) {
				String currentSqlMode = 
					this.serverVariables.get("sql_mode");
				
				boolean strictTransTablesIsSet = StringUtils.indexOfIgnoreCase(currentSqlMode, "STRICT_TRANS_TABLES") != -1;
				
				if (currentSqlMode == null ||
						currentSqlMode.length() == 0 || !strictTransTablesIsSet) {
					StringBuffer commandBuf = new StringBuffer("SET sql_mode='");
					
					if (currentSqlMode != null && currentSqlMode.length() > 0) {
						commandBuf.append(currentSqlMode);
						commandBuf.append(",");
					}
					
					commandBuf.append("STRICT_TRANS_TABLES'");
					
					execSQL(null,  commandBuf.toString(), -1, null,
							DEFAULT_RESULT_SET_TYPE,
							DEFAULT_RESULT_SET_CONCURRENCY, false,
							this.database, null, false);
					
					setJdbcCompliantTruncation(false); // server's handling this for us now
				} else if (strictTransTablesIsSet) {
					// We didn't set it, but someone did, so we piggy back on it
					setJdbcCompliantTruncation(false); // server's handling this for us now
				}
				
			}
		}
	}
大致的意思就是：如果sql_mode = ‘’，那么java会调高sql_mode的级别，commandBuf.append("STRICT_TRANS_TABLES'");
ok，这下我们已经知道此set来自java，那么问题又来了。即便设置STRICT_TRANS_TABLES，要出问题，master就会报错了，为啥master是好的，Slave A是好的，却Slave B 同步出错呢？
结果已经很明显，因为Slave B是5.6。说的明显一点就是：
在严格模式下，5.1中可以执行，但是5.6不行，这应该算是5.6安全方面的新特性么？
有兴趣的同学可以自己测试下。

  • 解决方案

    1. 配置java或者修改java源码，让其不要更改mysql的sql_mode
    2. 临时解决方案： insert ignore xxx；
    3. sql_mode的规范。

场景三

  • 来自case when的奇葩错误
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
* DB架构   Master(5.6)  <--- Slave (5.6)
 
* sql_mode 都是'';
* 报错如下：
  Replicate_Wild_Ignore_Table: mysql.%,test.%
                   Last_Errno: 1048
                   Last_Error: Error 'Column 'referer' cannot be null' on query. Default database: 'action_db'. Query: 'insert into oplogin_log(`cityId`,`userId`,`userName`,`uri
`,`referer`,`logType`,`logDate`,`ip`,`status`)
              values('','','kyqxmxyt','/login.php?rtn=1','http://xx.com:80/login.php?rtn=' RLIKE (SELECT (CASE WHEN (ORD(MID((SELECT IFNULL(CAST(COUNT(DISTINCT(schema_na
me)) AS CHAR),0x20) FROM INFORMATION_SCHEMA.SCHEMATA),1,1))>50) THEN 0x687474703a2f2f6f70746f6f6c732e616e6a756b652e636f6d3a38302f6c6f67696e2e7068703f72746e3d ELSE 0x28 END)) AND 'ae
WZ'='aeWZ','1','1416020259','114.242.250.192','2') #v1:checklogin@login.php (15) 1416020259'
这条奇葩且牛B的SQL，我来稍微翻译一下，如果INFORMATION_SCHEMA.SCHEMATA 去重后，得到的库名的第一个字符如果是1，返回0，否则返回 null。
将这种SQL稍微转换成简单一点的：
master:abc> desc abc;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| id2   | int(11) | NO   |     | 6       |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
master:abc> select * from abc;
+------+-----+
| id   | id2 |
+------+-----+
|    1 |   0 |
|    1 |   0 |
|    2 |   0 |
|    2 |   0 |
|    1 |   1 |
|    1 |   0 |
+------+-----+
6 rows in set (0.00 sec)
master:abc> select * from lc;
Empty set (0.00 sec)	
   
   
master:abc> insert into abc values('1', case when (select count(*) from lc) < 1 then 1 else NULL end );
Query OK, 1 row affected (0.00 sec)
查看master的binlog如下：
*binlog*
# at 1109
#141125 12:44:51 server id 101082106  end_log_pos 1271 CRC32 0x9ec0ca94         Query   thread_id=28    exec_time=0     error_code=0
SET TIMESTAMP=1416890691/*!*/;
insert into abc values('1', case when (select count(*) from lc) < 1 then 1 else NULL end )
/*!*/;
slave:abc> select * from abc;
+------+-----+
| id   | id2 |
+------+-----+
|    1 |   0 |
|    1 |   0 |
|    2 |   0 |
|    2 |   0 |
|    1 |   0 |
|    1 |   0 |
+------+-----+
6 rows in set (0.00 sec)
slave:abc> select * from lc;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
*slave status*
   Last_SQL_Errno: 1048
   Last_SQL_Error: Error 'Column 'id2' cannot be null' on query. Default database: 'abc'. Query: 'insert into abc values('1', case when (select count(*) from lc) < 1 then 1 else NULL end )'

  • 结论

    1. 最终binlog并不是RBR，所以会报错。
    2. 临时解决方案： insert ignore xxx. 然后再用pt-table-checksum && pt-sync等修复。
    3. 禁止case when语句。

Mysql reference 5.6 error code

Contents
  1. 1. 前提
  2. 2. 场景一
  3. 3. 场景二
  4. 4. 场景三

幸福,不在于得到的多

而在于计较的少