revdberr,Lost connection to MySQL server during query
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
revdberr,Lost connection to MySQL server during query
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?
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
Re: revdberr,Lost connection to MySQL server during query
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: revdberr,Lost connection to MySQL server during query
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
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
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
"Det bästa stället att hitta en hjälpande hand är i slutet av din egen arm" förutom här
Re: revdberr,Lost connection to MySQL server during query
Did you try with revQueryDatabase?
For example:
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: revdberr,Lost connection to MySQL server during query
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.
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
Re: revdberr,Lost connection to MySQL server during query
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'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
Re: revdberr,Lost connection to MySQL server during query
I would use this SQL syntax: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.
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: revdberr,Lost connection to MySQL server during query
Hi,
just hope your tables are not MyISAM - no support for transactions ...
just hope your tables are not MyISAM - no support for transactions ...
Have fun!The major deficiency of MyISAM is the absence of transactions support. Also, foreign keys are not supported.
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!
Re: revdberr,Lost connection to MySQL server during query
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.
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
Re: revdberr,Lost connection to MySQL server during query
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.
-
- Posts: 77
- Joined: Thu May 21, 2015 2:41 am
Re: revdberr,Lost connection to MySQL server during query
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.
Re: revdberr,Lost connection to MySQL server during query
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:(if this fails, the table is in use! Try later ...)
and then where "ax_p_MakeSalesHist" is a stored procedure.
The procedure "ax_p_MakeSalesHist" on the server is like the usual: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: until the result wasn't empty anymore, and showed a spinning beach ball meanwhile ;-)
Have fun!
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`;
and then
Code: Select all
CALL `ax_p_MakeSalesHist`;
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
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)
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!
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!