Posts

  • 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

    Link Post Logo

    August’s links.

    Read More
  • 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.

    Read More
  • 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?

    Read More
  • 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

    Link Post Logo

    June’s links.

    Read More
  • 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>
  • Let's Invest in Ourselves For 2017 Update

    In my Let’s Invest in Ourselves For 2017 post I laid out the books I was planning on reading in 2017 but plans changed so we’re going to read PHP Objects, Patterns, and Practice instead.

  • Lessons Learned From "Working Effectively With Legacy Code"

    As part of investing in myself I decided to read four different programming/management books in 2017 to improve myself. For the first quarter, I read Working Effectively with Legacy Code by Michael Feathers (even though I’m not getting this writeup out until the second quarter I swear I read it in Q1). This article is my way of keeping myself accountable for the actual reading. :-)

    Read More
  • Fixing the "Error in configuration. Last filter is multi filter. Needs to be non-multi one. Stopping" error

    Last week we ran into an interesting problem with our MySQL master/slave replication. During the week (when we’re working) the propagation delay from master to slave is less than a second but for some reason on Saturdays the delay jumps up for several minutes (still trying to figure this one out). This caused a problem where multiple records would try to be inserted for the same data (read fails to find the new row which causes it to assume there is a need for a new record).

    Read More

subscribe via RSS