PHP 8.1 upstream timed out (110: Unknown error)

Question

I had already a an issue when someone reached the basket on last 2023/03/29, it was a different PHP error which crashed the server. I had to manually restart the php service. Not enough memory:

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/mywebsite/prod/basket/basket_global.inc.php on line 956

So I added some memory to PHP and since yesterday I had no server crash (Nginx / PHP 8.1 / MySQL)!

memory_limit = 256M

Yesterday I had another crash of the server for which I just had to manually restart the php service:

systemctl restart php8.1-fpm.service

And once again, it happened just after someone went to the basket:

access.log:

185.230.yyyy.xxx - - [27/May/2023:02:15:09 +0000] "GET /us/basket HTTP/1.1" 200 10466 "-" "Mozilla/5.0 (X11; U; Linux i686; en-US) AppleWebKit/534.16 (KHTML, like Gecko) Chrome/10.0.648.133 Safari/534.16"

error.log

2023/05/27 02:15:09 [error] 3221563#3221563: *18311176 upstream timed out (110: Unknown error) while reading response header from upstream, client: 185.230.yyyy.xxxx, server: mywebsite.com, request: "GET /us/basket/ HTTP/1.1", upstream: "fastcgi://unix:/run/php/php8.1-fpm.sock", host: "mywebsite.com"

And then only upstream timed out (110: Unknown error) errors on the log file!

It happens randomly and not often, so I think it's because of this function from the basket part with an infinite while loop to generate unique working code:

function get_newMerchantReference() {
    global $config, $dataBase;

    while(1) {
        $merchantReference = mt_rand(100, 999).'-'.mt_rand(100, 999).'-'.mt_rand(100, 999);

        $sql = $dataBase->prepare('SELECT count(*) AS num
                                   FROM oo__basket_infos_hext
                                   WHERE merchant_reference LIKE :reference');
        $sql->execute(array('reference'  => $merchantReference));
        $countUniqueId = $sql->fetch();
        $sql->closeCursor();
        $sql = $dataBase->prepare('SELECT count(*) AS num
                                   FROM oo__order_infos_hext
                                   WHERE merchant_reference LIKE :reference');
        $sql->execute(array('reference'  => $merchantReference));
        $countUniqueId2 = $sql->fetch();
        $sql->closeCursor();
        if($countUniqueId['num'] == 0 && $countUniqueId2['num'] == 0) { break; }
    }
    return $merchantReference;
}

Can this while(1) loop go wrong in a random way and cause the PHP service to crash?

How to modify it to generate random number like 213-126-323 which have not been already used and is stored in oo__basket_infos_hext and oo__order_infos_hext?

Answer 1

If you're generating a random number for a reference, then I'd recommend something significantly less likely to collide, like a UUID v4. The odds of a collision are so low, you wouldn't need a loop to check for existence, you could just safely assume there are no duplicates. (Still, put a unique constraint on the column, though.)

That said, if you're unable to change the format of the reference, you could convert to incremental. Start with 0, use an autoincrement/serial field in your database, and just increment by one for each record. Then format the value with leading zeroes and dashes, so 0 becomes 000-000-000 and so on.

If you're unable to do that, and you need to use this format and have it be random, then you'll need to accept that it's going to get slower over time (depending on volume) and there's nothing you can do about it. There are still some areas you could improve though:

1. Switch from mt_rand() to random_int(), it provides cryptographically secure and uniformly selected output.
2. If the first query returns a row, there's no need to perform the second query, you can just immediately skip to the next iteration because you already know you've got a failure.
3. You're not checking for any errors when querying. prepare() and fetch() might return false.
4. Make sure both merchant_reference columns have unique constraints in the database. This will prevent accidental duplicates.
5. Make sure both merchant_reference columns are indexed in the database. This will make the lookup queries much faster.
6. Put a hard cap on the number of iterations:

$cap = 0;
while (true) {
   if (++$cap > 10) {
       throw new Exception('too many tries');
   }
   query1
   if (query1 finds a result) {
        continue;
   }
   query2
   if (query2 finds a result) {
        continue;
   }
   return $value;
}