MySQL: Rebuild Conekt related databases in wright

[sdash-github]

lis_conekt and lis_dev_conekt dbs are not functional anymore after MariaDB in wright was updated by Sam. To make Conekt prod and dev sites work again these two dbs need to be rebuilt from scratch.

Rename the old dbs, Use conekt linux installation instruction to re-establish mySQL schema. The data should be populated for rebuilding the site at the admin interface of Conekt site (raw loading files should exist with sdash for most large data except lesser ones that need to be redone manually).

[sdash-github]

Sven, Do you think a meeting will be more effective than me typing up (very very slow) all the things that need to be done in the beginning?

[svengato]

Do you think a meeting will be more effective than me typing up (very very slow) all the things that need to be done in the beginning?

I think a meeting will be more effective after I have the context. Thanks -

-Sven-

[sdash-github]

I can explain to you the context verbally.

[svengato]

I can explain to you the context verbally.

Okay, I am around all afternoon.

[sdash-github]

From email between sdash and sgr:

$ service mariadb start [...] ==== AUTHENTICATION COMPLETE ==== Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

$ mysql -u root ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) /=================

The volume is about 95% full too (may not be the problem). /=================

Looking at this again - the free space is less than 500 Mb. How much does the MySQL/MariaDB server need? -Sven-

[sdash-github]

How much does the MySQL/MariaDB server need?

We should not have a limit for this. The datasets are large and particularly so with profile neighbors/correlation data. We should have provision for making available more space as our need grows.

Is more space needed in wright? May be Andrew can weigh in for this question.

[adf-ncgr]

my guess based on the bit of info here in the thread is that Sven may be trying to start a server locally on the VM instead of using wright. The one on wright shouldn't need to be started, just have a db created.

[svengato]

$ mysql -h wright -u root ERROR 1130 (HY000): Host '172.20.129.4' is not allowed to connect to this MariaDB server (172.20.129.4 is dev-conekt)

[svengato]

Maybe I have to ssh to wright to add it to my dev-conekt's known_hosts? I must have done this from shokin-mines - now hunting for my password.

[sdash-github]

If you want to connect from outside wright, may be you should avoid as root. You should use the user:pw in the config.py in the conekt dir for the mysql connection.

[sdash-github]

Mariadb in wright shows unusual user listing.

Where is user 'conektweb' that Conekt uses to connekt to mysql? Did it get corrupted during update?

MariaDB [(none)]> select user from mysql.user; +-------------+ | user | +-------------+ | root | | root | | mariabackup | | root | | root | +-------------+

I am going to look at the user tables and modify/create and grant acces to check if Conekt user can access from outside wright.

[sdash-github]

Created conektweb mysql user and checked access from dev-conekt.

| Host | User | Password | % | conektweb | ------ |

GRANT ALL privileges ON lis_dev_conekt.* TO conektweb;

[svengato]

Drop and recreate the databases

On wright, launch mysql.

$ mysql -u root

Once in mysql, drop the databases.

> drop database lis_dev_conekt;
> drop database lis_conekt;

If it gives an error like this,

ERROR 1010 (HY000): Error dropping database (can't rmdir './lis_dev_conekt', errno: 39 "Directory not empty")

you must manually remove the directory and its contents from /var/lib/mysql. Andrew had to do this, as neither Sudhansu nor I have sudo access on wright.

Finally, recreate the databases.

> create database lis_dev_conekt;
> create database lis_conekt;

Rebuild the database schema and the Flask app

Start midway through CoNekT’s Linux installation instructions.

On dev-conekt, go to the app directory and launch the Python virtual environment.

$ cd /var/www/CoNekT
$ source bin/activate

Rebuild the database schema (defined in the migrations subdirectory).

$ export FLASK_APP=run.py
$ flask initdb
  [it prints out a long list of queries...]
An admin account has been created. Username='admin' and password='********'
IMPORTANT: Change the password for this account

If the migrations subdirectory already exists, the following step will not be able to build the Flask app. To remove this manually,

$ sudo rm -rf migrations

Build the Flask app. (Simply running flask db init gave a permissions error and did not recognize FLASK_APP.)

$ sudo FLASK_APP=run.py flask db init
$ flask run

Finally, exit Flask and the virtual environment.

> (ctrl-C)
$ deactivate

Test CoNekT

http://dev-conekt.lis.ncgr.org/conekt/

At first it failed with an Internal Server Error message, but worked after Andrew tweaked the selinux permissions.

OK, I think the proper access should be restored (it was an selinux
permissions thing):
[adf@dev-conekt ~]$ sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted
Current mode:                   enforcing
Mode from config file:          enforcing
Policy MLS status:              enabled
Policy deny_unknown status:     allowed
Memory protection checking:     actual (secure)
Max kernel policy version:      31
[adf@dev-conekt ~]$ sudo setenforce 0

(this only disables selinux until the next reboot, I will do it properly
once I remember how)

adf