geokrety/geokrety-scripts

waypoint/oc xml2sql.php error duplicate stop the mass import

Closed this issue · 3 comments

boly38 commented

HowToReproduce ?

Execute the following command

cd /opt/geokrety-scripts/waypointy/oc && php xml2sql.php

waypoint should be imported in one shot, but the mass import stop at the first error, ex:

error 1: 138028 INSERT INTO `gk-waypointy` ( `waypoint`, `lat` , `lon` , `name` , `owner`,  `typ`, `kraj` , `link`, `status`)
VALUES ('OC888B',  '50.95977', '7.11358', 'Einsatz an der Steinbreche  (#2)' //...//
ON DUPLICATE KEY UPDATE `waypoint`='OC888B', `lat`='50.9 //...//

suggested fix

on duplicate log as warn and continue

boly38 commented

If I execute the related query directly on adminer, I got only success

  • 1 line updated
  • 2 lines updated (issue with constraint ?????)
  • 0 line updated

related query

INSERT INTO `gk-waypointy` ( `waypoint`, `lat` , `lon` , `name` , `owner`,  `typ`, `kraj` , `link`, `status`)
VALUES ('OC888B',  '50.95977', '7.11358', 'Einsatz an der Steinbreche  (#2)', 'bergischhoch2', 'Multicache', 'Deutschland', 'https://www.opencaching.de/searchplugin.php?userinput=OC888B', '1')
ON DUPLICATE KEY UPDATE `waypoint`='OC888B', `lat`='50.95977', `lon`='7.11358', `name`='Einsatz an der Steinbreche  (#2)', `owner`='bergischhoch2', `typ`='Multicache', `kraj`='Deutschland', `link`='https://w
ww.opencaching.de/searchplugin.php?userinput=OC888B', `status`='1'

structure
image

SELECT * FROM `gk-waypointy` WHERE `waypoint` = 'OC888B' LIMIT 50

give me one result

clean to retry

DELETE FROM `gk-waypointy` WHERE `waypoint`='OC888B'

yep reproduced LOL

boly38 commented

got it: there is a cariage return in the link value field ! :)

boly38 commented

by fixing this issue, we also add many many waypoints that could have been skipped by previous execution error.

The way this script is fetching $modifiedsince from database content, we could run it multiple times and as result some cache will not be imported as their modifiedsince dateoffset will be too old for further executions.

xml2sql should never die ;)

Main root cause of the sql or die error is exotic characters in cache attributes. Will add errors historic files.