Doctrine generating invalid insert statement using table identifier

[mollenbu]

We have an AbstractDataset class with a child called Box using class table inheritance in Doctrine. The Box class uses a discriminator field called "TYPE".

Similarly we have fixtures template for the AbstractDataset and box fixtures that inherit the template.

This is all working fine until,

We updated "fzaninotto/faker" with "fakerphp/faker" and it introduced this error.

When its trying to generate the sql to insert the fixture data, the identifier field from the AbstractDataset entity is being included in the field list. This should be an auto-generated field. This only happens when working with fixtures. If we create a test case using the Box class it runs fine.

Here is the error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
"INSERT INTO common_Dataset (DatasetId, number, revision, version, workflowState, type) VALUES (?, ?, ?, ?, ?, ?), params => [5]"

Our fixture setup is too complicated to use the built in loader and purger. So we have overridden the services "fidry_alice_data_fixtures.loader.simple" and "fidry_alice_data_fixtures.doctrine.purger_loader".

We know this is something we probably did with the overwridden loader and purger. We're looking for insight how to best tackle this problem. If anyone has some ideas on what to look for that would be greatly appreciated.

DriverException.php:34, Doctrine\DBAL\Exception\DriverException->__construct()
ExceptionConverter.php:119, Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert()
Connection.php:1815, Doctrine\DBAL\Connection->handleDriverException()
Connection.php:1750, Doctrine\DBAL\Connection->convertExceptionDuringQuery()
Statement.php:184, Doctrine\DBAL\Statement->execute()
Statement.php:221, Doctrine\DBAL\Statement->executeStatement()
JoinedSubclassPersister.php:159, Doctrine\ORM\Persisters\Entity\JoinedSubclassPersister->executeInserts()
UnitOfWork.php:1128, Doctrine\ORM\UnitOfWork->executeInserts()
UnitOfWork.php:425, Doctrine\ORM\UnitOfWork->commit()
EntityManager.php:392, Doctrine\ORM\EntityManager->flush()
ObjectManagerPersister.php:95, Fidry\AliceDataFixtures\Bridge\Doctrine\Persister\ObjectManagerPersister->flush()
PersisterLoader.php:93, Fidry\AliceDataFixtures\Loader\PersisterLoader->load()
PurgerLoader.php:220, App\DataFixtures\Loader\PurgerLoader->load()
FileResolverLoader.php:72, Fidry\AliceDataFixtures\Loader\FileResolverLoader->load()
DoctrineOrmLoader.php:159, Hautelook\AliceBundle\Loader\DoctrineOrmLoader->loadFixtures()
DoctrineOrmLoader.php:125, Hautelook\AliceBundle\Loader\DoctrineOrmLoader->load()
DoctrineOrmLoadDataFixturesCommand.php:168, Hautelook\AliceBundle\Console\Command\Doctrine\DoctrineOrmLoadDataFixturesCommand->execute()
Command.php:255, Hautelook\AliceBundle\Console\Command\Doctrine\DoctrineOrmLoadDataFixturesCommand->run()
Application.php:1027, Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand()
Application.php:97, Symfony\Bundle\FrameworkBundle\Console\Application->doRunCommand()
Application.php:273, Symfony\Bundle\FrameworkBundle\Console\Application->doRun()
Application.php:83, Symfony\Bundle\FrameworkBundle\Console\Application->doRun()
Application.php:149, Symfony\Bundle\FrameworkBundle\Console\Application->run()
console:42, {main}()

common_dataset.yml

App\Entity\Common\Dataset:
    common_dataset (template):
        number (unique): <numberBetween(1, 99999)>
        revision: <revisionOrVersion()>
        version: <revisionOrVersion()>

box.yml

App\Entity\Edms\Box:
    edms_box_{1..10} (extends common_dataset):

JoinedSubclassPersister.php

foreach ($this->queuedInserts as $entity) {                 // $entity: Box {type, DatasetId, number, revision, version, workflowState} [6]
    $insertData = $this->prepareInsertData($entity);

    // Execute insert on root table
    $paramIndex = 1;

    foreach ($insertData[$rootTableName] as $columnName => $value) {                                // $insertData: {common_Dataset => [5]}
        $rootTableStmt->bindValue($paramIndex++, $value, $this->columnTypes[$columnName]);
    }

    $rootTableStmt->executeStatement();         // $rootTableStmt: {sql => "INSERT INTO common_Dataset (DatasetId, number, revision, version, workflowState, type) VALUES (?, ?, ?, ?, ?, ?)", params => [5]}

}

Box.php

namespace App\Entity\Edms;

use App\Entity\Common\AbstractDataset;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Repository\Edms\BoxRepository")
 */
class Box extends AbstractDataset
{
    const TYPE = 'boxes';

    /**
     * Returns entity short name
     *
     * @return string
     */
    public function getType()
    {
        return self::TYPE;
    }
}

AbstractDataset.php

namespace App\Entity\Common;

use Doctrine\ORM\Mapping as ORM;
use JMS\Serializer\Annotation as Serializer;
use Symfony\Component\Validator\Constraints as Assert;

/**
 * @ORM\Entity()
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn("type", length=64)
 * @ORM\Table(uniqueConstraints={
 *     @ORM\UniqueConstraint(name="unique_version", columns={"type", "number", "revision", "version"})
 * })
 * @Serializer\Discriminator(disabled=true, field="type", map={"simple": "App\Entity\Common\SimpleDataset"})
 */
abstract class AbstractDataset
{
    /** @var int default value for version */
    public const DEFAULT_VERSION = 0;

    /**
     * @ORM\Id()
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue()
     * @var integer
     */
    protected $DatasetId;
    /**
     * @Assert\NotNull()
     * @ORM\Column(type="integer")
     * @var null|integer
     */
    protected $number;
    /**
     * @ORM\Column(type="integer")
     * @var null|integer
     */
    protected $revision;
    /**
     * @ORM\Column(type="integer")
     * @var null|integer
     */
    protected $version = self::DEFAULT_VERSION;
    /**
     * @ORM\Column(type="string", nullable=true)
     * @var string
     */
    protected $workflowState;

    /**
     * @return string
     */
    abstract public function getType();

    /**
     * @codeCoverageIgnore
     */
    public function getNumber(): ?int
    {
        return $this->number;
    }

    /**
     * @param int $number
     * @throws \InvalidArgumentException when number value is not greater than zero
     */
    public function setNumber(int $number): void
    {
        if ($number <= 0) {
            throw new \InvalidArgumentException("number can not be zero or less than zero.");
        }
        $this->number = $number;
    }

    /**
     * @codeCoverageIgnore
     */
    public function getDatasetId(): ?int
    {
        return $this->DatasetId;
    }

    /**
     * @codeCoverageIgnore
     */
    public function getRevision(): ?int
    {
        return $this->revision;
    }

    /**
     * @param int $revision
     * @throws \InvalidArgumentException when revision is less than zero
     */
    public function setRevision(int $revision): void
    {
        if ($revision < 0) {
            throw new \InvalidArgumentException("revision can not be less than zero.");
        }
        $this->revision = $revision;
    }

    /**
     * @codeCoverageIgnore
     */
    public function getVersion(): int
    {
        return $this->version;
    }

    /**
     * @param int|null $version
     */
    public function setVersion(?int $version): void
    {
        $this->version = ($version === null) ? self::DEFAULT_VERSION : $version;
    }

    /**
     * @return string
     * @codeCoverageIgnore
     */
    public function getWorkflowState(): ?string
    {
        return $this->workflowState;
    }

    /**
     * @param string $workflowState
     * @codeCoverageIgnore
     */
    public function setWorkflowState(?string $workflowState): void
    {
        $this->workflowState = $workflowState;
    }

    /**
     * @return AbstractDataset
     * @throws \InvalidArgumentException if revision fails
     */
    public function revise(): AbstractDataset
    {
        $newRevision = clone $this;
        $newRevision->setRevision($this->revision + 1);
        $newRevision->setVersion(0);
        return $newRevision;
    }

    public function version(): AbstractDataset
    {
        $newVersion = clone $this;
        $newVersion->setVersion($this->version + 1);

        return $newVersion;
    }
}

[mollenbu]

I loaded the fixtures without our custom PurgerLoader and still got the same error "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens".

[theofidry]

@mollenbu just in case, I remember having this issue at some point with Doctrine on a specific PHP version. I think it was 7.4.16 or smth... So before digging any further into this, I suggest trying to change your PHP version see if that's the cause

[ghost]

Been running into the same issue (and same as #129). The changes introduced in 1.5.0 solved it for me but I'm currently vendor locked to version 1.4. Could it be possible to get a backport of those changes? It would be great for #207 to also be included, since I also needed it to solve my issues.

Thanks @theofidry!

[theofidry]

It's been a while and presumably fixed in 1.5 so I'll be closing this.