[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.

 

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.

 

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.

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

 

 

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.

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

 

 

 

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