Author Topic: DB Error when using geolocation scheduled job (Read 842 times)

benmoreassynt · January 22, 2014, 03:27:01 pm

Hi,

With a fresh Wordpress install of CiviCRM I'm finding the the geolocation cron job fails with a DB error. I've tried with various different Google API keys, to see if that was the problem, but I don't think it is. Here's the error.

Code: [Select]

2014-01-22 18:20:31	Geocode and Parse Addresses	
0
Summary
Finished execution of Geocode and Parse Addresses with result: Failure, Error message: DB Error: syntax error
Details


Parameters raw (from db settings): 
geocoding=[1 or 0] required
parse=[1 or 0] required
start=[contact ID] optional-begin with this contact ID
end=[contact ID] optional-process contacts with IDs less than this
throttle=[1 or 0] optional-1 adds five second sleep

Parameters parsed (and passed to API method): 
a:6:{s:7:"version";i:3;s:9:"geocoding";s:17:"[1 or 0] required";s:5:"parse";s:17:"[1 or 0] required";s:5:"start";s:48:"[contact ID] optional-begin with this contact ID";s:3:"end";s:62:"[contact ID] optional-process contacts with IDs less than this";s:8:"throttle";s:42:"[1 or 0] optional-1 adds five second sleep";}

Full message: 
Finished execution of Geocode and Parse Addresses with result: Failure, Error message: DB Error: syntax error

2014-01-22 18:20:31	Geocode and Parse Addresses	
0
Summary
Starting execution of Geocode and Parse Addresses
Details


Parameters raw (from db settings): 
geocoding=[1 or 0] required
parse=[1 or 0] required
start=[contact ID] optional-begin with this contact ID
end=[contact ID] optional-process contacts with IDs less than this
throttle=[1 or 0] optional-1 adds five second sleep

Parameters parsed (and passed to API method): 
a:6:{s:7:"version";i:3;s:9:"geocoding";s:17:"[1 or 0] required";s:5:"parse";s:17:"[1 or 0] required";s:5:"start";s:48:"[contact ID] optional-begin with this contact ID";s:3:"end";s:62:"[contact ID] optional-process contacts with IDs less than this";s:8:"throttle";s:42:"[1 or 0] optional-1 adds five second sleep";}

Full message: 
Starting execution of Geocode and Parse Addresses

Any ideas about what the problem is? Turning on CiviCRM error reporting and backtrace does not help in this case.

benmoreassynt · January 22, 2014, 03:43:26 pm

Having enabled and looked at MySQL query log, it appears the following query is getting sent to MySQL, which includes values which clearly should be replaced by numeric values:

Code: [Select]

SELECT     c.id,
               a.id as address_id,
               a.street_address,
               a.city,
               a.postal_code,
               s.name as state,
               o.name as country
    FROM       civicrm_contact  c
    INNER JOIN civicrm_address        a ON a.contact_id = c.id
    LEFT  JOIN civicrm_country        o ON a.country_id = o.id
    LEFT  JOIN civicrm_state_province s ON a.state_province_id = s.id
    WHERE      ( c.id = a.contact_id ) AND ( c.id >= [contact ID] optional-begin with this contact ID ) AND ( c.id <= [contact ID] optional-process contacts with IDs less than this ) AND ( a.geo_code_1 is null OR a.geo_code_1 = 0 ) AND ( a.geo_code_2 is null OR a.geo_code_2 = 0 ) AND ( a.country_id is not null )
      ORDER BY a.id

The bits of MySQL that are clearly wrong are:
( c.id >= [contact ID] optional-begin with this contact ID ) AND ( c.id <= [contact ID] optional-process contacts with IDs less than this )

Is this is a Wordpress bug, or a misconfiguration on my part?

CiviCRM 4.4.3 for Wordpress
Wordpress 3.8

Dave Greenberg · January 22, 2014, 03:46:06 pm

You have to set the parameters in the Admin UI -> Administer > System Settings > Scheduled Jobs
The default parameters provide 'help' but can't be used as is. There's a link to parameter doc for all the jobs from that admin page as well.

benmoreassynt · January 23, 2014, 06:50:19 am

Oh - facepalm. Thanks for that.

I must have set that stuff up on my other CiviCRM sites, but had clean forgotten.

On a side note, shouldn't the criteria be escaped? I know it's only admins who edit that stuff, but it still looks like unescaped user input to me.

Donald Lobo · January 23, 2014, 07:51:58 am

yes it should. can u please file an issue for this so this can be fixed for the next release

thanx

lobo

benmoreassynt · January 23, 2014, 11:15:12 am

Will do, thanks.

B

CiviCRM Community Forums (archive)

News:

Author Topic: DB Error when using geolocation scheduled job (Read 842 times)

benmoreassynt

DB Error when using geolocation scheduled job

benmoreassynt

Re: DB Error when using geolocation scheduled job

Dave Greenberg

Re: DB Error when using geolocation scheduled job

benmoreassynt

Re: DB Error when using geolocation scheduled job

Donald Lobo

Re: DB Error when using geolocation scheduled job

benmoreassynt

Re: DB Error when using geolocation scheduled job