问题描述

现象

5.6 和 5.7 时区设置相同，select now()也显示当前时间

5.7 error log 中时间和当前时间差8小时

screenshot.png

问题分析

5.6 写 error log 函数如下

取时间的函数是localtime_r(&skr, &tm_tmp)

日志中时间和系统时区相同

  1.     static void print_buffer_to_file(enum loglevel level, const char *buffer,                                                                                                                                       
  2.                                      size_t length)
  3.     {   
  4.       time_t skr; 
  5.       struct tm tm_tmp;
  6.       struct tm *start;
  7.       DBUG_ENTER("print_buffer_to_file");
  8.       DBUG_PRINT("enter",("buffer: %s", buffer));
  10.       mysql_mutex_lock(&LOCK_error_log);
  12.       skr= my_time(0);
  13.       localtime_r(&skr, &tm_tmp);
  14.       start=&tm_tmp;
  16.       fprintf(stderr, "%d-%02d-%02d %02d:%02d:%02d %lu [%s] %.*s\n",
  17.               start->tm_year + 1900,
  18.               start->tm_mon + 1,  
  19.               start->tm_mday,
  20.               start->tm_hour,
  21.               start->tm_min,
  22.               start->tm_sec,
  23.               current_pid,
  24.               (level == ERROR_LEVEL ? "ERROR" : level == WARNING_LEVEL ?
  25.                "Warning" : "Note"),
  26.               (int) length, buffer);
  28.       fflush(stderr);
  30.       mysql_mutex_unlock(&LOCK_error_log);
  31.       DBUG_VOID_RETURN;
  32.     }

5.7 写 error log 函数如下

取时间的函数是 make_iso8601_timestamp(my_timestamp)

  1.     static void print_buffer_to_file(enum loglevel level, const char *buffer,
  2.                                      size_t length)
  3.     {
  4.       DBUG_ENTER("print_buffer_to_file");
  5.       DBUG_PRINT("enter",("buffer: %s", buffer));
  7.       char my_timestamp[iso8601_size];
  9.       my_thread_id thread_id= 0;
  11.       /*
  12.         If the thread system is up and running and we're in a connection,
  13.         add the connection ID to the log-line, otherwise 0.
  14.       */
  15.       if (THR_THD_initialized && (current_thd != NULL))
  16.         thread_id= current_thd->thread_id();
  18.       make_iso8601_timestamp(my_timestamp);
  20.       /*
  21.         This must work even if the mutex has not been initialized yet.
  22.         At that point we should still be single threaded so that it is
  23.         safe to write without mutex.
  24.       */
  25.       if (error_log_initialized)
  26.         mysql_mutex_lock(&LOCK_error_log);
  28.       if (error_log_buffering)
  29.       {
  30.         // Logfile not open yet, buffer messages for now.
  31.         if (buffered_messages == NULL)
  32.           buffered_messages= new (std::nothrow) std::string();
  33.         std::ostringstream s;
  34.         s << my_timestamp << " " << thread_id;
  35.         if (level == ERROR_LEVEL)
  36.           s << " [ERROR] ";
  37.         else if (level == WARNING_LEVEL)
  38.           s << " [Warning] ";
  39.         else
  40.           s << " [Note] ";
  41.         s << buffer << std::endl;
  42.         buffered_messages->append(s.str());
  43.       }
  44.       else
  45.       {
  46.         fprintf(stderr, "%s %u [%s] %.*s\n",
  47.                 my_timestamp,
  48.                 thread_id,
  49.                 (level == ERROR_LEVEL ? "ERROR" : level == WARNING_LEVEL ?
  50.                  "Warning" : "Note"),
  51.                 (int) length, buffer);
  53.         fflush(stderr);
  54.       }
  56.       if (error_log_initialized)
  57.         mysql_mutex_unlock(&LOCK_error_log);
  58.       DBUG_VOID_RETURN;
  59.     }

make_iso8601_timestamp 中有代码段如下

参数 opt_log_timestamps 控制时间

  1.  if (opt_log_timestamps == 0)
  2.     gmtime_r(&seconds, &my_tm);
  3.   else
  4.   {   
  5.     localtime_r(&seconds, &my_tm);

opt_log_timestamps 对应 sys_vars.cc 中的 log_timestamps

取值 const char *timestamp_type_names[]= {“UTC”, “SYSTEM”, NullS};

log_timestamps = 0 时，日志中是 UTC 时区

log_timestamps = 1 时，日志中是 SYSTEM 时区

5.7 默认 log_timestamps = 0

5.7 error log 使用系统时区

set global log_timestamps = 1;