Database error please help

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*pi()/180)) * sin((msv1.string*pi()/180))+cos((*pi()/180)) * cos((msv1.string*pi' at line 1

QUERY: SELECT DISTINCT e.*, (((acos(sin((*pi()/180)) * sin((msv1.string*pi()/180))+cos((*pi()/180)) * cos((msv1.string*pi()/180)) * cos((( - msv2.string)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM elgg_entities e JOIN elgg_entity_relationships r on r.guid_one = e.guid JOIN elgg_metadata n_table on
e.guid = n_table.entity_guid JOIN elgg_metadata n_table1
on e.guid = n_table1.entity_guid JOIN elgg_metastrings msn1
on n_table1.name_id = msn1.id JOIN elgg_metastrings msv1
on n_table1.value_id = msv1.id JOIN elgg_metadata n_table2
on e.guid = n_table2.entity_guid JOIN elgg_metastrings msn2
on n_table2.name_id = msn2.id JOIN elgg_metastrings msv2
on n_table2.value_id = msv2.id WHERE (((msn1.string = 'geo:lat' AND BINARY msv1.string
!= '' AND ( (1 = 1) and n_table1.enabled='yes')) AND (msn2.string = 'geo:long' AND BINARY msv2.string
!= '' AND ( (1 = 1) and n_table2.enabled='yes')))) AND ((e.type = 'user')) AND (e.site_guid IN (1)) AND ( (1 = 1) and e.enabled='yes') ORDER BY distance ASC LIMIT 0, 5

  • I tried to see if I could find any info about it but no.

    What were you doing when this happened?

    Rodolfo Hernandez
    Arvixe/Elgg Community Liaison

  • I installed 1.8.3. Then my dev guy installed tons of custome plugins (trying to learn while he is away if I can). Everything was working fine until I tried to login as orginal admin, I get this error (actually longer). When I login as another test account I don't get the error. Hence, I cannot even login as admin.

    Thanks in advance!

  • The problem with the sql syntax is that there is nothing being multiplied by pi in 2 places

    sin((*pi()/180))

    and

    cos((*pi()/180))

    Not sure why this would be the case - potentially sql being generated dynamically and the developer didn't put a sanity check in place for a certain case.  What plugin is this from?

  • How would I know which plugin is causing it? I am only trying to learn something, I thought maybe I could cleanup the database or do some maintenance (rather than just point fingers at my dev guy) but I think this seems out of my league. Thanks for the help! If there's any update which helps the community learn something more, I will report back.

  • looks like ismayil's hype maps - if so - he should have some kinda techie (oriented) fixes sooner or later...

  • Dhrup wins the prize! Hypemaps it must be. Seems strange it only happens when I login as admin, but I guess that's the nature of bugs right, conflicts here but not there...

  • it is hypemaps. the ' ORDER BY distance' - ismayil was working on to make uber-streamlined that part doan q. work okidoki. i did give him some enhanced sql - so maybe he's just too busy for the next fix-release...

  • Dhrup, I don't remember any enhanced sql. You gave a few suggestions, but none of that was constructive enough for me to understand. So, if you can help enhance, that would be great. I will pm you.

    The error was resulting from an invalid geocoding result. Fixed now.

  • @ismayil - http://community.elgg.org/pg/forum/topic/844948/elgg_get_entities-and-mysql-syntax/ -- they say with age comes wisdom, but the memory starts to fail ;-P thats's why some are good at what they do. some peope usually do not forget who their friends are ;oO;X

    DhrupDeScoop
    37 days ago

    it worked ??? lolz ;-) wild 1/2 %s`ed lazy sql !!! wheww & wow btw - i wud have initially split up that elongated code -- too much code sometimes many times leads to blind-sight.. limiting ? -- might need similar 'inline formula' for limiting -- lolz ;-P long long long lines of c-o-d-e tee hee !! have funz ! btw - is this code w.r.t HypARgZ-Maps| or similar ?

    Ismayil Khayredinov
    37 days ago

    Thanks, Mr. So much help lolz wheww & wow :)

    Yup, the new hypeMaps with a super cool UI ;)