Multitenancy and Root user password error problem

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 ?

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)

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.

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.

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

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.

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;"