revdberr,Lost connection to MySQL server during query

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
DavJans
Posts: 270
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

revdberr,Lost connection to MySQL server during query

Post by DavJans » Wed Apr 06, 2016 9:26 pm

Anyone got any Ideas to try on this one? I time out at 21 seconds every time.

I have these set on the server.

set global max_allowed_packet=1048576000;
set global wait_timeout=3600;
set global interactive_timeout=3600;

The same Query from mySQL Workbench does not time out, it finishes at 23.73 seconds. so the server isn't set to time out at 21 seconds?

anything else to try?
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här

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

Re: revdberr,Lost connection to MySQL server during query

Post by MaxV » Thu Apr 14, 2016 3:36 pm

Code: Select all

set the socketTimeoutInterval to 25000
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

DavJans
Posts: 270
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

Re: revdberr,Lost connection to MySQL server during query

Post by DavJans » Thu Apr 14, 2016 5:01 pm

Thank you for trying, either it didn't work or I didn't put it in the right spot.

Dictionary tells us the default is 10 seconds, not close to 21, however I tried putting it in my script on my button just before the

Code: Select all

put revDataFromQuery(tab, cr, gConnectionID, tSQL) into jobID
maybe that's the wrong place and that's my problem but it didn't help, also tried 60 seconds, still same result at 21 seconds
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här

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

Re: revdberr,Lost connection to MySQL server during query

Post by MaxV » Fri Apr 15, 2016 8:48 am

Did you try with revQueryDatabase?
For example:

Code: Select all

put revQueryDatabase(connID, "SELECT * FROM images WHERE id=" & field "id" & ";") into tRecordSet
put revDatabaseColumnNamed(tRecordSet, "image", tImage) into tError
revCloseCursor tRecordSet
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

DavJans
Posts: 270
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

Re: revdberr,Lost connection to MySQL server during query

Post by DavJans » Mon Apr 18, 2016 10:40 pm

I did try that to put the data into a datagrid, unless that's not quite the same thing?

Also have done some more testing. After I get the error in livecode telling me I lost my connection, looking at the server my connection on that side is still there so the server it seems to me is not closing the connection and it must be livecode?

if I run my query in a loop on a few different jobs 3 small job for instance and then the big job that is taking 21+ second, the 3 jobs give results and the last one fails.
if I turn it around and run the long one first then all 4 fail.
the error given for the next 3 after the long one fails is revdberr,MySQL server has gone away.
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här

DavJans
Posts: 270
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

Re: revdberr,Lost connection to MySQL server during query

Post by DavJans » Tue Apr 19, 2016 8:53 pm

Well I'm trying a new approach on this because I cant seen to figure out the 21 second livecode just decides to close the connection.

I've added "INTO OUTFILE 'C://Temp//@JobNum.csv';" and changed it up to revExecuteSQL instead, however this also waits for confirmation from the server, so again its failing.

Is there a way to just send 3 or 4 or 10 query's to the server and not care about a return at all, I know it worked or failed by looking for the data in the export file.
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här

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

Re: revdberr,Lost connection to MySQL server during query

Post by MaxV » Wed Apr 20, 2016 10:41 am

DavJans wrote:Well I'm trying a new approach on this because I cant seen to figure out the 21 second livecode just decides to close the connection.

I've added "INTO OUTFILE 'C://Temp//@JobNum.csv';" and changed it up to revExecuteSQL instead, however this also waits for confirmation from the server, so again its failing.

Is there a way to just send 3 or 4 or 10 query's to the server and not care about a return at all, I know it worked or failed by looking for the data in the export file.
I would use this SQL syntax:

Code: Select all

BEGIN TRANSACTION;
UPDATE...;
SELECT...;
ALTER TABLE...;
COMMIT;
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

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

Re: revdberr,Lost connection to MySQL server during query

Post by AxWald » Wed Apr 20, 2016 12:58 pm

Hi,

just hope your tables are not MyISAM - no support for transactions ...
The major deficiency of MyISAM is the absence of transactions support. Also, foreign keys are not supported.
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!

DavJans
Posts: 270
Joined: Thu Dec 12, 2013 4:21 pm
Location: Spokane, WA USA

Re: revdberr,Lost connection to MySQL server during query

Post by DavJans » Wed Apr 20, 2016 4:07 pm

InnoDB. So that's not a problem.

I have gotten a result that will work for us, I didn't figure out the problem yet though. I ended up connecting to the db inside the repeat loop instead of before. This way if any query is long enough that I lose connection it doesn't matter. It wont help me pull data into my livecode app, but MySQL will at least dump the data to a file that we can use in excel. Getting data into excel is the end goal anyway.
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här

zaxos
Posts: 222
Joined: Thu May 23, 2013 11:15 pm
Location: Greece

Re: revdberr,Lost connection to MySQL server during query

Post by zaxos » Sun Apr 24, 2016 12:15 pm

Create a stored routine in the database and run that instead. All the processing will happend in the DB instead of Livecode
Knowledge is meant to be shared.

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

Re: revdberr,Lost connection to MySQL server during query

Post by Not a lot of thought » Sun May 28, 2017 11:55 pm

I have similar problems when I try to run queries from MYSQL through livecode using "revExecuteSQL" and "revDataFromQuery". I thought about running the a stored routine as mentioned by zaxos before, but I wasn't sure how to get the information back to livecode once it has run on the database side. I didn't see much on how it worked on my initial search, but my thought process was that livecode sends the command to execute the routine and then it doesn't matter if the connection is lost, the database runs the routine on its end, but I need to pull the information back to print to the programs screen. I'm a bit new to the stored routine/procedure process, so please correct me if I'm wrong.

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

Re: revdberr,Lost connection to MySQL server during query

Post by AxWald » Thu Jun 01, 2017 5:49 pm

Hi,

I have a rather long winded query on a MySQL database that runs each night and stores its data in a temp table, for faster access. The way I do it could be what you need.

The actions starts from Livecode. I send these Statements to the db:

Code: Select all

DROP TABLE IF EXISTS `myTable`;
(if this fails, the table is in use! Try later ...)
and then

Code: Select all

CALL `ax_p_MakeSalesHist`;
where "ax_p_MakeSalesHist" is a stored procedure.

The procedure "ax_p_MakeSalesHist" on the server is like the usual:

Code: Select all

BEGIN
CREATE TABLE `myTable` 
SELECT [ ... ]
FROM [ ... ]
WHERE [ ... ] 
ORDER BY [ ... ];
END
The next day, I just access myTable.

Times ago I had a query that had to yield "immediate" results, but still took about 2 minutes (on the server). I just added a UID row to the CREATE TABLE statement (using an argument), and polled every 5 seconds with a:

Code: Select all

WHERE [ ... ] AND (theUID = myUID)
until the result wasn't empty anymore, and showed a spinning beach ball meanwhile ;-)

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!

Post Reply

Return to “Databases”