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.

running the command from the mysql binary directly

OK. That's a good idea. First I added
mysql --user=root --password="$MYSQL_ROOT_PASSWORD" --host=mysql --execute "show databases;"
but this produces
ERROR 2005 (HY000): Unknown MySQL server host 'mysql' (-2)

I changed the host to 'localhost'
$ mysql --user=root --password="$MYSQL_ROOT_PASSWORD" --host=localhost --execute "show databases;"
and got a different error
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Then without any --host
mysql --user=root --password="$MYSQL_ROOT_PASSWORD" --execute "show databases;"
we get the same
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)

I've tried to search for examples, to save me guessing, but not found it yet. I'm sure it is simple if you know.

The host might be “database” per https://git.drupalcode.org/project/gitlab_templates/-/blob/main/includes...

Thanks. I've changed to --host=database but got ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES). Then I realised the user should be $MYSQL_USER not root. Now it works.

https://git.drupalcode.org/project/gitlab_templates_downstream/-/pipelin...

But recently we have not had any failures like we've seen in #23 and #32. That was the reason for adding these debug lines in the first place.

I ran this on Scheduler and I did get one of the errors as seen before
https://git.drupalcode.org/issue/scheduler-3445052/-/jobs/5946963#L610
It was not using this MR but just set

.test-variables:
  variables:
    MYSQL_DATABASE: my_own_db

but I also added the mysql debug output. It all looks fine.

Then re-ran the job and it passed
https://git.drupalcode.org/issue/scheduler-3445052/-/jobs/5946986

Re-running other pipelines here: https://git.drupalcode.org/issue/gitlab_templates-3463044/-/pipelines/55...

The output and checks added are great, they should stay regardless (tho maybe collapsed).

The error still shows in this one for example: https://git.drupalcode.org/project/api/-/jobs/5958461

We can see how the _new_ database was created, but not the per-test instance. We have the new output so we can debug.

The granting of priviledges is done here: https://git.drupalcode.org/project/drupalci_environments/-/blob/dev/db/m... (for mysql 8).

So do you think it is a permissions problem? The new drupal_database does appear to still exist at the end of the job, so the message 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. still confuses me.

I think the reporting might be misleading actually.

The server reports the following message: SQLSTATE[3D000]: Invalid catalog name: 1046 No database selected:...

Maybe Drupal isn't doing the use DATABASE_NAME; before attempting to create the new table. If by default, it falls back into mysql, that could explain why it works when we have that value and why it doesn't when we change it.

It might be core issue actually when creating the whole test database. I don't know where exactly in the code it might be located but it might be that.

I've been digging a bit on the code and couldn't find any instance of "mysql" where the context could be changed. Usually the create database DB_NAME is followed by use DB_NAME;.

Maybe if a whole new connection where the DB doesn't need to be created needs a use DB_NAME;.

That's something that we could test right before calling the script to run the tests: sudo -u www-data -H -E php web/core/scripts/run-tests.sh, at least to discard that part. I'm not sure it might be enough but it might be worth trying.

A new Scheduler pipeline got one failure
https://git.drupalcode.org/issue/scheduler-3445052/-/jobs/5971134#L620
But a simple re-run passed.
https://git.drupalcode.org/issue/scheduler-3445052/-/jobs/5971213#L551

It can't be a proper bug/fault in any code if the problem appears to be random and re-running fixes it. I was wondering if it is caused by a race condition or some conflict due to timing? That made me think if we have ever seen this failure when the phpunit job is run as one single job, i.e. not in parallel as a matrix of test groups?

More maybe a run-tests.sh vs phpunit binary perhaps. But in any case, the randomness of it make the whole thing more difficult to test/debug.

The last project where we saw this is the "api" project. There, we use run-tests.sh and just split the job in three parallel runs: https://git.drupalcode.org/project/api/-/blob/2.x/.gitlab-ci.yml?ref_typ...

We should try:
- No parallel runs and run-tests.sh
- phpunit binary

I created 📌 [ignore] Help with gitlab_templates issue Active . Feel free to play with it as much as needed.

I'm thinking that maybe we can add debug via a local patch in here: https://git.drupalcode.org/project/drupal/-/blob/11.x/core/lib/Drupal/Co...

We could print connection details, database in use or any extra info that we need before the "try" block.

That's a good idea. It is actually runTasks() where the error is reported, when trying to create drupal_install_test so we need to get info right before that happens.