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.

I have rebased again, given the 26 new commits since last time. Now all the GTD pipelines pass, including the two 'max php' ones which failed last time.

Running the rest of the pipelines here: https://git.drupalcode.org/issue/gitlab_templates-3463044/-/pipelines/50...

That's great! Marking RTBC.

Do you think the two 'Max PHP version' failures back in April have been properly fixed by new images? It seems like this is safe to merge now.

Do we need to document this? or is it entirely internal and not relevent to developers / maintainers?

It gives me confidence knowing that the GTD projects reported the failure and they no longer do it (it was such a great idea to have GTD!!), so yeah, I'm feeling confident too.

I think this is totally internal, and if somebody has overridden this value, that's still ok because that's actually the change that we did in here. Nobody should be using the "mysql" database for a Drupal installation, so we are actually fixing a (long standing) bug in my opinion.

Merged.

I was just running a full Scheduler pipeline on this MR to check, and there might be a problem in Previous Major (or it might be a Scheduler quirk, I have not had time to investigate yet). I should have suggested we do this before marking RTBC.

Database drupal_database not found. The server reports the
    following message when attempting to create the database: SQLSTATE[HY000]:
    General error: 1007 Can't create database 'drupal_database'; database
    exists.Failed to CREATE a test table on your database server with the
    command CREATE TABLE {drupal_install_test} (id int NOT NULL PRIMARY KEY).
    The server reports the following message: SQLSTATE[3D000]: Invalid catalog
    name: 1046 No database selected: CREATE TABLE
    "test49199329drupal_install_test" (id int NOT NULL PRIMARY KEY); Array  (     )
    .Are you sure the configured username has the necessary permissions to
    create tables in the database?

https://git.drupalcode.org/issue/scheduler-3445052/-/jobs/5378209#L495

This only happens for one of the matrix of tests (rules_integration) so I suspect it is not a problem that will effect everyone.

Oh, that's the same issue we had in #14/#15.

Let me revert the commit and we can continue the investigation then until we are 100% sure of it.

I keep thinking that it might be related to how the image is created, but I'm not sure of this (mostly where to look).

It's an easy change so we can recreate the MR as/when needed.

New MR to use: https://git.drupalcode.org/project/gitlab_templates/-/merge_requests/362...

In that Scheduler pipeline the same error also happened in a differtent variant (previous minor) in a different matrix job (functional javascript) so I don't think it is a problem necessarily with the way Scheduler Rules or javascript tests are configured.
https://git.drupalcode.org/issue/scheduler-3445052/-/jobs/5378200#L531
Can it really be random? That sounds very odd, but currently there is no clear cause connecting these failures.

I found this #2443839: Drupal does not install when auto-creating the MySQL database with special characters → . I'm not sure if that underscore in the name could be causing issues. It should not, but who knows.

I suggest that we try to connect to mysql (directly or via drush sql:cli) and try to run the show grants
and the show databases commands, to see if we can find more information about these errors.