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.

Just rebased, as the new MR362 was 170 commits behind (due to main being created in this fork nearly a year ago).

I think it might be worth trying to replicate the error from #23 in a GTD branch, then we can test it easily.

We have two failures in d10-plus, without any modification:

Max php version https://git.drupalcode.org/project/gitlab_templates_downstream/-/jobs/55...

Resolve all issues below to continue the installation. For help configuring your database server, see the installation handbook, or contact your hosting provider.
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.

previous minor https://git.drupalcode.org/project/gitlab_templates_downstream/-/jobs/55...

Drupal\Core\Installer\Exception\InstallerException: Resolve all issues below to continue the installation. For help configuring your database server, see the installation handbook, or contact your hosting provider.

This is a shorter form of the same message,so assume its the same cause.

jonathan1055 → changed the visibility of the branch 3463044-different-mysql-database to hidden.

Re-run and we now have failures in d9-basic Max PHP (which passed before) and d10-plus but only Max PHP (so the failure is Previous Minor did not happen this time). It does appear to be random but failure high chance of failure with these two GTD bramches.

Both D7 pipelnes pass. Have we ever seen a failure of this in D7?

I don't think that D7 has that problem.

In core, for D10+, they still use "mysql", like us: https://git.drupalcode.org/project/drupal/-/blob/11.x/.gitlab-ci/pipelin...

Whereas in D7, they use "drupal": https://git.drupalcode.org/project/drupal/-/blob/7.x/.gitlab-ci/pipeline...

I still don't know if the problem is with the images (tho we use the same ones on D7 and D10...), or with the way that the scripts setup the creation of the database.

I added debug to simpletest-db to check what we get for SIMPLETEST_DB and the log shows
_TARGET_DB_TYPE=mysql, SIMPLETEST_DB=mysql://drupaltestbot:drupaltestbotpw@database/drupal_database

I also tried adding echo "show databases; show grants;" | vendor/bin/drush --root=$_WEB_ROOT sql:cli both before the tests run and after, but both give the error

In SqlBase.php line 115:                                                                  
  Unable to load Drupal settings. Check your --root, --uri, etc.   

The echo pipe drush is correct (checked locally), so either --root=$_WEB_ROOT does not apply here like it does elsewhere, or the database is not available, or something. I also tried removeing --root and got the same result. Is the database deleted after the tests have completed? I'm not sure of the sequence or architecture of installing in this job.

I know that a new database is created and then dropped for a set of tests, but that shouldn't affect the command that you are trying to run, as it's just about showing DBs and grants.

Maybe we can try running the command from the mysql binary directly and see if we get any additional information.