Cody Taylor

I had to write some reports for some legacy software today and I was unpleasantly surprised with the results of my SQL queries. I was selecting dollar values and summing them to for the monthly spending of certain individuals. Easy enough right? I wrote a query something like this :

SELECT SUM(t.money_spent) as sum_of_spent,
c.customer_name 
from transactions t 
join customers c on t.customer_id=c.customer_id 
group by customer_name order by c.customer_name asc

I ended up getting numerical values that were 10 decimal places long with seemingly random numbers. After checking to make sure the database didn’t have any odd entries I stumbled on this bug report.
The ‘money_spent’ column had a data type of float, which is a waste, but I still don’t think that it should sum up incorrectly. When I select individual values I get proper two decimal results.
Apparently floats and doubles use floating point math, which deals with approximate values for numbers and can thus result in confusion like this. It seems that it isn’t really possible to store 0.1 in a column of type float. You can only store 0.00999999977648258. This behavior is a little silly but easily fixed by using the ROUND() function :

SELECT SUM(ROUND(t.money_spent)) as sum_of_money_spent,
c.customer_name from transactions t 
join customers c on t.customer_id=c.customer_id 
group by customer_name order by c.customer_name asc

If you have root access to a linux server and you don’t have the root mysql password, but need it, then you can easily reset the root mysql password in just a few commands. These commands probably differ depending on what linux distro you use. I was using Ubuntu 9.04 (Jaunty Jackalope) when I wrote this.

Firstly you will want to turn the mysql service off.

codytaylor@server:~$ sudo /etc/init.d/mysql stop
 * Stopping MySQL database server mysqld   

Now we restart the mysql server with the ‘skip-grant-tables’ option which basically allows anyone to do whatever they like. It’s usually preferable to include the ‘skip-networking’ option so that only localhost (you) have access to the naked database.

 codytaylor@server:~$ sudo mysqld_safe --skip-grant-tables --skip-networking &

Now all that is left is actually changing the root password. Log into the mysql monitor and change the root password.

codytaylor@server:~$ mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('password') WHERE User='root';
mysql> FLUSH PRIVILEGES;

Those commands will reset the root mysql password to ‘password’. Now you’ll probably want to restart the mysql service and have it run normally.

codytaylor@server:~$ sudo /etc/init.d/mysql restart

If you are using windows and you want to reset the mysql root password then check the mysql documentation.

Uniform resource locators (URL) are starting to get very long and I’m getting sick of typing ridiculously long strings into safari on my iphone. I don’t really care about the extra bandwidth, It’s just annoying when you’re on the phone telling someone to go check out a 67 character long url and they mistype it three times.

I’ve just started using the tiny url service and so far it’s been useful. On one of my sites I wanted to generate a tiny url for each of my pages to make them quicker to type in and also so people don’t know what get variables I’ve set until they get there. Here is the PHP function that I used to generate a tiny url for every page on the site.

There doesn’t seem to be any documentation at all on the Tiny URL website about this so I’m not sure if it’ll change in the future.
This function passes your desired url to the api-create.php script on the tinyurl domain which returns a nice short url that isn’t a pain to put into your iphone.

function get_tiny_url($url)
{
  $new_url = file_get_contents('http://tinyurl.com/api-create.php?url='.$url);
  return $new_url;
}

$tiny_url = get_tiny_url("http://codytaylor.org");

Not much to it but it made my life easier.

I tried a ‘tail settings.pyc’ in a new django project and I guess pyc means compiled?
Kinda neat what it did to my terminal though. Those lines ending in the dollar symbol are actually me hitting enter, that is my prompt.