Lost Connection to MySQL DB Thru Livecode

[Not a lot of thought]

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]

Is this limited to your LAN, or is the database exposed to the Internet?

[Not a lot of thought]

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]

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.

[Not a lot of thought]

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

[Not a lot of thought]

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]

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.

[Not a lot of thought]

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]

-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]

...
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