JanGlaser
September 18, 2022
140 views
0 votes
2 Answers

The problem

I am using Laravel 8.83.23
I have schema dump from squashed migrations in databaseschemamysql-schema.dump
tests are running above test database, as in database.php


    'testing' => [
                    'driver' => 'mysql',
                    'host' => env('DB_TEST_HOST', '127.0.0.1'),
                    'port' => env('DB_TEST_PORT', '3306'),
                    'database' => env('DB_TEST_DATABASE', 'forge'),
                    'username' => env('DB_TEST_USERNAME', 'forge'),
                    'password' => env('DB_TEST_PASSWORD', ''),
                ],

Before I squashed migrations, my test cases only used DatabaseMigrations trait, and the test database was recreated every time and all worked, example of test class:


    class SystemControllerTest extends TestCase
    {
        use WithFaker;
        use DatabaseMigrations;
        /**
         * @var User
         */
        private $user;
    
        public function setUp(): void
        {
            parent::setUp();
    
            //create roles and data
            $this->seed(RoleAndPermissionSeeder::class);
        ... etc

the migrations were found and executed, recreating the database
then, I squashed the migrations, so all migrations got deleted, and I got databaseschemamysql-schema.dump
php artisan migrate works as expected through command line, creating full database schemas from the dump (it finds it)
tests however no longer work, as there is an error


    SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cinema_test.roles' doesn't exist (SQL: delete from `roles`)

when I check the sql test database after the test runs, it is empty (only table migrations gets created there, and it is empty)
this error persists even when I call artisan migrate in the test’s setup:


    public function setUp(): void
        {
            parent::setUp();
            Artisan::call('migrate', array(
       '--database' => 'testing',
       '--force' => true));
            //it crashes here
            $this->seed(RoleAndPermissionSeeder::class);

RoleAndPermissionSeeder just operates with the sql tables, which do not exist, hence the error
I even tried DatabaseMigrations and DatabaseTransactions and RefreshDatabase traits, without any success
how do I populate the database data? There is no way for me to read the output of the Artisan::call('migrate') command, so I do not know what is happening there
return code of Artisan::call('migrate') is 0
is there maybe some setup I am missing?

Answers

Chosen as BEST ANSWER

I have finally figured this out.

The reason for the problem

The problem was in incorrect setup of the testing environment. I have not discovered the exact reason, but I figured out how to setup the testing environment so that the dump would be found and loaded.

How I hunt down the bug

This describes my steps on how I found a way to fix this.

In `database.php` I have copied testing database instead of normal one

in database.php I had the main database connection:


    'mysql' => [
                'driver' => 'mysql',
                'url' => env('DATABASE_URL'),
                'host' => env('DB_HOST', '127.0.0.1'),
                'port' => env('DB_PORT', '3306'),
                'database' => env('DB_DATABASE', 'forge'),
                'username' => env('DB_USERNAME', 'forge'),
                'password' => env('DB_PASSWORD', ''),
                'unix_socket' => env('DB_SOCKET', ''),
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
                'prefix_indexes' => true,
                'strict' => false,
                'engine' => null,
                'options' => extension_loaded('pdo_mysql') ? array_filter([
                    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                ]) : [],
            ],

and the testing connection


    'testing' => [
                    'driver' => 'mysql',
                    'host' => env('DB_TEST_HOST', '127.0.0.1'),
                    'port' => env('DB_TEST_PORT', '3306'),
                    'database' => env('DB_TEST_DATABASE', 'forge'),
                    'username' => env('DB_TEST_USERNAME', 'forge'),
                    'password' => env('DB_TEST_PASSWORD', ''),
                ],

I copied the testing connection data into a new mysql connection, just to see, whether on a command line I get same results
so, the file then looked like this


    'mysql' => [
                'url' => env('DATABASE_URL'),
                'driver' => 'mysql',
                    'host' => env('DB_TEST_HOST', '127.0.0.1'),
                    'port' => env('DB_TEST_PORT', '3306'),
                    'database' => env('DB_TEST_DATABASE', 'forge'),
                    'username' => env('DB_TEST_USERNAME', 'forge'),
                    'password' => env('DB_TEST_PASSWORD', ''),
                'unix_socket' => env('DB_SOCKET', ''),
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
                'prefix_indexes' => true,
                'strict' => false,
                'engine' => null,
                'options' => extension_loaded('pdo_mysql') ? array_filter([
                    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                ]) : [],
            ],
    
    /*'testing' => [
                    'driver' => 'mysql',
                    'host' => env('DB_TEST_HOST', '127.0.0.1'),
                    'port' => env('DB_TEST_PORT', '3306'),
                    'database' => env('DB_TEST_DATABASE', 'forge'),
                    'username' => env('DB_TEST_USERNAME', 'forge'),
                    'password' => env('DB_TEST_PASSWORD', ''),
                ],*/

on the console, I ran php artisan:migrate
the database dump was found and loaded
therefore, the dump was found in normal cases, but was not found, in testing cases
after some research, I changed the testing environment setup in phpunit.xml, I will explain it now

The file `phpunit.xml`

The phpunit.xml was as follows (not full file shown here):


       <server name="QUEUE_CONNECTION" value="sync"/>
        <server name="SESSION_DRIVER" value="array"/>
        <server name="TELESCOPE_ENABLED" value="false"/>
        <env name="DB_CONNECTION" value="testing"/>     
    </php>
</phpunit>

so, we can see that the testing database connection is defined for unit tests
on web I found advice to set the database table only, instead of changing entire connection for tests, because it is easier
I tried such an approach, so the phpunit.xml became

       <server name="QUEUE_CONNECTION" value="sync"/>
        <server name="SESSION_DRIVER" value="array"/>
        <server name="TELESCOPE_ENABLED" value="false"/>
        <env name="DB_DATABASE" value="cinema_test"/>   
    </php>
</phpunit>

I deleted the testing connection from database.php and related obsolete variables from .env file
this fixed the issue, and the dump file got loaded even in tests now

Conclusion

Although I have not figured out the real cause for the lavavel not loading the dump file, I have found a workaround which was to only change the database name for tests, instead of defining entirely new sql connection for testing pursposes. This solved the issue, and the database dump file gets loaded during tests now.

(Edit)

- WesleyNZ
- September 19, 2022 at 5:41 am
- 0 votes
0
Seems like schema dumps can’t be used for the in-memory database when testing

https://laravel.com/docs/9.x/migrations#squashing-migrations

May be able to do something like this

DB::unprepared(file_get_contents("path/file.sql"));

Would only try as a last resort, personally would want a test environment migration, also you should add a check for a test environment migration if you take this approach

Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – Laravel tests database migration from schema