非零时区下-Mysql增量同步数据到Elasticsearch的一些问题

Ruby exception occurred: undefined method `time' for nil:NilClass
Exception when executing JDBC query {:exception=>"Java::OrgJodaTime::IllegalInstantException: Illegal instant due to time zone offset transition (daylight savings time 'gap'): 1947-04-15T00:00:00.000 (Asia/Shanghai)"}

之前就发现过这两个问题，只不过那时候也是刚接触elasticsearch，所以采取的方法是不理会+改数据的形式。

看下我原本的logstash配置文件：

input {
  jdbc {  
  #根据type的不同输出到es的不同索引
  type => "hk_table"
    jdbc_driver_library => "D:/mysql-connector-java-8.0.23/mysql-connector-java-8.0.23.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/database"
    jdbc_user => "root"
    jdbc_password => "root"
    jdbc_paging_enabled => true
    jdbc_page_size => "50000"
    tracking_column => "unix_ts_in_secs"
  # 是否记录上次执行结果，true表示会将上次执行结果的tracking_column字段的值保存到last_run_metadata_path指定的文件中；
  record_last_run => "true"
  # record_last_run上次数据存放位置；
  last_run_metadata_path => "D:/.logstash_jdbc_last_run"
  clean_run => "false"
  # 需要记录查询结果某字段的值时，此字段为true，否则默认tracking_column为timestamp的值；
    use_column_value => true
    tracking_column_type => "numeric"
    schedule => "*/10 * * * * *"
  statement => "SELECT *, UNIX_TIMESTAMP(up_date) AS unix_ts_in_secs FROM test WHERE (UNIX_TIMESTAMP(up_date) > :sql_last_value AND up_date < NOW()) ORDER BY up_date ASC"
  }
  
}
filter {
  ruby {
    code => "
      event.set('Participation_date', event.get('Participation_date').time.localtime + 8*60*60 + 5*60+43 + 60*60)
      event.set('Retirement_date', event.get('Retirement_date').time.localtime + 8*60*60 + 5*60+43 + 60*60)
      " 
    }
  mutate {
    copy => { "number" => "[@metadata][_id]"}
    remove_field => ["unix_ts_in_secs"]
  #用mutate插件先转换为string类型,gsub只处理string类型的数据，在用正则匹配，最终得到想要的日期
  convert => {
    "Participation_date"=> "string"
    "Retirement_date"=> "string"
  }
  gsub => [
    "Participation_date", "T([\S\s]*?)Z", "",
    "Retirement_date", "T([\S\s]*?)Z", ""
  ] 
  }
  
}
output {
  stdout { codec =>  "rubydebug"}
  if [type] == "hk_table" {
  elasticsearch {
      index => "worldtest"
      document_id => "hk-%{[@metadata][_id]}"
  }
  }
}

JDBC里面的配置，之前都在记录MYSQL同步数据至ES的测试历程写过，

先说下第一种异常的原因，由于mysql数据库中的Participation_date（类型为date）为空，因此在ruby中event无法获取到时间对象，调用time函数报错，该错误不影响logstash的后续同步；解决方法如下：

在jdbc代码块中，修改statement如下，添加日期对象获取

SELECT *,Participation_date as date_ico,Retirement_date as date_iso,
 UNIX_TIMESTAMP(up_date) AS unix_ts_in_secs 
 FROM test WHERE (UNIX_TIMESTAMP(up_date) > :sql_last_value AND up_date < NOW()) ORDER BY up_date ASC

原来的ruby代码注释掉，添加判空条件，修改如下：

if [date_ico]{
  ruby {
    code => "
      event.set('Participation_date', event.get('Participation_date').time.localtime + 8*60*60 + 5*60+43 + 60*60)
      " 
    }
  }
  if [date_iso]{
  ruby {
    code => "
      event.set('Retirement_date', event.get('Retirement_date').time.localtime + 8*60*60 + 5*60+43 + 60*60)
      " 
    }
  }

这种不修改也无所谓，因为不会中断logstash的同步数据。

第二种夏令时间隙导致的时区转换就令人难受了，因为它会导致logstash的同步数据数据中断，也就是说，mysql增量同步会始终卡在这一条数据上，后续不会再更新到elasticsearch了。查了很多文档，基本都是建议将数据库里面的时区设置成零时区。

首先查看一下数据库的时区，时区跟随系统，windows电脑系统时间为东八区

show global variables like '%time_zone%';

我原本以为将time_zone设置为零时区即可，毕竟system_time_zone是一个readonly变量，

set global time_zone = '+00:00';
FLUSH PRIVILEGES;
show global variables like '%time_zone%';

再次测试logstash同步，可以看到仍然报错，而且诡异的是结尾还带了(Asia/Shanghai）的提示

于是干脆把windows系统的时间也改成了零时区，重启mariadb（后来的测试中发现无需重启！！更正记录）。

再次重启logstash，顺利同步

利用搜索引擎也查了很多资料，关于mysql的system_time_zone确实无法更改，因而想要完整数据的同步，就是需要更改系统的时区为零时区。

实战，centos完整版测试：

SELECT VERSION();

报错如下：

查看时区列表：

timedatectl list-timezones

修改服务器时区：

timedatectl set-timezone UTC
date
==>Tue Sep 13 13:38:38 UTC 2022

重启数据库(可使用ps uax|grep mariadb 查看数据库PID是否更换)，再次更正，无需重启数据库！

systemctl restart mysql
show global variables like '%time_zone%';

成功同步至elasticsearch

修改时区会有些麻烦，毕竟牵涉到web服务器的日志时间，crontab中定时运行的程序等等，也可以先修改成对应的UTC时区，再趁数据库不注意，偷偷改回东八区的时区吧，只要不重启mysql数据库，还是没啥问题的。

timedatectl set-timezone Asia/Shanghai

YOLO813

非零时区下-Mysql增量同步数据到Elasticsearch的一些问题