Tag Archives: The answer was on Google

Using cron to alert to known error conditions in log file

Recently I’ve been having issues with a VirtualBox machine crashing at random. The only common thread is a line of log file, and so far, google has not been helpful in determining the root cause. This machine is important enough that I need to know when it crashes, but not important enough to actually take the time to fix properly, so I researched and created a command to toss into cron to check the log file for the error line and email me when it finds it.

The command itself is simple. Search the log file with grep, if the search generated results send an email. I tried several iterations of this before finding one that did not email when the search was empty and also did not generate silent errors when empty as well. I run this via cron at 5 minute intervals

To use this command on ubuntu 14.04 you will need to install mailutils and moreutils

Software and versions used or mentioned:
mailutils
mailx
moreutils
ifne

Resources:
Forum post with grep | mailx solution
ServerFault post with ifne solution

PostgreSQL casting of ints to boolean fields

Recently I have been working with SQLite files part of a stand-alone application my company uses. We are getting ready to move the functionality from the stand-alone app to our web system. The app was designed with this eventual move in mind and it’s database file had been designed in such a way that we could simply do an export/import to move the data.

We used DB Browser for SQLite to export the database to a sql query we could import into our postgres database. There were some simple issues we needed to fix first (int(11) to int, tinyint(4) to boolean, etc). A simple find and replace for the file fixed those. But one issue we couldn’t fix was that PostgreSQL wouldn’t cast 0 and 1 to the boolean type. Due to the amount of data, a manual fix was impossible and we couldn’t think of a scripted way that wouldn’t cause unintended side effects. I googled the issue and there were some solutions, but it was an answer on a StackExchange question that had the fix (With a giant warning). This was an old answer and it was unclear what version of postgres the asker was using, so we tried it and it worked for us on Postgres 9.3.

Start by running the following:

Then we can run the offending SQL query. Of course we wouldn’t want to keep it like this for production, so we can reverse it with the following:

This is interesting, but I wanted to know more, all the query does is change a single field between ‘e’ and ‘a’, why does this work?

First off, pg_cast is a table that stores type conversion information, and it has a documentation page. We are changing the castcontext field of this table from ‘e’ to ‘a’ and back. Per the documentation: “e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column” So we are simply removing the need for explicit casting.

If we do a SELECT on pg_cast and see the rows the query affects, we find it only changes a single row. Looking up the oids of the cast source and target shows that it is the cast from int4 (an alias of int) to bool. Since we aren’t changing the function used to do the casting, Postgres is obviously capable of the cast already, we just needed to give it a poke.

Software and versions used or mentioned:
PostgreSQL 9.3
DB Browser for SQLite

Resources:
StackExchange question
PostgreSQL pg_cast documentation