PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

[videni]

it seems there is bug in this bundle which costs me a whole day. when doctrine tries to insert adAsset2 into database , it gives the error

PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

as you can see below, the AdAsset is the same Area entity, the later has no problem. why did this happen? I found out that the original id generator can't alway be restored. the exception will disappear if remove false === $generator->isPostInsertGenerator() .

theofidry/alice-data-fixtures/src/Bridge/Doctrine/Persister/ObjectManagerPersister.php

    public function persist($object)
 {
            //....
            // this can't always restore the original generator.
            if (null !== $generator && false === $generator->isPostInsertGenerator()) {
                // Restore the generator if has been temporary unset
                $metadata->setIdGeneratorType($generatorType);
                $metadata->setIdGenerator($generator);
            }
 }

fixture.yml

App\Bundle\AdvertisementBundle\Entity\Advertisement:
    ad1:
        deviceType: 'pad'
        name: '欢度中秋节'
        status: 'published'
        channel: '上海闵行店'
        city: '上海'
        channelId: 10
        link: 'http://example.com'
        startsAt: "<date_create()>"
        endsAt: "<date_create('2 day')>"
        template: 'dark'
        bannerLink: '/test.png'
        assets: ['@adAsset1']
        organization: '<entityReference("App\Bundle\OrganizationBundle\Entity\Organization", "findOneByName", "Main")>'
    ad2:
        deviceType: 'pad'
        name: '欢度国庆'
        status: 'published'
        channel: '成都高新店'
        city: '成都'
        channelId: 11
        link: 'http://example.com'
        startsAt: "<date_create()>"
        endsAt: "<date_create('2 day')>"
        template: 'dark'
        bannerLink: '/test.png'
        assets: ['@adAsset2']
        organization: '<entityReference("App\Bundle\OrganizationBundle\Entity\Organization", "findOneByName", "Second")>'

App\Bundle\AdvertisementBundle\Entity\Area:
    area1:
        name: '生鲜区'
        areaId: 10
        advertisement: '@ad1'
    area2:
        name: '熟食区'
        areaId: 11
        advertisement: '@ad2'

App\Bundle\AdvertisementBundle\Entity\AdAsset:
    adAsset1:
        advertisement: '@ad1'
        originalName: 'none'
        mineType: 'image/png'
        path: '/test.png'
    adAsset2:
        advertisement: '@ad2'
        originalName: 'none2'
        mineType: 'image/png'
        path: '/test2.png'

call stack

tring(104) "/Users/acme/www/demo_app/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php278"
array(1) {
  ["app_advertisement_asset"]=>
  array(4) {
    ["path"]=>
    string(9) "/test.png"
    ["mine_type"]=>
    string(9) "image/png"
    ["original_name"]=>
    string(4) "none"
    ["advertisement_id"]=>
    int(159)
  }
}
string(115) "/Users/acme/www/demo_app/vendor/theofidry/alice-data-fixtures/src/Bridge/Doctrine/Persister/ObjectManagerPersister.php"
array(1) {
  ["id"]=>
  int(98)
}
string(115) "/Users/acme/www/demo_app/vendor/theofidry/alice-data-fixtures/src/Bridge/Doctrine/Persister/ObjectManagerPersister.php"
array(0) {
}
string(104) "/Users/acme/www/demo_app/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php278"
array(1) {
  ["app_advertisement_asset"]=>
  array(5) {
    ["path"]=>
    string(10) "/test2.png"
    ["mine_type"]=>
    string(9) "image/png"
    ["original_name"]=>
    string(5) "none2"
    ["id"]=>      // empty Id ,  this is why doctrine complains
    NULL
    ["advertisement_id"]=>
    int(160)
  }
}

Time: 19.74 seconds, Memory: 156.26MB

There was 1 error:

1) App\Tests\Controller\AddvertisementApiTest::it_allows_to_get_paginated_advertisement
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO app_advertisement_asset (path, mine_type, original_name, advertisement_id) VALUES (?, ?, ?, ?)' with params ["\/test2.png", "image\/png", "none2", null, 160]:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

/Users/acme/www/demo_app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:126
/Users/acme/www/demo_app/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:184
/Users/acme/www/demo_app/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:158
/Users/acme/www/demo_app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php:178
/Users/acme/www/demo_app/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:288
/Users/acme/www/demo_app/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:1100
/Users/acme/www/demo_app/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:386
/Users/acme/www/demo_app/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:362
/Users/acme/www/demo_app/vendor/theofidry/alice-data-fixtures/src/Bridge/Doctrine/Persister/ObjectManagerPersister.php:110
/Users/acme/www/demo_app/vendor/theofidry/alice-data-fixtures/src/Loader/PersisterLoader.php:91
/Users/acme/www/demo_app/vendor/theofidry/alice-data-fixtures/src/Loader/PurgerLoader.php:119
/Users/acme/www/demo_app/vendor/lakion/api-test-case/src/ApiTestCase.php:305
/Users/acme/www/demo_app/tests/Controller/AdvertisementApiTest.php:22

[theofidry]

I'm not 100% sure this is the cause of your issue. Indeed this false === $generator->isPostInsertGenerator() is here to ensure we don't disable unnecessarily the generator over and over again. So at first glance, I see two possibilities:

• One of your App\Bundle\AdvertisementBundle\Entity\AdAsset has an ID assigned causing the generator to be wrongly disabled (generators are on a class basis, not entity basis, since Doctrine's Metadata are for the whole class)
• The issue you described comes from after the loading (but that doesn't look to be the case to me based on the stack trace) in which case, maybe the generator is not restored as it should after the whole loading. This would be the loading has a side-effect on the generator which would be undesirable and should be fixed

If it's none of those, I would need a reproducer to be able to debug this and look deeper into it

[ixarlie]

Hi @theofidry I am having the same issue.

My entity is using the Doctrine\ORM\Id\IdentityGenerator. I used to load a set of base fixtures to be shared for all the test scenario fixtures. I load them in different LoaderInterface::load callings but under the same execution.

To do not load the base fixtures over and over, they are persisted using a Doctrine\Common\DataFixtures\ProxyReferenceRepository class.

Then just right before the rest of test scenario fixtures are going to be loaded, I fetch the base fixtures from the proxy reference repository and I add them as part of the initial objects array.

if (!$isBaseFixtureFile) {
    // ...
    $objects = array_merge($objects, $proxyRepository->getReferences());
}

// Decorated ObjectManagerPersister
$this->delegate->load($fixturesFiles, $parameters, $objects, $purgeMode);

So all the base objects from the proxy repository were persisted and have an ID. When ObjectManagerPersister iterates over them, it changes the metadata information.

$metadata->usesIdGenerator() && false === empty($metadata->getIdentifierValues($object))

After the doctrine object manager persists the base objects, the next condition is never met because my generator isPostInsertGenerator returns true.

null !== $generator && false === $generator->isPostInsertGenerator())

That will be OK for other base fixtures with same class, but if I have fixtures with the same class in the second load calling, then as the metadata wasn't restored, the new objects will raise an ORMException because Doctrine cannot generate an ID for them (generator type none was set)

This was working well for v1.0.1 when it was only comparing null !== $generator

What would it be the best approach?

I think I can downgrade to v1.0.1, but I would like to have the new fixes from v1.1.2

Thanks!!!

[theofidry]

Maybe an alternative approach to your problem @ixarlie would be to:

• store the changed generators that are changed here
• restore the changes done after the flush here

You can try that approach locally, if that solves your problem I'll be more than happy to accept a patch for it although I admit I would really really appreciate a test to reproduce your issue in order to avoid regressions in the future: it's a very tricky part, so any additional test to capture problematic edge cases are greatly appreciated

[uginroot]

Restor not work without reset \Doctrine\ORM\Persisters\Entity\BasicEntityPersister->insertSql to null. The reason is that \Doctrine\ORM\Persisters\Entity\BasicEntityPersister save generate INSERT request and does not regenerate it if the id generator has changed.

Hack solution working for me. Usage example:

class A{
    public function __construct(ChangeIdentityGeneratorHelper $changeIdentityGeneratorHelper)
    {
        // Create prepaire user 
        $entity = new User();
        $entity->setId(1);
        // .....

        $changeIdentityGeneratorHelper->changeIdentity(
            User::class,
            ClassMetadata::GENERATOR_TYPE_NONE,
            new AssignedGenerator()
        );

        // persist and flush entities

       $changeIdentityGeneratorHelper->restoreIdentity(User::class);

        // Create not prepaire user 
        $entity = new User();
        // .....

        // persist and flush entities
    }
}

[theofidry]

@uginroot can you out https://github.com/theofidry/AliceDataFixtures/pull/135?

[uginroot]

I wandered here because Google did not find a solution. I left my decision here to help those who have the same problem. I do not use theofidry / AliceDataFixtures, I just encountered the same problem in my project, and decided to leave a solution to a similar problem here. This problem should be solved by the doctrine developers.

[theofidry]

I don't think it's a problem on Doctrine side: we are changing the class metadata, that's our problem... #135 is precisely a fix for that

[uginroot]

Tomorrow I will add a method clearInsertSqlQueryCache and if it is accepted then the problem can be solved without hacks.

[theofidry]

Hopefully fixed by #135

[AndersonNascimentoDosSantos]

in my case was the number of parameters, passed through the insert statement,in a form with re-captcha,he insert an hidden input,with the name without value and when pass the array to insert statement this failure happen

[Marinos20]

Voici mon code

function ajouter($nom,$prix, $image,$desc) { if(require("connexion.php")) { $req=$access->prepare("INSERT INTO Products(nom, prix, image, description) VALUES ('$nom',$prix,'$image','$desc')"); $req->execute(array($nom, $prix, $image, $desc));

$req->closeCursor();

Voilà l'erreur , aider moi

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /opt/lampp/htdocs/shop/config/commandes.php on line 28