mySQL on GCS "normal shutdown" causing LC to hang

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
adventuresofgreg
Posts: 349
Joined: Tue Oct 28, 2008 1:23 am
Contact:

mySQL on GCS "normal shutdown" causing LC to hang

Post by adventuresofgreg » Tue Feb 06, 2018 4:52 pm

Hi:

i am having an intermitent problem with my Livecode apps hanging at the same time during a mySQL querie. Checking GCS SQL error log, it seems like the issue is caused by a "Normal Shutdown" and i have no idea what that is, but if this is a GCS SQL update, then the fault is probably my code not tolerating SQL dissappearing part way through a download or something.

is there some way to write my code which would allow this "shut down" to happen, and just cycle through it without hanging?

Here is my SQL code:

Code: Select all

 if revopendatabases() is empty then
      sendlog "SQL CONNECTION ERROR - no commection. Please connect to the database first."
      SQLconnect
   end if
   
 
      put "UPDATE " & tablename & " SET " & ThisField & "='" & field ThisField & "' WHERE " & WHatAccount into tSQL
      revExecuteSQL gConnectionID, tSQL
      
      if the result contains "revdberr" then
         put "There was a problem adding the record to the database. Disconnecting.." & return & "these fields:" & return & FieldList & return & the result into field "Qtester"
         
         SQLdisconnect
         exit to top
      end if


adventuresofgreg
Posts: 349
Joined: Tue Oct 28, 2008 1:23 am
Contact:

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by adventuresofgreg » Tue Feb 06, 2018 5:15 pm

it seems that when i use

revExecuteSQL gConnectionID, tSQL

and gConnectionID is not a valid connection id, or if the connection is closed, then rather than RETURNING "revdberr" which would allow me to establish a new connection, it's throwing up a LiveCode execution error which is preventing my "revdberr" to pass through this. i think this is why my standalone is hanging.

if the result contains "revdberr" then
-- do something to try again, reconnect, or just pass through it all and do it next cycle
SQLdisconnect
exit to hypercard
end if

How do I prevent the LiveCode execution error?

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

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by FourthWorld » Tue Feb 06, 2018 6:00 pm

Try/catch will let you intercept exec errors with custom handing.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

adventuresofgreg
Posts: 349
Joined: Tue Oct 28, 2008 1:23 am
Contact:

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by adventuresofgreg » Tue Feb 06, 2018 6:18 pm

I'm getting a little closer to figuring out what the problem is...

My LiveCode apps are hanging when Google Cloud Service does a SQL "Update" because the update times in the log perfectly match the time when my apps crash. The update disconnects all SQL connections.

So.. my issue is, how do i make my LiveCode SQL code tolerant of these forced disconnections? I wish there was a way to simulate this "normal shutdown". I tried running my SQL query with my internet connection turned off, and sure enough, it appears to hang.. but as soon as the connection is reestablished, the handler finishes it's job.

ideas?
Greg

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by bogs » Tue Feb 06, 2018 8:59 pm

I don't deal with this type of thing normally, so this input may be of very limited (or non existent) value.

From my understanding, all servers issue a response to any request. There will be a normal "everything is working" response, or not. If you know the response that indicates everything is hunky dory, you can through a control structure test for it. If it is the hunky dory response, continue, else, answer "db offline" and exit the handler or send the handler back to itself after x mins/hours/days(?) to check the connection again.

Alternately, you could write out any db operations, and then 'update' the db when the connection is there by using your writing routine.

Just a couple thoughts.
Image

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

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by MaxV » Wed Feb 07, 2018 1:04 pm

Livecode advice you if the connectionis lost, the result of quesries is like "server gone".
However if you add "autoReconnect" = TRUE, you don't lose connection, see http://livecode.wikia.com/wiki/RevOpenDatabase
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

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

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by FourthWorld » Wed Feb 07, 2018 2:46 pm

Is this connection from a client, or do you have a persistent LiveCode instance running on the server?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

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

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by AxWald » Thu Feb 08, 2018 11:33 am

Hi,

the desired result from a revExecuteSQL call is the number of records affected, so you better test for this - revdberr is not send for this command (neither for the revDB_execute function equivalent)!

Besides, when working with remote databases it's essential to catch all & any errors. Doing this for each single query is nasty, so we better have one handler to catch all the database work. I have appended an example (it's a stripped down version of the one I use in my programs).

Parameters are easy:
  • theDB: the database you want to connect to.
    It's assumed that you have a custom property in your stack, named "cSQLCreds" - each line containing a 'theDB' as item 1, and then the 5 database credential parameters (as used in revOpenDatabase/ revDB_connect), all tab-delimited.
  • StrSQL: The SQL string that shall be sent to the DB.
  • theMode: 'GET' for SELECT queries, 'PUT' for action queries. If empty, 'GET' is used.
  • theDatArr: In 'PUT' mode an array can be provided as data. If this param isn't empty, its contents will be used as data array for the query. Else the query will be run as plain-text. (for syntax, see documentation for revExecuteSQL)
Call it this way:

Code: Select all

on mouseUp
   get runSQL("myDB", "SELECT * FROM t_test;","GET")
   if it begins with "error" then
      answer error it titled "What a mess!"
   else
      -- do whatever you want with 'em results ;-)
      put it
   end if
end mouseUp
It will return something starting with "error" when it fails, or the desired data (number of records affected in case of 'PUT').

Code: Select all

function RunSQL theDB, StrSQL, theMode, theDatArr
   --  set up theMode defaults -> GET:
   if theMode is not "PUT" then put "GET" into theMode  
   if (theDatArr is not empty) AND (theDatArr is not an array) then \
         return "Error in RunSQL: theDatArr is NOT an array!" & theDatArr   
   
   set itemdel to tab                  --  get the login parameters:
   put line lineOffset((theDB & tab), the cSQLCreds of this stack) \
         of the cSQLCreds of this stack into myDBStr
   if myDBStr is empty then return \
         "Error in RunSQL: Found no database parameters for '" & theDB & "'!"
   repeat with i = 2 to 6
      do "put item i of myDBStr into Parm" & (i -1)
   end repeat
   
   put revdb_connect(Parm1,Parm2,Parm3,Parm4,Parm5) into MyDBID  --  connect
   if not (MyDBID is a number) then return \
         "Error in RunSQL: Connecting to " & theDB & " failed, Reason:" & CR & MyDBID
   
   switch theMode
      case "GET"                            --  fetch query data
         put revdb_querylist(tab,return,MyDBID,StrSQL) into myData
         break
      case "PUT"                            --  do an action query
         if theDatArr is empty then   --  as fulltext
            put revdb_execute(MyDBID,StrSQL) into MyData
         else                                      -- or with parameterArray
            put revdb_execute(MyDBID,StrSQL,"theDatArr") into MyData
         end if
         if not (myData is a integer) then  -- we want a revdberr in case of failure
            if not (myData begins with "revdberr") then put "revdberr," before myData
         end if
         break
   end switch
   
   get revdb_disconnect(MyDBID)  --  disconnect
   if MyData begins with "revdberr" then
      return "Error in RunSQL: This query failed:" & CR & StrSQL & CR & \
            "Reason:" & CR & myData
   else
      return Mydata  --  return the result
   end if
end RunSQL
You'll notice that I open a fresh connection for every 'runSQL' call - I don't trust in long standing db connections. This means, 'runSQL' shouldn't be used inside a longer repeat loop - but we don't torture our databases with such anyways, we use elaborate SQL instead, don't we? ;-)

Btw., this also should properly handle a timeout.

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!

adventuresofgreg
Posts: 349
Joined: Tue Oct 28, 2008 1:23 am
Contact:

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by adventuresofgreg » Fri Feb 09, 2018 6:38 pm

Thanks for your response.. I'm taking a closer look.

adventuresofgreg
Posts: 349
Joined: Tue Oct 28, 2008 1:23 am
Contact:

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by adventuresofgreg » Fri Feb 09, 2018 6:41 pm

FourthWorld wrote:
Wed Feb 07, 2018 2:46 pm
Is this connection from a client, or do you have a persistent LiveCode instance running on the server?
This is a connection from a client.

To connect,I use:

put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult

Note that I DO NOT specify autoreconnect, as i check for revopendatabases() on each command, and reconnect within my script if it is required. Do you think adding autoreconnect could fix this issue? I doubt it because livecode hangs in this instance, and if it was just a connection issue, then my script would reconnect itself on next command.

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

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by AxWald » Sun Feb 11, 2018 9:16 am

Hi,
adventuresofgreg wrote:
Fri Feb 09, 2018 6:41 pm
[...] i check for revopendatabases() on each command, and reconnect within my script if it is required [...]
When I started working with web based MySQL (after years of local MS-SQL) I had serious problems with lost connections, at first. Asking for advice I was told that the usual MySQL at your friendly neighborhood provider is configured for servicing web sites, i.e. optimized for thousands of connections at any given time. So it would regularly purge "dead connections" & thus mess up my job, ignoring all efforts to set a high timeout value client side. I should just use a new connection for every job (or change the MySQL configuration on the server ...).

Now I'm no database admin, and not eager to fiddle with customers infrastructure as long as I don't know exactly what I do, so I started to follow the advice & use a fresh connection for every query (as seen in above function). Time measured for a simple connect/ disconnect is usually in the ~150 ms range here, where the round trip time is ~ 50 ms already. Using this in quite some different applications (desktop as well as mobile) for some years now I regard this method valid.

For sure, some things are special & don't work really well this way - updating records in a loop, for instance. For such the use of stored procedures, views & temp tables (on the db server) are well suited, so I rarely use them anymore.

tldr;: MySQL in the common LAMP variety (hosted by your web provider) may drop database connections unexpectedly; so just open a new connection for every request.

One more rarely mentioned fact: These MySQL databases usually default to the myISAM storage engine. And this one doesn't support transactions - but doesn't throw errors on Begin/ Commit/ Rollback, it just ignores them! Use InnoDB if you need these ...

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!

adventuresofgreg
Posts: 349
Joined: Tue Oct 28, 2008 1:23 am
Contact:

Re: mySQL on GCS "normal shutdown" causing LC to hang

Post by adventuresofgreg » Sun Feb 11, 2018 2:39 pm

AxWald wrote:
Sun Feb 11, 2018 9:16 am
Hi,
adventuresofgreg wrote:
Fri Feb 09, 2018 6:41 pm
[...] i check for revopendatabases() on each command, and reconnect within my script if it is required [...]
I started to follow the advice & use a fresh connection for every query (as seen in above function). Time measured for a simple connect/ disconnect is usually in the ~150 ms range here, where the round trip time is ~ 50 ms already. Using this in quite some different applications (desktop as well as mobile) for some years now I regard this method valid.
Thank you. I am running up to 200 LC apps on a single VM, and each app makes multiple reads / writes per minute. I've been connecting once, then checking to ensure that connection still exists prior to each access. I could abandon that and just open then close a new connection, but I'm concerned with the 150 ms time added to each access... Maybe I should set up a timer and measure the impact.

thanks!
Greg

Post Reply

Return to “Databases”