Docker SQL authentication error after update

[adnix42]

I am running Firefly III version 4.7.8 in Docker.

After update I started the upgrade process with the command "docker exec -it php artisan migrate". This generates the following errors:

[2018-10-29 09:50:05] local.ERROR: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: select * from information_schema.tables where table_schema = firefly_iii and table_name = migrations) {"exception":"[object] (Illuminate\Database\QueryException(code: 2054): SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: select * from information_schema.tables where table_schema = firefly_iii and table_name = migrations) at /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: 2054): SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client at /var/www/firefly-iii/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:50, PDOException(code: 2054): SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client at /var/www/firefly-iii/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:46, PDOException(code: 0): PDO::__construct(): The serverrequested authentication method unknown to the client [caching_sha2_password] at /var/www/firefly-iii/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:46)
[stacktrace]
#0 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback('select * from i...', Array, Object(Closure))
#1 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\Database\Connection->run('select * from i...', Array, Object(Closure))
#2 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Schema/MySqlBuilder.php(18): Illuminate\Database\Connection->select('select * from i...', Array)
#3 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Migrations/DatabaseMigrationRepository.php(169): Illuminate\Database\Schema\MySqlBuilder->hasTable('migrations')
#4 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php(556): Illuminate\Database\Migrations\DatabaseMigrationRepository->repositoryExists()
#5 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php(91): Illuminate\Database\Migrations\Migrator->repositoryExists()
#6 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Database/Console/Migrations/MigrateCommand.php(63): Illuminate\Database\Console\Migrations\MigrateCommand->prepareDatabase()
#7 [internal function]: Illuminate\Database\Console\Migrations\MigrateCommand->handle()
#8 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(29): call_user_func_array(Array, Array)
#9 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(87): Illuminate\Container\BoundMethod::Illuminate\Container\{closure}()
#10 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(31): Illuminate\Container\BoundMethod::callBoundMethod(Object(Illuminate\Foundation\Application), Array, Object(Closure))
#11 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Container/Container.php(572): Illuminate\Container\BoundMethod::call(Object(Illuminate\Foundation\Application), Array, Array, NULL)
#12 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Console/Command.php(183): Illuminate\Container\Container->call(Array)
#13 /var/www/firefly-iii/vendor/symfony/console/Command/Command.php(255): Illuminate\Console\Command->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
#14 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Console/Command.php(170): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
#15 /var/www/firefly-iii/vendor/symfony/console/Application.php(886): Illuminate\Console\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#16 /var/www/firefly-iii/vendor/symfony/console/Application.php(262): Symfony\Component\Console\Application->doRunCommand(Object(Illuminate\Database\Console\Migrations\MigrateCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /var/www/firefly-iii/vendor/symfony/console/Application.php(145): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Console/Application.php(89): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 /var/www/firefly-iii/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(122): Illuminate\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#20 /var/www/firefly-iii/artisan(37): Illuminate\Foundation\Console\Kernel->handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#21 {main}
"}

In Connection.php line 664:

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: select * from information_schema.tables where table_schema = firefly_iii and table_name = migrations)

In PDOConnection.php line 50:

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

In PDOConnection.php line 46:

SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

In PDOConnection.php line 46:

PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]

Steps to reproduce
Happens every time I run the first upgrade command "docker exec -it php artisan migrate"

Extra info
OS: Unraid 6.6.3.

[JC5]

How are you authenticating to your MySQL server? Is it also Docker?

[adnix42]

The container has been authenicating to MySQL with the container variables FF_DB_NAME, FF_DB_USER and FF_DB_PASSWORD. I have been able to authenticate directly into the mysql terminal with the variables assigned in the Firefly container settings.

[JC5]

Open database mysql, table users, and find the Firefly III user. What does it say under the "plugin" column?

[adnix42]

caching_sha2_password

[JC5]

Mmm. Did you recently reinitialize or upgrade your MySQL container?

You can find plenty online about the cause (old vs new way of storing passwords) but I am not sure why Firefly III is disagreeing with your server all of a sudden.

[adnix42]

I believe I did push an update to SQL over a week ago. However, I was in the Firefly application this morning without problem. It wasnt until I pushed the 4.7.8 FF update that connectivity became an issue.

I have been doing my research on the error but am drowning in information and possible resolutions.

[JC5]

Which version did you upgrade from? 4.7.7 or earlier?

[adnix42]

4.7.7

[JC5]

You could change the entry to "mysql_native_password" and update the password to whatever the environment variable is:

ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY '<password>';

[adnix42]

I was just in the middle of doing that conversion when you posted. This resolved the issue. Odd but what ya gonna do ¯\(ツ)/¯