Do not use the system "mysql" database to install Drupal

MR created and I ran some of the downstream pipelines for D10 and D7.
Ready for review.

fjgarlin → credited Berdir → .

Crediting @berdir for bringing the issue under the radar via slack.

As a note, "core" uses the "mysql" database: https://git.drupalcode.org/project/drupal/-/blob/11.x/.gitlab-ci/pipelin...
We might need to create an issue there too.

Another note:
DER project uses "mysql:5.7" as image: https://git.drupalcode.org/project/dynamic_entity_reference/-/blob/4.x/....
And it works.

Maybe we can explore simplifying our DB images to upstream ones. Out of scope for this issue but worth thinking for another issue.

This issue is getting failures after changing the DB name: https://www.drupal.org/project/entity_usage/issues/3430263#comment-15696617 📌 Automated Drupal 11 compatibility fixes for entity_usage Needs review
Maybe we need to create the database explicitly before running the tests?

Re 6, I created 📌 Do not use the system database for tests Needs work

Regarding #8 do you need me to test this MR on a different module? Or do you know what is happening and it's in hand.

As core 📌 Do not use the system database for tests Needs work has way more tests it is easy to spot what's happening. What happened in #8 was that the database was not created at the time of that particular test running. Some testing base classes create the database as part of the setup, but some others don't (ie: kernel tests).

We can see some very similar fails in the core issue:
- https://git.drupalcode.org/issue/drupal-3463563/-/jobs/2217342
- https://git.drupalcode.org/issue/drupal-3463563/-/jobs/2217350
- https://git.drupalcode.org/issue/drupal-3463563/-/jobs/2217345
- https://git.drupalcode.org/issue/drupal-3463563/-/jobs/2217339

So yeah, I think we need to test on other modules as we might not have enough variety of tests in the projects tested so far.
I think that we might need to create the DB before running anything.

Maybe a good place to do so could be simpletest-db

We could do something like (untested):

.simpletest-db: &simpletest-db
  - |
    [[ $_TARGET_DB_TYPE == "sqlite" ]] && export SIMPLETEST_DB=sqlite://localhost/sites/default/files/db.sqlite
    [[ $_TARGET_DB_TYPE == "mysql" ]] && export SIMPLETEST_DB=mysql://$MYSQL_USER:$MYSQL_PASSWORD@database/$MYSQL_DATABASE && mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e 'create database if not exist $MYSQL_DATABASE'
    [[ $_TARGET_DB_TYPE == "pgsql" ]] && export SIMPLETEST_DB=pgsql://$POSTGRES_USER:$POSTGRES_PASSWORD@database/$POSTGRES_DB
    [[ $_TARGET_DB_TYPE == "mariadb" ]] && export SIMPLETEST_DB=mysql://$MYSQL_USER:$MYSQL_PASSWORD@database/$MYSQL_DATABASE && mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e 'create database if not exist $MYSQL_DATABASE'

I rebased, and triggered all of our new GTD downstream pipelines. All passed except "PHPUnit (max php version)" in d9-basic and d10-plus

Thanks for the rebase. Rest of downstream pipelines: https://git.drupalcode.org/issue/gitlab_templates-3463044/-/pipelines/46...

The fact that it's just max php jobs might mean that the issue is at image level. Tho the database version seems to be the same one for all the variants, which makes it weirder.