Why is CakePHP unable to create fixtures for one controller and returns SQLSTATE[42000] while it is for another controller?

Quick line out of my CakePHP Application (important part):

I have a users table with all that is needed (Model, Controller, Views, Fixtures, TestCases). Among other data this user table references to a cities Table, a countries and a streets table. For the cities, countries and streets everything is there as well and works:(Model, Controller, Views, Fixtures, TestCases).

Fixtrues and Text Cases were built with Bake.

Wihle I can run the TestCases for cities, countries and streets,I cannot run the TestCases for Users as I get this Error Message:

Exception: Unable to create constraints for fixture "App\Test\Fixture\UsersFixture" in "App\Test\TestCase\Controller\UsersControllerTest" test case: 
SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'cake_user'@'localhost' for table 'cities'
In [/opt/git/premesec/vendor/cakephp/cakephp/src/TestSuite/Fixture/FixtureManager.php, line 335]

What puzzles me is that I can run the fixtures for cities when I run the cities TestCases, but there is a problem when the same fixtures should be run for users TestCases.

Here are the constraints form UsersFixtures.php:

'_constraints' => [
            'primary' => ['type' => 'primary', 'columns' => ['id'], 'length' => []],
            'city_id' => ['type' => 'foreign', 'columns' => ['city_id'], 'references' => ['cities', 'id'], 'update' => 'noAction', 'delete' => 'noAction', 'length' => []],
            'fk_country_id' => ['type' => 'foreign', 'columns' => ['country_id'], 'references' => ['countries', 'id'], 'update' => 'noAction', 'delete' => 'noAction', 'length' => []],
            'house_number_id' => ['type' => 'foreign', 'columns' => ['house_number_id'], 'references' => ['house_numbers', 'id'], 'update' => 'noAction', 'delete' => 'noAction', 'length' => []],
            'street_id' => ['type' => 'foreign', 'columns' => ['street_id'], 'references' => ['streets', 'id'], 'update' => 'noAction', 'delete' => 'noAction', 'length' => []],
        ],

Here are the constraints form CitiesFixtures.php:

'_constraints' => [
            'primary' => ['type' => 'primary', 'columns' => ['id'], 'length' => []],
        ],

CakePHP v4.0.6

PHP-Version: 7.2.24

mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64)

Solution

Check the error message closely, it's not really about the cities table, but specifically about the REFERENCES command that your account isn't allowed to run, ie it's the foreign key constraint in your Users fixture, which most likely isn't going to be involved in your other tests, ie your other tests aren't loading the Users fixture.

Long story short, you either need to grant your cake_user account the REFERENCES privilege accordingly, which is required for being able to create foreign key constraints, or choose a different user account that already has the required privileges.