1

My query is:

/**
  * @var \Magento\Framework\DB\Adapter\AdapterInterface
  */
private $connection;

$query =
            <<<SQL
                LOCK TABLE {$tablename} WRITE;
                SET  @num := 0;
                UPDATE {$tablename} SET {$idField} = @num := (@num+1);
                ALTER TABLE {$tablename} AUTO_INCREMENT = 1;
                UNLOCK TABLES;
SQL;
$this->connection->query($query);

I've got an error in CLI:

Multi queries forbidden

How to run the query simultaneously?

4
  • Why not running them into 5 different and sequential query ? Which is what sql is probably doing anyway.
    – Claims
    Apr 13 at 12:20
  • It's one of possible solutions. But: Suppose 3d query is failed. Is table leave locked? Apr 14 at 13:06
  • Sort of direction of solution. We can fetch PDO object: /** @var \PDO $pdo */ $pdo = $this->connection->getConnection(); $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, 0); $this->connection->exec($query); Apr 14 at 13:07
  • 1
    Well I would say you can try catch each request and in the failing part you can always run the unlock just to be sure i would say.
    – Claims
    Apr 15 at 9:31

1 Answer 1

2

Try something like this:

private ?AdapterInterface $connection = null;

private method doMultipleQueriesAndDoUnlockNoMatterWhat(): void
{
    $query1 = sprintf('LOCK TABLE %s WRITE', $tablename);
    $query2 = '';
    $query3 = '';
    $query4 = '';
    $query5 = 'UNLOCK TABLES';

    try {
        $this->connection->exec($query1);
        $this->connection->exec($query2);
        $this->connection->exec($query3);
        $this->connection->exec($query4);
    } catch(\Throwable $e) {
    
    } finally {
        $this->connection->exec($query5);
    }
}

You can use finally to proceed with some logic no matter what happens in the try-catch-block.

2
  • It looks like the most possible solution. I suggest use query() instead of exec(). Good idea with finally Apr 21 at 9:37
  • Best solution here would be to not use exec or query at all, instead have the right abstraction layer available to do it; but it would require you to write more code.
    – MLGS
    Apr 23 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.