mysql> desc fireway.sm_member_realtime_statistics;
+------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| member_id | varchar(64) | YES | | NULL | |
| job_id | varchar(64) | YES | | NULL | |
| member_name | varchar(100) | YES | | NULL | |
| admin_id | varchar(64) | YES | | NULL | |
| admin_name | varchar(64) | YES | | NULL | |
| apply_count | bigint(20) | YES | | NULL | |
| pass_count | bigint(20) | YES | | NULL | |
| reject_count | bigint(20) | YES | | NULL | |
| trade_amount | decimal(18,5) | YES | | NULL | |
| statis_date | date | YES | | NULL | |
| last_time | timestamp | YES | | NULL | |
| create_time | timestamp | YES | | NULL | |
| hui_apply_count | bigint(20) | YES | | NULL | |
| hui_pass_count | bigint(20) | YES | | NULL | |
| hui_reject_count | bigint(20) | YES | | NULL | |
| hui_trade_amount | decimal(18,5) | YES | | NULL | |
| pos_apply_count | bigint(20) | YES | | NULL | |
| pos_pass_count | bigint(20) | YES | | NULL | |
| pos_reject_count | bigint(20) | YES | | NULL | |
| pos_trade_amount | decimal(18,5) | YES | | NULL | |
+------------------+---------------+------+-----+---------+----------------+
insert into `fireway`.`sm_member_realtime_statistics`
set
id=2,
member_id='HJB2000000000001442105',
job_id='HHXJD' ,
member_name='惠花现金贷' ,
admin_id='HHXJD' ,
admin_name='惠花现金贷' ,
apply_count=22,
pass_count=14,
reject_count=0,
trade_amount=103800.00000,
statis_date='2017:07:12',
last_time=from_unixtime('1499788800'),
create_time=from_unixtime('1479957551'),
hui_apply_count=22,
hui_pass_count=14,
hui_reject_count=0,
hui_trade_amount=103800.00000,
pos_apply_count=0,
pos_pass_count=0,
pos_reject_count=0,
pos_trade_amount=0.00000;
# 注意row格式记录的timestamp格式是unixtime，需要转换以下
```shell
mysql> select from_unixtime('1499788800');
+-----------------------------+
| from_unixtime('1499788800') |
+-----------------------------+
| 2017-07-12 00:00:00.000000 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp('2017-07-12 00:00:00.000000');
+----------------------------------------------+
| unix_timestamp('2017-07-12 00:00:00.000000') |
+----------------------------------------------+
| 1499788800.000000 |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into `fireway`.`sm_member_realtime_statistics`
-> set
-> id=2,
-> member_id='HJB2000000000001442105',
-> job_id='HHXJD' ,
-> member_name='惠花现金贷' ,
-> admin_id='HHXJD' ,
-> admin_name='惠花现金贷' ,
-> apply_count=22,
-> pass_count=14,
-> reject_count=0,
-> trade_amount=103800.00000,
-> statis_date='2017:07:12',
-> last_time=from_unixtime('1499788800'),
-> create_time=from_unixtime('1479957551'),
-> hui_apply_count=22,
-> hui_pass_count=14,
-> hui_reject_count=0,
-> hui_trade_amount=103800.00000,
-> pos_apply_count=0,
-> pos_pass_count=0,
-> pos_reject_count=0,
-> pos_trade_amount=0.00000;
Query OK, 1 row affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 119.90.40.222
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.005170
Read_Master_Log_Pos: 588938705
Relay_Log_File: hjkj-mysql-relay-bin.000184
Relay_Log_Pos: 76867734
Relay_Master_Log_File: master-bin.005138
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: fireway.%,finance.%
Replicate_Wild_Ignore_Table: mysql.%,edw.%,etl2020.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 345303319
Relay_Log_Space: 35094719276
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 188042
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 5cc28f3c-6a8d-11e4-beff-00163e5563d9
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for Slave Worker to release partition
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 5cc28f3c-6a8d-11e4-beff-00163e5563d9:491028361-493119019
Executed_Gtid_Set: 5cc28f3c-6a8d-11e4-beff-00163e5563d9:1-491641136:491641180:491641188:491641380,
bcba2d0d-6797-11e7-bbb3-00163e00013d:1-280
Auto_Position: 1
1 row in set (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 119.90.40.222
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.005170
Read_Master_Log_Pos: 731174010
Relay_Log_File: hjkj-mysql-relay-bin.000184
Relay_Log_Pos: 93267434
Relay_Master_Log_File: master-bin.005138
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: fireway.%,finance.%
Replicate_Wild_Ignore_Table: mysql.%,edw.%,etl2020.%
Last_Errno: 1032
Last_Error: Worker 2 failed executing transaction '5cc28f3c-6a8d-11e4-beff-00163e5563d9:491645391' at master log master-bin.005138, end_log_pos 361797106; Could not execute Update_rows event on table fireway.sm_member_realtime_statistics; Can't find record in 'sm_member_realtime_statistics', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.005138, end_log_pos 361797106
Skip_Counter: 0
Exec_Master_Log_Pos: 361703019
Relay_Log_Space: 35236954946
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Worker 2 failed executing transaction '5cc28f3c-6a8d-11e4-beff-00163e5563d9:491645391' at master log master-bin.005138, end_log_pos 361797106; Could not execute Update_rows event on table fireway.sm_member_realtime_statistics; Can't find record in 'sm_member_realtime_statistics', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.005138, end_log_pos 361797106
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 5cc28f3c-6a8d-11e4-beff-00163e5563d9
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 170714 14:25:41
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 5cc28f3c-6a8d-11e4-beff-00163e5563d9:491028361-493120005
Executed_Gtid_Set: 5cc28f3c-6a8d-11e4-beff-00163e5563d9:1-491645390,
bcba2d0d-6797-11e7-bbb3-00163e00013d:1-281
Auto_Position: 1
1 row in set (0.00 sec)
mysql> select id,member_id,statis_date,last_time,create_time from `fireway`.`sm_member_realtime_statistics`;
+----+------------------------+-------------+---------------------+---------------------+
| id | member_id | statis_date | last_time | create_time |
+----+------------------------+-------------+---------------------+---------------------+
| 2 | HJB2000000000001442105 | 2017-07-12 | 2017-07-12 00:00:00 | 2016-11-24 11:19:11 |
| 55 | HJA2000000000002236599 | 2017-07-12 | 2017-07-12 00:00:00 | 2017-05-02 15:17:18 |
| 56 | HJB2000000000001234314 | 2017-07-12 | 2017-07-12 00:00:00 | 2016-10-21 19:26:25 |
| 57 | HJA2000000000002505016 | 2017-07-12 | 2017-07-12 00:00:00 | 2017-06-22 10:46:16 |
| 58 | HJB2000000000000097015 | 2017-07-12 | 2017-07-12 00:00:00 | 2015-12-13 00:00:00 |
| 59 | HJB2000000000001952016 | 2017-07-12 | 2017-07-12 00:00:00 | 2017-03-09 11:18:30 |
| 60 | HJA2000000000002497216 | 2017-07-12 | 2017-07-12 00:00:00 | 2017-06-21 10:24:31 |
| 61 | HJB2000000000001925065 | 2017-07-12 | 2017-07-12 00:00:00 | 2017-03-06 10:12:05 |
| 62 | HJB2000000000002021177 | 2017-07-12 | 2017-07-12 00:00:00 | 2017-04-03 13:32:27 |
| 63 | HJB2000000000001234160 | 2017-07-12 | 2017-07-12 00:00:00 | 2016-10-18 16:39:38 |
| 64 | HJA2000000000002505074 | 2017-07-12 | 2017-07-12 00:00:00 | 2017-06-22 14:13:43 |
| 65 | HJB2000000000001955228 | 2017-07-12 | 2017-07-12 00:00:00 | 2017-03-21 14:41:12 |
| 66 | HJA2000000000002506384 | 2017-07-12 | 2017-07-12 00:00:00 | 2017-06-24 14:21:54 |
+----+------------------------+-------------+---------------------+---------------------+
13 rows in set (0.00 sec)
|