7

My website is using onload AJAX. So when the user entering into a page 6 AJAX calls are executed parallel. In middle of the process if user close the browser or navigate to another page I wants to kill the queries.

Steps to achieve this:

1. Find the Next MySQL query execution ID(The connection identifier) and store it into a session.

http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html

We need to identify this ID before execute the READ(select) query. Because PHP will execute line by line.

Problem

How do we identify the next connection identifier?

OR

How do we reserve the connection identifier and execute the query on specified identifier?

2. Execute the query in database.

3. If user aborted is identified then kill the MySQL query execution. We can detect the user aborted status in PHP using connection_aborted()/ ignore_user_abort() function.

Use this following command to terminate this query execution:

KILL ID

Community
  • 1
  • 1
Sundar
  • 4,580
  • 6
  • 35
  • 61
  • `I wants to kill the queries`. Why? – Bart Friederichs Jan 10 '14 at 12:19
  • What do you mean "next id"? You can only kill queries that are currently executed, not some "future queries". Am I missing something? – Alma Do Jan 10 '14 at 12:19
  • @BartFriederichs The user is moved from one page to another page but in mysql still six queries are in process – Sundar Jan 10 '14 at 12:19
  • @Sundar these are long running queries, that take more than few seconds? –  Jan 10 '14 at 12:20
  • @AlmaDo The next id denotes the future mysql query will execute in that ID – Sundar Jan 10 '14 at 12:20
  • @BartFriederichs the query is running in 5 crore record DB and the process is little heavy – Sundar Jan 10 '14 at 12:21
  • @Sundar then I'm lost. Why? Isn't your task to terminate all queries that were run by current script? (i.e. script that received user abort signal) – Alma Do Jan 10 '14 at 12:21
  • @AlmaDo The task to terminate all queries that were run by current script(6 different ajax calls)? – Sundar Jan 10 '14 at 12:23
  • @Sundar yes. To be more precise, "task to terminate all queries that were done during execution of _this_ script instance" (so for 6 ajax calls there will be 6 processes, obviously) – Alma Do Jan 10 '14 at 12:30
  • Steps to achieve this: to abort some mySQL query from PHP, you need parallel execution in PHP; because otherwise PHP locks until mySQL has completed execution. – Alexander Jan 10 '14 at 13:17
  • @Alexander We can create one more database connection with root level privileged login. So we can parallel the process using AJAX and PHP. Due to PHP lock only I want to get the connection identifier before executing the query – Sundar Jan 10 '14 at 13:20

2 Answers2

3

step 1: Get thread id of the MySql connection 

    $thread_id = mysqli_thread_id($link);

step 2: Use ignore_user_abort(); in code

step 3: Check if connection is closed. If yes then kill the thread as follows:

        if (connection_aborted() && mysqli_kill($connection_link, $thread_id)) {
           die();
        }

Check the accepted solution of this question.

Community
  • 1
  • 1
Satish Gadhave
  • 2,880
  • 3
  • 20
  • 27
1

The following query also returns the current connection identifier

SELECT CONNECTION_ID();

After receiving this connection identifier we can execute our query on this identifier.

Sundar
  • 4,580
  • 6
  • 35
  • 61