Multitenancy and Root user password error problem

[anupd]

Hello,

I was trying to setup DNS based multitenancy. I already have a system up and running in erp1.example.com. For this a database named ‘erp1’ and mysql user named ‘erp1’ is already in use. While setting up multitenancy, I have one question and one problem.

Question: 1. Do I need a separate database and user for second site in the same bench ?

Problem:
I tried the command bench new-site erp2.example.com (A records are already set).
It returned the following error: Access denied for user 'root'@'localhost'

I had encountered same error while running other bench commands as well which was solved with the help of friendly forum members, found in this link.

However, it would not be practical to follow the suggestion in the above link for all commands. I don’t think bench commands work that way normally.

What do you think is creating the problem ?

[shashank_shirke]

Hi @anupd

Question: 1. Do I need a separate database and user for second site in the same bench ?

When you create a new site a new database is automatically created as part of the new site creation process.

When you create the site, it’ll ask for MySQL root password. Once you enter the correct root password, it’ll create a new database instance for the new site.

To have multiple sub-domains here are the step-by-step instructions:

1. Create new site
2. Add new domain and assign it to the new site
3. Run bench setup nginx and sudo service nginx reload
4. Add A record in your domain DNS panel
5. Run certbot (in case you have SSL activated for both domains)

[anupd]

Hello,
Thank you for your suggestions.

The only problem with new site creation is it says Access denied for user 'root'@'localhost' but I have not set any password for root. Pressing enter just does not work. FIY I have used this step by step guide to set up erpnext.

[shashank_shirke]

That is really strange though. MySQL root password is required when installing ERPNext via the EasyInstall script method.

Can you check if you’re able to login to the MySQL server using mysql -u root

Also, here’s a tried and tested method of installation which works flawlessly with the DNS based multi-tenancy setup that you’re trying to achieve.

Install v13 Production on Ubuntu 20.04 VPS - This is how I did it "Easy Install" script

Okay, I promised I would post the steps I used to get this done and here they are: **On Edit: found a typo in the commands for installing the prerequisites before running install.py. Corrected in this post now. 4/19/2021 **EDIT Again: improved/changed the steps to add the pre-install of redis server in order to also solve a problem with the redis.conf file that causes ERPNExt install failures on some Ubuntu 20.04 images. Not all images are affected this way but these extra steps …

I’m using a similar setup with more than 5 instances of ERPNext and plain Frappe on a single server.

[anupd]

Yes I can login with mysql -u root pressed “enter” when asked for password.

I installed manually, not easy install.

I did run mysql_secure_installation but did not set up root password (just skipped by pressing enter). I had created a new mysql for erpnext use (say: erpuser) with a password.

But when running bench commands, it asks for root password which doesnot exist and doesnot accept pressing enter.

[shashank_shirke]

Not setting up a root password for MySQL is a really risky option.

What we can try is;

1. Setup a root password

2. Grant all privileges to root user

    mysql --defaults-file=/etc/mysql/debian.cnf
   
    UPDATE mysql.user SET authentication_string=PASSWORD('YOUR-PASSWORD-HERE'), plugin='mysql_native_password' WHERE User='root';
   
    EXIT;
   
    service mysql restart
   

Once you reset your root password trying granting all privileges using this command

sudo mysql -u root -p PASSWORD-HERE -Bse "GRANT ALL PRIVILEGES ON *.* TO root'@'localhost' IDENTIFIED BY 'PASSWORD-HERE' WITH GRANT OPTION;"

[x1123]

This things have been going for long years on various Tutorial. My best understanding. On Debian system, mysql / mariadb is tied into system. The best practize (imo) by using unix socket authentication, thus upon update the OS (Debian System) mysql/ mariadb will not break and causing problem on the databases.

My best understanding, frappe-bench always requesting mysql ‘root’ password. for executing some command, especially related to databases. Therefore, it will problem for someone did not set root password for mysql.

It have been happening on various tutorial which ( sometimes ) use by ‘newbie’ with “minimal” understanding. In frappe-bench asking ‘root username’ for mysql/mariadb and some people mis-understanding between root username on OS System and ‘root username’ on my sql. For sure tutorial will be better explaining this matters. ‘root username’ in mysql can be created and assigned as 'let say" super admin or admin LOL.

I am not a techsavy, better consult the expert one on consult to distro forum.