这几天在研究下MYSQL CURRENT_TIMESTAMP()函数 返回到底是什么类型? MYSQL个每个表有两个字段,分别是CREATE_TIME和UPDATE_TIME.
每个默认值是DEFAULT CURRENT_TIMESTAMP() 和
UPDATE ON CURRENT_TIMESTAMP(). 因为担心是TIMESTAMP类型.
从官方网站来说是NOW()函数的代名词! 晕倒! 官方没有说NOW()函数是返回什么类型,或者基于什么时间函数取值!
只好查看源码了, 因为公司业务使用阿里云RDS,而RDS使用的是5.75版本.
并找啊找, 就是找不到函数的实现.
从盖总一篇文章得到启发,盖总文说 SYSDATE()函数是取SQL运行点的时间值.
而NOW()函数是取SQL运行开始时间的值. 其实这说法百度都有介绍.
盖总这样的DBA老江湖,自然不会那么蜻蜓点水. 他贴了源码出来.
一看源码 居然是 ITEM_FUNC_SYSDATE_LOCAL
这跟哪,哪儿呢?
它是个类!
其中有个类函数 获得函数名, 直接返回字符常量 "SYSDATE".
该源码文件里,其他函数也都是 包括NOW函数.
也就是说MYSQL 日期函数都是用类去实现的.
我看了下构造函数,然后套了一层构造函数 ITEM_FUNC_DATETIME
再套了一层ITEM_FUNC_XXX 类.
看了GET_DATE成员函数,好像也看不出来啥.
找啊找啊 结果放弃了.
朋友提醒 ,可以修改系统时间,来看NOW()具体返回是啥!
今天就在CENTOS 7 把时间一改!
希望满满地 SERVICE MYSQLD START
结果报PID 错误
不该啊 跑得好好的嘛,
换成 MYSQLDBA 用户来启动
结果 你猜?
难道还是 什么经典PID 端口 用户 权限..... ???
统统不追究, 因为节前才装好的, 直接看ERROR.LOG
the time has got past 2038 we need to shut this server down
不支持2038年啊! 那我该咋整?
我不就是想看下NOW()函数具体返回啥吗!
好吧 我先让你去启动起来,然后我再改成2051年.
结果 你猜!
对 对 那有那么幸运啊, 步步都是坑.
用TOAD FREE 查询SQL 就报错 什么服务断连.
然后PS -EF | GREP MYSQLD 找不着了
再看ERROR.LOG 居然自动关闭 也是报什么2038年
好吧 我把出错的信息 百度一下 有一篇文说 MYSQL不能超过2038年 并贴了源码片段.
源码好高深哦! 看不懂 直接找就是了
mysql 5.7.35
sql_parse.cc 1218:
bool dispatch_command(THD *thd, const COM_DATA *com_data,
enum enum_server_command command)
{
thd->set_time();
if (thd->is_valid_time() == false)
{
/*
If the time has gone past 2038 we need to shutdown the server. But
there is possibility of getting invalid time value on some platforms.
For example, gettimeofday() might return incorrect value on solaris
platform. Hence validating the current time with 5 iterations before
initiating the normal server shutdown process because of time getting
past 2038.
*/
const int max_tries= 5;
sql_print_warning("Current time has got past year 2038. Validating current "
"time with %d iterations before initiating the normal "
"server shutdown process.", max_tries);
int tries= 0;
while (++tries <= max_tries)
{
thd->set_time();
if (thd->is_valid_time() == true)
{
sql_print_warning("Iteration %d: Obtained valid current time from "
"system", tries);
break;
}
sql_print_warning("Iteration %d: Current time obtained from system is "
"greater than 2038", tries);
}
if (tries > max_tries)
{
/*
If the time has got past 2038 we need to shut this server down.
We do this by making sure every command is a shutdown and we
have enough privileges to shut the server down
TODO: remove this when we have full 64 bit my_time_t support
*/
sql_print_error("This MySQL server doesn't support dates later than 2038");
ulong master_access= thd->security_context()->master_access();
thd->security_context()->set_master_access(master_access | SHUTDOWN_ACL);
command= COM_SHUTDOWN;
}
}
这段代码是入口,每个线程先设置时间,然后然后校验时间,
等于TRUE 就打印一段信息 然后BREAK;
接下来是打印错误信息,
然后再重次 5次 最终打印我们看到的真实报错信息.
最后就关掉.
这里有两个关键点, 1 是获取时间, 2 是校验时间
sql_class.h 3298:
inline bool is_valid_time()
{
return (IS_TIME_T_VALID_FOR_TIMESTAMP(start_time.tv_sec));
}
这个IS_TIME_T_VAILD_FOR_TIMESTAMP不是函数,是个宏
my_time.h 47:
#ifndef _my_time_h_
#define _my_time_h_
#include "my_global.h"
#include "mysql_time.h"
typedef long my_time_t;
typedef enum enum_mysql_timestamp_type timestamp_type;
#define MY_TIME_T_MAX LONG_MAX
#define MY_TIME_T_MIN LONG_MIN
/* Time handling defaults */
#define TIMESTAMP_MAX_YEAR 2038
#define TIMESTAMP_MIN_YEAR (1900 + YY_PART_YEAR - 1)
#define TIMESTAMP_MAX_VALUE INT_MAX32
#define TIMESTAMP_MIN_VALUE 1
/* two-digit years < this are 20..; >= this are 19.. */
#define YY_PART_YEAR 70
/*
check for valid times only if the range of time_t is greater than
the range of my_time_t
*/
#if SIZEOF_TIME_T > 4
# define IS_TIME_T_VALID_FOR_TIMESTAMP(x) \
((x) <= TIMESTAMP_MAX_VALUE && \
(x) >= TIMESTAMP_MIN_VALUE)
#else
# define IS_TIME_T_VALID_FOR_TIMESTAMP(x) \
((x) >= TIMESTAMP_MIN_VALUE)
#endif
这段宏代码一时难看明白,其实这才是重点.
我怀疑是SET_TIME()函数返回 时间戳有问题
sql_class.h 3262:
inline void set_time()
{
ulonglong start_utime, utime_after_lock;
start_utime= utime_after_lock= my_micro_time();
if (user_time.tv_sec || user_time.tv_usec)
{
start_time= user_time;
}
else
my_micro_time_to_timeval(start_utime, &start_time);
#ifdef HAVE_PSI_THREAD_INTERFACE
PSI_THREAD_CALL(set_thread_start_time)(start_time.tv_sec);
#endif
}
这个SET_TIME 调用了两个函数,重点关注变量是START_TIME
sql_class.h 1451:
static inline void
my_micro_time_to_timeval(ulonglong micro_time, struct timeval *tm)
{
tm->tv_sec= (long) (micro_time / 1000000);
tm->tv_usec= (long) (micro_time % 1000000);
}
另外个函数 调用了系统库的TIME
#if HAVE_SYS_TIME_H
#include <sys/time.h>
#endif
my_getsystime.c 149:
ulonglong my_micro_time()
{
#ifdef _WIN32
ulonglong newtime;
my_get_system_time_as_file_time((FILETIME*)&newtime);
newtime-= OFFSET_TO_EPOCH;
return (newtime/10);
#else
ulonglong newtime;
struct timeval t;
/*
The following loop is here because gettimeofday may fail on some systems
*/
while (gettimeofday(&t, NULL) != 0)
{}
newtime= (ulonglong)t.tv_sec * 1000000 + t.tv_usec;
return newtime;
#endif
}
//===========================================================================================
my_global.h 516:
typedef unsigned long long int ulonglong;
//===========================================================================================
sys/time.h 23:
#if defined __need_timeval || defined __USE_GNU
# ifndef _STRUCT_TIMEVAL
# define _STRUCT_TIMEVAL 1
# include <bits/types.h>
/* A time value that is accurate to the nearest
microsecond but also has a range of years. */
struct timeval
{
__time_t tv_sec; /* Seconds. */
__suseconds_t tv_usec; /* Microseconds. */
};
# endif /* struct timeval */
#endif
//===========================================================================================
__STD_TYPE __TIME_T_TYPE __time_t; /* Seconds since the Epoch. */
#define __TIME_T_TYPE __SYSCALL_ULONG_TYPE
# define __SYSCALL_ULONG_TYPE __ULONGWORD_TYPE
#define __ULONGWORD_TYPE unsigned long int
函数开头的 WIN32 可以跳过 看下面的
while (gettimeofday(&t, NULL) != 0)
返回类型是 ulonglong
而 ulonglong 是 typedef unsigned long long int ulonglong;
已经是很大大的类型了, 2038 是32位整型存不下而已, 超过了21亿秒!
再看__time_t类型定义
跳啊跳 最后是 unsigned long int 型.
这就比较容易理解,C语言就3个整型.
short int
int
long int
简写
short
int
long
Linux long 是8个字节 64位. win64 依旧是4个字节32位. 应该说LINUX下支持2038年后.
为此我专门写个C程序来验证下
#include <stdio.h>
#include <sys/time.h>
typedef unsigned long long int ulonglong; /* ulong or unsigned long long */
typedef long long int longlong;
typedef longlong int64;
typedef ulonglong uint64;
struct timeval start_time;
struct timeval user_time;
ulonglong thr_create_utime;
ulonglong start_utime, utime_after_lock;
static inline void
my_micro_time_to_timeval(ulonglong micro_time, struct timeval *tm)
{
tm->tv_sec= (long) (micro_time / 1000000);
tm->tv_usec= (long) (micro_time % 1000000);
}
ulonglong My_micro_time()
//unsigned long int My_micro_time()
{
ulonglong newtime=0;
struct timeval t;
/*
The following loop is here because gettimeofday may fail on some systems
*/
while (gettimeofday(&t, NULL) != 0) {}
newtime= (ulonglong)t.tv_sec * 1000000 + t.tv_usec;
printf("tv_sec : %ld\n",t.tv_sec);
printf("tv_usec : %ld\n",t.tv_usec);
printf("newtime : %ld\n",newtime);
return newtime;
}
time_t set_time()
{
start_utime= utime_after_lock= My_micro_time();
if (user_time.tv_sec || user_time.tv_usec)
{
start_time= user_time;
printf("start_time.tv_sec : %ld\n",start_time.tv_sec);
}
else
{
my_micro_time_to_timeval(start_utime, &start_time);
printf("my_micro_time_to_timeval : %ld\n",start_time.tv_sec);
}
return start_time.tv_sec;
}
int main()
{
time_t s =set_time();
printf("Mytime is : %ld\n", s);
}
第一个程序是我以前写的时间封装的C,第二个就是我们验证的程序.
[root@CENTOS7GUI ~]# ./MyNow
UTC TimeStamp : 2580456226
UTC Datetime : 2051-10-9 9:23:46
Local Datetime Str : 2051-10-09 17:23:46
The Millisecond.Microsecond.NanoSecond : 667.126.583
The today on year number : 282
How today on week number : 1
[root@CENTOS7GUI ~]# ./mytime
tv_sec : 2580456231
tv_usec : 879856
newtime : 2580456231879856
my_micro_time_to_timeval : 2580456231
Mytime is : 2580456231
2051年 的TV_SEC 都是2580456XXX.
说明SYS/TIME.H 都支持2038年的.
那就要回到MYSQL自己身上了
关键是 SIZEOF_TIME_T > 4 表达式
my_time.h 47:
#ifndef _my_time_h_
#define _my_time_h_
#include "my_global.h"
#include "mysql_time.h"
typedef long my_time_t;
typedef enum enum_mysql_timestamp_type timestamp_type;
#define MY_TIME_T_MAX LONG_MAX
#define MY_TIME_T_MIN LONG_MIN
/* Time handling defaults */
#define TIMESTAMP_MAX_YEAR 2038
#define TIMESTAMP_MIN_YEAR (1900 + YY_PART_YEAR - 1)
#define TIMESTAMP_MAX_VALUE INT_MAX32
#define TIMESTAMP_MIN_VALUE 1
/* two-digit years < this are 20..; >= this are 19.. */
#define YY_PART_YEAR 70
/*
check for valid times only if the range of time_t is greater than
the range of my_time_t
*/
#if SIZEOF_TIME_T > 4
# define IS_TIME_T_VALID_FOR_TIMESTAMP(x) \
((x) <= TIMESTAMP_MAX_VALUE && \
(x) >= TIMESTAMP_MIN_VALUE)
#else
# define IS_TIME_T_VALID_FOR_TIMESTAMP(x) \
((x) >= TIMESTAMP_MIN_VALUE)
#endif
如果为TRUE 就 检验X 是否在TIMESTAMP_MAX_VALUE && TIME_STAMP_MIN_VALUE 之间
否则 判断 X 是否大于 最新值.
SIZEOF_TIME_T 具体什么值 没找到! 大意就是判断各个平台下 TIME_T类型占多少字节.
如果 大于4个字节 就检验当前系统时间是否在 范围之内. 根据前面最上层
在表示TRUE ,不在就返回FLASE,报错并关闭系统.
这就是逻辑BUG, 大于4个字节的平台就能保存超过2038年的秒数,反而小于或等于4个字节的平台才要检验是否在范围之内.
所以它整个逻辑反.
#define TIMESTAMP_MAX_VALUE INT_MAX32 要么改 成 INT_MAX64
最佳是#if SIZEOF_TIME_T > 4 改成 #if SIZEOF_TIME_T <= 4
那MYSQL 8.0.22 是否也有呢?
很不行的是有,不过人家没有逻辑BUG,只是还是限定了2038!
sql_parse.cc 1510:
if (thd->killed == THD::KILL_QUERY) thd->killed = THD::NOT_KILLED;
thd->set_time();
if (is_time_t_valid_for_timestamp(thd->query_start_in_secs()) == false) {
/*
If the time has gone past 2038 we need to shutdown the server. But
there is possibility of getting invalid time value on some platforms.
For example, gettimeofday() might return incorrect value on solaris
platform. Hence validating the current time with 5 iterations before
initiating the normal server shutdown process because of time getting
past 2038.
*/
const int max_tries = 5;
LogErr(WARNING_LEVEL, ER_CONFIRMING_THE_FUTURE, max_tries);
int tries = 0;
while (++tries <= max_tries) {
thd->set_time();
if (is_time_t_valid_for_timestamp(thd->query_start_in_secs()) == true) {
LogErr(WARNING_LEVEL, ER_BACK_IN_TIME, tries);
break;
}
LogErr(WARNING_LEVEL, ER_FUTURE_DATE, tries);
}
if (tries > max_tries) {
/*
If the time has got past 2038 we need to shut this server down
We do this by making sure every command is a shutdown and we
have enough privileges to shut the server down
TODO: remove this when we have full 64 bit my_time_t support
*/
LogErr(ERROR_LEVEL, ER_UNSUPPORTED_DATE);
ulong master_access = thd->security_context()->master_access();
thd->security_context()->set_master_access(master_access | SHUTDOWN_ACL);
error = true;
kill_mysql();
}
}
thd->set_query_id(next_query_id());
thd->reset_rewritten_query();
thd_manager->inc_thread_running();
这个代码写得非常明了,不亏是ORACLE的杰作! 没有那么烧脑!
检验不合格就重试五次,其中有一次合格就跳出去. 否则次数一到就安全退出线程.
my_time.h 198:
/**
Check for valid times only if the range of time_t is greater than
the range of my_time_t. Which is almost always the case and even time_t
does have the same range, the compiler will optimize away
the unnecessary test (checked with compiler explorer).
*/
inline bool is_time_t_valid_for_timestamp(time_t x) {
return x <= TIMESTAMP_MAX_VALUE && x >= TIMESTAMP_MIN_VALUE;
}
检验函数是个内联函数,返回一个表达式计算结果.
这里TIMESTAMP_MAX_VALUE的具体值是多少呢?
my_time.h 57:
/**
Portable time_t replacement.
Should be signed and hold seconds for 1902 -- 2038-01-19 range
i.e at least a 32bit variable
Using the system built in time_t is not an option as
we rely on the above requirements in the time functions
*/
using my_time_t = long int;
constexpr const my_time_t MY_TIME_T_MAX = std::numeric_limits<my_time_t>::max();
constexpr const my_time_t MY_TIME_T_MIN = std::numeric_limits<my_time_t>::min();
/** Time handling defaults */
constexpr const int TIMESTAMP_MAX_YEAR = 2038;
/** Two-digit years < this are 20XX; >= this are 19XX */
constexpr const int YY_PART_YEAR = 70;
constexpr const int TIMESTAMP_MIN_YEAR = (1900 + YY_PART_YEAR - 1);
constexpr const int TIMESTAMP_MAX_VALUE =
std::numeric_limits<std::int32_t>::max();
constexpr const int TIMESTAMP_MIN_VALUE = 1;
MAX_VALUE取值是C++标准库里的INT32_T类型最大值 是多少呢?
哎 就是INT 最大值啊
写个简单C++
#include <iostream>
/* run this program using the console pauser or add your own getch, system("pause") or input loop */
#include <cstddef> // std::size_t
#include <cstdint> // std::int32_t
#include <limits> // std::numeric_limits
int main(int argc, char** argv)
{
int TIMESTAMP_MAX_VALUE = std::numeric_limits<std::int32_t>::max();
printf("int32_t.max() is : %d\n",TIMESTAMP_MAX_VALUE);
return 0;
}
int32_t.max() is : 2147483647
--------------------------------
Process exited after 0.01219 seconds with return value 0
请按任意键继续. . .
看看其他整形类型的最大值
这是WIN64平台跑的
#include <iostream>
/* run this program using the console pauser or add your own getch, system("pause") or input loop */
#include <cstddef> // std::size_t
#include <cstdint> // std::int32_t
#include <limits> // std::numeric_limits
int main(int argc, char** argv)
{
int TIMESTAMP_MAX_VALUE = std::numeric_limits<std::int32_t>::max();
unsigned int TIMESTAMP_MAX_VALUE2 = std::numeric_limits<unsigned int>::max();
long TIMESTAMP_MAX_VALUE3 = std::numeric_limits<long>::max();
unsigned long TIMESTAMP_MAX_VALUE4 = std::numeric_limits<unsigned long>::max();
long long TIMESTAMP_MAX_VALUE5 = std::numeric_limits<long long>::max();
unsigned long long TIMESTAMP_MAX_VALUE6 = std::numeric_limits<unsigned long long>::max();
std::int64_t TIMESTAMP_MAX_VALUE7 = std::numeric_limits<std::int64_t>::max();
std::cout <<"int32_t.max() is : " <<std::dec <<TIMESTAMP_MAX_VALUE<< '\n';
std::cout <<"unsigned int.max() is : " <<std::dec <<TIMESTAMP_MAX_VALUE2<< '\n';
std::cout <<"long.max() is : " <<std::dec <<TIMESTAMP_MAX_VALUE3<< '\n';
std::cout <<"unsigned long.max() is : " <<std::dec <<TIMESTAMP_MAX_VALUE4<< '\n';
std::cout <<"long long.max() is : " <<std::dec <<TIMESTAMP_MAX_VALUE5<< '\n';
std::cout <<"unsigned long long.max() is : "<<std::dec <<TIMESTAMP_MAX_VALUE6<< '\n';
std::cout <<"int64_t.max() is : " <<std::dec <<TIMESTAMP_MAX_VALUE7<< '\n';
return 0;
}
结果如下
int32_t.max() is : 2147483647
unsigned int.max() is : 4294967295
long.max() is : 2147483647
unsigned long.max() is : 4294967295
long long.max() is : 9223372036854775807
unsigned long long.max() is : 18446744073709551615
int64_t.max() is : 92,2337,2036,8547,75807
--------------------------------
Process exited after 0.19 seconds with return value 0
请按任意键继续. . .
这里看出 WIN64平台下 INT LONG 依旧是4个字节.LONG LONG 才是8字节的64位.标准STD INT64_T 可以存储92亿亿个秒数. 那有是多少年呢?
好了不乱扯下去了. 回头 我们只要把
constexpr const int TIMESTAMP_MAX_VALUE =
std::numeric_limits<std::int32_t>::max();
改成
constexpr const int TIMESTAMP_MAX_VALUE =
std::numeric_limits<std::int64_t>::max();
它这句话
TODO: remove this when we have full 64 bit my_time_t support
而MY_TIME_T 在全源码文件里只有五处定义 分别是两种
typedef long my_time_t;
using my_time_t = long int;
这样改成LONG LONG 类型 不过涉及my_time_t 相关代码 工作量估计大多了
