Migration Seed for Postgres driver was get PDO Error (There is no active transaction for seed data) about migrations HOT 11 CLOSED

As far as I'm concerned, I tried your code and had the same problem.
Out of curiosity, I executed the content of the up() method in the app just at the end of the bootstrap.php file and it worked well.

So I dug further and I was able to make the migration work by passing the atomic option of the save() method to false :

$table->save($entity, ['atomic' => false]);

This turns transaction off and avoid the exception to be raised, so it should work (I tested this on Cake 3.0.5).

As for why it is working when executing while migrating and not while executing the app itself, I have no idea. I'll try to investigate this but it might take some times since I don't really that part of the framework.

from migrations.

Apparently, Phinx starts a transaction by itself when migrating (see source).

Maybe that's what causing a problem here.
I'll take a deeper look when I get the chance. Maybe there is something that can be done to prevent this. But I'm sceptical since it works well with MySQL...

Meanwhile, the atomic trick from my previous comment should work.

from migrations.

I think I figured it out.
After some poking around, I found out that phinx starts transactions with raw SQL statement (like BEGIN or START TRANSACTION).
However, it does not use the PDO::beginTransaction() method which is causing the problem here I think.
If I change this method to

public function beginTransaction()
{
    $this->execute('BEGIN');
    $this->getConnection()->beginTransaction();
}

Otherwise, it seems to create some kind of conflicts.
I think the problem is within phinx and nothing can really be done in this plugin (we could maybe try and build our own adapters but that is not really viable in the long term in my opinion).

from migrations.

So make a pr for it in phinx?

from migrations.

@josegonzalez That is the plan. I just have to do extensive testing beforehand (and find time to do it).

from migrations.

Hi HavokInspiration,

I have try to use

$table->save($entity, ['atomic' => false]);

So far, its working now. I hope i can get the latest fix updated on newest release.
Thanks for all.

from migrations.

@dremi I started working on a fix for phinx yesterday night. I still need to do some tests.
Hopefully I'll be able to submit it tonight.
Glad to see the workaround is working for you.

from migrations.

Hi HavokInspiration,

Thanks for your update.

from migrations.

I gave this another round and I think there is nothing to actually fix.
Here's what is really going on :

1. Migration starts
2. Phinx starts a transaction
3. CakePHP ORM starts saving the given rows.
   By default, at the end of a save, it commits the transaction created by Phinx
4. Phinx commits the transaction... but can not since it was already commited

And the problem is the other way around if the code base from phinx is left as is.
I have the same problem if phinx deals with transaction with PDO methods rather than with raw queries with MySQL this time. Except that data is saved in the database and the migration failed right after, but the data is still there)

So I'm back to square one.
The only real way to have something working in all cases would be to disable transactions on Cake when running in Migrations.
This way, Phinx deals with commiting and rolling back transactions based on what's going on.

Is there a way to do it other than passing atomic to false ? Have something more "global".

from migrations.

I can take a look at this @HavokInspiration

from migrations.

Closing as there is a pull request now.

from migrations.