From: cbimax at gmail dot com
Operating system: Linux Gentoo
PHP version: 7.2.31
Package: MySQLi related
Bug Type: Bug
Bug description:Insert queries that take more than a day are dropped
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 bug report at https://bugs.php.net/bug.php?id=79721&edit=1
--
Fix committed: https://bugs.php.net/fix.php?id=79721&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=79721&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=79721&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=79721&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=79721&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=79721&r=support
Expected behavior: https://bugs.php.net/fix.php?id=79721&r=notwrong
Not enough info: https://bugs.php.net/fix.php?id=79721&r=notenoughinfo
Submitted twice: https://bugs.php.net/fix.php?id=79721&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=79721&r=globals
PHP version support discontinued: https://bugs.php.net/fix.php?id=79721&r=phptooold
Daylight Savings: https://bugs.php.net/fix.php?id=79721&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=79721&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=79721&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=79721&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=79721&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=79721&r=mysqlcfg