Edit report at https://bugs.php.net/bug.php?id=79721&edit=1
ID: 79721
Comment by: cbimax at gmail dot com
Reported by: cbimax at gmail dot com
Summary: Insert queries that take more than a day are dropped
Status: Open
Type: Bug
Package: MySQLi related
Operating System: Linux Gentoo
PHP Version: 7.2.31
Block user comment: N
Private report: N
New Comment:
Can I add a parameter into php.ini to wait more than 24 hours?
Previous Comments:
------------------------------------------------------------------------
[2020-06-21 17:48:54] bugreports2 at gmail dot com
sounds more like whatever optimization just kills the connection afetr 24 hours no single byte was
transferred
------------------------------------------------------------------------
[2020-06-21 17:46:46] cbimax at gmail dot com
Description:
------------
We run extended queries to build OLAP' indexes. On previous version, when we issue:
$statement = "Insert Into summary ( operation, customs, trader, product, country, provenance,
via, fob, freight, cif, quantity, net, gross, movements ) ( Select operation, customs, trader,
product, country, provenance, via, sum( fob ), sum( freight ), sum( cif ), sum( quantity ), sum( net
), sum( gross ), sum( movements ) From summary Group by 1, 2, 3, 4, 5, 6, 7 )";
$connection->query( $statement );
the query could run for a few days (ordinary between 3 to 5 days). After update to PHP 7.2.24 (cli),
the statement continue running on MariaDB but the PHP script throws an error without any
description.
Test script:
---------------
function compute_totals() {
$sql = "Insert Into summary ( operation, customs, trader, product, country, ";
$sql.= "provenance, via, fob, freight, cif, quantity, net, gross, movements ) ";
$sql.= "( Select operation, customs, trader, product, country, ";
$sql.= "provenance, via, sum( fob ), sum( freight ), sum( cif ), ";
$sql.= "sum( quantity ), sum( net ), sum( gross ), sum( movements ) ";
$sql.= "From summary Group by 1, 2, 3, 4, 5, 6, 7 )";
if( !$connection->query( $sql ) )
{
$error = mysqli_error();
echo "Critical error [$error] on compute totals.";
echo "Last SQL instruction: [$sql]";
exit(1);
}
}
We can see thru mytop that the query is up and running but the script stop after exact 1 day:
MariaDB on localhost (10.4.12-MariaDB)
up 1+06:28:32 [14:41:17]
Queries: 216.0 qps: 0 Slow: 2.0 Se/In/Up/De(%): 08/00/00/00
Sorts: 0 qps now: 1 Slow qps: 0.0 Threads: 3 ( 8/ 1) 00/00/00/00
Handler: (R/W/U/D) 0/ 1696/ 0/ 0 Tmp: R/W/U: 104/ 104/ 0
ISAM Key Efficiency: 100.0% Bps in/out: 0.1/ 7.9 Now in/out: 22.7/ 3.7k
Id User Host/IP DB Time % Cmd State Query
-- ---- ------- -- ---- - --- ----- ----------
11 root localhost br 88874 0.0 Query Creating sort i Insert Into summary
( operation, customs, trader, product, country, provenance, via, fob, freight, cif, quantity, net,
gross, movements ) ( Select operation, customs, trader, product, country, provenance, via, sum( fob
), sum( freight ), sum( cif ), sum( quantity ), sum( net ), sum( gross ), sum( movements ) From
summary Group by 1, 2, 3, 4, 5, 6, 7 )
And our log reports didn't show any error informed by mysqli_error:
[23110] 2020-06-21 14:00:03 Critical error [] on compute_totals()
[23110] 2020-06-21 14:00:03 Last SQL instruction: [Insert Into summary ( operation, customs, trader,
product, country, provenance, via, fob, freight, cif, quantity, net, gross, movements ) ( Select
operation, customs, trader, product, country, provenance, via, sum( fob ), sum( freight ), sum( cif
), sum( quantity ), sum( net ), sum( gross ), sum( movements ) From summary Group by 1, 2, 3, 4, 5,
6, 7 )]
Expected result:
----------------
$connection->query( $statement ), should wait up to the query is finished.
Actual result:
--------------
$connection->query( $statement ), drops after exact 1 day of waiting the MariaDB query to
complete.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=79721&edit=1