Bug #79721 [Com]: Insert queries that take more than a day are dropped

Edit report at https://bugs.php.net/bug.php?id=79721&edit=1 ID: 79721 Comment by: bugreports2 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: what evidecne do you have that it's even php related? in my setups netfilter conntrack woud kill it long before net.netfilter.nf_conntrack_tcp_timeout_established = 7200 when a single query takes longer than a day fix the root cause hard_timeout => 2 => 2 Read timeout => 60 default_socket_timeout => 10 => 10 Previous Comments: ------------------------------------------------------------------------ [2020-06-21 17:52:59] cbimax at gmail dot com Can I add a parameter into php.ini to wait more than 24 hours? ------------------------------------------------------------------------ [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