MYSQL close connect can not halt the insert activity

Question

<?php
header('Content-Type: text/html; charset=utf8');
$dbhost = '192.168.1.23';
$dbuser = 'demouser01';
$dbpass = 'demo*PW';
$dbname = 'testdb1';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL connection');
mysql_select_db($dbname);
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER_SET_CLIENT='utf8'");
mysql_query("SET CHARACTER_SET_RESULTS='utf8'");
if(isset($_POST['start'])) {
  $chi = 'chiiiii';
  $id = 1;
  for ($i = 1; $i <= 99999999999; $i++) {
    $eng = substr(str_shuffle(str_repeat("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ", 5)), 0, 1024);
    $math = substr(str_shuffle(str_repeat("1234567890", 6)), 0, 16);
    $sql = "INSERT INTO test_tb (id,chi,eng,num) VALUES (".$id.",'".$chi."','".$eng."',".$math.")";
    $result = mysql_query($sql) or die('MySQL query error');
    $id++;
  }
  $id = 0;
  sleep(60);
  mysql_close($conn);
} else if(isset($_POST['stop'])) {
  mysql_close($conn);
}
?>
<form action="<?=$_SERVER['PHP_SELF'];?>" method="post">
  <input type="submit" name="start" value="Start Insert">
  <input type="submit" name="stop" value="Stop Insert">
</form>

There are two buttons in the website, insert to insert a record repeatedly, and stop to halt the insert. However, I tried using the MySQL close connection, it will stop functioning in PHP, but the activity still remains there. The insert still keeps performing. How to fix the problem? Thanks.

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — h2ooooooo, Feb 15 '13 at 10:39
..that said, why are you trying to insert `99,999,999,999` records into the database? Your main problem is that the `$conn` that is refered to in the stop command is not the same `$conn` that is started in the other command as they're two different requests. You **might** be able to save the handle (`$conn`) in a `$_SESSION` variable and then refer to it. — h2ooooooo, Feb 15 '13 at 10:40
You can't stop execution using `mysql_close()`. For that you have to kill the process. Refer this link for more info http://stackoverflow.com/questions/3787651/how-can-i-stop-a-running-mysql-query — Yogesh Suthar, Feb 15 '13 at 10:43
Every new request to the script will spawn a new MySQL connection, so you cannot close another instance's connection. — Cobra_Fast, Feb 15 '13 at 10:44
@h2ooooooo - Resources like a connection handle can't be serialized or stored in session — Mark Baker, Feb 15 '13 at 10:44
That's true I tried to echo the $conn and it return resource id #2 , which the id #1 is probably the insert request — user782104, Feb 15 '13 at 10:46
whoa whoa query in loop **`for ($i = 1; $i <= 99999999999; $i++) {`** ?? according to that loop looks like you have plan to kill your server in one query .... — NullPoiиteя, Feb 15 '13 at 10:46
@user782104 - Resource identifiers like #1 and #2 are within the process; and as the insert is running in a separate process its resource identifier numbers are completely independent of the process in which you echoud a resource — Mark Baker, Feb 15 '13 at 11:03
You got "Call to undefined function acp_fetch()" because you need PECL PHP extension installed. I posted new solution with SHM which is compiled by default with most recent PHP binaries. — Boynux, Feb 18 '13 at 08:24
I updated [my answer](http://stackoverflow.com/a/14893019/664108) regarding your APC question — Fabian Schmengler, Feb 18 '13 at 23:10

score 2 · Accepted Answer · answered Feb 15 '13 at 11:58

As PHP does not allow you to share resources between requestes (AFAIK) there are a few ways you can achieve this ability to stop a process in another request:

first add this line:

if ( isset ($_POST['start']) ) {
    $_SESSION['continue'] = true;

and then replace your for statement with while (It seems you don care about number of itterations, if so you can chage this behaviour):

for ($i = 1; $i <= 99999999999; $i++) {

replace with:

while ( $_SESSION['continue'] )

and instead of closing connection to stop insertion do this:

mysql_close ($con)

will chnage to:

$_SESSION['continue'] = false;

NOTE: Do not forget to call session_start () at beginning of your script. Thats all.

I assumed that you want to stop page from the same session if this is not the case you can use ACP ore SHM instead. for example:

replace:

$_SESSION['continue'] = true

with:

apc_store( 'continue', true );

and

while ( $_SESSION['continue'] )

with:

while ( acp_fetch ('continue') )

and

$_SESSION['continue'] = false;

with:

apc_store( 'continue', false );

Done!

Sessions will not work, unless you want to open and close the session file in each loop iteration of the worker script (if that's even possible) — Fabian Schmengler, Feb 15 '13 at 13:04
@fab, Yes, you are right! thank you. I just missed that post. So the best way is to use APC or SHM. — Boynux, Feb 15 '13 at 20:42
@Boynux and later readers. I feel the best and simplier way is instead to use the database. — regilero, Mar 01 '13 at 17:44
This also could be a solution. I leave it to readers and context of program. — Boynux, Mar 01 '13 at 18:48

score 2 · Answer 2 · answered Feb 19 '13 at 17:55

Every answer in this page is turning around one point:

share something between several PHP http reqests handlers

And of course the main problem is that php is a share nothing thing.

So some suggest using sessions, which may be quite hard to do as sessions are not re-read before close and you may overwrite data in session quite easily. Others suggest shared memory or APC.

We're talking of some sort of external semaphore. And of course the solution is not to close the connection but to break the loop on an external signal.

But you already have one big, easy, maybe too obvious share system. Your database connection.

Simply check in the database for an early end of loop instructions.

Databases usually provides advisory locks systems for this task. For example using GET_LOCK and RELEASE_LOCK instructions. Using IS_FREE_LOCK in each iteration of your main insert loop and taking this lock with the stop action would be one way to do it. With the problem of releasing the lock, maybe after another signal from all recorded insert loops, or after a while. But you could also manage your own table based system.

One 'advantage' of the advisory locks systems is that they ignore transactions. Using transactions you will have to ensure writes from others transactions are available in your scope if you build your own table based system (ok if not a serializable transaction).

Using the database to share informations between PHP instances is usually the default situation in LAMP, and you are guaranteed to have this tool on your insert situation, which is not the case for APC.

score 1 · Answer 3 · edited May 23 '17 at 10:24

Each invocation of a PHP script opens its own connection to MySQL and they cannot interfere with each other. An exception are connections opened with mysql_pconnect(): they stay open and will be shared, but also can not be closed with mysql_close().

Conclusion: What you are trying to do will not work (and is a bad idea anyway).

What you probably want instead, is creating a background task for your insertions and check for an interruption message there (which could be as simple as an empty file .STOP[PID] with [PID] = the process id).

UPDATE:

Can I not to use APC ?

Yes, you could use APC for this message, provided that the APC extension is installed. This is not the case, hence this error:

Call to undefined function acp_fetch() in /home/ext/library/public/testdb/test.php on line 31

Do you have root access to the server? Then you can install the extension yourself:

sudo pecl install apc

If this command does not work, you need to get the PECL installer first, see here: sudo pecl install apc returns error

Boynux · Answer 4 · 2013-02-18T06:18:25.683

Here is a complete solution:

<?php
    header('Content-Type: text/html; charset=utf8');

    $dbhost = '192.168.1.23';
    $dbuser = 'demouser01';
    $dbpass = 'demo*PW';
    $dbname = 'testdb1';

    $SHMKEY = ftok ( __FILE__, 'a');
    $SHMID  = shm_attach ( $SHMKEY );

    if ( !shm_has_var ( $SHMID, 1 ) ) {
      shm_put_var ( $SHMID, 1, true );
    }

    $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error with MySQL connection'); 
    mysql_select_db($dbname);

    mysql_query("SET NAMES 'utf8'");
    mysql_query("SET CHARACTER_SET_CLIENT='utf8'");
    mysql_query("SET CHARACTER_SET_RESULTS='utf8'");

    if(isset($_POST['start'])) { 
      $chi = 'chiiiii';

      $id = 1;

      // for ($i = 1; $i <= 99999999999; $i++) {
      while ( shm_get_var ( $SHMID, 1) ) { 

        $eng = substr(str_shuffle(str_repeat("abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ", 5)), 0, 1024);

        $math = substr(str_shuffle(str_repeat("1234567890", 6)), 0, 16);
        $sql = "INSERT INTO test_tb (id,chi,eng,num) VALUES (".$id.",'".$chi."','".$eng."',".$math.")";
        $result = mysql_query($sql) or die('MySQL query error');
        $id++;
      }

      $id = 0;
      shm_detach ( $SHMID );
      sleep(60);

      mysql_close($conn);
    }

  else if(isset($_POST['stop'])) {
    shm_put_var ( $SHMID, 1, false );
    // mysql_close($conn);
  }
?>

<form action="<?=$_SERVER['PHP_SELF'];?>" method="post">
  <input type="submit" name="start" value="Start Insert">
  <input type="submit" name="stop" value="Stop Insert">
</form>

You may want to use `$SHMKEY = ftok ( __FILE__, session_id() );` instead ,in order to isolate sessions. — RandomSeed, Feb 19 '13 at 11:14
Yes, It works with different sessions, I mean you can start it with one session and kill it from different session. — Boynux, Feb 19 '13 at 15:09

RandomSeed · Answer 5 · 2013-02-18T20:41:24.570

As it has been advised:

in your current code, you are actually closing a different connection (in the if(isset($_POST['stop'])) branch)
you cannot share connections to MySQL across PHP scripts, since you cannot store ressources in session

You may, however, store the connection id, and kill this connection from another script:

$conn = mysqli_connect($dbhost, $dbuser, $dbpass);

if(isset($_POST['start'])) {

    // store current connection ID
    // store an array of connection ID's in case several connections may run simultaneously
    $result = mysqli_query($conn, 'SELECT CONNECTION_ID()');
    $row = mysql_fetch_row($result);
    $_SESSION['connection_id'] = $row[0];

    // close session file
    // or else concurrent scripts using the same session will be blocked
    session_write_close();

    // do actual work here
    sleep(10);

    // keep in mind the connection may be killed from another script at any time, e.g.
    if (mysqli_query("...") == false) {
        // connection was probably killed
    }

    // reopen session (optional, do it only if you need your session data later)
    session_start();

} else if(isset($_POST['stop'])) {

    // kill pending connection(s)
    mysqli_kill($conn, $_SESSION['connection_id']);
    // equivalent query in pure (My)SQL : 'KILL ' . $_SESSION['connection_id']        
}

Instead of KILL [connection_id], you could use KILL QUERY [connection_id]. This will terminate any running query but will not close the connection (but in your case, you probably want to close the connection altogether).

MYSQL close connect can not halt the insert activity

5 Answers5

Linked