Questions on the test suite lights in CakePHP 4.3

[Rakasch]

Hallo there,

I have a problem when updating to Cake 4.3.
We are using „Fixture Factories 2.5.1“ and „test-suite-light 2.4“.

Problem:
The tables are not emptied between tests, since the triggers are not created in the database.
After some evaluation, the Sniffer’s truncateDirtyTables() method is called, but createTriggers() is not.

We are using a MariaDB database, so I used the MysqlTriggerBasedTableSniffer in my DB config.
'tableSniffer' => '\CakephpTestSuiteLight\Sniffer\MysqlTriggerBasedTableSniffer',

Note: I already had the problem on Cake 4.2 and before that the triggers were not created automatically.
Therefore I had created the triggers manually on the database. That worked.
With the update, this workaround no longer works.
The triggers are now deleted when the test is executed, since the DB schema is created anew each time by the schema generator.

Would appreciate any help.
Thanks.

[pabloelcolombiano]

Hello @Rakasch ,

are you creating the schema with the MIgrator tool? This will create the schema of the test DB once when the test suite starts. If you are using migrations, it will recreate the schema only if migrations were added or removed since the last test suite run.

If you are using the CakePHP 4.3 phpunit.xml.dist file, you should specify the truncation strategy by using the TruncateDirtyTables trait in your DB-interacting tests. The deprecated phpunit.xml.dist approach works as before.

You may start by droping your test database (just once, to make sure) and let your tests run.

You should not need to specify the tableSniffer in your config if you are using CakePHP's Mysql driver (see here).

So in short my two questions:

1. What is your strategy to create the schema of your test DB? How does your tests/bootstrap.php file look like?
2. How does your phpunit.xml configuration look like?

[Rakasch]

I dropped the test DB an recreated it as empty DB.
I started the test.

Syntax error or access violation: 1305 PROCEDURE testranet_test2.TruncateDirtyTables does not exist.

The tables were recreated, also a test_suite_light_dirty_tables table is there. No triggers and no TruncateDirtyTables Procedure.

I didn't use migrations on cake 4.2 (altough we were planning to add them to our workflow).
The upgrade guide of cakephp 4.3 kind of forced me to use migrations for the schema generator.
I followed the upgrade guide and added the schema generator and an initial migration.

phpunit.xml.dist

bootstrap.php
I have two test database configs "test" and "test_logs".

[Rakasch]

And the parent class of all my tests does have the TruncateDirtyTables trait.

[pabloelcolombiano]

Thanks for the additional info. Your setup looks fine imo.
Since the dirty table collector is created, but the procedure and the triggers aren't, I suppose that the code is breaking somewhere after this line.

Are all the tables marked as dirty, namely is the table test_suite_light_dirty_tables populated with the name of all the tables?

Something might be breaking due to the fact that you are using two test DB (although this should be supported). Let us debug that with one test DB only. What happens if you comment out the code related to the test_logs in tests/bootstrap.php and comment out the configuration in DB configs?

[Rakasch]

I have found the cause of the problem.
Even though there are still a few oddities open.

In my initial migration of "test_logs" the table "test_suite_light_dirty_tables" is included.
I have no idea why. In the live database for "test_logs", "test_suite_light_dirty_tables" is not present.
Also, it shouldn't have any effect on the other database "test".

In any case, that is the reason why the if-condition said that the table already exists and accordingly did not execute the init code at all.

If I take the test_suite_light_dirty_tables out of the migration, the tests are working.

[pabloelcolombiano]

Glad to read that.

Let me know about the other few oddities!

[Rakasch]

For the first 'oddity':
my initial migration of "test_logs" included the table "test_suite_light_dirty_tables".
I guess, I accidently created the migration from the test_logs db instead of the live logs db.

For the second 'oddity':
I probaby do not understand how to use migrations with two database configs.
Also I cannot find any documentation on the cake migrations page on how to use migrations with two database configs.

I've setup two initial migrations for my two database configs

bin/cake bake migration_snapshot Initial
bin/cake bake migration_snapshot InitialLogs --connection test_logs

Looking into the migration file, there's no information to which database this migration belongs to. After running the test, I have all tables from both migrations in the test db.
How should the migrator know which migration file to execute on which connection ?

in bootstrap.php
$migrator->run();
$migrator->run(['connection' => 'test_logs']);

[pabloelcolombiano]

Your migrations should be placed in two different directories, right? Otherways the logs migration would run on the default DB, and the default migrations on the logs DB when using the migrator. So assuming you have your default migrations in config/Migrations and your logs migrations in config/MigrationsLogs:

The snapshot commands should read:

bin/cake bake migration_snapshot Initial
bin/cake bake migration_snapshot InitialLogs --connection logs     // move the result into config/MigrationsLogs

When running the migrator on multiple sets of migrations, it is recommended to use the runMany() method. As described here, you should call:

$migrator->runMany([
    ['connection' => 'test'],
    ['connection' => 'test_logs', 'source' => 'MigrationsLogs'],
]); 

Does this help?

[Rakasch]

Looks promising.
I will try it out tomorrow.

Thank you very much for your time and support!

[Rakasch]

Ok, the tests are running now.
Although I still have two issues.

My tests on a db-view are failing.
In the live db it is a view, but the migration did create it as a table in the test db. Is that a bug?

You said If you are using migrations, it will recreate the schema only if migrations were added or removed since the last test suite run.
It's not behaving like this.
Everytime I start the test suite, it's rerunning my initial migrations, which of course takes a while.

[pabloelcolombiano]

The creation of views with migrations is still open here. Maybe something you would like to contribute to?

To the sesond point, how does you call with the migrator look like now?

[pabloelcolombiano]

Regarding the view, you could try to remove the table automatically created by the snapshot, and instead run a MySQL query in the migration file that creates this view, no?

[Rakasch]

bootstrap.php

This is, what's happening each time:

I'm new to migrations so I don't know yet how a lot of things work.
But I guessed this occures, because I never migrated the migration. Since the database already exists in that state.
I checked the documentation and found mark_migrated.

• the migration in default folder was skipped (already migrated).
• the migration in the logs folder was now marked migrated.
  Now both migrations are marked as migrated.
  I also found the entries in the phinxlog tables.

I restarted the test suite, but it's still running into the same if case on the screenshot.

[Rakasch]

Regarding the view, you could try to remove the table automatically created by the snapshot, and instead run a MySQL query in the migration file that creates this view, no?

Nice idea. But I guess it doesn't work so easily:
I tried that. The migrator wants to truncate the view and throws an error.
I added the view as "skip" parameter to the migrator call. And adjusted the queries with an "Create/Drop IF EXISTS".
But the tests are failing with "Base table or view not found" error, despite the view exists.
Stacktrace from /vendor/vierge-noire/cakephp-test-suite-light/src/Sniffer/MysqlTriggerBasedTableSniffer.php:41
Maybe thats another truncate on view problem.
Guess in my case it's easier to rewrite the code to not use the view.

[pabloelcolombiano]

Regarding the view:
CakePHP 's ConnectionInterface does not support listing views. An issue has been once open on that, but without implementation. Therefore the present package does not support the cleaning of views.
It is however possible for you to create an extension of the MysqlTriggerBasedTableSniffer overwritting the fetchAllTables method and returning the list of the tables and views.
You are welcome to submit your results in a PR!

Regarding the migrations:
The mark_migrate is something you want to use in your productive environement. As far as the tests are concerned, your initial migration should not be marked as migrated. If your issue on having the schema recreated before each test suite persists, I suggest to open an issue in the migrations repository.

I'll close this for the moment. Do not forget to star our packages if those are useful to you. This helps us funding our projects!

[pabloelcolombiano]

This might also be of interest to you: cakephp/cakephp#16122

[Rakasch]

Thank you again for everything.
Your support was very helpful.