Moving To And Away from Rackspace in the Same Year

I’ve been wanting to write this post for at least a year and I figured now would be a good time to take a break from my scheduled set of posts to discuss my transition both to and away from Rackspace last year.

Compressing MySQL Tables For Fun and Profit (or just Save Space)

Occasionally, you have a database table that holds a lot of text data and it’s not accessed regularly (log tables for example). InnoDB provides an easy way to compress your tables on disk so they use less space.

Lessons Learned From "PHP Objects, Patterns, and Practice"

As part of investing in myself I decided to read four different programming/management books in 2017. For the second quarter, I read PHP Objects, Patterns, and Practice by Matt Zandstra. This is my writeup for “proof”.

Count Distinct Values With A Condition

The other day I needed to find the total distinct count of a column as well as the distinct count of that column when a specific criteria had been met. In order to do this you can do the following:

select 
    count(distinct column) as full,
    count(distinct case when column2 = 1 then column end) as partial
from a;

This is mostly here so I’ll remember the next time I need this. :-)

Link Post and Podcast Roundup: August 2017 Edition

August’s links.

Beware of Joins on Derived Tables

MySQL is a powerful tool but it’s also a great way to shoot yourself in the foot. The other day we received reports of people finding a specific page slow but it was only slow when multiple people where using it.

Increasing Swap Space on a Running Ubuntu Server

You normally don’t want your servers using swap space because of the performance hit that occurs but for some operations you just can’t get around it. I’ve covered how to create a swap file before but what the swap you’ve created isn’t large enough?

Find Missing Columns in MySQL

With most of my projects I try not to delete data from my database. As part of this we have a “deleted” column in every table that keeps track of deleted data so it’s easy to restore and keeps valid FK relationships. Some tables (status tables, tables that form the basis for select elements) never have elements deleted so we don’t worry about this. The other day we needed to make sure every table had a deleted column.

In order to fix this we looked at the information_schema database that MySQL creates. It’s a set of dynamically generated tables that allow you to SQL your way through your database’s layout.

Below is the select we created to find these missing columns:

select TABLES.TABLE_NAME from information_schema.TABLES
left join information_schema.COLUMNS on TABLES.TABLE_NAME = COLUMNS.TABLE_NAME and TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA and COLUMNS.COLUMN_NAME = 'deleted'
where TABLES.TABLE_SCHEMA = '{yourschemaname}' and COLUMNS.TABLE_NAME is null

Link Post and Podcast Roundup: June 2017 Edition

June’s links.

Fixing the "Token undefined not a primary expression at column null of the expression" error

The other day I was working on an Angular application and I received the following error message:

Token ‘undefined’ not a primary expression at column null of the expression

This was not a very helpful error message but I tracked it down to the following:

<label ng-click=toggleIsRequiredChecked($event, activeModules, module)">
    <input type="checkbox" ng-checked="getIsRequiredChecked(activeModules, moduleName)">
    
</label>

Do you see the error? The double quote is missing from the ng-click attribute which caused the error. I added it and the code worked:

<label ng-click="toggleIsRequiredChecked($event, activeModules, module)">
    <input type="checkbox" ng-checked="getIsRequiredChecked(activeModules, moduleName)">
    
</label>