DBConnection on hosted server drops after a few minutes

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

Post Reply
nrprioleau
Posts: 27
Joined: Mon May 09, 2016 10:53 am

DBConnection on hosted server drops after a few minutes

Post by nrprioleau » Thu Oct 06, 2016 8:25 am

Good morning,

i have built my application using SQLYOGA and GLXApp Framework. It works beautifully. But...
I host my PHP/Mysql on Godaddy file hosting service. After I have connected to the DB and worked for a few minutes, the connection drops out and my application becomes unresponsive. Would this be a setting on the hosting service or is there something I can do on my app side to keep the connection alive?

Thanks
Nic

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: DBConnection on hosted server drops after a few minutes

Post by AxWald » Thu Oct 06, 2016 11:33 am

Hi,

MySQLs on hosting services are usually configured to work in "LAMP mode" - to provide web pages. This means mostly "MyISAM" tables, and a MySQL configuration optimized for many small queries, from many different connections.

For sure you can fiddle with the MySQL parameters, but this can open another can of worms (if it serves web pages too, for instance). You could also try to keep your connection alive, google "MySQL keep alive".

But as well you just could do what I do:
  • Open connection - Query - Close connection". This is what MySQL is good at, and it comes with few overhead. And saves tons of headaches!
  • Make your "working tables" InnoDB and enjoy transactions :)
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

dave.kilroy
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 858
Joined: Wed Jun 24, 2009 1:17 pm
Location: Plymouth, UK
Contact:

Re: DBConnection on hosted server drops after a few minutes

Post by dave.kilroy » Thu Oct 06, 2016 12:29 pm

+1 for what AxWald says about - open db - do stuff - close db...

EDIT: also, are you connecting to the db directly? If so then consider using a web service

AxWald wrote:Hi,

MySQLs on hosting services are usually configured to work in "LAMP mode" - to provide web pages. This means mostly "MyISAM" tables, and a MySQL configuration optimized for many small queries, from many different connections.

For sure you can fiddle with the MySQL parameters, but this can open another can of worms (if it serves web pages too, for instance). You could also try to keep your connection alive, google "MySQL keep alive".

But as well you just could do what I do:
  • Open connection - Query - Close connection". This is what MySQL is good at, and it comes with few overhead. And saves tons of headaches!
  • Make your "working tables" InnoDB and enjoy transactions :)
Have fun!
"...this is not the code you are looking for..."

robl
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 62
Joined: Wed Dec 22, 2010 9:50 pm
Location: 3rd planet from the Sun

Re: DBConnection on hosted server drops after a few minutes

Post by robl » Thu Oct 06, 2016 12:56 pm

AxWald is correct. In general, best practice dictates that you should not retain or expect a database connection to remain alive. You should connect, run a series of queries (i.e. just what is needed to populate a screen or report) and then disconnect, and repeat as necessary. Maintaining an open connection is [somewhat] expensive on the server side. Each active connection consumes RAM and a network socket connection. On a shared hosting platform, like GoDaddy, the same MySQL server may be shared among hundreds of customers and websites and each of those websites may have multiple visitors in a short span of time. Those server resources quickly add up.

Also for certain commercial databases, it would also consume a very expensive license, since many commercial database servers are licensed on the number of concurrent/active users.

Better still, a web service that handles that database interaction is the current trend. Your LiveCode app would just interact with the web service using and parsing the results as required.

nrprioleau
Posts: 27
Joined: Mon May 09, 2016 10:53 am

Re: DBConnection on hosted server drops after a few minutes

Post by nrprioleau » Fri Oct 07, 2016 7:55 am

Thanks for all the comments, I now understand why I am having the errors as I moved away from SQLYoga in various parts of my app as I was not sure how to achieve the results I needed using SQLYoga. Perhaps someone can shed some light for me.

I am building a chart and need to execute the following sql query. Can I do this with SQLYoga?

Code: Select all

select month(s.sales_salesDate) as mnth, year(s.sales_salesDate) as yr, sum(sd.salesd_qty) as qty, sum(sd.salesd_total) as total, 
sum(sd.salesd_qtyProduced) as produced, sum(sd.salesd_qtyCollected) as collected  from sales s
inner join sales_detail sd on sd.salesd_salesID = s.sales_ID
where year(s.sales_salesDate) = "2016"
group by mnth
order by mnth 

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: DBConnection on hosted server drops after a few minutes

Post by AxWald » Sun Oct 09, 2016 8:56 am

Hi.
nrprioleau wrote:

Code: Select all

[...] from sales s
inner join sales_detail sd on sd.salesd_salesID = s.sales_ID
where [...]
This looks fishy. Basic "inner join":

Code: Select all

FROM table_A INNER JOIN table_B ON table_A.field = table_B.field
"sales s" & "sales_detail sd"? Shoudn't it read:

Code: Select all

[...] from s inner join sd on sd.salesd_salesID = s.sales_ID [...]
If you can do this with SQLYoga, no idea. Never really tried this. Had a look at it once, but since I'm used to work on raw SQL, have my tested & trusty little helpers for doing so, and don't neither need nor desire another layer of abstraction between, I dropped it.

OK. Just for fun I threw your query to my tools - and this is what they made of it (MySQL-Version):

Code: Select all

put "SELECT MONTH(s.sales_salesDate) AS mnth, YEAR(s.sales_salesDate) AS yr, " & \
"SUM(sd.salesd_qty) AS qty, SUM(sd.salesd_total) AS total, " & \
"SUM(sd.salesd_qtyProduced) AS produced, SUM(sd.salesd_qtyCollected) AS collected " & \
"FROM `s` INNER JOIN `sd` ON sd.salesd_salesID = s.sales_ID " & \
"WHERE YEAR(s.sales_salesDate) = '" & MyYear & "' GROUP by mnth;" into StrSQL
Remarks:
  1. Writing SQL keywords in UPPERCASE helps keeping track of the code, but is optional.
  2. When writing SQL code that spans several lines, be careful with the line trailing spaces - they love to vanish & give you headache.
  3. Use single quotes in SQL statements. They work as well & don't get mixed up with LC string quotes ...
  4. For some strange reason MySQL sometimes demands table names in accent graves (`, chartonum(96)).
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

nrprioleau
Posts: 27
Joined: Mon May 09, 2016 10:53 am

Re: DBConnection on hosted server drops after a few minutes

Post by nrprioleau » Wed Oct 12, 2016 1:57 pm

Thank you.

I was using the sqlYoga syntax incorrectly and have managed to solve my problem

Post Reply

Return to “SQL Yoga”