Edits to support MariaDB in Ubuntu

[jlsam]

While trying to implement support for MariaDB / Ubuntu in my ansible playbook, '@BRX#1315' in Discord pointed me to his guide in https://docs.origintrail.io/dkg-v6-beta/testnet-node-setup-instructions/setup-instructions-arch-linux.

These instructions fixed a previous SQL error I was getting using a MySQL command, but hit a new error when running the npx sequelize command:

ERROR: Access denied for user 'root'@'localhost'

I started digging a bit and found the socket authentication method, which does not require a password. We have been setting up the testnet servers directly under root and the installations scripts also do not define a password for root, so this method actually seems more adequate for the current setup.

In this case, for MariaDB the SQL line

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;

would be changed to

ALTER USER root@localhost IDENTIFIED VIA unix_socket;

But this is not enough, as the error above persists. After digging a bit more I found a Postgres example here that I was able to adapt for this situation.

Directly replacing localhost with the socket path, as I found suggested in a few places, does not work. (config/sequelizeConfig.js)

I found the working socket path with netstat -lpn | grep mysqld: /run/mysqld/mysqld.sock

The password setting can be removed. A working config/sequelizeConfig.js would look like this:

require('dotenv').config();

module.exports = {
    username: 'root',
    database: 'operationaldb',
    dialect: 'mysql',
    host: 'localhost',
    port: 3306,
    migrationStorageTableName: 'sequelize_meta',
    logging: false,
    operatorsAliases: '0',
    define: {
        underscored: true,
        timestamps: true,
    },
    dialectOptions: {
    socketPath: '/run/mysqld/mysqld.sock'
    }
};

There is also a dialect 'mariadb', but it requires additional installation steps and internally it appears to use the same code as mysql, so for now that change doesn't seem necessary.

I didn't test these changes to config/sequelizeConfig.js for MySQL, but it seems to be using the same socket file unix 2 [ ACC ] STREAM LISTENING 21110 715/mysqld /var/run/mysqld/mysqld.sock

I can turn this into 1 pull request, although changes would also need to be made to the Bash installer to accommodate the differences in the SQL commands for MySQL or MariaDB.

[Valcyclovir]

Hello @jlsam, just a quick heads up that I am BRX from Discord :)

Just like you, I think the team should consider supporting mariadb going forward, as it is widely supported by all distros, is open source and according to some sources replicates faster than mysql can.

I have successfully tested my setup on arch linux without any permission issues. When testing on a beta tester's Linux Mint server running 20.04 LTS, I had trouble with permission issues and solved it by adding -p and entering the password following each command : mysql -u root -p

Have you tried putting an empty string for the password on the mysql config then running npx sequelize ? var mysql = require('mysql'); var connection = mysql.createConnection({ host: 'localhost', user: 'root', password: '' });

If this does not work, you can maybe try this as a potential fix : MariaDb/MySQL considers 'localhost' (unix socket) to be different than '127.0.0.1' (tcp socket) so you could set a password for TCP and none for Unix sockets like so:

MariaDb: GRANT ALL PRIVILEGES ON . TO 'admin'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION; INSTALL SONAME 'auth_socket'; GRANT ALL PRIVILEGES ON . TO 'admin'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;

MySQL/Percona: CREATE USER 'admin'@'127.0.0.1' IDENTIFIED BY 'xxx'; GRANT ALL PRIVILEGES ON . TO 'admin'@'127.0.0.1' WITH GRANT OPTION; INSTALL PLUGIN auth_socket SONAME 'auth_socket.so'; CREATE USER 'admin'@'localhost' IDENTIFIED WITH auth_socket; GRANT ALL PRIVILEGES ON . TO 'admin'@'localhost' WITH GRANT OPTION;

You can then login on the local host with mysql -u admin without a password over the unix socket or via tcp socket with a password using mysql -h 127.0.0.1 -u admin -p.

Let me know how that goes !

[jlsam]

Hey!

I think it makes sense to avoid prompts in ansible whenever possible. So the unix_socket change solves the authentication issue without having to deal with passwords. It's not that we were using a password for security anyways...

The current file https://github.com/OriginTrail/ot-node/blob/v6/develop/config/sequelizeConfig.js does include an empty password line password: '', which becomes unnecessary with unix_socket authentication.

The solution I'm proposing fixes the password problem for MariaDB and should also work without modifications to sequelizeConfig.js for MySQL. I was kind of waiting for a reply from the team to see if there is a point in actually testing this.

Why do you think having a MySQL password is important in our case? I get your idea about using TCP, but again we are not currently securing MySQL with any password and the TCP thing is more complicated than the unix socket. The solution I proposed should also work on Ubuntu Mint, no password required.

[Valcyclovir]

Definitely not saying we need a password for our use case, and if team goes ahead with mariaDB (which they should !!) then your fix to unix_socket does seem to be the way to go.

On another note, I was looking at your ansible solution for managing multiple nodes and speaking from experience from a ex-ansible and multinode user, I don't see the team supporting it at all. This was what I worked on for V5 and the team knew about it and Calvin's dockerless multinode solution but would not actively support it. It is absolutely a great tool to have if someone wants to monitor multiple nodes in the future. Also, notice on the new V6 website the words :

Node staking Improve your chances to be engaged as holder of published Assets by increasing the stake associated with your node.

Meaning that, speculatively, there won't be a necessary to run multiple nodes anymore :) To be seen !

edit: on second thought, being an alternative to the installer could be accepted by the team, but since ansible's purpose is to manage multiple nodes, it's at the team's discretion. Good luck !

[NZT48]

This issue is being closed as inactive due to the date of the last activity on it. If you believe this is still a problem, please create a new issue and confirm that it is reproducible in the current ot-node release version. We are working towards closing open issues that meet specific criteria and ask you to create a new one for those that that are truly bugs in current release. We'll be monitoring those issues so that they are properly managed.

Thank you, OriginTrail Team