CODEWITHSUNDEEP
phpsqljoomlavirtuemart
Michal Nahunek
Michal Nahunek

Reputation: 1

How to fix 504 Gateway Time-out nginx error in sql query?

I am beginner and I am trying update tables in Joomla (3.8) database and I get 504 Gateway Time-out nginx error at the following sql query:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$condition = array(
    $db->quoteName('B.virtuemart_product_id') . ' >= '.$product_id_from,
    $db->quoteName('B.virtuemart_product_id') . ' <= '.$product_id_to);

$query->select(array('B.virtuemart_product_id, A.product_sku, 
A.price_CZK, A.price_EUR'))
      ->from($db->quoteName('#__watrex_price_list_temp', 'A'))
      ->join('INNER' , $db->quoteName('#__virtuemart_products', 'B') . ' 
ON (' . $db->quoteName('B.product_sku') . ' = ' . $db- 
>quoteName('A.product_sku') . ')')
      ->where($condition,'AND');
$db->setQuery($query);
$num_rows = $db->getNumRows();
$results = $db->loadObjectList();
...

Result can contain up to 50000 items. How can I fix this problem? Thank you

Upvotes: 0

Views: 1939

Answers (1)

mickmackusa
mickmackusa

Reputation: 48041

I suspect that getNumRows() is the culprit here. When I run call echo $db->getNumRows() on my localhost with a successful query returning a non-empty result set to replicate the issue, I get:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in C:\wamp64\www\blah\libraries\joomla\database\driver\mysqli.php on line ###

NULL

To fix this, add $db->execute(); on the line before $db->getNumRows() and everything works happily and as desired. That said, I recommend just calling count() or sizeof() on $results because you'll get the same output without having to add the execute() call.

If that isn't the cause, this may or may not be within your control. You may wish to work through this checklist of advice: https://www.lifewire.com/504-gateway-timeout-error-explained-2622941

As for how to process your result set with less memory consumption, you might entertain James Garrett's suggestion.

As for subtle refinements to your query:

  1. Your SELECT clause renders appropriately, but the syntax seems to be designed with the intent to create an array of columns. Truth is, you have a single-element array containing all four columns. This only becomes problematic if you decide to apply quoteName() to the array.

  2. I recommend lowercase table aliases so that they do not "catch the eye" as MySQL keywords. SQL Queries - Paragraph 1

  3. The ON declaration doesn't need to be parenthetically wrapped.

  4. None of your tables or columns actually need quoteName() to be called on them to maintain stability/security. You may choose to omit them to make your code easier to read, but the Joomla coding standards demand 100% employment of the call (I personally dislike this stance). SQL Queries - Paragraph 5

    Table names and table column names should always be enclosed in the quoteName() method to escape the table name and table columns.

  5. It may not aid in performance, but BETWEEN is "inclusive" and is specifically designed to do what your two WHERE conditions require. https://www.techonthenet.com/mysql/between.php

My recommended snippet:

$db = JFactory::getDbo();
$query = $db->getQuery(true)
    ->select($db->quoteName(["b.virtuemart_product_id", "a.product_sku", "a.price_CZK", "a.price_EUR"]))
    ->from($db->quoteName("#__watrex_price_list_temp", "a"))
    ->innerJoin($db->quoteName("#__virtuemart_products", "b") . " ON " . $db->quoteName('b.product_sku') . " = " . $db>quoteName("a.product_sku"))
    ->where($db->quoteName("b.virtuemart_product_id") . " BETWEEN " . (int)$product_id_from . " AND " . (int)$product_id_to);
$db->setQuery($query);
if (!$results = $db->loadObjectList()) {
    echo "No Rows";
} else {
    // if you need to know the count...
    echo count($results);

    // iterate the result set
    foreach ($results as $row) {
        // ... yatta-yatta ...
    }
}

If ALL of the above fails, I recommend a re-think of your project. Perhaps you should be reducing the result set volume with LIMIT and using pagination techniques if necessary.

p.s. Rick James has some excellent advice about adding indexes.

Upvotes: 1

Related Questions