Access denied for additional DB services (mysql, mariadb)

[denics]

Dear all, this is related to #671 that have been closed without a real solution. I am trying to use lando with civicrm with no success because the service cannot access the second database.

This is my lando.yml (part of)

recipe: drupal7
config:
  php: '5.6'
  via: 'apache:2.4'
  webroot: 'web'
  database: 'mariadb:10.1'
  drush: ^8
  xdebug: true
services:
  appserver:
  node:
    type: node:10
  civicrm:
    type: mariadb:10.1

This is the relevant lando info:

[
  {
    service: 'appserver',
    urls: [
      'https://localhost:33118',
      'http://localhost:33119',
      'http://unesco.lndo.site',
      'https://unesco.lndo.site',
      'http://*.unesco.lndo.site',
      'https://*.unesco.lndo.site'
    ],
    type: 'php',
    via: 'apache:2.4',
    webroot: 'web',
    version: '5.6',
    meUser: 'www-data',
    hostnames: [
      'appserver.civicrm.internal'
    ]
  },
  {
    service: 'database',
    urls: [],
    type: 'mariadb',
    internal_connection: {
      host: 'database',
      port: '3306'
    },
    external_connection: {
      host: 'localhost',
      port: '33120'
    },
    creds: {
      database: 'drupal7',
      password: 'drupal7',
      user: 'drupal7'
    },
    config: {
      database: '/home/.../.lando/config/drupal7/mysql.cnf'
    },
    version: '10.1',
    meUser: 'www-data',
    hostnames: [
      'database.civicrm.internal'
    ]
  },
  {
    service: 'node',
    urls: [],
    type: 'node',
    config: {},
    version: '10',
    meUser: 'node',
    hostnames: [
      'node.civicrm.internal'
    ]
  },
  {
    service: 'civicrm',
    urls: [],
    type: 'mariadb',
    internal_connection: {
      host: 'civicrm',
      port: '3306'
    },
    external_connection: {
      host: 'localhost',
      port: 'not forwarded'
    },
    creds: {
      database: 'database',
      password: 'mariadb',
      user: 'mariadb'
    },
    config: {},
    version: '10.1',
    meUser: 'www-data',
    hostnames: [
      'civicrm.civicrm.internal'
    ]
  }
]

If I ssh into my civicrm service:

lando ssh -s civicrm

www-data@0fd6ac81f148:/app$ mysql -u mariadb -pmariadb civicrm
ERROR 1044 (42000): Access denied for user 'mariadb'@'%' to database 'civicrm'

lando ssh -s civicrm

www-data@0fd6ac81f148:/app$ mysql -u mariadb -pmariadb        
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33157
Server version: 10.1.41-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

so it seems that the user "mariadb" has access to the DB server, but not to the database???

[sluc23]

I had the same issue, couldn't make lando work with Drupal & CiviCRM, having both systems in separate DBs

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions and please check out this if you are wondering why we auto close issues.

[denics]

Activity. Maybe we should open an issue complaining about the @stale bot :)

[tarikflz]

@denics command: '--default-authentication-plugin=mysql_native_password' this might be temporary solution for you

[denics]

thanks @tarikflz , I'll try. I think this is important if we want to bring the CiviCRM community onboard.

[runofthemill]

I had a similar issue - it seems like the db user that gets created has very limited privileges, so I added a run step which fixed it. Note I was dealing with just a db service, and not Drupal or CiviCRM.

# .lando.yml
database:
    type: mariadb:10.1
    portforward: 32779
    creds:
      user: mariadb
      password: mariadb
      database: database
    run:
      - mysql -uroot < /app/scripts/db-setup.sql

# db-setup.sql
GRANT ALL PRIVILEGES ON *.* TO 'mariadb'@'%';
FLUSH PRIVILEGES;

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions and please check out this if you are wondering why we auto close issues.

[denics]

Please @stale don't close this :)

[gitressa]

I can import the two databases fine into Drupal 7, and browse the contacts, but if I try to update a contact I get this: Fatal error: Uncaught CRM_Core_Exception: [0: Transaction integrity error: Expected to find active frame thrown in /app/sites/all/modules/civicrm/Civi/Core/Transaction/Manager.php on line 107

Also, a custom view doesn't work. I get the error SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'drupal7'@'172.90.32.2' for table 'civicrm_contact'.

On the documentation page for Drupal views integration there is a link to the "Settings - Drupal integration", but these suggestions are missing: http://example.org/civicrm/admin/setting/uf?reset=1

My .lando.yml:

name: civicrm
recipe: drupal7
config:
  webroot: .

services:
  civicrm_db5:
    type: mysql
    creds:
      user: drupal7
      password: drupal7
      database: database

Example definition from civicrm.settings.php: define( 'CIVICRM_UF_DSN' , 'mysql://drupal7:drupal7@civicrm_db5/database?new_link=true' );

import, first Drupal then CiviCRM DB:

lando db-import civicrm_db2.sql
lando db-import civicrm_db5.sql --host civicrm_db5

Perhaps I need to run some of the methods to clear cache found on the Moving an Existing Installation to a New Server or Location page?

It would be awesome with a simple example on how to get CiviCRM to work with Lando.

[tbenice]

I ran into something similar with adding and connecting to a migration db inside the appserver.

To figure it out i did: lando mysql -h [my-second-db-internal-host] -A [my-drupal-db] show grants;

returns: GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION

So lando doesnt add any additional users, only root user, so you'd use:

define( 'CIVICRM_UF_DSN' , 'mysql://root:drupal7@civicrm_db5/database?new_link=true' );

Or you could add some tooling to your .lando to add a user with different privileges if you wanted.

Hope that helps!

[gitressa]

Thanks @tbenice! I tried your suggestion, but got a White Screen of Death, and errors with messages like Initialization Error, DB Error: connect failed, Access denied for user 'root'@'172.90.33.2'.

In stead, I tried updating the user in my .lando.yml settings, and that seemed to do the trick:

services:
  civicrm_db5:
    type: mysql
    creds:
      user: root
      password: drupal7
      database: database 

I initially thought this was the solution, because I could update a contact. But then I got the error again. Looking in the error log, I noticed some weird bits about The user specified as a definer ('webhotel_user'@'%') does not exist", which looked like old lingering online cruft ... Also, it looks like I actually would have needed to lando destroy, not just lando rebuild for it to take effect.

Anyway, I installed CiviCRM Clear All Caches, and ran it, and I can now update contacts. It seems to make no difference if I define user: root or user: drupal7 ... which makes sense, re the need to lando destroy. I did try to lando destroy and lando start with user: root, but it seemed to hang indefinitely at Waiting until civicrm_db5 service is ready... which never completed.

I still get SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'drupal7'@'172.90.33.2' for table 'civicrm_contact' though.

I noticed this bit on the CiviCRM Views integration page:

2. If you have different database users for your CiviCRM and Drupal databases, then you need to grant SELECT access for the Drupal user to all the CiviCRM tables.

So, like you mentioned, this sounds like the way to go. Do you have an example?

Or you could add some tooling to your .lando to add a user with different privileges if you wanted.

[gitressa]

In the end I got the fundamentals working (Create, update, delete) with this in .lando.yml to connect the CiviCRM database:

services:
  civicrm_db:
    type: mysql
    creds:
      user: drupal7
      password: drupal7
      database: database

Example connection from civicrm.settings.php:

define( 'CIVICRM_UF_DSN' , 'mysql://drupal7:drupal7@civicrm_db/database?new_link=true' );

Import databases, first Drupal then CiviCRM DB with this:

lando db-import civicrm_drupal.sql
lando db-import civicrm_db.sql --host civicrm_db

I also installed and ran the CiviCRM Clear All Caches, to clear out old lingering stuff in the cache tables. It might be easiest to install the module in production, before exporting database and files, so it's readily available for usage when importing.

Like I wrote above, CiviCRM Views integration still doesn't work. If anyone get it working, feel free to share the solution here.

Since migrating from Drupal 7 to Drupal 9 also involves connecting two databases, to help those who finds this page searching for assistance with this, here is how I connect the Drupal 7 database in .lando.yml:

services:
  d7db:
    type: mariadb
    creds:
      user: drupal7db
      password: drupal7db
      database: drupal7db
    portforward: true

In settings.php:

$databases['migrate']['default'] = [
  'database' => 'drupal7db',
  'username' => 'drupal7db',
  'password' => 'drupal7db',
  'prefix' => '',
  'host' => 'd7db',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
];

Import Drupal 7 database with this: lando db-import --host=d7db --user=drupal7db example_org.sql.gz

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions and please check out this if you are wondering why we auto close issues.

[gitressa]

Thanks Stalebot, but this issue is still relevant.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions and please check out this if you are wondering why we auto close issues.

[ptmkenny]

Not stale; having this issue now.

[alex-r-redfern]

Ditto. Seems to be only happening to me when I have a second database instance in play.

lando file:

name: project34
recipe: wordpress
config:
  php: '8.0'
  webroot: web
  via: nginx
  xdebug: true
services:
  appserver:
    xdebug: true
    overrides:
      environment:
        PHP_IDE_CONFIG: "serverName=lando"
  node:
    type: node:14
  mailhog:
    type: mailhog
    portforward: true
  database:
    type: mysql:8.0.29
    portforward: 49180
    configuration:
      properties:
        max_allowed_packet: 1073741824
    creds:
      user: wordpress
      password: wordpress
      database: wordpress
  database2:
    type: mysql:8.0.29
    portforward: 49181
    configuration:
      properties:
        max_allowed_packet: 1073741824
    creds:
      user: wordpress
      password: wordpress
      database: wordpress
tooling:
  npm:
    service: node

Once spun up, I can connect to the second DB fine with workbench, and add tables there manually, but running mysql -u wordpress -p --host=localhost --port=49181 wordpress < dump.sql gives the error ERROR 1698 (28000): Access denied for user 'wordpress'@'localhost'

The only caveat is that for workbench to connect, I had to add the following connection preferences: allowPublicKeyRetrieval = true useSSL = false

Very odd

[mialdi98]

So in my case, the user was named 'user' that's how to grant all privileges:

   run:
      - mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';FLUSH PRIVILEGES;"

So the end result will look like this:

services:
  database:
    type: mariadb:10.4
    portforward: 3310
    creds:
      user: user
      password: user
      database: d9
    run:
      - mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';FLUSH PRIVILEGES;"

Import/Deletion of the newly created DBs working fine.