New site failed on AWS RDS

Install / Update Deployment
,
1

I installed Frappe on AWS EC2 and would like to deploy the database on RDS. I have setup a new Maria instance on RDS with all the collation, char set…etc. However when I run sudo bench new-site erpnext the following error shows up.

I traced the logs and source python file and looks like it’s trying to do GRANT ALL to the newly created user for that site. However AWS RDS does not allow GRANT SUPER privilege so the GRANT ALL does not work when creating new site.

Is there anyway to work around this issue? I’m thinking install the new site on local Database then copy the database to RDS, then change Strappe to point to new DB?

Thank you.

  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 72, in create_database_and_user
    dbman.grant_all_privileges(db_name, db_name)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/model/db_schema.py", line 538, in grant_all_privileges
    user, host))
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 176, in sql
    self._cursor.execute(query)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 516, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
    result.read()
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 1066, in read
    first_packet = self.connection._read_packet()
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet
    packet.check_error()
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1044, u"Access denied for user 'root'@'%' to database '76cf18db07302d32'")
2

By default new site creation makes new user and password with hash code and then it initiated the db.
So you can try creating the site in you local mariadb and later change site db setting to the rds.

I think that should work. But you have to make sure reinstall the site after changing the db host link , username and password.

4

Thank you @fkardame
Can you elaborate more on “reinstall site”? Is there other command than new-site?

5

It is running @lokesh
It’s hosted RDS service I have many databases running on it.
Thx

6
7

This problem persist. @berniezhao11 was clear when he indicated that it is not possible to use AWS DB services using GRANT ALL. Is it possible to change in an upcoming version to detail the DB profiles and not use SUPER?

8

Try this instead. Only the “GRANT ALL” statement is prohibited.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON db.* TO 'user'@'%'

Modify this line of code:

1 Like
9

Tks @tmatteson for you tip.

I changed the file, but got the following error:
pymysql.err.InternalError: (1221, u’Incorrect usage of DB GRANT and GLOBAL PRIVILEGES’)

So I restricted the privileges permissions to only Databases and Tables:

self.db.sql("GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,  INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, 
CREATE VIEW, SHOW VIEW, CREATE ROUTINE, REFERENCES, GRANT OPTION, ALTER ROUTINE, TRIGGER ON `%s`.* TO '%s'@'%s';" % (target, user, host))

And sucess!!!

I recommend reviewing the need for each of the permissions by including them in an upcoming release.

1 Like
10

Glad you got it working. This isn’t really a long-term solution. I suspect that something referencing site_cofig.json is the best way to do it.

Can you elaborate on this point? I’m not sure I follow what you’re saying.

11

Did you create any paramter group for rds?

12

To use RDS MariaDB:

Master user must be root.

Parameter group:

character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

nano sites/common_site_config.json:

"db_host": "XXX.YYY.ap-southeast-1.rds.amazonaws.com", "rds_db": 1,

1 Like
13

It needed to create a parameter group in RDS and we are using 10.4 MariaDB. How to create a parameter group and How to add this

character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci