RDS failure for AWS EC2 instance launched from AMI

Install / Update Deployment
, ,
1

Step1: I have a EC2 instance(Instance 1) where my custom app is hosted in production mode and it works fine
Step2: and I’ve created an AMI from Instance 1
Step3: now, whenever I launch a new EC2 instance(Instance2) from the AMI created in Step2, I’m always facing this issue:

“pymysql.err.OperationalError: (1045, “Access denied for user ‘_f166f15b655fbd77’@‘172.31.24.122’ (using password: YES)”)”

the issue occurs only for Instance2 but Instance1 works fine.

note: I’m using AWS RDS(mariadb) and not local MySQL

extended log:

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/gunicorn/workers/sync.py”, line 136, in handle
self.handle_request(listener, req, client, addr)
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/gunicorn/workers/sync.py”, line 179, in handle_request
respiter = self.wsgi(environ, resp.start_response)
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/werkzeug/local.py”, line 354, in application
return ClosingIterator(app(environ, start_response), self.cleanup)
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/werkzeug/wrappers/request.py”, line 206, in application
resp = f(*args[:-2] + (request,))
File “/home/ubuntu/myfrappe/apps/frappe/frappe/app.py”, line 87, in application
response = handle_exception(e)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/app.py”, line 268, in handle_exception
response = get_response(“message”, http_status_code=http_status_code)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/website/serve.py”, line 23, in get_response
response = ErrorPage(exception=e).render()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/website/page_renderers/error_page.py”, line 6, in init
super().init(path=path, http_status_code=http_status_code)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/website/page_renderers/template_page.py”, line 28, in init
self.set_template_path()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/website/page_renderers/template_page.py”, line 36, in set_template_path
for app in frappe.get_installed_apps(frappe_last=True):
File “/home/ubuntu/myfrappe/apps/frappe/frappe/init.py”, line 1099, in get_installed_apps
installed = json.loads(db.get_global(“installed_apps”) or “”)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 813, in get_global
return self.get_default(key, user)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 817, in get_default
d = self.get_defaults(key, parent)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 835, in get_defaults
defaults = frappe.defaults.get_defaults(parent)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/defaults.py”, line 77, in get_defaults
globald = get_defaults_for()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/defaults.py”, line 193, in get_defaults_for
res = frappe.qb.from_(table).where(
File “/home/ubuntu/myfrappe/apps/frappe/frappe/query_builder/utils.py”, line 62, in execute_query
return frappe.db.sql(query, params, *args, **kwargs) # nosemgrep
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 131, in sql
self.connect()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 75, in connect
self._conn = self.get_connection()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/mariadb/database.py”, line 75, in get_connection
conn = pymysql.connect(
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/connections.py”, line 353, in init
self.connect()
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/connections.py”, line 633, in connect
self._request_authentication()
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/connections.py”, line 907, in _request_authentication
auth_packet = self._read_packet()
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/connections.py”, line 725, in _read_packet
packet.raise_for_error()
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/protocol.py”, line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/err.py”, line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, “Access denied for user ‘_f166f15b655fbd77’@‘172.31.24.122’ (using password: YES)”)
[2022-04-19 19:21:44 +0000] [1015] [ERROR] Error handling request /
Traceback (most recent call last):
File “/home/ubuntu/myfrappe/apps/frappe/frappe/app.py”, line 52, in application
init_request(request)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/app.py”, line 128, in init_request
frappe.local.http_request = frappe.auth.HTTPRequest()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/auth.py”, line 32, in init
self.set_session()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/auth.py”, line 69, in set_session
frappe.local.login_manager = LoginManager()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/auth.py”, line 123, in init
self.make_session(resume=True)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/auth.py”, line 205, in make_session
frappe.local.session_obj = Session(user=self.user, resume=resume,
File “/home/ubuntu/myfrappe/apps/frappe/frappe/sessions.py”, line 213, in init
self.resume()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/sessions.py”, line 279, in resume
validate_ip_address(self.user)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/auth.py”, line 389, in validate_ip_address
user = frappe.get_cached_doc(“User”, user) if not frappe.flags.in_test else frappe.get_doc(“User”, user)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/init.py”, line 875, in get_cached_doc
doc = get_doc(*args, **kwargs)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/init.py”, line 939, in get_doc
doc = frappe.model.document.get_doc(*args, **kwargs)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/model/document.py”, line 74, in get_doc
controller = get_controller(doctype)
File “/home/ubuntu/myfrappe/apps/frappe/frappe/model/base_document.py”, line 70, in get_controller
site_controllers[doctype] = _get_controller()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/model/base_document.py”, line 34, in _get_controller
module_name, custom = frappe.db.get_value(
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 391, in get_value
result = self.get_values(doctype, filters, fieldname, ignore, as_dict, debug,
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 463, in get_values
out = self._get_values_from_table(
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 677, in _get_values_from_table
r = self.sql(
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 131, in sql
self.connect()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/database.py”, line 75, in connect
self._conn = self.get_connection()
File “/home/ubuntu/myfrappe/apps/frappe/frappe/database/mariadb/database.py”, line 75, in get_connection
conn = pymysql.connect(
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/connections.py”, line 353, in init
self.connect()
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/connections.py”, line 633, in connect
self._request_authentication()
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/connections.py”, line 907, in _request_authentication
auth_packet = self._read_packet()
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/connections.py”, line 725, in _read_packet
packet.raise_for_error()
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/protocol.py”, line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File “/home/ubuntu/myfrappe/env/lib/python3.8/site-packages/pymysql/err.py”, line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, “Access denied for user ‘_f166f15b655fbd77’@‘172.31.24.122’ (using password: YES)”)

any inputs would be really helpful!

2

allow site’s db user to access RDS from any ip by using wildcard (%) as Host.

enter the RDS shell and execute query to set the user/host.

check: frappe_docker/troubleshoot.md at main · frappe/frappe_docker · GitHub

1 Like
4

thank you for the response, @revant_one

I’ve been getting this issue now:

ERROR 1044 (42000): Access denied for user ‘root’@‘%’ to database ‘<DB/Username>’

any thoughts on why this would happen? even though I’m logging in as a root user

extended log:

mysql -h <‘AWS RDS endpoint’> -P 3306 -u root -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 4397

Server version: 10.6.7-MariaDB-log managed by Managed SQL Database - Amazon Relational Database Service (RDS) - AWS

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> UPDATE mysql.user SET Host = ‘%’ where User = ‘<DB/Username>’; FLUSH PRIVILEGES;

Query OK, 1 rows affected (0.269 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Query OK, 0 rows affected (0.270 sec)

MariaDB [(none)]> SET PASSWORD FOR ‘<DB/Username>’@‘%’ = PASSWORD(‘’); FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.270 sec)

Query OK, 0 rows affected (0.270 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON <DB/Username>.* TO ‘<DB/Username>’@‘%’; FLUSH PRIVILEGES;

ERROR 1044 (42000): Access denied for user ‘root’@‘%’ to database ‘<DB/Username>’

Query OK, 0 rows affected (0.270 sec)

MariaDB [(none)]>

5

I think you can’t use root user to access RDS

you’ll have to use admin user. Or create new user with admin privileges and use that.

6

thanks again for the quick response, @revant_one

As suggested, I created an admin user and tried executing the command but still facing the same issue.

extended logs:

MariaDB [(none)]> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user;
±--------------±------------------±------------------------------------------±-----------±-----------+
| Host | User | Password | Grant_priv | Super_priv |
±--------------±------------------±------------------------------------------±-----------±-----------+
| localhost | mariadb.sys | | N | N |
| localhost | rdsadmin | ***************************************** | Y | Y |
| % | root | ***************************************** | Y | N |
| % | _f166f15b655fbd77 | ***************************************** | N | N |
| % | admin | ***************************************** | Y | N |
±--------------±------------------±------------------------------------------±-----------±-----------+
10 rows in set (0.314 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON admin.* TO ‘admin’@‘%’; FLUSH PRIVILEGES;
ERROR 1044 (42000): Access denied for user ‘admin’@‘%’ to database ‘admin’
Query OK, 0 rows affected (0.319 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON _f166f15b655fbd77.* TO ‘_f166f15b655fbd77’@‘%’; FLUSH PRIVILEGES;
ERROR 1044 (42000): Access denied for user ‘root’@‘%’ to database ‘_f166f15b655fbd77’
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> exit
Bye

The problem is that AWS RDS doesn’t allow to create a Super user. But, when I do anything with admin user, it should allow me to do everything but it isn’t, I really don’t have any clue on what should be done to mitigate this issue.

7

RDS doesn’t allow you to grant ALL PRIVILEGES

use

“SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, LOCK TABLES”

instead of “ALL PRIVILEGES”

1 Like
8

that was it, you’re awesome @revant_one

9

@revant_one one more question wrt horizontal scaling:
just like using RDS for the database, what’s the recommended way to sync all logs into one place?

10

is there any solution/recommendation/suggestion for this? @revant_one

11

I case of EC2 instances they can be dumped into a common AWS EFS volume.

In case of EKS they can be viewed using CloudWatch.

1 Like