getting error when connecting from CI to mysql RDS

I am getting this error when I try to connect from my CI based app to mysql RDS: 

Message: mysqli::real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

But I am able to connect to mysql RDS via phpMyAdmin. What do I need to modify in my database.php file to get this to work? Or must I change the mySQL running on the AWS server? Here is what I currently have:

$db['default'] = array(
    'dsn' => '',
    'hostname' => 'database-2.cwymdn16cxes.us-east-1.rds.amazonaws.com',
    'username' => 'root',
    'password' => 'xxxx',
    'database' => 'database-2',
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

proof that an old dog can learn new tricks

https://stackoverflow.com/questions/5002...ng-sha2-pa

https://stackoverflow.com/questions/5002...ng-sha2-pa

Thanks. I am using php 5.6. And I did see that stackoverflow. I just think that modifying the AWS RDS install is "playing with fire". I was hoping there was some simple way to modify the call from CI.

Honestly I don't even know how to login to mysql running on RDS? Is that doable from my CLI or do I need to install and SSH client to even connect?

proof that an old dog can learn new tricks

I am using php 5.6.

Are you able to upgrade? PHP 5.6 became end-of-life at the end of 2018.

I am using php 5.6.

Are you able to upgrade? PHP 5.6 became end-of-life at the end of 2018.

Anything is possible with enough effort! Right now I am immersed in trying to get this thing up on AWS.

proof that an old dog can learn new tricks

https://stackoverflow.com/questions/5002...ng-sha2-pa

Thanks. I am using php 5.6. And I did see that stackoverflow. I just think that modifying the AWS RDS install is "playing with fire". I was hoping there was some simple way to modify the call from CI.

Honestly I don't even know how to login to mysql running on RDS? Is that doable from my CLI or do I need to install and SSH client to even connect?

You can change php version.

"As of PHP 7.4, this is no longer an issue. Support for caching_sha2 authentication method has been added to mysqlnd."

OK. Is there any significant chance that upgrading to php 7.4 will lead to substantial problems with my existing code?

proof that an old dog can learn new tricks

There is no guarantee that your code will work.

https://www.php.net/manual/en/mysqli.requirements.php

Thanks for all of your help. I managed to get this working by installing an add on authentication library.

proof that an old dog can learn new tricks