Hi,
I am using mk(pt)-archiver to archive data from the on-line database server to an off-line archive server. The script used is as follows:
mk-archiver --statistics --source h=localhost,F=archive.conf,D=DB-NAME,t=TABLE-NAME --dest h=172.80.1.31,D=ARCHIVE-DB-NAME,t=TABLE-NAME --user=DB-USER --password=DB-PASS --no-check-charset --skip-foreign-key-checks --txn-size=600000 --sleep=2 --sleep-coef=1 --retries=3 --sentinel --header --file ‘/PATH/TO/SQL/FILE/%Y-%m-%d-%H-%i-%s-%D.%t’ --where ‘dtLocationDate < DATE_SUB(CURDATE(), INTERVAL 181 DAY) or dtLocationDate > DATE_ADD(CURDATE(), INTERVAL 2920 DAY)’
The script executes for some time, then prints the following error and exits.
DBD::mysql::st execute failed: Duplicate entry ‘1083-2022-03-28 08:29:16-51-0’ for key ‘PRIMARY’ [for Statement “INSERT INTO ARCHIVE-DB-NAME.TABLE-NAME(iasset,irecordtype,ialert,iindex,igpsmode,iprevfix,dtlocationdate,fltlatitude,fltlongitude,idirection,fltspeed,icummdist,fltavgspeed,iescalation,ibilling,iotherdata,ftgadistance,igadirection,sgalandmark,igaposition,ilandmark) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)” with ParamValues: 0=‘1083’, 1=‘51’, 2=‘0’, 3=‘2’, 4=‘3’, 5=‘1’, 6=‘2022-03-28 08:29:16’, 7=‘5.86916’, 8=‘118.093’, 9=‘0’, 10=‘0’, 11=‘64021676’, 12=undef, 13=‘0’, 14=‘14901181’, 15=‘0’, 16=‘3374.55’, 17=‘7’, 18=‘SANDAKAN
[*]’, 19=‘2’, 20=‘0’] at /usr/bin/mk-archiver line 4126.
According to the manual, I have 2 options of using –ignore or extending the tool incorporate the option of before_insert(row => @row) to include ON DUPLICATE KEY UPDATE.
I believe that incorporating ON DUPLICATE KEY UPDATE is better than using --ignore option.
Can someone please help me in extenting the tool incorporate the plugin.
Regards
Prashant
