System Administration

by

[ERROR] InnoDB: Cannot add field in table because after adding it, the row size is 8572 which is greater than maximum allowed size (8126) for a record on index leaf page.

One day, suddenly, we couldn’t reload a production backup.  Oh no!  It took a bit of digging around but here is the issue, should you find yourself running into it:

Using the InnoDB COMPACT ROW_FORMAT, there are some rules InnoDB uses to decide how much data to put on the first page and how much to put on overflow pages.  While VARCHAR and TEXT type columns are typically stored on overflow pages with no problem, when using the COMPACT row format, up to the first 767 bytes of each field will be stored on the primary page.

This error happens when the amount of actual data exceeds the allows limit for a row size on the main page, which is about 50% of the innodb_page_size variable.

https://mariadb.com/kb/en/library/innodb-compact-row-format/

If you have a circumstance with say, 11 TEXT or VARCHAR fields, each with at least 767 bytes of data, and running an innodb_page_size of 16k (which results in 8126 being max row size on main page) …… then 767*11=8437 stored on main page is greater than 8126 which is the “maximum allowed size for a record on index leaf page”, and you will get this error.

The solution is to investigate the use of either DYNAMIC or COMPRESSED row format, depending on your application.

 

by

Reset MySQL or MariaDB root password

If you need a super easy way to reset your MySQL or MariaDB root password because you locked yourself out, this is how it can be done.

  • Login as root@yourhost
  • Shutdown any remote access as your server will be without password for a few moments.
  • Edit /etc/my.cnf, and under the [server] section, add
[server]
skip-grant-tables
  • Restart MySQL
  • Login and update the password with a query like this:
# mysql
MariaDB> use mysql;
MariaDB> update user set 
Password=PASSWORD("yourpass"),
authentication_string=PASSWORD("yourpass") 
where User='root';
  • Remove the “skip-grant-tables” line from /etc/my.cnf
  • Restart mysql

You are back in action!  Don’t forget to re-enable remote access.

 

by

Installing Windows Subsystem for Linux (WSL) on Windows 10

Microsoft has been doing a lot to promote linux  interoperability on windows.  I see this as a great step in the right direction, after decades of a closed and exclusive culture that has been a pain for cross-os users like myself.

Here is a quick and dirty rundown of how (March 2019) to install Ubuntu on Windows 10:

See here for the full rundown: https://docs.microsoft.com/en-us/windows/wsl/install-win10

Install the Windows Subsystem for Linux

Before installing any Linux distros for WSL, you must ensure that the “Windows Subsystem for Linux” optional feature is enabled:

  1. Open PowerShell as Administrator (search for Powershell, right click, run as administrator), and run this command: 
    Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux
  2. Restart your computer when prompted.

Install your Linux Distribution of Choice

Go to the Microsoft Store and search for Ubuntu.  Install it.

—

Once you are in Ubuntu, there are a few things to do that will make everything nicer…

Update Ubuntu and setup ssh server
sudo apt-get update
sudo apt-get dist-upgrade
sudo apt-get install openssh-server
sudo service ssh start

Fix character encoding issue (until this is fixed upstream)
sudo gunzip –keep /usr/share/i18n/charmaps/UTF-8.gz
sudo dpkg-reconfigure –frontend=noninteractive locales

Setup SSH under your user
mkdir .ssh
chmod 700 .ssh
touch .ssh/authorized_keys
chmod 600 .ssh/authorized_keys
(put your public key in there)
Setup PuTTY
You can then configure PuTTY to connect to 127.0.0.1 port 22 with your username and key (ideally from Pageant)
—
You can access your c drive under /mnt/c/
You can access your desktop using /mnt/c/Users/YOU/Desktop
This enables you to write convenient scripts in ubuntu that affect windows files on your desktop for example.
by

Centos AMI on Amazon EC2 – How To Resize The Disk

When using the official CentOS Linux AMI on EC2, it comes (currently) with only an 8GB partition.  Want to resize that?  Here is how…

First make sure you start a small (t2.micro or similar) instance to do this work with.  It should be based on the Amazon Linux AMI.  You should have root access to it.  Let’s call it “fix-the-disk-instance”.

  1. Stop the NEW instance
    1. Go to EC2 > Instances
    2. Select the Instance
    3. Make sure the Instance has a name that makes sense (e.g. snap.appcove.net)
    4. Click “Actions” > “Instance State” > Stop
  2. Detach the block device
    1. Go to EC2 > Volumes
    2. Find the volume by looking under Attachment Information for the instance name (e.g. snap.appcove.net)
    3. Select the volume
    4. Click “Actions” > “Detach Volume”
  3. Attach the block device to “fix-the-disk-instance”
    1. Go to EC2 > Volumes
    2. Find the volume by looking under Attachment Information for the instance name (e.g. snap.appcove.net)
    3. Select the volume
    4. Click “Actions” > “Attach Volume”
    5. Attach it to “fix-the-disk-instance” so we can fix it up
  4. Start “fix-the-disk-instance” and login
    1. Go to EC2 > Volumes
    2. Select the Instance “fix-the-disk-instance”
    3. Click “Actions” > “Instance State” > Start
    4. Wait for it to start
    5. SSH into it and escalate to root
  5. Run `parted` command and print list
    1. As root, run the following command: parted
    2. Type the following command: print all<enter>
    3. Find your disk in the output and note the path (e.g. /dev/xvdf)
    4. Quit the program by typing: quit<enter>
  6. Resize the partition:
    1. Run fdisk on the device path found above (e.g. fdisk /dev/xvdf)
    2. List the partitions
      1. Type: p<enter>
      2. Take note of the “Start” column (e.g. 2048)
    3. Delete the partition
      1. Type: d<enter>
    4. Create a new one by typing:
      1. n (for new)
      2. p (for primary)
      3. 1 (for first partition)
      4. Type first sector the same as the Start column above (e.g. 2048)
      5. Last sector should be the default (end of disk)
      6. w (for writing to disk)
  7. Now check the partition and resize it
    1. Run this command making sure to use the right device path (but add a 1 to the end for the first partition)
      1. e2fsck -f /dev/xvdf1
    2. Resize the partition making sure to use the right device path (but add a 1 to the end for the first partition)
      1. resize2fs /dev/xvdf1
  8. Shutdown the instance “fix-the-disk-instance”
    1. Go to EC2 > Instances
    2. Select the Instance “fix-the-disk-instance”
    3. Click “Actions” > “Instance State” > Stop
  9. Detach the block device and reattach it to the original server
    1. Go to EC2 > Volumes
    2. Find the volume by looking under Attachment Information for the instance name (e.g. fix-the-device-instance).
    3. Make sure it is the right volume by looking at the ID/Size.
    4. Select the volume
    5. Click “Actions” > “Detach Volume”
    6. Click “Actions” > “Attach Volume”
    7. IMPORTANT: enter “/dev/sda1” as the device so it attaches as the root volume
    8. Attach it to the original server
  10. Start “fix-the-disk-instance” and login
    1. Go to EC2 > Volumes
    2. Select the original instance (e.g. “snap.appcove.net”)
    3. Click “Actions” > “Instance State” > Start
    4. Wait for it to start

 

And that’s all!

 

 

2016-03-20 - B162957

2016-03-20 - B164242

2016-03-20 - B164338

 

 

by

How to install a Trusted Certificate Authority on Windows 7

At my company AppCove, we have our own certificate authority that we use with development servers and sites.  This allows us to (at no additional cost) use HTTPS and SSL for all of these alternate domains and subdomains.

The downside is that our certificate is not trusted by any stock browser or operating system.

Therefore, to prevent getting an ugly and scary SSL warning, anyone who needs to visit these (private audience) sites must first “trust” our certificate authority.

A note on security.  If you are telling your computer to trust a certificate authority, then you must really actually “trust” that authority.  If the signing key fell into the wrong hands, then they could create fake certificates for other sites you visit, like http://www.google.com, and intercept your data.  At AppCove, we use aggressive security measures to protect the certificate authority key (as we do for customer data and applications).

In this example, I am causing my Windows 7 workstation to trust appcove-ca-cert.pem.crt

a

b

c

d

e

f

g

h

i

j

k

l

m

n

— Start of slight detour — 

If you want to verify it was installed, do this.  Otherwise, skip the next 2 screens.

o

p

— End of slight detour —

q

r

At this point, you should be able to visit any HTTPS site that was signed with this certificate authority and your browser will indicate that it is a secure connection.

by

nginx: how to specify a default server

Several years ago when I started using nginx, I was under the mistaken assumption that

server_name _;

was a wildcard server name and would be used if no other server names matched.

Nope.

I made a change on a production system, adding a new site on an existing IP address.  What harm could that cause, right?

After several clients quickly and graciously notified us that the wrong site was coming up when you visited their domain, I quickly tracked the problem down.

First you need to realize that server_name _ is actually not special.  It is just a non-match.

Second you need to realize that in the event of no matches, nginx will select the first server{} block and use that.

This means that the ORDER of your server blocks is critical if you are using `server_name _;`. 

In our case, the order was incorrect, and my new domain was picking up all requests for that IP address.  I tell this because I believe a number of system administrators have this incorrectly configured and waiting to bite them.

There is a better way.

The nginx `listen` directive includes a `default_server` option that looks like this:

server{
   listen 1.2.3.4:80 default_server;
   ...
}

From http://wiki.nginx.org/HttpCoreModule#listen

If the directive has the default_server parameter, then the enclosing server {…} block will be the default server for the address:port pair. This is useful for name-based virtual hosting where you wish to specify the default server block for hostnames that do not match any server_name directives. If there are no directives with the default_server parameter, then the default server will be the first server block in which the address:port pair appears.

The moral of the story

It is better to use the correct mechanism (above) than relying on a single non-matching server_name.

I hope someone finds this useful!

Reference: http://stackoverflow.com/questions/9454764/nginx-server-name-wildcard-or-catch-all

 

 

 

by

How to Generate a SSH Keypair (public/private) on Windows

Have you ever been asked to generate an SSH keypair in order to gain access to a server, github, or an sftp site?

Here is how on windows.

First, download puttygen.exe from here:

http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

 

Second, run puttygen.exe and follow these instructions:

(except, put your name instead of Sharon)

(On step 8, copy and paste this and send it to whomever requested it)

puttygen instructions

by

Why you should consider using the IUS Community Project

From http://iuscommunity.org/

“The IUS Community Project is aimed at providing up to date and regularly maintained RPM packages for the latest upstream versions of PHP, Python, MySQL and other common software specifically for Redhat Enterprise Linux. IUS can be thought of as a better way to upgrade RHEL, when you need to.”

Our Perspective at AppCove

http://www.appcove.com/yumrepo/

Imagine being able to combine the rock-solid stability of RedHat Enterprise Linux (or Oracle, Centos, Scientific) with the latest versions of popular software packages like PHP, Python, MySQL, mod_wsgi, redis, and others? The IUS Community Project is the answer.

Enterprise Linux is great for the stability, security, and compatibility. But sometimes you need a newer version of an installed package, like Python. At the time of this writing, RedHat is still not providing any standard way to obtain Python 3.2, MySQL 5.5, or PHP 5.4, years after they have been released.

The IUS Community project has provided AppCove, Inc. and all of our clients the perfect mix of stability and functionality. IUS has enabled us to focus on our core competencies (software development) while being confident that the packages we use are as secure and up-to-date as possible.

Our confidence in the IUS team is second to none. AppCove has worked in close conjunction with the IUS team on several occasions, and they have always been impeccably experienced, knowledgeable, and professional.

We highly recommend that any users of RedHat Enterprise Linux, Oracle Enterprise Linux, Scientific Linux, or Centos Linux take a close look at the IUS Community Project for their servers.

by

How to upgrade ImageMagick on RedHat Enterprise Linux 5

ImageMagick 6.2.8 comes with RHEL5.  This is pretty ancient in terms of being able to do some more advanced manipulations, like -kerning, -distort, etc…

As it turns out, ImageMagick publishes their own RPM for RHEL.  But if you try to just install it directly, you get something like this:

[root@boss ~]# rpm -Uvh http://www.imagemagick.org/download/linux/CentOS/x86_64/ImageMagick-6.7.9-6.x86_64.rpm
Retrieving http://www.imagemagick.org/download/linux/CentOS/x86_64/ImageMagick-6.7.9-6.x86_64.rpm
error: Failed dependencies:
libHalf.so.4()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
libIex.so.4()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
libIlmImf.so.4()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
libImath.so.4()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
libfftw3.so.3()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
libgs.so.8()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
libjasper.so.1()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
liblcms.so.1()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
libltdl.so.3()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
liblzma.so.0()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
librsvg-2.so.2()(64bit) is needed by ImageMagick-6.7.9-6.x86_64
libwmflite-0.2.so.7()(64bit) is needed by ImageMagick-6.7.9-6.x86_64

The answer is – use yum to take care of it:

As root, download the correct RPM from the ImageMagick site.  Then uninstall the system ImageMagick.  Then install this one.

http://www.imagemagick.org/script/binary-releases.php

wget http://www.imagemagick.org/download/linux/CentOS/x86_64/ImageMagick-6.7.9-6.x86_64.rpm
yum erase ImageMagick</p><p>yum install --nogpgcheck ImageMagick-6.7.9-6.x86_64.rpm

Note: because the version # is beyond the one shipped with RHEL, it will not be updated automatically.  You will need to monitor ImageMagick for security updates and install them yourself.

Note: this is not recommended — replacing a RHEL package.  But sometimes it is needed.

wget http://www.imagemagick.org/download/linux/CentOS/x86_64/ImageMagick-6.7.9-6.x86_64.rpm
yum erase ImageMagick
yum install --nogpgcheck ImageMagick-6.7.9-6.x86_64.rpm
by

Essentials of DNS: understand the basics well

DNS stands for “Domain Name Service”.  It is the mechanism in which the internet is able to map a name (eg http://www.sgasoftware.com) to an IP address (eg 12.34.56.78).

This writeup is not designed to be 100% technically accurate.  Rather, it is designed to give a layman understanding of relevant parts of the DNS system.
Much more comprehensive information can be found here: http://en.wikipedia.org/wiki/Domain_Name_System

A bit on IP Addresses and Routing

Packets of data to move to and fro across the internet based entirely on IP Address.  At every “network meeting point” on the internet, there is a router which examines all traffic to find out where it is headed, and then sends it to the next step.  This process is repeated at each “network meeting point” until the traffic arrives at the destination.
There is a popular utility called “Trace Route”, which will demonstrate all of the routers that a packet of data must go through to get from where you are to where it is headed.  In the following example, I ran a traceroute to http://www.google.com.
Notice the first step was a DNS lookup, converting www.google.com to 72.14.204.99. 
c:\>tracert www.google.com
Tracing route to www.l.google.com [72.14.204.99]
over a maximum of 30 hops:
  1    <1 ms    <1 ms    <1 ms  192.168.0.1   (our office router/gateway))
  2     1 ms    <1 ms    <1 ms  10.1.10.1     (our cable modem (another router/gateway)
  3    15 ms    13 ms    12 ms  96.179.208.1
  4     9 ms     9 ms    10 ms  ge-2-20-ur01.blairsville.pa.pitt.comcast.net [68.86.146.165]
  5    14 ms    10 ms     9 ms  te-9-1-ur01.indiana.pa.pitt.comcast.net [68.86.100.178]
  6    10 ms    55 ms    30 ms  te-9-3-ur01.punxy.pa.pitt.comcast.net [68.87.173.81]
  7    12 ms    18 ms    19 ms  te-9-1-ur01.ruralvalley.pa.pitt.comcast.net [68.86.100.122]
  8    21 ms    63 ms    53 ms  te-6-3-ar01.pittsburgh.pa.pitt.comcast.net [68.87.173.73]
  9    28 ms    29 ms    27 ms  te-3-1-0-0-cr01.chicago.il.ibone.comcast.net [68.86.90.181]
 10    25 ms    28 ms    25 ms  pos-1-6-0-0-pe01.350ecermak.il.ibone.comcast.net [68.86.87.130]
 11    43 ms    44 ms    44 ms  as15169-1.350ecermak.il.ibone.comcast.net [75.149.230.198]
 12    48 ms    52 ms    58 ms  209.85.254.130
 13    37 ms    39 ms    39 ms  209.85.248.222
 14    36 ms    43 ms    41 ms  66.249.94.46
 15    42 ms    39 ms    39 ms  iad04s01-in-f99.1e100.net [72.14.204.99]
Trace complete.
In true geek fashion, after 15 hops, our packet reached 1e100.net.  Google was derived from the word Googol, which is a number with 1 and 100 zeros.  Henceforth, 1e100.net stands for something to that effect.

Why does this matter?

It is important to understand that computers are binary, and therefore require input to be ultimately converted to binary.  An IPv4 address is simply 32 bits, but rather than presenting it as 11000000.10101000.00000000.00000001, humans find it more convenient to look at it like 192.168.0.1.  However, that is still a far cry from a human readable name like mail.domain.com.
This is where DNS comes into play.  DNS is a system which allows people to use names, while still providing for computers to use the numbers.  Of the many benefits of DNS, it allows you to move a domain to another IP address while keeping the domain name the same. 

Common Types of DNS Records

DNS records come in several types:
A record
Points to a specific IP address.  “A” stands for “Address”.

This means that appcove.com should resolve to 74.205.111.168
appcove.com.            70755   IN      A       74.205.111.168
CNAME record
Points to another record.  “CNAME” stands for “Canonical Name Record”
This means that http://www.appcove.com should resolve to appcove.com and in turn resolve to 74.205.111.168
www.appcove.com.        86400   IN      CNAME   appcove.com.
MX record
Specifies a domain which will handle incoming email for the domain.
This means that mail sent to @appcove.com will be handled by APPCOVE.COM.S9A1.PSMTP.com, and if that does not work, send it to APPCOVE.COM.S9A2.PSMTP.com and so on.  The weight indicates which server should be tried first — the lower the weight, the better.
appcove.com.            86400   IN      MX      40 APPCOVE.COM.S9B2.PSMTP.com.
appcove.com.            86400   IN      MX      10 APPCOVE.COM.S9A1.PSMTP.com.
appcove.com.            86400   IN      MX      20 APPCOVE.COM.S9A2.PSMTP.com.
appcove.com.            86400   IN      MX      30 APPCOVE.COM.S9B1.PSMTP.com.
NS record
Delegates a DNS zone to use the given authoritative name servers.  “NS” stands for “Name Server”.
This means that DNS for appcove.com is to be handled by either ns.rackspace.com or ns2.rackspace.com.
appcove.com.            57957   IN      NS      ns.rackspace.com.
appcove.com.            57957   IN      NS      ns2.rackspace.com.
TXT record
Specifies arbitrary text associated with a name.  “TXT” stands for “Text”
The provides a mechanism for the DNS system to be extended with different bits of text about a domain name.  The following TXT record stores SPF information, used to specify what servers are allowed to send mail on behalf of appcove.com.
appcove.com.            86400   IN      TXT     "v=spf1 include:fogcreek.com include:aspmx.googlemail.com ip4:207.126.144.0/20 ip4:64.18.0.0/20 ip4:74.125.148.0/22  ~all"

DNS Propagation

DNS is a distributed system.  This means that there are thousands of DNS servers scattered around the globe.  Most internet providers will have their own DNS servers.  Most hosting providers will have their own DNS servers.  Etc…
How do they all stay in sync?  They do and they don’t!
Every DNS entry has a corresponding TTL value associated with it.  TTL means “time to live”.  This value, expressed in seconds, states how long a given DNS entry is valid before it must be refreshed from the “authoritative” DNS server for the given domain.  Typically, the TTL is set to 86,400 seconds, or 1 day.
With a TTL of 1 day, any changes to authoritative DNS records may take up to a full day (or more) to propagate around the internet.  This is worth keeping in mind before you engage in any important DNS changes.
The proper way to change DNS is to first, at least 48 hours prior to the transition period, change the TTL to a very low value (like 5 minutes).  It will still take at least a day for this to fully propagate to all relevant DNS servers.   However, once they are all aware that the TTL is 5 minutes, they will frequently check your authoritative server for updates every 5 minutes or each time a new request comes in for one of your DNS records, whichever is longer.
In other words, DNS servers only look up what they are asked to look up.  Then they retain it for the TTL period, and finally discard it (only to repeat if needed).
Authoritative DNS
Every domain must be registered with a registrar.  Part of this registration information for every domain, is the DNS servers to use as “authoritative dns information” for that domain.   These NS records are transmitted to the root nameserver system. 
Every top level domain (com, net, edu, org, us, gb, br, uk, au, etc…) all have their own authoritative DNS servers.  This information is kept in the world’s root name servers, which are currently:
a.root-servers.net.     117655  IN      A       198.41.0.4
a.root-servers.net.     196594  IN      AAAA    2001:503:ba3e::2:30
b.root-servers.net.     113829  IN      A       192.228.79.201
c.root-servers.net.     120655  IN      A       192.33.4.12
d.root-servers.net.     113757  IN      A       128.8.10.90
e.root-servers.net.     115554  IN      A       192.203.230.10
f.root-servers.net.     121256  IN      A       192.5.5.241
f.root-servers.net.     280321  IN      AAAA    2001:500:2f::f
g.root-servers.net.     113755  IN      A       192.112.36.4
h.root-servers.net.     116456  IN      A       128.63.2.53
h.root-servers.net.     206320  IN      AAAA    2001:500:1::803f:235
i.root-servers.net.     115856  IN      A       192.36.148.17
i.root-servers.net.     337685  IN      AAAA    2001:7fe::53
j.root-servers.net.     119155  IN      A       192.58.128.30
If you lookup the NS record for com at one of the above root servers, you will be told this:
d.gtld-servers.net.     16131   IN      A       192.31.80.30
g.gtld-servers.net.     74653   IN      A       192.42.93.30
f.gtld-servers.net.     111162  IN      A       192.35.51.30
c.gtld-servers.net.     33590   IN      A       192.26.92.30
j.gtld-servers.net.     171076  IN      A       192.48.79.30
a.gtld-servers.net.     2665    IN      A       192.5.6.30
a.gtld-servers.net.     8352    IN      AAAA    2001:503:a83e::2:30
i.gtld-servers.net.     78083   IN      A       192.43.172.30
k.gtld-servers.net.     14957   IN      A       192.52.178.30
b.gtld-servers.net.     127812  IN      A       192.33.14.30
b.gtld-servers.net.     168301  IN      AAAA    2001:503:231d::2:30
e.gtld-servers.net.     29652   IN      A       192.12.94.30
h.gtld-servers.net.     47519   IN      A       192.54.112.30
l.gtld-servers.net.     41990   IN      A       192.41.162.30
m.gtld-servers.net.     42456   IN      A       192.55.83.30
If you lookup the NS record for appcove.com at one of the above “com” servers, you will be told this:
appcove.com.            172800  IN      NS      ns2.rackspace.com.
appcove.com.            172800  IN      NS      ns.rackspace.com.
Finally, we have the authoritative DNS servers for appcove.com.  Now, if you lookup the record for http://www.appcove.com at ns.rackspace.com, you will be told this:
www.appcove.com.        86400   IN      CNAME   appcove.com.
Finally, if you lookup appcove.com at it’s authoritative server ns.rackspace.com, you will be told this:
appcove.com.            86400   IN      A       74.205.111.168