MySQL 8 Causes Lando to fail startup

[alessandro-bonfatti-pv]

I'm trying to use Lando v3.0.0-rc.1 with the following configuration:

name: test-project
recipe: drupal8
config:
  webroot: docroot
  php: '7.2'
  xdebug: true
  conf:
    php: config/php.ini

services:
  appserver:
    overrides:
      services:
        environment:
          PHP_IDE_CONFIG: "serverName=test-project.lndo.site"
  database:
    type: mysql:8
    portforward: 3307

but when I start the environment the database doesn't get ready:

Waiting until database service is ready...
Waiting until database service is ready...
Waiting until database service is ready...
Waiting until database service is ready...
Waiting until database service is ready...
Waiting until database service is ready...
Waiting until database service is ready...
Waiting until database service is ready...
Waiting until database service is ready...
Waiting until database service is ready...
warn: Service database is unhealthy
warn: Run "lando logs -s database"

and the log shows:

database_1      | user-perms.sh kicking off as user uid=0(root) gid=0(root) groups=0(root)
database_1      | Lando ENVVARS set at
database_1      | LANDO_WEBROOT_USER: www-data
database_1      | LANDO_WEBROOT_GROUP: www-data
database_1      | LANDO_WEBROOT_UID: 33
database_1      | LANDO_WEBROOT_GID: 33
database_1      | Making sure correct user:group (www-data:www-data) exists...
database_1      | 33
database_1      | 33
database_1      | And here. we. go.
database_1      | Scanning /user/.ssh for keys...
database_1      | Scanning /lando/keys for keys...
database_1      | Checking whether /user/.ssh/.DS_Store is a private key...
database_1      | Checking whether /user/.ssh/config is a private key...
database_1      | Checking whether /user/.ssh/elasticpath.pem is a private key...
database_1      | Checking whether /user/.ssh/id_rsa is a private key...
database_1      | Ensuring permissions for /user/.ssh/elasticpath.pem...
database_1      | Ensuring permissions for /user/.ssh/id_rsa...
database_1      | Using the following keys: /user/.ssh/elasticpath.pem /user/.ssh/id_rsa
database_1      | Running command docker-entrypoint.sh mysqld
database_1      | Initializing database
database_1      |  100
database_1      |  100
database_1      | 2019-02-13T23:08:42.842846Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
database_1      | 2019-02-13T23:08:42.842910Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
database_1      | 2019-02-13T23:08:42.843548Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.15) initializing of server in progress as process 107
database_1      | 2019-02-13T23:08:48.224874Z 0 [Warning] [MY-010161] [Server] You need to use --log-bin to make --expire_logs_days work.
database_1      | 2019-02-13T23:08:49.020207Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_limit=1M'.
database_1      | 2019-02-13T23:08:49.020216Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
database_1      | 2019-02-13T23:08:49.020221Z 0 [ERROR] [MY-013236] [Server] Newly created data directory /var/lib/mysql/ is unusable. You can safely remove it.
database_1      | 2019-02-13T23:08:49.020449Z 0 [ERROR] [MY-010119] [Server] Aborting
database_1      | 2019-02-13T23:08:50.933407Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15)  MySQL Community Server - GPL.

Is there something I can do to solve this issue? I need to use MySQL 8 and I cannot downgrade to 5.x nor use another database.

[pirog]

@alessandro-bonfatti-pv you might have better luck on one of the newer versions
https://github.com/lando/lando/releases

id try one of those and then reopen this if you still have issues

[alessandro-bonfatti-pv]

I had the same issue after I updated Lando to the new release. I think the error is related to the mysql.cnf file used by lando (

lando/plugins/lando-recipes/recipes/drupal8/mysql.cnf

Line 33 in fbfaa88

query_cache_limit = 1M

). In my case Lando (latest release) used its default mysql configuration file which was the same used for MySQL 7. The log statement is kind of explicative:

database_1      | 2019-02-13T23:08:49.020207Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_limit=1M'.

I solved my issue by using a custom configuration file without the following:

#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 64M

[pirog]

@alessandro-bonfatti-pv this is fixed in RC10 but note that you need to lando destroy && lando start because the underlying image has changed

[alessandro-bonfatti-pv]

@pirog rc10 fixed that issue but I get a new error now:

[Tue Feb 19 19:30:16.848129 2019] [php7:notice] [pid 111] [client 172.21.0.2:46768] PDOException: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client in /app/core/lib/Drupal/Component/DependencyInjection/PhpArrayContainer.php on line 79 #0 /app/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php(420): PDO->__construct('mysql:host=data...', 'drupal8', 'drupal8', Array)\n#1 /app/core/lib/Drupal/Core/Database/Database.php(371): Drupal\\Core\\Database\\Driver\\mysql\\Connection::open(Array)\n#2 /app/core/lib/Drupal/Core/Database/Database.php(166): Drupal\\Core\\Database\\Database::openConnection('default', 'default')\n#3 [internal function]: Drupal\\Core\\Database\\Database::getConnection('default')\n#4 /app/core/lib/Drupal/Component/DependencyInjection/PhpArrayContainer.php(79): call_user_func_array('Drupal\\\\Core\\\\Dat...', Array)\n#5 /app/core/lib/Drupal/Component/DependencyInjection/Container.php(171): Drupal\\Component\\DependencyInjection\\PhpArrayContainer->createService(Array, 'database')\n#6 /app/core/lib/Drupal/Component/DependencyInjection/PhpArrayContainer.php(260): Drupal\\Component\\DependencyInjection\\Container->get('database', 1)\n#7 /app/core/lib/Drupal/Component/DependencyInjection/PhpArrayContainer.php(62): Drupal\\Component\\DependencyInjection\\PhpArrayContainer->resolveServicesAndParameters(Array)\n#8 /app/core/lib/Drupal/Component/DependencyInjection/Container.php(171): Drupal\\Component\\DependencyInjection\\PhpArrayContainer->createService(Array, 'cache.container')\n#9 /app/core/lib/Drupal/Core/DrupalKernel.php(543): Drupal\\Component\\DependencyInjection\\Container->get('cache.container')\n#10 /app/core/lib/Drupal/Core/DrupalKernel.php(904): Drupal\\Core\\DrupalKernel->getCachedContainerDefinition()\n#11 /app/core/lib/Drupal/Core/DrupalKernel.php(476): Drupal\\Core\\DrupalKernel->initializeContainer()\n#12 /app/core/lib/Drupal/Core/DrupalKernel.php(692): Drupal\\Core\\DrupalKernel->boot()\n#13 /app/index.php(19): Drupal\\Core\\DrupalKernel->handle(Object(Symfony\\Component\\HttpFoundation\\Request))\n#14 {main}

The Lando file is:

name: my-test
recipe: drupal8
config:
  webroot: .

services:
  database:
    type: mysql:8.0

I can work around it using default-authentication-plugin with mysql_native_password but it should work OOTB or be documented. Do you have new docs around it?

[pirog]

@alessandro-bonfatti-pv im guessing the thing that needs to work out of the box is the mysql client that drupal uses and not lando. See https://bugs.php.net/bug.php?id=76243

[alessandro-bonfatti-pv]

@pirog I may agree with that but some extra doc may help and the mysql.cnf could include a setting to avoid the error. The current status is that Lando mysql 8 doesn't work OOTB.

[pirog]

I don't think changing our default config for mysql8 because one client doesnt fully support it makes sense. Please note that Lando is meant for ALL KINDS of development, not just php, so a more accurate "current status" is that lando msyql 8 doesnt work OOTB with clients that dont currently/fully support mysql 8 which i think is expected.

I'm even hesitant to add a setting to a particular recipe eg drupal8 knowing that is a "workaround". We should wait till things are mature.

That said feel free to submit a PR with said documentation though, i feel like that is harmless as long as we remove it once this is fixed in the relevant upstreams.

[thejacer87]

just want to report that i got this issue too with the laravel recipe

[2019-04-29 16:56:24] local.ERROR: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE') {"exception":"[object] (Illuminate\\Database\\QueryException(code: 2054): SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE') at /app/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, PDOException(code: 2054): SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client at /app/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70, PDOException(code: 0): PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] at /app/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70)
[stacktrace]

[luispittagros]

@thejacer87 same thing is happening here

[federico-calo]

I got also this problem after having added solr service and after having rebuilt my app (Drupal9 / MySQL 8 / Solr 8)

[UnsettlingTrend]

I'm experiencing this same problem. When I try to set my mysql version to match my production environment (Drupal 9 with mySQL 8.0), it just hangs on "Waiting until database service is ready..." until it eventually fails, says it failed its health check, then shows the unknown variable query_cache_limit=1M'` error in the log.

If this is still the same issue, seems nearly 4 years is enough time to mature.

[pazthor]

I resolve that following Klonos' solution:
#2948 (comment)

[jonpugh]

Wow, the internet. I just started dealing with this today...

@pirog I think I figured out the issue based on that link above:

Note
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

Do the recipes my.cnf have that config in them?

[jonpugh]

UPDATE: No longer needed! see next comment.

So to clarify the fix to get MySQL 8 working:

1. Add to lando.yml:

database: mysql:8.0.22
config:
  database: config/my-custom.cnf

2. Copy a full my.cnf file to that path: Each DrupalX has one: https://github.com/lando/drupal/blob/main/recipes/drupal9/mysql.cnf#L30
3. Delete or comment out the deprecated settings:

#
# * Query Cache Configuration
#
# query_cache_limit = 1M
# query_cache_size = 64M

4. lando destroy && lando start
5. Wait a bit longer for mysql to startup. I had 6-7 "Waiting until" messages.
6. Profit!

I looked up @geerlingguy's my.cnf to be sure. It's an ansible template, so he conditions it on the version.

{% if '8.0.' not in mysql_cli_version.stdout %}
query_cache_type = {{ mysql_query_cache_type }}
query_cache_size = {{ mysql_query_cache_size }}
query_cache_limit = {{ mysql_query_cache_limit }}
{% endif %}

https://github.com/geerlingguy/ansible-role-mysql/blob/master/templates/my.cnf.j2#L90-L94

So, @pirog...

How do we handle this? Do the default database containers need these three settings?

FWIW these are the defaults in the geerlingguy.mysql ansible role. I checked and they are also the default for MySQL itself.

mysql_query_cache_type: "0"
mysql_query_cache_size: "16M"
mysql_query_cache_limit: "1M"

And here are the defaults in lando drupal9 recipe:

query_cache_size = 64M
query_cache_limit = 1M

Lando's cache size is 4.x bigger than a default geerlingguy.mysql ansible install, which is what we use for devshop.

Was this set larger for a reason? Does it improve performance? can we remove it from the all the existing recipies ? or would we need a whole new set for MySQL 8?

For now, the custom my.cnf file works!

Thanks @klonos

[jonpugh]

Whoah! I accidentally discovered an easier fix due to a typo.

I added a file to .lando.yml that does not exist. Lando created an empty directory at that path, but the database still started!

So instead of all this, just do this:

config:
  database: mysql:8.0.22
  config:
    database: /dev/null

Tada! 🚀

[reynoldsalec]

Interestingly enough, when I run with mysql:8.0 on a Drupal 9 recipe, the DB container spun up without an issue and seemed to operate correctly. However, was quickly able to replicate with mysql:8.0.22.

I believe this is because Lando tries to refer to a mysql8.cnf in the underlying laemp recipe when it gets a string match for 8.0 on mysql services. That mysql8.cnf file doesn't actually exist, but setting it basically creates the same condition that @jonpugh mentions in #1462 (comment); it's a null config file. However, any version of MySQL 8 other than 8.0 fails that conditional. Changing the conditional to look for 8. will force it to look for that mysql8.cnf.

Submitted a couple PRs to address this:

• Add mysql8.cnf files to all Drupal recipes. lando/lando#1462: Add mysql8.cnf files to all Drupal recipes. drupal#51
• Detect MySQL 8 database services and refer to mysql8.cnf lando/lando#1462: Properly detect MySQL 8.0 to add config. core#29
• Wordpress lando/lando#1426: MySQL 8.x compatibility for WordPress recipe. wordpress#32
• Laravel lando/lando#1462: Add compatibility for MySQL 8.x. laravel#30
• Backdrop lando/lando#1462: Add compatibility for MySQL 8.x. backdrop#23
• Symfony lando/lando#1462: MySql8 compatibility. symfony#20
• Joomla lando/lando#1462: MySQL8 compatibility for Joomla recipe. joomla#14