DBConnection on hosted server drops after a few minutes
Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore
-
- Posts: 27
- Joined: Mon May 09, 2016 10:53 am
DBConnection on hosted server drops after a few minutes
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
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
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:
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 :)
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!
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!
-
- 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
+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
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:Have fun!
- 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
"...this is not the code you are looking for..."
-
- 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
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.
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.
-
- Posts: 27
- Joined: Mon May 09, 2016 10:53 am
Re: DBConnection on hosted server drops after a few minutes
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?
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
Hi.
"sales s" & "sales_detail sd"? Shoudn't it read:
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):
Remarks:
This looks fishy. Basic "inner join":nrprioleau wrote:Code: Select all
[...] from sales s inner join sales_detail sd on sd.salesd_salesID = s.sales_ID where [...]
Code: Select all
FROM table_A INNER JOIN table_B ON table_A.field = table_B.field
Code: Select all
[...] from s inner join sd on sd.salesd_salesID = s.sales_ID [...]
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
- 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)).
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!
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!
-
- Posts: 27
- Joined: Mon May 09, 2016 10:53 am
Re: DBConnection on hosted server drops after a few minutes
Thank you.
I was using the sqlYoga syntax incorrectly and have managed to solve my problem
I was using the sqlYoga syntax incorrectly and have managed to solve my problem