Lost Connection to MySQL DB Thru Livecode

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Lost Connection to MySQL DB Thru Livecode

Post by Not a lot of thought » Mon May 29, 2017 1:43 pm

I've been struggling with lost connections on long queries going back to a MySQL database. The database connection is fine (from the database side); internet connection is good, but for some reason Livecode only lets the DB query run for about a minute before it records

Code: Select all

revdberr,Lost connection to MySQL server during query
What if I need it to run for several minutes or half an hour or something? I've tried doing smaller requests, but then it seems that I lose connection after the databaseConnect function is run too many times. If the databaseConnect isn't run in every iteration of a loop then it still loses connection. So, I'm at a loss on how to sustain longer server connections.

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9801
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Lost Connection to MySQL DB Thru Livecode

Post by FourthWorld » Mon May 29, 2017 6:17 pm

Is this limited to your LAN, or is the database exposed to the Internet?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Re: Lost Connection to MySQL DB Thru Livecode

Post by Not a lot of thought » Mon May 29, 2017 6:46 pm

I am using a cloud database through Amazon Web Services. I've already checked and had Amazon check their side. They don't show any connectivity issues and I can run it through MySQL Workbench just fine, I just can't get it to run through livecode.

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9801
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Lost Connection to MySQL DB Thru Livecode

Post by FourthWorld » Mon May 29, 2017 7:44 pm

Perhaps raising the socketTimeoutInterval would help.

But looking at the larger context, unless the DB is somehow restricted to only accepting connections from a small number of fixed IPs or other means of reducing the risks of having SQL's rich language exposed to brute-force attacks, you might consider the more conventional approach of using a REST API to access the DB. Being stateless, a REST API would eliminate socket timeouts as well.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Re: Lost Connection to MySQL DB Thru Livecode

Post by Not a lot of thought » Mon May 29, 2017 8:17 pm

Any advice on where I might learn more about that as it applies to Livecode?

Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Re: Lost Connection to MySQL DB Thru Livecode

Post by Not a lot of thought » Mon May 29, 2017 8:42 pm

Or better, a simple example. I'm kind of new to this idea and trying to learn. I was considering building stored procedures in the database that could be called from livecode perform the function on the server side in order to prevent the constant transmission of data between host and app, but I'm struggling with how to get the information back to the app. Is this similar to what you are referring to?

I did build a simple one on the server side which created a temp table and inserted information from a query into the table, but even this returned a lost connection method. Used a revDataFromQuery...maybe should have used a revExecuteSQL now that I think of it, but if I do that I'm guessing I would have to query the temp table to return the data, right?

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Lost Connection to MySQL DB Thru Livecode

Post by MaxV » Tue May 30, 2017 1:21 pm

Not a lot of thought wrote:I've been struggling with lost connections on long queries going back to a MySQL database. The database connection is fine (from the database side); internet connection is good, but for some reason Livecode only lets the DB query run for about a minute before it records

Code: Select all

revdberr,Lost connection to MySQL server during query
What if I need it to run for several minutes or half an hour or something? I've tried doing smaller requests, but then it seems that I lose connection after the databaseConnect function is run too many times. If the databaseConnect isn't run in every iteration of a loop then it still loses connection. So, I'm at a loss on how to sustain longer server connections.
Wait a minute, databases don't work good this way.
You send a query (SELECT, UPDATE, ALTER, whatever), the communication is less of a second. The time to do the task query is another thing.
Usually a complex query should be inserted in a BEGIN TRANSACTION / COMMIT:

Code: Select all

BEGIN TRANSACTION;
first query;
second query;
...
last query;
COMMIT;
If you don't use BEGIN TRANSACTION, you are doing wrong.

If you aren't using the local computer, you don't need to be connected for a long time, on the contrary you should be connected just for the strict necessary.
In very complex queries that alter Gigabytes of data, you don't remain connected. You put a special flag in the last query and every n minutes ask a SELECT to see if the flag changed status.

Please let me know if you need a full example.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Not a lot of thought
Posts: 77
Joined: Thu May 21, 2015 2:41 am

Re: Lost Connection to MySQL DB Thru Livecode

Post by Not a lot of thought » Tue May 30, 2017 11:50 pm

I tried executing the "Begin Transaction/Commit" for the query with a revDataFromQuery and a revExecuteSQL. The revExecuteSQL won't pull the data, so I'd have to create a table in order to gain the information that I need and query that (I'm guessing). The revDataFromQuery returned an error in the syntax. I tried using a START TRANSACTION/COMMIT structure and it did the same thing. What am I missing?

Also, I'm interested in the flag changed status. My database is about 20G and this is mainly held in 4 tables. 1 of which I am trying to query in this instance.

I really only want to do one thing at the moment, and that is just return a distinct list of the names of the companies in the table, in order to do a loop later to go through and run a query against each name for certain information.

Code: Select all

      put "START TRANSACTION;SELECT Company_ID FROM Company_Data GROUP BY Company_ID ORDER BY Company_ID ASC;COMMIT;" into LoadSQL
      revExecuteSQL gDatabaseID, LoadSQL
and

Code: Select all

      put "BEGIN TRANSACTION;SELECT Company_ID FROM Company_Data GROUP BY Company_ID ORDER BY Company_ID ASC;COMMIT;" into LoadSQL
      revExecuteSQL gDatabaseID, LoadSQL
What am I missing here?

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: Lost Connection to MySQL DB Thru Livecode

Post by bangkok » Wed May 31, 2017 5:19 am

-your DB is hosted on the cloud
-its size is huge (20 GB)
-large queries or small, do not work with Livecode... connection is lost after 1 mn
-but those queries seem to be okay with any other SQL clients

-meanwhile, you're running around with transactions, multiples databaseConnect in a loop, stored procedures, temporary tables etc....

->you should go back to the basics, and start small with LIMIT and play with the timeout in revOpenDatabase.

Question : how many company_id do you have in total in the database ?

Try this in a button

Code: Select all

on mouseUp
   
   put revOpenDatabase("mysql","XX.XX.XX.XX",YourdbName,YourdbUser,YourdbPass,,,0,false) into dbID
   
   ------- note the 0 = documentation = " (MySQL Only)  A number specifying the database read/write timeout in whole seconds.  Defaults to 20 seconds if empty or not a positive integer. If 0, the connection never times out."
   
   if dbID is not a number then
      answer error dbID
      exit to top
   end if
   
   put "SELECT Company_ID FROM Company_Data GROUP BY Company_ID LIMIT 200" into dbSQL
   
   put  revDataFromQuery(, , dbID, dbSQL) into tTheData
   
   revCloseDatabase dbID

   if tTheData begins with "revdberr" then
      answer error tTheData 
      exit to top
   end if
   
   answer the number of lines of TheData
   
   put TheData
   
end mouseUP
And then, increase LIMIT, and see how it goes.

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Lost Connection to MySQL DB Thru Livecode

Post by MaxV » Wed May 31, 2017 11:58 am

Not a lot of thought wrote: ...
I really only want to do one thing at the moment, and that is just return a distinct list of the names of the companies in the table, in order to do a loop later to go through and run a query against each name for certain information.
...
What am I missing here?
In your case just try this:

Code: Select all

put  revOpenDatabase("mysql", "www.example.com", "myDB", myUsr, myPass, false , , 600, true) into ConnectionID
put "SELECT DISTINCT Company_ID FROM Company_Data  ORDER BY Company_ID ASC;" into tSQL
put revDataFromQuery( , return, ConnectionID, tSQL) into myVar
It should return in few seconds the result. It isn't a complex query.
I also added the connection option of timeout of 10 minutes (otherwise is 20 seconds) and autoreconnect. See http://livecode.wikia.com/wiki/RevOpenDatabase
If you use SSL, change false to true
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Post Reply

Return to “Databases”