Monday, July 6, 2015

2015 New Brunswick Moose Draw Results are in!

The results of the NB Moose Draw are in and if you have your Medicare number or Outdoor card number you can check if you got drawn here:

It's really amazing on how hard that link has been to find,  it's not even up on the DNR website yet!

Thursday, April 9, 2015

Enabling MySQL Query Logging

In a perfect world, I would like to imagine we'd never had to poke around the internals of software systems to debug the side-effects and performance issues caused by applications and users.

I, however, do not live in a perfect world.   I do live in a world where sometimes it's unclear if the problem is a DBA or the application and sometimes neither are any help in telling me what they did and when... Or I just don't trust them.

Also when your dealing with questions about MySQL performance, 'show processlist' is a good start but it fails to show short lived queries that are running a huge number of times per second.

Luckily, I have a few tools that are my 'go to' tools for problem solving.  For example, packet captures rock and they are my typical go to solution for debugging SIP but sometimes there is a solution which is much more elegant. In these cases with MySQL, enabling the general log gives great insight in to what is happening in your MySQL server.

The MySQL general log lets us figure out which clients did what when and how long that query took.

Turning on the Logging

The following statement turns this logging on.  I typically use a 'select now()' statement in alot of my work so that I can quickly copy and paste such things into our issue tracking software.

mysql> select now(); set global log_output="TABLE";SET GLOBAL general_log = 'ON';
| now()               |
2015-04-04 01:04:28 |

If anyone is worried about the impact of the extra logging, check this out:

Since we have setup the general logging to go to a DB table, we can simply truncate this any time during production.

Intro to Using this for Query Analysis 

Now for the fun part.  You can analyse the queries with simple SQL select statements.  Check this table definition on how to structure your queries.

mysql> SHOW CREATE TABLE mysql.general_log;
CREATE TABLE `general_log` (  `event_time` timestamp ,  `user_host` mediumtext NOT NULL,  `thread_id` int(11) NOT NULL,  `server_id` int(10) unsigned NOT NULL,  `command_type` varchar(64) NOT NULL,  `argument` mediumtext NOT NULL)

Now we can start probing into the data, here is an example to get some of the INSERTs into the databases.   

mysql> select event_time, argument from mysql.general_log where command_type ='Query' and argument like 'INSERT%' limit 100;

It should also be possible to turn this on and analyse the frequency of various queries and look for queries that can be improved or even recommended for caching at the application level.

If you don't know about MySQL's 'EXPLAIN' feature,  I highly recommend learning about it.

Turning the Query Log Off

Don't forget to turn the Query log off!  On a busy database, this is going to consume resources and unchecked, it will fill the disk eventually.

mysql>  select now(); set global log_output="TABLE";SET GLOBAL general_log = 'OFF';
| now()               |
| 2015-04-04 09:33:03 |


MySQL's logging features and other analytic tools like 'EXPLAIN' should be used at every stage of a product life cycle to figure out where bottle necks are and to determine a baseline normal.   This can also be used to help track down which database user maybe corrupting data or not following business rules.

Wednesday, April 8, 2015

Free Topographical Maps

I've been doing more hiking these days and I've come to the realization that I need better topo's for my jaunts into the wilderness.

I also need a new compass to accompany me as well, but first things first!  I wanted a legit source that I could print the topographical map out on for the area I plan on exploring.

These topo maps are better then the base maps that Google uses.

Thursday, March 26, 2015

Things you don't want to find in your Server Room

One of my buddies that will remain nameless found this gem while out in the field.

Story is that this is the 'new' switch someone is forcing him to deploy...

Just in case that is hard to read, here is the writing on that piece of tape:

Personally I've stopped labeling equipment as defective,  I'm now in the habit of tossing the piece of junk out as soon as it's giving me grief.  This kind of irrational penny pinching is just a waste of everyone's time down the road.

Monday, January 26, 2015

How to fix: ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

If you have seen the follow message when  trying to shut down MySQL or Percona XTRADB Server and you see the following message:

 ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

and the process did not shutdown, the problem is not a bug in MySQL or Percona, the problem is that the password stored on disk for that account doesn't match what is stored in the 'mysql.Users' table.

The following is all one line and should fix the problem temporarily.

 echo "update mysql.user set password = PASSWORD('`sudo cat /etc/mysql/debian.cnf | grep password | tail -1 | awk '{print $3}'`') where user = 'debian-sys-maint';flush privileges;" | mysql -uroot -p

The likely cause of this root cause problem is that one of two things:

1. You have moved the '/var/lib/mysql/' directory from one server to another server possibly with rsync/scp with downtime or with Percona's backup tools with out causing down time.

2. You have replication setup and any updates to the 'mysql.Users' table was propagated out to all the slaves.

If you think that the root cause is more like the first case, I would recommend the treatment at the beginning of this post. If your root cause is more like the second case, I would recommend simply synchronizing the 'debian.cnf' file across all the MySQL nodes.

Alternatively, I've seen some people trying to use a combination of settings to not replicate the updates to the 'mysql.Users' table and they seem to have varying success in that approach. Unless you have a need to have different users on each node, I would recommend against this as it does seem to be tricky especially if your using ROW based replication.

Tuesday, January 20, 2015

How to Create Shared Folders with LXC

If you have ever used Shared Folders in either VMWare or Virtual Box,  you might have come to enjoy the ability to access files seemlessly between the Host and the Guest operating systems.

With LXC we can achieve the same thing with a few simple steps.  This method does have some shortcomings that may or not be a show stopper depending on the use case. Personally, I don't like this method but it is handy to know how to do this.

In this example I going going to show how to share a directory between a host and a guest name guest01.

First create the shared folder on the host:

$ mkdir /home/justin/Documents/guest01/

Next, edit the guest's fstab  file to include the following line:

/home/justin/Documents/guest01  /var/lib/lxc/guest01/rootfs/home/justin/Documents/ none bind 0 0  

Next, reboot your container. 

You'll find that you can now share files between your host and guest.

Why I don't like this method

There is two reasons why I do not like this method.  The first problem is that it breaks the isolation the guest and host have to some degree.   One really awesome thing about LXC and the virtualization trend in general is that added layer of security we have by layering our software stack.

The second reason why I do not like this method is that the file ownership and permissions problems that arise because of this solution.   A few other blogs and forum threads recommended using 'chmod -R 7777 ~Documents/guest01' but this is a horrible approach to managing permissions.  This completely clobbers any sane file permissions and allows any user to read and delete those files.

This approach does have it's uses in the right circumstances, like read-only access to a set of files or as a simple way of moving files but please be careful when using this tactic.