No announcement yet.

Keep Mysql Connection alive!

  • Filter
  • Time
  • Show
Clear All
new posts

  • Keep Mysql Connection alive!

    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.

  • #2
    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.


    • #3
      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);
      The idea is to check the connection every 60 seconds, but I don't know why this wont work :(


      • #4
        Could anybody give a shoot to my problem?! Please!


        • #5
          Originally posted by Nickj View Post
          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.


          • #6
            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
   Is Closed.

            Facebook Page


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


              • #8
                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();
                $arrCol[mysql_field_name($objQuery,$i)] = $obResult[$i];


                echo json_encode($resultArray);


                • #9
                  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 ...


                  • #10
                    I wrote a couple using luasql.mysql

                    eDB OOP:

                    What info are you pulling from db?


                    • #11
                      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();
                      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);
                      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:
                      Click image for larger version

Name:	lost_connection.png
Views:	1
Size:	8.5 KB
ID:	284105

                      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);


                      • #12
                        The trick that I found was this:
                        function GlobalTimer.OnTimer(nID)
                              if nID == 77 then
                                                 if keep_connection_alive == true then
                                                       for id in rows(MySQLConnection, "SELECT FROM users LIMIT 0, 1;") do
                        --------------------------------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.