Maintenance

I built a 14.04 server with Apache, PHP, MariaDB 10, and PHPMyAdmin and pushed the entire sql dump at it (structure and data in the same 200MB file). It gobbled it up without ANY drama. All data looks usable and complete (170,000+ soldiers).
Documentation isn't really necessary, but I followed the MariaDB instructions here, and the rest is basically bog-standard stuff (see here for a step-by-step).

The data I pushed out of MS SQL is kind of cruft-ey. I'm using some sed commands to clean up:
sed -i '/^-- /d' CGWP_data.sql
sed -i '/^LOCK TABLES/d' CGWP_data.sql
sed -i '/^\/\*\!/d' CGWP_data.sql
sed -i '/^UNLOCK TABLES/d' CGWP_data.sql
sed -i '/^[[:space:]]*$/d' CGWP_data.sql
csplit CGWP_data.sql '/^INSERT INTO/'

the csplit doesn't work properly though. I'll look in to it another day.

I've run through this four times successfully, so I'm calling these instructions final.

Required software

• MySQL Community Server (v5.6): http://dev.mysql.com/downloads/mysql/
• MySQL Workbench (v6.2): http://dev.mysql.com/downloads/workbench/
• MS SQL Server 2008 R2 (Express Edition): http://www.microsoft.com/en-ca/download/details.aspx?id=30438
• MS SQL Server Management Studio: available from the download window on above URL.

Note: the Microsoft downloads are named SQLEXPR_x64_ENU.exe and SQLManagementStudio_x64_ENU.exe

Once everything is installed and configured, make sure that MS SQL Server and MySQL server are running (type services.msc at a command prompt).

Import CGWP data using SQL Server Management Studio.

1. Connect to Server popup should provide sufficient default settings. Click 'Connect'.
2. Object Explorer displays a list of objects, including 'Databases'. Right-click 'Databases' and choose 'Restore Database...'.
3. To restore from the CGWP dump, Choose 'From device' as the 'Source for restore' and click the browse button to find the dump file. Select the binary dump file (you need to choose 'All Files(*)' in 'Files of type' before you can see the file). It will show up in the 'Select the backup to restore' list if the file is usable. Check the 'Restore' box beside the backup set you want to restore.
4. In the 'Destination for restore' field, copy the name of the backup set from the 'Select the backup sets to restore' panel. A database with this name will be created as part of the restoration process. Click 'OK'. Note that you will get an error if you try to restore a backup set to a database with a non-matching name.
5. You should get a message telling you that the restore was successful. You can quit the Management Studio now.

Migrate existing MS SQL database to MySQL using MySQL Workbench

NOTE: everything about the MySQL Workbench under Windows seems a bit sluggish. Be patient after clicking buttons.

1. We need a connection to a running instance of MySQL. On the MySQL Workbench home page, click on the ⊕ beside 'MySQL Connections' at the top of the page to launch the 'Setup New Connection' wizard.
   • Provide a 'Connection Name'. Click 'Configure Server Management...' button (bottom-left) to launch the 'Configure Local Management' wizard.
   • On the first page of the 'Configure Local Management' wizard, click 'Next'. You'll be prompted for the MySQL root password. Provide it (select 'Save password in vault' or you'll have problems later). Click 'OK'.
   • As long as a connection could be made, this page ('Testing the Database Connection') is merely informative. Click 'Next'.
   • If there is only one instance of MySQL running, click 'Next' on the 'Set Windows configuration parameters for this machine' page.
   • As long as a connection could be made, this page ('Testing Host Machine Settings') is merely informative. Click 'Next'.
   • Review settings or click 'Continue'.
   • Back in the 'Setup New Connection' wizard, click 'OK'.
2. Choose the 'Database Migration' shortcut on the right.
3. If you have already set up a system DSN for the MS SQL DB, click on 'Start Migration'. If you haven't, create one now.
   • Click the 'Open ODBC Administrator' button.
   • Choose 'System DSN' tab and click the 'Add...' button.
   • Select a driver: SQL Server Native Client 10.0 (although both will work it appears that this is the MS-preferred driver)
   • Configure the ODBC connection (see 'DSN setup' instructions below)
4. Click the 'Start Migration' button.
5. On the 'Source Selection' page, choose 'Microsoft SQL Server' as the Database System, and 'ODBC Data Source' as the Connection Method. You should see the DSN you created in the 'DSN' filed. Remove 'sa' from the 'Username' field if present. Click 'Next'.
6. On the 'Target Selection' page, choose the 'Stored Connection' you made in step 1. Click 'Next'. The 'Fetch Schema List' page should complete on its own without configuration or interaction. Click 'Next'.
7. On the 'Schema Selection' page, choose the database you want to migrate (it will have the name of the database you restored in MS SQL Server Management Studio). You can leave the 'Schema Name Mapping Method' alone. Click 'Next'.
8. The 'Reverse Engineer Source' page should complete on its own without configuration or interaction. Click 'Next'.
9. On the 'Source Objects' page, you will be prompted to select the objects you want to migrate. In this case we have: 'Migrate Table objects'; 'Migrate View objects'; and 'Migrate Routine objects'. I have been unsuccessful in migrating anything other than the tables, and, having looked through the views and stored procedures (aka Routine objects) I think it's safe to ignore them.
10. The 'Migration' page should complete on its own without configuration or interaction. Click 'Next'.
11. On the 'Manual Editing' page you can review any objects before the actual migration takes place. I consistently get 55 warnings and no errors. The warnings I've seen are collation issues (e.g. 'Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci') and NULL value problems (e.g. 'Default value N'NA' is not supported.' and 'Default value N'SUR' is not supported.'). As far as I can tell, they can safely be ignored.
12. On the 'Target Creation Options' the only thing selected by default is 'Create schema in target RDBMS'. This is sufficient for our needs. Click 'Next'.
13. If this is the first time through, the 'Create Schemas' page will run on its own. If it finds an existing MySQL schema you will need to allow MySQL Workbench to drop it.
14. The 'Create Target Results' page allows you to edit the individual SQL solutions produced by MySQL Workbench to do the actual migration. Leave this alone. Click 'Next'.
15. On the 'Data Transfer Setup' page 'Online copy of table data to target RDBMS' should be checked. You can also opt to create a batch file that will 'copy the data at another time'. Click 'Next'.
16. Copying the data will take a few minutes, and a progress bar will be displayed while the copy is running. Anything else is likely the result of a config error.

DSN setup for SQL Server Native Client 10.0 Driver

• Name: CGWP
• Description: ODBC connection to CGWP backup
• Server: (local)\sqlexpress (or $MACHINENAME\sqlexpress)
• Click 'Next>' button
• How should SQL Server verify the authenticity of the login ID? With Windows NT authentication using the network login ID = checked
• Connect to SQL Server to obtain default settings for the additional configuration options = checked
• Click 'Next>' button
• Select the 'Change the default database to:' option to include the database in the DSN - it's helpful. The name of the database will show up in the dropdown list as long as a connection can be made. Other than this, leave the other settings alone.
• Click 'Next>' button
• You can leave the settings on this panel alone.
• Click 'Finish' button to review/test the config. Note that a successful config/test connection does not always mean that you won't have problems later in the migration process!

MySQL Workbench can also dump the data from the new MySQL database. Just open up the MySQL connection that you created at the beginning and select 'Data Export' from the navigator on the left. There's a lot of data, so be sure to select complete insert statements in the advanced export options.

Using MySQL workbench on Windows I've managed to migrate the CGWP data in to MySQL. No testing yet, but it worked as far as I can tell.
Very rough notes on Workbench and ODBC:

Open ODBC admin tool
System DSN
Name: CGWP
Description: whatever
Server:Machinename\SQLEXPRESS - in this case GREGWIN7VM\SQLEXRESS
>>NEXT
How should SQL Server verify the authenticity of the login ID? With Windows NT auth...
Check the "Connect to SQL Server to obtain default settings..." box
>>NEXT
Leave these settings alone
>>NEXT
these too
>>FINISH

Start migration

Source: Microsoft SQL Server
Connection Method: ODBC Data Source
DSN: name you gave it (above)
Username: leave blank (remove 'sa')
>>NEXT
Target: MySQL
Connection Method: Standard (TCP/IP)
Hostname:127.0.0.1
Port: 3306
Username: root (unless you chose something else)

Select the MS SQL DB you want to import
Leave the rest of the choices as they are, but make sure to choose all of the tables, views, and procedures before the import
>>NEXT
You should have an import running

eXist 2.2 serializers now use the XQuery 3.0 rec. which breaks up the serialize instructions into individual declarations. So, according to the spec, you serialize as XHTML 5 like this:

declare namespace output = "http://www.w3.org/2010/xslt-xquery-serialization";
declare option output:method "xhtml";
declare option output:html-version "5.0";
declare option output:media-type "text/html";

Except, it looks like eXist may have a bug in there, because the above produces html without a doctype. To get around this use xhtml5 instead of just xhtml in the output:method declaration.

Also, we're using "text/html" for media-type instead of "application/xhtml+xml" because some stuff just doesn't work using xhtml+xml (Google Maps, specifically).

NOTE: Remember to change your page to use xquery 3.0

pgAdmin4
pgAdmin4 is now a localhost, web-based app (node.js I think).
Postgres has a repo that has bionic in it, so to install:

1. sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" >> /etc/apt/sources.list.d/pgdg.list
2. wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
3. sudo apt update && sudo apt install pgadmin4

To run/use the app:

1. Run the launcher. It will open the app in your browser
2. Click 'Add New Server' in the Dashboard
3. In the 'General' panel that opens, provide a name for the connection
4. Switch to the 'Connection' panel and provide the info as follows
   
   1. Host name: service/machine name or IP address of host
   2. Port: 5432 (this is the standard PG port)
   3. Username: your u/n for the DB
   4. Password: leave blank
5. Switch to the 'SSH Tunnel' panel and provide the info as follows
   
   1. Click the 'Use SSH tunnelling' button
   2. Tunnel host: machine name/IP address of machine that is in the PG ACL (SSH access to this machine is req.)
   3. Tunnel port: 22
   4. Username: the username on the tunnel host
   5. Password: leave blank
6. Once you try to connect you should be asked for the password to the SSH tunnel host and the Postgres machine. Provide those and you should be in.

pgAdmin3
To create a connection to a remote PostGreSQL DB you either need to get your local machine added to the PG ACLs or set up an SSH tunnel. Fortunately, the recent builds of pgAdminIII have an easy-peasy tunnel config tab. In case it isn't obvious, here's how to set it up:
File -> Add server... (or click the power plug icon in the top panel)
Properties tab -> Name: anything you like
Properties tab -> Host: e.g. mydb.domain.ca
Properties tab -> Port: probably 5432
Properties tab -> Service: leave blank
Properties tab -> Maintenance DB: postgres
Properties tab -> Username: your admin u/n for the DB
Properties tab -> Password: your admin p/w for the DB
SSH Tunnel tab -> Use SSH tunneling: check
SSH Tunnel tab -> Tunnel host: machine.domain.ca (this is a remote machine that has a PG ACL entry for the DB AND that you have SSH access to)
SSH Tunnel tab -> Tunnel port: non-standard SSH port (useful for host-only VMs) - new in v1.22
SSH Tunnel tab -> Username: the shell account u/n for the tunnel host above
SSH Tunnel tab -> Authentication: choose password, unless you know that you're using an ID file (SSH keys)
SSH Tunnel tab -> Password/Passphrase: either the p/w for the shell account above or the SSH keys passphrase to unlock the key

Overnight I updated teijenkins.hcmc.uvic.ca from 14.04 to 14.04.01. When it came back up, after re-enabling commented-out third-party repositories in /etc/sources.list.d (tei and jenkins), I discovered that the proxying of Jenkins on port 80 was no longer working. After some investigation, I discovered that the original sites-available/jenkins configuration file was not being recognized by apache -- running sudo a2ensite jenkins resulted in "jenkins site does not exist" -- because now it needs to be called jenkins.conf. Once I'd renamed it I was able to enable it, but it took a reboot to make it work; just restarting apache wasn't enough.

This is a reminder to myself because I've been caught by this twice:

Old versions of rsync would work fine over ssh with a target path like this:

mholmes@server.thing.ca:home/mholmes/folder/

But a particular upgrade means that you now get this error:

ERROR: module is read only
rsync error: syntax or usage error (code 1) at main.c(1034) [Receiver=3.0.9]
rsync: [sender] write error: Broken pipe (32)
rsync error: error in socket IO (code 10) at io.c(837) [sender=3.1.0]

You have to add the slash after the colon.

I've added a cron.daily script that will run zsync and pull down the most recent ISOs that we're likely to want around HCMC - specifically, it will grab the Trusty desktop, server, and Gnome-shell ISOs and store them in the ${SERVER_URL}/hcmc/iso directory.

It looks like this:

#!/bin/bash
zsync -qo /var/www/hcmc/iso/ubuntu-14.04-desktop-amd64.iso http://ubuntu-releases.mirror.nexicom.net/trusty/ubuntu-14.04-desktop-amd64.iso.zsync
zsync -qo /var/www/hcmc/iso/ubuntu-14.04-server-amd64.iso http://ubuntu-releases.mirror.nexicom.net/trusty/ubuntu-14.04-server-amd64.iso.zsync
zsync -qo /var/www/hcmc/iso/ubuntu-gnome-14.04-desktop-amd64.iso http://cdimage.ubuntu.com/ubuntu-gnome/releases/trusty/release/ubuntu-gnome-14.04-desktop-amd64.iso.zsync
zsync -qo /var/www/hcmc/iso/daily-desktop-amd64.iso http://cdimage.ubuntu.com/daily-live/current/utopic-desktop-amd64.iso.zsync
rm -rf /var/www/hcmc/iso/*.zs-old

--args: q is for quiet, o is for output file. In this case it includes an explicit path to the output file