# mdmsync

**Repository Path**: huang12321/mdmsync

## Basic Information

- **Project Name**: mdmsync
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2018-12-10
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README


# 部署
crate@172.18.18.30:/var/mdm_rfid/standardSync/sqlservertest-1.0-SNAPSHOT/bin

表名规则：
1. 库_表名   # 全部小写

## 同步rfid库(rfid_sap_size、rfid_sap_color)
sh sqlservertest onlineSync mdmlog
## 同步rfid刷卡表Bundlecardrecord
sh sqlservertest onlineSync rfid

## 同步ets库
sh sqlservertest onlineSync ets

## 同步mdmlog数据到redis
nohup sh sqlservertest RfidSyncMdmlogMain >/dev/null 2>&1 &

## 同步几张表的数据到crate
(
1. 查询生成离线文件
2. 生成待执行的shell脚本
3. 执行脚本
)
sh sqlservertest sync table1,table2,table3...

## 老曹说每天全量同步这几个表
Sap_color,sapcolorid
Sap_size,sapsizeid
Color,colorid
Size,sizeid
## 同步命令
sh sqlservertest onlineSync mdmlog
生成的shell文件在
/var/mdm_rfid/standardSync/shell/sqls.sh

## 每天比对rfid、ets刷卡表的数据
### 每天比对rfid刷卡表的数据
##### 3258=2018-12-03
select count(bundledate) count from Bundlecardrecord where bundledate=3263
select bundledate,count(bundledate) count from Bundlecardrecord where bundledate in (3258,3259) group by bundledate
select count(bundledate) count from rfid_bundlecardrecord where bundledate=3258

select count(*) count from rfid_bundlecardrecord where bundledate=3263;
select bundledate,count(*) count from rfid_bundlecardrecord group by bundledate order by bundledate desc limit 60;
cr> select bundledate,substr(begintime,0,10) begintime,count(bundledate) count from rfid_bundlecardrecord group by bundledate,substr(begintime,0,10) order by bundledate desc limit 60;
+------------+------------+--------+
| bundledate | begintime  |  count |
+------------+------------+--------+
| 3259       | 2018-12-04 |   9221 |
| 3258       | 2018-12-03 | 204915 |
| 3257       | 2018-12-02 |  93637 |
| 3256       | 2018-12-01 | 306089 |
| 3255       | 2018-11-30 | 460558 |
| 3254       | 2018-11-29 | 456923 |
| 3253       | 2018-11-28 | 463036 |
| 3252       | 2018-11-27 | 455211 |
| 3251       | 2018-11-26 | 437691 |
| 3250       | 2018-11-25 | 143647 |
+------------+------------+--------+
SELECT 10 rows in set (20.188 sec)

### 每天比对ets刷卡表的数据
// 查询很慢，而且查询一天的更慢
select count(*) from Vw_JJB where enddate>='2018-12-03 15:00:00' and enddate<='2018-12-03 15:59:59' and style_no like 'U02%' 
select count(*) from Vw_JJB where enddate>='2018-12-03 00:00:00' and enddate<='2018-12-03 23:59:59' and style_no like 'U02%' 

select count(*) from ets_tjjb_yn where enddate>='2018-12-03 00:00:00' and enddate<='2018-12-03 23:59:59' and style_no like 'U02%'
cr> select * from (select substr(enddate,0,10) enddate,count(*) c from ets_tjjb_yn where enddate BETWEEN '2018-12-01' AND '2018-12-06' group by substr(enddate,0,10) ) as t1 order by t1.enddate desc limit 30;
+------------+--------+
| enddate    |      c |
+------------+--------+
| 2018-12-03 | 447933 |
| 2018-12-02 |  10034 |
| 2018-12-01 | 407411 |
| 2018-11-30 | 466701 |
| 2018-11-29 | 478116 |
| 2018-11-28 | 454412 |
| 2018-11-27 | 511504 |
| 2018-11-26 | 473226 |
| 2018-11-25 |  18521 |
| 2018-11-24 | 432945 |
| 2018-11-23 | 486338 |
| 2018-11-22 | 523202 |
| 2018-11-21 | 469556 |
| 2018-11-20 | 472452 |
| 2018-11-19 | 441203 |
| 2018-11-18 |  14955 |
| 2018-11-17 | 401330 |
| 2018-11-16 | 454193 |
| 2018-11-15 | 455289 |
| 2018-11-14 | 472170 |
| 2018-11-13 | 470693 |
| 2018-11-12 | 436782 |
| 2018-11-11 |   4911 |
| 2018-11-10 | 402366 |
| 2018-11-09 | 462911 |
| 2018-11-08 | 478433 |
| 2018-11-07 | 449129 |
| 2018-11-06 | 470693 |
| 2018-11-05 | 427480 |
| 2018-11-04 |  16353 |
| 2018-11-03 | 392707 |
| 2018-11-02 | 425165 |
| 2018-11-01 | 412911 |
+------------+--------+
SELECT 33 rows in set (2.639 sec)

## rfid_failrecord 数量对比
select CONVERT(varchar(10), optime, 121) date,opdate,count(*) count from TQC_FailRecord where optime>='2018-11-01' and optime<='2018-12-06' group by CONVERT(varchar(10), optime, 121),opdate
select opdate,substr(optime,0,10) optime,count(*) count from rfid_failrecord group by opdate,substr(optime,0,10) order by substr(optime,0,10) desc limit 30;

select CONVERT(varchar(10), optime, 121) date,opdate,count(*) count from TQC_FlawRecord where optime>='2018-11-01' and optime<='2018-12-06' group by CONVERT(varchar(10), optime, 121),opdate
select opdate,substr(optime,0,10) optime,count(*) count from rfid_flawrecord group by opdate,substr(optime,0,10) order by substr(optime,0,10) desc limit 30;


## sql console
sh sqlservertest console ets|rfid

################ 待完成的任务 ##################
1. mdmlog写入crate迁移到此程序上
2. ets 刷卡表的自动同步(现在是手动)
3. sap 同步优化
4. redis监控：程序定时访问redis，把每个key的数量读取写到文件
5. 编写定时全量同步任务，每天跑
6. 目前ets自动同步刷卡表有bug，如果数据库连接断开，则程序抛异常且下次不自动同步了！需排查
7. 邮件或者文本显示出每天同步了多少数据，我们库的数据和对方库的每天数据进行比对
9. 每天自动同步、自动比对rfid,ets刷卡表的数据是否和他们的数据库一致
10. 排查mdmlog是否导致实时同步数据不一致
    目前排查到的情况是，rfid刷卡表的数据每天都变，而且今天之前的数据也经常变化(增多)，可能是增量没
    有同不好导致的！目前得从mdmlog增量同步上寻找问题的原因
11. 帮张瑞查看下sql：select * from rfid_orderform where orderformno='23107965' limit 1;
12. 

########同步数据服务#############
1. 每日全量同步服务,0-8点执行
    同步的表：rfid部分表、ets部分表
    sh sqlservertest onlineSync mdmlog
    cd /home/crate/crate-3.2.0-201809160202-a729590/bin
    sh sqls.sh

2. rfid刷卡表数据一致性检测服务,每10分钟检测一次数据是否一致
    不一致则开启增量或者某一天的全量同步,目的是尽可能的一致
    sh rfidSyncCheck.sh
    cat rfidSyncCheck.log |grep detail
    
3. ets刷卡表同步,每天0-8点同步昨天的
    (目前程序同步完了,要人工执行入库,得改成自动执行入库脚本)