Page 1 of 1
DBConnection on hosted server drops after a few minutes
Posted: Thu Oct 06, 2016 8:25 am
by nrprioleau
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
Re: DBConnection on hosted server drops after a few minutes
Posted: Thu Oct 06, 2016 11:33 am
by AxWald
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!
Re: DBConnection on hosted server drops after a few minutes
Posted: Thu Oct 06, 2016 12:29 pm
by dave.kilroy
+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!
Re: DBConnection on hosted server drops after a few minutes
Posted: Thu Oct 06, 2016 12:56 pm
by robl
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.
Re: DBConnection on hosted server drops after a few minutes
Posted: Fri Oct 07, 2016 7:55 am
by nrprioleau
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
Re: DBConnection on hosted server drops after a few minutes
Posted: Sun Oct 09, 2016 8:56 am
by AxWald
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:
- Writing SQL keywords in UPPERCASE helps keeping track of the code, but is optional.
- When writing SQL code that spans several lines, be careful with the line trailing spaces - they love to vanish & give you headache.
- Use single quotes in SQL statements. They work as well & don't get mixed up with LC string quotes ...
- For some strange reason MySQL sometimes demands table names in accent graves (`, chartonum(96)).
Have fun!
Re: DBConnection on hosted server drops after a few minutes
Posted: Wed Oct 12, 2016 1:57 pm
by nrprioleau
Thank you.
I was using the sqlYoga syntax incorrectly and have managed to solve my problem