Updating table with values from another table

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

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.


<Directory /var/www/>
  Options Indexes FollowSymLinks
  AllowOverride None
  Require all granted
</Directory>

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.

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
}