ERPNext Installation using AWS RDS MariaDB

rajeshanji8 · March 20, 2021, 7:01pm

I’m trying to install ERPNext with AWS RDS MariaDB.

RDS is not allowing to give admin / super privileges to any user.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'frappedb'@'%' IDENTIFIED BY 'frappedb' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'rootmasterpassword' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)
MariaDB [(none)]>

so I tried different ways.

creating a new user in the MariaDB & database.

CREATE database frappedb;
CREATE USER frappedb;
GRANT ALL ON frappedb.* TO ‘frappedb’@‘%’;
GRANT ALL ON frappedb.* TO ‘frappedb’@‘%’ IDENTIFIED BY ‘frappedb’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

I have update the mariadb host
bench set-mariadb-host frappedb.eqwrgqergreqgw34.ap-south-1.rds.amazonaws.com

this is the error its giving on the site creation

ubuntu@frappehost:~/frappe-bench$ bench new-site site1 --admin-password 'site1' --mariadb-root-username frappedb --mariadb-root-password 'frappedb' 

Traceback (most recent call last):
  File "/usr/lib/python3.8/runpy.py", line 194, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 99, in <module>
    main()
  File "/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 18, in main
    click.Group(commands=commands)(prog_name='bench')
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 1137, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/home/ubuntu/frappe-bench/apps/frappe/frappe/commands/site.py", line 43, in new_site
    _new_site(db_name, site, mariadb_root_username=mariadb_root_username,
  File "/home/ubuntu/frappe-bench/apps/frappe/frappe/commands/site.py", line 85, in _new_site
    install_db(root_login=mariadb_root_username, root_password=mariadb_root_password, db_name=db_name,
  File "/home/ubuntu/frappe-bench/apps/frappe/frappe/installer.py", line 27, in install_db
    setup_database(force, source_sql, verbose, no_mariadb_socket)
  File "/home/ubuntu/frappe-bench/apps/frappe/frappe/database/__init__.py", line 16, in setup_database
    return frappe.database.mariadb.setup_db.setup_database(force, source_sql, verbose, no_mariadb_socket=no_mariadb_socket)
  File "/home/ubuntu/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 40, in setup_database
    dbman.delete_user(db_name)
  File "/home/ubuntu/frappe-bench/apps/frappe/frappe/database/db_manager.py", line 31, in delete_user
    self.db.sql("DROP USER '%s'@'%s';" % (target, host))
  File "/home/ubuntu/frappe-bench/apps/frappe/frappe/database/database.py", line 171, in sql
    self._cursor.execute(query)
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py", line 684, in _read_packet
debug2: channel 0: window 999316 sent adjust 49260
    packet.check_error()
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1227, 'Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation')

using the same admin user

ubuntu@frappehost:~/frappe-bench$ bench new-site site1 --force --admin-password ‘site1’ --mariadb-root-username admin --mariadb-root-password ‘masterpassword’
Traceback (most recent call last):
File “/usr/lib/python3.8/runpy.py”, line 194, in _run_module_as_main
return _run_code(code, main_globals, None,
File “/usr/lib/python3.8/runpy.py”, line 87, in _run_code
exec(code, run_globals)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 99, in
main()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 18, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 764, in call
return self.main(*args, **kwargs)
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 717, in main
rv = self.invoke(ctx)
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 956, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 555, in invoke
return callback(*args, **kwargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands/site.py”, line 43, in new_site
_new_site(db_name, site, mariadb_root_username=mariadb_root_username,
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands/site.py”, line 85, in _new_site
install_db(root_login=mariadb_root_username, root_password=mariadb_root_password, db_name=db_name,
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/installer.py”, line 27, in install_db
setup_database(force, source_sql, verbose, no_mariadb_socket)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database/init.py”, line 16, in setup_database
return frappe.database.mariadb.setup_db.setup_database(force, source_sql, verbose, no_mariadb_socket=no_mariadb_socket)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py”, line 40, in setup_database
dbman.delete_user(db_name)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database/db_manager.py”, line 29, in delete_user
host = self.get_current_host()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database/db_manager.py”, line 15, in get_current_host
return self.db.sql(“select user()”)[0][0].split(‘@’)[1]
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database/database.py”, line 122, in sql
self.connect()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database/database.py”, line 75, in connect
self._conn = self.get_connection()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database/mariadb/database.py”, line 96, in get_connection
conn.select_db(self.user)
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 446, in select_db
self._read_ok_packet()
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 394, in _read_ok_packet
pkt = self._read_packet()
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 684, in _read_packet
packet.check_error()
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/protocol.py”, line 220, in check_error
err.raise_mysql_exception(self._data)
File “/home/ubuntu/frappe-bench/env/lib/python3.8/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (1049, “Unknown database ‘admin’”)

if i create the database with name admin & gives the same prillages & reinstall.

ubuntu@frappehost:~/frappe-bench$ bench new-site site1 --admin-password ‘site1’ --mariadb-root-username admin --mariadb-root-password ‘rootpassword’ --force
Creating Database…
sh: 1: mysql: not found
0.00 B 0:00:00 [0.00 B/s] [> ] 0%
Database not installed, this can due to lack of permission, or that the database name exists.
Check your mysql root password, or use --force to reinstall

but its creating database in the rds

Can anyone help me to resolve this issue?

Thanks In Advance.

rajeshanji8 · March 20, 2021, 7:05pm

I’ve followed the discussions on

https://github.com/frappe/frappe_docker/issues/308

I’ve tried on AWS MariaDB 10.2, 10.3, 10.4

Suresh_Thakor · March 20, 2021, 8:13pm

try this.

mysql_secure_installation
Enter current password for root (enter for none): 

Change the root password? [Y/n]: y
New password: yourdbpass
Re-enter new password: yourdbpass
Password updated successfully!
Reloading privilege tables..
... Success!

Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y
Thanks for using MariaDB!

 bench new-site site1 --db-name site1db

rajeshanji8 · March 20, 2021, 8:15pm

Hi Suresh, thanks for the reply.
I’m trying to use AWS RDS MariaDB as the database., It doesn’t have the option ‘mysql_secure_installation’.

revant_one · March 20, 2021, 11:47pm

github.com

frappe/frappe_docker/blob/5121e2710653c8df513e953a21e8d1ee8d3c2bde/build/common/commands/new.py#L103


      
          
              # Drop User if exists
              command = mysql_command + [f"DROP USER IF EXISTS '{db_name}'; FLUSH PRIVILEGES;"]
              run_command(command)
          
              # Grant permission to database and set password
              grant_privileges = "ALL PRIVILEGES"
          
              # for Amazon RDS
              if config.get(RDS_DB) or site_config.get(RDS_DB):
                  grant_privileges = RDS_PRIVILEGES
          
              command = mysql_command + [f"\
                  CREATE USER IF NOT EXISTS '{db_name}'@'%' IDENTIFIED BY '{db_password}'; \
                  GRANT {grant_privileges} ON `{db_name}`.* TO '{db_name}'@'%'; \
                  FLUSH PRIVILEGES;"]
              run_command(command)
          
          if frappe.redis_server:
              frappe.redis_server.connection_pool.disconnect()

ALL PRIVILEGES won’t work, check how query changes for RDS

system · April 3, 2021, 11:47pm

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.