MYSQL close connection cannot stop insert work
<?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>
The website has two buttons, an insert to re-insert a recording and a stop to insert. However, I tried using a MySQL close connection, it stops functioning in PHP, but the activity still remains. The insert is still working. How to solve a problem? Thank.
source to share
Since PHP does not allow you to share resources between requests (AFAIK), you can achieve this capability to stop the process in another request:
first add this line:
if ( isset ($_POST['start']) ) {
$_SESSION['continue'] = true;
and then replace your for while statement (you seem to care about the number of iterations if you can change this behavior):
for ($i = 1; $i <= 99999999999; $i++) {
replaced by:
while ( $_SESSION['continue'] )
and instead of closing the connection to stop the insert, do this:
mysql_close ($con)
will be used:
$_SESSION['continue'] = false;
NOTE. Remember to call session_start () at the beginning of your script. That's all.
I assumed you want to stop the page from the same session, if that is not the case, you can use ACP ore SHM instead. eg:
replace:
$_SESSION['continue'] = true
from:
apc_store( 'continue', true );
and
while ( $_SESSION['continue'] )
from:
while ( acp_fetch ('continue') )
and
$_SESSION['continue'] = false;
from:
apc_store( 'continue', false );
Done!
source to share
Each answer on this page bypasses one point:
- share something between multiple HTTP handlers http reqests
And of course the main problem is that php is not to exchange anything .
So, some suggest using sessions, which can be quite tricky to do since sessions are not re-read before closing and you can easily overwrite data in the session. Others offer shared memory or APC.
We are talking about some kind of external semaphore . And of course the solution is not to close the connection, but to break the loop into an external signal.
But you already have one big, simple, maybe too obvious exchange system. Database connection .
Just go to the database for cycle start instructions.
Databases usually provide advisory locks for this task. For example using the GET_LOCK and RELEASE_LOCK instructions. Using IS_FREE_LOCK on each iteration of your main insert loop and this locking with a stop action would be one way to do it. With the problem of releasing the lock, perhaps after another signal from all recorded insertion cycles or after a while. But you can also manage your own table system.
One "advantage" of advisory locking systems is that they ignore transactions. Using transactions, you will need to ensure that records from other transactions are available in your scope if you are creating your own table-based system (ok if not a serializable transaction).
Using a database to exchange information between PHP instances is usually the default situation in LAMP, and you are guaranteed to get this tool in your insert situation, which is not the case with APC.
source to share
Each PHP script call opens its own connection to MySQL and they cannot interfere with each other. An exception is connections opened with mysql_pconnect()
: they remain open and will be shared, but they also cannot be closed with mysql_close ().
Conclusion: What you are trying to do will not work (and this is a bad idea).
Instead, you most likely want to create a background job for your inserts and check for the interrupt message there (this can be as simple as an empty file .STOP[PID]
with [PID]
= process id).
UPDATE:
Can APC be used?
Yes, you can use APC for this message, provided the APC extension is installed. It is not, so this error:
Calling 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 doesn't work then you need to install PECL installer first, see here: sudo pecl install apc returns error
source to share
Here's the 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>
source to share
As recommended:
-
in your current code, you are actually closing another connection (on a branch
if(isset($_POST['stop']))
) -
you cannot exchange MySQL connections via PHP scripts as you cannot store resources in a session
However, you can keep the connection id and kill that connection from another script:
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(isset($_POST['start'])) {
// store current connection ID
// store an array of connection ID 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']
}
KILL [connection_id]
You can use instead KILL QUERY [connection_id]
. This will stop any request in progress, but it won't close the connection (but in your case, you probably want to close the connection at all).
source to share