I have an instance in Amazon RDS with many databases, all of them accessed by PHP (cakephp 3.9) without any issues in Elastic Beanstalk.
I have a main user that can access all of them, and each DB has its own user as well.
The issue? I have created a new database in the RDS instance that returns a 1045 error (auth error), but I can connect with the same credentials to SequelPro or another kind of DB manager. The error is kind of weird because the credentials are 100% correct (the same I use in the Db manager).
I have exactly the same error with both users (root and DB user). As I commented above, I have many Dbs in the same instance, and all of them are accessible without issues (the error only happens for this new database). The error is this:
default-log-channel: ERROR: 2022-09-15 04:19:57 Error: [PDOException] SQLSTATE[HY000] [1045] Access denied for user 'myuser'@'172.61.15.102' (using password: YES)
Here are the dumps I got when checking the dsn query string,both dbs in the same instance (obviously I have changed pwds and names)
mysql:host=project-testing-1.xxxxx.eu-west-2.rds.amazonaws.com;port=3306;dbname=my_db;charset=utf8
**First query, the one that returns the error.**
array (size=15)
'driver' => string 'Cake\Database\Driver\Mysql' (length=26)
'persistent' => boolean false
'host' => string 'project-testing-1.xxxxx.eu-west-2.rds.amazonaws.com' (length=55)
'port' => string '3306' (length=4)
'username' => string 'root' (length=13)
'password' => string 'xxxx^sdsdsdsd@j&sdsd&' (length=30)
'database' => string 'my_db' (length=17)
'encoding' => string 'utf8' (length=4)
'timezone' => string '+0:00' (length=5)
'cacheMetadata' => boolean true
'log' => boolean false
'quoteIdentifiers' => boolean false
'name' => string 'my_db' (length=17)
'flags' =>
array (size=3)
12 => boolean false
1000 => boolean true
3 => int 2
'init' =>
array (size=2)
0 => string 'SET time_zone = '+0:00'' (length=23)
1 => string 'SET NAMES utf8' (length=14)
The second one works fine (same instance).
**mysql:host=project-testing-1.xxxxx.eu-west-2.rds.amazonaws.com;port=3306;dbname=-testing-db;charset=utf8**
array (size=15)
'driver' => string 'Cake\Database\Driver\Mysql' (length=26)
'persistent' => boolean false
'host' => string 'project-testing-1.xxxxx.eu-west-2.rds.amazonaws.com' (length=55)
'port' => string '3306' (length=4)
'username' => string 'root' (length=15)
'password' => string 'xxxx^sdsdsdsd@j&sdsd&' (length=40)
'database' => string 'testing-db' (length=24)
'encoding' => string 'utf8' (length=4)
'timezone' => string '+0:00' (length=5)
'cacheMetadata' => boolean true
'log' => boolean false
'quoteIdentifiers' => boolean false
'name' => string 'testing-db' (length=7)
'flags' =>
array (size=3)
12 => boolean false
1000 => boolean true
3 => int 2
'init' =>
array (size=2)
0 => string 'SET time_zone = '+0:00'' (length=23)
1 => string 'SET NAMES utf8' (length=14)
UPDATE: I just realized that the issue it is not in a particular database, the issue is about the second database connection, the first always runs OK, but the second one returns an auth error (even with the root user).
DSN query string.
mysql://root:xxxxxxx@project-testing-1.xxxxx.eu-west-2.rds.amazonaws.com/testing-db
Thanks.