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: > But we only update PHP from 5.6 to 7.2; we don't touch the Kernel nor MariaDB and your stineedge old build used mysqlnd or libmysql and what is your current using? Previous Comments: ------------------------------------------------------------------------ [2020-06-21 18:06:15] cbimax at gmail dot com > 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 ------------------------------------------------------------------------ [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