php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79721 Insert queries that take more than a day are dropped
Submitted: 2020-06-21 17:46 UTC Modified: 2020-06-21 19:10 UTC
From: cbimax at gmail dot com Assigned:
Status: Open Package: MySQLi related
PHP Version: 7.2.31 OS: Linux Gentoo
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: cbimax at gmail dot com
New email:
PHP Version: OS:

 

 [2020-06-21 17:46 UTC] 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.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-06-21 17:48 UTC] 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:52 UTC] cbimax at gmail dot com
Can I add a parameter into php.ini to wait more than 24 hours?
 [2020-06-21 17:56 UTC] 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 18:06 UTC] 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 18:08 UTC] bugreports2 at gmail dot com
> 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?
 [2020-06-21 18:12 UTC] cbimax at gmail dot com
$ php -m    
[PHP Modules]
bz2
Core
ctype
curl
date
dba
dom
fileinfo
filter
gettext
gmp
hash
iconv
json
libxml
mbstring
mysqli
mysqlnd
openssl
pcre
PDO
pdo_mysql
Phar
posix
readline
Reflection
session
SimpleXML
soap
sockets
SPL
standard
tokenizer
xapian
xdebug
xml
Zend OPcache
zlib

[Zend Modules]
Xdebug
Zend OPcache
 [2020-06-21 18:14 UTC] bugreports2 at gmail dot com
mysqlnd - and your previous build?
 [2020-06-21 18:18 UTC] cbimax at gmail dot com
I believe didn't understand your question.

If it is useful we have been working under php-5.6 with mysqli interface.
 [2020-06-21 18:23 UTC] bugreports2 at gmail dot com
what about type mysqlnd in any random source engine given that mysqli can use mysqlnd or libmysql behind the scenes for a dull decade? i won't even consider to write a Bugreport at all in case whatever action is not finished within 24 hours - in that timeframe one could move a whole infrastructure including data to new hardware without any service interruption
 [2020-06-21 18:27 UTC] cbimax at gmail dot com
Sorry, I didn't quite follow you... perhaps 'cos English isn't my native language.

What are you suggesting then? What interface should we use in order to query MariaDB and wait a dull week for its completeness?
 [2020-06-21 18:37 UTC] bugreports2 at gmail dot com
the point is that you need to find out the differences of your builds to write a qualified bug report, coming out 2020 with a direct jump from a for years outdated version to a another no longer supported version is a joke

what about fix the root cause?
there is nothing I could imagine taking longer than 24 hours
 [2020-06-21 18:42 UTC] cbimax at gmail dot com
Sorry, I didn't known PHP 7.2 isn't no longer supported. We used it because xapian-bindings doesn't run yet on 7.4 on Gentoo.

Do you believe that PHP 7.4 will drop the query if it's longer more than one day?
 [2020-06-21 18:50 UTC] bugreports2 at gmail dot com
https://www.php.net/supported-versions.php

php 7.2 is out of support for 7 months now, anyways fix the root cause of a simple query running for days
 [2020-06-21 18:55 UTC] cbimax at gmail dot com
> what about fix the root cause?
> there is nothing I could imagine taking 
> longer than 24 hours

  There is nothing to fix from this side. A common OLAP index caché of 25 years take a few days to compute.

  If you can't imagine it, well, that another issue.

  I'll try to answer again: does PHP 7.4 drop the query if its longer than a day?
 [2020-06-21 18:59 UTC] bugreports2 at gmail dot com
nothing takes a day, however try it out! switch php builds takes the blink of an eye and when you are at it try out 7.0 and 7.1 too and especially find out if your precious binary used libmysql instead of mysqlnd
 [2020-06-21 19:03 UTC] cbimax at gmail dot com
Well, something has changed without any doubt... people politeness has dropped as valid queries!
 [2020-06-21 19:08 UTC] bugreports2 at gmail dot com
what do you expect? you jump from php5 to 7.2,skip 7.0 and 7.1 while you can't even say which database driver your old build used nor can you provide a reproducer

in a proper setup with error_reporting E_ALL you would get warnings in your log fwiw
 [2020-06-21 19:10 UTC] nikic@php.net
There is a mysqlnd.net_read_timeout option which defaults to 86400 (aka one day). Assuming you left this at the default, that should be the culprit.

If you did not change any php.ini settings, then the reason why this timeout may only be taking effect now might indeed be a switch from libmysql to mysqlnd, as suggested by the rude person.

So, adjusting this ini setting should resolve your issue.
 [2020-06-21 19:17 UTC] cbimax at gmail dot com
Thank you nikic at php.net !!

I'll try this ASAP and inform you (when we reprocess the longer query!)

With best regards,


cbi
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Mon Aug 10 19:01:25 2020 UTC