DB Error: connect failed when trying to call Job.execute using cli.php?

Question

I've set up a new CiviCRM install on a new Drupal install and I'm trying to configure Cron using the instructions at https://docs.civicrm.org/sysadmin/en/latest/setup/jobs/.

I'm a complete newbie at both CiviCRM and Drupal, and both the cv and Drush methods look like they need extra tools installed, so I've gone with the cli.php method.

I'm logged in to my host using SSH and using the command from the manual (obviously completed with the paths, domain, and credentials for my site):

$ /path/to/php /path/to/civcrm/bin/cli.php \
  -s example.org \
  -u myusername \
  -p mypassword \
  -e Job \
  -a execute

That returns a bunch of HTML, obviously designed to be shown in a browser not a terminal, in which the only recognisable error message is 'DB Error: connect failed'.

Can anyone tell me what I'm doing wrong?

Edit: OK, so I've tested getting the password wrong and that gets the same error, so I guess it's possible that I have a credentials problem of some kind. But I'm using the username and password of a Drupal administrator user that works just fine logging into the CMS via a browser.

The only other thing I can think is that I think my file installation and my database are on different servers. But I filled in all those details when installing Drupal/CiviCRM and I don't see any errors in the GUI. Do I need to manually edit a script somewhere?

Further Edit: Still without joy, I gave up on the cli.php method and tried the HTTP method documented on the same page above. Trying to open the following URL in a browser results in exactly the same error, though obviously presented properly instead of raw HTML.

http://<siteurl>/sites/all/modules/civicrm/bin/cron.php?name=<username>&pass=<password>&key=<sitekey>

Can that narrow down the source of the problem at all?

Yet Further Edit: Looking in the logs I see the following:

[code] => -24
[message] => DB Error: connect failed
[mode] => 16
[debug_info] =>  [nativecode=Access denied for user '<drupal_database_user>'@'<database_ip>' (using password: YES)]
[type] => DB_Error
[user_info] =>  [nativecode=Access denied for user '<drupal_database_user>'@'<database_ip>' (using password: YES)]
[to_string] => [db_error: message="DB Error: connect failed" code=-24 mode=callback callback=CRM_Core_Error::handle prefix="" info=" [nativecode=Access denied for user '<drupal_database_user>'@'<database_ip>' (using password: YES)]"]

I was surprised to see that it seems to be trying to connect with the user I had set up for the Drupal database, rather than the user for the CiviCRM database. That user originally only had Select access to the CiviCRM database. I've now given both users full access to both databases (which instinctively feels like a non-ideal setup?), but I'm still getting the same error.

Answer 1

First, you can view the error with greater detail if you enable "Debug and Backtrace", or view the CiviCRM log. The troubleshooting documentation will tell you how to do both.

Second, I would assume that "connect failed" is a MySQL error. Is it possible that you have two civicrm.settings.php files in your path, one with the wrong MySQL password?

Finally - yes, drush and cv are additional tools to install. However, if you're administering Drupal/CiviCRM through SSH, these tools are amazing timesavers. Configuring cron with cv or drush is 10 times easier than what you're doing here, even if you factor in the overhead of installing drush or cv. Drush in particular will give you a number of other command-line superpowers - you'll be glad you installed it.

Answer 2

I was also going to add that there are alternatives, e.g. CiviHosting offer this method which can be done via the hosting control panel https://civihosting.com/blog/how-to-make-a-cron-job-for-civicrm/

And then you can also use an extension to use the Drupal cron to trigger the CiviCRM cron: https://civicrm.org/extensions/civicrm-cron

Not quite as satisfying as figuring it out via the SSH route but if you've tried several times and to no avail, this might just get it going for you.

Answer 3

And after all that, it looks like it was a password error after all, just not the password I thought.

cli.php is retrieving the Drupal MySQL username and password from CIVICRM_UF_DSN in civicrm.settings.php as Jon G suggested. The instructions in that file say 'If any of these contain a single quote or backslash, escape those characters with a backslash: \' and \, respectively.' My password contained a double quote, and whichever script had generated that file put a backslash before the double quote: \". Taking the slash out seems to let the command run fine.