August 27, 2022

Symfony / Doctrine Fixtures and Importing SQL - "There is no active transaction"

I had to call some custom SQL alongside DoctrineFixtures. I thought it would be easy to run a raw SQL script (CREATE TABLE, CREATE PROCEDURE, CALL, etc.) at the same time I called AppFixtures. Some older blog posts show how to load a folder of SQL scripts using DataFixtures. Easy, right?

Appears that within Doctrine, DataFixtures and Migrations both utilize Transactions under the hood. This doesn't affect simple inserts when loading new data for dev and testing. But it does affect things such as running custom SQL as part of a data fixture.

Challenge is that you cannot use statements such as CREATE TABLE within a transaction. You will get the error "There is no active transaction."

The output from running DoctrineFixtures when running certain SQL scripts.

The below code snippet loads up SQL scripts, but if the script calls anything that can't work in a transaction, it will throw an error.

    public function load(ObjectManager $manager)
        $finder = new Finder();
        foreach ($finder as $file) {
            /* @var EntityManagerInterface $manager */
            $connection = $manager->getConnection();
            print "Importing: {$file->getBasename()} " . PHP_EOL;
            $sql = $file->getContents();

The easiest solution was to remove Symfony and Doctrine from the equation. And call MySQL from the command line within a shell script.

echo 'Creating and Loading Table ...'
mysql --socket=xxx --user=xxx --password=xxx --database=xx < ./src/DataFixtures/SQL/001_your_file.sql

While not highly secure (password in clear text), this is only used to load fake data into a dev environment regularly. The last thing I want to do is manually run Drop Database, Load DataFixtures, and Run SQL scripts. Add above in a shell script (or add to existing).

FILED UNDER:  Software Development

Symfony / Doctrine Cheat Sheet

Common Symfony and Doctrine commands for quick reference when writing PHP.