How to keep a php script from timing out because of a long mysql query

Question

I have an update query being run by a cron task that's timing out. The query takes, on average, five minutes to execute when executed in navicat.

The code looks roughly like this. It's quite simple:

// $db is a mysqli link
set_time_limit (0); // should keep the script from timing out
$query = "SLOW QUERY";
$result = $db->query($query);
if (!$result)
    echo "error";

Even though the script shouldn't timeout, the time spent waiting on the sql call still seems to be subject to a timeout.

Is there an asynchronous call that can be used? Or adjust the timeout?

Is the timeout different because it's being called from the command line rather than through Apache?

Thanks

Answer 1

I had the same problem somwhere, and "solved" it with the following code (first two lines of my file):

set_time_limit(0);
ignore_user_abort(1);

Answer 2

According to the manual:

Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running.

So it's unlikely to have anything to do with PHP's time limit. What message are you getting when it times out? Perhaps there's a MySQL setting involved.

Answer 3

Is your php running in safe-mode? Quote from PHP manual of set_time_limit:

This function has no effect when PHP is running in safe mode. There is no workaround other than turning off safe mode or changing the time limit in the php.ini.

Answer 4

I came across something similar in one of my PHP scripts, I added this inline right before executing the slow query:

$timeout_seconds = 3153600; // 1 year... 

// Make sure the PHP script doesn't time out
set_time_limit(0);
ignore_user_abort(1);

// Make sure the PHP socket doesn't time out
ini_set('default_socket_timeout', $timeout_seconds);
ini_set('mysqlnd.net_read_timeout', $timeout_seconds);

// Make sure the MySQL server doesn't time out
// Assuming your $link is a MySQLi object:
$link->query("SET SESSION connect_timeout=" . $timeout_seconds);
$link->query("SET SESSION delayed_insert_timeout=" . $timeout_seconds);
$link->query("SET SESSION have_statement_timeout='NO'");
$link->query("SET SESSION net_read_timeout=" . $timeout_seconds);
$link->query("SET SESSION net_write_timeout=" . $timeout_seconds);

Obviously, you'll want to set the seconds appropriately, but I didn't want my script to time out at all.

PHP Init directives: https://www.php.net/manual/en/ini.list.php

MySQL Server variables: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

Note: Check the documents for more information, and also verify the variables match the PHP and MySQL versions that you are using. I'm using PHP 7.3 and MySQL 5.7.

** Edit: Setting the PHP timeouts wasn't enough for my script, I had to add the MySQL SESSION variables too.

Answer 5

Assuming you are on linux, Debian based systems have separate configurations for mod_php/php cgi and php-cli. This shouldn't be too difficult to set up on a different linux system that doesn't separate cgi/cli configuration.

Once you have separate configs, I would adjust your php cli configuration. Disable safe mode and any time limits and ram limits.

Answer 6

Check out some of the resource limit variables in php,ini: max_execution_time, max_input_time, memory_limit

You could also set a time limit for the script in PHP itself: https://www.php.net/set_time_limit