Keep Mysql Connection alive!

[claus707]

Hello,
I am building a application that interacts with a mysql database that is stored on a web server. The problem is that when the program is let inactive for some time the connection is lost and if you try to use it it gives error.
I am using the default mysql plugin that comes with AMS8.

Could someone help me?!
Note: I know that is another free plugin to access mysql databases, but the issue is that I have wrote too much code in the build mysql connector.

[Nickj]

You shopuld be able to set the timeout in your connection string.
The command timeout would need to be set at the server end to match.

[claus707]

I have also tried to use global timer, but with no result

function GlobalTimer.OnTimer(nID)
      if nID == 77 then
            if not MySQLConnection then 
                  MySQLConnection = MySQL:connect(database, username, password, host, port);
                  error = Application.GetLastError();
                     if (error ~= 0) then
	                     Dialog.Message("Gabim ne lidhjen me databazen!", _tblErrorMessages[error], MB_OK, MB_ICONEXCLAMATION);
	                     DialogEx.Show("database_settings");
	                     Application.ExitScript();
                     end
            end
      end
end

The idea is to check the connection every 60 seconds, but I don't know why this wont work :(

[claus707]

Could anybody give a shoot to my problem?! Please!

[Nickj]

The command timeout would need to be set at the server end to match.

Go read about MySQL and the 'COMMAND TIMEOUT'
Checking the connection isn't going to do much to help you if the db has closed down that connection and is waiting for you to establish a new one because the timout at the db is not as long as you want it to be.

[reteset]

creating a persisten database connection is not recomended and and its stability is not guaranteed

you should make a connection only when you need it and you should destroy connection when you no longer need it

calling connect function multiple times will not hurt database server/provider

if you want to go with a single database handle then , you can call connect as you did at first time but without closing it

and if there is already a connection then , driver should return an error code or something like that "already connected" etc..

so this means you have already connected , it will create a new connection otherwise

default connection timeout is 10 minutes but even in this period you might lose connection ,for several reasons
because your app runs on a user computer ,conditions can not be compared with a fully configured web server

[webultra]

As AMS is unsafe, you should try to make a restful (CRUD) application fetch records in json, otherwise your user and password could be read from your app.

[jakabo]

Is this safe to use with AMS and php ?
this php page brings all the records but can anyone see this deatils ?

<?
$objConnect = mysql_connect("host","user","pass");
$objDB = mysql_select_db("accounts");

$strKeyword = $_POST["txtKeyword"];
$strSQL = "SELECT * FROM users WHERE name LIKE '%".$strKeyword."%' ";

$objQuery = mysql_query($strSQL);
$intNumField = mysql_num_fields($objQuery);
$resultArray = array();
while($obResult = mysql_fetch_array($objQuery))
{
$arrCol = array();
for($i=0;$i<$intNumField;$i++)
{
$arrCol[mysql_field_name($objQuery,$i)] = $obResult[$i];
}
array_push($resultArray,$arrCol);
}

mysql_close($objConnect);

echo json_encode($resultArray);
?>

[claus707]

Thank you to everyone!
In particular to reteset.
The point of the function that I have created was to check somehow if the connection was still open...
Maybe there is another way to do that. Because in my case the app fills with info 4 combo boxes and I should create and close the connection 4 times :/ The case is complicated but a nicer thing would be somehow to check the condition of the connection before executing a query ...

[webultra]

I wrote a couple using luasql.mysql

eDB: http://pastebin.com/a0zRNpEq
eDB OOP: http://pastebin.com/STr8K6L0

What info are you pulling from db?

[claus707]

I am using the example that comes with AMS8, for basic commands SELECT, INSERT and DELETE.
This function helps the program to executes the queries.

function rows (connection, sql_statement)
   local cursor = assert(connection:execute (sql_statement));
   return function ()
        return cursor:fetch();
    end
end

And this script to execute the select query something like this

local s = "";
for name, email in rows(MySQLConnection, "select * from contacts;") do
    s = s .. string.format("Name: %s, Email: %s\n", name, email);
end

After not using any query the connection to the database is lost and if you try to execute a query the program gives this error:


My idea was how to add a code that checks if the connection is still alive and if it is not then run the command:
MySQLConnection = MySQL:connect(database, username, password, host, port);

[claus707]

The trick that I found was this:

--------------------------------TIMER
function GlobalTimer.OnTimer(nID)
      if nID == 77 then
                         if keep_connection_alive == true then
                               for id in rows(MySQLConnection, "SELECT users.id FROM users LIMIT 0, 1;") do
                               end
                         end
      end
end
--------------------------------END TIMER

Before the execution of any query I set the keep_connection_alive = false
and after executing every query I set keep_connection_alive = true
The timer is fired every one minute and execute a query that does not slow down the application or the mysql server.

Maybe this is not the right way, but in my case I was obliged to use this.