tech stuff

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

Spending a lot of time on the command line lately I noticed that Ubuntu does not come with Vim syntax highlighting by default. Apparently it installs a version of Vim called vim-tiny which doesn’t include any syntax highlighting.

There are two packages that you can install to get syntax highlighting to work in Vim: vim-full and vim-common. Because I didn’t have gnome installed vim-full was a very large download (like 50MB) and it errored out anyway. vim-common is definitely the way to go.

sudo apt-get install vim

The above line will replace vim-tiny with vim-common and will allow for syntax highlighting. A lot of the time you will have to enable syntax highlighting by editing the vimrc config file either in /etc/vim or in yur home directory. You will need to uncomment the line “syntax on”.

Today I created a simple shell script and I was getting a few odd errors:

cody@taylor:/var/some_folder/server$ ./process_xml.sh
-bash: ./process_xml.sh: /bin/sh^M: bad interpreter: No such file or directory

I figured it was probably a permissions error or an issue with the shebang (#!/bin/sh) line. I tried removing the shebang line, changing it to use dash or bash explicitly, chmoding to 777 and still no luck and another odd error.

cody@taylor:/var/some_folder/server$ sh process_xml.sh
: not found.sh: 4:

I then checked the log file that the commands were supposed to be writing to and it was filled with ‘^M’ on every line break and the log name itself was followed by a ‘?’. Took a minute or two but I finally clued in that I wrote that script on a windows machine and then exported it to an ubuntu linux server via subversion. It was just a basic text format issue.

Under DOS (Windows/PC) the end of a line of text is signalled using the ASCII code sequence CarriageReturn,LineFeed. Alternately written as CR,LF or the bytes 0×0D,0×0A. On the Macintosh platform, only the CR character is used. Under UNIX, the opposite is true and only the LF character is used.

After a quick :

cody@taylor:/var/some_folder/server$ apt-get install tofrodos
cody@taylor:/var/some_folder/server$ dos2unix process_xml.sh

Everything worked fine.

My webhost recently moved all my sites to a ’stabilization’ server because my sites were using far to much CPU time and Memory. After reviewing the logs it looked like some bot from India decided to repeatedly scrape one of my sites in it’s entirety without any delays between requests. So the support team over there either requires me to correct the problem or upgrade to a dedicated server plan at ridiculous costs.

Since I didn’t really think that there was a problem I emailed back about the single IP address that was causing all the issues and took steps to prevent requests from that IP address from accessing the site. The support team replied saying that my usage was still high and that I still needed to correct the problem. A little frustrated, I did some research on how to improve my site’s load time and hopefully reduce CPU and memory usage.

Most of my sites use wordpress so I found a large number of articles geared specifically to optimizing wordpress blogs. Before I tried anything I backed up my entire public_html directory and did a dump of all my mySQL databases (took almost 20 minutes for the dump).

Dealing with Plugins
The first thing I did was upgrade all my plugins. Most wordpress plugins allow you to upgrade automatically so all you really have to do is click a button and all the work is done for you. I also deactivated and deleted a surprising number of plugins that I haven’t really had any use for recently. Apparently a lot of free plugins can cause large amounts of unneccesary load on your server due to the authors not really knowing or caring how well their software performs.

Dealing with spam bots
I have been using the Akismet plugin for awhile and it has been reporting large amounts of spam comments and pingbacks. It’s not really something that most people worry about because the spam is automatically deleted after a period of time. It does however increase server load, especially if it’s in the thousands of messages a day. I found this little mod_rewrite snippet to deny any blatent spammers that don’t have a proper referer :

RewriteEngine On
RewriteCond %{REQUEST_METHOD} POST
RewriteCond %{REQUEST_URI} .wp-comments-post\.php*
RewriteCond %{HTTP_REFERER} !.*codytaylor.org.* [OR]
RewriteCond %{HTTP_USER_AGENT} ^$
RewriteRule ^(.*)$ ^http://lemonparty.org//$ [R=301,L]

Cache and Compress
Since most of my pages rarely change it’s silly to generate every page for every request dynamically. After some reading I decided to use WP Super Cache to help optimize my Wordpress sites. Of course just enabling Super Cache in the WP Super Cache plugin didn’t really improve load times for the end user but it should reduce server load immensely. What did improve load times drastically was the Super Cache Compression. This was a little more involved to get going but if you’re comfortable with copying and pasting code into a .htaccess file then it shouldn’t be difficult as long as your host supports mod_mime, mod_rewrite, and mod_deflate.

After going through all that, my sites now average at about half the load time they used to. Hopefully my web host feels that I’ve done enough to get off the ’stabilization’ server so I don’t have to transfer all my stuff to another company.

I’ve been using wordpress for awhile and it seems that whenever I find a useful plugin or theme on the web the author always seems to embed some affiliate link or some other garbage on my main page. Usually they make sure that these links show up on every page and sometimes they even make other features of the software depend on it. Usually when I try to edit out the code it’s not as easy as removing an anchor or a bit of javascript. They always seem to obfuscate what they are doing as much as possible.

The most popular way to do this is to use a combination of gzinflate, base64_decodes, eval, and str_rot13. For most users this is really difficult to decode to figure out what is really going on. I wrote this function to hopefully make peoples lives easier (or harder, depending on who you are).
Usually the obfuscated code is written in the wordpress add-on like this :

$coded_string = "eval(gzinflate(base64_decode('FZfFDs..sdff/7nr/8B')));"

If your string looks like that then the function that follows should take decode it and return the html code pretty easily.

function decode_goofy_string($coded_string)
{
  while(preg_match("/eval\(gzinflate/",$coded_string)) 
  {
    $contents=preg_replace("/<\?|\?>/", "", $coded_string); 
    eval(preg_replace("/eval/", "\$coded_string=", $coded_string)); 
  }
  
  return trim($coded_string);
}

This function is pretty straight forward but if anyone has any troubles then leave a comment or send me an email at cody@codytaylor.org

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 spent awhile after work today trying to figure out why my mapped drives in windows weren’t working. I kept getting “MSHOME is not accessible” which was annoying because I wanted to watch some cartoons. Turns out that I booted my Linux box first and it became the master browser so I had to turn off my samba server and allow the windows xp machine to become the master browser. I found this out by using these commands and tools.

For informational purposes theres
net config server
net config workstation
ipconfig /all

Those commands can be helpful at times but I found that they do not give enough information about the computer browsing services.

I found a tool called Browstat that you can download for free that gives you the clues to properly debug your windows network. Once you’ve downloaded that file and put it in your C:\Windows folder you’re going to want to open up a command window and type: browstat status.
This will give you most of the information that you need to debug your network.

If anyone knows about any other great networking tools like browstat I would really like to know about them.

I’ve been spending way to much time on my iphone lately and It’s been becoming a little unresponsive. When I scroll I notice the refresh rate is delayed and loading programs like phone, sms and safari are delayed. At first I thought that my theme was the cause but even after turning off all winterboard effects I still have the same problem. I decided to research the issue and here are my results.

• The iphone is said to never shut a program down so the memory of the device is getting saturated. To cure this you need to reset the device.You would think that powering down and back up again would do this but apparently you need to hold the power button and the home button at the same time until the iphone shuts down.

• Remove unused programs. As soon as I got rid of a few of the apps that I never use the phone started responding a lot quicker but still not quick enough.

• Turn off ssh. Running the ssh daemon in the background is using resources. If you’re not scp’d or ssh’d into the phone at the moment then why do you have it running?

• Turn off the GPS services. If you need it then turn it back on. In Settings => General click “Location Services” to off. This will also help with battery life on the iphone.

• If you’re suffering from SMS lag I’ve read that opening and closing the app store solves the problem on the iphone.

Mostly straight forward but still useful. I’ve found that Winterboard does not cause any lag whatsoever. I’ve tried multiple themes and played around with each and they don’t seem to lag the phone out at all.

I came home today to find that my computers were powered off. All my clocks were blinking. Power outages suck balls.
I don’t have a UPS but I am running a test web site on my local machine that has been getting some nice traffic lately so I should probably invest. After starting everything up I tried running one of my php scripts that access my mysql database. Error.

Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /home/ctaylor/scripts/webcrawl.php on line 8

Why isn’t my database running? I have seen this before. Possible database corruption? I prayed that this wasn’t the case and executed:

codytaylor@desktop:~$ sudo /etc/init.d/mysql start
 * Starting MySQL database server mysqld                                                                 [fail] 
codytaylor@desktop:~$ sudo /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld                                                                [ OK ] 
 * Starting MySQL database server mysqld                                                                 [fail] 

Awesome. So now what?
Hoping to get a handle on what exactly is causing it not to start I first checked /var/log/mysql.err and /var/log/mysql.log.
They were both empty. Useful right?
I then did decided to check syslog. I didn’t find anything at first but then I followed syslog while trying to restart mysql.

codytaylor@desktop:/var/log$ tail -f syslog
Apr 24 19:59:54 desktop mysqld_safe[8173]: started
Apr 24 19:59:54 desktop mysqld[8176]: 090424 19:59:54 [Warning] Can't create test file /var/lib/mysql/desktop.lower-test
Apr 24 19:59:54 desktop mysqld[8176]: 090424 19:59:54 [Warning] Can't create test file /var/lib/mysql/desktop.lower-test
Apr 24 19:59:54 desktop mysqld[8176]: 090424 19:59:54 [Warning] One can only use the --user switch if running as root
Apr 24 19:59:54 desktop mysqld[8176]: 
Apr 24 19:59:54 desktop mysqld[8176]: 090424 19:59:54  InnoDB: Operating system error number 13 in a file operation.
Apr 24 19:59:54 desktop mysqld[8176]: InnoDB: The error means mysqld does not have the access rights to
Apr 24 19:59:54 desktop mysqld[8176]: InnoDB: the directory.
Apr 24 19:59:54 desktop mysqld[8176]: InnoDB: File name ./ibdata1
Apr 24 19:59:54 desktop mysqld[8176]: InnoDB: File operation call: 'open'.
Apr 24 19:59:54 desktop mysqld[8176]: InnoDB: Cannot continue operation.
Apr 24 19:59:54 desktop mysqld_safe[8183]: ended
Apr 24 20:00:08 desktop /etc/init.d/mysql[8336]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Apr 24 20:00:08 desktop /etc/init.d/mysql[8336]: Could not open required defaults file: /etc/mysql/debian.cnf
Apr 24 20:00:08 desktop /etc/init.d/mysql[8336]: Fatal error in defaults handling. Program aborted
Apr 24 20:00:08 desktop /etc/init.d/mysql[8336]: 

I executed the restart of mysql as root didn’t I? All the folders and files are owned by the mysql user. WTF?
So it’s saying that first it can’t create a test file /var/lib/mysql/dektop.lower-test and then that it can’t open ./ibdata.
My first instinct was to set everything to 777 and just go get a beer. But probably not a good idea.
So obviously this is a permissions error. But seeing as the mysql user owns everything in this folder it’s a little confusing.
I then executed:

codytaylor@desktop:/var/lib/mysql$ sudo chmod -R 755 ./*

I tried starting mysql again. Same error.
I got frustrated and said screw it for the night. This morning while I was trying to get rid of the hangover I tried again to start mysql again and I got a totally different error in my syslog.

Apr 25 20:49:48 desktop mysqld_safe[31095]: started
Apr 25 20:49:48 desktop mysqld[31098]: 090425 20:49:48  InnoDB: Started; log sequence number 0 60274
Apr 25 20:49:48 desktop mysqld[31098]: 090425 20:49:48 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address
Apr 25 20:49:48 desktop mysqld[31098]: 090425 20:49:48 [ERROR] Do you already have another mysqld server running on port: 3306 ?
Apr 25 20:49:48 desktop mysqld[31098]: 090425 20:49:48 [ERROR] Aborting
Apr 25 20:49:48 desktop mysqld[31098]: 
Apr 25 20:49:48 desktop mysqld[31098]: 090425 20:49:48  InnoDB: Starting shutdown...
Apr 25 20:49:50 desktop mysqld[31098]: 090425 20:49:50  InnoDB: Shutdown completed; log sequence number 0 60274
Apr 25 20:49:50 desktop mysqld[31098]: 090425 20:49:50 [Note] /usr/sbin/mysqld: Shutdown complete
Apr 25 20:49:50 desktop mysqld[31098]: 
Apr 25 20:49:50 desktop mysqld_safe[31119]: ended
Apr 25 20:50:02 desktop /etc/init.d/mysql[31263]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Apr 25 20:50:02 desktop /etc/init.d/mysql[31263]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed
Apr 25 20:50:02 desktop /etc/init.d/mysql[31263]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Apr 25 20:50:02 desktop /etc/init.d/mysql[31263]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Apr 25 20:50:02 desktop /etc/init.d/mysql[31263]: 

All of a sudden it’s only a problem with the bind address? What happened to the permissions error? I didn’t reboot or anything.
Easy enough to fix though.
I open up the my.cnf file which resides in /etc/mysql/.
I look for the bind-address and noticed it was set to 192.168.2.10 instead of my new ip 192.168.1.11.
DHCP assigned it a new ip address which sucks.
So I change the line to reflect the change and restart again. All is now well.
-Cody Taylor

Can’t Mount Drive in Ubuntu Linux 8.10. Cannot mount volume. Unable to mount the volume.

mount_point cannot contain the following characters: newline, G_DIR_SEPARATOR (usually /)

After a power outage it seems something got screwed up with my drive mounting.
I got this error when I decided to access my 500gig drive on my ubuntu linux desktop. A quick google and I found the solution.

Type this into the terminal:

codytaylor@desktop:~$ gconf-editor

Looks like a linux regedit a little eh? Scary.

Go to System -> Storage -> drives.

This will show you the value of the mount point. Change it to only one word without any slashes.
Thats it. For me it put it in as /media/disk-4 but it showed up properly in all the menus. I wish it would just auto mount so I don’t have to reconfigure sharing every time this happens.

-Cody Taylor