数据库内核月报 - 2015 / 02

MySQL · 答疑释惑· 5.5 和 5.6 时间类型兼容问题

问题描述

5.6.4及以上版本,datetime,time,timestamp的Binlog在5.6.4以下的备库无法执行,如:

5.6.16(主库): create table t1(t datetime default now()); insert into t1 values(now());

5.5.18(备库): show slave stauts\G ;

此时备库中断,报错:Last_Errno: 1677,

描述信息:Last_Error: Column 1 of table t1.t' cannot be converted from type '' to type 'datetime'

详情见Bug#70085

问题原因

1) 5.5版本存储的是datetime,time,timestamp这三种数据类型的长整型的数据,insert时的BT为:

#0 TIME_to_ulonglong_datetime (my_time=0x2ad2c82e84c0) at /u01/workplace/Percona-Server-5.5.18/sql-common/my_time.c:1187
#1 0x0000000000680b6d in Field_datetime::store (this=0x2ad2d000fb10, from=0x2ad2d0014fe0 "2014-02-25 11:20:42", len=19, cs=<value optimized out>) 
#2 0x00000000005488a4 in fill_record (thd=0xa602190, ptr=<value optimized out>, values=<value optimized out>, ignore_errors=<value optimized out>, triggers=0x0, event) 
#3 fill_record_n_invoke_before_triggers (thd=0xa602190, ptr=<value optimized out>, values=<value optimized out>, ignore_errors=<value optimized out>, triggers=0x0, event)

2) 5.6.16的相应堆栈为:

#0 my_datetime_packed_to_binary (nr=1842590951223066624, ptr=0x7fa88005dea1 "\231\222\062\265*", dec=0)
#1 0x00000000009155d4 in Field_datetimef::store_packed (this=0x7fa88005dec0, nr=1842590951223066624)
#2 0x000000000091553a in Field_datetimef::store_internal (this=0x7fa88005dec0, ltime=0x7fa8d42018f0, warnings=0x7fa8d4201920)
#3 0x000000000091191a in Field_temporal_with_date::store_internal_with_round (this=0x7fa88005dec0, ltime=0x7fa8d42018f0,warnings=0x7fa8d4201920) 
#4 0x00000000009109e9 in Field_temporal::store (this=0x7fa88005dec0, str=0x7fa8800052f8 "2014-02-25 11:20:42", len=19, cs=0x168e400)
#5 0x000000000065360b in Item::save_str_value_in_field (this=0x7fa880005310, field=0x7fa88005dec0, result=0x7fa880005320)
#6 0x0000000000663ef6 in Item_string::save_in_field (this=0x7fa880005310, field=0x7fa88005dec0, no_conversions=false)
#7 0x000000000077bbc6 in fill_record (thd=0x6f24020, ptr=0x7fa88005deb8, values=..., ignore_errors=false, bitmap=0x0)
#8 0x000000000077bcf7 in fill_record_n_invoke_before_triggers (thd=0x6f24020, ptr=0x7fa88005deb0, values=..., ignore_errors=false,triggers=0x0, event)

从面的两个堆栈可以看出,在构造插入数据的时候,调用的是Field的具体函数,根据不同类型调用的方法不同;5.5与5.6之间,datetime的数据类型不一致,当5.5升级到5.6时,其堆栈不变,原因是在表的FRM中,记录了表中列的数据类型,其中5.5中的数据类型为MYSQL_TYPE_DATETIME,5.6的数据类型为MYSQL_TYPE_DATETIME2,所以对于原表升级,不影响复制,但是对于新表中如果含有这三种数据类型的表,复制到备库就会出现问题,因为5.5中,没有MYSQL_TYPE_DATETIME2这种数据类型。

解决方法

对表的DML操作或DDL操作,都是依赖于表结构而言的,这也是为什么物理5.5升级到5.6后,对于原本含有datetime,time,timestamp这三种类型的表没有影响,但是对于新建的表就会有影响,原因就是对于产生Binlog的操作或存储引擎的操作的Field来源于FRM文件,所以,当在创建表的时候,如果5.5要使用5.6的Binlog,那我们对于DDL含有这三种数据类型的操作,使用5.5可以识别的数据类型:MYSQL_TYPE_DATETIME,而不是MYSQL_TYPE_DATETIME2,这样在MySQL内部的操作过程中就不会有问题,因此我们可以为MySQL添加一个参数,当参数打开时,创建datetime,time,timestamp的数据类型为兼容5.5的数据类型,否则为新的数据类型。

TimeStamp 与 Datetime 的区别

1. 值域不同

TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. DATETIME The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' TimeStamp带有时区信息,其中TimeStamp在存储时,将当前时间转化为UTC格式的时间,如北京时间,现在是2014-03-15 23:21:00,那么存储的会是2014-03-15 23:21:00 - 3600S;取数据的时候会加上当前时区时间。

2. 底层的存储结构不同

5.5 是以longlong类型存储的,而5.6 的格式如下:

timestamp:4+max(3); (变长,4-7个字节),没有Sign

datetime: 底层存储(变长,5-8个字节)

1 bit  sign            (used when on disk)
17 bits year*13+month   (year 0-9999, month 0-12)
5 bits day             (0-31)
5 bits hour            (0-23)
6 bits minute          (0-59)
6 bits second          (0-59)
24 bits microseconds    (0-999999)
Total: 64 bits = 8 bytes