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

WordPress is by far the most popular content management system for blog hosting. The wordpress content management system uses the mysql database. If you have a big site with a large number of posts then it can be handy to search the content of every post to find certain text. Sometimes you may even need to replace certain keywords with other keywords. As with most content management setups there is probably a plugin that will do just that, but it is far easier to just use basic sql if you know the structure of the wordpress database.
Within either phpmyadmin or mysqlyog (depending on what you are using) you can use this sql query to find the text that you are looking for:

select * from wp_posts where post_content 
like '%content management system%';

The ID that you get back is basically the page id. For example, if I query my database and get back an id of 13449 then that content will reside at http://codytaylor.org/?p=13449. Other useful columns are the post_content which is the content text of the post, post_name which is the title of the post, and the guid which is the full url (before mod_rewrite changes it) so you don’t have to copy and paste the id and append it to your url.

If you need to search and replace some text in more than one post then you can use this sql :

UPDATE wp_posts SET post_content = REPLACE (
post_content, 'content management system', 'CMS');

That SQL query will replace the ‘content management system’ with ‘CMS’.

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.

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.

If you need to install MySQL databases on a number of machines with roughly the same configuration then it becomes extremely tedious to run the installer wizard on each machine. You can download the MySQL server as a msi package which allows you to install with the MSIEXEC DOS command in windows hands free. To install all the necessary files for MySQL to run you need to type this command at the console:

msiexec /qn /i mysql-essential-5.1.37-win32.msi INSTALLDIR=C:\MySQL

The ‘/qn’ switch makes this install quiet. In this example I chose ‘C:\MySQL’ as the install directory. Feel free to replace that path with whatever you choose.

Just because MySQL is installed and has all the appropriate files registered doesn’t mean that it’s useful. You will probably want it to run as a service, have it listen on a certain port, and have a root user already set up. This can be done with the MySQLInstanceConfig.exe program, although the arguments are a little more involved.

C:\MySQL\bin\MySQLInstanceConfig.exe -i -q 
"-lC:\MySQL\mysql_install_log.txt" 
"-pC:\MySQL\bin" "-tC:\MySQL\my-template.ini" 
"-cC:\MySQL\my.ini" -v5.1.37 
ServerType=DEVELOPMENT 
DatabaseType=MIXED 
ConnectionUsage=DSS 
Port=3306 
ServiceName=MySQL
RootPassword=root1234 
SkipNetworking=no 
AddBinToPath=yes

The entire string above must be run as one line. If you just copy and paste then the console will error out. Most of the arguments above are straight forward if you’ve ever configured a MySQL server before but just in case I’ve detailed the parameters below.

-n product name
-p path of installation (no \bin)
-v version

Actions:
-i (install instance)
-r (remove instance)
-s (stop instance)
-q (be quiet)
-lfilename (write log file)

When launched manually, these can also be submitted
-t<.cnf template filename>
-c<.cnf filename>

Use the following options to define the parameters for the configuration file generation.
ServiceName=$
AddBinToPath={yes | no}
ServerType={DEVELOPMENT | SERVER | DEDICATED}
DatabaseType={MIXED | INNODB | MYISAM}
ConnectionUsage={DSS | OLTP}
ConnectionCount=#
SkipNetworking={yes | no}
Port=#
StrictMode={yes | no}
Charset=$
RootPassword=$
RootCurrentPassword=$

So if you use the example above you will get a basic mysql installation. When I used these commands I put them in a batch file followed by this command:

mysql –user=user_name –password=your_password db_name < create_database_and_tables.sql The ‘create_database_and_tables.sql’ obviously has all the sql code to create the MySQL databases and tables that are needed. The batch file installed, configured, and structured my MySQL databases. I spent awhile yesterday looking for a post like this so hopefully this saves someone some time.

Technorati Profile

Gary Pendergast writes “Monty Widenius, the ‘father’ of MySQL, has created the the Open Database Alliance, with the aim of becoming the industry hub for the MySQL open source database. He wants to unify all MySQL-related development and services, providing a potential solution to the fragmentation and uncertainty facing the communities, businesses and technical experts involved with MySQL, following the news of the Oracle acquisition of Sun.” Related to this, an anonymous reader writes that “MySQL has announced a project to refactor MySQL to be a more Drizzle-like database.”

Read more of this story at Slashdot.

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

The Register writes “Sun invited Oracle president Charles Phillips and chief corporate architect Edward Screven to an employee-only town hall this Wednesday, where they took questions on what’s coming. They said they’d be ‘crazy’ to close Java, that Oracle ‘needs’ MySQL, and all Sun’s processors look appealing. They hedged on OpenOffice — Phillips said he couldn’t comment on any product line — and on Sun’s work in high-performance computing. Screven made it pretty clear the Sun vision of cloud computing does not fit with Oracle’s; Oracle sees itself as a provider of infrastructure like virtualization to make clouds, not a provider of hosted services. As for who’s staying and who’s getting cut at Sun: Phillips said Oracle needs Sun, but warned ‘tough decisions’ will be coming. Don’t forget, this is the company that couriered pink slips to the PeopleSoft staff it cut following that acquisition.”

Read more of this story at Slashdot.

viktor.91 writes “Sun Microsystems announced three new MySQL products: MySQL 5.4, MySQL Cluster 7.0 and MySQL Enterprise Partner Program for ‘Remote DBA’ service providers.” which showed up in the firehose today next to Glyn Moody’s submission where he writes “Michael Widenius, founder and original developer of MySQL, says that most of the leading coders for that project have either left Sun or will be leaving in the wake of Oracle’s takeover. To ensure MySQL’s survival, he wants to fork from the official version — using his company Monty Program Ab to create what he calls a MySQL “Fedora” project. This raises the larger question of who really owns a commercial open software application: the corporate copyright holders, or the community?”

Read more of this story at Slashdot.