0

I'm trying to get data from MySQL DB into Rstudio-server. My actions are like

mydb = dbConnect(MySQL(), user='user', password='password', dbname='dbname', host='localhost')
query <- stri_paste('select sellings.updated_at AS Up_Date, concat(item_parameters.title, " ", ad_attributes.int_value) AS Class, CONCAT(geos.name, " ", geos.kind) AS place, geos.lon, geos.lat, sellings.price AS price, ((geo_routes.distance*2/1000 + 100)) AS delivery_cost FROM sellings, users, item_parameters, ad_attributes, geos, geo_routes WHERE users.encrypted_password!="" && item_parameters.title="Класс" && sellings.price IS NOT NULL && ad_attributes.int_value IS NOT NULL AND users.id=sellings.user_id AND item_parameters.id=ad_attributes.item_parameter_id AND sellings.id = ad_attributes.ad_id AND sellings.geo_guid = geos.guid AND geos.routable_guid = geo_routes.src_guid AND geo_routes.distance = (SELECT geo_routes.distance FROM geo_routes, geos WHERE geos.guid = sellings.geo_guid AND geo_routes.src_guid = geos.routable_guid AND geo_routes.dst_guid = (SELECT geos.routable_guid FROM geos WHERE geos.name = "Воронеж" && geos.kind = "г")) ORDER BY Up_Date;')
rs = dbGetQuery(mydb, query)

And I get an empty dataframe. But when I do the same with my local DB everything is OK. The query takes a pretty long time, about 3 minutes, but it works properly. Moreover the same query works right from the command line in MySQL. On the server, it takes about 4 seconds. OS of server is Debian 7, OS of local machine is Win 8. Any idea?

b4hand
  • 9,550
  • 4
  • 44
  • 49
Serj
  • 1
  • 1
    That sure is a long query. Why are you using `stri_paste()` here? And what package is that from? And what exactly is the question? It it why you are experiencing time differences? Or why is nothing returned? – MrFlick Jan 16 '15 at 17:56
  • The question is why is nothing returned and "Why everything works right on the local machine and doesn't work on server?". stri_paste() is from 'stringi' package. Shorter queries work right at the same time. – Serj Jan 16 '15 at 20:00

2 Answers2

0

Sometimes when querying from the command line the default schema has been set in a previous command. This command doesn't carry over to R so the exact same query from a command line to a R session might not work. Maybe check the dbname.

JHowIX
  • 1,683
  • 1
  • 20
  • 38
0

Insert the below statements in your SQL query

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

It worked for me

Duffer
  • 256
  • 1
  • 14