DOP Center database missing

[cxbrooks]

Some of the DOPCenter SpaceCadet seating chart tests in adm.installers.test.junit as reported in https://www.icyphy.org/ptII-test/reports/junit/html/index.html are failing.

I'm creating this issue so that we will know how to fix it next time.

Running $PTII/bin/vergil ptolemy/domains/space/demo/DOPCenter/DOPCenter.xml fails with

ptolemy.kernel.util.IllegalActionException: Failed to open connection to the database.
  in .DOPCenter.DatabaseManager
Because:
Communications link failure
 Last packet sent to the server was 0 ms ago.
    at ptolemy.actor.lib.database.DatabaseManager.getConnection(DatabaseManager.java:566)
    at ptolemy.actor.lib.database.DatabaseManager.preinitialize(DatabaseManager.java:583)
    at ptolemy.actor.Director.preinitialize(Director.java:1302) 

The issue here is that the database was removed on the server.

Happily https://wiki.eecs.berkeley.edu/icyphy/Main/Wessel#DOP_Center_Space_Database says:

DOP Center Space Database

We creating a daily dump of the tables used by SpaceCadet. This functionality is enable by activating the corresponding cron job: crontab /root/space/space_dump.cron

The space_dump.sh script creates a dump, checks it in, and pushes it to the eal/space repo.

The link at eal/space is to https://repo.eecs.berkeley.edu/svnadmin/accesspathview.php?accesspath=space.git

https://wiki.eecs.berkeley.edu/dopcenter/Main/Seating#Systems_Administrator_details says

Backing up data

The database resides on wessel.eecs.berkeley.edu. See the wiki.

To create a logical backup of the space database, run: /usr/bin/mysqldump --skip-lock-tables --databases space --user='spacecadet' --password='...' > db.sql

SQL Configuration

Remote Access

Open up the firewall:

ufw allow 3306

Grant remote access

Space Cadet User

As root on the new machine, create the spacecadet user with two sets of permissions. The 'spacecadet'@'localhost' user has very wide permissions, the 'spacecadet'@'%' account, which is used by remote connections has only INSERT,SELECT,UPDATE:

mysql> CREATE USER 'spacecadet'@'localhost' IDENTIFIED BY '...';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'spacecadet'@'localhost';

mysql> CREATE USER 'spacecadet'@'%' IDENTIFIED BY '...';

mysql> GRANT INSERT,SELECT,UPDATE ON *.* TO 'spacecadet'@'%' WITH GRANT OPTION;

mysql> \q

Load The Data

As root, restore the space database after creating a dump. mysql -u spacecadet --password="password" < /tmp/db.sql

[lhstrh]

The MySQL server is running again. I had to change the password of spacecadet, however, because it was flagged as unsafe.

[lhstrh]

I wasn't able to upload a secondary key to repo.eecs, so I just created a new repo here and copied some of the above documentation in its README.md. The scripts should be checked in there, too.

[cxbrooks]

Good news everyone! The space cadet tests passed and it looks like the space github repo was updated.

I'm closing this one.