Updating table with values from another table

January 19th, 2018

I have a database of interesting words on my server that has pronunciations and definitions. On another server, I want to create a database of boring words. Since there is some overlap, I thought I would save some time and populate the new database with pronunciation and definitions from the old one. It is fairly straightforward, just remember to put in the commas to separate the where conditions. I’m using phpMyAdmin and while I don’t think the backtics are required, I go in the habit of using them from writing PHP code. Even though you are only updating one table, you need to put both in the update line.


UPDATE `boring_words`,  `interesting_words` 
SET `boring_words`.`pronunciation` = `interesting_words`.`pronunciation`,
    `boring_words`.`definition` = `interesting_words`.`definition`,
    `boring_words`.`comment` = `interesting_words`.`comment`
WHERE `boring_words`.`word` = `interesting_words`.`word`;

Notes on creating a droplet

January 17th, 2018

I created a new Digital Ocean droplet and mostly followed the tutorials to get things up and running. A couple of things that I need to do to get the environment the way it is on other machines.

I had a bit of trouble with using Public Key Authorization. I think that I was selecting the key by triple clicking until everything was highlighted and then copying. I believe that I got a line feed a the end of the key that was messing up my login attempts. Carefully highlighting just the key seems to have done the trick.

I also was stymied for a while when my site didn’t display. First, since I turned on the firewall I needed to add either www (sudo ufw allow www) or Apache (sudo ufw allow Apache) to the rules. And then verify with sudo ufw status.

When I created the droplet, I destroyed the previous one so I needed to find the DNS records and change the IP address. Unlike other services, DO puts that in Networking.

I need to copy my .bash_profile file over to my home directory and add the line
. ~/.bash_profile to my .profile file.

Reload the profile with source ~/.profile

I also want all of the files in the www directory to be created with the admin group. That way, no matter who created them, I’ll be able to edit them.

You can change the default group for all files created in a particular directory by setting the setgid flag on the directory (chmod g+s _dir_). New files in the directory will then be created with the group of the directory (set using chgrp <group> <dir>). This applies to any program that creates files in the directory. mark4o

The default location for web files is /var/www, which is different than the setup on my older machines. (probably because the default location varies across distributions and over time). I put a symlink to it in the root. sudo ln -s /var/www/ www

I don’t want people to be able to view the directories, especially the images directory, so I disabled that ability by removing the word Indexes from the option line.


Options Indexes FollowSymLinks
AllowOverride None
Require all granted

I covered this in earlier posts, but it doesn’t hurt to repeat it. I make a few modifications to the apache config file to keep people from seeing things on the server. I added these lines after the section on .htaccess.


# We don't want people to see .inc files
<Files  ~ "\.inc$">
  Order allow,deny
  Deny from all
</Files>

# Do not allow .git version control files to be viewed
<Directorymatch "^/.*/\.git+/">
  Order deny,allow
  Deny from all
</Directorymatch>

# We don't want people to see .svn files, mainly in Wordpress installs
<Directorymatch "^/.*/\.svn+/">
  Order deny,allow
  Deny from all
</Directorymatch>

On my older servers I have lots of sites and their names end in .com, .net, etc. but on this version of Apache, the a2ensite requires files to end in .conf.

I also had some trouble with the https code that was added to the site by certbot. I took them out after reviewing the output of sudo journalctl -xe.

To install the certbot certificate I went to the page for my setup and followed the directions. I have a .com and .org version of this site and allow access using www and without so I need the certificate to work for all four of these. Rather than getting four certificates, I got one for the name I will use most, the .org, and then added the ones for www and .com.

Unfortunately, at the moment there is a security vulnerability so the normal method does not work.

Instead I had to stop Apache and run


sudo certbot --authenticator webroot --webroot-path /var/www/ACOV/ --installer apache2 -d example.org

sudo certbot certonly --cert-name example.org -d example.com,www.example.org,www.example.com

service apache2 restart

I had some problems at first because I had not set up the DNS records at Digital Ocean correctly. I typed the whole domain into the add a record field, when I should have just typed www. It added www.example.com.examp instead of www.example.org. After I fixed that it created the certificates and I checked them.

Unfortunately, something else is not right because Apache is not serving up the SSL connection. I’ll update the post when I figure it out.

Running out of space.

January 8th, 2018

A server that I administer from time to time has been running out of space. I manually deleted all of the logs and freed up a ton of space. I turns out that there are lots of wget calls on this server and as more customers were added the number of lines in the Apache logs increased tremendously.

The access log was rotated weekly but it gets to be very large and on an old machine with only 70GB total, it adds up. I changed the log rotation in /etc/logrotate.d/apache2 to daily from weekly and keep them for 1 day instead of 26 weeks. The free space has remained at 10% since I made the change.


/var/log/apache2/*.log {
        daily
  missingok
  rotate 1
  compress
  delaycompress
  notifempty
  create 640 root adm
  sharedscripts
  postrotate
    if [ -f "`. /etc/apache2/envvars ; echo ${APACHE_PID_FILE:-/var/run/apache2.pid}`" ]; then
      /etc/init.d/apache2 reload > /dev/null
    fi
  endscript
}

Fixing things Apple broke

November 14th, 2017

Every time Apple updates the OS they break things that I rely on. Sometimes it’s simple things like pinning the dock to the bottom of the screen. Other times it is things like making the menubar translucent. People usually figure out fixes and they are usually command-line switches. My Mac Mini is getting older, and recent updates to Xcode seem to have made the process of compiling apps much slower, so I’m probably going to upgrade my computer in the near future. This post will be a collection of things that I can apply to my next Mac.

Apps no longer appear in the tab switcher.
I noticed a while ago that if I close all of the windows in Safari after a while it does not appear in the app switcher list. According to Michael Tsai Safari and other apps are going into a new state called App Nap. You can prevent this with this line:


defaults write -g NSDisableAutomaticTermination -bool TRUE

Display File Extensions


defaults write NSGlobalDomain AppleShowAllExtensions -bool true

Show the Library Folder


chflags nohidden ~/Library/

Show Hidden Files
This is useful for finding things like git repositories.


defaults write com.apple.finder AppleShowAllFiles -bool true

Restart Finder
For some of these you need to restart the Finder. You can do it with the Force Quit menubar item or the command line.


killall Finder

Use Xcode’s Font in BBEdit


cp -R /Applications/Utilities/Terminal.app/Contents/Resources/Fonts/. /Library/Fonts/

Adding a subdomain with Certbot

October 16th, 2017

It’s relatively straightforward to have multiple domains and sub-domains use the same Certbot certificate when they all point to the same document root. Adding a sub-domain that points somewhere else is not as easy.

I wanted to add a beta sub-domain for testing a site rewrite. I could get the certificate to generate, but I couldn’t figure out how to modify the Apache config files for the beta. This is how I did it.

To figure out what should be done, I ran this code to expand the existing certificate.


sudo /opt/certbot/certbot-auto --installer apache --webroot -w /www/example -d example.com,www.example.com  --webroot -w /www/example_beta -d beta.example.com

To verify that it did what I wanted, I ran:


/opt/certbot/certbot-auto certificates

and got this:


Certificate Name: example.com
    Domains: example.com beta.example.com www.example.com
    Expiry Date: 2018-01-14 19:35:43+00:00 (VALID: 89 days)
    Certificate Path: /etc/letsencrypt/live/www.example.com/fullchain.pem
    Private Key Path: /etc/letsencrypt/live/www.example.com/privkey.pem

I was originally looking at the example.com file in the sites-available directory, but what I should have been looking at was in the Certbot generated files that end in -le-ssl.conf.


<IfModule mod_ssl.c>
<VirtualHost *:443>
    ServerName example.com
    ServerAlias www.example.com
    ServerAdmin root@example.com

    DocumentRoot /www/example

    CustomLog /var/log/apache2/example.com.access_log combined
    ErrorLog /var/log/apache2/example.com.error_log

    ErrorDocument 404 /missing.php
Include /etc/letsencrypt/options-ssl-apache.conf
SSLCertificateFile /etc/letsencrypt/live/www.example.com/cert.pem
SSLCertificateKeyFile /etc/letsencrypt/live/www.example.com/privkey.pem
SSLCertificateChainFile /etc/letsencrypt/live/www.example.com/chain.pem
</VirtualHost>

</IfModule>
<IfModule mod_ssl.c>
<VirtualHost *:443>
    ServerAlias beta.example.com
    ServerAdmin root@touringmachine.com

    DocumentRoot /www/example_beta

    CustomLog /var/log/apache2/example.com.access_log combined
    ErrorLog /var/log/apache2/example.com.error_log

    ErrorDocument 404 /missing.php
Include /etc/letsencrypt/options-ssl-apache.conf
SSLCertificateFile /etc/letsencrypt/live/www.example.com/cert.pem
SSLCertificateKeyFile /etc/letsencrypt/live/www.example.com/privkey.pem
SSLCertificateChainFile /etc/letsencrypt/live/www.example.com/chain.pem
</VirtualHost>
</IfModule>

You need to run this code after changing the config files.


sudo service apache2 restart

Don’t forget to change your DNS record to add the sub-domain.

Well Golly


Atheism Plus

Buy from Amazon