Install ERPNext without root access to the remote mariadb server

[Yves_Vindevogel]

Hi,

Is there a possibility to install ERPNext without the need to have root access to the remote MariaDB server. I have created the database myself and the user, given all grants on the database so this should be enough to set up the tables and so on. I see no reason to give root access, especially as it is very unsecure to allow root to login remotely.

A few more things in your setup scripts for MariaDB:

• innodb-file-format=barracuda
• innodb-file-per-table=1

The above should not be up to the developer of the application to decide. It’s the DBA who decides this. Not so happy with that part, so I did not change my settings at all. Could you tell me why you have it and if it’s really needed ?

• character-set-server=utf8mb4
• collation-server = utf8mb4_unicode_ci

The above are very unwanted too. The application should not decide this. It’s the DBA. You can easily change this when you create the DB: create database erpnext character set utf8mb4 collate utf8mb4_unicode_ci;

• innodb-large-prefix=1

I do not like the above, again, it’s the DBA who decides the server, not the application. I allowed it after reading what it does. But indexes on more than 768 characters ? What are you indexing that way ?

Regards,
Yves

[anand]

Hi Yves,

You ask good questions

These are the settings we use and most users are happy with them to be part of the default install.

When you add more than say 20-30 custom fields of type varchar/decimal, mariadb hits its limit for the default file format and changing it to barracuda and innodb-large-prefix allows one to add more fields.

You need to have innodb-file-per-table for barracuda.

This article explains the problem: Innodb row size limitation - Percona Database Performance Blog

utf8mb4 is for supporting asian languages.

We can modify the code to perform installs without root access, but most users we have don’t have their DBAs and hence, the installs work for them without any hassles.

Thanks,
Anand Doshi.

[Yves_Vindevogel]

I had read that one too But also another one, but the reply does not allow me to post more than 2 links. Don’t know even where it sees the first, but ok.

Ok, as such, I have nothing against barracuda, especially as I read it does not change the existing stuff, but still. I was a bit cautious to change without reading the stuff. I have to see on my test server as I was playing around a bit. I changed both settings, created a DB and a table, but still saw “Compact” for the type. Guess something is not right. Can look into it.

As I don’t use Asian languages, I should be ok with utf8 then. So I will not change my server settings (that was out of the question on this one), and not add it in my create table script. I should be ok.

You write: we can change the code. So it means that at this moment, the setup will not work without it ? As said, my MariaDB does not allow root to login remotely. Each type of database (joomla, redmine, mail configs, prestashop) has a dedicated user for it, that is allowed to login remotely, and during creation of the database, the user is given all grants to it. I will not step away from this. Root access is forbidden.

Now, I got a bit further than in the beginning. I have made a common_site_config.json that already holds the remote server, port, user / pwd (for user erpnext). Would it be possible to setup a site.local directory without the bench new-site command and to configure the site_config.json myself in order to connect using the erpnext user ?

Maybe one more remark on your centos install script. Why is the groupinstall “Development tools” needed. It installs a lot of stuff for Perl and C(++). I guess you need a couple of libraries there, but not all. It’s strange to me.

Ooops, one more, why postfix ?? I do not want a local postfix. Would this be needed if I setup my mail config to use SMTP/IMAP on another server (like Gmail for example) which has both ? I would rather have my own mail server being used for SMTP so I have a trace of all the mails going out, but certainly not on the local server. I would then use a system mail account.

Kind regards,
Yves

And thank you for the quick response on the previous / initial question

[pdvyas]

@Yves_Vindevogel

You write: we can change the code. So it means that at this moment, the setup will not work without it ? As said, my MariaDB does not allow root to login remotely. Each type of database (joomla, redmine, mail configs, prestashop) has a dedicated user for it, that is allowed to login remotely, and during creation of the database, the user is given all grants to it. I will not step away from this. Root access is forbidden.

The only thing we do as root is create a database, user and grant. You can do the following,

bench make-site site1.local --db-name your-db --db-pass your-db-pass --db-host localst # just makes the site directory
bench reinstall site1.local

I’ll make a pull request for the “make-site” command (That’s the only code change Anand is talking about )

@Yves_Vindevogel

Maybe one more remark on your centos install script. Why is the groupinstall “Development tools” needed. It installs a lot of stuff for Perl and C(++). I guess you need a couple of libraries there, but not all. It’s strange to me.

For installing any python deps with C extensions

@Yves_Vindevogel

Ooops, one more, why postfix ?? I do not want a local postfix. Would this be needed if I setup my mail config to use SMTP/IMAP on another server (like Gmail for example) which has both ? I would rather have my own mail server being used for SMTP so I have a trace of all the mails going out, but certainly not on the local server. I would then use a system mail accoun

Yes, you can definitely skip postfix and configure another SMTP server from the site_config.json or from the UI.

@Yves_Vindevogel

As I don’t use Asian languages, I should be ok with utf8 then. So I will not change my server settings (that was out of the question on this one), and not add it in my create table script. I should be ok.

It can also store emojis

[Yves_Vindevogel]

Hi pdvyas,

Thank you for your reply. I understand the pull request. It makes sense. You should not take over the job of a DBA.

Unfortunately, I must work on another thing now and I am not sure when/wether I can work on this again. Setting up the ERP is critical and other software is evaluated also now. Currently setting up Opentaps. We have to move forward, sorry. You had the best interface and according to my wife (she is the financial wizard) you had a very complete package and she liked it very much. But she was less forgiving that I work so long time in the evening … I have spend about 2 days on it, and still need to figure out how I need to do the Apache part, see if the remote database really works, … Lot’s of uncertain things to solve.

Regards,
Yves

[Yves_Vindevogel]

I may as well reply immediately to this. Opentaps is a disaster. Basic screens not working with sample data, not a single screen that has a bit of consistency. Going into the database to get stuff done. NO !!! Will not spend a single hour on it any more.

I will for now go with the easy install of ERPNext. With local database. I am not so fond of this, but it’s the better solution for now. I will later see how I can move this to a more solid environment with a clustered MySQL database. I hope the make-site is soon available.

Emojis ?? I need to get a new collation for smileys ??? Guess not.

[oneyb]

I had the same problem… It took me forever, but got things working now now. It’s not ideal, but works. See:

Getting MySQL to work on Debian 9

1 Proper MySQL set-up From: https://kofler.info/root-login-problem-mit-mariadb/ 2 Install prerequisites sudo apt-get install mysql-server mysql-client mysql-common libmariadbclient-dev 3 Set password sudo mysqladmin password -u root 4 Put passwords in file:///etc/mysql/debian.cnf 5 Remove unix_socket plugin sudo mysql -u root -p UPDATE mysql.user set PLUGIN='' where USER='root'; FLUSH PRIVILEGES; 6 Configure for ERPNext echo -e "[mysqld]\ninnodb-file-f…