Support

Akeeba Backup for WordPress

#34043 CLI Backup Could not connect to MySQL

Posted in ‘Akeeba Backup for WordPress’
This is a public ticket

Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Environment Information

WordPress version
n/a
PHP version
n/a
Akeeba Backup version
n/a

Latest post by on Thursday, 17 December 2020 20:17 CST

Lehmann

In the WEB Interface i can backup the complete WEB Site without Problems with the function Default Backup Profile (One-Click Backup)

in the crontab i got an error -> unsupported authorisation Protocol

Database: mysqli, 8.0.22

/usr/bin/php /var/www/html/nextfinish/wp-content/plugins/akeebabackupwp/app/cli/backup.php --description="[DATE] [TIME]"

 

Where can i change this protocol for the command line ?

nicholas
Akeeba Staff
Manager

You need to contact your host about this. Developers of PHP software like us are not in control of the MySQL authorisation protocol used when PHP is connecting to MySQL. This is part of PHP itself by way of the libmysql version it was compiled against.

I suspect that /usr/bin/php is an older PHP version which does not understand the MySQL 8 authorisation protocol. Your host will most likely give you a different path to use for PHP 7.4, something you will use instead of /usr/bin/php in your CRON job.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Lehmann

The backup via the WEB interface but works on the same host, isn't the same php version and job used here?

nicholas
Akeeba Staff
Manager

While that's a common misconception, it doesn't have to be the same version and it usually isn't.

Most web hosts have multiple PHP versions installed at the same time. The typical setup we see in 2020 includes PHP 5.6, 7.0, 7.1, 7.2, 7.3 and 7.4. Versions 5.6, 7.0 and 7.1 are end of life but are provided as a means for older sites which do not support newer PHP versions to continue function for a while until they can be upgraded.

The PHP version to be used by the web server for your site is selected in the hosting control panel. Depending on the server setup this either modifies your site's .htaccess file or, more rarely, the server configuration for your site. This means that your site can run on a different PHP version than the default PHP CLI version.

/usr/bin/php typically refers to the default PHP version which in most cases is the oldest available version. Newer versions have different paths. We typically see paths like /usr/local/bin/php74 or /usr/bin/ea-php74. The exact path to the PHP 7.4 CLI binary depends on the server setup, that's why the Schedule Automatic Backups page of our software and our documentation says that you need to ask your host for it.

Also note that due to the very limited information you provided your problem may be something different entirely. You say that you saw the "unsupported authorisation Protocol" error in your crontab. First of all, your crontab only includes the commands to run and when to run them. It is not a log. So I had to guess that you are talking about a CRON error log. Second, the message "unsupported authorisation Protocol" with that bad grammar and capitalization is not something that MySQL or PHP outputs. I guess it's translated from German. The only thing I could use was an inference. MySQL 8 uses by default an authentication scheme that's incompatible with older PHP versions. You use PHP 7.4 in the site which is very new and does support it. But on the CLI you are using /usr/bin/php which is the default PHP version of the server and chances are it's an old version that doesn't speak MySQL 8's authentication protocol. That's why I am asking you to check that first. It's an educated guess based on the incomplete information I was given.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Lehmann

Very interesting, it's our own host. Other Joomla sites also run on this host, and the CLI backup for Joomla works without any problems.

Only this one WordPress causes us problems. Will try again tomorrow myself, maybe I'll find the mistake

In fact, it´s the same mysql database on the localhost !

 

nicholas
Akeeba Staff
Manager

If it's your own server try running /usr/bin/php -i and examine the following:

  • What is the PHP Version displayed at the top? Is it 7.4.12 or something else?
  • Check the Configure Command. Does it state --with-mysqli=mysqlnd or not?
  • Check the mysqli section of the output. What is the "Client API library version" listed?
  • If the client API library version starts with mysqlnd check the mysqlnd section in the output. What are the Loaded plugins listed? Do they include auth_plugin_caching_sha2_password and auth_plugin_sha256_password which are required for talking to MySQL 8?

Please remember the following facts:

  • Your server can have multiple different PHP versions installed. What you are using on the web server to run your site and what you are using with the command line may be different versions of PHP.
  • Even if you are using the same PHP version in the web server and the CLI their configuration files (php.ini and other .ini files loaded) may be different. For example, all Debian-based Linux distribution have a different php.ini for the Apache handler, the PHP-FPM, the CLI and the CGI binaries of PHP.
  • Each MySQL user can be configured to use a different password hashing scheme. It is possible that the Joomla site's database user is using MySQL's native password scheme (that was the default in MySQL 5.7 and earlier and the only scheme older PHP MySQL drivers would speak) but the WordPress one is using the caching SHA256 password scheme which is the new default in MySQL 8. This is extremely important if your server was upgraded from an earlier version of MySQL.
  • We do not have any control over PHP's MySQL driver and which authentication scheme it's going to be using. Both PHP and your MySQL server need to have an authentication protocol in common to talk to each other.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Lehmann

now up all is up and running !

we have 2 php versions 7.2 and 7.4 on this host

with the apache config we use the version 7.4

on the command line version 7.2

now i change the version in the command line from 7.2 to 7.4 with the command 

update-alternatives --config php

Auswahl Pfad Priorität Status
------------------------------------------------------------
0 /usr/bin/php.default 100 automatischer Modus
1 /usr/bin/php.default 100 manueller Modus
2 /usr/bin/php7.2 72 manueller Modus
* 3 /usr/bin/php7.4 74 manueller Modus

and change it to Position 3

Thanks for help

 

nicholas
Akeeba Staff
Manager

You're welcome!

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

System Task
system
This ticket has been automatically closed. All tickets which have been inactive for a long time are automatically closed. If you believe that this ticket was closed in error, please contact us.

Support Information

Working hours: We are open Monday to Friday, 9am to 7pm Cyprus timezone (EET / EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets outside of our working hours, but we cannot respond to them until we're back at the office.

Support policy: We would like to kindly inform you that when using our support you have already agreed to the Support Policy which is part of our Terms of Service. Thank you for your understanding and for helping us help you!