5 Replies Latest reply: Jun 28, 2012 5:08 PM by cj RSS

    timeout

    user496880
      Hello,

      The setup is Apache, PHP and Oracle 11 via OCI. The client requests a php page, the php logs into the database and starts a PL/SQL procedure, which in turn starts waiting on an AQ. If it gets a message it emits some output, but otherwise it just stays there. The problem is that the client is going to leave (navigate away from the page) and it does not matter how many timeout settings I try to change in Apache, the connections stay there. If I check the server status on Apache I see that the process stays there even after the client is long gone (I talk about hours). Status W.

      I know that I could listen to AQ with nowait and sleep a bit between calls but I would prefer to leave it how it is. Any ideas how to set up a working timeout for this?

      Thanks,

      K
        • 1. Re: timeout
          cj
          Show us some code.

          Are you using persistent PHP connections? DRCP?

          What's your issue with the connection remaining?
          • 2. Re: timeout
            user496880
            My issue is the remaining connection, if we use the server this way it is going to eat up all the connections in an hour.

            We do not use DRCP, maybe we will in a month or two.

            The procedure we call waits for an AQ.

            SID     Seq #     Event Name     Wait Time     Seconds in Wait     State     P1     P1 Text     P2     P2 Text     P3     P3 Text
            80     8037     Streams AQ: waiting for messages in the queue     0     8     WAITING     246223     queue id     957009660     process#     2147483647     wait time

            If you check the wait time you will see that this session has been there for more than a day. The apache status shows the following:

            10-1     26304     8/76/76     W     0.29     80390     0     30.5     0.16     0.16     192.168.0.184     127.0.1.1     GET /something/http/longpull.php HTTP/1.1

            I guess Oracle does not realize that the session is dead, because Apache keeps the connection alive. If I shut down apache the session goes away. We have checked the settings and by this time apache should have timed out (the maximum execution time for PHP is also set). Now that I think about it this is not the right forum to ask, if you know a better one please advise.

            The code:

            <?php
            $input="<request><aukcio_id>30025</aukcio_id></request>";

            $VV_USER[uid]=1;

            $OCI_LOGIN='login';
            $passwd='pwd';
            $db='db';
            $connect=OciLogon($OCI_LOGIN,$passwd,$db,'EE8ISO8859P2');

            $proc=ociparse($connect,"begin package.procedure(:p_user_id,:p_xml_be,:p_xml_ki,:p_hiba); end;");
            ocibindbyname($proc,":p_user_id", $VV_USER[uid]);
            ocibindbyname($proc,":p_xml_be", $input);
            ocibindbyname($proc,":p_xml_ki", $p_valasz, 100000);
            ocibindbyname($proc,":p_hiba", $p_hiba);
            $r=ociexecute($proc,OCI_DEFAULT);
            ocifreestatement($proc);
            if ($p_hiba) {
            echo $p_hiba;
            } else {
            echo $p_valasz;
            }

            OciLogoff($connect);
            ?>
            • 3. Re: timeout
              cj
              The behavior of the connection being closed when Apache is shutdown is the behavior when persistent PHP OCI8 connections, i.e. oci_pconnect(), are used.

              I notice you are using the deprecated PHP 4 OCI8 function aliases. Are you using a recent version of the OCI8 extension? The connection management
              in OCI8 was substantially re-factored post PHP 4.
              • 4. Re: timeout
                user496880
                It has nothing to do with this. The problem is that php maximum execution time on Windows counts against the wall clock, while on Unix it counts only the time the CPU passes with executing php code. So if you spend all your time in a plsql stored procedure php is not going to time out on Unix. There are some modules which could help, but we solved our problem by changing the application logic. At least that is what we have experienced.
                • 5. Re: timeout
                  cj
                  Thanks for posting the solution.

                  And also for providing another data point about the issues of developing PHP on Windows and then deploying on Linux.