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:
> what evidecne do you have that it's even php related?
None. But we only update PHP from 5.6 to 7.2; we don't touch the Kernel nor MariaDB.
In case it is useful:
$ cat /etc/sysctl.conf
# /etc/sysctl.conf
#
# For more information on how this file works, please see
# the manpages sysctl(8) and sysctl.conf(5).
#
# In order for this file to work properly, you must first
# enable 'Sysctl support' in the kernel.
#
# Look in /proc/sys/ for all the things you can setup.
#
# Disables packet forwarding
net.ipv4.ip_forward = 0
# Disables IP dynaddr
#net.ipv4.ip_dynaddr = 0
# Disable ECN
#net.ipv4.tcp_ecn = 0
# Enables source route verification
#net.ipv4.conf.default.rp_filter = 1
# Enable reverse path
#net.ipv4.conf.all.rp_filter = 1
# Enable SYN cookies (yum!)
# http://cr.yp.to/syncookies.html
#net.ipv4.tcp_syncookies = 1
# Enable people in the specified (min, max) group range to send ICMP_ECHO
# messages (i.e. ping) and receive ICMP_ECHOREPLY responses. This allows
# you to run non-suid and non-caps ping, but it also means anyone with
# a gid in this range can send those packets (not just via ping).
#net.ipv4.ping_group_range = 100 100
# Disable source route
#net.ipv4.conf.all.accept_source_route = 0
#net.ipv4.conf.default.accept_source_route = 0
# Disable redirects
#net.ipv4.conf.all.accept_redirects = 0
#net.ipv4.conf.default.accept_redirects = 0
# Disable secure redirects
#net.ipv4.conf.all.secure_redirects = 0
#net.ipv4.conf.default.secure_redirects = 0
# Ignore ICMP broadcasts
#net.ipv4.icmp_echo_ignore_broadcasts = 1
# Disables the magic-sysrq key
#kernel.sysrq = 0
# When the kernel panics, automatically reboot in 3 seconds
#kernel.panic = 3
# Allow for more PIDs (cool factor!); may break some programs
#kernel.pid_max = 999999
# You should compile nfsd into the kernel or add it
# to modules.autoload for this to work properly
# TCP Port for lock manager
#fs.nfs.nlm_tcpport = 0
# UDP Port for lock manager
#fs.nfs.nlm_udpport = 0
Previous Comments:
------------------------------------------------------------------------
[2020-06-21 17:56:50] bugreports2 at gmail dot com
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
------------------------------------------------------------------------
[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