Блог

Я пытаюсь перенести веб-приложение PHP/MySQL 8-летней давности в более поздний стек серверов, используя MariaDB вместо MySQL. Я обнаружил, что невозможно запустить более одной хранимой процедуры в одном и том же соединении из-за ошибки «пакеты не в порядке». Ниже приведен код, который должен работать, но не работает. Может ли кто-нибудь указать, где я, возможно, сбился с пути или каков был бы успешный альтернативный подход?

 <?php

$host = "localhost";
$user = "mysqli_test";
$password = "";
$database = "mysqli_test";

function get_connection()
{
   GLOBAL $host, $user, $password, $database;
   $connection = new mysqli($host, $user, $password, $database);
   if (! $connection || mysqli_connect_errno() )
     printf("Connection failure: %s.n", mysqli_connect_error());

   return $connection;
}

// Minimum viable function: isolate necessary steps.
function get_person($connection, $first_name)
{
   $query = "CALL Get_By_First_Name(?)";
   if (($stmt = $connection->prepare($query)))  // error here after first pass
   {
      $stmt->bind_param('s', $first_name);
      if ($stmt->execute())
      {
         $stmt->store_result();
         $stmt->bind_result($id, $fname, $lname, $pets);

         while($stmt->fetch())
            printf("=  s  s -.n", $id, $fname, $lname, $pets);

         $stmt->free_result();
         while ($stmt->next_result())  // my suspected culprit
         {
            $stmt->store_result();
            while ($stmt->fetch())
               ;
            $stmt->free_result();
         }
      }

      $stmt->close();
   }
}

if ($conn = get_connection())
{
   get_person($conn, "Samuel");  // it works the first time
   get_person($conn, "Zelda");   // this time it fails
}

?>
 

Запуск кода aPHP/mysqli,
почти идентичный код C с использованием API C работает нормально, поэтому я могу выделить, где, по моему мнению, начинается проблема: с помощью функции next_result (). В коде C функция next_result() возвращает значение TRUE, указывающее, что новый результат был доступен после использования подготовленной инструкции для запуска хранимой процедуры. В коде PHP/mysqli функция next_result() возвращает значение false и фактически не дает нового результата, даже если я игнорирую возвращаемое значение false.

Я создал репозиторий github, который включает в себя дополнительные объяснения и сценарии, которые могут запускаться на вашем компьютере для репликации ошибки, если кому-то интересно.

Лучше всего избегать использования хранимых процедур из PHP… Это не всегда возможно; иногда хранимые процедуры необходимы, и они могут даже быть полезны в редких случаях. Но если вы можете, попробуйте перенести логику в приложение PHP, а не хранить ее на сервере MySQL. Таким образом, это гораздо менее громоздко.

Если вы хотите знать, как правильно вызывать хранимые процедуры, лучший ресурс, к которому можно обратиться, — это руководство по PHP. Недавно я улучшил большинство примеров в руководстве, поэтому знаю, что приведенные там примеры отражают лучшие практики и действительно работают. Прочитайте хранимые процедуры с помощью mysqli и mysqli::multi_query() документации.

Я бы посоветовал избегать mysqli::multi_query() , несмотря на то, что хранимые процедуры, вероятно, являются основной причиной существования этой функции. Вы сделали правильный выбор в пользу использования подготовленных операторов, чтобы связать параметры и избежать внедрения SQL.

Главное, что вы должны помнить, — это то, что CALL() утверждение приводит к пустому результату. Если хранимая процедура также создает результирующий набор/наборы, вам нужно выполнить их итерацию и извлечь каждый из них. Проблема с хранимыми процедурами заключается в том, что вы никогда не можете быть уверены, сколько наборов результатов будет создано. Как вы можете справиться с неизвестным?

Взгляните на этот пример (это ваш код, но я внес некоторые изменения и включил отчеты об ошибках).:

 function get_connection($host, $user, $password, $database): mysqli
{
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    return new mysqli($host, $user, $password, $database);
    // you might want to set the correct charset (e.g. utf8mb4) here before returning
}

function get_person(mysqli $connection, $first_name): mysqli_result
{
    $query = "CALL Get_By_First_Name(?)";
    $stmt = $connection->prepare($query);
    $stmt->bind_param('s', $first_name);
    $stmt->execute();

    /* We expect this SP to return the main result set,
        which we want to return and then an empty result for CALL.
        Get the result here and return immediately.
        The prepared statement will be closed automatically once
        we leave the scope and this will clean up the remaining result set.
    */
    return $stmt->get_result();
}

$conn = get_connection($host, $user, $password, $database);
$res1 = get_person($conn, "Samuel");
$res2 = get_person($conn, "Zelda");

var_dump($res1->fetch_all(), $res2->fetch_all());
 

В приведенном выше коде я предполагаю, что вызываемый мной SP вернет только два набора результатов. Мне нужен только первый, и меня не волнует результат CALL() . Второй результирующий набор отбрасывается при очистке инструкции. Если бы это было не так, мне нужно было бы звонить mysqli_stmt::next_result() до тех пор, пока не будут получены все результирующие наборы (не mysqli::next_result() !). Это самый простой способ справиться с громоздкими хранимыми процедурами.

Даже если вы преобразуете код в PDO, это все равно будет самый простой способ сделать это. С mysqli все может стать очень сложным, если вы переборщите, поэтому будьте осторожны, чтобы не перепроектировать решение. Если ваши хранимые процедуры используют курсоры, обратите внимание, что в PHP до версии PHP 7.4 была ошибка.

Вы должны удалить код, который обращается к базе данных. Вот краткий пример кода (вы можете закончить его), который запрашивает любую хранимую процедуру (несколько строк, несколько наборов результатов).

Допустим, у вас есть процедура, подобная вашей, которая выглядит следующим образом (обратите внимание, что в этом случае вы должны использовать уникальные псевдонимы для столбцов).:

 create procedure Get_By_First_Name( inName varchar(20))
begin

-- First result set with just one row
select 1 as 'id', inName as 'First_name', 'Lastname' as Lastname, 'Cat' as 'pet';

-- Second result set with with multiple rows
select 1 as id, now()
union
select 2, date_add(now(), interval  2 day);

end
 

Код для доступа к базе данных:

 <?php


class SQL_Query {
  private $host = "localhost";
  private $user = "mysqli_test";
  private $password = "";
  private $database = "mysqli_test";
  private $resultsets = [];
  
  function get_connection()
  {
     $mysqli = @new mysqli($this->host, $this->user, $this->password, $this->database);
     if (!$mysqli || $mysqli->connect_errno ) {
       printf("Connection failure: %s.n", $mysqli->connect_error);
       return null;
     }
     return $mysqli;
  }


  function __construct($sql, $params)
  {
    $mysqli = $this->get_connection();
    if ($mysqli) {
      $sql = 'call '.$sql.'(';
      for ($i=0; $i < sizeof($params); $i  ) {
        $sql .= ($i>0 ? ',' : '') . is_string($params[$i]) ? "'". $mysqli->real_escape_string($params[$i]) ."'" : $params[$i];
      }
      $sql .= ')';
  
      $rs_num = 0;
      if ($mysqli->multi_query($sql)) {
        do {
          if ($result = $mysqli->store_result()) {
            $rs_num  ;
            $fieldInfo = $result->fetch_fields();
            while ($row = $result->fetch_row() ) {
              $returned = [];
              foreach ($row as $key => $value) {
                $returned[$fieldInfo[$key]->name] = $value;
              }
              $this->resultsets[$rs_num][] = $returned;
            }
            $result->free();
          }
        } while ($mysqli->more_results() amp;amp; $mysqli->next_result());
      } else {
        printf("(%d): %s<br>SQL: %s", $mysqli->errno, $mysqli->error, $sql);
      }
      $mysqli->close();
    }
  }
  /*
    Return the selected resultset as an array
   */
  function get($rs=1)
  {
    return isset($this->resultsets[$rs]) ? $this->resultsets[$rs] : null;
  }
  /*
    Return one field from selected resultset
   */
  function get_one($field, $rs=1)
  {
    return isset($this->resultsets[$rs][0][$field]) ? $this->resultsets[$rs][0][$field] : null;
  }
  
}

$q = new SQL_Query('Get_By_First_Name', ["Samuel'"]);

var_dump($q->get());
var_dump($q->get_one('First_name'));

var_dump($q->get(2));


?>